Overview > Sample Applications
Many database management systems (DBMSs) have been developed over the past several decades. DB-2, Informix, Ingres, MySQL, Oracle, SQL Server, Sybase are a few that are commercially successful. SQLite is a recent addition to the DBMS family, and it is also quite successful commercially. It has made a long journey since its debut on May 29, 2000.[*] It is an SQL-based relational DBMS (RDBMS) with the following characteristics:
[*] SQLite received the 2005 OSCON Google and O'Reilly Integrator category award.
Zero configuration
There is no separate install or set up procedure to initialize SQLite before using it. There is no configuration file. Databases need minimal or no administration. You can download the SQLite source code from its homepage http://www.sqlite.org, compile it using your favorite C compiler, and start using the compiled library.
Embeddable
There is no need to maintain a separate server process dedicated to SQLite. The library is embeddable in your own applications.
Application interface
SQLite provides an SQL environment for C applications to manipulate databases. It is a call-level interface library for dynamic SQL; users can assemble SQL statements on the fly and pass them on to the interface for execution. (There are no other means, except by SQL, to manipulate databases.) There are no special preprocessing and compilation requirements for applications; a normal C compiler will do the work.
Transactional support
SQLite supports the core transactional properties, namely atomicity, consistency, isolation, and durability (ACID). No actions are required from database administrators upon a system crash or power failure to recover databases. When SQLite reads a database, it automatically performs necessary recovery actions on the database in a user-transparent manner.
Thread-safe
SQLite is a thread-safe library, and many threads in an application process can access the same or different databases concurrently. SQLite takes care of database-level thread concurrency.
Lightweight
SQLite has a small footprint of about 250 KB, and the footprint can be reduced further by disabling some advanced features when you compile it from the source code. SQLite runs on Linux, Windows, Mac OS X, OpenBSD, and a few other operating systems.
Customizable
SQLite provides a good framework in which you can define and use custom-made SQL functions, aggregate functions, and collating sequences. It also supports UTF-8 and UTF-16 standards-based encoding for Unicode text.
Cross-platform
SQLite lets you move database files between platforms. For example, you can create a database on a Linux x86 machine, and use the same database (by making a copy) on an ARM platform without any alterations. The database behaves identically on all supported platforms.
SQLite is different from most other modern SQL databases in the sense that its primary design goal is to be simple. SQLite strives to be simple, even if it leads to occasional inefficient implementations of some features. It is simple to maintain, customize, operate, administer, and embed in C applications. It uses simple techniques to implement ACID properties.
SQLite supports a large subset of SQL-92 data definition and manipulation features, and some SQLite specific commands. You can create tables, indexes, triggers, and views using standard data definition SQL constructs. You can manipulate stored information using INSERT, DELETE, UPDATE, and SELECT SQL constructs. The following is a list of additional features supported as of the SQLite 3.3.6 release (the latest set of supported features can be obtained from the SQLite homepage):
Partial support for ALTER TABLE (rename table, add column)
UNIQUE, NOT NULL, CHECK constraints
Subqueries, including correlated subqueries, INNER JOIN, LEFT OUTER JOIN, NATURAL JOIN, UNION, UNION ALL, INTERSECT, EXCEPT
Transactional commands: BEGIN, COMMIT, ROLLBACK
SQLite commands, including reindex, attach, detach, pragma
The following SQL-92 features are not yet supported as of the SQLite 3.3.6 release:
Foreign key constraints (parsed but not enforced)
Many ALTER TABLE features
Some TRIGGER-related features
RIGHT and FULL OUTER JOIN
Updating a VIEW
GRANT and REVOKE
SQLite stores an entire database in a single, ordinary native file that can reside anywhere in a directory of the native filesystem. Any user who has permission to read the file can read anything from the database. A user who has write permission on the file and the container directory can change anything in the database. SQLite uses a separate journal file to save transaction recovery information that is used in the event of transaction aborts or system failures. The SQLite attach command helps a transaction work on multiple databases simultaneously. Such transactions are also ACID-compliant. Pragma commands let you alter the behavior of the SQLite library.
SQLite allows multiple applications to access the same database concurrently. However, it supports a limited form of concurrency among transactions. It allows any number of concurrent read-transactions on a database, but only one exclusive write-transaction.
SQLite is a successful RDBMS. It has been widely used in low-to-medium tier database applications such as web site services (SQLite works fine with up to 100,000 evenly distributed hits a day; the SQLite development team has demonstrated that SQLite may even withstand 1,000,000 hits a day), cell phones, PDAs, set-top boxes, standalone appliances. You could even use it in teaching SQL to students in a beginner database course. The source code itself is well documented and commented, and you can use it in advanced database management courses, or in projects as a reference technology. It is available freely, and has no licensing complications because it is in the public domain.
In this section I will present some simple applications illustrating various features of SQLite. The applications are presented in the following subsections.
Let's begin our exploration of SQLite land by studying a very simple application. The following example presents a typical SQLite application. It is a C program that invokes SQLite APIs to work with an SQLite database file:
Here is a typical SQLite application:
Code View: Scroll / Show All
#include <stdio.h> #include "sqlite3.h" int main(void) { sqlite3* db = 0; sqlite3_stmt* stmt = 0; int retcode; retcode = sqlite3_open("MyDB", &db); /* Open a database named MyDB */ if (retcode != SQLITE_OK){ sqlite3_close(db); fprintf(stderr, "Could not open MyDB\n"); return retcode; } retcode = sqlite3_prepare(db, "select SID from Students order by SID", -1, &stmt, 0); if (retcode != SQLITE_OK){ sqlite3_close(db); fprintf(stderr, "Could not execute SELECT\n"); return retcode; } while (sqlite3_step(stmt) == SQLITE_ROW){ int i = sqlite3_column_int(stmt, 0); printf("SID = %d\n", i); } sqlite3_finalize(stmt); sqlite3_close(db); return SQLITE_OK; } You may compile the above example application and execute it. The sample output shown throughout this document was generated on a Linux machine, but these examples will work on other platforms that SQLite runs on.
These examples assume that you have already prepared the sqlite3 executable, the libsqlite3.so (sqlite3.dll on Windows and libsqlite3.dylib for Mac OS X) shared library, and the sqlite3.h interface definition file. You can obtain these in source or binary form from http://www.sqlite.org. You may find it easier to work with these examples if you put all three (sqlite3, the shared library, and sqlite3.h) in the same directory as the examples.
For example, suppose you are on a Linux system and that you've saved the app1.c sample program in the same directory as libsqlite3.so, sqlite3, and sqlite3.h. You can compile the file by executing this command:
gcc app1.c -o ./app1 -lsqlite3 -L.
It will produce a binary named app1 in the current working directory. You may execute the binary to see the output.
NOTE
Both the SQLite source code and the application must be compiled with the same compiler.
If you've installed SQLite as a package, or if your operating system came with it preinstalled, you may need to use a different set of compiler arguments. For example, on Ubuntu, you can install SQLite with the command sudo aptitude install sqlite3 libsqlite3-dev, and you can compile the example application with the command cc app1.c -o ./app1 -lsqlite3.
Because SQLite is included with recent versions of Mac OS X, this same compilation command works there as well.
The application opens the MyDB database file in the current working directory. The database needs at least one table, named Students; this table must have at least one integer column named SID. In the next example application, you will learn how to create new tables in databases, and how to insert rows (also called tuples and records) in tables, but for the moment, you can create and populate the table with these commands:
sqlite3 MyDB "create table students (SID integer)" sqlite3 MyDB "insert into students values (200)" sqlite3 MyDB "insert into students values (100)" sqlite3 MyDB "insert into students values (300)"
If you run the app1 now (to pull in the SQLite library, you may need to include your working directory name in the LD_LIBRARY_PATH environment variable on Linux systems), you will see the following output:
SID = 100 SID = 200 SID = 300
NOTE
On Linux, Unix, and Mac OS X, you may need to prefix app1 with ./ when you type its name at the command prompt, as in:
./app1
The application first prepares, then executes the SQL statement: select SID from Students order by SID. It then steps through the resulting rowset, fetches SID values one by one, and prints the values. Finally, it closes the database.
SQLite is a call-level interface library that is embedded in applications. The library implements all SQLite APIs as C functions. All API function names are prefixed with sqlite3_ and their signatures are declared in sqlite3.h. A few of them are used in the example application, namely sqlite3_open, sqlite3_prepare, sqlite3_step, sqlite3_column_int, sqlite3_finalize, and sqlite3_close. The application also uses mnemonic constants, namely SQLITE_OK and SQLITE_ROW, for comparing values returned by the APIs. The mnemonics are defined in sqlite3.h.
The following sections discuss some of the key functions in the SQLite API.
By executing the sqlite3_open function, an application opens a new connection to a database file via the SQLite library. (The application may have other open connections to the same or different databases. SQLite treats the connections distinctly, and they are independent of one another as far as SQLite is concerned.) SQLite automatically creates the database file if the file does not already exist.
NOTE
When opening or creating a file, SQLite follows a lazy approach: the actual opening or creation is deferred until the file is accessed for reading.
The sqlite3_open function returns a connection handle (a pointer to an object of type sqlite3) via a formal parameter (db, in the preceding example), and the handle is used to apply further operations on the database connection (for this open connection). The handle represents the complete state of this connection.
The sqlite3_prepare function compiles an SQL statement, and produces an equivalent internal object. This object is commonly referred to as a prepared statement in database literature, and is implemented as a bytecode program in SQLite. A bytecode program is an abstract representation of an SQL statement that is run on a virtual machine or an interpreter. For more information, see the later section, Bytecode Programming Language. I use the terms bytecode program and prepared statement interchangeably in this book.
The sqlite3_prepare function returns a statement handle (a pointer to an object of type sqlite3_stmt) via a formal parameter (stmt in the preceding example), and the handle is used to apply further operations to manipulate the prepared statement. In the example program, I prepared the select SID from Students order by SID statement as the stmt handle. This handle acts like an open cursor and is used to obtain the resulting rowset that the SELECT statement returns, one row at a time.
The sqlite3_step function executes the bytecode program until it hits a break point (because it has computed a new row), or until it halts (there are no more rows). In the former case, it returns SQLITE_ROW, and in the latter case, SQLITE_DONE. For SQL statements that don't return rows (such as UPDATE, INSERT, DELETE, and CREATE), it always returns SQLITE_DONE because there are no rows to process. The step function moves the position of the cursor for the results of a SELECT statement. Initially, the cursor points before the first row of the output rowset. Every execution of the step function moves the cursor pointer to the next row of the rowset. The cursor moves only in the forward direction.
If the step function returns SQLITE_ROW, you can retrieve the value of each column (also known as attribute or field) by executing the sqlite3_column_* API functions. The impedance (data type) mismatch between SQL/SQLite and the C language is handled automatically: the APIs convert data between the two languages and from storage types to requested types. In the example application, each output row is an integer value, and we read the value of SID by executing the sqlite3_column_int function that returns integer values.
The sqlite3_finalize function destroys the prepared statement. That is, it erases the bytecode program, and frees all resources allocated to the statement handle. The handle becomes invalid.
The sqlite3_close function closes the database connection, and frees all resources allocated to the connection. The connection handle becomes invalid.
The other widely used APIs are sqlite3_bind_* and sqlite3_reset. In an SQL statement string (input to sqlite3_prepare), one or more literal values can be replaced by the SQL parameter marker ? (or ?NNN, :AAA, @AAA, or $AAA where NNN is a number and AAA is an identifier). They become input parameters to the prepared statement. The values of these parameters can be set using the bind API functions. If no value is bound to a parameter, either the default value is taken, or SQL NULL is taken if no default is declared in the schema. The reset API function resets a statement handle (i.e., the prepared statement) back to its initial state with one exception: all parameters that had values bound to them retain their values. The statement becomes ready to be reexecuted by the application, and reuses these values in the reexecution. However, the application may replace these values by new ones executing the bind APIs once again before it starts the reexecution.
All API functions return zero or positive integer values. SQLite recommends using mnemonics for return value checks. The return value SQLITE_OK indicates a success; SQLITE_ROW indicates that the sqlite3_step function has found a new row in the rowset that the SELECT statement returned; SQLITE_DONE indicates that the statement execution is complete.
The next example presents another SQLite application that can be run from a command line to manipulate databases interactively. It takes two arguments: the first one is a database file name, and the second one an SQL statement. It first opens the database file, then applies the statement to the database by executing the sqlite3_exec API function, and finally closes the database file. The function executes the statement directly, without the need to go through the prepare and step API functions, as was done in the previous example. In case the statement produces output, the exec function executes the callback function for each output row. The user must have read permission on the given database file, and depending on the query type, she may need to have a write permission on the file and the directory it's contained in. Here is a command-line based application:
Code View: Scroll / Show All
#include <stdio.h> #include "sqlite3.h" static int callback(void *NotUsed, int argc, char **argv, char **colName) { // Loop over each column in the current row int i; for (i = 0; i < argc; i++){ printf("%s = %s\n", colName[i], argv[i] ? argv[i] : "NULL"); } return 0; } int main(int argc, char **argv) { sqlite3* db = 0; char* zErrMsg = 0; int rc; if (argc != 3){ fprintf(stderr, "Usage: %s DATABASE-FILE-NAME SQL-STATEMENT\n", argv[0]); return -1; } rc = sqlite3_open(argv[1], &db); if (rc != SQLITE_OK){ fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); return -2; } rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg); if (rc != SQLITE_OK){ fprintf(stderr, "SQL error: %s\n", zErrMsg); } sqlite3_close(db); return rc; } Compile the preceding application code into an executable, such as app2. You can now issue SQL statements that operate on a database. Suppose you are working on the same MyDB database in the current working directory. By executing the following command lines, you can insert new rows in the Students table:
./app2 MyDB "insert into Students values(100)" ./app2 MyDB "insert into Students values(10)" ./app2 MyDB "insert into Students values(1000)"
If you run the previous application (app1) now, you will see the following output:
SID = 10 SID = 100 SID = 100 SID = 200 SID = 300 SID = 1000
You can also create new tables in databases; for example, ./app2 MyDBExtn "create table Courses(name varchar, SID integer)" creates the Courses table in a new MyDBExtn database in the current working directory.
NOTE
SQLite has an interactive command-line program (sqlite3), shown earlier, which you can use to issue SQL commands. You can download a precompiled binary version of it from the SQLite site, or compile it from source. This app2 example is essentially a bare-bones implementation of sqlite3.
SQLite is a thread-safe library. Consequently, many threads in an application can access the same or different databases concurrently.
NOTE
In order to be thread-safe, the SQLite source code must be compiled with the THREADSAFE preprocessor macro set to 1 (enable this by passing --enable-threadsafe to the configure script when you prepare to compile SQLite). In the default setting, the multithreading feature is turned off for Linux, but turned on for Windows. There is no way you can query the SQLite library to find out whether it has the feature.
This example uses the pthreads library, which is not included with Windows. On Windows, you have two choices: use the tools and compilers from Cygwin (http://www.cygwin.com), or download pthreads for Windows from http://sourceware.org/pthreads-win32/.
Here is a typical multithreaded application:
Code View: Scroll / Show All
#include <stdio.h> #include <pthread.h> #include "sqlite3.h" void* myInsert(void* arg) { sqlite3* db = 0; sqlite3_stmt* stmt = 0; int val = (int)arg; char SQL[100]; int rc; rc = sqlite3_open("MyDB", &db); /* Open a database named MyDB */ if (rc != SQLITE_OK) { fprintf(stderr, "Thread[%d] fails to open the database\n", val); goto errorRet; } /* Create the SQL string. If you were using string values, you would need to use sqlite3_prepare() and sqlite3_bind_* to avoid an SQL injection vulnerability. However %d guarantees an integer value, so this use of sprintf is safe. */ sprintf(SQL, "insert into Students values(%d)", val); /* Prepare the insert statement */ rc = sqlite3_prepare(db, SQL, -1, &stmt, 0); if (rc != SQLITE_OK) { fprintf(stderr, "Thread[%d] fails to prepare SQL: %s -> return code %d\n", val, SQL, rc); goto errorRet; } rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { fprintf(stderr, "Thread[%d] fails to execute SQL: %s -> return code %d\n", val, SQL, rc); } else { printf("Thread[%d] successfully executes SQL: %s\n", val, SQL); } errorRet: sqlite3_close(db); return (void*)rc; } int main(void) { pthread_t t[10]; int i; for (i=0; i < 10; i++) pthread_create(&t[i], 0, myInsert, (void*)i); /* Pass the value of i */ /* wait for all threads to finish */ for (i=0; i<10; i++) pthread_join(t[i], 0); return 0; }
|
The application creates 10 threads, and each of them tries to insert one row in the Students table in the same MyDB database. SQLite implements a lock-based concurrency scheme, so some or all of the INSERT statements may fail due to lock conflict. The application does not need to worry about concurrency control and database consistency issues; it cannot corrupt the database. SQLite takes care of concurrency control and consistency issues. However, you will need to check for failures, and handle them appropriately within the code (for example, you might retry the command that failed, or inform the user that it failed and let her decide what to do next).
Each thread needs to connect to its database, and needs to create its own SQLite (connection and statement) handles. SQLite does not recommend sharing handles across threads. Even though sharing handles between threads may appear to work, there is no guarantee that you'll get the expected results. In fact, the SQLite library may break and dump core in some versions of Linux. Also, under Unix/Linux systems, you should not attempt to preserve connection handles across a fork system call into the child process. Problems such as database corruption or an application crash may arise if you do this.
NOTE
The restriction against sharing a database connection between threads is somewhat relaxed in SQLite 3.3.1 and subsequent versions. Threads can use a database connection safely in mutual exclusion. What this means is that you can switch a connection from one thread to another as long as the former thread does not hold any native file locks on the connection. You can safely assume that no locks are held if the thread has no pending transaction, and if it has reset or finalized all statements on the connection.
Here is a typical SQLite application that uses two databases:
Code View: Scroll / Show All
#include <stdio.h> #include "sqlite3.h" int main(void) { sqlite3* db = 0; sqlite3_open("MyDB", &db); /* Open a database named MyDB */ sqlite3_exec(db, "attach database MyDBExtn as DB1", 0, 0, 0); sqlite3_exec(db, "begin", 0, 0, 0); sqlite3_exec(db, "insert into Students values(2000)", 0, 0, 0); sqlite3_exec(db, "insert into Courses values('SQLite Database', 2000)", 0, 0, 0); sqlite3_exec(db, "commit", 0, 0, 0); sqlite3_close(db); return SQLITE_OK; } I have simplified the code by not including error checks. The application opens the MyDB database, and then attaches the MyDBExtn database to the current connection. MyDB needs to have a Students(SID) table, and the MyDBExtn database needs a Courses(name, SID) table. The application opens a transaction by executing the begin command, inserts one row in Students and one in Courses inside the transaction, and finally commits the transaction by executing the commit command. INSERT statements do not need a callback function, and hence, I pass 0 as the callback parameter in sqlite3_exec calls in the example application.
NOTE
SQLite permits multiple SQL statements in a single exec API call; the same batch of commands can be executed by passing this sequence of statements in a single exec call: begin; insert into Students values(2000); insert into Courses values('SQLite Database', 2000); commit. If the batch contains SELECT statements, the same callback function is used to process the resulting rowsets.
A catalog is a system table that is created and maintained by SQLite itself. It stores meta information about the database. SQLite maintains one master catalog, named sqlite_master, in every database. The catalog stores schema information about tables, indexes, triggers, and views. You can query the master catalog (e.g., select * from sqlite_master), but cannot directly modify the catalog. There are other optional catalog tables. All catalog table names start with the sqlite_ prefix, and the names are reserved by SQLite for internal use. You cannot create database objects (tables, views, indexes, triggers) with such names (in upper, lower or mixed case).
