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 choiceideally 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# 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:
C:\Apache\Data> sqlite3.exe todo.db3
SQLite version 3.3.17
Enter ".help" for instructions
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:
.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:
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.
- 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.
- Item modification. The application should allow users to edit or delete previously entered items.
- Item status change. The application should allow users to mark items as "done."
- 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
Note that index.php
are "read" scriptsthey merely read records from the database using various SELECT
, and done.php
are "write" scripts that actually make changes to the contents of the database.