Introduction
Today we are going to create a CRUD JQuery Mobile web app using IndexedDB as a back-end. I will demonstrate the following functionality:
1. Create an IndexedDB database & table
2. CReate a record
3. Update an existing record
4. Delete an existing record
5. View records in a list
6. Generate HTML table from records and
7. Export records to an Excel file from an HTML table.
The IndexedDB database resides in browser storage and works in the same fashion as WebSQL. The same CRUD functionality discussed in my article about JQuery Mobile CRUD WebSQL will be applied here.
But what is IndexedDB?
IndexedDB is an API for client-side storage of significant amounts of structured data, which also enables high performance searches of this data using indexes. IndexedDB is a transactional database system, like a SQL-based RDBMS; however whereas the latter uses tables with fixed columns, IndexedDB is a JavaScript-based object-oriented database. IndexedDB lets you store and retrieve objects that are indexed with a key; any objects supported by the structured clone algorithm can be stored. You need to specify the database schema, open a connection to your database, and then retrieve and update data within a series of transactions.
Operations performed using IndexedDB are done asynchronously, so as not to block the rest of an application's running. IndexedDB originally included both an asynchronous API and a synchronous API; the synchronous API being intended for use only with Web Workers. The synchronous version was removed from the spec because its need was questionable, however it may be reintroduced in the future if there is enough demand from web developers.
There isn't any limit on a single database item's size, however there is in some cases a limit on each IndexedDB database's total size. To get access to a database, call <font color="#0095dd">open()</font>
on the <font color="#0095dd">indexedDB</font>
attribute of a window object. This method returns an <font color="#0095dd">IDBRequest</font>
object; asynchronous operations communicate to the calling application by firing events on <font color="#0095dd">IDBRequest</font>
objects. For more information about IndexedDB, see here.
Some silly assumptions: You have created a JQuery Mobile app before, you are familiar with javascript.
Background
This article is an continuation of my articles about JQuery Mobile and the various database back-ends that developers can use in developing mobile applications. The various approaches of storing database information that I have discussed have been the following:
JQuery Mobile CRUD - LocalStorage
JQuery Mobile CRUD - JSON
JQuery Mobile CRUD - XML
JQuery Mobile CRUD - WebSQL
If you have gone through the articles, the same approach in developing, naming my applications has always been similar with the exception of the code handling the database operations. I'm following on that trend here. For this exercise I have developed an app to store my movie collection database. For each movie we will just keep simple details, Movie Name, Movie Year, Movie Genre. On our movie listing, we will display the movie name as a title, movie genre on the sub-title and on the count bubble the movie year.
Using the code
The code of this app is referenced within the js/app.js file as indicated in the main index.html file. In developing this app you use the normal boiler-plate to develop HTML5 application and customise it with JQuery references for both css and js files.
1. Create an IndexedDB database and table
To do this, we define variables that will be used with our database.
var dbMoviesDatabase;
var dbName = "MoviesDatabase";
var dbVersion = 1;
Here we define a database object dbMoviesDatabase that we will use to reference our database, the name of the database will be "MoviesDatabase" and this will be version 1. When all is done, from your browser (chrome), you will be able to select the MoviesDatabase from the Resources tab and be able to access the database as depicted below:
Expanding the database tree gives out the details of your table. The jscript that makes this possible is the following, all executed with our app.init function
var request = indexedDB.open(dbName, dbVersion);
request.onupgradeneeded = function(e) {
var thisDB = e.target.result;
var store = null;
if (!thisDB.objectStoreNames.contains("Movie")) {
store = thisDB.createObjectStore("Movie", {keyPath: "MovieName" });
}
};
request.onsuccess = function(e) {
dbMoviesDatabase = e.target.result;
}
From the code above, the object stores were checked for an existence of one "Movie" table. If it does not exist, this gets created with a primary key of MovieName. We are not using an autoincrement key for primary keys, thus just one line of code creates the table with the primary key. The image above shows how the information will be stored in your database backend when processed.
2. CR - eate a record
We have create a simple user interface to provide functionalituy for an end user to capture the movie information. This is depicted by the screen below.
A user enteres the movie information and presses Save to create a new record to the database storage. Three things happen here, 2.1 the screen is read from its html definition and displayed, 2.2 the movie details are read from the input controls when a user presses save and 2.3 a transaction is generated to store the record object to the database. Let's look at these sub steps in detail.
2.1 Create Movie Screen HTML definition
<div id="pgAddMovie" data-role="page">
<div data-position="left" data-display="overlay" data-position-fixed="true" id="pgAddMovieLeftPnl" data-role="panel">
<ul data-role="listview" id="pgAddMovieLeftPnlLV">
<li><a data-transition="slide" href="#pgAddMovie" class="ui-btn ui-icon-plus ui-btn-icon-left">New</a></li>
<li><a data-transition="slide" href="#pgAddMultMovie" class="ui-btn ui-icon-plus ui-btn-icon-left">New Multiple</a></li>
<li><a data-transition="slide" href="#pgRptMovie" class="ui-btn ui-icon-eye ui-btn-icon-left">Report</a></li>
<li><a data-transition="slide" href="#pgMovie" class="ui-btn ui-icon-carat-l ui-btn-icon-left">Back</a></li>
</ul>
</div>
<div data-position="right" data-display="overlay" data-position-fixed="true" id="pgAddMovieRightPnl" data-role="panel">
<ul data-role="listview" id="pgAddMovieRightPnlLV">
</ul>
</div>
<header id="pgAddMovieHdr" data-role="header" data-position="fixed" data-tap-toggle="false">
<h1>Movies Database 1.00</h1>
<a data-role="button" id="pgAddMovieMenu" data-icon="bars" href="#pgAddMovieLeftPnl" class="ui-btn-left">Menu</a>
<a data-role="button" id="pgAddMovieRightMenu" data-icon="records" href="#pgAddMovieRightPnl" class="ui-btn-right">Records</a>
</header>
<div id="pgAddMovieCnt" data-role="content">
<h3>Add Movie</h3><div id="pgAddMovieForm">
<div data-role="fieldcontain">
<label for="pgAddMovieMovieName">Movie Name<span class='red'>*</span></label>
<input required name="pgAddMovieMovieName" title="Enter movie name here." type="text" id="pgAddMovieMovieName" placeholder="Enter movie name here." autocomplete="off" data-clear-btn="true"></input>
</div>
<div data-role="fieldcontain">
<label for="pgAddMovieMovieYear">Movie Year<span class='red'>*</span></label>
<input required name="pgAddMovieMovieYear" title="Enter movie year here." type="text" id="pgAddMovieMovieYear" placeholder="Enter movie year here." autocomplete="off" data-clear-btn="true"></input>
</div>
<div data-role="fieldcontain">
<label for="pgAddMovieMovieGenre">Movie Genre<span class='red'>*</span></label>
<input required name="pgAddMovieMovieGenre" title="Enter movie genre here." type="text" id="pgAddMovieMovieGenre" placeholder="Enter movie genre here." autocomplete="off" data-clear-btn="true"></input>
</div>
</div>
</div>
<footer id="pgAddMovieFtr" data-role="footer" data-position="fixed" data-tap-toggle="false">
<div id="pgAddMovieFtrNavBar" data-role="navbar">
<ul>
<li><a id="pgAddMovieBack" data-icon="carat-l">Cancel</a>
</li>
<li><a type="submit" id="pgAddMovieSave" data-icon="action">Save</a>
</li>
</ul>
</div>
</footer>
</div>
There is a left sliding menu on this screen with a listview with functionality to add a new movie, add multiple movies, view the movies report and go back to the movie listing. This is activated when clicking the Menu button on the header. Also a right sliding menu exists that gets activated when clicking Records to list available movies in the database.
Input controls for the movie details are pgAddMovieMovieName, pgAddMovieMovieYear and pgAddMovieMovieGenre based on the fields we want to store for the movie model. The save button name is pgAddMovieSave.
2.2 Getting User Movie Input
$('#pgAddMovieSave').on('click', function (e) {
e.preventDefault();
e.stopImmediatePropagation();
var MovieRec = pgAddMovieGetRec();
app.addMovie(MovieRec);
});
As soon as a user presses the Save button, processes to get the user input and save it occur. Getting the data is done with pgAddMovieGetRec.
function pgAddMovieGetRec() {
var MovieRec = {};
MovieRec.MovieName = $('#pgAddMovieMovieName').val().trim();
MovieRec.MovieYear = $('#pgAddMovieMovieYear').val().trim();
MovieRec.MovieGenre = $('#pgAddMovieMovieGenre').val().trim();
return MovieRec;
}
then the movie record is saved with the app.addMovie(MovieRec), with that function receiving the details of the new movie to create.
2.3 Storing the movie to the database
app.addMovie = function (MovieRec) {
$.mobile.loading("show", {
text: "Creating record...",
textVisible: true,
textonly: false,
html: ""
});
var MovieName = MovieRec.MovieName;
MovieName = MovieName.split(' ').join('-');
MovieRec.MovieName = MovieName;
var tx = dbMoviesDatabase.transaction(["Movie"], "readwrite");
var store = tx.objectStore("Movie");
var request = store.add(MovieRec);
request.onsuccess = function(e) {
toastr.success('Movie record successfully added.', 'Movies Database');
var pgFrom = $('#pgAddMovie').data('from');
switch (pgFrom) {
case "pgSignIn":
$.mobile.changePage('#pgSignIn', {transition: pgtransition});
break;
default:
pgAddMovieClear();
}
}
request.onerror = function(e) {
toastr.error('Movie record NOT successfully added.', 'Movies Database');
}
$.mobile.loading("hide");
};
From the obtained movie record, we clean our primary key value, i.e. movie name and remove all spaces with -. Because we are writing a record to the IndexedDB database, we create a readwrite transaction. We then access the "movie" table and store the record as is. The record stores as you might have noted is in JSON notation format. When the movie is added, a toast is shown telling the user else an error toast is shown.
3. U - pdate an existing record
To update an existing record, you need to select it first from existing records and then update it. Just like the create record method, we define a record object based on the movie we are updating. Within this app, you access an existing movie from the movie listing page and this brings up the movie update page where you can also delete an existing movie. Lets look at this screen.
We have selected one of the existing movies here called Fast n Furious 7 and its details popped up. As the movie name is primary, it cannot be changed but the movie year and genre can be changed. After changing the movie details and clicking Update, the movie details are read and saved as an object and this gets passed as a variable to store the movie in the database. As you have noted, the update screen is similar to the add screen. The difference are the control names.
Input controls for the movie details in this care are pgEditMovieMovieName, pgEditMovieMovieYear and pgEditMovieMovieGenre based on the fields we want to store for the movie model. The save button name is pgEditMovieUpdate.
3.1 Getting Updated Movie Input
$('#pgEditMovieUpdate').on('click', function (e) {
e.preventDefault();
e.stopImmediatePropagation();
var MovieRec = pgEditMovieGetRec();
app.updateMovie(MovieRec);
});
As soon as a user presses the Save button, processes to get the user input and save it occur. Getting the data is done with pgEditMovieGetRec.
function pgEditMovieGetRec() {
var MovieRec = {};
MovieRec.MovieName = $('#pgEditMovieMovieName').val().trim();
MovieRec.MovieYear = $('#pgEditMovieMovieYear').val().trim();
MovieRec.MovieGenre = $('#pgEditMovieMovieGenre').val().trim();
return MovieRec;
}
then the movie record is saved with the app.updateMovie(MovieRec), with that function receiving the details of the updated movie record.
3.2 Storing the updated movie in the database
app.updateMovie = function (MovieRec) {
$.mobile.loading("show", {
text: "Update record...",
textVisible: true,
textonly: false,
html: ""
});
var MovieName = MovieRec.MovieName;
MovieName = MovieName.split(' ').join('-');
MovieRec.MovieName = MovieName;
var tx = dbMoviesDatabase.transaction(["Movie"], "readwrite");
var store = tx.objectStore("Movie");
store.get(MovieName).onsuccess = function(e) {
var request = store.put(MovieRec);
request.onsuccess = function(e) {
toastr.success('Movie record updated.', 'Movies Database');
pgEditMovieClear();
$.mobile.changePage('#pgMovie', {transition: pgtransition});
}
request.onerror = function(e) {
toastr.error('Movie record not updated, please try again.', 'Movies Database');
return;
}
};
$.mobile.loading("hide");
};
From the obtained movie record, we clean our primary key value, i.e. movie name and remove all spaces with -. Because we are writing a record to the IndexedDB database, we create a readwrite transaction. We then access the "movie" table, locate an existing record using the primary key, when that is succesful, we store the record details.
4. D - elete an existing record
To be able to delete an existing record, first select it and then delete it. To delete a movie in this app, you access that functionality from the Movie update screen. Clicking the delete button runs like this.
$('#pgEditMovieDelete').on('click', function (e) {
e.preventDefault();
e.stopImmediatePropagation();
var MovieName = $('#pgEditMovieMovieName').val().trim();
$('#msgboxheader h1').text('Confirm Delete');
$('#msgboxtitle').text(MovieName.split('-').join(' '));
$('#msgboxprompt').text('Are you sure that you want to delete this movie? This action cannot be undone.');
$('#msgboxyes').data('method', 'deleteMovie');
$('#msgboxno').data('method', 'editMovie');
$('#msgboxyes').data('id', MovieName.split(' ').join('-'));
$('#msgboxno').data('id', MovieName.split(' ').join('-'));
$('#msgboxyes').data('topage', 'pgEditMovie');
$('#msgboxno').data('topage', 'pgEditMovie');
$.mobile.changePage('#msgbox', {transition: 'pop'});
});
The movie name is read and this is stored within our message box screen definition. A user is asked to confirm the delete action.
When a user opts to delete a movie, by clicking Yes, app.deleteMovie gets called passing that the movie name stored in the data-id attribute of the Yes button.
app.deleteMovie = function (MovieName) {
$.mobile.loading("show", {
text: "Deleting record...",
textVisible: true,
textonly: false,
html: ""
});
MovieName = MovieName.split(' ').join('-');
var tx = dbMoviesDatabase.transaction(["Movie"], "readwrite");
var store = tx.objectStore("Movie");
var request = store.delete(MovieName);
request.onsuccess = function(e) {
toastr.success('Movie record deleted.', 'Movies Database');
$.mobile.changePage('#pgMovie', {transition: pgtransition});
}
request.onerror = function(e) {
toastr.error('Movie record not deleted, please try again.', 'Movies Database');
return;
}
$.mobile.loading("hide");
};
On success of this process, the user gets told that the record is deleted or else notified otherwise. From this again, a readwrite transaction is defined on the movie table. The store is them opened and then using the moviename a store.delete method is executed to delete the record in the database.
5. V - iew records in a list
Viewing records from the storage involves reading all existing records from the object store and in this case display them in the listview. This happens in the movie listing page. Let's look at the definition of the movie listing page. This is just a simple page with a blank listview that gets updated during runtime when the page is shown.
<div id="pgMovie" data-role="page">
<div data-position="left" data-display="overlay" data-position-fixed="true" id="pgMovieLeftPnl" data-role="panel">
<ul data-role="listview" id="pgMovieLeftPnlLV">
<li><a data-transition="slide" href="#pgAddMultMovie" class="ui-btn ui-icon-plus ui-btn-icon-left">New Multiple</a></li>
<li><a data-transition="slide" href="#pgRptMovie" class="ui-btn ui-icon-eye ui-btn-icon-left">Report</a></li>
<li><a data-transition="slide" href="#pgMenu" class="ui-btn ui-icon-carat-l ui-btn-icon-left">Back</a></li>
</ul>
</div>
<header id="pgMovieHdr" data-role="header" data-position="fixed" data-tap-toggle="false">
<h1>Movies Database 1.00</h1>
<a data-role="button" id="pgMovieMenu" data-icon="bars" data-transition="slide" href="#pgMovieLeftPnl" class="ui-btn-left">Menu</a>
<a data-role="button" id="pgMovieNew" data-icon="plus" data-theme="b" class="ui-btn-right">New</a>
</header>
<div id="pgMovieCnt" data-role="content">
<h3>Movies</h3><ul data-role="listview" data-inset="true" id="pgMovieList" data-autodividers="true" data-filter="true" data-filter-placeholder="Search Movies">
<li data-role="list-divider">Your Movies</li>
<li id="noMovie">You have no movies</li>
</ul>
</div>
</div>
To make this work, within out js/app.js, we defined some variables to manage the listview.
// variable definitions go here
var MovieLi = '<li><a data-id="Z2"><h2>Z1</h2><p>DESCRIPTION</p><p><span class="ui-li-count">COUNTBUBBLE</span></p></a></li>';
var MovieHdr = '<li data-role="list-divider">Your Movies</li>';
var noMovie = '<li id="noMovie">You have no movies</li>';
For each listview item, we want to show the title, description and countbubble as depicted below:
This movie listing happens when the page is being accessed by calling app.checkForMovieStorage which is called by the pagebeforechange method. Let's look at that.
app.checkForMovieStorage = function () {
$.mobile.loading("show", {
text: "Checking storage...",
textVisible: true,
textonly: false,
html: ""
});
var MovieObj = {};
var tx = dbMoviesDatabase.transaction(["Movie"], "readonly");
var store = tx.objectStore("Movie");
var request = store.openCursor();
request.onsuccess = function(e) {
var cursor = e.target.result;
if (cursor) {
MovieObj[cursor.key] = cursor.value;
cursor.continue();
}
if (!$.isEmptyObject(MovieObj)) {
app.displayMovie(MovieObj);
} else {
$('#pgMovieList').html(MovieHdr + noMovie).listview('refresh');
}
}
$.mobile.loading("hide");
request.onerror = function(e) {
$.mobile.loading("hide");
$('#pgMovieList').html(MovieHdr + noMovie).listview('refresh');
}
};
This creates a transaction within the Movie table in readonly mode as we just want to fetch records. We also define an object to store all movie information in the table, we call this MovieObj and loop through each movie with cursor.continue assigning each movie details to a key object within the MovieObj object. We later pass this to app.displayMovie to display the records to the listview.
app.displayMovie = function (MovieObj) {
$.mobile.loading("show", {
text: "Displaying records...",
textVisible: true,
textonly: false,
html: ""
});
var html = '';
var n;
for (n in MovieObj) {
var MovieRec = MovieObj[n];
var pkey = MovieRec.MovieName;
pkey = pkey.split('-').join(' ');
MovieRec.MovieName = pkey;
var nItem = MovieLi;
nItem = nItem.replace(/Z2/g,n);
var nTitle = '';
nTitle = n.split('-').join(' ');
nItem = nItem.replace(/Z1/g,nTitle);
var nCountBubble = '';
nCountBubble += MovieRec.MovieYear;
nItem = nItem.replace(/COUNTBUBBLE/g,nCountBubble);
var nDescription = '';
nDescription += MovieRec.MovieGenre;
nItem = nItem.replace(/DESCRIPTION/g,nDescription);
html += nItem;
}
$('#pgMovieList').html(MovieHdr + html).listview('refresh');
$.mobile.loading("hide");
};
Here we loop through each movie record obtained from checkStorage, replace our - with spaces. We then get the movie attributes and build on our listview item from the variables. After all the items are built we refresh the listview with our contents.
6. Generate HTML table from records and
Clicking Reports from any of the screens generates a HTML table of the movies stored. This is done by running app.MoviRpt.
app.MovieRpt = function () {
$.mobile.loading("show", {
text: "Loading report...",
textVisible: true,
textonly: false,
html: ""
});
$('#RptMovie tbody tr').remove();
var n, MovieRec;
var tx = dbMoviesDatabase.transaction(["Movie"], "readonly");
var store = tx.objectStore("Movie");
var request = store.openCursor();
request.onsuccess = function(e) {
var cursor = e.target.result;
if (cursor) {
n = cursor.key;
n = n.split('-').join(' ');
MovieRec = cursor.value;
var eachrow = '<tr>';
eachrow += '<td class="ui-body-c">' + n + '</td>';
eachrow += '<td class="ui-body-c">' + MovieRec.MovieYear + '</td>';
eachrow += '<td class="ui-body-c">' + MovieRec.MovieGenre + '</td>';
eachrow += '</tr>';
$('#RptMovie').append(eachrow);
cursor.continue();
}
$('#RptMovie').table('refresh');
}
$.mobile.loading("hide");
};
Just like checkStorage, this reads movies from the table in readonly mode. For each movie value read, we assign it to MovieRec variable and then generate each table row from this. This is appended to the table per record and generating this table as depicted below:
This HTML table can be exported to Excel by clicking the Export button at the header.
7. Export records to an Excel file from an HTML table.
This executes javascript to export the html table to excel. This uses the excellentexport.min.js file. Let's look at the definition of the report.
<div id="pgRptMovie" data-role="page">
<header id="pgRptMovieHdr" data-role="header" data-position="fixed" data-tap-toggle="false">
<h1>Movies Database 1.00</h1>
<a data-role="button" id="pgRptMovieBack" data-icon="carat-l" class="ui-btn-left">Back</a>
<div data-role="controlgroup" data-type="horizontal" class="ui-btn-right" style="margin-top:0;border-top:0;">
<a data-role="button" download="Movie.xls" onclick="return ExcellentExport.excel(this, 'RptMovie', 'Movie');" id="pgRptMovieExport" data-icon="exportexcel" href="#">Export</a>
<a data-role="button" id="pgRptMovieNew" data-icon="plus" data-theme="b" data-transition="slide" href="#pgAddMovie">New</a>
</div>
</header>
<div id="pgRptMovieCnt" data-role="content">
<table id="RptMovie" data-column-btn-text="Columns To Display" data-column-btn-theme="b" data-role="table" data-mode="columntoggle" data-column-popup-theme="a" class="ui-responsive table-stroke table-stripe ui-shadow">
<caption>Movies Report</caption>
<thead>
<tr class="ui-bar-a">
<th class="ui-bar-a ui-body-c">Movie Name</th>
<th data-priority="2" class="ui-bar-a ui-body-c">Movie Year</th>
<th data-priority="3" class="ui-bar-a ui-body-c">Movie Genre</th>
</tr>
</thead>
<tbody>
</tbody>
<tfoot>
<tr><td></td></tr>
<tr><td class='ui-body-c' colspan="3">Powered by JQM.Show - <a href="https://play.google.com/store/apps/details?id=com.b4a.JQMShow</td></tr></tfoot">https://play.google.com/store/apps/details?id=com.b4a.JQMShow</td></tr></tfoot</a>>
</table>
</div>
</div>
The two buttons in the header are created with a control-group when the onclick method fires when clicking the Export button to create the excel workbook.
<div data-role="controlgroup" data-type="horizontal" class="ui-btn-right" style="margin-top:0;border-top:0;">
<a data-role="button" download="Movie.xls" onclick="return ExcellentExport.excel(this, 'RptMovie', 'Movie');" id="pgRptMovieExport" data-icon="exportexcel" href="#">Export</a>
<a data-role="button" id="pgRptMovieNew" data-icon="plus" data-theme="b" data-transition="slide" href="#pgAddMovie">New</a>
</div>
Points of Interest
See Getting started with IndexedDB here in CodeProject here.