FLATFILEDATABASE 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
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>
<< Return to Documentation