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: |
140df585c350a5a193d0d498215b7278 |
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
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><pages></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><max_depth></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><pages></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><pages></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><max_depth></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 | <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 { | | | | | | | 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 | 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 { | | | | | | | | | 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 | ^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 { | | | | 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 | <td align="center"><em>any</em></td> <td align="center">memory</td></tr> </table> </blockquote>)^ } LegacyPragma temp_store_directory { | | | | | | | | 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 | <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> | | < < < | 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 | } Pragma wal_autocheckpoint { <p><b>PRAGMA wal_autocheckpoint;<br> PRAGMA wal_autocheckpoint=</b><i>N</i><b>;</b></p> | | > | > > > | 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 | 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 | | > > | 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> |
︙ | ︙ |