Documentation Source Text

Check-in [e3dca045a2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Further updates to pragma documentation. Mark deprecated pragmas as such. Tag pragmas used for debugging only.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e3dca045a230ff1e713e5291d0f781bf41b42b56
User & Date: drh 2010-11-26 15:18:09
Context
2010-11-26
18:23
Update documentation for fts3 matchinfo function. check-in: 4deb70dd76 user: dan tags: trunk
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/pragma.in.

20
21
22
23
24
25
26

















27
28
29

30
31
32







33
34
35
36
37
38
39
...
207
208
209
210
211
212
213
214
215
216



217
218
219
220
221
222
223
...
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
261
262
263
264
265
266
267
268
269
270
271
272
...
317
318
319
320
321
322
323



324
325
326
327
328
329
330
...
697
698
699
700
701
702
703



704
705
706
707
708
709
710
...
805
806
807
808
809
810
811
812

813
814
815


816
817

818
819




820
821
822
823
824
825
826
...
843
844
845
846
847
848
849
850
851
852
853

854
855
856
857



858
859
860
861
862
863
864
...
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
....
1017
1018
1019
1020
1021
1022
1023

















1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034





1035

1036
1037
1038
1039
1040
1041









1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
  set main_name [lindex $namelist 0]
  set PragmaBody($main_name) $content
  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
  }
}

















# Legacy pragma
proc LegacyPragma {namelist content} {
  Pragma $namelist $content

  global PragmaLegacy
  foreach x $namelist {set PragmaLegacy($x) 1}
}







</tcl>

<p>The PRAGMA statement is a SQL extension specific to SQLite and used to 
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
................................................................................
    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>



    <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
................................................................................
    that fired the trigger, whereas [sqlite3_changes()] and
    [sqlite3_total_changes()] do not.
}

LegacyPragma default_cache_size {
    ^(<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.
    ^The value of the default cache size is stored in the 4-byte
    big-endian integer located at offset 48 in the header of the
    database file.
    </p>
}

LegacyPragma empty_result_callbacks {
    <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()] 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>
}   
    

Pragma encoding {
   <p>^(<b>PRAGMA encoding;
       <br>PRAGMA encoding = "UTF-8";
       <br>PRAGMA encoding = "UTF-16";
................................................................................
       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>



    <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>
................................................................................
    all.
    </p>
}

LegacyPragma short_column_names {
    <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>
}

Pragma synchronous {
................................................................................
    </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 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>
................................................................................
    database connection.</p>)^
}

Pragma database_list {
    <p>^(<b>PRAGMA database_list;</b></p>
    <p>This pragma works like a query to return one row for each database
    attached to the current database connection.)^
    ^(Columns of the result set include the index and 
    the name the database was attached with.)^  ^The first row will be for 
    the main database.  ^The second row will be for the database used to 
    store temporary tables.</p>

}

LegacyPragma foreign_key_list {
    <p>^(<b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>



    <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.)^ ^Running
................................................................................
    <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>
}

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

}


















Section {List Of PRAGMAs} {toc} {{pragma list}}
</tcl>
<table border=0 width="100%" cellpadding=10>
<tr><td valign="top" align="left"><ul>
<tcl>
set allprag [lsort [array names PragmaRef]]
set nprag [llength $allprag]
set nrow [expr {($nprag+2)/3}]
for {set i 0} {$i<$nprag} {incr i} {
  set prag [lindex $allprag $i]





  hd_puts "<li><a href=\"#pragma_$prag\">$prag</a>\n"

  if {$i%$nrow==($nrow-1) && $i+1<$nprag} {
    hd_puts "</ul></td><td valign=\"top\" align=\"left\"><ul>\n"
  }
}
</tcl>
</ul></td></tr></table>









<tcl>
foreach prag [lsort [array names PragmaBody]] {
  hd_fragment pragma_$prag
  foreach x $PragmaKeys($prag) {
    hd_keywords *$x "PRAGMA $x" "$x pragma"
  }
  hd_puts "<hr>"
  hd_resolve $PragmaBody($prag)
}
</tcl>
<hr>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|

<
>



>
>
>
>
>
>
>







 







|


>
>
>







 







>
>
>













>
>











<
<
<
<







 







>
>
>







 







>
>
>







 







|
>
|
<

>
>

|
>
|
<
>
>
>
>







 







|
|
|
|
>




>
>
>







 







|

>
>







|

>
>









|

>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>











>
>
>
>
>
|
>






>
>
>
>
>
>
>
>
>











20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
...
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
...
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
...
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
...
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
...
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
...
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
...
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
....
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
  set main_name [lindex $namelist 0]
  set PragmaBody($main_name) $content
  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
  }
}
proc LegacyDisclaimer {} {
  return {
    <p><u>Do not use this pragma!</u> This pragma is deprecated and exists
    for backwards compatibility only.  New applications
    should avoid using this pragma.  Older applications should discontinue
    use of this pragma at the earliest opportunity.  This pragma may be omitted
    from the build when SQLite is compiled using [SQLITE_OMIT_DEPRECATED].
    </p>
  }
}
proc DebugDisclaimer {} {
  return {
    <p>This pragma is intended for use when debugging SQLite itself.  It
    is only contained in the build when the [SQLITE_DEBUG] compile-time option
    is used.</p>
  }
}
# Legacy pragma - do not use these
proc LegacyPragma {namelist content} {

  Pragma $namelist [string map [list DISCLAIMER [LegacyDisclaimer]] $content]
  global PragmaLegacy
  foreach x $namelist {set PragmaLegacy($x) 1}
}
# Debugging pragmas
proc DebugPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [DebugDisclaimer]] $content]
  global PragmaDebug
  foreach x $namelist {set PragmaDebug($x) 1}
}

</tcl>

<p>The PRAGMA statement is a SQL extension specific to SQLite and used to 
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
................................................................................
    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>
}

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
................................................................................
    that fired the trigger, whereas [sqlite3_changes()] and
    [sqlite3_total_changes()] do not.
}

LegacyPragma default_cache_size {
    ^(<b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>

    DISCLAIMER

    <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.
    ^The value of the default cache size is stored in the 4-byte
    big-endian integer located at offset 48 in the header of the
    database file.
    </p>
}

LegacyPragma empty_result_callbacks {
    <p><b>PRAGMA empty_result_callbacks;
       <br>PRAGMA empty_result_callbacks = </b><i>boolean</i><b>;</b></p>

    DISCLAIMER

    <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()] 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>




}   
    

Pragma encoding {
   <p>^(<b>PRAGMA encoding;
       <br>PRAGMA encoding = "UTF-8";
       <br>PRAGMA encoding = "UTF-16";
................................................................................
       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>
................................................................................
    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 {
................................................................................
    </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>
................................................................................
    database connection.</p>)^
}

Pragma database_list {
    <p>^(<b>PRAGMA database_list;</b></p>
    <p>This pragma works like a query to return one row for each database
    attached to the current database connection.)^
    ^(The second column is the "main" for the main database file, "temp"
    for the database file used to store TEMP objects, or the name of the
    ATTACHed database for other database files.)^
    ^(The third column is the name of the database file itself, or an empty
    string if the database is not associated with a file.)^</p>
}

LegacyPragma foreign_key_list {
    <p>^(<b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>

    DISCLAIMER

    <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.)^ ^Running
................................................................................
    <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>
}

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

    DISCLAIMER

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

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

    DISCLAIMER

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

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

    DISCLAIMER

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

}

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.)^
    ^The default setting is off, meaning that CHECK constraints are
    enforced by default.</p>
}

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

    <p>When this pragma is on, the SQLITE_MASTER tables in which database
    can be changed using ordinary [UPDATE], [INSERT], and [DELETE]
    statements.  ^Warning:  misuse of this pragma can easily result in
    a corrupt database file.</p>
}

Section {List Of PRAGMAs} {toc} {{pragma list}}
</tcl>
<table border=0 width="100%" cellpadding=10>
<tr><td valign="top" align="left"><ul>
<tcl>
set allprag [lsort [array names PragmaRef]]
set nprag [llength $allprag]
set nrow [expr {($nprag+2)/3}]
for {set i 0} {$i<$nprag} {incr i} {
  set prag [lindex $allprag $i]
  if {[info exists PragmaLegacy($prag)]} {
    hd_puts "<li><a href=\"#pragma_$prag\"><s>$prag</s></a>&sup1;\n"
  } elseif {[info exists PragmaDebug($prag)]} {
    hd_puts "<li><a href=\"#pragma_$prag\"><i>$prag</i></a>&sup2;\n"
  } else {
    hd_puts "<li><a href=\"#pragma_$prag\">$prag</a>\n"
  }
  if {$i%$nrow==($nrow-1) && $i+1<$nprag} {
    hd_puts "</ul></td><td valign=\"top\" align=\"left\"><ul>\n"
  }
}
</tcl>
</ul></td></tr></table>
<p>Notes:
<ol>
<li>Pragmas whose names are marked through in the list above
are deprecated that are maintained for historical compatibility only.
Do not use the deprecated pragmas in new applications.
Remove deprecated pragmas
from existing applications at your earliest opportunity.</blockquote>
<li>These pragmas are used for debugging SQLite itself and
are only available which SQLite is compiled using [SQLITE_DEBUG].</ol></p>
<tcl>
foreach prag [lsort [array names PragmaBody]] {
  hd_fragment pragma_$prag
  foreach x $PragmaKeys($prag) {
    hd_keywords *$x "PRAGMA $x" "$x pragma"
  }
  hd_puts "<hr>"
  hd_resolve $PragmaBody($prag)
}
</tcl>
<hr>