Pre populating database in phonegap app

Currently I am working on developing mobile app using Phonegap. Phonegap comes up with very nice javascript methods to access phone specific api and the developer like me who have fair knowledge of javascript HTML and CSS can learn it very quickly.

My app required bundling the database into the app but there is no straight forward way in Phonegap to do this but I found a way to do this.
I added “app.sql” file in my “www” folder and get the text of this file. My app.sql looks something like this

[java]
CREATE TABLE ‘category’ (categoryId long NOT NULL, name varchar(255) NOT NULL);\n
INSERT INTO ‘category’ VALUES(1,’cat_1′);\n
INSERT INTO ‘category’ VALUES(2,’cat_2′);\n
INSERT INTO ‘category’ VALUES(3,’cat_3′);\n
INSERT INTO ‘category’ VALUES(4,’cat_4′);\n
[/java]

I am using jquery in my example

[java]
function runInitialSetup() {
var filePath = "app.sql";
$.get(filePath, function (response) {
var statements = response.split(‘\n’);

var shortName = "database";
var version = ‘1.0’;
var displayName = ‘App Database’;
var maxSize = 200000; // bytes
db = openDatabase(shortName, version, displayName, maxSize);

db.transaction(function (transaction) {
jQuery.each(statements, function (index, value) {
if (value != ”) {
transaction.executeSql(value, [], successHandler, function (e) {
console.log("Error executing sql " + value)
});
}
});
});
});
}
[/java]

I executed above method whenever the app is installed first time.

Lets see whats happening in the above example. I read my app.sql file which was bundled in the app and the response I got is the text, so I split the content on newline character. There is the assumption that each sql statement is separated with newline character and the content in sql do not have the newline character.

Then I made the database connection and executed each statement to setup the database.

You can read any text file bundled in your app build. You can always read the content from your bundled app but never think of writing files because its not possible.

Hope it helps

Uday Pratap Singh
uday@intelligrape.com