oreilly.comSafari Books Online.Conferences.


The Power of Google Gears (Part 1)

by Jack Herrington

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:

  • LocalServer: Using the LocalServer portion of the Gears API you can cache resources, like images, locally for use when you are browsing offline.
  • Database: This is an SQLite database that is embedded in the browser so that you can create database, add, update, delete and query data all from the JavaScript on the page. This database is the primary focus of this article.
  • WorkerPool: This extension allows JavaScript on the page to create worker threads to asynchronously perform data transfer or long running business logic tasks without stalling the browser.

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 can be accessed by code on But that same database could not be accessed by code on the, 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
   CREATE TABLE article (
     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
   <form  action="articleadd.php" method="post">
   <td><input  type="text" name="title"></td></tr>
   <td><textarea  name="content"></textarea></td></tr>
   <input  type="submit" value="Add Article">

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

Listing 3. articleadd.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' ] ) );
Thanks for adding this  article.<br/><br/>
Would you like to <a  href="articleform.html">add another</a>?

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

Pages: 1, 2, 3

Next Pagearrow

Sponsored by: