Documentation Source Text

Check-in [ab87f9f72b]
Login

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

Overview
Comment:Add notes about the new sqlite3_db_config() interfaces. Enhanced discussion of the double-quoted string literal misfeature.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: ab87f9f72bc40a338a06275c61a652350d02a1ce3e451c4f73f53f537e8ce10c
User & Date: drh 2019-06-17 14:14:53
Context
2019-07-02
12:47
Add documentation for the SQLITE_DQS compile-time option. Adjust the documentation so that the default is now -DSQLITE_DQS=3. check-in: e2807b06c2 user: drh tags: trunk
2019-06-17
14:14
Add notes about the new sqlite3_db_config() interfaces. Enhanced discussion of the double-quoted string literal misfeature. check-in: ab87f9f72b user: drh tags: trunk
13:56
Track the revised DQS interface. Closed-Leaf check-in: 09cea3924b 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

    18     18     global nChng aChng xrefChng
    19     19     set aChng($nChng) [list $date $desc $options]
    20     20     set xrefChng($date) $nChng
    21     21     incr nChng
    22     22   }
    23     23   
    24     24   chng {2019-07-00 (3.29.0)} {
           25  +<li> The [double-quoted string literal] misfeature is deactivated by default
           26  +     for DDL statements.  It can be reactivated if needed, to support legacy
           27  +     applications, using the [SQLITE_DBCONFIG_DQS_DDL] action of the
           28  +     [sqlite3_db_config()] interface.
           29  +<li> Added the [SQLITE_DBCONFIG_DQS_DML] action to [sqlite3_db_config()] that
           30  +     will disable the [double-quoted string literal] misfeature for DML
           31  +     statements.
    25     32   <li> Improved optimization of AND and OR operators when one or the other
    26     33        operand is a constant.
    27     34   <li> Added the "[https://sqlite.org/src/file/ext/misc/dbdata.c|sqlite_dbdata]"
    28     35        virtual table for extracting raw low-level content from an SQLite database,
    29     36        even a database that is corrupt.
    30     37   <li> Enhancements to the [CLI]:
    31     38   <ol type="a">
    32     39   <li> Add the ".recover" command which tries to recover as much content
    33     40        as possible from a corrupt database file.
    34     41   <li> Add the ".filectrl" command useful for testing.
    35     42   <li> Add the long-standing ".testctrl" command to the ".help" menu.
           43  +<li> Added the ".dbconfig" command
    36     44   </ol>
    37     45   }
    38     46   
    39     47   chng {2019-04-16 (3.28.0)} {
    40     48   <li> Enhanced [window functions]:
    41     49   <ol type="a">
    42     50   <li> Add support the [EXCLUDE clause].

Changes to pages/lang.in.

   119    119   RecursiveBubbleDiagram alter-table-stmt
   120    120   </tcl>
   121    121   
   122    122   <p>SQLite supports a limited subset of ALTER TABLE.
   123    123   The ALTER TABLE command in SQLite allows the user to rename a table,
   124    124   to rename a column within a table, or to add a new column to an existing table.
   125    125   
   126         -<tcl>hd_fragment altertabrename {ALTER TABLE RENAME} {rename table}</tcl>
          126  +<tcl>hd_fragment altertabrename {ALTER TABLE RENAME} {rename table} \
          127  +                 {ALTER TABLE RENAME documentation}</tcl>
   127    128   <h3>ALTER TABLE RENAME</h3>
   128    129   
   129    130   <p> ^(The RENAME TO syntax changes the name of <yyterm>table-name</yyterm>
   130    131   to <yyterm>new-table-name</yyterm>.)^
   131    132   This command 
   132    133   cannot be used to move a table between attached databases, only to rename 
   133    134   a table within the same database.
................................................................................
   138    139   <b>Compatibility Note:</b>
   139    140   The behavior of ALTER TABLE when renaming a table was enhanced
   140    141   in versions 3.25.0 ([dateof:3.25.0]) and 3.26.0 ([dateof:3.26.0])
   141    142   in order to carry the rename operation forward into triggers and
   142    143   views that reference the renamed table.  This is considered an
   143    144   improvement. Applications that depend on the older (and
   144    145   arguably buggy) behavior can use the
   145         -[PRAGMA legacy_alter_table=ON] statement to make ALTER TABLE RENAME
          146  +[PRAGMA legacy_alter_table=ON] statement or the
          147  +[SQLITE_DBCONFIG_LEGACY_ALTER_TABLE] configuration parameter
          148  +on [sqlite3_db_config()] interface to make ALTER TABLE RENAME
   146    149   behave as it did prior to version 3.25.0.
   147    150   </blockquote>
   148    151   
   149    152   <p>
   150    153   Beginning with release 3.25.0 ([dateof:3.25.0]), references to the table
   151    154   within trigger bodies and view definitions are also renamed.
   152    155   </p>

Changes to pages/pragma.in.

   816    816       contain code that expect the incomplete behavior
   817    817       of [ALTER TABLE RENAME] found in older versions of SQLite.
   818    818       New applications should leave this flag turned off.
   819    819       <p>For compatibility with older [virtual table] implementations,
   820    820       this flag is turned on temporarily while the [sqlite3_module.xRename]
   821    821       method is being run.  The value of this flag is restore after the 
   822    822       [sqlite3_module.xRename] method finishes.
          823  +    <p>The legacy alter table behavior can also be toggled on and off
          824  +    using the [SQLITE_DBCONFIG_LEGACY_ALTER_TABLE] option to the
          825  +    [sqlite3_db_config()] interface.
   823    826   }
   824    827   
   825    828   Pragma legacy_file_format {
   826    829      <p>^(<b>PRAGMA legacy_file_format;
   827    830          <br>PRAGMA legacy_file_format = <i>boolean</i></b></p>
   828    831       <p>This pragma sets or queries the value of the legacy_file_format
   829    832       flag.)^  ^(When this flag is on, new SQLite databases are created in

Changes to pages/quirks.in.

   232    232   [http://site.icu-project.org/|International Components for Unicode]
   233    233   library.
   234    234   
   235    235   <tcl>hd_fragment dblquote {double-quoted string literal}</tcl>
   236    236   <h1>Double-quoted String Literals Are Accepted</h1>
   237    237   
   238    238   <p>
   239         -The SQL standard says that one should use double-quotes around identifiers
   240         -and single-quotes around string literals.
          239  +The SQL standard requires double-quotes around identifiers
          240  +and single-quotes around string literals.  For example:
   241    241   <ul>
   242    242   <li> <tt>"this is a legal SQL column name"</tt>
   243    243   <li> <tt>'this is an SQL string literal'</tt>
   244    244   </ul>
   245    245   <p>
   246    246   SQLite accepts both of the above.  But, in an effort to be compatible
   247         -with MySQL 3.x (which was very popular when SQLite was first being
   248         -designed) SQLite will also use content contained in double-quotes as a s
   249         -tring literal if the content does not match any valid identifier.
          247  +with MySQL 3.x (which was one of the most widely used RDBMSes
          248  +when SQLite was first being designed) SQLite will also interpret
          249  +a double-quotes string as
          250  +string literal if it does not match any valid identifier.
   250    251   <p>
   251         -An unfortunate side-effect of this is that a misspelled double-quoted
          252  +This misfeature means that a misspelled double-quoted
   252    253   identifier will be interpreted as a string literal, rather than generating
   253    254   an error.
   254         -Another problem is that this behavior allows developers who are new to
   255         -the SQL language to continue using double-quoted string literals when they
          255  +It also lures developers who are new to the SQL language into
          256  +bad habit of using double-quoted string literals when they
   256    257   really need to learn to use the correct single-quoted string literal form.
   257    258   <p>
   258    259   In hindsight, we should not have tried to make SQLite accept MySQL 3.x
   259    260   syntax, and should have never allowed double-quoted string literals.
   260         -However, we continue to support that capability to avoid breaking legacy
   261         -applications.
          261  +However, there are countless applications that make use of
          262  +double-quoted string literals and so we continue to support
          263  +that capability to avoid breaking legacy.
   262    264   <p>
   263         -Update: As of SQLite 3.27.0 ([dateof:3.27.0]) the use of a double-quoted
          265  +Updates:
          266  +<ul>
          267  +<li><p> As of SQLite 3.27.0 ([dateof:3.27.0]) the use of a double-quoted
   264    268   string literal causes a warning message to be sent to the [error log].
          269  +<li><p> As of SQLite 3.29.0 ([dateof:3.29.0]) the use of double-quoted
          270  +string literals inside of DDL statements ([CREATE TABLE], [CREATE INDEX],
          271  +and so forth) is disallowed and will cause a syntax error.  Double quoted
          272  +strings needed to be deactivated in DDL statements as they were causing
          273  +problems for [ALTER TABLE].
          274  +(See the ticket at [https://www.sqlite.org/src/info/9b78184be266f] for
          275  +details.)
          276  +If needed for compatibility, the older behavior can be restored by 
          277  +disabling the [SQLITE_DBCONFIG_DQS_DDL] option on the
          278  +[sqlite3_db_config()] interface.
          279  +<li><p> The [SQLITE_DBCONFIG_DQS_DML] option the [sqlite3_db_config()]
          280  +interface is available as of SQLite 3.29.0 ([dateof:3.29.0]) and can be
          281  +used to disable double-quoted string literals for DML statements.
          282  +This setting is currently off by default, but might default on in future
          283  +releases of SQLite.  Developers are encouraged to turn this setting on
          284  +now, in preparation for the future when it might be activated by default.
          285  +</ul>
   265    286   
   266    287   <h1>Keywords Can Often Be Used As Identifiers</h1>
   267    288   
   268    289   <p>
   269    290   The SQL language is rich in keywords.
   270    291   Most SQL implementations do not allow keywords to be used as identifiers
   271    292   (the names of table or columns) unless they are enclosed in double-quotes.