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: |
140df585c350a5a193d0d498215b7278 |
| 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
Changes to pages/compile.in.
95 95 COMPILE_OPTION {SQLITE_DEFAULT_TEMP_CACHE_SIZE=<i><pages></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><pages></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><max_depth></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>