Documentation Source Text

Check-in [3ec77ae315]
Login

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

Overview
Comment:Fix typos in generated column documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3ec77ae315610b591d68dda4d794e67ead2f7248268ec903b3a71d6de05a8e67
User & Date: drh 2019-10-29 19:47:56.977
Context
2019-11-02
18:17
Update the keyword list. (check-in: 6904f495e9 user: drh tags: trunk)
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)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/gencol.in.
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
   e TEXT AS (substr(c,b,b+1)) STORED
);
</codeblock>

<h2>VIRTUAL versus STORED columns</h2>

<p>Generated columns can be either VIRTUAL or STORED.  The value of
a VIRTUAL columns is computed when read, whereas the value of a STORED
column is computed when the row is written.  STORED columns take up space
in the database file, whereas VIRTUAL columns use more CPU cycles when
being read.

<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







|







48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
   e TEXT AS (substr(c,b,b+1)) STORED
);
</codeblock>

<h2>VIRTUAL versus STORED columns</h2>

<p>Generated columns can be either VIRTUAL or STORED.  The value of
a VIRTUAL column is computed when read, whereas the value of a STORED
column is computed when the row is written.  STORED columns take up space
in the database file, whereas VIRTUAL columns use more CPU cycles when
being read.

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


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







|
















|
|
>
>
>







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


<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 column 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>^The expression of a generated column may not directly reference
the [ROWID], though it can reference the [INTEGER PRIMARY KEY] column,
which is often the same thing.

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