SQLite Version 3 Overview

SQLite version 3.0 introduces important changes to the library, including:

  • A more compact format for database files.
  • Manifest typing and BLOB support.
  • Support for both UTF-8 and UTF-16 text.
  • User-defined text collating sequences.
  • 64-bit ROWIDs.
  • Improved Concurrency.

This document is a quick introduction to the changes for SQLite 3.0 for users who are already familiar with SQLite version 2.8.

Naming Changes

SQLite version 2.8 will continue to be supported with bug fixes for the foreseeable future. In order to allow SQLite version 2.8 and SQLite version 3.0 to peacefully coexist, the names of key files and APIs in SQLite version 3.0 have been changed to include the character "3". For example, the include file used by C programs has been changed from "sqlite.h" to "sqlite3.h". And the name of the shell program used to interact with databases has been changed from "sqlite.exe" to "sqlite3.exe". With these changes, it is possible to have both SQLite 2.8 and SQLite 3.0 installed on the same system at the same time. And it is possible for the same C program to link against both SQLite 2.8 and SQLite 3.0 at the same time and to use both libraries at the same time.

New File Format

The format used by SQLite database files has been completely revised. The old version 2.1 format and the new 3.0 format are incompatible with one another. Version 2.8 of SQLite will not read a version 3.0 database files and version 3.0 of SQLite will not read a version 2.8 database file.

To convert an SQLite 2.8 database into an SQLite 3.0 database, have ready the command-line shells for both version 2.8 and 3.0. Then enter a command like the following:

sqlite OLD.DB .dump | sqlite3 NEW.DB

The new database file format uses B+trees for tables. In a B+tree, all data is stored in the leaves of the tree instead of in both the leaves and the intermediate branch nodes. The use of B+trees for tables allows for better scalability and the storage of larger data fields without the use of overflow pages. Traditional B-trees are still used for indices.

The new file format also supports variable pages sizes between 512 and 32768 bytes. The size of a page is stored in the file header so the same library can read databases with different pages sizes, in theory, though this feature has not yet been implemented in practice.

The new file format omits unused fields from its disk images. For example, indices use only the key part of a B-tree record and not the data. So for indices, the field that records the length of the data is omitted. Integer values such as the length of key and data are stored using a variable-length encoding so that only one or two bytes are required to store the most common cases but up to 64-bits of information can be encoded if needed. Integer and floating point data is stored on the disk in binary rather than being converted into ASCII as in SQLite version 2.8. These changes taken together result in database files that are typically 25% to 35% smaller than the equivalent files in SQLite version 2.8.

Details of the low-level B-tree format used in SQLite version 3.0 can be found in header comments to the btreeInt.h source file and in the file format documentation.

Manifest Typing and BLOB Support

SQLite version 2.8 will deal with data in various formats internally, but when writing to the disk or interacting through its API, SQLite 2.8 always converts data into ASCII text. SQLite 3.0, in contrast, exposes its internal data representations to the user and stores binary representations to disk when appropriate. The exposing of non-ASCII representations was added in order to support BLOBs.

SQLite version 2.8 had the feature that any type of data could be stored in any table column regardless of the declared type of that column. This feature is retained in version 3.0, though in a slightly modified form. Each table column will store any type of data, though columns have an affinity for the format of data defined by their declared datatype. When data is inserted into a column, that column will make at attempt to convert the data format into the columns declared type. All SQL database engines do this. The difference is that SQLite 3.0 will still store the data even if a format conversion is not possible.

For example, if you have a table column declared to be of type "INTEGER" and you try to insert a string, the column will look at the text string and see if it looks like a number. If the string does look like a number it is converted into a number and into an integer if the number does not have a fractional part, and stored that way. But if the string is not a well-formed number it is still stored as a string. A column with a type of "TEXT" tries to convert numbers into an ASCII-Text representation before storing them. But BLOBs are stored in TEXT columns as BLOBs because you cannot in general convert a BLOB into text.

In most other SQL database engines the datatype is associated with the table column that holds the data - with the data container. In SQLite 3.0, the datatype is associated with the data itself, not with its container. Paul Graham in his book ANSI Common Lisp calls this property "Manifest Typing". Other writers have other definitions for the term "manifest typing", so beware of confusion. But by whatever name, that is the datatype model supported by SQLite 3.0.

Additional information about datatypes in SQLite version 3.0 is available separately.

Support for UTF-8 and UTF-16

The new API for SQLite 3.0 contains routines that accept text as both UTF-8 and UTF-16 in the native byte order of the host machine. Each database file manages text as either UTF-8, UTF-16BE (big-endian), or UTF-16LE (little-endian). Internally and in the disk file, the same text representation is used everywhere. If the text representation specified by the database file (in the file header) does not match the text representation required by the interface routines, then text is converted on-the-fly. Constantly converting text from one representation to another can be computationally expensive, so it is suggested that programmers choose a single representation and stick with it throughout their application.

In the current implementation of SQLite, the SQL parser only works with UTF-8 text. So if you supply UTF-16 text it will be converted. This is just an implementation issue and there is nothing to prevent future versions of SQLite from parsing UTF-16 encoded SQL natively.

When creating new user-defined SQL functions and collating sequences, each function or collating sequence can specify it if works with UTF-8, UTF-16be, or UTF-16le. Separate implementations can be registered for each encoding. If an SQL function or collating sequences is required but a version for the current text encoding is not available, then the text is automatically converted. As before, this conversion takes computation time, so programmers are advised to pick a single encoding and stick with it in order to minimize the amount of unnecessary format juggling.

SQLite is not particular about the text it receives and is more than happy to process text strings that are not normalized or even well-formed UTF-8 or UTF-16. Thus, programmers who want to store IS08859 data can do so using the UTF-8 interfaces. As long as no attempts are made to use a UTF-16 collating sequence or SQL function, the byte sequence of the text will not be modified in any way.

User-defined Collating Sequences

A collating sequence is just a defined order for text. When SQLite 3.0 sorts (or uses a comparison operator like "<" or ">=") the sort order is first determined by the data type.

  • NULLs sort first
  • Numeric values sort next in numerical order
  • Text values come after numerics
  • BLOBs sort last

Collating sequences are used for comparing two text strings. The collating sequence does not change the ordering of NULLs, numbers, or BLOBs, only text.

A collating sequence is implemented as a function that takes the two strings being compared as inputs and returns negative, zero, or positive if the first string is less than, equal to, or greater than the second. SQLite 3.0 comes with a single built-in collating sequence named "BINARY" which is implemented using the memcmp() routine from the standard C library. The BINARY collating sequence works well for English text. For other languages or locales, alternative collating sequences may be preferred.

The decision of which collating sequence to use is controlled by the COLLATE clause in SQL. A COLLATE clause can occur on a table definition, to define a default collating sequence to a table column, or on field of an index, or in the ORDER BY clause of a SELECT statement. Planned enhancements to SQLite are to include standard CAST() syntax to allow the collating sequence of an expression to be defined.

64-bit ROWIDs

Every row of a table has a unique rowid. If the table defines a column with the type "INTEGER PRIMARY KEY" then that column becomes an alias for the rowid. But with or without an INTEGER PRIMARY KEY column, every row still has a rowid.

In SQLite version 3.0, the rowid is a 64-bit signed integer. This is an expansion of SQLite version 2.8 which only permitted rowids of 32-bits.

To minimize storage space, the 64-bit rowid is stored as a variable length integer. Rowids between 0 and 127 use only a single byte. Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three bytes. And so forth. Negative rowids are allowed but they always use nine bytes of storage and so their use is discouraged. When rowids are generated automatically by SQLite, they will always be non-negative.

Improved Concurrency

SQLite version 2.8 allowed multiple simultaneous readers or a single writer but not both. SQLite version 3.0 allows one process to begin writing the database while other processes continue to read. The writer must still obtain an exclusive lock on the database for a brief interval in order to commit its changes, but the exclusive lock is no longer required for the entire write operation. A more detailed report on the locking behavior of SQLite version 3.0 is available separately.

A limited form of table-level locking is now also available in SQLite. If each table is stored in a separate database file, those separate files can be attached to the main database (using the ATTACH command) and the combined databases will function as one. But locks will only be acquired on individual files as needed. So if you redefine "database" to mean two or more database files, then it is entirely possible for two processes to be writing to the same database at the same time. To further support this capability, commits of transactions involving two or more ATTACHed database are now atomic.

Credits

SQLite version 3.0 is made possible in part by AOL developers supporting and embracing great Open-Source Software.


Recent SQLite News

SQLite Logo
Small. Fast. Reliable.
Choose any three.
About Sitemap Documentation Download License News Support

Recent News

2010-Jan-06 - Version 3.6.22

SQLite version 3.6.22 is a bug-fix release. Two bugs have been fixed that might cause incorrect query results.

  • Ticket 31338dca7e describes a problem with queries that have a WHERE clause of the form (x AND y) OR z where x and z come from one table of a join and y comes from a different table.
  • Ticket eb5548a849 describes a problem where the use of the CAST operator in the WHERE clause can lead to incorrect results if the column being cast to a new datatype is also used in the same WHERE clause without being cast.
Both bugs are obscure, but because they could arise in an application after deployment, it is recommended that all applications upgrade SQLite to version 3.6.22.

This release also includes other minor bug fixes and performance enhancements, especially in the FTS3 extension.

2009-Dec-07 - Version 3.6.21

SQLite version 3.6.21 focuses on performance optimization. For a certain set of traces, this version uses 12% fewer CPU instructions than the previous release (as measured by valgrind). In addition, the FTS3 extension has been through an extensive cleanup and rework and the sqlite3_trace() interface has been modified to insert bound parameter values into its output.

2009-Nov-04 - Version 3.6.20

SQLite version 3.6.20 is a general maintenance release. The query planner has been enhanced to work better with bound parameters in LIKE and GLOB operators and in range constraints and various minor bugs have been fixed. Upgrading from 3.6.19 is optional.

2009-Oct-14 - Version 3.6.19

SQLite version 3.6.19 adds native support for foreign key constraints, including deferred constraints and cascading deletes. Enforcement of foreign keys is disabled by default for backwards compatibility and must be turned on using the foreign_keys pragma.

Version 3.6.19 also adds support for the IS and IS NOT operators. Formerly, SQLite (as most other SQL database engines) supported IS NULL and IS NOT NULL. The IS and IS NOT operators are generalizations that allow the right-hand side to be an arbitrary expression. IS and IS NOT work the same as == (equals) and != (not equals) except that with IS and IS NOT the NULL values compare equal to one another.

2009-Sep-11 - Version 3.6.18

Beginning with this release, the SQLite source code is tracked and managed using the Fossil distributed configuration management system. SQLite was previously versioned using CVS. The entire CVS history has been imported into Fossil. The older CVS repository remains on the website but is not read-only.

There are two major enhancements in SQLite version 3.6.18. The first is a series or refinements to the query planner that help SQLite to choose better plans for joins where in the past it was selecting suboptimal query plans. The SQLITE_ENABLE_STAT2 compile-time option has been added to cause SQLite to collect histogram data on indices when the ANALYZE command is run. The use of histograms improve the query planning performance even more.

The second major enhancement is that SQLite now support recursive triggers. The older non-recursive behavior of triggers is still the default behavior. Recursive triggers are activated using the recursive_triggers pragma. In addition to allowing triggers to call themselves (either directly or indirectly) the new capability also fires DELETE triggers on rows that are removed from a table as a result of REPLACE conflict resolution processing.

Non-recursive triggers are still the default behavior since this is least likely to cause problems for existing applications. However, we anticipate that triggers will become recursive by default beginning with release 3.7.0. At that point, applications that want to continue using the older non-recursive trigger behavior will need to use the recursive_triggers pragma to disable recursive triggers.

This version of SQLite also contains bug fixes, though none of the bugs are serious and all are obscure, so upgrading is optional.

The SQLite core continues to have 100% branch test coverage and so despite the many changes in this release, the developers believe that this version of SQLite is stable and ready for production use.

2009-Aug-10 - Version 3.6.17

This is a monthly maintenance release with a focus of bug fixes, performance improvements, and increased test coverage. This is the first release of SQLite since 100% branch test coverage was achieved on the SQLite core.

In addition, a new interface sqlite3_strnicmp() is provided for the convenience of extension writers.

None of the bugs fixed in this release are serious. All bugs are obscure. Upgrading is optional.

2009-July-25 - 100% Branch Test Coverage

A subset of the TH3 test suite was measured by gcov to provide 100% branch test coverage over the SQLite core (exclusive of the VFS backend and of extensions such as FTS3 and RTREE) when compiled for SuSE 10.1 Linux on x86. The SQLite developers pledge to maintain branch test coverage at 100% in all future releases. Ongoing work will strive for 100% branch test coverage on the operating-system backends and extensions as well.

2009-Jun-27 - Version 3.6.16

SQLite version 3.6.16 is another general maintenance relase containing performance and robustness enhancements. A single notable bug was fixed (ticket #3929). This bug cause cause INSERT or UPDATE statements to fail on indexed tables that have AFTER triggers that modify the same table and index.

2009-Jun-15 - Version 3.6.15

SQLite version 3.6.15 is a general maintenance release containing performance and robustness enhancements and fixes for various obscure bugs.

2009-May-25 - Version 3.6.14.2

SQLite version 3.6.14.2 fixes an obscure bug in the code generator ( ticket #3879) section of SQLite which can potentially cause incorrect query results. The changes from the prior release consist of only this one bug fix, check-in [6676] and a change to the version number text.

The bug was introduced in version 3.6.14. It is recommended that users of version 3.6.14 and 3.6.14.1 upgrade to this release. Applications are unlikely to hit this bug, but since it is difficult to predict which applications might hit it and which might not, we recommend that all users of 3.6.14 and 3.5.14.1 upgrade to this release.

2009-May-19 - Version 3.6.14.1

SQLite version 3.6.14.1 is a patch release to version 3.6.14 with minimal changes that fixes three bugs. Upgrading is only necessary for users who are impacted by one or more of those bugs.

2009-May-07 - Version 3.6.14

SQLite version 3.6.14 provides new performance enhancements in the btree and pager layers and in the query optimizer. Certain workloads can be as much as twice as fast as the previous release, though 10% faster is a more typical result.

Queries against virtual tables that contain OR and IN operators in the WHERE clause are now able to use indexing.

A new optional asynchronous I/O backend is available for unix and windows. The asynchronous backend gives the illusion of faster response time by pushing slow write operations into a background thread. The tradeoff for faster response time is that more memory is required (to hold the content of the pending writes) and if a power failure or program crash occurs, some transactions that appeared to have committed might end up being rolled back upon restart.

This release also contains many minor bug fixes, documentation enhancements, new test cases, and cleanups and simplifications to the source code.

There is no compelling reason to upgrade from versions 3.6.12 or 3.6.13 if those prior versions are working. Though many users may benefit from the improved performance.

Old news...


Query Language Understood by SQLite

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

SQLite understands most of the standard SQL language. But it does omit some features while at the same time adding a few features of its own. This document attempts to describe precisely what parts of the SQL language SQLite does and does not support. A list of SQL keywords is also provided. The SQL language syntax is described by syntax diagrams.

The following syntax documentation topics are available:

  • aggregate functions
  • ALTER TABLE
  • ANALYZE
  • ATTACH DATABASE
  • BEGIN TRANSACTION
  • comment
  • COMMIT TRANSACTION
  • core functions
  • CREATE INDEX
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE VIEW
  • CREATE VIRTUAL TABLE
  • date and time functions
  • DELETE
  • DETACH DATABASE
  • DROP INDEX
  • DROP TABLE
  • DROP TRIGGER
  • DROP VIEW
  • END TRANSACTION
  • EXPLAIN
  • expression
  • INDEXED BY
  • INSERT
  • keywords
  • ON CONFLICT clause
  • PRAGMA
  • REINDEX
  • RELEASE SAVEPOINT
  • REPLACE
  • ROLLBACK TRANSACTION
  • SAVEPOINT
  • SELECT
  • UPDATE
  • VACUUM

The routines sqlite3_prepare_v2(), sqlite3_prepare(), sqlite3_prepare16(), sqlite3_exec(), and sqlite3_get_table() accept an SQL statement list (sql-stmt-list) which is a semicolon-separated list of statements.

sql-stmt-list:

syntax diagram sql-stmt-list

Each SQL statement in the statement list is an instance of the following:

sql-stmt:

syntax diagram sql-stmt


SQLite Query Language: Aggregate Functions

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

Aggregate Functions

The aggregate functions shown below are available by default. Additional aggregate functions written in C may be added using the sqlite3_create_function() API.

In any aggregate function that takes a single argument, that argument can be preceded by the keyword DISTINCT. In such cases, duplicate elements are filtered before being passed into the aggregate function. For example, the function "count(distinct X)" will return the number of distinct values of column X instead of the total number of non-null values in column X.

avg(X) | The avg() function returns the average value of all non-NULL X within a group. String and BLOB values that do not look like numbers are interpreted as 0. The result of avg() is always a floating point value as long as at there is at least one non-NULL input even if all inputs are integers. The result of avg() is NULL if and only if there are no non-NULL inputs.
count(X)

count(*) 		The count(X) function returns a count of the number of times that X is not NULL in a group. The count(*) function (with no arguments) returns the total number of rows in the group.
group_concat(X)

group_concat(X,Y) 		The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then is is used as the separator between instances of X. A comma (",") is used as the separator if Y is omitted. The order of the concatenated elements is arbitrary.
max(X) | The max() aggregate function returns the maximum value of all values in the group. The maximum value is the value that would be returned last in an ORDER BY on the same column. Aggregate max() returns NULL if and only if there are no non-NULL values in the group.
min(X) | The min() aggregate function returns the minimum non-NULL value of all values in the group. The minimum value is the first non-NULL value that would appear in an ORDER BY of the column. Aggregate min() returns NULL if and only if there are no non-NULL values in the group.
sum(X)

total(X) 		The sum() aggregate function returns sum of all non-NULL values in the group. If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it and most other SQL database engines implement sum() that way so SQLite does it in the same way in order to be compatible. The non-standard total() function is provided as a convenient way to work around this design problem in the SQL language.

The result of total() is always a floating point value. The result of sum() is an integer value if all non-NULL inputs are integers. If any input to sum() is neither an integer or a NULL then sum() returns a floating point value which might be an approximation to the true sum.

Sum() will throw an "integer overflow" exception if all inputs are integers or NULL and an integer overflow occurs at any point during the computation. Total() never throws an integer overflow.


SQLite Query Language: ALTER TABLE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

ALTER TABLE

alter-table-stmt:

syntax diagram alter-table-stmt

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

The RENAME TO syntax is used to rename the table identified by [database-name.]table-name to new-table-name. This command cannot be used to move a table between attached databases, only to rename a table within the same database.

If the table being renamed has triggers or indices, then these remain attached to the table after it has been renamed. However, if there are any view definitions, or statements executed by triggers that refer to the table being renamed, these are not automatically modified to use the new table name. If this is required, the triggers or view definitions must be dropped and recreated to use the new table name by hand.

If foreign key constraints are enabled when a table is renamed, then any REFERENCES clauses in any table (either the table being renamed or some other table) that refer to the table being renamed are modified to refer to the renamed table by its new name.

The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissable in a CREATE TABLE statement, with the following restrictions:

  • The column may not have a PRIMARY KEY or UNIQUE constraint.
  • The column may not have a default value of CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
  • If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
  • If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.

Note also that when adding a CHECK constraint, the CHECK constraint is not tested against preexisting rows of the table. This can result in a table that contains data that is in violation of the CHECK constraint. Future versions of SQLite might change to validate CHECK constraints as they are added.

The execution time of the ALTER TABLE command is independent of the amount of data in the table. The ALTER TABLE command runs as quickly on a table with 10 million rows as it does on a table with 1 row.

After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 and earlier.


SQLite Query Language: ANALYZE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

ANALYZE

analyze-stmt:

syntax diagram analyze-stmt

The ANALYZE command gathers statistics about indices and stores them in a special tables in the database where the query optimizer can use them to help make better index choices. If no arguments are given, all indices in all attached databases are analyzed. If a database name is given as the argument, all indices in that one database are analyzed. If the argument is a table name, then only indices associated with that one table are analyzed.

The default implementation stores all statistics in a single table named sqlite_stat1. If SQLite is compiled with the SQLITE_ENABLE_STAT2 option, then additional histogram data is collected and stored in sqlite_stat2. Future enhancements may create additional tables with the same name pattern except with the "1" or "2" changed to a different digit.

The DROP TABLE command does not work on the sqlite_stat1 or sqlite_stat2 tables, but all the content of those tables can be queried using SELECT and can be deleted, augmented, or modified using the DELETE, INSERT, and UPDATE commands. Appropriate care should be used when changing the content of the statistics tables as invalid content can cause SQLite to select inefficient query plans. Generally speaking, one should not modify the content of the statistics tables by any mechanism other than invoking the ANALYZE command.

Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.


SQLite Query Language: ATTACH DATABASE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

ATTACH DATABASE

attach-stmt:

syntax diagram attach-stmt

The ATTACH DATABASE statement adds another database file to the current database connection. The database-names 'main' and 'temp' refer to the main database and the database used for temporary tables. The main and temp databases cannot be attached or detached.

You cannot create a new table with the same name as a table in an attached database, but you can attach a database which contains tables whose names are duplicates of tables in the main database. If the system is not running in shared cache mode, it is also permissible to attach the same database file multiple times.

Tables in an attached database can be referred to using the syntax database-name.table-name. If the name of the table is unique across all attached databases and the main and temp databases, then the database-name prefix is not required. When a database is attached, all tables which don't have duplicate names become the default table of that name. Any tables of that name attached afterwards require the database prefix. If the default table of a given name is detached, then the last table of that name attached becomes the new default.

Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:". If the main database is ":memory:" then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.

There is a compile-time limit of SQLITE_MAX_ATTACHED attached database files.


SQLite Query Language: BEGIN TRANSACTION

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

BEGIN TRANSACTION

begin-stmt:

syntax diagram begin-stmt

commit-stmt:

syntax diagram commit-stmt

rollback-stmt:

syntax diagram rollback-stmt

No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.

END TRANSACTION is an alias for COMMIT.

Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands. The "TO SAVEPOINT name" clause of the ROLLBACK command shown in the syntax diagram above is only applicable to SAVEPOINT transactions. An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by SAVEPOINT or a prior BEGIN. The COMMIT command and the ROLLBACK command without the TO clause work the same on SAVEPOINT transactions as they do with transactions started by BEGIN.

Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.

An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized. An open sqlite3_blob used for incremental BLOB I/O counts as an unfinished statement. The sqlite3_blob finishes when it is closed.

The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write operations, the COMMIT command will fail with a error code SQLITE_BUSY.

An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.

The ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. Both read-only and read/write queries will cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because bad things will happen if the in-memory image of the database is changed out from under an active query.

If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined.

Response To Errors Within A Transaction

If certain kinds of errors occur within a transaction, the transaction may or may not be rolled back automatically. The errors that cause the behavior include:

  • SQLITE_FULL: database or disk full
  • SQLITE_IOERR: disk I/O error
  • SQLITE_BUSY: database in use by another process
  • SQLITE_NOMEM: out or memory
  • SQLITE_INTERRUPT: processing interrupted by application request

For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction. An application can tell which course of action SQLite took by using the sqlite3_get_autocommit() C-language interface.

It is recommended that applications respond to the errors listed above by explicitly issuing a ROLLBACK command. If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.

Future versions of SQLite may extend the list of errors which might cause automatic transaction rollback. Future versions of SQLite might change the error response. In particular, we may choose to simplify the interface in future versions of SQLite by causing the errors above to force an unconditional rollback.


SQLite Query Language: comment

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

comment

comment-syntax:

syntax diagram comment-syntax

Comments are not SQL commands, but can occur within the text of SQL queries passed to sqlite3_prepare_v2() and related interfaces. Comments are treated as whitespace by the parser. Comments can begin anywhere whitespace can be found, including inside expressions that span multiple lines.

SQL comments begin with two consecutive "-" characters (ASCII 0x2d) and extend up to and including the next newline character (ASCII 0x0a) or until the end of input, whichever comes first.

C-style comments begin with "/*" and extend up to and including the next "*/" character pair or until the end of input, whichever comes first. C-style comments can span multiple lines.

Comments can appear anywhere whitespace can occur, including inside expressions and in the middle of other SQL statements. Comments do not nest.


SQLite Query Language: Core Functions

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

Core Functions

The core functions shown below are available by default. Date & Time functions and aggregate functions are documented separately. An application may define additional functions written in C and added to the database engine using the sqlite3_create_function() API.

abs(X) | The abs(X) function returns the absolute value of the numeric argument X. Abs(X) returns NULL if X is NULL. Abs(X) return 0.0 if X is a string or blob that cannot be converted to a numeric value. If X is the integer -9223372036854775807 then abs(X) throws an integer overflow error since there is no equivalent positive 64-bit two complement value.
changes() | The changes() function returns the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement. The changes() SQL function is a wrapper around the sqlite3_changes() C/C++ function and hence follows the same rules for counting changes.
coalesce(X,Y,...) | The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. Coalesce() must be at least 2 arguments.
glob(X,Y) | The glob(X,Y) function is equivalent to the expression "Y GLOB X". Note that the X and Y arguments are reversed in the glob() function relative to the infix GLOB operator. If the sqlite3_create_function() interface is used to override the glob(X,Y) function with an alternative implementation then the GLOB operator will invoke the alternative implementation.
ifnull(X,Y) | The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.
hex(X) | The hex() function interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal rendering of the content of that blob.
last_insert_rowid() | The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.
length(X) | The length(X) function returns the length of X in characters if X is a string, or in bytes if X is a blob. If X is NULL then length(X) is NULL. If X is numeric then length(X) returns the length of a string representation of X.
like(X,Y)

like(X,Y,Z) 		The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the optional ESCAPE clause is present, then the like() function is invoked with three arguments. Otherwise, it is invoked with two arguments only. Note that the X and Y parameters are reversed in the like() function relative to the infix LIKE operator. The sqlite3_create_function() interface can be used to override the like() function and thereby change the operation of the LIKE operator. When overriding the like() function, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.
load_extension(X)

load_extension(X,Y) 		The load_extension(X,Y) function loads SQLite extensions out of the shared library file named X using the entry point Y. The result of load_extension() is always a NULL. If Y is omitted then the default entry point of sqlite3_extension_init is used. The load_extension() function raises an exception if the extension fails to load or initialize correctly.

The load_extension() function will fail if the extension attempts to modify or delete a SQL function or collating sequence. The extension can add new functions or collating sequences, but cannot modify or delete existing functions or collating sequences because those functions and/or collating sequences might be used elsewhere in the currently running SQL statement. To load an extension that changes or deletes functions or collating sequences, use the sqlite3_load_extension() C-language API.

lower(X) | The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.
ltrim(X)

ltrim(X,Y) 		The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X. If the Y argument is omitted, ltrim(X) removes spaces from the left side of X.
max(X,Y,...) | The multi-argument max() function returns the argument with the maximum value, or return NULL if any argument is NULL. The multi-argument max() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If none of the arguments to max() define a collating function, then the BINARY collating function is used. Note that max() is a simple function when it has 2 or more arguments but operates as an aggregate function if given only a single argument.
min(X,Y,...) | The multi-argument min() function returns the argument with the minimum value. The multi-argument min() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If none of the arguments to min() define a collating function, then the BINARY collating function is used. Note that min() is a simple function when it has 2 or more arguments but operates as an aggregate function if given only a single argument.
nullif(X,Y) | The nullif(X,Y) function returns its first argument if the arguments are different and NULL if the arguments are the same. The nullif(X,Y) function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If neither argument to nullif() defines a collating function then the BINARY is used.
quote(X) | The quote(X) function returns a string which is the value of its argument suitable for inclusion into another SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals.
random() | The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
randomblob(N) The randomblob(N) function return an N-byte blob containing pseudo-random bytes. If N is less than 1 then a 1-byte random blob is returned.

Hint: applications can generate globally unique identifiers using this function together with hex() and/or lower() like this:

hex(randomblob(16))

lower(hex(randomblob(16)))
replace(X,Y,Z) | The replace(X,Y,Z) function returns a string formed by substituting string Z for every occurrance of string Y in string X. The BINARY collating sequence is used for comparisons. If Y is an empty string then return X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior to processing.
round(X)

round(X,Y) 		The round(X,Y) function returns a string representation of the floating-point value X rounded to Y digits to the right of the decimal point. If the Y argument is omitted, the X value is truncated to an integer.
rtrim(X)

rtrim(X,Y) 		The rtrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the right side of X. If the Y argument is omitted, rtrim(X) removes spaces from the right side of X.
soundex(X) | The soundex(X) function returns a string that is the soundex encoding of the string X. The string "?000" is returned if the argument is NULL or contains no ASCII alphabetic characters. This function is omitted from SQLite by default. It is only available if the SQLITE_SOUNDEX compile-time option is used when SQLite is built.
sqlite_source_id() | The sqlite_source_id() function returns a string that identifies the specific version of the source code that was used to build the SQLite library. The string returned by sqlite_source_id() begins with the date and time that the source code was checked in and is follows by an SHA1 hash that uniquely identifies the source tree. This function is an SQL wrapper around the sqlite3_sourceid() C interface.
sqlite_version() | The sqlite_version() function returns the version string for the SQLite library that is running. This function is an SQL wrapper around the sqlite3_libversion() C-interface.
substr(X,Y,Z)

substr(X,Y) 		The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all character through the end of the string X beginning with the Y-th. The left-most character of X is number 1. If Y is negative the the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceeding the Y-th character are returned. If X is string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.
total_changes() | The total_changes() function returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the current database connection was opened. This function is a wrapper around the sqlite3_total_changes() C/C++ interface.
trim(X)

trim(X,Y) 		The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both ends of X. If the Y argument is omitted, trim(X) removes spaces from both ends of X.
typeof(X) | The typeof(X) function returns a string that indicates the datatype of the expression X: "null", "integer", "real", "text", or "blob".
upper(X) | The upper(X) function returns a copy of input string X in which all lower-case ASCII characters are converted to their upper-case equivalent.
zeroblob(N) | The zeroblob(N) function returns a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using incremental BLOB I/O. This SQL function is implemented using the sqlite3_result_zeroblob() routine from the C/C++ interface.


SQLite Query Language: CREATE INDEX

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

CREATE INDEX

create-index-stmt:

syntax diagram create-index-stmt

indexed-column:

syntax diagram indexed-column

The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key.

Each column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order. The sort order may or may not be ignored depending on the database file format. The "legacy" file format ignores index sort order. The descending index file format takes index sort order into account. Only copies of SQLite newer than version 3.3.0 (released on 2006-01-10) are able to understand the newer descending index file format and so for compatibility with older versions of SQLite, the legacy file format is generated by default. Use the legacy_file_format pragma to modify this behavior and generate databases that use the newer file format. Future versions of SQLite may begin to generate the newer file format by default.

The COLLATE clause optionally following each column name defines a collating sequence used for text entries in that column. The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating sequence is otherwise defined, the built-in BINARY collating sequence is used.

There are no arbitrary limits on the number of indices that can be attached to a single table. The number of columns in an index is limited to the value set by sqlite3_limit(SQLITE_LIMIT_COLUMN,...).

If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error. For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the standard is ambiguious) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and Microsoft SQL Server follow the other interpretation of the standard.

If the optional IF NOT EXISTS clause is present and another index with the same name aleady exists, then this command becomes a no-op.

Indexes are removed with the DROP INDEX command.


SQLite Query Language: CREATE TABLE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

CREATE TABLE

create-table-stmt:

syntax diagram create-table-stmt

column-def:

syntax diagram column-def

type-name:

syntax diagram type-name

column-constraint:

syntax diagram column-constraint

table-constraint:

syntax diagram table-constraint

foreign-key-clause:

syntax diagram foreign-key-clause

A CREATE TABLE statement is basically the keywords "CREATE TABLE" followed by the name of a new table and a parenthesized list of column definitions and constraints. Tables names that begin with "sqlite_" are reserved for use by SQLite itself and cannot normally appear in a CREATE TABLE statement.

Each column definition is the name of the column optionally followed by the datatype for that column, then one or more optional column constraints. SQLite uses dynamic typing; the datatype for the column does not restrict what data may be put in that column. The UNIQUE constraint causes an unique index to be created on the specified columns. All NULL values are considered different from each other and from all other values for the purpose of determining uniqueness, hence a UNIQUE column may contain multiple entries with the value of NULL. The COLLATE clause specifies what text collating function to use when comparing text entries for the column. The built-in BINARY collating function is used by default.

The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant, a number, or a constant expression enclosed in parentheses. The default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant or number, it is inserted into the column whenever an INSERT statement that does not specify a value for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

The PRIMARY KEY attribute normally creates a UNIQUE index on the column or columns that are specified as the PRIMARY KEY. The only exception to this behavior is special INTEGER PRIMARY KEY column, described below. According to the SQL standard, PRIMARY KEY should imply NOT NULL. Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. SQLite allows NULL values in a PRIMARY KEY column. We could change SQLite to conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem. So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns. Developers should be aware, however, that we may change SQLite to conform to the SQL standard in future and should design new programs accordingly.

SQLite uses dynamic typing instead of static typing. Except for the special case of INTEGER PRIMARY KEY, SQLite will allow values of any type to be stored in any column regardless of the declared datatype of that column. The declared datatype is a type affinity that SQLite attempts to comply with, but the operation will proceed even if compliance is not possible.

If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible within that same database connection and is automatically deleted when the database connection is closed. Any indices created on a temporary table are also temporary. Temporary tables and indices are stored in a separate file distinct from the main database file.

If a <database-name> is specified, then the table is created in the named database. It is an error to specify both a <database-name> and the TEMP keyword, unless the <database-name> is "temp". If no database name is specified and the TEMP keyword is not present then the table is created in the main database.

The optional conflict clause following each constraint allows the specification of an alternative default constraint conflict resolution algorithm for that constraint. If no conflict clause is specified, the ABORT algorithm is used. Different constraints within the same table may have different conflict resolution algorithms. If an INSERT or UPDATE statement specifies a conflict resolution algorithm, then the algorithm specified on the INSERT or UPDATE statement overrides the algorithm specified in the CREATE TABLE statement. See the section titled ON CONFLICT for additional information.

CHECK constraints are supported as of version 3.3.0. Prior to version 3.3.0, CHECK constraints were parsed but not enforced.

The number of columns in a table is limited by the SQLITE_MAX_COLUMN compile-time parameter. A single row of a table cannot store more than SQLITE_MAX_LENGTH bytes of data. Both of these limits can be lowered at runtime using the sqlite3_limit() C/C++ interface.

The CREATE TABLE AS form defines the table to be the result set of a query. The names of the table columns are the names of the columns in the result.

If the optional IF NOT EXISTS clause is present and another table with the same name aleady exists, then this command becomes a no-op.

Tables are removed using the DROP TABLE statement.

ROWIDs and the INTEGER PRIMARY KEY

Every row of every SQLite table has a 64-bit signed integer key that is unique within the same table. This integer is usually called the "rowid". The rowid is the actual key used in the B-Tree that implements an SQLite table. Rows are stored in rowid order. The rowid value can be accessed using one of the special names "ROWID", "OID", or "_ROWID_" assuming those names are no used by other conventional table columns.

If a column is declared to be an INTEGER PRIMARY KEY, then that column is not a "real" database column but instead becomes an alias for the rowid. Unlike normal SQLite columns, the rowid must be a non-NULL integer value. The rowid is not able to hold floating point values, strings, BLOBs, or NULLs.

An INTEGER PRIMARY KEY column is an alias for the 64-bit signed integer rowid.

An INTEGER PRIMARY KEY column can also include the keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B-Tree keys are automatically generated. Additional detail on automatic B-Tree key generation is available separately.

The special behavior of INTEGER PRIMARY KEY is only available if the type name is exactly "INTEGER" in any mixture of upper and lower case. Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid. The special behavior of INTEGER PRIMARY KEY is only available if the primary key is a single column. Multi-column primary keys do not become aliases for the rowid. The AUTOINCREMENT keyword only works on a column that is an alias for the rowid.

Note that searches against a rowid are generally about twice as fast as searches against any other PRIMARY KEY or indexed value.

Goofy behavior alert: The following three declarations all cause the column "x" be an alias for the rowid:

  • CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
  • CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
  • CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));

But, in contrast, the following declaration does not result in "x" being an alias for the rowid:

  • CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

This asymmetrical behavior is unfortunate and is really due to a bug in the parser in early versions of SQLite. But fixing the bug would result in very serious backwards incompatibilities. The SQLite developers feel that goofy behavior in an corner case is far better than a compatibility break, so the original behavior is retained.


SQLite Query Language: CREATE TRIGGER

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

CREATE TRIGGER

create-trigger-stmt:

syntax diagram create-trigger-stmt

The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations that are automatically performed when a specified database event occurs.

A trigger may be specified to fire whenever a DELETE, INSERT, or UPDATE of a particular database table occurs, or whenever an UPDATE occurs on on one or more specified columns of a table.

At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies that the SQL statements specified in the trigger may be executed (depending on the WHEN clause) for each database row being inserted, updated or deleted by the statement causing the trigger to fire.

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on events for which they are relevant, as follows:

INSERT | NEW references are valid
UPDATE | NEW and OLD references are valid
DELETE | OLD references are valid

If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.

The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification or removal of the associated row.

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.

Triggers are automatically dropped when the table that they are associated with (the table-name table) is dropped. However if the the trigger actions reference other tables, the trigger is not dropped or modified if those other tables are dropped or modified.

Triggers are removed using the DROP TRIGGER statement.

Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

The UPDATE, DELETE, and INSERT statements within triggers do not support the full syntax for UPDATE, DELETE, and INSERT statements. The following restrictions apply:

  • The name of the table to be modified in an UPDATE, DELETE, or INSERT statement must be an unqualified table name. In other words, one must use just "tablename" not "database.tablename" when specifying the table. The table to be modified must exist in the same database as the table or view to which the trigger is attached.

  • The "INSERT INTO table DEFAULT VALUES" form of the INSERT statement is not supported.

  • The INDEXED BY and NOT INDEXED clauses are not supported for UPDATE and DELETE statements.

  • The ORDER BY and LIMIT clauses on UPDATE and DELETE statements are not supported. ORDER BY and LIMIT are not normally supported for UPDATE or DELETE in any context but can be enabled for top-level statements using the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. However, that compile-time option only applies to top-level UPDATE and DELETE statements, not UPDATE and DELETE statements within triggers.

INSTEAD OF trigger

Triggers may be created on views, as well as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is not an error to execute an INSERT, DELETE or UPDATE statement on the view, respectively. Instead, executing an INSERT, DELETE or UPDATE on the view causes the associated triggers to fire. The real tables underlying the view are not modified (except possibly explicitly, by a trigger program).

Note that the sqlite3_changes() and sqlite3_total_changes() interfaces do not count INSTEAD OF trigger firings, but the count_changes pragma does count INSTEAD OF trigger firing.

Examples

Assuming that customer records are stored in the "customers" table, and that order records are stored in the "orders" table, the following trigger ensures that all associated orders are redirected when a customer changes his or her address:

CREATE TRIGGER update_customer_address UPDATE OF address ON customers
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;

With this trigger installed, executing the statement:

UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';

causes the following to be automatically executed:

UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';

Cautions On The Use Of BEFORE triggers

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result of the subsequent update or delete operation is undefined. Furthermore, if a BEFORE trigger modifies or deletes a row, then it is undefined whether or not AFTER triggers that would have otherwise run on those rows will in fact run.

The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the rowid is not explicitly set to an integer.

Because of the behaviors described above, programmers are encouraged to prefer AFTER triggers over BEFORE triggers.

The RAISE() function

A special SQL function RAISE() may be used within a trigger-program, with the following syntax

raise-function:

syntax diagram raise-function

When one of the first three forms is called during trigger-program execution, the specified ON CONFLICT processing is performed (either ABORT, FAIL or ROLLBACK) and the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the application, along with the specified error message.

When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would of been executed are abandoned. No database changes are rolled back. If the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes execution at the beginning of the next step.


SQLite Query Language: CREATE VIEW

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

CREATE VIEW

create-view-stmt:

syntax diagram create-view-stmt

The CREATE VIEW command assigns a name to a pre-packaged SELECT statement. Once the view is created, it can be used in the FROM clause of another SELECT in place of a table name.

If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "VIEW" then the view that is created is only visible to the process that opened the database and is automatically deleted when the database is closed.

If a <database-name> is specified, then the view is created in the named database. It is an error to specify both a <database-name> and the TEMP keyword on a VIEW, unless the <database-name> is "temp". If no database name is specified, and the TEMP keyword is not present, the VIEW is created in the main database.

You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use an INSTEAD OF trigger on the view to accomplish the same thing. Views are removed with the DROP VIEW command.


SQLite Query Language: CREATE VIRTUAL TABLE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

CREATE VIRTUAL TABLE

create-virtual-table-stmt:

syntax diagram create-virtual-table-stmt

A virtual table is an interface to an external storage or computation engine that appears to be a table but does not actually store information in the database file.

In general, you can do anything with a virtual table that can be done with an ordinary table, except that you cannot create indices or triggers on a virtual table. Some virtual table implementations might impose additional restrictions. For example, many virtual tables are read-only.

The <module-name> is the name of an object that implements the virtual table. The <module-name> must be registered with the SQLite database connection using sqlite3_create_module() or sqlite3_create_module_v2() prior to issuing the CREATE VIRTUAL TABLE statement. The module takes zero or more comma-separated arguments. The arguments can be just about any text as long as it has balanced parentheses. The argument syntax is sufficiently general that the arguments can be made to appear as column definitions in a traditional CREATE TABLE statement. SQLite passes the module arguments directly to the xCreate and xConnect methods of the module implementation without any interpretation. It is the responsibility of the module implementation to parse and interpret its own arguments.

A virtual table is destroyed using the ordinary DROP TABLE statement. There is no DROP VIRTUAL TABLE statement.


SQLite Query Language: Date And Time Functions

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

Date And Time Functions

SQLite supports five date and time functions as follows:

  1. date(timestring, modifier, modifier, ...)
  2. time(timestring, modifier, modifier, ...)
  3. datetime(timestring, modifier, modifier, ...)
  4. julianday(timestring, modifier, modifier, ...)
  5. strftime(format, timestring, modifier, modifier, ...)

All five date and time functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument.

The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD HH:MM:SS". The julianday() function returns the Julian day - the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar). The strftime() routine returns the date formatted according to the format string specified as the first argument. The format string supports the most common substitutions found in the strftime() function from the standard C library plus two new substitutions, %f and %J. The following is a complete list of valid strftime() substitutions:

%d | day of month: 00
%f | fractional seconds: SS.SSS
%H | hour: 00-24
%j | day of year: 001-366
%J | Julian day number
%m | month: 01-12
%M | minute: 00-59
%s | seconds since 1970-01-01
%S | seconds: 00-59
%w | day of week 0-6 with sunday==0
%W | week of year: 00-53
%Y | year: 0000-9999
%% %

Notice that all other date and time functions can be expressed in terms of strftime():

Function | Equivalent strftime()
date(...) | strftime('%Y-%m-%d', ...)
time(...) | strftime('%H:%M:%S', ...)
datetime(...) | strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...) | strftime('%J', ...)

The only reasons for providing functions other than strftime() is for convenience and for efficiency.

Time Strings

A time string can be in any of the following formats:

  1. YYYY-MM-DD
  2. YYYY-MM-DD HH:MM
  3. YYYY-MM-DD HH:MM:SS
  4. YYYY-MM-DD HH:MM:SS.SSS
  5. YYYY-MM-DD T HH:MM
  6. YYYY-MM-DD T HH:MM:SS
  7. YYYY-MM-DD T HH:MM:SS.SSS
  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now
  12. DDDDDDDDDD

In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as a floating point value.

Modifiers

The time string can be followed by zero or more modifiers that alter the date and time string. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.

  1. NNN days
  2. NNN hours
  3. NNN minutes
  4. NNN.NNNN seconds
  5. NNN months
  6. NNN years
  7. start of month
  8. start of year
  9. start of day
  10. weekday N
  11. unixepoch
  12. localtime
  13. utc

The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the preceding timestring and modifiers. Note that "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.

The "start of" modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.

The "weekday" modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.

The "unixepoch" modifier (11) only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970. If the "unixepoch" modifier does not follow a timestring of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the "unixepoch" modifier from prior DDDDDDDDDD then the behavior is undefined. Due to precision limitations imposed by the implementations use of 64-bit integers, the "unixepoch" modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 10675199167).

The "localtime" modifier (12) assumes the time string to its left is in Universal Coordinated Time (UTC) and adjusts the time string so that it displays localtime. If "localtime" follows a time that is not UTC, then the behavior is undefined. The "utc" is the opposite of "localtime". "utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC. If the prior string is not in localtime, then the result of "utc" is undefined.

Examples

Compute the current date.

SELECT date('now');

Compute the last day of the current month.

SELECT date('now','start of month','+1 month','-1 day');

Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Compute the current unix timestamp.

SELECT strftime('%s','now');

Compute the number of days since the signing of the US Declaration of Independent.

SELECT julianday('now') - julianday('1776-07-04');

Compute the number of seconds since a particular moment in 2004:

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

Compute the date of the first Tuesday in October for the current year.

SELECT date('now','start of year','+9 months','weekday 2');

Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):

SELECT (julianday('now') - 2440587.5)*86400.0;

Caveats And Bugs

The computation of local time depends heavily on the whim of politicians and is thus difficult to get correct for all locales. In this implementation, the standard C library function localtime_r() is used to assist in the calculation of local time. The localtime_r() C function normally only works for years between 1970 and 2037. For dates outside this range, SQLite attempts to map the year into an equivalent year within this range, do the calculation, then map the year back.

These functions only work for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julidan day numbers 1721059.5 through 5373484.5). For dates outside that range, the results of these functions are undefined.

Non-Vista Windows platforms only support one set of DST rules. Vista only supports two. Therefore, on these platforms, historical DST calculations will be incorrect. For example, in the US, in 2007 the DST rules changed. Non-Vista Windows platforms apply the new 2007 DST rules to all previous years as well. Vista does somewhat better getting results correct back to 1986, when the rules were also changed.

All internal computations assume the Gregorian calendar system. It is also assumed that every day is exactly 86400 seconds in duration.


SQLite Query Language: DELETE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

DELETE

delete-stmt:

syntax diagram delete-stmt

qualified-table-name:

syntax diagram qualified-table-name

The DELETE command is used to remove records from a table. The command consists of the "DELETE FROM" keywords followed by the name of the table from which records are to be removed.

Without a WHERE clause, all rows of the table are removed. If a WHERE clause is supplied, then only those rows that match the expression are removed.

Restrictions on DELETE Statements Within CREATE TRIGGER

There are additional syntax restrictions on DELETE statements that occur within the body of a CREATE TRIGGER statement. The table-name must be unqualified. In other words, the database-name. prefix on the table name is not allowed within triggers. The table from which to delete must be in the same database as the table to which the trigger is attached.

The INDEXED BY and NOT INDEXED clauses are not allowed on DELETE statements within triggers.

The LIMIT clause (described below) is unsupported within triggers.

The Truncate Optimization

When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This "truncate" optimization makes the delete run much faster. Prior to SQLite version 3.6.5, the truncate optimization also meant that the sqlite3_changes() and sqlite3_total_changes() interfaces and the count_changes pragma will not actually return the number of deleted rows. That problem has been fixed as of version 3.6.5.

The truncate optimization can be permanently disabled for all queries by recompiling SQLite with the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.

The truncate optimization can also be disabled at runtime using the sqlite3_set_authorizer() interface. If an authorizer callback returns SQLITE_IGNORE for an SQLITE_DELETE action code, then the DELETE operation will proceed but the truncate optimization will be bypassed and rows will be deleted one by one.

Use Of LIMIT

If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax of the DELETE statement is extended by the addition of optional ORDER BY and LIMIT clauses:

delete-stmt-limited:

syntax diagram delete-stmt-limited

The optional LIMIT clause can be used to limit the number of rows deleted, and thereby limit the size of the transaction. The ORDER BY clause on a DELETE statement is used only to determine which rows fall within the LIMIT. The order in which rows are deleted is arbitrary and is not determined by the ORDER BY clause.

The presence of a LIMIT clause defeats the truncate optimization causing all rows being deleted to be visited.


SQLite Query Language: DETACH DATABASE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

DETACH DATABASE

detach-stmt:

syntax diagram detach-stmt

This statement detaches an additional database connection previously attached using the ATTACH statement. It is possible to have the same database file attached multiple times using different names, and detaching one connection to a file will leave the others intact.

This statement will fail if SQLite is in the middle of a transaction.


SQLite Query Language: DROP INDEX

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

DROP INDEX

drop-index-stmt:

syntax diagram drop-index-stmt

The DROP INDEX statement removes an index added with the CREATE INDEX statement. The index is completely removed from the disk. The only way to recover the index is to reenter the appropriate CREATE INDEX command.


SQLite Query Language: DROP TABLE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

DROP TABLE

drop-table-stmt:

syntax diagram drop-table-stmt

The DROP TABLE statement removes a table added with the CREATE TABLE statement. The name specified is the table name. The dropped table is completely removed from the database schema and the disk file. The table can not be recovered. All indices and triggers associated with the table are also deleted.

The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.

If foreign key constraints are enabled, a DROP TABLE command performs an implicit DELETE FROM <tbl> command before removing the table from the database schema. Any triggers attached to the table are dropped from the database schema before the implicit DELETE FROM <tbl> is executed, so this cannot cause any triggers to fire. By contrast, an implicit DELETE FROM <tbl> does cause any configured foreign key actions to take place. If the implicit DELETE FROM <tbl> executed as part of a DROP TABLE command violates any immediate foreign key constraints, an error is returned and the table is not dropped. If the implicit DELETE FROM <tbl> causes any deferred foreign key constraints to be violated, and the violations still exist when the transaction is committed, an error is returned at the time of commit.


SQLite Query Language: DROP TRIGGER

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

DROP TRIGGER

drop-trigger-stmt:

syntax diagram drop-trigger-stmt

The DROP TRIGGER statement removes a trigger created by the CREATE TRIGGER statement. The trigger is deleted from the database schema. Note that triggers are automatically dropped when the associated table is dropped.


SQLite Query Language: DROP VIEW

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

DROP VIEW

drop-view-stmt:

syntax diagram drop-view-stmt

The DROP VIEW statement removes a view created by the CREATE VIEW statement. The name specified is the view name. It is removed from the database schema, but no actual data in the underlying base tables is modified.


SQLite Query Language: EXPLAIN

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

EXPLAIN

sql-stmt:

syntax diagram sql-stmt

An SQL statement can be preceded by the keyword "EXPLAIN" or by the phrase "EXPLAIN QUERY PLAN". Either modification causes the SQL statement to behave as a query and to return information about how the SQL statement would have operated if the EXPLAIN keyword or phrase had been omitted.

When the EXPLAIN keyword appears by itself it causes the statement to behave as a query that returns the sequence of virtual machine instructions it would have used to execute the command had the EXPLAIN keyword not been present. When the EXPLAIN QUERY PLAN phrase appears, the statement returns high-level information about what indices would have been used.

The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is undocumented, unspecified, and variable.


SQLite Query Language: expression

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

expression

expr:

syntax diagram expr

literal-value:

syntax diagram literal-value

signed-number:

syntax diagram signed-number

raise-function:

syntax diagram raise-function

This section is different from the others. Most other sections of this document talks about a particular SQL command. This section does not talk about a standalone command but about "expressions" which are subcomponents of most other commands.

Operators

SQLite understands the following binary operators, in order from highest to lowest precedence:

||
*    /    %
+    -
<<   >>   &    |
<    <=   >    >=
=    ==   !=   <>   IS  IN  LIKE  GLOB  MATCH  REGEXP
AND
OR

Supported unary prefix operators are these:

-    +    ~    NOT

The COLLATE operator can be thought of as a unary postfix operator. The COLLATE operator has the highest precedence. It always binds more tightly than any prefix unary operator or any binary operator.

The unary operator + is a no-op. It can be applied to strings, numbers, or blobs and it always gives as its result the value of the operand.

Note that there are two variations of the equals and not equals operators. Equals can be either = or ==. The non-equals operator can be either != or <>. The || operator is "concatenate" - it joins together the two strings of its operands. The operator % outputs the remainder of its left operand modulo its right operand.

The result of any binary operator is a numeric value, except for the || concatenation operator which gives a string result.

The IS and IS NOT operators work like = and != except that NULL values compare equal to one another. IS and IS NOT have the same precedence as =.

Literal Values

A literal value is a constant of some kind. Literal values may be integers, floating point numbers, strings, BLOBs, or NULLs. Scientific notation is supported for floating point literal values. The "." character is always used as the decimal point even if the locale setting specifies "," for this role - the use of "," for the decimal point would result in syntactic ambiguity. A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. For example:

X'53514C697465'

A literal value can also be the token "NULL".

Parameters

A "variable" or "parameter" token specifies a placeholder in the expression for a literal value that is filled in at runtime using the sqlite3_bind() family of C/C++ interfaces. Parameters can take several forms:

?NNN | A question mark followed by a number NNN holds a spot for the NNN-th parameter. NNN must be between 1 and SQLITE_MAX_VARIABLE_NUMBER.
? A question mark that is not followed by a number holds a spot for the next unused parameter.
:AAAA | A colon followed by an identifier name holds a spot for a named parameter with the name AAAA. Named parameters are also numbered. The number assigned is the next unused number. To avoid confusion, it is best to avoid mixing named and numbered parameters.
@AAAA | An "at" sign works exactly like a colon.
$AAAA | A dollar-sign followed by an identifier name also holds a spot for a named parameter with the name AAAA. The identifier name in this case can include one or more occurances of "::" and a suffix enclosed in "(...)" containing any text at all. This syntax is the form of a variable name in the Tcl programming language. The presence of this syntax results from the fact that SQLite is really a Tcl extension that has escaped into the wild.

Parameters that are not assigned values using sqlite3_bind() are treated as NULL.

The LIKE and GLOB operators

The LIKE operator does a pattern matching comparison. The operand to the right of the LIKE operator contains the pattern and the left hand operand contains the string to match against the pattern. A percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters in the string. An underscore ("_") in the LIKE pattern matches any single character in the string. Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)

If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. The escape character followed by a percent symbol (%), underscore (_), or a second instance of the escape character itself matches a literal percent symbol, underscore, or a single escape character, respectively.

The infix LIKE operator is implemented by calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).

The LIKE operator can be made case sensitive using the case_sensitive_like pragma.

The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator is implemented by calling the function glob(Y,X) and can be modified by overriding that function.

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a application-defined SQL function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator.

The MATCH operator is a special syntax for the match() application-defined function. The default match() function implementation raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.

The BETWEEN operator

The BETWEEN operator is logically equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent to "x>=y AND x<=z" except that with BETWEEN, the x expression is only evaluated once. The precedence of the BETWEEN operator is the same as the precedence as operators == and != and LIKE and groups left to right.

The CASE expression

A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages. WHEN expressions are evaluated from left to right until one is found that is true, at which point the corresponding THEN term becomes the result. If no WHEN expression is true then the ELSE clause determines the result or the result is NULL if there is no ELSE clase.

The optional expression that occurs in between the CASE keyword and the first WHEN keyword is the "base" expression. There are two basic forms of a CASE expression: those with and without a base expression. In a CASE without a base expression, each WHEN expression is evaluted as a boolean and the overall result is determined by first WHEN expression that is true. In a CASE with a base expression, the base expression is evaluted just once and the result is compared against each WHEN expression until a match is found. When comparing a base expression against a WHEN expression, the same collating sequence, affinity, and NULL-handling rules apply as if the base expression and WHEN expression are respectively the left- and right-hand operands of an = operator.

Assuming the subexpressions have no side-effects, the following two expressions are equivalent:

  • CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
  • CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END

The only difference between the two CASE expressions shown above is that the x expression is evaluated exactly once in the first example but might be evaluated multiple times in the second.

A NULL result is considered false when evaluating WHEN terms. If the base expression is NULL then the result of the CASE is the result of the ELSE expression if it exists, or NULL if the ELSE clause is omitted.

Table Column Names

A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers: "ROWID", "OID", or "_ROWID_". These special identifiers all describe the unique integer key (the rowid) associated with every row of every table. The special identifiers only refer to the row key if the CREATE TABLE statement does not define a real column with the same name. The rowid can be used anywhere a regular column can be used.

Subqueries

SELECT statements can appear in expressions as either the right-hand operand of the IN operator, as a scalar quantity, or as the operand of an EXISTS operator. As a scalar quantity or the operand of an IN operator, the SELECT should have only a single column in its result. Compound SELECTs (connected with keywords like UNION or EXCEPT) are allowed. With the EXISTS operator, the columns in the result set of the SELECT are ignored and the expression returns TRUE if one or more rows exist and FALSE if the result set is empty. If no terms in the SELECT expression refer to values in the containing query, then the expression is evaluated once prior to any other processing and the result is reused as necessary. If the SELECT expression does contain variables from the outer query, then the SELECT is reevaluated every time it is needed.

When a SELECT is the right operand of the IN operator, the IN operator returns TRUE if the SELECT result contains no NULLs and if the left operand matches any of the values in the SELECT result. The IN operator may be preceded by the NOT keyword to invert the sense of the test.

When a SELECT appears within an expression but is not the right operand of an IN operator, then the first row of the result of the SELECT becomes the value used in the expression. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the SELECT is NULL.

CAST expressions

A CAST expression changes the datatype of the into the type specified by <type>. <type> can be any non-empty type name that is valid for the type in a column definition of a CREATE TABLE statement. The <type> name is interpreted according to the rules for determining column affinity.

An explicit cast is strong than affinity; with the CAST expression the datatype conversion is forced even if it is lossy and irrreversible.

  • A cast of a REAL value into an INTEGER will truncate the fractional part of the REAL. If an REAL is too large to be represented as an INTEGER then the result of the cast is the largest negative integer: -9223372036854775808.

  • A cast of a TEXT value into an INTEGER or REAL will read a prefix of the TEXT value that looks like an integer or real value, respectively, and ignore the rest. When casting from TEXT into INTEGER or REAL, leading spaces in the TEXT value are ignored. A cast of a TEXT value that looks nothing like a number into INTEGER or REAL results in 0 or 0.0, respectively.

  • Casting a value into NUMERIC first does a forced conversion into REAL but will then further converts the result into INTEGER if and only if the conversion from REAL to INTEGER is lossless and reversible. This is the only context in SQLite where the NUMERIC and INTEGER affinities behave differently.

  • Casting a value into TEXT renders the value as if via sqlite3_snprintf() except that the resulting TEXT uses the encoding of the database connection.

  • Casting a value to a <type> with no affinity causes the value to be converted into a BLOB. Casting to a BLOB consists of first casting the value to TEXT in the encoding of the database connection, then interpreting the resulting byte sequence as a BLOB instead of as TEXT.

  • Casting a BLOB value into any type other than a BLOB consists of first interpreting the byte sequence of the BLOB as a TEXT string in the database encoding then continuing as if the value where originally of type TEXT.

  • Any cast of a NULL value yields a NULL result.

Note that the result from casting any non-BLOB value into a BLOB and the result from casting any BLOB value into a non-BLOB value will be different depending on whether the database encoding is UTF-8, UTF-16be, or UTF-16le.

Functions

Both simple and aggregate functions are supported. (For presentation purposes, simple functions are further subdivided into core functions and date-time functions.) A simple function can be used in any expression. Simple functions return a result immediately based on their inputs. Aggregate functions may only be used in a SELECT statement. Aggregate functions compute their result across all rows of the result set.


SQLite Query Language: INDEXED BY

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

INDEXED BY

The INDEXED BY phrase is a SQL extension found only in SQLite which can be used to verify that the correct indices are being used on a DELETE, SELECT, or UPDATE statement. The INDEXED BY phrase always follows the name of a table that SQLite will be reading. The INDEXED BY phrase can be seen in the following syntax diagrams:

qualified-table-name:

syntax diagram qualified-table-name

single-source:

syntax diagram single-source

The "INDEXED BY index-name" clause specifies that the named index must be used in order to look up values on the preceding table. If index-name does not exist or cannot be used for the query, then the preparation of the SQL statement fails. The "NOT INDEXED" clause specifies that no index shall be used when accessing the preceding table, including implied indices create by UNIQUE and PRIMARY KEY constraints. However, the INTEGER PRIMARY KEY can still be used to look up entries even when "NOT INDEXED" is specified.

Some SQL database engines provide non-standard "hint" mechanisms which can be used to give the query optimizer clues about what indices it should use for a particular statement. The INDEX BY clause of SQLite is not a hinting mechanism and it should not be used as such. The INDEXED BY clause does not give the optimizer hints about which index to use; it gives the optimizer a requirement of which index to use. If the query optimizer is unable to use the index specified by the INDEX BY clause, then the query will fail with an error.

The INDEXED BY clause is not intended for use in tuning the preformance of a query. The intent of the INDEXED BY clause is to raise a run-time error if a schema change, such as dropping or creating an index, causes the query plan for a time-sensitive query to change. The INDEXED BY clause is designed to help detect undesirable query plan changes during regression testing. Developers are admonished to omit all use of INDEXED BY during application design, implementation, testing, and tuning. If INDEXED BY is to be used at all, it should be inserted at the very end of the development process when "locking down" a design.

See Also:

The sqlite3_stmt_status() C/C++ interface together with the SQLITE_STMTSTATUS_FULLSCAN_STEP and SQLITE_STMTSTATUS_SORT verbs can be used to detect at run-time when an SQL statement is not making effective use of indices. Many applications may prefer to use the sqlite3_stmt_status() interface to detect index misuse rather than the INDEXED BY phrase described here.


SQLite Query Language: INSERT

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

INSERT

insert-stmt:

syntax diagram insert-stmt

The INSERT statement comes in three basic forms. The first form (with the "VALUES" keyword) creates a single new row in an existing table. If no column-list is specified then the number of values must be the same as the number of columns in the table. If a column-list is specified, then the number of values must match the number of specified columns. Columns of the table that do not appear in the column list are filled with the default value, or with NULL if no default value is specified.

The second form of the INSERT statement takes its data from a SELECT statement. The number of columns in the result of the SELECT must exactly match the number of columns in the table if no column list is specified, or it must match the number of columns named in the column list. A new entry is made in the table for every row of the SELECT result. The SELECT may be simple or compound.

The third form of an INSERT statement is with DEFAULT VALUES. The INSERT ... DEFAULT VALUES statement simply creates a single new row in the table in which each column is filled with its default value.

The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one INSERT command. See the section titled ON CONFLICT for additional information. For compatibility with MySQL, the parser allows the use of the single keyword REPLACE as an alias for "INSERT OR REPLACE".

The optional "database-name." prefix on the table-name is support for top-level INSERT statements only. The table name must be unqualified for INSERT statements that occur within CREATE TRIGGER statements. Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for top-level INSERT statements only and not for INSERT statements within triggers.


SQLite Query Language: SQLite Keywords

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

SQLite Keywords

The SQL standard specifies a huge number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object.

If you want to use a keyword as a name, you need to quote it. There are three ways of quoting keywords in SQLite:

'keyword' | A keyword in single quotes is a string literal.
"keyword" | A keyword in double-quotes is an identifier.
[keyword] | A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
`keyword` | A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

For resilience when confronted with historical SQL statements, SQLite will sometimes bend the quoting rules above:

  • If a keyword in single quotes (ex: 'key' or 'glob') is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.

  • If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifer.

Programmers are cautioned not to use the two exceptions described in the previous bullets. We emphasize that they exist only so that old and ill-formed SQL statements will run correctly. Future versions of SQLite might change to raise errors instead of accepting the malformed statements covered by the exceptions above.

SQLite adds new keywords from time to time when it takes on new features. So to prevent your code from being broken by future enhancements, you should normally quote any identifier that is an English language word, even if you do not have to.

The list below shows all possible keywords used by any build of SQLite regardless of compile-time options. Most reasonable configurations use more or all of these keywords, but some keywords may be omitted when SQL language features are disabled. Regardless of the compile-time configuration, any identifier that is not on the following 121 element list is not a keyword to the SQL parser in SQLite:

ABORT
ACTION
ADD
AFTER
ALL
ALTER
ANALYZE
AND
AS
ASC
ATTACH
AUTOINCREMENT
BEFORE
BEGIN
BETWEEN
BY
CASCADE
CASE
CAST
CHECK
COLLATE
COLUMN
COMMIT
CONFLICT
CONSTRAINT
CREATE
CROSS
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATABASE
DEFAULT
DEFERRABLE
DEFERRED
DELETE
DESC
DETACH
DISTINCT
DROP
EACH
ELSE
END
ESCAPE
EXCEPT
EXCLUSIVE
EXISTS
EXPLAIN
FAIL
FOR
FOREIGN
FROM
FULL
GLOB
GROUP
HAVING
IF
IGNORE
IMMEDIATE
IN
INDEX
INDEXED
INITIALLY
INNER
INSERT
INSTEAD
INTERSECT
INTO
IS
ISNULL
JOIN
KEY
LEFT
LIKE
LIMIT
MATCH
NATURAL
NO
NOT
NOTNULL
NULL
OF
OFFSET
ON
OR
ORDER
OUTER
PLAN
PRAGMA
PRIMARY
QUERY
RAISE
REFERENCES
REGEXP
REINDEX
RELEASE
RENAME
REPLACE
RESTRICT
RIGHT
ROLLBACK
ROW
SAVEPOINT
SELECT
SET
TABLE
TEMP
TEMPORARY
THEN
TO
TRANSACTION
TRIGGER
UNION
UNIQUE
UPDATE
USING
VACUUM
VALUES
VIEW
VIRTUAL
WHEN
WHERE

SQLite is extended to allow many keywords to be used unquoted as the names of databases, tables, indices, triggers, views, columns, user-defined functions, collations, attached databases, and virtual function modules. The following subset of 57 SQLite keywords can be used unquoted as object names:

ABORT
ACTION
AFTER
ANALYZE
ASC
ATTACH
BEFORE
BEGIN
BY
CASCADE
CAST
COLUMN
CONFLICT
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATABASE
DEFERRED
DESC
DETACH
EACH
END
EXCLUSIVE
EXPLAIN
FAIL
FOR
GLOB
IF
IGNORE
IMMEDIATE
INDEXED
INITIALLY
INSTEAD
KEY
LIKE
MATCH
NO
OF
OFFSET
PLAN
PRAGMA
QUERY
RAISE
REGEXP
REINDEX
RELEASE
RENAME
REPLACE
RESTRICT
ROW
SAVEPOINT
TEMP
TEMPORARY
TRIGGER
VACUUM
VIEW
VIRTUAL

The next subset of 64 keywords are those that must be quoted in order to be used as object names:

ADD
ALL
ALTER
AND
AS
AUTOINCREMENT
BETWEEN
CASE
CHECK
COLLATE
COMMIT
CONSTRAINT
CREATE
CROSS
DEFAULT
DEFERRABLE
DELETE
DISTINCT
DROP
ELSE
ESCAPE
EXCEPT
EXISTS
FOREIGN
FROM
FULL
GROUP
HAVING
IN
INDEX
INNER
INSERT
INTERSECT
INTO
IS
ISNULL
JOIN
LEFT
LIMIT
NATURAL
NOT
NOTNULL
NULL
ON
OR
ORDER
OUTER
PRIMARY
REFERENCES
RIGHT
ROLLBACK
SELECT
SET
TABLE
THEN
TO
TRANSACTION
UNION
UNIQUE
UPDATE
USING
VALUES
WHEN
WHERE

Special names

The following are not keywords in SQLite, but are used as names of system objects. They can be used as an identifier for a different type of object.

_ROWID_
MAIN
OID
ROWID
SQLITE_MASTER
SQLITE_SEQUENCE
SQLITE_TEMP_MASTER
TEMP


SQLite Query Language: ON CONFLICT clause

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

ON CONFLICT clause

conflict-clause:

syntax diagram conflict-clause

The ON CONFLICT clause is not a separate SQL command. It is a non-standard clause that can appear in many other SQL commands. It is given its own section in this document because it is not part of standard SQL and therefore might not be familiar.

The syntax for the ON CONFLICT clause is as shown above for the CREATE TABLE command. For the INSERT and UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to make the syntax seem more natural. For example, instead of "INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE". The keywords change but the meaning of the clause is the same either way.

The ON CONFLICT clause specifies an algorithm used to resolve constraint conflicts. There are five conflict resolution algorithm choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default conflict resolution algorithm is ABORT. This is what they mean:

ROLLBACK

When a constraint violation occurs, an immediate ROLLBACK occurs, thus ending the current transaction, and the command aborts with a return code of SQLITE_CONSTRAINT. If no transaction is active (other than the implied transaction that is created on every command) then this algorithm works the same as ABORT.

ABORT

When a constraint violation occurs, the command backs out any prior changes it might have made and aborts with a return code of SQLITE_CONSTRAINT. But no ROLLBACK is executed so changes from prior commands within the same transaction are preserved. This is the default behavior.

FAIL

When a constraint violation occurs, the command aborts with a return code SQLITE_CONSTRAINT. But any changes to the database that the command made prior to encountering the constraint violation are preserved and are not backed out. For example, if an UPDATE statement encountered a constraint violation on the 100th row that it attempts to update, then the first 99 row changes are preserved but changes to rows 100 and beyond never occur.

IGNORE

When a constraint violation occurs, the one row that contains the constraint violation is not inserted or changed. But the command continues executing normally. Other rows before and after the row that contained the constraint violation continue to be inserted or updated normally. No error is returned when the IGNORE conflict resolution algorithm is used.

REPLACE

When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row. Thus the insert or update always occurs. The command continues executing normally following REPLACE. No error is returned by the REPLACE conflict resolution. If a NOT NULL constraint violation occurs, the NULL value is replaced by the default value for that column. If the column has no default value, then the ABORT algorithm is used. If a CHECK constraint violation occurs then the IGNORE algorithm is used.

When this conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers only fire if recursive triggers are enabled.

The update hook is not invoked for rows that are deleted by an OR REPLACE resolution. Nor is the change counter incremented. The exceptional behaviors defined in this paragraph might change in a future release.

The algorithm specified in the OR clause of a INSERT or UPDATE overrides any algorithm specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm is used.


Pragma statements supported by SQLite

Choose any three.
About Sitemap Documentation Download License News Support

The PRAGMA statement is a SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data. The PRAGMA statement is issued using the same interface as other SQLite commands (e.g. SELECT, INSERT) but is different in the following important respects:

  • Specific pragma statements may be removed and others added in future releases of SQLite. There is no guarantee of backwards compatiblity.
  • No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact.
  • Some pragmas take effect during the SQL compilation stage, not the execution stage. This means if using the C-language sqlite3_prepare(), sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper interface), the pragma may run during the sqlite3_prepare() call, not during the sqlite3_step() call as normal SQL statements do. Or the pragma might run during sqlite3_step() just like normal SQL statements. Whether or not the pragma runs during sqlite3_prepare() or sqlite3_step() depends on the pragma and on the specific release of SQLite.
  • The pragma command is specific to SQLite and is very unlikely to be compatible with any other SQL database engine.

The available pragmas fall into four basic categories:

  • Pragmas used to modify the operation of the SQLite library in some manner, or to query for the current mode of operation.
  • Pragmas used to query the schema of the current database.
  • Pragmas used to query or modify the two version counters stored in the database: the schema-version and the user-version.
  • Pragmas used to debug the library and verify that database files are not corrupted.

PRAGMA command syntax

pragma-stmt:

syntax diagram pragma-stmt

pragma-value:

syntax diagram pragma-value

A pragma can take either zero or one argument. The argument is may be either in parentheses or it may be separated from the pragma name by an equal sign. The two syntaxes yield identical results. In many pragmas, the argument is a boolean. The boolean can be one of:

1 yes true on
0 no false off

Keyword arguments can optionally appear in quotes. (Example: 'yes' [FALSE].) Some pragmas takes a string literal as their argument. When pragma takes a keyword argument, it will usually also take a numeric equivalent as well. For example, "0" and "no" mean the same thing, as does "1" and "yes". When querying the value of a setting, many pragmas return the number rather than the keyword.

A pragma may have an optional database name before the pragma name. The database name is the name of an ATTACH-ed database or it can be "main" or "temp" for the main and the TEMP databases. If the optional database name is omitted, "main" is assumed. In some pragmas, the database name is meaningless and is simply ignored.

Pragmas to modify library operation

  • PRAGMA auto_vacuum;
    PRAGMA auto_vacuum =     0 | NONE | 1 | FULL | 2 | INCREMENTAL;

    Query or set the auto-vacuum status in the database.

    The default setting for auto-vacuum is 0 or "none", unless the SQLITE_DEFAULT_AUTOVACUUM compile-time option is used. The "none" setting means that auto-vacuum is disabled. When auto-vacuum is disabled and data is deleted data from a database, the database file remains the same size. Unused database file pages are added to a "freelist" and reused for subsequent inserts. So no database file space is lost. However, the database file does not shrink. In this mode the VACUUM command can be used to rebuild the entire database file and thus reclaim unused disk space.

    When the auto-vacuum mode is 1 or "full", the freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages at every transaction commit. Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse.

    Auto-vacuuming is only possible if the database stores some additional information that allows each database page to be traced backwards to its referer. Therefore, auto-vacuuming must be turned on before any tables are created. It is not possible to enable or disable auto-vacuum after a table has been created.

    When the value of auto-vacuum is 2 or "incremental" then the additional information needed to do auto-vacuuming is stored in the database file but auto-vacuuming does not occur automatically at each commit as it does with auto_vacuum=full. In incremental mode, the separate incremental_vacuum pragma must be invoked to cause the auto-vacuum to occur.

    The database connection can be changed between full and incremental autovacuum mode at any time. However, changing from "none" to "full" or "incremental" when the database is new (no tables have yet been created) or by running the VACUUM command. To change auto-vacuum modes, first use the auto_vacuum pragma to set the new desired mode, then invoke the VACUUM command to reorganize the entire database file. To change from "full" or "incremental" back to "none" always requires running VACUUM even on an empty database.

    When the auto_vacuum pragma is invoked with no arguments, it returns the current auto_vacuum mode.

  • PRAGMA cache_size;
    PRAGMA cache_size =     Number-of-pages;

    Query or change the suggested maximum number of database disk pages that SQLite will hold in memory at once per open database file. Whether or not this suggestion is honored is at the discretion of the Application Defined Page Cache. In the default page cache implemention, the suggested cache size is honored as long as it is 10 or greater. A suggested cache size of less than 10 are treated as if it were 10. Alternative application-defined page cache implementations may choose to interpret the suggested cache size in different ways or to ignore it all together. The default suggested cache size is 2000.

    When you change the cache size using the cache_size pragma, the change only endures for the current session. The cache size reverts to the default value when the database is closed and reopened. Use the default_cache_size pragma to check the cache size permanently.

  • PRAGMA case_sensitive_like = boolean;

    The default behavior of the LIKE operator is to ignore case for ASCII characters. Hence, by default 'a' LIKE 'A' is true. The case_sensitive_like pragma installs a new application-defined LIKE function that can change this behavior. When case_sensitive_like is enabled, 'a' LIKE 'A' is false but 'a' LIKE 'a' is still true.

    This pragma only works if the built-in like() SQL function has not been overloaded using sqlite3_create_function().

  • PRAGMA count_changes;
    PRAGMA count_changes =     boolean;

    Query or change the count-changes flag. Normally, when the count-changes flag is not set, INSERT, UPDATE and DELETE statements return no data. When count-changes is set, each of these commands returns a single row of data consisting of one integer value - the number of rows inserted, modified or deleted by the command. The returned change count does not include any insertions, modifications or deletions performed by triggers, or any changes made automatically by foreign key actions.

    Another way to get the row change counts is to use the sqlite3_changes() or sqlite3_total_changes() interfaces. There is a subtle different, though. When an INSERT, UPDATE, or DELETE is run against a view using an INSTEAD OF trigger, the count_changes pragma reports the number of rows in the view that fired the trigger, whereas sqlite3_changes() and sqlite3_total_changes() do not.

  • PRAGMA default_cache_size;
    PRAGMA default_cache_size =     Number-of-pages;

    This pragma queries or sets the suggested maximum number of pages of disk cache that will be allocated per open database file. The difference between this pragma and cache_size is that the value set here persists across database connections.

  • PRAGMA empty_result_callbacks;
    PRAGMA empty_result_callbacks =     boolean;

    Query or change the empty-result-callbacks flag.

    The empty-result-callbacks flag affects the sqlite3_exec() API only. Normally, when the empty-result-callbacks flag is cleared, the callback function supplied to the sqlite3_exec() is not invoked for commands that return zero rows of data. When empty-result-callbacks is set in this situation, the callback function is invoked exactly once, with the third parameter set to 0 (NULL). This is to enable programs that use the sqlite3_exec() API to retrieve column-names even when a query returns no data.

    This pragma is legacy. It was created long ago in the early days of SQLite before the prepared statement interface was available. Do not use this pragma. It is likely to go away in a future release

  • PRAGMA encoding;
    PRAGMA encoding = "UTF-8";
    PRAGMA encoding = "UTF-16";
    PRAGMA encoding = "UTF-16le";
    PRAGMA encoding = "UTF-16be";

    In first form, if the main database has already been created, then this pragma returns the text encoding used by the main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16 encoding) or "UTF-16be" (big-endian UTF-16 encoding). If the main database has not already been created, then the value returned is the text encoding that will be used to create the main database, if it is created by this session.

    The second and subsequent forms of this pragma are only useful if the main database has not already been created. In this case the pragma sets the encoding that the main database will be created with if it is created by this session. The string "UTF-16" is interpreted as "UTF-16 encoding using native machine byte-ordering". If the second and subsequent forms are used after the database file has already been created, they have no effect and are silently ignored.

    Once an encoding has been set for a database, it cannot be changed.

    Databases created by the ATTACH command always use the same encoding as the main database.

  • PRAGMA foreign_keys;
    PRAGMA foreign_keys =     boolean;

    Query, set, or clear the enforcement of foreign key constraints.

    This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

    Changing the foreign_keys setting affects the execution of all statements prepared using the database connection, including those prepared before the setting was changed. Any existing statements prepared using the legacy sqlite3_prepare() interface may fail with an SQLITE_SCHEMA error after the foreign_keys setting is changed.

    As of SQLite version 3.6.19, the default setting for foreign key enforcement is OFF. However, that might change in a future release of SQLite. To minimize future problems, applications should set the foreign key enforcement flag as required by the application and not depend on the default setting.

  • PRAGMA full_column_names;
    PRAGMA full_column_names =     boolean;

    Query or change the full_column_names flag. This flag together with the short_column_names flag determine the way SQLite assigns names to result columns of SELECT statements. Result columns are named by applying the following rules in order:

    1. If there is an AS clause on the result, then the name of the column is the right-hand side of the AS clause.

    2. If the result is a general expression, not a just the name of a source table column, then the name of the result is a copy of the expression text.

    3. If the short_column_names pragma is ON, then the name of the result is the name of the source table column without the source table name prefix: COLUMN.

    4. If both pragmas short_column_names and full_column_names are OFF then case (2) applies.

    5. The name of the result column is a combination of the source table and source column name: TABLE.COLUMN

  • PRAGMA fullfsync
    PRAGMA fullfsync =     boolean;

    Query or change the fullfsync flag. This flag affects determines whether or not the F_FULLFSYNC syncing method is used on systems that support it. The default value of the fullfsync flag is off. Only Mac OS X supports F_FULLFSYNC.

  • PRAGMA incremental_vacuum(N);

    The incremental_vacuum pragma causes up to N pages to be removed from the freelist. The database file is truncated by the same amount. The incremental_vacuum pragma has no effect if the database is not in auto_vacuum==incremental mode or if there are no pages on the freelist. If there are fewer than N pages on the freelist, or if N is less than 1, or if N is omitted entirely, then the entire freelist is cleared.

    As of version 3.4.0 (the first version that supports incremental_vacuum) this feature is still experimental. Possible future changes include enhancing incremental vacuum to do defragmentation and node repacking just as the full-blown VACUUM command does. And incremental vacuum may be promoted from a pragma to a separate SQL command, or perhaps some variation on the VACUUM command. Programmers are cautioned to not become enamored with the current syntax or functionality as it is likely to change.

  • PRAGMA journal_mode;
    PRAGMA     database.journal_mode;
    PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | OFF
    PRAGMA     database.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | OFF

    This pragma queries or sets the journal mode for databases associated with the current database connection.

    The first two forms of this pragma query the current journaling mode. In the first form, the default journal_mode is returned. The default journaling mode is the mode used by databases added to the connection by subsequent ATTACH statements. The second form returns the current journaling mode for a specific database.

    The last two forms change the journaling mode. The 4th form changes the journaling mode for a specific database connection. Use "main" for the main database (the database that was opened by the original sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2() interface call) and use "temp" for database that holds TEMP tables. The 3rd form changes the journaling mode on all databases and it changes the default journaling mode that will be used for new databases added by subsequent ATTACH commands. The new journal mode is returned. If the journal mode could not be changed, the original journal mode is returned.

    The DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit. (See the documented titled Atomic Commit In SQLite for additional detail.)

    The TRUNCATE journaling mode commits transactions by truncating the rollback journal to zero-length instead of deleting it. On many systems, truncating a file is much faster than deleting the file since the containing directory does not need to be changed.

    The PERSIST journaling mode prevents the rollback journal from being deleted at the end of each transaction. Instead, the header of the journal is overwritten with zeros. This will prevent other database connections from rolling the journal back. The PERSIST journaling mode is useful as an optimization on platforms where deleting or truncating a file is much more expensive than overwriting the first block of a file with zeros.

    The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.

    The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The ROLLBACK command no longer works; it behaves in an undefined way. Applications must avoid using the ROLLBACK command when the journal mode is OFF. If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt.

    Note that the journal_mode for an in-memory database is either MEMORY or OFF and can not be changed to a different value. An attempt to change the journal_mode of an in-memory database to any setting other than MEMORY or OFF is ignored. Note also that the journal_mode cannot be changed while a transaction is active.

  • PRAGMA journal_size_limit
    PRAGMA journal_size_limit =     N ;

    If a database connection is operating in either "exclusive mode" (PRAGMA locking_mode=exclusive) or "persistent journal mode" (PRAGMA journal_mode=persist) then under certain circumstances after committing a transaction the journal file may remain in the file-system. This increases efficiency but also consumes space in the file-system. After a large transaction (e.g. a VACUUM), it may consume a very large amount of space.

    This pragma may be used to limit the size of journal files left in the file-system after transactions are committed on a per database basis. Each time a transaction is committed, SQLite compares the size of the journal file left in the file-system to the size limit configured using this pragma and if the journal file is larger than the limit allows for, it is truncated to the limit.

    The second form of the pragma listed above is used to set a new limit in bytes for the specified database. A negative number implies no limit. Both the first and second forms of the pragma listed above return a single result row containing a single integer column - the value of the journal size limit in bytes. The default limit value is -1 (no limit), which may be overridden by defining the preprocessor macro SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT at compile time.

    This pragma only operates on the single database specified prior to the pragma name (or on the "main" database if no database is specified.) There is no way to operate on all attached databases using a single PRAGMA statement, nor is there a way to set the limit to use for databases that will be attached in the future.

  • PRAGMA legacy_file_format;
    PRAGMA legacy_file_format = boolean

    This pragma sets or queries the value of the legacy_file_format flag. When this flag is on, new SQLite databases are created in a file format that is readable and writable by all versions of SQLite going back to 3.0.0. When the flag is off, new databases are created using the latest file format which might not be readable or writable by versions of SQLite prior to 3.3.0.

    When the legacy_file_format pragma is issued with no argument, it returns the setting of the flag. This pragma does not tell which file format the current database is using; it tells what format will be used by any newly created databases.

    This flag only affects newly created databases. It has no effect on databases that already exist.

    The default file format is set by the SQLITE_DEFAULT_FILE_FORMAT compile-time option.

  • PRAGMA locking_mode;
    PRAGMA locking_mode = NORMAL | EXCLUSIVE

    This pragma sets or queries the database connection locking-mode. The locking-mode is either NORMAL or EXCLUSIVE.

    In NORMAL locking-mode (the default), a database connection unlocks the database file at the conclusion of each read or write transaction. When the locking-mode is set to EXCLUSIVE, the database connection never releases file-locks. The first time the database is read in EXCLUSIVE mode, a shared lock is obtained and held. The first time the database is written, an exclusive lock is obtained and held.

    Database locks obtained by a connection in EXCLUSIVE mode may be released either by closing the database connection, or by setting the locking-mode back to NORMAL using this pragma and then accessing the database file (for read or write). Simply setting the locking-mode to NORMAL is not enough - locks are not be released until the next time the database file is accessed.

    There are two reasons to set the locking-mode to EXCLUSIVE. One is if the application actually wants to prevent other processes from accessing the database file. The other is that a small number of filesystem operations are saved by optimizations enabled in this mode. This may be significant in embedded environments.

    When the locking_mode pragma specifies a particular database, for example:

    PRAGMA main.locking_mode=EXCLUSIVE;

    Then the locking mode applies only to the named database. If no database name qualifier precedes the "locking_mode" keyword then the locking mode is applied to all databases, including any new databases added by subsequent ATTACH commands.

    The "temp" database (in which TEMP tables and indices are stored) and in-memory databases always uses exclusive locking mode. The locking mode of temp and in-memory databases cannot be changed. All other databases use the normal locking mode by default and are affected by this pragma.

  • PRAGMA page_size;
    PRAGMA page_size =     bytes;

    Query or set the page size of the database. The page size may only be set if the database has not yet been created. The page size must be a power of two greater than or equal to 512 and less than or equal to SQLITE_MAX_PAGE_SIZE. The maximum value for SQLITE_MAX_PAGE_SIZE is 32768.

    When a new database is created, SQLite assigned a default page size based on information received from the xSectorSize and xDeviceCharacteristics methods of the sqlite3_io_methods object of the newly created database file. The page_size pragma will only cause an immediate change in the page size if it is issued while the database is still empty, prior to the first CREATE TABLE statement. As of version 3.5.8, if the page_size pragma is used to specify a new page size just prior to running the VACUUM command then VACUUM will change the page size to the new value.

    If SQLite is compiled with the SQLITE_ENABLE_ATOMIC_WRITE option, then the default page size is chosen to be the largest page size less than or equal to SQLITE_MAX_DEFAULT_PAGE_SIZE for which atomic write is enabled according to the xDeviceCharacteristics method of the sqlite3_io_methods object for the database file. If the SQLITE_ENABLE_ATOMIC_WRITE option is disabled or if xDeviceCharacteristics reports no suitable atomic write page sizes, then the default page size is the larger of SQLITE_DEFALT_PAGE_SIZE and the sector size as reported by the xSectorSize method of the sqlite3_io_methods object, but not more than SQLITE_MAX_DEFAULT_PAGE_SIZE. The normal configuration for SQLite running on workstations is for atomic write to be disabled, for the maximum page size to be set to 32768, for SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the maximum default page size to be set to 8192. The default xSectorSize method on workstation implementations always reports a sector size of 512 bytes. Hence, the default page size chosen by SQLite is usually 1024 bytes.

  • PRAGMA max_page_count;
    PRAGMA max_page_count =     N;

    Query or set the maximum number of pages in the database file. Both forms of the pragma return the maximum page count. The second form attempts to modify the maximum page count. The maximum page count cannot be reduced below the current database size.

  • PRAGMA read_uncommitted;
    PRAGMA read_uncommitted =     boolean;

    Query, set, or clear READ UNCOMMITTED isolation. The default isolation level for SQLite is SERIALIZABLE. Any process or thread can select READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except between connections that share a common page and schema cache. Cache sharing is enabled using the sqlite3_enable_shared_cache() API. Cache sharing is disabled by default.

    See SQLite Shared-Cache Mode for additional information.

  • PRAGMA recursive_triggers;
    PRAGMA recursive_triggers =     boolean;

    Query, set, or clear the recursive trigger capability.

    Changing the recursive_triggers setting affects the execution of all statements prepared using the database connection, including those prepared before the setting was changed. Any existing statements prepared using the legacy sqlite3_prepare() interface may fail with an SQLITE_SCHEMA error after the recursive_triggers setting is changed.

    Prior to SQLite version 3.6.18, recursive triggers were not supported. The behavior of SQLite was always as if this pragma was set to OFF. Support for recursive triggers was added in version 3.6.18 but was initially turned OFF by default, for compatibility. Recursive triggers may be turned on by default in future versions of SQLite.

    The depth of recursion for triggers has a hard upper limit set by the SQLITE_MAX_TRIGGER_DEPTH compile-time option and a run-time limit set by sqlite3_limit(db,SQLITE_LIMIT_TRIGGER_DEPTH,...).

  • PRAGMA reverse_unordered_selects;
    PRAGMA reverse_unordered_selects =     boolean;

    When enabled, this PRAGMA causes SELECT statements without a an ORDER BY clause to emit their results in the reverse order of what they normally would. This can help debug applications that are making invalid assumptions about the result order.

    SQLite makes no guarantees about the order of results if a SELECT omits the ORDER BY clause. Even so, the order of results does not change from one run to the next, and so many applications mistakenly come to depend on the arbitrary output order whatever that order happens to be. However, sometimes new versions of SQLite will contain optimizer enhancements that will cause the output order of queries without ORDER BY clauses to shift. When that happens, applications that depend on a certain output order might malfunction. By running the application multiple times with this pragma both disabled and enabled, cases where the application makes faulty assumptions about output order can be identified and fixed early, reducing problems that might be caused by linking against a different version of SQLite.

  • PRAGMA short_column_names;
    PRAGMA short_column_names =     boolean;

    Query or change the short-column-names flag. This flag affects the way SQLite names columns of data returned by SELECT statements. See the full_column_names pragma for full details.

  • PRAGMA synchronous;
    PRAGMA synchronous =     0 | OFF | 1 | NORMAL | 2 | FULL;

    Query or change the setting of the "synchronous" flag. The first (query) form will return the synchronous setting as an integer. When synchronous is FULL (2), the SQLite database engine will pause at critical moments to make sure that data has actually been written to the disk surface before continuing. This ensures that if the operating system crashes or if there is a power failure, the database will be uncorrupted after rebooting. FULL synchronous is very safe, but it is also slower. When synchronous is NORMAL (1), the SQLite database engine will still pause at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. With synchronous OFF (0), SQLite continues without pausing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF.

    The default setting is synchronous=FULL.

  • PRAGMA temp_store;
    PRAGMA temp_store =     0 | DEFAULT | 1 | FILE | 2 | MEMORY;

    Query or change the setting of the "temp_store" parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro SQLITE_TEMP_STORE is used to determine where temporary tables and indices are stored. When temp_store is MEMORY (2) temporary tables and indices are kept in as if they were pure in-memory databases memory. When temp_store is FILE (1) temporary tables and indices are stored in a file. The temp_store_directory pragma can be used to specify the directory containing temporary files when FILE is specified. When the temp_store setting is changed, all existing temporary tables, indices, triggers, and views are immediately deleted.

    It is possible for the library compile-time C preprocessor symbol SQLITE_TEMP_STORE to override this pragma setting. The following table summarizes the interaction of the SQLITE_TEMP_STORE preprocessor macro and the temp_store pragma:

    SQLITE_TEMP_STORE PRAGMA
    temp_store     		Storage used for
    TEMP tables and indices
    0 | any | file
    1 | 0 | file
    1 | 1 | file
    1 | 2 | memory
    2 | 0 | memory
    2 | 1 | file
    2 | 2 | memory
    3 | any | memory

  • PRAGMA temp_store_directory;
    PRAGMA temp_store_directory = '    directory-name';

    Query or change the setting of the "temp_store_directory" - the directory where files used for storing temporary tables and indices are kept.

    When the temp_store_directory setting is changed, all existing temporary tables, indices, triggers, and viewers are immediately deleted. In practice, temp_store_directory should be set immediately after the database is opened.

    Changing the temp_store_directory setting is not threadsafe. Never change the temp_store_directory setting if another thread within the application is running any SQLite interface at the same time. Doing so results in undefined behavior. Changing the temp_store_directory setting writes to the sqlite3_temp_directory global variable and that global variable is not protected by a mutex.

    The value directory-name should be enclosed in single quotes. To revert the directory to the default, set the directory-name to an empty string, e.g., PRAGMA temp_store_directory = ''. An error is raised if directory-name is not found or is not writable.

    The default directory for temporary files depends on the OS. Some OS interfaces may choose to ignore this variable in place temporary files in some other directory different from the directory specified here. In that sense, this pragma is only advisory.

Pragmas to query the database schema

  • PRAGMA collation_list;

    Return a list of the collating sequences defined for the current database connection.

  • PRAGMA database_list;

    This pragma works like a query to return one row for each database attached to the current database connection. Columns of the result set include the index and the name the database was attached with. The first row will be for the main database. The second row will be for the database used to store temporary tables.

  • PRAGMA foreign_key_list(table-name);

    This pragma returns one rwo for each foreign key that references a column in the argument table.

  • PRAGMA freelist_count;

    Return the number of unused pages in the database file. Running a "PRAGMA incremental_vaccum(N);" command with a large value of N will shrink the database file by this number of pages when incremental vacuum is enabled.

  • PRAGMA index_info(index-name);

    This program returns one row row each column in the named index.

  • PRAGMA index_list(table-name);

    This pragma returns one row for each index associated with the given table. Columns of the result set include the index name and a flag to indicate whether or not the index is UNIQUE.

  • PRAGMA page_count;

    Return the total number of pages in the database file.

  • PRAGMA table_info(table-name);

    This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column.

Pragmas to query/modify version values

  • PRAGMA schema_version;
    PRAGMA schema_version =     integer ;
    PRAGMA user_version;
    PRAGMA user_version =     integer ;

    The pragmas schema_version and user_version are used to set or get the value of the schema-version and user-version, respectively. Both the schema-version and the user-version are 32-bit signed integers stored in the database header.

    The schema-version is usually only manipulated internally by SQLite. It is incremented by SQLite whenever the database schema is modified (by creating or dropping a table or index). The schema version is used by SQLite each time a query is executed to ensure that the internal cache of the schema used when compiling the SQL query matches the schema of the database against which the compiled query is actually executed. Subverting this mechanism by using "PRAGMA schema_version" to modify the schema-version is potentially dangerous and may lead to program crashes or database corruption. Use with caution!

    The user-version is not used internally by SQLite. It may be used by applications for any purpose.

Pragmas to debug the library

  • PRAGMA integrity_check;
    PRAGMA integrity_check(    integer)

    This pragma does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems. At most integer errors will be reported before the analysis quits. The default value for integer is 100. If no errors are found, a single row with the value "ok" is returned.

  • PRAGMA quick_check;
    PRAGMA quick_check(    integer)

    The pragma is like integrity_check except that it does not verify that index content matches table content. By skipping the verification of index content, quick_check is able to run much faster than integrity_check. Otherwise the two pragmas are the same.

  • PRAGMA parser_trace = boolean;

    Turn tracing of the SQL parser inside of the SQLite library on and off. This is used for debugging. This only works if the library is compiled with the SQLITE_DEBUG compile-time option.

  • PRAGMA vdbe_trace = boolean;

    Turn tracing of the virtual database engine inside of the SQLite library on and off. This is used for debugging. See the VDBE documentation for more information.

  • PRAGMA vdbe_listing = boolean;

    Turn listings of virtual machine programs on and off. With listing is on, the entire content of a program is printed just prior to beginning execution. The statement executes normally after the listing is printed. This is used for debugging. See the VDBE documentation for more information.


SQLite Query Language: REINDEX

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

REINDEX

reindex-stmt:

syntax diagram reindex-stmt

The REINDEX command is used to delete and recreate indices from scratch. This is useful when the definition of a collation sequence has changed.

In the first form, all indices in all attached databases that use the named collation sequence are recreated. In the second form, if [database-name.]table/index-name identifies a table, then all indices associated with the table are rebuilt. If an index is identified, then only this specific index is deleted and recreated.

If no database-name is specified and there exists both a table or index and a collation sequence of the specified name, then indices associated with the collation sequence only are reconstructed. This ambiguity may be dispelled by always specifying a database-name when reindexing a specific table or index.


SQLite Query Language: REPLACE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

REPLACE

The REPLACE command is an alias for the "INSERT OR REPLACE" variant of the INSERT command. This alias is provided for compatibility other SQL database engines. See the INSERT command documentation for additional information.


SQLite Query Language: SELECT

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

SELECT

select-stmt:

syntax diagram select-stmt

select-core:

syntax diagram select-core

result-column:

syntax diagram result-column

join-source:

syntax diagram join-source

single-source:

syntax diagram single-source

join-op:

syntax diagram join-op

join-constraint:

syntax diagram join-constraint

ordering-term:

syntax diagram ordering-term

compound-operator:

syntax diagram compound-operator

The SELECT statement is used to query the database. The result of a SELECT is zero or more rows of data where each row has a fixed number of columns. The number of columns in the result is specified by the expression list in between the SELECT and FROM keywords. Any arbitrary expression can be used as a result. If a result expression is * then all columns of all tables are substituted for that one expression. If the expression is the name of a table followed by .* then the result is all columns in that one table.

The DISTINCT keyword causes a subset of result rows to be returned, in which each result row is different. NULL values are not treated as distinct from each other. The default behavior is that all result rows be returned, which can be made explicit with the keyword ALL.

The query is executed against one or more tables specified after the FROM keyword. If multiple tables names are separated by commas, then the query is against the cross join of the various tables. The full SQL-92 join syntax can also be used to specify joins. A sub-query in parentheses may be substituted for any table name in the FROM clause. The entire FROM clause may be omitted, in which case the result is a single row consisting of the values of the expression list.

The WHERE clause can be used to limit the number of rows over which the query operates.

The GROUP BY clause causes one or more rows of the result to be combined into a single row of output. This is especially useful when the result contains aggregate functions. The expressions in the GROUP BY clause do not have to be expressions that appear in the result. The HAVING clause is similar to WHERE except that HAVING applies after grouping has occurred. The HAVING expression may refer to values, even aggregate functions, that are not in the result.

The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY is a list of expressions that are used as the key for the sort. The expressions do not have to be part of the result for a simple SELECT, but in a compound SELECT each sort expression must exactly match one of the result columns. Each sort expression may be optionally followed by a COLLATE keyword and the name of a collating function used for ordering text and/or keywords ASC or DESC to specify the sort order.

Each term of an ORDER BY expression is processed as follows:

  1. If the ORDER BY expression is a constant integer K then the output is ordered by the K-th column of the result set.

  2. If the ORDER BY expression is an identifier and one of the output columns has an alias by the same name, then the output is ordered by the identified column.

  3. Otherwise, the ORDER BY expression is evaluated and the output is ordered by the value of that expression.

In a compound SELECT statement, the third ORDER BY matching rule requires that the expression be identical to one of the columns in the result set. The three rules are first applied to the left-most SELECT in the compound. If a match is found, the search stops. Otherwise, the next SELECT to the right is tried. This continues until a match is found. Each term of the ORDER BY clause is processed separately and may come from different SELECT statements in the compound.

The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradition is intentional - it maximizes compatibility with legacy SQL database systems.

A compound SELECT is formed from two or more simple SELECTs connected by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT. In a compound SELECT, all the constituent SELECTs must specify the same number of result columns. There may be only a single ORDER BY clause at the end of the compound SELECT. The UNION and UNION ALL operators combine the results of the SELECTs to the right and left into a single big table. The difference is that in UNION all result rows are distinct where in UNION ALL there may be duplicates. The INTERSECT operator takes the intersection of the results of the left and right SELECTs. EXCEPT takes the result of left SELECT after removing the results of the right SELECT. When three or more SELECTs are connected into a compound, they group from left to right.


SQLite Query Language: UPDATE

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

UPDATE

update-stmt:

syntax diagram update-stmt

qualified-table-name:

syntax diagram qualified-table-name

The UPDATE statement is used to change the value of columns in selected rows of a table. Each assignment in an UPDATE specifies a column name to the left of the equals sign and an arbitrary expression to the right. The expressions may use the values of other columns. All expressions are evaluated before any assignments are made. A WHERE clause can be used to restrict which rows are updated.

The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one UPDATE command. See the section titled ON CONFLICT for additional information.

Restrictions on UPDATE Statements Within CREATE TRIGGER

There are additional syntax restrictions on UPDATE statements that occur within the body of a CREATE TRIGGER statement. The table-name of the UPDATE must be unqualified. In other words, the database-name. prefix on the table name of the UPDATE is not allowed within triggers. The table to be updated must be in the same database as the table to which the trigger is attached.

The INDEXED BY and NOT INDEXED clauses are not allowed on UPDATE statements within triggers.

The LIMIT clause for UPDATE is unsupported within triggers.

Optional LIMIT and ORDER BY Clauses

If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT clauses as follows:

update-stmt-limited:

syntax diagram update-stmt-limited

The optional LIMIT clause can be used to limit the number of rows modifed, and thereby limit the size of the transaction. The ORDER BY clause on an UPDATE statement is used only to determine which rows fall within the LIMIT. The order in which rows are modified is arbitrary and is not determined by the ORDER BY clause.


SQLite Query Language: VACUUM

Choose any three.
About Sitemap Documentation Download License News Support

SQL As Understood By SQLite

[Top]

VACUUM

vacuum-stmt:

syntax diagram vacuum-stmt

When an object (table, index, trigger, or view) is dropped from the database, it leaves behind empty space. This empty space will be reused the next time new information is added to the database. But in the meantime, the database file might be larger than strictly necessary. Also, frequent inserts, updates, and deletes can cause the information in the database to become fragmented - scrattered out all across the database file rather than clustered together in one place.

The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

The VACUUM command may change the ROWIDs of entries in tables that do not have an explicit INTEGER PRIMARY KEY.

VACUUM only works on the main database. It is not possible to VACUUM an attached database file.

The VACUUM command will fail if there is an active transaction. The VACUUM command is a no-op for in-memory databases.

As of SQLite version 3.1, an alternative to using the VACUUM command is auto-vacuum mode, enabled using the auto_vacuum pragma. When auto_vacuum is enabled for a database, large deletes cause the size of the database file to shrink. However, auto_vacuum also causes excess fragmentation of the database file. And auto_vacuum does not compact partially filled pages of the database as VACUUM does.

The page_size and/or auto_vacuum mode of a database can be changed by invoking the page_size pragma and/or auto_vacuum pragma and then immediately VACUUMing the database.


Atomic Commit In SQLite

1.0 Introduction

An important feature of transactional databases like SQLite is "atomic commit". Atomic commit means that either all database changes within a single transaction occur or none of them occur. With atomic commit, it is as if many different writes to different sections of the database file occur instantaneously and simultaneously. Real hardware serializes writes to mass storage, and writing a single sector takes a finite amount of time. So it is impossible to truly write many different sectors of a database file simultaneously and/or instantaneously. But the atomic commit logic within SQLite makes it appear as if the changes for a transaction are all written instantaneously and simultaneously.

SQLite has the important property that transactions appear to be atomic even if the transaction is interrupted by an operating system crash or power failure.

This article describes the techniques used by SQLite to create the illusion of atomic commit.

2.0 Hardware Assumptions

Throughout this article, we will call the mass storage device "disk" even though the mass storage device might really be flash memory.

We assume that disk is written in chunks which we call a "sector". It is not possible to modify any part of the disk smaller than a sector. To change a part of the disk smaller than a sector, you have to read in the full sector that contains the part you want to change, make the change, then write back out the complete sector.

On a traditional spinning disk, a sector is the minimum unit of transfer in both directions, both reading and writing. On flash memory, however, the minimum size of a read is typically much smaller than a minimum write. SQLite is only concerned with the minimum write amount and so for the purposes of this article, when we say "sector" we mean the minimum amount of data that can be written to mass storage in a single go.

Prior to SQLite version 3.3.14, a sector size of 512 bytes was assumed in all cases. There was a compile-time option to change this but the code had never been tested with a larger value. The 512 byte sector assumption seemed reasonable since until very recently all disk drives used a 512 byte sector internally. However, there has recently been a push to increase the sector size of disks to 4096 bytes. Also the sector size for flash memory is usually larger than 512 bytes. For these reasons, versions of SQLite beginning with 3.3.14 have a method in the OS interface layer that interrogates the underlying filesystem to find the true sector size. As currently implemented (version 3.5.0) this method still returns a hard-coded value of 512 bytes, since there is no standard way of discovering the true sector size on either Unix or Windows. But the method is available for embedded device manufactures to tweak according to their own needs. And we have left open the possibility of filling in a more meaningful implementation on Unix and Windows in the future.

SQLite does not assume that a sector write is atomic. However, it does assume that a sector write is linear. By "linear" we mean that SQLite assumes that when writing a sector, the hardware begins at one end of the data and writes byte by byte until it gets to the other end. The write might go from beginning to end or from end to beginning. If a power failure occurs in the middle of a sector write it might be that part of the sector was modified and another part was left unchanged. The key assumption by SQLite is that if any part of the sector gets changed, then either the first or the last bytes will be changed. So the hardware will never start writing a sector in the middle and work towards the ends. We do not know if this assumption is always true but it seems reasonable.

The previous paragraph states that SQLite does not assume that sector writes are atomic. This is true by default. But as of SQLite version 3.5.0, there is a new interface called the Virtual File System (VFS) interface. The VFS is the only means by which SQLite communicates to the underlying filesystem. The code comes with default VFS implementations for Unix and Windows and there is a mechanism for creating new custom VFS implementations at runtime. In this new VFS interface there is a method called xDeviceCharacteristics. This method interrogates the underlying filesystem to discover various properties and behaviors that the filesystem may or may not exhibit. The xDeviceCharacteristics method might indicate that sector writes are atomic, and if it does so indicate, SQLite will try to take advantage of that fact. But the default xDeviceCharacteristics method for both Unix and Windows does not indicate atomic sector writes and so these optimizations are normally omitted.

SQLite assumes that the operating system will buffer writes and that a write request will return before data has actually been stored in the mass storage device. SQLite further assumes that write operations will be reordered by the operating system. For this reason, SQLite does a "flush" or "fsync" operation at key points. SQLite assumes that the flush or fsync will not return until all pending write operations for the file that is being flushed have completed. We are told that the flush and fsync primitives are broken on some versions of Windows and Linux. This is unfortunate. It opens SQLite up to the possibility of database corruption following a power loss in the middle of a commit. However, there is nothing that SQLite can do to test for or remedy the situation. SQLite assumes that the operating system that it is running on works as advertised. If that is not quite the case, well then hopefully you will not lose power too often.

SQLite assumes that when a file grows in length that the new file space originally contains garbage and then later is filled in with the data actually written. In other words, SQLite assumes that the file size is updated before the file content. This is a pessimistic assumption and SQLite has to do some extra work to make sure that it does not cause database corruption if power is lost between the time when the file size is increased and when the new content is written. The xDeviceCharacteristics method of the VFS might indicate that the filesystem will always write the data before updating the file size. (This is the SQLITE_IOCAP_SAFE_APPEND property for those readers who are looking at the code.) When the xDeviceCharacteristics method indicates that files content is written before the file size is increased, SQLite can forego some of its pedantic database protection steps and thereby decrease the amount of disk I/O needed to perform a commit. The current implementation, however, makes no such assumptions for the default VFSes for Windows and Unix.

SQLite assumes that a file deletion is atomic from the point of view of a user process. By this we mean that if SQLite requests that a file be deleted and the power is lost during the delete operation, once power is restored either the file will exist completely with all if its original content unaltered, or else the file will not be seen in the filesystem at all. If after power is restored the file is only partially deleted, if some of its data has been altered or erased, or the file has been truncated but not completely removed, then database corruption will likely result.

SQLite assumes that the detection and/or correction of bit errors caused by cosmic rays, thermal noise, quantum fluctuations, device driver bugs, or other mechanisms, is the responsibility of the underlying hardware and operating system. SQLite does not add any redundancy to the database file for the purpose of detecting corruption or I/O errors. SQLite assumes that the data it reads is exactly the same data that it previously wrote.

3.0 Single File Commit

We begin with an overview of the steps SQLite takes in order to perform an atomic commit of a transaction against a single database file. The details of file formats used to guard against damage from power failures and techniques for performing an atomic commit across multiple databases are discussed in later sections.

3.1 Initial State

The state of the computer when a database connection is first opened is shown conceptually by the diagram at the right. The area of the diagram on the extreme right (labeled "Disk") represents information stored on the mass storage device. Each rectangle is a sector. The blue color represents that the sectors contain original data. The middle area is the operating systems disk cache. At the onset of our example, the cache is cold and this is represented by leaving the rectangles of the disk cache empty. The left area of the diagram shows the content of memory for the process that is using SQLite. The database connection has just been opened and no information has been read yet, so the user space is empty.

3.2 Acquiring A Read Lock

Before SQLite can write to a database, it must first read the database to see what is there already. Even if it is just appending new data, SQLite still has to read in the database schema from the sqlite_master table so that it can know how to parse the INSERT statements and discover where in the database file the new information should be stored.

The first step toward reading from the database file is obtaining a shared lock on the database file. A "shared" lock allows two or more database connections to read from the database file at the same time. But a shared lock prevents another database connection from writing to the database file while we are reading it. This is necessary because if another database connection were writing to the database file at the same time we are reading from the database file, we might read some data before the change and other data after the change. This would make it appears as if the change made by the other process is not atomic.

Notice that the shared lock is on the operating system disk cache, not on the disk itself. File locks really are just flags within the operating system kernel, usually. (The details depend on the specific OS layer interface.) Hence, the lock will instantly vanish if the operating system crashes or if there is a power loss. It is usually also the case that the lock will vanish if the process that created the lock exits.

3.3 Reading Information Out Of The Database

After the shared lock is acquired, we can begin reading information from the database file. In this scenario, we are assuming a cold cache, so information must first be read from mass storage into the operating system cache then transferred from operating system cache into user space. On subsequent reads, some or all of the information might already be found in the operating system cache and so only the transfer to user space would be required.

Usually only a subset of the pages in the database file are read. In this example we are showing three pages out of eight being read. In a typical application, a database will have thousands of pages and a query will normally only touch a small percentage of those pages.

3.4 Obtaining A Reserved Lock

Before making changes to the database, SQLite first obtains a "reserved" lock on the database file. A reserved lock is similar to a shared lock in that both a reserved lock and shared lock allow other processes to read from the database file. A single reserve lock can coexist with multiple shared locks from other processes. However, there can only be a single reserved lock on the database file. Hence only a single process can be attempting to write to the database at one time.

The idea behind a reserved locks is that it signals that a process intends to modify the database file in the near future but has not yet started to make the modifications. And because the modifications have not yet started, other processes can continue to read from the database. However, no other process should also begin trying to write to the database.

3.5 Creating A Rollback Journal File

Prior to making any changes to the database file, SQLite first creates a separate rollback journal file and writes into the rollback journal the original content of the database pages that are to be altered. The idea behind the rollback journal is that it contains all information needed to restore the database back to its original state.

The rollback journal contains a small header (shown in green in the diagram) that records the original size of the database file. So if a change causes the database file to grow, we will still know the original size of the database. The page number is stored together with each database page that is written into the rollback journal.

When a new file is created, most desktop operating systems (Windows, Linux, Mac OS X) will not actually write anything to disk. The new file is created in the operating systems disk cache only. The file is not created on mass storage until sometime later, when the operating system has a spare moment. This creates the impression to users that I/O is happening much faster than is possible when doing real disk I/O. We illustrate this idea in the diagram to the right by showing that the new rollback journal appears in the operating system disk cache only and not on the disk itself.

3.6 Changing Database Pages In User Space

After the original page content has been saved in the rollback journal, the pages can be modified in user memory. Each database connection has its own private copy of user space, so the changes that are made in user space are only visible to the database connection that is making the changes. Other database connections still see the information in operating system disk cache buffers which have not yet been changed. And so even though one process is busy modifying the database, other processes can continue to read their own copies of the original database content.

3.7 Flushing The Rollback Journal File To Mass Storage

The next step is to flush the content of the rollback journal file to nonvolatile storage. As we will see later, this is a critical step in insuring that the database can survive an unexpected power loss. This step also takes a lot of time, since writing to nonvolatile storage is normally a slow operation.

This step is usually more complicated than simply flushing the rollback journal to the disk. On most platforms two separate flush (or fsync()) operations are required. The first flush writes out the base rollback journal content. Then the header of the rollback journal is modified to show the number of pages in the rollback journal. Then the header is flushed to disk. The details on why we do this header modification and extra flush are provided in a later section of this paper.

3.8 Obtaining An Exclusive Lock

Prior to making changes to the database file itself, we must obtain an exclusive lock on the database file. Obtaining an exclusive lock is really a two-step process. First SQLite obtains a "pending" lock. Then it escalates the pending lock to an exclusive lock.

A pending lock allows other processes that already have a shared lock to continue reading the database file. But it prevents new shared locks from being established. The idea behind a pending lock is to prevent writer starvation caused by a large pool of readers. There might be dozens, even hundreds, of other processes trying to read the database file. Each process acquires a shared lock before it starts reading, reads what it needs, then releases the shared lock. If, however, there are many different processes all reading from the same database, it might happen that a new process always acquires its shared lock before the previous process releases its shared lock. And so there is never an instant when there are no shared locks on the database file and hence there is never an opportunity for the writer to seize the exclusive lock. A pending lock is designed to prevent that cycle by allowing existing shared locks to proceed but blocking new shared locks from being established. Eventually all shared locks will clear and the pending lock will then be able to escalate into an exclusive lock.

3.9 Writing Changes To The Database File

Once an exclusive lock is held, we know that no other processes are reading from the database file and it is safe to write changes into the database file. Usually those changes only go as far as the operating systems disk cache and do not make it all the way to mass storage.

3.10 Flushing Changes To Mass Storage

Another flush must occur to make sure that all the database changes are written into nonvolatile storage. This is a critical step to insure that the database will survive a power loss without damage. However, because of the inherent slowness of writing to disk or flash memory, this step together with the rollback journal file flush in section 3.7 above takes up most the time required to complete a transaction commit in SQLite.

3.11 Deleting The Rollback Journal

After the database changes are all safely on the mass storage device, the rollback journal file is deleted. This is the instant where the transaction commits. If a power failure or system crash occurs prior to this point, then recovery processes to be described later make it appears as if no changes were ever made to the database file. If a power failure or system crash occurs after the rollback journal is deleted, then it appears as if all changes have been written to disk. Thus, SQLite gives the appearance of having made no changes to the database file or having made the complete set of changes to the database file depending on whether or not the rollback journal file exists.

Deleting a file is not really an atomic operation, but it appears to be from the point of view of a user process. A process is always able to ask the operating system "does this file exist?" and the process will get back a yes or no answer. After a power failure that occurs during a transaction commit, SQLite will ask the operating system whether or not the rollback journal file exists. If the answer is "yes" then the transaction is incomplete and is rolled back. If the answer is "no" then it means the transaction did commit.

The existence of a transaction depends on whether or not the rollback journal file exists and the deletion of a file appears to be an atomic operation from the point of view of a user-space process. Therefore, a transaction appears to be an atomic operation.

The act of deleting a file is expensive on many systems. As an optimization, SQLite can be configured to truncate the journal file to zero bytes in length or overwrite the journal file header with zeros. In either case, the resulting journal file is no longer capable of rolling back and so the transaction still commits. Truncating a file to zero length, like deleting a file, is assumed to be an atomic operation from the point of view of a user process. Overwriting the header of the journal with zeros is not atomic, but if any part of the header is malformed the journal will not roll back. Hence, one can say that the commit occurs as soon as the header is sufficiently changed to make it invalid. Typically this happens as soon as the first byte of the header is zeroed.

3.12 Releasing The Lock

The last step in the commit process is to release the exclusive lock so that other processes can once again start accessing the database file.

In the diagram at the right, we show that the information that was held in user space is cleared when the lock is released. This used to be literally true for older versions of SQLite. But more recent versions of SQLite keep the user space information in memory in case it might be needed again at the start of the next transaction. It is cheaper to reuse information that is already in local memory than to transfer the information back from the operating system disk cache or to read it off of the disk drive again. Prior to reusing the information in user space, we must first reacquire the shared lock and then we have to check to make sure that no other process modified the database file while we were not holding a lock. There is a counter in the first page of the database that is incremented every time the database file is modified. We can find out if another process has modified the database by checking that counter. If the database was modified, then the user space cache must be cleared and reread. But it is commonly the case that no changes have been made and the user space cache can be reused for a significant performance savings.

4.0 Rollback

An atomic commit is supposed to happen instantaneously. But the processing described above clearly takes a finite amount of time. Suppose the power to the computer were cut part way through the commit operation described above. In order to maintain the illusion that the changes were instantaneous, we have to "rollback" any partial changes and restore the database to the state it was in prior to the beginning of the transaction.

4.1 When Something Goes Wrong...

Suppose the power loss occurred during step 3.10 above, while the database changes were being written to disk. After power is restored, the situation might be something like what is shown to the right. We were trying to change three pages of the database file but only one page was successfully written. Another page was partially written and a third page was not written at all.

The rollback journal is complete and intact on disk when the power is restored. This is a key point. The reason for the flush operation in step 3.7 is to make absolutely sure that all of the rollback journal is safely on nonvolatile storage prior to making any changes to the database file itself.

4.2 Hot Rollback Journals

The first time that any SQLite process attempts to access the database file, it obtains a shared lock as described in section 3.2 above. But then it notices that there is a rollback journal file present. SQLite then checks to see if the rollback journal is a "hot journal". A hot journal is a rollback journal that needs to be played back in order to restore the database to a sane state. A hot journal only exists when an earlier process was in the middle of committing a transaction when it crashed or lost power.

A rollback journal is a "hot" journal if all of the following are true:

  • The rollback journal exist.
  • The rollback journal is not an empty file.
  • There is no reserved lock on the main database file.
  • The header of the rollback journal is well-formed and in particular has not been zeroed out.
  • The rollback journal does not contain the name of a master journal file (see section 5.5 below) or if does contain the name of a master journal, then that master journal file exists.

The presence of a hot journal is our indication that a previous process was trying to commit a transaction but it aborted for some reason prior to the completion of the commit. A hot journal means that the database file is in an inconsistent state and needs to be repaired (by rollback) prior to being used.

4.3 Obtaining An Exclusive Lock On The Database

The first step toward dealing with a hot journal is to obtain an exclusive lock on the database file. This prevents two or more processes from trying to rollback the same hot journal at the same time.

4.4 Rolling Back Incomplete Changes

Once a process obtains an exclusive lock, it is permitted to write to the database file. It then proceeds to read the original content of pages out of the rollback journal and write that content back to were it came from in the database file. Recall that the header of the rollback journal records the original size of the database file prior to the start of the aborted transaction. SQLite uses this information to truncate the database file back to its original size in cases where the incomplete transaction caused the database to grow. At the end of this step, the database should be the same size and contain the same information as it did before the start of the aborted transaction.

4.5 Deleting The Hot Journal

After all information in the rollback journal has been played back into the database file (and flushed to disk in case we encounter yet another power failure), the hot rollback journal can be deleted.

As in section 3.11, the journal file might be truncated to zero length or its header might be overwritten with zeros as an optimization on systems where deleting a file is expense. Either way, the journal is no long hot after this step.

4.6 Continue As If The Uncompleted Writes Had Never Happened

The final recovery step is to reduce the exclusive lock back to a shared lock. Once this happens, the database is back in the state that it would have been if the aborted transaction had never started. Since all of this recovery activity happens completely automatically and transparently, it appears to the program using SQLite as if the aborted transaction had never begun.

5.0 Multi-file Commit

SQLite allows a single database connection to talk to two or more database files simultaneously through the use of the ATTACH DATABASE command. When multiple database files are modified within a single transaction, all files are updated atomically. In other words, either all of the database files are updated or else none of them are. Achieving an atomic commit across multiple database files is more complex that doing so for a single file. This section describes how SQLite works that bit of magic.

5.1 Separate Rollback Journals For Each Database

When multiple database files are involved in a transaction, each database has its own rollback journal and each database is locked separately. The diagram at the right shows a scenario where three different database files have been modified within one transaction. The situation at this step is analogous to the single-file transaction scenario at step 3.6. Each database file has a reserved lock. For each database, the original content of pages that are being changed have been written into the rollback journal for that database, but the content of the journals have not yet been flushed to disk. No changes have been made to the database file itself yet, though presumably there are changes being held in user memory.

For brevity, the diagrams in this section are simplified from those that came before. Blue color still signifies original content and pink still signifies new content. But the individual pages in the rollback journal and the database file are not shown and we are not making the distinction between information in the operating system cache and information that is on disk. All of these factors still apply in a multi-file commit scenario. They just take up a lot of space in the diagrams and they do not add any new information, so they are omitted here.

5.2 The Master Journal File

The next step in a multi-file commit is the creation of a "master journal" file. The name of the master journal file is the same name as the original database filename (the database that was opened using the sqlite3_open() interface, not one of the ATTACHed auxiliary databases) with the text "-mj HHHHHHHH" appended where HHHHHHHH is a random 32-bit hexadecimal number. The random HHHHHHHH suffix changes for every new master journal.

(Nota bene: The formula for computing the master journal filename given in the previous paragraph corresponds to the implementation as of SQLite version 3.5.0. But this formula is not part of the SQLite specification and is subject to change in future releases.)

Unlike the rollback journals, the master journal does not contain any original database page content. Instead, the master journal contains the full pathnames for rollback journals for every database that is participating in the transaction.

After the master journal is constructed, its content is flushed to disk before any further actions are taken. On Unix, the directory that contains the master journal is also synced in order to make sure the master journal file will appear in the directory following a power failure.

5.3 Updating Rollback Journal Headers

The next step is to record the full pathname of the master journal file in the header of every rollback journal. Space to hold the master journal filename was reserved at the beginning of each rollback journal as the rollback journals were created.

The content of each rollback journal is flushed to disk both before and after the master journal filename is written into the rollback journal header. It is important to do both of these flushes. Fortunately, the second flush is usually inexpensive since typically only a single page of the journal file (the first page) has changed.

This step is analogous to step 3.7 in the single-file commit scenario described above.

5.4 Updating The Database Files

Once all rollback journal files have been flushed to disk, it is safe to begin updating database files. We have to obtain an exclusive lock on all database files before writing the changes. After all the changes are written, it is important to flush the changes to disk so that they will be preserved in the event of a power failure or operating system crash.

This step corresponds to steps 3.8, 3.9, and 3.10 in the single-file commit scenario described previously.

5.5 Delete The Master Journal File

The next step is to delete the master journal file. This is the point where the multi-file transaction commits. This step corresponds to step 3.11 in the single-file commit scenario where the rollback journal is deleted.

If a power failure or operating system crash occurs at this point, the transaction will not rollback when the system reboots even though there are rollback journals present. The difference is the master journal pathname in the header of the rollback journal. Upon restart, SQLite only considers a journal to be hot and will only playback the journal if there is no master journal filename in the header (which is the case for a single-file commit) or if the master journal file still exists on disk.

5.6 Clean Up The Rollback Journals

The final step in a multi-file commit is to delete the individual rollback journals and drop the exclusive locks on the database files so that other processes can see the changes. This corresponds to step 3.12 in the single-file commit sequence.

The transaction has already committed at this point so timing is not critical in the deletion of the rollback journals. The current implementation deletes a single rollback journal then unlocks the corresponding database file before proceeding to the next rollback journal. But in the future we might change this so that all rollback journals are deleted before any database files are unlocked. As long as the rollback journal is deleted before its corresponding database file is unlocked it does not matter in what order the rollback journals are deleted or the database files are unlocked.

6.0 Additional Details Of The Commit Process

Section 3.0 above provides an overview of how atomic commit works in SQLite. But it glosses over a number of important details. The following subsections will attempt to fill in the gaps.

6.1 Always Journal Complete Sectors

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

It is important to store all pages of a sector in the rollback journal in order to prevent database corruption following a power loss while writing the sector. Suppose that pages 1, 2, 3, and 4 are all stored in sector 1 and that page 2 is modified. In order to write the changes to page 2, the underlying hardware must also rewrite the content of pages 1, 3, and 4 since the hardware must write the complete sector. If this write operation is interrupted by a power outage, one or more of the pages 1, 3, or 4 might be left with incorrect data. Hence, to avoid lasting corruption to the database, the original content of all of those pages must be contained in the rollback journal.

6.2 Dealing With Garbage Written Into Journal Files

When data is appended to the end of the rollback journal, SQLite normally makes the pessimistic assumption that the file is first extended with invalid "garbage" data and that afterwards the correct data replaces the garbage. In other words, SQLite assumes that the file size is increased first and then afterwards the content is written into the file. If a power failure occurs after the file size has been increased but before the file content has been written, the rollback journal can be left containing garbage data. If after power is restored, another SQLite process sees the rollback journal containing the garbage data and tries to roll it back into the original database file, it might copy some of the garbage into the database file and thus corrupt the database file.

SQLite uses two defenses against this problem. In the first place, SQLite records the number of pages in the rollback journal in the header of the rollback journal. This number is initially zero. So during an attempt to rollback an incomplete (and possibly corrupt) rollback journal, the process doing the rollback will see that the journal contains zero pages and will thus make no changes to the database. Prior to a commit, the rollback journal is flushed to disk to ensure that all content has been synced to disk and there is no "garbage" left in the file, and only then is the page count in the header changed from zero to true number of pages in the rollback journal. The rollback journal header is always kept in a separate sector from any page data so that it can be overwritten and flushed without risking damage to a data page if a power outage occurs. Notice that the rollback journal is flushed to disk twice: once to write the page content and a second time to write the page count in the header.

The previous paragraph describes what happens when the synchronous pragma setting is "full".

PRAGMA synchronous=FULL;

The default synchronous setting is full so the above is what usually happens. However, if the synchronous setting is lowered to "normal", SQLite only flushes the rollback journal once, after the page count has been written. This carries a risk of corruption because it might happen that the modified (non-zero) page count reaches the disk surface before all of the data does. The data will have been written first, but SQLite assumes that the underlying filesystem can reorder write requests and that the page count can be burned into oxide first even though its write request occurred last. So as a second line of defense, SQLite also uses a 32-bit checksum on every page of data in the rollback journal. This checksum is evaluated for each page during rollback while rolling back a journal as described in section 4.4. If an incorrect checksum is seen, the rollback is abandoned. Note that the checksum does not guarantee that the page data is correct since there is a small but finite probability that the checksum might be right even if the data is corrupt. But the checksum does at least make such an error unlikely.

Note that the checksums in the rollback journal are not necessary if the synchronous setting is FULL. We only depend on the checksums when synchronous is lowered to NORMAL. Nevertheless, the checksums never hurt and so they are included in the rollback journal regardless of the synchronous setting.

6.3 Cache Spill Prior To Commit

The commit process shown in section 3.0 assumes that all database changes fit in memory until it is time to commit. This is the common case. But sometimes a larger change will overflow the user-space cache prior to transaction commit. In those cases, the cache must spill to the database before the transaction is complete.

At the beginning of a cache spill, the status of the database connection is as shown in step 3.6. Original page content has been saved in the rollback journal and modifications of the pages exist in user memory. To spill the cache, SQLite executes steps 3.7 through 3.9. In other words, the rollback journal is flushed to disk, an exclusive lock is acquired, and changes are written into the database. But the remaining steps are deferred until the transaction really commits. A new journal header is appended to the end of the rollback journal (in its own sector) and the exclusive database lock is retained, but otherwise processing returns to step 3.6. When the transaction commits, or if another cache spill occurs, steps 3.7 and 3.9 are repeated. (Step 3.8 is omitted on second and subsequent passes since an exclusive database lock is already held due to the first pass.)

A cache spill causes the lock on the database file to escalate from reserved to exclusive. This reduces concurrency. A cache spill also causes extra disk flush or fsync operations to occur and these operations are slow, hence a cache spill can seriously reduce performance. For these reasons a cache spill is avoided whenever possible.

7.0 Optimizations

Profiling indicates that for most systems and in most circumstances SQLite spends most of its time doing disk I/O. It follows then that anything we can do to reduce the amount of disk I/O will likely have a large positive impact on the performance of SQLite. This section describes some of the techniques used by SQLite to try to reduce the amount of disk I/O to a minimum while still preserving atomic commit.

7.1 Cache Retained Between Transactions

Step 3.12 of the commit process shows that once the shared lock has been released, all user-space cache images of database content must be discarded. This is done because without a shared lock, other processes are free to modify the database file content and so any user-space image of that content might become obsolete. Consequently, each new transaction would begin by rereading data which had previously been read. This is not as bad as it sounds at first since the data being read is still likely in the operating systems file cache. So the "read" is really just a copy of data from kernel space into user space. But even so, it still takes time.

Beginning with SQLite version 3.3.14 a mechanism has been added to try to reduce the needless rereading of data. In newer versions of SQLite, the data in the user-space pager cache is retained when the lock on the database file is released. Later, after the shared lock is acquired at the beginning of the next transaction, SQLite checks to see if any other process has modified the database file. If the database has been changed in any way since the lock was last released, the user-space cache is erased at that point. But commonly the database file is unchanged and the user-space cache can be retained, and some unnecessary read operations can be avoided.

In order to determine whether or not the database file has changed, SQLite uses a counter in the database header (in bytes 24 through 27) which is incremented during every change operation. SQLite saves a copy of this counter prior to releasing its database lock. Then after acquiring the next database lock it compares the saved counter value against the current counter value and erases the cache if the values are different, or reuses the cache if they are the same.

7.2 Exclusive Access Mode

SQLite version 3.3.14 adds the concept of "Exclusive Access Mode". In exclusive access mode, SQLite retains the exclusive database lock at the conclusion of each transaction. This prevents other processes for accessing the database, but in many deployments only a single process is using a database so this is not a serious problem. The advantage of exclusive access mode is that disk I/O can be reduced in three ways:

  1. It is not necessary to increment the change counter in the database header for transactions after the first transaction. This will often save a write of page one to both the rollback journal and the main database file.

  2. No other processes can change the database so there is never a need to check the change counter and clear the user-space cache at the beginning of a transaction.

  3. Each transaction can be committed by overwriting the rollback journal header with zeros rather than deleting the journal file. This avoids having to modify the directory entry for the journal file and it avoids having to deallocate disk sectors associated with the journal. Furthermore, the next transaction will overwrite existing journal file content rather than append new content and on most systems overwriting is much faster than appending.

The third optimization, zeroing the journal file header rather than deleting the rollback journal file, does not depend on holding an exclusive lock at all times. This optimization can be set independently of exclusive lock mode using the journal_mode pragma as described in section 7.6 below.

7.3 Do Not Journal Freelist Pages

When information is deleted from an SQLite database, the pages used to hold the deleted information are added to a "freelist". Subsequent inserts will draw pages off of this freelist rather than expanding the database file.

Some freelist pages contain critical data; specifically the locations of other freelist pages. But most freelist pages contain nothing useful. These latter freelist pages are called "leaf" pages. We are free to modify the content of a leaf freelist page in the database without changing the meaning of the database in any way.

Because the content of leaf freelist pages is unimportant, SQLite avoids storing leaf freelist page content in the rollback journal in step 3.5 of the commit process. If a leaf freelist page is changed and that change does not get rolled back during a transaction recovery, the database is not harmed by the omission. Similarly, the content of a new freelist page is never written back into the database at step 3.9 nor read from the database at step 3.3. These optimizations can greatly reduce the amount of I/O that occurs when making changes to a database file that contains free space.

7.4 Single Page Updates And Atomic Sector Writes

Beginning in SQLite version 3.5.0, the new Virtual File System (VFS) interface contains a method named xDeviceCharacteristics which reports on special properties that the underlying mass storage device might have. Among the special properties that xDeviceCharacteristics might report is the ability of to do an atomic sector write.

Recall that by default SQLite assumes that sector writes are linear but not atomic. A linear write starts at one end of the sector and changes information byte by byte until it gets to the other end of the sector. If a power loss occurs in the middle of a linear write then part of the sector might be modified while the other end is unchanged. In an atomic sector write, either the entire sector is overwritten or else nothing in the sector is changed.

We believe that most modern disk drives implement atomic sector writes. When power is lost, the drive uses energy stored in capacitors and/or the angular momentum of the disk platter to provide power to complete any operation in progress. Nevertheless, there are so many layers in between the write system call and the on-board disk drive electronics that we take the safe approach in both Unix and w32 VFS implementations and assume that sector writes are not atomic. On the other hand, device manufactures with more control over their filesystems might want to consider enabling the atomic write property of xDeviceCharacteristics if their hardware really does do atomic writes.

When sector writes are atomic and the page size of a database is the same as a sector size, and when there is a database change that only touches a single database page, then SQLite skips the whole journaling and syncing process and simply writes the modified page directly into the database file. The change counter in the first page of the database file is modified separately since no harm is done if power is lost before the change counter can be updated.

7.5 Filesystems With Safe Append Semantics

Another optimization introduced in SQLite version 3.5.0 makes use of "safe append" behavior of the underlying disk. Recall that SQLite assumes that when data is appended to a file (specifically to the rollback journal) that the size of the file is increased first and that the content is written second. So if power is lost after the file size is increased but before the content is written, the file is left containing invalid "garbage" data. The xDeviceCharacteristics method of the VFS might, however, indicate that the filesystem implements "safe append" semantics. This means that the content is written before the file size is increased so that it is impossible for garbage to be introduced into the rollback journal by a power loss or system crash.

When safe append semantics are indicated for a filesystem, SQLite always stores the special value of -1 for the page count in the header of the rollback journal. The -1 page count value tells any process attempting to rollback the journal that the number of pages in the journal should be computed from the journal size. This -1 value is never changed. So that when a commit occurs, we save a single flush operation and a sector write of the first page of the journal file. Furthermore, when a cache spill occurs we no longer need to append a new journal header to the end of the journal; we can simply continue appending new pages to the end of the existing journal.

7.6 Persistent Rollback Journals

Deleting a file is an expensive operation on many systems. So as an optimization, SQLite can be configured to avoid the delete operation of section 3.11. Instead of deleting the journal file in order to commit a transaction, the file is either truncated to zero bytes in length or its header is overwritten with zeros. Truncating the file to zero length saves having to make modifications to the directory containing the file since the file is not removed from the directory. Overwriting the header has the additional savings of not having to update the length of the file (in the "inode" on many systems) and not having to deal with newly freed disk sectors. Furthermore, at the next transaction the journal will be created by overwriting existing content rather than appending new content onto the end of a file, and overwriting is often much faster than appending.

SQLite can be configured to commit transactions by overwriting the journal header with zeros instead of deleting the journal file by setting the "PERSIST" journaling mode using the journal_mode PRAGMA. For example:

PRAGMA journal_mode=PERSIST;

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

PRAGMA journal_mode=DELETE;
BEGIN EXCLUSIVE;
COMMIT;

Beware of deleting persistent journal files by any other means since the journal file might be hot, in which case deleting it will corrupt the corresponding database file.

Beginning in SQLite version 3.6.4, the TRUNCATE journal mode is also supported:

PRAGMA journal_mode=TRUNCATE;

In truncate journal mode, the transaction is committed by truncating the journal file to zero length rather than deleting the journal file (as in DELETE mode) or by zeroing the header (as in PERSIST mode). TRUNCATE mode shares the advantage of PERSIST mode that the directory that contains the journal file and database does not need to be updated. Hence truncating a file is often faster than deleting it. TRUNCATE has the additional advantage that it is not followed by a system call (ex: fsync()) to synchronize the change to disk. It might be safer if it did. But on many modern filesystems, a truncate is an atomic and synchronous operation and so we think that TRUNCATE will usually be safe in the face of power failures. If you are uncertain about whether or not TRUNCATE will be synchronous and atomic on your filesystem and it is important to you that your database survive a power loss or operating system crash that occurs during the truncation operation, then you might consider using a different journaling mode.

On embedded systems with synchronous filesystems, TRUNCATE results in slower behavior than PERSIST. The commit operation is the same speed. But subsequent transactions are slower following a TRUNCATE because it is faster to overwrite existing content than to append to the end of a file. New journal file entries will always be appended following a TRUNCATE but will usually overwrite with PERSIST.

8.0 Testing Atomic Commit Behavior

The developers of SQLite are confident that it is robust in the face of power failures and system crashes because the automatic test procedures do extensive checks on the ability of SQLite to recover from simulated power loss. We call these the "crash tests".

Crash tests in SQLite use a modified VFS that can simulate the kinds of filesystem damage that occur during a power loss or operating system crash. The crash-test VFS can simulate incomplete sector writes, pages filled with garbage data because a write has not completed, and out of order writes, all occurring at varying points during a test scenario. Crash tests execute transactions over and over, varying the time at which a simulated power loss occurs and the properties of the damage inflicted. Each test then reopens the database after the simulated crash and verifies that the transaction either occurred completely or not at all and that the database is in a completely consistent state.

The crash tests in SQLite have discovered a number of very subtle bugs (now fixed) in the recovery mechanism. Some of these bugs were very obscure and unlikely to have been found using only code inspection and analysis techniques. From this experience, the developers of SQLite feel confident that any other database system that does not use a similar crash test system likely contains undetected bugs that will lead to database corruption following a system crash or power failure.

9.0 Things That Can Go Wrong

The atomic commit mechanism in SQLite has proven to be robust, but it can be circumvented by a sufficiently creative adversary or a sufficiently broken operating system implementation. This section describes a few of the ways in which an SQLite database might be corrupted by a power failure or system crash. (See also: How To Corrupt Your Database Files.)

9.1 Broken Locking Implementations

SQLite uses filesystem locks to make sure that only one process and database connection is trying to modify the database at a time. The filesystem locking mechanism is implemented in the VFS layer and is different for every operating system. SQLite depends on this implementation being correct. If something goes wrong and two or more processes are able to write the same database file at the same time, severe damage can result.

We have received reports of implementations of both Windows network filesystems and NFS in which locking was subtly broken. We can not verify these reports, but as locking is difficult to get right on a network filesystem we have no reason to doubt them. You are advised to avoid using SQLite on a network filesystem in the first place, since performance will be slow. But if you must use a network filesystem to store SQLite database files, consider using a secondary locking mechanism to prevent simultaneous writes to the same database even if the native filesystem locking mechanism malfunctions.

The versions of SQLite that come preinstalled on Apple Mac OS X computers contain a version of SQLite that has been extended to use alternative locking strategies that work on all network filesystems that Apple supports. These extensions used by Apple work great as long as all processes are accessing the database file in the same way. Unfortunately, the locking mechanisms do not exclude one another, so if one process is accessing a file using (for example) AFP locking and another process (perhaps on a different machine) is using dot-file locks, the two processes might collide because AFP locks do not exclude dot-file locks or vice versa.

9.2 Incomplete Disk Flushes

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 step 3.7 and step 3.10. 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 the IDE disk control lies and says that data has reached oxide while it is still held only in the volatile control cache.

On the Mac, you can set this pragma:

PRAGMA fullfsync=ON;

Setting fullfsync on a Mac will guarantee that data really does get pushed out to the disk platter on a flush. But the implementation of fullfsync involves resetting the disk controller. And so not only is it profoundly slow, it also slows down other unrelated disk I/O. So its use is not recommended.

9.3 Partial File Deletions

SQLite assumes that file deletion is an atomic operation from the point of view of a user process. If power fails in the middle of a file deletion, then after power is restored SQLite expects to see either the entire file with all of its original data intact, or it expects not to find the file at all. Transactions may not be atomic on systems that do not work this way.

9.4 Garbage Written Into Files

SQLite database files are ordinary disk files that can be opened and written by ordinary user processes. A rogue process can open an SQLite database and fill it with corrupt data. Corrupt data might also be introduced into an SQLite database by bugs in the operating system or disk controller; especially bugs triggered by a power failure. There is nothing SQLite can do to defend against these kinds of problems.

9.5 Deleting Or Renaming A Hot Journal

If a crash or power loss does occur and a hot journal is left on the disk, it is essential that the original database file and the hot journal remain on disk with their original names until the database file is opened by another SQLite process and rolled back. During recovery at step 4.2 SQLite locates the hot journal by looking for a file in the same directory as the database being opened and whose name is derived from the name of the file being opened. If either the original database file or the hot journal have been moved or renamed, then the hot journal will not be seen and the database will not be rolled back.

We suspect that a common failure mode for SQLite recovery happens like this: A power failure occurs. After power is restored, a well-meaning user or system administrator begins looking around on the disk for damage. They see their database file named "important.data". This file is perhaps familiar to them. But after the crash, there is also a hot journal named "important.data-journal". The user then deletes the hot journal, thinking that they are helping to cleanup the system. We know of no way to prevent this other than user education.

If there are multiple (hard or symbolic) links to a database file, the journal will be created using the name of the link through which the file was opened. If a crash occurs and the database is opened again using a different link, the hot journal will not be located and no rollback will occur.

Sometimes a power failure will cause a filesystem to be corrupted such that recently changed filenames are forgotten and the file is moved into a "/lost+found" directory. When that happens, the hot journal will not be found and recovery will not occur. SQLite tries to prevent this by opening and syncing the directory containing the rollback journal at the same time it syncs the journal file itself. However, the movement of files into /lost+found can be caused by unrelated processes creating unrelated files in the same directory as the main database file. And since this is out from under the control of SQLite, there is nothing that SQLite can do to prevent it. If you are running on a system that is vulnerable to this kind of filesystem namespace corruption (most modern journalling filesystems are immune, we believe) then you might want to consider putting each SQLite database file in its own private subdirectory.

10.0 Future Directions And Conclusion

Every now and then someone discovers a new failure mode for the atomic commit mechanism in SQLite and the developers have to put in a patch. This is happening less and less and the failure modes are becoming more and more obscure. But it would still be foolish to suppose that the atomic commit logic of SQLite is entirely bug-free. The developers are committed to fixing these bugs as quickly as they might be found.

The developers are also on the lookout for new ways to optimize the commit mechanism. The current VFS implementations for Unix (Linux and Mac OS X) and Windows make pessimistic assumptions about the behavior of those systems. After consultation with experts on how these systems work, we might be able to relax some of the assumptions on these systems and allow them to run faster. In particular, we suspect that most modern filesystems exhibit the safe append property and that many of them might support atomic sector writes. But until this is known for certain, SQLite will take the conservative approach and assume the worst.


Command Line Shell For SQLite

The SQLite library includes a simple command-line utility named sqlite3 (or sqlite3.exe on windows) that allows the user to manually enter and execute SQL commands against an SQLite database. This document provides a brief introduction on how to use the sqlite3 program.

Getting Started

To start the sqlite3 program, just type "sqlite3" followed by the name the file that holds the SQLite database. If the file does not exist, a new one is created automatically. The sqlite3 program will then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.

For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:

$ sqlite3 ex1
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>

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

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 added to the current SQL command. This feature allows you to enter SQL commands that span multiple lines. For example:

sqlite> CREATE TABLE tbl2 (
   ...>   f1 varchar(30) primary key,
   ...>   f2 text,
   ...>   f3 real
   ...> );
sqlite>

Aside: Querying the SQLITE_MASTER table

The database schema in an SQLite database is stored in a special table named "sqlite_master". You can execute "SELECT" statements against the special sqlite_master table just like any other table in an SQLite database. For example:

$ sqlite3 ex1
SQLite vresion 3.6.11
Enter ".help" for instructions
sqlite> select * from sqlite_master;
    type = table
    name = tbl1
tbl_name = tbl1
rootpage = 3
     sql = create table tbl1(one varchar(10), two smallint)
sqlite>

But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against the sqlite_master table. The sqlite_master table is updated automatically as you create or drop tables and indices from the database. You can not make manual changes to the sqlite_master table.

The schema for TEMPORARY tables is not stored in the "sqlite_master" table since TEMPORARY tables are not visible to applications other than the application that created the table. The schema for TEMPORARY tables is stored in another special table named "sqlite_temp_master". The "sqlite_temp_master" table is temporary itself.

Special commands to sqlite3

Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot ("."), then that line is intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements.

For a listing of the available dot commands, you can enter ".help" at any time. For example:

sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.genfkey ?OPTIONS?     Options are:
                         --no-drop: Do not drop old fkey triggers.
                         --ignore-errors: Ignore tables with fkey errors
                         --exec: Execute generated SQL immediately
                       See file tool/genfkey.README in the source
                       distribution for further information.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.iotrace FILE          Enable I/O diagnostic logging to FILE
.load FILE ?ENTRY?     Load an extension library
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds
.timer ON|OFF          Turn the CPU timer measurement on or off
.width NUM NUM ...     Set column widths for "column" mode
sqlite>

Changing Output Formats

The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.

The default output mode is "list". In list mode, each record of a query result is written on one line of output and each column within that record is separated by a specific separator string. The default separator is a pipe symbol ("|"). List mode is especially useful when you are going to send the output of a query to another program (such as AWK) for additional processing.

sqlite> .mode list
sqlite> select * from tbl1;
hello|10
goodbye|20
sqlite>

You can use the ".separator" dot command to change the separator for list mode. For example, to change the separator to a comma and a space, you could do this:

sqlite> .separator ", "
sqlite> select * from tbl1;
hello, 10
goodbye, 20
sqlite>

In "line" mode, each column in a row of the database is shown on a line by itself. Each line consists of the column name, an equal sign and the column data. Successive records are separated by a blank line. Here is an example of line mode output:

sqlite> .mode line
sqlite> select * from tbl1;
one = hello
two = 10

one = goodbye
two = 20
sqlite>

In column mode, each record is shown on a separate line with the data aligned in columns. For example:

sqlite> .mode column
sqlite> select * from tbl1;
one         two
----------  ----------
hello       10
goodbye     20
sqlite>

By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the ".width" command. Like this:

sqlite> .width 12 6
sqlite> select * from tbl1;
one           two
------------  ------
hello         10
goodbye       20
sqlite>

The ".width" command in the example above sets the width of the first column to 12 and the width of the second column to 6. All other column widths were unaltered. You can gives as many arguments to ".width" as necessary to specify the widths of as many columns as are in your query results.

If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.

The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the examples above, the column labels are on. To turn them off you could do this:

sqlite> .header off
sqlite> select * from tbl1;
hello         10
goodbye       20
sqlite>

Another useful output mode is "insert". In insert mode, the output is formatted to look like SQL INSERT statements. You can use insert mode to generate text that can later be used to input data into a different database.

When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:

sqlite> .mode insert new_table
sqlite> select * from tbl1;
INSERT INTO 'new_table' VALUES('hello',10);
INSERT INTO 'new_table' VALUES('goodbye',20);
sqlite>

The last output mode is "html". In this mode, sqlite3 writes the results of the query as an XHTML table. The beginning <TABLE> and the ending </TABLE> are not written, but all of the intervening <TR>s, <TH>s, and <TD>s are. The html output mode is envisioned as being useful for CGI.

Writing results to a file

By default, sqlite3 sends query results to standard output. You can change this using the ".output" command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use ".output stdout" to begin writing to standard output again. For example:

sqlite> .mode list
sqlite> .separator |
sqlite> .output test_file_1.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
hello|10
goodbye|20
$

Querying the database schema

The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.

For example, to see a list of the tables in the database, you can enter ".tables".

sqlite> .tables
tbl1
tbl2
sqlite>

The ".tables" command is similar to setting list mode then executing the following query:

SELECT name FROM sqlite_master
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
WHERE type IN ('table','view')
ORDER BY 1

In fact, if you look at the source code to the sqlite3 program (found in the source tree in the file src/shell.c) you'll find exactly the above query.

The ".indices" command works in a similar way to list all of the indices for a particular table. The ".indices" command takes a single argument which is the name of the table for which the indices are desired. Last, but not least, is the ".schema" command. With no arguments, the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. If you give the name of a table to ".schema", it shows the original CREATE statement used to make that table and all if its indices. We have:

sqlite> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite> .schema tbl2
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite>

The ".schema" command accomplishes the same thing as setting list mode, then entering the following query:

SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta'
ORDER BY tbl_name, type DESC, name

Or, if you give an argument to ".schema" because you only want the schema for a single table, the query looks like this:

SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name

You can supply an argument to the .schema command. If you do, the query looks like this:

SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE '%s'
  AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name

The "%s" in the query is replace by your argument. This allows you to view the schema for some subset of the database.

sqlite> .schema %abc%

Along these same lines, the ".table" command also accepts a pattern as its first argument. If you give an argument to the .table command, a "%" is both appended and prepended and a LIKE clause is added to the query. This allows you to list only those tables that match a particular pattern.

The ".databases" command shows a list of all databases open in the current connection. There will always be at least 2. The first one is "main", the original database opened. The second is "temp", the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second column is the filename of the external file.

sqlite> .databases

Converting An Entire Database To An ASCII Text File

Use the ".dump" command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite3.

A good way to make an archival copy of a database is this:

$ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz

This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:

$ zcat ex1.dump.gz | sqlite3 ex2

The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:

$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2

Other Dot Commands

The ".explain" dot command can be used to set the output mode to "column" and to set the column widths to values that are reasonable for looking at the output of an EXPLAIN command. The EXPLAIN command is an SQLite-specific SQL extension that is useful for debugging. If any regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and analyzed but is not executed. Instead, the sequence of virtual machine instructions that would have been used to execute the SQL command are returned like a query result. For example:

sqlite> .explain
sqlite> explain delete from tbl1 where two<20;
addr  opcode        p1     p2     p3
----  ------------  -----  -----  -------------------------------------
0     ListOpen      0      0
1     Open          0      1      tbl1
2     Next          0      9
3     Field         0      1
4     Integer       20     0
5     Ge            0      2
6     Key           0      0
7     ListWrite     0      0
8     Goto          0      2
9     Noop          0      0
10    ListRewind    0      0
11    ListRead      0      14
12    Delete        0      0
13    Goto          0      11
14    ListClose     0      0

The ".timeout" command sets the amount of time that the sqlite3 program will wait for locks to clear on files it is trying to access before returning an error. The default value of the timeout is zero so that an error is returned immediately if any needed database table or index is locked.

And finally, we mention the ".exit" command which causes the sqlite3 program to exit.

Using sqlite3 in a shell script

One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as an added convenience, sqlite3 allows a single SQL command to be entered on the command line as a second argument after the database name. When the sqlite3 program is launched with two arguments, the second argument is passed to the SQLite library for processing, the query results are printed on standard output in list mode, and the program exits. This mechanism is designed to make sqlite3 easy to use in conjunction with programs like "awk". For example:

$ sqlite3 ex1 'select * from tbl1' |
>  awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$

Ending shell commands

SQLite commands are normally terminated by a semicolon. In the shell you can also use the word "GO" (case-insensitive) or a slash character "/" on a line by itself to end a command. These are used by SQL Server and Oracle, respectively. These won't work in sqlite3_exec(), because the shell translates these into a semicolon before passing them to that function.

Compiling the sqlite3 program from sources

The source code to the sqlite3 command line interface is in a single file named "shell.c" which you can download from the SQLite website. Compile this file (together with the sqlite3 library source code to generate the executable. For example:

gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread


Datatypes In SQLite Version 3

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statement that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

1.0 Storage Classes and Datatypes

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

  • NULL. The value is a NULL value.

  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

  • BLOB. The value is a blob of data, stored exactly as it was input.

Note that a storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer). And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangably.

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

All values in SQL statements, whether they are literals embedded in SQL statement text or parameters bound to pre-compiled SQL statements have an implicit storage class. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.

1.1 Boolean Datatype

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

2.0 Type Affinity

In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

Each column in an SQLite 3 database is assigned one of the following type affinities:

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • NONE

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.

A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 30000, not as the floating point value 300000.0.

A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression.

A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.)

A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

2.1 Determination Of Column Affinity

The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

  1. If the declared type contains the string "INT" then it is assigned INTEGER affinity.

  2. If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

  3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity NONE.

  4. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.

  5. Otherwise, the affinity is NUMERIC.

Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.

2.2 Affinity Name Examples

The following table shows how many common datatype names from more traditional SQL implementations are converted into affinities by the five rules of the previous section. This table shows only a small subset of the datatype names that SQLite will accept. Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

Example Typenames From The
CREATE TABLE Statement
or CAST Expression 		Resulting Affinity Rule Used To Determine Affinity
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8 		INTEGER 1
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB 		TEXT 2
BLOB
no datatype specified 		NONE 3
REAL
DOUBLE
DOUBLE PRECISION
FLOAT 		REAL 4
NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME 		NUMERIC 5

Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

2.3 Column Affinity Behavior Example

The following SQL demonstrates how SQLite uses column affinity to do type conversions when values are inserted into a table.

CREATE TABLE t1(
    t  TEXT,     -- text affinity by rule 2
    nu NUMERIC,  -- numeric affinity by rule 5
    i  INTEGER,  -- integer affinity by rule 1
    r  REAL,     -- real affinity by rule 4
    no BLOB      -- no affinity by rule 3
);

-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text

-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|real

-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer

-- BLOBs are always stored as BLOBs regardless of column affinity.
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
blob|blob|blob|blob|blob

-- NULLs are also unaffected by affinity
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null

3.0 Comparison Expressions

SQLite version 3 has the usual set of SQL comparison operators including "=", "<", "<=", ">=", "!=", "IN", "BETWEEN", and "IS".

3.1 Sort Order

The results of a comparison depend on the storage classes of the operands, according to the following rules:

  • A value with storage class NULL is considered less than any other value (including another value with storage class NULL).

  • An INTEGER or REAL value is less than any TEXT or BLOB value. When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed.

  • A TEXT value is less than a BLOB value. When two TEXT values are compared an appropriate collating sequence is used to determine the result.

  • When two BLOB values are compared, the result is determined using memcmp().

3.2 Affinity Of Comparison Operands

SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the affinity of the operands. Operand affinity is determined by the the following rules:

  • An expression that is a simple reference to a column value has the same affinity as the column. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions for the purpose of determining affinity.

  • An expression of the form "CAST(expr TO type)" has an affinity that is the same as a column with a declared type of "type".

  • Otherwise, an expression has NONE affinity.

3.3 Type Conversions Prior To Comparison

To "apply affinity" means to convert an operand to a particular storage class if and only if the conversion is lossless and reversible. Affinity is applied to operands of a comparison operator prior to the comparison according to the following rules in the order shown:

  • If one operand has INTEGER, REAL or NUMERIC affinity and the other operand as TEXT or NONE affinity then NUMERIC affinity is applied to other operand.

  • If one operand has TEXT affinity and the other has NONE affinity, then TEXT affinity is applied to the other operand.

  • Otherwise, no affinity is applied and both operands are compared as is.

The expression "a BETWEEN b AND c" is treated as two separate binary comparisons "a >= b AND a <= c", even if that means different affinities are applied to 'a' in each of the comparisons. Datatype conversions in comparisons of the form "x IN (SELECT y ...)" are handled is if the comparison were really "x=y". The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR a = +y OR a = +z OR ...". In other words, the values to the right of the IN operator (the "x", "y", and "z" values in this example) are considered to have no affinity, even if they happen to be column values or CAST expressions.

3.4 Comparison Example

CREATE TABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
    d            -- no affinity
);

-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40,   a < 60,   a < 600 FROM t1;
0|1|1

-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1

-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right.  Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur.  All
-- values are compared numerically.
SELECT b < 40,   b < 60,   b < 600 FROM t1;
0|0|1

-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers.  Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40,   c < 60,   c < 600 FROM t1;
0|0|0

-- No affinity conversions occur.  Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40,   d < 60,   d < 600 FROM t1;
0|0|1

-- No affinity conversions occur.  INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1

All of the result in the example are the same if the comparisons are commuted - if expressions of the form "a<40" are rewritten as "40>a".

4.0 Operators

All mathematical operators (+, -, *, /, %, <<, >>, &, and |) cast both operands to the NUMERIC storage class prior to being carried out. The cast is carried through even if it is lossy and irreversible. A NULL operand on a mathematical operator yields a NULL result. An operand on a mathematical operator that does not look in any way numeric and is not NULL is converted to 0 or 0.0.

5.0 Sorting, Grouping and Compound SELECTs

When query results are sorted by an ORDER BY clause, values with storage class NULL come first, followed by INTEGER and REAL values interspersed in numeric order, followed by TEXT values in collating sequence order, and finally BLOB values in memcmp() order. No storage class conversions occur before the sort.

When grouping values with the GROUP BY clause values with different storage classes are considered distinct, except for INTEGER and REAL values which are considered equal if they are numerically equal. No affinities are applied to any values as the result of a GROUP by clause.

The compound SELECT operators UNION, INTERSECT and EXCEPT perform implicit comparisons between values. No affinity is applied to comparison operands for the implicit comparisons associated with UNION, INTERSECT, or EXCEPT - the values are compared as is.

6.0 Collating Sequences

When SQLite compares two strings, it uses a collating sequence or collating function (two words for the same thing) to determine which string is greater or if the two strings are equal. SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM.

  • BINARY - Compares string data using memcmp(), regardless of text encoding.
  • NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed. Note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required.
  • RTRIM - The same as binary, except that trailing space characters are ignored.

An application can register additional collating functions using the sqlite3_create_collation() interface.

6.1 Assigning Collating Sequences from SQL

Every column of every table has an associated collating function. If no collating function is explicitly defined, then the collating function defaults to BINARY. The COLLATE clause of the column definition is used to define alternative collating functions for a column.

The rules for determining which collating function to use for a binary comparison operator (=, <, >, <=, >=, !=, IS, and IS NOT) are as follows and in the order shown:

  1. If either operand has an explicit collating function assignment using the postfix COLLATE operator, then the explicit collating function is used for comparison, with precedence to the collating function of the left operand.

  2. If either operand is a column, then the collating function of that column is used with precedence to the left operand. For the purposes of the previous sentence, a column name preceded by one or more unary "+" operators is still considered a column name.

  3. Otherwise, the BINARY collating function is used for comparison.

The expression "x BETWEEN y and z" is logically equivalent to two comparisons "x >= y AND x <= z" and works with respect to collating functions as if it were two separate comparisons. The expression "x IN (SELECT y ...)" is handled in the same way as the expression "x = y" for the purposes of determining the collating sequence. The collating sequence used for expressions of the form "x IN (y, z, ...)" is the collating sequence of x.

Terms of the ORDER BY clause that is part of a SELECT statement may be assigned a collating sequence using the COLLATE operator, in which case the specified collating function is used for sorting. Otherwise, if the expression sorted by an ORDER BY clause is a column, then the collating sequence of the column is used to determine sort order. If the expression is not a column and has no COLLATE clause, then the BINARY collating sequence is used.

6.2 Collation Sequence Examples

The examples below identify the collating sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required, and no collating sequence used, in the case of numeric, blob or NULL values.

CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);
                   /* x   a     b     c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');

/* Text comparison a=b is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1

/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1


Distinctive Features Of SQLite

This page highlights some of the characteristics of SQLite that are unusual and which make SQLite different from many other SQL database engines.

Zero-Configuration

SQLite does not need to be "installed" before it is used. There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured. There is no need for an administrator to create a new database instance or assign access permissions to users. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot.

SQLite just works.

Other more familiar database engines run great once you get them going. But doing the initial installation and configuration can be intimidatingly complex.

Serverless

Most SQL database engines are implemented as a separate server process. Programs that want to access the database communicate with the server using some kind of interprocess communication (typically TCP/IP) to send requests to the server and to receive back results. SQLite does not work this way. With SQLite, the process that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process.

There are advantages and disadvantages to being serverless. The main advantage is that there is no separate server process to install, setup, configure, initialize, manage, and troubleshoot. This is one reason why SQLite is a "zero-configuration" database engine. Programs that use SQLite require no administrative support for setting up the database engine before they are run. Any program that is able to access the disk is able to use an SQLite database.

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 control database access with more precision, allowing for finer grain locking and better concurrency.

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

Single Database File

An SQLite database is a single ordinary disk file that can be located anywhere in the directory hierarchy. If SQLite can read the disk file then it can read anything in the database. If the disk file and its directory are writable, then SQLite can change anything in the database. Database files can easily be copied onto a USB memory stick or emailed for sharing.

Other SQL database engines tend to store data as a large collection of files. Often these files are in a standard location that only the database engine itself can access. This makes the data more secure, but also makes it harder to access. Some SQL database engines provide the option of writing directly to disk and bypassing the filesystem all together. This provides added performance, but at the cost of considerable setup and maintenance complexity.

Stable Cross-Platform Database File

The SQLite file format is cross-platform. A database file written on one machine can be copied to and used on a different machine with a different architecture. Big-endian or little-endian, 32-bit or 64-bit does not matter. All machines use the same file format. Furthermore, the developers have pledged to keep the file format stable and backwards compatible, so newer versions of SQLite can read and write older database files.

Most other SQL database engines require you to dump and restore the database when moving from one platform to another and often when upgrading to a newer version of the software.

Compact

When optimized for size, the whole SQLite library with everything enabled is less than 275KiB in size (as measured on an ix86 using the "size" utility from the GNU compiler suite.) Unneeded features can be disabled at compile-time to further reduce the size of the library to under 190KiB if desired.

Most other SQL database engines are much larger than this. IBM boasts that its recently released CloudScape database engine is "only" a 2MiB jar file - an order of magnitude larger than SQLite even after it is compressed! Firebird boasts that its client-side library is only 350KiB. That's 50% larger than SQLite and does not even contain the database engine. The Berkeley DB library from Oracle is 450KiB and it omits SQL support, providing the programmer with only simple key/value pairs.

Manifest typing

Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.)

As far as we can tell, the SQL language specification allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python.

Variable-length records

Most other SQL database engines allocated a fixed amount of disk space for each row in most tables. They play special tricks for handling BLOBs and CLOBs which can be of wildly varying length. But for most tables, if you declare a column to be a VARCHAR(100) then the database engine will allocate 100 bytes of disk space regardless of how much information you actually store in that column.

SQLite, in contrast, use only the amount of disk space actually needed to store the information in a row. If you store a single character in a VARCHAR(100) column, then only a single byte of disk space is consumed. (Actually two bytes - there is some overhead at the beginning of each column to record its datatype and length.)

The use of variable-length records by SQLite has a number of advantages. It results in smaller database files, obviously. It also makes the database run faster, since there is less information to move to and from disk. And, the use of variable-length records makes it possible for SQLite to employ manifest typing instead of static typing.

Readable source code

The source code to SQLite is designed to be readable and accessible to the average programmer. All procedures and data structures and many automatic variables are carefully commented with useful information about what they do. Boilerplate commenting is omitted.

SQL statements compile into virtual machine code

Every SQL database engine compiles each SQL statement into some kind of internal data structure which is then used to carry out the work of the statement. But in most SQL engines that internal data structure is a complex web of interlinked structures and objects. In SQLite, the compiled form of statements is a short program in a machine-language like representation. Users of the database can view this virtual machine language by prepending the EXPLAIN keyword to a query.

The use of a virtual machine in SQLite has been a great benefit to the library's development. The virtual machine provides a crisp, well-defined junction between the front-end of SQLite (the part that parses SQL statements and generates virtual machine code) and the back-end (the part that executes the virtual machine code and computes a result.) The virtual machine allows the developers to see clearly and in an easily readable form what SQLite is trying to do with each statement it compiles, which is a tremendous help in debugging. Depending on how it is compiled, SQLite also has the capability of tracing the execution of the virtual machine - printing each virtual machine instruction and its result as it executes.

Public domain

The source code for SQLite is in the public domain. No claim of copyright is made on any part of the core source code. (The documentation and test code is a different matter - some sections of documentation and test logic are governed by open-source licenses.) All contributors to the SQLite core software have signed affidavits specifically disavowing any copyright interest in the code. This means that anybody is able to legally do anything they want with the SQLite source code.

There are other SQL database engines with liberal licenses that allow the code to be broadly and freely used. But those other engines are still governed by copyright law. SQLite is different in that copyright law simply does not apply.

The source code files for other SQL database engines typically begin with a comment describing your license rights to view and copy that file. The SQLite source code contains no license since it is not governed by copyright. Instead of a license, the SQLite source code offers a blessing:

May you do good and not evil
May you find forgiveness for yourself and forgive others
May you share freely, never taking more than you give.

SQL language extensions

SQLite provides a number of enhancements to the SQL language not normally found in other database engines. The EXPLAIN keyword and manifest typing have already been mentioned above. SQLite also provides statements such as REPLACE and the ON CONFLICT clause that allow for added control over the resolution of constraint conflicts. SQLite supports ATTACH and DETACH commands that allow multiple independent databases to be used together in the same query. And SQLite defines APIs that allows the user to add new SQL functions and collating sequences.


NULL Handling in SQLite

Choose any three.
About Sitemap Documentation Download License News Support

NULL Handling in SQLite Versus Other Database Engines

The goal is to make SQLite handle NULLs in a standards-compliant way. But the descriptions in the SQL standards on how to handle NULLs seem ambiguous. It is not clear from the standards documents exactly how NULLs should be handled in all circumstances.

So instead of going by the standards documents, various popular SQL engines were tested to see how they handle NULLs. The idea was to make SQLite work like all the other engines. A SQL test script was developed and run by volunteers on various SQL RDBMSes and the results of those tests were used to deduce how each engine processed NULL values. The original tests were run in May of 2002. A copy of the test script is found at the end of this document.

SQLite was originally coded in such a way that the answer to all questions in the chart below would be "Yes". But the experiments run on other SQL engines showed that none of them worked this way. So SQLite was modified to work the same as Oracle, PostgreSQL, and DB2. This involved making NULLs indistinct for the purposes of the SELECT DISTINCT statement and for the UNION operator in a SELECT. NULLs are still distinct in a UNIQUE column. This seems somewhat arbitrary, but the desire to be compatible with other engines outweighted that objection.

It is possible to make SQLite treat NULLs as distinct for the purposes of the SELECT DISTINCT and UNION. To do so, one should change the value of the NULL_ALWAYS_DISTINCT #define in the sqliteInt.h source file and recompile.

Update 2003-07-13: Since this document was originally written some of the database engines tested have been updated and users have been kind enough to send in corrections to the chart below. The original data showed a wide variety of behaviors, but over time the range of behaviors has converged toward the PostgreSQL/Oracle model. The only significant difference is that Informix and MS-SQL both treat NULLs as indistinct in a UNIQUE column.

The fact that NULLs are distinct for UNIQUE columns but are indistinct for SELECT DISTINCT and UNION continues to be puzzling. It seems that NULLs should be either distinct everywhere or nowhere. And the SQL standards documents suggest that NULLs should be distinct everywhere. Yet as of this writing, no SQL engine tested treats NULLs as distinct in a SELECT DISTINCT statement or in a UNION.

The following table shows the results of the NULL handling experiments.

 | SQLite | PostgreSQL | Oracle | Informix | DB2 | MS-SQL | OCELOT
Adding anything to null gives null | Yes | Yes | Yes | Yes | Yes | Yes | Yes
Multiplying null by zero gives null | Yes | Yes | Yes | Yes | Yes | Yes | Yes
nulls are distinct in a UNIQUE column | Yes | Yes | Yes | No | (Note 4) | No | Yes
nulls are distinct in SELECT DISTINCT | No | No | No | No | No | No | No
nulls are distinct in a UNION | No | No | No | No | No | No | No
"CASE WHEN null THEN 1 ELSE 0 END" is 0? | Yes | Yes | Yes | Yes | Yes | Yes | Yes
"null OR true" is true | Yes | Yes | Yes | Yes | Yes | Yes | Yes
"not (null AND false)" is true | Yes | Yes | Yes | Yes | Yes | Yes | Yes
 | MySQL 3.23.41 | MySQL 4.0.16 | Firebird | SQL Anywhere | Borland Interbase
Adding anything to null gives null | Yes | Yes | Yes | Yes | Yes
Multiplying null by zero gives null | Yes | Yes | Yes | Yes | Yes
nulls are distinct in a UNIQUE column | Yes | Yes | Yes | (Note 4) | (Note 4)
nulls are distinct in SELECT DISTINCT | No | No | No (Note 1) | No | No
nulls are distinct in a UNION | (Note 3) | No | No (Note 1) | No | No
"CASE WHEN null THEN 1 ELSE 0 END" is 0? | Yes | Yes | Yes | Yes | (Note 5)
"null OR true" is true | Yes | Yes | Yes | Yes | Yes
"not (null AND false)" is true | No | Yes | Yes | Yes | Yes
Notes: | 1. | Older versions of firebird omits all NULLs from SELECT DISTINCT and from UNION.
 | 2. | Test data unavailable.
 | 3. | MySQL version 3.23.41 does not support UNION.
 | 4. | DB2, SQL Anywhere, and Borland Interbase do not allow NULLs in a UNIQUE column.
 | 5. | Borland Interbase does not support CASE expressions.

The following script was used to gather information for the table above.

-- I have about decided that SQL's treatment of NULLs is capricious and cannot be
-- deduced by logic.  It must be discovered by experiment.  To that end, I have
-- prepared the following script to test how various SQL databases deal with NULL.
-- My aim is to use the information gather from this script to make SQLite as much
-- like other databases as possible.
--
-- If you could please run this script in your database engine and mail the results
-- to me at drh@hwaci.com, that will be a big help.  Please be sure to identify the
-- database engine you use for this test.  Thanks.
--
-- If you have to change anything to get this script to run with your database
-- engine, please send your revised script together with your results.
--

-- Create a test table with data
create table t1(a int, b int, c int);
insert into t1 values(1,0,0);
insert into t1 values(2,0,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);
insert into t1 values(7,null,null);

-- Check to see what CASE does with NULLs in its test expressions
select a, case when b<>0 then 1 else 0 end from t1;
select a+10, case when not b<>0 then 1 else 0 end from t1;
select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;
select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;
select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
select a+60, case b when c then 1 else 0 end from t1;
select a+70, case c when b then 1 else 0 end from t1;

-- What happens when you multiple a NULL by zero?
select a+80, b*0 from t1;
select a+90, b*c from t1;

-- What happens to NULL for other operators?
select a+100, b+c from t1;

-- Test the treatment of aggregate operators
select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1;

-- Check the behavior of NULLs in WHERE clauses
select a+110 from t1 where b<10;
select a+120 from t1 where not b>10;
select a+130 from t1 where b<10 OR c=1;
select a+140 from t1 where b<10 AND c=1;
select a+150 from t1 where not (b<10 AND c=1);
select a+160 from t1 where not (c=1 AND b<10);

-- Check the behavior of NULLs in a DISTINCT query
select distinct b from t1;

-- Check the behavior of NULLs in a UNION query
select b from t1 union select b from t1;

-- Create a new table with a unique column.  Check to see if NULLs are considered
-- to be distinct.
create table t2(a int, b int unique);
insert into t2 values(1,1);
insert into t2 values(2,null);
insert into t2 values(3,null);
select * from t2;

drop table t1;
drop table t2;


File Locking And Concurrency In SQLite Version 3

Choose any three.
About Sitemap Documentation Download License News Support

1.0 File Locking And Concurrency In SQLite Version 3

Version 3 of SQLite introduces a more complex locking and journaling mechanism designed to improve concurrency and reduce the writer starvation problem. The new mechanism also allows atomic commits of transactions involving multiple database files. This document describes the new locking mechanism. The intended audience is programmers who want to understand and/or modify the pager code and reviewers working to verify the design of SQLite version 3.

2.0 Overview

Locking and concurrency control are handled by the the pager module. The pager module is responsible for making SQLite "ACID" (Atomic, Consistent, Isolated, and Durable). The pager module makes sure changes happen all at once, that either all changes occur or none of them do, that two or more processes do not try to access the database in incompatible ways at the same time, and that once changes have been written they persist until explicitly deleted. The pager also provides a memory cache of some of the contents of the disk file.

The pager is unconcerned with the details of B-Trees, text encodings, indices, and so forth. From the point of view of the pager the database consists of a single file of uniform-sized blocks. Each block is called a "page" and is usually 1024 bytes in size. The pages are numbered beginning with 1. So the first 1024 bytes of the database are called "page 1" and the second 1024 bytes are call "page 2" and so forth. All other encoding details are handled by higher layers of the library. The pager communicates with the operating system using one of several modules (Examples: os_unix.c, os_win.c) that provides a uniform abstraction for operating system services.

The pager module effectively controls access for separate threads, or separate processes, or both. Throughout this document whenever the word "process" is written you may substitute the word "thread" without changing the truth of the statement.

3.0 Locking

From the point of view of a single process, a database file can be in one of five locking states:

UNLOCKED | No locks are held on the database. The database may be neither read nor written. Any internally cached data is considered suspect and subject to verification against the database file before being used. Other processes can read or write the database as their own locking states permit. This is the default state.
SHARED | The database may be read but not written. Any number of processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active.
RESERVED | A RESERVED lock means that the process is planning on writing to the database file at some point in the future but that it is currently just reading from the file. Only a single RESERVED lock may be active at one time, though multiple SHARED locks can coexist with a single RESERVED lock. RESERVED differs from PENDING in that new SHARED locks can be acquired while there is a RESERVED lock.
PENDING | A PENDING lock means that the process holding the lock wants to write to the database as soon as possible and is just waiting on all current SHARED locks to clear so that it can get an EXCLUSIVE lock. No new SHARED locks are permitted against the database if a PENDING lock is active, though existing SHARED locks are allowed to continue.
EXCLUSIVE | An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held.

The operating system interface layer understands and tracks all five locking states described above. The pager module only tracks four of the five locking states. A PENDING lock is always just a temporary stepping stone on the path to an EXCLUSIVE lock and so the pager module does not track PENDING locks.

4.0 The Rollback Journal

Any time a process wants to make a changes to a database file, it first records enough information in the rollback journal to restore the database file back to its initial condition. Thus, before altering any page of the database, the original contents of that page must be written into the journal. The journal also records the initial size of the database so that if the database file grows it can be truncated back to its original size on a rollback.

The rollback journal is a ordinary disk file that has the same name as the database file with the suffix "-journal" added.

If SQLite is working with multiple databases at the same time (using the ATTACH command) then each database has its own journal. But there is also a separate aggregate journal called the master journal. The master journal does not contain page data used for rolling back changes. Instead the master journal contains the names of the individual file journals for each of the ATTACHed databases. Each of the individual file journals also contain the name of the master journal. If there are no ATTACHed databases (or if none of the ATTACHed database is participating in the current transaction) no master journal is created and the normal rollback journal contains an empty string in the place normally reserved for recording the name of the master journal.

A individual rollback journal is said to be hot if it needs to be rolled back in order to restore the integrity of its database. A hot journal is created when a process is in the middle of a database update and a program or operating system crash or power failure prevents the update from completing. Hot journals are an exception condition. Hot journals exist to recover from crashes and power failures. If everything is working correctly (that is, if there are no crashes or power failures) you will never get a hot journal.

If no master journal is involved, then a journal is hot if it exists and its corresponding database file does not have a RESERVED lock. If a master journal is named in the file journal, then the file journal is hot if its master journal exists and there is no RESERVED lock on the corresponding database file. It is important to understand when a journal is hot so the preceding rules will be repeated in bullets:

  • A journal is hot if...
    • It exists, and
    • Its master journal exists or the master journal name is an empty string, and
    • There is no RESERVED lock on the corresponding database file.

4.1 Dealing with hot journals

Before reading from a a database file, SQLite always checks to see if that database file has a hot journal. If the file does have a hot journal, then the journal is rolled back before the file is read. In this way, we ensure that the database file is in a consistent state before it is read.

When a process wants to read from a database file, it followed the following sequence of steps:

  1. Open the database file and obtain a SHARED lock. If the SHARED lock cannot be obtained, fail immediately and return SQLITE_BUSY.
  2. Check to see if the database file has a hot journal. If the file does not have a hot journal, we are done. Return immediately. If there is a hot journal, that journal must be rolled back by the subsequent steps of this algorithm.
  3. Acquire a PENDING lock then an EXCLUSIVE lock on the database file. (Note: Do not acquire a RESERVED lock because that would make other processes think the journal was no longer hot.) If we fail to acquire these locks it means another process is already trying to do the rollback. In that case, drop all locks, close the database, and return SQLITE_BUSY.
  4. Read the journal file and roll back the changes.
  5. Wait for the rolled back changes to be written onto the surface of the disk. This protects the integrity of the database in case another power failure or crash occurs.
  6. Delete the journal file.
  7. Delete the master journal file if it is safe to do so. This step is optional. It is here only to prevent stale master journals from cluttering up the disk drive. See the discussion below for details.
  8. Drop the EXCLUSIVE and PENDING locks but retain the SHARED lock.

After the algorithm above completes successfully, it is safe to read from the database file. Once all reading has completed, the SHARED lock is dropped.

4.2 Deleting stale master journals

A stale master journal is a master journal that is no longer being used for anything. There is no requirement that stale master journals be deleted. The only reason for doing so is to free up disk space.

A master journal is stale if no individual file journals are pointing to it. To figure out if a master journal is stale, we first read the master journal to obtain the names of all of its file journals. Then we check each of those file journals. If any of the file journals named in the master journal exists and points back to the master journal, then the master journal is not stale. If all file journals are either missing or refer to other master journals or no master journal at all, then the master journal we are testing is stale and can be safely deleted.

5.0 Writing to a database file

To write to a database, a process must first acquire a SHARED lock as described above (possibly rolling back incomplete changes if there is a hot journal). After a SHARED lock is obtained, a RESERVED lock must be acquired. The RESERVED lock signals that the process intends to write to the database at some point in the future. Only one process at a time can hold a RESERVED lock. But other processes can continue to read the database while the RESERVED lock is held.

If the process that wants to write is unable to obtain a RESERVED lock, it must mean that another process already has a RESERVED lock. In that case, the write attempt fails and returns SQLITE_BUSY.

After obtaining a RESERVED lock, the process that wants to write creates a rollback journal. The header of the journal is initialized with the original size of the database file. Space in the journal header is also reserved for a master journal name, though the master journal name is initially empty.

Before making changes to any page of the database, the process writes the original content of that page into the rollback journal. Changes to pages are held in memory at first and are not written to the disk. The original database file remains unaltered, which means that other processes can continue to read the database.

Eventually, the writing process will want to update the database file, either because its memory cache has filled up or because it is ready to commit its changes. Before this happens, the writer must make sure no other process is reading the database and that the rollback journal data is safely on the disk surface so that it can be used to rollback incomplete changes in the event of a power failure. The steps are as follows:

  1. Make sure all rollback journal data has actually been written to the surface of the disk (and is not just being held in the operating system's or disk controllers cache) so that if a power failure occurs the data will still be there after power is restored.
  2. Obtain a PENDING lock and then an EXCLUSIVE lock on the database file. If other processes still have SHARED locks, the writer might have to wait until those SHARED locks clear before it is able to obtain an EXCLUSIVE lock.
  3. Write all page modifications currently held in memory out to the original database disk file.

If the reason for writing to the database file is because the memory cache was full, then the writer will not commit right away. Instead, the writer might continue to make changes to other pages. Before subsequent changes are written to the database file, the rollback journal must be flushed to disk again. Note also that the EXCLUSIVE lock that the writer obtained in order to write to the database initially must be held until all changes are committed. That means that no other processes are able to access the database from the time the memory cache first spills to disk until the transaction commits.

When a writer is ready to commit its changes, it executes the following steps:

  4. Obtain an EXCLUSIVE lock on the database file and make sure all memory changes have been written to the database file using the algorithm of steps 1-3 above.
  5. Flush all database file changes to the disk. Wait for those changes to actually be written onto the disk surface.
  6. Delete the journal file. This is the instant when the changes are committed. Prior to deleting the journal file, if a power failure or crash occurs, the next process to open the database will see that it has a hot journal and will roll the changes back. After the journal is deleted, there will no longer be a hot journal and the changes will persist.
  7. Drop the EXCLUSIVE and PENDING locks from the database file.

As soon as PENDING lock is released from the database file, other processes can begin reading the database again. In the current implementation, the RESERVED lock is also released, but that is not essential. Future versions of SQLite might provide a "CHECKPOINT" SQL command that will commit all changes made so far within a transaction but retain the RESERVED lock so that additional changes can be made without given any other process an opportunity to write.

If a transaction involves multiple databases, then a more complex commit sequence is used, as follows:

  4. Make sure all individual database files have an EXCLUSIVE lock and a valid journal.
  5. Create a master-journal. The name of the master-journal is arbitrary. (The current implementation appends random suffixes to the name of the main database file until it finds a name that does not previously exist.) Fill the master journal with the names of all the individual journals and flush its contents to disk.
  6. Write the name of the master journal into all individual journals (in space set aside for that purpose in the headers of the individual journals) and flush the contents of the individual journals to disk and wait for those changes to reach the disk surface.
  7. Flush all database file changes to the disk. Wait for those changes to actually be written onto the disk surface.
  8. Delete the master journal file. This is the instant when the changes are committed. Prior to deleting the master journal file, if a power failure or crash occurs, the individual file journals will be considered hot and will be rolled back by the next process that attempts to read them. After the master journal has been deleted, the file journals will no longer be considered hot and the changes will persist.
  9. Delete all individual journal files.
  10. Drop the EXCLUSIVE and PENDING locks from all database files.

5.1 Writer starvation

In SQLite version 2, if many processes are reading from the database, it might be the case that there is never a time when there are no active readers. And if there is always at least one read lock on the database, no process would ever be able to make changes to the database because it would be impossible to acquire a write lock. This situation is called writer starvation.

SQLite version 3 seeks to avoid writer starvation through the use of the PENDING lock. The PENDING lock allows existing readers to continue but prevents new readers from connecting to the database. So when a process wants to write a busy database, it can set a PENDING lock which will prevent new readers from coming in. Assuming existing readers do eventually complete, all SHARED locks will eventually clear and the writer will be given a chance to make its changes.

6.0 How To Corrupt Your Database Files

The pager module is robust but it is not completely failsafe. It can be subverted. This section attempts to identify and explain the risks. (See also the Things That Can Go Wrong section of the article on Atomic Commit.

Clearly, a hardware or operating system fault that introduces incorrect data into the middle of the database file or journal will cause problems. Likewise, if a rogue process opens a database file or journal and writes malformed data into the middle of it, then the database will become corrupt. There is not much that can be done about these kinds of problems so they are given no further attention.

SQLite uses POSIX advisory locks to implement locking on Unix. On Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite assumes that these system calls all work as advertised. If that is not the case, then database corruption can result. One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under Windows. Your best defense is to not use SQLite for files on a network filesystem.

SQLite uses the fsync() system call to flush data to the disk under Unix and it uses the FlushFileBuffers() to do the same under Windows. Once again, SQLite assumes that these operating system services function as advertised. But it has been reported that fsync() and FlushFileBuffers() do not always work correctly, especially with inexpensive IDE disks. Apparently some manufactures of IDE disks have defective controller chips that report that data has reached the disk surface when in fact the data is still in volatile cache memory in the disk drive electronics. There are also reports that Windows sometimes chooses to ignore FlushFileBuffers() for unspecified reasons. The author cannot verify any of these reports. But if they are true, it means that database corruption is a possibility following an unexpected power loss. These are hardware and/or operating system bugs that SQLite is unable to defend against.

If a crash or power failure occurs and results in a hot journal but that journal is deleted, the next process to open the database will not know that it contains changes that need to be rolled back. The rollback will not occur and the database will be left in an inconsistent state. Rollback journals might be deleted for any number of reasons:

  • An administrator might be cleaning up after an OS crash or power failure, see the journal file, think it is junk, and delete it.
  • Someone (or some process) might rename the database file but fail to also rename its associated journal.
  • If the database file has aliases (hard or soft links) and the file is opened by a different alias than the one used to create the journal, then the journal will not be found. To avoid this problem, you should not create links to SQLite database files.
  • Filesystem corruption following a power failure might cause the journal to be renamed or deleted.

The last (fourth) bullet above merits additional comment. When SQLite creates a journal file on Unix, it opens the directory that contains that file and calls fsync() on the directory, in an effort to push the directory information to disk. But suppose some other process is adding or removing unrelated files to the directory that contains the database and journal at the the moment of a power failure. The supposedly unrelated actions of this other process might result in the journal file being dropped from the directory and moved into "lost+found". This is an unlikely scenario, but it could happen. The best defenses are to use a journaling filesystem or to keep the database and journal in a directory by themselves.

For a commit involving multiple databases and a master journal, if the various databases were on different disk volumes and a power failure occurs during the commit, then when the machine comes back up the disks might be remounted with different names. Or some disks might not be mounted at all. When this happens the individual file journals and the master journal might not be able to find each other. The worst outcome from this scenario is that the commit ceases to be atomic. Some databases might be rolled back and others might not. All databases will continue to be self-consistent. To defend against this problem, keep all databases on the same disk volume and/or remount disks using exactly the same names after a power failure.

7.0 Transaction Control At The SQL Level

The changes to locking and concurrency control in SQLite version 3 also introduce some subtle changes in the way transactions work at the SQL language level. By default, SQLite version 3 operates in autocommit mode. In autocommit mode, all changes to the database are committed as soon as all operations associated with the current database connection complete.

The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is optional) is used to take SQLite out of autocommit mode. Note that the BEGIN command does not acquire any locks on the database. After a BEGIN command, a SHARED lock will be acquired when the first SELECT statement is executed. A RESERVED lock will be acquired when the first INSERT, UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired until either the memory cache fills up and must be spilled to disk or until the transaction commits. In this way, the system delays blocking read access to the file file until the last possible moment.

The SQL command "COMMIT" does not actually commit the changes to disk. It just turns autocommit back on. Then, at the conclusion of the command, the regular autocommit logic takes over and causes the actual commit to disk to occur. The SQL command "ROLLBACK" also operates by turning autocommit back on, but it also sets a flag that tells the autocommit logic to rollback rather than commit.

If the SQL COMMIT command turns autocommit on and the autocommit logic then tries to commit change but fails because some other process is holding a SHARED lock, then autocommit is turned back off automatically. This allows the user to retry the COMMIT at a later time after the SHARED lock has had an opportunity to clear.

If multiple commands are being executed against the same SQLite database connection at the same time, the autocommit is deferred until the very last command completes. For example, if a SELECT statement is being executed, the execution of the command will pause as each row of the result is returned. During this pause other INSERT, UPDATE, or DELETE commands can be executed against other tables in the database. But none of these changes will commit until the original SELECT statement finishes.


SQL Features That SQLite Does Not Implement

Rather than try to list all the features of SQL92 that SQLite does support, it is much easier to list those that it does not. Unsupported features of SQL92 are shown below.

The order of this list gives some hint as to when a feature might be added to SQLite. Those features near the top of the list are likely to be added in the near future. There are no immediate plans to add features near the bottom of the list.

RIGHT and FULL OUTER JOIN | LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.
Complete ALTER TABLE support | Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
Complete trigger support | FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
Writing to VIEWs | VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.
GRANT and REVOKE | Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine.

If you find other SQL92 features that SQLite does not support, please add them to the Wiki page at http://www.sqlite.org/cvstrac/wiki?p=Unsupported


SQLite Shared-Cache Mode

Choose any three.
About Sitemap Documentation Download License News Support

1.0 SQLite Shared-Cache Mode

Starting with version 3.3.0, SQLite includes a special "shared-cache" mode (disabled by default) intended for use in embedded servers. If shared-cache mode is enabled and a thread establishes multiple connections to the same database, the connections share a single data and schema cache. This can significantly reduce the quantity of memory and IO required by the system.

In version 3.5.0, shared-cache mode was modified so that the same cache can be shared across an entire process rather than just within a single thread. Prior to this change, there were restrictions on passing database connections between threads. Those restrictions were dropped in 3.5.0 update. This document describes shared-cache mode as of version 3.5.0.

Shared-cache mode changes the semantics of the locking model in some cases. The details are described by this document. A basic understanding of the normal SQLite locking model (see File Locking And Concurrency In SQLite Version 3 for details) is assumed.

2.0 Shared-Cache Locking Model

Externally, from the point of view of another process or thread, two or more database connections using a shared-cache appear as a single connection. The locking protocol used to arbitrate between multiple shared-caches or regular database users is described elsewhere.

Figure 1

Figure 1 depicts an example runtime configuration where three database connections have been established. Connection 1 is a normal SQLite database connection. Connections 2 and 3 share a cache The normal locking protocol is used to serialize database access between connection 1 and the shared cache. The internal protocol used to serialize (or not, see "Read-Uncommitted Isolation Mode" below) access to the shared-cache by connections 2 and 3 is described in the remainder of this section.

There are three levels to the shared-cache locking model, transaction level locking, table level locking and schema level locking. They are described in the following three sub-sections.

2.1 Transaction Level Locking

SQLite connections can open two kinds of transactions, read and write transactions. This is not done explicitly, a transaction is implicitly a read-transaction until it first writes to a database table, at which point it becomes a write-transaction.

At most one connection to a single shared cache may open a write transaction at any one time. This may co-exist with any number of read transactions.

2.2 Table Level Locking

When two or more connections use a shared-cache, locks are used to serialize concurrent access attempts on a per-table basis. Tables support two types of locks, "read-locks" and "write-locks". Locks are granted to connections - at any one time, each database connection has either a read-lock, write-lock or no lock on each database table.

At any one time, a single table may have any number of active read-locks or a single active write lock. To read data a table, a connection must first obtain a read-lock. To write to a table, a connection must obtain a write-lock on that table. If a required table lock cannot be obtained, the query fails and SQLITE_LOCKED is returned to the caller.

Once a connection obtains a table lock, it is not released until the current transaction (read or write) is concluded.

2.2.1 Read-Uncommitted Isolation Mode

The behaviour described above may be modified slightly by using the read_uncommitted pragma to change the isolation level from serialized (the default), to read-uncommitted.

A database connection in read-uncommitted mode does not attempt to obtain read-locks before reading from database tables as described above. This can lead to inconsistent query results if another database connection modifies a table while it is being read, but it also means that a read-transaction opened by a connection in read-uncommitted mode can neither block nor be blocked by any other connection.

Read-uncommitted mode has no effect on the locks required to write to database tables (i.e. read-uncommitted connections must still obtain write-locks and hence database writes may still block or be blocked). Also, read-uncommitted mode has no effect on the sqlite_master locks required by the rules enumerated below (see section "Schema (sqlite_master) Level Locking").

  /* Set the value of the read-uncommitted flag:
  **
  **   True  -> Set the connection to read-uncommitted mode.
  **   False -> Set the connectino to serialized (the default) mode.
  */
  PRAGMA read_uncommitted = <boolean>;

  /* Retrieve the current value of the read-uncommitted flag */
  PRAGMA read_uncommitted;

2.3 Schema (sqlite_master) Level Locking

The sqlite_master table supports shared-cache read and write locks in the same way as all other database tables (see description above). The following special rules also apply:

  • A connection must obtain a read-lock on sqlite_master before accessing any database tables or obtaining any other read or write locks.
  • Before executing a statement that modifies the database schema (i.e. a CREATE or DROP TABLE statement), a connection must obtain a write-lock on sqlite_master.
  • A connection may not compile an SQL statement if any other connection is holding a write-lock on the sqlite_master table of any attached database (including the default database, "main").

3.0 Thread Related Issues

In SQLite versions 3.3.0 through 3.4.2 when shared-cache mode is enabled, a database connection may only be used by the thread that called sqlite3_open() to create it. And a connection could only share cache with another connection in the same thread. These restrictions were dropped beginning with SQLite version 3.5.0.

4.0 Shared Cache And Virtual Tables

In older versions of SQLite, shared cache mode could not be used together with virtual tables. This restriction was removed in SQLite version 3.6.17.

5.0 Enabling Shared-Cache Mode

Shared-cache mode is enabled on a per-process basis. Using the C interface, the following API can be used to enable or disable shared-cache mode for the calling thread:

int sqlite3_enable_shared_cache(int);

Each call sqlite3_enable_shared_cache() effects subsequent database connections created using sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2(). Database connections that already exist are uneffected. Each call to sqlite3_enable_shared_cache() overrides all previous calls within the same process.

Individual database connections created using sqlite3_open_v2() can choose to participate or not participate in shared cache mode by using the SQLITE_OPEN_SHAREDCACHE or SQLITE_OPEN_PRIVATECACHE flags the third parameter. The use of either of these flags overrides the global shared cache mode setting established by sqlite3_enable_shared_cache(). No more than one of the flags should be used; if both SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_PRIVATECACHE flags are used in the third argument to sqlite3_open_v2() then the behavior is undefined.


The SQLite Query Optimizer Overview

Choose any three.
About Sitemap Documentation Download License News Support

The SQLite Query Planner

This document provides overview of how the query planner and optimizer for SQLite works.

Given a single SQL statement, there might be dozens, hundreds, or even thousands of ways to implement that statement, depending on the complexity of the statement itself and of the underlying database schema. The task of the query planner is to select an algorithm from among the many choices that provides the answer with a minimum of disk I/O and CPU overhead.

1.0 WHERE clause analysis

The WHERE clause on a query is broken up into "terms" where each term is separated from the others by an AND operator. If the WHERE clause is composed of constraints separate by the OR operator then the entire clause is considered to be a single "term" to which the OR-clause optimization is applied.

All terms of the WHERE clause are analyzed to see if they can be satisfied using indices. Terms that cannot be satisfied through the use of indices become tests that are evaluated against each row of the relevant input tables. No tests are done for terms that are completely satisfied by indices. Sometimes one or more terms will provide hints to indices but still must be evaluated against each row of the input tables.

The analysis of a term might cause new "virtual" terms to be added to the WHERE clause. Virtual terms can be used with indices to restrict a search. But virtual terms never generate code that is tested against input rows.

To be usable by an index a term must be of one of the following forms:

  column = expression
  column > expression
  column >= expression
  column < expression
  column <= expression
  expression = column
  expression > column
  expression >= column
  expression < column
  expression <= column
  column IN (expression-list)
  column IN (subquery)
  column IS NULL

If an index is created using a statement like this:

  CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);

Then the index might be used if the initial columns of the index (columns a, b, and so forth) appear in WHERE clause terms. The initial columns of the index must be used with the = or IN operators. The right-most column that is used can employ inequalities. For the right-most column of an index that is used, there can be up to two inequalities that must sandwich the allowed values of the column between two extremes.

It is not necessary for every column of an index to appear in a WHERE clause term in order for that index to be used. But there can not be gaps in the columns of the index that are used. Thus for the example index above, if there is no WHERE clause term that constraints column c, then terms that constrain columns a and b can be used with the index but not terms that constraint columns d through z. Similarly, no index column will be used (for indexing purposes) that is to the right of a column that is constrained only by inequalities.

1.1 Index term usage examples

For the index above and WHERE clause like this:

  ... WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'

The first four columns a, b, c, and d of the index would be usable since those four columns form a prefix of the index and are all bound by equality constraints.

For the index above and WHERE clause like this:

  ... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'

Only columns a, b, and c of the index would be usable. The d column would not be usable because it occurs to the right of c and c is constrained only by inequalities.

For the index above and WHERE clause like this:

  ... WHERE a=5 AND b IN (1,2,3) AND d='hello'

Only columns a and b of the index would be usable. The d column would not be usable because column c is not constrained and there can be no gaps in the set of columns that usable by the index.

For the index above and WHERE clause like this:

  ... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'

The index is not usable at all becaues the left-most column of the index (column "a") is not constrained.) Assuming there are no other indices, the query above would result in a full table scan.

For the index above and WHERE clause like this:

  ... WHERE a=5 OR b IN (1,2,3) OR c NOT NULL OR d='hello'

The index is not usable because the WHERE clause terms are connected by OR instead of AND. This query would result in a full table scan. However, if three additional indices where added that contained columns b, c, and d as their left-most columns, then the OR-clause optimization might apply.

2.0 The BETWEEN optimization

If a term of the WHERE clause is of the following form:

  expr1 BETWEEN expr2 AND expr3

Then two virtual terms are added as follows:

  expr1 >= expr2 AND expr1 <= expr3

If both virtual terms end up being used as constraints on an index, then the original BETWEEN term is omitted and the corresponding test is not performed on input rows. Thus if the BETWEEN term ends up being used as an index constraint no tests are ever performed on that term. On the other hand, the virtual terms themselves never causes tests to be performed on input rows. Thus if the BETWEEN term is not used as an index constraint and instead must be used to test input rows, the expr1 expression is only evaluated once.

3.0 OR optimizations

WHERE clause constraints that are connected by OR instead of AND are handled in one of two way. If a term consists of multiple subterms containing a common column name and separated by OR, like this:

  column = expr1 OR column = expr2 OR column = expr3 OR ...

Then that term is rewritten as follows:

  column IN (expr1,expr2,expr3,expr4,...)

The rewritten term then might go on to constrain an index using the normal rules for IN operators. Note that column must be the same column in every OR-connected subterm, although the column can occur on either the left or the right side of the = operator.

If and only if the previously described conversion of OR to an IN operator does not work, the second OR-clause optimization is attempted. Suppose the OR clause consists of multiple subterms as follows:

  expr1 OR expr2 OR expr3

Individual subterms might be a single comparison expression like a=5 or x>y or they can be LIKE or BETWEEN expressions, or a subterm can be a parenthesized list of AND-connected sub-subterms. Each subterm is analyzed as if it were itself the entire WHERE clause in order to see if the subterm is indexable by itself. If every subterm of an OR clause is separately indexable then the OR clause might be coded such that a separate index is used to evaluate each term of the OR clause. One way to think about how SQLite uses separate indices foreach each OR clause term is to imagine that the WHERE clause where rewritten as follows:

  rowid IN (SELECT rowid FROM table WHERE expr1
            UNION SELECT rowid FROM table WHERE expr2
            UNION SELECT rowid FROM table WHERE expr3)

The rewritten expression above is conceptual; WHERE clauses containing OR are not really rewritten this way. The actual implemention of the OR clause uses a mechanism that is more efficient than subqueries and which works even even for tables where the "rowid" column name has been overloaded for other uses and no longer refers to the real rowid. But the essence of the implementation is captured by the statement above: Separate indices are used to find candidate result rows from each OR clause term and the final result is the union of those rows.

Note that in most cases, SQLite will only use a single index for each table in the FROM clause of a query. The second OR-clause optimization described here is the exception to that rule. With an OR-clause, a different index might be used for each subterm in the OR-clause.

For any given query, the fact that the OR-clause optimization described here can be used does not guarantee that it will be used. SQLite uses a cost-based query planner that estimates the CPU and disk I/O costs of various competing query plans and chooses the plan that it thinks will be the fastest. If there are many OR terms in the WHERE clause or if some of the indices on individual OR-clause subterms are not very selective, then SQLite might decide that it is faster to use a different query algorithm, or even a full-table scan. Application developers can use the EXPLAIN QUERY PLAN prefix on a statement to get a high-level overview of the chosen query strategy.

4.0 The LIKE optimization

Terms that are composed of the LIKE or GLOB operator can sometimes be used to constrain indices. There are many conditions on this use:

  1. The left-hand side of the LIKE or GLOB operator must be the name of an indexed column with TEXT affinity.
  2. The right-hand side of the LIKE or GLOB must be either a string literal or a parameter bound to a string literal that does not begin with a wildcard character.
  3. The ESCAPE clause cannot appear on the LIKE operator.
  4. The build-in functions used to implement LIKE and GLOB must not have been overloaded using the sqlite3_create_function() API.
  5. For the GLOB operator, the column must use the default BINARY collating sequence.
  6. For the LIKE operator, if case_sensitive_like mode is enabled then the column must use the default BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must use the built-in NOCASE collating sequence.

The LIKE operator has two modes that can be set by a pragma. The default mode is for LIKE comparisons to be insensitive to differences of case for latin1 characters. Thus, by default, the following expression is true:

  'a' LIKE 'A'

But if the case_sensitive_like pragma is enabled as follows:

  PRAGMA case_sensitive_like=ON;

Then the LIKE operator pays attention to case and the example above would evaluate to false. Note that case insensitivity only applies to latin1 characters - basically the upper and lower case letters of English in the lower 127 byte codes of ASCII. International character sets are case sensitive in SQLite unless a application-defined collating sequence and like() SQL function are provided that take non-ASCII characters into account. But if an application-defined collating sequence and/or like() SQL function are provided, the LIKE optimization described here will never be taken.

The LIKE operator is case insensitive by default because this is what the SQL standard requires. You can change the default behavior at compile time by using the SQLITE_CASE_SENSITIVE_LIKE command-line option to the compiler.

The LIKE optimization might occur if the column named on the left of the operator uses the BINARY collating sequence (which is the default) and case_sensitive_like is turned on. Or the optimization might occur if the column uses the built-in NOCASE collating sequence and the case_sensitive_like mode is off. These are the only two combinations under which LIKE operators will be optimized. If the column on the right-hand side of the LIKE operator uses any collating sequence other than the built-in BINARY and NOCASE collating sequences, then no optimizations will ever be attempted on the LIKE operator.

The GLOB operator is always case sensitive. The column on the left side of the GLOB operator must always use the built-in BINARY collating sequence or no attempt will be made to optimize that operator with indices.

The LIKE optimization will only be attempted if the right-hand side of the GLOB or LIKE operator is either literal string or a parameter that has been bound to a string literal. The string literal must not begin with a wildcard; if the right-hand side begins with a wildcard character then this optimization is attempted. If the right-hand side is a parameter that is bound to a string, then this optimization is only attempted if the prepared statement containing the expression was compiled with sqlite3_prepare_v2() or sqlite3_prepare16_v2(). The LIKE optimization is not attempted if the right-hand side is a parameter and the statement was prepared using sqlite3_prepare() or sqlite3_prepare16(). The LIKE optimization is not attempted if there is an EXCEPT phrase on the LIKE operator.

Suppose the initial sequence of non-wildcard characters on the right-hand side of the LIKE or GLOB operator is x. We are using a single character to denote this non-wildcard prefix but the reader should understand that the prefix can consist of more than 1 character. Let y be the smallest string that is the same length as /x/ but which compares greater than x. For example, if x is hello then y would be hellp. The LIKE and GLOB optimizations consist of adding two virtual terms like this:

  column >= x AND column < y

Under most circumstances, the original LIKE or GLOB operator is still tested against each input row even if the virtual terms are used to constrain an index. This is because we do not know what additional constraints may be imposed by characters to the right of the x prefix. However, if there is only a single global wildcard to the right of x, then the original LIKE or GLOB test is disabled. In other words, if the pattern is like this:

  column LIKE x%
  column GLOB x*

then the original LIKE or GLOB tests are disabled when the virtual terms constrain an index because in that case we know that all of the rows selected by the index will pass the LIKE or GLOB test.

Note that when the right-hand side of a LIKE or GLOB operator is a parameter and the statement is prepared using sqlite3_prepare_v2() or sqlite3_prepare16_v2() then the statement is automatically reparsed and recompiled on the first sqlite3_step() call of each run if the binding to the right-hand side parameter has changed since the previous run. This reparse and recompile is essentially the same action that occurs following a schema change. The recompile is necessary so that the query planner can examine the new value bound to the right-hand side of the LIKE or GLOB operator and determine whether or not to employ the optimization described above.

5.0 Joins

The ON and USING clauses of a inner join are converted into additional terms of the WHERE clause prior to WHERE clause analysis described above in paragraph 1.0. Thus with SQLite, there is no computational advantage to use the newer SQL92 join syntax over the older SQL89 comma-join syntax. They both end up accomplishing exactly the same thing on inner joins.

For a LEFT OUTER JOIN the situation is more complex. The following two queries are not equivalent:

  SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.y;
  SELECT * FROM tab1 LEFT JOIN tab2 WHERE tab1.x=tab2.y;

For an inner join, the two queries above would be identical. But special processing applies to the ON and USING clauses of an OUTER join: specifically, the constraints in an ON or USING clause do not apply if the right table of the join is on a null row, but the constraints do apply in the WHERE clause. The net effect is that putting the ON or USING clause expressions for a LEFT JOIN in the WHERE clause effectively converts the query to an ordinary INNER JOIN - albeit an inner join that runs more slowly.

5.1 Order of tables in a join

The current implementation of SQLite uses only loop joins. That is to say, joins are implemented as nested loops.

The default order of the nested loops in a join is for the left-most table in the FROM clause to form the outer loop and the right-most table to form the inner loop. However, SQLite will nest the loops in a different order if doing so will help it to select better indices.

Inner joins can be freely reordered. However a left outer join is neither commutative nor associative and hence will not be reordered. Inner joins to the left and right of the outer join might be reordered if the optimizer thinks that is advantageous but the outer joins are always evaluated in the order in which they occur.

When selecting the order of tables in a join, SQLite uses a greedy algorithm that runs in polynomial (O(N²)) time. Because of this, SQLite is able to efficiently plan queries with 50- or 60-way joins.

Join reordering is automatic and usually works well enough that programmers do not have to think about it, especially if ANALYZE has been used to gather statistics about the available indices. But occasionally some hints from the programmer are needed. Consider, for example, the following schema:

  CREATE TABLE node(
     id INTEGER PRIMARY KEY,
     name TEXT
  );
  CREATE INDEX node_idx ON node(name);
  CREATE TABLE edge(
     orig INTEGER REFERENCES node,
     dest INTEGER REFERENCES node,
     PRIMARY KEY(orig, dest)
  );
  CREATE INDEX edge_idx ON edge(dest,orig);

The schema above defines a directed graph with the ability to store a name at each node. Now consider a query against this schema:

  SELECT *
    FROM edge AS e,
         node AS n1,
         node AS n2
   WHERE n1.name = 'alice'
     AND n2.name = 'bob'
     AND e.orig = n1.id
     AND e.dest = n2.id;

This query asks for is all information about edges that go from nodes labeled "alice" to nodes labeled "bob". The query optimizer in SQLite has basically two choices on how to implement this query. (There are actually six different choices, but we will only consider two of them here.) Pseudocode below demonstrating these two choices.

Option 1:

  foreach n1 where n1.name='alice' do:
    foreach n2 where n2.name='bob' do:
      foreach e where e.orig=n1.id and e.dest=n2.id
        return n1.*, n2.*, e.*
      end
    end
  end

Option 2:

  foreach n1 where n1.name='alice' do:
    foreach e where e.orig=n1.id do:
      foreach n2 where n2.id=e.dest and n2.name='bob' do:
        return n1.*, n2.*, e.*
      end
    end
  end

The same indices are used to speed up every loop in both implementation options. The only difference in these two query plans is the order in which the loops are nested.

So which query plan is better? It turns out that the answer depends on what kind of data is found in the node and edge tables.

Let the number of alice nodes be M and the number of bob nodes be N. Consider two scenarios. In the first scenario, M and N are both 2 but there are thousands of edges on each node. In this case, option 1 is perferred. With option 1, the inner loop checks for the existence of an edge between a pair of nodes and outputs the result if found. But because there are only 2 alice and bob nodes each, the inner loop only has to run 4 times and the query is very quick. Option 2 would take much longer here. The outer loop of option 2 only executes twice, but because there are a large number of edges leaving each alice node, the middle loop has to iterate many thousands of times. It will be much slower. So in the first scenario, we prefer to use option 1.

Now consider the case where M and N are both 3500. Alice nodes are abundant. But suppose each of these nodes is connected by only one or two edges. In this case, option 2 is preferred. With option 2, the outer loop still has to run 3500 times, but the middle loop only runs once or twice for each outer loop and the inner loop will only run once for each middle loop, if at all. So the total number of iterations of the inner loop is around 7000. Option 1, on the other hand, has to run both its outer loop and its middle loop 3500 times each, resulting in 12 million iterations of the middle loop. Thus in the second scenario, option 2 is nearly 2000 times faster than option 1.

So you can see that depending on how the data is structured in the table, either query plan 1 or query plan 2 might be better. Which plan does SQLite choose by default? As of version 3.6.18, without running ANALYZE, SQLite will choose option 2. But if the ANALYZE command is run in order to gather statistics, a different choice might be made if the statistics indicate that the alternative is likely to run faster.

5.2 Manual Control Of Query Plans

SQLite provides the ability for advanced programmers to exercise control over the query plan chosen by the optimizer. One method for doing this is to fudge the ANALYZE results in the sqlite_stat1 and sqlite_stat2 tables. That approach is not recommended except for the one scenario described in the following paragraph.

For an program that uses an SQLite database as its application file format, when a new database instances is first created the ANALYZE command is ineffective because the database contain no data from which to gather statistics. In that case, one could construct a large prototype database containing typical data during development and run the ANALYZE command on this prototype database to gather statistics, then save the prototype statistics as part of the application. After deployment, when the application goes to create a new database file, it can run the ANALYZE command in order to create the sqlite_stat1 and sqlite_stat2 tables, then copy the precomputed statistics obtained from the prototype database into these new statistics tables. In that way, statistics from large working data sets can be preloaded into newly created application files.

If you really must take manual control of join loop nesting order, the preferred method is to use some peculiar (though valid) SQL syntax to specify the join. If you use the keyword CROSS in a join, then the two tables connected by that join will not be reordered. So in the query, the optimizer is free to reorder the tables of the FROM clause anyway it sees fit:

  SELECT *
    FROM node AS n1,
         edge AS e,
         node AS n2
   WHERE n1.name = 'alice'
     AND n2.name = 'bob'
     AND e.orig = n1.id
     AND e.dest = n2.id;

But in the following logically equivalent formulation of the query, the substitution of "CROSS JOIN" for the "," means that the order of tables must be N1, E, N2.

  SELECT *
    FROM node AS n1 CROSS JOIN
         edge AS e CROSS JOIN
         node AS n2
   WHERE n1.name = 'alice'
     AND n2.name = 'bob'
     AND e.orig = n1.id
     AND e.dest = n2.id;

Hence, in the second form, the query plan must be option 2. Note that you must use the keyword CROSS in order to disable the table reordering optimization; INNER JOIN, NATURAL JOIN, JOIN, and other similar combinations work just like a comma join in that the optimizer is free to reorder tables as it sees fit. (Table reordering is also disabled on an outer join, but that is because outer joins are not associative or commutative. Reordering tables in outer joins changes the result.)

6.0 Choosing between multiple indices

Each table in the FROM clause of a query can use at most one index (except when the OR-clause optimization comes into paly) and SQLite strives to use at least one index on each table. Sometimes, two or more indices might be candidates for use on a single table. For example:

  CREATE TABLE ex2(x,y,z);
  CREATE INDEX ex2i1 ON ex2(x);
  CREATE INDEX ex2i2 ON ex2(y);
  SELECT z FROM ex2 WHERE x=5 AND y=6;

For the SELECT statement above, the optimizer can use the ex2i1 index to lookup rows of ex2 that contain x=5 and then test each row against the y=6 term. Or it can use the ex2i2 index to lookup rows of ex2 that contain y=6 then test each of those rows against the x=5 term.

When faced with a choice of two or more indices, SQLite tries to estimate the total amount of work needed to perform the query using each option. It then selects the option that gives the least estimated work.

To help the optimizer get a more accurate estimate of the work involved in using various indices, the user may optionally run the ANALYZE command. The ANALYZE command scans all indices of database where there might be a choice between two or more indices and gathers statistics on the selectiveness of those indices. The statistics gathered by this scan are stored in special database tables names shows names all begin with "sqlite_stat". The content of these tables is not updated as the database changes so after making significant changes it might be prudent to rerun ANALYZE. The results of an ANALYZE command are only available to database connections that are opened after the ANALYZE command completes.

The various sqlite_stat N tables contain information on how selective the various indices are. For example, the sqlite_stat1 table might indicate that an equality constraint on column x reduces the search space to 10 rows on average, whereas an equality constraint on column y reduces the search space to 3 rows on average. In that case, SQLite would prefer to use index ex2i2 since that index.

Terms of the WHERE clause can be manually disqualified for use with indices by prepending a unary + operator to the column name. The unary + is a no-op and will not slow down the evaluation of the test specified by the term. But it will prevent the term from constraining an index. So, in the example above, if the query were rewritten as:

  SELECT z FROM ex2 WHERE +x=5 AND y=6;

The + operator on the x column will prevent that term from constraining an index. This would force the use of the ex2i2 index.

Note that the unary + operator also removes type affinity from an expression, and in some cases this can cause subtle changes in the meaning of an expression. In the example above, if column x has TEXT affinity then the comparison "x=5" will be done as text. But the + operator removes the affinity. So the comparison "+x=5" will compare the text in column x with the numeric value 5 and will always be false.

6.1 Range Queries

Consider a slightly different scenario:

  CREATE TABLE ex2(x,y,z);
  CREATE INDEX ex2i1 ON ex2(x);
  CREATE INDEX ex2i2 ON ex2(y);
  SELECT z FROM ex2 WHERE x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 100;

Further suppose that column x contains values spread out between 0 and 1,000,000 and column y contains values that span between 0 and 1,000. In that scenario, the range constraint on column x should reduce the search space by a factor of 10,000 whereas the range constraint on column y should reduce the search space by a factor of only 10. So the ex2i1 index should be preferred.

SQLite will make this determination, but only if it has been compiled with SQLITE_ENABLE_STAT2. The SQLITE_ENABLE_STAT2 option causes the ANALYZE command to collect a histogram of column content in the sqlite_stat2 table and to use this histogram to make a better guess at the best query to use for range constraints such as the above.

The histogram data is only useful if the right-hand side of the constraint is a simple compile-time constant or parameter and not an expression.

Another limitation of the histogram data is that it only applies to the left-most column on an index. Consider this scenario:

  CREATE TABLE ex3(w,x,y,z);
  CREATE INDEX ex3i1 ON ex2(w, x);
  CREATE INDEX ex3i2 ON ex2(w, y);
  SELECT z FROM ex3 WHERE w=5 AND x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 100;

Here the inequalities are on columns x and y which are not the left-most index columns. Hence, the histogram data which is collected no left-most column of indices is useless in helping to choose between the range constraints on columns x and y.

7.0 Avoidance of table lookups

When doing an indexed lookup of a row, the usual procedure is to do a binary search on the index to find the index entry, then extract the rowid from the index and use that rowid to do a binary search on the original table. Thus a typical indexed lookup involves two binary searches. If, however, all columns that were to be fetched from the table are already available in the index itself, SQLite will use the values contained in the index and will never look up the original table row. This saves one binary search for each row and can make many queries run twice as fast.

8.0 ORDER BY optimizations

SQLite attempts to use an index to satisfy the ORDER BY clause of a query when possible. When faced with the choice of using an index to satisfy WHERE clause constraints or satisfying an ORDER BY clause, SQLite does the same work analysis described above and chooses the index that it believes will result in the fastest answer.

9.0 Subquery flattening

When a subquery occurs in the FROM clause of a SELECT, the simplest behavior is to evaluate the subquery into a transient table, then run the outer SELECT against the transient table. But such a plan can be suboptimal since the transient table will not have any indices and the outer query (which is likely a join) will be forced to do a full table scan on the transient table.

To overcome this problem, SQLite attempts to flatten subqueries in the FROM clause of a SELECT. This involves inserting the FROM clause of the subquery into the FROM clause of the outer query and rewriting expressions in the outer query that refer to the result set of the subquery. For example:

  SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5

Would be rewritten using query flattening as:

  SELECT x+y AS a FROM t1 WHERE z<100 AND a>5

There is a long list of conditions that must all be met in order for query flattening to occur.

  1. The subquery and the outer query do not both use aggregates.
  2. The subquery is not an aggregate or the outer query is not a join.
  3. The subquery is not the right operand of a left outer join.
  4. The subquery is not DISTINCT or the outer query is not a join.
  5. The subquery is not DISTINCT or the outer query does not use aggregates.
  6. The subquery does not use aggregates or the outer query is not DISTINCT.
  7. The subquery has a FROM clause.
  8. The subquery does not use LIMIT or the outer query is not a join.
  9. The subquery does not use LIMIT or the outer query does not use aggregates.
  10. The subquery does not use aggregates or the outer query does not use LIMIT.
  11. The subquery and the outer query do not both have ORDER BY clauses.
  12. The subquery and outer query do not both use LIMIT.
  13. The subquery does not use OFFSET.
  14. The outer query is not part of a compound select or the subquery does not have both an ORDER BY and a LIMIT clause.
  15. The outer query is not an aggregate or the subquery does not contain ORDER BY.
  16. The sub-query is not a compound select, or it is a UNION ALL compound clause made up entirely of non-aggregate queries, and the parent query:
    • is not itself part of a compound select,
    • is not an aggregate or DISTINCT query, and
    • has no other tables or sub-selects in the FROM clause.
    The parent and sub-query may contain WHERE clauses. Subject to rules (11), (12) and (13), they may also contain ORDER BY, LIMIT and OFFSET clauses.
  17. If the sub-query is a compound select, then all terms of the ORDER by clause of the parent must be simple references to columns of the sub-query.
  18. The subquery does not use LIMIT or the outer query does not have a WHERE clause.
  19. If the sub-query is a compound select, then it must not use an ORDER BY clause.

The casual reader is not expected to understand or remember any part of the list above. The point of this list is to demonstrate that the decision of whether or not to flatten a query is complex.

Query flattening is an important optimization when views are used as each use of a view is translated into a subquery.

10.0 The MIN/MAX optimization

Queries of the following forms will be optimized to run in logarithmic time assuming appropriate indices exist:

  SELECT MIN(x) FROM table;
  SELECT MAX(x) FROM table;

In order for these optimizations to occur, they must appear in exactly the form shown above - changing only the name of the table and column. It is not permissible to add a WHERE clause or do any arithmetic on the result. The result set must contain a single column. The column in the MIN or MAX function must be an indexed column.


SQLite Home Page

Choose any three.
About Sitemap Documentation Download License News Support

Welcome.

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

Sponsors

Ongoing development and maintenance of SQLite is sponsored in part by SQLite Consortium members, including:

Adobe revolutionizes how the world engages with ideas and information - anywhere, anytime, and through any medium.
Mozilla - Working to preserve choice and innovation on the internet.
Symbian - The market-leading open operating system for advanced data-enabled smartphones.
Bloomberg - A world leader in financial-information technology.

Current Status

  • Version 3.6.22 of SQLite is recommended for all new development. Upgrading from version 3.6.12 and 3.6.13 is optional. Upgrading from all other SQLite versions is recommended.

Common Links

  • Features
  • Frequently Asked Questions
  • Well-known Users
  • Getting Started
  • SQL Syntax
  • C/C++ Interface Spec
  • Timeline
  • Wiki