Getting Small: Building Lightweight Web Applications with Small-Footprint Databases

f you’ve done any open-source database development recently, you probably already know that when it comes to selecting a database for your application, you have a plethora of choices. You are no longer limited to commercial products such as Microsoft SQL Server or Oracle; open-source products such as MySQL and PostgreSQL are viable alternatives, offering similar features at a fraction of the cost.

However, while these products have rich, robust feature sets and reduce costs, they’re not small?the latest downloadable versions of PostgreSQL and MySQL weigh in at 12 MB and 57 MB respectively. For small-scale applications that have minimal database needs, using any large feature-rich database products is often overkill; it’s often more appropriate to use a small-footprint database instead. Even though small-footprint databases may lack sophisticated features such as triggers, views, and stored procedures, they make up for the reduced feature set by requiring minimal resources and disk space.

But what small-footprint databases are available, and how do you use them in a project? This article attempts to answer that very question by describing some of the options available and building a sample application. The sample web application that accompanies this article is a personal to-do list, which allows an individual user to log items “to do” in a database. A browser-based interface supports commands to add, edit, or delete items from the to-do list, and displays a list of completed and pending items.

What You Need
The sample application uses PHP, running under the Apache Web server. Both are open-source projects that you can freely download and install to your development environment.

Choosing A Database
Here’s a quick overview of six small-footprint database choices available. All the contenders described here are lightweight open source products suitable for small-to-medium complexity applications.

  • Apache Derby is possibly the best-known of the small-footprint Java database engines. As an open-source project, it is freely available for download and use (in personal or commercial projects) under the Apache License. Derby is fully ACID-compliant, meets the ANSI SQL standards, and is specifically designed to be embedded directly into a Java application. It runs in the same JVM as the source application and uses an embedded JDBC driver for database communication. Derby supports multiple concurrent users (even in embedded mode), integrates well with IDEs such as Eclipse and NetBeans and server environments such as Tomcat and WebSphere, and includes various interactive, Java-based, command-line tools for database manipulation.
  • H2 is another small, fast Java-based database engine, usable via both JDBC and ODBC APIs. It can be used in both embedded and server modes, and includes support for triggers, joins, views, stored procedures, and encryption. Concurrent use is supported, and H2 also supports a simple clustering mechanism, which makes it suitable for use in mission-critical applications that have high uptime requirements. H2 is freely available online under the Mozilla Public License.
  • Ocelot is a Windows-only database engine that provides full compliance with SQL-92 and SQL-99. Packaged as a 32-bit Windows DLL, Ocelot integrates easily into Windows applications, and is accessible via the standard ODBC API in both single-user and multi-user mode. Ocelot fully supports triggers, stored procedures, and views, and comes with a graphical administration tool for database maintenance and query construction.
  • Firebird is a full-featured client/server RDBMS available for both Windows and *NIX platforms. It’s based on the open-source version of the InterBase database by Borland. Firebird also provides an embedded single-user database engine as a library file, which you can integrate directly with any Windows or Linux application (there are some constraints on the Linux version). This embedded engine is fully compliant with SQL-92 and most of SQL-99; it supports ACID-compliant transactions, sequences, triggers, sub-selects, and referential integrity constraints, and includes various command-line tools for database interaction. The embedded Firebird engine is available under a license equivalent to the Mozilla Public License.
  • One$DB is an open-source version of a commercial Java RDBMS called DaffodilDB, and is embeddable into any Java application. It is compliant with SQL-99, and is accessible via JDBC (a PHP extension is also available). One$DB includes support for encrypted tables, triggers, views, and stored procedures, and is available for both personal and commercial use under the LGPL.
  • SQLite is a free, single-user, embeddable database engine implemented as a standalone C library. It uses a single disk file per database. It supports “most of SQL-92,” according to the documentation, but does not include support for foreign key constraints, triggers, or stored procedures. It also supports only a limited version of the ALTER TABLE command and a small subset of field data types. However, it is the smallest and lightest of all the database engines in this collection, and is natively supported in PHP 5.x, both via a SQLite-specific driver and through the PDO data abstraction layer.

Which of these databases is best suited to the sample application being developed? Of course, the answer depends on your needs. Using the open-source LAMP stack helps keep costs down, so the Windows-specific Ocelot is out immediately if you take that route. Of the remaining options, SQLite is my choice for this application, for a couple of reasons. First, it’s natively supported in PHP, so PHP developers can begin using it for development immediately, without any additional configuration or installation requirements. Second, although it lacks many of the more sophisticated features (such as triggers, stored procedures, and foreign key constraints) found in other databases, it’s light and resource efficient, which translates into better performance. And third, a subjective factor: I’m not as familiar with Java as I’d like to be, and therefore find SQLite easier to use than an equivalent Java-based RDBMS engine.

Installing the Database
Having selected SQLite as the database, you need to install it. Unlike MySQL and PostgreSQL, which use a client-server paradigm, SQLite offers a command-line program that you use to manipulate the disk-based database files directly. You can download a pre-compiled binary of this program for your platform. Pre-compiled binaries are currently available for Windows, and Linux, plus there’s a cross-platform “starkit” for Linux, Windows, and Mac OS X systems.

Installing SQLite on both Windows and Linux systems is extremely simple: just unzip the contents of the download archive (a single binary file) to a directory of your choice?ideally one that is already specified in your default system path. You can then use this binary to create and manipulate SQLite databases.

If you’d prefer to compile the program from source code, either to customize it or because a pre-compiled binary is not available for your system, download the source code archive to your system, create a build area, and then compile the code using the standard configure/make/make install procedure shown below:

   [email protected]:/tmp# tar -xzvf sqlite-3.3.17.tar.gz   [email protected]:/tmp# mkdir build   [email protected]:/tmp# cd build   [email protected]:/tmp# ../sqlite-3.3.17/configure   [email protected]:/tmp# make   [email protected]:/tmp# make install   

By default, the compiled SQLite binary will install to /usr/local/bin.

After installing the SQLite binary, use it to initialize an empty database named todo.db3 for the to-do list application, defining the database as follows:

On Windows:

   C:ApacheData> sqlite3.exe todo.db3   SQLite version 3.3.17   Enter ".help" for instructions   sqlite>   

On Linux:

   [email protected]:/usr/local/apache/data# sqlite3 todo.db3   SQLite version 3.3.17   Enter ".help" for instructions   sqlite>   

You’ll see the SQLite command prompt. Enter commands preceded by a period. For example, to get help at any time, type the command .help. You’ll see a listing similar to this:

   sqlite> .help   .bail ON|OFF           Stop after hitting an error. Default is OFF   .databases             List names and files of attached databases   .dump ?TABLE? ...      Dump the database in an SQL text format   .echo ON|OFF           Turn command echo on or off   .exit                  Exit this program   .explain ON|OFF        Turn output mode for EXPLAIN on or off.   .header(s) ON|OFF      Turn display of headers on or off   .help                  Show this message   .import FILE TABLE     Import data from FILE into TABLE   .indices TABLE         Show names of all indices on TABLE   ...   

To exit the command-line SQLite program, type .quit. You’ll exit to the OS command prompt:

   sqlite> .quit   C:ApacheData>   

For security reasons, it’s a good idea to store the todo.db3 database file outside your web server root. This eliminates the possibility of a remote user being able to download the file over HTTP.

Defining Application Requirements
Here’s a brief list of the functions the sample application should support. The list functions as a guideline for developing the database structure a little later in the article.

  1. Item entry. The application should let users enter new to-do items. Each item record should contain a description, a due date, and a priority level.
  2. Item modification. The application should allow users to edit or delete previously entered items.
  3. Item status change. The application should allow users to mark items as “done.”
  4. Item list display. The application should allow users to view a list of all complete and pending items, arranged by date and priority.
 
Figure 1. Index Page: The figure shows the application’s index page, which lists the to-do items in reverse date order, providing links for adding or changing items.

The functions described here are just a “first cut”?you can make the application more complicated if you wish.

The application supports these requirements through various PHP scripts, which interact with the database and manipulate the records in the items table. Here’s a quick overview of the scripts:

The application’s index page (see Figure 1) is a good place to start. It relies on a script named index.php that displays an up-to-date summary of both pending and completed tasks, ordered by date. It does this by reading records from the database using SELECT queries and formatting the output into a neat HTML page.

A pair of PHP scripts handle adding a new item to the database: form.php generates an HTML form (see Figure 2) into which users can enter data, while save.php saves the user entries to the database.

 
Figure 2. Item Entry Form: The figure shows the form where users add new to-do items.

People use this same form to edit existing to-do items; the form.php script contains intelligence to automatically pre-populate the form with an existing record if invoked in “edit” mode, or generate an empty form when invoked in “add” mode.

After users save an item to the database, they can perform various actions on it. Pending items may be deleted, marked as complete or edited, while completed items may only be deleted. Each of these actions is represented by a different script: delete.php uses a DELETE query to remove the named record, while done.php uses an UPDATE query to alter the status of the to-do item in the database.

You’ll find PHP constants and CSS style rules for the application in include.php and main.css respectively.

Note that index.php and form.php are “read” scripts?they merely read records from the database using various SELECT queries?while save.php, delete.php, and done.php are “write” scripts that actually make changes to the contents of the database.

Assembling the Application
Now that the implementation roadmap is clearer, you can begin assembling the application. First, create a new SQLite database by calling the SQLite program with the name of the database file:

   [email protected]:/usr/local/apache/data# sqlite3 todo.db3   SQLite version 3.3.17   Enter ".help" for instructions   sqlite>

Then, add a table to it using the following CREATE TABLE command:

   sqlite> CREATE TABLE items (      ...> id INTEGER NOT NULL PRIMARY KEY,      ...> name TEXT NOT NULL,      ...> due INTEGER NOT NULL,      ...> complete INTEGER NULL,      ...> priority INTEGER NOT NULL,      ...> status INTEGER NOT NULL      ...> );

Table 1 shows a brief explanation for each field in the preceding table definition.

Table 1: The table shows the database fields in the “items” table in the sample application, along with a brief explanation of each.
Field Description
Id Record identifier
Name Description of to-do item
Due Date item becomes due
Complete Date item was actually completed
Priority Priority of to-do item, ranging from 1 (low) to 5 (high)
Status Status of item: 0 (complete) or 1 (pending)

You can verify that the table has been created with the .tables command, which lists all the tables in the current database; if the table was created correctly, the .tables command will return one table named items:

   sqlite> .tables   items   

Next, insert some dummy records to get things rolling:

   sqlite> INSERT INTO "items" VALUES(1,      'Buy anniversary present',1184437800,NULL,4,1);   sqlite> INSERT INTO "items" VALUES(2,      'Send invoices',1180549800,NULL,5,1);   sqlite> INSERT INTO "items" VALUES(3,      'Finish homework',1180549800, 1180290600,1,0);   sqlite> INSERT INTO "items" VALUES(4,      'Visit Jane',1180290600,NULL,2,1);   sqlite> INSERT INTO "items" VALUES(5,      'Finalize hotel reservations',1181737000,NULL,5,1);   sqlite> INSERT INTO "items" VALUES(6,      "Angie's birthday",1182537000,NULL,4,1);   sqlite> INSERT INTO "items" VALUES(7,      'Weed the lawn',1182237000,NULL,1,1);   

In case you’re wondering about the value inserted into the due field?which doesn’t look like a date?it’s a UNIX timestamp representation of a date. You can generate such a timestamp easily with PHP’s mktime() function; you’ll see how to do that a little later.

Author’s Note: A quick word here about SQLite’s datatypes. SQLite supports five basic datatypes: NULL, INTEGER, REAL, TEXT, and BLOB. However, when inserting data into an SQLite database, the datatype of each value being inserted is given higher priority than the datatype of the field into which it is being inserted. This feature, referred to as “manifest typing,” is unique to SQLite and it allows, for example, INTEGER values to be inserted into a TEXT field, or vice-versa. The only exception is a field of type INTEGER PRIMARY KEY, which creates an automatically-incrementing numeric field?you can see that field type in the preceding schema.

If you find manifest typing distressing, relax; you can configure SQLite to run in “strict affinity mode,” which returns an error if you attempt to insert an incorrectly typed value into a field. You can read more about manifest typing here, and you can find a complete list of SQL-92 features omitted from or unsupported in SQLite.

Developing the Main To-Do List Page
Now you can begin writing some code to interact with the database created in the previous section. The application index page is a good place to begin. This displays a summary of pending and completed to-do items. For greater portability, this listing and all subsequent listings use PHP’s PDO extension and PDO/SQLite driver, both of which are installed by default in PHP version 5.1 and later. Listing 1 contains the complete code for the index page.

 
Figure 3. Index.php Output: The index.php script retrieves pending and completed items and displays them as a neat table.

Most of the work in Listing 1 happens in the first few lines, which set up a connection to the SQLite database and retrieve records from it. First, the script initializes a new PDO object and passes it a DSN string that tells it where to find the SQLite database file. This DSN string varies depending on the database you’re trying to connect to (see the sidebar “Portability” for more on this topic). Next, the script calls the PDO object’s query() method to execute two SELECT queries; the first retrieves a list of pending items, and the second retrieves a list of completed items. In both cases the fetchAll() method formats the returned result set as a nested series of arrays, to simplify later processing.

After obtaining all the required data, the script generates a simple HTML page and uses a foreach() loop to process the arrays returned by fetchAll(). For each record, the script prints the description, due date, completion date (if available), and priority to the page in a neat table (see Figure 3). Note how the script uses PHP’s date() method to format the UNIX timestamp returned in the due and complete fields into a human-readable date representation.

You may have also noticed that Listing 1 includes two external files. The first, include.php, merely contains constants used in more than one listing, such as English-language labels for priority levels and month names:

    'Very Low',       '2' => 'Low',       '3' => 'Medium',       '4' => 'High',       '5' => 'Very High',   );      // define array of month names   $months = array(       '1' => 'Jan',       '2' => 'Feb',       '3' => 'Mar',       '4' => 'Apr',       '5' => 'May',       '6' => 'Jun',       '7' => 'Jul',       '8' => 'Aug',       '9' => 'Sep',       '10' => 'Oct',       '11' => 'Nov',       '12' => 'Dec',   );   ?>

The second, main.css (see Listing 2), contains the CSS style sheet used to format the various HTML documents generated by the application.

Adding List Items
Listing 1 also contains a link to form.php (see Listing 3), which is the script that adds new to-do items to the database using an HTML form.

At first glance, Listing 3 is a fairly uncomplicated form that builds the screen shown in Figure 2 containing a text field for the item description and various drop-down selectors for the due date and priority. However, it will become more complicated shortly, so take a moment now to admire its simplicity.

When a user submits this form, it invokes the PHP script named save.php (see Listing 4), which does the hard work of validating the user’s input and generating the SQL necessary to save it to the database.

Listing 1 showed you how to retrieve records from an SQLite database with a SELECT query. Listing 4 demonstrates adding a new record to the database. The top half of Listing 4 is devoted solely to validation: the script uses a regular expression to test the name field to ensure it doesn’t contain illegal characters, and the checkdate() function to verify that the supplied due date is, in fact, a valid date.

Assuming the input is valid, the script proceeds to initialize a new PDO instance in the same manner as Listing 1, using PDO’s quote() method to escape special characters in the form submission. The script then interpolates these values into a SQL INSERT query string. Finally, it uses PDO’s exec() method to execute the query, saving the record to the database. The exec() method returns a number specifying how many rows were modified as a result of the query. After the query has completed, the script destroys the PDO instance, and redirects the client browser to the index page (Listing 1).

Editing List Items
With a little bit of creativity, it’s possible to revise the form and form processor demonstrated in Listing 3 and Listing 4 to support editing existing records.

First, though, let’s revise the index page slightly to add a “Change” link next to each record. To do that, when building the Pending Items table, add an extra

tag containing the “Change” link. Note that the link builds a URL containing the item’s ID. The relevant code is highlighted below:

 ...    $p['due']) ? 'late' : 'ontime';     ?>       
Due date Priority
Change
...

Next, revise form.php so that when the script is called with a record ID, it automatically retrieves the corresponding record from the database and pre-populates the form. Listing 5 contains the revised version of the form.

The first new thing Listing 5 does is add various PDO calls to the beginning of the script. When called with a record ID as parameter, the script initializes a new PDO connection, and uses a SELECT query to retrieve the corresponding record as an array of field-value pairs. This same snippet of code also turns the UNIX timestamp value for the due field into separate numeric values for day, month, and year.

Within the form itself, the script then modifies the various input fields such that they retrieve the record set array generated by query() and fetch(), pre-populating the form with these values. Where the input fields are drop-downs, the script uses a conditional test to automatically pre-select the correct option. This version of the script also introduces a new hidden field in Listing 6 named id, to hold the ID of the record being retrieved.

One important implication of the changes to form.php is that when a user submits this form, the save.php script requires additional intelligence to determine whether the data being submitted belongs to an existing or a new record, so that it can formulate an appropriate UPDATE or INSERT query. Thus, save.php needs to change as well. Listing 6 contains the revised version of form.php.

Not too many changes here?the script now uses the hidden id field as a flag to determine whether an UPDATE or INSERT query is appropriate. PDO’s exec() method is used as before, to execute the query on the database.

Marking and Deleting List Items
At this point, you’ve seen how to perform SELECT, INSERT and UPDATE queries on the small-footprint SQLite database from a PHP web application. The remaining tasks are to be able to delete records with the DELETE command and mark records as complete with an UPDATE command.

Neither of these two functions is particularly difficult to implement. Marking an item as complete involves updating the item’s ‘status’ field from 1 (pending) to 0 (complete). The done.php script handles the update:

   exec("UPDATE items SET status = '0',               complete = '$now' WHERE id = '$id'");                      // redirect to index page           header('Location: index.php');           exit();       } catch (PDOException $e) {          die('Error: ' . $e->getMessage());       }       } else {       die('Error in form submission');       }   ?>

The preceding script uses an UPDATE query to change the status field of the selected record to 0, again via PDO’s exec() method. Notice that the script also saves the current date (the completion date) in UNIX timestamp format.

Removing an item consists merely of issuing a DELETE query using the item’s record ID, which the delete.php script handles:

   exec("DELETE FROM items WHERE id = '$id'");                      // redirect to index page           header('Location: index.php');           exit();       } catch (PDOException $e) {          die('Error: ' . $e->getMessage());       }       } else {       die('Error in form submission');       }   ?>

At this point, it’s also necessary to update the application’s index page, to add links to these new functions next to each record. Listing 7 contains the revised index page.

The changes cause every pending item to display options to “Mark as Done” and “Remove,” and every completed item to display a “Remove” option. The script has one further refinement?it checks the current date (for pending items) or the completed date (for completed items) against the item’s due date, and marks “late” items in red. You can see all these features in Figure 1.

Backing Up And Restoring The Database
At this point, the to-do list application is complete?you can begin using it to save to-do items, assign and edit priorities, mark items as done, and remove completed items. Before ending this article, though, it’s worthwhile spending a few moments on an important aspect of the application’s operational use: backing up the database.

Backing up a SQLite database is simple because the database is stored in a single disk file. By far the easiest backup mechanism is simply to copy the file onto backup media and store it in a safe place. However, there’s also another way?using SQLite’s built-in .dump command to generate a file containing the CREATE TABLE and INSERT commands necessary to recreate the database from scratch:

   sqlite> .dump   BEGIN TRANSACTION;   CREATE TABLE items (     id integer not null primary key,     name text not null,     due integer not null,     complete integer null,     priority integer not null,     status integer not null   );   INSERT INTO "items" VALUES(1,     'Buy anniversary present',1184437800,NULL,4,1);   INSERT INTO "items" VALUES(2,     'Send invoices',1180549800,NULL,5,1);   INSERT INTO "items" VALUES(3,     'Finish homework',1180549800,NULL,1,0);   INSERT INTO "items" VALUES(4,     'Visit Jane',1180290600,NULL,2,1);   INSERT INTO "items" VALUES(5,     'Finalize hotel reservations',1181737000,NULL,5,1);   INSERT INTO "items" VALUES(6,     'Angie''s birthday',1182537000,NULL,4,1);   INSERT INTO "items" VALUES(7,     'Weed the lawn',1182237000,NULL,1,1);   COMMIT;

Sending the dump to the screen is not very useful, but you can direct the output to a file by preceding the .dump command with an .output command specifying a file name:

   sqlite> .output todo.sql   sqlite> .dump   sqlite> .output stdout

Restoring a database from this backup file is simple; you call SQLite’s .read command with the file name. That automatically imports and executes the SQL in the named file, which recreates the database:

   sqlite> .read todo.sql

As you can see, using a small-footprint database in a Web application has become a viable option?and there are small-footprint databases that support the platforms, languages, and feature sets you need. So the next time you need database functionality but don’t want the cost or the space requirements of a full-size database, consider giving one of these small-footprint databases a try.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Related Posts