SQLite

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects. SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen() If you need a GUI for database management, you can install NAVICAT. SQLite is a compact library. With all features enabled, the library size can be less than 300KiB, depending on compiler optimization settings. (Some compiler optimizations such as aggressive function inlining and loop unrolling can cause the object code to be much larger.) If optional features are omitted, the size of the SQLite library can be reduced below 180KiB.

SQLite can also be made to run in minimal stack space (4KiB) and very little heap (100KiB), making SQLite a popular database engine choice on memory constrained gadgets such as cellphones, PDAs, and MP3 players. There is a tradeoff between memory usage and speed. SQLite generally runs faster the more memory you give it. Nevertheless, performance is usually quite good even in low-memory environments.

• Add support for PostgreSQL-style UPSERT.
• Add support for auxiliary columns in r-tree tables.
• Add C-language APIs for discovering SQL keywords used by SQLite: sqlite3_keyword_count(), sqlite3_keyword_name(), and sqlite3_keyword_check().
• Add C-language APIs for dynamic strings based on the sqlite3_str object.
• Enhance ALTER TABLE so that it recognizes "true" and "false" as valid arguments to DEFAULT.
• Add the sorter-reference optimization as a compile-time option. Only available if compiled with SQLITE_ENABLE_SORTER_REFERENCES.
• Improve the format of the EXPLAIN QUERY PLAN raw output, so that it gives better information about the query plan and about the relationships between the various components of the plan.
• Added the SQLITE_DBCONFIG_RESET_DATABASE option to the sqlite3_db_config() API.

CLI Enhancements:

• Automatically intercepts the raw EXPLAIN QUERY PLAN output and reformats it into an ASCII-art graph.
• Lines that begin with "#" and that are not in the middle of an SQL statement are interpreted as comments.
• Added the --append option to the ".backup" command.
• Added the ".dbconfig" command.

Performance:

• UPDATE avoids writing database pages that do not actually change. For example, "UPDATE t1 SET x=25 WHERE y=?" becomes a no-op if the value in column x is already 25. Similarly, when doing UPDATE on records that span multiple pages, only write the subset of pages that contain the changed value(s).
• Queries that use ORDER BY and LIMIT now try to avoid computing rows that cannot possibly come in under the LIMIT. This can greatly improve performance of ORDER BY LIMIT queries, especially when the LIMIT is small relative to the number of unrestricted output rows.
• The OR optimization is allowed to proceed even if the OR expression has also been converted into an IN expression. Uses of the OR optimization are now also more clearly shown in the EXPLAIN QUERY PLAN output.
• The query planner is more aggressive about using automatic indexes for views and subqueries for which it is not possible to create a persistent index.
• Make use of the one-pass UPDATE and DELETE query plans in the R-Tree extension where appropriate.
• Performance improvements in the LEMON-generated parser.

Bug fixes:

• For the right-hand table of a LEFT JOIN, compute the values of expressions directly rather than loading precomputed values out of an expression index as the expression index might not contain the correct value. Ticket 7fa8049685b50b5aeb0c2
• Do not attempt to use terms from the WHERE clause to enable indexed lookup of the right-hand table of a LEFT JOIN. Ticket 4ba5abf65c5b0f9a96a7a
• Fix a memory leak that can occur following a failure to open error in the CSV virtual table
• Fix a long-standing problem wherein a corrupt schema on the sqlite_sequence table used by AUTOINCREMENT can lead to a crash. Ticket d8dc2b3a58cd5dc2918a1
• Fix the json_each() function so that it returns valid results on its "fullkey" column when the input is a simple value rather than an array or object.