SQLite Introduction

Intended Audience
Introduction
What is SQLite?
What's in it for me?
SQLite Extension
Installing SQLite
Using SQLite
Chained Queries
New Functions
SQLite Iterators
Utility Functions
Buffered Advantage
Custom Functions
Summary
About the Author

Intended Audience

This article is intended for PHP programmers interested in learning about the SQLite extension. The article will introduce the reader to the functionality offered by the PHP SQLite extension, and overview the benefits of SQLite in relation to other database systems.

A basic understanding of how PHP and SQL work is assumed. Some prior experience with MySQL or PostgreSQL is an advantage.

Introduction

In recent months you have probably heard about the new database extension for PHP: SQLite. By most accounts SQLite seems to be the best thing since sliced bread, offering a fast SQL interface to a flat file database, and creating an elegant alternative to bulky database interfaces, without the functionality or speed loss one might expect. In this article we will explore this wondrous new extension, and hopefully validate some of the benefits it is rumored to have.

What is SQLite?

SQLite is an embedded database library that implements a large subset of the SQL 92 standard. Its claim to fame is the combination of both the database engine and the interface (to said engine) within a single library, as well as the ability to store all the data in a single file. I terms of functionality SQLite resides somewhere between MySQL and PostgreSQL. However, when it comes to performance, SQLite is often 2-3 times faster (or even more). This is thanks to a highly tuned internal architecture, and the elimination of server-to-client and client-to-server communication.

All this is combined into a package that is only slightly larger than the MySQL client library, an impressive feat considering you get an entire database system with it. Utilizing a highly efficient memory infrastructure, SQLite maintains its small size in a tiny memory footprint, far smaller then that of any other database system. This makes SQLite a very handy tool that can efficiently be applied to virtually any task requiring a database.

What's in it for me?

Besides speed and efficiency SQLite has a number of other advantages that make it an ideal solution for many tasks. Because SQLite's databases are simple files, there is no need for administrative staff to spend time creating a complex permission structure to protect user databases. This is automatically handled by file system permissions, which also means that (in space restricted environments) no special rules are needed to keep track of user disk space. The users benefit from the ability to create as many databases as they desire and have absolute control over those databases.

The fact that a database is just a single file makes SQLite very portable across servers. SQLite also eliminates the need for database daemon processes that could eat significant amount of memory and other resources, even when the database system is liberally used.

SQLite Extension

As the newest database extension, SQLite is fortunate in not having legacy code, unlike older extensions such as MySQL, which must maintain obsolete behavior for backwards compatibility reasons. It also allows the extension to utilize the newest PHP features to attain the highest level of performance and functionality. The developers of the extension have made it easy for users to migrate from other database systems to SQLite, by keeping the interface similar to those already implemented in PHP.

SQLite also supports a flexible mechanism for passing database resources in the procedural interfaces, making it equally easy to switch from MySQL, where the resource is passed last, and PostgreSQL, where it is passed first.

SQLite also features a powerful OO interface that can be used to efficiently retrieve data from a database, saving you from having to implement your own OO wrappers around the procedural interface. As the example below demonstrates, the OO interface also allows you to avoid passing resources altogether.


// create new database (OO interface)
$db = new SQLiteDatabase("db.sqlite");

// create table foo and insert sample data
$db->query("BEGIN;
        CREATE TABLE foo(id INTEGER PRIMARY KEY, name CHAR(255));
        INSERT INTO foo (name) VALUES('Ilia');
        INSERT INTO foo (name) VALUES('Ilia2');
        INSERT INTO foo (name) VALUES('Ilia3');
        COMMIT;");

// execute a query    
$result = $db->query("SELECT * FROM foo");
// iterate through the retrieved rows
while ($result->valid()) {
    // fetch current row
    $row = $result->current();     
    print_r($row);
// proceed to next row
    $result->next();
}

// not generally needed as PHP will destroy the connection
unset($db);

?>

Installing SQLite

In PHP 5.0 installing SQLite is a snap because both the extension and the library are bundled, so all you need to do is add –with-sqlite to your configure line. I would still recommend installing the SQLite library, if only because it comes with a SQLite binary that allows you to open and manipulate SQLite databases without using PHP. This is quite handy for debugging and executing various one time commands, as well as testing your queries. Often enough you will find that the bundled SQLite library is a bit dated, so building your PHP with an external library will allow you to benefit from the latest fixes and features of SQLite. It will also allow you to upgrade your SQLite library in future without having to recompile PHP.

To build the SQLite extension against an external library simply use –with-sqlite=/path/to/lib/.

I should also mention that the SQLite extension comes with a comprehensive series of tests for both OO and procedural interfaces that test every single function and method supported by SQLite. This is not only an excellent resource for examples on how every SQLite method and function works, but also supplies the expected output, allowing you to see the end result of every operation.

Using SQLite

The procedural interface to SQLite is nearly identical to that of MySQL and other database extensions. For the most part switching to SQLite will only require changing the mysql/pq/etc... function prefix to sqlite.

// create new database (procedural interface)
$db = sqlite_open("db.sqlite");

// uncomment next line if you still need to create table foo
// sqlite_query($db , "CREATE TABLE foo (id INTEGER PRIMARY KEY, name CHAR(255))");

// insert sample data
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia')");
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia2')");
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia3')");

// execute query
$result = sqlite_query($db, "SELECT * FROM foo");
// iterate through the retrieved rows
while ($row = sqlite_fetch_array($result)) {
    print_r($row);
    /* each result looks something like this
    Array
    (
        [0] => 1
        [id] => 1
        [1] => Ilia
        [name] => Ilia
    )
*/
}

// close database connection
sqlite_close($db);

?>

The big differences between SQLite and other databases are actually in the database engine itself. Unlike other databases, SQLite is loosely typed; all data is stored as NULL terminated strings rather then the binary representation of data in a particular column type. For compatibility reasons SQLite still supports type specification during table creation such as INT, CHAR, FLOAT, TEXT and so on, but they are not used. Internally, SQLite only makes a distinction between strings and integers during sorting. Therefore, if you do not intend to sort the data, you could avoid specifying column types for CREATE TABLE statements in SQLite altogether.

SQLite's typeless nature makes sorting and comparing data somewhat slow, as each time SQLite will need to determine the type of the data and apply either a string or a numeric sorting/comparison mechanism. SQL tables often require an auto-incremented key for quick access to rows, as well as means of retrieving a reference to the last inserted row. In SQLite this has a slightly unusual syntax. To create such a field, you need to declare the field as INTEGER PRIMARY KEY, rather then specifying a special type or assigning additional properties that indicate that a field is auto-incremented.

Chained Queries

As you may expect, SQLite comes with many new features that both increase its performance and expand its functionality. One of these features is the ability to do chained queries, which means you can execute multiple queries via a single query execution function. This reduces the amount of PHP functions you need to run, thus improving the speed of the script. It also allows you to easily wrap query blocks inside transactions, further improving performance. This can be a significant factor when performing multiple write queries. There are, however, a few ‘gotchas’ to keep in mind when using this functionality.

If any query in SQLite uses user-specified input you should take extra care to validate that input, to prevent SQL injection. Unlike in MySQL, where this would only cause an embarrassing query error, in SQLite it would allow the attacker to execute a query on your server, with potentially disastrous consequences. If the query block you execute performs inserts, and you wish to retrieve the id, the sqlite_last_insert_rowid() normally used for this purpose would only retrieve the id of the last insert. On the other hand, when trying to determine how many rows were affected using sqlite_changes() the result will have the total number of rows affected by all of the executed queries. If your query block contains a SELECT, make sure it is the first query, otherwise your result set will not contain the rows retrieved by that query.


// create a new memory only database
$db = new SQLiteDatabase(":memory:");
// Create a 2-column table bar & insert 2 rows into it
/* For improved performance the entire query block is wrapped inside a transaction. */
$db->query("BEGIN;
        CREATE TABLE bar ( id INTEGER PRIMARY KEY, id2 );
        INSERT INTO bar (id2) VALUES(1);
        INSERT INTO bar (id2) VALUES(2);
        COMMIT;");
// will print "2 insert queries"
echo $db->changes()." insert queries\n";
// will print "last inserted row id: 2"
echo "last inserted row id: ".$db->lastInsertRowid();

?>

New Functions

In addition to back end features, SQLite also offers a number of new functions that simplify and accelerate data retrieval from SQLite.


$db = new SQLiteDatabase("db.sqlite");
/* Execute the query and retrieve all rows as an associated array */
$result_array = $db->arrayQuery("SELECT * FROM foo", SQLITE_ASSOC);
print_r($result_array);

?>

The functions allow both the query execution and the data retrieval to be performed with a single function call, virtually eliminating the entire PHP execution overhead. The PHP script itself is simplified as you now have a single function, where otherwise you would have a series of data retrieval functions in a loop. In instances where only a single column is retrieved sqlite_single_query() can be used, which immediately returns a string or an array of strings depending on the number of rows that are retrieved.


$db = sqlite_open("db.sqlite");
// Retrieve the id of column (as string)
$id = sqlite_single_query($db, "SELECT id FROM foo WHERE name='Ilia'");
var_dump($id); //string(1)

// In the event >1 row matches, the result is an array
$ids = sqlite_single_query($db, "SELECT id FROM foo WHERE name LIKE 'I%'");
var_dump($ids); // array(3)

?>

As with any feature, you should use it and not abuse it. When fetching all of the data returned by a query in one go you must remember that all the results will be stored in memory. If the result set contains a large quantity of data, the cost of allocating the memory will likely offset any advantages gained through the reduced number of function calls. Therefore you should reserve the usage of these functions for instances when small quantities of data are retrieved.

SQLite Iterators

In PHP 5.0 there is another way to retrieve row data from a query via the use of iterators.


$db = new SQLiteDatabase("db.sqlite");
// reduce memory usage by executing un-buffered query
$res = $db->unbufferedQuery("SELECT * FROM foo");
foreach ($res as $row) { // iterate through result object
        // Output code
        print_r($row);
}

?>

Iteration of objects works very much like iteration of an array via foreach(), except that this time you don't have 'keys' and the value represents an array containing data from a particular result row. Because iterators are internal engine handlers and not functions, they have very little PHP overhead compared with the sqlite_fetch_*() functions, and do not require the result set to be buffered in memory. The end result is an extremely fast, yet simple and familiar method of retrieving data. There are no downsides to using SQLite's object iterators, and whenever you need to go through a multi-row result set you should definitely consider using them.

Utility Functions

The SQLite extension also features a number of utility functions that may come in handy when working with a database. One of those functions, sqlite_num_fields(), can be used to determine the number of fields (columns) in a particular result set.
Alternatively, if you intend to fetch data you can simply use count() on the first result, which will give you the same number. If both string and numeric keys are retrieved, you will need to divide the result by two, since there are twice as many entries in the result array as there are fields. This number may be important if your script needs to retrieve the field names inside a particular table. If so you could use sqlite_field_name() inside a loop to access this information, as the example below demonstrates.


$db = new SQLiteDatabase("db.sqlite");
$res = $db->unbufferedQuery("SELECT * FROM foo LIMIT 1");
// fetch the number of fields
$n_fields = $res->numFields();
$i = 0;
while ($i < $n_fields) {
    // retrieve individual fields
    $field_name = $res->fieldName($i++);
    echo $field_name."\n";
}

?>

This of course is not an ideal way to retrieve the column names from the table, simply because it would fail in the event that the source table contains no rows, and also because it requires you to fetch some data you never intend to use. A much better solution is to use the sqlite_fetch_column_types() function, which will fetch the columns and their types for a particular table regardless of the amount of data in it.

Buffered Advantage

In most cases, for performance and memory reasons, you would want to execute unbuffered queries. However, this comes at a slight loss of functionality that may be needed in certain cases, which is why unbuffered queries are not always the best choice.

For example, suppose you wanted to find out how many rows were actually retrieved by your query. Using an unbuffered query, you would need to retrieve every single row before being able to determine this. Using buffered queries, this is simply a matter of running the sqlite_num_rows() function, which would instantly retrieve this information from the result. Unbuffered queries are also restricted to linear data retrieval, meaning that you must retrieve information from all the rows in a series, one row at a time. There is no such limitation with buffered queries, where you can use sqlite_seek() to move to any row and retrieve data from it, or even access rows in reverse order if necessary.


$db = new SQLiteDatabase("db.sqlite");
$res = $db->query("SELECT * FROM foo");
$n_rows = $res->numRows(); // get number result rows
$res->seek($n_rows - 1); // move to the last row
// retrieve data in reverse order
do {
    $data = $res->current(SQLITE_ASSOC); // get row data
    print_r($data);
}
while ($res->hasPrev()&& $res->prev()); // until first row

?>

Custom Functions

One of the most interesting features that the SQLite extension brings to the table is the ability to create functions of your own to use within SQL. This is possible due to the fact that SQLite combines both the interface and the database engine in a single library that is coupled with PHP. Through the use of sqlite_create_function() you can create functions that can then be applied to the result set or used inside a WHERE condition.

/* determine the difference between the user supplied string and the one in the database based on the contained characters */
function char_compare($db_str, $user_str) {
    return similar_text($db_str, $user_str);
}

$db = new SQLiteDatabase("db.sqlite");

/* Create char_compare() function inside SQLite based on our PHP function, char_compare(). The 3rd parameter indicates how many arguments the function requires */
$db->createFunction('char_compare', 'char_compare', 2);
        
/* Execute query, where char_compare() is used to perform the string comparison between name & specified string */
$res = $db->arrayQuery("SELECT name, char_compare(name, 'Il2') AS sim_index FROM foo", SQLITE_ASSOC);

print_r($res);

?>

The ability to use PHP from within SQL allows you to simplify the actual script making it accessible to larger audience of developers. This allows PHP to be used as a templating engine that simply populates HTML structures with database data. In many instances this can simplify the code so much that there is no need to place a templating system on top of PHP. Beyond the code simplification, this also improves performance and reduces the script's memory footprint, since no data manipulations need to occur in user-space.

Keep in mind that if the data you are working with can potentially contain binary data, you should utilize the sqlite_udf_decode_binary() function to decode the data from SQLite's internal binary encoding before processing it. Once you are done, you then need to encode the binary data using sqlite_udf_encode_binary() to ensure that this data can be accessed without corruptions at a later point.

Summary

Now that you’ve seen how SQLite works and what it can offer, perhaps you’ll consider using it in your current and future applications. Hopefully, this brief introduction has familiarized you with the functionality offered by SQLite, and not dispelled any of the good things you have heard about it.

Like every tool, SQLite has its strengths and weaknesses. While being an ideal solution for small and/or mostly-read applications, it is not well suited for large-scale applications performing frequent writes. This limitation is due to SQLite’s single file based architecture, which doesn’t allow multiplexing across servers, or the usage of database-wide locks on writes.