Upgrading to PHP 5

Chapter 4. SQLite

Substituting text files for a database is like cutting a fish with a hammer. You might get it to work, but it’s going to be a really messy process. When your application needs a server-side storage mechanism but you can’t rely upon the presence of a specific database, turn to SQLite. It correctly handles locking and concurrent accesses, the two big headaches with home-brewed flat files.

Since the SQLite database is bundled with PHP 5, now every PHP 5 script can read, write, and search data using SQL. SQLite differs from most databases because it is not a separate application. Instead, SQLite is an extension that reads from and writes to regular files on the hard drive. Any PHP users who have permission to manipulate files can use SQLite to edit a database, just like they can use GD to edit images.

Although the name SQLite hints at a less than full-featured product, SQLite actually supports almost all of SQL92, the SQL standard specification. Besides the usual INSERTs and SELECTs, with SQLite you can also use transactions, query using subselects, define your own functions, and invoke triggers.

SQLite actually performs most actions more quickly than many other popular databases. In particular, SQLite excels at SELECTing data. If your application does an initial (or periodic) data INSERT and then reads many times from the database, SQLite is an excellent choice. The PHP web site uses SQLite to handle some forms of searches.

Unfortunately, SQLite has some downsides. Specifically, when you update the database by adding new data, SQLite must lock the entire file until the alteration completes. Therefore, it does not make sense in an environment where your data is constantly changing. SQLite does not have any replication support, because there’s no master program to handle the communication between the master database and its slaves.

Additionally, SQLite has no concept of access control, so the GRANT and REVOKE keywords are not implemented. This means you cannot create a protected table that only certain users are allowed to access. Instead, you must implement access control by using the read and write permissions of your filesystem.

SQLite is not for sites that are flooded with heavy traffic or that require access permissions on their data. But for low-volume personal web sites and small business intranet applications, SQLite lets you do away with the burden of database administration. SQLite is also perfect for log file analysis scripts and other applications that benefit from a database but whose authors don’t want to require the user to install one. SQLite is bundled with PHP 5, so unless it has been specifically omitted, it’s part of every PHP 5 installation.

The SQLite home page (http://www.sqlite.org/) has more details about SQLite’s features, limitations, and internals. A list of PHP’s SQLite functions is online at http://www.php.net/sqlite.

This chapter starts off with SQLite basics: creating databases, passing SQL queries to SQLite, and retrieving results—everything you need to start using SQLite. It then moves on to alternative SQLite retrieval functions and interfaces, including a nifty object-oriented interface. After covering how to talk with SQLite, this chapter shows how to improve SQLite performance with indexes and how to gracefully handle errors. It closes with a few advanced features: transactions and user-defined functions, which help keep your data consistent and extend SQLite, respectively.

SQLite Basics

It’s easy to get up and running with SQLite. Its design eliminates the need for any configuration variables, such as a database server name or a database username and password. All you need is the name of a file where the data is stored:

$db = sqlite_open('/www/support/users.db');
sqlite_query($db, 'CREATE TABLE users(username VARCHAR(100),
                                      password VARCHAR(100))');

This creates a users table stored in the database file located at /www/support/users.db. When you try to open a database file that doesn’t already exist, SQLite automatically creates it for you; you don’t need to execute a special command to initialize a new database.

If you cannot seem to get SQLite to work, make sure you have both read and write permission for the location on the filesystem where you’re trying to create the database.

SQLite has even fewer data types than PHP—everything’s a string. While you can define a column as INTEGER, SQLite won’t complain if you then INSERT the string PHP into that column. This feature (the SQLite manual declares this a feature, not a bug) is unusual in a database, but PHP programmers frequently use this to their advantage in their scripts, so it’s not a completely crazy idea. A column’s type matters only when SQLite sorts its records (what comes first: 2 or 10?) and when you enforce UNIQUEness (0 and 0.0 are different strings, but the same integer).

The table created in this example has two columns: username and password. The columns’ fields are all declared as VARCHARs because they’re supposed to hold text. Although it doesn’t really matter what type you declare your fields, it can be easier to remember what they’re supposed to hold if you give them explicit types.

Inserting Data

Add new rows to the database using INSERT and sqlite_db_query( ):

$username = sqlite_escape_string($username);
$password = sqlite_escape_string($password);

sqlite_query($db, "INSERT INTO users VALUES ('$username', '$password')");

You must call sqlite_escape_string( ) to avoid the usual set of problems with single quotes and other special characters. Otherwise, a password of abc'123 will cause a parser error. Don’t use addslashes( ) instead of sqlite_escape_string( ), because the two functions are not equivalent.

Retrieving Data

To retrieve data from an SQLite database, call sqlite_query( ) with your SELECT statement and iterate through the results:

$r = sqlite_query($db, 'SELECT username FROM users');
while ($row = sqlite_fetch_array($r)) {
    // do something with $row
}

By default, sqlite_fetch_array( ) returns an array with the fields indexed as both a numeric array and an associative array. For example, if this query returned one row with a username of rasmus, the preceding code would print:

               Array (
                   [0] => rasmus
                   [username] => rasmus
               )

As you can see, sqlite_fetch_array( ) works like mysqli_fetch_array( ).

When you’re using user-entered data in a WHERE clause, in addition to calling sqlite_escape_string( ), you must filter out SQL wildcard characters. The easiest way to do this is with strtr( ):

$username = sqlite_escape_string($_GET['username']);
$username = strtr($username, array('_' => '\_', '%' => '\%'));

$r = sqlite_query($db, 
                  "SELECT * FROM users WHERE username LIKE '$username'");

Use sqlite_num_rows( ) to find the total number of rows returned by your query without iterating through the results and counting them yourself:

$count = sqlite_num_rows($r);

You can call sqlite_num_rows( ) without retrieving the results from SQLite. Remember, this function takes the query result handle, like sqlite_fetch_array( ).

If speed is a concern, use sqlite_array_query( ). This retrieves all the data and puts it into an array in a single request:

$r = sqlite_array_query($db, 'SELECT * FROM users');
foreach ($r as $row) {
    // do something with $row
}

However, if you have more than 50 rows and only need sequential access to the data, use sqlite_unbuffered_query( ):

$r = sqlite_unbuffered_query($db, 'SELECT * FROM users');
while ($row = sqlite_fetch_array($r)) {
    // do something with $row
}

This is the most efficient way to print items in an XML feed or rows in an HTML table because the data flows directly from SQLite to your PHP script without any overhead tracking behind the scenes. However, you can’t use it with sqlite_num_row( ) or any function that needs to know the “current” location within the result set.

When you are done with the connection, call sqlite_close( ) to clean up:

sqlite_close($db);

Technically, this is not necessary, since PHP will clean up when your script finishes. However, if you open many SQLite connections, calling sqlite_close( ) when you’re finished reduces memory usage.

SQLite Versus MySQL

The SQLite function names are similar to the MySQL functions, but not identical. Table 4-1 provides a side-by-side comparison of the two.

Table 4-1. Comparison of major MySQL and SQLite function names

MySQL	SQLite
mysqli_connect( )	sqlite_connect( )
mysqli_close( )	sqlite_close( )
mysqli_query( )	sqlite_query( )
mysqli_fetch_row( )	sqlite_fetch_array( )
mysqli_fetch_assoc( )	sqlite_fetch_array( )
mysqli_num_rows( )	sqlite_num_rows( )
mysqli_insert_id( )	sqlite_last_insert_rowid( )
mysqli_real_escape_string( )	sqlite_escape_string( )