Documentation Source Text

Check-in [3ae5d72cbd]
Login

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

Overview
Comment:Improved sync pragma documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3ae5d72cbdc75a40238e7fd56ff8745d9dc33282
User & Date: drh 2010-11-20 01:45:21.000
Context
2010-11-21
01:36
Updates to documentation on date/time functions and pragmas. (check-in: 2d231c8139 user: drh tags: trunk)
2010-11-20
01:45
Improved sync pragma documentation. (check-in: 3ae5d72cbd user: drh tags: trunk)
2010-11-18
20:42
Fix a typo in the PRAGMA auto_vacuum documentation. (check-in: 8c4c6ab123 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
43
44
45
46
47
48
49
50
51
52
53



54
55
56
57
58
59
60
}

chng {2010 December 08 (3.7.4)} {
<li> Added the [sqlite3_blob_reopen()] interface to allow an existing
     [sqlite3_blob] object to be rebound to a new row.
<li> Use the new [sqlite3_blob_reopen()] interface to improve the performance
     of FTS.
<li> [sqlite3_vfs | VFSes] that does not support shared memory are allowed
     to access [WAL] databases if [PRAGMA locking_mode] is set to EXCLUSIVE.
<li> Enhancements to [EXPLAIN QUERY PLAN].
<li> Added the [sqlite3_stmt_readonly()] interface.



}

chng {2010 October 08 (3.7.3)} {
<li> Added the [sqlite3_create_function_v2()] interface that includes a
     destructor callback.
<li> Added support for [custom r-tree queries] using application-supplied
     callback routines to define the boundary of the query region.







|



>
>
>







43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
}

chng {2010 December 08 (3.7.4)} {
<li> Added the [sqlite3_blob_reopen()] interface to allow an existing
     [sqlite3_blob] object to be rebound to a new row.
<li> Use the new [sqlite3_blob_reopen()] interface to improve the performance
     of FTS.
<li> [sqlite3_vfs | VFSes] that do not support shared memory are allowed
     to access [WAL] databases if [PRAGMA locking_mode] is set to EXCLUSIVE.
<li> Enhancements to [EXPLAIN QUERY PLAN].
<li> Added the [sqlite3_stmt_readonly()] interface.
<li> Added [PRAGMA checkpoint_fullfsync].
<li> Added the [SQLITE_FCNTL_FILE_POINTER] option
     to [sqlite3_file_control()].
}

chng {2010 October 08 (3.7.3)} {
<li> Added the [sqlite3_create_function_v2()] interface that includes a
     destructor callback.
<li> Added support for [custom r-tree queries] using application-supplied
     callback routines to define the boundary of the query region.
Changes to pages/pragma.in.
189
190
191
192
193
194
195














196
197
198
199
200
201
202
    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>
}















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 







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







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

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

    <p>If the [fullfsync] flag is set, then the F_FULLFSYNC syncing
    method is used for all sync operations and the checkpoint_fullfsync
    setting is irrelevant.</p>
}

Pragma count_changes {
    <p>^(<b>PRAGMA count_changes;
       <br>PRAGMA count_changes = </b>boolean</i><b>;</b></p>
    <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 
320
321
322
323
324
325
326
327
328
329
330
331

332
333
334
335
336
337
338
        and source column name:  TABLE.COLUMN</p></li>
    </ol>)^
}

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

}

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







|


|
|
>







334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
        and source column name:  TABLE.COLUMN</p></li>
    </ol>)^
}

Pragma fullfsync {
    <p>^(<b>PRAGMA fullfsync
       <br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p>
    <p>Query or change the fullfsync flag.)^ ^This flag
    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>

    <p>See also [checkpoint_fullfsync].</p>
}

Pragma incremental_vacuum {
    <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
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712









713
714

715
716
717
718
719
720
721
    <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>

}

Pragma temp_store {
    <p>^(<b>PRAGMA temp_store;
       <br>PRAGMA temp_store = </b>
            <i>0 | DEFAULT | 1 | FILE | 2 | MEMORY</i><b>;</b></p>








|
|
|
|
|

|





|







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







701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
    <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
    use the xSync method of the VFS to ensure that all content is safely
    written to the disk surface prior to continuing.
    This ensures that an operating system crash or power failure will
    not corrupt the dartbase.
    FULL synchronous is very safe, but it is also slower. 
    ^When synchronous is NORMAL (1), the SQLite database
    engine will still sync 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 syncing
    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>^In [WAL] mode when synchronous is NORMAL (1), the WAL file is
    synchronized before each [checkpoint] and the database file is
    synchronized after each completed [checkpoint], but no other sync
    operations occur.  ^With synchronous=FULL in WAL mode, an additional
    sync operation of the WAL file happens after each transaction commit.
    If durability is not a concern, then synchronous=NORMAL is normally
    all one needs in WAL mode.</p>

    <p>^The default setting is synchronous=FULL.</p>

    <p>See also the [fullfsync] and [checkpoint_fullfsync] pragmas.</p>
}

Pragma temp_store {
    <p>^(<b>PRAGMA temp_store;
       <br>PRAGMA temp_store = </b>
            <i>0 | DEFAULT | 1 | FILE | 2 | MEMORY</i><b>;</b></p>