Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Comment: | Update the syntax bubble diagrams to show that the WITH clause is allowed. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3f48c6857f80732af2476205cbe5d183 |
User & Date: | drh 2014-01-17 23:50:15.655 |
2014-01-23
| ||
15:25 | Recursive syntax diagram display. (check-in: 87e7eb3d0d user: drh tags: trunk) | |
2014-01-17
| ||
23:50 | Update the syntax bubble diagrams to show that the WITH clause is allowed. (check-in: 3f48c6857f user: drh tags: trunk) | |
2014-01-15
| ||
03:02 | Clarify some sentences in the documentation on ANALYZE. (check-in: 5fc1073e06 user: drh tags: trunk) | |
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
︙ | ︙ | |||
82 83 84 85 86 87 88 | stack {line CREATE {opt UNIQUE} INDEX {opt IF NOT EXISTS}} {line {optx /database-name .} /index-name ON /table-name ( {loop indexed-column ,} )} {line {optx WHERE /expr}} } indexed-column { | | | 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | stack {line CREATE {opt UNIQUE} INDEX {opt IF NOT EXISTS}} {line {optx /database-name .} /index-name ON /table-name ( {loop indexed-column ,} )} {line {optx WHERE /expr}} } indexed-column { line /column-name {opt COLLATE /collation-name} {or nil ASC DESC} } create-table-stmt { stack {line CREATE {or {} TEMP TEMPORARY} TABLE {opt IF NOT EXISTS}} {line {optx /database-name .} /table-name} {or {line ( {loop column-def ,} {loop {} {, table-constraint}} ) {opt WITHOUT ROWID}} |
︙ | ︙ | |||
200 201 202 203 204 205 206 207 | } create-virtual-table-stmt { stack {line CREATE VIRTUAL TABLE {opt IF NOT EXISTS}} {line {optx /database-name .} /table-name} {line USING /module-name {optx ( {loop /module-argument ,} )}} } delete-stmt { | > > > > > > > | > | > | 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 | } create-virtual-table-stmt { stack {line CREATE VIRTUAL TABLE {opt IF NOT EXISTS}} {line {optx /database-name .} /table-name} {line USING /module-name {optx ( {loop /module-argument ,} )}} } with-clause { stack {line WITH {opt RECURSIVE}} {loop {line /table-name {optx ( {loop /column-name ,} )} AS ( /select-stmt )} ,} } delete-stmt { stack {line {opt with-clause} DELETE FROM qualified-table-name} {optx WHERE expr} } delete-stmt-limited { stack {line {opt with-clause} DELETE FROM qualified-table-name} {optx WHERE expr} {optx {stack {optx ORDER BY {loop ordering-term ,}} {line LIMIT /expr {optx {or OFFSET ,} /expr}} } } } |
︙ | ︙ | |||
280 281 282 283 284 285 286 | {line {loop /digit nil} {opt /decimal-point {loop nil /digit}}} {line /decimal-point {loop /digit nil}} } {opt E {or nil + -} {loop /digit nil}} } insert-stmt { stack | | | > > > > > | < | | | | | < < | | 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 | {line {loop /digit nil} {opt /decimal-point {loop nil /digit}}} {line /decimal-point {loop /digit nil}} } {opt E {or nil + -} {loop /digit nil}} } insert-stmt { stack {line {opt with-clause} {or INSERT REPLACE {line INSERT OR REPLACE} {line INSERT OR ROLLBACK} {line INSERT OR ABORT} {line INSERT OR FAIL} {line INSERT OR IGNORE} } INTO } {line {optx /database-name .} /table-name {optx ( {loop /column-name ,} )}} {or {line VALUES {loop {line ( {loop expr ,} )} ,}} select-stmt {line DEFAULT VALUES} } } pragma-stmt { line PRAGMA {optx /database-name .} /pragma-name {or nil {line = pragma-value} |
︙ | ︙ | |||
323 324 325 326 327 328 329 | {line {optx /database-name .} {tailbranch /table-name /index-name} } } } select-stmt { stack | > | | | < | | | | < < < < > | > > > > > | 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 | {line {optx /database-name .} {tailbranch /table-name /index-name} } } } select-stmt { stack {line {opt with-clause} {loop simple-query-stmt {nil compound-operator nil}}} {optx ORDER BY {loop ordering-term ,}} {optx LIMIT /expr {optx {or OFFSET ,} /expr}} } simple-query-stmt { stack {line SELECT {or nil DISTINCT ALL} {loop result-column ,}} {optx FROM join-source} {optx WHERE expr} {optx GROUP BY {loop expr ,} {optx HAVING expr}} } result-column { or * {line /table-name . *} {line expr {optx {optx AS} /column-alias}} } join-source { line simple-join-source {opt {loop {line nil join-op simple-join-source join-constraint nil} {}}} } simple-join-source { or {line ( join-source )} {line ( select-stmt ) {optx {optx AS} /table-alias} } {stack {line {optx /database-name .} /table-name {optx {optx AS} /table-alias} } {or nil {line INDEXED BY /index-name} {line NOT INDEXED}} } } join-op { or {line ,} {line {opt NATURAL} {or nil {line LEFT {or OUTER nil}} INNER CROSS} |
︙ | ︙ | |||
381 382 383 384 385 386 387 | line expr {opt COLLATE /collation-name} {or nil ASC DESC} } compound-operator { or {line UNION {optx ALL}} INTERSECT EXCEPT } update-stmt { stack | > > > > | > > > > | | 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 | line expr {opt COLLATE /collation-name} {or nil ASC DESC} } compound-operator { or {line UNION {optx ALL}} INTERSECT EXCEPT } update-stmt { stack {line {opt with-clause} UPDATE {or {} {line OR ROLLBACK} {line OR ABORT} {line OR REPLACE} {line OR FAIL} {line OR IGNORE}} qualified-table-name} {line SET {loop {line /column-name = expr} ,} {optx WHERE expr}} } update-stmt-limited { stack {line {opt with-clause} UPDATE {or {} {line OR ROLLBACK} {line OR ABORT} {line OR REPLACE} {line OR FAIL} {line OR IGNORE}} qualified-table-name} {line SET {loop {line /column-name = expr} ,} {optx WHERE expr}} {optx {stack {optx ORDER BY {loop ordering-term ,}} {line LIMIT /expr {optx {or OFFSET ,} /expr}} } |
︙ | ︙ |
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
︙ | ︙ | |||
9 10 11 12 13 14 15 | set syntax_linkage(compound-operator) {{} select-stmt} set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}} set syntax_linkage(create-index-stmt) {indexed-column sql-stmt} set syntax_linkage(create-table-stmt) {{column-def select-stmt table-constraint} sql-stmt} set syntax_linkage(create-trigger-stmt) {{delete-stmt expr insert-stmt select-stmt update-stmt} sql-stmt} set syntax_linkage(create-view-stmt) {select-stmt sql-stmt} set syntax_linkage(create-virtual-table-stmt) {{} sql-stmt} | | | | | | | < | | > | | > | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | set syntax_linkage(compound-operator) {{} select-stmt} set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}} set syntax_linkage(create-index-stmt) {indexed-column sql-stmt} set syntax_linkage(create-table-stmt) {{column-def select-stmt table-constraint} sql-stmt} set syntax_linkage(create-trigger-stmt) {{delete-stmt expr insert-stmt select-stmt update-stmt} sql-stmt} set syntax_linkage(create-view-stmt) {select-stmt sql-stmt} set syntax_linkage(create-virtual-table-stmt) {{} sql-stmt} set syntax_linkage(delete-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}} set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt} set syntax_linkage(detach-stmt) {{} sql-stmt} set syntax_linkage(drop-index-stmt) {{} sql-stmt} set syntax_linkage(drop-table-stmt) {{} sql-stmt} set syntax_linkage(drop-trigger-stmt) {{} sql-stmt} set syntax_linkage(drop-view-stmt) {{} sql-stmt} set syntax_linkage(expr) {{literal-value raise-function select-stmt type-name} {attach-stmt column-constraint create-trigger-stmt delete-stmt delete-stmt-limited insert-stmt join-constraint ordering-term result-column simple-query-stmt table-constraint update-stmt update-stmt-limited}} set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}} set syntax_linkage(indexed-column) {{} {create-index-stmt table-constraint}} set syntax_linkage(insert-stmt) {{expr select-stmt with-clause} {create-trigger-stmt sql-stmt}} set syntax_linkage(join-constraint) {expr join-source} set syntax_linkage(join-op) {{} join-source} set syntax_linkage(join-source) {{join-constraint join-op simple-join-source} {simple-join-source simple-query-stmt}} set syntax_linkage(literal-value) {{} {column-constraint expr}} set syntax_linkage(numeric-literal) {{} {}} set syntax_linkage(ordering-term) {expr {delete-stmt-limited select-stmt update-stmt-limited}} set syntax_linkage(pragma-stmt) {pragma-value sql-stmt} set syntax_linkage(pragma-value) {signed-number pragma-stmt} set syntax_linkage(qualified-table-name) {{} {delete-stmt delete-stmt-limited update-stmt update-stmt-limited}} set syntax_linkage(raise-function) {{} expr} set syntax_linkage(reindex-stmt) {{} sql-stmt} set syntax_linkage(release-stmt) {{} sql-stmt} set syntax_linkage(result-column) {expr simple-query-stmt} set syntax_linkage(rollback-stmt) {{} sql-stmt} set syntax_linkage(savepoint-stmt) {{} sql-stmt} set syntax_linkage(select-stmt) {{compound-operator ordering-term simple-query-stmt with-clause} {create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt simple-join-source sql-stmt}} set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}} set syntax_linkage(simple-join-source) {{join-source select-stmt} join-source} set syntax_linkage(simple-query-stmt) {{expr join-source result-column} select-stmt} set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list} set syntax_linkage(sql-stmt-list) {sql-stmt {}} set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt} set syntax_linkage(type-name) {signed-number {column-def expr}} set syntax_linkage(update-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}} set syntax_linkage(update-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt} set syntax_linkage(vacuum-stmt) {{} sql-stmt} set syntax_linkage(with-clause) {{} {delete-stmt delete-stmt-limited insert-stmt select-stmt update-stmt update-stmt-limited}} set syntax_order {sql-stmt-list sql-stmt alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt rollback-stmt savepoint-stmt release-stmt create-index-stmt indexed-column create-table-stmt column-def type-name column-constraint signed-number table-constraint foreign-key-clause conflict-clause create-trigger-stmt create-view-stmt create-virtual-table-stmt with-clause delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt expr raise-function literal-value numeric-literal insert-stmt pragma-stmt pragma-value reindex-stmt select-stmt simple-query-stmt result-column join-source simple-join-source join-op join-constraint ordering-term compound-operator update-stmt update-stmt-limited qualified-table-name vacuum-stmt comment-syntax} |
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
︙ | ︙ | |||
14 15 16 17 18 19 20 | set nChng 0 proc chng {date desc {options {}}} { global nChng aChng set aChng($nChng) [list $date $desc $options] incr nChng } | | > | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | set nChng 0 proc chng {date desc {options {}}} { global nChng aChng set aChng($nChng) [list $date $desc $options] incr nChng } chng {2014-02-11 (3.8.3)} { <li>Added support for [common table expressions] and the [WITH clause]. <li>Added the [printf()] SQL function. <li>Added [SQLITE_DETERMINISTIC] as an optional bit in the 4th argument to the [sqlite3_create_function()] and related interfaces, providing applications with the ability to create new functions that can be factored out of inner loops when they have constant arguments. <li>Add [SQLITE_READONLY_DBMOVED] error code, returned at the beginning of a transaction, to indicate that the underlying database file has been renamed |
︙ | ︙ |
︙ | ︙ | |||
3043 3044 3045 3046 3047 3048 3049 | [INSERT] command documentation for additional information.</p> <tcl> ############################################################################### Section SELECT select {SELECT query} BubbleDiagram select-stmt 1 | | | | 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 | [INSERT] command documentation for additional information.</p> <tcl> ############################################################################### Section SELECT select {SELECT query} BubbleDiagram select-stmt 1 BubbleDiagram simple-query-stmt BubbleDiagram result-column BubbleDiagram join-source BubbleDiagram simple-join-source BubbleDiagram join-op BubbleDiagram join-constraint BubbleDiagram ordering-term BubbleDiagram compound-operator </tcl> |
︙ | ︙ | |||
3068 3069 3070 3071 3072 3073 3074 | steps. It is important to keep in mind that this is purely illustrative - in practice neither SQLite nor any other SQL engine is required to follow this or any other specific process. <h3>Simple Select Processing</h3> <p>The syntax for a simple SELECT statement is depicted in the | | | 3068 3069 3070 3071 3072 3073 3074 3075 3076 3077 3078 3079 3080 3081 3082 | steps. It is important to keep in mind that this is purely illustrative - in practice neither SQLite nor any other SQL engine is required to follow this or any other specific process. <h3>Simple Select Processing</h3> <p>The syntax for a simple SELECT statement is depicted in the [simple-query-stmt syntax diagram]. Generating the results of a simple SELECT statement is presented as a four step process in the description below: <ol> <li> <p>[FROM clause] processing: The input data for the simple SELECT is determined. The input data is either implicitly a single row with 0 columns (if there is no FROM clause) or is determined by analyzing the [join-source syntax diagram|join-source] specification that follows |
︙ | ︙ | |||
3605 3606 3607 3608 3609 3610 3611 | The INDEXED BY phrase is an extension that is particular to SQLite and is not portable to other SQL database engines. The INDEXED BY phrase can be seen in the following syntax diagrams:</p> <tcl> BubbleDiagram qualified-table-name | | | 3605 3606 3607 3608 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 | The INDEXED BY phrase is an extension that is particular to SQLite and is not portable to other SQL database engines. The INDEXED BY phrase can be seen in the following syntax diagrams:</p> <tcl> BubbleDiagram qualified-table-name BubbleDiagram simple-join-source </tcl> <p>^The "INDEXED BY index-name" phrase specifies that the named index must be used in order to look up values on the preceding table. ^If index-name does not exist or cannot be used for the query, then the preparation of the SQL statement fails. ^(The "NOT INDEXED" clause specifies that no index shall be used when |
︙ | ︙ |