Recent SQLite News

Small. Fast. Reliable.
Choose any three.

About Sitemap Documentation Download License News Support |

## Recent News

### 2011-September-19 - Version 3.7.8

> SQLite version 3.7.8 is a quarterly maintenance release. Upgrading from versions 3.7.6.3, 3.7.7, or 3.7.7.1 is optional. Upgrading from other versions is recommended.

> This release features a new "external merge sort" algorithm used to implement ORDER BY and GROUP BY and also to presort the content of an index for CREATE INDEX. The new algorithm does approximately the same number of comparisons and I/Os as before, but the I/Os are much more sequential and so runtimes are greatly reduced when the size of the set being sorted is larger than the filesystem cache. The performance improvement can be dramatic - orders of magnitude faster for large CREATE INDEX commands. On the other hand, the code is slightly slower (1% or 2%) for a small CREATE INDEX. Since CREATE INDEX is not an operation that commonly occurs on a speed-critical path, we feel that this tradeoff is a good one. The slight slowdown for small CREATE INDEX statements might be recovered in a future release. ORDER BY and GROUP BY operations should now be faster for all cases, large and small.

> The query planner has been enhanced to do a better job of handling the DISTINCT keyword on SELECT statements.

> There has been a lot of work on the default VFSes. The unix VFS has been enhanced to include more overrideable system calls - a feature requested by Chromium to make it easier to build SQLite into a sandbox. The windows VFS has been enhanced to be more resistant to interference from anti-virus software.

> Every version of SQLite is better tested than the previous, and 3.7.8 is no exception to this rule. Version 3.7.8 has been used internally by the SQLite team for mission critical functions and has performed flawlessly. And, of course, it passes our rigorous testing procedures with no problems detected. Version 3.7.8 is recommended for all new development.

### 2011-June-28 - Version 3.7.7.1

> SQLite version 3.7.7.1 adds a one-line bug fix to 3.7.7 to fix a problem causing PRAGMA case_sensitive_like statements compiled using the legacy sqlite3_prepare() interface to fail with an SQLITE_SCHEMA error. Because sqlite3_exec() uses sqlite3_prepare() internally, the problem also affects sqlite3_exec().

> Upgrading from 3.7.7 is only required for applications that use "PRAGMA case_sensitive_like" and the sqlite3_prepare() (or sqlite3_exec()) interface.

### 2011-June-24 - Version 3.7.7

> SQLite version 3.7.7 is a regularly scheduled bi-monthly maintenance release. Upgrading from version 3.7.6.3 is optional. Upgrading from all prior releases is recommended.

> This release adds support for naming database files using URI filenames. URI filenames are disabled by default (for backwards compatibility) but applications are encouraged to enable them since incompatibilities are likely to be exceedingly rare and the feature is useful. See the URI filename documentation for details.

> Most of the other enhancements in this release involve virtual tables. The virtual table interface has been enhanced to support SAVEPOINT and ON CONFLICT clause processing, and the built-in RTREE and FTS3/FTS4 have been augmented to take advantage of the new capability. This means, for example, that it is now possible to use the REPLACE command on FTS3/FTS4 and RTREE tables.

> The FTS4 full-text index extension has been enhanced to support the FTS4 prefix option and the FTS4 order option. These two enhancements are provided in support of search-as-you-type interfaces where search results begin to appear after the first keystroke in the "search" box and are refined with each subsequent keystroke. The way this is done is to do a separate full-text search after each key stroke, and add the "*" wildcard at the end of the word currently being typed. So, for example, if the text typed so far is "fast da" and the next character typed is "t", then the application does a full-text search of the pattern "fast dat*" and displays the results. Such capability has always existed. What is new is that the FTS4 prefix option allows the search to be very fast (a matter of milliseconds) even for difficult cases such as "t*" or "th*".

> There has been a fair amount of work done on the FTS4 module for this release. But the core SQLite code has changed little and the previous release has not given any problems, so we expect this to be a very stable release.

### 2011-May-19 - Version 3.7.6.3

> SQLite version 3.7.6.3 is a patch release that fixes a single bug associated with WAL mode. The bug has been in SQLite ever since WAL was added, but the problem is very obscure and so nobody has noticed before now. Nevertheless, all users are encouraged to upgrade to version 3.7.6.3 or later.

> The bug is this: If the cache_size is set very small (less than 10) and SQLite comes under memory pressure and if a multi-statement transaction is started in which the last statement prior to COMMIT is a SELECT statement and if a checkpoint occurs right after the transaction commit, then it might happen that the transaction will be silently rolled back instead of being committed.

> The default setting for cache_size is 2000. So in most situations, this bug will never appear. But sometimes programmers set cache_size to very small values on gadgets and other low-memory devices in order to save memory space. Such applications are vulnerable. Note that this bug does not cause database corruption. It is as if ROLLBACK were being run instead of COMMIT in some cases.

> Bug Details

> Transactions commit in WAL mode by adding a record onto the end of the WAL (the write-ahead log) that contains a "commit" flag. So to commit a transaction, SQLite takes all the pages that have changed during that transaction, appends them to the WAL, and sets the commit flag on the last page. Now, if SQLite comes under memory pressure, it might try to free up memory space by writing changed pages to the WAL prior to the commit. We call this "spilling" the cache to WAL. There is nothing wrong with spilling cache to WAL. But if the memory pressure is severe, it might be that by the time COMMIT is run, all changed pages for the transaction have already been spilled to WAL and there are no pages left to be written to WAL. And with no unwritten pages, there was nothing to put the commit flag on. And without a commit flag, the transaction would end up being rolled back.

> The fix to this problem was that if all changed pages has already been written to the WAL when the commit was started, then page 1 of the database will be written to the WAL again, so that there will always be a page available on which to set the commit flag.

### 2011-April-17 - Version 3.7.6.2

> SQLite version 3.7.6.2 adds a one-line bug fix to 3.7.6.1 that enables pthreads to work correctly on NetBSD. The problem was a faulty function signature for the open system call. The problem does not appear to have any adverse impact on any system other than NetBSD.

> Upgrading from version 3.7.6.1 is only needed on NetBSD.

### 2011-April-13 - Version 3.7.6.1

> SQLite version 3.7.6.1 fixes a single bug in 3.7.6 that can cause a segfault if SQLITE_FCNTL_SIZE_HINT is used on a unix build that has SQLITE_ENABLE_LOCKING_MODE set to 0 and is compiled with HAVE_POSIX_FALLOCATE.

> Upgrading from 3.7.6 is only needed for users effected by the configuration-specific bug described above. There are no other changes to the code.

### 2011-April-12 - Version 3.7.6

> SQLite version 3.7.6 is a regularly scheduled bi-monthly maintenance release of SQLite. Upgrading from version 3.7.5 is optional. Upgrading releases prior to 3.7.5 is recommended.

### 2011-February-01 - Version 3.7.5

> SQLite version 3.7.5 is a regularly scheduled bi-monthly maintenance release of SQLite. Due to the discovery and fix of an obscure bug that could cause database corruption, upgrading from all prior releases of SQLite is recommended. This bug was found during code review and has not been observed in the wild.

> This release adds new opcodes for the sqlite3_db_status() interface that allow more precise measurement of how the lookaside memory allocator is performing, which can be useful for tuning in applications with very tight memory constraints.

> The sqlite3_vsnprintf() interface was added. This routine is simply a varargs version of the long-standing sqlite3_snprintf() interface.

> The output from sqlite3_trace() interface has been enhanced to work better (and faster) in systems that use recursive extensions such as FTS3 or RTREE.

> Testing with Valgrind shows that this release of SQLite is about 1% or 2% faster than the previous release for most operations.

> A fork of the popular ADO.NET adaptor for SQLite known as System.Data.SQLite is now available on http://System.Data.SQLite.org/. The originator of System.Data.SQLite, Robert Simpson, is aware of this fork, has expressed his approval, and has commit privileges on the new Fossil repository. The SQLite development team intends to maintain System.Data.SQLite moving forward.

### 2010-December-08 - Version 3.7.4

> SQLite version 3.7.4 is a regularly scheduled bi-monthly maintenance release of SQLite. Upgrading from version 3.7.2 and version 3.7.3 is optional. Upgrading from all other SQLite releases is recommended.

> This release features full-text search enhancements. The older FTS3 virtual table is still fully supported, and should also run faster. In addition, the new FTS4 virtual table is added. FTS4 follows the same syntax as FTS3 but holds additional metadata which facilitates some performance improvements and more advanced matchinfo() output. Look for further full-text search enhancements in subsequent releases.

> Also in this release, the EXPLAIN QUERY PLAN output has been enhanced and new documentation is provided so that application developers can more easily understand how SQLite is performing their queries.

> Thanks to an account from the folks at http://www.devio.us/, OpenBSD has been added to the list of platforms upon which we test SQLite prior to every release. That list of platforms now includes:

> - Linux x86 & x86_64
> - MacOS 10.5 & 10.6
> - MacOS 10.2 PowerPC
> - WinXP and Win7
> - Android 2.2
> - OpenBSD 4.7

> The previous release of SQLite (version 3.7.3) has proven to be very robust. The only serious issue discovered was ticket 80ba201079 that describes an incorrect query result that can occur under very unusual circumstances. The ticket description contains details of the problem. Suffice it to say here that the problem is very obscure and is unlikely to effect most applications and so upgrading is optional. The problem is fixed, of course, in this release.

### 2010-October-08 - Version 3.7.3

> SQLite version 3.7.3 is a regularly scheduled bi-monthly maintenance release of SQLite. Upgrading from version 3.7.2 is optional. Upgrading from all other releases is recommended.

> This release adds two new interfaces (really just variations on existing interfaces). The sqlite3_create_function_v2() interface adds a destructor for the application-data pointer. The new sqlite3_soft_heap_limit64() interface allows the soft heap limit to be set to a value greater than 231.

> The RTREE extension has been enhanced with the ability to have an application-defined query region. This might be used, for example, to locate all objects within the field of view of a camera.

> The 3.7.3 release also includes some performance enhancements, including query planner improvements, documentation updates, and fixes to some very obscure bugs.

### 2010-August-24 - Version 3.7.2

> SQLite version 3.7.2 fixes a long-standing bug that can cause the database free-page list to go corrupt if incremental_vacuum is used multiple times to partially reduce the size of a database file that contains many hundreds of unused database pages. The original bug reports together with links to the patch that fixes it can be seen here.

> This bug has been in the code for at least a year and possibly longer. The bug has nothing to do with the versions 3.7.1 or 3.7.0 or any other recent release. The fact that the bug was discovered (and fixed) within hours of the 3.7.1 release is purely a coincidence.

> The bug is impossible to hit without using incremental_vacuum and is very difficult to hit even with incremental_vacuum. And the kind of corruption that the bug causes can usually be fixed simply by running VACUUM. Nevertheless, because the bug can result in database corruption, it is recommended that all SQLite users upgrade to version 3.7.2 or later.

### 2010-August-23 - Version 3.7.1

> SQLite version 3.7.1 is a stabilization release for the 3.7.x series. Other than the filesize-in-header bug that was fixed in version 3.7.0.1, no major problems have been seen in 3.7.0. Some minor corner-case performance regressions have been fixed. A typo in the OS/2 interface has been repaired.

> A biggest part of the 3.7.1 release is a cleanup and refactoring of the pager module within SQLite. This refactoring should have no application-visible effects. The purpose was to reorganize the code in ways that make it easier to prove correctness.

> The 3.7.1 release adds new experimental methods for obtained more detailed memory usage information and for controlling database file fragmentation. And the query planner now does a better job of optimizing the LIKE and GLOB operators.

> This release increases the maximum size of database pages from 32KiB to 64KiB. A database with 64KiB pages will not be readable or writable by older versions of SQLite. Note that further increases in page size are not feasible since the file format uses 16-bit offsets to structures within each page.

### 2010-August-04 - Version 3.7.0.1

> SQLite version 3.7.0.1 is a patch release to fix a bug in the new filesize-in-header feature of the SQLite file format that could cause database corruption if the same database file is written alternately with version 3.7.0 and version 3.6.23.1 or earlier. A performance regression was also fixed in this release.

### 2010-July-22 - Version 3.7.0

> SQLite version 3.7.0 is a major release of SQLite that features a new transaction control mechanism using a write-ahead log or WAL. The traditional rollback-journal is still used as the default so there should be no visible change for legacy programs. But newer programs can take advantage of improved performance and concurrency by enabling the WAL journaling mode.

> SQLite version 3.7.0 also contains some query planner enhancements and a few obscure bug fixes, but the only really big change is the addition of WAL mode.

Old news...