{"id":945,"date":"2013-08-15T18:15:21","date_gmt":"2013-08-15T12:45:21","guid":{"rendered":"http:\/\/ramkulkarni.com\/blog\/?p=945"},"modified":"2013-08-15T18:15:21","modified_gmt":"2013-08-15T12:45:21","slug":"pre-packaging-database-with-html5-mobile-application","status":"publish","type":"post","link":"http:\/\/ramkulkarni.com\/blog\/pre-packaging-database-with-html5-mobile-application\/","title":{"rendered":"Pre-packaging database with HTML5 Mobile Application"},"content":{"rendered":"

I was discussing with a couple of colleagues of mine yesterday about a HTML5 mobile application that we are developing, and one of the requirements was to pre-package database with the application. A few months back I had created an application that did just that. I thought the solution might be of interest to some of the readers of this blog.<\/p>\n

Mobile (or for that matter non-mobile) browsers can create databases for your HTML5 applications. HTML5 provides APIs to create and access the database. But how do you pre-package the database?<\/p>\n

The solution is \u00a0not really to pre-package the database, but package data that you want a database to initialize with. I don’t think you can really package a SQLite database and ask browser to use it for your HTML5 application. You need to create database and tables when the application is run the first time and then load data packaged with the application. Let’s say we want to create a table called ‘person’ and want to populate this table when the application is run the first time.
\nTo keep the table simple, we will create only three fields in the table – id, name and picture_file. We could create a blob field to hold pictures in the table, but that will unnecessarily duplicate the data, one that we pre-package and another we insert in the table. So we will just save the file name in the database. We will use JSON to specify initial data, because it would be easy to convert that data to JS objects.<\/span><\/p>\n

In the www folder of our mobile application, we will create following folders and files –
\n_data
\n|—-images
\n| \u00a0 \u00a0 \u00a0|—-person1.jpeg
\n| \u00a0 \u00a0 \u00a0|—-person2.jpeg
\n|—-json_data.js
\nindex.html
\napp.js<\/p>\n

Let’s say we want to initialize the table with two records. So we will create an array with two objects in json_data.js<\/p>\n

\n
[\n\t{\n\t\tid:1<\/span>,\n\t\tname:<\/span>\"Person1\"<\/span>,\n\t\tpicture_file:<\/span>\"person1.jpeg\"<\/span>\n\t},\n\t{\n\t\tid:2<\/span>,\n\t\tname:<\/span>\"Person2\"<\/span>,\n\t\tpicture_file:<\/span>\"person2.jpeg\"<\/span>\n\t}\n]<\/pre>\n<\/div>\n
 <\/p>\n
In the app.js we will write JS code to create and access database –<\/p>\n
\n
\/\/app.js<\/span>\ndb =<\/span> null<\/span>; \/\/Reference to open database<\/span>\ntableName =<\/span> \"persons\"<\/span>; \/\/Table name<\/span>\n_onDBInitializedCallback =<\/span> null<\/span>; \/\/Callback function after DB is initialized<\/span>\n\nfunction<\/span> openDB()\n{\n\tdb =<\/span> window<\/span>.openDatabase(\"persons\"<\/span>, \"1.0\"<\/span>, \"Persons DB\"<\/span>, 1000000<\/span>);\n\t\/\/We will use a flag in localStorage to check if database is already<\/span>\n\t\/\/populated with pre-packaged data.<\/span>\n\tif<\/span> (localStorage.tablesCreated ==<\/span> undefined<\/span> ||<\/span> localStorage.tablesCreated ==<\/span> \"false\"<\/span>)\n\t{\n\t\t\/\/Database is not initilized. Create tables and inserts initial records<\/span>\n\t\t\/\/specified in _data\/json_data.js<\/span>\n\t\tcreateTable();\n\t}\n\telse<\/span> if<\/span> (_onDBInitializedCallback !=<\/span> null<\/span>)\n\t{\n\t\t\/\/Person table is already created and initilized. <\/span>\n\t\t\/\/Call callback function.<\/span>\n\t\t_onDBInitializedCallback();\n\t}\n}\n\n\/\/Reads data from json_data.js and return aray of Person objects<\/span>\nfunction<\/span> getPackagedData()\n{\n\t\/\/Get data from json_data.js using XHR<\/span>\n\tvar<\/span> httpReq =<\/span> new<\/span> XMLHttpRequest();\n\thttpReq.open(\"GET\"<\/span>,\"_data\/json_data.js\"<\/span>,false<\/span>);\n\thttpReq.send();\n\t\/\/Convert string to JavaScript objects using eval<\/span>\n\treturn<\/span> eval<\/span>(httpReq.responseText);\n}\n\n\/\/Create Person table<\/span>\nfunction<\/span> createTable()\n{\n\tdb.transaction (function<\/span> (tx){\n\t\tvar<\/span> sql =<\/span> \"create table if not exists \"<\/span> +<\/span> tableName +<\/span>\n\t\t\t\" (id integer, name text, picture_file text)\"<\/span>;\n\t\ttx.executeSql(sql);\n\t},\n\tonError,\n\tinitializeTable);\n}\n\n\/\/Inserts records in Person table after reading <\/span>\n\/\/initial data from json_data.js<\/span>\nfunction<\/span> initializeTable()\n{\n\tvar<\/span> persons =<\/span> getPackagedData();\n\tinsertPerson(persons,0<\/span>);\n\tlocalStorage.tablesCreated =<\/span> \"true\"<\/span>;\n}\n\n\/\/Inserts person at given index from persons array<\/span>\nfunction<\/span> insertPerson (persons, index)\n{\n\t\/\/Checkif we have inserted all records<\/span>\n\tif<\/span> (index >=<\/span> persons.length)\n\t{\n\t\t\/\/Database is initialized. Call callback function<\/span>\n\t\tif<\/span> (_onDBInitializedCallback !=<\/span> null<\/span>)\n\t\t{\n\t\t\t_onDBInitializedCallback();\n\t\t\treturn<\/span>;\n\t\t}\n\t}\n\n\tvar<\/span> person =<\/span> persons[index];\n\n\tdb.transaction(function<\/span> (tx){\n\t\t\/\/Got transaction<\/span>\n\t\tvar<\/span> sql =<\/span> \"insert into \"<\/span> +<\/span> tableName +<\/span> \"(id,name,picture_file) values (?,?,?)\"<\/span>;\n\t\ttx.executeSql(sql,[person.id, person.name, person.picture_file]);\n\t},\n\tonError,\n\tfunction<\/span> () {\n\t\t\/\/insert next person in persons array<\/span>\n\t\tinsertPerson(persons,++<\/span>index);\n\t});\n}\n\n\/\/DB error handler<\/span>\nfunction<\/span> onError(err)\n{\n\tif<\/span> (typeof<\/span> err.message !=<\/span> 'undefined'<\/span>)\n\t\terr =<\/span> err.message;\n\talert(err);\n}\n\n\/\/Get all persons from Person table. <\/span>\n\/\/Calls callbackFunc with array of person object as argument<\/span>\nfunction<\/span> getPersons(callbackFunc)\n{\n\tdb.transaction(function<\/span> (tx){\n\t\tvar<\/span> sql =<\/span> \"select * from \"<\/span> +<\/span> tableName +<\/span> \" order by name\"<\/span>;\n\t\ttx.executeSql(sql,null<\/span>, function<\/span>(tx, result) {\n\t\t\tvar<\/span> persons =<\/span> new<\/span> Array<\/span>();\n\t\t\tvar<\/span> rows =<\/span> result.rows;\n\n\t\t\tfor<\/span> (var<\/span> i =<\/span> 0<\/span>; i <<\/span> rows.length; i++<\/span>)\n\t\t\t{\n\t\t\t\tvar<\/span> person =<\/span> new<\/span> Object<\/span>();\n\t\t\t\tperson.id =<\/span> rows.item(i).id;\n\t\t\t\tperson.name =<\/span> rows.item(i).name;\n\t\t\t\tperson.picture_file =<\/span> \"_data\/images\/\"<\/span> +<\/span> rows.item(i).picture_file;\n\t\t\t\tpersons.push(person);\n\t\t\t}\n\n\t\t\tif<\/span> (callbackFunc)\n\t\t\t\tcallbackFunc(persons);\n\t\t})\n\t},\n\tonError,\n\tnull<\/span>);\n}<\/pre>\n<\/div>\n
 <\/p>\n
To test DB functions we have written in app.js, we will create index.html, in which will open the database, wait till it is initialized and then call\u00a0getPersons function to fetch records from the database and then display images and names of persons in a table –<\/p>\n
\n
<!DOCTYPE html><\/span>\n<html><\/span>\n\t<head><\/span>\n\t\t<title><\/span>Example - Pre-packaged HTML5 DB Demo<\/title><\/span>\n\t\t<script <\/span>src=<\/span>\"app.js\"<\/span>><\/script><\/span>\n\n\t\t<script><\/span>\n\t\t\t\/\/Specify function to call after database is initialized<\/span>\n\t\t\t\/\/_onDBInitializedCallback is declared in app.js<\/span>\n\t\t\t_onDBInitializedCallback =<\/span> function<\/span> onDBInitialized() {\n\n\t\t\t\tgetPersons(function<\/span>(persons){\n\t\t\t\t\tvar<\/span> buf =<\/span> \"\"<\/span>;\n\t\t\t\t\tvar<\/span> tableElm =<\/span> document<\/span>.getElementById(\"personsTable\"<\/span>);\n\t\t\t\t\tfor<\/span> (var<\/span> i =<\/span> 0<\/span>; i <<\/span> persons.length; i++<\/span>)\n\t\t\t\t\t{\n\t\t\t\t\t\tvar<\/span> row =<\/span> \"<tr><td>\"<\/span> +<\/span>\n\t\t\t\t\t\t\t\"<img src='\"<\/span> +<\/span> persons[i].picture_file +<\/span> \"'><\/img><\/td>\"<\/span> +<\/span>\n\t\t\t\t\t\t\t\"<td>\"<\/span> +<\/span> persons[i].name +<\/span> \"<\/td><\/tr>\"<\/span>;\n\t\t\t\t\t\ttableElm.innerHTML +=<\/span> row;\n\t\t\t\t\t}\n\t\t\t\t});\n\t\t\t}\n\n\t\t\t\/\/Open the database<\/span>\n\t\t\topenDB();\n\t\t<\/script><\/span>\n\t<\/head><\/span>\n\t<body><\/span>\n\t\t<table<\/span> id=<\/span>\"personsTable\"<\/span>><\/span>\n\t\t<\/table><\/span>\n\t<\/body><\/span>\n<\/html><\/span><\/pre>\n<\/div>\n
 <\/p>\n
-Ram Kulkarni<\/p>\n","protected":false},"excerpt":{"rendered":"
I was discussing with a couple of colleagues of mine yesterday about a HTML5 mobile application that we are developing, and one of the requirements was to pre-package database with the application. A few months back I had created an application that did just that. I thought the solution might be of interest to some … <\/p>\n
Continue reading “Pre-packaging database with HTML5 Mobile Application”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"Pre-packaging database with HTML5 Mobile Application","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[10,78,1],"tags":[11,79,80],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2g9O8-ff","jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/ramkulkarni.com\/blog\/wp-json\/wp\/v2\/posts\/945"}],"collection":[{"href":"http:\/\/ramkulkarni.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/ramkulkarni.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/ramkulkarni.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/ramkulkarni.com\/blog\/wp-json\/wp\/v2\/comments?post=945"}],"version-history":[{"count":0,"href":"http:\/\/ramkulkarni.com\/blog\/wp-json\/wp\/v2\/posts\/945\/revisions"}],"wp:attachment":[{"href":"http:\/\/ramkulkarni.com\/blog\/wp-json\/wp\/v2\/media?parent=945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/ramkulkarni.com\/blog\/wp-json\/wp\/v2\/categories?post=945"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/ramkulkarni.com\/blog\/wp-json\/wp\/v2\/tags?post=945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}