InstantDB - Supported SQL syntax

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

Several items are included for future compatability. In particular join_spec (joins in the FROM clause of a SELECT) and FOREIGN KEY (i.e. referencial intergrity conditions) are accepted but ignored.

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. It's use is not recommended and is not supported.


data_type =              BYTE|TINYINT|SMALLINT|INT|INTEGER|SHORT|LONG|CURRENCY|DOUBLE|FLOAT|DATE|
                         {BINARY|VARBINARY|LONGVARBINARY|LONGCHAR|TEXT|IMAGE|OLE|
                         CHAR|VARCHAR|TEXT}[(<#length>)];
col_condition =          UNIQUE | PRIMARY KEY | FOREIGN KEY | [NOT] NULL | 
                         AUTO INCREMENT | CACHE <#rows> {ROWS|PERCENT};
column_def =             <colName> data_type [DEFAULT {<default>|USER|NULL}] [col_condition...];
col_list =               (<colName>,...);
table_constraint =       UNIQUE col_list | 
                         PRIMARY KEY col_list |
                         FOREIGN KEY col_list |
                         REFERENCES <refTable> [col_list];
table_defn_item =        column_def | table_constraint;
create_table_statement = CREATE [TEMPORARY] TABLE <tableName> (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,...);

col_ref =                [<tableName>.]<colName> ;
function_name =          AVG|MAX|MIN|SUM|COUNT;
function =               function_name ([DISTINCT|ALL] expr);
expr_item =              [\\{<x>]{<# int_lit> | <$ str_lit> | 
                         col_ref | function | (expr) | COUNT(*)}[\\}];
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 =              col_ref [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 =              {<refTable> [[AS] <pseudoTable>] | (select_statement) [AS] <pseudoTable>};
join_item =              <tableName> | (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,...|[<tableName>.]*} 
                         FROM table_ref,... [join_spec]
                         [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 | 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;

set_statement =          SET {DATE FORMAT <$ date> | 
                         CURRENCY {SYMBOL <$ cur>|DECIMAL <# dec>} |
                         <table> AUTO INCREMENT {ON|OFF} |
                         LITERALS [STRICT_ON|STRICT_OFF] |
                         EXPORT <filename> [CSVDELIMITED|FIXEDLENGTH] 
                         [COLNAMEHEADER] [ROWNUMBERS] [QUOTE <quoteChar>] [CONTROLCOL] [SUMMARYHEADER]
                         [TRACE <# level> [CONSOLE][TIME]]};

assignment =             [<table>.]<column> = {expr|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];

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;	};