The Swiss army knife of data storage.
So, what is SQLite? It's a library that implements a large amount of the SQL 92 standard and stores data in a single file. In addition, the library is both small and fast (up to 2-3 times faster than MySQL and PostgreSQL in many cases), adding very little footprint (in terms of executable size and memory usage) to your application. Unlike the other SQL databases supported by PHP, there is no separate server process to manage the database - your application *is* the server. This means that multiple instances (or concurrent users, in web terminology) need to use file based locking to safely update the database. This is an important point, as it affects the uses to which you can apply SQLite - if your data is mostly read-only, then you have no problems, but if you need to make a very large number of concurrent updates, your application will spend more time negotiating locks than doing any real work. You can see a more complete list of features in the text box.
| SQLite features |
>Implements most of SQL 92, including triggers and transactions.
>Atomic commit and rollback protect data integrity.
>Database files can be shared
between machines with different byte orders.
>Supports databases up to 2 terabytes in size Small memory footprint
>Sources are public domain - use for any purpose |
|
Why would you want to use SQLite instead of a real database like MySQL, PostgreSQL or Oracle? Well, firstly, there is the question of administration - real databases are often quite complex to setup, or require special privileges. Shared hosting providers don't always provide a real database, or do so for a premium. These things can put SQL database access out of the reach of many PHP users. SQLite is an ideal solution to this problem, since it requires no special privileges or configuration. It stores the data in a single file and you can be up and running with an SQLite database in seconds. SQLite is not just for people to use on cheap shared hosts - larger applications can also benefit from its speed and flexibility - we are using SQLite in production on the php.net website to power the function lookup in our online manual (we get a LOT of traffic there).
Getting SQLite
Enough talk already, let's get SQLite installed. Using PHP 4.3.x, you can use the
pear command line tool to fetch and install it:
% pear install sqlite
If you are tough enough to be running a beta or snapshot version of PHP 5, you will find that SQLite is included and enabled by default in the base distribution.
Fig.1 pear install sqlite: Part 1
Fig.2 pear install sqlite: Part 2
Installing SQLite using the pear tool will download the package, compile and install it. Depending on your PHP installation, you might require root or administrative privileges to install the extension. Windows users can download a precompiled binary from the PHP snapshots web site (see the link at the end of this article). The screenshots show the output of the
pear install sqlite command. The boring part inbetween is left out. Note that your output might be slightly different, depending on your operating system and installed software.
Once compiled and installed, you need to adjust your
php.ini file so that it loads the extension (if you are running PHP 5, you don't need to modify your
php.ini)
. The lines that you need to add are:
; load the SQLite extension (UNIX)
extension=sqlite.so
; windows users will want to use this version instead
; extension=php_sqlite.dll
The PHP SQLite extension contains only the bindings for creating and accessing SQLite databases. It doesn't include the
sqlite command line tool which you can use to play with your databases. If you are a fan of the
mysql or
psql command line tools, you might miss this - if so, I recommend that you download and install the full SQLite distribution from the main SQLite website - you can find the link at the end of this article.
Using SQLite
The programming interface for the SQLite extension is similar to that for
mysql and
postgres extensions, although there are a couple of differences. One of my aims while writing the extension was not to put too much magic between the native C API of the library and the functions exposed to PHP, so keep that in mind when you wonder why some of the functions are different - they are different because the SQLite library is different, not because I wanted to name them differently ;-)
As I mentioned earlier, SQLite supports a large amount of SQL 92 standard - the full SQL language syntax can be found on the SQLite website (see the links section at the end of this article). However, the major difference between SQLite and other SQL databases is that SQLite is type less - or more correctly, it is loosely typed. All the fields are stored as NULL-terminated strings instead of as a binary representation of an integer, for example. When you create a table, you can specify any of the usual data types, such as VARCHAR, TEXT, INTEGER, FLOAT and so on, but these will be generally ignored by the SQLite database engine - in fact, you can completely omit them from your CREATE TABLE statement if you like.
The data type of a column or expression is only really important when it comes to sorting and comparing data. In this situation, the engine classes the column or expression as being either
numeric or
text. If the type is
text, then the strings are compared using a binary comparison. If the type is
numeric, the values are converted to floating point numbers and compared numerically.
Enough talk - time for some code. Listing 1 shows a simple script that creates a database, a table, inserts some data, and then queries it back out of the database again.
Listing 1: demonstrating all the basic features <?php
// open the database - will create it if it doesn't
// exist.
$db = sqlite_open("example.db") or
die("failed to open/create the database");
// now create the sample table
sqlite_query($db, "CREATE TABLE sample(email, name)");
// fill it with some data
$data = array(
'wez@php.net' => 'Wez Furlong',
'helly@php.net' => 'Marcus Boerger',
'derick@php.net' => 'Derick Rethans',
// sorry to all the other PHP developers
// for not listing them here too...
);
foreach ($data as $email => $name) {
$email = sqlite_escape_string($email);
$name = sqlite_escape_string($name);
sqlite_query($db,
"INSERT INTO sample(email, name) "
."VALUES ('$email', '$name')");
}
// Now pull it out again
$res = sqlite_query($db, "SELECT name, email from sample");
if (!$res) {
// This shouldn't happen :)
echo "No data";
} else {
while ($row = sqlite_fetch_array($res)) {
echo "row: $row[name] -> $row[email]\n";
}
}
?>
Hopefully, Listing 1 will make a lot of sense to you. It introduces four of the SQLite extension's functions - the four you will make most use of. The first of these is
sqlite_open which, not surprisingly, is responsible for returning a handle to a database. Unlike other databases, nothing special beyond accessing a plain file is required, so there is no need to pass in additional security options as parameters. There
are additional parameters, but you probably won't need them.
sqlite_open will open an existing database, or create one if it does not exist already. The important thing to remember here is that you need to have write permissions on the directory in which you are creating the database.
The next function is
sqlite_query which issues a query and returns a result handle.
sqlite_query will execute multiple SQL statements separated by semi-colon characters, which is useful for importing a batch of SQL statements from a file.
sqlite_query returns
false if the query failed, a result handle if the query returned results (such as a SELECT query) or
true if it was an UPDATE or INSERT query that succeeded. If you successfully executed a batch of statements the return value is undefined (it won't be
false though).
Just like all dialects of SQL, it is important to quote the data you are inserting correctly - not just to avoid syntax errors, but to avoid SQL injection attacks. Using
addslashes is not the correct way to quote data for SQLite - instead you need to use
sqlite_escape_string.
sqlite_escape_string actually performs some magic on the string if it looks like binary data - this is something we will visit again in the next article.
The final function is
sqlite_fetch_array which extracts the next row from a result handle. This operates in very much the same way as the
mysql extension equivalent -
mysql_fetch_array.
That was easy - give me more!
Moving on, we are going to look at a couple of other functions/features that you will find most useful, before moving on with some real-world sample uses for SQLite.
Just about everyone wants to have some kind of automatic integer identifier for rows in their tables - this is typically implemented as some kind of auto increment field. SQLite has this feature - in fact, every row in every table gets an automatic ID, in a similar way to Postgres, although it is much better practice to explicitly declare an auto increment field. Listing 2 shows you the SQL you need to create such a field in your table:
Listing 2: Creating an auto increment field CREATE TABLE ideas(
-- idea_id is the auto increment field
idea_id INTEGER PRIMARY KEY,
-- details about the idea
idea_body TEXT,
-- how well the idea worked
success_rating INTEGER
);
When you insert a row where the
idea-_id field has a
NULL value, the SQLite engine will instead assign a value with an integer value that is one greater than the largest key already in the table. If you're wondering about the dashes in front of the second, third and fifth line, they are the SQL standard's way of defining a comment.
If you don't want SQLite to auto increment the field, you have two options - either declare the field as INT PRIMARY KEY instead, or always insert your own value for the column.
Whenever an auto increment field is updated in this way, you can retrieve the generated ID using the
sqlite_last_insert_rowid function in your PHP code, or you can use the SQL function
last_insert_rowid to use that value directly in a batch of SQL statements.
Another function in the same vein as
sqlite_last_insert_rowid is
sqlite_changes - it returns the number of rows that were changed by the last SQL statement which was executed (similar to
mysql_affected_rows if you like a comparison).
Listing 3: Adding items to the database and getting their auto increment ID <?php
$db = sqlite_open("idea.db");
if (isset($_POST['NEW'])) {
$body = sqlite_escape_string($_POST['body']);
sqlite_query($db, "INSERT INTO ideas (idea_id, "
. "idea_body, success_rating) VALUES ("
. "NULL, $body, 0)");
$id = sqlite_last_insert_rowid($db);
$changes = sqlite_changes($db);
echo "<b>Idea $id added; changes = $changes</b><br>";
} else {
?>
<form method="post">
<textarea name="body"></textarea>
<input type="submit" name="NEW" value="Add Idea">
</form>
<?php
}
?>
Real Uses
Now it is time for some real world examples of SQLite. Starting from the basics, people taking their firsts steps with web sites generally want some kind of guestbook. It's a trivial thing to implement for more advanced PHP programmers, but for the beginner it can be a daunting task. An advanced PHP programmer would probably sit down and write a solution for this situation using something like the
dba extension or even just implement their own regular file based code using the
fopen() function. While both of these things are likely to work (the latter will work everywhere that PHP works), it's a lot of effort particularly if you later want to review the guestbook contents by date, or add some other features. Cooking up your own file-based storage is not so good if there is scope for evolution of the application.
Enter SQLite and you can pretty much code the guestbook in your sleep - you don't need to sit there scratching your head wondering how to make it work, or even how the fields should be typed since SQLite is typeless - just like PHP - it doesn't matter.
OK, so let's come up with the specification for our simple guestbook. Firstly, we want to know who visited. The info that should be recorded includes their name and also their email address. While we are at it, we might also want to know their website address. The most important thing is, of course, their comment - the note that they wanted to leave for you. We will probably also want to store the date.
Since SQLite is typeless, it doesn't have any date or time types or functions. At first this sounds like a tragedy, but it's actually not too bad. There are two solutions that come to my mind - the first of these is to store
time_t values in the database.
time_t is the C data type that holds UNIX timestamp values - you are probably familiar with these from PHP as they are the integer values returned from the
time() function. Simply declare an integer field in your database and store the
time() value when you insert the record. The other alternative is to store ISO formatted dates as strings, e.g.: "2003-09-21 15:13:12". Both solutions will sort correctly if you do an
ORDER BY query. The first is the simplest to work with, because you can easily compare time values and calculate differences using integer mathematics and format the time using a whole bunch of built-in PHP functions. Using an ISO date string is a bit more portable, but you'll need to write some functions of your own to work with the string. Since we are aiming for simplicity here, we will use the timestamp approach.
Now, we can write some code - Listing 4 is intended to go into a shared include file that will be used by a couple of pages on the user's web site.
Listing 4: guestbook functions <?php
// opens the database, initializing the tables
// if it hasn't been done already
function gb_open()
{
$db = sqlite_open('guestbook.db');
$q = sqlite_query($db, "select name from "
. "sqlite_master where type='table' and "
. "tbl_name='guests'");
if (!$q || !sqlite_fetch_array($q)) {
// the guests table does not exist
// so create it.
sqlite_query($db, "create table guests("
. "id INTEGER PRIMARY KEY, "
. "name text, "
. "email text, "
. "comment text, "
. "web text, "
. "posted integer);");
}
return $db;
}
// Add an entry to the guest book
function gb_add($db, $name, $email, $comment, $web)
{
$name = sqlite_escape_string($name);
$email = sqlite_escape_string($comment);
$web = sqlite_escape_string($web);
$now = time();
return sqlite_query($db, "INSERT INTO guests "
. "(id, name, email, comment, web, posted)"
. " VALUES (NULL, '$name', '$email', '$comment',"
. "'$web', $now)");
}
// Display entries from the guestbook
function gb_show($db)
{
$q = sqlite_query($db, "SELECT * from guests "
. " order by posted desc");
echo "<table><tr><th>date</th><th>Guest</th></tr>";
while ($row = sqlite_fetch_array($q, SQLITE_ASSOC)){
echo "<tr>";
echo "<td>" . strftime("%c", $row['posted']) . "</td>";
echo "<td><a href='mailto:" .
htmlentities($row['email']) .
"'>" . htmlentities($row['name']) . '</a><br>';
if (strlen($row['web'])) {
echo "<a href='" . htmlentities($row['web']) .
"'>" . htmlentities($row['web']) . "</a><br>";
}
echo "<blockquote>"
. htmlentities($row['comment'])
. "</blockquote>";
echo "</tr>";
}
echo "<table>";
}
?>
The astute readers among you will notice that I've sneaked in a little bit of magic to the
gb_open() function. The purpose of
gb_open() is to open the database and return a database handle. It will check to see if the database has already been set up. It does this by querying the meta data from the
sqlite_master table. This is a special magic table that holds the table schema and other meta data for permanent tables in your database. In our code, we are simply testing for the existence of the guests table. If no rows match, we know that we need to create the table. And this is what the code does. You don't need to write your code this way - you might prefer to have a separate installation script that handles that part, but, again, we are going for simplicity here - the user just wants to download some files and run with it without worrying about the complexities of setting things up.
The other two functions there are
gb_add() and
gb_show(). They are quite straight-forward -
gb_add() creates a new guestbook entry and
gb_show() displays all the existing entries in a table, with the most recent first. Listings 5 and 6 show how these functions are intended to be used - I've omitted all the usual HTML stuff around them for readability.
Listing 5: guestbook.php <?php
$db = gb_open();
gb_show($db);
?>
Add a comment!<br>
<form method="post" action="new.php">
Name: <input type="text" name="name"><br>
Email: <input type="text" name="email"><br>
Home page: <input type="text" name="web"><br>
Comments:<br>
<textarea name="comment"></textarea><br>
<input type="submit" name="ADD" value="Add Comment">
</form>
Listing 6: new.php <?php
$db = gb_open();
if (isset($_POST['ADD'])) {
gb_add($db, $_POST['name'], $_POST['email'],
$_POST['comment'], $_POST['web']);
header("Location: http://"
. $_SERVER['HTTP_HOST'] . "/guestbook.php");
exit;
}
?>
The idea is that the visitor arrives at
guestbook.php, sees the list of comments, and fills in the form at the bottom. The form is submitted to the
new.php script which creates the entry and redirects the browser back to the guestbook page. If everything worked out nicely, their comment will appear at the top of the guestbook. Redirecting like this avoids a warning from some browsers about pages being expired, and helps reduce questions from puzzled users and duplicate entries in the guestbook from click-happy users.
Summarizing the guestbook
If youre thinking I can't believe he just wrote all of that about a guestbook, don't worry - so am I. There is method to my madness when you realize that most of the things you will end up writing in PHP are simply glorified guest books. No doubt, some of you are saying No! My XYZ application is much more than a guestbook - it has features J, K and L! Well, it's true. All your extra features (that store/retrieve data) are individually not much more than the guestbook above. Once we've realized this, we can then apply the same technique to all our PHP applications. Guess what? SQLite makes it very easy. If you wrote the equivalent code using MySQL or postgres, you would have to worry about database names, permissions, usernames and passwords. Actually, you would have had to worry about the person installing your guestbook worrying about usernames and passwords. As we all know, end-user support is something nobody likes having to do, is time consuming (expensive!). So it's always nice if we can avoid it.
There are a whole bunch of similar maintenance and installation benefits from using SQLite instead of a real database as I mentioned earlier in the article. However, please don't think that SQLite is only suitable for guest books - you
can build some more advanced applications more in the spirit of real relational databases. I will be touching on these advanced uses in part 2 of the article in the next issue - teaching you how to write UDF (user defined functions) for native use in your SQLite SQL statements and demonstrating how to use SQLite to expand those big number crunching scripts beyond the 64K-array-element limit.
I hope that you found this article useful, and hope to see you again next time! If you can't wait for the next issue, don't be afraid to look around the online documentation both for SQLite itself (they have their own community to help your with your SQLite SQL), and the PHP Manual for the SQLite extension, which I co-authored with Johann Peter Hartmann.
Links und Literature 
