|
Supported SQL SyntaxThe following pseudo-BNF notation describes the syntax accepted by InstantDB. The following conventions are used:
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; };
|
||
