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 1)

by Jack Herrington
06/28/2007

Web application development is the perfect 95 percent solution. It's very easy to develop a simple HTML frontend to something like PHP or Rails, to deliver data to and from a MySQL database. You can even give it a more desktop feel by using Ajax tools. But that last five percent, the ability for desktop applications to work offline, was missing from the web world until the release of Google Gears.

I look at Gears a lot like Ajax, it's a small set of new functionality that takes traditional web pages and moves them one step toward having a complete desktop feel. There are three new elements that Gears brings to the table:

Google has made all of this available as a quick one package download to the client that works on both Firefox and Internet Explorer.

Covering all of these APIs in a single article, at any depth, would be too much. So I'm going to concentrate on the database portion of the API which provides access to the simple and easy to use SQLite database.

Before I get into the example, let me cover a few basics about the database portion of the API. First, much like the XMLHttpRequest object, access to the database is restricted to pages within the same schema, domain and port. So, for example, a Gears database created by the code on http://mydomain.com/index.php can be accessed by code on http://mydomain.com/anotherpage.php. But that same database could not be accessed by code on the http://anotherdomain.com/anotherpage.php, because the domain is different. This is really a security measure to ensure that malicious code doesn't get access to the data stored in the client-side database.

I'd also like to point out that this is a very full-featured database. You can build multiple tables, relate those tables, and perform joins in your queries. You can use SQL functions, ordering and grouping, in the queries as well. Additionally, Google has enabled full-text searching in the engine. This is not just a bare bones database, it's a real utility that can provide a decent backend for real JavaScript-based application development.

My example will use just the basics of the database: the creation of the database and a table within it, the addition of new rows to the table, and the query of the table.

It starts with a simple content management site. This site has a database with a set of articles. The Gears-enabled frontend can browse the articles offline because those articles are stored locally in the Gears database after they are transferred to the client using Ajax. This is shown in Figure 1.

Image 1
Figure 1. The relationship between the Gears-enabled client and the web server

The server component is on the lefthand side. The articles.php page talks to the MySQL database containing the articles and returns an XML blob containing the articles, their IDs, titles, and contents.

The local side of the equation is on the righthand side. The web browser loads up the index.html page from the server. This page then requests the articles using Ajax, stores them locally in the Gears database, and updates the display with the article titles.

The reader can then disconnect from the Web and read articles all day long, working with just the data in the Gears database. Then when she reconnects she can resync, get the new articles, and continue browsing on or offline as she chooses. Even better, she can close the browser window, close the browser, or even reboot and still have access to the articles located in the Gears database without syncing to the site.

Implementing the Server Side

The code starts with the database for the server side to host the articles. The MySQL schema is shown in Listing 1.

Listing 1. articles.sql
   DROP TABLE IF EXISTS  article;
   CREATE TABLE article (
     id INT NOT NULL AUTO_INCREMENT,
     title VARCHAR(255),
     content TEXT,
     PRIMARY KEY( id )
 );

It's a pretty simple schema with the automatically generated ID for the article, title, and content of the article.

I create the database and add the schema to it using the command line shown below.

 % mysqladmin create articles
 % mysql articles < articles.sql

The next step is to make a simple entry form in HTML so that I can add some records to the database. This form is shown in Listing 2.

Listing 2. articleform.html
   <html><body>
   <form  action="articleadd.php" method="post">
   <table><tr><td>Title</td>
   <td><input  type="text" name="title"></td></tr>
   <tr><td>Content</td>
   <td><textarea  name="content"></textarea></td></tr>
   </table>
   <input  type="submit" value="Add Article">
 </form></body></html>

The PHP script that the form talks to, which adds the article to the database, is in Listing 3.

Listing 3. articleadd.php
<?php
require_once("DB.php");

$db =& DB::Connect(  'mysql://root@localhost/articles', array() );
     if (PEAR::isError($db))  { die($db->getMessage()); }

$sth = $db->prepare(  'INSERT INTO article VALUES ( null, ?, ? )' );
$db->execute( $sth,  array( $_POST['title'], $_POST['content' ] ) );
?>
<html><body>
Thanks for adding this  article.<br/><br/>
Would you like to <a  href="articleform.html">add another</a>?
</body></html>

This script uses the PEAR DB module to connect to the database and execute the INSERT statement to add the row.

If you don't have the DB module installed you can use the pear command line script to install it using the following statement.

   % pear install DB

This will download the code for the module and install it automatically in the correct location. Sweet! To test out this article entry form I'll navigate to it in my Firefox browser, and I get something like Figure 2.

Image 2
Figure 2. Adding an article about Google Gears

From there I enter in a small fragment of an article about Google Gears and hit the Add Article button. That submits the data, and I see something like Figure 3.

Image 3
Figure 3. After I have added the article

To get started with the Gears portion, I need to create one final server element, and that's the XML feed that will be consumed by the Ajax script on the client. This articles.php script is shown in Listing 4.

Listing 4. articles.php
 <?php
 require_once("DB.php");

header(  "content-type: text/xml" );

$db =& DB::Connect(  'mysql://root@localhost/articles', array() );
if (PEAR::isError($db))  { die($db->getMessage()); }

$res = $db->query(  'SELECT * FROM article' );
?>
<articles>
<?php
while(  $res->fetchInto( $row ) ) {
?>
<article  id="<?php echo($row[0]); ?>" title="<?php  echo($row[1]); ?>">
<?php echo($row[2]);  ?>
</article>
<?php
}
?>
</articles>

Once again I use the DB module to talk to the database and get all of the articles. I then use loop to write out the article tag for each of the articles. This article tag has the ID and title of the article as attributes, and the content of the article as text inside the node.

If I run the script on the command line I see the following output.

% php articles.php
<articles>
  <article  id="1" title="Apple releases iPhone">
    Apple Computer is going  to release the iPhone on June 29th at 6PM.</article>
   <article  id="2" title="Google release Gears">
    Google, Inc. of Mountain View California  has released a new toolkit for web developers...</article>
</articles>

I added another article about the iPhone just to make it interesting, and maybe pick up a few Google hits.

Implementing the Gears Page

To implement the Gears-enabled viewing page I'm going to need two JavaScript libraries. The first is Protoype.js, which makes writing the Ajax request to get the article data a breeze. And the second is the Gears JavaScript library, gears_init.js, which provides me access to the Gears database. The gears_init.js file comes with the download of the development kit from Google. I've referenced both of these libraries in the head section of the index.html file shown in Listing 5.

Listing 5. index.html
   <html>
   <head>
   <script  type="text/javascript"  src="gears_init.js"></script>
   <script  type="text/javascript" src="prototype.js"></script>
   </head>
   <body  onload="initializedb()">

<table  width="100%">
   <tr><td  width="20%" valign="top">
   <table  width="100%" id="elArticles">
   </table>
   </td><td  width="80%" valign="top">
   <div  id="elContent"></div>
   </td></tr></table>

<a href="javascript:void  sync();">Go Online</a>

<script>
     var db;

function sync()
     {
     new Ajax.Request( 'articles.php', { method:  'get',
       onSuccess: function( transport ) {
       var articleTags =  transport.responseXML.getElementsByTagName( 'article' );

    for( var a = 0; a < articleTags.length;  a++ ) {
           addArticle( parseInt(  articleTags[a].getAttribute('id') ), 
               articleTags[a].getAttribute('title'),
              articleTags[a].firstChild.nodeValue  );
         }
       showArticles();
     } } );
     }

function initializedb()  {
     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('gearsintro');
       db.execute('create table if not exists  articles' +
          ' ( article_id int, title varchar(255),  content text )');
     }
     showArticles();
     }

function showArticle( id  )
     {
     var rs = db.execute( 'select content from  articles where article_id = ?', [ id ] );
     var found = 0;
     while (rs.isValidRow()) {  $('elContent').innerHTML = rs.field(0); rs.next(); }
     rs.close();
     }

function showArticles()
     {
     while( $('elArticles').rows.length > 0 )
      $('elArticles').deleteRow( -1 );

  var rs = db.execute( 'select * from articles'  );
     while (rs.isValidRow())
     {
       var elTR =  $('elArticles').insertRow( -1 );
       var elTD = elTR.insertCell( -1 );
       elTD.onmouseover = function() {  this.style.background = '#eee'; };
       elTD.onmouseout = function() {  this.style.background = 'none'; };

   elTD.id = rs.field( 0 );
       elTD.onmouseup = function() { showArticle(  this.id ); };

   elTD.appendChild( document.createTextNode(  rs.field(1) ) );
       rs.next();
     }
     rs.close();
     }

function addArticle( id,  title, content )
     {
     var rs = db.execute( 'select * from articles  where article_id = ?', [ id ] );
     var found = 0;
     while (rs.isValidRow()) { found++; rs.next();  }
     rs.close();
     if ( found == 0 )
       db.execute('insert into articles values (?,  ?, ?)', [id, title, content]);
     }
   </script>
   </body>
   </html>

The beginning of the example shows the minimal HTML table that lists the articles on the lefthand side, and the currently selected articles on the righthand side. The rest of the example is a lot of JavaScript to do both the Gears and Ajax work.

The Gears work starts with initializedb, which opens the Gears database and creates the articles table within it. The last thing initializedb does is call showArticles. The showArticles function updates the display by reading the contents of the Gears database using a SELECT call and creating <td> elements for each of the articles.

The sync function, which is called when the reader presses the Go Online link, does the Ajax work. It uses the Ajax.Request function to call articles.php on the server. It then breaks up the XML returned from articles.php and calls addArticle with each of the article tags.

The addArticles method first looks to see if any articles are in the database with the given ID, if there are, then the request is ignored. Otherwise, it adds the article to the database using an INSERT statement.

Once all of the articles are added in the sync function, the showArticles function is called once again to update the display to match what's in the database.

When I first go to the page it's blank with the exception of the Go Online link because the Gears database is empty. When I hit Go Online I see something like Figure 4.

Image 4
Figure 4. The Gears page showing the list of articles

I then click on one of the articles and the showArticle code is called to replace the righthand side of the display with the content of the selected article. This is shown in Figure 5.

Image 5
Figure 5. After clicking on the recently added Google Gears article

OK, so that's the rudiments of a Gears-based approach to client-side content caching.

To make the synchronization a bit more sophisticated I can, have the client request just the new articles that have appeared since it last checked. To do that I need to upgrade the articles.php page as shown below to accept an optional 'sinceid' URL argument.

Listing 6. articles2.php
   ...
   $sinceid = 0;
   if ( array_key_exists(  'sinceid', $_GET ) )
     $sinceid = $_GET['sinceid'];

$res = $db->query(  'SELECT * FROM article WHERE id > ?', $sinceid );
     ...

If specified the script would only return articles sequenced after the particular ID. I can then upgrade the client page to get the maximum ID in the database, and pass that to the script as shown in Listing 7.

Listing 7. index2.html
   ...
   function sync()
   {
     var rs = db.execute( 'select max(article_id)  from articles' );
     var lastid = 0;
     while (rs.isValidRow()) { lastid =  rs.field(0); rs.next(); }
     rs.close();

  new Ajax.Request(  'articles2.php?sinceid='+lastid, { method: 'get',
         onSuccess: function( transport ) {
           var articleTags =  transport.responseXML.getElementsByTagName( 'article' );

      for( var a = 0; a < articleTags.length;  a++ ) {
             addArticle( parseInt(  articleTags[a].getAttribute('id') ), 
                 articleTags[a].getAttribute('title'),
                articleTags[a].firstChild.nodeValue  );
           }
           showArticles();
       } } );
     }
     ...

This demonstrates how you can use SQL functions in SELECT statements just like you can in MySQL or other production quality databases.

Pros, Cons, and Alternatives

As with any solution, Gears has both positives and negatives. The feature set is the obvious positive, as there is currently no other widely available offline alternative to developers. But this doesn't come without costs:

The example I've shown in this article gets around most of these problems by being unrealistically simple in its approach. That's a necessity in an article this short. But a production use of Gears will likely run into some, if not all of these issues.

Conclusions

Google has gifted web developers with a lightweight offline solution. And developers have already started to embrace it by creating JavaScript helper libraries around it's core features. There is so much potential for what can be done now that we have an offline solution. The final article in this two-part series will show more of Gears' potential using several techniques for offline data entry.

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.