Documentation Source Text

Check-in [8e16497ef1]
Login

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

Overview
Comment:Add documentation for the query_only and defer_foreign_keys pragmas. Update the change log for the 3.8.0 release.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:8e16497ef14bf157c1f1622a89516553963057d2
User & Date: drh 2013-08-23 01:57:51
Context
2013-08-23
16:16
Fix documentation typos. check-in: ce2a4ec1e7 user: drh tags: trunk
01:57
Add documentation for the query_only and defer_foreign_keys pragmas. Update the change log for the 3.8.0 release. check-in: 8e16497ef1 user: drh tags: trunk
2013-08-20
17:06
Fix typos in the cache_spill pragma documentation. check-in: 4bfef7edb7 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to pages/changes.in.

    51     51   <li>Add support for [partial indexes]</li>
    52     52   <li>Cut-over to the [next generation query planner] for faster and better query plans.
    53     53   <li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    54     54       rows generated by each loop in a join.
    55     55   <li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.
    56     56   <li>Added the [SQLITE_STMTSTATUS_VM_STEP] option to [sqlite3_stmt_status()].
    57     57   <li>Added the [cache_spill pragma].
           58  +<li>Added the [query_only pragma].
           59  +<li>Added the [defer_foreign_keys pragma] and the
           60  +    [sqlite3_db_status](db, [SQLITE_DBSTATUS_DEFERRED_FKS],...) C-language interface.
    58     61   <li>Added the "percentile()" function as a [loadable extension] in the ext/misc
    59     62       subdirectory of the source tree.
    60     63   <li>Added the [SQLITE_ALLOW_URI_AUTHORITY] compile-time option.
    61     64   <li>Add the [sqlite3_cancel_auto_extension(X)] interface.
    62     65   <li>A running SELECT statement that lacks a FROM clause (or any other statement that
    63     66       never reads or writes from any database file) will not prevent a read
    64     67       transaction from closing.
................................................................................
    68     71       the query planner uses an automatic index.
    69     72   <li>Added the [SQLITE_FTS3_MAX_EXPR_DEPTH] compile-time option.
    70     73   <li>Added an optional 5th parameter defining the collating sequence to the 
    71     74       next_char() extension SQL function.
    72     75   <li>The [SQLITE_BUSY_SNAPSHOT] extended error code is returned in WAL mode when
    73     76       a read transaction cannot be upgraded to a write transaction because the read is
    74     77       on an older snapshot.
           78  +<li>Enhancements to the sqlite3_analyzer utility program to provide size
           79  +    information separately for each individual index of a table, in addition to
           80  +    the aggregate size.
    75     81   <li>Allow read transactions to be freely opened and closed by SQL statements run 
    76     82       from within the implementation of [application-defined SQL functions] if the
    77     83       function is called by a SELECT statement that does not access any database table.
    78     84   <li>Disable the use of posix_fallocate() on all (unix) systems unless the
    79     85       HAVE_POSIX_FALLOCATE compile-time option is used.
    80     86   <li>Update the ".import" command in the [command-line shell] to support multi-line
    81     87       fields and correct RFC-4180 quoting and to issue warning and/or error messages
    82     88       if the input text is not strictly RFC-4180 compliant.
    83     89   <li>Bug fix: In the [unicode61] tokenizer of [FTS4], treat all private code points
    84     90       as identifier symbols.
           91  +<li>Bug fix: Bare identifiers in ORDER BY clauses bind more tightly to output column
           92  +    names, but indentifiers in expressions bind more tightly to input column names.
           93  +    Indentifers in GROUP BY clauses always prefer output column names, however.
    85     94   <li>Bug fixes: Multiple problems in the legacy query optimizer were fixed by the 
    86     95       move to [NGQP].
    87     96   }
    88     97   
    89     98   chng {2013-05-20 (3.7.17)} {
    90     99   <li>Add support for [memory-mapped I/O].
    91    100   <li>Add the [sqlite3_strglob()] convenience interface.

Changes to pages/foreignkeys.in.

   503    503   <codeblock>
   504    504     NOT DEFERRABLE INITIALLY DEFERRED            <i>-- An immediate foreign key constraint</i>
   505    505     NOT DEFERRABLE INITIALLY IMMEDIATE           <i>-- An immediate foreign key constraint</i>
   506    506     NOT DEFERRABLE                               <i>-- An immediate foreign key constraint</i>
   507    507     DEFERRABLE INITIALLY IMMEDIATE               <i>-- An immediate foreign key constraint</i>
   508    508     DEFERRABLE                                   <i>-- An immediate foreign key constraint</i>
   509    509   </codeblock>)^
          510  +
          511  +  <p>The [defer_foreign_keys pragma] can be used to temporarily change all foreign
          512  +    key constraints to deferred regardless of how they are declared.
   510    513   
   511    514     <p>
   512    515       ^(The following example illustrates the effect of using a deferred foreign
   513    516       key constraint.
   514    517   
   515    518   <codeblock>
   516    519     <i>-- Database schema. Both tables are initially empty. </i>

Changes to pages/pragma.in.

   309    309       DELETE is run against a view using an [INSTEAD OF trigger],
   310    310       the count_changes pragma reports the number of rows in the view
   311    311       that fired the trigger, whereas [sqlite3_changes()] and
   312    312       [sqlite3_total_changes()] do not.
   313    313   
   314    314       DISCLAIMER
   315    315   }
          316  +
          317  +Pragma defer_foreign_keys {
          318  +    <p><b>PRAGMA defer_foreign_keys
          319  +       <br>PRAGMA defer_foreign_keys = </b><i>boolean</i><b>;</b></p>
          320  +    <p>^When the defer_foreign_keys [PRAGMA] is on,
          321  +    enforcement of all [foreign key constraints] is delayed until the
          322  +    outermost transaction is committed.  ^The defer_foreign_keys pragma
          323  +    defaults to OFF so that foreign key constraints are only deferred if
          324  +    they are created as "DEFERRABLE INITIALLY DEFERRED".  This pragma is
          325  +    only meaningful if foreign key constraints are enabled, of course.</p>
          326  +
          327  +    <p>The [sqlite3_db_status](db,[SQLITE_DBSTATUS_DEFERRED_FKS],...)
          328  +    C-language interface can be used during a transaction to determine 
          329  +    if there are deferred and unresolved foreign key constraints.</p>
          330  +}
          331  +
   316    332   
   317    333   LegacyPragma default_cache_size {
   318    334       ^(<b>PRAGMA default_cache_size;
   319    335          <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
   320    336   
   321    337       <p>This pragma queries or sets the suggested maximum number of pages
   322    338       of disk cache that will be allocated per open database file.)^
................................................................................
   744    760          <br>PRAGMA max_page_count = </b><i>N</i><b>;</b></p>
   745    761       <p>Query or set the maximum number of pages in the database file.)^
   746    762       ^Both forms of the pragma return the maximum page count.  ^The second
   747    763       form attempts to modify the maximum page count.  ^The maximum page
   748    764       count cannot be reduced below the current database size.
   749    765       </p>
   750    766   }
          767  +
          768  +Pragma query_only {
          769  +    <p><b>PRAGMA query_only;
          770  +       <br>PRAGMA query_only = </b><i>boolean</i><b>;</b></p>
          771  +
          772  +    <p>The query_only pragma prevents all changes to database files when
          773  +    enabled.</p>
          774  +}   
   751    775   
   752    776   Pragma read_uncommitted {
   753    777       <p>^(<b>PRAGMA read_uncommitted;
   754    778          <br>PRAGMA read_uncommitted = </b><i>boolean</i><b>;</b></p>
   755    779       <p>Query, set, or clear READ UNCOMMITTED isolation.)^ ^The default isolation
   756    780       level for SQLite is SERIALIZABLE.  ^Any process or thread can select
   757    781       READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except