InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors


Supported SQL Syntax

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

  • [ ] indicates optional items;
  • { } indicates one item must be supplied;
  • < > indicates a placeholder for an identifier;
  • | separates alternatives;
  • ... means repeated items;
  • all CAPITALS indicates a reserved word;
  • # indicates a numeric value;
  • $ indicates a string literal;
  • \ indicates an escape on a syntax special character.

Several items are included for future compatability. In particular FOREIGN KEY (i.e. referencial intergrity conditions) is accepted but ignored.

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


  Previous Release

     Basic

     Advanced      Reference

data_type =              BYTE|TINYINT|SMALLINT|INT|INTEGER|SHORT|LONG|CURRENCY|
                         DOUBLE|FLOAT|DATE|{NUMERIC|DECIMAL} [(<precision>
                         [,<scale>])]|{BINARY|VARBINARY|LONGVARBINARY|
                         LONGCHAR|TEXT|IMAGE|OLE|CHAR|VARCHAR|SMALLCHAR|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> [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);
expr_item =              [\\{<x>]{<# int_lit> | <$ str_lit>
                         [.<$ str_lit2>]| function | rowfunc | (expr) | 
                         col_ref | 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 =              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 | 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] |
                         INCREMENT_BASE {<base>|MAX} ON {<table>.<column> |
                         ALL} | DEFAULT_USERNAME <uname> [DEFAULT_PASSWORD
                         <pword>] | EXPORT <filename> [CSVDELIMITED|FIXEDLENGTH
                         |DELIMITED(<delimiter>)] [COLNAMEHEADER] [ROWNUMBERS]
                         [QUOTE <quoteChar>] [[NO]SQL] [CONTROLCOL]
                         [SUMMARYHEADER] [TRACE <# level> [CONSOLE][TIME]]};

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

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