Documentation Source Text

Check-in [4b80493d37]
Login

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: 4b80493d3729c76f8691e365149204fc854540d04993a4daf39800de5481b005
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
Unified Diff Show Whitespace Changes Patch
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






























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












<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>Restrictions And 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.


<li><p>
Every table must have at least one non-generated column.




</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.



















>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|



|




|



|


|



|

|
>


|
>
>
>
>









>
>
>
>
>
>
>
>
>
>
>
>
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
834

835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
    [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;

       <br>PRAGMA legacy_file_format = <i>boolean</i></b></p>
    <p>This pragma sets or queries the value of the legacy_file_format
    flag.)^  ^(When this flag is on, new SQLite databases are created in
    a file format that is readable and writable by all versions of
    SQLite going back to 3.0.0.)^  ^(When the flag is off, new databases
    are created using the latest file format which might not be
    readable or writable by versions of SQLite prior to 3.3.0.)^</p>

    <p>^When the legacy_file_format pragma is issued with no argument,
    it returns the setting of the flag.  ^This pragma does <u>not</u> tell
    which file format the current database is using; it tells what format
    will be used by any newly created databases.</p>

    <p>^The legacy_file_format pragma is initialized to OFF when an existing
    database in the newer file format is first opened.</p>

    <p>^The default file format is set by the
    [SQLITE_DEFAULT_FILE_FORMAT] compile-time option.</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. 







|
>
|
<
<
<
<
<
<
|
<
<
<
<
|
<
<
|
<
<







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.