|
Basic Topics | Advanced Topics | Reference Topics |
| Documentation Home Page |
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; };