PDA

View Full Version : SqliteProxy



tomalex0
8 Sep 2011, 12:51 AM
I used to work with sqlite storage for while but didn't thought to write down a separate proxy until i came across http://www.sencha.com/forum/showthread.php?139095-IndexedDB-Proxy-Ext.data.proxy.IndexedDB by grgur (http://www.sencha.com/forum/member.php?9339-grgur) . So heavily depending on his code set and help from Lisimba I tried to write down another sqliteproxy for sencha touch. This is just in initial stage.

I write down the proxy based on implementing a Demo Functionality and it works fine for it. Usecase of others will be different. So please have a look and let me know your feed back.

You can find code and demo implementation under
(https://github.com/tomalex0/SenchaTouch-SqliteProxy)SqliteProxy-Github (https://github.com/tomalex0/SenchaTouch-SqliteProxy)

Note : Sencha guys already planned for this (http://www.sencha.com/forum/showthread.php?140662-Phonegap-Sqlite-Store-Proxy).

temolesto
19 Sep 2011, 12:50 AM
Thanks a lot, i'm just starting with Sencha and this is the first thing that i was looking into.

tomalex0
19 Sep 2011, 1:07 AM
Nice to know that this turned out to be helpful for you. If you come across any issues please let me know

temolesto
20 Sep 2011, 1:24 PM
how can i change the Proxy.dbConfig.dbQuery of a sqliteproxy after definition of the store or model

i want to press a button and change the criteria of the selected rows of the table, i have tried this when updating


var p = myStore.getProxy();
p.queryDB(p.dbConfig.dbConn, 'Update table set column=300;');
successfully executing the update query or select some data

function querySuccess(tx, results) {
document.getElementById('total').innerHTML=results.rows.item(0).Num+' Total';
}
var p = this.store.getProxy();
p.queryDB(p.dbConfig.dbConn, 'select count(*) as Num FROM table;',querySuccess);


but now i want to change the dbquery of the storage and i cant find how to do this

var p = this.store.getProxy();
p.dbconfig.dbQuery = 'Select * from table order by column desc Limit 30';


im just starting with sencha and js in general, thanks for your help

tomalex0
20 Sep 2011, 6:33 PM
Can you try



this.store.load();


after setting the dbQuery

temolesto
20 Sep 2011, 11:19 PM
thanks, my error was actually dbconfig should be dbConfig :">


var p = this.store.getProxy();
p.dbConfig.dbQuery = 'Select * from table order by column desc Limit 30';
this.store.load();

thanks for your help

karthiktheraja
24 Sep 2011, 1:50 PM
Hi I am new to sencha touch and I want to use sqlite for offline data store . I want know whether there is performance difference between Phonegap sqlite and sqliteproxy.Does sqliteproxy supports iphone , android and Blackberry . Can you please tell me whether there is any memory restriction . Kindly help me to proceed further ....

tomalex0
25 Sep 2011, 11:14 PM
@karthik

I haven't tried it in Phonegap,
Regarding support, sqlite Db is supported in all webkit based browsers. So sqlite proxy meant to work with those .
I think developement on sqlite in browser has been dropped by w3c and likely to be replaced with indexDB.
http://www.w3.org/TR/webdatabase/
(http://www.w3.org/TR/webdatabase/)
By default device will use 5MB of space, if it tends to exceed it will prompt an option to increase the space.

I'm also not sure about how phonegap works with DB that we creates using html5 websql?

karthiktheraja
3 Oct 2011, 3:56 AM
Thanks for your reply . I followed your sample and It works great . But I am not able to understand the flow of your code. I am just doing a login form validation . I need to create a login table and need to insert ,select and delete user account. In your example you have commented out select query but still i get a log for select query. Please give me a short explanation of how to work with your code. I want know how your code is working with browser sqlite as in phoneGap I used window.openDatabase("Database (http://docs.phonegap.com/en/1.1.0/phonegap_storage_storage.md.html#Database)","1.0","PhoneGap Demo", 200000); method to open database.

tomalex0
4 Oct 2011, 3:23 AM
Hi,
The way proxy read is done like that it takes either tablename or query. If you have specified query it will be executed else select all from tablename.
So in both case there will be a select query executed when store load in initiated.

When any action is carried out with model having sqlite proxy it will create a table with fields mentioned in model. The inner function already checks for if table already exists.

Lookout the property isTableField.

I have also made a small change in github code.

tomalex0
19 Oct 2011, 1:54 AM
Updated sqlitproxy compatibale with ST2 class structure.

https://github.com/tomalex0/SenchaTouch-v2-SqliteProxy

Salva
20 Oct 2011, 7:36 AM
@tomalex

Thanks for sharing your plugin. I've just started to play with it, and it seems to mostly work, but I've found two details that I thought I should mention.

1. In the updateRecord function, you have this code:


var me = this,
id = record.internalId,
key = primarykey,
modifiedData = record.modified,

Shouldn't it be:

var me = this,
id = record.get(primarykey),
key = primarykey,
modifiedData = record.modified,

Otherwise, in the end you are generating an SQL sentence that tries to compare the 'id' property of the record with an internal Sencha value, not with the value for the 'id' field of the record that we want to modify.

2. It's a minor detail, but I've changed (on setRecord):

onSuccess = function (tx, rs) {
var returnrecord = record,
insertId = rs.insertId,
json = '{' + primarykey + ' : ' + insertId + '}'; // notice no quotes
returnrecord.set(eval("(" + json + ")"));
returnrecord.internalId = rs.insertId;
},
you originally included rs.InsertId in the text that gets evaluated, but it caused problems when minifying the js file (because you are including the name of the variable, that can be modified by the minifier, in plain text).

Regards,
Salva.

tomalex0
26 Oct 2011, 9:15 PM
@salva

Thanks for pointing out those issues.

The issue you pointed out in updateRecord, did you come across this issue?

if you look into setRecord function



returnrecord.internalId = rs.insertId;

this is meant to set the insert id from table to sencha internal id. I thought this should fix the issue that you have pointed out.


Regarding issue with minifying the variable , i was actually not aware of those :).

tomalex0
26 Oct 2011, 9:42 PM
@salva

i think record.get(primarykey) , will be better option. I will update my github.

and also get rid of those eval implementation :).

ultimate5289
1 Nov 2011, 12:41 AM
@tomalex0

I use your SQLite proxy for PhoneGap database but i don't know how to config it for PhoneGap database .Can you give me a detail example to get your proxy work fine with below database:

var db;
function connectDB() {
db = window.openDatabase("SpotLife_Spot", "1.0", "Spot Database", 5000000);
}


function populateDB(tx) {
tx.executeSql("CREATE TABLE IF NOT EXISTS CAPTUREDSPOT (SpotId unique, Title, Category, Latitude, Longitude, Owner, ImgPath, Tip, DetailAddress)");
}


function errorPopulateDB(err){
navigator.notification.alert("Error processing SQL: " + err.toString());
}


function createDB(){
connectDB();
db.transaction(populateDB, errorPopulateDB);
}

Sorry i have just jumped into Sencha Touch and so confuse on make a custom proxy with sencha touch.

tomalex0
1 Nov 2011, 1:09 AM
Try this
Db Connection


var dbconnval = {
dbName: "SpotLife_Spot",
dbDescription: "Spot Database"
};


Ext.DbConnection = new Ext.Sqlite.Connection(dbconnval);



Table Model which creates Table


Ext.regModel('CAPTUREDSPOT', {
fields: [{
name: 'SpotId',
type: 'int',
fieldOption: 'UNIQUE'
}, {
name: 'Title',
type: 'string'
}, {
name: 'Category',
type: 'string'
}, {
name: 'Latitude',
type: 'string'


}, {
name: 'Longitude',
type: 'string'


}, {
name: 'Owner',
type: 'string'


}, {
name: 'ImgPath',
type: 'string'


}, {
name: 'Tip',
type: 'string'


}, {
name: 'DetailAddress',
type: 'string'
}],
proxy: {
type: 'sqlitestorage',
dbConfig: {
tablename: 'CAPTUREDSPOT',
dbConn: Ext.DbConnection.dbConn,
},
reader: {
idProperty: 'ID'
}
},
writer: {
type: 'json'
}
});

CAPTUREDSPOTStore = new Ext.data.Store({
autoLoad: true,
model: 'CAPTUREDSPOT'
});

tomalex0
1 Nov 2011, 1:12 AM
If you are checking in Chrome try this in sqlite console.


SELECT sql FROM sqlite_master WHERE type='table' and name='CAPTUREDSPOT';

ultimate5289
1 Nov 2011, 2:14 AM
Thank for your support :D. I just don't understand why you override destroy on model and when the destroy function on proxy work .


if(e.getTarget(".delete")){
var rec = view.store.getAt(index);
var user = Ext.ModelMgr.create(rec.data, 'Contacts');
user.destroy();
view.store.remove(rec);
}

The destroy function of proxy just only work on model not on store ? If i use "view.store.remove(rec) ; view.store.sync(); ",it still work ? I just get it from this http://edspencer.net/2011/02/proxies-extjs-4.html .

tomalex0
1 Nov 2011, 2:44 AM
destroy method was missing from STversion 1.1.0, I think it is fixed in v1.1.1. This is needed when we are trying to delete model instance. I haven't think about the sync function after store record is removed.
Thanks for pointing out :).

bastard
2 Nov 2011, 12:49 AM
I keep getting this error in Chrome console: Uncaught TypeError: Cannot read property 'dbConn' of undefined. The demo provided on github runs file. I'm not even doing anything with the model. And I have quadruple checked to see if all the files are included in index.html.

Any ideas? Is there a problem with doing this in an MVC application?

myModel.js . I keep getting the error at the line "dbConn: Ext.DbConnection.dbConn"


Ext.regModel('sqliteModel', {
fields: [
{
name: 'firstname',
type: 'string'
},
{
name: 'lastname',
type: 'string'
},
{
name : 'ID',
type : 'int',
fieldOption: 'UNIQUE'
},
{
name : 'modifyDate',
type : 'string'
}
],

proxy: {
type: 'sqlitestorage',
dbConfig: {
tablename: 'contactsTable',
dbConn: Ext.DbConnection.dbConn, <-------- ERROR HERE!!!!
},
reader: {
idProperty: 'ID'
}
},
writer: {
type: 'json'
}

});

BT.stores.InputStore = new Ext.data.Store({
autoLoad:true,
model : 'sqliteModel',

});



Here is my app.js incase you need it -->




Ext.regApplication({
name : "BT",
icon: "resources/images/icons/icon.png",
glossOnIcon : false,
tabletStartupScreen: 'resources/images/startup/tabletStartupScreen_768x1004_v1.png',
phoneStartupScreen: 'resources/images/startup/phoneStartupScreen320x460_v1a.png',
launch: function() {
Ext.ns('DbConnection');

var dbconnval = {dbName : "btDB", dbDescription: "the Bt database"};

Ext.DbConnection = new Ext.Sqlite.Connection(dbconnval);

BT.views.Viewport= new BT.views.Viewport({
fullscreen: true,
});
}
});

tomalex0
2 Nov 2011, 1:47 AM
It looks likes stores and models are defined before db connection. Can you define dbconnection outside

regApplication before store and model.

disy_schmidt
25 Nov 2011, 3:46 AM
@ tomalex(): I would also like to use your SqliteProxy in my app based on PhoneGap and Sencha Touch. Which license is your SqliteProxy? My app I create will be eventually a commercial product some day. That's why I'm asking.
Thanks for your answer!

tomalex0
25 Nov 2011, 4:00 AM
@ tomalex(): I would also like to use your SqliteProxy in my app based on PhoneGap and Sencha Touch. Which license is your SqliteProxy? My app I create will be eventually a commercial product some day. That's why I'm asking.
Thanks for your answer!

Actually i haven't took time to understand those license :D . You are free to use this, it will be always open.

disy_schmidt
25 Nov 2011, 4:01 AM
@tomalex() That's really great! Thank you!

diesalher
12 Dec 2011, 12:52 AM
Thanks! I was exactly looking for this.

I'm gonna try your plugin in my app.

goeast
11 Jan 2012, 8:21 AM
Hi,

great Plugin tomalex0.

Is there a way to put data from a webserver (ajax) into the sqlite database on first startup of the app?
After, i want to use the app offline and check only for updates when the phone is online.

Thank you for your response.
Regards, Adrian

eryx
27 Jan 2012, 11:58 AM
Hey there really appreciate your work. I'm using your code for some time and it really helps me.
At the moment I try to create a table where to fields together build an Unique key


CREATE TABLE test(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Content TEXT, groupid INTEGER, leaguecode INTEGER, leaguetext TEXT, leaguetype TEXT, wertungsmodus TEXT, UNIQUE (groupid, leaguecode) ON CONFLICT REPLACE);

This is my sql to create the table but I don't know how I can do this like it is intended with an sqlitestorage.



var dbconnval = {
dbName: "app24",
dbDescription: "app24_db",
};
Ext.DbConnection = new Ext.Sqlite.Connection(dbconnval);

Ext.regModel('othergroups', {
fields: [
{
name: 'id',
type: 'int',
fieldOption: 'NOT NULL PRIMARY KEY AUTOINCREMENT'
}, {
name: 'content',
type: 'string'
}, {
name: 'groupid',
type: 'int'
}, {
name: 'leaguecode',
type: 'int'
}, {
name: 'leaguetext',
type: 'string'
}, {
name: 'leaguetype',
type: 'string'
}, {
name: 'wertungsmodus',
type: 'string'
}
],
proxy: {
type: 'sqlitestorage',
dbConfig: {
tablename: 'othergroups',
dbConn: Ext.DbConnection.dbConn
},
reader: {
idProperty: 'ID'
}
},
writer: {
type: 'json'
}
});

Normally I build it like the one above and I know I can set fieldOption to 'UNIQUE'. This works perfectly. But this time the field groupdid and leaguecode build together a UNIQUE key.

I hope you can help me point me to the right point. Thanks for any help

tomalex0
27 Jan 2012, 7:26 PM
@goeast
Sorry for late reply, i also thought to implement a feature like that. I will update you once it is finished.

@eryx

I haven't came across situation like this


UNIQUE (groupid, leaguecode) ON CONFLICT REPLACE :). Probably we need to change the way query is build in create section. Let me check and will let you

eryx
29 Jan 2012, 9:16 AM
Thanks that would be amazing if you could implement something like that.

Greetings Eryx

tomalex0
30 Jan 2012, 8:24 AM
Hi,

For time being can you try something like below mentioned field options and it is based on query that you have shown, its not a proper way but will do the trick.



fields: [{
name: 'ID',
type: 'int',
fieldOption: 'NOT NULL PRIMARY KEY AUTOINCREMENT'
},{
name: 'Content',
type: 'string'
}, {
name: 'groupid',
type: 'int'
},{
name: 'leaguecode',
type: 'int'
},{
name : 'leaguetext',
type: 'string'
},{
name : 'wertungsmodus',
type : 'string',
fieldOption : ',UNIQUE (groupid, leaguecode) ON CONFLICT REPLACE'
}]

eryx
30 Jan 2012, 9:20 AM
Hey once again.

You're absolutely right this helps me very much for now. I almost did the same like the one you mentioned. But I should have looked at he query. Of course you must define this on the last property else it will not work :D

Gretings Eryx

ahaeusse
8 Feb 2012, 1:03 PM
Hi tomalex,

thx for sharing this nice plugin. It was exactly what i was looking for.

I am stuck with a little problem here (just started whith sencha touch).
I have a json file containing data i want to import into my db when the app is started for the first time.
The json is an export from a database and looks similar to this structure:



{"21":
{ "firstname":"Alex",
"lastname":"Testuser",
"age":"43"
},
"22":
{ "firstname":"Andy",
"lastname":"Testuser2",
"age":"23"
},
<here come 200 more records>
}


I created the database and the table using your example (worked perfect).

Do you have a little example or idea how i could "import" my json file?

Thx in advance.
Alex

tomalex0
2 Mar 2012, 1:34 AM
If you gone through the Demo implementation, you will find Add functionality its like

create a record and then save it.

Based on your json data, you will have to iterate the json object and save record in the loop.



[
{
"21": {
"firstname": "Alex",
"lastname": "Testuser",
"age": "43"
},
"22": {
"firstname": "Andy",
"lastname": "Testuser2",
"age": "23"
}
}
]
Ext.iterate(a,function(key,obj){
var rec = Ext.ModelMgr.create(obj, 'ModelName').save();
});

digeridoo
7 Mar 2012, 2:45 AM
Pardon my ignorance, but could you explain exactly what the SQLite Proxy can be used for? Could it be used for storing large media (i.e. pictures, Video) files in an SQLite database in the browser?

Thanks

eryx
9 Mar 2012, 1:17 AM
Hi there it's me again :D

I'm struggeling a little bit with the changeversion of the database atm and I hope you may be able to help me.

My mainproblem is with this



/**
* @cfg {String} version
* database version. If different than current, use updatedb event to update database
*/
dbVersion: '1.0',


I update my database with he method changeversion from 1.0 to 2.0, altering a table and all that goes well. My problem now is I don't now how to update the one in the code above. If I set it to 2.0 it will fail at first startup but works after a restart.

If I let it on 1.0 it will never work. Could you explain what you mean with updatedb event or how I could update the version in SqliteConnection.js.

Thanks for your time and have a nice day

Greetings Eryx

bstrappazzon
16 Mar 2012, 6:37 AM
Hi Tomalex,

First I want to thank you for this great proxy, I started using it in sencha 1.1 and keep using it with this new version.

I have a little problem when trying to update an object. Actually I get my object model from the store, modify it and finally save it but the proxy keep trying to make an insert instead of and update.

Here is some code :



var store = Ext.getStore('NewsStore');
var newsRecord = store.getById(id);

console.log(newsRecord, 'News');


//update model with news content
newsRecord.set('read',true);
newsRecord.set('content','my new content');
newsRecord.save();



And this is my model


Ext.define('Kaveat.model.News', {
extend: "Ext.data.Model",
config: {
fields: [
{name: 'newsId', type: 'int', fieldOption: 'PRIMARY KEY'},
{name: 'type', type: 'int'},
{name: 'title', type: 'string'},
{name: 'content', type: 'string'},
{name: 'date', type: 'string'},
{name: 'read', type: 'boolean'},
{name: 'dateModified',type: 'string'}
],
proxy: {
type: 'sqlitestorage',
dbConfig: {
tablename: 'news',
dbConn: Ext.DbConnection
},
}
}
});



Do you have any idea on this issue?

Again thank you for work and for your answer :D

Ben

hihei
22 Mar 2012, 8:57 AM
Hi Tomalex, I insert data to sqlite db withou proxy, but only with api instruction, bu I don't understand why the when I do customeStore.load() the it's empty. E declared proxy in model . . . Thank you in advance!

gcw07
23 Mar 2012, 1:55 PM
Ben, while I'm using SqliteProxy 2 and Sencha Touch 2, the same problem exists there too. I was able to finally track down the reason after back stepping through the code. It appears the system thinks it is a phantom record and because of this, it calls the create method instead of the update method. Unfortunately the only way around it so far I've found is to manually set the phantom flag:

newsRecord.phantom = false;That seems to fix it, though it isn't a great thing to manually be setting that, but don't see any other way around it at this time.

dmregister
4 Apr 2012, 6:12 AM
I have been trying to get sencha 2 and sqlite 2 to work in phonegap but cant seem to.

The problem, it seems that my dbConfig, which are the first few line in the app.js file, right above Ext.application


Ext.ns('DbConnection');
var dbconnval = {
dbName: "dbStorage",
dbDescription: "local storage db"
};
Ext.DbConnection = Ext.create('Ext.Sqlite.Connection',dbconnval);
Ext.application( etc etc)


The DbConnection code gets executed before phonegap is ready. I have tried multiple solutions to no avail. Anyone ever had this problem?

The error that I am getting is INVALID_STATE_ERR: DOM EXCEPTION 11: An attempt was made to use an object that is not, or is no longer usable.

Any help would be greatly appreciated.

khushkedia
5 Apr 2012, 9:46 PM
Hello Team,

I am trying to use the Sqllite proxy for Sencha Touch. I got the latest code from the github.

Not knowing what exactly has to be done with it, cause am very new to Sencha touch and sqlllite,
I put the "sqlite.js in store", "override.js in model" and "sqliteproxy.js and sqlliteconnection.js in views".

I get an error that says sqlite.js not found. Any idea what am i missing in this.

Thanks in advance.

Khushbu

khushkedia
9 Apr 2012, 8:15 PM
Hello Team,

I took the latest sqlite proxy code from github. After 3 days, I finally figured out how to remove the errors from the code and get it running to the previous state.

But the database table is still not created. I have the SqliteProxy.js and SqliteConnection file.js which is supposed to create the table(I assumed).

Any suggestion is welcome.

Thank you in advance.

naokazu.terada
10 Apr 2012, 8:22 AM
I have a question, and i wish someone teach me...

I'm trying to make backup system in my app.
Is it possible that dynamically reload store from sqlite database ?
My app is sencha touch(1.1.1) with phonegap(1.5).

My condition is...

1: copy websql file to another directory as "buckup.db".
2: save new record to the store. and new one is in the list.
3: copy "buckup.db" to original place back (with original name).
4: store.load(); and refresh list.
5: new one still in the list.
6: I quit app. and restart. new one disappear correctly.

I hope that new one could be disappeard in 4 (without restart app).

Any Idea ? please !

thank you. :)

Sasha172
15 May 2012, 1:47 AM
Hi,
Does this work for offline storage of data ?? Because as of now when I clear the browser cache the database empties

eryx
15 May 2012, 1:55 AM
Hey this should work perfectly with offline storage. I did this in multiple apps.
What I do is trying to load data from external and save this data into the database. Next time I try to load data from external I can load the "cached" data if the request fails.

Greetings Eryx

jigiman
19 May 2012, 5:24 PM
I tried to build your sqlitedemo in phonegap for android but it did not work well. is there anything i'm missing?
here's the error:
E/Web Console(537): Uncaught Error: [Ext.Loader] Failed loading 'app/util/InitSQLite.js', please verify that the file exists at file:///android_asset/www/sdk/sencha-touch-all.js:8010

Gaspard22
3 Dec 2012, 6:09 AM
After dowloading the lastest github sources, i'm not able to make it work in a MVC structure , does it will take long to be available ?

tomalex0
3 Dec 2012, 7:30 AM
Have you tried

http://docs.sencha.com/touch/2-1/#!/api/Ext.data.proxy.SQL

Gaspard22
3 Dec 2012, 7:33 AM
ok thanks , i missed this one

Prabjot
11 Mar 2013, 5:37 AM
Can u plz tell me, How to add the differenrt file

Sqlite.js
sqliteConnection
overide.js

in the project structure.

Same Above problem is coming

Ext.DbConnection.dbConn is undefined...

mohamedSabry
27 Mar 2013, 12:52 AM
i'm trying to use this for reading some data from a database
i've managed to remove the errors (working on ST2.1.1) ,but i still can't figure out how to get it working

i tried to see how sqlDemo works,but i can't even find its database, i'm stuck
any one can help?