PHP - SQLite

From LXF Wiki

Table of contents

Practical PHP Programming

(Original version written by Paul Hudson for Linux Format magazine issue 51.)


What do you do if you want a database but don't want a database manager? With SQLite, flat file storage is back with a vengeance - we show you why...


So far in our tour of the PHP language, we've only looked at two database managers: MySQL and PEAR::DB. These haven't been chosen because they were easy to learn or full of special insights - they were chosen because combined they allow you to use the majority of databases you're likely to encounter. MySQL continues to be the most popular database system for PHP projects, and PEAR::DB, as you know, allows you to work with MySQL, PostgreSQL, Oracle, and others through one generic interface. While using these two cover most database uses, there is one more possibility that neither of these two handle well - what do you do if you want to distribute your application without any requirement for a database manager at all?

SQLite, which has been under development since 2000, is the solution to this problem. Essentially it allows you to create your own local database stored in simple files, and provides a full SQL querying interface on top of that. Being file-based, there are a number of optimisations that SQLite simply cannot do - MySQL, for example, has a large number of caches and buffers available that remain open at all times so that subsequent PHP scripts can take advantage of them. The develoeprs do claim that there are some things SQLite does do particularly quickly, although we've yet to find them. Furthermore, SQLite locks data on a per-file basis, which means that performance for concurrent users will likely take quite a dive.

Performance isn't what's important, though - SQLite can be used anywhere, even on embedded scripts that use PHP-GTK. Furthermore, SQLite supports advanced database functionality such as sub-selects, triggers, and transactions, and if that weren't enough SQLite also has some really incredible functionality that allows you to -- well, we'll get onto /that/ later.


First steps

What do you do if you want to store and retrieve custom data in your script /without/ having a requirement for a DBMS? SQLite lets you have all the power of MySQL without the complexity, and that's where we'll start. SQLite uses a file for every database you create, which is stored like any other file on the filesystem. As you can imagine it's stored in a highly optimised format that enables fast querying and indexing - don't expect to be able to read an SQLite table with Kate.

If you don't have much experience working with MySQL, I suggest you go back and re-read the LXF32 installment of PPP to get back into the swing of things. Once you're fairly smooth on the MySQL front, switching to SQLite should be a cinch as it shares the same programming paradigm. The syntax is /slightly/ different, as the MySQL extension always implicitly uses the most recently opened database connection, whereas you always have to be specific with SQLite. This is helpful in the SQLite world as it allows you to easily work with various files on disk at the same time - more on that later.

Otherwise, things are mostly the same. Where MySQL has mysql_connect(), mysql_close(), mysql_query(), and mysql_fetch_array(), SQLite has sqlite_open(), sqlite_close(), sqlite_query(), and sqlite_fetch_array(). Note that there is no direct function equivalent for the mysql_fetch_assoc() function, however you can pass in a constant to sqlite_fetch_array() to achieve the same results. Here's how that looks, in our first example script - save this as sqlite1.php:

<?php
  $dbfile = sqlite_open('lxf51.db');
  if ($dbfile) {
    sqlite_query($dbfile, "CREATE TABLE users (FirstName CHAR(50), LastName CHAR(50));");
    sqlite_query($dbfile, "INSERT INTO users VALUES ('Nick', 'Veitch')");
    $result = sqlite_query($dbfile, "SELECT FirstName, LastName FROM users");
    var_dump(sqlite_fetch_array($result, SQLITE_ASSOC));
  } else {
    print "Database could not be opened\n";
  }
?>

As you can see, sqlite_open() takes one parameter of interest, which is the filename to read the database from. If this file does not exist, SQLite will automatically create it and return as normal. The file extension ".db" is not necessary, and I've just used it to help make the file type clear - you may find people using ".sqlite" for their databases, which is equally valid. Note that there is no username or password restriction on the SQLite file, which means you need to take added security measures if you want the contents of your file to be free from tampering - simply using the extension ".foo" as opposed to ".sqlite" does /not/ constitute a security measure!

Querying the database uses plain old SQL, but note what I said about the lack of fetching an associative array - the SQLITE_ASSOC constant is passed to sqlite_fetch_array() to get the same effect as sqlite_fetch_assoc() would have.

Smart functions

Back in LXF48 we looked at the PEAR::DB functions for database manipulation, and if you following along you'll probably remember that PEAR::DB has a nice set of functions to return meta-information about the query. SQLite has some too, and learning them will help you maximise your use of the database. For example, if you got used to PEAR::DB's getOne() function, you'll be pleased to see that SQLite has an equivalent in sqlite_fetch_single(), and there's also sqlite_last_insert_rowid() as an equivalent to mysql_insert_id() and also sqlite_array_query(), which is something entirely new - we'll get there in a moment.

Starting with the easiest first, sqlite_last_insert_rowid() allows you to extract an automatically incrementing number from your database. In MySQL this field would need to be declared as INTEGER PRIMARY KEY AUTO_INCREMENT, but the AUTO_INCREMENT is implicit in SQLite. As this number is inserted for you by the database, you can find out what the last number to be inserted was by using sqlite_last_insert_rowid() and passing in the connection resource as the only parameter.

Secondly, sqlite_fetch_single() can be used to return the first column of the first row of your query result, and requires the return value of sqlite_query() as its only parameter. PEAR::DB has this, and is a nice, quick way to manipulate your database if you're only working with small values.

So far, so good, but SQLite still has one interesting trick up its sleeve in the form of sqlite_array_query(), which hands you back an array of all the rows and all the columns in your query result. Add some more rows to the "users" table we created earlier, then save this script as sqlite2.php and run it:

<?php
  $dbfile = sqlite_open('lxf51.db');
  if ($dbfile) {
    var_dump(sqlite_array_query($dbfile, "SELECT * FROM users", SQLITE_ASSOC));
  } else {
    print "Database could not be opened.\n";
  }
?>

You can ignore the lines opening and testing the database, as the key part is the call to sqlite_array_query(). Notice that it requires the connection to query, the query to use, and the manner in which to return the values as its three parameters - this is because it works as a combined sqlite_query() and sqlite_fetch_array(), and returns an array of data where each array element is a row in the table and is an array itself holding the field names and values.

Run the script and you should see that each row in the "users" table gets printed out neatly using var_dump() so that you can see all fields in the table. As it's all done in one function call, using sqlite_array_query() is a very fast way to extract lots of data from your database, being substantially faster than individual calls.


Advanced usage

So far we've looked at simple calls to SQLite and a couple of the neat functions PHP makes available to you, but this is really just the shallow end of the pool - because SQLite is wholly built into PHP, as opposed to just having the client libraries built in as with the MySQL libraries, we're able to integrate PHP data into SQLite calls in the same way we already integrate SQLite data into PHP calls.

Using the function call sqlite_create_function() we can tell SQLite to return data based upon the result of a PHP function. The requirements of this are simple: the function must already be defined when you call sqlite_create_function(), and the function must return a value that can be read as true or false. Your function can accept parameters from SQLite in the form of fields from the table being selected, so we could, for example, use code like this:

<?php
  $conn = sqlite_open("users");
  sqlite_query($conn, "CREATE TABLE users (ID INTEGER NOT NULL PRIMARY KEY, Name VARCHAR(255), Age TINYINT);");
  sqlite_query($conn, "INSERT INTO users (Name, Age) VALUES ('James Fisher', 23);");
  sqlite_query($conn, "INSERT INTO users (Name, Age) VALUES ('Peter Hutchinson', 48);");
  sqlite_query($conn, "INSERT INTO users (Name, Age) VALUES ('Richard Hartis', 24);");

  function IsUnder25($age) {
    return ($age < 25);
  }

  sqlite_create_function($conn, "IS_UNDER_25", "IsUnder25");
  $result = sqlite_query($conn, "SELECT Name, Age FROM users WHERE IS_UNDER_25(Age)");

  while($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
    extract($row);
    print "$Name is in under 25, with an age of $Age\n";
  }

  sqlite_query($conn, "DROP TABLE users;");
?>

The first five lines there simply create a database "users" along with a table "users" and insert a few fields in there for our tests. This table is dropped at the end of the script so that the same users don't keep getting recreated as the script gets called again.

The crucial part in that code starts with the function definition of IsUnder25 - note that it takes one parameter, $age, then returns true if that value is under 25. If it's 25 or over, it return false. This could have been written in the wordier form:

if ($age < 25) {
  return 1;
} else {
  return 0;
}

However, I think that would actually lessen the readability. Moving on, we tie the PHP function IsUnder25 to the SQLite function IS_UNDER_25 using sqlite_create_function(). As you can see, it takes the connection as its first parameter, because these functions are connection dependent, then the name of the SQLite function you want to bind to and finally the PHP function you are binding. As soon as this call returns you're able to start using the function, and the very next line uses it immediately - as you can see, our bound function can be used like any other function native to the DBMS. When that query is executed, SQLite will automatically pass each Age value to PHP for comparison in the IsUnder25() function, and if a 1 (true) is returned the row will be included in the result set.

Of course, it would be much more sensible to just alter the query to say "WHERE Age < 25", but that's no fun, and certainly wouldn't demonstrate how to use the extra flexibility!

Just to make the point quite clear, here's the same example again except this time there are two bound functions - there could be well have been ten bound functions or a hundred, as there is really no limit beyond the resources available.

<?php
  $conn = sqlite_open("users");
  sqlite_query($conn, "CREATE TABLE users (ID INTEGER NOT NULL PRIMARY KEY, Name VARCHAR(255), Age TINYINT);");
  sqlite_query($conn, "INSERT INTO users (Name, Age) VALUES ('James Fisher', 23);");
  sqlite_query($conn, "INSERT INTO users (Name, Age) VALUES ('Peter Hutchinson', 48);");
  sqlite_query($conn, "INSERT INTO users (Name, Age) VALUES ('Richard Hartis', 24);");

  function IsUnder25($age) {
    return ($age < 25);
  }

  function BeginsWithAJ($name) {
    return (strtoupper($name{0}) == "J");
  }

  sqlite_create_function($conn, "IS_UNDER_25", "IsUnder25");
  sqlite_create_function($conn, "BEGINS_WITH_A_J", "BeginsWithAJ");
  $result = sqlite_query($conn, "SELECT Name, Age FROM users WHERE IS_UNDER_25(Age) AND BEGINS_WITH_A_J(Name);");

  while($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
    extract($row);
    print "$Name is in under 25, with an age of $Age, and also starts with a J.\n";
  }

  sqlite_query($conn, "DROP TABLE users;");
?>

As our bound functions work just like normal SQL functions, they are just chained together using AND (or OR).

That just about rounds up over coverage of the SQLite DBMS. As I hope you've seen, it's a viable alternative to the traditional client/server DBMS model, and makes perfect sense in the situations where you want full client-side deployment without needing to install a hefty DBMS alongside.


So what's left?

Our trek through PHP has so far seen us covering the new object-oriented programming features, SimpleXML, and now SQLite, but there's still one new feature yet to be touched on and that's /exceptions/. This can be quite a confusing topic in any language. so I'll try to make it especially clear. Take a look at this code:

<?php
  class LowAgeException extends Exception { };
  class HighAgeException extends Exception { };

  try {
    $Age = 90;
    if ($Age < 20) {
      throw new LowAgeException('Age is less than 20!');
    }

    if ($Age > 60) {
      throw new HighAgeException('Age is greater than 60!');
    }
  }
//  catch (Exception $exception) {
//    echo "Generic exception caught: {$exception->getMessage()}\n";
//    print_r($exception);
//  }

  catch (LowAgeException $exception) {
    echo "LowAgeException caught: {$exception->getMessage()}\n";
    print_r($exception);
  }

  catch (HighAgeException $exception) {
    echo "HighAgeException caught: {$exception->getMessage()}\n";
    print_r($exception);
  }
?>

Yes, that's quite a lot of code for a topic I'm supposed to be making easy, but it full encapsulates pretty much all you need to know about exceptions in one script. Firstly, note that there is a built-in class, "Exception", which provides a lot of basic functionality for your exceptions - the reason for all the print_r()s (an easier to read version of var_dump()) is to show you how much info the default Exception class puts in for you. We'll look at that in a moment - for now, just see that we define two of our own exceptions as extensions to the base Exception class.

The exception block beings with "try {", and ends with a corresponding brace. Within the try block you can have as much code as you want, but you can also use the new "throw" keyword to throw exceptions. In our example code above, we either throw a LowAgeException (passing in the message to throw) or a HighAgeException depending on the value of $Age. As soon as an exception is thrown, the try block is exited and execution jumps to the catch blocks. After the closing brace you can have any number of "catch" blocks, and PHP will search through them sequentially to find the first matching type. In our example, we have three catch blocks, of which the first is commented out. As it stands, the above code will throw a HighAgeException, so it will jump to the catch blocks, skip the LowAgeException handler as it doesn't match the exception type, but it will execute the HighAgeException code because it does match. As a result, the script will print out "HighAgeException caught: Age is greater than 60!"

Now, the reason the first exception handler is commented out is because it is for the generic Exception class. To check whether each exception handler matches the type that was thrown, PHP uses the instanceof operator we looked at last issue, which returns true if an object is of type class /or is descended from it/. As both LowAgeException and HighAgeException are descended from the general Exception class, and because PHP only executes the first matching catch block that it finds in your script, the first catch block ("catch (Exception $exception)") will be executed. Sometimes this is the desired behaviour - as long as you inherit all your exception classes from Exception, you'll always be able to use catch (Exception $exception) as your catch-all exception handler.

To use the catch-all handler, simply put the catch (Exception $exception) as the last catch block. If you do not use a catch-all handler and your script throws an exception for which there is no catch block, you'll find PHP bails out with the error "uncaught exception" - not very graceful! As a result, it's generally best to use a catch-all handler.

The last thing to note is quite how much the default Exception class does for you, which is amply shown by the inclusion of the print_r() function. Here's the actual output of the script:

HighAgeException caught: Age is greater than 60!
highageexception Object
(
    [message:protected] => Age is greater than 60!
    [string:private] =>
    [code:protected] => 0
    [file:protected] => /home/paul/tmp/except.php
    [line:protected] => 12
    [trace:private] =>
    [trace] => Array
        (
        )

)

There are five key variables in there, thanks to the Exception class, and these are message, code, file, line, and trace. The first two are set when you throw the exception - if you pass one parameter in, it is taken as the message to use for the exception, and if you pass two the second is used as the error code. Both file and line are set automatically for you to point to the exact filename (absolute on your hard disk) and the line on which the exception was thrown. The final helpful variable is "trace", which is also set automatically for you at the time the exception was thrown. The constructor for the Exception class automatically unwinds the call stack and places information in the trace array as it goes. As our example code above is in the global scope (that is, we don't throw the exception from inside an object or a function), trace is empty. However, if we were to put the entire try/catch block inside a function called dofoo() then call that function from the global scope, here's how trace would look:

[trace] => Array
    (
        [0] => Array
            (
                [file] => /home/paul/tmp/except.php
                [line] => 27
                [function] => dofoo
                [args] => Array
                    (
                    )
            )
    )

So, combined with the file and line variables we looked at already that tells us where the exception was thrown in absolute terms, the trace array now contains information telling us the exception was thrown in function dofoo(), called on line 27 in /home/paul/tmp/except.php, and that no arguments were passed to the function call. This is an immense help for debugging, as you can imagine!


All done for PHP 5

It's taken us three issues, but we've managed to cover the vast majority of changes in PHP 5. I hope you'll agree that it's a very exciting upgrade for the language and really gives it a whole new lease of life. Above and beyond everything covered here, look out for the minor tweaks that make life easier, such as the new "c" parameter for date() that XML programmers have been crying out for for so long. Of course, there are also many hundreds of bug fixes to look forward to and that's even before we see the first maintenance release, 5.0.1!

Even if you don't plan on using PHP 5 in production immediately, do at least spend the time to test your scripts thoroughly to make sure there aren't any glaring problems. The new OOP changes are likely to break quite a few scripts, so be especially wary if you're using objects in your code. Good luck!


Permissions

As SQLite is file-based, PHP needs to have the correct file permissions to access the data. If your SQLite databases are stored in your htdocs directory, naturally Apache/PHP will already have read access to them - equivalent to having the SELECT privilege. However, if you want to be able to insert or update data, create tables, or create databases, you will need write permissions to the storage folder. This might require a little thinking at first, but it's not all that different from the privileges system in operation by MySQL. If anything, it should be /easier/ than MySQL, because you already know how Unix permissions work.


Case folding

As with other databases, SQLite will automatically return field names with their case sensitivity kept intact - Name is different to NAME, etc. However, this is actually configured using php.ini, which means you can tweak it if you want. Open up your php.ini and search for sqlite.assoc_case - it's set to 0 by default, which means "preserve case". If you set this to 1, PHP will automatically uppercase the field names, whereas setting it to 2 will automatically lowercase the field names. This is much faster than doing case folding yourself - you have been warned!