6.3. SQLite

PHP 5 introduced a new bundled and, by default, an available "database" engine called SQLite.

6.3.1. SQLite Strengths and Weaknesses

This section describes the characteristics of SQLite compared to other DBMSes.

6.3.1.1 Strength: Self-Contained, No Server Required

SQLite does not use a client/server model. It is embedded in your application, and only requires access to the database files. This makes integrating SQLite into other applications easier because there is no dependency on an external service.

6.3.1.2 Strength: Easy to Get Started

Setting up a new database with SQLite is easy and requires no intervention from system administrators.

6.3.1.3 Strength: Bundled with PHP 5

The entire SQLite engine is bundled with PHP 5. There is no need to install extra packages to make it available to PHP developers.

6.3.1.4 Strength: Lightweight and Fast

The newest of the databases covered in this chapter, SQLite has little compatibility baggage and still has a lean and light design. For most queries, it is on par with or exceeds the performance of MySQL.

6.3.1.5 Strength: Both a Procedural and an OO Interface

SQLite's PHP ex-tension features both procedural interfaces and an object-oriented interface. The latter makes it possible to have less code, and is, in some cases, faster than its procedural alternative.

6.3.1.6 Weakness: No Server Process

Although this is one of SQLite's strong points, the fact that SQLite has no server process leads to a series of scaling difficulties: file locking and concurrency issues, lack of persistent query caches, and scaling problems when handling very large data volumes.

Also, the only way to share a database between hosts is to share the file system with the database file. This way of running remote queries is much slower than sending queries and responses through a network socket, as well as less reliable.

6.3.1.7 Weakness: Not Binary Safe

SQLite does not handle binary data natively. To put binary data in a SQLite database, you first need to encode it. Likewise, after a SELECT, you need to decode the encoded binary data.

6.3.1.8 Weakness: Transactions Lock All Tables

Most databases lock individual tables (or even only rows) during transactions, but because of its implementation, SQLite locks the whole database on inserts, which makes concurrent read/write access dramatically slow.

6.3.2. Best Areas of Use

SQLite's primary point of excellence is that it is stand alone and extremely well suited for web-hosting environments. Because the SQLite client works on files, there is no need to maintain a second set of credentials for database access; if you can write to the database file, you can make changes in the database. Hosting companies just need to support the SQLite PHP extension, and their customers can take care of the rest.

A hosting company can limit the maximum size of databases (in combination with other data in the web space) easily because the SQLite database is just a file that takes space inside the web space of its customer.

SQLite excels at stand alone applications. Especially in web-hosting environments where there are many read queries and little write queries, the speed of SQLite is fully shown. An example of such an application might be a weblog where all hits pull out comments from the database, but where only a few comments are added.

6.3.3. PHP Interface

In this section, we present a full-fledged example using most of SQLite's feature sets. Each subsection introduces you to a new step in building an automatic indexed email storage system. We use the OO-based API in the examples, but also mention the procedural equivalent. The way this works is similar to the MySQLi extension.

6.3.3.1 Setting Up Databases

Because SQLite doesn't require a daemon to function, setting up a database is in fact nothing more than creating a specially formatted file. To create a new database, you simply try to open one; if the database does not exist, a new one will be created for you. That's the reason why the second parameter to the constructor can be used to specify the permissions for the created database.

The example script we start with is the create.php script, which creates the database and all tables inside our database (see Table 6.6).

sqlite_open(...)
$sqlite = new SQLiteDatabase(...)

You can also create in-memory databases by using the special keyword ":memory:" as the first parameter to the SQLiteDatabase constructor. This allows for ultra-fast temporary SQL power. Do not forget to store your data somewhere else before ending a script; if you do not, the data you put into the database is gone.

Here's an example:

<?php
    $db = new SQLiteDatabase("./crm.db", 0666, &$error)
        or die("Failed: $error");
    ...
    unset($db);
?>

6.3.3.2 Simple Queries

When the database is opened, we can start executing queries on the database. Because no tables are available in a new database, we have to create them first. The following example explains how to do this:

<?php
...
    $create_query = "
CREATE TABLE document (
    id INTEGER PRIMARY KEY,
    title,
    intro,
    body
);

CREATE TABLE dictionary (
    id INTEGER PRIMARY KEY,
    word
);

CREATE TABLE lookup (
    document_id INTEGER,
    word_id     INTEGER,
    position    INTEGER
);

CREATE UNIQUE INDEX word ON dictionary(word);
";

    $db->query($create_query);
?>

If you are familiar with other database systems, you will most likely notice the absence of types for some of the field definitions in the CREATE TABLE queries shown earlier. SQLite actually has only two types internally: INTEGER, which is used to store numbers, and "something else", which can be compared to a VARCHAR field in other databases. SQLite's VARCHAR can store more than 255 characters, though, which is sometimes a limitation in other database systems. You can also make an INTEGER field auto-increment by adding "PRIMARY KEY" as a postfix to the field definition. Of course, you can do this for only one field per table.

Something else that you might notice is that we execute multiple CREATE TABLE queries with one function call to the query() method. This is often not possible with other PHP interfaces to other database systems, such as the MySQL (not MySQLi) extension.

6.3.3.3 Error Handling

SQLite's error handling is a bit flakey because each of the query functions might throw a warning. It is therefore important to prepend the query functions with the "shut-up" operator @. The result of the function then needs to be checked against FALSE to see if the query succeeded. If it did not succeed, you can use sqlite_last_error() and sqlite_error_string() to retrieve a textual description of the error. Unfortunately, this error message is not very descriptive, either.

SQLite's constructor might also throw an SQLiteException, which you need to handle yourself (with a TRy...catch block). There will be some future work on SQLite's error handling, but that's likely something for PHP 5.1.

6.3.3.4 Simpler Queries and Transactions

By creating only the tables, our email indexer still does nothing useful, so the next step is to add the emails into our database. We do that in a new script called "insert.php". Here is part of its code:

<?php
    $db = new SQLiteDatabase("./crm.db", 0666, &$error)
        or die("Failed: $error");
    ...
    if ($argc < 2) {
        echo "Usage:\n\tphp insert.php <filename>\n\n";
        return;
    }

First, we open the database and check if the number of parameters to this command-line script is correct. The first (and only) parameter passed to this script is the mailbox (in UNIX, the MBOX format) we're going to store and later index.

$body = file_get_contents($argv[1]);
$mails = preg_split('/^From /m', $body);
unset($body);

We load the mailbox into memory and split it into separate emails with a regular expression. You might wonder what happens if a line in an email starts with From:; in this case, the UNIX MBOX format requires this From: to be escaped with the > character.

    // $db->query("BEGIN");
    foreach ($mails as $id => $mail) {
        $safe_mail = sqlite_escape_string($mail);
        $insert_query = "
INSERT INTO document(title, intro, body)
VALUES ('Title', 'This is an intro.', '{$safe_mail}')
";
        echo "Indexing mail #$id.\n";
        $db->query($insert_query);
    }
    // $db->query("COMMIT");

?>

Here, we loop over the mails, making sure we escape all possible dangerous characters with the sqlite_escape_string() functions, and insert the data into the database with the query() method.

By default, SQLite commits all queries directly to disk, which makes the inserting of many queries rather slow. Another problem that might arise is that other processes can insert data into the database during the process of importing our emails. To fix those two problems, you can simply use a transaction to perform the entire importing. To start a transaction, you can execute a query containing "BEGIN TRANSACTION" or simply "BEGIN". At the end of the transaction, you can use the "COMMIT" query to commit all queries in the transaction to disk. In the full example (including the tricks we discuss later in this section), the time for importing 638 emails dropped from 60m29s to 1m59s, which is quite a speed boost.

6.3.3.5 Triggers

SQLite has some advanced featuresfor example, it supports triggers. Triggers can be set to data-modifying queries, and consist of a small SQL script that runs whenever the specified action is "triggered." Our example will use triggers to automatically update our search index whenever a new document is added. To define the trigger, we extend our create.php script and add the following code to the file:

...
    $trigger_query = "
CREATE TRIGGER index_new
AFTER INSERT ON document
BEGIN
SELECT php_index(new.id, new.title, new.intro, new.body);
END;";
    $db->query($trigger_query);
?>

This creates a trigger named index_new to be run after an insert query on the document table. The SQL script that runs when the trigger fires is a simple select query, but that query is not that simple as it appears. You can see that there is no FROM clause, nor is the php_index() function a function defined in the SQL standard. This brings us to the next cool feature of SQLite: User Defined Functions.

6.3.3.6 User-Defined Functions (UDFs)

Because SQLite is Lite, it does not implement all the default SQL functions, but SQLite does provide you with the possibility to write your own functions that you then can use from your SQL queries.

sqlite_create_function(...)
$sqlite->createFunction(...)

We're adding this function registration call after the argument check in insert.php:

...
    $db->createFunction("php_index", "index_document", 4);
...

Of course, we create this new PHP function index_document. We place this function, with another helper function at the start of our script:

function normalize($body)
{
    $body = strtolower($body);
    $body = preg_replace(
        '/[.;,:!?¿¡\[\]@\(\)]/', ' ', $body);
    $body = preg_replace('/[^a-z0-9 -]/', '_', $body);

    return $body;
}

This helper function strips non-wanted characters and lowercase characters, and changes punctuation marks to spaces. It is used to normalize the words we put into our search index. After the helper function, our main function begins as follows:

function index_document($id, $title, $intro, $body)
{
    global $db;

Because this function is called through SQLite, we need to import our database handle into the function's scope; we do that with the global keyword:

$id = $db->singleQuery("SELECT max(id) from document");

Because of a bug in the SQLite library, we have to figure out the latest auto-increment value ourselves because we cannot trust the value passed through our callback function by SQLite. Using the PHP function sqlite_last_insert_row_id() (or the OO variant lastInsertRowId()) did not work here, either.

$body = substr($body, 0, 32000);
$body = normalize($body);

Here, we reduce the body to only 32KB with the reason that emails larger than this usually have an attachment, and that's not important to put into our index. After that, the text is normalized so that we can make a nice search index out of it:

$words = preg_split(
    '@([\W]+)@', $body, -1,
    PREG_SPLIT_OFFSET_CAPTURE |
    PREG_SPLIT_NO_EMPTY
);

This regular expression splits the body into words and calculates their position in the message (you can find more about regular expressions in Chapter 9, "Mainstream Extensions").

foreach ($words as $word) {
    $safe_word = sqlite_escape_string($word[0]);

    if ((strpos($safe_word, '_') === false) &&
        (strlen($safe_word) < 24))
    {

Here, we start looping over all the words that the regular expression created. We escape the word, and enter only the index section of this function if there is no underscore present in the word, and when it is smaller than 24 characters.

$result = @$db->query(
    "INSERT INTO dictionary(word) ".
    "VALUES('$safe_word');");
if ($result != SQLITE_OK) {
    /* already exists, need to fetch the
     * ID then */
    $word_id = $db->singleQuery(
        "SELECT id FROM dictionary ".
        "WHERE word = '$safe_word'");
} else {
    $word_id = $db->lastInsertRowID();
}

Here, we insert our word into the dictionary table, relying on the unique key of the word to prevent duplicate entries. In case the word already exists in the dictionary, the query will fail and we run a SELECT query to obtain the ID of the word with the singleQuery() method; otherwise, we request the ID with which the new word was inserted into the database. The singleQuery() method runs the query, and returns the first column of the first record returned by the query.

            $db->query(
                "INSERT INTO ".
                "lookup(document_id, word_id, position) ".
                "VALUES($id, $word_id, {$word[1]})");
        }
    }
}

When we know the ID of the word, we insert it with the document_id and the position into the lookup table (see Table 6.9).

sqlite_last_insert_row_id(...)
$sqlite->lastInsertRowId()

sqlite_single_query(...)
$sqlite->singleQuery(...)

6.3.3.7 Other Querying Functions

The singleQuery() method is one of many specialized functions for data retrieval. They are added for performance reasons, and there are a few more than we've already seen (see Table 6.10).

sqlite_query()
$sqlite->query()

sqlite_unbuffered_query()
$sqlite->unbufferedQuery()

$sqlite->queryExec()
sqlite_exec()

$sqlite->arrayQuery()
sqlite_array_query()

$sqlite->singleQuery()
sqlite_single_query()

6.3.3.8 Fetching Data

For the two functions that return handles to the resource, there is a complementary group of functions to actually fetch the data (see Table 6.11).

sqlite_fetch_array()
$sqlite->fetch()

sqlite_fetch_object()
$sqlite->fetchObject()

sqlite_fetch_single()
sqlite_fetch_string()
$sqlite->fetchSingle()

$sqlite->fetchAll()
sqlite_fetch_all()

The mode parameter determines how a result will be returned. When the SQLITE_ASSOC mode is used, the returned array will have the fields indexed by field name. When the SQLITE_NUM is used, the fields will be indexed by a field number only. When SQLITE_BOTH is used, there will be a numerical index and a field name index for each field in the returned array.

One of the more interesting fetch functions is $sqlite->fetchObject(), and thus, we present a small example here (which has nothing to do with our email indexing scripts):

<?php
$db = new SQLiteDatabase("./crm.db", 0666, &$error)
    or die("Failed: $error");

class Article {
    private $id;
    private $title;
    public $intro;
    private $body;
    private $fromDb;

    function save($db)
    {
        $intro = sqlite_escape_string($this->intro);
        $db->query(
            "UPDATE document SET intro = '$intro' ".
            "WHERE id = {$this->id}");
    }
}

This is our class definition with only two interesting things to mention. The names of the properties are the same as the name of the fields in the database. This way, they will be automatically filled in with the property visibility level. As you can see, only the intro field is a public property. The second interesting part is the save() method that executes an update query with the new intro data. It uses the stored $id property to update the correct record.

$result = $db->query(
    "SELECT * FROM document WHERE body LIKE '%conf%'");
$obj1 = $result->fetchObject('Article', NULL);

Here, we execute our query, fetch the first record as an object of class article, and pass as only a parameter to the constructor of that class the value TRue (which we don't use, though).

$obj1->intro = "This is a changed intro";
$obj1->save($db);
?>

This last part of the code changes the intro property of the object and then calls the save() method to save the changed data into the database.

6.3.3.9 Iterators

There is another way to navigate through a result set, and that is with an iterator. Using an iterator to iterate over the result set does not involve calling any functions, so it is therefore a bit faster than when you would use one of the fetch functions. In this example, we present the search.php script to find an email matching certain words:

<?php
$db = new SQLiteDatabase("./crm.db", 0666, &$error)
    or die("Failed: $error");

if ($argc < 2) {
    echo "Usage:\n\tphp search.php <search words>\n\n";
    return;
}

function escape_word(&$value)
{
    $value = sqlite_escape_string($value);
}

$search_words = array_splice($argv, 1);
array_walk($search_words, 'escape_word');
$words = implode("', '", $search_words);;

The parameters that are passed to the script are the search words, which we, of course, need to escape with the sqlite_escape_string() function. In the previous example, we use the array_walk() function to iterate over the array and escape the words. After they are escaped, we construct a list of them to use in the queries with the implode() function.

$search_query = "
    SELECT document_id, COUNT(*) AS cnt
    FROM dictionary d, lookup l
    WHERE d.id = l.word_id
        AND word IN ('$words')
    GROUP BY document_id
    ORDER BY cnt DESC
    LIMIT 10
";

$doc_ids = array();
$rank = $db->query($search_query, SQLITE_NUM);
foreach ($rank as $key => $row) {
    $doc_ids[$key] = $row[0];
}
$doc_ids = implode(", ", $doc_ids);
    ...

Next, we execute the query with the query() method that returns a result handle. With the foreach loop, we iterate over the result just as we would iterate over an array, except that we don't actually create an array first. The iterator tied to the SQLite buffered query object fetches the data for us row by row. In the most ideal case, we would use an unbuffered query here, but we can't do that because we need to reuse this result set; reusing result sets is not possible with an unbuffered query because the data is not buffered, of course.

6.3.3.10 Homegrown Iteration

To more clearly see how the iterator internally works, you can also do it manually (without foreach doing all the magic), as is shown here in the second part of the script:

$details_query = "
    SELECT document_id, substr(doc.body, position - 20, 100)
    FROM dictionary d, lookup l, document doc
    WHERE d.id = l.word_id
        AND word in ('$words')
        AND document_id IN ($doc_ids)
        AND document_id = doc.id
    GROUP BY document_id, doc.body
";
$result = $db->unbufferedQuery($details_query, SQLITE_NUM);
while ($result->valid()) {
    $record = $result->current();
    $list[$record[0]] = $record[1];
    $result->next();
}

By default, the $result points to the first row when iterating, and the current() method returns the current record (indexed in the way indicated by the second parameter to unbufferedQuery()). With the next() method, you can advance to the next record in the result set. There are a few more methods that you can use; the next table shows which ones, and also lists the procedural functions for them. The first parameter to the procedural interface functions is always the result handle, and this one is not listed in Table 6.12.

$result->seek()
sqlite_seek()

$result->rewind()
sqlite_rewind()

$result->next()
sqlite_next()

$result->prev()
sqlite_prev()

$result->valid()
sqlite_valid()
sqlite_has_more()

$result->hasPrev()
sqlite_has_prev()

Now, only the last part of our search script followsthe part where we actually output the results:

foreach ($rank as $record) {
    echo $record[0], "\n====\n...",
        $list[$record[0]], "...\n---------\n";
}
?>

Here, we just reiterate over our first query result and use the message ID as key to the result set to display the relevant parts of the emails found.

6.3.3.11 Other Result Set-Related Functions

You can use a few other functions and methods on result sets. The method numFields() (sqlite_num_fields()) returns the number of fields in the result set, and the method fieldName() (sqlite_field_name()) returns the name of the field. The only parameter to this method is the index of the field into the resultset (zero-based). If you do make a join between multiple tables, notice that this function returns the name of the field "as-is" from the query; for example, if the query contains "SELECT a.field1 FROM address a", the name of the field that is returned will be "a.field1".

Another peculiarity with column names, which is also valid for keys in returned arrays with the SQLITE_ASSOC option set, is that they are always returned in the same case as they were created in the "CREATE TABLE" statement. By setting the sqlite.assoc_case option in php.ini to 1, you force the SQLite extension to return uppercase column names. By setting it to 2, you force the extension to return lowercase column names. A setting of 0 (the default) does not touch the case of column names at all.

The numRows() method (sqlite_num_rows()) returns the number of records in the result set, but only works for buffered queries.

6.3.3.12 Aggregate User Defined Functions

Besides normal UDFs similar to those we used to generate our index from a trigger, it is also possible to define a UDF for aggregation functions. In the following example, we calculate the average length of the words in our dictionary:

<?php
$db = new SQLiteDatabase("./crm.db", 0666, &$error)
    or die("Failed: $error");

After opening the database, we define two functions that will be called during the aggregation. The first one is called for each queried record, and the second one is called when all records have been returned.

function average_length_step(&$ctxt, $string)
{
    if (!isset($ctxt['count'])) {
        $ctxt['count'] = 0;
    }
    if (!isset($ctxt['length'])) {
        $ctxt['length'] = 0;
    }

    $ctxt['count']++;
    $ctxt['length'] += strlen($string);
}

The $ctxt parameter can be used to maintain state between different records; in this case, we use the parameter as an array to store the number of words and the total lengths of all the words we've seen. We also need to initialize the two elements of the array to hide the "Warning: Undefined index: count" warnings that PHP will issue otherwise.

function average_length_finalize(&$ctxt)
{
    return sprintf(
        "Avg. over {$ctxt['count']} words is %.3f chars.",
        $ctxt['length'] / $ctxt['count']);
}

The finalize function returns a string containing the text "Avg. over x words is y chars.", where x and y are filled in dependent on the data.

$db->createAggregate(
    'average_length',
    'average_length_step', 'average_length_finalize'
);

The createAggregate() method creates our aggregate function. The first parameter is the name of the function that can be used from SQL queries; the second one is the function that is executed for each record (also called step); and the third parameter is the name of the function that is run when all records are selected.

$avg = $db->singleQuery(
    "SELECT average_length(word) FROM dictionary");
echo "$avg\n";
?>

Here, we simply execute the query using our newly defined function and echo the result, which should look like something like this:

Average over 28089 words is 10.038 chars.

6.3.3.13 Character Encoding

SQLite has support for two character sets: ISO-8859-1, which is the default and used for most western-European languages, and UTF-8. To enable UTF-8 mode, you need to tell the PHP ./configure command to do so. The switch to use SQLite's UTF-8 mode is --enable-sqlite-utf8. This option only affects sorting results.

6.3.3.14 Tuning

We already saw that you can speed up large amounts of inserts by encapsulating the queries into a transaction. But, there are a few more tricks that we can do. Usually, when inserting a lot of data into the database, we're not interested in how many changes there were in the result set. SQLite allows you to turn off the counting of changes, which obviously improves speed during insertion. You can instruct SQLite not to count changes by running the following SQL query:

PRAGMA count_changes = 0

For example, with

$db->query("PRAGMA count_changes = 0");

Another trick is to change the way SQLite flushes data to disk. With the synchronous pragma, you can switch between the following modes, as shown in Table 6.13.

In situations where there are a lot of reads from the SQLite database, it might be worthwhile to increase the cache size. Where the default is 2,000 pages (a page is 1,536 bytes), you can increase this size with the following query:

PRAGMA cache_size=5000;

This setting only has effect for the current session, and the value will be lost when the connection to the database is broken. If you want to persist this setting, you need to use the default_cache_size pragma instead of just cache_size.

6.3.3.15 Other Tricks

There are still a few things untold about SQLitefor example, what the method is to query the database structure. The answer is easyby using the following query:

SELECT * FROM sqlite_master

This returns one element per database object (table, index, and trigger) with the following information: type of object, the name of the object, the table to which the object is linked (only useful for indexes and triggers), an ID, and the SQL DDL query to create the object. When executed on our example, the result is shown in Table 6.14.

CREATE TABLE document (
    id INTEGER PRIMARY KEY,
    title,
    intro,
    body
)

CREATE TABLE dictionary (
    id INTEGER PRIMARY KEY,
    word
)

CREATE TABLE lookup (
    document_id INTEGER,
    word_id     INTEGER,
    position    INTEGER
)

CREATE TRIGGER index_new AFTER INSERT ON document
BEGIN
SELECT php_index(new.id, new.title, new.intro, new
.body);
END

The last thing to discuss are views, an SQL feature to simplify user-land queries. For example, if we want to create a view called "document_body_id" that contains only the id and body fields of the document table, we can execute the following query:

CREATE VIEW document_id_body AS
SELECT id, body FROM document;

After the view is created, you can use it in SQL queries just like it was a real table. For example, the following query uses the view to return the ID and body fields of the first two record of our document table:

SELECT * FROM document_id_body LIMIT 2;

Of course, in this case, it doesn't really make sense to create a view on one table only, but it does make sense to create a view over a complex query that joins multiple tables. Another original idea of views was that you can assign permissions to specific views as though they were tables, but of course, that doesn't make sense with SQLite, which doesn't know anything about permissions except for permissions on the file system where the database file resides.

6.3.3.16 Words of Wisdom

At last, here are some words of wisdom from the author of the SQLite engine, which he uses instead of a copyright notice:

• May you do good and not evil.

• May you find forgiveness for yourself and forgive others.

• May you share freely, never taking more than you give.

D. Richard Hipp