Documentation Source Text

Check-in [b5201ace8f]
Login

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: b5201ace8f761b5a8a8ded9c889dac0e12224389
User & Date: drh 2010-11-23 15:57:02.000
Context
2010-11-26
15:18
Further updates to pragma documentation. Mark deprecated pragmas as such. Tag pragmas used for debugging only. (check-in: e3dca045a2 user: drh tags: trunk)
2010-11-23
15:57
Updates to the PRAGMA documentation. (check-in: b5201ace8f user: drh tags: trunk)
2010-11-21
01:36
Updates to documentation on date/time functions and pragmas. (check-in: 2d231c8139 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/pragma.in.
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
}

Pragma checkpoint_fullfsync {
    <p>^(<b>PRAGMA checkpoint_fullfsync
       <br>PRAGMA checkpoint_fullfsync = </b><i>boolean</i><b>;</b></p>
    <p>Query or change the fullfsync flag for [checkpoint] operations.)^
    ^If this flag is set, then the F_FULLFSYNC syncing method is used
    during checkpoint operations on systems that support it.  
    ^The default value of the checkpoint_fullfsync flag
    is off.  Only Mac OS X supports F_FULLFSYNC.</p>

    <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>
}

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







|

|

|







198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
}

Pragma checkpoint_fullfsync {
    <p>^(<b>PRAGMA checkpoint_fullfsync
       <br>PRAGMA checkpoint_fullfsync = </b><i>boolean</i><b>;</b></p>
    <p>Query or change the fullfsync flag for [checkpoint] operations.)^
    ^If this flag is set, then the F_FULLFSYNC syncing method is used
    during checkpoint operations on systems that support F_FULLFSYNC. 
    ^The default value of the checkpoint_fullfsync flag
    is off.  Only Mac OS-X supports F_FULLFSYNC.</p>

    <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>
}

Pragma count_changes {
    <p>^(<b>PRAGMA count_changes;
       <br>PRAGMA count_changes = </b>boolean</i><b>;</b></p>
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
    <p>^In first form, if the main database has already been
    created, then this pragma returns the text encoding used by the
    main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16
    encoding) or "UTF-16be" (big-endian UTF-16 encoding).  ^If the main
    database has not already been created, then the value returned is the
    text encoding that will be used to create the main database, if 
    it is created by this session.</p>
    <p>^The second and subsequent forms of this pragma are only useful if
    the main database has not already been created.  ^In this case the 
    pragma sets the encoding that the main database will be created with if
    it is created by this session. ^The string "UTF-16" is interpreted
    as "UTF-16 encoding using native machine byte-ordering".  ^If the second
    and subsequent forms are used after the database file has already
    been created, they have no effect and are silently ignored.</p>

    <p>^Once an encoding has been set for a database, it cannot be changed.</p>

    <p>^Databases created by the [ATTACH] command always use the same encoding
    as the main database.</p>

}

Pragma foreign_keys {
     <p>^(<b>PRAGMA foreign_keys;
       <br>PRAGMA foreign_keys = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear the enforcement of [foreign key constraints].)^








|
|
|

|
|
|




|
>







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
    <p>^In first form, if the main database has already been
    created, then this pragma returns the text encoding used by the
    main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16
    encoding) or "UTF-16be" (big-endian UTF-16 encoding).  ^If the main
    database has not already been created, then the value returned is the
    text encoding that will be used to create the main database, if 
    it is created by this session.</p>

    <p>^The second through fifth forms of this pragma
    set the encoding that the main database will be created with if
    it is created by this session. ^The string "UTF-16" is interpreted
    as "UTF-16 encoding using native machine byte-ordering".  ^It is not
    possible to change the text encoding of a database after it has been
    created and any attempt to do so will be silently ignored.</p>

    <p>^Once an encoding has been set for a database, it cannot be changed.</p>

    <p>^Databases created by the [ATTACH] command always use the same encoding
    as the main database.  ^An attempt to [ATTACH] a database with a different
    text encoding from the "main" database will fail.</p>
}

Pragma foreign_keys {
     <p>^(<b>PRAGMA foreign_keys;
       <br>PRAGMA foreign_keys = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear the enforcement of [foreign key constraints].)^

589
590
591
592
593
594
595
596
597
598
599



600
601
602
603
604
605
606
    SQLITE_DEFALT_PAGE_SIZE
    and the sector size as reported by the xSectorSize method of the
    [sqlite3_io_methods] object, but not more than 
    SQLITE_MAX_DEFAULT_PAGE_SIZE.  ^The normal configuration for SQLite
    running on workstations is for atomic write to be
    disabled, for the maximum page size to be set to 65536, for
    SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the
    maximum default page size to be set to 8192.  ^(The default xSectorSize
    method on workstation implementations always reports a sector size
    of 512 bytes.  Hence, 
    the default page size chosen by SQLite is usually 1024 bytes.)^</p>



}

Pragma max_page_count {
    <p>^(<b>PRAGMA max_page_count;
       <br>PRAGMA max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.)^
    ^Both forms of the pragma return the maximum page count.  ^The second







|
|

|
>
>
>







590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
    SQLITE_DEFALT_PAGE_SIZE
    and the sector size as reported by the xSectorSize method of the
    [sqlite3_io_methods] object, but not more than 
    SQLITE_MAX_DEFAULT_PAGE_SIZE.  ^The normal configuration for SQLite
    running on workstations is for atomic write to be
    disabled, for the maximum page size to be set to 65536, for
    SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the
    maximum default page size to be set to 8192.  The default xSectorSize
    method on unix workstation implementations always reports a sector size
    of 512 bytes.  Hence, 
    the default page size chosen by SQLite on unix is usually 1024 bytes.
    On windows, the GetDiskFreeSpace() interface is used to obtain the
    actual device sector size and hence the default page size on windows
    will sometimes be greater than 1024.</p>
}

Pragma max_page_count {
    <p>^(<b>PRAGMA max_page_count;
       <br>PRAGMA max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.)^
    ^Both forms of the pragma return the maximum page count.  ^The second
897
898
899
900
901
902
903
904
905
906

907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
    <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>

  
<p>    ^(The schema-version is usually only manipulated internally by SQLite.  
       It is incremented by SQLite whenever the database schema is modified 
       (by creating or dropping a table or index).)^ ^The schema version is 
       used by SQLite each time a query is executed to ensure that the 
       internal cache of the schema used when compiling the SQL query matches 
       the schema of the database against which the compiled query is actually 
       executed.  ^Subverting this mechanism by using "PRAGMA schema_version" 
       to modify the schema-version is potentially dangerous and may lead 
       to program crashes or database corruption. Use with caution!</p>
  
<p>    ^The user-version is not used internally by SQLite. It may be used by
       applications for any purpose.</p>
}

Pragma compile_options {
    <p><b>PRAGMA compile_options;</b></p>
    <p>^This pragma returns the names of [compile-time options] used when
    building SQLite, one option per row.  ^The "SQLITE_" prefix is omitted







|
|
|
>











|







901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
    <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. ^(The
       schema-version and the user-version are big-endian 32-bit signed
       integers stored in the database header at offsets 40 and 60,
       respectively.)^</p>
  
<p>    ^(The schema-version is usually only manipulated internally by SQLite.  
       It is incremented by SQLite whenever the database schema is modified 
       (by creating or dropping a table or index).)^ ^The schema version is 
       used by SQLite each time a query is executed to ensure that the 
       internal cache of the schema used when compiling the SQL query matches 
       the schema of the database against which the compiled query is actually 
       executed.  ^Subverting this mechanism by using "PRAGMA schema_version" 
       to modify the schema-version is potentially dangerous and may lead 
       to program crashes or database corruption. Use with caution!</p>
  
<p>    The user-version is not used internally by SQLite. It may be used by
       applications for any purpose.</p>
}

Pragma compile_options {
    <p><b>PRAGMA compile_options;</b></p>
    <p>^This pragma returns the names of [compile-time options] used when
    building SQLite, one option per row.  ^The "SQLITE_" prefix is omitted
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971


972
973
974
975
976
977
978
979
980
981
982
983


984

985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
    integrity_check.  Otherwise the two pragmas are the same.
    </p>
}

Pragma parser_trace {
    <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>

    <p>This PRAGMA is used to help test and debug the SQLite library
    itself.  It is of little or not use to applications and is disabled
    if SQLite is not compiled with [SQLITE_DEBUG].</p>
}

Pragma vdbe_trace {
    <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>

    <p>This PRAGMA is used to help test and debug the SQLite library
    itself.  It is of little or not use to applications and is disabled
    if SQLite is not compiled with [SQLITE_DEBUG].</p>
}

Pragma vdbe_listing {
    <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>

    <p>This PRAGMA is used to help test and debug the SQLite library
    itself.  It is of little or not use to applications and is disabled
    if SQLite is not compiled with [SQLITE_DEBUG].</p>
}

Pragma wal_checkpoint {
    <p><b>PRAGMA </b><i>database</i><b>.wal_checkpoint;</b></p>

    <p>^If the [write-ahead log] is enabled (via the [journal_mode pragma]),
    this pragma causes a checkpoint operation to run on database







<
<
|
<
|
|
<
|





|
>
>
|


<
<
<
<





>
>
|
>



|


<
<
<
<







956
957
958
959
960
961
962


963

964
965

966
967
968
969
970
971
972
973
974
975
976
977




978
979
980
981
982
983
984
985
986
987
988
989
990
991
992




993
994
995
996
997
998
999
    integrity_check.  Otherwise the two pragmas are the same.
    </p>
}

Pragma parser_trace {
    <p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p>



    <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time

    option, then the parser_trace pragma can be used to turn on tracing
    of the SQL parser used internally by SQLite.

    This feature is used for debugging SQLite itself.</p>
}

Pragma vdbe_trace {
    <p><b>PRAGMA vdbe_trace = </b><i>boolean</i><b>;</b></p>

    <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
    option, then the vdbe_trace pragma can be used to cause virtual machine
    opcodes to be printed on standard output as they are evaluated.
    This feature is used for debugging SQLite.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 
    information.</p>




}

Pragma vdbe_listing {
    <p><b>PRAGMA vdbe_listing = </b><i>boolean</i><b>;</b></p>

    <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
    option, then the vdbe_listing pragma can be used to cause a complete
    listing of the virtual machine opcodes to appear on standard output
    as each statement is evaluated.
    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 feature is used for debugging SQLite itself.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 
    information.</p>




}

Pragma wal_checkpoint {
    <p><b>PRAGMA </b><i>database</i><b>.wal_checkpoint;</b></p>

    <p>^If the [write-ahead log] is enabled (via the [journal_mode pragma]),
    this pragma causes a checkpoint operation to run on database
Changes to pages/support.in.
8
9
10
11
12
13
14
15

16
17
18
19




20
21
22
23
24
25
26
27
<p>
If you would like professional support for SQLite
or if you want custom modifications performed by the
original author of SQLite, these services are available for a modest fee.
For additional information visit
[http://www.hwaci.com/sw/sqlite/prosupport.html] or contact:</p>

<blockquote>

D. Richard Hipp <br />
Hwaci - Applied Software Research <br />
704.948.4565 <br />
<a href="mailto:drh@hwaci.com">drh@hwaci.com</a>




</blockquote>

<h3>Proprietary SQLite Extensions</h3>

<p>The core SQLite library found on this website is in the
<a href="copyright.html">public domain</a>.  But there also exist
proprietary, licensed extensions to SQLite, written and maintained
by the original developer.</p>







|
>




>
>
>
>
|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<p>
If you would like professional support for SQLite
or if you want custom modifications performed by the
original author of SQLite, these services are available for a modest fee.
For additional information visit
[http://www.hwaci.com/sw/sqlite/prosupport.html] or contact:</p>

<table border="0" cellpadding="15">
<tr><td valign="top">
D. Richard Hipp <br />
Hwaci - Applied Software Research <br />
704.948.4565 <br />
<a href="mailto:drh@hwaci.com">drh@hwaci.com</a>
</td><td valign="top">
<form method="GET" action="http://www.hwaci.com/sw/sqlite/prosupport.html">
<input type="submit" value="More Info"></form>
</td></tr>
</table>

<h3>Proprietary SQLite Extensions</h3>

<p>The core SQLite library found on this website is in the
<a href="copyright.html">public domain</a>.  But there also exist
proprietary, licensed extensions to SQLite, written and maintained
by the original developer.</p>