Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance wrap.tcl to recognize <yyterm> elements in the input HTML and convert them into real HTML that renders an terminal-symbol oval around the enclosed text. Use this markup when talking about terminal symbols in the language specification. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
8e1d4f3bb5b9fc6a0f0036baf72d4cf5 |
User & Date: | drh 2015-09-09 15:39:43.051 |
Context
2015-09-09
| ||
19:01 | Add examples to the json1 documentation. (check-in: bc16b0015f user: drh tags: trunk) | |
15:39 | Enhance wrap.tcl to recognize <yyterm> elements in the input HTML and convert them into real HTML that renders an terminal-symbol oval around the enclosed text. Use this markup when talking about terminal symbols in the language specification. (check-in: 8e1d4f3bb5 user: drh tags: trunk) | |
02:06 | Fix a typo in the json1 documentation. (check-in: 57b4eafa71 user: drh tags: trunk) | |
Changes
Changes to pages/lang.in.
︙ | ︙ | |||
125 126 127 128 129 130 131 | </tcl> <p>SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. </p> | | | | 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | </tcl> <p>SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. </p> <p> ^(The RENAME TO syntax changes the name of <yyterm>table-name</yyterm> to <yyterm>new-table-name</yyterm>.)^ This command cannot be used to move a table between attached databases, only to rename a table within the same database.</p> <p> ^If the table being renamed has triggers or indices, then these remain attached to the table after it has been renamed. ^However, if there are any view definitions, or statements executed by triggers that refer to |
︙ | ︙ | |||
479 480 481 482 483 484 485 | <p> ^END TRANSACTION is an alias for COMMIT. </p> <p> ^(Transactions created using BEGIN...COMMIT do not nest.)^ ^For nested transactions, use the [SAVEPOINT] and [RELEASE] commands. | | | 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 | <p> ^END TRANSACTION is an alias for COMMIT. </p> <p> ^(Transactions created using BEGIN...COMMIT do not nest.)^ ^For nested transactions, use the [SAVEPOINT] and [RELEASE] commands. The "TO SAVEPOINT <yyterm>name</yyterm>" clause of the ROLLBACK command shown in the syntax diagram above is only applicable to [SAVEPOINT] transactions. ^An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by [SAVEPOINT] or a prior BEGIN. ^The COMMIT command and the ROLLBACK command without the TO clause work the same on [SAVEPOINT] transactions as they do with transactions started by BEGIN.</p> |
︙ | ︙ | |||
843 844 845 846 847 848 849 | </ul> <p>Every CREATE TABLE statement must specify a name for the new table. ^(Table names that begin with "sqlite_" are reserved for internal use. It is an error to attempt to create a table with a name that starts with "sqlite_".)^ | | | > | | 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 | </ul> <p>Every CREATE TABLE statement must specify a name for the new table. ^(Table names that begin with "sqlite_" are reserved for internal use. It is an error to attempt to create a table with a name that starts with "sqlite_".)^ <p> ^If a <yyterm>schema-name</yyterm> is specified, it must be either "main", "temp", or the name of an [ATTACH DATABASE|attached database]. ^In this case the new table is created in the named database. ^If the "TEMP" or "TEMPORARY" keyword occurs between the "CREATE" and "TABLE" then the new table is created in the temp database. ^It is an error to specify both a <yyterm>schema-name</yyterm> and the TEMP or TEMPORARY keyword, unless the <yyterm>schema-name</yyterm> is "temp". ^If no schema name is specified and the TEMP keyword is not present then the table is created in the main database. <p> ^It is usually an error to attempt to create a new table in a database that already contains a table, index or view of the same name. ^However, if the "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and |
︙ | ︙ | |||
917 918 919 920 921 922 923 924 925 926 927 928 929 930 | described under [constraints|SQL Data Constraints] below. <p>^Unlike most SQL databases, SQLite does not restrict the type of data that may be inserted into a column based on the columns declared type. Instead, SQLite uses [dynamic typing]. ^The declared type of a column is used to determine the [affinity] of the column only. <p>The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an [INSERT]. ^If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL. ^(An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special case-independent keywords | > | 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 | described under [constraints|SQL Data Constraints] below. <p>^Unlike most SQL databases, SQLite does not restrict the type of data that may be inserted into a column based on the columns declared type. Instead, SQLite uses [dynamic typing]. ^The declared type of a column is used to determine the [affinity] of the column only. <tcl>hd_fragment dfltval {default column value} {default value}</tcl> <p>The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an [INSERT]. ^If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL. ^(An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special case-independent keywords |
︙ | ︙ | |||
967 968 969 970 971 972 973 | <tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl> <p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. ^Or, if a PRIMARY KEY clause is specified as a [table-constraint], then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. ^The PRIMARY KEY clause must contain only column names — the use of | | | | 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 | <tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl> <p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. ^Or, if a PRIMARY KEY clause is specified as a [table-constraint], then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. ^The PRIMARY KEY clause must contain only column names — the use of expressions in an [indexed-column] of a PRIMARY KEY is not supported. ^An error is raised if more than one PRIMARY KEY clause appears in a CREATE TABLE statement. ^The PRIMARY KEY is optional for ordinary tables but is required for [WITHOUT ROWID] tables. <p>If a table has a single column primary key and the declared type of that column is "INTEGER" and the table is not a [WITHOUT ROWID] table, then the column is known as an [INTEGER PRIMARY KEY]. See [rowid|below] for a description of the special properties and behaviors associated with an [INTEGER PRIMARY KEY]. <p>^Each row in a table with a primary key must have a unique combination of values in its primary key columns. ^For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so that two or more rows |
︙ | ︙ | |||
1003 1004 1005 1006 1007 1008 1009 | that a single table may have any number of UNIQUE constraints. ^For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. ^For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs. ^As with PRIMARY KEYs, a UNIQUE [table-constraint] clause must contain only column names — the use of | | | 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 | that a single table may have any number of UNIQUE constraints. ^For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. ^For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs. ^As with PRIMARY KEYs, a UNIQUE [table-constraint] clause must contain only column names — the use of expressions in an [indexed-column] of a UNIQUE [table-constraint] is not supported. <p>^In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are [INTEGER PRIMARY KEY] and PRIMARY KEYs on [WITHOUT ROWID] tables.) |
︙ | ︙ | |||
1394 1395 1396 1397 1398 1399 1400 | </p> <p>^If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "VIEW" then the view that is created is only visible to the [database connection] that created it and is automatically deleted when the database connection is closed.</p> | | > | | > | > | | | | < | < | | | 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 | </p> <p>^If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "VIEW" then the view that is created is only visible to the [database connection] that created it and is automatically deleted when the database connection is closed.</p> <p> ^If a <yyterm>schema-name</yyterm> is specified, then the view is created in the specified database. ^It is an error to specify both a <yyterm>schema-name</yyterm> and the TEMP keyword on a VIEW, unless the <yyterm>schema-name</yyterm> is "temp". ^If no schema name is specified, and the TEMP keyword is not present, the VIEW is created in the main database.</p> <p>^You cannot [DELETE], [INSERT], or [UPDATE] a view. ^Views are read-only in SQLite. ^However, in many cases you can use an [INSTEAD OF trigger] on the view to accomplish the same thing. ^Views are removed with the [DROP VIEW] command.</p> <p>^If a <yyterm>column-name</yyterm> list follows the <yyterm>view-name</yyterm>, then that list determines the names of the columns for the view. ^If the <yyterm>column-name</yyterm> list is omitted, then the names of the columns in the view are derived from the names of the result-set columns in the [select-stmt]. Note that the <yyterm>column-name</yyterm> list syntax is only supported in SQLite versions 3.8.12 and later. <tcl> ############################################################################## Section {CREATE VIRTUAL TABLE} {createvtab} {{CREATE VIRTUAL TABLE}} RecursiveBubbleDiagram create-virtual-table-stmt </tcl> <p>A [virtual table] is an interface to an external storage or computation engine that appears to be a table but does not actually store information in the database file.</p> <p>In general, you can do anything with a [virtual table] that can be done with an ordinary table, except that ^you cannot create indices or triggers on a virtual table. ^Some virtual table implementations might impose additional restrictions. For example, many virtual tables are read-only.</p> <p>The <yyterm>module-name</yyterm> is the name of an object that implements the virtual table. ^The <yyterm>module-name</yyterm> must be registered with the SQLite database connection using [sqlite3_create_module()] or [sqlite3_create_module_v2()] prior to issuing the CREATE VIRTUAL TABLE statement. ^The module takes zero or more comma-separated arguments. ^The arguments can be just about any text as long as it has balanced parentheses. The argument syntax is sufficiently general that the arguments can be made to appear as [column definitions] in a traditional |
︙ | ︙ | |||
1458 1459 1460 1461 1462 1463 1464 | ############################################################################## Section DELETE delete {DELETE *DELETEs} RecursiveBubbleDiagram delete-stmt </tcl> <p>The DELETE command removes records from the table identified by the | | | | > | | 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 | ############################################################################## Section DELETE delete {DELETE *DELETEs} RecursiveBubbleDiagram delete-stmt </tcl> <p>The DELETE command removes records from the table identified by the [qualified-table-name]. <p>^If the WHERE clause is not present, all records in the table are deleted. ^If a WHERE clause is supplied, then only those rows for which the WHERE clause [boolean expression] is true are deleted. ^Rows for which the expression is false or NULL are retained. <h3>Restrictions on DELETE Statements Within CREATE TRIGGER</h3> <p>The following restrictions apply to DELETE statements that occur within the body of a [CREATE TRIGGER] statement: <ul> <li><p>^The <yyterm>table-name</yyterm> specified as part of a DELETE statement within a trigger body must be unqualified. ^(In other words, the <i>schema-name</i><b>.</b> prefix on the table name is not allowed within triggers.)^ ^If the table to which the trigger is attached is not in the temp database, then DELETE statements within the trigger body must operate on tables within the same database as it. ^If the table to which the trigger is attached is in the TEMP database, then the unqualified name of the table being deleted is resolved in the same way as |
︙ | ︙ | |||
1600 1601 1602 1603 1604 1605 1606 | ^All indices and triggers associated with the table are also deleted.</p> <p>^The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.</p> <p>^If [foreign key constraints] are enabled, a DROP TABLE command performs an | | | | | | | 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 | ^All indices and triggers associated with the table are also deleted.</p> <p>^The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.</p> <p>^If [foreign key constraints] are enabled, a DROP TABLE command performs an implicit [DELETE | DELETE FROM] command before removing the table from the database schema. ^Any triggers attached to the table are dropped from the database schema before the implicit DELETE FROM is executed, so this cannot cause any triggers to fire. By contrast, ^an implicit DELETE FROM does cause any configured [foreign key actions] to take place. ^If the implicit DELETE FROM executed as part of a DROP TABLE command violates any immediate foreign key constraints, an error is returned and the table is not dropped. ^If the implicit DELETE FROM causes any deferred foreign key constraints to be violated, and the violations still exist when the transaction is committed, an error is returned at the time of commit. <tcl> ############################################################################## Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}} |
︙ | ︙ | |||
2175 2176 2177 2178 2179 2180 2181 | a correlated subquery. ^A correlated subquery is reevaluated each time its result is required. ^An uncorrelated subquery is evaluated only once and the result reused as necessary. <tcl>hd_fragment castexpr {CAST expression} {CAST} {cast} {CAST operator}</tcl> <h3>CAST expressions</h3> | | | | | | | > | | 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 | a correlated subquery. ^A correlated subquery is reevaluated each time its result is required. ^An uncorrelated subquery is evaluated only once and the result reused as necessary. <tcl>hd_fragment castexpr {CAST expression} {CAST} {cast} {CAST operator}</tcl> <h3>CAST expressions</h3> <p>A CAST expression of the form "CAST(<i>expr</i> AS <i>type-name</i>)" is used to convert the value of <i>expr</i> to a different [storage class] specified by <yyterm>type-name</yyterm>. ^A CAST conversion is similar to the conversion that takes place when a [column affinity] is applied to a value except that with the CAST operator the conversion always takes place even if the conversion lossy and irreversible, whereas column affinity only changes the data type of a value if the change is lossless and reversible. <p>^If the value of <i>expr</i> is NULL, then the result of the CAST expression is also NULL. ^Otherwise, the storage class of the result is determined by applying the [rules for determining column affinity] to the <yyterm>type-name</yyterm>. <table border=1> <tr> <th> Affinity of <yyterm>type-name</yyterm> <th> Conversion Processing <tr> <td> NONE <td> ^Casting a value to a <yyterm>type-name</yyterm> with no affinity causes the value to be converted into a BLOB. ^Casting to a BLOB consists of first casting the value to TEXT in the [encoding] of the database connection, then interpreting the resulting byte sequence as a BLOB instead of as TEXT. <tr> <td> TEXT <td> ^To cast a BLOB value to TEXT, the sequence of bytes that make up the |
︙ | ︙ | |||
2229 2230 2231 2232 2233 2234 2235 | <td> ^When casting a BLOB value to INTEGER, the value is first converted to TEXT. <p>^When casting a TEXT value to INTEGER, the longest possible prefix of the value that can be interpreted as an integer number is extracted from the TEXT value and the remainder ignored. ^Any leading spaces in the TEXT value when converting from TEXT to INTEGER are ignored. ^If there is no prefix that can be interpreted as an integer number, the result | | | | | | 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 | <td> ^When casting a BLOB value to INTEGER, the value is first converted to TEXT. <p>^When casting a TEXT value to INTEGER, the longest possible prefix of the value that can be interpreted as an integer number is extracted from the TEXT value and the remainder ignored. ^Any leading spaces in the TEXT value when converting from TEXT to INTEGER are ignored. ^If there is no prefix that can be interpreted as an integer number, the result of the conversion is 0. ^(The CAST operator understands decimal integers only — conversion of [hexadecimal integers] stops at the "x" in the "0x" prefix of the hexadecimal integer string and thus result of the CAST is always zero.)^ <p>^A cast of a REAL value into an INTEGER results in the integer between the REAL value and zero that is closest to the REAL value. ^If a REAL is greater than the greatest possible signed integer (+9223372036854775807) then the result is the greatest possible signed integer and if the REAL is less than the least possible signed integer (-9223372036854775808) then the result is the least possible |
︙ | ︙ | |||
3115 3116 3117 3118 3119 3120 3121 | RecursiveBubbleDiagram insert-stmt </tcl> <p>The INSERT statement comes in three basic forms. <ul> <li><p>^The first form (with the "VALUES" keyword) creates one or more new rows in | | > | | | | | | | | | | > | 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 | RecursiveBubbleDiagram insert-stmt </tcl> <p>The INSERT statement comes in three basic forms. <ul> <li><p>^The first form (with the "VALUES" keyword) creates one or more new rows in an existing table. ^If the <yyterm>column-name</yyterm> list after <yyterm>table-name</yyterm> is omitted then the number of values inserted into each row must be the same as the number of columns in the table. ^In this case the result of evaluating the left-most expression from each term of the VALUES list is inserted into the left-most column of each new row, and so forth for each subsequent expression. ^If a <yyterm>column-name</yyterm> list is specified, then the number of values in each term of the VALUE list must match the number of specified columns. ^Each of the named columns of the new row is populated with the results of evaluating the corresponding VALUES expression. ^Table columns that do not appear in the column list are populated with the [default column value] (specified as part of the [CREATE TABLE] statement), or with NULL if no [default value] is specified. <li><p>The second form of the INSERT statement contains a [SELECT] statement instead of a VALUES clause. ^A new entry is inserted into the table for each row of data returned by executing the SELECT statement. ^If a column-list is specified, the number of columns in the result of the SELECT must be the same as the number of items in the column-list. ^Otherwise, if no column-list is specified, the number of columns in the result of the SELECT must be the same as the number of columns in the table. ^Any SELECT statement, including [compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, may be used in an INSERT statement of this form. <li><p>The third form of an INSERT statement is with DEFAULT VALUES. ^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the named table.)^ ^Each column of the new row is populated with its [default value], or with a NULL if no default value is specified as part of the column definition in the [CREATE TABLE] statement. </ul> <p>^The "REPLACE" and "INSERT OR <i>action</i>" forms specify an alternative constraint conflict resolution algorithm to use during this one INSERT command. See the section titled [ON CONFLICT] for additional information. For compatibility with MySQL, ^the parser allows the use of the single keyword <a href="lang_replace.html">REPLACE</a> as an alias for "INSERT OR REPLACE". <p>^(The optional "<i>schema-name</i><b>.</b>" prefix on the <yyterm>table-name</yyterm> is support for top-level INSERT statements only.)^ ^The table name must be unqualified for INSERT statements that occur within [CREATE TRIGGER] statements. ^Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for top-level INSERT statements only and not for INSERT statements within triggers. <p> |
︙ | ︙ | |||
3282 3283 3284 3285 3286 3287 3288 | all indices in all attached databases that use the named collation sequences are recreated. <p>^Or, if the argument attached to the REINDEX identifies a specific database table, then all indices attached to the database table are rebuilt. ^If it identifies a specific database index, then just that index is recreated. | > | | | 3289 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 3308 | all indices in all attached databases that use the named collation sequences are recreated. <p>^Or, if the argument attached to the REINDEX identifies a specific database table, then all indices attached to the database table are rebuilt. ^If it identifies a specific database index, then just that index is recreated. <p>^If no <yyterm>schema-name</yyterm> is specified and there exists both a table or index and a collation sequence of the specified name, SQLite interprets this as a request to rebuild the indices that use the named collation sequence. This ambiguity in the syntax may be avoided by always specifying a <yyterm>schema-name</yyterm> when reindexing a specific table or index. <tcl> ############################################################################### Section REPLACE replace REPLACE </tcl> |
︙ | ︙ | |||
3360 3361 3362 3363 3364 3365 3366 | </ol> <p>To put it another way, a recursive common table expression must look like the following: <tcl>RecursiveBubbleDiagram recursive-cte</tcl> | | | | | > | > | > | | | > | | > | > | 3368 3369 3370 3371 3372 3373 3374 3375 3376 3377 3378 3379 3380 3381 3382 3383 3384 3385 3386 3387 3388 3389 3390 3391 3392 3393 3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 3409 3410 3411 3412 3413 3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 3436 | </ol> <p>To put it another way, a recursive common table expression must look like the following: <tcl>RecursiveBubbleDiagram recursive-cte</tcl> <p>Call the table named by the [cte-table-name] in a recursive common table expression the "recursive table". In the [recursive-cte] bubble diagram above, the recursive table must appear exactly once in the FROM clause of the <yyterm>recursive-select</yyterm> and must not appear anywhere else in either the <yyterm>initial-select</yyterm> or the <yyterm>recursive-select</yyterm>, including subqueries. The <yyterm>initial-select</yyterm> may be a [compound select], but it may not include an ORDER BY, LIMIT, or OFFSET. The recursive-select must be a simple select, not a compound. The recursive-select is allowed to include an ORDER BY, LIMIT, and/or OFFSET. <p>The basic algorithm for computing the content of the recursive table is as follows: <ol> <li> Run the <yyterm>initial-select</yyterm> and add the results to a queue. <li> While the queue is not empty: <ol type="a"> <li> Extract a single row from the queue. <li> Insert that single row into the recursive table <li> Pretend that the single row just extracted is the only row in the recursive table and run the recursive-select, adding all results to the queue. </ol> </ol> <p>The basic procedure above may modified by the following additional rules: <ul> <li><p> If a UNION operator connects the <yyterm>initial-select</yyterm> with the <yyterm>recursive-select</yyterm>, then only add rows to the queue if no identical row has been previously added to the queue. Repeated rows are discarded before being added to the queue even if the repeated rows have already been extracted from the queue by the recursion step. If the operator is UNION ALL, then all rows generated by both the <yyterm>initial-select</yyterm> and the <yyterm>recursive-select</yyterm> are always added to the queue even if they are repeats. When determining if a row is repeated, NULL values compare equal to one another and not equal to any other value. <li><p> The LIMIT clause, if present, determines the maximum number of rows that will ever be added to the recursive table in step 2b. Once the limit is reached, the recursion stops. A limit of zero means that no rows are ever added to the recursive table, and a negative limit means an unlimited number of rows may be added to the recursive table. <li><p> The OFFSET clause, if it is present and has a positive value N, prevents the first N rows from being added to the recursive table. The first N rows are still processed by the <yyterm>recursive-select</yyterm> — they just are not added to the recursive table. Rows are not counted toward fulfilling the LIMIT until all OFFSET rows have been skipped. <li><p> If an ORDER BY clause is present, it determines the order in which rows are extracted from the queue in step 2a. If there is no ORDER BY clause, then the order in which rows are extracted is undefined. (In the current implementation, the queue becomes a FIFO if the ORDER BY clause is omitted, |
︙ | ︙ | |||
4399 4400 4401 4402 4403 4404 4405 | Section UPDATE update {UPDATE *UPDATEs} RecursiveBubbleDiagram update-stmt </tcl> <p>^An UPDATE statement is used to modify a subset of the values stored in zero or more rows of the database table identified by the | | | > | > | | > | 4413 4414 4415 4416 4417 4418 4419 4420 4421 4422 4423 4424 4425 4426 4427 4428 4429 4430 4431 4432 4433 4434 4435 4436 4437 4438 4439 4440 4441 4442 4443 4444 4445 4446 4447 4448 4449 4450 4451 4452 4453 4454 4455 4456 4457 4458 4459 4460 | Section UPDATE update {UPDATE *UPDATEs} RecursiveBubbleDiagram update-stmt </tcl> <p>^An UPDATE statement is used to modify a subset of the values stored in zero or more rows of the database table identified by the [qualified-table-name] specified as part of the UPDATE statement. <p>^If the UPDATE statement does not have a WHERE clause, all rows in the table are modified by the UPDATE. ^Otherwise, the UPDATE affects only those rows for which the WHERE clause [boolean expression|boolean expression is true]. ^It is not an error if the WHERE clause does not evaluate to true for any row in the table - this just means that the UPDATE statement affects zero rows. <p>The modifications made to each row affected by an UPDATE statement are determined by the list of assignments following the SET keyword. Each assignment specifies a <yyterm>column-name</yyterm> to the left of the equals sign and a scalar expression to the right. ^For each affected row, the named columns are set to the values found by evaluating the corresponding scalar expressions. ^If a single column-name appears more than once in the list of assignment expressions, all but the rightmost occurrence is ignored. ^Columns that do not appear in the list of assignments are left unmodified. ^The scalar expressions may refer to columns of the row being updated. ^In this case all scalar expressions are evaluated before any assignments are made. <p>^The optional "OR <i>action</i>" conflict clause that follows the UPDATE keyword allows the user to nominate a specific constraint conflict resolution algorithm to use during this one UPDATE command. Refer to the section entitled [ON CONFLICT] for additional information. <h3>Restrictions on UPDATE Statements Within CREATE TRIGGER</h3> <p>The following additional syntax restrictions apply to UPDATE statements that occur within the body of a [CREATE TRIGGER] statement. <ul> <li><p>^The <yyterm>table-name</yyterm> specified as part of an UPDATE statement within a trigger body must be unqualified. ^(In other words, the <i>schema-name</i><b>.</b> prefix on the table name of the UPDATE is not allowed within triggers.)^ ^Unless the table to which the trigger is attached is in the TEMP database, the table being updated by the trigger program must reside in the same database as it. ^If the table to which the trigger is attached is in the TEMP database, then the unqualified name of the table being updated is resolved in the same way |
︙ | ︙ | |||
4555 4556 4557 4558 4559 4560 4561 | The INDEXED BY phrase is an SQLite extension and is not portable to other SQL database engines.</p> <tcl> RecursiveBubbleDiagram qualified-table-name </tcl> | > | | | > | | 4572 4573 4574 4575 4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 4596 4597 4598 4599 4600 4601 4602 4603 4604 4605 4606 4607 4608 4609 4610 4611 4612 | The INDEXED BY phrase is an SQLite extension and is not portable to other SQL database engines.</p> <tcl> RecursiveBubbleDiagram qualified-table-name </tcl> <p>^The "INDEXED BY <yyterm>index-name</yyterm>" phrase specifies that the named index must be used in order to look up values on the preceding table. ^If <yyterm>index-name</yyterm> 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 accessing the preceding table, including implied indices create by UNIQUE and PRIMARY KEY constraints. However, the [rowid] can still be used to look up entries even when "NOT INDEXED" is specified.)^</p> <p>Some SQL database engines provide non-standard "hint" mechanisms which can be used to give the query optimizer clues about what indices it should use for a particular statement. The INDEX BY clause of SQLite is <em>not</em> a hinting mechanism and it should not be used as such. ^The INDEXED BY clause does not give the optimizer hints about which index to use; it gives the optimizer a requirement of which index to use. ^If the query optimizer is unable to use the index specified by the INDEX BY clause, then the query will fail with an error.</p> <p>The INDEXED BY clause is <em>not</em> intended for use in tuning the performance of a query. The intent of the INDEXED BY clause is to raise a run-time error if a schema change, such as dropping or creating an index, causes the query plan for a time-sensitive query to change. The INDEXED BY clause is designed to help detect undesirable query plan changes during regression testing. Application developers are admonished to omit all use of INDEXED BY during application design, implementation, testing, and tuning. If INDEXED BY is to be used at all, it should be inserted at the very end of the development process when "locking down" a design.</p> <h3>See Also:</h3> <ol> |
︙ | ︙ |
Changes to pages/pragma.in.
︙ | ︙ | |||
14 15 16 17 18 19 20 | } unset -nocomplain PragmaBody PragmaRef PragmaDud PragmaKeys # Each pragma is recorded by invoking this procedure. proc Pragma {namelist content} { global PragmaBody PragmaRef PragmaKeys set main_name [lindex $namelist 0] | | | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | } unset -nocomplain PragmaBody PragmaRef PragmaDud PragmaKeys # Each pragma is recorded by invoking this procedure. proc Pragma {namelist content} { global PragmaBody PragmaRef PragmaKeys set main_name [lindex $namelist 0] regsub -all {PRAGMA DB\.} $content {PRAGMA </b><i>schema.</i><b>} content set PragmaBody($main_name) $content set PragmaKeys($main_name) $namelist foreach x $namelist { set PragmaRef($x) $main_name } } proc LegacyDisclaimer {} { |
︙ | ︙ | |||
124 125 126 127 128 129 130 | (Example: <tt>'yes' [FALSE]</tt>.) Some pragmas takes a string literal as their argument. When pragma takes a keyword argument, it will usually also take a numeric equivalent as well. For example, "0" and "no" mean the same thing, as does "1" and "yes". When querying the value of a setting, many pragmas return the number rather than the keyword.</p> | | > | | | | | | 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | (Example: <tt>'yes' [FALSE]</tt>.) Some pragmas takes a string literal as their argument. When pragma takes a keyword argument, it will usually also take a numeric equivalent as well. For example, "0" and "no" mean the same thing, as does "1" and "yes". When querying the value of a setting, many pragmas return the number rather than the keyword.</p> <p>^A pragma may have an optional <yyterm>schema-name</yyterm> before the pragma name. ^The <yyterm>schema-name</yyterm> is the name of an [ATTACH]-ed database or "main" or "temp" for the main and the TEMP databases. ^If the optional schema name is omitted, "main" is assumed. ^In some pragmas, the schema name is meaningless and is simply ignored. In the documentation below, pragmas for which the schema name is meaningful are shown with a "<i>schema.</i>" prefix.</p> <tcl>Pragma {application_id} { <p><b>PRAGMA DB.application_id; <br>PRAGMA DB.application_id = </b><i>integer </i><b>;</b> |
︙ | ︙ |
Changes to wrap.tcl.
︙ | ︙ | |||
71 72 73 74 75 76 77 78 79 80 81 82 83 84 | # appropriate <a href=""> markup. # # Links to keywords within the same main file are resolved using # $::llink() if possible. All other links and links that could # not be resolved using $::llink() are resolved using $::glink(). # proc hd_resolve_2ndpass {text} { regsub -all {\[(.*?)\]} $text \ "\175; hd_resolve_one \173\\1\175; hd_puts \173" text eval "hd_puts \173$text\175" } proc hd_resolve_one {x} { if {[string is integer $x]} { hd_puts \[$x\] | > > | 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | # appropriate <a href=""> markup. # # Links to keywords within the same main file are resolved using # $::llink() if possible. All other links and links that could # not be resolved using $::llink() are resolved using $::glink(). # proc hd_resolve_2ndpass {text} { regsub -all {<yyterm>} $text {<span class='yyterm'>} text regsub -all {</yyterm>} $text {</span>} text regsub -all {\[(.*?)\]} $text \ "\175; hd_resolve_one \173\\1\175; hd_puts \173" text eval "hd_puts \173$text\175" } proc hd_resolve_one {x} { if {[string is integer $x]} { hd_puts \[$x\] |
︙ | ︙ | |||
460 461 462 463 464 465 466 467 468 469 470 471 472 473 | .fancy p.todo { border: solid #AA3333 1px; padding: 1ex } .fancy img { display:block; } .fancy :link:hover, .fancy :visited:hover { background: wheat } .fancy p,.fancy ul,.fancy ol,.fancy dl { margin: 1em 5ex } .fancy li p { margin: 1em 0 } /* End of "fancyformat" specific rules. */ </style> } puts $fd {</head>} if {[file exists DRAFT]} { set tagline {<font size="6" color="red">*** DRAFT ***</font>} } else { set tagline {Small. Fast. Reliable.<br>Choose any three.} | > > > > > > > | 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 | .fancy p.todo { border: solid #AA3333 1px; padding: 1ex } .fancy img { display:block; } .fancy :link:hover, .fancy :visited:hover { background: wheat } .fancy p,.fancy ul,.fancy ol,.fancy dl { margin: 1em 5ex } .fancy li p { margin: 1em 0 } /* End of "fancyformat" specific rules. */ .yyterm { background: #fff; border: 1px solid #000; border-radius: 11px; padding-left: 4px; padding-right: 4px; } </style> } puts $fd {</head>} if {[file exists DRAFT]} { set tagline {<font size="6" color="red">*** DRAFT ***</font>} } else { set tagline {Small. Fast. Reliable.<br>Choose any three.} |
︙ | ︙ |