Documentation Source Text

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

Changes to pages/changes.in.

    22     22   }
    23     23   
    24     24   chng {2019-12-31 (3.31.0)} {
    25     25   <li>Add support for [generated columns].
    26     26   <li>Faster response to [sqlite3_interrupt()].
    27     27   <li>Added the [https://sqlite.org/src/file/ext/misc/uuid.c|uuid.c] extension module
    28     28       implementing functions for processing RFC-4122 UUIDs.
           29  +<li>The [legacy_file_format pragma] is deactivated.  It is now a no-op.  In its place,
           30  +    the [SQLITE_DBCONFIG_LEGACY_FILE_FORMAT] option to [sqlite3_db_config()] is
           31  +    provided.  The legacy_file_format pragma is deactivated because (1) it is
           32  +    rarely useful and (2) it is incompatible with [VACUUM] in schemas that have
           33  +    tables with both generated columns and descending indexes.
           34  +    Ticket [https://www.sqlite.org/src/info/6484e6ce678fffab|6484e6ce678fffab]
    29     35   }
    30     36   
    31     37   chng {2019-10-11 (3.30.1)} {
    32     38   <li> Fix a bug in the [query flattener] that might cause a segfault
    33     39   for nested queries that use the new 
    34     40   [FILTER clause on aggregate functions].
    35     41   Ticket [https://www.sqlite.org/src/info/1079ad19993d13fa|1079ad19993d13fa]

Changes to pages/gencol.in.

    60     60   <p>From the point of view of SQL, STORED and VIRTUAL columns are almost
    61     61   exactly the same.  Queries against either class of generated column
    62     62   produce the same results.  The only functional difference is that
    63     63   one cannot add new STORED columns using the
    64     64   [ALTER TABLE ADD COLUMN] command.  Only VIRTUAL columns can be added
    65     65   using ALTER TABLE.
    66     66   
    67         -<h2>Restrictions And Limitations</h2>
           67  +<h2>Capabilities</h2>
           68  +
           69  +<ol>
           70  +<li><p>
           71  +^Generated columns can have a datatype.  ^SQLite attempts to transform
           72  +the result of the generating expression into that datatype using the
           73  +same [affinity] rules as for ordinary columns.
           74  +
           75  +<li><p>
           76  +^Generated columns may have NOT NULL, CHECK, and UNIQUE constraints,
           77  +and foreign key constraints, just like ordinary columns.
           78  +
           79  +<li><p>
           80  +^Generated columns can participate in indexes, just like ordinary
           81  +columns.
           82  +
           83  +<li><p>
           84  +^The expression of a generated column can refer to any of the
           85  +other declared columns in the table, including other generated columns,
           86  +as long as the expression does not directly or indirectly refer back
           87  +to itself.
           88  +
           89  +<li><p>
           90  +^Generated columns can occur anywhere in the table definition.  ^Generated
           91  +columns can be interspersed among ordinary columns.  ^It not necessary
           92  +to put generated columns at the end of the list of columns in the
           93  +table definition, as is shown in the examples above.
           94  +</ol>
           95  +
           96  +
           97  +<h2>Limitations</h2>
    68     98   
    69     99   <ol>
    70    100   <li><p>
    71         -Generated columns may not have a [default value] (they may not use the
          101  +^Generated columns may not have a [default value] (they may not use the
    72    102   "DEFAULT" clause).  The value of a generated columns is always the value
    73    103   specified by the expression that follows the "AS" keyword.
    74    104   
    75    105   <li><p>
    76         -Generated columns may not be used as part of the [PRIMARY KEY].
          106  +^Generated columns may not be used as part of the [PRIMARY KEY].
    77    107   (Future versions of SQLite might relax this constraint for STORED columns.)
    78    108   
    79    109   <li><p>
    80         -The expression of a generated column has the same restrictions as the
          110  +^The expression of a generated column has the same restrictions as the
    81    111   expression of a [CHECK constraint]: The expression may only reference
    82    112   constant literals and columns within the same row, and may only use
    83         -scalar [deterministic functions].  The expression may not use subqueries,
          113  +scalar [deterministic functions].  ^The expression may not use subqueries,
    84    114   aggregate functions, window functions, or table-valued functions.
    85    115   
    86    116   <li><p>
    87         -The expression of a generated column may refer to other generated columns
          117  +^The expression of a generated column may refer to other generated columns
    88    118   in the same row, but no generated column can depend upon itself, either
    89         -directly or indirectly.
          119  +directly or indirectly.  ^Nor may a generated column depend on the
          120  +[ROWID].
          121  +
          122  +<li><p>
          123  +^Every table must have at least one non-generated column.
    90    124   
    91    125   <li><p>
    92         -Every table must have at least one non-generated column.
          126  +^It is not possible to [ALTER TABLE ADD COLUMN] a STORED column.
          127  +^One can add a VIRTUAL column, however.
    93    128   </ol>
    94    129   
    95    130   <h1>Compatibility</h1>
    96    131   
    97    132   <p>Generated column support was added with SQLite version 3.31.0
    98    133   ([dateof:3.31.0]).  If an earlier version of SQLite attempts to read
    99    134   a database file that contains a generated column in its schema, then
   100    135   that earlier version will perceive the generated column syntax as an
   101    136   error and will report that the database schema is corrupt.
          137  +
          138  +<p>To clarify:  SQLite version 3.31.0 can read and write any database
          139  +created by any prior version of SQLite going back to 
          140  +SQLite 3.0.0 ([dateof:3.0.0]).  And, earlier versions of SQLite,
          141  +prior to 3.31.0, can read and write databases created by SQLite
          142  +version 3.31.0 and later as long
          143  +as the database schema does not contain features, such as
          144  +generated columns, that are not understood by the earlier version.
          145  +Problems only arise if you create a new database that contains
          146  +generated columns, using SQLite version 3.31.0 or later, and then
          147  +try to read or write that database file using an earlier version of
          148  +SQLite that does not understand generated columns.

Changes to pages/pragma.in.

   827    827       [sqlite3_module.xRename] method finishes.
   828    828       <p>The legacy alter table behavior can also be toggled on and off
   829    829       using the [SQLITE_DBCONFIG_LEGACY_ALTER_TABLE] option to the
   830    830       [sqlite3_db_config()] interface.
   831    831   }
   832    832   
   833    833   Pragma legacy_file_format {
   834         -   <p>^(<b>PRAGMA legacy_file_format;
   835         -       <br>PRAGMA legacy_file_format = <i>boolean</i></b></p>
   836         -    <p>This pragma sets or queries the value of the legacy_file_format
   837         -    flag.)^  ^(When this flag is on, new SQLite databases are created in
   838         -    a file format that is readable and writable by all versions of
   839         -    SQLite going back to 3.0.0.)^  ^(When the flag is off, new databases
   840         -    are created using the latest file format which might not be
   841         -    readable or writable by versions of SQLite prior to 3.3.0.)^</p>
   842         -
   843         -    <p>^When the legacy_file_format pragma is issued with no argument,
   844         -    it returns the setting of the flag.  ^This pragma does <u>not</u> tell
   845         -    which file format the current database is using; it tells what format
   846         -    will be used by any newly created databases.</p>
   847         -
   848         -    <p>^The legacy_file_format pragma is initialized to OFF when an existing
   849         -    database in the newer file format is first opened.</p>
   850         -
   851         -    <p>^The default file format is set by the
   852         -    [SQLITE_DEFAULT_FILE_FORMAT] compile-time option.</p>
          834  +   <p>^(<b>PRAGMA legacy_file_format;</b>
          835  +    <p>This pragma no longer functions.  It has become a no-op.)^
          836  +    The capabilities formerly provided by PRAGMA legacy_file_format
          837  +    are now available using the [SQLITE_DBCONFIG_LEGACY_FILE_FORMAT]
          838  +    option to the [sqlite3_db_config()] C-language interface.
          839  +    <p>
   853    840   }
   854    841   
   855    842   Pragma {locking_mode {exclusive locking mode} {EXCLUSIVE locking mode}} {
   856    843       <p>^(<b>PRAGMA DB.locking_mode;
   857    844       <br>PRAGMA DB.locking_mode
   858    845                   = <i>NORMAL | EXCLUSIVE</i></b>)^</p>
   859    846       <p>^This pragma sets or queries the database connection locking-mode.