Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Like most hardcore geeks, I tend to collect large amounts of, well, geeky stuff. For instance, I don’t have one PDA, I have four. And why run one Linux server, when three can do so much more? And everybody knows that although one monitor is fine, two make you so much more productive, right? Sound familiar? And let’s not even talk about the collection of Star Wars Pez dispensers littering my desk.
This tendency towards immoderation is equally prevelant when it comes to software. Whether it’s ignorance, or ego, or simply the desire to create and use the biggest, baddest software out there, many software developers and administrators have historically exchanged efficiency for exorbitance at every opportunity. The result has so often resulted in extraordinary maintenance costs (for instance, an Oracle-backed web interface, when a simple Wiki could have been used instead; believe me, I’ve seen it happen), expensive development requirements (deciding to build a fairly simply web site using EJB and Websphere when a LAMP solution would have been sufficient), and confused users (nearly any member of the Microsoft Office Suite comes to mind).
Thankfully, in recent years the software development community really seems to have come around to the notion that bigger isn’t necessarily better, and the virtues of simplicity often outweigh the allure of complexity. The Ruby on Rails framework is a great example of such thinking, as are products such as Google Docs & Spreadsheets, MySQL, and SugarCRM. This approach really seems to have sparked a trend, because we’re seeing even further streamlining of these already svelte solutions.
One such solution is SQLite, a fully-featured relational database that, at just 250KB, is by itself able to easily fit on a floppy disk yet is capable of managing terabyte-sized databases. What’s more, it’s largely SQL92-compliant, and supports advanced features such as transactions. The compact size and considerable power is even more impressive when you consider the database requires almost no administrative overhead! And because it’s public domain software, you don’t have to deal with any licensing issues! In this tutorial, I’ll introduce SQLite, showing you how this database can offer you maximum return with minimal investment.
Introducing SQLite
The SQLite relational database engine is the brainchild of Dr. Richard Hipp, who started work on the database some seven years ago. Since then, the database has been embraced by tens of thousands of users around the world, and is a key component of many wildly popular products, including Google Gears, the Firefox web browser, and Adobe’s Adobe Integrated Runtime initiative.
SQLite’s success has as much to do with its impressively small size and speedy performance as its hands-off administrative requirements. This is because SQLite databases are stored within standard textfiles, whose security dependencies rely solely upon the database file’s owner as specified by the underlying operating system. Backups are a snap; just copy the database file to the storage media as you would any other. This trait makes SQLite particularly compelling for uses such as small web sites and embedded systems alike, and is supported by numerous popular programming languages, including C, C++, PHP, Python, Ruby, Tcl, and others.
Using SQLite
To download SQLite, head over to http://sqlite.org/download.html and download the appropriate version according to your operating system. The installation process is standard regardless of the operating system, so I’ll presume you’re capable of figuring it out and will instead move on to usage-related matters.
Once installed, proceed to your command-line prompt and navigate to SQLite’s installation directory. From there, execute the following command to enter the SQLite client:
%>sqlite
On Windows, you’ll need to execute:
%>sqlite3
To learn more about some of SQLite’s client commands, execute .help for a listing.
Creating a new database is ridiculously easy. From within the SQLite client, execute .quit and then re-enter the SQLite client using the following command:
%>sqlite library
Congratulations, you’ve just created a new database, titled library! From here, you’re able to use standard SQL-92 syntax to create tables, and then insert, select, modify, and delete information as you please. Let’s create a table named books:
sqlite>CREATE TABLE books ( ...>id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ...>title VARCHAR(100) NOT NULL, ...>author VARCHAR(50) NOT NULL);
Next, you can insert a few rows using typical SQL statements:
sqlite>insert into books values(NULL, "Purple Cow", "Seth Godin"); sqlite>insert into books values(NULL, "Good to Great", "Jim Collins");
Once done, you can review your work with a SELECT statement:
sqlite>select * from books;
Returning:
1|Purple Cow|Seth Godin 2|Good to Great|Jim Collins
Modification and deletion statements are carried out as easily as the aforementioned exercises! Of course, managing data via the SQLite data is an educational yet perhaps impractical exercise, particularly given the prevalence of using APIs to interact with a database. In the next section, I’ll show you just how easy it is to connect SQLite to the most basic of PHP scripts.
Using SQLite with PHP
SQLite APIs are available for a wide variety of languages. Among the most popular are:
- Java
- PHP’s SQLite Extension
- PySQLite (Python)
- Ruby
- Tcl
Because SQLite is such an ideal database solution for web sites, I’ll conclude this article by showing you how easy it is use SQLite in conjunction with PHP to create database-driven website solutions. Suppose your library database continues to grow, and you want to make it available online so your friends can browse the collection. As you’ll see, a simple PHP script will do the trick, but first you need to make sure the SQLite extension is installed. If you’re running PHP 5.0.X, the extension is enabled by default; therefore, if you’re running a 5.0.X version, you have nothing further to do. If you’re running PHP 5.1.X (which I presume you are), it’s ideal to access SQLite through PDO. To do so, you’ll need to open the php.ini file and uncomment the following lines:
extension=php_pdo.dll extension=php_pdo_sqlite.dll extension=php_sqlite.dll
Save and close php.ini, restart Apache, and you’re ready to begin using PHP’s SQLite API!
If you’ve used another database in conjunction with PHP, the SQLite API is quite similar, although even easier to use. You simply open a connection to the database by invoking the PDO class and then perform any necessary queries. The following example retrieves the book collection, and outputs each title and author to the browser:
<?php
$dbh = new PDO("sqlite:books");
foreach ($dbh->query("SELECT title, author
FROM books ORDER BY title") AS $row) {
printf("%s (%s) <br />", $row['title'], $row['author']);
}
?>
Producing HTML that looks like this:
Good to Great (Jim Collins) <br /> Purple Cow (Seth Godin) <br /> The Sun Also Rises (Ernest Hemingway)<br /> Beginning Ruby (Peter Cooper)<br /> Bobby Fischer Teaches Chess (Bobby Fischer et al.)<br />
Simple as that! SQLite is an amazingly powerful database with almost no administrative overhead. I’d love to hear what you’re doing with it. Please email me at the address found in my bio below!
About the Author
W. Jason Gilmore is Apress’ Open Source Editorial Director, and co-founder of IT Enlightenment. He’s the author of several books, including the best-selling Beginning PHP and MySQL 5, Second Edition (Apress, 2006. 913pp.), and more recently, Beginning PHP and Oracle. Jason loves receiving email, so don’t hesitate to write him at wjATwjgilmore.com.
