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.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 140df585c350a5a193d0d498215b7278b68febe1
User & Date: drh 2010-12-09 18:56:06.000
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
Unified Diff Ignore Whitespace Patch
Changes to pages/compile.in.
95
96
97
98
99
100
101






102
103
104
105
106
107
108
COMPILE_OPTION {SQLITE_DEFAULT_TEMP_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
  This macro sets the default size of the page-cache for temporary files
  created by SQLite to store intermediate results, in pages. It does
  not affect the page-cache for the temp database, where tables created
  using [CREATE TABLE | CREATE TEMP TABLE] are stored. The default value
  is 500.
}







COMPILE_OPTION {YYSTACKDEPTH=<i>&lt;max_depth&gt;</i>} {
  This macro sets the maximum depth of the LALR(1) stack used by
  the SQL parser within SQLite.  The default value is 100.  A typical
  application will use less than about 20 levels of the stack.
  Developers whose applications contain SQL statements that 
  need more than 100 LALR(1) stack entries should seriously







>
>
>
>
>
>







95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
COMPILE_OPTION {SQLITE_DEFAULT_TEMP_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
  This macro sets the default size of the page-cache for temporary files
  created by SQLite to store intermediate results, in pages. It does
  not affect the page-cache for the temp database, where tables created
  using [CREATE TABLE | CREATE TEMP TABLE] are stored. The default value
  is 500.
}

COMPILE_OPTION {SQLITE_DEFAULT_WAL_AUTOCHECKPOINT=<i>&lt;pages&gt;</i>} {
  This macro sets the default page count for the [WAL]
  [checkpointing | automatic checkpointing] feature.  If unspecified,
  the default page count is 1000.
}

COMPILE_OPTION {YYSTACKDEPTH=<i>&lt;max_depth&gt;</i>} {
  This macro sets the maximum depth of the LALR(1) stack used by
  the SQL parser within SQLite.  The default value is 100.  A typical
  application will use less than about 20 levels of the stack.
  Developers whose applications contain SQL statements that 
  need more than 100 LALR(1) stack entries should seriously
Changes to pages/pragma.in.
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260

    <p>^If the [fullfsync] flag is set, then the F_FULLFSYNC syncing
    method is used for all sync operations and the checkpoint_fullfsync
    setting is irrelevant.</p>
}

LegacyPragma count_changes {
    <p>^(<b>PRAGMA count_changes;
       <br>PRAGMA count_changes = </b>boolean</i><b>;</b></p>

    DISCLAIMER
  
    <p>Query or change the count-changes flag.)^ ^Normally, when the
    count-changes flag is not set, [INSERT], [UPDATE] and [DELETE] statements
    return no data. ^When count-changes is set, each of these commands 
    returns a single row of data consisting of one integer value - the
    number of rows inserted, modified or deleted by the command. ^The 
    returned change count does not include any insertions, modifications
    or deletions performed by triggers, or any changes made automatically
    by [foreign key actions].</p>

    <p>Another way to get the row change counts is to use the
    [sqlite3_changes()] or [sqlite3_total_changes()] interfaces.
    There is a subtle different, though.  ^When an INSERT, UPDATE, or
    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.
}

LegacyPragma default_cache_size {







|




|

|

|






|







230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260

    <p>^If the [fullfsync] flag is set, then the F_FULLFSYNC syncing
    method is used for all sync operations and the checkpoint_fullfsync
    setting is irrelevant.</p>
}

LegacyPragma count_changes {
    <p><b>PRAGMA count_changes;
       <br>PRAGMA count_changes = </b>boolean</i><b>;</b></p>

    DISCLAIMER
  
    <p>Query or change the count-changes flag. Normally, when the
    count-changes flag is not set, [INSERT], [UPDATE] and [DELETE] statements
    return no data. When count-changes is set, each of these commands 
    returns a single row of data consisting of one integer value - the
    number of rows inserted, modified or deleted by the command. The 
    returned change count does not include any insertions, modifications
    or deletions performed by triggers, or any changes made automatically
    by [foreign key actions].</p>

    <p>Another way to get the row change counts is to use the
    [sqlite3_changes()] or [sqlite3_total_changes()] interfaces.
    There is a subtle different, though.  When an INSERT, UPDATE, or
    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.
}

LegacyPragma default_cache_size {
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
       release of SQLite.  To minimize future problems, applications should
       set the foreign key enforcement flag as required by the application
       and not depend on the default setting.
}


LegacyPragma full_column_names {
    <p>^(<b>PRAGMA full_column_names;
       <br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p>

    DISCLAIMER

    <p>Query or change the full_column_names flag.)^ ^This flag together 
    with the [short_column_names] flag determine
    the way SQLite assigns names to result columns of [SELECT] statements.
    ^(Result columns are named by applying the following rules in order:
    <ol>
    <li><p>If there is an AS clause on the result, then the name of
        the column is the right-hand side of the AS clause.</p></li>
    <li><p>If the result is a general expression, not a just the name of
        a source table column,
        then the name of the result is a copy of the expression text.</p></li>
    <li><p>If the [short_column_names] pragma is ON, then the name of the
        result is the name of the source table column without the 
        source table name prefix:  COLUMN.</p></li>
    <li><p>If both pragmas [short_column_names] and [full_column_names]
        are OFF then case (2) applies.
        </p></li>
    <li><p>The name of the result column is a combination of the source table
        and source column name:  TABLE.COLUMN</p></li>
    </ol>)^
}

Pragma fullfsync {
    <p>^(<b>PRAGMA fullfsync
       <br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p>
    <p>Query or change the fullfsync flag.)^ ^This flag
    determines whether or not the F_FULLFSYNC syncing method is used
    on systems that support it.  ^The default value of the fullfsync flag
    is off.  Only Mac OS X supports F_FULLFSYNC.</p>

    <p>See also [checkpoint_fullfsync].</p>
}

Pragma incremental_vacuum {
    <p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
    <p>^The incremental_vacuum pragma causes up to <i>N</i> pages to
    be removed from the [freelist].  ^The database file is truncated by
    the same amount.  ^The incremental_vacuum pragma has no effect if
    the database is not in
    <a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode
    or if there are no pages on the freelist.  ^If there are fewer than
    <i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or
    if <i>N</i> is omitted entirely, then the entire freelist is cleared.</p>
}







|




|


|














|














|
|
|







341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
       release of SQLite.  To minimize future problems, applications should
       set the foreign key enforcement flag as required by the application
       and not depend on the default setting.
}


LegacyPragma full_column_names {
    <p><b>PRAGMA full_column_names;
       <br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p>

    DISCLAIMER

    <p>Query or change the full_column_names flag. This flag together 
    with the [short_column_names] flag determine
    the way SQLite assigns names to result columns of [SELECT] statements.
    Result columns are named by applying the following rules in order:
    <ol>
    <li><p>If there is an AS clause on the result, then the name of
        the column is the right-hand side of the AS clause.</p></li>
    <li><p>If the result is a general expression, not a just the name of
        a source table column,
        then the name of the result is a copy of the expression text.</p></li>
    <li><p>If the [short_column_names] pragma is ON, then the name of the
        result is the name of the source table column without the 
        source table name prefix:  COLUMN.</p></li>
    <li><p>If both pragmas [short_column_names] and [full_column_names]
        are OFF then case (2) applies.
        </p></li>
    <li><p>The name of the result column is a combination of the source table
        and source column name:  TABLE.COLUMN</p></li>
    </ol>
}

Pragma fullfsync {
    <p>^(<b>PRAGMA fullfsync
       <br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p>
    <p>Query or change the fullfsync flag.)^ ^This flag
    determines whether or not the F_FULLFSYNC syncing method is used
    on systems that support it.  ^The default value of the fullfsync flag
    is off.  Only Mac OS X supports F_FULLFSYNC.</p>

    <p>See also [checkpoint_fullfsync].</p>
}

Pragma incremental_vacuum {
    ^(<p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
    <p>The incremental_vacuum pragma causes up to <i>N</i> pages to
    be removed from the [freelist].)^  ^The database file is truncated by
    the same amount.  ^The incremental_vacuum pragma has no effect if
    the database is not in
    <a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode
    or if there are no pages on the freelist.  ^If there are fewer than
    <i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or
    if <i>N</i> is omitted entirely, then the entire freelist is cleared.</p>
}
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
    ^When multiple database connections share the same cache, changing
    the secure-delete flag on one database connection changes it for them
    all.
    </p>
}

LegacyPragma short_column_names {
    <p>^(<b>PRAGMA short_column_names;
       <br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p>

    DISCLAIMER

    <p>Query or change the short-column-names flag.)^ ^This flag affects
    the way SQLite names columns of data returned by [SELECT] statements.
    See the [full_column_names] pragma for full details.
    </p>
}

Pragma synchronous {
    <p>^(<b>PRAGMA synchronous;







|




|







724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
    ^When multiple database connections share the same cache, changing
    the secure-delete flag on one database connection changes it for them
    all.
    </p>
}

LegacyPragma short_column_names {
    <p><b>PRAGMA short_column_names;
       <br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p>

    DISCLAIMER

    <p>Query or change the short-column-names flag. This flag affects
    the way SQLite names columns of data returned by [SELECT] statements.
    See the [full_column_names] pragma for full details.
    </p>
}

Pragma synchronous {
    <p>^(<b>PRAGMA synchronous;
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
        <td align="center"><em>any</em></td>
        <td align="center">memory</td></tr>
    </table>
    </blockquote>)^
}

LegacyPragma temp_store_directory {
    <p>^(<b>PRAGMA temp_store_directory;
       <br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p>
    <p>Query or change the value of the [sqlite3_temp_directory] global
    variable, which many operating-system interface backends use to
    determine where to store [temporary tables] and indices.</p>)^

    DISCLAIMER

    <p>^When the temp_store_directory setting is changed, all existing temporary
    tables, indices, triggers, and viewers in the database connection that
    issued the pragma are immediately deleted.  In
    practice, temp_store_directory should be set immediately after the first
    database connection for a process is opened.  If the temp_store_directory
    is changed for one database connection while other database connections
    are open in the same process, then the behavior is undefined and
    probably undesirable.</p>

    <p>Changing the temp_store_directory setting is <u>not</u> threadsafe.
    Never change the temp_store_directory setting if another thread
    within the application is running any SQLite interface at the same time.
    Doing so results in undefined behavior.  ^Changing the temp_store_directory
    setting writes to the [sqlite3_temp_directory] global
    variable and that global variable is not protected by a mutex.</p>

    <p>The value <i>directory-name</i> should be enclosed in single quotes.
    ^(To revert the directory to the default, set the <i>directory-name</i> to
    an empty string, e.g., <i>PRAGMA temp_store_directory = ''</i>.)^  ^An
    error is raised if <i>directory-name</i> is not found or is not
    writable. </p>

    <p>The default directory for temporary files depends on the OS.  Some
    OS interfaces may choose to ignore this variable and place temporary
    files in some other directory different from the directory specified
    here.  In that sense, this pragma is only advisory.</p>







|



|



|











|




|
|







835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
        <td align="center"><em>any</em></td>
        <td align="center">memory</td></tr>
    </table>
    </blockquote>)^
}

LegacyPragma temp_store_directory {
    <p><b>PRAGMA temp_store_directory;
       <br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p>
    <p>Query or change the value of the [sqlite3_temp_directory] global
    variable, which many operating-system interface backends use to
    determine where to store [temporary tables] and indices.</p>

    DISCLAIMER

    <p>When the temp_store_directory setting is changed, all existing temporary
    tables, indices, triggers, and viewers in the database connection that
    issued the pragma are immediately deleted.  In
    practice, temp_store_directory should be set immediately after the first
    database connection for a process is opened.  If the temp_store_directory
    is changed for one database connection while other database connections
    are open in the same process, then the behavior is undefined and
    probably undesirable.</p>

    <p>Changing the temp_store_directory setting is <u>not</u> threadsafe.
    Never change the temp_store_directory setting if another thread
    within the application is running any SQLite interface at the same time.
    Doing so results in undefined behavior.  Changing the temp_store_directory
    setting writes to the [sqlite3_temp_directory] global
    variable and that global variable is not protected by a mutex.</p>

    <p>The value <i>directory-name</i> should be enclosed in single quotes.
    To revert the directory to the default, set the <i>directory-name</i> to
    an empty string, e.g., <i>PRAGMA temp_store_directory = ''</i>.  An
    error is raised if <i>directory-name</i> is not found or is not
    writable. </p>

    <p>The default directory for temporary files depends on the OS.  Some
    OS interfaces may choose to ignore this variable and place temporary
    files in some other directory different from the directory specified
    here.  In that sense, this pragma is only advisory.</p>
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914

    <p>This pragma returns one row for each foreign key that references
    a column in the argument table.)^
}

Pragma freelist_count {
    <p>^(<b>PRAGMA freelist_count;</b></p>
    <p>Return the number of unused pages in the database file.)^ ^Running
    a <a href="#pragma_incremental_vacuum">"PRAGMA incremental_vaccum(N);"</a> 
    command with a large value of N will shrink the database file by this
    number of pages when incremental vacuum is enabled. </p>
}

Pragma index_info {
    <p>^(<b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
    <p>This pragma returns one row each column in the named index.)^
    ^The first column of the result is the rank of the column within the index.
    ^The second column of the result is the rank of the column within the







|
<
<
<







897
898
899
900
901
902
903
904



905
906
907
908
909
910
911

    <p>This pragma returns one row for each foreign key that references
    a column in the argument table.)^
}

Pragma freelist_count {
    <p>^(<b>PRAGMA freelist_count;</b></p>
    <p>Return the number of unused pages in the database file.)^</p>



}

Pragma index_info {
    <p>^(<b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
    <p>This pragma returns one row each column in the named index.)^
    ^The first column of the result is the rank of the column within the index.
    ^The second column of the result is the rank of the column within the
1052
1053
1054
1055
1056
1057
1058
1059

1060
1061
1062
1063
1064
1065
1066
1067



1068
1069
1070
1071
1072
1073
1074

}

Pragma wal_autocheckpoint {
    <p><b>PRAGMA wal_autocheckpoint;<br>
     PRAGMA wal_autocheckpoint=</b><i>N</i><b>;</b></p>

    <p>^This pragma queries or sets the [write-ahead log] auto-checkpoint

    interval.  When the [write-ahead log] is enabled (via the
    [journal_mode pragma]) a checkpoint will be run automatically whenever
    the write-ahead log equals or exceeds <i>N</i> pages in length.
    Setting the auto-checkpoint size to zero or a negative value
    turns auto-checkpointing off.</p>
    
    <p>^This pragma is a wrapper around the
    [sqlite3_wal_autocheckpoint()] C interface.</p>




}

Pragma ignore_check_constraints {
    <p>^(<b>PRAGMA ignore_check_constraints  = </b><i>boolean</i><b>;</b></p>

    <p>This pragma enables or disables the enforcement of CHECK constraints.)^







|
>
|







>
>
>







1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075

}

Pragma wal_autocheckpoint {
    <p><b>PRAGMA wal_autocheckpoint;<br>
     PRAGMA wal_autocheckpoint=</b><i>N</i><b>;</b></p>

    <p>^This pragma queries or sets the [write-ahead log] 
    [checkpointing | auto-checkpoint] interval.
    When the [write-ahead log] is enabled (via the
    [journal_mode pragma]) a checkpoint will be run automatically whenever
    the write-ahead log equals or exceeds <i>N</i> pages in length.
    Setting the auto-checkpoint size to zero or a negative value
    turns auto-checkpointing off.</p>
    
    <p>^This pragma is a wrapper around the
    [sqlite3_wal_autocheckpoint()] C interface.</p>

    <p>^Autocheckpointing is enabled by default with an interval
    of 1000 or [SQLITE_DEFAULT_WAL_AUTOCHECKPOINT].</p>

}

Pragma ignore_check_constraints {
    <p>^(<b>PRAGMA ignore_check_constraints  = </b><i>boolean</i><b>;</b></p>

    <p>This pragma enables or disables the enforcement of CHECK constraints.)^
Changes to pages/wal.in.
95
96
97
98
99
100
101
102


103
104
105
106
107
108
109
write-ahead log is that in the rollback-journal
approach, there are two primitive operations, reading and writing,
whereas with a write-ahead log
there are now three primitive operations:  reading, writing, and
checkpointing.</p>

<p>By default, SQLite does a checkpoint automatically when the WAL file
reaches a threshold size of 1000 pages.  Applications using WAL do


not have to do anything in order to for these checkpoints to occur.  
But if they want to, applications can adjust the automatic checkpoint
threshold.  Or they can turn off the automatic checkpoints and run 
checkpoints during idle moments or in a separate thread or process.</p>

<tcl>hd_fragment concurrency {WAL concurrency}</tcl>
<h3>Concurrency</h3>







|
>
>







95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
write-ahead log is that in the rollback-journal
approach, there are two primitive operations, reading and writing,
whereas with a write-ahead log
there are now three primitive operations:  reading, writing, and
checkpointing.</p>

<p>By default, SQLite does a checkpoint automatically when the WAL file
reaches a threshold size of 1000 pages.  (The
[SQLITE_DEFAULT_WAL_AUTOCHECKPOINT] compile-time option can be used to
specify a different default.) Applications using WAL do
not have to do anything in order to for these checkpoints to occur.  
But if they want to, applications can adjust the automatic checkpoint
threshold.  Or they can turn off the automatic checkpoints and run 
checkpoints during idle moments or in a separate thread or process.</p>

<tcl>hd_fragment concurrency {WAL concurrency}</tcl>
<h3>Concurrency</h3>