SQLite: The Sensible Database Solution
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.
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.
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:
On Windows, you'll need to execute:
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:
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;
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.