ONLamp.com
oreilly.comSafari Books Online.Conferences.

advertisement


The Power of Google Gears (Part 2)
Pages: 1, 2, 3

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.



Sponsored by: