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 Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

51
52
53
54
55
56
57



58
59
60
61
62
63
64
..
68
69
70
71
72
73
74



75
76
77
78
79
80
81
82
83
84



85
86
87
88
89
90
91
<li>Add support for [partial indexes]</li>
<li>Cut-over to the [next generation query planner] for faster and better query plans.
<li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    rows generated by each loop in a join.
<li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.
<li>Added the [SQLITE_STMTSTATUS_VM_STEP] option to [sqlite3_stmt_status()].
<li>Added the [cache_spill pragma].



<li>Added the "percentile()" function as a [loadable extension] in the ext/misc
    subdirectory of the source tree.
<li>Added the [SQLITE_ALLOW_URI_AUTHORITY] compile-time option.
<li>Add the [sqlite3_cancel_auto_extension(X)] interface.
<li>A running SELECT statement that lacks a FROM clause (or any other statement that
    never reads or writes from any database file) will not prevent a read
    transaction from closing.
................................................................................
    the query planner uses an automatic index.
<li>Added the [SQLITE_FTS3_MAX_EXPR_DEPTH] compile-time option.
<li>Added an optional 5th parameter defining the collating sequence to the 
    next_char() extension SQL function.
<li>The [SQLITE_BUSY_SNAPSHOT] extended error code is returned in WAL mode when
    a read transaction cannot be upgraded to a write transaction because the read is
    on an older snapshot.



<li>Allow read transactions to be freely opened and closed by SQL statements run 
    from within the implementation of [application-defined SQL functions] if the
    function is called by a SELECT statement that does not access any database table.
<li>Disable the use of posix_fallocate() on all (unix) systems unless the
    HAVE_POSIX_FALLOCATE compile-time option is used.
<li>Update the ".import" command in the [command-line shell] to support multi-line
    fields and correct RFC-4180 quoting and to issue warning and/or error messages
    if the input text is not strictly RFC-4180 compliant.
<li>Bug fix: In the [unicode61] tokenizer of [FTS4], treat all private code points
    as identifier symbols.



<li>Bug fixes: Multiple problems in the legacy query optimizer were fixed by the 
    move to [NGQP].
}

chng {2013-05-20 (3.7.17)} {
<li>Add support for [memory-mapped I/O].
<li>Add the [sqlite3_strglob()] convenience interface.







>
>
>







 







>
>
>










>
>
>







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

chng {2013-05-20 (3.7.17)} {
<li>Add support for [memory-mapped I/O].
<li>Add the [sqlite3_strglob()] convenience interface.

Changes to pages/foreignkeys.in.

503
504
505
506
507
508
509



510
511
512
513
514
515
516
<codeblock>
  NOT DEFERRABLE INITIALLY DEFERRED            <i>-- An immediate foreign key constraint</i>
  NOT DEFERRABLE INITIALLY IMMEDIATE           <i>-- An immediate foreign key constraint</i>
  NOT DEFERRABLE                               <i>-- An immediate foreign key constraint</i>
  DEFERRABLE INITIALLY IMMEDIATE               <i>-- An immediate foreign key constraint</i>
  DEFERRABLE                                   <i>-- An immediate foreign key constraint</i>
</codeblock>)^




  <p>
    ^(The following example illustrates the effect of using a deferred foreign
    key constraint.

<codeblock>
  <i>-- Database schema. Both tables are initially empty. </i>







>
>
>







503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
<codeblock>
  NOT DEFERRABLE INITIALLY DEFERRED            <i>-- An immediate foreign key constraint</i>
  NOT DEFERRABLE INITIALLY IMMEDIATE           <i>-- An immediate foreign key constraint</i>
  NOT DEFERRABLE                               <i>-- An immediate foreign key constraint</i>
  DEFERRABLE INITIALLY IMMEDIATE               <i>-- An immediate foreign key constraint</i>
  DEFERRABLE                                   <i>-- An immediate foreign key constraint</i>
</codeblock>)^

  <p>The [defer_foreign_keys pragma] can be used to temporarily change all foreign
    key constraints to deferred regardless of how they are declared.

  <p>
    ^(The following example illustrates the effect of using a deferred foreign
    key constraint.

<codeblock>
  <i>-- Database schema. Both tables are initially empty. </i>

Changes to pages/pragma.in.

309
310
311
312
313
314
315
















316
317
318
319
320
321
322
...
744
745
746
747
748
749
750








751
752
753
754
755
756
757
    DELETE is run against a view using an [INSTEAD OF trigger],
    the count_changes pragma reports the number of rows in the view
    that fired the trigger, whereas [sqlite3_changes()] and
    [sqlite3_total_changes()] do not.

    DISCLAIMER
}

















LegacyPragma default_cache_size {
    ^(<b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>

    <p>This pragma queries or sets the suggested maximum number of pages
    of disk cache that will be allocated per open database file.)^
................................................................................
       <br>PRAGMA max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.)^
    ^Both forms of the pragma return the maximum page count.  ^The second
    form attempts to modify the maximum page count.  ^The maximum page
    count cannot be reduced below the current database size.
    </p>
}









Pragma read_uncommitted {
    <p>^(<b>PRAGMA read_uncommitted;
       <br>PRAGMA read_uncommitted = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear READ UNCOMMITTED isolation.)^ ^The default isolation
    level for SQLite is SERIALIZABLE.  ^Any process or thread can select
    READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except







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







 







>
>
>
>
>
>
>
>







309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
...
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
    DELETE is run against a view using an [INSTEAD OF trigger],
    the count_changes pragma reports the number of rows in the view
    that fired the trigger, whereas [sqlite3_changes()] and
    [sqlite3_total_changes()] do not.

    DISCLAIMER
}

Pragma defer_foreign_keys {
    <p><b>PRAGMA defer_foreign_keys
       <br>PRAGMA defer_foreign_keys = </b><i>boolean</i><b>;</b></p>
    <p>^When the defer_foreign_keys [PRAGMA] is on,
    enforcement of all [foreign key constraints] is delayed until the
    outermost transaction is committed.  ^The defer_foreign_keys pragma
    defaults to OFF so that foreign key constraints are only deferred if
    they are created as "DEFERRABLE INITIALLY DEFERRED".  This pragma is
    only meaningful if foreign key constraints are enabled, of course.</p>

    <p>The [sqlite3_db_status](db,[SQLITE_DBSTATUS_DEFERRED_FKS],...)
    C-language interface can be used during a transaction to determine 
    if there are deferred and unresolved foreign key constraints.</p>
}


LegacyPragma default_cache_size {
    ^(<b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>

    <p>This pragma queries or sets the suggested maximum number of pages
    of disk cache that will be allocated per open database file.)^
................................................................................
       <br>PRAGMA max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.)^
    ^Both forms of the pragma return the maximum page count.  ^The second
    form attempts to modify the maximum page count.  ^The maximum page
    count cannot be reduced below the current database size.
    </p>
}

Pragma query_only {
    <p><b>PRAGMA query_only;
       <br>PRAGMA query_only = </b><i>boolean</i><b>;</b></p>

    <p>The query_only pragma prevents all changes to database files when
    enabled.</p>
}   

Pragma read_uncommitted {
    <p>^(<b>PRAGMA read_uncommitted;
       <br>PRAGMA read_uncommitted = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear READ UNCOMMITTED isolation.)^ ^The default isolation
    level for SQLite is SERIALIZABLE.  ^Any process or thread can select
    READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except