Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates, clarifications, and corrections to the pragma documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
36a68629e1f0eb7ebbf31a07d7851271 |
User & Date: | drh 2009-04-03 01:44:55.000 |
Context
2009-04-06
| ||
16:42 | Updates to the PRAGMA documentation. (check-in: a84577d9a2 user: drh tags: trunk) | |
11:46 | Fix a typo in fileformat.html: 230 -> 2^30. (check-in: 928d0e91a3 user: dan tags: trunk) | |
2009-04-03
| ||
01:44 | Updates, clarifications, and corrections to the pragma documentation. (check-in: 36a68629e1 user: drh tags: trunk) | |
2009-04-02
| ||
20:17 | Improvements and clarifications to the auto_vacuum pragma documentation. (check-in: ae7cc4efbb user: drh tags: trunk) | |
Changes
Changes to pages/pragma.in.
︙ | ︙ | |||
63 64 65 66 67 68 69 70 71 72 73 74 75 | <tcl> Section {PRAGMA command syntax} syntax {PRAGMA} BubbleDiagram pragma-stmt BubbleDiagram pragma-value </tcl> <tcl>Section {Pragmas to modify library operation} modify</tcl> </tcl> <ul> <tcl>Subsection auto_vacuum</tcl> <li><p><b>PRAGMA auto_vacuum;<br> | > > > > > > > > > > > > > > > > > > > > > > | | | 63 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 95 96 97 98 99 100 101 102 103 104 105 106 | <tcl> Section {PRAGMA command syntax} syntax {PRAGMA} BubbleDiagram pragma-stmt BubbleDiagram pragma-value </tcl> <p> A pragma can take a single argument or it can have no argument at all. 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. 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, the number is returned, not 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> <tcl>Section {Pragmas to modify library operation} modify</tcl> </tcl> <ul> <tcl>Subsection auto_vacuum</tcl> <li><p><b>PRAGMA auto_vacuum;<br> PRAGMA auto_vacuum = </b><i>boolean</i><b>;</b></p> <p>Query or set the auto-vacuum status in the database.</p> <p>The default setting for auto-vacuum is 0 or "none", unless the [SQLITE_DEFAULT_AUTOVACUUM] compile-time option is used. The "none" setting means that auto-vacuum is disabled. When auto-vacuum is disabled and data is deleted data from a database, the database file remains the same size. Unused database file |
︙ | ︙ | |||
125 126 127 128 129 130 131 | <p>When the auto_vacuum pragma is invoked with no arguments, it returns the current auto_vacuum mode.</p> </li> <tcl>Subsection cache_size</tcl> <li><p><b>PRAGMA cache_size; <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p> | | | > | < > > > | | | < | | | < | < | < | | > > | | > > > > > | 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 | <p>When the auto_vacuum pragma is invoked with no arguments, it returns the current auto_vacuum mode.</p> </li> <tcl>Subsection cache_size</tcl> <li><p><b>PRAGMA cache_size; <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p> <p>Query or change the suggested maximum number of database disk pages that SQLite will hold in memory at once per open database file. Whether or not this suggestion is honored is at the discretion of the [sqlite3_pcache_methods | Application Defined Page Cache]. The default page cache implemention that is built into SQLite does honor the suggestion if it can, but alternative page caches implementations set by the application at run-time may choose to ignore this suggestion. The default suggested cache size is 2000.</p> <p>When you change the cache size using the cache_size pragma, the change only endures for the current session. The cache size reverts to the default value when the database is closed and reopened. Use the [default_cache_size] pragma to check the cache size permanently.</p></li> <tcl>Subsection case_sensitive_like</tcl> <li><p><b>PRAGMA case_sensitive_like = </b><i>boolean</i><b>;</b></p> <p>The default behavior of the [LIKE] operator is to ignore case for ASCII characters. Hence, by default <b>'a' LIKE 'A'</b> is true. The case_sensitive_like pragma installs a new application-defined LIKE function that can change this behavior. When case_sensitive_like is enabled, <b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.</p> </li> <tcl>Subsection count_changes</tcl> <li><p><b>PRAGMA count_changes; <br>PRAGMA count_changes = </b>boolean</i><b>;</b></p> <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.</p> <p>The use of this pragma is discouraged. A better way to get the row change count is to use the [sqlite3_changes()] or [sqlite3_total_changes()] interfaces.</p> <tcl>Subsection default_cache_size</tcl> <li><p><b>PRAGMA default_cache_size; <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p> <p>This pragma queries or sets the suggested maximum number of pages of disk cache that will be allocated per open database file. The difference between this pragma and [cache_size] is that the value set here persists across database connections. </p></li> <tcl>Subsection empty_result_callbacks</tcl> <li><p><b>PRAGMA empty_result_callbacks; <br>PRAGMA empty_result_callbacks = </b><i>boolean</i><b>;</b></p> <p>Query or change the empty-result-callbacks flag.</p> <p>The empty-result-callbacks flag affects the [sqlite3_exec()] API only. Normally, when the empty-result-callbacks flag is cleared, the callback function supplied to the [sqlite3_exec()] call is not invoked for commands that return zero rows of data. When empty-result-callbacks is set in this situation, the callback function is invoked exactly once, with the third parameter set to 0 (NULL). This is to enable programs that use the [sqlite3_exec()] API to retrieve column-names even when a query returns no data.</p> <p>This pragma is legacy. It was created long ago in the early days of SQLite before the prepared statement interface was available. Do not use this pragma. It is likely to go away in a future release</p> <tcl>Subsection encoding</tcl> <li><p><b>PRAGMA encoding; <br>PRAGMA encoding = "UTF-8"; <br>PRAGMA encoding = "UTF-16"; <br>PRAGMA encoding = "UTF-16le"; <br>PRAGMA encoding = "UTF-16be";</b></p> |
︙ | ︙ | |||
219 220 221 222 223 224 225 | <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; | | | | 247 248 249 250 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 | <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 affects the way SQLite names columns of data returned by [SELECT] statements when the expression for the column is a table-column name or the wildcard "*". Normally, such result columns are named <table-name/alias><column-name> if the [SELECT] statement joins two or more tables together, or simply <column-name> if the [SELECT] statement queries a single table. When the full-column-names flag is set, such columns are always named <table-name/alias> <column-name> regardless of whether or not a join is performed. </p> <p>If both the short-column-names and full-column-names are set, then the behaviour associated with the full-column-names flag is exhibited. </p> </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> |
︙ | ︙ | |||
270 271 272 273 274 275 276 | 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; | < < < | 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 | 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 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. |
︙ | ︙ | |||
368 369 370 371 372 373 374 | 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; | | | 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 | 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 older versions of SQLite.</p> |
︙ | ︙ | |||
487 488 489 490 491 492 493 | form attempts to modify the maximum page count. The maximum page count cannot be reduced below the current database size. </p> </li> <tcl>Subsection read_uncommitted</tcl> <li><p><b>PRAGMA read_uncommitted; | | | | 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 | form attempts to modify the maximum page count. The maximum page count cannot be reduced below the current database size. </p> </li> <tcl>Subsection read_uncommitted</tcl> <li><p><b>PRAGMA read_uncommitted; <br>PRAGMA read_uncommitted = </b><i>boolean</i><b>;</b></p> <p>Query, set, or clear READ UNCOMMITTED isolation. The default isolation level for SQLite is SERIALIZABLE. Any process or thread can select READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except between connections that share a common page and schema cache. Cache sharing is enabled using the [sqlite3_enable_shared_cache()] API. 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 = </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 |
︙ | ︙ | |||
522 523 524 525 526 527 528 | identified and fixed early, reducing problems that might be caused by linking against a different version of SQLite. </p> </li> <tcl>Subsection short_column_names</tcl> <li><p><b>PRAGMA short_column_names; | | | | | | < | | | | < | | | 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 | identified and fixed early, reducing problems that might be caused by linking against a different version of SQLite. </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 when the expression for the column is a table-column name or the wildcard "*". Normally, such result columns are named <table-name/alias><column-name> if the [SELECT] statement joins two or more tables together, or simply <column-name> if the [SELECT] statement queries a single table. When the short-column-names flag is set, such columns are always named <column-name> regardless of whether or not a join is performed. </p> <p>If both the short-column-names and full-column-names are set, then the behaviour associated with the full-column-names flag is exhibited. </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> <p>Query or change the setting of the "synchronous" flag. The first (query) form will return the setting as an integer. When synchronous is FULL (2), the SQLite database engine will pause at critical moments to make sure that data has actually been written to the disk surface before continuing. This ensures that if the operating system crashes or if there is a power failure, the database will be uncorrupted after rebooting. FULL synchronous is very safe, but it is also slower. When synchronous is NORMAL, the SQLite database engine will still pause at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. With synchronous OFF (0), SQLite continues without pausing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF. </p> <p>The default setting is synchronous=FULL. </p> </li> <tcl>Subsection temp_store</tcl> <li><p><b>PRAGMA temp_store; <br>PRAGMA temp_store = </b> <i>0 | DEFAULT | 1 | FILE | 2 | MEMORY</i><b>;</b></p> <p>Query or change the setting of the "<b>temp_store</b>" parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro [SQLITE_TEMP_STORE] is used to determine where temporary tables and indices are stored. When temp_store is MEMORY (2) [temporary tables] and indices are kept in as if they were pure [in-memory databases] memory. When temp_store is FILE (1) [temporary tables] and indices are stored in a file. The [temp_store_directory] pragma can be used to specify the directory containing temporary files when <b>FILE</b> is specified. When the temp_store setting is changed, all existing temporary tables, indices, triggers, and views are immediately deleted.</p> <p>It is possible for the library compile-time C preprocessor symbol [SQLITE_TEMP_STORE] to override this pragma setting. The following table summarizes |
︙ | ︙ | |||
634 635 636 637 638 639 640 | </table> </blockquote> </li> <br> <tcl>Subsection temp_store_directory</tcl> <li><p><b>PRAGMA temp_store_directory; | | | 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 | </table> </blockquote> </li> <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. This setting lasts for the duration of the current connection only and resets to its default value for each new connection opened. <p>When the temp_store_directory setting is changed, all existing temporary tables, indices, triggers, and viewers are immediately deleted. In |
︙ | ︙ | |||
685 686 687 688 689 690 691 | <li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p> <p>For each foreign key that references a column in the argument table, invoke the callback function with information about that foreign key. The callback function will be invoked once for each column in each foreign key.</p></li> <tcl>Subsection freelist_count</tcl> | | | | | | | | 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 | <li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p> <p>For each foreign key that references a column in the argument table, invoke the callback function with information about that foreign key. The callback function will be invoked once for each column in each foreign key.</p></li> <tcl>Subsection freelist_count</tcl> <li><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. </p></li> <tcl>Subsection index_info</tcl> <li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p> <p>For each column that the named index references, invoke the callback function once with information about that column, including the column name, and the column number.</p></li> <tcl>Subsection index_list</tcl> <li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p> <p>For each index on the named table, invoke the callback function once with information about that index. Arguments include the index name and a flag to indicate whether or not the index must be unique.</p></li> <tcl>Subsection page_count</tcl> <li><p><b>PRAGMA page_count;</b></p> <p>Return the total number of pages in the database file.</p></li> <tcl>Subsection table_info</tcl> <li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p> <p>For each column in the named table, invoke the callback function once with information about that column, including the column name, data type, whether or not the column can be NULL, and the default value for the column.</p></li> </ul> <tcl>Section {Pragmas to query/modify version values} version</tcl> <ul> <tcl>Subsection schema_version user_version</tcl> <li><p><b>PRAGMA schema_version; <br>PRAGMA schema_version = </b><i>integer </i><b>; <br>PRAGMA user_version; <br>PRAGMA user_version = </b><i>integer </i><b>;</b> <p> The pragmas schema_version and user_version are used to set or get the value of the schema-version and user-version, respectively. Both the schema-version and the user-version are 32-bit signed integers stored in the database header.</p> |
︙ | ︙ | |||
773 774 775 776 777 778 779 | <p>The pragma is like [integrity_check] except that it does not verify that index content matches table content. By skipping the verification of index content, quick_check is able to run much faster than integrity_check. Otherwise the two pragmas are the same. </p></li> <tcl>Subsection parser_trace</tcl> | | | | > | | | | | 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 | <p>The pragma is like [integrity_check] except that it does not verify that index content matches table content. By skipping the verification of index content, quick_check is able to run much faster than integrity_check. Otherwise the two pragmas are the same. </p></li> <tcl>Subsection parser_trace</tcl> <li><p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p> <p>Turn tracing of the SQL parser inside of the SQLite library on and off. This is used for debugging. This only works if the library is compiled with the SQLITE_DEBUG compile-time option. </p></li> <tcl>Subsection vdbe_trace</tcl> <li><p><b>PRAGMA vdbe_trace = </b><i>boolean</i><b>;</b></p> <p>Turn tracing of the virtual database engine inside of the SQLite library on and off. This is used for debugging. See the <a href="vdbe.html#trace">VDBE documentation</a> for more information.</p></li> <tcl>Subsection vdbe_listing</tcl> <li><p><b>PRAGMA vdbe_listing = </b><i>boolean</i><b>;</b></p> <p>Turn listings of virtual machine programs on and off. With listing is on, the entire content of a program is printed just prior to beginning execution. The statement executes normally after the listing is printed. This is used for debugging. See the <a href="vdbe.html#trace">VDBE documentation</a> for more information.</p></li> </ul> |