RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

Not every application needs a full-featured enterprise-scale database. In such cases, you can reduce costs and save resources by using a small-footprint database.

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:

   root@thor:/tmp# tar -xzvf sqlite-3.3.17.tar.gz
   root@thor:/tmp# mkdir build
   root@thor:/tmp# cd build
   root@thor:/tmp# ../sqlite-3.3.17/configure
   root@thor:/tmp# make
   root@thor:/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:\Apache\Data> sqlite3.exe todo.db3
   SQLite version 3.3.17
   Enter ".help" for instructions
On Linux:

   root@thor:/usr/local/apache/data# sqlite3 todo.db3
   SQLite version 3.3.17
   Enter ".help" for instructions
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
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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date