Documentation Source Text

Check-in [a900d49554]
Login

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

Overview
Comment:Add requirements markings to the pragma documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a900d49554caed68bfa97874a5d04ec418869238
User & Date: drh 2009-12-15 00:29:58
Context
2009-12-15
14:05
Fix a typo in the privatebranch.html document. check-in: 04a7a8e7dd user: drh tags: trunk
00:29
Add requirements markings to the pragma documentation. check-in: a900d49554 user: drh tags: trunk
2009-12-14
23:00
Identify requirements in the autoinc.html and datatype3.html documents. Make corrections in datatype3.html for accuracy and for clearer presentation. check-in: e18ce3ebd0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/pragma.in.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
..
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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
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
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
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
325
326
327
328
329
330
331
332
333
334
335
336
337
338
...
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
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
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
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
540
541
542
543
544
545
546
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
...
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
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
...
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
...
762
763
764
765
766
767
768
769
770
771
772
773
774
775

776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793

794
795
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
830
831
832
833
834
835
836
837
838
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
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
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:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. There is no guarantee of backwards compatiblity.
<li>No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language [sqlite3_prepare()], 
    [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
    interface), the pragma may run during the [sqlite3_prepare()] call,
    not during the [sqlite3_step()] call as normal SQL statements do.
    Or the pragma might run during sqlite3_step() just like normal
    SQL statements.  Whether or not the pragma runs during sqlite3_prepare()
    or sqlite3_step() depends on the pragma and on the specific release
    of SQLite.
<li>The pragma command is specific to SQLite and is very unlikely 
    to be compatible with any other SQL database engine.
</ul>

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

<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>0 | NONE | 1 | FULL | 2 | INCREMENTAL</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 
    pages are added to a "freelist" and reused for subsequent inserts.  So
    no database file space is lost.  However, the database file does not
    shrink.  In this mode the [VACUUM]
    command can be used to rebuild the entire database file and
    thus reclaim unused disk space.</p>

    <p>When the auto-vacuum mode is 1  or "full", the freelist pages are
    moved to the end of the database file and the database file is truncated
    to remove the freelist pages at every transaction commit.
    Note, however, that auto-vacuum only truncates the freelist pages
    from the file.  Auto-vacuum does not defragment the database nor
    repack individual database pages the way that the
    [VACUUM] command does.  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>

    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referer.  Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>When the value of auto-vacuum is 2 or "incremental" then the additional
    information needed to do auto-vacuuming is stored in the database file
    but auto-vacuuming does not occur automatically at each commit as it
    does with auto_vacuum=full.  In incremental mode, the separate
    [incremental_vacuum] pragma must
    be invoked to cause the auto-vacuum to occur.</p>

    <p>The database connection can be changed between full and incremental
    autovacuum mode at any time.  However, the connection can only be changed
    "none" to "full" or "incremental" when the database is empty (no tables
    have yet been created) or by running the [VACUUM] command.  To
    change auto-vacuum modes, first use the auto_vacuum pragma to set
    the new desired mode, then invoke the [VACUUM] command to 
    reorganize the entire database file.  To change from "full" or
    "incremental" back to "none" always requires running [VACUUM] even
    on an empty database.
    </p>

    <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].   In the
    default page cache implemention, the suggested cache size is honored
    as long as it is 10 or greater.  A suggested cache size of less
    than 10 are treated as if it were 10.
    Alternative application-defined page cache implementations
    may choose to interpret the suggested cache size in different ways
    or to ignore it all together.
    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>

    <p>This pragma only works if the [like | built-in like() SQL function]
    has not been overloaded using [sqlite3_create_function()].</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, or any changes made automatically
    by [foreign key actions].</p>

    <p>Another way to get the row change counts is to use the
    [sqlite3_changes()] or [sqlite3_total_changes()] interfaces.
    There is a subtle different, though.  When an INSERT, UPDATE, or
    DELETE is run against a view using an [INSTEAD OF trigger],
    the count_changes pragma reports the number of rows in the view
    that fired the trigger, whereas [sqlite3_changes()] and
    [sqlite3_total_changes()] do not.</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>
    <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>
</li>

<tcl>Subsection foreign_keys</tcl>
<li><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].

    <p>This pragma is a no-op within a transaction.  Foreign key constraint
       enforcement may only be enabled or disabled when there is no pending
       [BEGIN] or [SAVEPOINT].

    <p>Changing this setting affects the execution of all statements prepared

       using the database connection, including those prepared before the
       setting was changed. Any existing statements prepared using the legacy 
       [sqlite3_prepare()] interface may fail with an [SQLITE_SCHEMA] error
       after this setting is changed.

    <p>As of SQLite [version 3.6.19], the default setting for foreign
       key enforcement is OFF.  However, that might change in a future
       release of SQLite.  To minimize future problems, applications should
       set the foreign key enforcement flag as required by the application
       and not depend on the default setting.
</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.
        </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
................................................................................
    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.
    The default journaling mode is the mode used by databases added
    to the connection by subsequent [ATTACH] statements.  The second
    form returns the current journaling mode for a specific database.</p>

    <p>The last two forms change the journaling mode.  The 4th form
    changes the journaling mode for a specific database connection.
    Use "main" for the main database (the database that was opened by
    the original [sqlite3_open()], [sqlite3_open16()], or
    [sqlite3_open_v2()] interface call) and use "temp" for database
    that holds TEMP tables.  The 3rd form changes the journaling mode
    on all databases and it changes the default journaling mode that
    will be used for new databases added by subsequent [ATTACH]
    commands.  The new journal mode is returned.  If the journal mode
    could not be changed, the original journal mode is returned.</p>

    <p>The DELETE journaling mode is the normal behavior.  In the DELETE
    mode, the rollback journal is deleted at the conclusion of each
    transaction.  Indeed, the delete operation is the action that causes
    the transaction to commit.
    (See the documented titled <a href="atomiccommit.html">
    Atomic Commit In SQLite</a> for additional detail.)</p>

    <p>The TRUNCATE journaling mode commits transactions by truncating
    the rollback journal to zero-length instead of deleting it.  On many
    systems, truncating a file is much faster than deleting the file since
    the containing directory does not need to be changed.</p>

    <p>The PERSIST journaling mode prevents the rollback journal from
    being deleted at the end of each transaction.  Instead, the header
    of the journal is overwritten with zeros.  This will prevent other
    database connections from rolling the journal back.  The PERSIST
    journaling mode is useful as an optimization on platforms where
    deleting or truncating a file is much more expensive than overwriting
    the first block of a file with zeros.</p>

    <p>The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  This saves disk I/O but that the expense of database
    safety and integrity.  If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely go corrupt.</p>

    <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; it behaves in an undefined way.  Applications must
    avoid using the [ROLLBACK] command when the journal mode is OFF.
    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 ignored.  Note also that
    the journal_mode cannot be changed while a transaction is active.</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>

  <p>If a database connection is operating in either "exclusive mode"
  (PRAGMA locking_mode=exclusive) or "persistent journal mode"
  (PRAGMA journal_mode=persist) then under certain circumstances
  after committing a transaction the journal file may remain in
  the file-system. This increases efficiency but also consumes
  space in the file-system. After a large transaction (e.g. a VACUUM),
  it may consume a very large amount of space.

  <p>This pragma may be used to limit the size of journal files left
  in the file-system after transactions are committed on a per database
  basis. Each time a transaction is committed, SQLite compares the
  size of the journal file left in the file-system to the size limit
  configured using this pragma. If the journal file is larger than the
  limit allows for, it is truncated to the limit.

  <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
    effect on databases that already exist.</p>

    <p>The default file format is set by the
    [SQLITE_DEFAULT_FILE_FORMAT] compile-time option.</p>
</li>

<tcl>Subsection locking_mode</tcl>
<li><p><b>PRAGMA locking_mode;
       <br>PRAGMA locking_mode = <i>NORMAL | EXCLUSIVE</i></b></p>
    <p>This pragma sets or queries the database connection locking-mode. 
    The locking-mode is either NORMAL or EXCLUSIVE.

    <p>In NORMAL locking-mode (the default), a database connection
    unlocks the database file at the conclusion of each read or
    write transaction. When the locking-mode is set to EXCLUSIVE, the
    database connection never releases file-locks. The first time the
    database is read in EXCLUSIVE mode, a shared lock is obtained and 
    held. The first time the database is written, an exclusive lock is
    obtained and held.</p>

    <p>Database locks obtained by a connection in EXCLUSIVE mode may be
    released either by closing the database connection, or by setting the
    locking-mode back to NORMAL using this pragma and then accessing the
    database file (for read or write). Simply setting the locking-mode to
    NORMAL is not enough - locks are not be released until the next time
    the database file is accessed.</p>

    <p>There are two reasons to set the locking-mode to EXCLUSIVE. One
    is if the application actually wants to prevent other processes from
    accessing the database file. The other is that a small number of
    filesystem operations are saved by optimizations enabled in this
    mode. This may be significant in embedded environments.</p>

    <p>When the locking_mode pragma specifies a particular database,
    for example:</p>

    <blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
    </blockquote>

    <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>
    <p>Query or set the page size of the database. The page size
    may only be set if the database has not yet been created. The page
    size must be a power of two greater than or equal to 512 and less
    than or equal to [SQLITE_MAX_PAGE_SIZE].
    The maximum value for [SQLITE_MAX_PAGE_SIZE] is 32768.
    </p>

    <p>When a new database is created, SQLite assigned a default page size
    based on information received from the xSectorSize and 
    xDeviceCharacteristics methods of the [sqlite3_io_methods] object
    of the newly created database file.  The page_size pragma will only
    cause an immediate change in the
    page size if it is issued while the database is still empty, prior 
    to the first CREATE TABLE statement.  As of [version 3.5.8], if
    the page_size pragma is used to specify a new page size just prior to
    running the [VACUUM] command then [VACUUM] will change the page
    size to the new value.</p>

    <p>If SQLite is compiled with the SQLITE_ENABLE_ATOMIC_WRITE option,
    then the default page size is chosen to be the largest page size
    less than or equal to SQLITE_MAX_DEFAULT_PAGE_SIZE for which atomic
    write is enabled according to the
    xDeviceCharacteristics method of the [sqlite3_io_methods] object for
    the database file.  If the SQLITE_ENABLE_ATOMIC_WRITE option is
    disabled or if xDeviceCharacteristics reports no suitable atomic
    write page sizes, then the default page size is the larger of 
    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 32768, 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>
</li>

<tcl>Subsection max_page_count</tcl>
<li><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
    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 recursive_triggers</tcl>
<li><p><b>PRAGMA recursive_triggers;
       <br>PRAGMA recursive_triggers = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear the recursive trigger capability.


    <p>Changing this setting affects the execution of all statements prepared
       using the database connection, including those prepared before the
       setting was changed. Any existing statements prepared using the legacy 
       [sqlite3_prepare()] interface may fail with an [SQLITE_SCHEMA] error
       after this setting is changed.


    <p>Prior to SQLite version 3.6.18, recursive triggers were not
    supported.  The behavior of SQLite was always as if this pragma was
    set to OFF.  Support for recursive triggers was added in version 3.6.18
    but was initially turned OFF by default, for compatibility.  Beginning
    with SQLite version 3.7.0, recursive triggers are turned ON by default.    
    </p>

    <p>The depth of recursion for triggers has a hard upper limit set by
    the [SQLITE_MAX_TRIGGER_DEPTH] compile-time option and a run-time
    limit set by [sqlite3_limit](db,[SQLITE_LIMIT_TRIGGER_DEPTH],...).</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
    on the arbitrary output order whatever that order happens to be.  However, 
................................................................................
    application makes faulty assumptions about output order can be
    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.
    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>

    <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
    the interaction of the [SQLITE_TEMP_STORE] preprocessor macro and the
    temp_store pragma:</p>

    <blockquote>
    <table cellpadding="2" border="1">
    <tr><th valign="bottom">[SQLITE_TEMP_STORE]</th>
        <th valign="bottom">PRAGMA<br>temp_store</th>
................................................................................
    <tr><td align="center">2</td>
        <td align="center">2</td>
        <td align="center">memory</td></tr>
    <tr><td align="center">3</td>
        <td align="center"><em>any</em></td>
        <td align="center">memory</td></tr>
    </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.</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 collation_list</tcl>
<li><p><b>PRAGMA collation_list;</b></p>
    <p>Return a list of the collating sequences defined for the current
    database connection.</p></li>

<tcl>Subsection database_list</tcl>
<li><p><b>PRAGMA database_list;</b></p>
    <p>For each open database, invoke the callback function once with

    information about that database.  Arguments 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></li>

<tcl>Subsection foreign_key_list</tcl>
<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>
  
<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>
</li>
</ul>

<tcl>Section {Pragmas to debug the library} debug</tcl>

<ul>
<tcl>Subsection integrity_check</tcl>
<li><p><b>PRAGMA integrity_check;
    <br>PRAGMA integrity_check(</b><i>integer</i><b>)</b></p>
    <p>This pragma does an integrity check of the entire database.  It
    looks for out-of-order records, missing pages, malformed records, and
    corrupt indices.
    If any problems are found, then strings are returned (as multiple
    rows with a single column per row) which describe
    the problems.  At most <i>integer</i> errors will be reported
    before the analysis quits.  The default value for <i>integer</i>
    is 100.  If no errors are found, a single row with the value "ok" is
    returned.</p></li>

<tcl>Subsection quick_check</tcl>
<li><p><b>PRAGMA quick_check;
    <br>PRAGMA quick_check(</b><i>integer</i><b>)</b></p>
    <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>







|


|




|







 







|

|
|




|

|







|
|
|
|













|

|
|
|


|



|


|


|





|



|


|



|
|
|
|


|




|




|


|

|

|

|


|

|
|





|
|
|
|

|


|




|

|

|








|






|


|
|








|



|
|












|



|
|


|



|
|

|
|



|

|




|

|

|



|
>

|

|

|
|







|

|

|
|







 







|



|

|

|
|





|
|
|


|







 







|







|

|
|
|
|


|

|


|


|


|






|




|

|





|
|
|



|
|
|



|



|

|
|








|







|

|

|


|
|
|

|



|








|


|

|



|
|
|


|
|

|




|
|
|
|

|

|
|

|


|


|









|






|




|

|

|




|

|
|





|


|


|


|

|




|





|



|


|



|

|
|
|





|

|
|










|

|

>
|

|

<
>




|
|


|

|



|
|
|







 







|

|






|



|
|
|





|






|







|





|



|
|

|


|
|

|



|

|







 







|




|



|

|







|




|
|







 







|

|


|
|
>
|
|
|



|
|
|
<
<


|
|


<
>


|
<
<
<
<
>


|
<
<
>
>
|
|


|
|


|
|
<
>







 







|
|



|

|



|



|










|


|

|
|
|





|








|








|







|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
..
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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
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
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
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
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
...
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
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
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
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
540
541
542
543
544
545
546
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
...
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
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
...
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
762
763
...
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787


788
789
790
791
792
793

794
795
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
830
831
832
833
834
835
836
837
838
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
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
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:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. There is no guarantee of backwards compatiblity.
<li>^No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>^Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language [sqlite3_prepare()], 
    [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
    interface), the pragma may run during the [sqlite3_prepare()] call,
    not during the [sqlite3_step()] call as normal SQL statements do.
    ^Or the pragma might run during sqlite3_step() just like normal
    SQL statements.  Whether or not the pragma runs during sqlite3_prepare()
    or sqlite3_step() depends on the pragma and on the specific release
    of SQLite.
<li>The pragma command is specific to SQLite and is very unlikely 
    to be compatible with any other SQL database engine.
</ul>

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

<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>0 | NONE | 1 | FULL | 2 | INCREMENTAL</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 
    pages are added to a "freelist" and reused for subsequent inserts.  So
    no database file space is lost.  However, the database file does not
    shrink.  ^In this mode the [VACUUM]
    command can be used to rebuild the entire database file and
    thus reclaim unused disk space.</p>

    <p>^When the auto-vacuum mode is 1  or "full", the freelist pages are
    moved to the end of the database file and the database file is truncated
    to remove the freelist pages at every transaction commit.
    ^(Note, however, that auto-vacuum only truncates the freelist pages
    from the file.  Auto-vacuum does not defragment the database nor
    repack individual database pages the way that the
    [VACUUM] command does.)^  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>

    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referer.  ^Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>^When the value of auto-vacuum is 2 or "incremental" then the additional
    information needed to do auto-vacuuming is stored in the database file
    but auto-vacuuming does not occur automatically at each commit as it
    does with auto_vacuum=full.  ^In incremental mode, the separate
    [incremental_vacuum] pragma must
    be invoked to cause the auto-vacuum to occur.</p>

    <p>^The database connection can be changed between full and incremental
    autovacuum mode at any time.  ^However, changing from
    "none" to "full" or "incremental" when the database is new (no tables
    have yet been created) or by running the [VACUUM] command.  ^To
    change auto-vacuum modes, first use the auto_vacuum pragma to set
    the new desired mode, then invoke the [VACUUM] command to 
    reorganize the entire database file.  ^To change from "full" or
    "incremental" back to "none" always requires running [VACUUM] even
    on an empty database.
    </p>

    <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].   ^In the
    default page cache implemention, the suggested cache size is honored
    as long as it is 10 or greater.  ^A suggested cache size of less
    than 10 are treated as if it were 10.
    ^Alternative application-defined page cache implementations
    may choose to interpret the suggested cache size in different ways
    or to ignore it all together.
    ^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>

    <p>^This pragma only works if the [like | built-in like() SQL function]
    has not been overloaded using [sqlite3_create_function()].</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, or any changes made automatically
    by [foreign key actions].</p>

    <p>Another way to get the row change counts is to use the
    [sqlite3_changes()] or [sqlite3_total_changes()] interfaces.
    There is a subtle different, though.  ^When an INSERT, UPDATE, or
    DELETE is run against a view using an [INSTEAD OF trigger],
    the count_changes pragma reports the number of rows in the view
    that fired the trigger, whereas [sqlite3_changes()] and
    [sqlite3_total_changes()] do not.</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()] 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>
    <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>
</li>

<tcl>Subsection foreign_keys</tcl>
<li><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].)^

    <p>^This pragma is a no-op within a transaction; foreign key constraint
       enforcement may only be enabled or disabled when there is no pending
       [BEGIN] or [SAVEPOINT].

    <p>^Changing the foreign_keys setting affects the execution of
       all statements prepared
       using the database connection, including those prepared before the
       setting was changed. ^Any existing statements prepared using the legacy 
       [sqlite3_prepare()] interface may fail with an [SQLITE_SCHEMA] error
       after the foreign_keys setting is changed.

    <p>^(As of SQLite [version 3.6.19], the default setting for foreign
       key enforcement is OFF.)^  However, that might change in a future
       release of SQLite.  To minimize future problems, applications should
       set the foreign key enforcement flag as required by the application
       and not depend on the default setting.
</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 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>
    <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.
        </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 of the fullfsync flag
    is off.  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
................................................................................
    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.
    ^The default journaling mode is the mode used by databases added
    to the connection by subsequent [ATTACH] statements.  ^The second
    form returns the current journaling mode for a specific database.</p>

    <p>^The last two forms change the journaling mode.  ^The 4th form
    changes the journaling mode for a specific database connection.
    ^Use "main" for the main database (the database that was opened by
    the original [sqlite3_open()], [sqlite3_open16()], or
    [sqlite3_open_v2()] interface call) and use "temp" for database
    that holds TEMP tables.  ^The 3rd form changes the journaling mode
    on all databases and it changes the default journaling mode that
    will be used for new databases added by subsequent [ATTACH]
    commands.  ^The new journal mode is returned.  ^If the journal mode
    could not be changed, the original journal mode is returned.</p>

    <p>^The DELETE journaling mode is the normal behavior.  ^In the DELETE
    mode, the rollback journal is deleted at the conclusion of each
    transaction.  Indeed, the delete operation is the action that causes
    the transaction to commit.
    (See the documented titled <a href="atomiccommit.html">
    Atomic Commit In SQLite</a> for additional detail.)</p>

    <p>^The TRUNCATE journaling mode commits transactions by truncating
    the rollback journal to zero-length instead of deleting it.  On many
    systems, truncating a file is much faster than deleting the file since
    the containing directory does not need to be changed.</p>

    <p>^(The PERSIST journaling mode prevents the rollback journal from
    being deleted at the end of each transaction.  Instead, the header
    of the journal is overwritten with zeros.)^  This will prevent other
    database connections from rolling the journal back.  The PERSIST
    journaling mode is useful as an optimization on platforms where
    deleting or truncating a file is much more expensive than overwriting
    the first block of a file with zeros.</p>

    <p>^The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  ^This saves disk I/O but at the expense of database
    safety and integrity.  ^If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely go corrupt.</p>

    <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; it behaves in an undefined way.  Applications must
    avoid using the [ROLLBACK] command when the journal mode is OFF.
    ^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 ignored.  ^Note also that
    the journal_mode cannot be changed while a transaction is active.</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>

  <p>^If a database connection is operating in either "exclusive mode"
  (PRAGMA locking_mode=exclusive) or "persistent journal mode"
  (PRAGMA journal_mode=persist) then under certain circumstances
  after committing a transaction the journal file may remain in
  the file-system. This increases efficiency but also consumes
  space in the file-system. After a large transaction (e.g. a VACUUM),
  it may consume a very large amount of space.

  <p>^This pragma may be used to limit the size of journal files left
  in the file-system after transactions are committed on a per database
  basis.  ^Each time a transaction is committed, SQLite compares the
  size of the journal file left in the file-system to the size limit
  configured using this pragma and if the journal file is larger than the
  limit allows for, it is truncated to the limit.

  <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 legacy_file_format 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
    effect on databases that already exist.)^</p>

    <p>^The default file format is set by the
    [SQLITE_DEFAULT_FILE_FORMAT] compile-time option.</p>
</li>

<tcl>Subsection locking_mode</tcl>
<li><p>^(<b>PRAGMA locking_mode;
       <br>PRAGMA locking_mode = <i>NORMAL | EXCLUSIVE</i></b>)^</p>
    <p>^This pragma sets or queries the database connection locking-mode. 
    ^The locking-mode is either NORMAL or EXCLUSIVE.

    <p>^In NORMAL locking-mode (the default), a database connection
    unlocks the database file at the conclusion of each read or
    write transaction. ^When the locking-mode is set to EXCLUSIVE, the
    database connection never releases file-locks. ^The first time the
    database is read in EXCLUSIVE mode, a shared lock is obtained and 
    held. ^The first time the database is written, an exclusive lock is
    obtained and held.</p>

    <p>^Database locks obtained by a connection in EXCLUSIVE mode may be
    released either by closing the database connection, or by setting the
    locking-mode back to NORMAL using this pragma and then accessing the
    database file (for read or write). ^Simply setting the locking-mode to
    NORMAL is not enough - locks are not be released until the next time
    the database file is accessed.</p>

    <p>There are two reasons to set the locking-mode to EXCLUSIVE. One
    is if the application actually wants to prevent other processes from
    accessing the database file. The other is that a small number of
    filesystem operations are saved by optimizations enabled in this
    mode. This may be significant in embedded environments.</p>

    <p>^(When the locking_mode pragma specifies a particular database,
    for example:</p>

    <blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
    </blockquote>

    <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>
    <p>Query or set the page size of the database.)^ ^The page size
    may only be set if the database has not yet been created. ^The page
    size must be a power of two greater than or equal to 512 and less
    than or equal to [SQLITE_MAX_PAGE_SIZE].
    The maximum value for [SQLITE_MAX_PAGE_SIZE] is 32768.
    </p>

    <p>^When a new database is created, SQLite assigned a default page size
    based on information received from the xSectorSize and 
    xDeviceCharacteristics methods of the [sqlite3_io_methods] object
    of the newly created database file.  ^The page_size pragma will only
    cause an immediate change in the
    page size if it is issued while the database is still empty, prior 
    to the first CREATE TABLE statement.  ^(As of [version 3.5.8], if
    the page_size pragma is used to specify a new page size just prior to
    running the [VACUUM] command then [VACUUM] will change the page
    size to the new value.)^</p>

    <p>^If SQLite is compiled with the SQLITE_ENABLE_ATOMIC_WRITE option,
    then the default page size is chosen to be the largest page size
    less than or equal to SQLITE_MAX_DEFAULT_PAGE_SIZE for which atomic
    write is enabled according to the
    xDeviceCharacteristics method of the [sqlite3_io_methods] object for
    the database file.  ^If the SQLITE_ENABLE_ATOMIC_WRITE option is
    disabled or if xDeviceCharacteristics reports no suitable atomic
    write page sizes, then the default page size is the larger of 
    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 32768, 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>
</li>

<tcl>Subsection max_page_count</tcl>
<li><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
    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 recursive_triggers</tcl>
<li><p>^(<b>PRAGMA recursive_triggers;
       <br>PRAGMA recursive_triggers = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear the recursive trigger capability.)^

    <p>^Changing the recursive_triggers setting affects the execution of
       all statements prepared
       using the database connection, including those prepared before the
       setting was changed. ^Any existing statements prepared using the legacy 
       [sqlite3_prepare()] interface may fail with an [SQLITE_SCHEMA] error

       after the recursive_triggers setting is changed.

    <p>Prior to SQLite version 3.6.18, recursive triggers were not
    supported.  The behavior of SQLite was always as if this pragma was
    set to OFF.  Support for recursive triggers was added in version 3.6.18
    but was initially turned OFF by default, for compatibility.  Recursive
    triggers may be turned on by default in future versions of SQLite.
    </p>

    <p>^(The depth of recursion for triggers has a hard upper limit set by
    the [SQLITE_MAX_TRIGGER_DEPTH] compile-time option and a run-time
    limit set by [sqlite3_limit](db,[SQLITE_LIMIT_TRIGGER_DEPTH],...).)^</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
    on the arbitrary output order whatever that order happens to be.  However, 
................................................................................
    application makes faulty assumptions about output order can be
    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.
    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>

    <p>Query or change the setting of the "synchronous" flag.)^
    ^The first (query) form will return the synchronous 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 (1), 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
    the interaction of the [SQLITE_TEMP_STORE] preprocessor macro and the
    temp_store pragma:</p>

    <blockquote>
    <table cellpadding="2" border="1">
    <tr><th valign="bottom">[SQLITE_TEMP_STORE]</th>
        <th valign="bottom">PRAGMA<br>temp_store</th>
................................................................................
    <tr><td align="center">2</td>
        <td align="center">2</td>
        <td align="center">memory</td></tr>
    <tr><td align="center">3</td>
        <td align="center"><em>any</em></td>
        <td align="center">memory</td></tr>
    </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.</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 collation_list</tcl>
<li><p>^(<b>PRAGMA collation_list;</b></p>
    <p>Return a list of the collating sequences defined for the current
    database connection.</p>)^</li>

<tcl>Subsection database_list</tcl>
<li><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></li>

<tcl>Subsection foreign_key_list</tcl>
<li><p>^(<b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
    <p>This pragma returns one rwo for each foreign key that references
    a column in the argument table.)^</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 when incremental vacuum is enabled. </p></li>

<tcl>Subsection index_info</tcl>
<li><p>^(<b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>




    <p>This program returns one row row each column in the named index.</p>)^

<tcl>Subsection index_list</tcl>
<li><p>^(<b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>


    <p>This pragma returns one row for each index associated with the
    given table.)^   ^Columns of the result set include the
    index name and a flag to indicate whether or not the index is 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>This pragma returns one row for each column in the named table.)^

    ^Columns in the result set include 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>
  
<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>
</li>
</ul>

<tcl>Section {Pragmas to debug the library} debug</tcl>

<ul>
<tcl>Subsection integrity_check</tcl>
<li><p><b>PRAGMA integrity_check;
    <br>PRAGMA integrity_check(</b><i>integer</i><b>)</b></p>
    <p>^This pragma does an integrity check of the entire database.  ^It
    looks for out-of-order records, missing pages, malformed records, and
    corrupt indices.
    ^If any problems are found, then strings are returned (as multiple
    rows with a single column per row) which describe
    the problems.  ^At most <i>integer</i> errors will be reported
    before the analysis quits.  ^The default value for <i>integer</i>
    is 100.  ^If no errors are found, a single row with the value "ok" is
    returned.</p></li>

<tcl>Subsection quick_check</tcl>
<li><p><b>PRAGMA quick_check;
    <br>PRAGMA quick_check(</b><i>integer</i><b>)</b></p>
    <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>