PDA

View Full Version : Storing JSON data to WebSQL (SQLite DB)



displaced
16 Oct 2011, 3:21 PM
Hi,

I'm building part of my Sencha-based app where data requested from a service needs to be stored in a local (WebSQL) database.

So far I've successfully used the standard json store type to pull data from my service. This works fine. Also, I've written a test app to give tomalex0's SqliteProxy store implementation a try. That also works great.

But my question is: what's the most efficient way to get the JSON data into the Sqlite database? Do I create an instance of both stores and copy objects from one to the other, one-by-one? Or is there a neater way to do it? Is there an easy way to maintain two identical store definitions for each entity -- one for Sqlite and one for the Json data?

I'm also thinking about how to handle updates and deletions (e.g. sqldb contains rowID's 1,2,3,4 and the json data contains rowID's 2,3,4,5 -- so I'd need to delete row 1, and update/create rows 2,3,4 and 5 in the sqlite table from the json data.

I'm still pretty new to Sencha (if you haven't gathered already!), but am really enjoying it.

Any help/advice greatly appreciated.

Cheers,
Chris

displaced
17 Oct 2011, 3:12 AM
Hi

Apologies - this should probably have been posted in the 1.x forum! Would an admin be able to move this thread for me?

Thanks,
Chris

kortovos
17 Oct 2011, 5:56 AM
Why not merge the two stores? I have something similar. My tables in the db, have three columns, 2 identifiers and one for the json data (serialized). Data is read from the db into the store, in the store the records are altered/updated/removed and the result is written to the db.

displaced
17 Oct 2011, 8:42 AM
Thanks for the suggestion!

Like I say, I'm something of a newbie at the moment, but I'll have a little experiment to see how I get on.

It's quite a data-intensive app, with 4 or 5 tables (models) each with about 10-15 columns. The idea is that these tables are populated or updated via data received from a web service call which replies with Json. This will occur only at the point of logging in to the app. After that point, the data's manipulated on the SQL database (via the SQLite store implementation I linked to.

Once this is in place, I'm hoping to turn this into an offline-enabled application which runs in an 'occasionally connected' manner.

Cheers,
Chris

kortovos
17 Oct 2011, 10:22 PM
I will try to give you some good code examples, as soon as I have some time to document some.

For the moment I suggest you read this tutorial about versioning your db, since the versioning of browsers (any browser) is pretty much broken.

http://blog.maxaller.name/2010/03/html5-web-sql-database-intro-to-versioning-and-migrations/

displaced
18 Oct 2011, 1:35 AM
Wow - thanks kortovos, that'd be fantastic!

I'm reading through that link now.

Cheers,
Chris

kortovos
18 Oct 2011, 5:49 AM
Ext.claims.data.Store = Ext.extend(Ext.data.Store,{
constructor: function(config){
this.type = 'CLAIMS',
this.autoload = false;
this.batchUpdateMode = 'complete';
this.autosave = false;
this.masking = true;

this.getIndex = function(record){
var oid = record.get('orderId');
var fleetowner = record.get('fleetowner');

var currentIndex = -1;

if(oid != null && fleetowner != null){
var records = null;

if(this.snapshot != undefined && this.snapshot != null){
records = this.snapshot.items;
}else{
records = this.data.items;
}

for(var i = 0; i < records.length; i++){
var rec = records[i];

if(rec.get('orderId') == oid && rec.get('fleetowner') == fleetowner && rec.get('index') != null){
if(rec.get('index') > currentIndex){
currentIndex = rec.get('index');
}
}
}
}
return (currentIndex +1);
};

this.synching = function(callback){
this.fireEvent('save', callback);
},

Ext.apply(this, config);
this.addEvents({
"save":true,
"beforeload": true,
"add": true
});

this.on('save', this.onSave);
this.on('beforeload', this.onBeforeLoad);
this.on('add', this.onAdd);
this.showAll = function(){
this.each(function(rec){
console.log(rec);
});
};

Ext.claims.data.Store.superclass.constructor.call(this, config);
},
onBeforeLoad: function(store, operation){
var masked = Ext.getBody().isMasked();
if(masked == false && store.masking == true){
Ext.getBody().mask('<div class="application-loading ">loading</div>');
}

var orderId = operation.orderId;
var fleetowner = operation.fleetowner;
var callback = operation.callback;

database.Main.getData(this.table, orderId, fleetowner, function(records, success){
var results = new Array();
for(var i = 0; i < records.length; i++){
var rec = records[i];
results[results.length] = Ext.ModelMgr.create(rec, store.model.modelName);
}

if(masked == false && store.masking == true){
Ext.getBody().unmask();
}

if(success){
store.loading = true;
store.insert(store.data.length, results);
store.loading = false;

for(var i = 0; i < results.length; i++){
var rec = results[i];
rec.phantom = false;
}
}

if(callback){
callback(results, store, success, operation);
}
});

return false;
},
onSave:function(callback){
var store = this;
var masked = Ext.getBody().isMasked();

if(masked == false && store.masking == true){
Ext.getBody().mask('<div class="application-loading ">loading</div>');
}

var newRecords = store.getNewRecords();

for(var i = 0; i < newRecords.length; i++){
var rec = newRecords[i];

if(rec.get('index') == null){
rec.set('index', store.getIndex(rec));
}
}

var updateRecords = store.getUpdatedRecords();
var removedRecords = new Array();
var array = store.getRemovedRecords();
for(var i = 0; i < array.length; i++){
removedRecords[removedRecords.length] = array[i].copy();
}

for(var i = 0; i < newRecords.length; i++){
var newRec = newRecords[i];
for(var i2 = 0; i2 < updateRecords.length; i2++){
var updateRec = updateRecords[i2];
if(newRec == updateRec){
updateRecords.splice(i2, 1);
break;
}
}
}

if(newRecords.length > 0 || removedRecords.length > 0 || updateRecords.length > 0){
store.removed = [];

for(var i = 0; i < newRecords.length; i++){
var rec = newRecords[i];
rec.dirty = false;
rec.phantom = false;
rec.needsAdd = false;
}

for(var i = 0; i < updateRecords.length; i++){
var rec = updateRecords[i];
rec.dirty = false;
}

database.Main.processAllData(newRecords, updateRecords, removedRecords, function(success){
if(success == false){
for(var i = 0; i < newRecords.length; i++){
var rec = newRecords[i];
rec.dirty = true;
rec.needsAdd = false;
}

for(var i = 0; i < updateRecords.length; i++){
var rec = updateRecords[i];
rec.dirty = true;
}

for(var i = 0; i < removedRecords.length; i++){
var rec = removedRecords[i];
store.getRemovedRecords().push(rec);
}

store.removed = [];
}

if(callback){
callback(success, store);
}

if(masked == false && store.masking == true){
Ext.getBody().unmask();
}

}, this.table);
}else{
if(callback){
callback(true, store);
}

if(masked == false && store.masking == true){
Ext.getBody().unmask();
}
}
},

onAdd:function(store, records, index){
for(var i = 0; i < records.length; i++){
var rec = records[i];
if(rec.get('index') == null){
rec.set('index', this.getIndex(rec));
}
}
}
});

Ext.reg('claimsStore', Ext.claims.data.Store);

var store = new Ext.claims.data.Store({
model : 'Model',
table:'TABLE_NAME',
name:'Store'
});


This is an example of the store I use in combination with the DB (database.Main). The store has a combination of two attributes (orderId & fleetowner) as primary key. The difficult part of linking a db and a store, is the fact that you won't know which id the db will assign to the records (or you could request is everytime a new record is inserted). So I assigned an index to every record. The combination orderId, fleetowner and index is always unique.

The method database.Main.processAllData, will make queries for all inserts, updates and deletes, and then run it.

Where you normally do 'store.synch()', here you do 'store.synching(function(){'code execute after query has run'});'.

This is by no means 'elegant' or efficient code, but it should give you a good startingpoint.