Upgrading to PHP 5

Indexes, Error Handling, and In-Memory Tables

Now that you know the basics, it’s time to cover the features needed to create robust applications using SQLite. Features such as creating primary and other keys, using in-memory tables, and error handling are all necessary to keep your site up and running in a responsive manner.

Indexes

Adding an index, also called a key, is the easiest way to improve application performance. When SQLite searches a database without keys, it needs to look at every single row in the table to check for matches. However, after you apply an index to the search fields, SQLite can often avoid this time-consuming process. Instead, it consults a specially constructed record that allows SQLite to quickly look up a field’s location within the table.

If you know ahead of time that the data in a particular field in your database is going to be unique (i.e., each value will appear in only one record for that field), then you should declare the field UNIQUE in your CREATE TABLE SQL statement. SQLite automatically indexes UNIQUE fields.

CREATE TABLE users (username TEXT UNIQUE, password TEXT);

In a web application, a username field is often unique, whereas a password field is not. When username is UNIQUE, SQLite creates a key, since SQLite needs to scan the column to protect against duplicate entries every time you insert a record. This prevents the database from having two users named rasmus. Also, you often query against fields that are important enough to be UNIQUE:

SELECT * FROM users WHERE username LIKE 'rasmus';

To add an index to any existing SQLite table, issue the CREATE INDEX statement:

CREATE INDEX indexname ON tablename(fieldname);

Here, indexname is the name of the index. It can be anything, but tablename _ fieldname _index is a good way to protect against reusing the same index name. For example:

CREATE INDEX users_username_index ON users(username);

Creating a plain-vanilla INDEX imposes no UNIQUEness constraints on your data. This is helpful because there are non-unique fields, such as locations or dates, where you still need quick search capabilities:

SELECT * FROM stores WHERE state LIKE 'New York';

SELECT * FROM purchases WHERE date LIKE '2004-07-22';

You can add a UNIQUE key to a pre-existing table:

CREATE UNIQUE INDEX indexname ON tablename(fieldname);

To remove an index, issue the DROP INDEX command:

DROP INDEX indexname;

Indexes make your database files larger. Other than that, there’s usually no harm in keeping an index around, even if you’re not using it.

Primary Keys

A primary key is a special kind of index. When you place primary key status upon a column in your table, the field serves as a unique identifier for a row. Therefore, if you’re interested in gathering information about a specific row in the database, the best way to retrieve it is by using its primary key.

A field with primary key status must be an integer. SQLite assigns the number 1 to the first row put into the table, 2 to the second, and so on. If you delete a line from the table, SQLite preserves the hole in the database and places any new records at the end instead of filling up the empty row.

To get SQLite to automatically create this strictly increasing set of values, first define a column in your table as an INTEGER PRIMARY KEY. Extending the previous example:

CREATE TABLE users (userid   INTEGER PRIMARY KEY, 
                    username TEXT UNIQUE, 
                    password TEXT );

Then, when you add a new row to the table, pass NULL as the value of the primary key:

INSERT INTO users VALUES (NULL, 'rasmus', 'z.8cMpdFbNAPw');

If you want to assign a specific number to a row as its primary key, pass that number instead of NULL. To find the value of the primary key of the last added row, call sqlite_last_insert_rowid( ) (or lastInsertRowid( ) when using the OO interface). For example:

$db = new SQLiteDatabase('/www/support/users.db');
$sql = "INSERT INTO users VALUES (NULL, '$username', '$password');";
$db->query($sql);
$rowid = $db->lastInsertRowid( );

The $rowid variable holds the primary key assigned to your INSERT.

This method is better than writing a query that retrieves the largest valued key; it’s possible that between inserting your initial row and making this request, another user has altered the table.

Error Handling

Just like the mysqli extension, SQLite error handling differs depending on whether you use the procedural or object-oriented interface. With the procedural interface, you must check the return value of each SQLite call and then consult the message in a special SQLite error variable. Alternatively, the SQLite object tosses an SQLiteException whenever it encounters dragons.

Procedural error handling

Here is a good way to structure your procedural code to check for SQLite errors:

$db = sqlite_open($database, 0666, $sqlite_error) or die ($sqlite_error);

if ($r = sqlite_query($db, $sql)) {
    // row iteration code here
} else {
    die (sqlite_error_string(sqlite_last_error($db)));
}

There are three different ways to access SQLite errors. When you initially try to connect to an SQLite database, the third parameter to sqlite_open( ) (in this case $sqlite_error) is a variable passed by reference. If SQLite cannot open the database, it will return false and store the error message in $sqlite_error.

The second parameter to sqlite_open( ) is the mode, which is an octal number that describes the file permissions SQLite uses when creating a new database. Currently, the SQLite extension always uses a mode of 0666, regardless of what’s passed in during sqlite_open( ). In other words, this value is ignored completely; however, it may be respected in future versions of the extension. This mode means the database is readable and writable by all users, including the web server.

Once your connection is established, SQLite still returns false upon errors, but it no longer uses $sqlite_error. Instead, it has a pair of error-reporting functions: sqlite_last_error( ) and sqlite_error_string( ).

The first function, sqlite_last_error( ), returns the SQLite error code for the most recent error. Since the error code is a number, it’s not very helpful for humans. To convert the number to an actual error message, pass it to sqlite_error_string( ).

In the previous example, any error triggers a die( ). More user-friendly applications require gentler error handling. Using error_log( ) in combination with a polite, generic message to users may be the best solution.

You cannot “save up” error checking while you complete a series of queries. SQLite resets the value returned by sqlite_last_error( ) after every query, so old error messages will be removed before you view them. SQLite even resets the message after an error-free query, so a query with an error followed by valid query leaves you with an error message of not an error.

Object-oriented error handling

When you use the object-oriented interface to the SQLite extension, you need to process exceptions or risk a fatal error. Exceptions are a method of error processing that eliminates the need to check return values of functions. They’re described in more detail in Chapter 7.

SQLite doesn’t always throw exceptions instead of returning NULL. In fact, the opposite it true: it throws exceptions only from its constructor. Therefore, while you need to catch that single exception, you still need to rely on traditional error handling for other errors.

Example 4-2 demonstrates this.

Example 4-2. Catching SQLite exceptions

$database = 'sqlite.db';
$sql      = 'INVALID SQL';
try {
    $db = new SQLiteDatabase($database);
} catch (SQLiteException $error) {
    print "Message: ".$error->getMessage( )."\n";
    print "File:".$error->getFile( )."\n"; die;
}

if ($r = $db->query($sql)) {
    // row iteration code here
} else {
    die (sqlite_error_string($db->lastError( )));
}

When SQLite has an error, it throws an SQLiteException. After you catch the exception, learn more about the specific error by calling getMessage( ) and find out which file caused the error with getFile( ).

For example, if you try to create an SQLite database file in a location where you do not have write permission, the code inside the catch block prints:

                  Message: sqlite_factory( ): unable to open database: /sbin/sqlite.db
                  File: /www/docroot/sqlite.php

When you detect an error outside of the constructor, as in the query( ) method in Example 4-2, use the lastError( ) method to retrieve the error code. To convert this number to a human-understandable message, use sqlite_error_string( ). The function sqlite_error_string( ) is not an object method, because it is static and does not vary between database instances.

In-Memory Tables

For extra-fast access, SQLite supports storing tables in RAM instead of on disk. Unfortunately, these tables do not persist across requests, so you cannot create them once and then refer to them again and again; instead, they need to be created each time a page loads. Therefore, these tables are best used in applications that load in lots of data up front and then make a series of requests, such as a report generation script.

To tell SQLite to use an in-memory database, pass the token :memory: as your database name:

sqlite_open(':memory:');
sqlite_query('CREATE TABLE...');

Besides the special database name, there’s no difference between using in-memory and on-disk tables. You interact with them using the same set of PHP and SQL commands.