Facebook Twitter Gplus LinkedIn RSS
Home Local Database Database Versioning

Database Versioning

An important concept, especially for evolving web apps, is how to handle database versioning.  It would be a hassle for your user to have to go to Settings -> Safari -> Databases, and clear your database every time you want to add another column to your local database table.  How do we fix that?  Well, with database versioning, of course.  I wrote this script below that works well with my web app, and I don’t mind sharing it at all.  I’ll go into a little bit more detail how each part configures to the overall product after the code block.  Keep in mind this is using a globally defined variable called version which was used to open the database.

function checkDBCallback(tx) {
//functions written to drop all tables of database, and then recreate the new tables with the updated fields
clearDB();
createTables();
}

function checkDBError(error) {
//show the database version error and end the call
alert('Error in database conversion.  Error was ' + error.message);
alert('DB Version: ' + db.version);
return true;
}

function checkDBSuccess() {
//tell the user the database has changed, and then refresh the page
alert('Database changed to version ' + version + '.');
setTimeout('window.location.reload();', 100);}

function checkDBChange() {
window['db'] = window.openDatabase(shortName, '', displayName, maxSize);

if (!db) {
alert('Could not open database connection.');
}

if (db.changeVersion) {
alert('Database versioning is possible.');
} else {
alert('Database versioning not compatible with this browser version.');
}
if (db.version === '') {
db.changeVersion(db.version, version,checkDBCallback, checkDBError, checkDBSuccess);
} else if (db.version != version) {
try {
alert('Database version ' + version + ' is now available.');
alert('We will now update your database.  Warning: All your data will be lost. Press OK to continue.');
db.changeVersion(db.version, version,checkDBCallback, checkDBError, checkDBSuccess);
} catch(e) {
alert('Changeversion failed');
alert('DB Version still: ' + db.version);
}
}

}
//call function on document ready using jQuery
$(document).ready(function(){
checkDBChange();
});

This might seem as a handful, but it’s basically several functions made specifically for database management thrown together as callbacks to a larger function, db.changeVersion(). Let’s start with the syntax of db.changeVersion, which goes a little something like this:

db.changeVersion(currentVersion, newVersion, callbackFunction, errorFunction, successFunction);

The current version, which can be found by using the command db.version, is being compared to the version, which is the new version of the database you want the user to change to. The callback, error, and success functions are self explanatory, and perform tasks based on each. I’ll go into more detail about them in a bit. First let’s start with the overall checkDBChange() function.

function checkDBChange() {
window['db'] = window.openDatabase(shortName, '', displayName, maxSize);
...

This section of the code, when passed the parameter ” as the database version (setting the openDatabase globally through window['db']), will allow you to open the current database regardless of what the current version is, or what version you specify in your global variable version. Shortly after, we check to see if the database is even opened by saying:

if (!db) {
alert('Could not open database connection.');
}

Now that we’ve established whether or not our database is open, we need to check against the browser to see if we are allowed to use the changeVersion() function at all. We can do this by setting:

 if (db.changeVersion) {
alert('Database versioning is possible.');
} else {
alert('Database versioning not compatible with this browser version.');
}

This will alert the user whether or not this function is available for us to use.

Sometimes, users will visit our site for the first time, and will in result open and create their local database for the first time. In this case, we don’t want to user to see that the database version has “changed” when in fact their database hasn’t even existed yet! Technically, if you do not include this next line, then the app will compare ” to the current version, which although flags true, it will give a false message to the user. Instead, let’s not notify the user at all that the database has been made, just to make their life simpler. You can achieve this by using:

if (db.version === '') {
db.changeVersion(db.version, version,checkDBCallback, checkDBError, checkDBSuccess);
} else if...

The next half of the code

else if (db.version != version) {
try {
alert('Database version ' + version + ' is now available.');
alert('We will now update your database. Warning: All your data will be lost. Press OK to continue.');
db.changeVersion(db.version, version,checkDBCallback, checkDBError, checkDBSuccess);
} catch(e) {
alert('Changeversion failed');
alert('DB Version still: ' + db.version);
}
}

will take care of any user alerts about the database version changing. You can see in the above two halves that in each one, success of the condition will fire the db.changeVersion function. We will in turn pass it the version to switch from and to, and all the functions we need it to perform after the version change is done. Once db.changeVersion() is fired, the database will begin to switch versions, and then calls the callback function. In my case, I wrote two functions, one that deletes all the tables in the database, and one that recreates them with the fields I want, to fire on the callback function. This acts as the way to “recreate” the database.

After that, I want the user to have their page automatically refreshed, which I can do with a setTimeout call. These callback, error, and success functions can be personalized to fit your need, but the sequence will always be the same, so have your commands fire wisely.

Aside from this, if you release a new version of your database and change your globally defined version variable, don’t forget to update your manifest by at least one byte. That way the user client can recognize the change and it will perform this switch. The manifest can be annoying in the end but it’s also pretty awesome.

Well that’s it! This concludes my How to make an Offline Web App guide. If there is anything you are unclear about, feel free to send me a message or comment on any post, and I’ll try my hardest to answer it! If you feel there’s some part of the web app guide that is lacking or some sort of content you wish I could add to the guide, please let me know also! I thank you for reading my guide and hope that you found it very useful. Please check out the blog on this site for updates from me on the web development side, and any random solutions or findings I come up with that might help you in your every day development! :)

-Jeffery

 

One Response

  1. Ari Ogoke

    Hi Jeffery,

    Thank you for a great, great series!!! I found your articles/blog while looking for information on “window.openDatabase” method. You answered all the questions I had clearly and then some!

    The information you provided and your writing style are great! I will play with this some more and then contact you with any questions that I may have.

    Thanks again!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>