Cartoon Clouds
Mountains
Sunrise
Clouds
Green Clouds
None
HTML5 Local Databases or WebSQL
Getting started with HTML5's new local databases.
So you've probably heard a lot about the new HTML, HTML5. This is tutorial is about one of my favorite new specifications from it, client-side databases. This new specification will be popular, web-apps are now changing the web and portability it the key. People don't have the internet everywhere (yet) so this is where client-side functionality comes in, these databases allow for storage offline and when connection is available, update online.
» Creating the database
openDatabase(shortName, version, displayName, maxSize);
We use the openDatabase to create and open a database. This function which has a few specific parameters.
- shortName - This is basically the name you will use to reference the database. Keep it simple and distinguishable.
- version - This is a string, so anything is generally accepted i.e using the alpha (α) or beta (β) symbols
- displayName - Just for readability, has no real effect on the database.
- maxSize - An integer which states the maximum size of the database in bytes
» Querying the database
database.transaction(
function (transaction) {
//Usage: transaction.executeSql(_QUERY_, [vars], callback);
//Create Table
transaction.executeSql("CREATE TABLE foobar(id, foo, bar)");
//Insert data into database
transaction.executeSql("INSERT INTO table_name(foo, bar) VALUES(?, ?)", ['foo', 'bar']);
} );The transaction function is basically the wrapper for the executeSql function. ExecuteSql is the vital function for querying the database. The parameters for executeSql is just the SQL query.
» Getting the data
//Your query
function(transaction) {
transaction.executeSql("SELECT * FROM sample", [], retrieveData); //The retrieveData function is where the data is sent/returned to in parameters (transation, results)
});The key to retrieving the data is to send it to a function to parse and use/display it. As in the example above, I use a function called retriveData. The data is sent as a parameter in the function.
function retrieveData(transaction, results) {
//Usage: results.rows.item(int)[columnName];
//Loop through the data, similar to using PHP etc.
for(var i = 0; i < results.rows.length; i++) {
//This will return the data from row i (the integers from loop) from the column 'foobar'
var data = results.rows.item(i)['foobar']
}
}To access the data, we need to use the results parameter. The data is a bit different from accessing normal Javascript variables as it needs the keywords rows and item(). Item(int) needs an integer to select a specific row, it does not return every row. This will return a Javascript object with the data in that row. After we have our row selected, we select a column we want the data returned from, so we add our column name after item() e.g item(2)['foo'] This is exactly the same as if you were retrieve data in a normal JS Object.
Okay, so this is where the actual tutorial starts. In this we will create a simple Web App to add and remove data from a simple, local database. Lets get started!
YAYMusic, this is the fictional (but awesome) app we will be creating. We will create a simple song addition (Not actual MP3, but just names etc.) and rating system. Nothing mildly complex.
What we need:
- Check compatibility
- To create the database and tables
- To load songs and rating already added (if any) on load.
- Form to add songs to database
- Rating functionality on each song
We need to firstly check compatibility. Only a few browsers, as listed above are currently supporting local databases which meaning's we'd like to tell the user why there is no content coming up or why errors are popping up, basically wtf is going on. So, Although it is messy, I find it handy to manage if you completely wrap your code in and [if/else] statement.
if(!window.openDatabase){
setAnError("Woops, your browser doesn't support Database. Please update/change your browser");
} else {
//Run main you code
}
What it's really saying: IF it's not possible to open database => Display error ELSE it is possible to open database => Run code.
Now that we know that the user supports Local Databases, we are set for creating one. For this we'll need to decide on it's name, version, long name and maximum size. This is done with the openDatabase function. It does exactly what it says on the tin, it opens or creates a database.
function createDatabase() {
var shortName = 'yaymusic';
var version = '1.0';
var displayName = 'YAYMusic Database';
var maxSize = 100000; //The variables to open the database
return yaymusic = openDatabase(shortName, version, displayName, maxSize); //This will return the database
}
}
var db = createDatabase(); //Create the DB and set it to a global variable for easy access
var yaymusic = openDatabase(shortName, version, displayName, maxSize);
This is where the database is created, as you can see in the above code, each of its parameters (shortName, version etc.) are defined above it. To check if you have created your database, you can open your inspector window and check the storage tab, we should see our 'yaymusic' database as clear as daylight.
So once you have your database created, we need to populate it with some tables! So what would we need for storing a song?
- id - Primary key
- name - text
- artist - text
- album - text
- genre - text
- rating - Integer
CREATE TABLE songlist( id INTEGER PRIMARY KEY, name TEXT, artist TEXT, album TEXT, genre TEXT, rating INTEGER );
Now that we have our query constructed, we should put it to the database, in a nice and tidy function. Later on we may want to remove and the add the table.
//Lets make some tables
function createTables(db) {
db.transaction(
function(transaction) {
//Query to create database
transaction.executeSql('CREATE TABLE IF NOT EXISTS songlist(id INTEGER PRIMARY KEY, name TEXT, artist TEXT, album TEXT, genre TEXT, rating INTEGER, user TEXT);');
});
}
This function will be called when the app is first loaded, and will create a table IF NOT EXISTS. As mention above, when we will need to delete all of the records, we will destroy the table then re-add it, therefore needing to call the createTables function.
STILL IN PROGRESS, IN THE MEANTIME
(Of course this tutorial is subject to mistakes and mishaps (my own stupidity), please correct me on anything incorrect.)
(Local Database capable browsers only i.e Chrome, Opera, Safari)
and the column names are based off the standards at work. 
. Things might have changed now.
Help
Register Now!