Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to the generated column documentation. Notate that the legacy_file_format pragma has been removed and replaced by the SQLITE_DBCONFIG_LEGACY_FILE_FORMAT option to sqlite3_db_config(). |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
4b80493d3729c76f8691e365149204fc |
User & Date: | drh 2019-10-29 16:31:42.139 |
Context
2019-10-29
| ||
19:47 | Fix typos in generated column documentation. (check-in: 3ec77ae315 user: drh tags: trunk) | |
16:31 | Updates to the generated column documentation. Notate that the legacy_file_format pragma has been removed and replaced by the SQLITE_DBCONFIG_LEGACY_FILE_FORMAT option to sqlite3_db_config(). (check-in: 4b80493d37 user: drh tags: trunk) | |
09:03 | Fix typo in the generated columns description. (check-in: eaa1cd343f user: drh tags: trunk) | |
Changes
Changes to pages/changes.in.
︙ | ︙ | |||
22 23 24 25 26 27 28 29 30 31 32 33 34 35 | } chng {2019-12-31 (3.31.0)} { <li>Add support for [generated columns]. <li>Faster response to [sqlite3_interrupt()]. <li>Added the [https://sqlite.org/src/file/ext/misc/uuid.c|uuid.c] extension module implementing functions for processing RFC-4122 UUIDs. } chng {2019-10-11 (3.30.1)} { <li> Fix a bug in the [query flattener] that might cause a segfault for nested queries that use the new [FILTER clause on aggregate functions]. Ticket [https://www.sqlite.org/src/info/1079ad19993d13fa|1079ad19993d13fa] | > > > > > > | 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | } chng {2019-12-31 (3.31.0)} { <li>Add support for [generated columns]. <li>Faster response to [sqlite3_interrupt()]. <li>Added the [https://sqlite.org/src/file/ext/misc/uuid.c|uuid.c] extension module implementing functions for processing RFC-4122 UUIDs. <li>The [legacy_file_format pragma] is deactivated. It is now a no-op. In its place, the [SQLITE_DBCONFIG_LEGACY_FILE_FORMAT] option to [sqlite3_db_config()] is provided. The legacy_file_format pragma is deactivated because (1) it is rarely useful and (2) it is incompatible with [VACUUM] in schemas that have tables with both generated columns and descending indexes. Ticket [https://www.sqlite.org/src/info/6484e6ce678fffab|6484e6ce678fffab] } chng {2019-10-11 (3.30.1)} { <li> Fix a bug in the [query flattener] that might cause a segfault for nested queries that use the new [FILTER clause on aggregate functions]. Ticket [https://www.sqlite.org/src/info/1079ad19993d13fa|1079ad19993d13fa] |
︙ | ︙ |
Changes to pages/gencol.in.
︙ | ︙ | |||
60 61 62 63 64 65 66 | <p>From the point of view of SQL, STORED and VIRTUAL columns are almost exactly the same. Queries against either class of generated column produce the same results. The only functional difference is that one cannot add new STORED columns using the [ALTER TABLE ADD COLUMN] command. Only VIRTUAL columns can be added using ALTER TABLE. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | | | | | > | > > > > > > > > > > > > > > > > | 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 | <p>From the point of view of SQL, STORED and VIRTUAL columns are almost exactly the same. Queries against either class of generated column produce the same results. The only functional difference is that one cannot add new STORED columns using the [ALTER TABLE ADD COLUMN] command. Only VIRTUAL columns can be added using ALTER TABLE. <h2>Capabilities</h2> <ol> <li><p> ^Generated columns can have a datatype. ^SQLite attempts to transform the result of the generating expression into that datatype using the same [affinity] rules as for ordinary columns. <li><p> ^Generated columns may have NOT NULL, CHECK, and UNIQUE constraints, and foreign key constraints, just like ordinary columns. <li><p> ^Generated columns can participate in indexes, just like ordinary columns. <li><p> ^The expression of a generated column can refer to any of the other declared columns in the table, including other generated columns, as long as the expression does not directly or indirectly refer back to itself. <li><p> ^Generated columns can occur anywhere in the table definition. ^Generated columns can be interspersed among ordinary columns. ^It not necessary to put generated columns at the end of the list of columns in the table definition, as is shown in the examples above. </ol> <h2>Limitations</h2> <ol> <li><p> ^Generated columns may not have a [default value] (they may not use the "DEFAULT" clause). The value of a generated columns is always the value specified by the expression that follows the "AS" keyword. <li><p> ^Generated columns may not be used as part of the [PRIMARY KEY]. (Future versions of SQLite might relax this constraint for STORED columns.) <li><p> ^The expression of a generated column has the same restrictions as the expression of a [CHECK constraint]: The expression may only reference constant literals and columns within the same row, and may only use scalar [deterministic functions]. ^The expression may not use subqueries, aggregate functions, window functions, or table-valued functions. <li><p> ^The expression of a generated column may refer to other generated columns in the same row, but no generated column can depend upon itself, either directly or indirectly. ^Nor may a generated column depend on the [ROWID]. <li><p> ^Every table must have at least one non-generated column. <li><p> ^It is not possible to [ALTER TABLE ADD COLUMN] a STORED column. ^One can add a VIRTUAL column, however. </ol> <h1>Compatibility</h1> <p>Generated column support was added with SQLite version 3.31.0 ([dateof:3.31.0]). If an earlier version of SQLite attempts to read a database file that contains a generated column in its schema, then that earlier version will perceive the generated column syntax as an error and will report that the database schema is corrupt. <p>To clarify: SQLite version 3.31.0 can read and write any database created by any prior version of SQLite going back to SQLite 3.0.0 ([dateof:3.0.0]). And, earlier versions of SQLite, prior to 3.31.0, can read and write databases created by SQLite version 3.31.0 and later as long as the database schema does not contain features, such as generated columns, that are not understood by the earlier version. Problems only arise if you create a new database that contains generated columns, using SQLite version 3.31.0 or later, and then try to read or write that database file using an earlier version of SQLite that does not understand generated columns. |
Changes to pages/pragma.in.
︙ | ︙ | |||
827 828 829 830 831 832 833 | [sqlite3_module.xRename] method finishes. <p>The legacy alter table behavior can also be toggled on and off using the [SQLITE_DBCONFIG_LEGACY_ALTER_TABLE] option to the [sqlite3_db_config()] interface. } Pragma legacy_file_format { | | > | < < < < < < | < < < < | < < | < < | 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 | [sqlite3_module.xRename] method finishes. <p>The legacy alter table behavior can also be toggled on and off using the [SQLITE_DBCONFIG_LEGACY_ALTER_TABLE] option to the [sqlite3_db_config()] interface. } Pragma legacy_file_format { <p>^(<b>PRAGMA legacy_file_format;</b> <p>This pragma no longer functions. It has become a no-op.)^ The capabilities formerly provided by PRAGMA legacy_file_format are now available using the [SQLITE_DBCONFIG_LEGACY_FILE_FORMAT] option to the [sqlite3_db_config()] C-language interface. <p> } Pragma {locking_mode {exclusive locking mode} {EXCLUSIVE locking mode}} { <p>^(<b>PRAGMA DB.locking_mode; <br>PRAGMA DB.locking_mode = <i>NORMAL | EXCLUSIVE</i></b>)^</p> <p>^This pragma sets or queries the database connection locking-mode. |
︙ | ︙ |