How It's Done

In this application we take advantage of some of SQLite's advanced capabilities. Both triggers and views will be used. A trigger, code that executes in response to an add, edit, or delete event, will be used to mimic a datestamp field—records will be automatically stamped whenever they are added or changed.

Views are a convenient way of storing queries and can replace tables in the FROM clause of a SELECT statement. They can also be used with triggers so that "updating" a view updates the associated table.

No database used in conjunction with PHP can escape comparison to MySQL. Where appropriate, I will point out differences in SQL syntax between SQLite and MySQL. Likewise, SQLite has a variety of different query methods. These will also be contrasted with MySQL functions.

As you have seen, throwing exceptions rather than trapping errors makes for cleaner code. SQLite has a built-in OO interface, and there is an SQLiteException class. However, only the SQLite database constructor throws exceptions. By extending the SQLite database class, we can override the query methods so that a failed query also throws an exception. This derived class will also include data verification methods that make use of metadata extracted from the database. This will be done by querying the sqlite_master table and through the use of pragmas. A pragma modifies the way the SQLite library works but can also be used to query the database structure. We're only interested in the second use.

A limited number of functions are available for use with SQLite's dialect of SQL. You'll see how this shortcoming can be overcome by creating user-defined functions (UDFs).