Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2008 (Washington, D.C.) with:

Refactoring My Safari SQLite Local Database Example

By Ben Nadel on

The other day, I played around with Safari's SQLite support for the first time, creating a very simple, single-table example. This really opened my eyes up to some of the possibilities of creating very rich, very responsive web-based apps geared towards mobile deployment (via the various WebKit implementations). Of course, the exploration only created about a thousand new questions in my head and almost no new answers. As such, I figured the next best step would be to take my first example and refactor it a bit into a slightly more object-oriented approach.

 
 
 
 
 
 
 
 
 
 

Looking at my first example, the two things that I don't like about it are that the service functions are not grouped together and that the database creation and maintenance is part of the primary page controller. For this refactoring, I wanted to move both of these areas of functionality into their own domain objects. I took the service functions - getGirls(), deleteGirls(), saveGirl(), etc. - and moved them into a service object, GirlService. That part seemed obvious; but what about the database creation and table creation? I've never had to work with an application that actually had to deploy its own database schema before.

To solve this dilemma, I moved up a layer in the domain hierarchy; I knew the service layer (GirlService) was going to need an instance of the database connection in order to function properly. So, I created a Factory object to handle all of the Dependency Injection (DI) / Inversion of Control (IoC) for object instantiation. And, since the Factory object was going to be handing off references to the database connection, I figured that the Factory was as good a place as any to handle the database and table creation itself.

With these objects created and my code refactored, my primary page controller is now much more straightforward and easy to follow:

  • <!DOCTYPE HTML>
  • <html>
  • <head>
  • <title>Safari SQLite - Factory And Service Objects</title>
  • <script type="text/javascript" src="jquery-1.4.2.min.js"></script>
  • <script type="text/javascript" src="girl_service.js"></script>
  • <script type="text/javascript" src="factory.js"></script>
  • <script type="text/javascript">
  •  
  • // Create the application namespace and whatever cached
  • // components would be necessary. In our case, the Factory
  • // object provides access to cached Service and DAO classes.
  • var app = {
  • factory: new Factory({
  • fileName: "sqlite_helloWorld",
  • version: "1.0",
  • displayName: "SQLite Hello World",
  • maxSize: 1024
  • })
  • };
  •  
  •  
  • // -------------------------------------------------- //
  • // -------------------------------------------------- //
  •  
  •  
  • // When the DOM is ready, init the scripts.
  • $(function(){
  • // Get the form.
  • var form = $( "form" );
  •  
  • // Get the girl list.
  • var list = $( "#girls" );
  •  
  • // Get the Clear Girls link.
  • var clearGirls = $( "#clearGirls" );
  •  
  • // Get service class references. This is the class that
  • // provides encapsulated access to the girl database.
  • var girlService = app.factory.get( "GirlService" );
  •  
  •  
  • // I refresh the girls list.
  • var refreshGirls = function( results ){
  • // Clear out the list of girls.
  • list.empty();
  •  
  • // Check to see if we have any results.
  • if (!results){
  • return;
  • }
  •  
  • // Loop over the current list of girls and add them
  • // to the visual list.
  • $.each(
  • results.rows,
  • function( rowIndex ){
  • var row = results.rows.item( rowIndex );
  •  
  • // Append the list item.
  • list.append( "<li>" + row.name + "</li>" );
  • }
  • );
  • };
  •  
  •  
  • // Bind the form to save the girl.
  • form.submit(
  • function( event ){
  • // Prevent the default submit.
  • event.preventDefault();
  •  
  • // Save the girl.
  • girlService.saveGirl(
  • {
  • name: form.find( "input.name" ).val()
  • },
  • function(){
  • // Reset the form and focus the input.
  • form.find( "input.name" )
  • .val( "" )
  • .focus()
  • ;
  •  
  • // Refresh the girl list.
  • girlService.getGirls( refreshGirls );
  • }
  • );
  • }
  • );
  •  
  •  
  • // Bind to the clear girls link.
  • clearGirls.click(
  • function( event ){
  • // Prevent default click.
  • event.preventDefault();
  •  
  • // Clear the girls
  • girlService.deleteGirls( refreshGirls );
  • }
  • );
  •  
  •  
  • // Refresh the girls list - this will pull the persisted
  • // girl data out of the SQLite database and put it into
  • // the UL element.
  • girlService.getGirls( refreshGirls );
  • });
  •  
  • </script>
  • </head>
  • <body>
  •  
  • <h1>
  • Safari SQLite - Factory And Service Objects
  • </h1>
  •  
  • <form>
  • Name:
  • <input type="text" name="name" class="name" />
  • <input type="submit" value="Add Girl" />
  • </form>
  •  
  • <h2>
  • Girls
  • </h2>
  •  
  • <ul id="girls">
  • <!-- To be populated dynamically from SQLite. -->
  • </ul>
  •  
  • <p>
  • <a id="clearGirls" href="#">Clear Girls</a>!
  • </p>
  •  
  • </body>
  • </html>

The functionality itself hasn't changed at all. The only difference is that we are now including a few more Javascript files and, rather than defining all the service stuff in the main page, we are instantiating an instance of the Factory class. The Factory class handles all of the database maintenance and provides accessed to the cached service objects (GirlService in our case).

As I was refactoring this code, one thing that kept throwing me for a loop is the fact that all database interaction in Safari is asynchronous. This makes me nervous when it comes to dependent database interaction. For example, I don't ever want to try to query the "girls" table before it is created. And to be honest, I am not sure if that is a real possibility? If both the "CREATE" and "SELECT" statements execute in parallel to the page, is it possible that the SELECT might be processed before the CREATE? Without knowing how the internal queuing is taking place, I am not sure that I can safely rely on the order of execution of SQL statements to mimic their order of declaration; if this were ColdFusion and CFThread, we would quickly find out that we cannot make this assumption at all.

Ideally, I suppose I would need not only a "DOM-Ready" event, but also a "Database-Ready" event to bind to. For the time being, however, I'm just going to gloss over this complication and assume I'll find a graceful solution later on. That said, here are my domain classes if you are interested in looking at them:

Factory

  • // Define the Factory class. This will take care of DAO and service
  • // class instantiations including dependency injection (you know,
  • // like ColdSpring).
  • ;window.Factory = (function( $ ){
  •  
  • // I am the factory.
  • function Factory( databaseOptions ){
  • // Define the database options. When we create the local
  • // databse, we need to define the way it is opened or
  • // created.
  • this.databaseOptions = databaseOptions;
  •  
  • // Open a connection to the local database. This will be
  • // passed into any service object that requires it.
  • this.databaseConnection = openDatabase(
  • this.databaseOptions.fileName,
  • this.databaseOptions.version,
  • this.databaseOptions.displayName,
  • this.databaseOptions.maxSize
  • );
  •  
  • // Initialize the databse.
  • this.initDatabase();
  •  
  • // I am a cache of Service and DAO objects. The objects
  • // will be cached by their class name.
  • this.cache = {};
  •  
  • // -- Create cachable class instances. -- //
  • this.cache[ "GirlService" ] = new GirlService(
  • this.databaseConnection
  • );
  • }
  •  
  •  
  • // Define the class methods.
  • Factory.prototype = {
  •  
  • // I provide access to the cached class instances. For this
  • // demo, we are going to assume that all cached services are
  • // singletons - no transient objects.
  • get: function( className ){
  • return( this.cache[ className ] );
  • },
  •  
  •  
  • // I initiahze the database - creating the necessary tables
  • // if they do not exist. Since all the database interaction
  • // in Safari is asynchronous, this function allows a callback
  • // to be passed-in for success feedback.
  • initDatabase: function( callback ){
  • // Now that we have the databse connection, let's create
  • // our first table if it doesn't exist.
  • this.databaseConnection.transaction(
  • function( transaction ){
  •  
  • // Create our girls table if it doesn't exist.
  • transaction.executeSql(
  • "CREATE TABLE IF NOT EXISTS girls (" +
  • "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
  • "name TEXT NOT NULL" +
  • ");",
  • [],
  • function(){
  • // Check to see if a callback was
  • // provided for success feedback.
  • if (callback){
  • callback();
  • }
  • }
  • );
  •  
  • }
  • );
  • }
  •  
  • };
  •  
  •  
  • // ------------------------------------------------------ //
  • // ------------------------------------------------------ //
  •  
  • // Return the Factory class definition.
  • return( Factory );
  •  
  • })( jQuery );

As always, I like to wrap my class definition up in its own self-executing function block. I just like the fact that I can safely define variables here without polluting the global name space. Also, a wonderful feature that this affords me is the ability to give the internal and external class references different names. I'm sure that this very concept throws red flags in people's minds; but, I like that I am able to use an "intent"-driven name internally and an "instance"-driven name externally.

GirlService

  • // Define the girl service class.
  • ;window.GirlService = (function( $ ){
  •  
  • // I am the girl service class.
  • function Service( database ){
  • // Store the database connection.
  • this.database = database;
  • };
  •  
  •  
  • // Define the class methods.
  • Service.prototype = {
  •  
  • // I delete all the girls.
  • deleteGirls: function( callback ){
  • // Get all the girls.
  • this.database.transaction(
  • function( transaction ){
  •  
  • // Delete all the girls.
  • transaction.executeSql(
  • (
  • "DELETE FROM " +
  • "girls " +
  • ";"
  • ),
  • [],
  • function(){
  • // Execute the success callback.
  • callback();
  • }
  • );
  •  
  • }
  • );
  • },
  •  
  •  
  • // I get all the girls.
  • getGirls: function( callback ){
  • // Get all the girls.
  • this.database.transaction(
  • function( transaction ){
  •  
  • // Get all the girls in the table.
  • transaction.executeSql(
  • (
  • "SELECT " +
  • "* " +
  • "FROM " +
  • "girls " +
  • "ORDER BY " +
  • "name ASC" +
  • ";"
  • ),
  • [],
  • function( transaction, results ){
  • // Return the girls results set.
  • callback( results );
  • }
  • );
  •  
  • }
  • );
  • },
  •  
  •  
  • // I save a girl.
  • saveGirl: function( values, callback ){
  • // Check to see if we are dealing with an existing girl
  • // or a new girl (based on the ID).
  • if (values.id){
  •  
  • // Update an existing girl.
  • this.database.transaction(
  • function( transaction ){
  •  
  • // Execute UPDATE command.
  • transaction.executeSql(
  • (
  • "UPDATE " +
  • "girls " +
  • "SET " +
  • "name = ? " +
  • "WHERE " +
  • "id = ? " +
  • ";"
  • ),
  • [
  • values.name,
  • values.id
  • ],
  • function( transaction, results ){
  • // Execute the success callback.
  • callback( values.id );
  • }
  • );
  •  
  • }
  • );
  •  
  • } else {
  •  
  • // Insert a new girl.
  • this.database.transaction(
  • function( transaction ){
  •  
  • // Execute INSERT command.
  • transaction.executeSql(
  • (
  • "INSERT INTO girls (" +
  • "name " +
  • ") VALUES ( " +
  • "? " +
  • ");"
  • ),
  • [
  • values.name
  • ],
  • function( transaction, results ){
  • // Execute the success callback,
  • // passing back the newly created ID.
  • callback( results.insertId );
  • }
  • );
  •  
  • }
  • );
  •  
  • }
  • }
  •  
  • };
  •  
  •  
  • // ------------------------------------------------------ //
  • // ------------------------------------------------------ //
  •  
  • // Return the GirlService class definition.
  • return( Service );
  •  
  • })( jQuery );

Here, you can see more of what I am talking about with the naming duality. Externally, this class is known as "GirlService." But, internally, I just reference it as "Service". Perhaps this is just my fascination with self-executing function blocks and I will later decide that this is a horrible and confusing idea. But, for the moment, there's something about it that I just find attractive.

So anyway, I didn't really add any functionality here or answer any interesting questions. Mostly, what I wanted to do was refactor my previous example such that I might be in a better place to think about how I want to move forward with this exploration. Ideally, what I want to work up to is an example that has a local database that syncs, as needed, to a live database. Once we get there, a whole new world of opportunities opens up to us.




Reader Comments

Are you 100% sure that you can't do synchronous calls? I find that rather surprising. It's definitely supported in AIR, and I'd assume you could do it here as well.

Reply to this Comment

@Raymond,

I'm not 100% sure; but, I did read something in passing that said that Safari's specific implementation only supports asynchronous calls. I don't know if this is all WebKit, or just what Safari did specifically.

Reply to this Comment

You got a link to their API docs? I found plenty of demos, but not one damn actual API ref that says, "here is openDatabase and here is the formal list of arguments."

Reply to this Comment

Did you notice that in that example, they don't use the callback as the last argument? That could - possibly - mean it does it in synchronous mode?

Worse comes to worse - you do what I did in my initial examples:

Open
on success - run create table if not exists
on success - set the app in 'ready' state so you can now do generic SQL

So it _is_ possible to have a safe startup routine.

Reply to this Comment

Here is the reference API:

http://www.w3.org/TR/webdatabase/

You can do sync calls, but I don't think it's cross-browser:

openDatabaseSync() instead of openDatabase()

(I think it's supposed to be cross-browser, but something nags at the back of my brain telling me that at least one Class A browser doesn't support it.)

Reply to this Comment

Also, I found that the "run this stuff on creation" was buggy and not reliable. It was easier to have my own manual check-and-fix routine that ran every time no matter what.

Reply to this Comment

Well shoot - there is an openDatabaseSync() command. So it sounds like you could do what I did.

Use the sync to open/create/update tables. When done, create a global async connection.

Reply to this Comment

@Rick,

Awesome, thanks. That's lame that it's not cross-browser compliant; though, I guess this stuff is still rather cutting-edge.

Reply to this Comment

@Raymond,

Yeah, that looks good to me. That's what you were doing in your AIR demo, right, if I remember correctly.

Reply to this Comment

"That's lame that it's not cross-browser compliant; though, I guess this stuff is still rather cutting-edge."

Heh, it's all good. It will be nice and officially supported in 2020 - a mere 10 years or so away. ;)

p.s. Yes - joking - I love HTML - but let's not forget that HTML5 is adding crap that Flash has had for years. Shoot, HTML5 is adding stuff Visual Basic 1 had back in the 80s.

Reply to this Comment

I cannot get openDatabaseSync() to work in Safari (desktop or iPhone). It doesn't work off the window object and I can't seem to find any WorkerUtils object no matter what the casing. Safari has a Worker class, but that doesn't seem to have anything to do with database interaction.

Reply to this Comment

Has anyone figured out how to delete the local storage file or where it's at? I've tried clearing my browsers cache. This is problematic when adding columns to a table because while a DROP table seems to work initially, the table (and it's contents!) get resurected as soon as you do a openDatabase( ??? The only way around I've found is to change the database name and/or version....but is my machine continuing to hold onto that data?

Reply to this Comment

@Bill,

Have you tried doing it through the Safari console? Right-clicking on the database or something? I can't remember if I ever deleted my database. I don't even have that computer anymore :)

Reply to this Comment

hi
i have created sqllite database(question) and i save this my desktop. now i want to access(question) using html5 pls help me i am new in html5 its urgent.

thanks in advance

Reply to this Comment

In 2010, you said that you couldn't get openDatabaseSync to work in Safari, but is that still true? Do you think it might work with web workers in 2013?

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.