Move Over MySQL, SQLite is here!!
Overview
In the last few months, PHP developers have been a happy lot. The latest version of PHP (PHP5) has a large number of language imporovements and a spate of new features. One such feature is the fast and efficient database extension called SQLite. SQLite extension allows you to create full fledged databases on the fly without using traditional databases like MySQL. This article will introduce you to the functionality offered by the PHP/SQLite extension and also review the benefits of using SQLite in relation to other database systems.
The LAMP way
Linux, Apache,MySQL and the P languages (PHP/Perl/Python), collectively called the LAMP platform is one of the fastest growing web development platforms. Apache runs on nearly 70% of all websites. PHP is the most popular "mod" for Apache with over 50% of Apache installations having PHP installed. And the most popular Open Souce Database system out there; MySQL powers more than 5 million websites world wide. PHP and MySQL together play a huge role in this success. If we look back at the origins of both PHP and MySQL, they have had tremondous growth in the last few years. Without taking away anything from PHP and MySQL, let me point out that there have been other technically sound and mature languages (perl/python) and feature rich and reliable databases (Firebird/Postgresql). But, PHP+MySQL combination outnumbers any other web development platfrom. We can attribute this to the "good-enough" attitude both these tools have taken over time. PHP never tried to out gun Perl in "there is more than one way to do it" philosophy. Nor did MySQL compete with PostgreSQL in terms of features. PHP and MySQL together lowered the entry barrier for the developer, by the sheer simplicity of churning out web applications on demand.
How good is good enough?
Alfredo Pareto, an Italian economist observed that 20% of the people own 80% of country's wealth.Over time and through application in a variety of environments, this analysis has come to be called Pareto's Principle or the 80-20 Rule. Applied to the software context, we can rephrase it as, "80% of the time, we use 20% of the features of any given software". Lets dig deep into SQLite with this attitude in mind.
What is SQLite
SQLite is a C library that implements a self-contained,zero configuration embeddable SQL database engine. It is a project started by Richard Hipp in 2000 and since then it has gained immense popularity among programmers.
Unlike traditional database systems like MySQL or PostgreSQL it does not require a seperte process running in the background. A program linked with SQLite communicates directly with the filesystem where the data is stored. SQLite is special compared to other database engines because it combines both the database engine as well as the interface to the engine in a single library as well storing all the data in a single file.
Today, it has language bindings to C/C++, Java, Perl, Python, PHP, lisp, .Net and any language that you can possible think of. We will use PHP to explore the functionality of SQLite in this article. SQLite comes with a unique license, which allows you to use it for any purpose, with no strings attached.
** The author disclaims copyright to this material. ** In place of a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give.
PHP SQLite Extension
Starting with PHP5, SQLite comes bundled with the distribution. In effect, to create a database driven web application we need just Apache and PHP! For older versions of PHP, SQLite is available as a PECL extension. The development of SQLite extension for PHP started in 2003 and this in part has helped in SQLite in not having any legacy cruft associated with it. At the same time the developers of the extension have made it easier for programmers to migrate from other database systems to SQLite. If you have programmed in PHP+MySQL earlier, using SQLite should be a cinch. Lets look at an example of creating a new database and querying with SQLite/PHP.
Listing 1
0. <?php
1. //create a new db; if the file does not exist, it is created.
2. $db = sqlite_open("mydb.db");
3. //create a new table
4. sqlite_exec($db, "CREATE TABLE ADDRESSBOOK(ID,NAME,PHONENUM)");
5. sqlite_exec($db, "INSERT INTO ADDRESSBOOK VALUES('1','neo','298001')");
6. sqlite_exec($db, "INSERT INTO ADDRESSBOOK VALUES('2','trinity','298002')");
7. sqlite_exec($db, "INSERT INTO ADDRESSBOOK VALUES('3','morpheus','298004')");
8. //a select statement
9. $result = sqlite_query($db, "SELECT * FROM ADDRESSBOOK");
10. //fetch all data
11. $data = sqlite_fetch_all($result);
12. //display the data
13. foreach($data as $key => $row)
14. {
15. echo "$row['NAME'] - $row['PHONENUM']
";
16. }
17. ?>
18. //close the database
19. sqlite_close($db);
If you look at line 4, you will notice that there is type declaration for any of the columns. This is one of the biggest differences between SQLite and other databases. SQLite is loosely typed; all data is stored as null terminated strings. SQLite supports type declaration for compatibility reasons but the types are not used. If you remember that PHP is also a dynamically typed language, this 'feature' may not so shocking.
The biggest change to PHP5 comes with a complete redesign of its object model, and with it, a tighter integration to object-oriented (OO) paradigms. SQLite is built to take advantage of that completely. The same code as shown in listing 1 can be rewritten with OOP style as:
Listing 2
0. <?php
1. // create new database (OO interface)
2. $db = new SQLiteDatabase("mydb.db");
3. // create table and insert some data
4. $db->query("BEGIN;
5. CREATE TABLE ADDRESSBOOK(ID INTEGER PRIMARY KEY,NAME,PHONENUM);
6. INSERT INTO ADDRESSBOOK (ID,NAME,PHONENUM) VALUES ('1','neo','298001');
7. INSERT INTO ADDRESSBOOK (ID,NAME,PHONENUM) VALUES ('2','trinity','298002');
8. INSERT INTO ADDRESSBOOK (ID,NAME,PHONENUM) VALUES ('3','morpheus','298003');
9. COMMIT;");
10. // execute a query
11. $result = $db->query("SELECT * FROM ADDRESSBOOK");
12. // iterate through the fetched rows
13. while ($result->valid()) {
14. // fetch current row
15. $row = $result->current();
16. print_r($row);
17. // proceed to next row
18. $result->next();
19. }
20. //not generally needed as PHP will destroy the connection
21. unset($db);
22. ?>
Apart from the obvious use OO interface you can also notice the use of transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. You can manually trigger a trasaction using the BEGIN command and can be terminated with a COMMIT. SQLite supports conflict resolution algorithms to graceful resolve transaction which might hamper data fidelity.
One feature that could evoke an "aha!" from the experienced programmer, is SQLite's ability to call PHP functions from within SQL statements. 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. Lets say you want to print out all the entries in the ADDRESSBOOK table as a table in HTML. Typically we fetch the SELECT query results as an array and attach HTML to the variabl value. A large amount of PHP+MySQL code is just formatting HTML. The same thing written using SQLite is
Listing 3.
0. <?php
1. function markup($vals){
2. $str = "<tr>";
3. $str .= "<td>".$vals."</td>";
4. return $str."</tr<";
5. }
6. $db = sqlite_open("mydb.db");
7. sqlite_create_function($db,'markup','markup');
8. $sql = "SELECT markup(NAME) from ADDRESSBOOK";
9. $rows = sqlite_array_query($db,$sql);
10. foreach ($rows as $r){
11. echo $r[0];
12. }
13. sqlite_close($db);
14. ?>
line 1-5 defines a regular PHP function: markup();which converts a given string into a HTML row.
line 7 registers markup() with SQLite as an UDF(User defined function), so that it can be called from your SQL statements. As you can see, this approach facilitates a seperation of business logic and presentation.
In memory databases with SQLite
The hard disk is the slowest part of your computer. So, in case your application does a lot of writes to the disk and thus slowing down your application, you may consider using SQLite's in memory databases. Instead of writing to the disk after every transaction, the data will be written into memory. Memory I/Obeing serveral orders faster than disk I/O, will give considerable speed up to your applications. But the caveat to this approach is, if the power goes off, you lose data. Using :memory: as the database name, you can create temporary in-memory databases, as shown below.
Listing 4
<?php
//open the database, ':memory:' is used as filename
$db = sqlite_open(":memory:");
//... rest of the code is same as a file based db
?>
Typical applications of SQLite
Use SQLite in situations where simplicity of programming, administration and maintenance are more important than esoteric features offered by enterprise databases. Consider using SQLite for buidling low to medium traffic websites, embedded devices and applications, desktop applications, teaching/learning SQL,developing prototype applications for a larger project. You must avoid using SQLite in scenarios where the same database will be concurrently accessed by several users or over the network.
