I recently came across Ken Corbett’s helper module developed to assist in working with HTML5 Web Databases. While the debate continues on offline storage and the HTML5 spec I decided to give this library a try. Below I will detail a few pointers for those interested in getting started. You can also find some documentation on github.
The first step to getting started is to think about your database structure. Since you are going to be suing offline browser storage it is important to have the browser setup the database and create your tables for you when someone visits your web app.
UPDATE: For quick access to the code download my example on Github:
Create a file named “setup.sql”https://github.com/tegansnyder/JQuery-Mobile—HTML5-Web-DB-Example#readme
CREATE TABLE person (id INTEGER NOT NULL, first_name VARCHAR(35), last_name VARCHAR(35), age INTEGER, money DOUBLE, PRIMARY KEY (id));
CREATE TABLE places (id INTEGER NOT NULL, name VARCHAR(35), location VARCHAR(35) PRIMARY KEY (id));
This file will execute the first time a user visits your web app and will create all your required tables.
Download html5sql from github. Then create a blank JQuery mobile page using this structure.
My Web App
<script type="text/javascript" src="http://code.jquery.com/jquery-1.6.4.min.js"></script><script type="text/javascript" src="http://code.jquery.com/mobile/1.0rc3/jquery.mobile-1.0rc3.min.js"></script>
<script type="text/javascript" src="js/html5sql.js"></script><script type="text/javascript" src="js/app.js"></script></pre>
<div class="type-index" data-role="page" data-theme="a">
<div data-role="header" data-theme="a">
<h1>My Web App</h1>
</div>
<div data-role="content">
<ul id="person" data-role="listview" data-inset="true" data-theme="a" data-dividertheme="a">
<li data-icon="plus"><a href="new_run.html">Person Details</a></li>
</ul>
</div>
</div>
<pre>
In your app.js file setup your connection like this:
$(document).ready(function(){
if(!html5sql.database){
html5sql.openDatabase("com.myapp.appdb", "App Data", 5*1024*1024);
$.get('setup.sql',function(sqlStatements){
html5sql.process(
sqlStatements,
function(){
},
function(error, statement){
console.error("Error: " + error.message + " when processing " + statement);
}
);
});
}
html5sql.logInfo = true;
html5sql.logErrors = true;
html5sql.putSelectResultsInArray = true;
});
This will process on DOM ready and setup your table. You can see if it succeed by opening up Google Chrome web inspector and clicking on the Resources tab. You should see your database has been created.
To add data use:
var first_name = 'Tegan';
var last_name = 'Snyder';
var age = '25';
var money = '5500';
html5sql.process(
[
"INSERT INTO person (first_name, last_name, age, money) VALUES ('" + first_name + "','" + last_name + "','" + age + "','" + money + "')"
],
function(){
alert('sucess');
},
function(error, statement){
console.error("Error: " + error.message + " when processing " + statement);
}
);
To select data use:
html5sql.process(
[
"SELECT * FROM person WHERE id = 1"
],
function(transaction, results, rowArray) {
var html = '</pre>
<ul>
<li data-theme="b">';
$.each(rowArray, function(index, value) {
html += '
First name: ' + value.first_name + '
';
html += '
Last name: ' + value.last_name + '
';
html += '
Age: ' + value.age + '
';
});
html += '</li>
</ul>
<pre>
';
//refresh jquery mobile listview
$('#person').append(html);
$('#person').listview("refresh");
},
function(error, statement){
console.error("Error: " + error.message + " when processing " + statement);
}
);
This is just a quick overview. More details to come as I start playing with this and session storage.
Check out Kens website: http://html5sql.com
-
Tim Bendall
-
Rachel