Sporting a footprint of less than 250KB and requiring minimal administration, SQLite is a tiny database engine that packs a powerful punch. This article showcases this surprisingly capable database, and PHP’s ability to interface with it to create robust data-driven web applications.

Introduction
SQLite is a compact, multiplatform SQL database engine written in C. Practically SQL 92 compliant, it offers many of the core database management features made available by competing products such as MySQL, Oracle, and PostgreSQL. Additionally, SQLite offers considerable savings in terms of cost, learning curve, and administration investment.

Some of SQLite’s more compelling characteristics include:

• SQLite stores an entire database in a single file, allowing for easy backup and transfer.
• SQLite’s entire database security strategy is based entirely on the executing user’s fi le permissions. So, for example, user web might own the web server daemon process, and through a script executed on that server, attempt to open and write to an SQLite database named mydatabase.db. Whether this user is capable of doing so depends entirely on the mydatabase.db permissions.
• SQLite offers default transactional support, automatically integrating commit and rollback support.
• SQLite is available under a public domain license (it is free) for both the Microsoft Windows and Unix platforms.

This section offers a brief guide to the SQLite command-line interface. The reason for doing so is twofold. First, it provides you with an introductory look at this useful client. Second, the steps demonstrated create the data that will serve as the basis for all subsequent examples in this article.

Installing SQLite
To install SQLite, head on over to the SQLite home page, and download one of the precompiled binaries available for both Linux and Windows. The source code is also available if you’d like to build it yourself.

As of PHP 5.0, the SQLite extension was enabled by default. Enabling the extension was made optional with the version 5.1 release. The SQLite extension requires PDO, meaning that PHP 5.1+ users will need to add the following two lines to the php.ini file and then restart Apache:

extension=php_pdo.dll
extension=php_sqlite.dll

Note that if you are interested in using SQLite solely from PHP, you do not need to install the SQLite binary, as the entire database engine is bundled with the extension.

Using the SQLite Command-Line Interface
The SQLite command-line interface offers a simple means for interacting with the SQLite database server. With this tool, you can create and maintain databases, execute administrative processes such as backups and scripts, and tweak the client’s behavior. Begin by opening a terminal window and executing SQLite with the help option:

%>sqlite –help

If you have downloaded SQLite version 3 for Windows, you need to execute it like so:

%>sqlite3 –help

In either case, before exiting back to the command line, you will be greeted with the command’s usage syntax and a menu consisting of numerous options. Note that the usage syntax specifies that a filename is required to enter the SQLite interface. This filename is actually the name of the database. When supplied, a connection to this database will be opened, if the executing user possesses adequate permissions. If the supplied database does not exist, it will be created, if the executing user possesses the necessary privileges.

Next, we will see how to use SQLite’s command-line program to create the database, table, and sample data. Although this section is not intended as a replacement for the documentation, it should be suffice for familiarizing yourself with the basic aspects of SQLite and its command-line interface.

As an example, let us create a test database named mydatabase. db, which consists of a single table, employee:

1. Open a new SQLite database. Because this database presumably does not already exist, the mere act of opening a non-existent database will first result in its creation:

%>sqlite mydatabase.db

2. Create a table:

sqlite>create table employee (
...> empid integer primary key,
...>name varchar(25),
...>title varchar(25));

3. Check the table structure for accuracy:

sqlite>.schema employee

Note that a period (.) prefaces the schema command. This syntax requirement holds true for all commands found under the ‘Help’ menu.

4. Insert a few data rows:

sqlite> insert into employee values(NULL,”Jason Gilmore”,”Chief Slacker”);
sqlite> insert into employee values(NULL,”Sam Spade”,”Technologist”);
sqlite> insert into employee values(NULL,”Ray Fox”,”Comedian”);

5. Query the table, to ensure that all is correct:

sqlite>select * from employee;

you should see:

1|Jason Gilmore|Chief Slacker
2|Sam Spade|Technologist
3|Ray Fox|Comedian

6. Quit the interface with the following command:

sqlite>.quit

PHP’s SQLite Library
The SQLite functions introduced in this section are similar to those found in the other PHP-supported database libraries such as MySQL and PostgreSQL. In fact, for many of the functions, the name is the only real differentiating factor. If you have a background in MySQL, picking up SQLite should be a snap. Even if you are entirely new to the concept, do not worry; you will likely find that these functions are easy to use.

SQLite Directives
One PHP configuration directive is pertinent to SQLite:

sqlite.assoc_case (0,1,2)

While SQLite uses (and retrieves) column names in exactly the same format in which they appear in the database schema, various other database servers attempt to standardize name formats by always returning them in uppercase letters. This dichotomy can be problematic when porting an application to SQLite, because the column names used in the application may be standardized in uppercase to account for the database server’s tendencies. To modify this behavior, you can use the sqlite.assoc_case directive. It determines the case used for retrieved column names. By default, this directive is set to 0, which retains the case used in the table definitions. If it is set to 1, the names will be converted to uppercase. If it is set to 2, the names will be converted to lowercase.

Opening a Connection
Before you can retrieve or manipulate any data located in an SQLite database, you must first establish a connection. Two functions are available for doing so, sqlite_open() and sqlite_popen().


sqlite_open()
The sqlite_open() function opens an SQLite database, first creating the database if it does not already exist. The filename parameter specifies the database name. The optional mode parameter determines the access privilege level under which the database will be opened, and is specified as an octal value (the default is 0666) as might be used to specify modes in Unix. Currently, this parameter is unsupported by the API. The optional error_message parameter is actually automatically assigned a value specifying an error if the database could not be opened. If the database is successfully opened, the function returns a resource handle pointing to that database. The syntax is as follows:

resource sqlite_open (string filename [, int mode [, string &error_message]])

Consider an example:

<?php
$sqldb = sqlite_open
(“/home/book/22/mydatabase.db”)
or die(“Could not connect!”);
?>

This either opens an existing database named mydatabase.db, creates a database named mydatabase.db within the directory /home/book/22/, or results in an error, likely because of privilege problems. If you experience problems creating or opening the database, be sure the user owning the web server process possesses adequate permissions for writing to this directory.

The function sqlite_popen() operates identically to sqlite_open() except it uses PHP’s persistent connection feature in an effort to conserve resources.

Creating a Table in Memory
Sometimes your application may require database access performance surpassing even that offered by SQLite’s default behavior, which is to manage databases in self-contained files. To satisfy such desires, SQLite supports the creation of in-memory (RAM-based) databases, accomplished by calling sqlite_open() like so:

$sqldb = sqlite_open(“:memory:”);

Once open, you can create a table that will reside in memory by calling sqlite_query(), passing in a CREATE TABLE statement. (sqlite_query() is introduced later in the article.) Keep in mind that such tables are volatile, disappearing once the script has finished
executing!

Closing a Connection
Good programming practice dictates that you close pointers to resources once you are finished with them. This maxim holds true for SQLite; once you have completed working with a database, you should close the open handle. One function, sqlite_close(), accomplishes just this.

sqlite_close()
The function sqlite_close() closes the connection to the database resource specified by dbh. You should call it after all necessary tasks involving the database have been completed. This is the syntax:

void sqlite_close (resource dbh)

Following is an example of how you can use it:

<?php
$sqldb = sqlite_open(“mydatabase.db”);
// Perform necessary tasks
sqlite_close($sqldb);
?>

Note that if a pending transaction has not been completed at the time of closure, the transaction will automatically be rolled back.

Querying a Database
The majority of your time spent interacting with a database server takes the form of SQL queries. Using sqlite_query() you can query SQLite and return the subsequent result sets.

sqlite_query()
The sqlite_query() function executes a SQL query against the database specified by dbh. If the query is intended to return a result set, FALSE is returned if the query fails. All other queries return TRUE if the query was successful, and FALSE otherwise. To provide a practical example, other functions are used in this example that have not yet been introduced. Not to worry; just understand that the sqlite_query() function is responsible for sending and executing a SQL query. Soon enough, you will learn the specifics regarding other functions used in the example. The following script:

<?php
$sqldb = sqlite_open(“mydatabase.db”);
$results = sqlite_query($sqldb, “SELECT * FROM employee”);
while (list($empid, $name) = sqlite_fetch_array($results)) {
echo “Name: $name (Employee ID: $empid) <br />”;
}
sqlite_close($sqldb);
?>

yields the following results:

Name: Jason Gilmore (Employee ID: 1)
Name: Sam Spade (Employee ID: 2)
Name: Ray Fox (Employee ID: 3)

Note that sqlite_query() will only execute the query and return a result set (if one is warranted); it will not output (or offer) any additional information regarding the returned data. To obtain such information, you need to pass the result set into one or several other functions, all of which are introduced in the following sections. Furthermore, sqlite_query() is not limited to executing SELECT queries. You can use this function to execute any supported SQL-92 query.

Parsing Result Sets
Once a result set has been returned, you will likely want to do something with the data. This section introduces one of the most commonly used functions for doing so, sqlite_fetch_array().

sqlite_fetch_array()
The sqlite_fetch_array() function returns an associative array consisting of the items found in the result set’s next available row, or returns FALSE if no more rows are available. The optional result_type parameter can be used to specify whether the columns found in the result set row should be referenced by their integer-based position in the row or by their actual name. Specifying SQLITE_NUM enables the former, while SQLITE_ASSOC enables the latter. You can return both referential indexes by specifying SQLITE_BOTH. Finally, the optional decode_binary parameter determines whether PHP will decode the binary-encoded target data that had been previously encoded using the function sqlite_escape_string(). See the PHP manual for more information about this function. This is the syntax for sqlite_fetch_array():

array sqlite_fetch_array (resource result
[, int result_type [, bool decode_binary])

Consider the following example:

<?php
$sqldb = sqlite_open(“mydatabase.db”);
$results = sqlite_query($sqldb, “SELECT * FROM employee”);
while ($row = sqlite_fetch_array($results,SQLITE_BOTH)) {
echo “Name: $row[1] (Employee ID: “.$row[‘empid’].”)<br />”;
}
sqlite_close($sqldb);
?>

which returns:

Name: Jason Gilmore (Employee ID: 1)
Name: Sam Spade (Employee ID: 2)
Name: Ray Fox (Employee ID: 3)

Note that the SQLITE_BOTH option was used so the returned columns could be referenced both by their numerically indexed position and by their name. Although it is not entirely practical, this example serves as an ideal means for demonstrating the function’s flexibility.

One great way to render your code a tad more readable is to use PHP’s list() function in conjunction with sqlite_fetch_array(). With it, you can both return and parse the array into the required components all on the same line. Let us revise the previous example to take this idea into account. See Listing 1.

Listing 1


Determining the Number of Rows Returned and Modified
Two functions are available for learning more about the total number of rows returned from a SELECT query and for determining the number of rows modified by an UPDATE or DELETE query. Both are introduced in the next section.

sqlite_num_rows()
The sqlite_num_rows() function returns the number of rows located in the result_set. This is the syntax:

int sqlite_num_rows (resource result_set)

An example follows:

<?php
$sqldb = sqlite_open(“mydatabase.db”);
$results = sqlite_query($sqldb, “SELECT * FROM employee”);
echo “Total rows returned: “.sqlite_num_rows($results).”<br />”;
sqlite_close($sqldb);
?>

This returns:

Total rows returned: 3

sqlite_changes()
The sqlite_changes() function returns the total number of rows affected by the most recent modification query. For instance, if an UPDATE query modified a field located in 12 rows, then executing the function following that query would return 12.


Creating and Overriding SQLite Functions
An intelligent programmer will take every opportunity to reuse code. Because many database driven applications often require the use of a core task set, there are ample opportunities to reuse code. Such tasks often seek to manipulate database data, producing some sort of outcome based on the retrieved data. As a result, it would be convenient if the task results can be directly returned via the SQL query, like so:

sqlite>SELECT convert_salary_to_gold(salary)
...> FROM employee WHERE empID=1”;

PHP’s SQLite library offers a means for registering and maintaining customized functions such as this. The next section explains how to accomplish this.

Listing 2



sqlite_create_function()
The sqlite_create_function() function enables you to register custom PHP functions as SQLite user-defined functions (UDFs). Following is the syntax:

boolean sqlite_create_function (resource dbh, string func, mixed callback [, int num_args])

For example, this function would be used to register the convert_salary_to_gold() function discussed in the opening paragraphs of this section. See Listing 2. Assuming the user, Jason, makes $10,000 per year, you can expect the following output:

The employee can purchase 25 ounces.

Creating Aggregate Functions
When you work with database-driven applications, it is useful to derive some value based on a collective calculation of all values found within a particular column or set of columns. Several such functions are commonly made available within a SQL server’s core functionality set. A few such commonly implemented functions, known as aggregate functions, include sum(), max(), and min(). However, you might require a custom aggregate function not otherwise available within the server’s default capabilities. SQLite
compensates for this by offering the ability to create your own. The function used to register your custom aggregate functions, sqlite_create_aggregate(), is introduced in this section.

sqlite_create_aggregate()
The sqlite_create_aggregate() function is used to register a userdefined aggregate function, step_func. Actually, it registers two functions:

• step_func is called on every row of the query target
• final_func is used to return the aggregate value back to the caller

Once registered, you can call final_func within the caller by the alias ‘func’. The optional num_args parameter specifies the number of parameters the aggregate function should take. Although the SQLite parser attempts to discern the number if this parameter is omitted, you should always include it for clarity sake. This is the syntax:

boolean sqlite_create_aggregate
(resource dbh, string func, mixed step_func, mixed final_func [, int num_args])

Consider an example: building on the salary conversion example from the previous section, suppose you want to calculate the total amount of gold that employees could collectively purchase, you can whip up a script similar to that shown in Listing 3. If your employees’ salaries total $16,000, you could expect the following output:

The employees can purchase 40 ounces.

Listing 3


Conclusion
The administrative overhead required of many database servers often outweighs the advantages of the added power they offer to many projects. SQLite offers an ideal remedy to this dilemma, providing a fast and capable back-end at a cost of minimum maintenance. Given SQLite’s commitment to standards, ideal licensing arrangements
and quality, consider saving yourself time, resources and money by using SQLite for your future projects.

About the Author

W. Jason Gilmore has been immersed in open source technologies for more than eight years. His writings on technology have been featured in numerous industry publications, and have been adopted for use within United Nations and Ford Foundation educational programs. Jason is the author of three books, including the most recent and best selling “Beginning PHP and MySQL 5”, Second Edition, and with co-author Robert Treat, “Beginning PHP and PostgreSQL 8”. These days, Jason divides his time between running Apress’ Open Source program, experimenting with spatially-enabled web applications, and slowly remodeling his home. Visit Jason’s web site at www.wjgilmore.com.