EXTENDING SQLITE

SQLite comes packaged with PHP 5. It has advanced capabilities and a built-in object-
oriented (OO) interface. Examining the classes and methods of SQLite is the ostensible
reason for including this chapter—but that’s not the only reason. SQLite is a great addition to PHP, but because MySQL is so entrenched, programmers tend to ignore SQLite.
Don’t let the “Lite” in SQLite lead you to underestimate the capabilities of this database. Because it is bundled with PHP, there is no external server to worry about—think of it as “Lite” in the sense of “no extra baggage.” In some situations it is the ideal database to use. Its advanced features can help simplify your code and create an application that outperforms other solutions.
In this chapter, we will develop a link management application using a class derived from the SQLite database class. A minimum of PHP version
5.0.5 is a requirement. (Prior to this version the SQLite database class is declared as final, so it cannot be extended.)

Brief Overview

Relevant sections of code will be reproduced here, but, as usual, the entire application is available for download on the companion website. The front end for this application will display alphabetically ordered website links, as shown in Figure 15-1.

An alphabetic navigation bar of hyperlinks will make any specific link easily accessible. Recently added links will be highlighted, making the list even more useful to regular visitors.
A submission form will allow visitors to suggest additional links. These links will not appear on the site until they have been reviewed. There will be a back end to review and maintain links.

Directory Structure

Because of the number of files in the download for this chapter, it’s helpful to make a few comments about the way the files are organized. Download and decompress the files to follow along.
The front-end capabilities of this application are accessible from the links in the index.php file in the top level directory and the back end is found using the index.php file in the linkmanagement directory. On a production server the linkmanagement directory would be password protected but for ease of use that hasn’t been done here.
For reasons of version compatibility, the database file itself is not included with the downloads. It should be installed in the dbdir directory. Version 2.8.17 of SQLite was used to test this application (but if you are already up and run- ning with another version of SQLite you shouldn’t run into any problems). Install the database from the db_install_script.php file (also included in the dbdir directory). Instructions on how to do this will follow shortly.

How It’s Done

In this application we take advantage of some of SQLite’s advanced capa- bilities. 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 inter- ested 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).

Getting Started

SQLite comes bundled with PHP 5, so all you have to do to install the database is run the db_install_script.php file.
However, if you do things this way you’ll have to write code just to view your data or to examine the structure of your database. You might want to download the command-line version of SQLite instead. PHP 5, depending upon the subversion number, comes with SQLite versions 2.8.11 through
2.8.17. To find out which version is running on your system, display the
results of the PHP function phpinfo in your browser and search for SQLite. For convenience, you might want to install the binary of sqlite in the same directory as your database.
Creating a database is as simple as typing the name of the SQLite executable file at the command line followed by the database name—for example, sqlite resources.sqlite. Doing so will run sqlite and create or open an existing database of the specified name. You can now create a table using SQL from the command line. However, let me make one more suggestion. At some point you will want to dump your database, and if you have created it from the command line the output won’t be very readable.

If you use a text editor to format your CREATE TABLE statement and then redirect this file to the database, the results will be much more acceptable. Do this whenever you create tables, views, or triggers.

NOTE Precompiled binaries for most operating systems are available at the SQLite download page (http://sqlite.org/download.html). For compatibility reasons it is important to get the command-line version number that matches the version incorporated into PHP. At the SQLite site you may have difficulty finding older versions. If there is no link to the version you require, enter the URL http://sqlite.org, followed by the version number
you require, into the address bar of your browser—for example, http://www.sqlite.org/ sqlite-2_8_16.zip. You might get away with using a slightly higher or lower version number, but version 3 databases are an entirely different format from version 2, so do not attempt to use the version 3 command-line tool with a version 2 database.

The database used in this application is called resources.sqlite and is stored in a subdirectory named dbdir. If you haven’t already created it using the db_install_script.php file, you can do so by redirecting the dump.sql file from the command line in the following way:

sqlite resources.sqlite < dump.sql

A database dump is formatted as a transaction, so, if this command worked properly, you’ve already used one of SQLite’s advanced features.
You can test that the database has been installed by executing a SELECT statement. Typing SELECT * FROM tblresources; should display all the records in the resources table.

Creating a Table

The SQL used to create the tblresources table in our database is shown in
Listing 15-1.

CREATE TABLE tblresources(
id INTEGER PRIMARY KEY,
url VARCHAR(255) NOT NULL UNIQUE default '', email VARCHAR(70) NOT NULL default '', precedingcopy VARCHAR(100) NOT NULL default '', linktext VARCHAR(255) NOT NULL default '', followingcopy VARCHAR(255) NOT NULL default '', target VARCHAR(35) default '_blank',
category VARCHAR(100) NOT NULL default '',
theirlinkpage VARCHAR(100) default NULL, whenaltered TIMESTAMP default '0000-00-00', reviewed BOOLEAN default 0,
whenadded DATE default '2006-05-05');

Listing 15-1: Creating a table

Let’s have a look at the details.
To create a table with an autonumber field named id, the data type INTEGER is used in conjunction with PRIMARY KEY. This is equivalent to identifying a field as INTEGER auto_increment PRIMARY KEY in MySQL. In SQLite this field definition is the one exception to the rule that SQLite fields are typeless—otherwise all fields are strings. Creating fields as types other than string helps document the data types you are expecting but will not restrict the value entered. You can put a string into a float type field and a float into a Boolean. Further, specify- ing the length of a VARCHAR type field will not truncate data that exceeds the defined length. Any length of string can be entered into any field. Otherwise, the syntax for creating a table functions exactly as you might expect.
The field names used in creating this table are self-documenting, but a few comments are in order. A resource won’t be displayed until the reviewed field is set to true. The field with the data type TIMESTAMP whenaltered will be maintained using a trigger as will the whenadded field.

Views

Views are stored SELECT queries. If you repeatedly use the same query, it is worthwhile creating it as a view.
To make resource links easily accessible, let’s order them alphabetically and create hyperlinks to each letter of the alphabet. With this in mind, take a look at the alphabet view shown in Listing 15-2.

CREATE VIEW alphabet AS
SELECT DISTINCT UPPER(SUBSTR(linktext,1,1)) AS letter
FROM tblresources
WHERE reviewed = 1
ORDER BY letter;
CREATE VIEW specific_link AS SELECT id, url,
(precedingcopy || ' ' || linktext || ' ' || followingcopy) AS copy
FROM tblresources;

Listing 15-2: Views

The alphabet view creates a row of links as pictured at the top of
Figure 15-1.
Rather than repeat the SQL statement that makes up the alphabet view, we can instead simply SELECT * FROM alphabet using the name of the view in the FROM clause.
The second view, specific_link, also shown in Listing 15-2, demonstrates how a view can be “updated” when used in conjunction with a trigger. We will return to this view in the following discussion about triggers, but do note the use of || as the string concatenation operator.
As you can see, SQLite defines its own string manipulation functions. For a complete list of functions and operators, see www.sqlite.org/lang_expr.html.

Triggers

For those programmers who pride themselves on their laziness, triggers are a wonderful thing. By creating a trigger you can get maximum effect with mini- mum effort.
Triggers are activated by an INSERT, DELETE, or UPDATE SQL statement. They are often used to maintain referential integrity and avoid orphaned records— for example, deleting an invoice might well trigger deletion of all related invoice items. We’re going to create three triggers for our application: one to mimic a timestamp field, another to show the advantages of laziness, and finally a trigger to demonstrate how a view can be “updated.”
The timestamp triggers are shown in Listing 15-3. They are activated whenever a record in the tblresources table is added or updated.

CREATE TRIGGER insert_resources AFTER INSERT ON tblresources
BEGIN
UPDATE tblresources SET whenaltered = DATETIME('NOW','LOCALTIME') WHERE id = new.id;
END;
CREATE TRIGGER update_resources AFTER UPDATE ON tblresources
BEGIN
UPDATE tblresources SET whenaltered = DATETIME('NOW','LOCALTIME') WHERE id = new.id;
END;
CREATE TRIGGER add_date AFTER INSERT ON tblresources
BEGIN
UPDATE tblresources SET whenadded = DATE('NOW','LOCALTIME') WHERE id = new.id;
END;
CREATE TRIGGER delete_link INSTEAD OF DELETE ON specific_link
FOR EACH ROW BEGIN
DELETE FROM tblresources
WHERE id = old.id; END;

Listing 15-3: Triggers

There is no need to remember to update the whenaltered field each time a change is made to a record—the insert_resources and update_resources triggers will do this for you. The current date and time will be added in the background. Effectively, this field will now function like a MYSQL TIMESTAMP field.
Likewise with the add_date trigger, also shown in Listing 15-3. We want to highlight new links. This trigger makes it possible to capture the date a link is added. By using a trigger we don’t have to worry about forgetting to main- tain this field, and we don’t have to write additional code each time a record is added.

Creating a trigger on a view is a convenient way of performing an “update” against a view. By themselves, views are not updatable. If you attempt to delete from a view that has no associated trigger, you’ll get a warning like the following:

Warning: SQLiteDatabase::query() [function.query]: cannot modify specific_link because it is a view...

We solved this problem in the trigger we created on the view specific_link shown in Listing 15-3. Because we used an INSTEAD OF clause, any attempt to delete from this view instead removes the appropriate record from the table, tblresources.
In this trigger we have specified FOR EACH ROW. Doing so is optional. A FOR EACH STATEMENT clause also exists but is not yet supported.
The WHERE clause of a trigger is somewhat intuitive but may cause some confusion. Using new.id to specify a newly inserted record and old.id for a deleted record clearly makes sense. Either old or new may be used when a record is updated.
Using triggers is very convenient, although the same effect could be achieved programmatically. But because triggers are embedded in the data- base, they are activated even when you make changes from the command line. Triggers help maintain the integrity of your database when it is modified outside of your application. Laziness has its rewards.

PHP Implementation of SQLite

For the most part, the OO methods of SQLite are exactly the same as the procedural functions. The only difference is that the leading sqlite is dropped and the studly caps naming convention is used in place of under- scores (although some methods added in version 5.1 don’t quite follow this rule). Method parameters are the same as those used with the procedural functions, except that there is no need to pass a resource handle since the object itself is the handle. A few functions are only available in a procedural form; these will be mentioned where appropriate.
There are three built-in, ready-to-use SQLite objects: an SQLite database, a buffered result set, and an unbuffered result set. All three classes will be used in this chapter, but the focus will be on the database class.

Extending SQLiteDatabase

One of the nice things about object-oriented programming (OOP) is excep- tion handling. Procedural error trapping is not only tedious, it clutters up your code and can make it unreadable. Taking an OO approach and using exception handling sounds like the ideal solution—until you realize that the database constructor is the only method of all the SQLite classes that throws

an exception. If you want to check for errors when creating result sets, you are stuck using procedural code. It looks like we’re right back where we started.
We’ll next discuss how this can be fixed.

Override the Query Methods

The simple solution to this problem is inheritance. On the surface, this would seem fairly straightforward: Create a class that extends SQLiteDatabase and override all the query methods. If errors arise within those overridden methods, simply throw an exception. In this way, the messy details of error trapping can be buried inside the class file and a single catch block can handle all errors. The first five methods in the class definition file shown in Listing 15-4 do exactly this.

Error Messages

The comment immediately inside each method definition shows the method prototype as defined on the PHP site. This is especially useful because it shows the type of object returned. Some of the base class methods take an optional string reference argument (&$error_msg).

NOTE In versions of PHP prior to 5.1, passing in this string reference results in this warning:
SQLiteDatabase::query() expects at most 2 parameters, 3 given.

The reason a third parameter is necessary is explained as follows (from http://php.net/sqlite_query):

... [$error_msg] will be filled if an error occurs.

This is especially important because SQL syntax errors can’t be fetched using the [sqlite_last_error()] function.

Quite true. The sqlite_last_error function returns an uninformative message: SQL logic error or missing database. Our code doesn’t make use of this error message but this isn’t an insurmountable problem. A more
specific error message would certainly help in the debugging process, how- ever. Fortunately, if you have warnings turned on while you are developing,

you will get something more meaningful. Forcing a warning by referencing a nonexistent table results in the following, more specific, output:

Warning: SQLiteDatabase::query()[function.query]: no such table: tblnonexistent...

Query Methods

Look again at Listing 15-4. It includes the five methods for creating result sets. The buffered and unbuffered methods are fairly self-explanatory—you are probably quite familiar with the equivalent MySQL functions. However, MySQL (prior to the MySQL improved extension) has nothing to match the singleQuery, queryExec, or arrayQuery methods. Let’s look at these methods in more detail.
The singleQuery method is a recent addition, and the PHP site warns that it is not currently documented. Let’s carry on regardless because this method looks especially useful for those situations where a query returns only one row—when using the COUNT function to return the number of records in a table, for example. Here’s one view of how this method ought to behave: This method returns only one record, and no result set is created. If the second argument is false, the value returned is an array of the first row. If the sec- ond argument is true, then only the first column of the first row is returned, and it is returned as a scalar value.
This speculation may make the best sense of how this method ought to work, but it doesn’t describe what actually happens. In fact, this method only ever returns the first column and any number of rows. If the second argument is false, then an array is returned; if the second argument is true and only one row is returned, a scalar is returned. On the PHP site, this second argument is identified as bool first_row_only and the return type is identified as an array. It looks like the return type should be mixed. In any case, this method doesn’t yet work the way it ought to. We were warned.
There is no requirement that you use the singleQuery method instead
of query. As with MySQL, you can always create a result set and then use the appropriate fetch function to retrieve the value of the first row or a specific field. But why return an object or an array when all that’s needed is the value of one column? You may use the singleQuery method for any kind of query— data manipulation or otherwise—but it was designed specifically for situa- tions where a single value or single column is returned, and is presumably optimized for this situation.
As you can see, there is also an arrayQuery method. Like the singleQuery method, this method allows us to directly copy results into an array, bypassing the intermediate step of creating a result set. This method is best used when a limited number of records are returned.
MySQL versions prior to 4.1 have no equivalent to the queryExec method of SQLite because queryExec is specifically designed for use with multiple queries. Multiple, semicolon-separated queries may be passed as a single query string to queryExec. (The install script uses this method to create the tables, triggers, and views and to insert records into the tblresources table.) This method gives significant performance improvements over repeated querying and performs

the same job as the MySQL-improved (the mysqli functions added to PHP 5 to support MySQL 4.1) method, mysqli_multi_query. If you like, you can of course use this method to execute a single query.

query

Use of this method to create an SQLiteResult object is shown in Listing 15-5.

$db = new SQLiteDatabasePlus('resources.sqlite');
//alphabet view
$strsql = "SELECT * FROM alphabet";
//use buffered result set to get number of rows
$result = $db->query($strsql);
//create alphabet here if($result->numRows() > 0){
echo get_alphabet($result);
}

Listing 15-5: query method returns a buffered result set

Remember, an SQLiteResult is buffered so you can use the numRows method with this result set. It is also iterable, so this result set may be used in a foreach loop. In this, SQLite differs from MySQL. Because SQLiteResult implements Iterator, all the iterator methods are present—rewind, next, valid, and current. These methods can be used directly, but their real purpose is to allow an SQLite result set to be used in a foreach loop in exactly the same way that you might use an array. (As you might expect, the rewind method can’t be applied to an unbuffered result set.) Only this method and the unbuffered query method return a result set object.

unbufferedQuery
There is no need to buffer the result set returned in Listing 15-6.

try{

$db = new SQLiteDatabasePlus('../dbdir/resources.sqlite');
$type="Edit";
//retrieve from db
$strsql = "SELECT * FROM tblresources ". "WHERE id = '$id'";
//get recordset as row
$result = $db->unbufferedQuery($strsql);
$row = $result->fetch();
//can't use below because returns first column only
//$row = $db->singleQuery($strsql, false);
// assume vars same as fields while(list($var, $val)=each($row)) {
$$var=$val;

}
}catch(SQLiteException $e){
//debug msg
echo $e->getMessage();
}
}

Listing 15-6: The unbufferedQuery method

This listing shows an unbuffered query. In this case, a functional singleQuery method would be preferable because we know that only one record will be returned. However, given the problems with singleQuery, we use the unbufferedQuery method of an SQLiteDatabase object to create a result set object and then use the fetch method to copy the first row into an array.

arrayQuery

The PHP site warns against using the arrayQuery method with queries that return more than 45 records (a somewhat arbitrary number perhaps, but this method stores results in memory so returning a large number of records can exhaust memory). We’ve used this method in Listing 15-7.

...

$db = new SQLiteDatabasePlus('../dbdir/resources.sqlite');
$db->createFunction('class_id','set_class_id',0);
$sql = "SELECT id, url, email, ".
"(precedingcopy || ' ' || linktext || ' ' || followingcopy) ". "AS copy, linktext, reviewed, class_id() AS classid ".
"FROM tblresources ". "ORDER BY id DESC ".
"LIMIT $recordoffset,". PERPAGE;
//use arrayQuery
$resultarray = $db->arrayQuery($sql);

Listing 15-7: Using arrayQuery

As you can see, we know exactly how many records are returned because our SQL has a LIMIT clause. Again, this method allows us to bypass creation of a result set.

singleQuery
The code below uses the singleQuery method and does exactly what we need—it returns a single scalar value rather than a result set or an array.

$totalrecords = $db->singleQuery('Select COUNT(*) FROM
tblresources', true);

queryExec

This method is commonly used to process a transaction. Use the command- line command .dump to dump your database or view the file dump.sql. You’ll see that it is formatted as a transaction. You can recreate an entire database by passing this listing as a string to the queryExec method, as we have done with the install script, db_install_script.php.
The ability to perform multiple queries using one string does raise security issues. When using this query method, it is especially important to filter data in order to avoid a possible SQL injection attack.

Utility Methods

By overriding all the query methods of the SQLiteDatabase class we ensure that any failed query throws an exception. This done, we needn’t worry about error trapping whenever we perform a query. The remaining methods of our derived class are utility methods aimed at helping verify data posted from a form. These methods give us an opportunity to explore some of the ways to retrieve metadata from an SQLite database. Find those methods in Listing 15-8.

/**
Get all table names in database
*/
public function getTableNames(){
if (!isset($this->tablenames)){
$this->setTablenames();
}
return $this->tablenames;
}
/////////////////////////////////////////////////////////////
/**
Retrieve field names/types for specified table
*/
public function getFields($tablename){
if (!isset($this->tablenames)){
$this->setTablenames();
}
if (!in_array($tablename, $this->tablenames)){
throw new SQLiteException("Table $tablename not in database.");
}
$fieldnames = array();
$sql = "PRAGMA table_info('$tablename')";
$result = $this->unbufferedQuery($sql);
//no error - bad pragma ignored
//get name and data type as defined upon creation foreach ($result as $row){
$fieldnames[$row['name']] = $row['type'];
}
return $fieldnames;
}
//////////////////////////////////////////////////////////////
//private methods
/**
private method - initializes table names array
*/
private function setTableNames(){
$sql = "SELECT name ". "FROM sqlite_master ". "WHERE type = 'table' ". "OR type = 'view'";
$result = $this->unbufferedQuery($sql);
foreach ($result as $row){
$this->tablenames[] = $row['name'];
}
}

Listing 15-8: Metadata methods

The two methods that make use of metadata are setTableNames and getFieldNames. Let’s examine the method setTableNames in Listing 15-8. This method makes use of the table sqlite_master—a table that defines the schema for the database. By querying sqlite_master, we can retrieve the names of all the tables and views in the database. The type field defines the kind of resource, in our case a table or view. This method retrieves the names of all the tables and views and stores them in an array.
Ideally, this method would be called once from the constructor, but the constructor for an SQLite database is declared final, so it may not be overridden.
Pragmas perform a variety of functions in SQLite. One of those func- tions is to provide information about the database schema—about indices, foreign keys, and tables.
Running the pragma table_info returns a result set that contains the column name and data type. The data type returned is the data type used when the table was created. This may seem pointless—since, excepting one case, all fields are strings—but this information could be used to assist data validation. For example, with access to a data type description, we could programmatically enforce which values are allowed for which fields. Notice that the pragma table_info can also be used with views. However, when used with views, all field types default to numeric.
A word of warning about pragmas: They fail quietly, issuing no warning or error, and there is no guarantee of forward compatibility with newer versions of SQLite.

Getting Metadata

Metadata methods allow us to discover field names at runtime. This is useful when we want to match posted values to the appropriate field in a table. Fig- ure 15-2 shows the form that we will use to post data to the database.

As you can see in Listing 15-9, the cleanData method verifies that the keys of the posted array match table field names by calling the matchNames method. It throws an exception if they don’t. However, it also removes slashes if magic quotes are on. If you regularly use MySQL with magic quotes on, escaping data may be something you never give much thought to. However, unlike MySQL, SQLite does not escape characters by using a backslash; you must use the sqlite_escape_string function instead. There is no OO method for doing this.
There is no requirement to call the cleanData method, and there may be situations where its use is not appropriate—perhaps where security is a prime concern, so naming form controls with table field names is not advisable. How- ever, it is a convenient way of confirming that the right value is assigned to the right field.

User-Defined Functions

One of the requirements of our application is to highlight recently added links. We are going to achieve this effect by using a different CSS class for links that have been added within the last two weeks. Subtracting the value stored in the whenadded field from the current date will determine the links that satisfy this criterion. Were we to attempt this task using MySQL, we could add the following to a SELECT statement:

IF(whenadded > SUBDATE(CURDATE(),INTERVAL '14' DAY), 'new',
'old') AS cssclass

This would create a field aliased as cssclass that has a value of either new or old. This field identifies the class of the anchor tag in order to change its appearance using CSS. It’s much tidier to perform this operation using SQL rather than by manipulating the whenadded field from PHP each time we retrieve a row.
But SQLite has no date subtraction function. In fact, the SQLite site doesn’t document any date functions whatsoever. Does this mean that we are stuck retrieving the whenadded field from the database and then performing the date operations using PHP? Well, yes and no. SQLite allows for user-defined functions (UDFs). Let’s take a look at how this works.
The first thing to do is create a function in PHP to subtract dates—not a terribly difficult task. See the function check_when_added in Listing 15-10 for the implementation.

function check_when_added($whenadded){
//less than 2 weeks old
$type = 'old';
// use date_default_timezone_set if available
$diff = floor(abs(strtotime('now') - strtotime($whenadded))/86400);
if($diff < 15){
$type = 'new';
}

}
...

...

return $type;

//register function
$db-> createFunction('cssclass','check_when_added',1);
$strsql ="SELECT url, precedingcopy, linktext, followingcopy, ". "UPPER(SUBSTR(linktext,1,1)) AS letter, ". "cssclass(whenadded) AS type, target ".
"FROM tblresources ". "WHERE reviewed = 1 ". "ORDER BY letter ";
$result = $db->query($strsql);

Listing 15-10: A user-defined function

Also shown in Listing 15-10 is the createFunction method of an SQLite- Database, which is used to make check_when_added available from SQLite. Calling this function is as simple as adding the expression cssclass(whenadded) AS type to our SELECT statement. Doing this means that the result set will
contain a field called type with either a value of new or no value at all. We can use this value as the class identifier for each resource anchor tag. The new anchors can be highlighted by assigning them different CSS display
characteristics.
The back end of our application also makes use of a UDF; improved readability is the motivation behind its creation.
The set_class_id function in Listing 15-11 ( ) shows how the mod operator can be used in a UDF to return alternate values. When this value is used as the id attribute for a tr tag, text can be alternately shaded and unshaded by setting the style characteristics for table rows with the id set to shaded. Again, it is much tidier to return a value in our result set rather than to perform this operation from PHP. Once you are familiar with UDFs you’ll see more and more opportunities for using them. Be careful. Using them can become addictive.

//add function to SQLite function set_class_id(){
static $x = 0;
$class = 'unshaded';
if(($x % 2) == 0){
$class = "shaded";

}
...

}
$x++;
return $class;

$db = new SQLiteDatabasePlus('../dbdir/resources.sqlite');
$db->createFunction('class_id','set_class_id',0);

Listing 15-11: UDF shades alternate rows

You can’t permanently add a UDF to a database, but the ability to create them certainly compensates for the limited number of functions in SQLite, especially those related to date manipulation. In fact, in my view, this way of subtracting dates is much easier to implement because it doesn’t involve looking up or remembering the quirky syntax of functions such as the MySQL SUBDATE function referenced earlier. However, UDFs lack the performance benefits of built-in functions.

Uses and Limitations of SQLite

No database can be all things to all people. SQLite supports any number of simultaneous readers, but a write operation locks the entire database. Version 3 offers some improvement in this respect, but SQLite is still best used in appli- cations with infrequent database updates, like the one described here.
Of course, if access control is important, then SQLite is not the appropri- ate tool. Because GRANT and REVOKE are not implemented, there can be no
database-enforced user restrictions.
However, even a relatively modest application can make use of the advanced capabilities of SQLite. With the application discussed in this chapter, we haven’t had to sacrifice anything by using SQLite rather than MySQL. Unavailability of a timestamp field is remedied by use of a trigger. A UDF makes up for SQLite’s lack of date manipulation functions. In fact,
overall, we achieve better performance because there is no overhead incurred by a database server, and maintenance is reduced through the use of triggers.
Not only has using SQLite simplified our code through the use of views, triggers, and UDFs, as well as by extending the OO interface, but it also makes for cleaner code through its more varied ways of querying a database. In these or similar circumstances, SQLite is definitely a superior choice.