Browse DevX
Sign up for e-mail newsletters from DevX


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

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 <td> tag containing the "Change" link. Note that the link builds a URL containing the item's ID. The relevant code is highlighted below:

... <table> <tr> <td class="head"></td> <td class="head">Due date</td> <td class="head">Priority</td> <td></td> </tr> <?php foreach ($pending as $p) { $class = (mktime() > $p['due']) ? 'late' : 'ontime'; ?> <tr> <td class="<?php echo $class; ?>"> <?php echo $p['name']; ?></td> <td class="<?php echo $class; ?>"> <?php echo date('d M Y', $p['due']); ?></td> <td class="<?php echo $class; ?>"> <?php echo $priorities[$p['priority']]; ?></td> <td><a href="form.php?id= <?php echo (int) $p['id']; ?>">Change</a></td> </tr> <?php } ?> </table> ...

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:

<?php // check for record ID if (isset($_GET['id']) && is_numeric($_GET['id'])) { $id = (int) $_GET['id']; try { // connect to database $dbh = new PDO('sqlite:../../db/todo.db3'); // update item record // change status field $now = mktime(); $dbh->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:

<?php // check for record ID if (isset($_GET['id']) && is_numeric($_GET['id'])) { $id = (int) $_GET['id']; try { // connect to database $dbh = new PDO('sqlite:../../db/todo.db3'); // delete item $dbh->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.

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