Documentation Source Text

Check-in [3f48c6857f]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the syntax bubble diagrams to show that the WITH clause is allowed.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3f48c6857f80732af2476205cbe5d1830ec14580
User & Date: drh 2014-01-17 23:50:15
Context
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to art/syntax/alter-table-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/analyze-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/attach-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/begin-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/bubble-generator-data.tcl.

    82     82       stack
    83     83          {line CREATE {opt UNIQUE} INDEX {opt IF NOT EXISTS}}
    84     84          {line {optx /database-name .} /index-name
    85     85                ON /table-name ( {loop indexed-column ,} )}
    86     86          {line {optx WHERE /expr}}
    87     87     }
    88     88     indexed-column {
    89         -      line /column-name {optx COLLATE /collation-name} {or ASC DESC nil} 
           89  +      line /column-name {opt COLLATE /collation-name} {or nil ASC DESC} 
    90     90     }
    91     91     create-table-stmt {
    92     92       stack
    93     93          {line CREATE {or {} TEMP TEMPORARY} TABLE {opt IF NOT EXISTS}}
    94     94          {line {optx /database-name .} /table-name}
    95     95          {or {line ( {loop column-def ,} {loop {} {, table-constraint}} )
    96     96                     {opt WITHOUT ROWID}}
................................................................................
   200    200     }
   201    201     create-virtual-table-stmt {
   202    202       stack
   203    203          {line CREATE VIRTUAL TABLE {opt IF NOT EXISTS}}
   204    204          {line {optx /database-name .} /table-name}
   205    205          {line USING /module-name {optx ( {loop /module-argument ,} )}}
   206    206     }
          207  +  with-clause {
          208  +    stack
          209  +      {line WITH {opt RECURSIVE}}
          210  +      {loop {line /table-name {optx ( {loop /column-name ,} )}
          211  +            AS ( /select-stmt )} ,}
          212  +  }
   207    213     delete-stmt {
   208         -    line DELETE FROM qualified-table-name {optx WHERE expr}
          214  +    stack
          215  +      {line {opt with-clause} DELETE FROM qualified-table-name}
          216  +      {optx WHERE expr}
   209    217     }
   210    218     delete-stmt-limited {
   211    219       stack
   212         -        {line DELETE FROM qualified-table-name {optx WHERE expr}}
          220  +        {line {opt with-clause} DELETE FROM qualified-table-name}
          221  +        {optx WHERE expr}
   213    222           {optx
   214    223               {stack
   215    224                 {optx ORDER BY {loop ordering-term ,}}
   216    225                 {line LIMIT /expr {optx {or OFFSET ,} /expr}}
   217    226               }
   218    227           }
   219    228     }
................................................................................
   280    289               {line {loop /digit nil} {opt /decimal-point {loop nil /digit}}}
   281    290               {line /decimal-point {loop /digit nil}}
   282    291            }
   283    292            {opt E {or nil + -} {loop /digit nil}}
   284    293     }
   285    294     insert-stmt {
   286    295       stack
   287         -       {line
          296  +       {line {opt with-clause}
   288    297             {or 
   289         -              {line INSERT {opt OR {or ROLLBACK ABORT REPLACE FAIL IGNORE}}}
          298  +              INSERT
   290    299                 REPLACE
          300  +              {line INSERT OR REPLACE}
          301  +              {line INSERT OR ROLLBACK}
          302  +              {line INSERT OR ABORT}
          303  +              {line INSERT OR FAIL}
          304  +              {line INSERT OR IGNORE}
   291    305             }
   292         -          INTO {optx /database-name .} /table-name
          306  +          INTO
   293    307          }
   294         -       {tailbranch
   295         -          {line 
   296         -                {optx ( {loop /column-name ,} )}
   297         -                {tailbranch
   298         -                    {line VALUES {loop {line ( {loop expr ,} )} ,}}
   299         -                    select-stmt
   300         -                }
   301         -          }
   302         -          {line DEFAULT VALUES}
          308  +       {line {optx /database-name .} /table-name
          309  +             {optx ( {loop /column-name ,} )}}
          310  +       {or
          311  +         {line VALUES {loop {line ( {loop expr ,} )} ,}}
          312  +         select-stmt
          313  +         {line DEFAULT VALUES}
   303    314          }
   304    315     }
   305    316     pragma-stmt {
   306    317        line PRAGMA {optx /database-name .} /pragma-name
   307    318             {or
   308    319                 nil
   309    320                 {line = pragma-value}
................................................................................
   323    334                {line {optx /database-name .}
   324    335                    {tailbranch /table-name /index-name}
   325    336                }
   326    337             }
   327    338     }
   328    339     select-stmt {
   329    340       stack
   330         -       {loop {line select-core nil} {nil compound-operator nil}}
          341  +       {line {opt with-clause}
          342  +             {loop simple-query-stmt {nil compound-operator nil}}}
   331    343          {optx ORDER BY {loop ordering-term ,}}
   332    344          {optx LIMIT /expr {optx {or OFFSET ,} /expr}}
   333    345     }
   334         -  select-core {
   335         -     stack
          346  +  simple-query-stmt {
          347  +    stack
   336    348          {line SELECT {or nil DISTINCT ALL} {loop result-column ,}}
   337    349          {optx FROM join-source}
   338    350          {optx WHERE expr}
   339    351          {optx GROUP BY {loop expr ,} {optx HAVING expr}}
   340         -        
   341    352     }
   342    353     result-column {
   343    354        or
   344    355           *
   345    356           {line /table-name . *}
   346    357           {line expr {optx {optx AS} /column-alias}}
   347    358     }
   348    359     join-source {
   349    360        line
   350         -        single-source
   351         -        {opt {loop {line nil join-op single-source join-constraint nil} {}}}
          361  +        simple-join-source
          362  +        {opt {loop {line nil join-op simple-join-source join-constraint nil} {}}}
   352    363     }
   353         -  single-source {
          364  +  simple-join-source {
   354    365        or
   355         -       {line
   356         -          {optx /database-name .} /table-name
   357         -          {optx {optx AS} /table-alias}
   358         -          {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
   359         -       }
          366  +       {line ( join-source )}
   360    367          {line
   361    368             ( select-stmt ) {optx {optx AS} /table-alias}
   362    369          }
   363         -       {line ( join-source )}
          370  +       {stack
          371  +          {line
          372  +             {optx /database-name .} /table-name
          373  +             {optx {optx AS} /table-alias}
          374  +          }
          375  +          {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
          376  +       }
   364    377     }
   365    378     join-op {
   366    379        or
   367    380           {line ,}
   368    381           {line
   369    382               {opt NATURAL}
   370    383               {or  nil {line LEFT {or OUTER nil}} INNER CROSS}
................................................................................
   381    394         line expr {opt COLLATE /collation-name} {or nil ASC DESC} 
   382    395     }
   383    396     compound-operator {
   384    397        or {line UNION {optx ALL}} INTERSECT EXCEPT
   385    398     }
   386    399     update-stmt {
   387    400        stack
   388         -        {line UPDATE {opt OR {or ROLLBACK ABORT REPLACE FAIL IGNORE}}
          401  +        {line {opt with-clause} UPDATE {or {} {line OR ROLLBACK}
          402  +                                     {line OR ABORT}
          403  +                                     {line OR REPLACE}
          404  +                                     {line OR FAIL}
          405  +                                     {line OR IGNORE}}
   389    406                 qualified-table-name}
   390    407           {line SET {loop {line /column-name = expr} ,} {optx WHERE expr}}
   391    408     }
   392    409     update-stmt-limited {
   393    410        stack
   394         -        {line UPDATE {opt OR {or ROLLBACK ABORT REPLACE FAIL IGNORE}}
          411  +        {line {opt with-clause} UPDATE {or {} {line OR ROLLBACK}
          412  +                                     {line OR ABORT}
          413  +                                     {line OR REPLACE}
          414  +                                     {line OR FAIL}
          415  +                                     {line OR IGNORE}}
   395    416                 qualified-table-name}
   396    417           {line SET {loop {line /column-name = expr} ,} {optx WHERE expr}}
   397    418           {optx
   398    419               {stack
   399    420                 {optx ORDER BY {loop ordering-term ,}}
   400    421                 {line LIMIT /expr {optx {or OFFSET ,} /expr}}
   401    422               }

Changes to art/syntax/column-constraint.gif.

cannot compute difference between binary files

Changes to art/syntax/column-def.gif.

cannot compute difference between binary files

Changes to art/syntax/comment-syntax.gif.

cannot compute difference between binary files

Changes to art/syntax/commit-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/compound-operator.gif.

cannot compute difference between binary files

Changes to art/syntax/conflict-clause.gif.

cannot compute difference between binary files

Changes to art/syntax/create-index-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/create-trigger-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/create-view-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/create-virtual-table-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/delete-stmt-limited.gif.

cannot compute difference between binary files

Changes to art/syntax/delete-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/detach-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/drop-index-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/drop-table-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/drop-trigger-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/drop-view-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/expr.gif.

cannot compute difference between binary files

Changes to art/syntax/foreign-key-clause.gif.

cannot compute difference between binary files

Changes to art/syntax/indexed-column.gif.

cannot compute difference between binary files

Changes to art/syntax/insert-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/join-constraint.gif.

cannot compute difference between binary files

Changes to art/syntax/join-op.gif.

cannot compute difference between binary files

Changes to art/syntax/join-source.gif.

cannot compute difference between binary files

Changes to art/syntax/literal-value.gif.

cannot compute difference between binary files

Changes to art/syntax/numeric-literal.gif.

cannot compute difference between binary files

Changes to art/syntax/ordering-term.gif.

cannot compute difference between binary files

Changes to art/syntax/pragma-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/pragma-value.gif.

cannot compute difference between binary files

Changes to art/syntax/qualified-table-name.gif.

cannot compute difference between binary files

Changes to art/syntax/raise-function.gif.

cannot compute difference between binary files

Changes to art/syntax/reindex-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/release-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/result-column.gif.

cannot compute difference between binary files

Changes to art/syntax/rollback-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/savepoint-stmt.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/signed-number.gif.

cannot compute difference between binary files

Changes to art/syntax/single-source.gif.

cannot compute difference between binary files

Changes to art/syntax/sql-stmt-list.gif.

cannot compute difference between binary files

Changes to art/syntax/sql-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/syntax_linkage.tcl.

     9      9   set syntax_linkage(compound-operator) {{} select-stmt}
    10     10   set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}}
    11     11   set syntax_linkage(create-index-stmt) {indexed-column sql-stmt}
    12     12   set syntax_linkage(create-table-stmt) {{column-def select-stmt table-constraint} sql-stmt}
    13     13   set syntax_linkage(create-trigger-stmt) {{delete-stmt expr insert-stmt select-stmt update-stmt} sql-stmt}
    14     14   set syntax_linkage(create-view-stmt) {select-stmt sql-stmt}
    15     15   set syntax_linkage(create-virtual-table-stmt) {{} sql-stmt}
    16         -set syntax_linkage(delete-stmt) {{expr qualified-table-name} {create-trigger-stmt sql-stmt}}
    17         -set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name} sql-stmt}
           16  +set syntax_linkage(delete-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
           17  +set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
    18     18   set syntax_linkage(detach-stmt) {{} sql-stmt}
    19     19   set syntax_linkage(drop-index-stmt) {{} sql-stmt}
    20     20   set syntax_linkage(drop-table-stmt) {{} sql-stmt}
    21     21   set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
    22     22   set syntax_linkage(drop-view-stmt) {{} sql-stmt}
    23         -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 select-core table-constraint update-stmt update-stmt-limited}}
           23  +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}}
    24     24   set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
    25     25   set syntax_linkage(indexed-column) {{} {create-index-stmt table-constraint}}
    26         -set syntax_linkage(insert-stmt) {{expr select-stmt} {create-trigger-stmt sql-stmt}}
           26  +set syntax_linkage(insert-stmt) {{expr select-stmt with-clause} {create-trigger-stmt sql-stmt}}
    27     27   set syntax_linkage(join-constraint) {expr join-source}
    28     28   set syntax_linkage(join-op) {{} join-source}
    29         -set syntax_linkage(join-source) {{join-constraint join-op single-source} {select-core single-source}}
           29  +set syntax_linkage(join-source) {{join-constraint join-op simple-join-source} {simple-join-source simple-query-stmt}}
    30     30   set syntax_linkage(literal-value) {{} {column-constraint expr}}
    31     31   set syntax_linkage(numeric-literal) {{} {}}
    32     32   set syntax_linkage(ordering-term) {expr {delete-stmt-limited select-stmt update-stmt-limited}}
    33     33   set syntax_linkage(pragma-stmt) {pragma-value sql-stmt}
    34     34   set syntax_linkage(pragma-value) {signed-number pragma-stmt}
    35     35   set syntax_linkage(qualified-table-name) {{} {delete-stmt delete-stmt-limited update-stmt update-stmt-limited}}
    36     36   set syntax_linkage(raise-function) {{} expr}
    37     37   set syntax_linkage(reindex-stmt) {{} sql-stmt}
    38     38   set syntax_linkage(release-stmt) {{} sql-stmt}
    39         -set syntax_linkage(result-column) {expr select-core}
           39  +set syntax_linkage(result-column) {expr simple-query-stmt}
    40     40   set syntax_linkage(rollback-stmt) {{} sql-stmt}
    41     41   set syntax_linkage(savepoint-stmt) {{} sql-stmt}
    42         -set syntax_linkage(select-core) {{expr join-source result-column} select-stmt}
    43         -set syntax_linkage(select-stmt) {{compound-operator ordering-term select-core} {create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt single-source sql-stmt}}
           42  +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}}
    44     43   set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}}
    45         -set syntax_linkage(single-source) {{join-source select-stmt} join-source}
           44  +set syntax_linkage(simple-join-source) {{join-source select-stmt} join-source}
           45  +set syntax_linkage(simple-query-stmt) {{expr join-source result-column} select-stmt}
    46     46   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}
    47     47   set syntax_linkage(sql-stmt-list) {sql-stmt {}}
    48     48   set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt}
    49     49   set syntax_linkage(type-name) {signed-number {column-def expr}}
    50         -set syntax_linkage(update-stmt) {{expr qualified-table-name} {create-trigger-stmt sql-stmt}}
    51         -set syntax_linkage(update-stmt-limited) {{expr ordering-term qualified-table-name} sql-stmt}
           50  +set syntax_linkage(update-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
           51  +set syntax_linkage(update-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
    52     52   set syntax_linkage(vacuum-stmt) {{} sql-stmt}
    53         -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 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 select-core result-column join-source single-source join-op join-constraint ordering-term compound-operator update-stmt update-stmt-limited qualified-table-name vacuum-stmt comment-syntax}
           53  +set syntax_linkage(with-clause) {{} {delete-stmt delete-stmt-limited insert-stmt select-stmt update-stmt update-stmt-limited}}
           54  +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}

Changes to art/syntax/table-constraint.gif.

cannot compute difference between binary files

Changes to art/syntax/type-name.gif.

cannot compute difference between binary files

Changes to art/syntax/update-stmt-limited.gif.

cannot compute difference between binary files

Changes to art/syntax/update-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/vacuum-stmt.gif.

cannot compute difference between binary files

Changes to pages/changes.in.

    14     14   set nChng 0
    15     15   proc chng {date desc {options {}}} {
    16     16     global nChng aChng
    17     17     set aChng($nChng) [list $date $desc $options]
    18     18     incr nChng
    19     19   }
    20     20   
    21         -chng {2014-02-?? (3.8.3)} {
           21  +chng {2014-02-11 (3.8.3)} {
           22  +<li>Added support for [common table expressions] and the [WITH clause].
    22     23   <li>Added the [printf()] SQL function.
    23     24   <li>Added [SQLITE_DETERMINISTIC] as an optional bit in the 4th argument to the
    24     25       [sqlite3_create_function()] and related interfaces, providing applications with
    25     26       the ability to create new functions that can be factored out of inner loops when
    26     27       they have constant arguments.
    27     28   <li>Add [SQLITE_READONLY_DBMOVED] error code, returned at the beginning of a
    28     29       transaction, to indicate that the underlying database file has been renamed

Changes to pages/lang.in.

  3043   3043   [INSERT] command documentation for additional information.</p>  
  3044   3044   
  3045   3045   <tcl>
  3046   3046   ###############################################################################
  3047   3047   Section SELECT select {SELECT query}
  3048   3048   
  3049   3049   BubbleDiagram select-stmt 1
  3050         -BubbleDiagram select-core
         3050  +BubbleDiagram simple-query-stmt
  3051   3051   BubbleDiagram result-column
  3052   3052   BubbleDiagram join-source
  3053         -BubbleDiagram single-source
         3053  +BubbleDiagram simple-join-source
  3054   3054   BubbleDiagram join-op
  3055   3055   BubbleDiagram join-constraint
  3056   3056   BubbleDiagram ordering-term
  3057   3057   BubbleDiagram compound-operator
  3058   3058   </tcl>
  3059   3059   
  3060   3060   
................................................................................
  3068   3068   steps. It is important to keep in mind that this is purely illustrative -
  3069   3069   in practice neither SQLite nor any other SQL engine is required to follow 
  3070   3070   this or any other specific process.
  3071   3071   
  3072   3072   <h3>Simple Select Processing</h3>
  3073   3073   
  3074   3074   <p>The syntax for a simple SELECT statement is depicted in the 
  3075         -[select-core syntax diagram]. Generating the results of a simple SELECT
         3075  +[simple-query-stmt syntax diagram]. Generating the results of a simple SELECT
  3076   3076   statement is presented as a four step process in the description below:
  3077   3077   
  3078   3078   <ol>
  3079   3079     <li> <p>[FROM clause] processing: The input data for the simple SELECT is
  3080   3080          determined. The input data is either implicitly a single row with 0
  3081   3081          columns (if there is no FROM clause) or is determined by analyzing the
  3082   3082          [join-source syntax diagram|join-source] specification that follows 
................................................................................
  3605   3605   The INDEXED BY phrase is an extension that is particular to SQLite and
  3606   3606   is not portable to other SQL database engines.
  3607   3607   The INDEXED BY phrase can be seen in the following syntax
  3608   3608   diagrams:</p>
  3609   3609   
  3610   3610   <tcl>
  3611   3611   BubbleDiagram qualified-table-name
  3612         -BubbleDiagram single-source
         3612  +BubbleDiagram simple-join-source
  3613   3613   </tcl>
  3614   3614   
  3615   3615   <p>^The "INDEXED BY index-name" phrase specifies that the named index
  3616   3616   must be used in order to look up values on the preceding table.
  3617   3617   ^If index-name does not exist or cannot be used for the query, then
  3618   3618   the preparation of the SQL statement fails.
  3619   3619   ^(The "NOT INDEXED" clause specifies that no index shall be used when