Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to the PRAGMA documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
a84577d9a2b85d7aa0ff726b375e2258 |
User & Date: | drh 2009-04-06 16:42:17.000 |
Context
2009-04-06
| ||
18:51 | Add documentation for the collation_list pragma. (check-in: db4897a3dd user: drh tags: trunk) | |
16:42 | Updates to the PRAGMA documentation. (check-in: a84577d9a2 user: drh tags: trunk) | |
2009-04-03
| ||
01:44 | Updates, clarifications, and corrections to the pragma documentation. (check-in: 36a68629e1 user: drh tags: trunk) | |
Changes
Changes to pages/compile.in.
︙ | ︙ | |||
64 65 66 67 68 69 70 71 72 73 74 75 76 77 | be able to read and write database files created by newer versions of SQLite, the default file format is set to 1 for maximum compatibility. The file format for a new database can be set at runtime using the [PRAGMA legacy_file_format] command. } COMPILE_OPTION {SQLITE_DEFAULT_MEMSTATUS=<i><1 or 0></i>} { This macro is used to determine whether or not the features enabled and disabled using the SQLITE_CONFIG_MEMSTATUS argument to [sqlite3_config()] are available by default. The default value is 1 ([SQLITE_CONFIG_MEMSTATUS] related features enabled). } | > > > > > > > > | 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | be able to read and write database files created by newer versions of SQLite, the default file format is set to 1 for maximum compatibility. The file format for a new database can be set at runtime using the [PRAGMA legacy_file_format] command. } COMPILE_OPTION {SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT=<i><bytes></i>} { This option sets the size limit on rollback journal files in [journal_mode pragma | persistent journal mode]. When this compile-time option is omitted there is no upper bound on the size of the rollback journal file. The journal file size limit can be changed at run-time using the [journal_size_limit pragma]. } COMPILE_OPTION {SQLITE_DEFAULT_MEMSTATUS=<i><1 or 0></i>} { This macro is used to determine whether or not the features enabled and disabled using the SQLITE_CONFIG_MEMSTATUS argument to [sqlite3_config()] are available by default. The default value is 1 ([SQLITE_CONFIG_MEMSTATUS] related features enabled). } |
︙ | ︙ |
Changes to pages/pragma.in.
︙ | ︙ | |||
64 65 66 67 68 69 70 | Section {PRAGMA command syntax} syntax {PRAGMA} BubbleDiagram pragma-stmt BubbleDiagram pragma-value </tcl> <p> | | > > | > | | | 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | Section {PRAGMA command syntax} syntax {PRAGMA} BubbleDiagram pragma-stmt BubbleDiagram pragma-value </tcl> <p> A pragma can take either zero or one argument. The argument is may be either in parentheses or it may be separated from the pragma name by an equal sign. The two syntaxes yield identical results. In many pragmas, the argument is a boolean. The boolean can be one of: </p> <center> <b>1 yes true on<br>0 no false off</b> </center> <p>Keyword arguments can optionally appear in quotes. (Example: <tt>'yes' &91;FALSE&93;</tt>.) Some pragmas takes a string literal as their argument. When pragma takes a keyword argument, it will usually also take a numeric equivalent as well. For example, "0" and "no" mean the same thing, as does "1" and "yes". When querying the value of a setting, many pragmas return the number rather than the keyword.</p> <p>A pragma may have an optional database name before the pragma name. The database name is the name of an [ATTACH]-ed database. Or it can be "main" or "temp" for the main and the TEMP databases. If the optional database name is omitted, "main" is assumed. In some pragmas, the database name is meaningless and is simply ignored.</p> |
︙ | ︙ | |||
248 249 250 251 252 253 254 | <p>Databases created by the [ATTACH] command always use the same encoding as the main database.</p> </li> <tcl>Subsection full_column_names</tcl> <li><p><b>PRAGMA full_column_names; <br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p> | | > | < | < | > > > | > > > | > | < > | < < < > > | > | > > > | 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 | <p>Databases created by the [ATTACH] command always use the same encoding as the main database.</p> </li> <tcl>Subsection full_column_names</tcl> <li><p><b>PRAGMA full_column_names; <br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p> <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 results returned by [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 for simple queries and case (5) applies for joins. </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> </li> <tcl>Subsection fullfsync</tcl> <li><p><b>PRAGMA fullfsync <br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p> <p>Query or change the fullfsync flag. This flag affects determines whether or not the F_FULLFSYNC syncing method is used on systems that support it. The default value is off. As of this writing (2006-02-10) only Mac OS X supports F_FULLFSYNC. </p> </li> <tcl>Subsection incremental_vacuum</tcl> <li><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> <p>As of [version 3.4.0] (the first version that supports incremental_vacuum) this feature is still experimental. Possible future changes include enhancing incremental vacuum to do defragmentation and node repacking just as the full-blown [VACUUM] command does. And incremental vacuum may be promoted from a pragma to a separate SQL command, or perhaps some variation on the [VACUUM] command. Programmers are cautioned to not become enamored with the current syntax or functionality as it is likely to change.</p> </li> <tcl>Subsection journal_mode</tcl> <li><p><b>PRAGMA journal_mode; <br>PRAGMA </b><i>database</i><b>.journal_mode; <br>PRAGMA journal_mode = <i>DELETE | TRUNCATE | PERSIST | MEMORY | OFF</i> <br>PRAGMA </b><i>database</i><b>.journal_mode = <i>DELETE | TRUNCATE | PERSIST | MEMORY | OFF</i></b></p> <p>This pragma queries or sets the journal mode for databases associated with the current [database connection].</p> <p>The first two forms of this pragma query the current journaling mode. In the first form, the default journal_mode is returned. |
︙ | ︙ | |||
353 354 355 356 357 358 359 360 361 362 363 364 365 366 | <p>The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The [ROLLBACK] command no longer works. If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt.</p> </li> <tcl>Subsection journal_size_limit</tcl> <li><p><b> PRAGMA journal_size_limit<br> PRAGMA journal_size_limit = </b><i>N</i> <b>;</b> | > > > > > | 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 | <p>The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The [ROLLBACK] command no longer works. If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt.</p> <p>Note that the journal_mode for an [in-memory database] is either MEMORY or OFF and can not be changed to a different value. An attempt to change the journal_mode of an [in-memory database] to any setting other than MEMORY or OFF is silently ignored.</p> </li> <tcl>Subsection journal_size_limit</tcl> <li><p><b> PRAGMA journal_size_limit<br> PRAGMA journal_size_limit = </b><i>N</i> <b>;</b> |
︙ | ︙ | |||
381 382 383 384 385 386 387 | <p>The second form of the pragma listed above is used to set a new limit in bytes for the specified database. A negative number implies no limit. Both the first and second forms of the pragma listed above return a single result row containing a single integer column - the value of the journal size limit in bytes. The default limit value is -1 (no limit), which may be overridden by defining the preprocessor macro | | | | | | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 | <p>The second form of the pragma listed above is used to set a new limit in bytes for the specified database. A negative number implies no limit. Both the first and second forms of the pragma listed above return a single result row containing a single integer column - the value of the journal size limit in bytes. The default limit value is -1 (no limit), which may be overridden by defining the preprocessor macro [SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT] at compile time.</p> <p>This pragma only operates on the single database specified prior to the pragma name (or on the "main" database if no database is specified.) There is no way to operate on all attached databases using a single PRAGMA statement, nor is there a way to set the limit to use for databases that will be attached in the future. </li> <tcl>Subsection legacy_file_format</tcl> <li><p><b>PRAGMA legacy_file_format; <br>PRAGMA legacy_file_format = <i>boolean</i></b></p> <p>This pragma sets or queries the value of the legacy_file_format flag. When this flag is on, new SQLite databases are created in a file format that is readable and writable by all versions of SQLite going back to 3.0.0. When the flag is off, new databases are created using the latest file format which might not be readable or writable by versions of SQLite prior to 3.3.0.</p> <p>When the pragma is issued with no argument, it returns the setting of the flag. This pragma does <u>not</u> tell which file format the current database is using. It tells what format will be used by any newly created databases.</p> <p>This flag only affects newly created databases. It has no |
︙ | ︙ | |||
453 454 455 456 457 458 459 | <p>Then the locking mode applies only to the named database. If no database name qualifier precedes the "locking_mode" keyword then the locking mode is applied to all databases, including any new databases added by subsequent [ATTACH] commands.</p> <p>The "temp" database (in which TEMP tables and indices are stored) | > | > | 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 | <p>Then the locking mode applies only to the named database. If no database name qualifier precedes the "locking_mode" keyword then the locking mode is applied to all databases, including any new databases added by subsequent [ATTACH] commands.</p> <p>The "temp" database (in which TEMP tables and indices are stored) and [in-memory databases] always uses exclusive locking mode. The locking mode of temp and [in-memory databases] cannot be changed. All other databases use the normal locking mode by default and are affected by this pragma.</p> </li> <tcl>Subsection page_size</tcl> <li><p><b>PRAGMA page_size; <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p> |
︙ | ︙ | |||
525 526 527 528 529 530 531 | Cache sharing is disabled by default. </p> <p>See [SQLite Shared-Cache Mode] for additional information.</p> </li> <tcl>Subsection reverse_unordered_selects</tcl> | | > | 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 | Cache sharing is disabled by default. </p> <p>See [SQLite Shared-Cache Mode] for additional information.</p> </li> <tcl>Subsection reverse_unordered_selects</tcl> <li><p><b>PRAGMA reverse_unordered_selects; <br>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b></p> <p>When enabled, this PRAGMA causes [SELECT] statements without a an ORDER BY clause to emit their results in the reverse order of what they normally would. This can help debug applications that are making invalid assumptions about the result order.<p>SQLite makes no guarantees about the order of results if a SELECT omits the ORDER BY clause. Even so, the order of results does not change from one run to the next, and so many applications mistakenly come to depend |
︙ | ︙ | |||
549 550 551 552 553 554 555 | </p> </li> <tcl>Subsection short_column_names</tcl> <li><p><b>PRAGMA short_column_names; <br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p> <p>Query or change the short-column-names flag. This flag affects | | < | < < < < < < < < < | 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 | </p> </li> <tcl>Subsection short_column_names</tcl> <li><p><b>PRAGMA short_column_names; <br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p> <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> </li> <tcl>Subsection synchronous</tcl> <li><p><b>PRAGMA synchronous; <br>PRAGMA synchronous = </b> <i>0 | OFF | 1 | NORMAL | 2 | FULL</i><b>;</b></p> |
︙ | ︙ | |||
660 661 662 663 664 665 666 | <br> <tcl>Subsection temp_store_directory</tcl> <li><p><b>PRAGMA temp_store_directory; <br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p> <p>Query or change the setting of the "temp_store_directory" - the directory where files used for storing [temporary tables] and indices | | < | > > > > > > > | | | < < < < < < < < | | 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 | <br> <tcl>Subsection temp_store_directory</tcl> <li><p><b>PRAGMA temp_store_directory; <br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p> <p>Query or change the setting of the "temp_store_directory" - the directory where files used for storing [temporary tables] and indices are kept.</p> <p>When the temp_store_directory setting is changed, all existing temporary tables, indices, triggers, and viewers are immediately deleted. In practice, temp_store_directory should be set immediately after the database is opened.</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 in place temporary files in some other directory different from the directory specified here. In that sense, this pragma is only advisory.</p> </li> </ul> <tcl>Section {Pragmas to query the database schema} schema</tcl> <ul> <tcl>Subsection database_list</tcl> |
︙ | ︙ |