Documentation Source Text

  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2019-07-00 (3.29.0)} {
<li> The [double-quoted string literal] misfeature is deactivated by default
     for DDL statements.  It can be reactivated if needed, to support legacy
     applications, using the [SQLITE_DBCONFIG_DQS_DDL] action of the
     [sqlite3_db_config()] interface.
<li> Added the [SQLITE_DBCONFIG_DQS_DML] action to [sqlite3_db_config()] that
     will disable the [double-quoted string literal] misfeature for DML
     statements.
<li> Improved optimization of AND and OR operators when one or the other
     operand is a constant.
<li> Added the "[https://sqlite.org/src/file/ext/misc/dbdata.c|sqlite_dbdata]"
     virtual table for extracting raw low-level content from an SQLite database,
     even a database that is corrupt.
<li> Enhancements to the [CLI]:
<ol type="a">
<li> Add the ".recover" command which tries to recover as much content
     as possible from a corrupt database file.
<li> Add the ".filectrl" command useful for testing.
<li> Add the long-standing ".testctrl" command to the ".help" menu.
<li> Added the ".dbconfig" command
</ol>
}

chng {2019-04-16 (3.28.0)} {
<li> Enhanced [window functions]:
<ol type="a">
<li> Add support the [EXCLUDE clause].

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.
^If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed.

<blockquote style='background-color: #ffd0d0;'>
<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>

    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

[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 one of the most widely used RDBMSes
when SQLite was first being designed) SQLite will also interpret
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_DQS_DDL] option on the
[sqlite3_db_config()] interface.
<li><p> The [SQLITE_DBCONFIG_DQS_DML] 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 for DML statements.
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.