Documentation Source Text

Check-in [4ebc1051c2]
Login

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

Overview
Comment:Documentation on the new SQLITE_DBCONFIG options.
Timelines: family | ancestors | descendants | both | new-dbconfig-options
Files: files | file ages | folders
SHA3-256: 4ebc1051c22d1dbd9b670c3c70cf730f2ee8f273c90833ec06f2d8b643749407
User & Date: drh 2019-06-15 15:24:54
Context
2019-06-17
13:56
Track the revised DQS interface. Closed-Leaf check-in: 09cea3924b user: drh tags: new-dbconfig-options
2019-06-15
15:24
Documentation on the new SQLITE_DBCONFIG options. check-in: 4ebc1051c2 user: drh tags: new-dbconfig-options
2019-06-12
20:31
Fix the description of how collation is computed to indicate that the collation is not blocked by a CAST operator. Fix for ticket https://www.sqlite.org/src/info/b148fa61059fb6c2. check-in: 9f887f15e5 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

119
120
121
122
123
124
125
126

127
128
129
130
131
132
133
...
138
139
140
141
142
143
144
145


146
147
148
149
150
151
152
RecursiveBubbleDiagram alter-table-stmt
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table,
to rename a column within a table, or to add a new column to an existing table.

<tcl>hd_fragment altertabrename {ALTER TABLE RENAME} {rename table}</tcl>

<h3>ALTER TABLE RENAME</h3>

<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.
................................................................................
<b>Compatibility Note:</b>
The behavior of ALTER TABLE when renaming a table was enhanced
in versions 3.25.0 ([dateof:3.25.0]) and 3.26.0 ([dateof:3.26.0])
in order to carry the rename operation forward into triggers and
views that reference the renamed table.  This is considered an
improvement. Applications that depend on the older (and
arguably buggy) behavior can use the
[PRAGMA legacy_alter_table=ON] statement to make ALTER TABLE RENAME


behave as it did prior to version 3.25.0.
</blockquote>

<p>
Beginning with release 3.25.0 ([dateof:3.25.0]), references to the table
within trigger bodies and view definitions are also renamed.
</p>







|
>







 







|
>
>







119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
...
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
RecursiveBubbleDiagram alter-table-stmt
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table,
to rename a column within a table, or to add a new column to an existing table.

<tcl>hd_fragment altertabrename {ALTER TABLE RENAME} {rename table} \
                 {ALTER TABLE RENAME documentation}</tcl>
<h3>ALTER TABLE RENAME</h3>

<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.
................................................................................
<b>Compatibility Note:</b>
The behavior of ALTER TABLE when renaming a table was enhanced
in versions 3.25.0 ([dateof:3.25.0]) and 3.26.0 ([dateof:3.26.0])
in order to carry the rename operation forward into triggers and
views that reference the renamed table.  This is considered an
improvement. Applications that depend on the older (and
arguably buggy) behavior can use the
[PRAGMA legacy_alter_table=ON] statement or the
[SQLITE_DBCONFIG_LEGACY_ALTER_TABLE] configuration parameter
on [sqlite3_db_config()] interface to make ALTER TABLE RENAME
behave as it did prior to version 3.25.0.
</blockquote>

<p>
Beginning with release 3.25.0 ([dateof:3.25.0]), references to the table
within trigger bodies and view definitions are also renamed.
</p>

Changes to pages/pragma.in.

816
817
818
819
820
821
822



823
824
825
826
827
828
829
    contain code that expect the incomplete behavior
    of [ALTER TABLE RENAME] found in older versions of SQLite.
    New applications should leave this flag turned off.
    <p>For compatibility with older [virtual table] implementations,
    this flag is turned on temporarily while the [sqlite3_module.xRename]
    method is being run.  The value of this flag is restore after the 
    [sqlite3_module.xRename] method finishes.



}

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







>
>
>







816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
    contain code that expect the incomplete behavior
    of [ALTER TABLE RENAME] found in older versions of SQLite.
    New applications should leave this flag turned off.
    <p>For compatibility with older [virtual table] implementations,
    this flag is turned on temporarily while the [sqlite3_module.xRename]
    method is being run.  The value of this flag is restore after the 
    [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

Changes to pages/quirks.in.

232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248

249
250
251

252
253
254
255
256
257
258
259


260
261
262


263
264

















265
266
267
268
269
270
271
[http://site.icu-project.org/|International Components for Unicode]
library.

<tcl>hd_fragment dblquote {double-quoted string literal}</tcl>
<h1>Double-quoted String Literals Are Accepted</h1>

<p>
The SQL standard says that one should use double-quotes around identifiers
and single-quotes around string literals.
<ul>
<li> <tt>"this is a legal SQL column name"</tt>
<li> <tt>'this is an SQL string literal'</tt>
</ul>
<p>
SQLite accepts both of the above.  But, in an effort to be compatible
with MySQL 3.x (which was very popular when SQLite was first being
designed) SQLite will also use content contained in double-quotes as a s

tring literal if the content does not match any valid identifier.
<p>
An unfortunate side-effect of this is that a misspelled double-quoted

identifier will be interpreted as a string literal, rather than generating
an error.
Another problem is that this behavior allows developers who are new to
the SQL language to continue using double-quoted string literals when they
really need to learn to use the correct single-quoted string literal form.
<p>
In hindsight, we should not have tried to make SQLite accept MySQL 3.x
syntax, and should have never allowed double-quoted string literals.


However, we continue to support that capability to avoid breaking legacy
applications.
<p>


Update: As of SQLite 3.27.0 ([dateof:3.27.0]) the use of a double-quoted
string literal causes a warning message to be sent to the [error log].


















<h1>Keywords Can Often Be Used As Identifiers</h1>

<p>
The SQL language is rich in keywords.
Most SQL implementations do not allow keywords to be used as identifiers
(the names of table or columns) unless they are enclosed in double-quotes.







|
|






|
|
>
|

<
>


|
|




>
>
|
<

>
>
|

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251

252
253
254
255
256
257
258
259
260
261
262
263

264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
[http://site.icu-project.org/|International Components for Unicode]
library.

<tcl>hd_fragment dblquote {double-quoted string literal}</tcl>
<h1>Double-quoted String Literals Are Accepted</h1>

<p>
The SQL standard requires double-quotes around identifiers
and single-quotes around string literals.  For example:
<ul>
<li> <tt>"this is a legal SQL column name"</tt>
<li> <tt>'this is an SQL string literal'</tt>
</ul>
<p>
SQLite accepts both of the above.  But, in an effort to be compatible
with MySQL 3.x (which was probably the most widely used RDBMS
when SQLite was first being designed) SQLite will also use
a double-quotes string as
string literal if it does not match any valid identifier.
<p>

This misfeature means that a misspelled double-quoted
identifier will be interpreted as a string literal, rather than generating
an error.
It also lures developers who are new to the SQL language into
bad habit of using double-quoted string literals when they
really need to learn to use the correct single-quoted string literal form.
<p>
In hindsight, we should not have tried to make SQLite accept MySQL 3.x
syntax, and should have never allowed double-quoted string literals.
However, there are countless applications that make use of
double-quoted string literals and so we continue to support
that capability to avoid breaking legacy.

<p>
Updates:
<ul>
<li><p> As of SQLite 3.27.0 ([dateof:3.27.0]) the use of a double-quoted
string literal causes a warning message to be sent to the [error log].
<li><p> As of SQLite 3.29.0 ([dateof:3.29.0]) the use of double-quoted
string literals inside of DDL statements ([CREATE TABLE], [CREATE INDEX],
and so forth) is disallowed and will cause a syntax error.  Double quoted
strings needed to be deactivated in DDL statements as they were causing
problems for [ALTER TABLE].
(See the ticket at [https://www.sqlite.org/src/info/9b78184be266f] for
details.)
If needed for compatibility, the older behavior can be restored by 
disabling the [SQLITE_DBCONFIG_NO_DQS_SCHEMA] option on the
[sqlite3_db_config()] interface.
<li><p> The [SQLITE_DBCONFIG_NO_DQS] option the [sqlite3_db_config()]
interface is available as of SQLite 3.29.0 ([dateof:3.29.0]) and can be
used to disable double-quoted string literals in all contexts.
This setting is currently off by default, but might default on in future
releases of SQLite.  Developers are encouraged to turn this setting on
now, in preparation for the future when it might be activated by default.
</ul>

<h1>Keywords Can Often Be Used As Identifiers</h1>

<p>
The SQL language is rich in keywords.
Most SQL implementations do not allow keywords to be used as identifiers
(the names of table or columns) unless they are enclosed in double-quotes.