SQLite, initial load and future updates

[Erich93063]

I am creating an Android app using PhoneGap and jQuery mobile. My app
will need a SQLite database to house some data. Nothing crazy, but
enough to where I need SQLite. Now, I plan on initially populating the
database with the data if the database doesn't exist yet on the
initial load of the app. When they load the app for the first time, I
will do a remote web call and get the initial data and populate the
local SQLite dB (or perhaps hard code the initial seed data as part of
the actual code of the app). Then, as time goes on, I will have a
remote database that I will update manually with new data. I would
like to have my Android app do a check and see if the users database
is an older version, my app will do a remote web call and get a JSON
or maybe an XML response and recreate the database on the users device
with the updated data. I initially just thought of having my app ONLY
do remote calls to my database to get the data, but I want the user to
be able to have access to the data locally, in case they do not have
service, they can still use the app, hence the need for the SQLite
database.

Has anyone done this before? What would be the best way to handle this
whole scenario? Things I would like to do:

1) When the app starts for the very first time, I will need the
database to be created and the seed data inserted
2) If they start the app in the future, I do NOT want the database to
be created and I want to use the data in the local SQLite database
3) If I update my remote data, I need a way for the user to get this
latest data via the web ONCE (JSON?, XML?), and populate the local
SQLite database so the next time they open the app, they will have the
data locally

Any help is GREATLY appreciated.

[Nick McCloud]

On Mar 21, 9:30 pm, Erich93063 <erich93...@gmail.com> wrote:
> 1) When the app starts for the very first time, I will need the
> database to be created and the seed data inserted

Check to see if a settings record in a settings table exists - if not,
create all tables.

> 2) If they start the app in the future, I do NOT want the database to
> be created and I want to use the data in the local SQLite database

Check to see if a settings record in a settings table exists - if so,
use tables.

> 3) If I update my remote data, I need a way for the user to get this
> latest data via the web ONCE (JSON?, XML?), and populate the local
> SQLite database so the next time they open the app, they will have the
> data locally

Check to see if a settings record in a settings table has the latest
version number of the data stored - if not, request data.

You can do JSON but for many records it is quite bulky - CSV less so.

I loop round getting batches of data at a time rather than trying to
get all 10 squillion rows in one web request.

[KenCorbettJr]

I actually thing a better approach would be using the version already
build into the Web Database like is described in this blog post:
http://blog.maxaller.name/2010/03/html5-web-sql-database-intro-to-versioning-and-migrations/

He created a github project which has a migrations script which might
be useful but I have also built this version control into a light
javascript library I wrote called html5sql (https://github.com/
KenCorbettJr/html5sql). Using this javascript library you can easily
process all of the statements you need to set up your tables in
sequence all the while controlling the version of your database.

Basically, using html5sql you would open your database by calling
something like this:

html5sql.openDatabase("com.yourDatabase.name", "Your Database",
200000);

You notice that unlike the native openDatabase function you do not
pass it a version parameter. This is because the script automatically
opens a new database with an empty version, or your existing database
with whatever version it happens to be.

You would then initially handle versioning by doing something like
this:

$.get("http://www.yourserver.com/currentVersion.php",
function(serverVersion){
if(html5sql.database.version != serverVersion){
$.get("http://www.yourserver.com/databaseSetupSQL.sql",
function(sql){

html5sql.changeVersion(html5sql.database.version,serverVersion,
sql,
function(){
//Success Callback after each and every SQL
//Statement has been processed in sequential
order
},
function(error, statement){
//An error callback if any of your statements
have
//an error. Encountering an error rolls back any
//any transactions that were completed before the
error.
}
);
}
);
}
}
);

I hope the code is clear but basically you would open the database,
query your server for the version of the database on the server, if it
didn't match you would retrieve a set of sql statements which would
drop and then rebuild the client's database based on the server's
instruction. The beauty of the html5sql javascript library is the sql
you retrieve would be processed in the sequence they were listed.

Hopefully this helps.

Ken Corbett