Documentation Source Text

Check-in [140df585c3]
Login

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

Overview
Comment:Add documentation for the SQLITE_DEFAULT_WAL_AUTOCHECKPOINT compile-time option. Other updates to PRAGMA documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 140df585c350a5a193d0d498215b7278b68febe1
User & Date: drh 2010-12-09 18:56:06
Context
2010-12-16
17:19
Fix a detail of the case_sensitive_like pragma documentation to more accurately describe what really happens. check-in: 78d484e889 user: drh tags: trunk
2010-12-09
18:56
Add documentation for the SQLITE_DEFAULT_WAL_AUTOCHECKPOINT compile-time option. Other updates to PRAGMA documentation. check-in: 140df585c3 user: drh tags: trunk
2010-12-08
21:13
Remove an obolete requirement from the cache_size pragma. Un-deprecate the foreign_key_list pragma, since people seem to use it far more than we suspected. check-in: d399230aae user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/compile.in.

    95     95   COMPILE_OPTION {SQLITE_DEFAULT_TEMP_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
    96     96     This macro sets the default size of the page-cache for temporary files
    97     97     created by SQLite to store intermediate results, in pages. It does
    98     98     not affect the page-cache for the temp database, where tables created
    99     99     using [CREATE TABLE | CREATE TEMP TABLE] are stored. The default value
   100    100     is 500.
   101    101   }
          102  +
          103  +COMPILE_OPTION {SQLITE_DEFAULT_WAL_AUTOCHECKPOINT=<i>&lt;pages&gt;</i>} {
          104  +  This macro sets the default page count for the [WAL]
          105  +  [checkpointing | automatic checkpointing] feature.  If unspecified,
          106  +  the default page count is 1000.
          107  +}
   102    108   
   103    109   COMPILE_OPTION {YYSTACKDEPTH=<i>&lt;max_depth&gt;</i>} {
   104    110     This macro sets the maximum depth of the LALR(1) stack used by
   105    111     the SQL parser within SQLite.  The default value is 100.  A typical
   106    112     application will use less than about 20 levels of the stack.
   107    113     Developers whose applications contain SQL statements that 
   108    114     need more than 100 LALR(1) stack entries should seriously

Changes to pages/pragma.in.

   230    230   
   231    231       <p>^If the [fullfsync] flag is set, then the F_FULLFSYNC syncing
   232    232       method is used for all sync operations and the checkpoint_fullfsync
   233    233       setting is irrelevant.</p>
   234    234   }
   235    235   
   236    236   LegacyPragma count_changes {
   237         -    <p>^(<b>PRAGMA count_changes;
          237  +    <p><b>PRAGMA count_changes;
   238    238          <br>PRAGMA count_changes = </b>boolean</i><b>;</b></p>
   239    239   
   240    240       DISCLAIMER
   241    241     
   242         -    <p>Query or change the count-changes flag.)^ ^Normally, when the
          242  +    <p>Query or change the count-changes flag. Normally, when the
   243    243       count-changes flag is not set, [INSERT], [UPDATE] and [DELETE] statements
   244         -    return no data. ^When count-changes is set, each of these commands 
          244  +    return no data. When count-changes is set, each of these commands 
   245    245       returns a single row of data consisting of one integer value - the
   246         -    number of rows inserted, modified or deleted by the command. ^The 
          246  +    number of rows inserted, modified or deleted by the command. The 
   247    247       returned change count does not include any insertions, modifications
   248    248       or deletions performed by triggers, or any changes made automatically
   249    249       by [foreign key actions].</p>
   250    250   
   251    251       <p>Another way to get the row change counts is to use the
   252    252       [sqlite3_changes()] or [sqlite3_total_changes()] interfaces.
   253         -    There is a subtle different, though.  ^When an INSERT, UPDATE, or
          253  +    There is a subtle different, though.  When an INSERT, UPDATE, or
   254    254       DELETE is run against a view using an [INSTEAD OF trigger],
   255    255       the count_changes pragma reports the number of rows in the view
   256    256       that fired the trigger, whereas [sqlite3_changes()] and
   257    257       [sqlite3_total_changes()] do not.
   258    258   }
   259    259   
   260    260   LegacyPragma default_cache_size {
................................................................................
   341    341          release of SQLite.  To minimize future problems, applications should
   342    342          set the foreign key enforcement flag as required by the application
   343    343          and not depend on the default setting.
   344    344   }
   345    345   
   346    346   
   347    347   LegacyPragma full_column_names {
   348         -    <p>^(<b>PRAGMA full_column_names;
          348  +    <p><b>PRAGMA full_column_names;
   349    349          <br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p>
   350    350   
   351    351       DISCLAIMER
   352    352   
   353         -    <p>Query or change the full_column_names flag.)^ ^This flag together 
          353  +    <p>Query or change the full_column_names flag. This flag together 
   354    354       with the [short_column_names] flag determine
   355    355       the way SQLite assigns names to result columns of [SELECT] statements.
   356         -    ^(Result columns are named by applying the following rules in order:
          356  +    Result columns are named by applying the following rules in order:
   357    357       <ol>
   358    358       <li><p>If there is an AS clause on the result, then the name of
   359    359           the column is the right-hand side of the AS clause.</p></li>
   360    360       <li><p>If the result is a general expression, not a just the name of
   361    361           a source table column,
   362    362           then the name of the result is a copy of the expression text.</p></li>
   363    363       <li><p>If the [short_column_names] pragma is ON, then the name of the
................................................................................
   364    364           result is the name of the source table column without the 
   365    365           source table name prefix:  COLUMN.</p></li>
   366    366       <li><p>If both pragmas [short_column_names] and [full_column_names]
   367    367           are OFF then case (2) applies.
   368    368           </p></li>
   369    369       <li><p>The name of the result column is a combination of the source table
   370    370           and source column name:  TABLE.COLUMN</p></li>
   371         -    </ol>)^
          371  +    </ol>
   372    372   }
   373    373   
   374    374   Pragma fullfsync {
   375    375       <p>^(<b>PRAGMA fullfsync
   376    376          <br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p>
   377    377       <p>Query or change the fullfsync flag.)^ ^This flag
   378    378       determines whether or not the F_FULLFSYNC syncing method is used
................................................................................
   379    379       on systems that support it.  ^The default value of the fullfsync flag
   380    380       is off.  Only Mac OS X supports F_FULLFSYNC.</p>
   381    381   
   382    382       <p>See also [checkpoint_fullfsync].</p>
   383    383   }
   384    384   
   385    385   Pragma incremental_vacuum {
   386         -    <p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
   387         -    <p>^The incremental_vacuum pragma causes up to <i>N</i> pages to
   388         -    be removed from the [freelist].  ^The database file is truncated by
          386  +    ^(<p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
          387  +    <p>The incremental_vacuum pragma causes up to <i>N</i> pages to
          388  +    be removed from the [freelist].)^  ^The database file is truncated by
   389    389       the same amount.  ^The incremental_vacuum pragma has no effect if
   390    390       the database is not in
   391    391       <a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode
   392    392       or if there are no pages on the freelist.  ^If there are fewer than
   393    393       <i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or
   394    394       if <i>N</i> is omitted entirely, then the entire freelist is cleared.</p>
   395    395   }
................................................................................
   724    724       ^When multiple database connections share the same cache, changing
   725    725       the secure-delete flag on one database connection changes it for them
   726    726       all.
   727    727       </p>
   728    728   }
   729    729   
   730    730   LegacyPragma short_column_names {
   731         -    <p>^(<b>PRAGMA short_column_names;
          731  +    <p><b>PRAGMA short_column_names;
   732    732          <br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p>
   733    733   
   734    734       DISCLAIMER
   735    735   
   736         -    <p>Query or change the short-column-names flag.)^ ^This flag affects
          736  +    <p>Query or change the short-column-names flag. This flag affects
   737    737       the way SQLite names columns of data returned by [SELECT] statements.
   738    738       See the [full_column_names] pragma for full details.
   739    739       </p>
   740    740   }
   741    741   
   742    742   Pragma synchronous {
   743    743       <p>^(<b>PRAGMA synchronous;
................................................................................
   835    835           <td align="center"><em>any</em></td>
   836    836           <td align="center">memory</td></tr>
   837    837       </table>
   838    838       </blockquote>)^
   839    839   }
   840    840   
   841    841   LegacyPragma temp_store_directory {
   842         -    <p>^(<b>PRAGMA temp_store_directory;
          842  +    <p><b>PRAGMA temp_store_directory;
   843    843          <br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p>
   844    844       <p>Query or change the value of the [sqlite3_temp_directory] global
   845    845       variable, which many operating-system interface backends use to
   846         -    determine where to store [temporary tables] and indices.</p>)^
          846  +    determine where to store [temporary tables] and indices.</p>
   847    847   
   848    848       DISCLAIMER
   849    849   
   850         -    <p>^When the temp_store_directory setting is changed, all existing temporary
          850  +    <p>When the temp_store_directory setting is changed, all existing temporary
   851    851       tables, indices, triggers, and viewers in the database connection that
   852    852       issued the pragma are immediately deleted.  In
   853    853       practice, temp_store_directory should be set immediately after the first
   854    854       database connection for a process is opened.  If the temp_store_directory
   855    855       is changed for one database connection while other database connections
   856    856       are open in the same process, then the behavior is undefined and
   857    857       probably undesirable.</p>
   858    858   
   859    859       <p>Changing the temp_store_directory setting is <u>not</u> threadsafe.
   860    860       Never change the temp_store_directory setting if another thread
   861    861       within the application is running any SQLite interface at the same time.
   862         -    Doing so results in undefined behavior.  ^Changing the temp_store_directory
          862  +    Doing so results in undefined behavior.  Changing the temp_store_directory
   863    863       setting writes to the [sqlite3_temp_directory] global
   864    864       variable and that global variable is not protected by a mutex.</p>
   865    865   
   866    866       <p>The value <i>directory-name</i> should be enclosed in single quotes.
   867         -    ^(To revert the directory to the default, set the <i>directory-name</i> to
   868         -    an empty string, e.g., <i>PRAGMA temp_store_directory = ''</i>.)^  ^An
          867  +    To revert the directory to the default, set the <i>directory-name</i> to
          868  +    an empty string, e.g., <i>PRAGMA temp_store_directory = ''</i>.  An
   869    869       error is raised if <i>directory-name</i> is not found or is not
   870    870       writable. </p>
   871    871   
   872    872       <p>The default directory for temporary files depends on the OS.  Some
   873    873       OS interfaces may choose to ignore this variable and place temporary
   874    874       files in some other directory different from the directory specified
   875    875       here.  In that sense, this pragma is only advisory.</p>
................................................................................
   897    897   
   898    898       <p>This pragma returns one row for each foreign key that references
   899    899       a column in the argument table.)^
   900    900   }
   901    901   
   902    902   Pragma freelist_count {
   903    903       <p>^(<b>PRAGMA freelist_count;</b></p>
   904         -    <p>Return the number of unused pages in the database file.)^ ^Running
   905         -    a <a href="#pragma_incremental_vacuum">"PRAGMA incremental_vaccum(N);"</a> 
   906         -    command with a large value of N will shrink the database file by this
   907         -    number of pages when incremental vacuum is enabled. </p>
          904  +    <p>Return the number of unused pages in the database file.)^</p>
   908    905   }
   909    906   
   910    907   Pragma index_info {
   911    908       <p>^(<b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
   912    909       <p>This pragma returns one row each column in the named index.)^
   913    910       ^The first column of the result is the rank of the column within the index.
   914    911       ^The second column of the result is the rank of the column within the
................................................................................
  1052   1049   
  1053   1050   }
  1054   1051   
  1055   1052   Pragma wal_autocheckpoint {
  1056   1053       <p><b>PRAGMA wal_autocheckpoint;<br>
  1057   1054        PRAGMA wal_autocheckpoint=</b><i>N</i><b>;</b></p>
  1058   1055   
  1059         -    <p>^This pragma queries or sets the [write-ahead log] auto-checkpoint
  1060         -    interval.  When the [write-ahead log] is enabled (via the
         1056  +    <p>^This pragma queries or sets the [write-ahead log] 
         1057  +    [checkpointing | auto-checkpoint] interval.
         1058  +    When the [write-ahead log] is enabled (via the
  1061   1059       [journal_mode pragma]) a checkpoint will be run automatically whenever
  1062   1060       the write-ahead log equals or exceeds <i>N</i> pages in length.
  1063   1061       Setting the auto-checkpoint size to zero or a negative value
  1064   1062       turns auto-checkpointing off.</p>
  1065   1063       
  1066   1064       <p>^This pragma is a wrapper around the
  1067   1065       [sqlite3_wal_autocheckpoint()] C interface.</p>
         1066  +
         1067  +    <p>^Autocheckpointing is enabled by default with an interval
         1068  +    of 1000 or [SQLITE_DEFAULT_WAL_AUTOCHECKPOINT].</p>
  1068   1069   
  1069   1070   }
  1070   1071   
  1071   1072   Pragma ignore_check_constraints {
  1072   1073       <p>^(<b>PRAGMA ignore_check_constraints  = </b><i>boolean</i><b>;</b></p>
  1073   1074   
  1074   1075       <p>This pragma enables or disables the enforcement of CHECK constraints.)^

Changes to pages/wal.in.

    95     95   write-ahead log is that in the rollback-journal
    96     96   approach, there are two primitive operations, reading and writing,
    97     97   whereas with a write-ahead log
    98     98   there are now three primitive operations:  reading, writing, and
    99     99   checkpointing.</p>
   100    100   
   101    101   <p>By default, SQLite does a checkpoint automatically when the WAL file
   102         -reaches a threshold size of 1000 pages.  Applications using WAL do
          102  +reaches a threshold size of 1000 pages.  (The
          103  +[SQLITE_DEFAULT_WAL_AUTOCHECKPOINT] compile-time option can be used to
          104  +specify a different default.) Applications using WAL do
   103    105   not have to do anything in order to for these checkpoints to occur.  
   104    106   But if they want to, applications can adjust the automatic checkpoint
   105    107   threshold.  Or they can turn off the automatic checkpoints and run 
   106    108   checkpoints during idle moments or in a separate thread or process.</p>
   107    109   
   108    110   <tcl>hd_fragment concurrency {WAL concurrency}</tcl>
   109    111   <h3>Concurrency</h3>