UFOSQL MANUAL
FEATURES AND RESTRICTIONS
----------------------------------------------------------------------------------
* Column types are below:
----------------------------------------------------------------------------------------------------------------------
| type_name | data_length | option | option_info |
----------------------------------------------------------------------------------------------------------------------
| tinyint | 1 byte number | IDENTITY | Add this option to make colum AUTO INCREMENT |
| smallint | 2 bytes number | IDENTITY | " |
| int | 4 bytes number | IDENTITY | " |
| bigint | 8 bytes number | IDENTITY | " |
| double | 8 bytes Floating Point Number | | |
| text | Variable length (Max length:2147483647) | CI | Add this option to make column Case-Insensitive |
----------------------------------------------------------------------------------------------------------------------
* Up to one IDENTITY column can be defined.
* Column(s) are nullable (To set column data to null simply add NULL keyword.) except colum is AUTO INCREMENT. But the following query is valid.
Assume that Persons table's structure is as
---------------------
| per_id | per_name |
---------------------
For example, let the per_id column be AUTO INCREMET, the following queries apply.
INSERT INTO Persons(per_name) VALUES('Gökhan')
INSERT INTO Persons(per_id, per_name) VALUES(NULL, 'Gökhan')
* As long as the value is unique, the desired value within the number type limits can be entered in the AUTO INCREMENT field.
For example, the following queries apply to the table above.
INSERT INTO Persons(per_id, per_name) VALUES(5, 'Gökhan')
INSERT INTO Persons(per_id, per_name) VALUES(3, 'John')
INSERT INTO Persons(per_id, per_name) VALUES(100, 'Smith')
* Both the date and time type do not exist. I suggest using the bigint type instead. For example: 20200824235900 (YYYYMMDDHHMMSS)
* 'Transactions' does not exist.
* Column names of SELECT statement must be in order, for example :
Assume that Persons table's structure is as
---------------------
| per_id | per_name |
---------------------
If you use "SELECT per_name, per_id FROM Persons"
Then your result will be in different order!
* When you use UPDATE statement, you can use only + operator
* Supported aggregate functions are : COUNT(), SUM(), AVG(), MAX(), MIN()
* You can create index(es) for any table when related table is empty!
* root password is gokhan
* If electricity cuts when ufoSQL runs, and if any database corrupt, you can repair your databases after electricity returns if necessary.
(If necessary repairing process will start automatically when server starts up. Repairing process may be take a long time so be sure your computer plugged in to an UPS)
STRUCTURE OF config.ini
----------------------------------------------------------------------------------
[GENERAL]
Port=19991
Recordset_Activity_Timeout=60
Connection_Timeout=60
Received_TCP_Packet_Size_In_UTF16_Characters=1048576
Max_Connections_Count_Per_Client=100
Logging=true
----------------------------------------------------------------------------------
DESCRIPTION OF config.ini VARIABLES
----------------------------------------------------------------------------------
Port=Port number what ufoSQL.exe uses to accept connections
Recordset_Activity_Timeout=Value in seconds
Connection_Timeout=Value in seconds
Received_TCP_Packet_Size_In_UTF16_Characters=__int64 Value
Max_Connections_Count_Per_Client=int Value
Logging=Boolean value
PREDICATES
----------------------------------------------------------------------------------
<Field> := IdTok
<Constant> := StrTok | NumericTok
<Expression> := <Field> | <Constant>
<TermType1> :=
<Expression> = <Expression> |
<Expression> > <Expression> |
<Expression> >= <Expression> |
<Expression> < <Expression> |
<Expression> <= <Expression> |
<Expression> [NOT] LIKE <Expression>
<TermType2> :=
<Expression> = <Expression>
<PredicateType1> := <TermType1> [ AND | OR <PredicateType1> ]
<PredicateType2> := <TermType2> [ AND <PredicateType2> ]
QUERIES
----------------------------------------------------------------------------------
<Query> := SELECT <ProjectSet> FROM <Table>
[ [INNER | LEFT] JOIN <Table> ON IdTok = IdTok ]
[ WHEREWITHINDEX <PredicateType2> ]
[ WHERE <PredicateType1> ]
[ GROUP BY <IdSet> ]
[ HAVING <PredicateType1> ]
[ ORDER BY <SortList> [ DESC | ASC ] ]
[ LIMIT [<Constant>,] <Constant> ]
<IdSet> := <Field> [ , <IdSet> ]
<Table> := IdTok
<AggFn> := AVG(<Field>) | COUNT(<Field>) | MAX(<Field>) | MIN(<Field>) | SUM(<Field>) AS IdTok
<ProjectSet> := <Field> | <AggFn> [ , <ProjectSet>]
<SortList> := <Field> [ , <SortList>]
<FieldValueSet> := <Field> = <Expression> [ , <FieldValueSet>]
UPDATES
----------------------------------------------------------------------------------
<UpdateCmd> := <Insert> | <Delete> | <Modify> | <Create> | <Drop>
<Create> := <CreateTable> | <CreateIndex>
<Drop> := <DropTable> | <DropIndex> | <DropDatabase>
<Insert> := INSERT INTO IdTok ( <FieldList> )
VALUES ( <ConstantList> )
<FieldList> := <Field> [ , <Field> ]
<ConstantList> := <Constant> [ , <Constant> ]
<Delete> := DELETE FROM IdTok [ WHEREWITHINDEX <PredicateType2> ] [ WHERE <PredicateType1> ]
<Modify> := UPDATE IdTok SET <ModifyTermList>
[ WHEREWITHINDEX <PredicateType2> ] [ WHERE <PredicateType1> ]
<ModifyExpression> := <Expression> | <Expression> + <Expression>
<ModifyTermList> := <Field> = <ModifyExpression> [ , <ModifyTermList> ]
<CreateTable> := CREATE TABLE IdTok ( <FieldDefs> )
<DropTable> := DROP TABLE IdTok
<DropDatabase> := DROP DATABASE IdTok
<FieldDefs> := <FieldDef> [ , <FieldDef> ]
<FieldDef> := IdTock <TypeDef>
<TypeDef> := tinyint | smallint | int | bigint | double | text
<CreateIndex> := CREATE [ UNIQUE ] INDEX IdTok ON IdTok ( <IdSet> )
<DropIndex> := DROP INDEX IdTok
<CreateFunction> := CREATE FUNCTION <Function Name> <Return Type[string | double]> <Base64 Encoded JavaScript Function Source>
<DropFunction> := DROP FUNCTION <Function Name>
OTHER COMMANDS
----------------------------------------------------------------------------------
<ShowDatabases> := SHOW DATABASES // Requires root user access right
<ShowTables> := SHOW TABLES <DatabaseName>
<ShowTable> := SHOW TABLE <TableName>
<ShowIndexes> := SHOW INDEXES <TableName>
<ShowIndex> := SHOW INDEX <IndexName>
<ShowFunctions> := SHOW FUNCTIONS <DatabaseName>
<ShowFunction> := SHOW FUNCTION <FunctionName>
<ChangePassword> := CHANGE PASSWORD <NewPassword>
<Shutdown> := SHUTDOWN // Requires root user access right
Homepage : http://www.ufosql.com
