Wednesday, December 28, 2011

On the Fourth Day of PhoneGapping: Creating a Database from a SQL Dump

On a slightly different track than Day 2 we are going to create a SQLiteDB from scratch using only JavaScript on startup of our application. In order to do this we are going to use the handy JavaScript library HTML5SQL.js by Ken Corbett Jr.

First we'll do a check to see if we've already created our database. If we haven't we'll do a XHR to get the SQL dump file containing all the statements we need to create and populate our tables. Finally, upon success we'll set a flag so this doesn't run every time we start our application.

and here is our sql file:


The html5sql.js lib makes working with the Web SQL specification much easier than hard coding it yourself. You owe it to yourself to look into this library as it will save you some time and hair.

17 comments:

Philip said...

Hi Simon
Thanks for sharing your experimenting with phonegap. I just installed it a few days myself and so I'll be following your progress. Actually, I'm probably only going to need phonegap for packaging my application. Most of the functionality I need I should be able to handle with Sencha Touch. Anyways, exciting times ahead!

Xander Dumaine said...

This works great for reading a single file, but I need to get a listing of the files in a directory in the assets folder. Is there a way to get a directory listing of a folder in the assets with XHR?

Creating a text file manually before hand and reading that doesn't solve my problem, because if that's what I must do, I may as well not use XHR at all, and list the files in the source.

The purpose of this (for context) is to be able to drop some images in the folder, and the javascript can create an image element for each one, without altering the source.

Simon MacDonald said...

@Xander

Currently there is no way to get a listing of the asset directory in PhoneGap short of creating a plugin to do it. It would be a useful plugin and not to difficult. If you want to take it on I'll do coaching.

Mobile Magic said...

Hi Simon, thanks for this code, i used it but i get a problem at process method as given below.

html5sql.process(res, sucCall, failC);
there is no data in .db file .
plz help me.

Simon MacDonald said...

@Mobile Magic

Well it is hard to tell without seeing your code but when you are calling process with "res" is that "responseText"?

Mobile Magic said...

Hi Simon
I got the problem. It was my mistake, there were some html tag remaining in .sql file.

Thanks again

Sergi Kolesnik said...

@Xander,

Since you 'drop' images beforehand, you surely know their names. So you just create a csv file (or anyfile for that matter) that will list all names you need to read.

If you are downloading images dynamically over net connection, that save them to permanent storage. And from there you can request what files are stored in the directory.

So, is plugin to read files in the asset directory really needed? Because, once again, you know beforehand what files are listed there.

r said...

hello.

is there any way to got it work with ios?

Simon MacDonald said...

@r

I can't see why this wouldn't work in iOS.

Anonymous said...

Thanks to Simon and Ken.

Question: anyone able to run this in Chrome without getting a security error?

SECURITY_ERR: DOM Exception 18

The error is happening when I attempt to get the sql file.

request.open("GET", "mydb.sql", true);

The sql is in the same location as index, and I see the error if I use the full path "file:///users/me/myfolder/mydb.sql"

I've run Chrome with a flag to allow-file-access-from-files.

Also in my mobileinit function I have:
$.support.cors = true;
$.mobile.allowCrossDomainPages = true;

Sure would be nice to debug this thing in Chrome!

Simon MacDonald said...

@Annette

You need to set:

-disable-web-security

as well.

http://joshuamcginnis.com/2011/02/28/how-to-disable-same-origin-policy-in-chrome/

Unknown said...

Hi simon,
I have developed android app using phonegap. I have used sqllite and local storage in that app. The application gets closed suddenly while using. I am facing this problem in android device only not in iPhone.

Used phonegap version 2.3.0

Tested android device 4.0.4

I am getting this error in logcat

02-19 15:41:31.795: E/dalvikvm(20055): JNI ERROR (app bug): local reference table overflow (max=512)
02-19 15:41:31.795: W/dalvikvm(20055): JNI local reference table (0x12bada0) dump:
02-19 15:41:31.795: W/dalvikvm(20055): Last 10 entries (of 512):
02-19 15:41:31.795: W/dalvikvm(20055): 511: 0x2bb95f70 android.content.res.AssetManager
02-19 15:41:31.795: W/dalvikvm(20055): 510: 0x2ce0b078 byte
02-19 15:41:31.795: W/dalvikvm(20055): 509: 0x2ce03060 byte
02-19 15:41:31.795: W/dalvikvm(20055): 508: 0x2cdfb048 byte
02-19 15:41:31.795: W/dalvikvm(20055): 507: 0x2cdf3030 byte
02-19 15:41:31.795: W/dalvikvm(20055): 506: 0x2cdeb018 byte
02-19 15:41:31.795: W/dalvikvm(20055): 505: 0x2cde3000 byte
02-19 15:41:31.795: W/dalvikvm(20055): 504: 0x2cddafe8 byte
02-19 15:41:31.795: W/dalvikvm(20055): 503: 0x2cdd2fd0 byte
02-19 15:41:31.795: W/dalvikvm(20055): 502: 0x2cdcafb8 byte
02-19 15:41:31.795: W/dalvikvm(20055): Summary:
02-19 15:41:31.795: W/dalvikvm(20055): 1 of java.lang.Class
02-19 15:41:31.795: W/dalvikvm(20055): 510 of byte (510 unique instances)
02-19 15:41:31.795: W/dalvikvm(20055): 1 of android.content.res.AssetManager
02-19 15:41:31.795: E/dalvikvm(20055): Failed adding to JNI local ref table (has 512 entries)
02-19 15:41:31.795: I/dalvikvm(20055): "Thread-2487" prio=5 tid=16 RUNNABLE
02-19 15:41:31.795: I/dalvikvm(20055): | group="main" sCount=0 dsCount=0 obj=0x2bbe6160 self=0x1c34b0
02-19 15:41:31.795: I/dalvikvm(20055): | sysTid=20078 nice=0 sched=0/0 cgrp=[fopen-error:2] handle=2394304
02-19 15:41:31.795: I/dalvikvm(20055): | schedstat=( 0 0 0 ) utm=109 stm=59 core=0
02-19 15:41:31.795: I/dalvikvm(20055): at android.content.res.AssetManager.readAsset(Native Method)
02-19 15:41:31.795: I/dalvikvm(20055): at android.content.res.AssetManager.access$700(AssetManager.java:35)
02-19 15:41:31.795: I/dalvikvm(20055): at android.content.res.AssetManager$AssetInputStream.read(AssetManager.java:573)
02-19 15:41:31.795: I/dalvikvm(20055): at dalvik.system.NativeStart.run(Native Method)
02-19 15:41:31.795: E/dalvikvm(20055): VM aborting
02-19 15:41:31.795: A/libc(20055): Fatal signal 11 (SIGSEGV) at 0xdeadd00d (code=1)

Thanks

Simon MacDonald said...

@niranjana devi

What is in your assets folder? This is a signal 11 problem coming from the OS. What is in assets that it is trying to read?

Unknown said...

Hi Simon,

I am having normal html,js, and css files in my assets. Normally i m getting this error when my app size goes beyond 50 mb. I searched in google about this.. i found few links like

http://stackoverflow.com/questions/12803309/android-webview-asset-reference-memory-leak

http://stackoverflow.com/questions/9278149/android-webview-memory-leak-when-using-assets

that says to load html file from internal or external storage instead of assets. But i don't know this is correct way or not.

Do you have any idea to resolve this? Is there any way to clear cache?

Thanks,

Simon MacDonald said...

@niranjana devi

50mb is the usual maximum file size for an apk. You will probably need to use an expansion file.

https://developer.android.com/google/play/expansion-files.html

lse123 said...

I got fail when run localhost on desktop... well required mobile device? phonegap library must also included? With other way can run on desktop but without phonegap.js and html5sql.js?

Simon MacDonald said...

@lse123

Yes, you can do this from a desktop but the xhr call is probably failing for you with a CORS issue. Look at http://html5sql.com/ for more info on the library.