Starting with Safari 4, iPhone/iPad OS3, Chrome 5, and Opera 10.5 (Desktop), HTML5 Local Databases are now supported. I’ve been reading about local databases for quite some time and decided to do a write up with some basic examples on how to get started.
SETTING UP THE DATABASE
- function initDatabase() {
- try {
- if (!window.openDatabase) {
- alert('Databases are not supported in this browser.');
- } else {
- var shortName = 'DEMODB';
- var version = '1.0';
- var displayName = 'DEMO Database';
- var maxSize = 100000; // bytes
- DEMODB = openDatabase(shortName, version, displayName, maxSize);
- createTables();
- selectAll();
- }
- } catch(e) {
- if (e == 2) {
- // Version number mismatch.
- console.log("Invalid database version.");
- } else {
- console.log("Unknown error "+e+".");
- }
- return;
- }
- }
First we check if the browser supports the openDatabase method, is so we continue and define the database parameters:
shortNameis the DB name as it will be referred to by the browser and SQLversionopenDatabase version. 1.0 for this (more on that here)displayNameThe full display name / description of the databasemaxSizeThis is max size in bytes is the size you expect the database to reach. This is essential for memory management purposes.
Next, we call the
createTables(); function where the table is defined and pre-populated with initial data (optional).BUILDING THE TABLE
- function createTables(){
- DEMODB.transaction(
- function (transaction) {
- transaction.executeSql('CREATE TABLE IF NOT EXISTS page_settings(id INTEGER NOT NULL PRIMARY KEY, fname TEXT NOT NULL,bgcolor TEXT NOT NULL, font TEXT, favcar TEXT);', [], nullDataHandler, errorHandler);
- }
- );
- prePopulate();
- }
This basic function executes the
executeSql method which runs as the CREATE TABLE query. The SQL syntax is based on SQLite so it should feel familiar to many Web & Mobile developers. For this demo, we are going to pre-populate the newly created page_settings table with some initial data:- function prePopulate(){
- DEMODB.transaction(
- function (transaction) {
- //Optional Starter Data when page is initialized
- var data = ['1','none','#B3B4EF','Helvetica','Porsche 911 GT3'];
- transaction.executeSql("INSERT INTO page_settings(id, fname, bgcolor, font, favcar) VALUES (?, ?, ?, ?, ?)", [data[0], data[1], data[2], data[3], data[4]]);
- }
- );
- }
Note: although we don’t really need a PK for such a basic demo it will be there for future extensions and advancements.
Now the database is initialized, a table has been created and we have a row of default data inserted. Since the data is stored, you can now reload or page or close and reopen it and the data will remain. To visualize this in Safari, go to Develop > Show Web Inspector > Databases tab where you can view the database and its contents as they are added or changed.
SELECT THE DATA
After the initial page load the data is stored and we run the
selectAll() function to get the data:- function selectAll(){
- DEMODB.transaction(
- function (transaction) {
- transaction.executeSql("SELECT * FROM page_settings;", [],
- dataSelectHandler, errorHandler);
- }
- );
- }
The demo consists of only one row so we use
* to grab everything from the database but please optimize your queries if you decide to use this example for your own projects.- function dataSelectHandler(transaction, results){
- // Handle the results
- for (var i=0; i<results.rows.length; i++)="" {="" var="" row="results.rows.item(i);" newfeature="new" object();="" newfeature.fname="row['fname'];" newfeature.bgcolor="row['bgcolor'];" newfeature.font="row['font'];" newfeature.favcar="row['favcar'];" $('body').css('background-color',newfeature.bgcolor);="" $('body').css('font-family',newfeature.font);="" $('#content').html('="" <h4="" id="your_car">Your Favorite Car is a '+ newFeature.favcar +'
- ');
- if(newFeature.fname != 'none') {
- $('#greeting').html('Howdy-ho, '+ newFeature.fname+'!');
- $('#fname').val(newFeature.fname);
- }
- $('select#font_selection').find('option[value='+newFeature.font+']').attr('selected','selected');
- $('select#bg_color').find('option[value='+newFeature.bgcolor+']').attr('selected','selected');
- $('select#fav_car').find('option[value='+newFeature.favcar+']').attr('selected','selected');
- }
- }
- </results.rows.length;>
This function loops through our data and uses a bit of jQuery to apply the specific values to CSS properties on the DOM. Going further, we set the HTML form defaults based on data with the help of jQuery.
MAKING UPDATES
This function reads the form values, validates the
text input and updates the database- function updateSetting(){
- DEMODB.transaction(
- function (transaction) {
- if($('#fname').val() != '') {
- var fname = $('#fname').val();
- } else {
- var fname = 'none';
- }
- var bg = $('#bg_color').val();
- var font = $('#font_selection').val();
- var car = $('#fav_car').val();
- transaction.executeSql("UPDATE page_settings SET fname=?, bgcolor=?, font=?, favcar=? WHERE id = 1", [fname, bg, font, car]);
- }
- );
- selectAll();
- }
DROPPING THE TABLE
We could simply remove the row with a
DELETE query but since we already check for the presence of data, it makes more sense to DROP the table:- function dropTables(){
- DEMODB.transaction(
- function (transaction) {
- transaction.executeSql("DROP TABLE page_settings;", [], nullDataHandler, errorHandler);
- }
- );
- location.reload();
- }
After the table is dropped, the page is refreshed which triggers the initial database setup procedures defined earlier.
THE DEMO:
WHY SHOULD YOU USE LOCAL DATABASES?
- →
- They are fast, flexible and never expire. Developers can define the maximum size of the database and since all of the queries are run via JavaScript, no external server calls are required and unlike cookies and sessions, they persist until manually removed.
- →
- Unlike cookies and sessions, which are created on the server side and then stored in the browser, the information stored in a local database is truly local.
- →
- Safari and Opera’s local databases are based on SQLite and are threadsafe which prevents multiple instances of the data from being opened concurrently.
- →
- iPhone and iPad OS support local databases through their Mobile Safari browsers, making this storage platform ideal for mobile web apps.
- →
- From the UX perspective, the possibility of storing user and page settings and options locally, instantly and securely without the out the need to make external server or server side database calls is incredible. Since the HTML5 is widely supported in Webkit and Opera, there are nearly limitless possibilities for HTML5-based games and web apps.
WHY SHOULD YOU NOT USE LOCAL DATABASES?
Local databases are a great way for you to simplify and improve your projects but they have a few obvious drawbacks:
- →
- Limited cross-browser support in the current form. At the time of this article, Safari 4, iPhone / iPad OS3, Chrome 5 and Opera 10.5 (Desktop) are the only mainstream browsers with support.
- →
- Not ideal for sensitive information. Sure, JavaScript can do MD5 and SHA1 hashing but ifthese guys can crack the code while listening to KoЯn, how safe do you feel?
- →
- Even though SQLite has been around for 10 years now, Webkit / Opera support is much newer and has consequently seen less vetting from those who would want to steal your data.
WHAT ABOUT FIREFOX, CHROME AND IE?
- →
- Firefox introduced support for localStorage in version 3.5 in the
key-valuepair format (similar to cookies and sessions) but has so far (and for the foreseeable future) stayed away from the SQL Database Storage model. More about that here, here and here - →
- Internet Explorer has offered persistent storage support since IE5.5 which they’ve termed userData Behavior. This technique is very limited in features and has a maximum storage size of 128k per page. IE local storage improved in IE8 with the introduction of localStorage. Both IE local storage formats are
key-valueand there is currently no native local database support in IE—maybe in IE9? - →
- Prior to version 4, local database storage support in Chrome was limited to implimentaiton via the Google Gears API. Gears offers a very similar SQLite-based local storage component that works across most browsers including IE6+, FireFox, Opera and Safari. The Gears platform was created to let developers create web applications that can be used offline and also offers support for other features such as Canvas, Desktop support and Geolocation. The downside is that users must install the Google Gears browser extension on their machine before they can access these features. Additionally, Gears requires developers to include a JavaScript file in their application in order to use Gears which makes it a less than ideal choice for the low-bandwidth mobile web application world. More on the Google Gears Database API here.
No comments:
Post a Comment