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 INSERT s and SELECT s, 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 SELECT ing 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.