InstantDB      Basic Topics      Advanced Topics      Reference Topics
Documentation Home Page

InstantDB SQL syntax

The following pseudo-BNF notation describes the syntax accepted by InstantDB. The following conventions are used:

Nested joins in the FROM clause are not currently implemented.

Reserved words can be "un-reserved" using the ignoreKeywords property. e.g., ignoreKeywords=url,quote would allow the keywords "url" and "quote" to be used as table or column names. Note that this facility is provided as a result of user demands. Its use is not recommended and is not supported.


data_type =              BIT|BOOLEAN|BYTE|TINYINT|SMALLINT|INT|INTEGER|SHORT|LONG|CURRENCY|DOUBLE|FLOAT|
                         DATE|DATETIME|TIME|TIMESTAMP|
                         {NUMERIC|DECIMAL} [(<precision>[,<scale>])]|
                         {BINARY|VARBINARY|LONGVARBINARY|LONGCHAR|TEXT|IMAGE|OLE|
                         CHAR|VARCHAR|VARCHAR2|SMALLCHAR|TEXT}[(<#length>)];
deferrence =             [NOT] DEFERRABLE;
fk_spec =                CASCADE|SET {NULL|DEFAULT}|NO ACTION|RESTRICT;
on_delete =              ON DELETE fk_spec;
on_update =              ON UPDATE fk_spec;
ref_spec =               on_delete | on_update;
col_condition =          UNIQUE | PRIMARY KEY | 
                         [FOREIGN KEY] REFERENCES <refTable>[(<refColumn>)] [ref_spec...] |
                         [NOT] NULL | AUTO INCREMENT | CACHE <#rows> {ROWS|PERCENT} |
                         INITIALLY {DEFERRED|IMMEDIATE} | deferrence | FORMAT <formatString>;
column_def =             <colName> data_type [DEFAULT {<default>|USER|NULL}] [col_condition...];
col_list =               (<colName>,...);
table_constraint =       [CONSTRAINT <name>] {UNIQUE col_list | 
                         PRIMARY KEY col_list |
                         FOREIGN KEY col_list REFERENCES <refTable> [(<refColumn>)] [ref_spec]} 
                         [INITIALLY {DEFERRED|IMMEDIATE} | deferrence];
table_defn_item =        column_def | table_constraint;
create_table_statement = CREATE [TEMPORARY] TABLE <tableName> [ON PARTITION <# partitionNumber>]
                         (table_defn_item,...) [table_constraint...];


insert_ref =             [<tableName>.]<colName> ;
insert_list =            (insert_ref,...);
insert_item =            [\\{<x>]<value>[\\}]|NULL;
insert_statement =       INSERT INTO <tableName> [insert_list]
                         {VALUES (insert_item,...) | select_statement};


index_item =             <colName> [ASC|DESC];
create_index_statement = CREATE [UNIQUE] INDEX <indexName> ON <tableName> (index_item,...);

all_cols =               <tableName>.*;
col_ref =                {[<tableName>.]<colName>|all_cols} ;
function_name =          AVG|MAX|MIN|SUM|COUNT;
parameter =              expr;
parameter_list =         parameter,...;
rowfunc =                <funcname> ([parameter_list]);
function =               function_name ([DISTINCT|ALL] expr);
internal_parameter =     __IDB_INTERNAL <param> FROM expr3 IN TABLE expr2 ;
text_item =              TEXT (<$ str_lit>);
expr_item =              [\\{<x>]{<# int_lit> | <$ str_lit> [.<$ str_lit2>]| 
                         function | rowfunc | (expr) | col_ref | COUNT(*) | 
                         text_item | internal_parameter}[\\}];
expr_item2 =             expr_item;
expr =                   expr_item [{+|-|*|/} expr_item2];

exist_test =             EXISTS (select_statement);
set_test =               expr [NOT] IN ({<value>,...|select_statement});
null_test =              col_ref IS [NOT] NULL;
like_test =              expr [NOT] LIKE <value> [[\\{]ESCAPE <escape>[\\}]][IGNORE CASE];
expr3 =                  expr;
expr2 =                  expr;
between_test =           expr [NOT] BETWEEN expr2 AND expr3;
comparison_test =        expr {=|\\<>|\\<|\\<=|>|>=} {expr2| [ALL|ANY|SOME] (select_statement)};
search_item =            [NOT] {comparison_test|between_test|like_test|null_test
                         |set_test|exist_test|(search_condition)};
search_item2 =           {AND|OR} search_condition;
search_condition =       search_item [search_item2];

order_clause =           [<tabName>.]<colName> [ASC|DESC];
select_item =            expr [[AS] <pseudoCol>];
table_ref =              join_spec
                         | {<refTable> [[AS] <pseudoTable>]
                         | (select_statement) [AS] <pseudoTable>};
join_item =              <tableName> [[AS] <pseudoTable>]| (join_spec);
join_item2 =             join_item;
join =                   join_item [INNER|{FULL|LEFT|RIGHT} [OUTER]] JOIN join_item2 
                         {ON search_condition|USING (<colName>,...)};
natural_join =           join_item NATURAL [INNER|{FULL|LEFT|RIGHT} [OUTER]] JOIN join_item2;
join_spec =              natural_join | join;
group_condition =        GROUP BY {select_item,...} [HAVING search_condition];
select_statement =       SELECT [ALL|DISTINCT] {select_item,...|*} 
                         [INTO <destTable>] FROM table_ref,... 
                         [WHERE search_condition] [group_condition] 
                         [ORDER BY order_clause,...];

delete_statement =       DELETE FROM <table> [WHERE search_condition];

import_statement =       IMPORT <table> FROM [URL] <filename> [USING <schema>] [BUFFER <rows>];


header =                 \\[<tableName>.<extension>\\];
header_spec =            COLNAMEHEADER = {TRUE|FALSE};
format_spec =            FORMAT={AUTO|FIXEDLENGTH|DELIMITED(<delimiter>)|TABDELIMITED|CSVDELIMITED} [STRICT];
scan_spec =              MAXSCANROWS=<scan>;
char_spec =              CHARACTERSET={OEM|ANSI};
col_type =               BIT | BOOLEAN | BYTE | INTEGER | SHORT | LONG |
                         BINARY | VARBINARY | LONGVARBINARY | LONGCHAR | TEXT | IMAGE | OLE |
                         FLOAT | SINGLE | DOUBLE | CHAR | DATE | TEXT | CURRENCY;
col_spec =               <column> = <colname> col_type [WIDTH <#length>];
schema_line =            header | header_spec | format_spec | scan_spec | char_spec | col_spec;

export_option =          CSVDELIMITED | FIXEDLENGTH | DELIMITED(<delimiter>) | COLNAMEHEADER |
                         ROWNUMBERS | QUOTE <quoteChar> | [NO]SQL | CONTROLCOL | SUMMARYHEADER |
                         TRACE <# level> | CONSOLE | TIME;
set_statement =          SET {{DATE|DATETIME} FORMAT <$ date> | 
                         CURRENCY {SYMBOL <$ cur>|DECIMAL <# dec>} |
                         <table> AUTO INCREMENT {ON|OFF} |
                         LITERALS [STRICT_ON|STRICT_OFF] |
                         INCREMENT_BASE {<base>|MAX} ON {<table>.<column> | ALL} |
                         DEFAULT_USERNAME <uname> [DEFAULT_PASSWORD <pword>] |
                         EXPORT <filename> [export_option...]};

assignment =             [<table>.]<column> = {expr|(select_statement)|NULL};
update_statement =       UPDATE <table> SET {assignment,...} [WHERE search_condition];

commit_statement =       {COMMIT | ROLLBACK} WORK;

drop_index_statement =   DROP INDEX <index> [ON <table>];
drop_table_statement =   DROP TABLE <table>;

add_col =                ADD column_def;
alter_col =              ALTER <column> {SET DEFAULT <value> | DROP DEFAULT};
drop_col =               DROP {<column> | PRIMARY KEY};
change_col =             ADD table_constraint;
alter_table_statement =  ALTER TABLE <table> {add_col | alter_col | drop_col | change_col};

shutdown_statement =     SHUTDOWN [NOWAIT] [THIS_DATABASE];

statement =              select_statement |
                         insert_statement |
                         update_statement |
                         create_table_statement |
                         create_index_statement |
                         delete_statement |
                         import_statement |
                         set_statement |
                         commit_statement |
                         drop_index_statement |
                         drop_table_statement |
                         alter_table_statement |
                         shutdown_statement |
                         schema_line;	};




Copyright © 2000, 2001 Lutris Technologies. All rights reserved.