Intended Audience
Introduction
What is SQLite?
What’s in it for me?
SQLite Extension
Installing SQLite
Using SQLite
Chained Queries
New Functions
SQLite Iterators
Utility Functions
Buffered Advantage
Custom Functions
Summary
About the Author
Intended Audience
This article is intended for PHP programmers interested in
learning about the SQLite extension. The article will introduce the reader to
the functionality offered by the PHP SQLite extension, and overview the benefits
of SQLite in relation to other database systems.
A basic understanding of how PHP and SQL work is assumed. Some
prior experience with MySQL or PostgreSQL is an advantage.
Introduction
In recent months you have probably heard about the new
database extension for PHP: SQLite. By most accounts SQLite seems to be the best
thing since sliced bread, offering a fast SQL interface to a flat file database,
and creating an elegant alternative to bulky database interfaces, without the
functionality or speed loss one might expect. In this article we will explore
this wondrous new extension, and hopefully validate some of the benefits it is
rumored to have.
What is SQLite?
SQLite is an embedded database library that implements a large
subset of the SQL 92 standard. Its claim to fame is the combination of both the
database engine and the interface (to said engine) within a single library, as
well as the ability to store all the data in a single file. I terms of
functionality SQLite resides somewhere between MySQL and PostgreSQL. However,
when it comes to performance, SQLite is often 2-3 times faster (or even more).
This is thanks to a highly tuned internal architecture, and the elimination of
server-to-client and client-to-server communication.
All this is combined into a package that is only slightly
larger than the MySQL client library, an impressive feat considering you get an
entire database system with it. Utilizing a highly efficient memory
infrastructure, SQLite maintains its small size in a tiny memory footprint, far
smaller then that of any other database system. This makes SQLite a very handy
tool that can efficiently be applied to virtually any task requiring a
database.
What’s in it for me?
Besides speed and efficiency SQLite has a number of other
advantages that make it an ideal solution for many tasks. Because SQLite’s
databases are simple files, there is no need for administrative staff to spend
time creating a complex permission structure to protect user databases. This is
automatically handled by file system permissions, which also means that (in
space restricted environments) no special rules are needed to keep track of user
disk space. The users benefit from the ability to create as many databases as
they desire and have absolute control over those databases.
The fact that a database is just a single file makes SQLite
very portable across servers. SQLite also eliminates the need for database
daemon processes that could eat significant amount of memory and other
resources, even when the database system is liberally used.
SQLite Extension
As the newest database extension, SQLite is fortunate in not
having legacy code, unlike older extensions such as MySQL, which must maintain
obsolete behavior for backwards compatibility reasons. It also allows the
extension to utilize the newest PHP features to attain the highest level of
performance and functionality. The developers of the extension have made it easy
for users to migrate from other database systems to SQLite, by keeping the
interface similar to those already implemented in PHP.
SQLite also supports a flexible mechanism for passing database
resources in the procedural interfaces, making it equally easy to switch from
MySQL, where the resource is passed last, and PostgreSQL, where it is passed
first.
SQLite also features a powerful OO interface that can be used
to efficiently retrieve data from a database, saving you from having to
implement your own OO wrappers around the procedural interface. As the example
below demonstrates, the OO interface also allows you to avoid passing resources
altogether.
<?php
// create new database (OO interface)
$db = new SQLiteDatabase("db.sqlite");
// create table foo and insert sample data
$db->query("BEGIN;
CREATE TABLE foo(id INTEGER PRIMARY KEY, name CHAR(255));
INSERT INTO foo (name) VALUES('Ilia');
INSERT INTO foo (name) VALUES('Ilia2');
INSERT INTO foo (name) VALUES('Ilia3');
COMMIT;");
// execute a query
$result = $db->query("SELECT * FROM foo");
// iterate through the retrieved rows
while ($result->valid()) {
// fetch current row
$row = $result->current();
print_r($row);
// proceed to next row
$result->next();
}
// not generally needed as PHP will destroy the connection
unset($db);
?>
Installing SQLite
In PHP 5.0 installing SQLite is a snap because both the
extension and the library are bundled, so all you need to do is add
–with-sqlite to your configure line. I would still recommend
installing the SQLite library, if only because it comes with a SQLite binary that
allows you to open and manipulate SQLite databases without using PHP. This is quite
handy for debugging and executing various one time commands, as well as testing your
queries. Often enough you will find that the bundled SQLite library is a bit dated, so
building your PHP with an external library will allow you to benefit from the latest
fixes and features of SQLite. It will also allow you to upgrade your SQLite
library in future without having to recompile PHP.
To build the SQLite extension against an external library
simply use –with-sqlite=/path/to/lib/.
I should also mention that the SQLite extension comes with a
comprehensive series of tests for both OO and procedural interfaces that test
every single function and method supported by SQLite. This is not only an
excellent resource for examples on how every SQLite method and function works,
but also supplies the expected output, allowing you to see the end result of
every operation.
Using SQLite
The procedural interface to SQLite is nearly identical to that
of MySQL and other database extensions. For the most part switching to SQLite
will only require changing the mysql/pq/etc… function prefix to sqlite.
<?php
// create new database (procedural interface)
$db = sqlite_open("db.sqlite");
// uncomment next line if you still need to create table foo
// sqlite_query($db , "CREATE TABLE foo (id INTEGER PRIMARY KEY, name CHAR(255))");
// insert sample data
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia')");
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia2')");
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia3')");
// execute query
$result = sqlite_query($db, "SELECT * FROM foo");
// iterate through the retrieved rows
while ($row = sqlite_fetch_array($result)) {
print_r($row);
/* each result looks something like this
Array
(
[0] => 1
[id] => 1
[1] => Ilia
[name] => Ilia
)
*/
}
// close database connection
sqlite_close($db);
?>
The big differences between SQLite and other
databases are actually in the database engine itself. Unlike other databases,
SQLite is loosely typed; all data is stored as
NULL terminated strings rather then the
binary representation of data in a particular column type. For compatibility
reasons SQLite still supports type specification during table creation such as
INT, CHAR, FLOAT, TEXT and so on, but
they are not used. Internally, SQLite only makes a distinction between strings
and integers during sorting. Therefore, if you do not intend to sort the data,
you could avoid specifying column types for CREATE statements in SQLite altogether.
TABLE
SQLite’s typeless nature makes sorting and comparing data
somewhat slow, as each time SQLite will need to determine the type of the data
and apply either a string or a numeric sorting/comparison mechanism. SQL tables
often require an auto-incremented key for quick access to rows, as well as means
of retrieving a reference to the last inserted row. In SQLite this has a
slightly unusual syntax. To create such a field, you need to declare the field
as INTEGER PRIMARY KEY, rather then
specifying a special type or assigning additional properties that indicate that
a field is auto-incremented.
Chained Queries
As you may expect, SQLite comes with many new features that
both increase its performance and expand its functionality. One of these
features is the ability to do chained queries, which means you can execute
multiple queries via a single query execution function. This reduces the amount
of PHP functions you need to run, thus improving the speed of the script. It
also allows you to easily wrap query blocks inside transactions, further
improving performance. This can be a significant factor when performing multiple
write queries. There are, however, a few ‘gotchas’ to keep in mind
when using this functionality.
If any query in SQLite uses user-specified input you should
take extra care to validate that input, to prevent SQL injection. Unlike in
MySQL, where this would only cause an embarrassing query error, in SQLite it
would allow the attacker to execute a query on your server, with potentially
disastrous consequences. If the query block you execute performs inserts, and
you wish to retrieve the id, the
sqlite_last_insert_rowid()
normally used for this purpose would only retrieve the id of the last
insert. On the other hand, when trying to determine how many rows were affected
using sqlite_changes() the result will have the total number
of rows affected by all of the executed queries. If your query block contains
a SELECT, make sure it is the first query, otherwise your result set will not contain
the rows retrieved by that query.
<?php
// create a new memory only database
$db = new SQLiteDatabase(":memory:");
// Create a 2-column table bar & insert 2 rows into it
/* For improved performance the entire query block is wrapped inside a transaction. */
$db->query("BEGIN;
CREATE TABLE bar ( id INTEGER PRIMARY KEY, id2 );
INSERT INTO bar (id2) VALUES(1);
INSERT INTO bar (id2) VALUES(2);
COMMIT;");
// will print "2 insert queries"
echo $db->changes()." insert queries\n";
// will print "last inserted row id: 2"
echo "last inserted row id: ".$db->lastInsertRowid();
?>
New Functions
In addition to back end features, SQLite also offers a number
of new functions that simplify and accelerate data retrieval from SQLite.
<?php
$db = new SQLiteDatabase("db.sqlite");
/* Execute the query and retrieve all rows as an associated array */
$result_array = $db->arrayQuery("SELECT * FROM foo", SQLITE_ASSOC);
print_r($result_array);
?>
The functions allow both the query execution and the
data retrieval to be performed with a single function call, virtually
eliminating the entire PHP execution overhead. The PHP script itself is
simplified as you now have a single function, where otherwise you would have a
series of data retrieval functions in a loop. In instances where only a single
column is retrieved sqlite_single_query() can be
used, which immediately returns a string or an array of strings depending on the
number of rows that are retrieved.
<?php
$db = sqlite_open("db.sqlite");
// Retrieve the id of column (as string)
$id = sqlite_single_query($db, "SELECT id FROM foo WHERE name='Ilia'");
var_dump($id); //string(1)
// In the event >1 row matches, the result is an array
$ids = sqlite_single_query($db, "SELECT id FROM foo WHERE name LIKE 'I%'");
var_dump($ids); // array(3)
?>
As with any feature, you should use it and not abuse it.
When fetching all of the data returned by a query in one go you must remember
that all the results will be stored in memory. If the result set contains a
large quantity of data, the cost of allocating the memory will likely offset any
advantages gained through the reduced number of function calls. Therefore you
should reserve the usage of these functions for instances when small quantities
of data are retrieved.
SQLite Iterators
In PHP 5.0 there is another way to retrieve row data from a
query via the use of iterators.
<?php
$db = new SQLiteDatabase("db.sqlite");
// reduce memory usage by executing un-buffered query
$res = $db->unbufferedQuery("SELECT * FROM foo");
foreach ($res as $row) { // iterate through result object
// Output code
print_r($row);
}
?>
Iteration of objects works very much like iteration of
an array via foreach(), except that
this time you don’t have ‘keys’ and the value represents an array containing
data from a particular result row. Because iterators are internal engine
handlers and not functions, they have very little PHP overhead compared with the
sqlite_fetch_*() functions, and do not
require the result set to be buffered in memory. The end result is an extremely
fast, yet simple and familiar method of retrieving data. There are no downsides
to using SQLite’s object iterators, and whenever you need to go through a
multi-row result set you should definitely consider using them.
Utility Functions
The SQLite extension also features a number of utility
functions that may come in handy when working with a database. One of those
functions, sqlite_num_fields(), can be
used to determine the number of fields (columns) in a particular result
set.
Alternatively, if you intend to fetch data you can simply use
count() on the first result, which will
give you the same number. If both string and numeric keys are retrieved, you
will need to divide the result by two, since there are twice as many entries in
the result array as there are fields. This number may be important if your
script needs to retrieve the field names inside a particular table. If so you
could use sqlite_field_name() inside a
loop to access this information, as the example below demonstrates.
<?php
$db = new SQLiteDatabase("db.sqlite");
$res = $db->unbufferedQuery("SELECT * FROM foo LIMIT 1");
// fetch the number of fields
$n_fields = $res->numFields();
$i = 0;
while ($i < $n_fields) {
// retrieve individual fields
$field_name = $res->fieldName($i++);
echo $field_name."\n";
}
?>
This of course is not an ideal way to retrieve the
column names from the table, simply because it would fail in the event that the
source table contains no rows, and also because it requires you to fetch some
data you never intend to use. A much better solution is to use the
sqlite_fetch_column_types() function,
which will fetch the columns and their types for a particular table regardless
of the amount of data in it.
Buffered Advantage
In most cases, for performance and memory reasons, you would
want to execute unbuffered queries. However, this comes at a slight loss of
functionality that may be needed in certain cases, which is why unbuffered
queries are not always the best choice.
For example, suppose you wanted to find out how many rows were
actually retrieved by your query. Using an unbuffered query, you would need to
retrieve every single row before being able to determine this. Using buffered
queries, this is simply a matter of running the
sqlite_num_rows() function, which would
instantly retrieve this information from the result. Unbuffered queries are also
restricted to linear data retrieval, meaning that you must retrieve information
from all the rows in a series, one row at a time. There is no such limitation
with buffered queries, where you can use
sqlite_seek() to move to any row and
retrieve data from it, or even access rows in reverse order if necessary.
<?php
$db = new SQLiteDatabase("db.sqlite");
$res = $db->query("SELECT * FROM foo");
$n_rows = $res->numRows(); // get number result rows
$res->seek($n_rows - 1); // move to the last row
// retrieve data in reverse order
do {
$data = $res->current(SQLITE_ASSOC); // get row data
print_r($data);
}
while ($res->hasPrev()&& $res->prev()); // until first row
?>
Custom Functions
One of the most interesting features that the SQLite extension
brings to the table is the ability to create functions of your own to use within
SQL. This is possible due to the fact that SQLite combines both the interface
and the database engine in a single library that is coupled with PHP. Through
the use of sqlite_create_function() you
can create functions that can then be applied to the result set or used inside a
WHERE condition.
<?php
/* determine the difference between the user supplied string and the one in the database based on the contained characters */
function char_compare($db_str, $user_str) {
return similar_text($db_str, $user_str);
}
$db = new SQLiteDatabase("db.sqlite");
/* Create char_compare() function inside SQLite based on our PHP function, char_compare(). The 3rd parameter indicates how many arguments the function requires */
$db->createFunction('char_compare', 'char_compare', 2);
/* Execute query, where char_compare() is used to perform the string comparison between name & specified string */
$res = $db->arrayQuery("SELECT name, char_compare(name, 'Il2') AS sim_index FROM foo", SQLITE_ASSOC);
print_r($res);
?>
The ability to use PHP from within SQL allows you
to simplify the actual script making it accessible to larger audience of
developers. This allows PHP to be used as a templating engine that simply
populates HTML structures with database data. In many instances this can
simplify the code so much that there is no need to place a templating system on
top of PHP. Beyond the code simplification, this also improves performance and
reduces the script’s memory footprint, since no data manipulations need to occur
in user-space.
Keep in mind that if the data you are working with can
potentially contain binary data, you should utilize the
sqlite_udf_decode_binary() function to
decode the data from SQLite’s internal binary encoding before processing it.
Once you are done, you then need to encode the binary data using
sqlite_udf_encode_binary() to ensure
that this data can be accessed without corruptions at a later point.
Summary
Now that you’ve seen how SQLite works and what it can
offer, perhaps you’ll consider using it in your current and future
applications. Hopefully, this brief introduction has familiarized you with the
functionality offered by SQLite, and not dispelled any of the good things you
have heard about it.
Like every tool, SQLite has its strengths and weaknesses.
While being an ideal solution for small and/or mostly-read applications, it is
not well suited for large-scale applications performing frequent writes. This
limitation is due to SQLite’s single file based architecture, which
doesn’t allow multiplexing across servers, or the usage of database-wide
locks on writes.
About the Author
Ilia Alshanetsky has been developing web applications for over
7 years, predominantly based on PHP. For past several years he has been actively
involved in the development of PHP and co-authored a number of extensions,
including SQLite. At the present time Ilia runs his own company, Advanced
Internet Designs Inc., which specializes in the development and support of
FUDforum, an open
source bulletin board.
Ilia can be reached at
ilia@prohost.org
December 29, 2008 at 8:21 am
If you try and use the example mentioned in section 7 – Using SQLite and get errors, note that the first command, i.e. $db = sqlite_open("db.sqlite"); will try and create a file in the location where your script is.
If you are running this on your webserver, it is probable that you’d get an error, as the webserver user rarely have write access to that location.
Try using $db = sqlite_open(sys_get_temp_dir() . DIRECTORY_SEPARATOR . "db.sqlite"); instead and see if the errors stop. If it did, then find a proper real location for your db file to be at, where the werserver user does have write permissions.
Another good practice would be to check that $db is actually a resource, as sqlite_open() will return FALSE on error – e.g. something like -
$db = sqlite_open(sys_get_temp_dir() . DIRECTORY_SEPARATOR . "db.sqlite");
if (! $db) {
error_log(‘Could not open the DB file’);
die();
}
August 16, 2009 at 5:12 pm
Since SQL is such a common (i think i can even call it standard) because it is beyond a programming language as functionality, any enhancement, idea or change is welcomed. For example…how could i have my <a href="http://www.rentcar.ro">blog</a> without MySql databases and PHP? If it can be lite, it’s even better
August 31, 2009 at 10:58 pm
<?php
// Credits to Ilia Alshanetsky tutorial on OO SQLite for the foundation of this script
// http://devzone.zend.com/article/760-SQLite-Introduction#Heading1
// Establish file path outside of root directory
// If creating a file inside your root replace $_SERVER… with "file.name"
$filepath = $_SERVER['DOCUMENT_ROOT']."../../sqlite3/sqlite.db";
// Ccreate new database (OO interface)
$db = new SQLiteDatabase($filepath);
// Create table books and Insert test data
$db->query("BEGIN;
CREATE TABLE books (id INTEGER PRIMARY KEY, title varchar(255) not null, author varchar(255) not null );
INSERT INTO books (title, author) values (‘The Lord Of The Rings’, ‘J.R.R. Tolkien’);
INSERT INTO books (title, author) values (‘The Murders In The Rue Morgue’, ‘Edgar Allen Poe’);
INSERT INTO books (title, author) values (‘Three Men In A Boat’, ‘Jerome K. Jerome’);
INSERT INTO books (title, author) values (‘A Study In Scarlet’, ‘Arthur Conan Doyle’);
INSERT INTO books (title, author) values (‘Alice In Wonderland’, ‘Lewis Carroll’);
COMMIT;");
// Execute a query
$result = $db->query("SELECT * FROM books");
// If rows exist in $result
if ($result->valid())
{
// get each row as an array
// print values
echo ‘<table cellpadding=10 border=1>’;
while ($result->valid())
{
// fetch current row
$row = $result->current();
echo ‘<tr>’;
echo ‘<td>’.$row[0].’</td>’;
echo ‘<td>’.$row[1].’</td>’;
echo ‘<td>’.$row[2].’</td>’;
echo ‘</tr>’;
// proceed to next row
$result->next();
}
echo ‘</table>’;
}
else
{
echo ‘books table in sqlite.db database is empty’;
}
// not generally needed as PHP will destroy the connection
unset($db);
?>
May 16, 2011 at 2:44 pm
HELP!! (Yes that sounds desperate but I’m struggling here with what appears to be straightforward…)
I have PHP 5.3.xx installed and I am trying to get off the ground using it with SQLite.
DISCLAIMER – I am doing this on a Windows 7 machine running AppWeb server (see http://www.embedthis.com). This is only supposed to be a very simple proof of concept for something ultimately targeted to run in an embedded (read: kiosk) environment.
Not sure if I need to tweak PHP.ini or something else (dll placements, etc). Trying both the OO example or the prcedural example, I get either an Undefined Class or Undefined Function error. Frustrating !
Regards,
Gman