Documentation Source Text

Changes On Branch branch-3.12
Login

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

Changes In Branch branch-3.12 Excluding Merge-Ins

This is equivalent to a diff from f343e93d94 to 7c96132d65

2016-05-05
13:33
Merge changes from the 3.12 branch. (check-in: d545903147 user: drh tags: trunk)
13:28
Clarify that using an database file with a link count different from 1 results in undefined behavior. (Leaf check-in: 7c96132d65 user: drh tags: branch-3.12)
2016-04-25
21:58
Clarify restrictions on the use of PRAGMA page_size. (check-in: 44656180a0 user: drh tags: branch-3.12)
2016-04-01
01:05
Add a change log entry for the query planner enhancement backport to 3.9.3. (check-in: cc1675f94a user: drh tags: branch-3.12)
2016-03-30
14:26
Start a change log for version 3.13.0 (check-in: 6633347c5b user: drh tags: trunk)
2016-03-29
22:30
On second thought, remove the information disclosure clean-up policy. (check-in: f343e93d94 user: drh tags: trunk)
22:27
Add the information disclosure clean-up policy document. (check-in: 454218b20d user: drh tags: trunk)

Changes to pages/34to35.in.

48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
  be required to upgrade to newer versions.
}

HEADING 1 {Overview Of Changes}

PARAGRAPH {
  A quick enumeration of the changes in SQLite version 3.5.0
  is provide here.  Subsequent sections will describe these
  changes in more detail.
}
PARAGRAPH {
  <ol>
  <li>The OS interface layer has been completely reworked:
  <ol type="a">
  <li>The undocumented <b>sqlite3_os_switch()</b> interface has







|







48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
  be required to upgrade to newer versions.
}

HEADING 1 {Overview Of Changes}

PARAGRAPH {
  A quick enumeration of the changes in SQLite version 3.5.0
  is provided here.  Subsequent sections will describe these
  changes in more detail.
}
PARAGRAPH {
  <ol>
  <li>The OS interface layer has been completely reworked:
  <ol type="a">
  <li>The undocumented <b>sqlite3_os_switch()</b> interface has
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149

PARAGRAPH {
  The new OS interface for SQLite is built around an object named
  [sqlite3_vfs].  The "vfs" stands for "Virtual File System".
  The sqlite3_vfs object is basically a structure containing pointers
  to functions that implement the primitive disk I/O operations that
  SQLite needs to perform in order to read and write databases.  
  In this article, we will often refer a sqlite3_vfs objects as a "VFS".
}

PARAGRAPH {
  SQLite is able to use multiple VFSes at the same time.  Each
  individual database connection is associated with just one VFS.
  But if you have multiple database connections, each connection
  can be associated with a different VFS.







|







135
136
137
138
139
140
141
142
143
144
145
146
147
148
149

PARAGRAPH {
  The new OS interface for SQLite is built around an object named
  [sqlite3_vfs].  The "vfs" stands for "Virtual File System".
  The sqlite3_vfs object is basically a structure containing pointers
  to functions that implement the primitive disk I/O operations that
  SQLite needs to perform in order to read and write databases.  
  In this article, we will often refer to an sqlite3_vfs objects as a "VFS".
}

PARAGRAPH {
  SQLite is able to use multiple VFSes at the same time.  Each
  individual database connection is associated with just one VFS.
  But if you have multiple database connections, each connection
  can be associated with a different VFS.
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
}

CODE {
int sqlite3_vfs_register(sqlite3_vfs*, int makeDflt);
}

PARAGRAPH {
  Applications can call sqlite3_vfs_register at any time, though of course
  a VFS needs to be registered before it can be used.  The first argument
  is a pointer to a customized VFS object that the application has prepared.
  The second argument is true to make the new VFS the default VFS so that
  it will be used by the legacy [sqlite3_open()] and [sqlite3_open16()] APIs.
  If the new VFS is not the default, then you will probably have to use
  the new [sqlite3_open_v2()] API to use it.  Note, however, that if
  a new VFS is the only VFS known to SQLite (if SQLite was compiled without
  its usual default VFS or if the precompiled default VFS was removed
  using [sqlite3_vfs_unregister()]) then the new VFS automatic becomes the
  default VFS regardless of the makeDflt argument to [sqlite3_vfs_register()].
}

PARAGRAPH {
  Standard builds include the default "unix" or "win32" VFSes.
  But if you use the -DOS_OTHER=1 compile-time option, then SQLite is
  built without a default VFS.  In that case, the application must







|








|







171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
}

CODE {
int sqlite3_vfs_register(sqlite3_vfs*, int makeDflt);
}

PARAGRAPH {
  Applications can call sqlite3_vfs_register() at any time, though of course
  a VFS needs to be registered before it can be used.  The first argument
  is a pointer to a customized VFS object that the application has prepared.
  The second argument is true to make the new VFS the default VFS so that
  it will be used by the legacy [sqlite3_open()] and [sqlite3_open16()] APIs.
  If the new VFS is not the default, then you will probably have to use
  the new [sqlite3_open_v2()] API to use it.  Note, however, that if
  a new VFS is the only VFS known to SQLite (if SQLite was compiled without
  its usual default VFS or if the precompiled default VFS was removed
  using [sqlite3_vfs_unregister()]) then the new VFS automatically becomes the
  default VFS regardless of the makeDflt argument to [sqlite3_vfs_register()].
}

PARAGRAPH {
  Standard builds include the default "unix" or "win32" VFSes.
  But if you use the -DOS_OTHER=1 compile-time option, then SQLite is
  built without a default VFS.  In that case, the application must
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248

PARAGRAPH {
  Once a VFS has been registered, it should never be modified.  If
  a change in behavior is required, a new VFS should be registered.
  The application could, perhaps, use [sqlite3_vfs_find()] to locate
  the old VFS, make a copy of the old VFS into a new [sqlite3_vfs]
  object, make the desired modifications to the new VFS, unregister
  the old VFS, the register the new VFS in its place.  Existing
  database connections would continue to use the old VFS even after
  it is unregistered, but new database connections would use the
  new VFS.
}  

HEADING 3 {The VFS Object}








|







234
235
236
237
238
239
240
241
242
243
244
245
246
247
248

PARAGRAPH {
  Once a VFS has been registered, it should never be modified.  If
  a change in behavior is required, a new VFS should be registered.
  The application could, perhaps, use [sqlite3_vfs_find()] to locate
  the old VFS, make a copy of the old VFS into a new [sqlite3_vfs]
  object, make the desired modifications to the new VFS, unregister
  the old VFS, then register the new VFS in its place.  Existing
  database connections would continue to use the old VFS even after
  it is unregistered, but new database connections would use the
  new VFS.
}  

HEADING 3 {The VFS Object}

324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
PARAGRAPH {
  The pAppData pointer is unused by the SQLite core.  The pointer is
  available to store auxiliary information that a VFS information might
  want to carry around.
}

PARAGRAPH {
  The remaining fields of the [sqlite3_vfs] object all store pointer
  to functions that implement primitive operations.  We call these
  "methods".  The first methods, xOpen, is used to open files on
  the underlying storage media.  The result is an [sqlite3_file]
  object.  There are additional methods, defined by the [sqlite3_file]
  object itself that are used to read and write and close the file.
  The additional methods are detailed below.  The filename is in UTF-8.
  SQLite will guarantee that the zFilename string passed to
  xOpen() is a full pathname as generated by xFullPathname() and
  that the string will be valid and unchanged until xClose() is







|

|







324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
PARAGRAPH {
  The pAppData pointer is unused by the SQLite core.  The pointer is
  available to store auxiliary information that a VFS information might
  want to carry around.
}

PARAGRAPH {
  The remaining fields of the [sqlite3_vfs] object all store pointers
  to functions that implement primitive operations.  We call these
  "methods".  The first method, xOpen, is used to open files on
  the underlying storage media.  The result is an [sqlite3_file]
  object.  There are additional methods, defined by the [sqlite3_file]
  object itself that are used to read and write and close the file.
  The additional methods are detailed below.  The filename is in UTF-8.
  SQLite will guarantee that the zFilename string passed to
  xOpen() is a full pathname as generated by xFullPathname() and
  that the string will be valid and unchanged until xClose() is
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
  ORDER BY or GROUP BY clause.  Both TEMP_DB and TRANSIENT_DB databases
  are private and are deleted automatically.  TEMP_DB databases last
  for the duration of the database connection.  TRANSIENT_DB databases
  last only for the duration of a single SQL statement.
}

PARAGRAPH {
  The xDelete method is used delete a file.  The name of the file is
  given in the second parameter.  The filename will be in UTF-8.
  The VFS must convert the filename into whatever character representation
  the underlying operating system expects.  If the syncDir parameter is
  true, then the xDelete method should not return until the change
  to the directory contents for the directory containing the
  deleted file have been synced to disk in order to ensure that the
  file does not "reappear" if a power failure occurs soon after.







|







394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
  ORDER BY or GROUP BY clause.  Both TEMP_DB and TRANSIENT_DB databases
  are private and are deleted automatically.  TEMP_DB databases last
  for the duration of the database connection.  TRANSIENT_DB databases
  last only for the duration of a single SQL statement.
}

PARAGRAPH {
  The xDelete method is used to delete a file.  The name of the file is
  given in the second parameter.  The filename will be in UTF-8.
  The VFS must convert the filename into whatever character representation
  the underlying operating system expects.  If the syncDir parameter is
  true, then the xDelete method should not return until the change
  to the directory contents for the directory containing the
  deleted file have been synced to disk in order to ensure that the
  file does not "reappear" if a power failure occurs soon after.
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
  the xRandomness method on the default VFS is used.  The xRandomness
  methods on other VFSes are never accessed by SQLite.
  The xRandomness routine requests that nByte bytes of randomness
  be written into zOut.  The routine returns the actual number of
  bytes of randomness obtained.  The quality of the randomness so obtained
  will determine the quality of the randomness generated by built-in 
  SQLite functions such as random() and randomblob().  SQLite also
  uses its PRNG to generate temporary file names..  On some platforms
  (ex: Windows) SQLite assumes that temporary file names are unique
  without actually testing for collisions, so it is important to have
  good-quality randomness even if the random() and randomblob() 
  functions are never used.
}

PARAGRAPH {
  The xSleep method is used to suspend the calling thread for at
  least the number of microseconds given.  This method is used to
  implement the [sqlite3_sleep()] and [sqlite3_busy_timeout()] APIs.
  In the case of [sqlite3_sleep()] the xSleep method of the default
  VFS is always used.  If the underlying system does not have a
  microsecond resolution sleep capability, then the sleep time should
  be rounded up.  xSleep returns this rounded-up value.
}

PARAGRAPH {
  The xCurrentTime method finds the current time and date and writes
  the result as double-precision floating point value into pointer
  provided by the second parameter.  The time and date is in
  coordinated universal time (UTC) and is a fractional Julian day number.
}

HEADING 3 {The Open File Object}

PARAGRAPH {







|


















|







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
  the xRandomness method on the default VFS is used.  The xRandomness
  methods on other VFSes are never accessed by SQLite.
  The xRandomness routine requests that nByte bytes of randomness
  be written into zOut.  The routine returns the actual number of
  bytes of randomness obtained.  The quality of the randomness so obtained
  will determine the quality of the randomness generated by built-in 
  SQLite functions such as random() and randomblob().  SQLite also
  uses its PRNG to generate temporary file names.  On some platforms
  (ex: Windows) SQLite assumes that temporary file names are unique
  without actually testing for collisions, so it is important to have
  good-quality randomness even if the random() and randomblob() 
  functions are never used.
}

PARAGRAPH {
  The xSleep method is used to suspend the calling thread for at
  least the number of microseconds given.  This method is used to
  implement the [sqlite3_sleep()] and [sqlite3_busy_timeout()] APIs.
  In the case of [sqlite3_sleep()] the xSleep method of the default
  VFS is always used.  If the underlying system does not have a
  microsecond resolution sleep capability, then the sleep time should
  be rounded up.  xSleep returns this rounded-up value.
}

PARAGRAPH {
  The xCurrentTime method finds the current time and date and writes
  the result as a double-precision floating point value into pointer
  provided by the second parameter.  The time and date is in
  coordinated universal time (UTC) and is a fractional Julian day number.
}

HEADING 3 {The Open File Object}

PARAGRAPH {
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
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
  The locking level is specified as the second argument to both xLock
  and xUnlock.  The xLock method increases the locking level to the
  specified locking level or higher.  The xUnlock method decreases the
  locking level to no lower than the level specified.  
  [SQLITE_LOCK_NONE] means that the file is unlocked.  [SQLITE_LOCK_SHARED]
  gives permission to read the file.  Multiple database connections can
  hold [SQLITE_LOCK_SHARED] at the same time.
  [SQLITE_LOCK_RESERVED] is like [SQLITE_LOCK_SHARED] in that its is permission
  to read the file.  But only a single connection can hold a reserved lock
  at any point in time.  The [SQLITE_LOCK_PENDING] is also permission to
  read the file.  Other connections can continue to read the file as well,
  but no other connection is allowed to escalate a lock from none to shared.
  [SQLITE_LOCK_EXCLUSIVE] is permission to write on the file.  Only a single
  connection can hold an exclusive lock and no other connection can hold
  any lock (other than "none") while one connection is hold an exclusive
  lock.  The xLock returns [SQLITE_OK] on success, [SQLITE_BUSY] if it
  is unable to obtain the lock, or [SQLITE_IOERR_RDLOCK] if something else
  goes wrong.  The xUnlock method returns [SQLITE_OK] on success and
  [SQLITE_IOERR_UNLOCK] for problems.
}

PARAGRAPH {
  The xCheckReservedLock method checks to see if another connection or
  another process is currently holding a reserved, pending, or exclusive
  lock on the file.  It returns true or false.
}

PARAGRAPH {
  The xFileControl() method is a generic interface that allows custom
  VFS implementations to directly control an open file using the
  (new and experimental)
  [sqlite3_file_control()] interface.  The second "op" argument
  is an integer opcode.   The third
  argument is a generic pointer which is intended to be a pointer
  to a structure that may contain arguments or space in which to
  write return values.  Potential uses for xFileControl() might be
  functions to enable blocking locks with timeouts, to change the
  locking strategy (for example to use dot-file locks), to inquire
  about the status of a lock, or to break stale locks.  The SQLite
  core reserves opcodes less than 100 for its own use. 
  A [SQLITE_FCNTL_LOCKSTATE | list of opcodes] less than 100 is available.
  Applications that define a custom xFileControl method should use opcodes 
  greater than 100 to avoid conflicts.
}

PARAGRAPH {
  The xSectorSize returns the "sector size" of the underlying
  non-volatile media.  A "sector" is defined as the smallest unit of
  storage that can be written without disturbing adjacent storage.
  On a disk drive the "sector size" has until recently been 512 bytes,
  though there is a push to increase this value to 4KiB.  SQLite needs
  to know the sector size so that it can write a full sector at a
  time, and thus avoid corrupting adjacent storage space if a power
  lose occurs in the middle of a write.
}

PARAGRAPH {
  The xDeviceCharacteristics method returns an integer bit vector that
  defines any special properties that the underlying storage medium might
  have that SQLite can use to increase performance.  The allowed return
  is the bit-wise OR of the following values:







|






|







|









|




















|







609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
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
  The locking level is specified as the second argument to both xLock
  and xUnlock.  The xLock method increases the locking level to the
  specified locking level or higher.  The xUnlock method decreases the
  locking level to no lower than the level specified.  
  [SQLITE_LOCK_NONE] means that the file is unlocked.  [SQLITE_LOCK_SHARED]
  gives permission to read the file.  Multiple database connections can
  hold [SQLITE_LOCK_SHARED] at the same time.
  [SQLITE_LOCK_RESERVED] is like [SQLITE_LOCK_SHARED] in that it is permission
  to read the file.  But only a single connection can hold a reserved lock
  at any point in time.  The [SQLITE_LOCK_PENDING] is also permission to
  read the file.  Other connections can continue to read the file as well,
  but no other connection is allowed to escalate a lock from none to shared.
  [SQLITE_LOCK_EXCLUSIVE] is permission to write on the file.  Only a single
  connection can hold an exclusive lock and no other connection can hold
  any lock (other than "none") while one connection holds an exclusive
  lock.  The xLock returns [SQLITE_OK] on success, [SQLITE_BUSY] if it
  is unable to obtain the lock, or [SQLITE_IOERR_RDLOCK] if something else
  goes wrong.  The xUnlock method returns [SQLITE_OK] on success and
  [SQLITE_IOERR_UNLOCK] for problems.
}

PARAGRAPH {
  The xCheckReservedLock() method checks to see if another connection or
  another process is currently holding a reserved, pending, or exclusive
  lock on the file.  It returns true or false.
}

PARAGRAPH {
  The xFileControl() method is a generic interface that allows custom
  VFS implementations to directly control an open file using the
  (new and experimental)
  [sqlite3_file_control()] interface.  The second "op" argument
  is an integer opcode.  The third
  argument is a generic pointer which is intended to be a pointer
  to a structure that may contain arguments or space in which to
  write return values.  Potential uses for xFileControl() might be
  functions to enable blocking locks with timeouts, to change the
  locking strategy (for example to use dot-file locks), to inquire
  about the status of a lock, or to break stale locks.  The SQLite
  core reserves opcodes less than 100 for its own use. 
  A [SQLITE_FCNTL_LOCKSTATE | list of opcodes] less than 100 is available.
  Applications that define a custom xFileControl method should use opcodes 
  greater than 100 to avoid conflicts.
}

PARAGRAPH {
  The xSectorSize returns the "sector size" of the underlying
  non-volatile media.  A "sector" is defined as the smallest unit of
  storage that can be written without disturbing adjacent storage.
  On a disk drive the "sector size" has until recently been 512 bytes,
  though there is a push to increase this value to 4KiB.  SQLite needs
  to know the sector size so that it can write a full sector at a
  time, and thus avoid corrupting adjacent storage space if a power
  loss occurs in the middle of a write.
}

PARAGRAPH {
  The xDeviceCharacteristics method returns an integer bit vector that
  defines any special properties that the underlying storage medium might
  have that SQLite can use to increase performance.  The allowed return
  is the bit-wise OR of the following values:
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
  sqlite3_int64 iThreshold
);
}

PARAGRAPH {
 The [sqlite3_memory_alarm] routine is used to register
 a callback on memory allocation events.
 This routine registers or clears a callbacks that fires when
 the amount of memory allocated exceeds iThreshold.  Only
 a single callback can be registered at a time.  Each call
 to [sqlite3_memory_alarm()] overwrites the previous callback.
 The callback is disabled by setting xCallback to a NULL
 pointer.
}








|







777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
  sqlite3_int64 iThreshold
);
}

PARAGRAPH {
 The [sqlite3_memory_alarm] routine is used to register
 a callback on memory allocation events.
 This routine registers or clears a callback that fires when
 the amount of memory allocated exceeds iThreshold.  Only
 a single callback can be registered at a time.  Each call
 to [sqlite3_memory_alarm()] overwrites the previous callback.
 The callback is disabled by setting xCallback to a NULL
 pointer.
}

Changes to pages/35to36.in.

90
91
92
93
94
95
96
97
98
99
100
101
102
103
104

  <li><p>The signature of the xCheckReservedLock method on [sqlite3_io_methods]
      has been modified so that it returns an [error code] and stores its
      boolean result into an integer pointed to by a parameter.  In
      association with this change, a new extended error code
      [SQLITE_IOERR_CHECKRESERVEDLOCK] has been added.</p></li>

  <li><p>When SQLite is ported to new operation systems (operating systems 
      other than Unix, Windows, and OS/2 for which ports are provided together
      with the core)
      two new functions, [sqlite3_os_init()] and [sqlite3_os_end()], must
      be provided as part of the port.</p></li>

  <li><p>The way in which the IN and NOT IN operators handle NULL values
      in their right-hand expressions has been brought into compliance with







|







90
91
92
93
94
95
96
97
98
99
100
101
102
103
104

  <li><p>The signature of the xCheckReservedLock method on [sqlite3_io_methods]
      has been modified so that it returns an [error code] and stores its
      boolean result into an integer pointed to by a parameter.  In
      association with this change, a new extended error code
      [SQLITE_IOERR_CHECKRESERVEDLOCK] has been added.</p></li>

  <li><p>When SQLite is ported to new operating systems (operating systems 
      other than Unix, Windows, and OS/2 for which ports are provided together
      with the core)
      two new functions, [sqlite3_os_init()] and [sqlite3_os_end()], must
      be provided as part of the port.</p></li>

  <li><p>The way in which the IN and NOT IN operators handle NULL values
      in their right-hand expressions has been brought into compliance with
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
  through [sqlite3_status()].</p></li>

  <li><p>The [sqlite3_initialize()] interface can be called to explicitly
  initialize the SQLite subsystem.  The [sqlite3_initialize()] interface is
  called automatically when invoking certain interfaces so the use of
  [sqlite3_initialize()] is not required, but it is recommended.</p></li>

  <li><p>The [sqlite3_shutdown()] interface causes SQLite release any
  system resources (memory allocations, mutexes, open file handles)
  that it might have been allocated by [sqlite3_initialize()].</p></li>

  <li><p>The [sqlite3_next_stmt()] interface allows an application to discover
  all [prepared statements] associated with a [database connection].</p></li>

  <li><p>Added the [page_count] PRAGMA for returning the size of the underlying
  database file in pages.</p></li>

  <li><p>Added a new [rtree | R*Tree index extension].</p></li>

  </ol>
}
</tcl>







|

|












293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
  through [sqlite3_status()].</p></li>

  <li><p>The [sqlite3_initialize()] interface can be called to explicitly
  initialize the SQLite subsystem.  The [sqlite3_initialize()] interface is
  called automatically when invoking certain interfaces so the use of
  [sqlite3_initialize()] is not required, but it is recommended.</p></li>

  <li><p>The [sqlite3_shutdown()] interface causes SQLite to release any
  system resources (memory allocations, mutexes, open file handles)
  that might have been allocated by [sqlite3_initialize()].</p></li>

  <li><p>The [sqlite3_next_stmt()] interface allows an application to discover
  all [prepared statements] associated with a [database connection].</p></li>

  <li><p>Added the [page_count] PRAGMA for returning the size of the underlying
  database file in pages.</p></li>

  <li><p>Added a new [rtree | R*Tree index extension].</p></li>

  </ol>
}
</tcl>

Changes to pages/appfileformat.in.

54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
</ul>

<p>We make a distinction between a "file format" and an "application format".
A file format is used to store a single object.  So, for example, a GIF or
JPEG file stores a single image, and an XHTML file stores text,
so those are "file formats" and not "application formats".  An EPUB file, 
in contrast, stores both text and images (as contained XHTML and GIF/JPEG
files) and so it is considered a "application format".  This article is
about "application formats".

<p>The boundary between a file format and an application format is fuzzy.
This article calls JPEG a file format, but for an image editor, JPEG 
might be considered the application format.  Much depends on context.
For this article, let us say that a file format stores a single object
and an application format stores many different objects and their relationships







|







54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
</ul>

<p>We make a distinction between a "file format" and an "application format".
A file format is used to store a single object.  So, for example, a GIF or
JPEG file stores a single image, and an XHTML file stores text,
so those are "file formats" and not "application formats".  An EPUB file, 
in contrast, stores both text and images (as contained XHTML and GIF/JPEG
files) and so it is considered an "application format".  This article is
about "application formats".

<p>The boundary between a file format and an application format is fuzzy.
This article calls JPEG a file format, but for an image editor, JPEG 
might be considered the application format.  Much depends on context.
For this article, let us say that a file format stores a single object
and an application format stores many different objects and their relationships
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
as an equivalent ZIP archive, and it has the advantage
of being able to update individual "files" without rewriting
the entire document.

<p>
But an SQLite database is not limited to a simple key/value structure
like a pile-of-files database.  An SQLite database can have dozens
or hundreds or thousands of different of tables, with dozens or
hundreds or thousands of fields per table, each with different datatypes
and constraints and particular meanings, all cross-referencing each other,
appropriately and automatically indexed for rapid retrieval,
and all stored efficiently and compactly in a single disk file.
And all of this structure is succinctly documented for humans
by the SQL schema.








|







151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
as an equivalent ZIP archive, and it has the advantage
of being able to update individual "files" without rewriting
the entire document.

<p>
But an SQLite database is not limited to a simple key/value structure
like a pile-of-files database.  An SQLite database can have dozens
or hundreds or thousands of different tables, with dozens or
hundreds or thousands of fields per table, each with different datatypes
and constraints and particular meanings, all cross-referencing each other,
appropriately and automatically indexed for rapid retrieval,
and all stored efficiently and compactly in a single disk file.
And all of this structure is succinctly documented for humans
by the SQL schema.

226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
and avoid time spent "heads down" fiddling with low-level file
formatting details.

<p>A pile-of-files format can be viewed as a key/value database.  
A key/value database is better than no database at all.
But without transactions or indices or a high-level query language or
a proper schema,
it much harder and more error prone to use a key/value database than
a relational database.

<li><p><b>Accessible Content.</b>
Information held in an SQLite database file is accessible using 
commonly available open-source command-line tools - tools that 
are installed by default on Mac and Linux systems and that are 
freely available as a self-contained EXE file on Windows.







|







226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
and avoid time spent "heads down" fiddling with low-level file
formatting details.

<p>A pile-of-files format can be viewed as a key/value database.  
A key/value database is better than no database at all.
But without transactions or indices or a high-level query language or
a proper schema,
it is much harder and more error prone to use a key/value database than
a relational database.

<li><p><b>Accessible Content.</b>
Information held in an SQLite database file is accessible using 
commonly available open-source command-line tools - tools that 
are installed by default on Mac and Linux systems and that are 
freely available as a self-contained EXE file on Windows.
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
(a single page).

<p>SQLite also supports continuous update.
Instead of collecting changes in memory and then writing
them to disk only on a File/Save action, changes can be written back to
the disk as they occur.  This avoids loss of work on a system crash or
power failure.  An [automated undo/redo stack], managed using triggers,
can be kept in the on-disk  database, meaning that undo/redo can occur 
across session boundaries.

<li><p><b>Easily Extensible.</b>
As an application grows, new features can be added to an
SQLite application file format simply by adding new tables to the schema
or by adding new columns to existing tables.  Adding columns or tables
does not change the meaning of prior queries, so with a 







|







294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
(a single page).

<p>SQLite also supports continuous update.
Instead of collecting changes in memory and then writing
them to disk only on a File/Save action, changes can be written back to
the disk as they occur.  This avoids loss of work on a system crash or
power failure.  An [automated undo/redo stack], managed using triggers,
can be kept in the on-disk database, meaning that undo/redo can occur 
across session boundaries.

<li><p><b>Easily Extensible.</b>
As an application grows, new features can be added to an
SQLite application file format simply by adding new tables to the schema
or by adding new columns to existing tables.  Adding columns or tables
does not change the meaning of prior queries, so with a 

Changes to pages/atomiccommit.in.

787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
in the gaps.</p>

<tcl>hd_fragment completesectors</tcl>
<h3>6.1 Always Journal Complete Sectors</h3>

<p>When the original content of a database page is written into
the rollback journal (as shown in <a href="#section_3_5">section 3.5</a>),
SQLite always writes a complete sectors worth of data, even if the
page size of the database is smaller than the sector size.  
Historically, the sector size in SQLite has been hard coded to 512
bytes and since the minimum page size is also 512 bytes, this has never
been an issue.  But beginning with SQLite version 3.3.14, it is possible
for SQLite to use mass storage devices with a sector size larger than 512
bytes.  So, beginning with version 3.3.14, whenever any page within a
sector is written into the journal file, all pages in that same sector







|







787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
in the gaps.</p>

<tcl>hd_fragment completesectors</tcl>
<h3>6.1 Always Journal Complete Sectors</h3>

<p>When the original content of a database page is written into
the rollback journal (as shown in <a href="#section_3_5">section 3.5</a>),
SQLite always writes a complete sector of data, even if the
page size of the database is smaller than the sector size.  
Historically, the sector size in SQLite has been hard coded to 512
bytes and since the minimum page size is also 512 bytes, this has never
been an issue.  But beginning with SQLite version 3.3.14, it is possible
for SQLite to use mass storage devices with a sector size larger than 512
bytes.  So, beginning with version 3.3.14, whenever any page within a
sector is written into the journal file, all pages in that same sector
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
<a href="pragma.html#pragma_journal_mode">journal_mode</a> PRAGMA.
For example:</p>

<blockquote><pre>
PRAGMA journal_mode=PERSIST;
</per></blockquote>

<p>The use of persistent journal mode provide a noticeable performance
improvement on many systems.  Of course, the drawback is that the 
journal files remain on the disk, using disk space and cluttering
directories, long after the transaction commits.  The only safe way
to delete a persistent journal file is to commit a transaction
with journaling mode set to DELETE:</p>

<blockquote><pre>







|







1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
<a href="pragma.html#pragma_journal_mode">journal_mode</a> PRAGMA.
For example:</p>

<blockquote><pre>
PRAGMA journal_mode=PERSIST;
</per></blockquote>

<p>The use of persistent journal mode provides a noticeable performance
improvement on many systems.  Of course, the drawback is that the 
journal files remain on the disk, using disk space and cluttering
directories, long after the transaction commits.  The only safe way
to delete a persistent journal file is to commit a transaction
with journaling mode set to DELETE:</p>

<blockquote><pre>
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
dot-file locks or vice versa.</p>

<tcl>hd_fragment fsync</tcl>
<h3>9.2 Incomplete Disk Flushes</h3>

<p>SQLite uses the fsync() system call on Unix and the FlushFileBuffers()
system call on w32 in order to sync the file system buffers onto disk
oxide as shown in  <a href="#section_3_7">step 3.7</a> and
<a href="#section_3_10">step 3.10</a>.  Unfortunately, we have received
reports that neither of these interfaces works as advertised on many
systems.  We hear that FlushFileBuffers() can be completely disabled
using registry settings on some Windows versions.  Some historical
versions of Linux contain versions of fsync() which are no-ops on
some filesystems, we are told.  Even on systems where 
FlushFileBuffers() and fsync() are said to be working, often







|







1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
dot-file locks or vice versa.</p>

<tcl>hd_fragment fsync</tcl>
<h3>9.2 Incomplete Disk Flushes</h3>

<p>SQLite uses the fsync() system call on Unix and the FlushFileBuffers()
system call on w32 in order to sync the file system buffers onto disk
oxide as shown in <a href="#section_3_7">step 3.7</a> and
<a href="#section_3_10">step 3.10</a>.  Unfortunately, we have received
reports that neither of these interfaces works as advertised on many
systems.  We hear that FlushFileBuffers() can be completely disabled
using registry settings on some Windows versions.  Some historical
versions of Linux contain versions of fsync() which are no-ops on
some filesystems, we are told.  Even on systems where 
FlushFileBuffers() and fsync() are said to be working, often

Changes to pages/autoinc.in.

95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
^If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
different ROWID selection algorithm is used.  
^The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before existed in that same table.  ^If the table has never
before contained any data, then a ROWID of 1 is used.  ^If the table
has previously held a row with the largest possible ROWID, then new INSERTs
are not allowed and any attempt to insert a new row will fail with an
SQLITE_FULL error.  ^(Only ROWID values from previously transactions that
were committed are considered.  ROWID values that were rolled back
are ignored and can be reused.)^
</p>

<p>
^SQLite keeps track of the largest ROWID that a table has ever held using
an [internal table] named "[sqlite_sequence]".







|







95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
^If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
different ROWID selection algorithm is used.  
^The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before existed in that same table.  ^If the table has never
before contained any data, then a ROWID of 1 is used.  ^If the table
has previously held a row with the largest possible ROWID, then new INSERTs
are not allowed and any attempt to insert a new row will fail with an
SQLITE_FULL error.  ^(Only ROWID values from previous transactions that
were committed are considered.  ROWID values that were rolled back
are ignored and can be reused.)^
</p>

<p>
^SQLite keeps track of the largest ROWID that a table has ever held using
an [internal table] named "[sqlite_sequence]".

Changes to pages/backup.in.

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
      ** pTo accordingly. */
      pFrom = (isSave ? pInMemory : pFile);
      pTo   = (isSave ? pFile     : pInMemory);
  
      /* Set up the backup procedure to copy from the "main" database of 
      ** connection pFile to the main database of connection pInMemory.
      ** If something goes wrong, pBackup will be set to NULL and an error
      ** code and  message left in connection pTo.
      **
      ** If the backup object is successfully created, call backup_step()
      ** to copy data from pFile to pInMemory. Then call backup_finish()
      ** to release resources associated with the pBackup object.  If an
      ** error occurred, then  an error code and message will be left in
      ** connection pTo. If no error occurred, then the error code belonging
      ** to pTo is set to SQLITE_OK.
      */
      pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
      if( pBackup ){
        (void)sqlite3_backup_step(pBackup, -1);
        (void)sqlite3_backup_finish(pBackup);
      }
      rc = sqlite3_errcode(pTo);
    }

    /* Close the database connection opened on database file zFilename
    ** and return the result of this function. */
    (void)sqlite3_close(pFile);
    return rc;
  }
}
</tcl>

<p>
  The C function to the right demonstrates of one of the simplest,
  and most common, uses of the backup API: loading and saving the contents
  of an in-memory database to a file on disk. The backup API is used as
  follows in this example:

  <ol>
    <li>Function [sqlite3_backup_init()] is called to create an [sqlite3_backup]
        object to copy data between the two databases (either from a file and







|




|




















|







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
      ** pTo accordingly. */
      pFrom = (isSave ? pInMemory : pFile);
      pTo   = (isSave ? pFile     : pInMemory);
  
      /* Set up the backup procedure to copy from the "main" database of 
      ** connection pFile to the main database of connection pInMemory.
      ** If something goes wrong, pBackup will be set to NULL and an error
      ** code and message left in connection pTo.
      **
      ** If the backup object is successfully created, call backup_step()
      ** to copy data from pFile to pInMemory. Then call backup_finish()
      ** to release resources associated with the pBackup object.  If an
      ** error occurred, then an error code and message will be left in
      ** connection pTo. If no error occurred, then the error code belonging
      ** to pTo is set to SQLITE_OK.
      */
      pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
      if( pBackup ){
        (void)sqlite3_backup_step(pBackup, -1);
        (void)sqlite3_backup_finish(pBackup);
      }
      rc = sqlite3_errcode(pTo);
    }

    /* Close the database connection opened on database file zFilename
    ** and return the result of this function. */
    (void)sqlite3_close(pFile);
    return rc;
  }
}
</tcl>

<p>
  The C function to the right demonstrates one of the simplest,
  and most common, uses of the backup API: loading and saving the contents
  of an in-memory database to a file on disk. The backup API is used as
  follows in this example:

  <ol>
    <li>Function [sqlite3_backup_init()] is called to create an [sqlite3_backup]
        object to copy data between the two databases (either from a file and
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
  function is sleeping, then SQLite detects this and usually restarts the 
  backup process when sqlite3_backup_step() is next called. There is one 
  exception to this rule: If the source database is not an in-memory database,
  and the write is performed from within the same process as the backup
  operation and uses the same database handle (pDb), then the destination
  database (the one opened using connection pFile) is automatically updated
  along with the source. The backup process may then be continued after the 
  xSleep() call returns as if nothing had happened. 

<p>
  Whether or not the backup process is restarted as a result of writes to
  the source database mid-backup, the user can be sure that when the backup
  operation is completed the backup database contains a consistent and 
  up-to-date snapshot of the original. However:








|







335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
  function is sleeping, then SQLite detects this and usually restarts the 
  backup process when sqlite3_backup_step() is next called. There is one 
  exception to this rule: If the source database is not an in-memory database,
  and the write is performed from within the same process as the backup
  operation and uses the same database handle (pDb), then the destination
  database (the one opened using connection pFile) is automatically updated
  along with the source. The backup process may then be continued after the 
  sqlite3_sleep() call returns as if nothing had happened. 

<p>
  Whether or not the backup process is restarted as a result of writes to
  the source database mid-backup, the user can be sure that when the backup
  operation is completed the backup database contains a consistent and 
  up-to-date snapshot of the original. However:

Changes to pages/btreemodule.in.

77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
        [Tr] <td> L****** <td> Requirement statements specifying some details of the internal workings of the B-Tree module.
      </table>

  [h2 "Glossary"]

    <table id=glossary>
      [Glossary "Balance-Siblings Algorithm" {
        The balance-siblings algorithm is one of four algorithms that may be used
	used to redistribute data within a b-tree structure after an insert or
        delete operation that causes a b-tree node to become overfull or underfull.
        See section <cite>balance_siblings</cite> for details.
      }]
      [Glossary "B-Tree Cursor" {
        <span class=todo>Define this.
      }]







|







77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
        [Tr] <td> L****** <td> Requirement statements specifying some details of the internal workings of the B-Tree module.
      </table>

  [h2 "Glossary"]

    <table id=glossary>
      [Glossary "Balance-Siblings Algorithm" {
        The balance-siblings algorithm is one of four algorithms that may be
	used to redistribute data within a b-tree structure after an insert or
        delete operation that causes a b-tree node to become overfull or underfull.
        See section <cite>balance_siblings</cite> for details.
      }]
      [Glossary "B-Tree Cursor" {
        <span class=todo>Define this.
      }]

Changes to pages/changes.in.

11
12
13
14
15
16
17
18
19

20
21






































22
23
24
25
26
27
28
http://www.sqlite.org/src/timeline?t=release</a>.
See the [chronology] a succinct listing of releases.
</p>

<tcl>
set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]

  incr nChng
}







































chng {2016-03-29 (3.12.0)} {
<p><b>Potentially Disruptive Change:</b>
<li>The [SQLITE_DEFAULT_PAGE_SIZE] is increased from 1024 to 4096.  
    The [SQLITE_DEFAULT_CACHE_SIZE] is changed from 2000 to -2000 so 
    the same amount of cache memory is used by default.
    See the application note on the







|

>


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







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
http://www.sqlite.org/src/timeline?t=release</a>.
See the [chronology] a succinct listing of releases.
</p>

<tcl>
set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2016-04-18 (3.12.2)} {
<li>Fix a backwards compatibility problem in version 3.12.0 and 3.12.1:
    Columns declared as <tt>"INTEGER" PRIMARY KEY</tt> (with quotes around
    the datatype keyword) where not being recognized as an
    [INTEGER PRIMARY KEY], which resulted in an incompatible database file.
    Ticket [https://www.sqlite.org/src/info/7d7525cb01b68|7d7525cb01b68]
<li>Fix a bug (present since [version 3.9.0]) that can cause the [DELETE]
    operation to miss rows if [PRAGMA reverse_unordered_selects] is turned on.
    Ticket [https://www.sqlite.org/src/info/a306e56ff68b8fa5|a306e56ff68b8fa5]
<li>Fix a bug in the code generator that can causes incorrect results if
    two or more [virtual tables] are joined and the virtual table used in
    outer loop of the join has an [IN operator] constraint.
<li>Correctly interpret negative "PRAGMA cache_size" values when determining
    the cache size used for sorting large amounts of data.
<p><b>Hashes:</b>
<li>SQLITE_SOURCE_ID: "2016-04-18 17:30:31 92dc59fd5ad66f646666042eb04195e3a61a9e8e"
<li>SHA1 for sqlite3.c: de5a5898ebd3a3477d4652db143746d008b24c83
} {patchagainst 1 patchagainst 3}

chng {2016-04-08 (3.12.1)} {
<li>Fix a boundary condition error introduced by version 3.12.0
    that can result in a crash during heavy [SAVEPOINT] usage.
    Ticket [https://www.sqlite.org/src/info/7f7f8026eda38|7f7f8026eda38].
<li>Fix [views] so that they inherit column datatypes from the 
    table that they are defined against, when possible.
<li>Fix the query planner so that IS and IS NULL operators are able
    to drive an index on a LEFT OUTER JOIN.
<p><b>Hashes:</b>
<li>SQLITE_SOURCE_ID: "2016-04-08 15:09:49 fe7d3b75fe1bde41511b323925af8ae1b910bc4d"
<li>SHA1 for sqlite3.c: ebb18593350779850e3e1a930eb84a70fca8c1d1
} {patchagainst 2}

chng {2016-04-01 (3.9.3)} {
<li>Backport a simple query planner optimization that allows the IS operator
    to drive an index on a LEFT OUTER JOIN.  No other changes from the
    [version 3.9.2] baseline.
}

chng {2016-03-29 (3.12.0)} {
<p><b>Potentially Disruptive Change:</b>
<li>The [SQLITE_DEFAULT_PAGE_SIZE] is increased from 1024 to 4096.  
    The [SQLITE_DEFAULT_CACHE_SIZE] is changed from 2000 to -2000 so 
    the same amount of cache memory is used by default.
    See the application note on the

Changes to pages/cli.in.

54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}
</tcl>

<p>You can terminate the sqlite3 program by typing your systems
End-Of-File character (usually a Control-D).  Use the interrupt
character (usually a Control-C) to stop a long-running SQL statement.</p>

<p>Make sure you type a semicolon at the end of each SQL command!
The sqlite3 program looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite3 will give you a
continuation prompt and wait for you to enter more text to be







|







54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}
</tcl>

<p>You can terminate the sqlite3 program by typing your system
End-Of-File character (usually a Control-D).  Use the interrupt
character (usually a Control-C) to stop a long-running SQL statement.</p>

<p>Make sure you type a semicolon at the end of each SQL command!
The sqlite3 program looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite3 will give you a
continuation prompt and wait for you to enter more text to be

Changes to pages/crew.in.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<title>SQLite Developers</title>

<h2>The SQLite Development Team</h2>

<img src="images/drh1.jpg" align="left" hspace="25" vspace="0">
<p>
<b>D. Richard Hipp
</b> began the SQLite project in on 2000-May-29
and continues to serve as the project architect.  Richard was born,
lives, and works in 
[http://en.wikipedia.org/wiki/Charlotte,_North_Carolina | Charlotte, North Carolina.]  He holds degrees from
[http://www.gatech.edu/ | Georgia Tech] (MSEE, 1984) and
[http://www.duke.edu/ | Duke University] (PhD, 1992) and is
the founder of the consulting firm
[http://www.hwaci.com/ | Hwaci].</p>







|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<title>SQLite Developers</title>

<h2>The SQLite Development Team</h2>

<img src="images/drh1.jpg" align="left" hspace="25" vspace="0">
<p>
<b>D. Richard Hipp
</b> began the SQLite project on 2000-05-29
and continues to serve as the project architect.  Richard was born,
lives, and works in 
[http://en.wikipedia.org/wiki/Charlotte,_North_Carolina | Charlotte, North Carolina.]  He holds degrees from
[http://www.gatech.edu/ | Georgia Tech] (MSEE, 1984) and
[http://www.duke.edu/ | Duke University] (PhD, 1992) and is
the founder of the consulting firm
[http://www.hwaci.com/ | Hwaci].</p>

Changes to pages/dev.in.

1
2
3
4
5
6
7
8
9
<title>SQLite Developer Links</title>
<h1 align="center">Developer Resources</h1>

<ul>
<li> <a href="http://www.sqlite.org/src/wiki/name=Bug+Reports">Report A Bug</a>
<li> <a href="http://www.sqlite.org/src/timeline">Timeline</a>
<li> <a href="http://www.sqlite.org/src/dir">Browse Historical Source Files</a>
<li> <a href="http://www.sqlite.org/docsrc">Documentation Source Text</a>
</ul>




|




1
2
3
4
5
6
7
8
9
<title>SQLite Developer Links</title>
<h1 align="center">Developer Resources</h1>

<ul>
<li> <a href="http://www.sqlite.org/src/wiki?name=Bug+Reports">Report A Bug</a>
<li> <a href="http://www.sqlite.org/src/timeline">Timeline</a>
<li> <a href="http://www.sqlite.org/src/dir">Browse Historical Source Files</a>
<li> <a href="http://www.sqlite.org/docsrc">Documentation Source Text</a>
</ul>

Changes to pages/howtocorrupt.in.

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
then the two processes will be talking to different database files with 
the same name.  (Note that this is only possible on Posix and Posix-like
systems that permit a file to be unlinked while it is still open for
reading and writing.  Windows does not allow this to occur.)
Since rollback journals and WAL files are based on the name of the database
file, the two different database files will share the same rollback
journal or WAL file.  A rollback or recovery for one of the databases
might use content from the other database, resulting in corruption.</p>

<p>A similar problem occurs if a database file is renamed while it is
opened and a new file is created with the old name.</p>




<p>Beginning with SQLite [version 3.7.17], the unix OS interface will
send SQLITE_WARNING messages to the [error log] if a database file is unlinked
while it is still in use.</p>

<tcl>hd_fragment alias {database filename aliasing}</tcl>
<h3>2.5 Multiple links to the same file</h3>

<p>If a single database file has multiple links (either hard or soft links)
then that is just another way of saying that the file has multiple names.
If two or more processes open the database using different names, then
they will use different rollback journals and WAL files.  That means that
if one process crashes, the other process will be unable to recover the
transaction in progress because it will be looking in the wrong place
for the appropriate journal.</p>




<p>Beginning with SQLite [version 3.7.17], the unix OS interface will
send SQLITE_WARNING messages to the [error log] if a database file has 
multiple hard links.  As of this writing, SQLite still does not yet detect 
or warn about the use of database files through soft links.</p>







<h2>3.0 Failure to sync</h2>

<p>In order to guarantee that database files are always consistent, SQLite
will occasionally ask the operating system to flush all pending writes to
persistent storage then wait for that flush to complete.  This is 
accomplished using the <tt>fsync()</tt> system call under unix and







|
<
|


>
>
>















>
>
>


|
<

>
>
>
>
>







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
then the two processes will be talking to different database files with 
the same name.  (Note that this is only possible on Posix and Posix-like
systems that permit a file to be unlinked while it is still open for
reading and writing.  Windows does not allow this to occur.)
Since rollback journals and WAL files are based on the name of the database
file, the two different database files will share the same rollback
journal or WAL file.  A rollback or recovery for one of the databases
might use content from the other database, resulting in corruption.

A similar problem occurs if a database file is renamed while it is
opened and a new file is created with the old name.</p>

<p>In other words, unlinking or renaming an open database file 
results in behavior that is undefined and probably undesirable.</p>

<p>Beginning with SQLite [version 3.7.17], the unix OS interface will
send SQLITE_WARNING messages to the [error log] if a database file is unlinked
while it is still in use.</p>

<tcl>hd_fragment alias {database filename aliasing}</tcl>
<h3>2.5 Multiple links to the same file</h3>

<p>If a single database file has multiple links (either hard or soft links)
then that is just another way of saying that the file has multiple names.
If two or more processes open the database using different names, then
they will use different rollback journals and WAL files.  That means that
if one process crashes, the other process will be unable to recover the
transaction in progress because it will be looking in the wrong place
for the appropriate journal.</p>

<p>In other words, opening and using a database file that has two or
more names results in behavior that is undefined and probably undesirable.</p>

<p>Beginning with SQLite [version 3.7.17], the unix OS interface will
send SQLITE_WARNING messages to the [error log] if a database file has 
multiple hard links.</p>


<p>Beginning with SQLite [version 3.10.0], the unix OS interface will
attempt to resolve symbolic links and open the database file by its
canonical name.  Prior to version 3.10.0, opening a database file 
through a symbolic link was similar to opening a database file
that had multiple hard links and resulted in undefined behavior.</p>

<h2>3.0 Failure to sync</h2>

<p>In order to guarantee that database files are always consistent, SQLite
will occasionally ask the operating system to flush all pending writes to
persistent storage then wait for that flush to complete.  This is 
accomplished using the <tt>fsync()</tt> system call under unix and

Changes to pages/index.in.

106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_12_0.html">Version 3.12.0</a>
of SQLite is recommended for all new development.
</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>







|







106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_12_2.html">Version 3.12.2</a>
of SQLite is recommended for all new development.
</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>

Changes to pages/news.in.

14
15
16
17
18
19
20




























21
22
23
24
25
26
27
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}





























newsitem {2016-03-29} {Release 3.12.0} {
<p>SQLite [version 3.12.0] is a regularly scheduled maintenance release.
   A notable change in this release is an
   [increase in the default page size] for newly created database files.
   There are also various performance improvements.
   See the [version 3.12.0|change log] for details.







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







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2016-04-18} {Release 3.12.2} {
<p>Yikes!  The 3.12.0 and 3.12.1 releases contain a backwards compatibility bug!
   Tables that declare a column with type <tt>"INTEGER" PRIMARY KEY</tt>
   (where the datatype name INTEGER is quoted) generate an incompatible
   database file.  The mistake came about because the developers have never
   thought to put a typename in quotes before, and so there was no documentation 
   of that capability nor any tests.  (There are tests now, though, of course.)
   Instances of quoting the datatype name are probably infrequent in the wild,
   so we do not expect the impact of this bug to be too severe.
   Upgrading is still strongly recommended.
<p>Fixes for three other minor issues were included in this patch release.
   The other issues would have normally been deferred until the next scheduled
   release, but since a patch release is being issued anyhow, they might as
   well be included.
}

newsitem {2016-04-08} {Release 3.12.1} {
<p>SQLite [version 3.12.1] is an emergency patch release to address a 
   [https://www.sqlite.org/src/info/7f7f8026eda38|crash bug] that snuck
   into [version 3.12.0].  Upgrading from version 3.12.0 is highly
   recommended.
<p>Another minor problem involving datatypes on [view] columns, and
   a query planner deficiency are fixed at the same time.  These two
   issues did not justify a new release on their own, but since a release
   is being issued to deal with the crash bug, we included these other
   fixes for good measure.
}

newsitem {2016-03-29} {Release 3.12.0} {
<p>SQLite [version 3.12.0] is a regularly scheduled maintenance release.
   A notable change in this release is an
   [increase in the default page size] for newly created database files.
   There are also various performance improvements.
   See the [version 3.12.0|change log] for details.

Changes to pages/not-found.in.

1
2
3
4
5
6
7
8
9
<title>Page Not Found</title>
<h1 align='center'>Page Not found</h1>

<p>The document you seek is not available.  
Please consider one of the links below
or use the Search feature on the right-hand side of the
menu bar above.

<p><ul>

|







1
2
3
4
5
6
7
8
9
<title>Page Not Found</title>
<h1 align='center'>Page Not Found</h1>

<p>The document you seek is not available.  
Please consider one of the links below
or use the Search feature on the right-hand side of the
menu bar above.

<p><ul>

Changes to pages/onefile.in.

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[http://en.wikipedia.org/wiki/Endianness | big-endian] and 
[http://en.wikipedia.org/wiki/Endianness | little-endian]
architectures.
</p>

<p>
The SQLite database file format is also stable.
All releases of of SQLite version 3 can read and write database
files created by the very first SQLite 3 release (version 3.0.0)
going back to 2004-06-18.  This is "backwards compatibility".
The developers promise to maintain backwards compatibility of
the database file format for all future releases of SQLite 3.
"Forwards compatibility" means that older releases
of SQLite can also read and write databases created by newer
releases.  SQLite is usually, but not completely forwards







|







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[http://en.wikipedia.org/wiki/Endianness | big-endian] and 
[http://en.wikipedia.org/wiki/Endianness | little-endian]
architectures.
</p>

<p>
The SQLite database file format is also stable.
All releases of SQLite version 3 can read and write database
files created by the very first SQLite 3 release (version 3.0.0)
going back to 2004-06-18.  This is "backwards compatibility".
The developers promise to maintain backwards compatibility of
the database file format for all future releases of SQLite 3.
"Forwards compatibility" means that older releases
of SQLite can also read and write databases created by newer
releases.  SQLite is usually, but not completely forwards

Changes to pages/partialindex.in.

34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
are created by UNIQUE or PRIMARY KEY constraints inside of CREATE TABLE
statements) are ordinary full indexes.
</p>

<p>
^The expression following the WHERE clause may contain operators,
literal values, and names of columns in the table being indexed.
^The WHERE clause may <em>not</em> contains subqueries, references to other
tables, functions, or [bound parameters].  The LIKE, GLOB, MATCH,
and REGEXP operators in SQLite are implemented as functions by the same name.
^Since functions are prohibited in the 
WHERE clause of a CREATE INDEX statement, so too are the LIKE, GLOB,
MATCH, and REGEXP operators.</p>

<p>







|







34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
are created by UNIQUE or PRIMARY KEY constraints inside of CREATE TABLE
statements) are ordinary full indexes.
</p>

<p>
^The expression following the WHERE clause may contain operators,
literal values, and names of columns in the table being indexed.
^The WHERE clause may <em>not</em> contain subqueries, references to other
tables, functions, or [bound parameters].  The LIKE, GLOB, MATCH,
and REGEXP operators in SQLite are implemented as functions by the same name.
^Since functions are prohibited in the 
WHERE clause of a CREATE INDEX statement, so too are the LIKE, GLOB,
MATCH, and REGEXP operators.</p>

<p>

Changes to pages/pgszchng2016.in.

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
a 4096 byte page is a faster and better choice.
So, beginning with SQLite [version 3.12.0] (circa 2016) the default 
page size for new database files has been increased to 4096 bytes.
</p>

<p>
The upper bound on the database [cache_size|cache size] has 
traditionally defaulted to 2000 page.  SQLite [version 3.12.0] also
changes this default setting to be "-2000" which means 2000*1024
bytes, regardless of page size.  So, the upper bound on the amount
of memory used for the page cache is unchanged.
</p>

<h2>2.0 <u>Not</u> a Compatibility Break</h2>








|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
a 4096 byte page is a faster and better choice.
So, beginning with SQLite [version 3.12.0] (circa 2016) the default 
page size for new database files has been increased to 4096 bytes.
</p>

<p>
The upper bound on the database [cache_size|cache size] has 
traditionally defaulted to 2000 pages.  SQLite [version 3.12.0] also
changes this default setting to be "-2000" which means 2000*1024
bytes, regardless of page size.  So, the upper bound on the amount
of memory used for the page cache is unchanged.
</p>

<h2>2.0 <u>Not</u> a Compatibility Break</h2>

Changes to pages/pragma.in.

798
799
800
801
802
803
804
805

806
807
808
809
810
811
812
813
    <p>^When a new database is created, SQLite assigned a page size to
    the database based on platform and filesystem.  For many years,
    the default page size was almost always 1024 bytes, but beginning
    with SQLite [version 3.12.0] in 2016, the default page size increased
    to 4096.

    <p>^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.  ^(If the page_size pragma is
    used to specify a new page size just prior to
    running the [VACUUM] command and if the database is not in
    [WAL | WAL journal mode] then [VACUUM] will change the page
    size to the new value.)^</p>

    <p>^The [SQLITE_DEFAULT_PAGE_SIZE] compile-time option can be used
    to change the default page size assigned to new databases.







|
>
|







798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
    <p>^When a new database is created, SQLite assigned a page size to
    the database based on platform and filesystem.  For many years,
    the default page size was almost always 1024 bytes, but beginning
    with SQLite [version 3.12.0] in 2016, the default page size increased
    to 4096.

    <p>^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 statement) and if the database is not in
    [WAL mode].  ^(If the page_size pragma is
    used to specify a new page size just prior to
    running the [VACUUM] command and if the database is not in
    [WAL | WAL journal mode] then [VACUUM] will change the page
    size to the new value.)^</p>

    <p>^The [SQLITE_DEFAULT_PAGE_SIZE] compile-time option can be used
    to change the default page size assigned to new databases.

Changes to pages/quickstart.in.

16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

<ul>
<li><p>At a shell or DOS prompt, enter: "<b>sqlite3 test.db</b>".  This will
create a new database named "test.db".  (You can use a different name if
you like.)</p></li>
<li><p>Enter SQL commands at the prompt to create and populate the
new database.</p></li>
<li><p>Additional documentation is available [CLI | here]</li>
</ul>

<h2>Write Programs That Use SQLite</h2>

<ul>
<li><p>Below is a simple 
[http://www.tcl-lang.org | TCL program] that demonstrates how to use







|







16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

<ul>
<li><p>At a shell or DOS prompt, enter: "<b>sqlite3 test.db</b>".  This will
create a new database named "test.db".  (You can use a different name if
you like.)</p></li>
<li><p>Enter SQL commands at the prompt to create and populate the
new database.</p></li>
<li><p>Additional documentation is available [CLI | here].</li>
</ul>

<h2>Write Programs That Use SQLite</h2>

<ul>
<li><p>Below is a simple 
[http://www.tcl-lang.org | TCL program] that demonstrates how to use

Changes to pages/serverless.in.

27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<p>
On the other hand, a database engine that uses a server can 
provide better protection from bugs in the client 
application - stray pointers in a client cannot corrupt memory 
on the server. 
And because a server is a single persistent process,
it is able to control database access with more precision, 
allowing for finer grain locking and better concurrency.
</p>

<p>
Most SQL database engines are client/server based. 
Of those that are serverless, SQLite is the only one
known to this author that allows multiple applications
to access the same database at the same time. 







|







27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<p>
On the other hand, a database engine that uses a server can 
provide better protection from bugs in the client 
application - stray pointers in a client cannot corrupt memory 
on the server. 
And because a server is a single persistent process,
it is able to control database access with more precision, 
allowing for finer-grained locking and better concurrency.
</p>

<p>
Most SQL database engines are client/server based. 
Of those that are serverless, SQLite is the only one
known to this author that allows multiple applications
to access the same database at the same time. 

Changes to pages/threadsafe.in.

1
2
3
4
5
6
7
8
9
10
11
12
13
<title>Using SQLite In Multi-Threaded Applications</title>
<tcl>hd_keywords {threading mode}</tcl>

<h2>SQLite And Multiple Threads</h2>

<p>SQLite support three different threading modes:</p>

<ol>
<li><p><b>Single-thread</b>.
In this mode, all mutexes are disabled and SQLite is unsafe to use in
more than a single thread at once.</p></li>

<li><p><b>Multi-thread</b>.





|







1
2
3
4
5
6
7
8
9
10
11
12
13
<title>Using SQLite In Multi-Threaded Applications</title>
<tcl>hd_keywords {threading mode}</tcl>

<h2>SQLite And Multiple Threads</h2>

<p>SQLite supports three different threading modes:</p>

<ol>
<li><p><b>Single-thread</b>.
In this mode, all mutexes are disabled and SQLite is unsafe to use in
more than a single thread at once.</p></li>

<li><p><b>Multi-thread</b>.