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 | 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 | | | 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 | 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. | | | 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 | } CODE { int sqlite3_vfs_register(sqlite3_vfs*, int makeDflt); } PARAGRAPH { | | | | 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 | 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 | | | 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 | 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 { | | | | 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 | 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 { | | | 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 | 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 | | | | 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 | 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. | | | | | | | 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 | sqlite3_int64 iThreshold ); } PARAGRAPH { The [sqlite3_memory_alarm] routine is used to register a callback on memory allocation events. | | | 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 | <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> | | | 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 | 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> | | | | 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 | </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 | | | 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 | 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 | | | 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 | 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, | | | 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 | (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, | | | 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 | 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>), | | | 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 | <a href="pragma.html#pragma_journal_mode">journal_mode</a> PRAGMA. For example:</p> <blockquote><pre> PRAGMA journal_mode=PERSIST; </per></blockquote> | | | 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 | 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 | | | 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 | ^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 | | | 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 | ** 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 | | | | | 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 | 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 | | | 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 | [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" { | | | 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 | 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 {}}} { | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | sqlite> (((select * from tbl1;))) hello!|10 goodbye|20 sqlite> } </tcl> | | | 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 | <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 | | | 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 | <title>SQLite Developer Links</title> <h1 align="center">Developer Resources</h1> <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 | 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 | | < | > > > > > > | < > > > > > | 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 | </td> <td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td> <td valign="top"> <h3>Current Status</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 | <title>Page Not Found</title> | | | 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 | [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. | | | 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 | 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. | | | 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 | 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 | | | 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 | <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 | | > | | 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 | <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> | | | 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 | <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, | | | 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 | <title>Using SQLite In Multi-Threaded Applications</title> <tcl>hd_keywords {threading mode}</tcl> <h2>SQLite And Multiple Threads</h2> | | | 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>. |
︙ | ︙ |