Finley, I am. » Blog. » Of Javascript and Databases. (Part 2)

Of Javascript and Databases. (Part 2)

Yesterday, I showed y'all one of the cool, new technologies of HTML5: Javascript databases. I also released my jQuery plugin allowing for a Code Igniter style "Active Record." Create and manager local databases with ease, even without knowing SQL. In my view, one of the big uses of this technology is synchronizing data from a server to the local database, and vis versa. Sure, there are apps that you may write that the database existing only locally is fine, but if the user wants to use it on multiple devices (iPhone, desktop, and laptop?), you'll need to synchronize the data.

That is where the second plugin, the second half of my "system," comes into play. Let's take a Twitter app for example. Say I'm building a Twitter app, on par with other Twitter apps on the iPhone/iPod touch platform. For this, I would only need a one-way sync, meaning that the data only changes on Twitter's servers. I would like to send out a check every 5 minutes to get the latest data, but also (of course) trigger the check after I tweet, or when I click a button. Let's look at some code.

Create the database and table to store the tweets.

//open connection to database
$.db.open_db({
    shortName: 'twitter',
    version:   '1.0',
    name:      'My Twitter App',
    maxSize:   5242880 //up to 5 megabytes
});

//create tweets table, if it doesn't exist
$.db.create_table({
    name: 'tweets',
    fields: [{
        name: 'id',
        type: 'INTEGER',
        extra:'PRIMARY KEY AUTOINCREMENT'
    }, {
        name: 'created_at'
    }, {
        name: 'user_name'
    }, {
        name: 'screen_name'
    }, {
        name: 'user_id'
    }, {
        name: 'text',
        type: 'TEXT(140)'
    }, {
        name: 'in_reply_to_status_id'
    }, {
        name: 'favorited'
    }]
}, true);

If you don't know what all that is, consult the article from yesterday or the guide in the Projects section of this site. Now, of course, you could set up a few interval functions to carry out a sync yourself, but watch this:

Synchronize the Database.

$.db_sync.init('tweets', {
    primaryKey: 'id',
    syncURL: 'http://username:password@twitter.com/statuses/friends_timeline.json?count=50&callback=?',
    interval: 300000,
    processData: function (data) {
        if (data.error) return false;
        var ret = [];

        var obj;
        var i = data.length;
        while (i--) {
            obj                       = {};
            obj.id                    = data[i].id;
            obj.created_at            = data[i].created_at;
            obj.user_name             = data[i].user.name;
            obj.screen_name           = data[i].user.screen_name;
            obj.user_id               = data[i].user.id;
            obj.text                  = addslashes(data[i].text);
            obj.in_reply_to_status_id = data[i].in_reply_to_status_id ? data[i].in_reply_to_status_id : 0;
            obj.favorited             = data[i].favorited;
            ret.push(obj);
        }

        return ret;
    }
});

So what are we doing here? $.db_sync.init initializes a sync on a table, as specified in argument 1. It assumes a connection is made to a database, using the db plugin, and that the table is already created. The second argument is settings. primaryKey is, of course the primary key of the table. syncURL is what we are syncing with (which returns JSON). interval is the time in thousandths of a second. processData is a cleaner, and where the power happens. You want to clean the data to include only, and all of, the fields that are in the table, and return it as an array of objects.

So that is all it takes, and now every five minutes it will grab the data from syncURL, clear the table, and store the new data. But how do you know when it is syncing? Or when it is done? Callbacks, of course! There are three main callbacks that are triggered during the syncing process. They are: window.onsyncpercent, window.onsyncstatuschange, and window.onsyncready.

Monitor the Sync.

//on percentage change
window.onsyncpercent = function (table, percent) {
    window.console.log(table+' is syncing at '+percent+'%');
};

//on status change
window.onsyncstatuschange = function (table, status) {
    window.console.log(table+' status: '+status);
};

//on sync, build interface
window.onsyncready = function (table, status) {
    $.db.order_by('id').get('tweets', function (r) {
        window.console.log(r.results);
    });
};

The onsyncready is the main one, though you could use onsyncstatuschange to catch the "ready" status. Here, when the sync is ready, you may refresh the interface with the latest tweets, play a sound, or any number of things.

Force the Sync.

$.db_sync.sync('tweets');

This will cause the sync to be called now, resetting the timer for 5 minutes from now. Call this after a tweet is posted, for example.

Still to be Done...

There is still a list of things to be done with this. One is better handling of two-way sync. If you make a change to local data, it will be overridden by the sync. There is a way to stop the sync from happening, and it is the precheck function setting when you initialize the sync. The precheck is handed a function to carry out the sync.

$.db_sync.init('tweets', {
    primaryKey: 'id',
    syncURL: 'http://username:password@twitter.com/statuses/friends_timeline.json?count=50&callback=?',
    interval: 300000,
    precheck: function (syncFN) {
        if (changesMade !== true) {
            syncFN();
        }
        else {
            //send new data to server, then sync
        }
    },
    processData: function (data) {
        ... //example code above
    }
});

Also, I would like to add a way to merge the data, as opposed to replacing it, allowing only new data to be inserted. But, that is database synchronization at it's core. You wanna create a Twitter app with just Javascript, HTML, and CSS now, storing the data locally? Easy! More to come in the coming week.