Storing Data in a Client-Side Relational Database in Html5

About Storing Data in Html5

The localStorage and sessionStorage methods give us an easy way to store simple name/value pairs on the client’s computer, but sometimes we need more than that The localStorage and sessionStorage methods give us an easy way to store simple name/value pairs on the client’s computer, but sometimes we need more than that. . The HTML5 specification initially introduced the ability to store data in relational databases to Storing Data in Html5. It’s since been spun off into a separate specification called Web SQL Storage. 6 If you have even a basic background in writing SQL statements, you’ll feel right at home in no time. To get you comfortable, we’ll use W e b SQL Storage to create, retrieve, update, and destroy notes in a client-side database. CRUD inY our Browser The term CRUD, an acronym for “Create, Retrieve, Update, and Delete,” 7 pretty much describes what we can do with our client-side database. The specification and implementations allow us to insert, select, update, and delete records. AwesomeCo wants to equip their sales team with a simple application to collect notes while they’re on the road. This application will need to let users create new notes, as well as update and delete existing ones . To change existing notes, we’ll need to let users retrieve them from the database. Here are the SQL statements we’ll need to write in order to make this to Storing Data in Html5.

Type                Statement
Create a note       INSERT INTO notes (title, note) V A L UES(" T est", "This is a note");
Retrieve            all
notes               SELECT id, title, note FROM notes;

Retrieve a specific     SELECT id, title, note FROM notes where id = 1;
note

Update a note              UPDATE notes set title = “bar”, note = “Changed” where id =1;
Delete a note                 DELETE FROM notes where id = 1;

<body>
<section id="sidebar">
<input type="button" id="new_button" value="New note">
<ul id="notes">
</ul>
</section>
<section id="main">
<form>
<ol>
<li>
<input type="submit" id="save_button" value="Save">
<input type="submit" id="delete_button" value="Delete">
</li>
<li>
<label for="title">Title </label>
<input type="text" id="title">
</li>
<li>
<label for="note">Note </label>
<textarea id="note"></textarea>
</li>
</ol>
</form>
</section>
</body>
</html>

This style sheet turns off the bullet points, sizes the text areas, and lays things out in two columns. Now that we have the interface done, we can build the JavaScript we need to make this work. Connecting to the Database We need to make a connection and create a database to Storing Data in Html5:

// Database reference
var db = null ;
// Creates a connection to the local database
connectToDB = function()
{
db = window.openDatabase( 'awesome_notes' , '1.0' ,
'AwesomeNotes Database' , 1024 *1024*3);
};

We are declaring the db variable at the top of ours cript. Doingt his makes it available to the rest of the methods we’ll create. 8 W e then declare the method to connect to the database by using the window.openDatabase method. This takes the name of the database, a version number, a description, and a size parameter. Creating the NotesT all be Our notes table needs three columns for Storing Data in Html5:
Field          Description
id                Uniquely identifies the note. Primary key, integer,
auto-         incrementing.
title           The title of the note, for easy reference.
Note          The note itself.

createNotesTable = function()
{
db.transaction(function(tx){
tx.executeSql(
"CREATE TABLE notes (id INTEGER \
PRIMARY KEY, title TEXT, note TEXT)", [],
function(){ alert( 'Notes database created successfully!' ); },
function(tx, error){ alert(error.message); } );
});
};

We fire the SQL statement inside a transaction for Storing Data in Html5, and the transaction has two callback methods: one for a successful execution and one for a failure. This is the pattern we’ll use for each of our actions to Storing Data in Html5. Note that the executeSql() method also takes an array as its second parameter. This array is for binding placeholders in the SQL to variables. This lets us avoid string concatenation and is similar to prepared statements in other languages. In this case, the array is empty because we have no placeholders in our query to populate. Now that we have our first table, we can make this application actually do something. Loading Notes When the application loads, we want to connect to the database, create the table if it doesn’t already exist, and then fetch any existing notes from the database here to Storing Data in Html5 .

// loads all records from the notes table of the database;
fetchNotes = function(){
db.transaction(function(tx) {
tx.executeSql('SELECT id, title, note FROM notes' , [],
function(SQLTransaction, data){
for (var i = 0; i < data.rows.length; ++i) {
var row = data.rows.item(i);
var id = row[ 'id' ];
var title = row[ 'title' ];
addToNotesList(id, title);
}
});
});
};

We are embedding the ID of the record into a custom data attribute. We will use that ID to locate the record to load when the user clicks the list item. W e then add the new list item we create to the unordered list in our interface with the ID of notes. Now we need to add code to load that item into the form when we select a note from this list to Storing Data in Html5.

Fetchnig a Specific Record with Storing Data in Html5

We could add a click event to each list item, but a more practical approach is to watch any clicks on the unordered list and then determine which one was clicked. This way, when we add new entries to the list (like when we add a new note), we don’t have to add the click event to the list.
With in our jQuery function, we’ll add this code for Storing Data in Html5:

$("#notes" ).click(function(event){
if ($(event.target).is( 'li' )) {
var element = $(event.target);
loadNote(element.attr("data-id" ));
}
});
loadNote = function(id){
db.transaction(function(tx) {
tx.executeSql('SELECT id, title, note FROM notes where id = ?' , [id],
function(SQLTransaction, data){
var row = data.rows.item(0);
var title = $( "#title" );
var note = $( "#note" );
title.val(row["title" ]);
title.attr("data-id" , row[ "id" ]);
note.val(row["note" ]);
$("#delete_button" ).show();
});
});
}

Falling Back with Storing Data in Html5

Unlike our other solutions, there are no good libraries available that would let us implement SQL storage ourselves, so we have no way to provide support to Internet Explorer users natively. However, if this type of application is something you think could be useful, you could convince your users to use Google Chrome, which works on all platforms, for this specific application. That’s not an unheard of practice, especially if using an alternative browser allows you to build an internal application that could be made to work on mobile devices as well. Another alternative is to use the Google Chrome Frame plug-in. This snippet gets read by the Google Chrome Frame plug-in and activates it for this page. If you want to detect the presence of the plug-in and prompt your users to install it if it doesn’t exist, you can add this snippet right above the closing body tag for Storing Data in Html5:

<script type= "text/javascript"
src=
"http://ajax.googleapis.com/ajax/libs/chrome-frame/1/CFInstall.min.js" >
</script>
<script>
window.attachEvent("onload" , function() {
CFInstall.check({
mode: "inline" , // the default
node: "prompt"
});
});
</script>

This will give the user an option to install the plug-in so they can work with your site. Google Chrome Frame may not be a viable solution for a web application meant to be used by the general public, but it works well for internal applications like the one we just wrote. There may be corporate IT policies that prohibit something like this, but I’ll leave that up to you to work out how you can get something like this approved if you’re in that situation. Installing a plug-in is certainly more cost-effective than writing your own SQL database system to Storing Data in Html5.

andcombd