The official code distribution is known as the amalgamation. The amalgamation is a single C source file that contains the entire SQLite core. It is created by assembling the individual development files into a single C source file that is almost 4 megabytes in size and over 100,000 lines long. The amalgamation, along with its corresponding header file, is all that is needed to integrate SQLite into your application.

The amalgamation has two main advantages. First, with everything in one file, it is extremely easy to integrate SQLite into a host application. Many projects simply copy the amalgamation files into their own source directories. It is also possible to compile the SQLite core into a library and simply link the library into your application.

Second, the amalgamation also helps improve performance. Many compiler optimizations are limited to a single translation unit. In C, that’s a single source file. By putting the whole library into a single file, a good optimizer can process the whole package at once. Compared to compiling the individual source files, some platforms see a 5% or better performance boost just by using the amalgamation.

The only disadvantage of using the amalgamation is size. Some debuggers have issues with files more than 65,535 lines long. Things typically run correctly, but it can be difficult to set breakpoints or look at stack traces. Compiling a source file over 100,000 lines long also takes a fair number of resources. While this is no problem for most desktop systems, it may push the limits of any compilers running on limited platforms.

When working with the amalgamation, there are four important source files:

The first two, sqlite3.c and sqlite3.h, are all that is needed to integrate SQLite into most applications. The sqlite3ext.h file is used to build extensions and modules. Building extensions is covered in SQLite Extensions. The shell.c file contains the source code for the sqlite3 command-line shell. All of these files can be built on Linux, Mac OS X, or Windows, without any additional configuration files.

The SQLite website offers five source distribution packages. Most people will be interested in one of the first two files.

The Windows amalgamation file consists of the four main files, plus a .def file to build a DLL. No makefile, project, or solution files are included.

The Unix amalgamation file, which works on Linux, Mac OS X, and many other flavors of Unix, contains the four main files plus an sqlite3 manual page. The Unix distribution also contains a basic configure script, along with other autoconf files, scripts, and makefiles. The autoconf files should also work under the Minimalist GNU for Windows (MinGW) environment (http://www.mingw.org/).

The Tcl extension distribution is a specialized version of the amalgamation. It is only of interest to those working in the Tcl language. See the included documentation for more details.

The Unix source tree is an unsupported legacy distribution. This is what the standard distribution looked like before the amalgamation became the officially supported distribution. It is made available for those that have older build environments or development branches that utilize the old distribution format. This distribution also includes a number of README files that are unavailable elsewhere.

The Windows source distribution is essentially a .zip file of the source directory from the source tree distribution, minus some test files. It is strictly source files and header files, and contains no build scripts, makefiles, or project files.

If you’re using the Unix amalgamation distribution, you can build and install SQLite using the standard configure script. After downloading the distribution, it is fairly easy to unpack, configure, and build the source:

$ tar xzf sqlite-amalgamation-3.x.x.tar.gz
$ cd sqlite-3.x.x
$ ./configure
  [...]
$ make

By default, this will build the SQLite core into both static and dynamic libraries. It will also build the sqlite3 utility. These will be built with many of the extra features (such as full text search and R*Tree support) enabled. Once this finishes, the command make install will install these files, along with the header files and sqlite3 manual page. By default, everything is installed into /usr/local, although this can be changed by giving a --prefix=/path/to/install option to configure. Issue the command configure --help for information on other build options.

Because the main SQLite amalgamation consists of only two source files and two header files, it is extremely simple to build by hand. For example, to build the sqlite3 shell on Linux, or most other Unix systems:

$ cc -o sqlite3 shell.c sqlite3.c -ldl -lpthread

The additional libraries are needed to support dynamic linking and threads. Mac OS X includes those libraries in the standard system group, so no additional libraries are required when building for Mac OS X:

$ cc -o sqlite3 shell.c sqlite3.c

The commands are very similar on Windows, using the Visual Studio C compiler from the command-line:

> cl /Fesqlite3 shell.c sqlite3.c

This will build both the SQLite core and the shell into one application. That means the resulting sqlite3 executable will not require an installed library in order to operate.

If you want to build things with one of the optional modules installed, you need to define the appropriate compiler directives. This shows how to build things on Unix with the FTS3 (full text search) extension enabled:

$ cc -DSQLITE_ENABLE_FTS3 -o sqlite3 shell.c sqlite3.c -ldl -lpthread

Or, on Windows:

> cl /Fesqlite3 /DSQLITE_ENABLE_FTS3 shell.c sqlite3.c

Building the SQLite core into a dynamic library is a bit more complex. We need to build the object file, then build the library using that object file. If you’ve already built the sqlite3 utility, and have an sqlite3.o (or .obj) file, you can skip the first step. First, in Linux and most Unix systems:

$ cc -c sqlite3.c
$ ld -shared -o libsqlite3.so sqlite3.o

Some versions of Linux may also require the -fPIC option when compiling.

Mac OS X uses a slightly different dynamic library format, so the command to build it is slightly different. It also needs the standard C library to be explicitly linked:

$ cc -c sqlite3.c
$ ld -dylib -o libsqlite3.dylib sqlite3.o -lc

And finally, building a Windows DLL (which requires the sqlite3.def file):

> cl /c sqlite3.c
> link /dll /out:sqlite3.dll /def:sqlite3.def sqlite3.obj

You may need to edit the sqlite3.def file to add or remove functions, depending on which compiler directives are used.

The SQLite core is aware of a great number of compiler directives. Appendix A covers all of these in detail. Many are used to alter the standard default values, or to adjust some of the maximum sizes and limits. Compiler directives are also used to enable or disable specific features and extensions. There are several dozen directives in all.

The default build, without any specific directives, will work well enough for a wide variety of applications. However, if your application requires one of the extensions, or has specific performance concerns, there may be some ways to tune the build. Many of the parameters can also be adjusted at runtime, so a recompile may not always be necessary, but it can make development more convenient.

The core of SQL is a declarative language. In a declarative language, you state what you want the results to be and allow the language processor to figure out how to deliver the desired results. Compare this to imperative languages, such as C, Java, Perl, or Python, where each step of a calculation or operation must be explicitly written out, leaving it up to the programmer to lead the program, step by step, to the correct conclusion.

The first SQL standards were specifically designed to make the language approachable and usable by “non-computer people”—at least by the 1980s definition of that term. This is one of the reasons why SQL commands tend to have a somewhat English-like syntax. Most SQL commands take the form verb-subject. For example, CREATE (verb) TABLE (subject), DROP INDEX, UPDATE table_name.

The almost-English, declarative nature of SQL has both advantages and disadvantages. Declarative languages tend to be simpler (especially for nonprogrammers) to understand. Once you get used to the general structure of the commands, the use of English keywords can make the syntax easier to remember. The fixed command structure also makes it much easier for the database engine to optimize queries and return data more efficiently.

The predefined nature of declarative statements can sometimes feel a bit limited, however—especially in a command-based language like SQL, where individual, isolated commands are constructed and issued one at a time. If you require a query that doesn’t fit into the processing order defined by the SELECT command, you may find yourself having to patch together nested queries or temporary tables. This is especially true when the problem you’re trying to solve is inherently nonrelational, and you’re forced to jump through extra hoops to account for that.

Despite its oddities and somewhat organic evolution, SQL is a powerful language with a surprisingly broad ability to express complex operations. Once you wrap your head around what makes SQL tick, you can often find moderately simple solutions to even the most off-the-beaten-path problems. It can take some adjustment, however, especially if your primary experience is with imperative languages.

SQL’s biggest flaw is that formal standardization has almost always followed common implementations. Almost every database product (including SQLite) has custom extensions and enhancements to the core language that help differentiate it from other products, or expose features or control systems that are not covered by the core SQL standard. Often these enhancements are related to performance enhancements, and can be difficult to ignore.

While this less-strict approach to language purity has allowed SQL to grow and evolve in very practical ways, it means that “real world” SQL portability is not all that practical. If you strictly limit yourself to standardized SQL syntax, you can achieve a moderate degree of portability, but normally this comes at the cost of lower performance and less data integrity. Generally, applications will write to the specific SQL dialect they’re using and not worry about cross-database compatibility. If cross-database compatibility is important to a specific application, the normal approach is to develop a core list of SQL commands required by the application, with minor tweaks for each specific database product.

SQLite makes an effort to follow the SQL standards as much as possible. SQLite will also recognize and correctly parse a number of nonstandard syntax conventions used by other popular databases. This can help with the portability issues.

SQL is not without other issues, but considering its lineage, it is surprisingly well suited for the task at hand. Love it or hate it, it is the relational database language of choice, and it is likely to be with us for a long time.

SQL consists of a number of different commands, such as CREATE TABLE or INSERT. These commands are issued and processed one at a time. Each command implements a different action or feature of the database system.

Although it is customary to use all capital letters for SQL commands and keywords, SQL is a case-insensitive^[1] language. All commands and keywords are case insensitive, as are identifiers (such as table names and column names).

Identifiers must be given as literals. If necessary, identifiers can be enclosed in the standards compliant double-quotes (" ") to allow the inclusion of spaces or other nonstandard characters in an identifier. SQLite also allows identifiers to be enclosed in square brackets ([ ]) or back ticks (` `) for compatibility with other popular database products. SQLite reserves the use of any identifier that uses sqlite_ as a prefix.

SQL is whitespace insensitive, including line breaks. Individual statements are separated by a semicolon. If you’re using an interactive application, such as the sqlite3 command-line tool, then you’ll need to use a semicolon to indicate the end of a statement. The semicolon is not strictly required for single statements, however, as it is properly a statement separator and not a statement terminator. When passing SQL commands into the programming API, the semicolon is not required unless you are passing more than one command statement within a single string.

Single-line comments start with a double dash (--) and go to the end of the line. SQL also supports multi-line comments using the C comment syntax (/* */).

As with most languages, numeric literals are represented bare. Both integer (453) and real (rational) numbers (43.23) are recognized, as is exponent-style scientific notation (9.745e-6). In order to avoid ambiguities in the parser, SQLite requires that the decimal point is always represented as a period (.), regardless of the current internationalization setting.

Text literals are enclosed in single quotes (' '). To represent a string literal that includes a single quote character, use two single quotes in a row (publisher = 'O''Reilly'). C-style backslash escapes ( \' ) are not part of the SQL standard and are not supported by SQLite. BLOB literals (binary data) can be represented as an x (or X) followed by a string literal of hexadecimal characters (x'A554E59C').

SQL statements and expressions frequently contain lists. A comma is used as the list separator. SQL does not allow for a trailing comma following the last item of a list.

In general, expressions can be used any place a literal data value is allowed. Expressions can include both mathematical statements, as well as functions. Function-calling syntax is similar to most other computer languages, utilizing the name of the function, followed by a list of parameters enclosed in parentheses. Expressions can be grouped into subexpressions using parentheses.

If an expression is evaluated in the context of a row (such as a filtering expression), the value of a row element can be extracted by naming the column. You may have to qualify the column name with a table name or alias. If you’re using cross-database queries, you may also have to specify which database you’re referring to. The syntax is:

[[database_name.]table_name.]column_name

If no database name is given, it is assumed you’re referring to the main database on the default connection. If the table name/alias is also omitted, the system will make a best-guess using just the column name, but will return an error if the name is ambiguous.

SQL allows any value to be assigned a NULL. NULL is not a value in itself (SQLite actually implements it as a unique valueless type), but is used as a marker or flag to represent unknown or missing data. The thought is that there are times when values for a specific row element may not be available or may not be applicable.

A NULL may not be a value, but it can be assigned to data elements that normally have values, and can therefore show up in expressions. The problem is that NULLs don’t interact well with other values. If a NULL represents an unknown that might be any possible value, how can we know if the expression NULL > 3 is true or false?

To deal with this problem, SQL must employ a concept called three-valued logic. Three-valued logic is often abbreviated TVL or 3VL, and is more formally known as ternary logic. 3VL essentially adds an “unknown” state to the familiar true/false Boolean logic system.

Here are the truth tables for the 3VL operators NOT, AND, and OR:

3VL also dictates how comparisons work. For example, any equality check (=) involving a NULL will evaluate to NULL, including NULL = NULL. Remember that NULL is not a value, it is a flag for the unknown, so the expression NULL = NULL is asking, “Does this unknown equal that unknown?” The only practical answer is, “That is unknown.” It might, but it might not. Similar rules apply to greater-than, less-than, and other comparisons.

If you’re having trouble resolving an expression, just remember that a NULL marks an unknown or unresolved value. This is why the expression False AND NULL is false, but True AND NULL is NULL. In the case of the first expression, the NULL can be replaced by either true or false without altering the expression result. That isn’t true of the second expression, where the outcome is unknown (in other words, NULL) because the output depends on the unknown input.

SQLite supports the following unary prefix operators:

There are also a number of binary operators. They are listed here in descending precedence.

In addition to these basics, SQL supports a number of specific expression operations. For more information on these and any SQL-specific expressions, see Appendix D.

The most common DDL command is CREATE TABLE. No data values can be stored in a database until a table is defined to hold that data. At the bare minimum, the CREATE TABLE command defines the table name, plus the name of each column. Most of the time you’ll want to define a type for each column as well, although types are optional when using SQLite. Optional constraints, conditions, and default values can also be assigned to each column. Table-level constraints can also be assigned, if they involve multiple columns.

In some large RDBMS systems, CREATE TABLE can be quite complex, defining all kinds of storage options and configurations. SQLite’s version of CREATE TABLE is somewhat simpler, but there are still a great many options available. For full explanation of the command, see CREATE TABLE in Appendix C.

CREATE TABLE table_name
(
   column_name  column_type,
   [...]
);

CREATE TABLE table_name
(
   column_name  column_type    column_constraints...,
   [... ,]

   table_constraints,
   [...]
);

CREATE TABLE parts
(
    part_id    INTEGER   PRIMARY KEY,
    stock      INTEGER   DEFAULT 0   NOT NULL,
    desc       TEXT      CHECK( desc != '' )    -- empty strings not allowed
);

CREATE TABLE rooms
(
    room_number       INTEGER  NOT NULL,
    building_number   INTEGER  NOT NULL,
    [...,]

    PRIMARY KEY( room_number, building_number )
);

CREATE [TEMP] TABLE table_name AS SELECT query_statement;

DROP TABLE table_name;

Views provide a way to package queries into a predefined object. Once created, views act more or less like read-only tables. Just like tables, new views can be marked as TEMP, with the same result. The basic syntax of the CREATE VIEW command is:

CREATE [TEMP] VIEW view_name AS SELECT query_statement

The CREATE VIEW syntax is almost identical to the CREATE TABLE...AS SELECT command. This is because both commands serve a similar purpose, with one important difference. The result of a CREATE TABLE command is a new table that contains a full copy of the data. The SELECT statement is run exactly once and the output of the query is stored in the newly defined table. Once created, the table will hold its own, independent copy of the data.

A view, on the other hand, is fully dynamic. Every time the view is referenced or queried, the underlying SELECT statement is run to regenerate the view. This means the data seen in a view automatically updates as the data changes in the underlying tables. In a sense, views are almost like named queries.

Views are commonly used in one of two ways. First, they can be used to package up commonly used queries into a more convenient form. This is especially true if the query is complex and prone to error. By creating a view, you can be sure to get the same query each time.

Views are also commonly used to create user-friendly versions of standard tables. A common example are tables with date and time records. Normally, any time or date value is recorded in Coordinated Universal Time, or UTC. UTC is a more proper format for dates and times because it is unambiguous and time-zone independent. Unfortunately, it can also be a bit confusing if you’re several time zones away. It is often useful to create a view that mimics the base table, but converts all the times and dates from UTC into the local time zone. This way the data in the original tables remains unchanged, but the presentation is in units that are more user-friendly.

Views are dropped with the DROP VIEW command:

DROP VIEW view_name;

Dropping a view will not have any effect on the tables it references.

Indexes (or indices) are a means to optimize database lookups by pre-sorting and indexing one or more columns of a table. Ideally, this allows specific rows in a table to be found without having to scan every row in the table. In this fashion, indexes can provide a large performance boost to some types of queries. Indexes are not free, however, requiring updates with each modification to a table as well as additional storage space. There are even some situations when an index will cause a drop in performance. See Indexes for more information on when it makes sense to use an index.

The basic syntax for creating an index specifies the name of the new index, as well as the table and column names that are indexed. Indexes are always associated with one (and only one) table, but they can include one or more columns from that table:

CREATE [UNIQUE] INDEX index_name ON table_name ( column_name [, ...] );

Normally indexes allow duplicate values. The optional UNIQUE keyword indicates that duplicate entries are not allowed, and any attempt to insert or update a table with a nonunique value will cause an error. For unique indexes that reference more than one column, all the columns must match for an entry to be considered duplicate. As discussed with CREATE TABLE, NULL isn’t considered a value, so a UNIQUE index will not prevent one or more NULLs. If you want to prevent NULLs, you must indicate NOT NULL in the original table definition.

Each index is tied to a specific table, but they all share a common namespace. Although you can name an index anything you like, it is standard practice to name an index with a standard prefix (such as idx_), and then include the table name and possibly the names of the columns included in the index. For example:

CREATE INDEX idx_employees_name ON employees ( name );

This makes for long index names but, unlike table or view names, you typically only reference an index’s name when you create it and when you drop it.

As with all the DROP commands, the DROP INDEX command is very simple, and requires only the name of the index that is being dropped:

DROP INDEX index_name;

Dropping an index will remove the index from the database, but will leave the associated table intact.

As described earlier, the CREATE TABLE command will automatically create unique indexes to enforce a UNIQUE or PRIMARY KEY constraint. All automatic indexes will start with an sqlite_ prefix. Because these indexes are required to enforce the table definition, they cannot be manually dropped with the DROP INDEX command. Dropping the automatic indexes would alter the table behavior as defined by the original CREATE TABLE command.

Conversely, if you have manually defined a UNIQUE index, dropping that index will allow the database to insert or update redundant data. Be careful when auditing indexes and remember that not all indexes are created for performance reasons.

There are three commands used for adding, modifying, and removing data from the database. INSERT adds new rows, UPDATE modifies existing rows, and DELETE removes rows. These three commands are used to maintain all of the actual data values within the database. All three update commands operate at a row level, adding, altering, or removing the specified rows. Although all three commands are capable of acting on multiple rows, each command can only directly act upon rows contained within a single table.

INSERT INTO table_name (column_name [, ...]) VALUES (new_value [, ...]);

INSERT INTO parts ( name, stock, status ) VALUES ( 'Widget', 17, 'IN STOCK' );

INSERT INTO table_name VALUES (new_value [, ...]);

INSERT INTO table_name (column_name, [...]) SELECT query_statement;

UPDATE table_name SET column_name=new_value [, ...] WHERE expression

-- Update the price and stock of part_id 454:
UPDATE parts SET price = 4.25, stock = 75 WHERE part_id = 454;

DELETE FROM table_name WHERE expression;

-- Delete the row with rowid 385:
DELETE FROM parts WHERE part_id = 385;

-- Delete all rows with a rowid greater than or equal to 43
-- and less than or equal to 246:
DELETE FROM parts WHERE part_id >= 43 AND part_id <= 246;

DELETE FROM parts WHERE 1; -- delete all rows, force per-row processing

The final DML command to cover is the SELECT command. SELECT is used to extract or return values from the database. Almost any time you want to extract or return some kind of value, you’ll need to use the SELECT command. Generally, the returned values are derived from the contents of the database, but SELECT can also be used to return the value of simple expressions. This is a great way to test out expressions, for example:

sqlite> SELECT 1+1, 5*32, 'abc'||'def', 1>2;
1+1         5*32        'abc' || 'def'  1>2       
----------  ----------  --------------  ----------
2           160         abcdef          0         

SELECT is a read-only command, and will not modify the database (unless the SELECT is embedded in a different command, such as a CREATE TABLE...AS SELECT or an INSERT INTO...SELECT).

Without question, SELECT is the most complex SQL command, both in terms of syntax as well as function. The SELECT syntax tries to represent a generic framework that is capable of expressing a great many different types of queries. While it is somewhat successful at this, there are areas where SELECT has traded away simplicity for more flexibility. As a result, SELECT has a large number of optional clauses, each with its own set of options and formats.

Understanding how to mix and match these optional clauses to get the result you’re looking for can take some time. While the most basic syntax can be shown with a good set of examples, to really wrap your head around SELECT, it is best to understand how it actually works and what it is trying to accomplish.

Because SELECT can be so complex, and because SELECT is an extremely important command, we will spend the whole next chapter looking very closely at SELECT and each of its clauses. There will be some discussion about what is going on behind the scenes, to provide more insight into how to read and write complex queries.

For now, we’ll just give you a taste. That should provide enough information to play around with the other commands in this chapter. The most basic form of SELECT is:

SELECT output_list FROM input_table WHERE row_filter;

The output list is a list of expressions that should be evaluated and returned for each resulting row. Most commonly, this is simply a list of columns. The output list can also include a wildcard (*) that indicates all known columns should be returned.

The FROM clause defines the source of the table data. The next chapter will show how tables can be linked and joined, but for now we’ll stick with querying one table at a time.

The WHERE clause is a conditional filtering expression that is applied to each row. It is essentially the same as the WHERE clause in the UPDATE and DELETE commands. Those rows that evaluate to true will be part of the result, while the other rows will be filtered out.

Consider this table:

sqlite> CREATE TABLE tbl ( a, b, c, id INTEGER PRIMARY KEY );
sqlite> INSERT INTO tbl ( a, b, c ) VALUES ( 10, 10, 10 );
sqlite> INSERT INTO tbl ( a, b, c ) VALUES ( 11, 15, 20 );
sqlite> INSERT INTO tbl ( a, b, c ) VALUES ( 12, 20, 30 );

We can return the whole table like this:

sqlite> SELECT * FROM tbl;
a           b           c           id         
----------  ----------  ----------  ----------
10          10          10          1         
11          15          20          2         
12          20          30          3         

We can also just return specific columns:

sqlite> SELECT a, c FROM tbl;
a           c         
----------  ----------
10          10        
11          20        
12          30        

Or specific rows:

sqlite> SELECT * FROM tbl WHERE id = 2;
a           b           c           id        
----------  ----------  ----------  ----------
11          15          20          2         

For more specifics, see Chapter 5 and SELECT in Appendix C.

A transaction is used to group together a series of low-level changes into a single, logical update. A transaction can be anything from updating a single value to a complex, multistep procedure that might end up inserting several rows into a number of different tables.

The classic transaction example is a database that holds account numbers and balances. If you want to transfer a balance from one account to another, that is a simple two-step process: subtract an amount from one account balance and then add the same amount to the other account balance. That process needs to be done as a single logical unit of change, and should not be broken apart. Both steps should either succeed completely, resulting in the balance being correctly transferred, or both steps should fail completely, resulting in both accounts being left unchanged. Any other outcome, where one step succeeds and the other fails, is not acceptable.

Typically a transaction is opened, or started. As individual data manipulation commands are issued, they become part of the transaction. When the logical procedure has finished, the transaction can be committed, which applies all of the changes to the permanent database record. If, for any reason, the commit fails, the transaction is rolled back, removing all traces of the changes. A transaction can also be manually rolled back.

The standard for reliable, robust transactions is the ACID test. ACID stands for Atomic, Consistent, Isolated, and Durable. Any transaction system worth using must possess these qualities.

Most people think that the atomic nature of transactions is their most important quality, but all four aspects must work together to ensure the overall integrity of the database. Durability, especially, is often overlooked. SQLite tries extremely hard to guarantee that if a transaction is successfully committed, those changes are actually physically written to permanent storage and are there to stay. Compare this to traditional filesystem operations, where writes might go into an operating system file cache. Updates may sit in the cache anywhere from a few seconds to a few minutes before finally being spooled off to storage. Even then, it is possible for the data to wait around in device buffers before finally being committed to physical storage. While this type of buffering can increase efficiency, it means that a normal application really has no idea when its data is safely committed to permanent storage.

Power failures and disappearing filesystems may seem like rare occurrences, but that’s not really the point. Databases are designed to deal with absolutes, especially when it comes to reliability. Besides, having a filesystem disappear is not that radical of an idea when you consider the prevalence of flash drives and USB thumb drives. Disappearing media and power failures are even more commonplace when you consider the number of SQLite databases that are found on battery-operated, handheld devices such as mobile phones and media players. The use of transactions is even more important on devices like this, since it is nearly impossible to run data recovery tools in that type of environment. These types of devices must be extremely robust and, no matter what the user does (including yanking out flash drives at inconvenient times), the system must stay consistent and reliable. Use of a transactional system can provide that kind of reliability.

Transactions are not just for writing data. Opening a transaction for an extended read-only operation is sometimes useful if you need to gather data with multiple queries. Having the transaction open keeps your view of the database consistent, ensuring that the data doesn’t change between queries. That is useful if, for example, you use one query to gather a bunch of record IDs, and then issue a series of queries against each ID value. Wrapping all the queries in a transaction guarantees all of the queries see the same set of data.

Normally, SQLite is in autocommit mode. This means that SQLite will automatically start a transaction for each command, process the command, and (assuming no errors were generated) automatically commit the transaction. This process is transparent to the user, but it is important to realize that even individually entered commands are processed within a transaction, even if no TCL commands are used.

The autocommit mode can be disabled by explicitly opening a transaction. The BEGIN command is used to start or open a transaction. Once an explicit transaction has been opened, it will remain open until it is committed or rolled back. The keyword TRANSACTION is optional:

BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION]

The optional keywords DEFERRED, IMMEDIATE, or EXCLUSIVE are specific to SQLite and control how the required read/write locks are acquired. If only one client is accessing the database at a time, the locking mode is largely irrelevant. When more than one client may be accessing the database, the locking mode defines how to balance peer access with ensured success of the transaction.

By default, all transactions (including autocommit transactions) use the DEFERRED mode. Under this mode, none of the database locks are acquired until they are required. This is the most “neighborly” mode and allows other clients to continue accessing and using the database until the transaction has no other choice but to lock them out. This allows other clients to continue using the database, but if the locks are not available when the transaction requires them, the transaction will fail and may need to be rolled back and restarted.

BEGIN IMMEDIATE attempts to acquire a reserved lock immediately. If it succeeds, it guarantees the write locks will be available to the transaction when they are needed, but still allows other clients to continue to access the database for read-only operations. The EXCLUSIVE mode attempts to lock out all other clients, including read-only clients. Although the IMMEDIATE and EXCLUSIVE modes are more restrictive to other clients, the advantage is that they will fail immediately if the required locks are not available, rather than after you’ve issued your DDL or DML commands.

Once a transaction is open, you can continue to issue other SQL commands, including both DML and DDL commands. You can think of the changes resulting from these commands as “proposed” changes. The changes are only visible to the local client and have not been fully and permanently applied to the database. If the client process is killed or the server loses power in the middle of an open transaction, the transaction and any proposed changes it has will be lost, but the rest of the database will remain intact and consistent. It is not until the transaction is closed that the proposed changes are committed to the database and made “real.” The COMMIT command is used to close out a transaction and commit the changes to the database. You can also use the alias END. As with BEGIN, the TRANSACTION keyword is optional.

COMMIT [TRANSACTION]
END [TRANSACTION]

Once a COMMIT has successfully returned, all the proposed changes are fully committed to the database and become visible to other clients. At that point, if the system loses power or the client process is killed, the changes will remain safely in the database.

Things don’t always go right, however. Rather than committing the proposed changes, the transaction can be manually rolled back, effectively canceling the transaction and all of the changes it contains. Rolling back a set of proposed changes is useful if an error is encountered. This might be a database error, such as running out of disk space half-way through inserting a series of related records, or it might be an application logic error, such as trying to assign an invoice to an order that doesn’t exist. In such cases, it usually doesn’t make sense to continue with the transaction, nor does it make sense to leave inconsistent data in the database. Pretty much the only choice is to back out and try again.

To cancel the transaction and roll back all the proposed changes, you can use the ROLLBACK command. Again, the keyword TRANSACTION is optional:

ROLLBACK [TRANSACTION]

ROLLBACK will undo and revert all the proposed changes made by the current transaction and then close the transaction. It does not necessarily return the database to its prior state, as other clients may have been making changes in parallel. A ROLLBACK only cancels the proposed changes made by this client within the current transaction.

Both COMMIT and ROLLBACK will end the current transaction, putting SQLite back into autocommit mode.

In addition to ACID-compliant transactions, SQLite also supports save-points. Save-points allow you to mark specific points in the transaction. You can then accept or rollback to individual save-points without having to commit or rollback an entire transaction. Unlike transactions, you can have more than one save-point active at the same time. Save-points are sometimes called nested transactions.

Save-points are generally used in conjunction with large, multistep transactions, where some of the steps or sub-procedures require rollback ability. Save-points allow a transaction to proceed and (if required) roll back one step at a time. They also allow an application to explore different avenues, attempting one procedure, and if that doesn’t work, trying another, without having to roll back the entire transaction to start over. In a sense, save-points can be thought of as “undo” markers in SQL command stream.

You can create a save-point with the SAVEPOINT command. Since multiple save-points can be defined, you must provide a name to identify the save-point:

SAVEPOINT savepoint_name

Save-points act as a stack. Whenever you create a new one, it is put at the top of the stack. Save-point identifiers do not need to be unique. If the same save-point identifier is used more than once, the one nearest to the top of the stack is used.

To release a save-point and accept all of the proposed changes made since the save-point was set, use the RELEASE command:

RELEASE [SAVEPOINT] savepoint_name

The RELEASE command does not commit any changes to disk. Rather, it flattens all of the changes in the save-point stack into the layer below the named save-point. The save-point is then removed. Any save-points contained by the named save-point are automatically released.

To cancel a set of commands and undo everything back to where a save-point was set, use the ROLLBACK TO command:

ROLLBACK [TRANSACTION] TO [SAVEPOINT] savepoint_name

Unlike a transaction ROLLBACK, a save-point ROLLBACK TO does not close out and eliminate the save-point. ROLLBACK TO rolls back and cancels any changes issued since the save-point was established, but leaves the transaction state exactly as it was after the SAVEPOINT command was issued.

Consider the following series of SQL statements. The indentation is used to show the save-point stack:

CREATE TABLE t (i);
BEGIN;
  INSERT INTO t (i) VALUES 1;
  SAVEPOINT aaa;
    INSERT INTO t (i) VALUES 2;
    SAVEPOINT bbb;
      INSERT INTO t (i) VALUES 3;

At this point, if the command ROLLBACK TO bbb is issued, the state of the database would be as if the following commands were entered:

CREATE TABLE t (i);
BEGIN;
  INSERT INTO t (i) VALUES 1;
  SAVEPOINT aaa;
    INSERT INTO t (i) VALUES 2;
    SAVEPOINT bbb;

Again, notice that rolling back to save-point bbb still leaves the save-point in place. Any new commands will be associated with SAVEPOINT bbb. For example:

CREATE TABLE t (i);
BEGIN;
  INSERT INTO t (i) VALUES 1;
  SAVEPOINT aaa;
    INSERT INTO t (i) VALUES 2;
    SAVEPOINT bbb;
      DELETE FROM t WHERE i=1;

Continuing, if the command RELEASE aaa was issued, we would get the equivalent of:

CREATE TABLE t (i);
BEGIN;
  INSERT INTO t (i) VALUES 1;
  INSERT INTO t (i) VALUES 2;
  DELETE FROM t WHERE i=1;

In this case, the proposed changes from both the aaa and the enclosed bbb save-points were released and merged outward. The transaction is still open, however, and a COMMIT would still be required to make the proposed changes permanent.

Even if you have open save-points, you can still issue transaction commands. If the enclosing transaction is committed, all outstanding save-points will automatically be released and then committed. If the transaction is rolled back, all the save-points are rolled back.

If the SAVEPOINT command is issued when SQLite is in autocommit mode—that is, outside of a transaction—then a standard autocommit BEGIN DEFERRED TRANSACTION will be started. However, unlike with most commands, the autocommit transaction will not automatically commit after the SAVEPOINT command returns, leaving the system inside an open transaction. The automatic transaction will remain active until the original save-point is released, or the outer transaction is either explicitly committed or rolled back. This is the only situation when a save-point RELEASE will have a direct effect on the enclosing transaction. As with other save-points, if an autocommit save-point is rolled back, the transaction will remain open and the original save-point will be open, but empty.

The FROM clause takes one or more source tables from the database and combines them into one large table. Source tables are usually named tables from the database, but they can also be views or subqueries (see Subqueries for more details on subqueries).

Tables are combined using the JOIN operator. Each JOIN combines two tables into a larger table. Three or more tables can be joined together by stringing a series of JOIN operators together. JOIN operators are evaluated left-to-right, but there are several different types of joins, and not all of them are commutative or associative. This makes the ordering and grouping very important. If necessary, parentheses can be used to group the joins correctly.

Joins are the most important and most powerful database operator. Joins are the only way to bring together information stored in different tables. As we’ll see in the next chapter, nearly all of database design theory assumes the user is comfortable with joins. If you can master joins, you’ll be well on your way to mastering relational databases.

SQL defines three major types of joins: the CROSS JOIN, the INNER JOIN, and the OUTER JOIN.

CROSS JOIN

A CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y columns, respectively, the resulting table will have x+y columns. If the input tables have n and m rows, respectively, the resulting table will have n·m rows. In mathematics, a CROSS JOIN is known as a Cartesian product.

The syntax for a CROSS JOIN is quite simple:

SELECT ... FROM t1 CROSS JOIN t2 ...

Figure 5-1 shows how a CROSS JOIN is calculated.

Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to only use them when appropriate.

Figure 5-1. In a CROSS JOIN, each row from the first table is matched to each row in the second table.

INNER JOIN

An INNER JOIN is very similar to a CROSS JOIN, but it has a built-in condition that is used to limit the number of rows in the resulting table. The conditional is normally used to pair up or match rows from the two source tables. An INNER JOIN without any type of conditional expression (or one that always evaluates to true) will result in a CROSS JOIN. If the input tables have x and y columns, respectively, the resulting table will have no more than x+y columns (in some cases, it can have fewer). If the input tables have n and m rows, respectively, the resulting table can have anywhere from zero to n·m rows, depending on the condition. An INNER JOIN is the most common type of join, and is the default type of join. This makes the INNER keyword optional.

There are three primary ways to specify the conditional. The first is with an ON expression. This provides a simple expression that is evaluated for each potential row. Only those rows that evaluate to true are actually joined. A JOIN...ON looks like this:

SELECT ... FROM t1 JOIN t2 ON conditional_expression ...

An example of this is shown in Figure 5-2.

Figure 5-2. In an INNER JOIN, the rows are matched based off a condition.

If the input tables have C and D columns, respectively, a JOIN...ON will always result in C+D columns.

The conditional expression can be used to test for anything, but the most common type of expression tests for equality between similar columns in both tables. For example, in a business employee database, there is likely to be an employee table that contains (among other things) a name column and an eid column (employee ID number). Any other table that needs to associate rows to a specific employee will also have an eid column that acts as a pointer or reference to the correct employee. This relationship makes it very common to have queries with ON expressions similar to:

SELECT ... FROM employee JOIN resource ON employee.eid = resource.eid ...

This query will result in an output table where the rows from the resource table are correctly matched to their corresponding rows in the employee table.

This JOIN has two issues. First, that ON condition is a lot to type out for something so common. Second, the resulting table will have two eid columns, but for any given row, the values of those two columns will always be identical. To avoid redundancy and keep the phrasing shorter, inner join conditions can be declared with a USING expression. This expression specifies a list of one or more columns:

SELECT ... FROM t1 JOIN t2 USING ( col1 ,... ) ...

Queries from the employee database would now look something like this:

SELECT ... FROM employee JOIN resource USING ( eid ) ...

To appear in a USING condition, the column name must exist in both tables. For each listed column name, the USING condition will test for equality between the pairs of columns. The resulting table will have only one instance of each listed column.

If this wasn’t concise enough, SQL provides an additional shortcut. A NATURAL JOIN is similar to a JOIN...USING, only it automatically tests for equality between the values of every column that exists in both tables:

SELECT ... FROM t1 NATURAL JOIN t2 ...

If the input tables have x and y columns, respectively, a JOIN...USING or a NATURAL JOIN will result in anywhere from max(x,y) to x+y columns.

Assuming eid is the only column identifier to appear in both the employee and resource table, our business query becomes extremely simple:

SELECT ... FROM employee NATURAL JOIN resource ...

NATURAL JOINs are convenient, as they are very concise, and allow changes to the key structure of various tables without having to update all of the corresponding queries. They can also be a tad dangerous unless you follow some discipline in naming your columns. Because none of the columns are explicitly named, there is no error checking in the sanity of the join. For example, if no matching columns are found, the JOIN will automatically (and without warning) degrade to a CROSS JOIN, just like any other INNER JOIN. Similarly, if two columns accidentally end up with the same name, a NATURAL JOIN will automatically include them in the join condition, if you wanted it or not.

OUTER JOIN

The OUTER JOIN is an extension of the INNER JOIN. The SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL. Currently, SQLite only supports the LEFT OUTER JOIN.

OUTER JOINs have a conditional that is identical to INNER JOINs, expressed using an ON, USING, or NATURAL keyword. The initial results table is calculated the same way. Once the primary JOIN is calculated, an OUTER join will take any unjoined rows from one or both tables, pad them out with NULLs, and append them to the resulting table. In the case of a LEFT OUTER JOIN, this is done with any unmatched rows from the first table (the table that appears to the left of the word JOIN).

Figure 5-3 shows an example of a LEFT OUTER JOIN.

Figure 5-3. An OUTER JOIN is just like an INNER JOIN, only unmatched rows are included in the results table. This shows a LEFT OUTER JOIN, where unmatched rows from the left (t1) table are added to the results.

The result of a LEFT OUTER JOIN will contain at least one instance of every row from the lefthand table. If the input tables have x and y columns, respectively, the resulting table will have no more than x+y columns (the exact number depends on which conditional is used). If the input tables have n and m rows, respectively, the resulting table can have anywhere from n to n·m rows.

Because they include unmatched rows, OUTER JOINs are often specifically used to search for unresolved or “dangling” rows.

SELECT ... FROM x AS x1 JOIN x AS x2 ON x1.col1 = x2.col2 ...

SELECT ... FROM ( SELECT ... ) AS sub ...

The WHERE clause is used to filter rows from the working table generated by the FROM clause. It is very similar to the WHERE clause found in the UPDATE and DELETE commands. An expression is provided that is evaluated for each row. Any row that causes the expression to evaluate to false or NULL is discarded. The resulting table will have the same number of columns as the original table, but may have fewer rows. It is not considered an error if the WHERE clause eliminates every row in the working table. Figure 5-4 shows how the WHERE clause works.

Figure 5-4. The WHERE clause filters rows based off a filter expression.

Some WHERE clauses can get quite complex, resulting in a long series of AND operators used to join sub-expressions together. Most filter for a specific row, however, searching for a specific key value.

The GROUP BY clause is used to collapse, or “flatten,” groups of rows. Groups can be counted, averaged, or otherwise aggregated together. If you need to perform any kind of inter-row operation that requires data from more than one row, chances are you’ll need a GROUP BY.

The GROUP BY clause provides a list of grouping expressions and optional collations. Very often the expressions are simple column references, but they can be arbitrary expressions. The syntax looks like this:

GROUP BY grouping_expression [COLLATE collation_name] [,...]

The grouping process has two steps. First, the GROUP BY expression list is used to arrange table rows into different groups. Once the groups are defined, the SELECT header (discussed in the next section) defines how those groups are flattened down into a single row. The resulting table will have one row for each group.

To split up the working table into groups, the list of expressions is evaluated across each row of the table. All of the rows that produce equivalent values are grouped together. An optional collation can be given with each expression. If the grouping expression involves text values, the collation is used to determine which values are equivalent. For more information on collations, see ORDER BY Clause.

Figure 5-5 shows how the rows are grouped together with the GROUP BY clause.

Figure 5-5. The GROUP BY clause groups rows based off a list of grouping expressions.

Once grouped together, each collection of rows is collapsed into a single row. This is typically done using aggregate functions that are defined in the SELECT heading, which is described in the next section, on page .

Because it is common to GROUP BY using expressions that are defined in the SELECT header, it is possible to simply reference SELECT heading expressions in the GROUP BY expression list. If a GROUP BY expression is given as a literal integer, that number is used as a column index in the result table defined by the SELECT header. Indexes start at one with the leftmost column. A GROUP BY expression can also reference a result column alias. Result column aliases are explained in the next section.

The SELECT header is used to define the format and content of the final result table. Any column you want to appear in the final results table must be defined by an expression in the SELECT header. The SELECT heading is the only required step in the SELECT command pipeline.

The format of the header is fairly simple, consisting of a list of expressions. Each expression is evaluated in the context of each row, producing the final results table. Very often the expressions are simple column references, but they can be any arbitrary expression involving column references, literal values, or SQL functions. To generate the final query result, the list of expressions is evaluated once for each row in the working table.

Additionally, you can provide a column name using the AS keyword:

SELECT expression [AS column_name] [,...]

Don’t confuse the AS keyword used in the SELECT header with the one used in the FROM clause. The SELECT header uses the AS keyword to assign a column name to one of the output columns, while the FROM clauses uses the AS keyword to assign a source-table alias.

Providing an output column name is optional, but recommended. The column name assigned to a results table is not strictly defined unless the user provides an AS column alias. If your application searches for a specific column name in the query results, be sure to assign a known name using AS. Assigning a column name will also allow other parts of the SELECT statement to reference an output column by name. Steps in the SELECT pipeline that are processed before the SELECT header, such as the WHERE and GROUP BY clause, can also reference output columns by name, just as long as the column expression does not contain an aggregate function.

If there is no working table (no FROM clause), the expression list is evaluated a single time, producing a single row. This row is then used as the working table. This is useful to test and evaluate standalone expressions.

Although the SELECT header appears to filter columns from the working table, much like the WHERE clause filters rows, this isn’t exactly correct. All of the columns from the original working table are still available to clauses that are processed after the SELECT header. For example, it is possible to sort the results (via ORDER BY, which is processed after the SELECT header) using a column that doesn’t appear in the query output.

It would be more accurate to say that the SELECT header tags specific columns for output. Not until the whole SELECT pipeline has been processed and the results are ready to be returned, are the unused columns stripped out. Figure 5-6 illustrates this point.

Figure 5-6. The SELECT heading tags specific columns for output. The unused columns are not removed until the query result is actually returned. Later SELECT clauses (such as ORDER BY) still have access to columns that are not part of the query result.

In addition to the standard expressions, SELECT supports two wildcards. A simple asterisk (*) will cause every user-defined column from every source table in the FROM clause to be output. You can also target a specific table (or table alias) using the format table_name.*. Although both of these wildcards are capable of returning more than one column, they can be mixed along with other expressions in the expression list. Wildcards cannot use a column alias, since they often return more than one column.

Be aware that the SELECT wildcards will not return any automatically generated ROWID columns. To return both the ROWID and the user-defined columns, simply ask for them both:

SELECT ROWID, * FROM table;

Wildcards do include any user-defined INTEGER PRIMARY KEY column that have replaced the standard ROWID column. See Primary keys for more information about how ROWID and INTEGER PRIMARY KEY columns interact.

In addition to determining the columns of the query result, the SELECT header determines how row-groups (produced by the GROUP BY clause) are flattened into a single row. This is done using aggregate functions. An aggregate function takes a column expression as input and aggregates, or combines, all of the column values from the rows of a group and produces a single output value. Common aggregate functions include count(), min(), max(), and avg(). Appendix E provides a full list of all the built-in aggregate functions.

Any column or expression that is not passed through an aggregate function will assume whatever value was contained in the last row of the group. However, because SQL tables are unordered, and because the SELECT header is processed before the ORDER BY clause, we don’t really know which row is “last.” This means the values for any unaggregated output will be taken from some essentially random row in the group. Figure 5-7 shows how this works.

Figure 5-7. The SELECT header will flatten any row groups created by GROUP BY. This figure shows how different columns from one row-group are flattened into an output row. Any value not computed by an aggregate function comes from the last row. Because column A was used as a GROUP BY expression, all the rows are known to have the same value, and it is safe to return. Column B is run through an aggregate function, and is also safe to return. Column C is not safe to return, as the order of the rows within a group is undefined.

In some cases, picking the value from a random row is not a bad thing. For example, if a SELECT header expression is also used as a GROUP BY expression, then we know that column has an equivalent value in every row of a group. No matter which row you choose, you will always get the same value.

Where you can run into trouble is when the SELECT header uses column references that were not part of the GROUP BY clause, nor were they passed through aggregate functions. In those cases there is no deterministic way to figure out what the output value will be. To avoid this, when using a GROUP BY clause, SELECT header expressions should only use column references as aggregate function inputs, or the header expressions should match those used in the GROUP BY clause.

Here are some examples. In this case all of the expressions are bare column references to help make things clear:

SELECT col1, sum( col2 ) FROM tbl GROUP BY col1;  -- well formed

This is a well formed statement. The GROUP BY clause shows that the rows are being grouped based off the values in col1. That makes it safe for col1 to appear in the SELECT header, since every row in a particular group will have an equivalent value in col1. The SELECT header also references col2, but it is fed into an aggregate function. The aggregate function will take all of the col2 values from different rows in the group and produce a logical answer—in this case, a numerical summation.

The result of this statement will be two columns. The first column will have one row for each unique value from col1. Each row of the second column will have the sum of all the values in col2 that are associated with the col1 value listed in the first result column. More detailed examples can be found at the end of the chapter.

This next statement is not well formed:

SELECT col1, col2 FROM tbl GROUP BY col1;  -- NOT well formed

As before, the rows are grouped based off the value in col1, which makes it safe for col1 to appear in the SELECT header. The column col2 appears bare, however, and not as an aggregate parameter. When this statement is run, the second return column will contain random values from the original col2 column.

Although every row within a group should have an equivalent value in a column or expression that was used as a grouping key, that doesn’t always mean the values are the exact same. If a collation such as NOCASE was used, different values (such as 'ABC' and 'abc') are considered equivalent. In these cases, there is no way to know the specific value that will be returned from a SELECT header. For example:

CREATE TABLE tbl ( t );
INSERT INTO tbl VALUES ( 'ABC' );
INSERT INTO tbl VALUES ( 'abc' );
SELECT t FROM tbl GROUP BY t COLLATE NOCASE;

This query will only return one row, but there is no way to know which specific value will be returned.

Finally, if the SELECT header contains an aggregate function, but the SELECT statement has no GROUP BY clause, the entire working table is treated as a single group. Since flattened groups always return one row, this will cause the query to return only one row—even if the working table contained no rows.

Functionally, the HAVING clause is identical to the WHERE clause. The HAVING clause consists of a filter expression that is evaluated for each row of the working table. Any row that evaluates to false or NULL is filtered out and removed. The resulting table will have the same number of columns, but may have fewer rows.

The main difference between the WHERE clause and the HAVING clause is where they appear in the SELECT pipeline. The HAVING clause is processed after the GROUP BY and SELECT clauses, allowing HAVING to filter rows based off the results of any GROUP BY aggregate. HAVING clauses can even have their own aggregates, allowing them to filter on aggregate results that are not part of the SELECT header.

HAVING clauses should only contain filter expressions that depend on the GROUP BY output. All other filtering should be done in the WHERE clause.

Both the HAVING and WHERE clauses can reference result column names defined in the SELECT header with the AS keyword. The main difference is that the WHERE clause can only reference expressions that do not contain aggregate functions, while the HAVING clause can reference any result column.

The DISTINCT keyword will scan the result set and eliminate any duplicate rows. This ensures the returned rows constitute a proper set. Only the columns and values specified in the SELECT header are considered when determining if a row is a duplicate or not. This is one of the few cases when NULLs are considered to have “equality,” and will be eliminated.

Because SELECT DISTINCT must compare every row against every other row, it is an expensive operation. In a well-designed database, it is also rarely required. Therefore, its usage is somewhat unusual.

The ORDER BY clause is used to sort, or order, the rows of the results table. A list of one or more sort expressions is provided. The first expression is used to sort the table. The second expression is used to sort any equivalent rows from the first sort, and so on. Each expression can be sorted in ascending or descending order.

The basic format of the ORDER BY clause looks like this:

ORDER BY expression [COLLATE collation_name] [ASC|DESC] [,...]

The expression is evaluated for each row. Very often the expression is a simple column reference, but it can be any expression. The resulting value is then compared against those values generated by other rows. If given, the named collation is used to sort the values. A collation defines a specific sorting order for text values. The ASC or DESC keywords can be used to force the sort in an ascending or descending order. By default, values are sorted in an ascending order using the default collation.

An ORDER BY expression can utilize any source column, including those that do not appear in the query result. Like GROUP BY, if an ORDER BY expression consists of a literal integer, it is assumed to be a column index. Column indexes start on the left with 1, so the phrase ORDER BY 2 will sort the results table by its second column.

Because SQLite allows different datatypes to be stored in the same column, sorting can get a bit more interesting. When a mixed-type column is sorted, NULLs will be sorted to the top. Next, integer and real values will be mixed together in proper numeric order. The numbers will be followed by text values, with BLOB values at the end. There will be no attempt to convert types. For example, a text value holding a string representation of a number will be sorted in with the other text values, and not with the numeric values.

In the case of numeric values, the natural sort order is well defined. Text values are sorted by the active collation, while BLOB values are always sorted using the BINARY collation. SQLite comes with three built-in collation functions. You can also use the API to define your own collation functions. The three built-in collations are:

While ORDER BY is extremely useful, it should only be used when it is actually needed—especially with very large result tables. Although SQLite can sometimes make use of an index to calculate the query results in order, in many cases SQLite must first calculate the entire result set and then sort it, before rows are returned. In that case, the intermediate results table must be held in memory or on disk until it is fully computed and can then be sorted.

Overall, there are plenty of situations where ORDER BY is justified, if not required. Just be aware there can be some significant costs involved in its use, and you shouldn’t get in the habit of tacking it on to every query “just because.”

The LIMIT and OFFSET clauses allow you to extract a specific subset of rows from the final results table. LIMIT defines the maximum number of rows that will be returned, while OFFSET defines the number of rows to skip before returning the first row. If no OFFSET is provided, the LIMIT is applied to the top of the table. If a negative LIMIT is provided, the LIMIT is removed and will return the whole table.

There are three ways to define a LIMIT and OFFSET:

LIMIT limit_count
LIMIT limit_count OFFSET offset_count
LIMIT offset_count, limit_count

Here are some examples. Notice that the OFFSET value defines how many rows are skipped, not the position of the first row:

LIMIT 10             -- returns the first 10 rows (rows 1 - 10)
LIMIT 10 OFFSET 3    -- returns rows  4 - 13
LIMIT 3  OFFSET 20   -- returns rows 21 - 23
LIMIT 3, 20          -- returns rows  4 - 23 (different from above!)

Although it is not strictly required, you usually want to define an ORDER BY if you’re using a LIMIT. Without an ORDER BY, there is no well-defined order to the result, making the limit and offset somewhat meaningless.

The SELECT command provides a great deal of flexibility, but there are times when a single SELECT command cannot fully express a query. To help with these situations, SQL supports subqueries. A subquery is nothing more than a SELECT statement that is embedded in another SELECT statement. Subqueries are also known as sub-selects.

Subqueries are most commonly found in the FROM clause, where they act as a computed source table. This type of subquery can return any number of rows or columns, and is similar to creating a view or running the query, recording the results into a temporary table, and then referencing that table in the main query. The main advantage of using an in-line subquery is that the query optimizer is able to merge the subquery into the main SELECT statement and look at the whole problem, often leading to a more efficient query plan.

To use a subquery in the FROM clause, simply enclose it in parentheses. The following two statements will produce the same output:

SELECT * FROM TblA AS a JOIN TblB AS b;
SELECT * FROM TblA AS a JOIN (SELECT * FROM TblB) AS b;

Subqueries can show up in other places, including general expressions used in any SQL command. The EXISTS and IN operators both utilize subqueries. In fact, you can use a subquery any place an expression expects a list of literal values (a subquery cannot be used to generate a list of identifiers, however). See Appendix D for more details on SQL expressions.

In addition to subqueries, multiple SELECT statements can be combined together to form a compound SELECT. Compound SELECTs use set operators on the rows generated by a series of SELECT statements.

In order to combine correctly, each SELECT statement must generate the same number of columns. The column names from the first SELECT statement will be used for the overall result. Only the last SELECT statement can have an ORDER BY, LIMIT or OFFSET clause, which get applied to the full compound result table. The syntax for a compound SELECT looks like this:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ...

compound_operator

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ...

[...]

ORDER BY ... LIMIT ... OFFSET ...

Multiple compound operators can be used to include additional SELECT statements.

SQLite supports the UNION, UNION ALL, INTERSECT, and EXCEPT compound operators. Figure 5-8 shows the result of each operator.

Figure 5-8. The compound operators UNION ALL, UNION, INTERSECT and EXCEPT.

Once all the compound operators have been combined, any trailing ORDER BY, LIMIT, and OFFSET is applied to the final result table. In the case of compound SELECT statements, the expressions present in any ORDER BY clause must be exactly match one of the result columns, or use a column index.

There are two styles of join notation. The style shown earlier in this chapter is known as explicit join notation. It is named such because it uses the keyword JOIN to explicitly describe how each table is joined to the next. The explicit join notation is also known as ANSI join notation, as it was introduced when SQL went through the standardization process.

The older, original join notation is known as implicit join notation. Using this notation, the FROM clause is simply a comma-separated list of tables. The tables in the list are combined using a Cartesian product and the relevant rows are extracted with additional WHERE conditions. In effect, it degrades every join to a CROSS JOIN and then moves the join conditions out of the FROM clause and into the WHERE clause.

This first statement uses the explicit join notation we learned earlier in the chapter:

SELECT ... 
   FROM employee JOIN resource ON ( employee.eid = resource.eid ) 
   WHERE ...

This is the same statement written with the implicit join notation:

SELECT ...
   FROM employee, resource
   WHERE employee.eid = resource.eid AND ...

There is no performance difference between the two notations: it is purely a matter of syntax.

In general, the explicit notion (the first one) has become the standard way of doing things. Most people find the explicit notation easier to read, making the intent of the query more transparent and easier to understand. I’ve always felt the explicit notation is a bit cleaner, as it puts the complete join specification into the FROM clause, leaving the WHERE clause free for query-specific filters. Using the explicit notation, the FROM clause (and the FROM clause alone) fully and independently specifies what you’re selecting “from.”

The explicit notation also lets you be much more specific about the type and order of each JOIN. In SQLite, you must use the explicit notation if you want an OUTER JOIN—the implicit notation can only be used to indicate a CROSS JOIN or INNER JOIN.

If you’re learning SQL for the first time, I would strongly suggest you become comfortable with the explicit notation. Just be aware that there is a great deal of SQL code out there (including older books and tutorials) using the older, implicit notation.

Let’s start with a simple select that returns all of the columns and rows in table x. The SELECT * syntax returns all columns by default:

sqlite> SELECT * FROM x;

a           b         
----------  ----------
1           Alice     
2           Bob       
3           Charlie   

We can also return expressions, rather than just columns:

sqlite> SELECT d, d*d AS dSquared FROM y;

d           dSquared          
----------  ------------
3.14159     9.8695877281
2.71828     7.3890461584
1.61803     2.6180210809

Now some joins. By default, the bare keyword JOIN indicates an INNER JOIN. However, when no additional condition is put on the JOIN, it reverts to a CROSS JOIN. As a result, all three of these queries produce the same results. The last line uses the implicit join notation.

sqlite> SELECT * FROM x JOIN y;
sqlite> SELECT * FROM x CROSS JOIN y;
sqlite> SELECT * FROM x, y;

a           b           c           d         
----------  ----------  ----------  ----------
1           Alice       1           3.14159   
1           Alice       1           2.71828   
1           Alice       2           1.61803   
2           Bob         1           3.14159   
2           Bob         1           2.71828   
2           Bob         2           1.61803   
3           Charlie     1           3.14159   
3           Charlie     1           2.71828   
3           Charlie     2           1.61803   

In the case of a cross join, every row in table a is matched to every row in table y. Since both tables had three rows and two columns, the result set has nine rows (3·3) and four columns (2+2).

Next, a fairly simple inner join using a basic ON join condition:

sqlite> SELECT * FROM x JOIN y ON a = c;

a           b           c           d         
----------  ----------  ----------  ----------
1           Alice       1           3.14159   
1           Alice       1           2.71828   
2           Bob         2           1.61803   
      

This query still generates four columns, but only those rows that fulfill the join condition are included in the result set.

The following statement requires the columns to be qualified, since both table x and table z have an a column. Notice that two different a columns are returned, one from each source table:

sqlite> SELECT * FROM x JOIN z ON x.a = z.a;

a           b           a           e         
----------  ----------  ----------  ----------
1           Alice       1           100       
1           Alice       1           150       
3           Charlie     3           300       

If we use a NATURAL JOIN or the USING syntax, the duplicate column will be eliminated. Since both table x and table z have only column a in common, both of these statements produce the same output:

sqlite> SELECT * FROM x JOIN z USING ( a );
sqlite> SELECT * FROM x NATURAL JOIN z;

a           b           e         
----------  ----------  ----------
1           Alice       100       
1           Alice       150       
3           Charlie     300       

A LEFT OUTER JOIN will return the same results as an INNER JOIN, but will also include rows from table x (the left/first table) that were not matched:

sqlite> SELECT * FROM x LEFT OUTER JOIN z USING ( a );

a           b           e         
----------  ----------  ----------
1           Alice       100       
1           Alice       150       
2           Bob         [NULL]    
3           Charlie     300       

In this case, the Bob row from table x has no matching row in table z. Those column values normally provided by table z are padded out with NULL, and the row is then included in the result set.

It is also possible to JOIN multiple tables together. In this case we join table x to table y, and then join the result to table z:

sqlite> SELECT * FROM x JOIN y ON x.a = y.c LEFT OUTER JOIN z ON y.c = z.a;

a           b           c           d           a           e         
----------  ----------  ----------  ----------  ----------  ----------
1           Alice       1           3.14159     1           100       
1           Alice       1           3.14159     1           150       
1           Alice       1           2.71828     1           100       
1           Alice       1           2.71828     1           150       
2           Bob         2           1.61803     [NULL]      [NULL]    

If you don’t see what is going on here, work through the joins one at a time. First look at what FROM x JOIN y ON x.a = y.c will produce (shown in one of the previous examples). Then look at how this result set would combine with table z using a LEFT OUTER JOIN.

Our last join example shows a self-join, where a table is joined against itself. This creates two unique instances of the same table and necessitates the use of table aliases:

sqlite> SELECT * FROM x AS x1 JOIN x AS x2 ON x1.a + 1 = x2.a;

a           b           a           b
----------  ----------  ----------  ----------
1           Alice       2           Bob
2           Bob         3           Charlie

Also, notice that the join condition is a more arbitrary expression, rather than being a simple test of column references.

Moving on, the WHERE clause is used to filter rows. We can pick out a specific row:

sqlite> SELECT * FROM x WHERE b = 'Alice';

a           b         
----------  ----------
1           Alice     

Or a range of values:

sqlite> SELECT * FROM y WHERE d BETWEEN 1.0 AND 3.0;

c           d         
----------  ----------
1           2.71828   
2           1.61803   

In this case, the WHERE expression references the output column by its assigned name:

sqlite> SELECT c, d, c+d AS sum FROM y WHERE sum < 4.0;

c           d           sum       
----------  ----------  ----------
1           2.71828     3.71828   
2           1.61803     3.61803   

Now let’s look at a few GROUP BY statements. Here we group table z by the a column. Since there are three unique values in z.a, the output has three rows. Only the grouping a=1 has more than one row, however. We can see this in the count() values returned by the second column:

sqlite> SELECT a, count(a) AS count FROM z GROUP BY a;

a           count     
----------  ----------
1           2         
3           1         
9           1         

This is a similar query, only now the second output column represents the sum of all the z.e values in each group:

sqlite> SELECT a, sum(e) AS total FROM z GROUP BY a;

a           total     
----------  ----------
1           250       
3           300       
9           900       

We can even compute our own average and compare that to the avg() aggregate:

sqlite> SELECT a, sum(e), count(e),
   ...>    sum(e)/count(e) AS expr, avg(e) AS agg
   ...>   FROM z GROUP BY a;

a           sum(e)      count(e)    expr        agg       
----------  ----------  ----------  ----------  ----------
1           250         2           125         125.0     
3           300         1           300         300.0     
9           900         1           900         900.0     

A HAVING clause can be used to filter rows based off the results of the sum() aggregation:

sqlite> SELECT a, sum(e) AS total FROM z GROUP BY a HAVING total > 500;

a           total     
----------  ----------
9           900       

The output can also be sorted. Most of these examples already look sorted, but that’s mostly by chance. The ORDER BY clause enforced a specific order:

sqlite> SELECT * FROM y ORDER BY d;

c           d         
----------  ----------
2           1.61803   
1           2.71828   
1           3.14159   

Limits and offsets can also be applied to pick out specific rows from an ordered result. Conceptually, these are fairly simple, however.

These tables and queries are available as part of the book download. Feel free to load the data into sqlite3 and try out different queries. Don’t worry about creating SELECT statements that use every available clause. Start with simple queries where you can understand all the steps, then start to combine clauses to build larger and more complex queries.

When designing a table, you usually start by specifying the primary key. The primary key consists of one or more columns that uniquely identify each row of a table. In a sense, the primary key values represent the fundamental identity of each row in the table. The primary key columns identify what the table is all about. All the other columns should provide supporting data that is directly relevant to the primary key.

Sometimes the primary key is an actual unique data value, such as a room number or a hostname. Very often the primary key is simply an arbitrary identification number, such as an employee or student ID number. The important point is that primary keys must be unique over every possible entry in the table, not just the rows that happen to be in there right now. This is why names are normally not used as primary keys—in a large group of people, it is too easy to end up with duplicate (or very similar) names.

While there is nothing particularly special about the columns that make up a primary key, the keys themselves play a very important role in the design and use of a database. Their role as a unique identifier for each row makes them analogous to the key of a hash table, or the key to a dictionary class of data structure. They are essentially “lookup” values for the rows of a table.

A primary key can be identified in the CREATE TABLE command. Explicitly identifying the primary key will cause the database system to automatically create a UNIQUE index across all of the primary key columns. Declaring the primary key also allows for some syntax shortcuts when establishing relationships between tables.

For example, this table definition defines the employee_id field to be a primary key:

CREATE TABLE employee (
    employee_id   INTEGER   PRIMARY KEY   NOT NULL,
    name          TEXT   NOT NULL
    /* ...etc... */
);

For more information on the syntax used to define a primary key, see the section Primary keys.

In schema documentation, primary keys are often indicated with the abbreviation “PK.” It is also common to double underline primary keys when drawing out tables, as shown in Figure 6-1.

Figure 6-1. Primary keys are sometimes identified with the abbreviation PK. It is also common to use a double underline when diagramming the table.

Many database queries use a table’s primary key as either the input or output. The database might be asked to return the row with a given key, such as, “return the record for employee #953.” It is also common to ask for collections of keys, such as, “gather the ID values for all employees hired more than two years ago.” This set of keys might then be joined to another table as part of a report.

In addition to identifying each row in a table, primary keys are also central to joining tables together. Since the primary key acts as a unique row identifier, you can create a reference to a specific row by recording the row’s primary key. This is known as a foreign key. A foreign key is a copy or recording of a primary key, and is used as a reference or pointer to a different (or “foreign”) row, most often in a different table.

Like the primary key, columns that make up a foreign key can be identified within the CREATE TABLE command. In this example, we define the format of a task assignment. Each task gets assigned to a specific employee by referencing the employee_id field from the employee table:

CREATE TABLE task_assignment (
    task_assign_id   INTEGER   PRIMARY KEY,
    task_name        TEXT      NOT NULL,
    employee_id      INTEGER   NOT NULL   REFERENCES employee( employee_id )
    /* ...etc... */
);

The REFERENCES constraint indicates that this column is a foreign key. The constraint indicates which table is referenced and, optionally, which column. If no column is indicated, the foreign key will reference the primary key (meaning the column reference used in the prior example is not required, since employee_id is the primary key of the employee table). The vast majority of foreign keys will reference a primary key, but if a column other than the primary key is used, that column must have a UNIQUE constraint, or it must have a single-column UNIQUE index.

A foreign key can also be defined as a table constraint. In that case, there may be multiple local columns that refer to multiple columns in the referenced table. The referenced columns must be a multicolumn primary key, or they must otherwise have a multicolumn UNIQUE index. A foreign key definition can include several other optional parts. For the full syntax, see CREATE TABLE in Appendix C.

Unlike a table’s own primary key, foreign keys are not required to be unique. This is because multiple foreign key values (multiple rows) in one table may refer to the same row in another table. This is known as a one-to-many relationship. Please see the section One-to-Many Relationships. Foreign keys are often marked with the abbreviation “FK,” as shown in Figure 6-2.

Figure 6-2. Foreign keys are copies of the primary key from another row. Foreign keys act as references or pointers to other rows. They are often identified with the abbreviation FK.

Declaring foreign keys in the table definition allows the database to enforce foreign key constraints. Foreign key constraints are used to keep foreign key references in sync. Among other things, foreign key constraints can prevent “dangling references” by requiring that all foreign key values correctly match a row value from the columns of the referenced table. Foreign keys can also be set to NULL. A NULL clearly marks the foreign key as unassigned, which is a bit different than having an invalid value. In many cases, unassigned foreign keys don’t fit the design of the database. In that case, the foreign key columns should be declared with a NOT NULL constraint.

Using our previous example, foreign key constraints would require that every task_assignment.employee_id element needs to contain the value of a valid employee.employee_id. By default, a NULL foreign key would also be allowed, but we’ve defined the task_assignment.employee_id column with a NOT NULL constraint. This demands that every task reference a valid employee.

Native foreign key support was added in SQLite 3.6.19, but is turned off by default. You must use the PRAGMA foreign_keys command to turn on foreign key constraints. This was done to avoid problems with existing applications and database files. A future version of SQLite may have foreign key constraints enabled by default. If your application is dependent on this setting, it should explicitly turn it on or off.

Modifications to either the foreign key table or the referenced table can potentially cause violations of the foreign key constraint. For example, if a statement attempted to update a task_assignment.employee_id value to an invalid employee_id, the foreign key constraint would be violated. Similarly, if an employee row was assigned a new employee_id value, any existing task_assignment references that point to the old value would become invalid. This would also violate the foreign key constraint.

If the database detects a change that would cause a foreign key violation, there are several actions that can be taken. The default action is to prohibit the change. For example, if you attempted to delete an employee that still had tasks assigned to them, the delete would fail. You would need to delete the tasks or transfer them to a different employee before you could delete the original employee.

Other conflict resolutions are also available. For example, using an ON DELETE CASCADE foreign key definition, deleting an employee would cause the database to automatically delete any tasks assigned to that employee. For more information on conflict resolutions and other advanced foreign key options, please see the SQLite website. Up-to-date documentation on SQLite’s support for foreign keys can be found at http://www.sqlite.org/foreignkeys.html.

Correctly defining foreign keys is one of the most critical aspects of data integrity and security. Once defined, foreign key constraints make sure that data relationships remain valid and consistent.

If you look at most database designs, you’ll notice that a large number of the tables have a generic ID column that is used as the primary key. The ID is typically an arbitrary integer value that is automatically assigned as new rows are inserted. The number may be incrementally assigned, or it might be assigned from a sequence mechanism that is guaranteed to never assign the same ID number twice.

When an SQLite column is defined as an INTEGER PRIMARY KEY, that column will replace the hidden ROWID column that acts as the root column of every table. Using an INTEGER PRIMARY KEY allows for some significant performance enhancements. It also allows SQLite to automatically assign sequenced ID values. For more details, see Primary keys.

At first, a generic ID field might seem like a design cheat. If each table should have a specific and well-defined role, then the primary key should reflect what makes any given row unique—reflecting, in part, the essential definition of the items in a table. Using a generic and arbitrary ID to define that uniqueness seems to be missing the point.

From a theoretical standpoint, that may be correct, but this is one of those areas where theory bumps into reality, and reality usually wins.

Practically speaking, many datasets don’t have a truly unique representation. For example, the names of people are not sufficiently unique to be used as database keys. Names are reasonably unique, and they do a fair job at identifying individuals in person, but they lack the inherent and complete uniqueness that good database design demands. Names also change from time to time, such as when people get married.

The more you dig around, the more you’ll find that the world is full of data like this. Data that is sufficiently unique and stable for casual use, but not truly, absolutely unique or fixed enough to use as a smart database key. In these situations, the best solution is to simply use an arbitrary ID that the database designer has total control over, even if it is meaningless outside of the database. This type of key is known as a surrogate key.

There are also situations when the primary key consists of three or four (or more!) columns. This is somewhat rare, but there are situations when it does come up. If you’ve got a large multicolumn primary key in the middle of a complex set of relationships, it can be a big pain to create and match all those multicolumn foreign keys. To simplify such situations, it is often easier to simply create an arbitrary ID and use that as the primary key.

Using an arbitrary ID is also useful if the customary primary key is physically large. Because each foreign key is a full copy of the primary key, it is unwise to use a lengthy text value or BLOB as the primary key. Rather, it would be better to use an arbitrary identifier and simply reference to the identifier.

One final comment on key names. There is often a temptation to name a generic ID field something simple, such as id. After all, if you’ve got an employee table, it might seem somewhat redundant to name the primary key employee_id; you end up with a lot of column references that read employee.employee_id, when it seems that employee.id is clear enough.

Well, by itself, it is clear enough, but primary keys tend to show up in other tables as foreign keys. While employee.employee_id might be slightly redundant, the name task_assignment.employee_id is not. That name also gives you significant clues about the column’s function (a foreign key) and what table and column it references (the employee_id column, which is the PK column of the employee table). Using the same name for primary keys and foreign keys makes the inherent meaning and linkage a lot more obvious. It also allows shortcuts, such as the NATURAL JOIN or JOIN...USING( ) syntax. Both of these forms require that matching columns have the exact same name.

Using a more explicit name also avoids the problem of having multiple tables, each with a column named id. Such a common name can make things somewhat confusing if you join together three or four tables. While I wouldn’t necessarily prefix every column name, keeping primary key names unique within a database (and using the exact same name for foreign keys) can make the intent of the database design a lot more clear.

The biggest stumbling block for beginning database developers is that they don’t create enough tables. Less experienced developers tend to view tables as large and monumental structures. There tends to be a feeling that tables are important, and that each table needs a fair number of columns containing significant amounts of data to justify its existence. If a design change calls for a new column or set of data points, there tends to be a strong desire to lump everything together into large centralized tables rather than breaking things apart into logical groups.

The “tables must be big” mentality will quickly lead to poor designs. While you will have a few large, significant tables at the core of most designs, a typical design will also have a fair number of smaller auxiliary tables that may only hold two or three columns of data. In fact, in some cases you may find yourself building tables that consist of nothing but external references to other tables. Creating a new table is the only means you have to define a new data structure or data container, so any time you find yourself needing a new container, that’s going to indicate a new table.

Every table should have a well-defined and clear role, but that doesn’t always mean it will be big or stuffed with data.

The most basic kind of inter-table relationship is the one-to-one relationship. As you can guess, this type of relationship establishes a reference from a single row in one table to a single row in another table. Most commonly, one-to-one relationships are represented by having a foreign key in one table reference a primary key in another table. If the foreign key column is made unique, only one reference will be allowed. As Figure 6-3 illustrates, a unique foreign key creates a one-to-one relationship between the rows of the two tables.

Figure 6-3. In a one-to-one relationship, table B has a foreign key that references the primary key of table A. This associates every non-NULL foreign key in table B with some row in table A.

In the strictest sense, a foreign key relationship is a one-to-(zero or one) relationship. When two tables are involved in a one-to-one relationship, there is nothing to enforce that every primary key has an incoming reference from a foreign key. For that matter, if the foreign key allows NULLs, there may be unassigned foreign keys as well.

One-to-one relationships are commonly used to create detail tables. As the name implies, a detail table typically holds details that are linked to the records in a more prominent table. Detail tables can be used to hold data that is only relevant to a small subsection of the database application. Breaking the detail data apart from the main tables allows different sections of the database design to evolve and grow much more independently.

Detail tables can also be helpful when extended data only applies to a limited number of records. For example, a website might have a sales_items table that lists common information (price, inventory, weight, etc.) for all available items. Type-specific data can then be held in detail tables, such as cd_info for CDs (artist name, album name, etc.) or dvd_info (directors, studio, etc.) for DVDs. Although the sales_items table would have a unique one-to-one relationship with every type-specific info table, each individual row in the sales_item table would be referenced by only one type of detail table.

One-to-one relationships can also be used to isolate very large data elements, such as BLOBs. Consider an employee database that contains an image of each employee. Due to the data storage and I/O overhead, it might be unwise to include a photo column directly in the employee table, but it is easy to create a photo table that references the employee table. Consider these two tables:

CREATE TABLE employee ( 
    employee_id  INTEGER   NOT NULL   PRIMARY KEY,
    name         TEXT   NOT NULL
    /* ...etc... */
);

CREATE TABLE employee_photo (
    employee_id  INTEGER   NOT NULL   PRIMARY KEY
                 REFERENCES employee,
    photo_data   BLOB
    /* ...etc... */
);

This example is a bit unique, because the employee_photo.employee_id column is both the primary key for the employee_photo table, as well as a foreign key to the employee table. Since we want a one-to-one relationship, it makes sense to just pair up primary keys. Because this foreign key does not allow NULL keys, every employee_photo row must be matched to a specific employee row. The database does not guarantee that every employee will have a matching employee_photo, however.

One-to-many relationships establish a link between a single row in one table to multiple rows in another table. This is done by expanding a one-to-one relationship, allowing one side to contain duplicate keys. One-to-many relationships are often used to associate lists or arrays of items back to a single row. For example, multiple shipping addresses can be linked to a single account. Unless otherwise specified, “many” usually means “zero or more.”

The only difference between a one-to-one relationship and a one-to-many relationship is that the one-to-many relationship allows for duplicate foreign key values. This allows multiple rows in one table (the many table) to refer back to a single row in another table (the One table). In building a one-to-many relationship, the foreign key must always be on the many side.

Figure 6-4 illustrates the relationship in more detail.

Figure 6-4. When building a one-to-many relationship, the primary keys must be unique, but foreign key columns may contain duplicate values. This means a one-to-many relationship must have the foreign key on the many side. Here we see a foreign key value in Table B refer to the primary key in Table A.

Any time you find yourself wondering how to stuff an array or list into the column of a table, the solution is to separate the array elements out into their own table and establish a one-to-many relationship.

The same is true any time you start to contemplate sets of columns, such as item0, item1, item2, etc., that are all designed to hold instances of the same type of value. Such designs have inherent limits, and the insert, update, and removal process becomes quite complex. It is much easier to just break the data out into its own table and establish a proper relationship.

One example of a one-to-many relationship is music albums, and the songs they contain. Each album has a list of songs associated with that album. For example:

CREATE TABLE albums (
        album_id INTEGER   NOT NULL   PRIMARY KEY,
        album_name TEXT );

CREATE TABLE tracks (
        track_id INTEGER   NOT NULL   PRIMARY KEY,
        track_name TEXT,
        track_number INTEGER,
        track_length INTEGER, -- in seconds
        album_id INTEGER   NOT NULL   REFERENCES albums );

Each album and track has a unique ID. Each track also has a foreign key reference back to its album. Consider:

INSERT INTO albums VALUES ( 1, "The Indigo Album" );
INSERT INTO tracks VALUES ( 1, "Metal Onion", 1, 137, 1 );
INSERT INTO tracks VALUES ( 2, "Smooth Snake", 2, 212, 1 );
INSERT INTO tracks VALUES ( 3, "Turn A", 3, 255, 1 );

INSERT INTO albums VALUES ( 2, "Morning Jazz" );
INSERT INTO tracks VALUES ( 4, "In the Bed", 1, 214, 2 );
INSERT INTO tracks VALUES ( 5, "Water All Around", 2, 194, 2 );
INSERT INTO tracks VALUES ( 6, "Time Soars", 3, 265, 2 );
INSERT INTO tracks VALUES ( 7, "Liquid Awareness", 4, 175, 2 );

To get a simple list of tracks and their associated album, we just join the tables back together. We can also sort by both album name and track number:

sqlite> SELECT album_name, track_name, track_number
   ...>   FROM albums JOIN tracks USING ( album_id )
   ...>   ORDER BY album_name, track_number;

album_name    track_name  track_number
------------  ----------  ------------
Morning Jazz  In the Bed  1           
Morning Jazz  Water All   2           
Morning Jazz  Time Soars  3           
Morning Jazz  Liquid Awa  4           
The Indigo A  Metal Onio  1           
The Indigo A  Smooth Sna  2           
The Indigo A  Turn A      3           

We can also manipulate the track groupings:

sqlite> SELECT album_name, sum( track_length ) AS runtime, count(*) AS tracks
   ...>   FROM albums JOIN tracks USING ( album_id )
   ...>   GROUP BY album_id;

album_name        runtime     tracks    
----------------  ----------  ----------
The Indigo Album  604         3         
Morning Jazz      848         4         

This query groups the tracks based off their album, and then aggregates the track data together.

The next step is the many-to-many relationship. A many-to-many relationship associates one row in the first table to many rows in the second table while simultaneously allowing individual rows in the second table to be linked to multiple rows in the first table. In a sense, a many-to-many relationship is really two one-to-many relationships built across each other.

Figure 6-5 shows the classic many-to-many example of people and groups. One person can belong to many different groups, while each group is made up of many different people. Common operations are to find all the groups a person belongs to, or to find all the people in a group.

Figure 6-5. A many-to-many relationship is like two one-to-many relationships built across each other. In this example, each individual person can be a member of one or more groups, while each group can contain one or more people.

Many-to-many relationships are a bit more complex than other relationships. Although the tables have a many-to-many relationship with each other, the entries in both tables must remain unique. We cannot duplicate either person rows or group rows for the purpose of matching keys. This is a problem, since each foreign key can only reference one row. This makes it impossible for a foreign key of one table (such as a group) to refer to multiple rows of another table (such as people).

To solve this, we go back to the advice from before: if you need to add a list to a row, break out that list into its own table and establish a one-to-many relationship with the new table. You cannot directly represent a many-to-many relationship with only two tables, but you can take a pair of one-to-many relationships and link them together. The link requires a small table, known as a link table, or bridge table, that sits between the two many tables. Each many-to-many relationship requires a unique bridge table.

In its most basic form, the bridge table consists of nothing but two foreign keys—one for each of the tables it is linking together. Each row of the bridge table links one row in the first many table to one row in the second many table. In our People-to-Groups example, the link table defines a membership of one person in one group. This is illustrated in Figure 6-6.

Figure 6-6. Implementing a many-to-many relationship requires a bridge table. In this example, each row of the bridge table represents a membership of one person in one group. Note that the primary key of the bridge table is a multicolumn key over (p_id, g_id). This keeps memberships unique.

The logical representation of a many-to-many relationship is actually a one-to-many-to-one relationship, with the bridge table (acting as a record of membership) in the middle. Each person has a one-to-many relationship with memberships, just as groups have a one-to-many relationship with memberships. In addition to binding the two tables together, the bridge table can also hold additional information about the membership itself, such as a first-joined date or an expiration date.

Here are three tables. The people and groups table are obvious enough. The p_g_bridge table acts as the bridge table between the people table and groups table. The two columns are both foreign key references, one to people and one to groups. Establishing a primary key across both foreign key columns ensures the memberships remain unique:

CREATE TABLE people ( pid INTEGER   PRIMARY KEY, name TEXT, ... );
CREATE TABLE groups ( gid INTEGER   PRIMARY KEY, name TEXT, ... );
CREATE TABLE p_g_bridge( 
        pid INTEGER   NOT NULL   REFERENCES people,
        gid INTEGER   NOT NULL   REFERENCES groups,
        PRIMARY KEY ( pid, gid )
);

This query will list all the groups that a person belongs to:

SELECT groups.name AS group_name
  FROM people JOIN p_g_bridge USING ( pid ) JOIN groups USING ( gid )
  WHERE people.name = search_person_name;

The query simply links people to groups using the bridge table, and then filters out the appropriate rows.

We don’t always need all three tables. This query counts all the members of a group without utilizing the people table:

SELECT name AS group_name, count(*) AS members
  FROM groups JOIN p_g_bridge USING ( gid )
  GROUP BY gid;

There are many other queries we can do, like finding all the groups that have no members:

SELECT name AS group_name
  FROM groups LEFT OUTER JOIN p_g_bridge USING ( gid )
  WHERE pid IS NULL;

This query performs an outer join from the groups table to the p_g_bridge table. Any unmatched group rows will be padded out with a NULL in the p_g_bridge.pid column. Since this column is marked NOT NULL, we know the only possible way for a row to be NULL in that column is from the outer join, meaning the row is unmatched to any memberships. A very similar query could be used to find any people that have no memberships.

Hierarchies and other tree-style data relationships are common and frequently show up in database design. Modeling one in a database can be a challenge because you tend to ask different types of questions when dealing with hierarchies.

Common tree operations include finding all the sub-nodes under a given node, or querying the depth of a given node. These operations have an inherent recursion in them—a concept that SQL doesn’t support. This can lead to clumsy queries or complex representations.

There are two common methods for representing a tree relation using database tables. The first is the adjacency model, which uses a simple representation that is easy to modify but complex to query. The other common representation is the nested set, which allows relatively simple queries, but at the cost of a more complex representation that can be expensive to modify.

CREATE TABLE tree (
    node   INTEGER   NOT NULL   PRIMARY KEY,
    name   TEXT,
    parent INTEGER   REFERENCES tree );

A
  A.1
    A.1.a
  A.2
    A.2.a
    A.2.b
  A.3

INSERT INTO tree VALUES ( 1, 'A',     NULL );
INSERT INTO tree VALUES ( 2, 'A.1',   1 );
INSERT INTO tree VALUES ( 3, 'A.1.a', 2 );
INSERT INTO tree VALUES ( 4, 'A.2',   1 );
INSERT INTO tree VALUES ( 5, 'A.2.a', 4 );
INSERT INTO tree VALUES ( 6, 'A.2.b', 4 );
INSERT INTO tree VALUES ( 7, 'A.3',   1 );

sqlite> SELECT n.name AS node, p.name AS parent
   ...>   FROM tree AS n JOIN tree AS p ON n.parent = p.node;

node        parent    
----------  ----------
A.1         A         
A.1.a       A.1       
A.2         A         
A.2.a       A.2       
A.2.b       A.2       
A.3         A         

CREATE TABLE nest (
    name  TEXT,
    lower INTEGER   NOT NULL   UNIQUE,
    upper INTEGER   NOT NULL   UNIQUE,
    CHECK ( lower < upper ) );

A( A.1( A.1.a( ) ), A.2( A.2.a( ), A.2.b( )  ), A.3(  )  )
 1    2      3 4 5     6      7 8       9 10 11    12 13 14

INSERT INTO nest VALUES ( 'A',      1, 14 );
INSERT INTO nest VALUES ( 'A.1',    2,  5 );
INSERT INTO nest VALUES ( 'A.1.a',  3,  4 );
INSERT INTO nest VALUES ( 'A.2',    6, 11 );
INSERT INTO nest VALUES ( 'A.2.a',  7,  8 );
INSERT INTO nest VALUES ( 'A.2.b',  9, 10 );
INSERT INTO nest VALUES ( 'A.3',   12, 13 );

SELECT name FROM nest WHERE lower + 1 = upper;

sqlite> SELECT n.name AS name, count(*) AS depth
   ...>   FROM nest AS n JOIN nest AS p
   ...>     ON p.lower <= n.lower AND p.upper >= n.upper
   ...>   GROUP BY n.name;

name        depth     
----------  ----------
A           1         
A.1         2         
A.1.a       3         
A.2         2         
A.2.a       3         
A.2.b       3         
A.3         2         

The normalization process is useful for two reasons. First, normalization specifies design criteria that can act as a guide in the design process. If you have a set of tables that are proving to be difficult to work with, that often points to a deeper design problem or assumption. The normalization process provides a set of rules and conditions that can help identify trouble spots, as well as provide possible solutions to reorganize the data in a more consistent and clean fashion.

The other advantage, which shows up more at runtime, is that data integrity is much easier to enforce and maintain in a normalized database. Although the overall database design is often more complex (i.e., more tables), the individual parts are usually much simpler and fill more clearly defined roles. This often translates to better INSERT, UPDATE, and DELETE performance, since changes are often smaller and more localized.

Localizing data is a core goal of data normalization. Most of the normal forms deal with eliminating redundant or replicated data so that each unique token of data is stored once—and only once—in the database. Everything else simply references that definitive copy. This makes updates easier, since there is only one place an update needs to be applied, but it also makes the data more consistent, as it is impossible for multiple copies of the data to become out of sync with each other. When working on a schema design, a question you should constantly ask yourself is, “If this piece of data changes, how many different places will I need to make that change?” If the answer is anything other than one, chances are you’re not in Normal Form.

Normalizing a database and spreading the data out into different tables means that queries usually involve joining several tables back together. This can occasionally lead to performance concerns, especially for complex reports that require data from a large number of tables. These concerns can sometimes lead to the process of denormalization, where duplicate copies of the same data are intentionally introduced to reduce the number of joins required for common queries. This is typically done on systems that are primarily read-only, such as data-warehouse databases, and is often done by computing temporary tables from properly normalized source data.

While a large number of joins can lead to performance concerns, database optimization is just like code optimization—don’t start too early and don’t make assumptions. In general, the advantages of normalization far outweigh the costs. A correct database that runs a tad slower is infinitely more useful than a very fast database that returns incorrect or inconsistent answers.

The First Normal Form, or 1NF, is the lowest level of normalization. It is primarily concerned with making sure a table is in the proper format. There are three conditions that must be met for a table to be in 1NF.

The first condition relates to ordering. To be in 1NF, the individual rows of a table cannot have any meaningful or inherent order. Each row should be an isolated, standalone record. The meaning of a value in one row cannot depend on any of the data values from neighboring rows, either by insertion order, or by some sorted order. This condition is usually easy to meet, as SQL does not guarantee any kind of row ordering.

The second condition is uniqueness. Every row within a 1NF table must be unique, and must be unique by those columns that hold meaningful data for the application. For example, if the only difference between two rows is the database-maintained ROWID column, then the rows aren’t really unique. However, it is perfectly fine to consider an arbitrary sequence ID (such as an INTEGER PRIMARY KEY) to be part of the application data. This condition establishes that the table must have some type of PRIMARY KEY, consisting of one or more columns that creates a unique definition of what the table represents.

The third and final condition for 1NF requires that every column of every row holds one (and only one) logical value that cannot be broken down any further. The concern is not with compound types, such as dates (which might be broken down into integer day, month, and year values) but with arrays or lists of logical values. For example, you shouldn’t be recording a text value that contains a comma-separated list of logical, independent values. Arrays or lists should be broken out into their own one-to-many relationships.

The Second Normal Form, or 2NF, deals with compound keys (multicolumn keys) and how other columns relate to such keys. 2NF has only one condition: every column that is not part of the primary key must be relevant to the primary key as a whole, and not just a sub-part of the key.

Consider a table that lists all the conference rooms at a large corporate campus. At minimum, the conf_room table has columns for building_numb and room_numb. Taken together, these two columns will uniquely identify any conference room across the whole campus, so that will be our compound primary key.

Next, consider a column like seating_capacity. The values in this column are directly dependent on each specific conference room. That, by definition, makes the column dependent on both the building number and the room number. Including the seating_capacity column will not break 2NF.

Now consider a column like building_address. This column is dependent on the building_numb column, but it is not dependent on the room_numb column. Since building_address is dependent on only part of the primary key, including this column in the conf_room table would break 2NF.

Because 2NF is specifically concerned with multicolumn keys, any table with a single-column primary key that is in 1NF is automatically in 2NF.

To recognize a column that might be breaking 2NF, look for columns that have duplicate values. If the duplicate values tend to line up with duplicate values in one of the primary key columns, that is a strong indication of a problem. For example, the building_address column will have a number of duplicate values (assuming most buildings have more than one conference room). The duplicate address values can be matched to duplicate values in the building_numb column. This alignment shows how the address column is tied to only the building_numb column specifically, and not the whole primary key.

The Third Normal Form, or 3NF, extends the 2NF to eliminate transitive key dependencies. A transitive dependency is when A depends on B, and B depends on C, and therefore A depends on C. 3NF requires that each nonprimary key column has a direct (nontransitive) dependency on the primary key.

For example, consider an inventory database that is used to track laptops at a small business. The laptop table will have a primary key that uniquely identifies each laptop, such as an inventory control number. It is likely the table would have other columns that include the make and model of the machine, the serial number, and perhaps a purchase date. For our example, the laptop table will also include a responsible_person_id column. When an employee is assigned a laptop, their employee ID number is put in this column.

Within a row, the value of the responsible_person_id column is directly dependent on the primary key. In other words, each individual laptop is assigned a specific responsible person, making the values in the responsible_person_id column directly dependent on the primary key of the laptop table.

Now consider what happens when we add a column like responsible_person_email. This is a column that holds the email address of the responsible person. The value of this column is still dependent on the primary key of the laptop table. Each individual laptop has a specific responsible_person_email field that is just as unique as the responsible_person_id field.

The problem is that the values in the responsible_person_email column are not directly dependent on an individual laptop. Rather, the email column is tied to the responsible_person_id, and the responsible_person_id is, in turn, dependent on the individual laptop. This transitive dependency breaks 3NF, indicating that the responsible_person_email column doesn’t belong there.

In the employee table, we will also find both a person_id column and an email column. This is perfectly acceptable if the person_id is the primary key (likely). That would make the email column directly dependent on the primary key, keeping the table in 3NF.

A good way to recognize columns that may break 3NF is to look for pairs or sets of unrelated columns that need to be kept in sync with each other. Consider the laptop table. If a system was reassigned to a new person, you would always update both the responsible_person_id column and the responsible_person_email column. The need to keep columns in sync with each other is a strong indication of a dependency to each other, rather than to the primary key.

We’re not going to get into the details of BCNF, or the Fourth or Fifth (or beyond) Normal Forms, other than to mention that the Fourth and Fifth Normal Forms start to deal with inter-table relationships and how different tables interact with each other. Most database designers make a solid effort to get everything into 3NF and then stop worrying about it. It turns out that if you get the hang of things and tend to turn out table designs that are in 3NF, chances are pretty good that your tables will also meet the conditions for 4NF and 5NF, if not higher. To a large extent, the higher Normal Forms are formal ways of addressing some edge cases that are somewhat unusual, especially in simpler designs.

Although the conditions of the Normal Forms build on each other, the typical design process doesn’t actually iterate over the individual Forms. You don’t sit down with a new design and alter it until everything is 1NF, just to turn around and muck with the design until everything is 2NF, and so on, in a isolated step-by-step manner. Once you understand the ideas and concepts behind the First, Second, and Third Normal Forms, it becomes second nature to design directly to 3NF. Stepping over the conditions one at a time can help you weed out especially difficult trouble spots, but it doesn’t take long to gain a sense of when a design looks clean and when something “just ain’t right.”

The core concept to remember is that each table should try to represent one and only one thing. The primary key(s) for that table should uniquely and inherently identify the concept behind the table. All other columns should provide supporting data specific to that one concept. When speaking of the first three Normal Forms in a 1982 CACM article, William Kent wrote that each non-key column “ . . . must provide a fact about the key, the whole key, and nothing but the key.” If you incorporate only one formal aspect of database theory into your designs, that would be a great place to start.

Each index is associated with a specific table. Indexes can be either single column or multicolumn, but all the columns of a given index must belong to the same table. There is no limit to the number of indexes a table can have, nor is there a limit on the number of indexes a column can belong to. You cannot create an index on a view or on a virtual table.

Internally, the rows of a normal table are stored in an indexed structure. SQLite uses a B-Tree for this purpose, which is a specific type of multi-child, balanced tree. The details are unimportant, other than understanding that as rows are inserted into the tree, the rows are sorted, organized, and optimized, so that a row with a specific, known ROWID can be retrieved relatively directly and quickly.

When you create an index, the database system creates another tree structure to hold the index data. Rather than using the ROWID column as the sort key, the tree is sorted and organized using the column or columns you’ve specified in the index definition. The index entry consists of a copy of the values from each of the indexed columns, as well as a copy of the corresponding ROWID value. This allows an indexed entry to be found very quickly using the values from the indexed columns.

If we have a table like this:

CREATE TABLE tbl ( a, b, c, d );

And then create an index that looks like this:

CREATE INDEX idx_tbl_a_b ON tbl ( a, b );

The database generates an internal data structure that is conceptually similar to:

SELECT a, b, ROWID FROM tbl ORDER BY a, b;

If SQLite needs to quickly find all the rows where, for example, a = 45, it can use the sorted index to quickly jump to that range of values and extract the relevant index entries. If it’s looking for the value of b, it can simply extract that from the index and be done. If we need any other value in the row, it needs to fetch the full row. This is done by looking up the ROWID. The last value of any index entry is the ROWID of its corresponding table row. Once SQLite has a list of ROWID values for all rows where a = 45, it can efficiently look up those rows in the original table and retrieve the full row. If everything works correctly, the process of looking up a small set of index entries, and then using those to look up a small set of table rows, will be much faster and more efficient than doing a full table scan.

To have a positive impact on query performance, indexes must be diverse. The cost of fetching a single row through an index is significantly higher than fetching a single row through a table scan. To reduce the overall cost, an index must overcome this overhead by eliminating the vast majority of row fetches. By significantly reducing the number of row lookups, the total query cost can be reduced, even if the individual row lookups are more expensive.

The break-even point for index performance is somewhere in the 10% to 20% range. Any query that fetches more rows from a table will do better with a table scan, while any query that fetches fewer rows will see an improvement by using an index. If an index cannot isolate rows to this level, there is no reason for it to be there. In fact, if the query optimizer mistakenly uses an index that returns a large percentage of rows, the index can reduce performance and slow things down.

This creates two concerns. First, if you’re trying to improve the performance of a query that fetches a moderate percentage of rows, adding an index is unlikely to help. An index can only improve performance if it is used to target a focused number of rows.

Second, even if the query is asking for a specific value, this can still lead to a higher percentage of fetched rows if the indexed columns are not reasonably unique. For example, if a column only has four unique values, a successful query will never fetch fewer than approximately 25% of the rows (assuming a reasonable distribution of values or queries). Adding an index to this type of column will not improve performance. Creating an index on a true/false column would be even worse.

When the query optimizer is trying to figure out if it should use an index or not, it typically has very little information to go on. For the most part, it assumes that if an index exists, it must be a good index, and it will tend to use it. The ANALYZE command can help mitigate this by providing statistical information to the query optimizer (see ANALYZE for more details), but keeping that updated and well maintained can be difficult in many environments.

To avoid problems, you should avoid creating indexes on columns that are not reasonably unique. Indexing such columns rarely provides any benefit, and it can confuse the query optimizer.

When you declare one or more columns to be a PRIMARY KEY, the database system automatically creates a unique index over those columns. The fundamental purpose of this index is to enforce the UNIQUE constraint that is implied with every PRIMARY KEY. It also happens that many database operations typically involve the primary key, such as natural joins, or conditional lookups in UPDATE or DELETE commands. Even if the index wasn’t required to enforce the UNIQUE constraint, chances are good you would want an index over those columns anyway.

There are further advantages to using an INTEGER PRIMARY KEY. As we’ve discussed, when an INTEGER PRIMARY KEY is declared, that column replaces the automatic ROWID column, and becomes the root column for the tree. In essence, the column becomes the index used to store the table itself, eliminating the need for a second, external index.

INTEGER PRIMARY KEY columns also provide better performance than standard indexes. INTEGER PRIMARY KEYs have direct access to the full set of row data. A normal index simply references the ROWID value, which is then looked up in the table root. INTEGER PRIMARY KEYs can provide indexed lookups, but skip this second lookup step, and directly access the table data.

If you’re using generic row ID values, it is worth the effort to define them as INTEGER PRIMARY KEY columns. Not only will this reduce the size of the database, it can make your queries faster and more efficient.

When creating a multicolumn index for performance purposes, the column order is very important. If an index has only one column, that column is used as the sort key. If additional columns are specified in the index definition, the additional columns will be used as secondary, tertiary, etc., sort keys. All of the data is sorted by the first column, then any groups of duplicate values are further sorted by the second column, and so on. This is very similar to a typical phonebook. Most phone books sort by last name. Any group of common last names is then sorted by first name, and then by middle name or initial.

In order to utilize a multicolumn index, a query must contain conditions that are able to utilize the sort keys in the same order they appear in the index definition. If the query does not contain a condition that keys off the first column, the index cannot be used.

Consider looking up a name in the phonebook. You can use a phonebook to quickly find the phone number for “Jennifer T. Smith.” First, you look up the last name “Smith.” Then you refine the search, looking for the first name, “Jennifer,” and finally the middle initial “T” (if required). This sequence should allow you to focus in on a specific entry very quickly.

A phonebook isn’t much use to quickly find all the people with the first name “Jennifer.” Even though the first name is part of the phonebook index, it isn’t the first column of the index. If our query cannot provide a specific condition for the first column of a multicolumn index, the index cannot be used, and our only option is to do a full scan. At that point it is usually more efficient to do a full scan on the table itself, rather than the index.

It is not necessary to utilize every column, only that you utilize the columns in order. If you’re looking up a very unique name in the phone book, you may not need to search off the first name or middle initial. Or, perhaps your query is to find every “Smith.” In that case, the conditions of the query are satisfied with just the first column.

In the end, you need to be very careful when considering the order of a multicolumn index. The additional columns should be viewed as a refinement on the first column, not a replacement. A single multicolumn index is very different from multiple single-column indexes. If you have different queries that use different columns as the main lookup condition, you may be better off with multiple small indexes, rather than one large multicolumn index.

Multicolumn indexes are very useful in some situations, but there are limitations on when they can be effectively used. In some cases, it is more appropriate to create multiple single-column indexes on the same table.

This too has limitations. You cannot create a single-column index on every column of a table and expect the query system to mix and match whatever indexes it needs. Once a subset of rows is extracted from a table (via index or full table scan), that set of rows conceptually becomes an independent temporary table that is no longer part of the original table. At that point, any index associated with the source table becomes unavailable.

In general, the query optimizer can only utilize one index per table-instance per query. Multiple indexes on a single table only make sense if you have a different queries that extract rows using different columns (or have multiple UNIQUE constraints). Different queries that key off different columns can pick the index that is most appropriate, but each individual query will only use one index per table-instance. If you want a single query to utilize multiple indexed columns, you need a multicolumn index.

The major exception is a series of OR conditions. If a WHERE clause has a chain of OR conditions on one or more columns of the same table, then there are cases when SQLite may go ahead and use multiple indexes for the same table in a single query.

For all their power, indexes are often a source of great frustration. The right index will result in a huge performance boost, while the wrong index can significantly slow things down. All indexes add cost to table modifications and add bulk to the database size. A well-placed index is often worth the overhead costs, but it can be difficult to understand what makes a well-placed index.

To complicate matters, you don’t get to tell the query optimizer when or how to use your indexes—the query planner makes its own decisions. Having everything be automatic means that queries will work no matter what, but it can also make it difficult to determine if an index is being used. It can be even more difficult to figure out why an index is being ignored.

This essentially leaves the database designer in the position of second-guessing the query optimizer. Changes and optimizations must be searched for on something of a trial-and-error basis. To make things worse, as your application changes and evolves, changes in the query structure or patterns can cause a change in index use. All in all, it can be a difficult situation to manage.

Traditionally, this is where the role of the DBA, or Database Administrator, comes in. This person is responsible for the care and feeding of a large database server. They do things like monitor query efficiency, adjust tuning parameters and indexes, and make sure all the statistical data is kept up to date. Unfortunately, that whole idea is somewhat contrary to what SQLite is all about.

So how do you maintain performance? For starters, have a solid design. A well-crafted and normalized database is going to go a long way toward defining your access patterns. Taking the time to correctly identify and define your primary keys will allow the database system to create appropriate indexes and give the query optimizer (and future developers) some insight into your design and intentions.

Also remember that (with the exception of UNIQUE constraints) a database will operate correctly without any indexes. It might be slow, but all the answers will be correct. This lets you worry about design first, and performance later. Once you have a working design, it is always possible to add indexes after the fact, without needing to alter your table structure or your query commands.

The queries of a fully normalized database will typically have quite a few JOIN operations. Nearly all of these joins are across primary and foreign keys. Primary keys are automatically indexed, but in most cases you need to manually create indexes on your foreign keys. With those in place, your database should already have the most critical indexes defined.

Start from here. Measure the performance of the different types of queries your application uses and find the problem areas. Looking at these queries, try to find columns where an index might boost performance. Look for any constraints and conditions that may benefit from an index, especially in join conditions that reference nonkey columns. Use EXPLAIN and EXPLAIN QUERY PLAN to understand how SQLite is accessing the data. These commands can also be used to verify if a query is using an index or not. For more information, see EXPLAIN in Appendix C. You can also use the sqlite3_stmt_status() function to get a more measured understanding of statement efficiency. See sqlite3_stmt_status() in Appendix G for more details.

All in all, you shouldn’t get too hung up on precise index placement. You may need a few well-placed indexes to deal with larger tables, but the stock PRIMARY KEY indexes do surprisingly well in most cases. Further index placement should be considered performance tuning and shouldn’t be worried about until an actual need is identified. Never forget that indexes have cost, so you shouldn’t create them unless you can identify a need.

The most common misconception is to think of tables as instances of a compound data structure. A table looks a whole lot like an array or a dynamic list, so it is easy to make this mistake.

Tables should be thought of as type definitions. You should never use a named table as a data organizer or record grouping. Rather, each table definition should be treated like a data structure definition or a class definition. SQL DDL commands such as CREATE TABLE are conceptually similar to those C/C++ header files that define an application’s data structures and classes. The table itself should be considered a global management pool for all instances of that type. If you need a new instance of that type, you simply insert a new row. If you need to group or catalog sets of instances, do that with key associations, not by creating new tables.

If you ever find yourself creating a series of tables with identical definitions, that’s usually a big warning. Any time your application uses string manipulation to programmatically build table names, that’s also a big warning—especially if the table names are derived from values stored elsewhere in the database.

Another stumbling block is the proper use of keys. Keys are very similar to pointers. A primary key is used to identify a unique instance of a data structure. This is similar to the address of a record. Anything that wants to reference that record needs to record its address as a pointer or, in the cases of databases, as a foreign key. Foreign keys are essentially database pointers.

The trick is that database references are backwards. Rather than pointers that indicate ownership (“I manage that”), foreign keys indicate a type of possession (“I am managed by that”).

In C or C++, if a main data record manages a list of sub-records, the main data record would have some kind of pointer list. Each pointer would reference a specific sub-record associated with this main record. If you are dealing with the main data record and need the list of sub-records, you simply look at the pointer list.

Databases do it the other way around. In a one-to-many relationship, the main record (the “one” side row) would simply have a primary key. All the sub-records (the “many” side rows) would have foreign keys that point back at the main record. If you are dealing with the main record and need the list of sub-records, you ask the database system to look at the global pool of all subrecord instances and return just those subrecords that are managed by this main record.

This tends to make application developers uncomfortable. This is not the way traditional programming language data structures tend to be organized, and the idea of searching a large global record pool just to retrieve a small number of records tends to raise all kinds of performance concerns. Thankfully, this is exactly the kind of thing that databases are very good at doing.

My final design advice is more general. As with data structures or classes, the fundamental idea behind a table is that it should represent instances of one single idea or “thing.” It might represent a set of nouns or things, such as people, or it might represent verbs or actions, such as a transaction log. Tables can even represent less concrete things, such as a many-to-many bridge table that records membership. But no matter what it is, each table should have one, and only one, clearly defined role in the database. Normally the problem isn’t too many tables, it is too few.

If the meaning of one field is ever dependent on the value of another field, the design is heading in a bad direction. Two different meanings should have two different tables (or two different columns).

The C API for SQLite 3 includes a dozen-plus data structures, a fair number of constants, and well over one hundred different function calls. While the API is somewhat large, using it doesn’t have to be complex. A fair number of the functions are highly specialized and infrequently used by most developers. Many of the remaining functions are simple variations of the same basic operation. For example, there are a dozen variations on the sqlite3_value_xxx() function, such as sqlite3_value_int(), sqlite3_value_double(), and sqlite3_value_text(). All of these functions perform the same basic operation and can be considered simple type variations of the same basic interface.

All public API function calls and datatypes have the prefix sqlite3_, indicating they are part of version 3.x of the SQLite product. Most of the constants, such as error codes, use the prefix SQLITE_. The design and API differences between SQLite 2.x and 3.x were significant enough to warrant a complete change of all API names and structures. The depth of these changes required anyone upgrading from SQLite 2 to SQLite 3 to modify their application, so changing the names of the API functions only helped keep the names distinct and keep any version questions clear. The distinct names also allowed applications that were in transition to link to both libraries at the same time.

In addition to the sqlite3_ prefix, public function calls can be identified by the use of lowercase letters and underscores in their names. Private functions use run-together capitalized words (also known as CamelCase). For example, sqlite3_create_function() is a public API function (used to register a user-defined SQL function), while sqlite3CreateFunc() is an internal function that should never be called directly. Internal functions are not in the public header file, are not documented, and are subject to change at any time.

The stability of the public interface is extremely important to the SQLite development team. An existing API function call will not be altered once it has been made public. The only possible exceptions are brand new interfaces that are marked experimental, and even experimental interfaces tend to become fairly solid after a few releases.

If a revised version of a function call is needed, the newer function will generally be introduced with the suffix _v2. For example, when a more flexible version of the existing sqlite3_open() function was introduced, the old version of the function was retained as is and the new, improved sqlite3_open_v2() was introduced. Although no _v3 (or higher) functions currently exist, it is possible they may be introduced in the future.

By adding a new function, rather than modifying the parameters of an existing function, new code could take advantage of the newer features, while existing, unmodified code could continue to link against updated versions of the SQLite library. The use of a different function name also means that if a newer application is ever accidentally linked against an older version of the library, the result will be a link error rather than a program crash, making it much easier to track down and resolve the problem.

There are a number of API functions that have a 16 variant. For instance, both an sqlite3_column_text() function and an sqlite3_column_text16() function are available. The first requests a text value in UTF-8 format, while the second will request a text value in UTF-16.

All of the strings in an SQLite database file are stored using the same encoding. SQLite database files support the UTF-8, UTF-16LE, and UTF-16BE encodings. A database’s encoding is determined when the database is created.

Regardless of the database, you can insert or request text values in either UTF-8 or UTF-16. SQLite will automatically convert text values between the database encoding and the API encoding. The UTF-16 encoding passed by the 16 APIs will always be in the machine’s native byte order. UTF-16 buffers use a void* C data type. The wchar_t data type is not used, as its size is not fixed, and not all platforms define a 16-bit type.

Most of the string- and text-related functions have some type of length parameter. SQLite does not assume input text values are null-terminated, so explicit lengths are often required. These lengths are always given in bytes, not characters, regardless of the string encoding.

This difference is important to keep in mind when using international strings.

SQLite follows the convention of returning integer error codes in any situation when there is a chance of failure. If data needs to be passed back to the function caller, it is returned through a reference parameter.

In all cases, if a function succeeds, it will return the constant SQLITE_OK, which happens to have the value zero. If something went wrong, API functions will return one of the standard error codes to indicate the nature of the error.

More recently, a set of extended error codes were introduced. These provide a more specific indication of what went wrong. However, to keep things backwards compatible, these extended codes are only available when you activate them.

The situation is complex enough to warrant its own discussion later in the chapter. It will be much easier to explain the different error codes once you’ve had a chance to see how the API works. See Result Codes and Error Codes for more details.

I also have to give the standard “do as I say, not as I do” caveat about properly checking error codes and return results. The example code in this chapter and elsewhere in this book tends to have extremely terse (as in, almost none at all) error checking. This is done to keep the examples short and clear. Needless to say, this isn’t the best approach for production code. When working with your own code, do the right thing and check your error codes.

Although the native SQLite API is often referred to as a C/C++ API, technically the interface is only available in C. As mentioned in Building, the SQLite source code is strictly C based, and as such can only be compiled with a C compiler. Once compiled, the library can be easily linked to, and called from, both C and C++ code, as well as any other language that follows the C linking conventions for your platform.

Although the API is written in C, it has a distinct object-like flavor. Most of the program state is held in a series of opaque data structures that act like objects. The most common data structures are database connections and prepared statements. You should never directly access the fields of these data structures. Instead, functions are provided to create, destroy, and manipulate these structures in much the same way that object methods are used to manipulate object instances. This results in an API design that has similar feelings to an object-oriented design. In fact, if you download one of the third-party C++ wrappers, you’ll notice that the wrappers tend to be rather thin, owing most of their structure to the underlying C functions and the data structures.

It is important that you allow SQLite to allocate and manage its own data structures. The design of the API means that you should never manually allocate one of these structures, nor should you put these structures on the stack. The API provides calls to internally allocate the proper data structures, initialize them, and return them. Similarly, for every function that allocates a data structure, there is some function that is used to clean it up and release it. As with memory management, you need to be sure these calls are balanced, and that every data structure that is created is eventually released.

The core of the SQLite API focuses on opening database connections, preparing SQL statements, binding parameter values, executing statements, and finally stepping through the results. These procedures are the focus of this chapter.

There are also interfaces to create your own SQL functions, load dynamic modules, and create code-driven virtual tables. We’ll be covering some of these more advanced interfaces in other chapters.

Beyond that, there are a fair number of management and customization functions. Not all of these are covered in the main part of the book, but a reference for the full API can be found in Appendix G. If the description of a function leaves you with additional questions, be sure to check that appendix for more specific details.

Database connections are allocated and established with one of the sqlite3_open_xxx() commands. These pass back a database connection in the form of an sqlite3 data structure. There are three variants:

For new code, it is recommended that you use the call sqlite3_open_v2(). The newer call allows more control over how the database is opened and processed.

The use of the double pointer may be a bit confusing at first, but the idea behind it is simple enough. The pointer-to-a-pointer is really nothing more than a pointer that is passed by reference. This allows the function call to modify the pointer that is passed in. For example:

sqlite3    *db = NULL;
rc = sqlite3_open_v2( "database.sqlite3", &db, SQLITE_OPEN_READWRITE, NULL );
/* hopefully, db now points to a valid sqlite3 structure */ 

Note that db is an sqlite3 pointer (sqlite3*), not an actual sqlite3 structure. When we call sqlite3_open_xxx() and pass in the pointer reference, the open function will allocate a new sqlite3 data structure, initialize it, and set our pointer to point to it.

This approach, including the use of a pointer reference, is a common theme in the SQLite APIs that are used to create or initialize something. They all basically work the same way and, once you get the hang of them, they are pretty straightforward and easy to use.

There is no standard file extension for an SQLite3 database file, although .sqlite3, .db, and .db3 are popular choices. The extension .sdb should be avoided, as this extension has special meaning on some Microsoft Windows platforms, and may suffer from significantly slower I/O performance.

The string encoding used by a database file is determined by the function that is used to create the file. Using sqlite3_open() or sqlite3_open_v2() will result in a database with the default UTF-8 encoding. If sqlite3_open16() is used to create a database, the default string encoding will be UTF-16 in the native byte order of the machine. You can override the default string encoding with the SQL command PRAGMA encoding. See encoding in Appendix F for more details.

In addition to recognizing standard filenames, SQLite recognizes a few specialized filename strings. If the given filename is a NULL pointer or an empty string (""), then an anonymous, temporary, on-disk database is created. An anonymous database can only be accessed through the database connection that created it. Each call will create a new, unique database instance. Like all temporary items, this database will be destroyed when the connection is closed.

If the filename :memory: is used, then a temporary, in-memory database is created. In-memory databases live in the database cache and have no backing store. This style of database is extremely fast, but requires sufficient memory to hold the entire database image in memory. As with anonymous databases, each open call will create a new, unique, in-memory database, making it impossible for more than one database connection to access a given in-memory database.

In-memory databases make great structured caches. It is not possible to directly image an in-memory database to disk, but you can copy the contents of an in-memory database to disk (or disk to memory) using the database backup API. See the section sqlite3_backup_init() in Appendix G for more details.

To close and release a database connection, call sqlite3_close().

Any pointer returned by a call to sqlite3_open_xxx(), including a NULL pointer, can be passed to sqlite3_close(). This function verifies there are no outstanding changes to the database, then closes the file and frees the sqlite3 data structure. If the database still has nonfinalized statements, the SQLITE_BUSY error will be returned. In that case, you need to correct the problem and call sqlite3_close() again.

In most cases, sqlite3_open_xxx() will return a pointer to an sqlite3 structure, even when the return code indicates a problem. This allows the caller to retrieve an error message with sqlite3_errmsg(). (See Result Codes and Error Codes.) In these situations, you must still call sqlite3_close() to free the sqlite3 structure.

Here is the outline of a program that opens a database, performs some operations, and then closes it. Most of the other examples in this chapter will build from this example by inserting code into the middle:

#include "sqlite3.h"
#include <stdlib.h>

int main( int argc, char **argv )
{
    char            *file = ""; /* default to temp db */
    sqlite3         *db = NULL;
    int             rc = 0;

    if ( argc > 1 )
        file = argv[1];

    sqlite3_initialize( );
    rc = sqlite3_open_v2( file, &db, SQLITE_OPEN_READWRITE | 
                                     SQLITE_OPEN_CREATE, NULL );
    if ( rc != SQLITE_OK) {
        sqlite3_close( db );
        exit( -1 );
    }

    /*  perform database operations  */

    sqlite3_close( db );
    sqlite3_shutdown( );
}

The default filename is an empty string. If passed to sqlite3_open_xxx(), this will result in a temporary, on-disk database that will be deleted as soon as the database connection is closed. If at least one argument is given, the first argument will be used as a filename. If the database does not exist, it will be created and then opened for read/write access. It is then immediately closed.

If this example is run using a new filename, it will not create a valid database file. The SQLite library delays writing the database header until some actual data operation is performed. This “lazy” initialization gives an application the chance to adjust any relevant pragmas, such as the text encoding, page size, and database file format, before the database file is fully created.

The life cycle of a prepared statement is a bit complex. Unlike database connections, which are typically opened, used for some period of time, and then closed, a statement can be in a number of different states. A statement might be prepared, but not run, or it might be in the middle of processing. Once a statement has run to completion, it can be reset and re-executed multiple times before eventually being finalized and released.

The life cycle of a typical sqlite3_stmt looks something like this (in pseudo-code):

/* create a statement from an SQL string */
sqlite3_stmt *stmt = NULL;
sqlite3_prepare_v2( db, sql_str, sql_str_len, &stmt, NULL );

/* use the statement as many times as required */
while( ... )
{
    /* bind any parameter values */
    sqlite3_bind_xxx( stmt, param_idx, param_value... );
    ...

    /* execute statement and step over each row of the result set */
    while ( sqlite3_step( stmt ) == SQLITE_ROW )
    {
        /* extract column values from the current result row */
        col_val = sqlite3_column_xxx( stmt, col_index );
        ...
    }

    /* reset the statement so it may be used again */
    sqlite3_reset( stmt );
    sqlite3_clear_bindings( stmt );  /* optional */
}

/* destroy and release the statement */
sqlite3_finalize( stmt );
stmt = NULL;

The prepare process converts an SQL command string into a prepared statement. That statement can then have values bound to any statement parameters. The statement is then executed, or “stepped through.” In the case of a query, each step will make a new results row available for processing. The column values of the current row can then be extracted and processed. The statement is stepped through, row by row, until no more rows are available.

The statement can then be reset, allowing it to be re-executed with a new set of bindings. Preparing a statement can be somewhat costly, so it is a common practice to reuse statements as much as possible. Finally, when the statement is no longer in use, the sqlite3_stmt data structure can be finalized. This releases any internal resources and frees the sqlite3_stmt data structure, effectively deleting the statement.

To convert an SQL command string into a prepared statement, use one of the sqlite3_prepare_xxx() functions:

If the length parameter is negative, the length will be automatically computed by the prepare call. This requires that the command string be properly null-terminated. If the length is positive, it represents the maximum number of bytes that will be parsed. For optimal performance, provide a null-terminated string and pass a valid length value that includes the null-termination character. If the SQL command string passed to sqlite3_prepare_xxx() consists of only a single SQL statement, there is no need to terminate it with a semicolon.

Once a statement has been prepared, but before it is executed, you can bind parameter values to the statement. Statement parameters allow you to insert a special token into the SQL command string that represents an unspecified literal value. You can then bind specific values to the parameter tokens before the statement is executed. After execution, the statement can be reset and new parameter values can be assigned. This allows you to prepare a statement once and then re-execute it multiple times with different parameter values. This is commonly used with commands, such as INSERT, that have a common structure but are repeatedly executed with different values.

Parameter binding is a somewhat in-depth topic, so we’ll get back to that in the next section. See Bound Parameters for more details.

Preparing an SQL statement causes the command string to be parsed and converted into a set of byte-code commands. This byte-code is fed into SQLite’s Virtual Database Engine (VDBE) for execution. The translation is not a consistent one-to-one affair. Depending on the database structure (such as indexes), the query optimizer may generate very different VDBE command sequences for similar SQL commands. The size and flexibility of the SQLite library can be largely attributed to the VDBE architecture.

To execute the VDBE code, the function sqlite3_step() is called. This function steps through the current VDBE command sequence until some type of program break is encountered. This can happen when a new row becomes available, or when the VDBE program reaches its end, indicating that no more data is available.

In the case of a SELECT query, sqlite3_step() will return once for each row in the result set. Each subsequent call to sqlite3_step() will continue execution of the statement until the next row is available or the statement reaches its end.

The function definition is quite simple:

If the first call to sqlite3_step() returns SQLITE_DONE, it means that the statement was successfully run, but there was no result data to make available. This is the typical case for most commands, other than SELECT. If sqlite3_step() is called repeatedly, a SELECT command will return SQLITE_ROW for each row of the result set before finally returning SQLITE_DONE. If a SELECT command returns no rows, it will return SQLITE_DONE on the first call to sqlite3_step().

There are also some PRAGMA commands that will return a value. Even if the return value is a simple scalar value, that value will be returned as a one-row, one-column result set. This means that the first call to sqlite3_step() will return SQLITE_ROW, indicating result data is available. Additionally, if PRAGMA count_changes is set to true, the INSERT, UPDATE, and DELETE commands will return the number of rows they modified as a one-row, one-column integer value.

Any time sqlite3_step() returns SQLITE_ROW, new row data is available for processing. Row values can be inspected and extracted from the statement using the sqlite3_column_xxx() functions, which we will look at next. To resume execution of the statement, simply call sqlite3_step() again. It is common to call sqlite3_step() in a loop, processing each row until SQLITE_DONE is returned.

Rows are returned as soon as they are computed. In many cases, this spreads the processing costs out across all of the calls to sqlite3_step(), and allows the first row to be returned reasonably quickly. However, if the query has a GROUP BY or ORDER BY clause, the statement may be forced to first gather all of the rows within the result set before it is able to complete the final processing. In these cases, it may take a considerable time for the first row to become available, but subsequent rows should be returned very very quickly.

Any time sqlite3_step() returns the code SQLITE_ROW, a new result set row is available within the statement. You can use the sqlite3_column_xxx() functions to inspect and extract the column values from this row. Many of these functions require a column index parameter (cidx). Like C arrays, the first column in a result set always has an index of zero, starting from the left.

These sqlite3_column_xxx() functions allow your code to get an idea of what the available row looks like. Once you’ve figured out the correct value type, you can extract the value with one of these typed sqlite3_column_xxx() functions. All of these functions take the same parameters: a statement pointer and a column index.

There is no sqlite3_column_null() function. There is no need for one. If the native datatype is NULL, there is no additional value or state information to extract.

Any pointers returned by these functions become invalid if another call to any sqlite3_column_xxx() function is made using the same column index, or when sqlite3_step() is next called. Pointers will also become invalid if the statement is reset or finalized. SQLite will take care of all the memory management associated with these pointers.

If you request a datatype that is different from the native value, SQLite will attempt to convert the value. Table 7-1 describes the conversion rules used by SQLite.

Some conversions are done in place, which can cause subsequent calls to sqlite3_column_type() to return undefined results. That’s why it is important to call sqlite3_column_type() before trying to extract a value, unless you already know exactly what datatype you want.

Although numeric values are returned directly, text and BLOB values are returned in a buffer. To determine how large that buffer is, you need to ask for the byte count. That can be done with one of these two functions.

Be aware that these functions can cause a data conversion in text values. That conversion can invalidate any previously returned pointer. For example, if you call sqlite3_column_text() to get a pointer to a UTF-8 encoded string, and then call sqlite3_column_bytes16() on the same column, the internal column value will be converted from a UTF-8 encoded string to a UTF-16 encoded string. This will invalidate the character pointer that was originally returned by sqlite3_column_text().

Similarly, if you first call sqlite3_column_bytes16() to get the size of UTF-16 encoded string, and then call sqlite3_column_text(), the internal value will be converted to a UTF-8 string before a string pointer is returned. That will invalidate the length value that was originally returned.

The easiest way to avoid problems is to extract the datatype you want and then call the matching bytes function to find out how large the buffer is. Here are examples of safe call sequences:

/* correctly extract a blob */
buf_ptr = sqlite3_column_blob( stmt, n );
buf_len = sqlite3_column_bytes( stmt, n );

/* correctly extract a UTF-8 encoded string */
buf_ptr = sqlite3_column_text( stmt, n );
buf_len = sqlite3_column_bytes( stmt, n );

/* correctly extract a UTF-16 encoded string */
buf_ptr = sqlite3_column_text16( stmt, n );
buf_len = sqlite3_column_bytes16( stmt, n );

By matching the correct bytes function for your desired datatype, you can avoid any type conversions keeping both the pointer and length valid and correct.

You should always use sqlite3_column_bytes() to determine the size of a BLOB.

When a call to sqlite3_step() returns SQLITE_DONE, the statement has successfully finished execution. At that point, there is nothing further you can do with the statement. If you want to use the statement again, it must first be reset.

The function sqlite3_reset() can be called any time after sqlite3_step() is called. It is valid to call sqlite3_reset() before a statement is finished executing (that is, before sqlite3_step() returns SQLITE_DONE or an error indicator). You can’t cancel a running sqlite3_step() call this way, but you can short-circuit the return of additional SQLITE_ROW values.

For example, if you only want the first six rows of a result set, it is perfectly valid to call sqlite3_step() only six times and then reset the statement, even if sqlite3_step() would continue to return SQLITE_ROW.

The function sqlite3_reset() simply resets a statement, it does not release it. To destroy a prepared statement and release its memory, the statement must be finalized.

The function sqlite3_finalize() can be called at any time on any statement that was successfully prepared. All of the prepared statements associated with a database connection must be finalized before the database connection can be closed.

Although both of these functions can return errors, they always perform their function. Any error that is returned was generated by the last call to sqlite3_step(). See Result Codes and Error Codes for more details.

It is a good idea to reset or finalize a statement as soon as you are done using it. A call to sqlite3_reset() or sqlite3_finalize() ensures the statement will release any locks it might be holding, and frees any resources associated with the prior statement execution. If an application keeps statements around for an extended period of time, they should be kept in a reset state, ready to be bound and executed.

Prepared statements have a significant amount of state. In addition to the currently bound parameter values and other details, every prepared statement is always in one of three major states. The first is the “ready” state. Any freshly prepared or reset statement will be “ready.” This indicates that the statement is ready to execute, but hasn’t been started. The second state is “running,” indicating that a statement has started to execute, but hasn’t yet finished. The final state is “done,” which indicates the statement has completed executing.

Knowing the current state of a statement is important. Although some API functions can be called at any time (like sqlite3_reset()), other API functions can only be called when a statement is in a specific state. For example, the sqlite3_bind_xxx() functions can only be called when a statement is in its “ready” state. Figure 7-1 shows the different states and how a statement transitions from one state to another.

There is no way to query the current state of a statement. Transitions between states are normally controlled by the design and flow of the application.

Figure 7-1. Prepared statement transitions. A statement can be in one of three states. Depending on the current state, only some API functions are valid. Calling a function in an inappropriate state will result in an SQLITE_MISUSE error.

Here are two examples of using prepared statements. The first example executes a CREATE TABLE statement by first preparing the SQL string and then calling sqlite3_step() to execute the statement:

    sqlite3_stmt    *stmt = NULL;

    /* ... open database ... */

    rc = sqlite3_prepare_v2( db, "CREATE TABLE tbl ( str TEXT )", -1, &stmt, NULL );
    if ( rc != SQLITE_OK) exit( -1 );

    rc = sqlite3_step( stmt );
    if ( rc != SQLITE_DONE ) exit ( -1 );
    
    sqlite3_finalize( stmt );

    /* ... close database ... */

The CREATE TABLE statement is a DDL command that does not return any type of value and only needs to be “stepped” once to fully execute the command. Remember to reset or finalize statements as soon as they’re finished executing. Also remember that all statements associated with a database connection must be fully finalized before the connection can be closed.

This second example is a bit more complex. This code performs a SELECT and loops over sqlite3_step() extracting all of the rows in the table. Each value is displayed as it is extracted:

    const char      *data = NULL;
    sqlite3_stmt    *stmt = NULL;

    /* ... open database ... */

    rc = sqlite3_prepare_v2( db, "SELECT str FROM tbl ORDER BY 1", -1, &stmt, NULL );
    if ( rc != SQLITE_OK) exit( -1 );

    while( sqlite3_step( stmt ) == SQLITE_ROW ) {
        data = (const char*)sqlite3_column_text( stmt, 0 );
        printf( "%s\n", data ? data : "[NULL]" );
    }
    
    sqlite3_finalize( stmt );

    /* ... close database ... */

This example does not check the type of the column value. Since the value will be displayed as a string, the code depends on SQLite’s internal conversion process and always requests a text value. The only tricky bit is that the string pointer may be NULL, so we need to be prepared to deal with that in the printf() statement.

SQLite supports five different styles of statement parameters. These short string tokens are placed directly into the SQL command string, which can then be passed to one of the sqlite3_prepare_xxx() functions. Once the statement is prepared, the individual parameters are referenced by index.

To get an idea of how these work, consider this INSERT statement:

INSERT INTO people (id, name) VALUES ( ?, ? );

The two statement parameters represent the id and name values being inserted. Parameter indexes start at one, so the first parameter that represents the id value has an index of one, and the parameter used to reference the name value has an index of two.

Notice that the second parameter, which is likely a text value, does not have single quotes around it. The single quotes are part of the string-literal representation, and are not required for a parameter value.

Once this statement has been prepared, it can be used to insert multiple rows. For each row, simply bind the appropriate values, step through the statement, and then reset the statement. After the statement has been reset, new values can be bound to the parameters and the statement can be stepped again.

You can also use explicit index values:

INSERT INTO people (id, name) VALUES ( ?1, ?2 );

Using explicit parameter indexes has two major advantages. First, you can have multiple instances of the same index value, allowing the same value to be bound to more than one place in the same statement.

Second, explicit indexes allow the parameters to appear out of order. There can even be gaps in the index sequence. This can help simplify application code maintenance if the query is modified and parameters are added or removed.

This level of abstraction can be taken even further by using named parameters. In this case, you allow SQLite to assign parameter index values as it sees fit, in a similar fashion to anonymous parameters. The difference is that you can ask SQLite to tell you the index value of a specific parameter based off the name you’ve given it. Consider this statement:

INSERT INTO people (id, name) VALUES ( :id, :name );

In this case, the parameter values are quite explicit. As we will see in the next section, the code that binds values to these parameters is also quite explicit, making it very clear what is going on. Best of all, it doesn’t matter if new parameters are added. As long as the existing names remain unchanged, the code will properly find and bind the named parameters.

Note, however, that parameters can only be used to replace literal values, such as quoted strings or numeric values. Parameters cannot be used in place of identifiers, such as table names or column names. The following bit of SQL is invalid:

SELECT * FROM ?;   -- INCORRECT: Cannot use a parameter as an identifier

If you attempt to prepare this statement, it will fail. This is because the parameter (which acts as an unknown literal value) is being used where an identifier is required. This is invalid, and the statement will not prepare correctly.

Within a statement, it is best to choose a specific parameter style and stick with it. Mixing anonymous parameters with explicit indexes or named parameters is likely to cause confusion about what index belongs to which parameter.

Personally, I prefer to used the colon-name-style parameters. Using named parameters eliminates the need to know any specific index values, allowing you to just reference the name at runtime. The use of short, significant names can also make the intent of both your SQL statements and your bind code easier to understand.

When you first prepare a statement with parameters, all of the parameters start out with a NULL assigned to them. Before you execute the statement, you can bind specific values to each parameter using the sqlite3_bind_xxx() family of functions.

There are nine sqlite3_bind_xxx() functions available, plus a number of utility functions. These functions can be called any time after the statement is prepared, but before sqlite3_step() is called for the first time. Once sqlite3_step() has been called, these functions cannot be called again until the statement is reset.

All the sqlite3_bind_xxx() functions have the same first and second parameters and return the same result. The first parameter is always a pointer to an sqlite3_stmt, and the second is the index of the parameter to bind. Remember that for anonymous parameters, the first index value starts with one. For the most part, the third parameter is the value to bind. The fourth parameter, if present, indicates the length of the data value in bytes. The fifth parameter, if present, is a function pointer to a memory management callback.

All the bind functions return an integer error code, which is equal to SQLITE_OK upon success.

The bind functions are:

In addition to these type-specific bind functions, there is also a specialized function:

The text and BLOB variants of sqlite3_bind_xxx() require you to pass a buffer pointer for the data value. Normally this buffer and its contents must remain valid until a new value is bound to that parameter, or the statement is finalized. Since that might be some time later in the code, these bind functions have a fifth parameter that controls how the buffer memory is handled and possibly released.

If the fifth parameter is either NULL or the constant SQLITE_STATIC, SQLite will take a hands-off approach and assume the buffer memory is either static or that your application code is taking care of maintaining and releasing any memory.

If the fifth parameter is the constant SQLITE_TRANSIENT, SQLite will make an internal copy of the buffer. This allows you to release your buffer immediately (or allow it to go out of scope, if it happens to be on the stack). SQLite will automatically release the internal buffer at an appropriate time.

The final option is to pass a valid void mem_callback( void* ptr ) function pointer. This callback will be called when SQLite is done with the buffer and wants to release it. If the buffer was allocated with sqlite3_malloc() or sqlite3_realloc(), you can pass a reference to sqlite3_free() directly. If you allocated the buffer with a different set of memory management calls, you’ll need to pass a reference to a wrapper function that calls the appropriate memory release function.

Once a value has been bound to a parameter, there is no way to extract that value back out of the statement. If you need to reference a value after it has been bound, you must keep track of it yourself.

To help you figure out what parameter index to use, there are three utility functions:

Using the sqlite3_bind_parameter_index() function, you can easily find and bind named parameters. The sqlite3_bind_xxx() functions will properly detect an invalid index range, allowing you to look up the index and bind a value in one line:

sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(stmt, ":pid"), pid);

If you want to clear all of the bindings back to their initial NULL defaults, you can use the function sqlite3_clear_bindings():

If you want to be absolutely sure bound values won’t leak from one statement execution to the next, it is best to clear the bindings any time you reset the statement. If you’re doing manual memory management on data buffers, you can free any memory used by bound values after this function is called.

There are significant security advantages to using bound parameters. Many times people will manipulate SQL strings to substitute the values they want to use. For example, consider building an SQL statement in C using the string function snprintf():

snprintf(buf, buf_size,
         "INSERT INTO people( id, name ) VALUES ( %d, '%s' );",
         id_val, name_val);

In this case we do need single quotes around the string value, as we’re trying to form a literal representation. If we pass in these C values:

id_val = 23;
name_val = "Fred";

Then we get the following SQL statement in our buffer:

INSERT INTO people( id, name ) VALUES ( 23, 'Fred');

This seems simple enough, but the danger with a statement like this is that the variables need to be sanitized before they’re passed into the SQL statement. For example, consider these values:

id_val = 23;
name_val = "Fred' ); DROP TABLE people;";

This would cause our snprintf() to create the following SQL command sequence, with the individual commands split out onto their own lines for clarity:

INSERT INTO people( id, name ) VALUES ( 23, 'Fred' );
DROP TABLE people;
' );

While that last statement is nonsense, the second statement is cause for concern.

Thankfully, things are not quite as bad as they seem. The sqlite3_prepare_xxx() functions will only prepare a single statement (up to the first semicolon), unless you explicitly pass the remainder of the SQL command string to another sqlite3_prepare_xxx() call. That limits what can be done in a case like this, unless your code automatically prepares and executes multiple statements from a single command buffer.

Be warned, however, that the interfaces provided by many scripting languages will do exactly that, and will automatically process multiple SQL statements passed in with a single call. The SQLite convenience functions, including sqlite3_exec(), will also automatically process multiple SQL commands passed in through a single string. What makes sqlite3_exec() particularly dangerous is that the convenience functions don’t allow the use of bound values, forcing you to programmatically build SQL command statements and opening you up to problems. Later in the chapter, we’ll take a closer look at sqlite3_exec() and why it usually isn’t the best choice.

Even if SQLite will only process the first command, damage can still be done with subqueries and other commands. Bad input can also force a statement to fail. Consider the result if the name value is:

Fred', 'extra junk

If you’re updating a series of records based off this id value, you had better wrap all the commands up in a transaction and be prepared to roll it back if you encounter an error. If you just assume the commands will work, you’ll end up with an inconsistent database.

This type of attack is known as an SQL injection attack. An SQL injection attack inserts SQL command fragments into data values, causing the database to execute arbitrary SQL commands. Unfortunately, it is extremely common for websites to be susceptible to this kind of attack. It also borders on inexcusable, because it is typically very easy to avoid.

One defense against SQL injections is to try to sanitize any string values received from an untrusted source. For example, you might try to substitute all single quote characters with two single quote characters (the standard SQL escape mechanism). This can get quite complex, however, and you’re putting utter faith in the code’s ability to correctly sanitize untrusted strings.

A much easier way to defend yourself against SQL injections is to use SQL statement parameters. Injection attacks depend on a data value being represented as a literal value in an SQL command statement. The attack only works if the attack value is passed through the SQL parser, where it alters the meaning of the surrounding SQL commands.

In the case of SQL parameters, the bound values are never passed through the SQL parser. An SQL statement is only parsed when the command is prepared. If you’re using parameters, the SQL engine parses only the parameter tokens. Later, when you bind a value to a specific parameter, that value is bound directly in its native format (i.e. string, integer, etc.) and is not passed through the SQL parser. As long as you’re careful about how you extract and display the string, it is perfectly safe to directly bind an untrusted string value to a parameter value without fear of an SQL injection.

Besides avoiding injection attacks, parameters can also be faster and use less memory than string manipulations. Using a function such as snprintf() requires an SQL command template, and a sufficiently large output buffer. The string manipulation functions also need working memory, plus you may need additional buffers to copy and sanitize values. Additionally, a number of datatypes, such as integers and floating-point numbers (and especially BLOBs), often take up significantly more space in their string representation. This further increases memory usage. Finally, once the final command buffer has been created, all the data needs to be passed through the SQL parser, where the literal data values are converted back into their native format and stored in additional buffers.

Compare that to the resource usage of preparing and binding a statement. When using parameters, the SQL command statement is essentially static, and can be used as is, without modification or additional buffers. The parser doesn’t need to deal with converting and storing literal values. In fact, the data values normally never leave their native format, further saving time and memory by avoiding conversion in and out of a string representation.

Using parameters is the safe and wise choice, even for situations when a statement is only used once. It may take a few extra lines of code, but the process will be safer, faster, and more memory efficient.

This example executes an INSERT statement. Although this statement is only executed once, it still uses bind parameters to protect against possible injection attacks. This eliminates the need to sanitize the input value.

The statement is first prepared with a statement parameter. The data value is then bound to the statement parameter before we execute the prepared statement:

    char            *data = ""; /* default to empty string */
    sqlite3_stmt    *stmt = NULL;
    int             idx = -1;

    /* ... set "data" pointer ... */
    /* ... open database ... */

    rc = sqlite3_prepare_v2( db, "INSERT INTO tbl VALUES ( :str )", -1, &stmt, NULL );
    if ( rc != SQLITE_OK) exit( -1 );

    idx = sqlite3_bind_parameter_index( stmt, ":str" );
    sqlite3_bind_text( stmt, idx, data, -1, SQLITE_STATIC );

    rc = sqlite3_step( stmt );
    if (( rc != SQLITE_DONE )&&( rc != SQLITE_ROW )) exit ( -1 );
    
    sqlite3_finalize( stmt );

    /* ... close database ... */

In this case we look for either an SQLITE_DONE or an SQLITE_ROW return value. Both are possible. Although the INSERT itself will be fully executed on the first call to sqlite3_step(), if PRAGMA count_changes is enabled, then the statement may return a value. In this case, we want to ignore any potential return value without triggering an error, so we must check for both possible return codes. For more details, see count_changes in Appendix F.

It is important to understand that all parameters must have some literal associated with them. As soon as you prepare a statement, all the parameters are set to NULL. If you fail to bind an alternate value, the parameter still has a literal NULL associated with it. This has a few ramifications that are not always obvious.

The general rule of thumb is that bound parameters act as literal string substitutions. Although they offer additional features and protections, if you’re trying to figure out the expected behavior of a parameter substitution, it is safe to assume you’ll get the exact same behavior as if the parameter was a literal string substitution.

In the case of an INSERT statement, there is no way to force a default value to be used. For example, if you have the following statement:

INSERT INTO membership ( pid, gid, type ) VALUES ( :pid, :gid, :type );

Even if the type column has a default value available, there is no way this statement can use it. If you fail to bind a value to the :type parameter, a NULL will be inserted, rather than the default value. The only way to insert a default value into the type column is to use a statement that doesn’t reference it, such as:

INSERT INTO membership ( pid, gid ) VALUES ( :pid, :gid );

This means that if you’re heavily dependent on database-defined default values, you may need to prepare several variations of an INSERT statement to cover the different cases when different data values are available. Of course, if your application code is aware of the proper default values, it can simply bind that value to the proper parameter.

The other area where parameters can cause surprises is in NULL comparisons. For example, consider the statement:

SELECT * FROM employee WHERE manager = :manager;

This works for normal values, but if a NULL is bound to the :manager parameter, no rows will ever be returned. If you need the ability to test for a NULL in the manager column, make sure you use the IS operator:

SELECT * FROM employee WHERE manager IS :manager;

For more details, see IS in Appendix D.

This behavior also makes it tricky to “stack” conditionals. For example, if you have the statement:

SELECT * FROM employee WHERE manager = :manager AND project = :project;

you must provide a meaningful value for both :manager and :project. If you want the ability to search on a manager, on a project, or on a manager and a project, you need to prepare multiple statements, or you need to add a bit more logic:

...WHERE ( manager = :manager OR :manager IS NULL )
     AND ( project = :project OR :project IS NULL );

This query will ignore one (or both) of the value conditions if you assign a NULL to the appropriate parameters. This expression won’t let you explicitly search for NULLs, but that can be done with additional parameters and logic. Preparing more flexible statements reduces the number of unique statements you need to manage, but it also tends to make them more complex and can make them run slower. If they get too complex, it might make more sense to simply define a new set of statements, rather than adding more and more parameter logic to the same statement.

Before we get into when things go wrong, let’s take a quick look at when things go right. Generally, any API call that simply needs to indicate, “that worked,” will return the constant SQLITE_OK. Not all non-SQLITE_OK return codes are errors, however. Recall that sqlite3_step() returns SQLITE_ROW or SQLITE_DONE to indicate specific return state.

Table 7-2 provides a quick overview of the standard error codes. At this point in the development life cycle, it is unlikely that additional standard error codes will be added. Additional extended error codes may be added at any time, however.

Many of these errors are fairly specific. For example, SQLITE_RANGE will only be returned by one of the sqlite3_bind_xxx() functions. Other codes, like SQLITE_ERROR, provide almost no information about what went wrong.

Of specific interest to the developer is SQLITE_MISUSE. This indicates an attempt to use a data structure or API call in an incorrect or otherwise invalid way. For example, trying to bind a new value to a prepared statement that is in the middle of an sqlite3_step() sequence would result in a misuse error. Occasionally, you’ll get an SQLITE_MISUSE that results from failing to properly deal with a previous error, but many times it is a good indication that there is some more basic conceptual misunderstanding about how the library is designed to work.

The extended codes were added later in the SQLite development cycle. They provide more specific details on the cause of an error. However, because they can change the value returned by a specific error condition, they are turned off by default. You need to explicitly enable them for the older API calls, indicating to the SQLite library that you’re aware of the extended error codes and willing to accept them.

All of the standard error codes fit into the least-significant byte of the integer value that is returned by most API calls. The extended codes are all based off one of the standard error codes, but provide additional information in the higher-order bytes. In this way, the extended codes can provide more specific details about the cause of the error. Currently, most of the extended error codes provide specific details for the SQLITE_IOERR result. You can find a full list of the extended error codes at http://sqlite.org/c3ref/c_ioerr_access.html.

The following APIs are used to enable the extended error codes and extract more information about any current error conditions.

It is acceptable to leave extended error codes off and intermix calls to sqlite3_errcode() and sqlite3_extended_errcode().

Because the error state is stored in the database connection, it is easy to end up with race conditionals in a threaded application. If you’re sharing a database connection across threads, it is best to wrap your core API call and error-checking code in a critical section. You can grab the database connection’s mutex lock with sqlite3_db_mutex(). See sqlite3_db_mutex() in Appendix G for more details.

Similarly, the error handling system can’t deal with multiple errors. If there is an error that goes unchecked, the next call to a core API function is likely to return SQLITE_MISUSE, indicating the attempt to use an invalid data structure. In this and similar situations where multiple errors have been encountered, the state of the error message can become inconsistent. You need to check and handle any errors after each API call.

In addition to the standard and extended codes, the newer _v2 versions of sqlite3_prepare_xxx() change the way prepared statement errors are processed. Although the newer and original versions of sqlite3_prepare_xxx() share the same parameters, the sqlite3_stmt returned by the _v2 versions is slightly different.

The most noticeable difference is in how errors from sqlite3_step() are handled. For statements prepared with the original version of sqlite3_prepare_xxx(), the majority of errors within sqlite3_step() will return the rather generic SQLITE_ERROR. To find out the specifics of the situation, you had to call sqlite3_reset() or sqlite3_finalize() to extract a more detailed error code. This would, of course, cause the statement to be reset or finalized, which limited your recovery options.

Things work a bit differently if the statement was prepared with the _v2 version. In that case, sqlite3_step() will return the specific error directly. The call sqlite3_step() may return a standard code or an extended code, depending if extended codes are enabled or not. This allows the developer to extract the error directly, and provides for more recovery options.

The other major difference is how database schema changes are handled. If any Data Definition Language command (such as DROP TABLE) is issued, there is a chance the prepared statement is no longer valid. For example, the prepared statement may refer to a table or index that is no longer there. The only way to resolve any possible problems is to reprepare the statement.

The _v2 versions of sqlite3_prepare_xxx() make a copy of the SQL statement used to prepare a statement. (This SQL can be extracted. See sqlite3_sql() in Appendix G for more details.) By keeping an internal copy of the SQL, a statement is able to reprepare itself if the database schema changes. This is done automatically any time SQLite detects the need to rebuild the statement.

The statements created with the original version of prepare didn’t save a copy of the SQL command, so they were unable to recover themselves. As a result, any time the schema changed, an API call involving any previously prepared statement would return SQLITE_SCHEMA. The program would then have to reprepare the statement using the original SQL and try again. If the schema change was significant enough that the SQL was no longer valid, sqlite3_prepare_xxx() would return an appropriate error when the program attempted to reprepare the SQL command.

Statements created with the _v2 version of prepare can still return SQLITE_SCHEMA. If a schema change is detected and the statement is unable to automatically reprepare itself, it will still return SQLITE_SCHEMA. However, under the _v2 prepare, this is now considered a fatal error, as there is no way to recover the statement.

Here is a side-by-side comparison of the major differences between the original and _v2 version of prepare:

Because the _v2 error handling is a lot simpler, and because of the ability to automatically recover from schema changes, it is strongly recommended that all new development use the _v2 versions of sqlite3_prepare_xxx().

Transactions and checkpoints add a unique twist to the error recovery process. Normally, SQLite operates in autocommit mode. In this mode, SQLite automatically wraps each SQL command in its own transaction. In terms of the API, that’s the time from when sqlite3_step() is first called until SQLITE_DONE is returned by sqlite3_step() (or when sqlite3_reset() or sqlite3_finalize() is called).

If each statement is wrapped up in its own transaction, error recovery is reasonably straightforward. Any time SQLite finds itself in an error state, it can simply roll back the current transaction, effectively canceling the current SQL command and putting the database back into the state it was in prior to the command starting.

Once a BEGIN TRANSACTION command is executed, SQLite is no longer in autocommit mode. A transaction is opened and held open until the END TRANSACTION or COMMIT TRANSACTION command is given. This allows multiple commands to be wrapped up in the same transaction. While this is useful to group together a series of discrete commands into an atomic change, it also limits the options SQLite has for error recovery.

When an error is encountered during an explicit transaction, SQLite attempts to save the work and undo just the current statement. Unfortunately, this is not always possible. If things go seriously wrong, SQLite will sometimes have no choice but to roll back the current transaction.

The errors most likely to result in a rollback are SQLITE_FULL (database or disk full), SQLITE_IOERR (disk I/O error or locked file), SQLITE_BUSY (database locked), SQLITE_NOMEM (out of memory), and SQLITE_INTERRUPT (interrupt requested by application). If you’re processing an explicit transaction and receive one of these errors, you need to deal with the possibility that the transaction was rolled back.

To figure out which action was taken by SQLite, you can use the sqlite3_get_autocommit() function.

If SQLite was forced to do a full rollback, the database will once again be in autocommit mode. If the database is not in autocommit mode, it must still be in a transaction, indicating that a rollback was not required.

Although there are situations when it is possible to recover and continue a transaction, it is considered a best practice to always issue a ROLLBACK if one of these errors is encountered. In situations when SQLite was already forced to roll back the transaction and has returned to autocommit mode, the ROLLBACK will do nothing but return an error that can be safely ignored.

SQLite employs a number of different locks to protect the database from race conditions. These locks allow multiple database connections (possibly from different processes) to access the same database file simultaneously without fear of corruption. The locking system is used for both autocommit transactions (single statements) as well as explicit transactions.

The locking system involves several different tiers of locks that are used to reduce contention and avoid deadlocking. The details are somewhat complex, but the system allows multiple connections to read a database file in parallel, but any write operation requires full, exclusive access to the entire database file. If you want the full details, see http://www.sqlite.org/lockingv3.html.

Most of the time the locking system works reasonably well, allowing applications to easily and safely share the database file. If coded properly, most write operations only last a fraction of a second. The library is able to get in, make the required modifications, verify them, and then get out, quickly releasing any locks and making the database available to other connections.

However, if more than one connection is trying to access the same database at the same time, sooner or later they’ll bump into each other. Normally, if an operation requires a lock that the database connection is unable to acquire, SQLite will return the error SQLITE_BUSY or, in some more extreme cases, SQLITE_IOERR (extended code SQLITE_IOERR_BLOCKED). The functions sqlite3_prepare_xxx(), sqlite3_step(), sqlite3_reset(), and sqlite3_finalize() can all return SQLITE_BUSY. The functions sqlite3_backup_step() and sqlite3_blob_open() can also return SQLITE_BUSY, as these functions use sqlite3_prepare_xxx() and sqlite3_step() internally. Finally, sqlite3_close() may return SQLITE_BUSY if there are unfinalized statements associated with the database connection, but that’s not related to locking.

Gaining access to a needed lock is often a simple matter of waiting until the current holder finishes up and releases the lock. In most cases, this is not a particularly long period of time. The waiting can either be done by the application, which can respond to an SQLITE_BUSY by simply trying to reprocess the statement and trying again, or it can be done with a busy handler.

There are several functions available to query the version of the SQLite library. Each API call has a corresponding #define macro that declares the same value.

If you’re building your own application, you can use the #define macros and the function calls to verify that you’re using the correct header for the available library. The #define values come from the header file, and are set when your application is compiled. The function calls return the same values that were baked into the library when it was compiled.

If you’re using a dynamic library of some sort, you can use these macros and functions to prevent your application from linking with a library version other than the one it was originally compiled against. This might not be a good thing, however, as it will also prevent upgrades. If you need to lock in a specific version, you should probably be using a static library.

If you want to check the validity of a dynamic library, it might be better to do something like this:

if ( SQLITE_VERSION_NUMBER > sqlite3_libversion_number( ) ) {
    /* library too old; report error and exit. */
}

Remember that the macro will hold the version used when your code was compiled, while the function call will return the version of the SQLite library. In this case, we report an error if the library is older (smaller version) than the one used to compile and build the application code.

When SQLite needs to dynamically allocate memory, it normally calls the default memory handler of the underlying operating system. This causes SQLite to allocate its memory from the application heap. However, SQLite can also be configured to do its own internal memory management (see sqlite3_config() in Appendix G). This is especially important on embedded and hand-held devices where memory is limited and overallocation may lead to stability problems.

Regardless, you can access whatever memory manager SQLite is using with these SQLite memory management functions:

While a number of SQLite calls require the use of sqlite3_free(), application code is free to use whatever memory management is most appropriate. Where these functions become extremely useful is in writing custom functions, virtual tables, or any type of loadable module. Since this type of code is meant to operate in any SQLite environment, you will likely want to use these memory management functions to ensure the maximum portability for your code.

When creating date and time data values, there are a few basic questions that need to be answered. Many of these seem obvious enough, but skipping over them too quickly can lead to big problems down the road.

First, you need to figure out what you’re trying to store. It might be a time of day, such as a standalone hour, minute, second value without any associated date. You may need a date record, that refers to a specific day, month, year, but has no associated time. Many applications require timestamps, which include both a date and time to mark a specific point in time. Some applications need to record a specific day of the year, but not for a specific year (for example, a holiday). Many applications also use durations (time deltas). Even if the application doesn’t store durations or offsets, they are often computed for display purposes, such as the amount of time between “now” and a specific event.

It is also worth considering the range and precision required by your application. As already discussed, dates in the far past (or far future) are poorly represented by some calendaring systems. A database that holds reservations for a conference room may require minute precision, while a database that holds network packet dumps may require precision of a microsecond or better.

As with many other datatypes, the class of data an application needs to store, along with the required range and precision, often drives the decision on what representation to use. The two most common representations used by SQLite are some type of formatted text-based value or a floating-point value.

Nearly all of the time and date functionality within SQLite comes from five SQL functions. One of these functions is essentially a universal translator, designed to convert nearly any time or date format into any other format. The other four functions act as convenience wrappers that provide a fixed, predefined output format.

In addition to the conversion functions, SQLite also provides a three literal expressions. When an expression is evaluated, these literals will be translated into an appropriate time value that represents “now.”

strftime( format, time, modifier, modifier... )