SQLite 3.14.1

SQLite is a lightweight and embeddable database. It's fully ACID and SQL92 compliant with 100% test coverage. As storage a single cross-platform disk file format is used, which may be up to terrabytes in size. It requires no setup and little administrative work. SQLite itself has a simple yet expandable API. Bindings for most programming languages exist on all major plattforms.

Tags c sql database sqlite embedded
License Public Domain
State stable

Recent Releases

3.14.113 Aug 2016 18:45 security: A performance enhancement to the page-cache "truncate" operation Reduces COMMIT time by dozens of milliseconds on systems with a Large page cache.
3.1409 Aug 2016 09:25 security: Added support for WITHOUT ROWID virtual tables. Improved the query planner so that the OR optimization can be used on virtual tables even if one or more of the disjuncts. Use the LIKE, GLOB, REGEXP, MATCH operators. Added the CSV virtual table for reading RFC 4180 formatted comma-separated. Value files. Added the carray() table-valued function extension. Enabled persistent loadable extensions using the new SQLITE_OK_LOAD_PERMANENTLY return code from the extension. Entry point. Added the SQLITE_DBSTATUS_CACHE_USED_SHARED option to sqlite3_db_status(). Add the Vfsstat.c Loadable extension - a VFS shim that measures I/O Together with an eponymous virtual table that provides access to the measurements. Improved algorithm for running queries with both an ORDER BY and a LIMIT where Only the inner-most loop naturally generates rows in the correct order. Enhancements to Lemon parser generator, so that it generates a Faster parser. The PRAGMA compile_options command now attempts to show the version number of the compiler that generated the library. Enhance PRAGMA table_info so that it provides information about. Eponymous virtual tables. Added the "win32-none" VFS, analogous to the "unix-none" VFS, that works like The default "win32" VFS except that it ignores all file locks. The query planner uses a full scan of a partial index instead of a Full scan of the main table, in cases where that makes sense. Allow table-valued functions to appear on the right-hand side of an IN operator. Created the dbhash.exe command-line utility. Added two new C-language interfaces: sqlite3_expanded_sql() and Sqlite3_trace_v2(). These new interfaces subsume the functions of Sqlite3_trace() and sqlite3_profile() which are now deprecated. Added the json_quote() SQL function to the json1 extension. Disable the authorizer callback while reparsing the schema. Added the SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION compile-time option and turned that Option on by def
3.13.019 May 2016 15:25 security: Postpone I/O associated with TEMP files for as long as possible, with the hope That the I/O can ultimately be avoided completely. Merged the session extension into trunk. Added the ".auth ON OFF" command to the command-line shell. Added the "--indent" option to the ".schema" and ".fullschema" commands of The command-line shell, to turn on pretty-printing. Added the ".eqp full" option to the command-line shell, that does both EXPLAIN And EXPLAIN QUERY PLAN on each statement that is evaluated. Improved unicode filename handling in the command-line shell on Windows. Improved resistance against goofy query planner decisions caused by Incomplete or incorrect modifications to the sqlite_stat1 Table by the application. Added the sqlite3_db_config (db, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION ) interface Which allows the sqlite3_load_extension() C-API to be enabled while keeping the Load_extension() SQL function disabled for security. Change the temporary directory search algorithm on Unix to allow directories with Write and execute permission, but without read permission, to serve as temporary Directories. Apply this same standard to the "." fallback directory... : a problem with the multi-row one-pass DELETE optimization that was. Causing it to compute incorrect answers with a self-referential subquery in The WHERE clause. for dc6ebeda9396087 a possible segfault with DELETE when table is a rowid table with an INTEGER PRIMARY KEY and the WHERE clause contains a OR and. The table has one or more indexes that are able to trigger the OR optimization, But none of the indexes reference any table columns other than the INTEGER PRIMARY KEY. When checking for the WHERE-clause push-down optimization, verify that all terms of the compound inner SELECT are non-aggregate, not just the last term. for f7f8c97e97597.
3.12.220 Apr 2016 07:45 security: a backwards compatibility problem in version 3.12.0 and 3.12.1: Columns declared as. "INTEGER" PRIMARY KEY (with quotes around. The datatype keyword) where not being recognized as an INTEGER PRIMARY KEY, which resulted in an incompatible database file. a (present since version 3.9.0 ) that can cause the DELETE. Operation to miss rows if PRAGMA reverse_unordered_selects is turned on. a 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.
3.12.111 Apr 2016 08:05 security: a boundary condition error introduced by version 3.12.0 That can result in a crash during heavy SAVEPOINT usage. views so that they inherit column datatypes from the Table that they are defined against, when possible.
3.12.030 Mar 2016 17:45 security: 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. Version 3.12.0 page size change for further information... Performance enhancements: Enhancements to the Lemon. Parser generator so that it creates a smaller and faster SQL parser. Only create master journal files if two or more attached databases are all Modified, do not have PRAGMA synchronous set to OFF, and do not have the journal_mode set to OFF, MEMORY, or WAL. Only create statement journal files when their size exceeds a threshold. Otherwise the journal is held in memory and no I/O occurs. The threshold. Can be configured at compile-time using SQLITE_STMTJRNL_SPILL or at Start-time using sqlite3_config ( SQLITE_CONFIG_STMTJRNL_SPILL ). The query planner is able to optimize IN operators on virtual tables Even if the xBestIndex method does not set the Sqlite3_index_constraint_usage.omit flag of the Virtual table column to the left of the IN operator. The query planner now does a better job of optimizing virtual table Accesses in a 3-way or higher join where constraints on the virtual Table are split across two or more other tables of the join. More efficient handling of application-defined SQL functions, especially in cases where the application defines hundreds or thousands of. Custom functions. The query planner considers the LIMIT clause when estimating the cost of ORDER BY. The configure script (on unix) automatically detects. Pread() and pwrite() and sets compile-time options to use those OS Interfaces if they are available. Reduce the amount of memory needed to hold the schema. Other miscellaneous micro-optimizations for improved performance and reduced Memory usage... New Features: Added the SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER option to sqlite3_db_config(). Which allows the two-argument version of the fts3_tokenizer() S
3.11.104 Mar 2016 07:45 security: Improvements to the Makefiles and build scripts used by VisualStudio. an FTS5 in which the 'optimize' command could cause index corruption. a buffer overread that might occur if FTS5 is used to query a corrupt Database file.
3.11.016 Feb 2016 15:45 security: Enhanced WAL mode so that it works efficiently with transactions that are Larger than the cache_size. Added the FTS5 detail option. Added the "EXTRA" option to PRAGMA synchronous that does a sync of the Containing directory when a rollback journal is unlinked in DELETE mode, For better durability. The SQLITE_EXTRA_DURABLE compile-time option enables PRAGMA synchronous=EXTRA by default. Enhanced the query planner so that it is able to use a covering index as part of the OR optimization. Avoid recomputing NOT NULL and CHECK constraints on unchanged. Columns in UPDATE statement. Many micro-optimizations, resulting in a library that is Faster than the previous release... Enhancements to the command-line shell : By default, the shell is now in "auto-explain" mode. The output of EXPLAIN commands is automatically formatted. Added the ".vfslist" dot-command. The SQLITE_ENABLE_EXPLAIN_COMMENTS compile-time option is now turned on by default in the standard builds... Enhancements to the TCL Interface : If a database connection is opened with the "-uri 1" option, then URI filenames are honored by the "backup" and "restore" commands. Added the "-sourceid" option to the "sqlite3" command... Makefile improvements: Improved pthreads detection in configure scripts. Add the ability to do MSVC Windows builds from the amalgamation tarball..., an with incorrect sharing of VDBE temporary registers between co-routines that could cause incorrect query results in obscure cases. d06a25c84454a. a problem in the sqlite3_result_subtype() interface that could. Cause problems for the json1 extension under obscure circumstances. For f45ac567eaa9f9. Escape control characters in JSON strings. for ad2559db380abf8. Reenable the xCurrentTime and xGetLastError methods in the built-in Unix VFSes as long as SQLITE_OMIT_DEPRECATED is not defined... Backwards Compatibility:
3.10.222 Jan 2016 01:25 feature: Version 3.10.0 introduced a case-folding in the LIKE operator which is by this patch release. 80369eddd5c94... Other miscellaneous : a use-after-free that can occur when SQLite is compiled with -DSQLITE_HAS_CODEC. the build so that it works with -DSQLITE_OMIT_WAL.
3.10.007 Jan 2016 20:05 feature: Added support for LIKE, GLOB, and REGEXP operators on virtual tables. Added the colUsed field to sqlite3_index_info for use by The sqlite3_module.xBestIndex method. Enhance the PRAGMA cache_spill statement to accept a 32-bit integer Parameter which is the threshold below which cache spilling is prohibited. On unix, if a symlink to a database file is opened, then the corresponding Journal files are based on the actual filename, not the symlink name. Added the "--transaction" option to sqldiff. Added the sqlite3_db_cacheflush() interface. Added the sqlite3_strlike() interface. When using memory-mapped I/O map the database file read-only so that stray pointers And/or array overruns in the application cannot accidently modify the database file. Added the. experimental sqlite3_snapshot_get(), sqlite3_snapshot_open(), And sqlite3_snapshot_free() interfaces. These are subject to change or removal in a subsequent release. Enhance the 'utc' modifier in the date and time functions so that it is a no-op if. The date/time is known to already be in UTC. (This is not a compatibility break since The behavior has long been documented as "undefined" in that case.) Added the json_group_array() and json_group_object() SQL functions in the. Json extension. Added the SQLITE_LIKE_DOESNT_MATCH_BLOBS compile-time option. Many small performance optimizations... Portability enhancements: Work around a sign-exension in the optimizer of the HP C compiler on HP/UX. details).. Enhancements to the command-line shell : Added the ".changes ON OFF" and ".vfsinfo" dot-commands. Translate between MBCS and UTF8 when. Running in cmd.exe on Windows... Enhancements to makefiles: Added the --enable-editline and --enable-static-shell options to the various autoconf-generated configure scripts. Omit all use of "awk" in the makefiles, to make building easier for MSVC users... Important : inconsistent integer to floating-point comparison operations that. Could result in
3.9.203 Nov 2015 20:45 feature: The schema parser so that it interprets certain obscure and ill-formed) CREATE TABLE statements the same as legacy. for ac661962a2aeab3c331.
3.9.121 Oct 2015 03:25 feature: The json1 extension so that it does. not recognize ASCII form-feed as a Whitespace character, in order to comply with RFC-7159. for 57eec374ae1d0a1d
3.9.016 Oct 2015 09:45 feature: The version numbering conventions for SQLite are revised to use the Emerging standard of semantic versioning... New Features And Enhancements: Added the json1 extension module in the source tree, and in the amalgamation. Enable support using the SQLITE_ENABLE_JSON1 compile-time option. Added Full Text Search version 5 (FTS5) to the amalgamation, enabled. Using SQLITE_ENABLE_FTS5. FTS5 will be considered "experimental" (subject to incompatible changes) for at least one more release cycle. The CREATE VIEW statement now accepts an optional list of. Column names following the view name. Added support for indexes on expressions. Added support for table-valued functions in the FROM clause of a SELECT statement. Added support for eponymous virtual tables. A VIEW may now reference undefined tables and functions when. Initially created. Missing tables and functions are reported when The VIEW is used in a query. Added the sqlite3_value_subtype() and sqlite3_result_subtype() Interfaced. The query planner is now able to use partial indexes that contain AND-connected terms in the WHERE clause. The sqlite3_analyzer.exe utility is updated to report the depth of. Each btree and to show the average fanout for indexes and WITHOUT ROWID tables. Enhanced the dbstat virtual table so that it can be used as a. Table-valued function where the argument is the schema to be Analyzed... Other changes: The sqlite3_memory_alarm() interface, which has been deprecated and. Undocumented for 8 years, is changed into a no-op... Important : a critical in the SQLite Encryption Extension that. Could cause the database to become unreadable and unrecoverable if a VACUUM command Changed the size of the encryption nonce. Added a memory barrier in the implementation of Sqlite3_initialize() to help ensure that it is thread-safe. the OR optimization so that it always ignores subplans that do not use an index. Aug 2015 18:45 feature: Restore an undocumented side-effect of PRAGMA cache_size: force the database schema to be parsed if the database has not been previously accessed.
3.8.1128 Jul 2015 12:05 feature: Added the experimental RBU extension. Note that this extension is experimental and subject to change in incompatible ways. Added the experimental FTS5 extension. Note that this extension is experimental and subject to change in incompatible ways. Added the sqlite3_value_dup() and sqlite3_value_free() interfaces. Enhance the spellextension to support ON CONFLICT clauses. The IS operator is now able to drive indexes. Enhance the query planner to permit automatic indexing on FROM-clause subqueries that are implemented by co-routine. Disallow the use of "rowid" in common table expressions. Added the PRAGMA cell_size_check command for better and earlier detection of database file corruption. Added the matchinfo 'b' flag to the matchinfo() function in FTS3. Improved fuzz-testing of database files, with fixes for problems found. Add the fuzzcheck test program and automatically run this program using both SQL and database test cases on "make test". Added the SQLITE_MUTEX_STATIC_VFS1 static mutex and use it in the Windows VFS. The sqlite3_profile() callback is invoked (by sqlite3_reset() or sqlite3_finalize()) for statements that did not run to completion. Enhance the page cache so that it can preallocate a block of memory to use for the initial set page cache lines. Set the default preallocation to 100 pages. Yields about a 5 performance increase on common workloads. Miscellaneous micro-optimizations result in 22.3 more work for the same number of CPU cycles relative to the previous release. SQLite now runs twice as fast as version 3.8.0 and three times as fast as version 3.3.9. Measured using cachegrind on the speedtest1.c workload on Ubuntu 14.04 x64 with gcc 4.8.2 and -Os. Your performance may vary.). Added the sqlite3_result_zeroblob64() and sqlite3_bind_zeroblob64() interfaces... Important bug fixes: Fix CREATE TABLE AS so that columns of type TEXT never end up holding an INT value. Ticket f2ad7de056ab1dc9200. Fix CREATE TABLE AS so that it does not leave NULL entrie May 2015 19:45 minor bugfix: Fix an index corruption issue introduced by version 3.8.7. An index with a TEXT key can be corrupted by an INSERT into the corresponding table if the table has two nested triggers that convert the key value to INTEGER and back to TEXT again. May 2015 02:05 feature: Make sqlite3_compileoption_used() responsive to the SQLITE_ENABLE_DBSTAT_VTAB compile-time option. Fix a harmless warning in the command-line shell on some versions of MSVC.
3.8.1008 May 2015 18:05 feature: Added the sqldiff.exe utility program for computing the differences between two SQLite database files. Added the y format string to the matchinfo() function of FTS3. Performance improvements for ORDER BY, VACUUM, CREATE INDEX, PRAGMA integrity_check, and PRAGMA quick_check. Fix many obscure problems discovered while SQL fuzzing. Identify all methods for important objects in the interface documentation. example). Made the American Fuzzy Lop fuzzer a standard part of SQLite's testing strategy. Add the ".binary" and ".limits" commands to the command-line shell.
3.8.910 Apr 2015 01:45 feature: Add VxWorks-7 as an officially supported and tested platform. Added the sqlite3_status64() interface. Fix memory size tracking so that it works even if SQLite uses more than 2GiB of memory. Added the PRAGMA index_xinfo command. Fix a potential 32-bit integer overflow problem in the sqlite3_blob_read() and sqlite3_blob_write() interfaces. Ensure that prepared statements automatically reset on extended error codes of SQLITE_BUSY and SQLITE_LOCKED even when compiled using SQLITE_OMIT_AUTORESET. Correct miscounts in the sqlite3_analyzer.exe utility related to WITHOUT ROWID tables. Added the ".dbinfo" command to the command-line shell. Improve the performance of fts3/4 queries that use the OR operator and at least one auxiliary fts function. Feb 2015 22:05 feature: Fix a bug that can lead to incorrect results if the qualifying constraint of a partial index appears in the ON clause of a LEFT JOIN. Jan 2015 07:25 minor bugfix: Fix a bug in the sorting logic, present since version 3.8.4, that can cause output to appear in the wrong order on queries that contains an ORDER BY clause, a LIMIT clause, and that have approximately 60 or more columns in the result set. Jan 2015 10:45 feature: Fix a bug in the sorting logic, present since version 3.8.4, that can cause output to appear in the wrong order on queries that contains an ORDER BY clause, a LIMIT clause, and that have approximately 60 or more columns in the result set.
3.8.817 Jan 2015 16:25 feature: Added the PRAGMA data_version command that can be used to determine if a database file has been modified by another process. Added the SQLITE_CHECKPOINT_TRUNCATE option to the sqlite3_wal_checkpoint_v2() interface, with corresponding enhancements to PRAGMA wal_checkpoint. Added the sqlite3_stmt_scanstatus() interface, available only when compiled with SQLITE_ENABLE_STMT_SCANSTATUS. The sqlite3_table_column_metadata() is enhanced to work correctly on WITHOUT ROWID tables and to check for the existence of a a table if the column name parameter is NULL. The interface is now also included in the build by default, without requiring the SQLITE_ENABLE_COLUMN_METADATA compile-time option. Added the SQLITE_ENABLE_API_ARMOR compile-time option. Added the SQLITE_REVERSE_UNORDERED_SELECTS compile-time option. Added the SQLITE_SORTER_PMASZ compile-time option and SQLITE_CONFIG_PMASZ start-time option. Added the SQLITE_CONFIG_PCACHE_HDRSZ option to sqlite3_config() which makes it easier for applications to determine the appropriate amount of memory for use with SQLITE_CONFIG_PAGECACHE. The number of rows in a VALUES clause is no longer limited by SQLITE_LIMIT_COMPOUND_SELECT. Added the eval.c loadable extension that implements an eval() SQL function that will recursively evaluate SQL. Performance Enhancements: Reduce the number of memcpy() operations involved in balancing a b-tree, for 3.2 overall performance boost. Improvements to cost estimates for the skip-scan optimization. The automatic indexing optimization is now capable of generating a partial index if that is appropriate. Bug fixes: Ensure durability following a power loss with PRAGMA journal_mode=TRUNCATE" by calling fsync() right after truncating the journal file. The query planner now recognizes that any column in the right-hand table of a LEFT JOIN can be NULL, even if that column has a NOT NULL constraint. Avoid trying to optimize out NULL tests in those cases. Fix for ticket 6f2222d550f5b0ee7ed. Make sure ORDER BY Dec 2014 12:25 feature: This release fixes adds in a mutex that is required by the changes of the patch but was accidently omitted. The mutex was not required by any of the internal SQLite tests, but Firefox crashes without it. Test cases have been added to ensure that mutex is never again missed. Dec 2014 20:05 feature: Bug fix: Ensure the cached KeyInfo objects (an internal abstraction not visible to the application) do not go stale when operating in shared cache mode and frequently closing and reopening some database connections while leaving other database connections on the same shared cache open continuously. Ticket e4a18565a36884b00edf. Nov 2014 03:19 feature: Enhance the ROLLBACK command so that pending queries are allowed to continue as long as the schema is unchanged. Formerly, a ROLLBACK would cause all pending queries to fail with an SQLITE_ABORT or SQLITE_ABORT_ROLLBACK error. That error is still returned if the ROLLBACK modifies the schema. Bug fix: Make sure that NULL results from OP_Column are fully and completely NULL and do not have the MEM_Ephem bit set. Bug fix: The c format in sqlite3_mprintf() is able to handle precisions greater than 70. Bug fix: Do not automatically remove the DISTINCT keyword from a SELECT that forms the right-hand side of an IN operator since it is necessary if the SELECT also contains a LIMIT. Oct 2014 03:15 feature: In PRAGMA journal_mode=TRUNCATE mode, call fsync() immediately after truncating the journal file to ensure that the transaction is durable across a power loss. Fix an assertion fault that can occur when updating the NULL value of a field at the end of a table that was added using ALTER TABLE ADD COLUMN. Do not attempt to use the strchrnul() function from the standard C library unless the HAVE_STRCHRNULL compile-time option is set.
3.8.718 Oct 2014 03:17 feature: Many micro-optimizations result in 20.3 more work for the same number of CPU cycles relative to the previous release. The cumulative performance increase since version 3.8.0 is 61 . Measured using cachegrind on the speedtest1.c workload on Ubuntu 13.10 x64 with gcc 4.8.1 and -Os. Your performance may vary.) The sorter can use auxiliary helper threads to increase real-time response. This feature is off by default and may be enabled using the PRAGMA threads command or the SQLITE_DEFAULT_WORKER_THREADS compile-time option. Enhance the skip-scan optimization so that it is able to skip index terms that occur in the middle of the index, not just as the left-hand side of the index. Improved optimization of CAST operators. Various improvements in how the query planner uses sqlite_stat4 information to estimate plan costs. New Features: Added new interfaces with 64-bit length parameters: sqlite3_malloc64(), sqlite3_realloc64(), sqlite3_bind_blob64(), sqlite3_result_blob64(), sqlite3_bind_text64(), and sqlite3_result_text64(). Added the new interface sqlite3_msize() that returns the size of a memory allocation obtained from sqlite3_malloc64() and its variants. Added the SQLITE_LIMIT_WORKER_THREADS option to sqlite3_limit() and PRAGMA threads command for configuring the number of available worker threads. The spellfix1 extension allows the application to optionally specify the rowid for each INSERT. Added the User Authentication extension. Bug Fixes: Fix a bug in the partial index implementation that might result in an incorrect answer if a partial index is used in a subquery or in a view. Fix a query planner bug that might cause a table to be scanned in the wrong direction thus reversing the order of output) when a DESC index is used to implement the ORDER BY clause on a query that has an identical GROUP BY clause. Fix a bug in sqlite3_trace() that was causing it to sometimes fail to print an SQL statement if that statement needed to be re-prepared. Fix a faulty assert() st
3.8.616 Aug 2014 03:15 feature: Added support for hexadecimal integer literals in the SQL parser. Ex: 0x123abc) Enhanced the PRAGMA integrity_check command to detect UNIQUE and NOT NULL constraint violations. Increase the maximum value of SQLITE_MAX_ATTACHED from 62 to 125. Increase the timeout in WAL mode before issuing an SQLITE_PROTOCOL error from 1 second to 10 seconds. Added the likely(X) SQL function. The unicode61 tokenizer is now included in FTS4 by default. Trigger automatic reprepares on all prepared statements when ANALYZE is run. Added a new loadable extension source code file to the source tree: fileio.c Add extension functions readfile(X) and writefile(X,Y) using code copy/pasted from fileio.c in the previous bullet) to the command-line shell. Added the .fullschema dot-command to the command-line shell. Performance Enhancements: Deactivate the DISTINCT keyword on subqueries on the right-hand side of the IN operator. Add the capability of evaluating an IN operator as a sequence of comparisons as an alternative to using a table lookup. Use the sequence of comparisons implementation in circumstances where it is likely to be faster, such as when the right-hand side of the IN operator is small and/or changes frequently. The query planner now uses sqlite_stat4 information (created by ANALYZE) to help determine if the skip-scan optimization is appropriate. Ensure that the query planner never tries to use a self-made transient index in place of a schema-defined index. Other minor tweaks to improve the quality of VDBE code. Bug Fixes: Fix a bug in CREATE UNIQUE INDEX, introduced when WITHOUT ROWID support added in version 3.8.2, that allows a non-unique NOT NULL column to be given a UNIQUE index. Fix a bug in R-Tree extension, introduced in the previous release, that can cause an incorrect results for queries that use the rowid of the R-Tree on the left-hand side of an IN operator. Fix the sqlite3_stmt_busy() interface so that it gives the correct answer for ROLLBAC