Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Update the syntax diagrams to include the new window function syntax. |
|---|---|
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
f7a6a2f7c26e16badf599e6ea3df333b |
| User & Date: | drh 2018-07-06 18:30:18 |
Context
|
2018-07-06
| ||
| 20:43 | Work on the window function documentation, including importing the new syntax diagrams. check-in: 6d60e5d021 user: drh tags: trunk | |
| 18:30 | Update the syntax diagrams to include the new window function syntax. check-in: f7a6a2f7c2 user: drh tags: trunk | |
|
2018-07-05
| ||
| 21:13 | Fix typo in the RANGE documentation of windows functions. check-in: 463da73728 user: drh tags: trunk | |
Changes
Changes to art/syntax/bubble-generator-data.tcl.
274 275 276 277 278 279 280 281 282 283 284 285 286 287 ... 369 370 371 372 373 374 375 376 377 378 379 380 381 382 ... 390 391 392 393 394 395 396 397 398 399 400 401 402 403 ... 512 513 514 515 516 517 518 519 |
( {or {toploop expr ,} {}} ) }
}
}
{line {optx {optx NOT} EXISTS} ( select-stmt )}
{line CASE {optx expr} {loop {line WHEN expr THEN expr} {}}
{optx ELSE expr} END}
{line raise-function}
}
raise-function {
line RAISE (
{or IGNORE
{line {or ROLLBACK ABORT FAIL} , /error-message }
} )
}
................................................................................
{or
{indentstack 2
{line SELECT {or nil DISTINCT ALL}
{loop result-column ,}}
{optx FROM {or {loop table-or-subquery ,} join-clause}}
{optx WHERE expr}
{optx GROUP BY {loop expr ,} {optx HAVING expr}}
}
{line VALUES {loop {line ( {toploop expr ,} )} ,}}
}
compound-operator
}
{optx ORDER BY {loop ordering-term ,}}
{optx LIMIT expr {optx {or OFFSET ,} expr}}
................................................................................
or
{indentstack 2
{line SELECT {or nil DISTINCT ALL}
{loop result-column ,}}
{optx FROM {or {loop table-or-subquery ,} join-clause}}
{optx WHERE expr}
{optx GROUP BY {loop expr ,} {optx HAVING expr}}
}
{line VALUES {loop {line ( {toploop expr ,} )} ,}}
}
factored-select-stmt {
stack
{opt {line WITH {opt RECURSIVE} {loop common-table-expression ,}}}
{line {loop select-core compound-operator}}
................................................................................
comment-syntax {
or
{line -- {loop nil /anything-except-newline}
{or /newline /end-of-input}}
{line /* {loop nil /anything-except-*/}
{or */ /end-of-input}}
}
}
|
> > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 ... 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 ... 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 ... 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 |
( {or {toploop expr ,} {}} ) }
}
}
{line {optx {optx NOT} EXISTS} ( select-stmt )}
{line CASE {optx expr} {loop {line WHEN expr THEN expr} {}}
{optx ELSE expr} END}
{line raise-function}
{line /window-func ( {or {line {toploop expr ,}} {} *} )
{opt filter} OVER {or {line ( window-defn )} /window-name}}
}
raise-function {
line RAISE (
{or IGNORE
{line {or ROLLBACK ABORT FAIL} , /error-message }
} )
}
................................................................................
{or
{indentstack 2
{line SELECT {or nil DISTINCT ALL}
{loop result-column ,}}
{optx FROM {or {loop table-or-subquery ,} join-clause}}
{optx WHERE expr}
{optx GROUP BY {loop expr ,} {optx HAVING expr}}
{optx WINDOW {loop {line /window-name AS window-defn} ,}}
}
{line VALUES {loop {line ( {toploop expr ,} )} ,}}
}
compound-operator
}
{optx ORDER BY {loop ordering-term ,}}
{optx LIMIT expr {optx {or OFFSET ,} expr}}
................................................................................
or
{indentstack 2
{line SELECT {or nil DISTINCT ALL}
{loop result-column ,}}
{optx FROM {or {loop table-or-subquery ,} join-clause}}
{optx WHERE expr}
{optx GROUP BY {loop expr ,} {optx HAVING expr}}
{optx WINDOW {loop {line /window-name AS window-defn} ,}}
}
{line VALUES {loop {line ( {toploop expr ,} )} ,}}
}
factored-select-stmt {
stack
{opt {line WITH {opt RECURSIVE} {loop common-table-expression ,}}}
{line {loop select-core compound-operator}}
................................................................................
comment-syntax {
or
{line -- {loop nil /anything-except-newline}
{or /newline /end-of-input}}
{line /* {loop nil /anything-except-*/}
{or */ /end-of-input}}
}
filter {
line FILTER ( WHERE expr )
}
window-defn {
line {opt PARTITION BY {loop expr ,}}
{opt ORDER BY {loop ordering-term ,}}
{opt frame-spec}
}
frame-spec {
line {or RANGE ROWS} {or
{line BETWEEN {or {line UNBOUNDED PRECEDING}
{line expr PRECEDING}
{line CURRENT ROW}
{line expr FOLLOWING}
}
AND {or {line expr PRECEDING}
{line CURRENT ROW}
{line expr FOLLOWING}
{line UNBOUNDED FOLLOWING}
}
}
{or {line UNBOUNDED PRECEDING}
{line expr PRECEDING}
{line CURRENT ROW}
{line expr FOLLOWING}
}
}
}
function-invocation {
line /function-name ( {or {line {optx DISTINCT} {toploop expr ,}} {} *} )
}
window-function-invocation {
line /window-func ( {or {line {toploop expr ,}} {} *} )
{opt filter} OVER {or {line ( window-defn )} /window-name}
}
}
|
Changes to art/syntax/bubble-generator.tcl.
672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 |
.c config -width $x1 -height $y1
update
.c postscript -file $name.ps -width [expr {$x1+2}] -height [expr {$y1+2}]
global DPI
.c delete bgrect
exec convert -density ${DPI}x$DPI -antialias $name.ps $name.gif
if {$do_xv} {
if {[catch {exec xv $name.gif &}]} {
exec display $name.gif &
}
}
}
proc draw_all_graphs {} {
global all_graphs
set f [open all.html w]
foreach {name graph} $all_graphs {
|
< | < |
672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 |
.c config -width $x1 -height $y1
update
.c postscript -file $name.ps -width [expr {$x1+2}] -height [expr {$y1+2}]
global DPI
.c delete bgrect
exec convert -density ${DPI}x$DPI -antialias $name.ps $name.gif
if {$do_xv} {
exec display $name.gif &
}
}
proc draw_all_graphs {} {
global all_graphs
set f [open all.html w]
foreach {name graph} $all_graphs {
|
Changes to art/syntax/expr.gif.
cannot compute difference between binary files
Added art/syntax/filter.gif.
cannot compute difference between binary files
Added art/syntax/frame-spec.gif.
cannot compute difference between binary files
Added art/syntax/function-invocation.gif.
cannot compute difference between binary files
Changes to art/syntax/select-core.gif.
cannot compute difference between binary files
Changes to art/syntax/select-stmt.gif.
cannot compute difference between binary files
Changes to art/syntax/syntax_linkage.tcl.
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 55 56 57 58 59 60 61 62 |
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 compound-select-stmt create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited factored-select-stmt indexed-column insert-stmt join-constraint ordering-term result-column select-core select-stmt simple-select-stmt table-constraint table-or-subquery update-stmt update-stmt-limited upsert-clause}}
set syntax_linkage(factored-select-stmt) {{common-table-expression compound-operator expr ordering-term select-core} {}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {expr {create-index-stmt table-constraint upsert-clause}}
set syntax_linkage(insert-stmt) {{expr select-stmt upsert-clause with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-clause) {{join-constraint join-operator table-or-subquery} {select-core select-stmt table-or-subquery}}
set syntax_linkage(join-constraint) {expr join-clause}
set syntax_linkage(join-operator) {{} join-clause}
set syntax_linkage(literal-value) {{} {column-constraint expr}}
set syntax_linkage(numeric-literal) {{} {}}
set syntax_linkage(ordering-term) {expr {compound-select-stmt delete-stmt-limited factored-select-stmt select-stmt simple-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(recursive-cte) {cte-table-name {}}
set syntax_linkage(reindex-stmt) {{} sql-stmt}
set syntax_linkage(release-stmt) {{} sql-stmt}
set syntax_linkage(result-column) {expr {select-core select-stmt}}
set syntax_linkage(rollback-stmt) {{} sql-stmt}
set syntax_linkage(savepoint-stmt) {{} sql-stmt}
set syntax_linkage(select-core) {{expr join-clause result-column table-or-subquery} {compound-select-stmt factored-select-stmt simple-select-stmt}}
set syntax_linkage(select-stmt) {{common-table-expression compound-operator expr join-clause ordering-term result-column table-or-subquery} {common-table-expression create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt sql-stmt table-or-subquery with-clause}}
set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}}
set syntax_linkage(simple-select-stmt) {{common-table-expression expr ordering-term select-core} {}}
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(table-or-subquery) {{expr join-clause select-stmt} {join-clause select-core select-stmt}}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{column-name-list expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{column-name-list expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(upsert-clause) {{column-name-list expr indexed-column} insert-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
set syntax_linkage(with-clause) {{cte-table-name select-stmt} {delete-stmt delete-stmt-limited insert-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 cte-table-name recursive-cte common-table-expression 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 upsert-clause pragma-stmt pragma-value reindex-stmt select-stmt join-clause select-core factored-select-stmt simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt column-name-list update-stmt-limited qualified-table-name vacuum-stmt comment-syntax}
|
| > > > | | | > > | |
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 55 56 57 58 59 60 61 62 63 64 65 66 67 |
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) {{filter literal-value raise-function select-stmt type-name window-defn} {attach-stmt column-constraint compound-select-stmt create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited factored-select-stmt filter frame-spec function-invocation indexed-column insert-stmt join-constraint ordering-term result-column select-core select-stmt simple-select-stmt table-constraint table-or-subquery update-stmt update-stmt-limited upsert-clause window-defn window-function-invocation}}
set syntax_linkage(factored-select-stmt) {{common-table-expression compound-operator expr ordering-term select-core} {}}
set syntax_linkage(filter) {expr {expr window-function-invocation}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(frame-spec) {expr window-defn}
set syntax_linkage(function-invocation) {expr {}}
set syntax_linkage(indexed-column) {expr {create-index-stmt table-constraint upsert-clause}}
set syntax_linkage(insert-stmt) {{expr select-stmt upsert-clause with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-clause) {{join-constraint join-operator table-or-subquery} {select-core select-stmt table-or-subquery}}
set syntax_linkage(join-constraint) {expr join-clause}
set syntax_linkage(join-operator) {{} join-clause}
set syntax_linkage(literal-value) {{} {column-constraint expr}}
set syntax_linkage(numeric-literal) {{} {}}
set syntax_linkage(ordering-term) {expr {compound-select-stmt delete-stmt-limited factored-select-stmt select-stmt simple-select-stmt update-stmt-limited window-defn}}
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(recursive-cte) {cte-table-name {}}
set syntax_linkage(reindex-stmt) {{} sql-stmt}
set syntax_linkage(release-stmt) {{} sql-stmt}
set syntax_linkage(result-column) {expr {select-core select-stmt}}
set syntax_linkage(rollback-stmt) {{} sql-stmt}
set syntax_linkage(savepoint-stmt) {{} sql-stmt}
set syntax_linkage(select-core) {{expr join-clause result-column table-or-subquery window-defn} {compound-select-stmt factored-select-stmt simple-select-stmt}}
set syntax_linkage(select-stmt) {{common-table-expression compound-operator expr join-clause ordering-term result-column table-or-subquery window-defn} {common-table-expression create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt sql-stmt table-or-subquery with-clause}}
set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}}
set syntax_linkage(simple-select-stmt) {{common-table-expression expr ordering-term select-core} {}}
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(table-or-subquery) {{expr join-clause select-stmt} {join-clause select-core select-stmt}}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{column-name-list expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{column-name-list expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(upsert-clause) {{column-name-list expr indexed-column} insert-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
set syntax_linkage(window-defn) {{expr frame-spec ordering-term} {expr select-core select-stmt window-function-invocation}}
set syntax_linkage(window-function-invocation) {{expr filter window-defn} {}}
set syntax_linkage(with-clause) {{cte-table-name select-stmt} {delete-stmt delete-stmt-limited insert-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 cte-table-name recursive-cte common-table-expression 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 upsert-clause pragma-stmt pragma-value reindex-stmt select-stmt join-clause select-core factored-select-stmt simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt column-name-list update-stmt-limited qualified-table-name vacuum-stmt comment-syntax filter window-defn frame-spec function-invocation window-function-invocation}
|
Added art/syntax/window-definition.gif.
cannot compute difference between binary files
Added art/syntax/window-defn.gif.
cannot compute difference between binary files
Added art/syntax/window-function-invocation.gif.
cannot compute difference between binary files