ONLamp.com    
 Published on ONLamp.com (http://www.onlamp.com/)
 See this if you're having trouble printing code examples


The Power of Google Gears (Part 2)

by Jack Herrington
07/12/2007

Google Gears for Offline Data Entry

The ease of HTML and server-side programming makes web development, and the use of web applications, very popular. But dogging the web application developer all this time has been one nagging problem; what happens when the customer can't get access to the Web?

Most of the options available to engineers thus far have either been fragile, relying on the customer not to change the page or close the browser, or they required the download of some software, which is what we are trying to get away from in the first place. Google Gears provides an interesting mid-point for web applications developers. With a little extra JavaScript code, they can use a client-side cache of data to drive their applications until the user becomes connected again.

I won't go into the Google Gears architecture in this article. I'll leave that to my previous article, which provides a good introduction to everything Gears provides and explains how to get started with Gears. In this article I'm going to use the SQLite database Gears provides to allow for offline data entry, which then syncs up with a PHP web application when connectivity is available.

My example is a simple contact database that holds first names, last names, and email addresses. It's the kind of task you can imagine doing when you are on the train looking at some business cards you just got. But you can't with a traditional application, because you are away from the Web. My solution here will be to hold the data locally in the SQLite Gears database, and then update the web server database when connectivity becomes available.

The Web Server Code

The first thing I need to do is to build the web server database and the simple PHP application. The MySQL database schema is shown in Listing 1.

Listing 1. addresses.sql
DROP TABLE IF EXISTS address;
CREATE TABLE address (
  first VARCHAR(255),
  last VARCHAR(255),
  email VARCHAR(255)
);

I create the database and add this table to it by using the following Unix command line shown below:

% mysqladmin create addresses
% mysql addresses < addresses.sql

The next step is to write a PHP page that will add a record to the database and return the current list of addresses in the database. This code is shown in Listing 2.

Listing 2. add.php
<?php
require_once("DB.php");
$db =& DB::Connect( 'mysql://root@localhost/addresses', array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$sth = $db->prepare( 'INSERT INTO address VALUES ( ?, ?, ? )' );
$db->execute( $sth, array( $_POST['first'], $_POST['last'], $_POST['email' ] ) );
$count = 0;
$res1 = $db->query( 'SELECT count(*) FROM address' );
while( $res1->fetchInto( $row1 ) ) { $count = $row1[0]; }
echo( $count." addresses in the database " );
?>
<table>
<?php
$res2 = $db->query( 'SELECT * FROM address' );
while( $res2->fetchInto( $row2 ) ) {
?>
<tr><td><?php echo($row2[0]); ?></td>
<td><?php echo($row2[1]); ?></td>
<td><?php echo($row2[2]); ?></td></tr>
<?php
}
?>
</table>

At the beginning of the script I include the PEAR DB library that I will be using to talk to the database. The script then connects to the database and adds the posted record to the address table using the insert statement. After that, the script gets the current count of rows and outputs that, then gets the complete list of addresses and outputs that as a table.

If you don't have the PEAR DB library installed already, you can install it using this command line:

% pear install DB

Now, you might have a look at that page and think to yourself, "That's not a complete page, it's just a fragment of a page." Right you are! This PHP page is meant to be called using Ajax from a page with the data entry form. The output of this add.php page will then be put into a <div> within that original page to show the result of the update.

Just to make sure that all works, I'll create a non-Gears test page called form1.html. This page is shown in Listing 3.

Listing 3. form1.html
<html><head>
<script src="prototype.js"></script>
<script>
function postForm()
{
  new Ajax.Updater( 'result', 'add.php', {
    method: 'post', parameters: $('addform').serialize()
  } );
}
</script>
</head><body>
<form id="addform">
<table><tr><td>First</td>
<td><input type="text" name="first"></td></tr>
<tr><td>Last</td>
<td><input type="text" name="last"></td></tr>
<tr><td>Email</td>
<td><input type="text" name="email"></td></tr>
</table>
</form>
<button onclick="postForm()">Add</button><br/><br/>
<div id="result"></div>
</body></html>

Right at the beginning, I include the excellent prototype.js library, which makes writing Ajax requests a breeze. There is then a JavaScript function that posts the form data from the entry fields located below.

The Ajax.Updater class takes a couple of parameters. The first is the id of the <div> tag to update with the output of the add.php page. The second is the URL to make the request to. And then, some optional parameters are included in a hash. In this case, I'm specifying that the POST method should be used, and specifying the post data using the very handy serialize method that comes from the Prototype library.

When I bring this page up in the browser and add a first, last, and email, I see something like Figure 1.

The form1.html test page
Figure 1. The form1.html test page

When I hit the add button the page uses the Ajax.Updater class to post the data to the add.php page, which then sends back the count of records and the records table. This is shown in Figure 2.

After hitting the Add button
Figure 2. After hitting the Add button

All right, now I know that Ajax works. So, what happens if I'm disconnected? Can I save this data up so that it can be posted later, when I'm connected again? With Google Gears I can, very easily.

Batching Up Data with Gears

My first pass at using Gears to store up the data for later posting is going to be really simple. I'll take the serialized versions of the post and store them in a very rudimentary SQLite database within Gears. Then, when the user hits a button, I'll dump that database into an array of items to post and, one by one, I will use the Ajax.Updater class to send that data to the server.

The code for this second version of the data entry form is shown in Listing 4.

Listing 4. form2.html
<html><head>
<script src="prototype.js"></script>
<script src="gears_init.js"></script>
<script>
function () {
  if (!window.google || !google.gears)
    return;
  try {
    db = google.gears.factory.create('beta.database', '1.0');
  } catch (ex) {
    alert('Could not create database: ' + ex.message);
  }
  if (db) {
    db.open('addresses');
    db.execute('create table if not exists datablobs ( data text )');
  }
  showQueued();
}
function showQueued()
{
  var rs = db.execute( 'select count(*) from datablobs' );
  while( rs.isValidRow() ) {
    $('result').innerHTML = rs.field(0)+' records queued';    rs.next();
  }
  rs.close();
}
function addData()
{
  var rs = db.execute( 'insert into datablobs values ( ? )',
   [ $('addform').serialize() ] );
  rs.close();
  showQueued();
}
var blobs = [];
function startPosting()
{
  var rs = db.execute( 'select data from datablobs' );
  while ( rs.isValidRow() ) {
    blobs.push( rs.field(0) ); rs.next();
  }
  rs.close();
  rs = db.execute( 'delete from datablobs' );
  rs.close();
  postData();
}
function postData( )
{
  if ( blobs.length == 0 ) return;
  new Ajax.Updater( 'result', 'add.php', {
    method: 'post', parameters: blobs.pop(),
    onSuccess: function() { window.setTimeout( postData, 10 ); }
  } );
}
</script>
</head>
<body onload="initializedb()">
<form id="addform">
...
</form>
<button onclick="addData()">Add</button>
<button onclick="startPosting()">Post to server</button><br/><br/>
<div id="result"></div>
</body></html>

Right at the top of the page I include the gears_init.js file, which is part of the Google Gears download. This is used to provide access to the different Gears subsystems including the SQLite database.

The initializedb JavaScript function called on the load of the page. It makes sure that Gears is installed and opens a connection to the database. It then creates a database called addresses and a single table called datablobs that will hold the serialized versions of the form postings. This SQLite table is really simple; it just has a single field called data which is of type text.

The next important function is addData, which adds a record to the datablobs table with the current text of the first, last, and email fields. It also calls the showQueued function, which updates the <div> tag with the id of results to the count of queued records.

Finally, when the user presses the Post to Server button the startPosting function is called. This function dumps the database into a local array and calls the postData function to post the first element from the blobs array to the server. The postData function then calls itself when the Ajax request is complete. This will post each item until there is no data left.

To give it a try, I launch the browser and go to the form2.html page, as seen in Figure 3.

The Gears-enabled page with the new Post to Server button
Figure 3. The Gears-enabled page with the new Post to Server button

I then add some data to the first, last, and email fields and hit the Add button. This is shown in Listing 4. (See Figure 4.)

The page after having added a record to the Gears queue
Figure 4. The page after having added a record to the Gears queue

So, now there is one item in the queue. At this point I could close the browser and come back tomorrow and still see that one item is in the queue. Why? Because that SQLite database is persistent between sessions, browser relaunches, heck, even restarting the system.

To get the data to the server I hit the Post to Server button and I see something like Figure 5.

The page after having hit the Post to Server button
Figure 5. The page after having hit the Post to Server button

I wanted to show a solution like this because it's very generic and simple. With the addition of the Gears code, you can take any standard data entry form that currently uses Ajax and make it offline-capable using this technique.

But I also want to show a more elaborate use of the SQLite database.

Making Better Use of SQLite

The SQLite database in Gears can be used for a lot more than just a single table with a single field. To push it a little further, I'm going to build a better version of the data table with specific fields for the first, last, and email elements. This also allows me to upgrade the user interface to show the queued elements in a nice table.

This upgraded code is shown in Listing 5.

Listing 5. form3.html
<html><head>
<script src="prototype.js"></script>
<script src="gears_init.js"></script>
<script>
function initializedb() {
  ...
  if (db) {
    db.open('addresses');
    db.execute('create table if not exists address '+
     '( first varchar(255), last varchar(255), email varchar(255) )');
  }
  showQueued();
}
function showQueued()
{
  var tbl = $('elQueue');
  while( tbl.rows.length > 0 )
   tbl.deleteRow( -1 );
  var rs = db.execute( 'select first, last, email from address' );
  while( rs.isValidRow() ) {
    var elTR = tbl.insertRow( -1 );
    var elTD1 = elTR.insertCell( -1 );
    elTD1.appendChild( document.createTextNode( rs.field(0) ) );
    var elTD2 = elTR.insertCell( -1 );
    elTD2.appendChild( document.createTextNode( rs.field(1) ) );
    var elTD3 = elTR.insertCell( -1 );
    elTD3.appendChild( document.createTextNode( rs.field(2) ) );
    rs.next();
  }
  rs.close();
}
function addData()
{
  var rs = db.execute( 'insert into address values ( ?, ?, ? )',
   [ $('elFirst').value, $('elLast').value, $('elEmail').value ] );
  rs.close();
  showQueued();
}
var blobs = [];
function startPosting()
{
  var rs = db.execute( 'select first, last, email from address' );
  while ( rs.isValidRow() ) {
    var dh = { first: rs.field(0), last: rs.field(1), email: rs.field(2) };
    blobs.push( Hash.toQueryString( dh ) ); rs.next();
  }
  rs.close();
  rs = db.execute( 'delete from address' );
  rs.close();
  showQueued();
  postData();
}
function postData( )
{
  if ( blobs.length == 0 )
    return;
  new Ajax.Updater( 'result', 'add.php', {
    method: 'post', parameters: blobs.pop(),
    onSuccess: function() { window.setTimeout( postData, 10 ); }
  } );
}
</script>
</head><body onload="initializedb()">
<form id="addform">
...
</form>
<button onclick="addData()">Add</button>
<button onclick="startPosting()">Post to server</button><br/><br/>
<h2>Queue</h2>
<table id="elQueue">
</table>
<h2>Server Result</h2>
<div id="result"></div>
</body>
</html>

The big changes here are in the initializedb function where I create a table called address, instead of datablobs. This new table has specific fields for the first name, last name, and email. The addData function then adds the data to the new fields and the showQueued function shows those fields in a table with the id of elQueue.

The startPosting function does what the original Gears page did. It takes the contents of the table, turns them into strings ready for the POST to the server with Ajax.Upater.

To test out this page I open up form3.html in the browser. I then enter in an email for my daughter, Megan, and hit the Add button. From there I see something like Figure 6.

The Gears-enabled page now with a friendly view of queued items
Figure 6. The Gears-enabled page now with a friendly view of queued items

Now I see the friendlier version of the queue. I can see what is in there. And you can imagine on a more complete page being able to add, edit, or delete records on the fly completely in this offline mode.

To complete the example, I hit the Add to Server button and get the result shown in Figure 7.

The page after I hit Post to Server
Figure 7. The page after I hit Post to Server

While the original approach of just storing the serialized blobs works for any form, this code is specific to this one application. I can see advantages both ways. You can use whichever approach you think suits your application the best. As with all of the articles I write, you can use the code freely.

Finishing off the code for this article, I want to show how to use the auto-incrementing ids in SQLite to build a server posting mechanism that is a bit more reliable.

Listing 6. form4.html
<script>
function initializedb() {
...
  if (db) {
    db.open('addresses');
    db.execute('create table if not exists address2 '+
     '( id integer primary key autoincrement, '+
     'first varchar(255), last varchar(255), email varchar(255) )');
  }
  showQueued();
}
function showQueued() { ... }
function addData()
{
  var rs = db.execute( 'insert into address2 values ( null, ?, ?, ? )',
   [ $('elFirst').value, $('elLast').value, $('elEmail').value ] );
  rs.close();
  showQueued();
}
function postData( )
{
  var id = -1;
  var data = null;
  var rs = db.execute( 'select id, first, last, email from address2 limit 1' );
  while ( rs.isValidRow() ) {
    var id = rs.field( 0 );
    var dh = { first: rs.field(1), last: rs.field(2),
      email: rs.field(3) };
    data = Hash.toQueryString( dh ); rs.next();
  }
  rs.close();
  if ( id == -1 )
    return;
  new Ajax.Updater( 'result', 'add.php', {
    method: 'post', parameters: data,
    onSuccess: function() {
       rs = db.execute( 'delete from address2 where id=?', [ id ] );
       rs.close();
       showQueued();
       window.setTimeout( postData, 10 ); }
  } );
}
</script>

I've now created another version of the address table called address2, which has an auto-incrementing unique ID as the first field. The postData function has been upgraded to get the first row from the table, then turn it into a POST serialized form and submit it to the server. It then deletes that row using the unique ID associated with the record. This is a more reliable mechanism since records are deleted only when posting to the server is successful.

Conclusions

For years now, web applications have been stuck at the 90 percent solution mark. Web applications are incredibly easy to develop, but fail utterly when the customer isn't connected to the Web. On the day Google Gears was released, I knew that this status quo had instantly changed. Just as the addition of Ajax functionality to the browser was the one small step that added rich interactivity, this small step of Gears solves one of the few remaining problems of web development. What a powerful tool indeed!

Jack Herrington is an engineer, author and presenter who lives and works in the Bay Area. His mission is to expose his fellow engineers to new technologies. That covers a broad spectrum, from demonstrating programs that write other programs in the book Code Generation in Action. Providing techniques for building customer centered web sites in PHP Hacks. All the way writing a how-to on audio blogging called Podcasting Hacks.


Return to ONLamp.com.

Copyright © 2009 O'Reilly Media, Inc.