From bc1716747717442e611c8b8ec602c1ce5c71bb3a Mon Sep 17 00:00:00 2001 From: drh Date: Mon, 6 Jan 2014 18:32:18 +0000 Subject: [PATCH] Start a new experimental branch for support of Oracle-style CONNECT BY syntax. FossilOrigin-Name: 4365ddd62d88dc73c59962cf27a3e038eabdb50b --- manifest | 17 ++++++++++------- manifest.uuid | 2 +- src/parse.y | 26 +++++++++++++++++++++++++- tool/mkkeywordhash.c | 9 +++++++++ 4 files changed, 45 insertions(+), 9 deletions(-) diff --git a/manifest b/manifest index 1d0ad0ed3f..4a72d9a90c 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Fix\san\stypo\sthat\sbreaks\sthe\sbuild\swhen\sSQLITE_ENABLE_TREE_EXPLAIN\sis\sdefined. -D 2014-01-04T20:00:14.172 +C Start\sa\snew\sexperimental\sbranch\sfor\ssupport\sof\sOracle-style\sCONNECT\sBY\nsyntax. +D 2014-01-06T18:32:18.802 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 2ef13430cd359f7b361bb863504e227b25cc7f81 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -209,7 +209,7 @@ F src/os_unix.c abeb9d54036aaea6f4395050ce823f51217ae4d4 F src/os_win.c 16eac0961603182ffc10c02b39fe830126538e07 F src/pager.c efa923693e958696eee69b205a20bfbc402c8480 F src/pager.h ffd5607f7b3e4590b415b007a4382f693334d428 -F src/parse.y acee1a9958539e21263362b194594c5255ad2fca +F src/parse.y 201902e23b29ecee8e58bbd013733493cee6da86 F src/pcache.c f8043b433a57aba85384a531e3937a804432a346 F src/pcache.h a5e4f5d9f5d592051d91212c5949517971ae6222 F src/pcache1.c 57fee9a9a617218f5037afbbe49b09da65bde56b @@ -1113,7 +1113,7 @@ F tool/lemon.c 796930d5fc2036c7636f3f1ee12f9ae03719a2eb F tool/lempar.c 01ca97f87610d1dac6d8cd96ab109ab1130e76dc F tool/logest.c 7ad625cac3d54012b27d468b7af6612f78b9ba75 F tool/mkautoconfamal.sh f8d8dbf7d62f409ebed5134998bf5b51d7266383 -F tool/mkkeywordhash.c 189d76644e373c7d0864c628deb8ce7b4f403591 +F tool/mkkeywordhash.c 86acc19b96361e90e18d2324b3f56af621aeda85 F tool/mkopts.tcl 66ac10d240cc6e86abd37dc908d50382f84ff46e F tool/mkpragmatab.tcl 78a77b2c554d534c6f2dc903130186ed15715460 F tool/mkspeedsql.tcl a1a334d288f7adfe6e996f2e712becf076745c97 @@ -1148,7 +1148,10 @@ F tool/vdbe-compress.tcl 0cf56e9263a152b84da86e75a5c0cdcdb7a47891 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh d1a6de74685f360ab718efda6265994b99bbea01 F tool/win/sqlite.vsix 030f3eeaf2cb811a3692ab9c14d021a75ce41fff -P d6fcfc8890489b942e5b3f1bc271835d77c5ef96 -R 92a605458ee112680310df2ab331e715 +P f461e2b3973d0fe6a7b8cb7a3aaab8a30b3e16c0 +R 8ea35e9854af1f75afacc581ccadba4b +T *branch * connect-by +T *sym-connect-by * +T -sym-trunk * U drh -Z 1b6a595171700e1d6836f451072293d3 +Z 2785f3b4e9984046b3600748ab3ddac7 diff --git a/manifest.uuid b/manifest.uuid index 89e098a77e..e62e133bad 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -f461e2b3973d0fe6a7b8cb7a3aaab8a30b3e16c0 \ No newline at end of file +4365ddd62d88dc73c59962cf27a3e038eabdb50b \ No newline at end of file diff --git a/src/parse.y b/src/parse.y index ba9feb10fc..30284fcaea 100644 --- a/src/parse.y +++ b/src/parse.y @@ -102,6 +102,15 @@ struct ValueList { Select *pSelect; }; +/* +** An instance of this structure stores the optional Oracle Hierarchical +** Query clauses - the START WITH and CONNECT BY clauses. +*/ +struct HierarchicalQuery { + Expr *pStartWith; /* The START WITH condition */ + Expr *pConnectBy; /* The CONNECT BY condition */ +}; + } // end %include // Input is a single SQL command @@ -438,7 +447,7 @@ multiselect_op(A) ::= UNION(OP). {A = @OP;} multiselect_op(A) ::= UNION ALL. {A = TK_ALL;} multiselect_op(A) ::= EXCEPT|INTERSECT(OP). {A = @OP;} %endif SQLITE_OMIT_COMPOUND_SELECT -oneselect(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y) +oneselect(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y) hq_opt groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). { A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset); } @@ -639,6 +648,19 @@ limit_opt(A) ::= LIMIT expr(X) OFFSET expr(Y). limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). {A.pOffset = X.pExpr; A.pLimit = Y.pExpr;} +///////// Oracle-style CONNECT BY clauses for hierarchical queries //////////// + +%type hq_opt {struct HierarchicalQuery} +%destructor hq_opt { + sqlite3ExprDelete(pParse->db, $$.pStartWith); + sqlite3ExprDelete(pParse->db, $$.pConnectBy); +} + +hq_opt(A) ::= START WITH expr(X) CONNECT BY expr(Y). + { A.pStartWith=X.pExpr; A.pConnectBy=Y.pExpr; } +hq_opt(A) ::= CONNECT BY expr(Y). { A.pStartWith=0; A.pConnectBy=Y.pExpr; } +hq_opt(A) ::= . { A.pStartWith=0; A.pConnectBy=0; } + /////////////////////////// The DELETE statement ///////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT @@ -985,6 +1007,8 @@ expr(A) ::= MINUS(B) expr(X). [BITNOT] {spanUnaryPrefix(&A,pParse,TK_UMINUS,&X,&B);} expr(A) ::= PLUS(B) expr(X). [BITNOT] {spanUnaryPrefix(&A,pParse,TK_UPLUS,&X,&B);} +expr(A) ::= PRIOR(B) expr(X). [BITNOT] + {spanUnaryPrefix(&A,pParse,TK_PRIOR,&X,&B);} %type between_op {int} between_op(A) ::= BETWEEN. {A = 0;} diff --git a/tool/mkkeywordhash.c b/tool/mkkeywordhash.c index f3ef73f394..0a76c4e02d 100644 --- a/tool/mkkeywordhash.c +++ b/tool/mkkeywordhash.c @@ -138,6 +138,11 @@ struct Keyword { #else # define AUTOVACUUM 0x00020000 #endif +#ifdef SQLITE_OMIT_CONNECTBY +# define CONNECTBY 0 +#else +# define CONNECTBY 0x00040000 +#endif /* ** These are the keywords @@ -167,6 +172,7 @@ static Keyword aKeywordTable[] = { { "COLUMN", "TK_COLUMNKW", ALTER }, { "COMMIT", "TK_COMMIT", ALWAYS }, { "CONFLICT", "TK_CONFLICT", CONFLICT }, + { "CONNECT", "TK_CONNECT", CONNECTBY }, { "CONSTRAINT", "TK_CONSTRAINT", ALWAYS }, { "CREATE", "TK_CREATE", ALWAYS }, { "CROSS", "TK_JOIN_KW", ALWAYS }, @@ -232,6 +238,7 @@ static Keyword aKeywordTable[] = { { "PLAN", "TK_PLAN", EXPLAIN }, { "PRAGMA", "TK_PRAGMA", PRAGMA }, { "PRIMARY", "TK_PRIMARY", ALWAYS }, + { "PRIOR", "TK_PRIOR", CONNECTBY }, { "QUERY", "TK_QUERY", EXPLAIN }, { "RAISE", "TK_RAISE", TRIGGER }, { "REFERENCES", "TK_REFERENCES", FKEY }, @@ -247,6 +254,7 @@ static Keyword aKeywordTable[] = { { "SAVEPOINT", "TK_SAVEPOINT", ALWAYS }, { "SELECT", "TK_SELECT", ALWAYS }, { "SET", "TK_SET", ALWAYS }, + { "START", "TK_START", CONNECTBY }, { "TABLE", "TK_TABLE", ALWAYS }, { "TEMP", "TK_TEMP", ALWAYS }, { "TEMPORARY", "TK_TEMP", ALWAYS }, @@ -262,6 +270,7 @@ static Keyword aKeywordTable[] = { { "VALUES", "TK_VALUES", ALWAYS }, { "VIEW", "TK_VIEW", VIEW }, { "VIRTUAL", "TK_VIRTUAL", VTAB }, + { "WITH", "TK_WITH", CONNECTBY }, { "WITHOUT", "TK_WITHOUT", ALWAYS }, { "WHEN", "TK_WHEN", ALWAYS }, { "WHERE", "TK_WHERE", ALWAYS }, -- 2.39.5