PDA

View Full Version : SQLite Proxy for ST2 (advanced)



shepsii
16 Apr 2012, 7:16 AM
https://github.com/shepsii/SQLite-Proxy-ST2

Hi guys,

I was playing around with the proxy tomalex0 put together (http://www.sencha.com/forum/showthread.php?151444-SqliteProxy-for-ST2) and started to make a number of changes that I think others may find useful.

A massive amount of credit goes to Thomas for starting off the proxy and I release this sneak preview version, based on his code, with his permission. This code is currently still in development, but I've got to a point now where I'm using it in development of an app and only modify the proxy as and when I see the need to, so it will not be complete for a while - hence the release now. It is fully functional, just needs cleaning up and a couple of optimisations.

Note this is a true SQLite proxy, not a WebSQL proxy. This will be persistant across iOS5.1 hybrid app loads if you use the sqlite plugin for PhoneGap.

Here is a list of how it differs from the original proxy:

- cooperates with Ext.Loader

- exists cleanly outside of the ST src directory

- operations not marked completed and successful/error until the db transaction has completed, so you can listen to the operation and act on its completion knowing that the db transaction has finished

- some queries are grouped into a single transaction to massively increase db performance, as opposed to one transaction per single query.

- more closely mimics ST2's webstorage proxy. By this I mean that for the most part, this proxy is considered "local", not "client" or "server"-side (with one notable exception which I'll expand upon shortly). You can bring up any of the ST2 demos that use localstorage, change the proxy to sqlitestorage (and specify a table name as in the included sqlitedemo app) and the app will work the same. So you could use it in conjunction with, say, server side data which you sync to the app and save locally so the app can be used offline.

- remote filtering and sorting is supported on the proxy. This is where the word "remote" in facts means "the sqllite database". So adding filters and sorts will modify the query that the proxy uses against the sqlite db; very useful if you're handling large datasets. Make sure to add a page size to the store.

- uses sqlite's "hidden" __ROWID__ to handle the IDs of each record. You just need to add an integer field called "id" to each model.

- supports fields where persist is set to false in the config - won't save these to the database.

- I removed Tom's debugging console logs

- Supports array and object types on models - I've found this amazingly useful. The proxy just encodes the fields to JSON same as localstorage and saves them in a text row.

Sorry for no readme in the repo, all the info you need should be in this post. I've included a quick demo app.

Note that if you edit the fields in your models, you will need to manually remove the table from the sqlite db before you restart the app (use webkit's developer kit: resources -> databases -> click on db name, type "DROP TABLE tbl_name").

Obviously a good target from here is to get the proxy working with associations, which it currently doesn't. So steer clear! But then, neither does localstorage in ST2 so I'd like so see Sencha's implementation of that before I mimic it for this proxy.

Here's the link to the code again:
https://github.com/shepsii/SQLite-Proxy-ST2

Known issues:
- no store/record association is saved

gcw07
18 Apr 2012, 1:19 AM
This looks great. I was having tons of problems getting the standard version working within the standard loader without having loading warnings. I eventually was able to make changes to get rid of the warnings, but the way I did it was super messy and I didn't like it at all. Your version is so clean and nice. I haven't had a chance to implement it, but will soon. Just wanted to thank you for posting these changes.

shepsii
18 Apr 2012, 1:28 AM
It's a pleasure to share things back to the community!

Note the Ext.Loader config at the top of the app file needs to define the directory for the sqlite classes. Just follow the demo included on github and you should be ok. I'll post a more extensive readme hopefully sometime today.

eeldwin
18 Apr 2012, 4:52 PM
Obviously a good target from here is to get the proxy working with associations, which it currently doesn't. So steer clear! But then, neither does localstorage in ST2 so I'd like so see Sencha's implementation of that before I mimic it for this proxy.

I'm a newbie using ST2 and a bit confused with your statement related with association. If we can populate the data from different table using proxy to store, can we put the association reference between stores?

shepsii
18 Apr 2012, 11:59 PM
My understanding of associations is that they should always be put on models.

The thing is, I don't really think adding them to the model gives you too many advantages. For instance, if you use a hasMany association, all that gives you is a function to get a store of the owned records.

It's not a whole lot of effort just to do this yourself by creating a new store and setting a filter with the foreign key of the parent record on it.

Maybe list your models in this thread as you have them right now, and I can adapt them so that they'd work with this proxy and explain the workarounds required.

eeldwin
20 Apr 2012, 12:13 AM
I think i'm understand now. Thanks a lot.

Just wondering, have you tried this custom proxy using phonegap? If so, I have another question. How do you initialize the tables on database?

I did successfully running my application on iOS, but it's a bit buggy on android.

shepsii
20 Apr 2012, 12:17 AM
I haven't tried running this on PhoneGap or if I'm honest Android.

Theoretically, it should be fine on both. Last year I built an app using different web technologies but an sqlite database and had no problems with my queries running. The key is to know that transactions will take different amount of time on different platforms, so don't do:



store.load();
store.each(function(record) {
// something assuming records loaded
});


this works with localstorage on sencha but not sqlite; the following does work with my proxy but not the original sqlite proxy:



store.load({
scope: this,
callback: function() {
store.each(function(record) {
// something assuming records loaded
});
}
});

martv
20 Apr 2012, 3:12 AM
Hi,

thanks for this awesome plugin. It works fine on browsers, but when I tried to build a native app for Android with Sencha SDK Tools 2, this error occured:



[ERROR] Error thown from your application with message: SECURITY_ERR: DOM Exception 18: An attempt was made to break through the security policy of the user agent.
[ERROR] Failed loading your application from: 'file:///f:/www/new/Mobile/index.html'. Try setting the absolute URL to your application for the 'url' item inside 'app.json'


What am I doing wrong?

shepsii
20 Apr 2012, 4:38 AM
If you switch the proxy for the model to localstorage, do you still get the error?

This isn't my best area of knowledge but interested in anyone who has answers as it is a problem I'm going to have to tackle myself soon.

If when you switch the proxy to localstorage you still get the error, it's down to something else in your app.

If not, then it's a security exception thrown by trying to access sqlite database. To get around this you need to use phonegap with the sqlite plugin instead of sencha's native packaging. I believe this is the plugin:
https://github.com/davibe/Phonegap-SQLitePlugin

T (https://github.com/davibe/Phonegap-SQLitePlugin)here are other guides on the forum to exporting with sqlite... what would be really awesome is if we could get to a point where we have that demo app I put on github working in a phonegap app that we could also share with everyone.

martv
20 Apr 2012, 5:38 AM
If you switch the proxy for the model to localstorage, do you still get the error?

Thanks for your quick reply. When I switch to localstorage, everything works fine. I was hoping to resolve this so I could use native Sencha packager, but it seems that there is no solution at the moment.

I have zero experience with Phonegap, but I'll try to build my app with it and report back to this thread.

shepsii
20 Apr 2012, 5:43 AM
Yeh this is something that Sencha could certainly take a look at, especially with the regression of the localstorage persistance in the newest ios.

PhoneGap works fine also, there really shouldn't be performance differences between the two, just phonegap will be a bit awkward to setup at first until we have a working template.

I know there are tutorials and other threads around that guide through a phone gap package - let us know how you get on. I may be able to help somewhat - I have packaged many an app in phonegap, just not a sencha one as of yet...

shepsii
24 Apr 2012, 1:43 AM
It looks like phonegap has a fix out for these annoying ios 5.1 issues, the latest version sorts the DOM Exception 18 bug and the persistant localstorage/db data. I will get round to doing this around end of June and will release a template form here, but if anyone can beat me to it that would be great (I'd certainly appreciate it!)

JacobGu
3 May 2012, 10:54 AM
Has anybody implemented an automatic mechanism to synchronize model changes to WebSql? It might do something like this on app startup for each model:
If table doesn't exist, create it. (that's all that happens in current implementation)
If table does exist, check for differences between model and table. If there are changes:
If table has rows, clone to temporary table.
Drop original table.
Create new table according to latest model.
Copy temporary table rows into new table. If columns were removed or column type is not compatible, then those data columns will be lost.

shepsii
3 May 2012, 10:58 AM
I think it's important to distinguish between websql and sqlite, this proxy is for sqlite.

This isn't a feature I've implemented yet; in testing when I change the fields on a model I drop the table from the database and reload.

I can't see a deployment situation where you'd change the fields on a model at runtime, so this has been a low priority feature to date. If you can think of a useful one let me know and the feature rises up the priority list!

We can actually do a little better than the list in your post; it is possible to amend table schemas in sqlite. We'd then need to fill out all existing rows of a table with the default value of the field's config.

Here's what I wrote about this at release:



Note that if you edit the fields in your models, you will need to manually remove the table from the sqlite db before you restart the app (use webkit's developer kit: resources -> databases -> click on db name, type "DROP TABLE tbl_name").

JacobGu
3 May 2012, 11:09 AM
Thanks. I need to examine more closely the differences between WebSql and PhoneGap's implementation. Can they be made interchangeable so that a developer can package in PhoneGap, but also develop and test in a desktop browser?

My scenario for model changes is that successive versions of my app may add or change model fields, and I'd like to be able the proxy to handle it behind the scenes without having to write explicit migration code each time.

shepsii
3 May 2012, 1:29 PM
Sqlite definitely works in phonegap (I released an app to the app store last year that wasn't sencha touch but was jqmobile + sqlite and worked fine).

It also works in Chrome/safari at least, I'm not up on other browsers but I believe it's in the HTML5 spec.

I test locally on safari and plan to release with phonegap. They'll be hurdles to cross but I know that sqlite works fine in the newest phonegap version, and I also know it's possible to package ST2 in phonegap.

I will do my best to getting round to modifying the schema of tables where fields have changed... bear with me.

shepsii
3 May 2012, 11:29 PM
Hey Jacob,

Just made a new commit to address changing the fields config. The implementation isn't extensive (as explained below) but does allow you to use stores/records in the standard sencha touch way, to do anything you'd need to do.

I didn't use the full drop table/recreate table, copy across data algorithm because of the overhead on each app start; if the database was a full 5mb+ this would be too long a loading time. I've copied the readme additions on the repository below (https://github.com/shepsii/SQLite-Proxy-ST2)



A quick note on page sizes - because this proxy does do "remote filtering", ie, altering the query to the database if remoteFilter is set to true, it also takes notice of the pageSize config (which I believe defaults to 24). So if you do not want to use pageSize, set it to some arbitrary large number that will be larger than the number of records of the relevant model.

The proxy now includes primitive handling for changing the fields on a model.

New fields added to a model with be detected on app startup, and the appropriate column will be added to the existing Sqlite table. If the field has a defaultValue then this will be set for all records already in the database.

If a model has zero data already in the database, on app startup the table will be recreated. Obviously as soon as the database is populated, this doesn't happen.Removed fields do NOT have their columns removed from the database. This is due to a lack of REMOVE COLUMN command in Sqlite. There is no drawback to this outside of having redundant data remain in the database.

Fields that have had their type altered will NOT be updated in the database. Again there is no facility within Sqlite to alter the type of a column. A drawback here is the types that Sencha Touch work with may not lineup with the database, meaning that values will not be consistent across loads, after a field's type has changed. I would strongly advise renaming a field if its type is changed.

Finally, if you're making large changes to a model from one version of the app to another, and wish for the data to persist across versions, I would recommend the following:
- change the table name on the proxy's db config
- on a relevant controller or on the application, within the init function create a new store, that uses as its proxy the OLD table name
- load data from this store, adjust it to fit the new model config, and then add each record into the new store
- sync the new store
- remove all data from the "old table" store and sync to the database

The next targets remain associations, store/record member recording and a working example for iOS5.1 with the newest PhoneGap version.

gcw07
13 May 2012, 1:52 AM
Has anybody had any problems doing a Sencha app production build? My app works fine in development, however whenever I use "sencha app build production" and then try to access the "app/build/production/" version of it, I am getting problems with the proxy. I'm getting a "TypeError: Cannot read property 'dbConn' of undefined"". Not sure why it would throw this error in a production build but not the development one. Thanks for any help.

shepsii
13 May 2012, 2:12 AM
I'll have a look at this over the next couple of days. My guess would be that it's an issue with the order of Ext.Loader since it works differently when all the javascript is already loaded (as it's in the same file).

eryx
13 May 2012, 11:42 AM
Just startet to play around with your SQLite Proxy and I must say I'm impressed! :D

Nice work!

Greetings eryx

shepsii
13 May 2012, 9:27 PM
Thanks eryx, I'm glad it's useful to others!

The Sencha Command issue is now fixed. It's not the world's prettiest fix, but it works. NB: the fix is in the demo files, NOT the proxy itself. Specifically look at the model definition and the util class.

https://github.com/shepsii/SQLite-Proxy-ST2

I changed the Ext.Loader.setPath call and removed the source within the demo directory so make sure your setPath call is pointing to where the sqlite directory is, relative to app.js. Ext.Loader needs this during testing to load in the files and sencha command needs it to grab the contents of the sqlite files to put them into the single app.js file for production or testing builds.


* 2012/05/14 SENCHA COMMAND COMPATIBILITY *
An issue with the demo app not working when it's been built into production or testing (ie, into a single js file) has been sorted.

It's a fix that works; for some reason when Sencha is in a single file the models will be initiated and their proxies created BEFORE the onReady method of the Ext.Application call is executed. In a pre-build, multi-file development environment, the model calls are after.

NB: I have also changed the Ext.Loader.SetPath call in the app.js of the demo so the source code for the sqliteproxy is only included once in the root of the repository (rather than twice - ie, it's no longer copied in the demo directory).

gcw07
13 May 2012, 10:26 PM
shepsii Thank you for the production build fix. Wonder why the development build loads things differently from the production build. Either way thank you.

shepsii
14 May 2012, 12:24 AM
No worries! I think it's all to do with everything being in one file, and having the contents of app.js at the bottom... javascript hits and executes the model code before the Ext.Application can call the onReady method. Annoying! Different variants of that issue have hit different areas of my code before...

eryx
14 May 2012, 12:37 AM
Hi there it's me again:)) and I have a question.

In Thomas Alexander plugin I was able to do this



var storeProxy = app.stores.NewsStore.getProxy();
storeProxy.queryDB(storeProxy.dbConfig.dbConn, sqlQuery);


So I made an Ajax request got some xml data parsed it and prepared an SQL Statement, then I simply put it into the db like above.

Getting the proxy seems to be the same or atleast it works :D



store = Ext.getStore('News');
proxy = store.getProxy();


So getting the store and then the proxy seems to be no problem

My question now how would I go about inserting this into my db. Is there a function where I can do this over the plugin, or do I need to write this by myself?

Thanks for your time!

Greetings eryx

shepsii
14 May 2012, 1:06 AM
Hi Eryx,

TA's proxy's function was called queryDB - while that's true, it was doing it within a transaction so I renamed the function transactionDB, and changed it to accept multiple queries.

Also, it's not best practice to do things in this way, because if I change the way the proxy works internally in the future, perhaps to fix a bug, you might be stuck with the buggy version if your SQL statement is not compatible with the new schemas the proxy creates.

Better practice is to load the data into a store using an XML proxy, and then copy across the data to another store with the sqlite proxy (or.... set the original store's proxy to an sqlite proxy, and then set all records to dirty or phantom and resave).

If you still want to go ahead with the way you're doing things now, you need to use the transactionDB method of the proxy. This accepts the following parameters:
dbConn - just use proxy.getDb() (in a future version, I really should lose this parameter as it's not necessary)
queries - an array of functions that accept tx as a parameter and execute queries
successcallback - called on transaction success
errorcallback - called on transaction error
callback - always called at end of transaction (I don't advise using this as it's currently in the incorrect place in the code, no-one said this was a final release yet haha!)

So here's an example call:




storeProxy.transactionDB(storeProxy.getDb(), [
function(tx) {
tx.executeSql(sqlQueryString, parameterArray, Ext.emptyFn, Ext.emptyFn);
}
],
function() { console.log('success!'); },
function() { console.log('error!'); }
);



You might for instance then use the onsuccess callback to load a store using the data you've just inserted into the database.

eryx
14 May 2012, 1:30 AM
Hi thanks for your fast and extensive reply. That really helps me. I think I will try the approach with the XML Proxy. I haven't even tried that so I need to see if I'm able to do so ^^.

For now I created a small function for myself. It's almost the same as your transactionDB function.


Ext.define('swissunihockey.handler.Sqlite', {
insert: function (db, sql, callback) {
var me = this;//saving scope
db.transaction(function (tx) {
//do not add () to me.sqlitErrorHandler or it will be
//executed immediately when parsed
tx.executeSql(sql, [], callback, me.sqliteErrorHandler);
});
},
/**
* if you return true this means its a fatal error and the transaction gets aborted
* return false to ignore the error and finish the transaction anyway
* @param {Object} transaction-object
* @param {Object}, error-object
* @return {Boolean}
*/
throwDbError: function (transaction, error) {
console.log('Error! Errormessage was ' + error.message + ' (Code ' + error.code + ')');
return true;
}
});

Thanks for your nice answer and have a nice day

eryx

eryx
14 May 2012, 5:01 AM
Hi again I hope I don't bother you to much but I have one last question ^^

I couldn't work out the whole xml proxy part and I'm trying to use the functions in your plugin now.
But I'm really new to Sencha Touch 2 and somehow I can't wrap my head around this.



Ext.define('swissunihockey.handler.Parser', {
//extend: 'swissunihockey.handler.Sqlite',
//extend: 'Sqlite.data.proxy.SqliteStorage',

newsparser: function (xmlDoc) {

var header = 0,
sqlQuery = '',
i = 0,
el,
news = xmlDoc.getElementsByTagName('item');

sqlQuery = 'INSERT INTO news(title, link, description, pubDate, imagebig, imagesmall)';

for (i = 0; i < news.length; i++) {
el = news[i];
sqlQuery += header++ === 0 ? "SELECT " : " UNION SELECT ";
t = encodeURIComponent(el.getElementsByTagName('title')[0].textContent);
sqlQuery += '\'' + encodeURIComponent(el.getElementsByTagName('title')[0].textContent) + '\' AS \"title\", ' +
'\'' + encodeURIComponent(el.getElementsByTagName('link')[0].textContent) + '\' AS \"link\", ' +
'\'' + encodeURIComponent(el.getElementsByTagName('description')[0].textContent) + '\' AS \"description\", ' +
'\'' + encodeURIComponent(el.getElementsByTagName('pubDate')[0].textContent) + '\' AS \"pubDate\", ' +
'\'' + encodeURIComponent(el.getElementsByTagName('bildgross')[0].textContent) + '\' AS \"imagebig\", ' +
'\'' + encodeURIComponent(el.getElementsByTagName('bildklein')[0].textContent) + '\' AS \"imagesmall\"';
}
//console.log(sqlQuery);
a = sqlQuery;
store = Ext.getStore('News');
proxy = store.getProxy();
}
});



This is my current class but I somehow don't get how I can use a function from the plugin from here inside this class. I tried extending from the sqlite plugin but then I need to pass a config object or I get the error:
TypeError: Cannot read property 'dbConn' of null
I Hope you can help me on that point :/

Greetings eryx

shepsii
14 May 2012, 5:06 AM
Ok, here's the deal... the way you're doing it right now is ugly and not best practice. I'm more than happy to help doing it the best way I can think of (loading using xml parser). Do you want to post your model config and a sample XML? Also, do you have control of the XML file - ie, can you change its contents or is it fixed as it is?

eryx
14 May 2012, 5:11 AM
Ok then let's do it the right way!

Here is my model at the moment



Ext.define('swissunihockey.model.News', {
extend: 'Ext.data.Model',

config: {
fields: [
{
name: 'id',// Every model must start with an id of type int
type: 'int'
}, {
name: 'title',
type: 'string'
}, {
name: 'link',
type: 'string'
}, {
name: 'description',
type: 'string',
}, {
name: 'pubDate',
type: 'int'
}, {
name: 'imagebig',
type: 'string'
}, {
name: 'imagesmall',
type: 'string'
}
],
proxy: {
type: 'sqlitestorage',
dbConfig: {
tablename: 'news',
dbConn: swissunihockey.util.InitSQLite.getConnection()
}
}
}
});


And an example how my xml looks like. Sadly I don't have control over it. I can't change it.



<xml><item><title>WM-Gold für Finnland</title><link>http://www.spox.ch/spx-rss-redirect.aspx?newsid=225734&navi=755&sprache=D&rnr=110</link><description><=!=[=C=D=A=T=A=[<p class="lead"><strong>Die Schweizer U19-Damen-Nationalmannschaft unterliegt im WM-Finale Finnland mit 1:3 und muss sich mit der Silbermedaille begnügen.</strong></p> <div class="image_right_floated"> <dl style="width: 228px;" class="image right_floated"> <dt> <str]=]=><=!=[=C=D=A=T=A=[ong>Finnland sichert sich WM-Gold</strong></dt> </dl> </div> <p>Wie im Halbfinale gegen Tschechien gingen die Schweizer U19-Frauen mit einer sehr defensiven Einstellung ins WM-Finale. Diesmal ging die Abwarte-Taktik aber nicht auf. In der 16. Minute mussten die Schweizerinnen den ersten Gegentreffer hinnehmen, bei Spielhälfte innert zwei Minuten zwei weitere. Das 0:3 war dann eine zu grosse Hypothek, auch wenn die Schweiz im Schlussdrittel mit Vehemenz den Anschlusstreffer suchte. Erst in der 59. Minute traf Fabienne Walther zum 1:3. Zu mehr reichte es nicht. Trotzdem fanden die Schweizerinnen rasch nach Spielschluss ihr Lachen wieder. "Wir haben Silber gewonnen", sagte auch Co-Nationaltrainer Bernhard Nussbaum. Von fünf Partien verlor die Schweiz in Nitra nur eine. Finnland stellte die ausgeglichenste Equipe und gewann verdient den Weltmeistertitel. Der alte Weltmeister Schweden musste mit dem dritten Platz vorlieb nehmen. Mit Simona Stock (Tor) und Nina Bärtschi (Sturm) fanden auch zwei Schweizerinnen Aufnahme im All-Star-Team des Turniers.</p> <h3>Rückkehr am Sonntag</h3> <p>Die Schweizerinnen treten morgen Sonntag ihre Heimreise an und treffen um 11.30 Uhr am Flughafen Zürich (Terminal 1) ein. Selbstverständlich freuen sie sich über einen herzhaften Empfang!</p> <div class="youtube"> </div> <h3>Schweiz-Finnland: 1:3 (0:1,0:2,1:0)</h3> <p>Tore: 15:46 Pevra Juulia (Manninen Laura) 0:1, 24:53 Wickman Mira (Arkkina Hillevi) 0:2, 26:05 Hietanen Senni (Eskelinen Silja) 0:3, 58:51 Walther Fabienne (Bärtschi Nina) 1:3 .</p> <h3>Schweiz:</h3> <p>Stock Simona; Liechti Lisa, Decurtins Prisca; Leu Claudia, Handl Nadine; Reinhard Nadja, Zellweger Rahel; Sturzenegger Sina; Krebs Corina, Walther Fabienne, Bärtschi Nina; Gämperli Andrea, Huber Nicole, Meer Vera; Luck Kassandra, Marendaz Laura, Spichiger Nathalie; Schibli Gisela, Ludwig Ramona.</p>]=]=></description><pubDate>1336379280000</pubDate><bildgross>http://www.spox.ch/cgi-bin/mobilescout/si/2012/05/crop-310-225734-or.jpg</bildgross><bildklein>http://www.spox.ch/cgi-bin/mobilescout/si/2012/05/crop-115-225734-or.jpg</bildklein></item>
</xml>



Hm I see the xml code is unreadable.
This is the structure

<xml>
<item>
<titel></title>
<link></link>
<description></description>
<pubDate></pubDate>
<bildgross></bildgross>
<bildklein></bildklein>
</item>
</xml>

shepsii
14 May 2012, 5:28 AM
Ok so trying to work this out myself, but the first step will be to get the data loaded using an XML proxy and reader.

http://docs.sencha.com/touch/2-0/#!/api/Ext.data.reader.Xml

I (http://docs.sencha.com/touch/2-0/#!/api/Ext.data.reader.Xml) note that your sencha touch field names are different from the XML attribute names. I believe we can use mapping to adapt this on the field configurations, but I've not done this before nor have I seen another example! Here's the link:

http://docs.sencha.com/touch/2-0/#!/api/Ext.data.Field


I (http://docs.sencha.com/touch/2-0/#!/api/Ext.data.Field)'ve not done this myself before, but it looks like you just need to set the mapping attribute of each field to a query that will match it in the dom. May need some trial and error here! Likely you need to have something like this for each field that is different on the model:



{ name: 'imagebig', type: 'string', mapping: 'bildgross' }


I'm not sure if that exactly will work, look into the query format you need for the 'bildgross' string...

eryx
14 May 2012, 5:47 AM
Hi thanks again I think I'm on the right path now. The whole naming thing is not a big deal i just made a model that reflects the names exactly from the xml. Maybe in a later point I can try to figure out how I could change this. For now I made a new model and a store with an xml proxy. I'm able to load the whole data into the store.

My model


Ext.define('swissunihockey.model.Test', {
extend: 'Ext.data.Model',

config: {
fields: [
{
name: 'id',// Every model must start with an id of type int
type: 'int'
}, {
name: 'title',
type: 'string'
}, {
name: 'link',
type: 'string'
}, {
name: 'description',
type: 'string',
}, {
name: 'pubDate',
type: 'int'
}, {
name: 'bildgross',
type: 'string'
}, {
name: 'bildklein',
type: 'string'
}
]
}
});


And my store



Ext.define('swissunihockey.store.Test', {
extend: 'Ext.data.Store',
requires: [
'swissunihockey.model.Test',
'Ext.data.reader.Xml'
],
config: {
model: 'swissunihockey.model.Test',
proxy: {
type: 'ajax',
url: 'http://swissunihockey.xservice.ch/xml/newsdata.aspx',
reader: {
type: 'xml',
record: 'item',
rootProperty: 'xml'
}
}
}
});


So now I need to copy this in an store with a model that has the type sqlitestorage?

Again thank you so much for your help I really appreciate it!

Greetings eryx

shepsii
14 May 2012, 6:09 AM
Hey eryx, great stuff. I think that mapping would fairly easily deal with the difference but if it's nothing to you it doesn't matter. I think the names look better in german anyway!!

So now you need to create a new store with the sqlite proxy on it, and copy across the data.

I've done exactly this in a project myself - I've copied the code below.

All you need to do is run this code when the XML store has loaded, and you've created the sqlite store (you might be better off giving these stores different names... but whatever).

The listener here is useful as I don't hide the loading dialog from the user until this listener fires - I don't want them thinking the whole load/save thing has finished before I can guarantee that the data is now in the database.



var writeListener = function(targetStore) {
sqliteStore.un('write', writeListener, this);

// you can put some code here that you want to run when the sqlite store has saved to the db successfully
};

sqliteStore.removeAll();
xmlStore.each(function(record) {
sqliteStore.add(record);
});

sqliteStore.on('write', writeListener, this);
sqliteStore.sync();

eryx
14 May 2012, 6:21 AM
WoW that was easy! Just used your posted code and now I got the data in the db!
Will need to clean up my whole code and then I can post the full solution for future questions related to that.
Thank you so much! Now I'm happy :D

greetings Eryx

Stju
14 May 2012, 1:35 PM
Found a bug:
Add new person, click to edit, rename to something else, tap save. data is updated in db, but List is not synced.

shepsii
14 May 2012, 9:50 PM
Which ST2 version is that on?

A workaround would be to save the phantom record first, and add it to the store after it's been saved. The issue is it's being added to the list before it has an id; when the record is saved to sqlite it's given an id, and then touch tries to update the list, but because there's no id... no match and no update.

It's an annoying bug... let me have a look into how localstorage deals with this.

Also, you could just reload the store after every write operation but with large data sets that will come with overhead.

shepsii
15 May 2012, 1:06 AM
So the exact same issue happens if you use the localstorage proxy... the bug here is in the demo app, not the sqlite proxy, and the workaround is simple as explained... just save the record and add it to the store on the save record callback (this is incidentally why the proxy must go on the record - I've encountered this issue before!!)

No record should ever go into a store unless it has an id, and it just so happens that if Sencha Touch uses IDs related to the local storage protocol (be it web storage or sqlite db) those are not created until the record has been saved. IMO a store should be able to handle phantom records and have some way of updating phantom records that are now saved in the store's dataviews, but that's an issue for the base code.

shepsii
15 May 2012, 12:33 PM
Running into loads of issues trying to build production/testing apps with this thing... will see what solutions I can come up with.

gcw07
15 May 2012, 12:41 PM
Running into loads of issues trying to build production/testing apps with this thing... will see what solutions I can come up with.After you fix the other day to the proxy I had no problems building production and package versions of my app. Now I did have loads of errors trying to build production version of the sqlite demo.

shepsii
15 May 2012, 12:53 PM
Hey, which branch from github could you package up without any issues? Very keen to hear! I'll sort this tomorrow. Had one of those days where nothing goes right aggh!

What errors do you get now? For me (on windows) I get a dom level 18 exception on build.

*EDIT: bizarrely it does however let me build the demo. IDKWTFIGOH. I just made that up. But the IGOH means is going on here, you should be able to work out the rest ;-)

Will take another look tomorrow but any help on what works / did work would be great.

gcw07
15 May 2012, 2:07 PM
I unfortunately never had any luck building the demo on any version of it. I just had luck building my own app which uses the sqlite proxy. When trying to build the sqlite demo app I was also getting those DOM 18 errors. I kind of gave up trying to get a build working because I needed to fix other things with my app and phonegap.

shepsii
15 May 2012, 9:49 PM
That's very strange because I have no problem building the demo app, but can't build my own at all.

Can anyone else say if they can build the demo app?

Stju
15 May 2012, 11:05 PM
I created my own test app using proxy and, yes, no build issues at all.

shepsii
15 May 2012, 11:06 PM
Thanks stju, good to hear that! Would you mind just quickly trying if you can build the demo app? Cheers!

Stju
15 May 2012, 11:27 PM
Ok, tried, it failed.
So you need to comment out line 76 in app.json file stating: "resources/images",
then it builds and runs correctly.

shepsii
16 May 2012, 12:14 AM
This is so strange because it works fine for me without doing that.

So I can do it fine, stju needs to comment out a line in app.json (presumably the error message wasn't related to a dom 18 exception?) and gcw07 gets the exception.

And then when I try to compile my other app, I get the exception. Aggh...

caretta
16 May 2012, 12:39 AM
can we use store hasmany table relation ?
I-|

shepsii
16 May 2012, 12:40 AM
I'm still baffled but some progress!

Whether or not the dom exception 18 is raised is entirely dependent on the name of the database.

For some reason, on my machine, any app I try to build where the database name is "sqlitedemo", I have no issues whatsoever. Any other name and it raises the exception.

No idea why; there's no difference between sqlitedemo and any other database names, completely baffles me.

shepsii
16 May 2012, 12:42 AM
can we use store hasmany table relation ?
I-|

No, sorry. But then, you can't do that with localstorage proxy either.

My substitute is to have a getChildStore method on the model which creates and returns a new store with the owned records contained. You can use the remoteFilter config of the store and the proxy will do an sql search just for the records that match the filter defined on the store's config.

caretta
16 May 2012, 12:43 AM
i m using with no problem. I have database and tables. But i found a bug. i used ListPaging plugin and not showing correctly. i think store.totalCount always get 0.

shepsii
16 May 2012, 12:45 AM
I always use store.getCount() and have never had any issues with that - it's always been accurate. Do you get the same issue if you change the proxy to localstorage?

caretta
16 May 2012, 12:49 AM
i never try that but i try to override store.totalCount property with a transaction "Select count(*)" but i cant do this : )

shepsii
16 May 2012, 12:53 AM
The transaction would only produce a result on the callback anyway so that's unlikely to be of any use. Could you post some more code? I'm not familiar with the listpaging plugin.

You are correct that the _totalCount property is only ever 0. Let me look into how localstorage deals with this. I see there's a distinction between _totalCount and getCount().

caretta
16 May 2012, 12:58 AM
listpaging plugin works with store.totalCount and the getCount parameters. If store.totalCount property comes 0 than listpaging shows that "No More Records" because of totalCount=0.

http://docs.sencha.com/touch/2-0/#!/api/Ext.plugin.ListPaging

shepsii
16 May 2012, 1:01 AM
Ok really easy fix for you that I will update on github asap.

Override the applyData function of the SqliteStorage.js file to this:



applyData: function(data, operation, callback, scope) {
var me = this;

operation.setResultSet(Ext.create('Ext.data.ResultSet', {
records: data,
total : data.length,
loaded : true
}));

// finish with callback
operation.setRecords(data);

operation.setSuccessful();
operation.setCompleted();

if (typeof callback == "function") {
callback.call(scope || me, operation);
}
},

caretta
16 May 2012, 1:04 AM
i use that but store.totalCount is giving undefined still.

shepsii
16 May 2012, 1:08 AM
ok I need more of your code then because with the proxy set as that, with the demo app, I can use Ext.getStore('People').getTotalCount() to see the correct number in the store.

Note that store.totalCount shouldn't be anything. store._totalCount is where sencha touch puts the data in memory, but that value should always be accessed using store.getTotalCount()

shepsii
16 May 2012, 1:56 AM
I'm still baffled but some progress!

Whether or not the dom exception 18 is raised is entirely dependent on the name of the database.

For some reason, on my machine, any app I try to build where the database name is "sqlitedemo", I have no issues whatsoever. Any other name and it raises the exception.

No idea why; there's no difference between sqlitedemo and any other database names, completely baffles me.

Ok so back to this...

I have to everytime use 'sqlitedemo' as my database name and then do a find and replace on the generated file.

I need to understand more about the scope that javascript is running from sencha command to get what's going on. It would make sense that it's only when the database doesn't yet exist - but in which scope is the javascript running and where would it be looking for the sqlite database? How would the database have been created in the first place? grrr

caretta
16 May 2012, 2:58 AM
i have override some functions for listpagging plugin fix; because store.getTotalCount must return "Total Row Number". Not paged row number. store.getCount() returning paged row number.
So i override these functions:



applyData: function(data, operation, callback, scope, totalcount) { var me = this;
operation.setResultSet(Ext.create('Ext.data.ResultSet', { records: data, total : totalcount, /* changed for listpaging plugin */ loaded : true })); // finish with callback operation.setRecords(data); operation.setSuccessful(); operation.setCompleted(); if (typeof callback == "function") { callback.call(scope || me, operation); } }, applyDataToModel: function(tx, results, operation, callback, scope) { var me = this, Model = me.getModel(), fields = Model.getFields().items; var records = me.parseData(tx, results); var storedatas = []; if (results.rows && records.length) { for (i = 0; i < results.rows.length; i++) { var rowid = records[i].rowid; var record = {}; Ext.each(fields, function(f) { if (f.getType().type.toUpperCase() == 'AUTO') { record[f.getName()] = Ext.decode(Ext.isDefined(records[i][f.getName()]) ? records[i][f.getName()] : null); } else { record[f.getName()] = Ext.isDefined(records[i][f.getName()]) ? records[i][f.getName()] : null; } }); storedatas.push(new Model(record, rowid)); } }
/* changed for listpaging plugin */ me.getTotalRowCount(storedatas, operation, callback, scope); },


And also i add the total count function;



/* added for listpaging plugin */ getTotalRowCount: function(storedatas, operation, callback, scope) { var me = this var onSuccess, onError; onSuccess = function(tx, results) { var totalcount=results.rows.item(0).Co; me.applyData(storedatas, operation, callback, scope, totalcount); };
onError = function(tx, err) { me.applyData(storedatas, operation, callback, scope, 0); me.throwDbError(tx, err); };
me.transactionDB(me.getDb(), [function(tx) { tx.executeSql("SELECT COUNT(*) AS Co FROM "+me.getDbConfig().tablename, [], onSuccess, onError); }], null, null); },

this works good :) also ty to u, shepsii

henry.wilson
16 May 2012, 6:13 AM
I'm having issues with actually reading the data back out of the SQLite DB, I suspect I'm missing something obvious cos I'm fairly new to Sencha Touch so apologies if that's the case...

My app is designed to Grab data from a web service, then store it locally for persistence in the SQLite DB. So I've got a SyncStore to grab the data from the Web Service, and a local store to save it into the DB. I can see the data getting saved into the DB (I can query it using SQLiteStudio) but it's not coming back out into the app. Code snippets follow.

Model Object (with proxy to save into SQLLite DB ):

Ext.define('Navis.model.Claim', {
extend: 'Ext.data.Model',

config: {
fields: [
...
],
proxy: {
type: 'sqlitestorage',
dbConfig: {
tablename: 'claims',
dbConn: Navis.util.InitSQLite.getConnection()
}
}
}
});

SyncStore (pulls from Web Service)

Ext.define('Navis.store.ClaimsSync', {
extend: 'Ext.data.Store',
requires: ['Navis.model.Claim'],

config: {

model: 'Navis.model.Claim',
proxy: {
type: 'ajax',
url: 'http://localhost/naviswebservice/syncservice.svc/GetClaimsData',
reader : {
type : 'json',
model: 'Navis.model.Claim'
}
}
}
});

Local store (should retrieve from SQLite DB but doesn't)

Ext.define('Navis.store.Claims', {
extend: 'Ext.data.Store',
requires: ['Navis.model.Claim'],

config: {
model: 'Navis.model.Claim',
autoLoad: true,
storeId: 'Claims',
pageSize: 1000
}
});

function to sync data:

syncData: function(){
//get stores
var claimStore = Ext.getStore('Claims');
var claimSyncStore = Ext.getStore('ClaimsSync');
//clear local store
console.log('Sync: Clearing Local Datastore');
claimStore.removeAll(true);
//set handler on main store, when sync is done disabled masking
claimStore.on('load', function() {
Ext.Viewport.setMasked(false);
console.log('Sync: Finished!');
});
//set handler on sync store, when load from web service is done, save every record locally
claimSyncStore.on('load', function() {
console.log('Sync: Saving data to local DB');
claimSyncStore.each(function(record){
record.save();
});
claimStore.load();
});
//enable mask
Ext.Viewport.setMasked({
xtype: 'loadmask',
message: 'Syncing data, please wait...'
});
console.log('Sync: Retrieving Data from Web Service');
//load from web service
claimSyncStore.load();
}

caretta
16 May 2012, 6:47 AM
i think that you must use 2 diffrent model. One for offline mod one for online sync.



claimSyncStore.on('load', function() { console.log('Sync: Saving data to local DB'); claimSyncStore.each(function(record){ var newRec=Ext.create('Navis.model.Claim'); newRec.set(record.getData); Ext.getStore('claimStore').add(newRec); }); claimStore.sync(); claimStore.load(); });

shepsii
16 May 2012, 6:59 AM
I think this has something to do with your use of removeAll. A call to this function alone doesn't remove all the records from the database - you need to sync to do that, and that itself would need you to listen for when that first sync has finished.

You shouldn't need two models for both the stores, my code is fine with just one.

henry.wilson
16 May 2012, 7:39 AM
I don't think removeAll is the issue - I tried taking that line out, clearing the DB manually using SQLiteStudio and then running the sync again, and I get the same result - no data is viewable through the ClaimStore in the app, but if you check the DB manually the records have all been saved.

When you say "You shouldn't need two models for both the stores" are you referring to the use of a different store to retrieve the data from the web service? I'm doing this deliberately because I only want the web service call to be made when an explicit data sync is requested by the user - the rest of the time the app should just go to the 'offline' store (i.e. the SQLite DB ). Not sure how you'd do this without two stores, unless you were to handle the web service call manually rather than using a store with a proxy to handle it.

Still stumped as to why I've got data in the DB that isn't showing up in the app :(

shepsii
16 May 2012, 8:12 AM
It's really important to distinguish between "model" and "record" - a record is an instance of a model.

You don't need to define two different models here.

You may need two different records. I'd recommend using record.copy() when you get it from the first store's load.

I'd try this as well when you know there's data in the db, and without all the syncing code, ie just try to start the app and straight away load data into the sqlite store. Do you have any luck then?

henry888
16 May 2012, 10:48 AM
[QUOTE=shepsii;804577]Ok so back to this...
I have to everytime use 'sqlitedemo' as my database name and then do a find and replace on the generated file.


Every time, I want to change the database name, I had to delete Databases.db and file_0 in ~/Library/Application Support/Ofi Labs/PhantomJS on my mac, then do the build again.

gcw07
16 May 2012, 2:06 PM
Henry, I'm doing something similar to you in my app. I copy all the data from an online source to the local sqlite database for offline usage. Whether I'm doing things correctly by the book or not, I'm not sure, but it does work for me.

Stores

/**
* @class People
* @extends Ext.data.Store
*/
Ext.define('App.store.People', {
extend: 'Ext.data.Store',

config: {
model: 'App.model.Person',
autoLoad: false,
pageSize: 1000,

sorters: [
{
property: 'lastName',
direction: 'ASC'
}
],

//group the store using the first letter of the lastName field
grouper: function(record) {
return record.get('lastName')[0];
},
}
});
/**
* @class PeopleOnline
* @extends Ext.data.Store
*/
Ext.define('App.store.PeopleOnline', {
extend: 'Ext.data.Store',

requires: [
'Ext.data.proxy.JsonP',
],

config: {
model: 'App.model.PersonOnline',
autoLoad: false,

//sort the store using the lastName field
sorters: [
{
property: 'lastName',
direction: 'ASC'
}
],

//group the store using the first letter of the lastName field
grouper: function(record) {
return record.get('lastName')[0];
},

proxy: {
type: 'jsonp',
url: 'http://www.url.com/api/people',

startParam: '',
pageParam: '',
limitParam: '',

extraParams: {
userId: false,
appKey: false,
},

reader: {
type: 'json',
rootProperty: 'people.items'
}
}
}
});
Models

/**
* @class Person
* @extends Ext.data.Model
*/
Ext.define('App.model.Person', {
extend: 'Ext.data.Model',

config: {
//idProperty: 'id', // uniqueid // if we have field with name as id, conflicts happens with default idProperty(id) which always have value as ext-record-x
clientIdProperty: 'userId',

fields: [
{name: 'userId', type: 'int', fieldOption: 'PRIMARY KEY'},
{name: 'firstName', type: 'string'},
{name: 'lastName', type: 'string'},
{name: 'code', type: 'int'}
],

proxy: {
type: 'sqlitestorage',
dbConfig: {
tablename: 'people',
dbConn: App.util.InitSQLite.getConnection()
//dbConn: Ext.DbConnection
},
reader: {
type: 'array'
}
}
},
writer: {
type: 'array'
}
});
/**
* @class PersonOnline
* @extends Ext.data.Model
*/
Ext.define('App.model.PersonOnline', {
extend: 'Ext.data.Model',

config: {
fields: [
{name: 'userId', type: 'int'},
{name: 'firstName', type: 'string'},
{name: 'lastName', type: 'string'},
{name: 'code', type: 'int'}
],
}
});
Sync Function

/**
* Get the people data from the server
*/
syncData: function() {
var onlinePeopleStore = Ext.getStore('PeopleOnline');
var peopleStore = Ext.getStore('People');

// Load the online store and then copy to offline
onlinePeopleStore.load({
callback: function(records, operation, success) {
// Clear out the offline people list
peopleStore.getProxy().truncate('people');

onlinePeopleStore.each(function(record) {
var rec = Ext.create('App.model.Person', {userId: record.get('userId'), firstName: record.get('firstName'), lastName: record.get('lastName'), code: record.get('code')});
rec.save();
});
peopleStore.load(); // Reload store
},
scope: this
});
},
That works for me. Now the saving 1 at a time part when you copy from the online to the offline is wrong (it should be batched), but I haven't had time to update it since I switched to the new advanced version of the sqlite proxy. Hope this helps some.

henry.wilson
17 May 2012, 12:10 AM
shepsii, gcw07, thank you both for the advice - I'll have a play about and see if I can get the code to work correctly. It's good to see such a great community for Sencha, I really appreciate it!

shepsii
17 May 2012, 12:29 AM
Let us know how you get on Henry.

Everyone who seems to use this proxy is using it to sync remote data to local.

My code assumes those local records are not changed, and I suspect my app's code is susceptible to the bug originally pointed out by Stju. Otherwise I would have posted it by now!

I'll look into some kind of system of adding in an extension of Ext.data.Store to this proxy, I'm thinking something like this would be useful:

Let's call the store Sqlite.data.SyncStore.

It's created and has a proxy set on it just like any other store - it also needs a sqliteProxy config with what currently goes into the proxy config on the sqlite store.

Whenever data from this store is loaded and the proxy is the original, non-sqlite proxy, the store will automatically:
- perform a sqlite transaction to remove everything that was previously in the database and insert all the new records
- switch the store's proxy to sqlite.

I'd also add a function to the store that would clarify the current proxy settings of the store. And another one to switch back to the original proxy would be good too.

henry.wilson
17 May 2012, 1:44 AM
Sounds like a great idea shepsii!

I have a question regarding something I've observed by looking at the database in SQLiteStudio - does the ordering of columns matter? For some reason there is one field in my Claim object which, despite being declared in the middle of the field list, always appears as the first column in the database, before the Id column. Would this be messing things up? I would hope that the order of columns is irrelevent, but it's weird that this column goes in at the front when all the other ones appear in the order they're declared in the code.

gcw07
17 May 2012, 1:52 AM
While it is a cool idea it could be somewhat automated, it should definitely be an extension or another library within the proxy. That way it doesn't add extra load to the proxy itself and bog down stores that aren't loading from remote sources. Especially, since I think it can already be done, just need to manually do a couple steps yourself.

gcw07
17 May 2012, 1:55 AM
Henry, the order of the columns really shouldn't matter. I have had some of my columns appear out of order too and I could never figure it out. Renaming them sometimes put them back into the correct location, but honestly it really doesn't make a huge difference outside of when your manually looking it over.

shepsii
17 May 2012, 2:01 AM
That shouldn't matter..

What do you mean by the id column? The id shouldn't have a "column" in the database, it uses the sqlite _ROWID_ so I think strictly speaking this is more like the row numbers in excel than an actual column with values.

henry.wilson
17 May 2012, 2:05 AM
Ahhh OK, is your code case sensitive? My column is named "Id" with a capital I, this may be the reason it's showing up as a column. I'm pulling this across in the data from my Web Service too but I'm guessing I don't need to set that value?

shepsii
17 May 2012, 2:08 AM
Ok so the first field must be of type int, and called "id" - case sensitive. This is an ID that the proxy must be left to generate and set all on its own. It will correspond with the _ROWID_ of the record in the database.

If you have remote ID's as well you want to bring across, just call the field "r_id" or something and set them there.

IDs get very confusing very quickly, especially here when Sencha Touch can easily get confused - what is the remote server, the sqlite database or the remote server serving the data? It kind of needs to be both for different reasons, so we need to keep the id's seperate. This allows us to use remoteFilter on sqlite stores to add where clauses to the select query on the proxy.

henry.wilson
17 May 2012, 2:10 AM
Yeah I can see this causing all kinds of trouble. I don't actually need the Id column from the source database so I'll get rid of that. I think I've probably got the knowledge to get this working now...watch this space :)

henry.wilson
17 May 2012, 3:47 AM
Great success! I've got a working sync now using a method similar to the code snippets gcw07 posted earlier in the thread. Need to test that it all works on an actual device (so far I've only tested with Google Chrome) but generally things are looking good. Thanks again for all your help guys :)

henry.wilson
23 May 2012, 4:55 AM
Another question for you guys...

I've got the app working happily on iPad. However when I eventually got it packaged for Android, I get no such luck, the app hangs at load time with the following error:

05-21 13:27:51.710: E/Web Console(3063): Uncaught Error: [Ext.Loader] Failed loading 'app/util/InitSQLite.js', please verify that the file exists at file:///android_asset/sdk/src/chart/sencha-touch-debug.js:7896

(file:///\\android_asset\sdk\src\chart\sencha-touch-debug.js:7896)It looks like an issue with relative paths in the android packaging process. Has anyone successfully packaged an app for android using the SQLite Proxy? Any help would be appreciated.

shepsii
23 May 2012, 4:56 AM
Hi Henry,

Where are you requiring the InitSQLite file? Is it's contents in the single app.js file generated by the sencha production/testing build?

henry.wilson
23 May 2012, 5:04 AM
Hi Shepsii

The top of my app.js has the require for the SQlite file:

Ext.Loader.setConfig({
enabled: true
});

//<debug>
Ext.Loader.setPath({
'Ext': 'sdk/src',
'Sqlite': 'sdk/sqlite',
'Navis': 'app'
});
//</debug>

Ext.require('Navis.util.InitSQLite');

...

Not sure what you're referring to with regard to the sencha production/testing build? The android packaging process just spits out a .apk file. I've looked at the inside of that (using 7-zip) and the code structure seems to be the same as the original structure i.e. everything is in seperate files, not consolidated into one app.js.

shepsii
23 May 2012, 5:06 AM
Hi Henry,

How are you building this to a native app? It is my understanding that sencha app build native is no good for sqlite apps and we have to use phonegap instead. Data is not guaranteed to be persistent from load to load with sencha native packaging; while they do plan to support this in the future it's not there now and they don't have dates.

The approach I will take when I'm doing this myself in a couple of weeks is:
1. build using sencha app build production/testing
2. package with phonegap (I've seen posts suggesting 1.6.1 is the optimal phonegap version for sencha touch 2)

The issue you have right now is with the loader not working for some reason in the android app. Don't worry about this; package the app into a single js file using sencha app build and then package with phonegap.

henry.wilson
23 May 2012, 5:10 AM
Hmmm OK just found this thread (http://www.sencha.com/forum/archive/index.php/t-192470.html?) which states there is no Android 3 support, the tablet i'm using is Android 3 so that's probably the issue I'm having.

shepsii
23 May 2012, 5:10 AM
That's true also! Android 3 is a nightmare for HTML5.

I'd still keep to the method I put above though if you can.

henry.wilson
23 May 2012, 5:14 AM
Thanks for the advice Shepsii

To reply to your questions...

For packaging to iPad I've been using sencha build app native (with appropriate settings). This works fine and I've had no problems with the iPad version.

For packaging to Android I've been running sencha package build packager-android.json

I guess as you say Android doesn't support the Loader so I'll have to look at the process you outlined using Phonegap. I'll look into it as/when I have time (the iPad was the priority for the project, I'm now just doing this for fun cos it'd be neat to see it running on both platforms!)

shepsii
23 May 2012, 5:16 AM
Ext.Loader should be fine on Android (non v3) and iPad - but it should be irrelevant apart from the micro loader when you get to production.

There are plenty other issues with Android v3 apart from Ext.Loader too!!

Phonegap won't help with this problem, but it should help with keeping data persistent in the app from load to load. Have you not experienced any problems with this on the iPad? Make sure the iPad is on at least iOS 5.1 as I believe this is when these horrible bugs with data persistence first came in.

henry.wilson
23 May 2012, 5:22 AM
The iPad in question isn't mine and I don't have it to hand to check the iOS version but I didn't have any trouble with it - I wanted to ensure the data was persisted correctly after shutdown and load of the app so I did a full iPad restart after a sync and the data was still there. Will keep an eye out for this though, definitely worth bearing in mind.

Thanks again for all your help and insight!

gcw07
23 May 2012, 9:52 AM
shepsii have you had success building your apps with phonegap? I mean I can get mine to build fine with phonegap, but if I include the actual cordova.js file, it just doesn't work. I'm sure I'm doing something wrong, but so far have had no luck figuring out what I need to do to include the js file because everytime I do it just loads a white screen when you access the app.

shepsii
23 May 2012, 10:24 AM
I haven't tried yet.

My advice would be to either wait til I sort it in a couple weeks (haha!) or first try to package a non-sqlite st2 app, then the basic sqlite demo, then your app. I have read elsewhere that the correct phonegap version to use is 1.6.1, apparently 1.7 does not play nice with st2. Is this perhaps what's going on with your build?

gcw07
23 May 2012, 10:47 AM
Oh, the problem isn't with the sqlite proxy at all. In fact that works great. I just can't get it to work when including the phonegap javascript file. If you don't include that in the package build, everything works fine in the app it builds. Luckily I don't need access to anything that exists within the phonegap javscript file, so it isn't a huge deal, but just could never figure out how to get it working with their file included (just in case I needed the features it supports in the future).

shepsii
23 May 2012, 11:31 AM
Ok, but without the access to stuff like camera, contacts etc it works fine? Would you mind sharing a copy of the package with the sqlitedemo? Or is it really so easy I need to stop worrying about it?!

gcw07
23 May 2012, 11:39 AM
Yeah, I didn't need access to any of those things and it works fine. I haven't tried packaging up the sqlitedemo and running it through phonegap yet. Really I just did a simple "package" build on my app and then uploaded that to the build.phonegap.com site. I don't think you will need to worry about it that much. The one change I did make was to this line in the Connection file:

me.dbConn = openDatabase(me.dbName, me.dbVersion, me.dbDescription, me.dbSize);

changed to:

me.dbConn = window.openDatabase(me.dbName, me.dbVersion, me.dbDescription, me.dbSize);

Not sure if that was needed or not, just read it in the phonegap docs so went with it and it made it work. Now I've only tested my app on iOS. For all I know it doesn't work with Android or Windows Phone.

shepsii
23 May 2012, 11:50 AM
Awesome. I very much suspect it will work with Android; I deployed a jqmobile app with a sqlite db using phonegap last year.

gcw07
23 May 2012, 11:53 AM
It probably does. I just wish I could figure out what causes the white screen of death when the phonegap js file is included. Doesn't make sense to me.

shepsii
23 May 2012, 11:56 AM
Well I would imagine that build.phonegap.com uses the latest phonegap version?

Might be something to do with this also:
http://www.sencha.com/forum/showthread.php?162322-Sencha-Touch-2-PhoneGap-are-not-working-on-Android-4/page4

gcw07
23 May 2012, 12:09 PM
You can set it to use any version back to 1.4 I believe. I have it set to 1.6.1 and tried that javascript file. It probably has something to do with the way the sencha package build works, but tried production and no luck on that either. I'm not super worried about it for now. Just not a whole lot of info out there on what the best way of getting your code ready for an upload to build.phonegap.com and making it all work.

shepsii
23 May 2012, 12:11 PM
Ok cool, I'll have a go in a couple of weeks and post how far I get here.

gcw07
23 May 2012, 12:18 PM
Yeah I just would be curious to see how it goes for you. I'm not in any rush since it works fine at the moment for my needs. Just want to figure out what I'm doing wrong.

trkaky
21 Jun 2012, 1:44 AM
Thanks for this great piece of work.

Have two questions;

When I set the dB size, lets say to 50mb I get prompted to increase db size by Safari (on a mac) but not by Phonegap, isnt it a bit weird? How can I be sure the limit is increased?

This proxy is obviously WebSQL but it's cached by Phonegap into the Library folder so it can be permanently stored in the device. It's said that it can be turned into a true native SQLite DB using the Phonegap plugin, which plugin is that? and is there a sample code for both the proxy and plugin combined to have native SQLite behaviour?

Thanks
T

shepsii
22 Jun 2012, 6:59 AM
On phonegap, the limit is not increased unless you're more phonegap-proficient than me and have managed to successfully install this plugin:
https://github.com/davibe/Phonegap-SQLitePlugin
I (https://github.com/davibe/Phonegap-SQLitePlugin)n which case let me know how it's done because the readme is rubbish!

That same plugin will get you the native behaviour on iOS. Otherwise only deploy this as a webapp or keep the db size under 5mb.

trkaky
22 Jun 2012, 7:09 AM
have a look at this https://github.com/chbrody/Cordova-SQLitePlugin

(https://github.com/chbrody/Cordova-SQLitePlugin)it should theorically work after a couple of fixes on your script.

Had to jump to another stuff but will get back to it soon and hopefully get them both working.

zmagyar
2 Jul 2012, 8:17 AM
Thanks for the work on this. Just trying to convert my app to use this rather than localstorage. I have noticed that the clear() function of the proxy is not implemented. Is there any particular reason for that?

shepsii
2 Jul 2012, 11:58 PM
I can't see a clear method in the docs - please do correct me if I'm wrong or it's listed elsewhere!
http://docs.sencha.com/touch/2-0/#!/api/Ext.data.proxy.Proxy

To remove all records I just use:


store.removeAll();
store.sync();


(http://docs.sencha.com/touch/2-0/#!/api/Ext.data.proxy.Proxy)

zmagyar
5 Jul 2012, 4:35 AM
BTW the code below doesn't seem to work for me.



To remove all records I just use:


store.removeAll();
store.sync();


(http://docs.sencha.com/touch/2-0/#!/api/Ext.data.proxy.Proxy)

shepsii
5 Jul 2012, 4:43 AM
Ok, could you elaborate on "doesn't work" and perhaps post some code?

I know that code works in one of my apps so there must be a difference in how it's being used.

Note it's not removed from the database instantly. The db transaction happens in another thread.

If you want to execute code as soon as it is finished, add a write listener to the store before you sync.

zmagyar
5 Jul 2012, 4:45 AM
Just figured out that the store needs to be loaded first to get removeAll() working. Is that the expected behavior?

shepsii
5 Jul 2012, 4:49 AM
Yes that would be consistent with how the localstorage proxy works.

When you do removeAll(), ST2 will get all the records in the store and remove them, and make a record of what was removed so it can be fetched with http://docs.sencha.com/touch/2-0/#!/api/Ext.data.Store-method-getRemovedRecords .

Of course its there's nothing in the store to remove because it hasn't been loaded yet, it won't delete anything!

Then on a sync call, ST2 sends to the proxy the records to delete which the proxy will then do.

zmagyar
5 Jul 2012, 5:00 AM
Thanks, it makes sense now.

Unfortunately I'm hitting another issue now. Probably I'm doing something dumb but multiple records are appearing in the DB now. I'm loading data from an online store to an sqlite store by the code below.


storeOnlineCustomer.load(function(){
storeOnlineCustomer.each(function (record) {
storeOfflineCustomer.add(record);
console.log('Customer: ', record.data);
});
storeOfflineCustomer.sync();
me.redirectTo('home/');
});


The console log shows the correct number of records. However if I browse the DB 2-3 copies of each record appearing. Is there any clue what could be the reason for that?

shepsii
5 Jul 2012, 5:03 AM
Can you post the model definition?

zmagyar
5 Jul 2012, 5:10 AM
Ext.define('FieldServices.model.Customer', {
extend: 'Ext.data.Model',
alias: 'model.Customer',
uses: [
'FieldServices.model.Contact',
'FieldServices.model.Address'
],
config: {
fields: [
{
name: 'id',
type: 'int'
},
{
name: 'company',
type: 'string'
},
{
name: 'created',
type: 'int'
},
{
name: 'company_id',
type: 'int'
}
],
hasMany: [
{
model: 'FieldServices.model.Contact',
autoLoad: true,
foreignKey: 'customer_id',
name: 'contacts'
},
{
model: 'FieldServices.model.Address',
autoLoad: true,
foreignKey: 'customer_id',
name: 'address'
}
]
}
});

shepsii
5 Jul 2012, 5:24 AM
Ok, I don't think it's going to work with the hasMany config for starters, but then neither does the localstorage proxy yet!

My solution for that is just to load stores in for the submodels with remoteFilter: true and then filter by their customer_id field.

But I can't see from that why multiple records are going into the database. Have you tried dropping the db table and starting again? Maybe it's a bug you've already fixed, but the proxy's not clever enough to try to fix the database on each load.

stirucherai
5 Jul 2012, 5:31 AM
I have attached a list to SQLite store. The list contionusly shows loading sign. using Java Console I could see that store is loaded but only to 25 elements when actually in DB i have 155.

Is there some trick I am missing.

Store Code is as follows



Ext.define('NDP.store.menuList', {
extend: 'Ext.data.Store',
requires: 'NDP.model.menuList',
config : {
model: 'NDP.model.menuList',
autoLoad: true,
root:'root'
}
});


added pagesize = 1000;

Now it loads all the 154 records. But issue is it is not finishing meaning it is just showing "Loading"

shepsii
5 Jul 2012, 5:40 AM
If it's loading the records into the store, but they're not showing in the list, that sounds like an issue with the list, not with the proxy. The proxy's job is to get the records into the store - from there touch should take over.

I'm happy to have a look at the list if you don't mind posting the code for it.

stirucherai
5 Jul 2012, 5:50 AM
This is the Model code



Ext.define('NDP.model.menuList', {
extend: 'Ext.data.Model',
config : {
fields: [
{name: 'menuID', type: 'string'},
{name: 'menuName', type: 'string'},
{name: 'menuParentID', type: 'string'},
{name: 'menuParentName', type: 'string'},
{name: 'pattuName', type: 'string'},
{name: 'leaf', type: 'boolean'},
{name: 'createdBy', type: 'string'}
],
proxy: {
type: 'sqlitestorage',
dbConfig :{
tablename : 'menuList',
dbConn: NDP.util.InitSQLite.getConnection()
},

reader : {
idProperty : 'menuID',
type:'array'
}
},

writer: {
type: 'json'
}

}


});




Here is the list declaration



{
xtype: 'list',
flex:1,
// allowDeselect:true,
deselectOnContainerClick:true,
store: 'menuList',
itemTpl:'<span class="item-title">{menuName}</span><tpl if="leaf !== true"><span class="x-list-disclosure"></span></tpl>',
id:'menuListView',
scrollable:'vertical'}


As I mentioned the store is loaded with 154 records now (as per in DB) issue is that list still keeps showing loading (no data visible).

shepsii
5 Jul 2012, 5:54 AM
So I don't know if this is the issue stopping it here, but you need the first field of the model to just be called "id" of type integer. It must be exactly that and it's case sensitive. The proxy does not take not of the idProperty config - comment the whole reader bit out of the config to be safe.

Hopefully it will work then! I imagine the transaction is not returning because it's trying to set a field on the model ("id"), that doesn't exist.

zmagyar
6 Jul 2012, 4:25 AM
The defined associations didn't have any affect on that. It was the same after removing the definitions from the models.

Finally I have found that it was down to the autoLoad and autoSync flags. By some reason multiple records are created when these flags are on. It's quite weird.

shepsii
6 Jul 2012, 4:26 AM
I could see how that might happen if you were manually calling store.sync as well as letting the autosync try to sync it?

zmagyar
6 Jul 2012, 4:35 AM
Yep, exactly that was the case.

bbjay
11 Jul 2012, 8:04 AM
@martv: i had the same "SECURITY_ERR: DOM Exception 18" error during a package build.
I was able to resolve this by setting dbSize back to 5mb

klaus777
12 Jul 2012, 4:47 AM
Working mucho excellente. Thank you !!! :-)

How are you doing a select statement on a table, to query certain records?

So far I started with this, but don't know how to continue:


var myStore = Ext.getStore('SQLiteDB');
var p = myStore.getProxy();
// p.doThatSexyQueryNow!!!!(); ;)


Please, please please please please provide a simple example on how to do a simple SELECT :-)

shepsii
12 Jul 2012, 5:07 AM
The proxy supports remoteFilter's and I believe sorts too... what do you need to do with a select query that you can't do with those? e.g. turn remoteFilter on on the store, set a filter on the store and load the store, and the proxy will create a select query with the filters included.

The problem with using a select query is you're bypassing a lot of stuff that the proxy needs to do on the returned results, and you're losing the possibility of switching to another proxy in the future.

klaus777
12 Jul 2012, 5:18 AM
Well I have 2 tables...

Artist Table and Track Table...


So I hope I can do a query to get all tracks from an artist or something.

I'm still trying to understand Sencha as such, so this SQLite isn't that easy for me to understand :-)


But sooner or later I will have to do sth like this here and I thought I'd do this with a SELECT:
http://stackoverflow.com/questions/7566742/sqlite-how-to-entities-associations-without-foreign-keys

klaus777
12 Jul 2012, 10:57 PM
Is there support for associations?

Or how can you make SQL statements work, the most common task in sql-environments?

Or should I program this myself, with some javascript?

Or should I use phonegap sqlite plugin to make SQL statements?


There must be foreign keys, primary keys and selects/queries to make sql work like it should work :-)

Whats the best approach?

shepsii
12 Jul 2012, 11:17 PM
Hey Klaus,

So I think it's important to abstract completely the SQL work to the end programmer (you!).

This is how the other proxies with sencha touch work. If you use localstorage proxy for instance, you don't need any understanding of how localstorage works - all you need to know is that if you sync() and load(), the data is persistent!

There are lots of those things like foreign keys, primary keys, selects/queries going on in the proxy. But you shouldn't need to worry yourself with them (more in a bit)

As for associations, localstorage proxy with ST2 does not yet support these, but imo associations in sencha touch are weak and not work the effort they create as the whole associations system in touch has never been properly debugged in my opinion.

But fear not! I have achieved what you are trying to do with your artist and track models, using this proxy. In my case, I have "leagues" and "teams" - where a league can own multiple teams. Here are my models:



Ext.define('FFDraftGM.model.goas.FFLeague', {
extend: 'FFDraftGM.model.Model',

config: {
fields: [
{ name: 'id', type: 'int' },
{ name: 'name', type: 'string' }

],

proxy: {
type: 'sqlitestorage',
dbConfig: {
tablename: 'ffleagues',
dbConn: GOAS.util.InitSQLite.getConnection()
}
}
},

getTeamsStore: function() {
return Ext.create('Ext.data.Store', {
autoLoad: true,
model: 'FFDraftGM.model.goas.FFTeam',
remoteFilter: true,
filters: [{ property: 'ffleague_id', value: this.get('id') }]
});
}

});




Ext.define('FFDraftGM.model.goas.FFTeam', {
extend: 'FFDraftGM.model.Model',

config: {
fields: [
{ name: 'id', type: 'int' },
{ name: 'ffleague_id', type: 'int' },
{ name: 'name', type: 'string' }
],

proxy: {
type: 'sqlitestorage',
dbConfig: {
tablename: 'ffteams',
dbConn: GOAS.util.InitSQLite.getConnection()
}
}
}
});


note the getTeamsStore function on a league. This is the equivalent of what would be the teams() function would be using associations. Note the remoteFilter set on the store - this will tell the proxy that it needs to add a condition to the sqlite select statement to only get this league's teams. Note also the store is set to autoLoad: true. This is important as it means the data will not be there immediately after the call to this function!. The store must load first. That's fine if you're setting it straight onto a dataview, but if you want extra processing over the data, you must add a load listener after the store is returned and act on the listener.

As you can see, ffleague_id on the team model is the "foreign key", although sqlite is not a complicated enough database to need to be given the exact information as to what this column means.

Note on creating a league and teams at the same time, I need to save the league first. The reason for this is because until the league is saved, the proxy has not been given an opportunity to generate an id for it.

As soon as it has been saved, it has an id, so we can set that id on the ffleague_id field of the owned teams, and then save them.

Your code for this looks something like:



var league = Ext.create('FFDraftGM.model.goas.FFLeague', { name: 'Demo League' });
league.save({
scope: this,
callback: function() {
for(var i = 1; i <= 10; i++) {
var team = Ext.create('FFDraftGM.model.goas.FFTeam', { name: 'Team ' + i, ffleague_id: league.get('id') });
team.save();
}
}
});


It would be neater code here to get the teams store after the league has saved, add in all the teams, and then sync the store, but that would have taken me longer to type in ;-)

As for the phonegap Sqlite plugin. Don't think about this at all until after you've finished developing the app as a web app and you're looking to package. You will only need this plugin if your database is bigger than 5mb. But that's a concern for packaging the app to native and shouldn't affect your javascript programming at all.

If I can help further just ask!

klaus777
13 Jul 2012, 4:49 AM
Wow, thank you so much for your great help :-)
This is awesome :-)

Thanks shepsii !!!!!! :-)

joeshke
19 Jul 2012, 1:35 AM
First of all thank you shepsii for your great proxy,
but I have a little problem with your proxy.

I load data via ajax request and set the data to the storage after sync the list refresh and the new data is visible.

But when I refresh the page or load the data from the store again only the first 25 items are visible. In chrome debugger all items in the table are visible.

Thank you in advance

joeshke
19 Jul 2012, 3:04 AM
First of all thank you shepsii for your great proxy,
but I have a little problem with your proxy.

I load data via ajax request and set the data to the storage after sync the list refresh and the new data is visible.

But when I refresh the page or load the data from the store again only the first 25 items are visible. In chrome debugger all items in the table are visible.

Thank you in advance

Ok found the solution -> pageSize ... seems like I'm a real noob!

shepsii
19 Jul 2012, 3:19 AM
Was just about to post that for you! It's useful for others to have in the thread anyways!

zmagyar
23 Jul 2012, 4:00 AM
Do you plan a fix for this please?

fayway
22 Aug 2012, 9:51 AM
Hi shepsii

Thank you for the great work, we really appreciate your share.

I wonder what's the best practice to define initial data records for a model using an sqlitestorage proxy.
Of course those data must be inserted just the first time the table was created for the given store.



/**
for example I want the table books to be pre-populated with
init_books = [
{ id: 1, name: 'sencha', available: true },
{ id: 2, name: 'javascript', available: true }
];
**/

Ext.define('GS.model.Book', {
extend: 'Ext.data.Model',

config: {
fields: [
{ name: 'id', type: 'int' },
{ name: 'title', type: 'string' },
{ name: 'available', type: 'boolean' }
],
proxy : {
type : 'sqlitestorage',
dbConfig : {
tablename : 'books',
dbConn : GS.utils.InitSQLite.getConnection()
}
}
}
})

Ext.define('GS.store.Books', {
extend : 'Ext.data.Store',
requires : 'Ext.data.proxy.LocalStorage',


config : {
model : 'GS.model.Book',
autoLoad: true,
storeId: 'Books',
pageSize: 1000
}
})



Thanks in advance

Kind regards

shepsii
23 Aug 2012, 7:22 AM
On every app load:

load the store.
if after loading, it has a size of 0:
add the records
sync

tip: reload after the sync has written to get the ids (use write listener to do this)

fayway
23 Aug 2012, 11:37 PM
On every app load:

load the store.
if after loading, it has a size of 0:
add the records
sync

tip: reload after the sync has written to get the ids (use write listener to do this)

Ok thanks shepsii, I thought we can somehow also insert initial data during table creation, this part of code was may be made for this purpose:

https://github.com/shepsii/SQLite-Proxy-ST2/blob/master/sqlite/data/proxy/SqliteStorage.js#L62

(https://github.com/shepsii/SQLite-Proxy-ST2/blob/master/sqlite/data/proxy/SqliteStorage.js#L62)Thanks anyway

BoomerBrian
26 Aug 2012, 1:57 PM
How do I get the id from my model and not the ext-record number?

Like the example I use "id" in my model but when I retrieve it the ext-record number returns.

Thanks.

zmagyar
26 Aug 2012, 10:43 PM
The id field is set when the record is written to the DB. If you want to make sure the proper value is read after sync() then you need to listen the write event of the store.

ridan
27 Aug 2012, 6:42 AM
I have the same question as BoomerBrian, the id field is not stored in the database. It is deliberate (In getDbFields method):

if ((f.config.persist || !Ext.isDefined(f.config.persist)) && (f.getName() != m.getIdProperty())) {

Can you please explain why it is ignored ?

Thanks in advance,

bumpy
27 Aug 2012, 1:00 PM
I came across this proxy today and tried using it.
I couldn't help but notice that the records I updated or added to the store had their "dirty" property set to true even after I called sync().
I put some console.log to watch the sql being executed by the proxy, and it showed me that once a model is updated or added to the store, every subsequent sync operation executes an update command on the record.
That means that if I update 6 records (even if I call sync after each update) the 6th sync will cause 6 updates to execute (1 for each record).

I investigated this issue for a while and I'm pretty sure the reason is that the proxy never calls commit on the record. I solved this temporarily by registering to the write event of the store and committing the record there. It seems it wouldn't be that hard fixing the proxy, but I wasn't sure what was the right place to insert the fix...

jatinderbimra
28 Aug 2012, 11:05 AM
Hi Sephii,

I am using your sqlite proxy, how I can fetch records from local db in range like example below.

From date: and To Date, From Jan 2011 to Jan 2012

How to fire a query to fetch the records from jan 2011 to jan 2012 from saved data in db?

And can we insert Json data into db and then retrieve it back again from local db, any example link to share or documentation.

I am using ST 2.0.1.1 version.

Thanks in advanced.

Jatinder

ridan
28 Aug 2012, 1:11 PM
@bumpyI fixed your problem by changing onSucess cb in both create and update methods:

onSuccess = function () {
operation.setCompleted();
operation.setSuccessful();

for (i = 0; i < length; i++) {
records[i].commit();
}

if (typeof callback == 'function') {
callback.call(scope, operation);
}
},

ridan
29 Aug 2012, 2:22 PM
Hi,I'm trying to run SQLite Proxy with a cordova plugin for android. The problem is that read method is called before the query "SELECT sql FROM sqlite_master WHERE name=?" has finished.

The query is executed before the createTable function is called.Is there any way to indicate that no query can be executed before this piece of code is completely executed:

me.transactionDB(me.getDb(), [function (tx) {
tx.executeSql('SELECT sql FROM sqlite_master WHERE name=?',
[ me.getDbConfig().tablename ],
function (tx, result) {
if (result.rows.length > 0) checkDataExists(tx);
else createTable(tx);


}, onError);


}], Ext.emptyFn, Ext.emptyFn);

BoomerBrian
29 Aug 2012, 3:39 PM
The id field is set when the record is written to the DB. If you want to make sure the proper value is read after sync() then you need to listen the write event of the store.

How do I do this in my controller? The store.sync doesn't have callbacks does it?

tling
30 Sep 2012, 11:06 AM
The query is executed before the createTable function is called.Is there any way to indicate that no query can be executed before this piece of code is completely executed:

me.transactionDB(me.getDb(), [function (tx) {
tx.executeSql('SELECT sql FROM sqlite_master WHERE name=?',
[ me.getDbConfig().tablename ],
function (tx, result) {
if (result.rows.length > 0) checkDataExists(tx);
else createTable(tx);


}, onError);


}], Ext.emptyFn, Ext.emptyFn);


First I need to said thanks to shepsii for his excellent work!!!

As for ridan's problem. I have the exact same problem with trying to get it to work using ST2+phonegap+SQLitePlugin on iOS . I did some debugging and it seem the createTable fn is called but the sql statement "CREATE TABLE ....." is never executed. I am not sure why is there due to my limited knowledge of sql... :(

But I did come up with a solution replace the original function with this one seems to get it to works in my iOS app

createTable = function (tx) {
var tableSql = 'CREATE TABLE IF NOT EXISTS ' + me.getDbConfig().tablename + ' ('+ me.constructFields() +')';
me.getDb().transaction(function(tx) { tx.executeSql(tableSql); });
},


Try it and let me know whether this work on android.

One additional note. DO remember to set the pageSize on your storage otherwise it will limit to 25 entries per load. (I was scratching my head when only 25 records are loaded with 40 records)

Have fun and thank again shepsii!

Qumhieh
1 Oct 2012, 11:24 AM
First i want to thank you for the amazing work, this will really save a lot of time
i have questions about performance on huge data, lets say about 15000 record, and i wanna search for specific record, the sencha findRecord ( for stores ) can handle it?? or i need to make sqlite query and if i need to make a query where i can use it?

thanks in advance

shepsii
3 Oct 2012, 3:38 AM
qumhieh - the way the proxy works is that it will load all records into the store, and then sencha's search will operate over the store in the javascript memory. Yes, it will work - but storing that many objects in javascript's memory is not a good idea.

For comparison's sake, I built an app that stored about 5000 records in a store, that used this proxy. Performance was so slow I had to abandon Sencha's model/store standards and go with my own implementation that didn't have all the listeners and gunk sencha puts into the memory for it's auto updating of dataviews.

For ridan's problem, I deployed with ST2 + Phonegap + SqLite Proxy and did not have any issues. Hopefully I have some time to look into this further for you guys, in the meantime I would say start with the example code, this definitely works, then adapt from there. When it doesn't work anymore, take a look at what you just changed...

badgerb1
3 Oct 2012, 5:05 AM
Hi Qumhieh (http://www.sencha.com/forum/member.php?443542-Qumhieh),

The Proxy works like a webproxy, so if you use a filter to retrieve your 1 record you will only have the one record in memory. That's the way we use the DB. We filter to the records we need and don't rely on the findxxx functions for the major lookups.

Just remember that you will need to do some finagling on the filters to clear them out since they stack on top of each other. So if you do a filter
store.filter('eyeColor', 'Brown')

And then try to do another filter


store.filter('eyeColor', 'Blue')
You will have no records left since you are saying filter where eyeColor is both Blue and Brown. You have to clear the filters and reapply them.

Bob

tling
3 Oct 2012, 6:37 AM
For ridan's problem, I deployed with ST2 + Phonegap + SqLite Proxy and did not have any issues. Hopefully I have some time to look into this further for you guys, in the meantime I would say start with the example code, this definitely works, then adapt from there. When it doesn't work anymore, take a look at what you just changed...

Thanks for the reply shepsii.

Which version of PhoneGap and SqLite do you have. Is it the latest one?

I tired the example and having the same issue with ridan with PhoneGap 2.1.0 and this latest SQLite Plugin https://github.com/chbrody/Cordova-SQLitePlugin.

shepsii
3 Oct 2012, 6:50 AM
It was 1.x phonegap not 2.x... wish I could be more specific but don't have the time to dig up the files right now, sorry!

anhnt3
8 Oct 2012, 8:40 AM
Thank shepsii for you work.


I am building Phonegap2.1 + lastest Sqlite Proxy + lastest Sqlite Plugin for Android with Sencha 2.1 Beta 3 (RC1 now) and Sencha CMD 3 v190. However the models still be initiated and their proxies created BEFORE the onReady method of the Ext.Application call is executed when i build package/tesing -> it is still OK in development enviroment.
I have double checked and applied your lasted fix related to the issue, but it seem not OK on Sencha 2 Beta 3.


Can you please help us work around.

Qumhieh
9 Oct 2012, 6:40 AM
Thanks for reply shepsii and badgerb1
i have another question, on the application launch event i need to check data from database before any view
created, so what im trying to do is get the store and find the record im looking for (Ext.getStore('store').findRecord()), but its seems during launch event all stores still empty and they get data after launch event done. (i add 'autoload : true' to store but still not works)
so how i can get the data before any view created?

anhnt3
10 Oct 2012, 5:00 PM
Thank shepsii for you work.


I am building Phonegap2.1 + lastest Sqlite Proxy + lastest Sqlite Plugin for Android with Sencha 2.1 Beta 3 (RC1 now) and Sencha CMD 3 v190. However the models still be initiated and their proxies created BEFORE the onReady method of the Ext.Application call is executed when i build package/tesing -> it is still OK in development enviroment.
I have double checked and applied your lasted fix related to the issue, but it seem not OK on Sencha 2 Beta 3.


Can you please help us work around.

I have asked sencha team in here : http://www.sencha.com/forum/showthread.php?245491-Sencha-2RC1-Sencha-CMD-v.190-App-onready-event-order-is-incorrect-in-package-build&p=898047#post898047

(http://www.sencha.com/forum/showthread.php?245491-Sencha-2RC1-Sencha-CMD-v.190-App-onready-event-order-is-incorrect-in-package-build&p=898047#post898047) Hopefully, we can continue to use sqlite plugin in next sencha release.

Note: i also adapted sqlitestorage of shepsii with sqlite plugin for Android on Phonegap 2.1.

shepsii
11 Oct 2012, 2:03 AM
You need to realise that loading from a database is not an instant event - it takes time.

So if you just call store.load() or set autoLoad to true, you can't assume data will be there instantly.

If you want to be able to make that assumption, listen to the load event on the store, and when this event fires, then instantiate your UI.


anhnt3 - could you share your adapted file - perhaps by branching on github? I wish I could spend the time tracking this down myself, personally I used Sencha Touch 2.1 and thought it was still a complete mess and nowhere near production ready. Promising, but when the general release happens, which in my experience with Sencha will be equivalent quality to a release candidate, I'll treat that as a RC and go through it filing all the bugs I can find... excuse my cynicism that there will still be loads!

anhnt3
11 Oct 2012, 2:13 AM
You need to realise that loading from a database is not an instant event - it takes time.

So if you just call store.load() or set autoLoad to true, you can't assume data will be there instantly.

If you want to be able to make that assumption, listen to the load event on the store, and when this event fires, then instantiate your UI.


anhnt3 - could you share your adapted file - perhaps by branching on github? I wish I could spend the time tracking this down myself, personally I used Sencha Touch 2.1 and thought it was still a complete mess and nowhere near production ready. Promising, but when the general release happens, which in my experience with Sencha will be equivalent quality to a release candidate, I'll treat that as a RC and go through it filing all the bugs I can find... excuse my cynicism that there will still be loads!


There is no tx.executeSql interface in Sqlite Phonegap Plugin, it only support transactionDB API.
So in your setTable function, esspecially createTable, checkDataExists and checkExistingSchema, I must modify to use transactionDB.
Since then when application startup in native Android, DB and table is created succesfully.
Other operation like read,create... I am trying but currently I cannot work around [model 's proxy init before app' onready] problem in package build :( (Sencha 2.1 and Sencha CMD 3 v/190)

Qumhieh
11 Oct 2012, 4:51 AM
You need to realise that loading from a database is not an instant event - it takes time.

So if you just call store.load() or set autoLoad to true, you can't assume data will be there instantly.

If you want to be able to make that assumption, listen to the load event on the store, and when this event fires, then instantiate your UI.


thanks shepsii.....thats works !!! :)

ludm
24 Oct 2012, 2:07 PM
I'm interedted in your sqlite proxy, but I have a few questions :

How to make the demo work ?
We have to test it with phonegap/cordova ?
Do you have a tutorial to do an application with the proxy ?

reco123
26 Oct 2012, 11:41 AM
I have these following bug when i try to build the app using sencha cmd v3 , any idea?



[ERROR] def was null
[ERROR] failed to find meta class definition for name proxy.sqlitestorage
[ERROR] BUILD FAILED
[ERROR] com.sencha.exceptions.BasicException:

munder
26 Oct 2012, 1:58 PM
This proxy has been working superbly in Chrome during development. However, when I come to package it (sencha app build production) I also get a similar error to the one above. Mine's slightly different, so I might as well list it in full:
[ERROR] def was null
[ERROR] failed to find meta class definition for name proxy.sqlitestorage
[ERROR] Exception running app build : JavaException: com.sencha.exceptions.BasicException: null
[ERROR] javax.script.ScriptException: JavaException: com.sencha.exceptions.BasicException: null in <Unknown source> at line number 709
If anyone could point me to where I can find out what on earth any of that means, I'd be very grateful.

shepsii
1 Nov 2012, 8:04 AM
As I've said before, I can't vouch for this working with ST2.1+, Sencha Cmd 3, or sencha native packaging.

I only have time to maintain for my own current packaging workflows, which are ST2.0 and phonegap 1.8.1.

If other people can work out these issues, please submit a fork on github and I will review and merge.

Otherwise, I will likely be using ST2.1+ and Phonegap 2 to compile my next app around March time.

munder
3 Nov 2012, 7:55 AM
With reference to the error messages I mentioned above: I packaged the app with Applaud's Phonegap bundle and it works fine on Android.

Manoj Solanki
5 Dec 2012, 7:08 AM
I have modify model field:

fields: [
{ name: 'id', type: 'int' }, // Every model must start with an id of type int
{name:'SNO', type : 'int', fieldOption : 'PRIMARY KEY ASC'},
{ name: 'name', type: 'string' } // bools, floats, objects and arrays also supported
],


On controller i want to delete record when list tapped I have updated controller :

editPerson: function(list, index, target, record, event) {
store = Ext.getStore('People');
var obj=store.getAt(index);
console.log(record.data.name);
store.remove(obj);
store.sync();
console.log("editPerson Function Called");
},


These work fine only when i run the code for the fitst time but when I again build my app error is occure an no reflection on database...

Error:-

Primary key must be unique.

badgerb1
5 Dec 2012, 8:01 AM
How is your SNO field being set from the DB? The Primary key error should be happening when you are inserting into the DB. The error is occurring when the editperson function is called??? If so you may not be calling sync when you insert the records and the sync isn't being called until this method is run.

I'm guessing when you start out with a blank db you are filling the store with records and manually incrementing the SNO field, then you delete a record which executes the sync() command and actually writes the records to the table. Then when you restart the app, the SNO increment is restarted at 1 and the new records are added to the store (but not synced yet) and then when you call the editPerson function the sync runs which tries to add all the new records which have the same SNO values.

Remove the fieldOption: from your SNO field and see what gets into the DB. (you'll probably see the records have the same values). I'm pretty sure it really has nothing to do with the remove method.

BTW. I'd change the code for the editPerson function. Manually looking up the store and the lookup by index is unnecessary since you already have the record and the list should be backed by the correct store to remove the person from.

editPerson: function(list, index, target, record, event) {
store = list.getStore();
store.remove(record);
store.sync();
},

Bob

Manoj Solanki
5 Dec 2012, 10:56 PM
Thanks for the reply bob..

SNo field is Autoincrement field. Thats so I have added field option with Sno field.


editPerson: function(list, index, target, record, event) {
store = list.getStore();
store.remove(record);
store.sync();
},

These code is fine with table having No Autoincrement field but when i took field Autoincrement(Primary Key with integer type) these code give error of Primary key must be unique.

Not for the first run but when we stop the app and again start error occur and no delete reflect on Database....

jastako
13 Dec 2012, 12:33 PM
Did anyone try this configuration:
ST 2.1 + PhoneGap 2.2 + PhoneGap-SQLitePlugin (by chbrody: https://github.com/chbrody/Cordova-SQLitePlugin/) + SQLite Proxy by Shepsii

on Android?

It gives me following log, using weinre (http://people.apache.org/~pmuellr/weinre/docs/latest/):



SQLitePlugin
SQLitePlugin.prototype.open
DB opened: warsaw-ztm
MyApp.utils.Globals#updateDb
MyApp.controller.screen.BusStopSelection#onDatabaseReady
SQLitePluginTransaction - this.trans_id:1355429776541000
SQLitePluginTransaction.prototype.executeSql
success not null:SELECT sql FROM sqlite_master WHERE name=?
executeSql - add_to_transactionSELECT sql FROM sqlite_master WHERE name=?
SQLitePluginTransaction.prototype.complete
SQLitePluginTransaction.queryCompleteCallback
executeSql callback:[{"sql":"CREATE TABLE bus_stops_complexes (\n\tcomplex INTEGER NOT NULL, \n\tname VARCHAR NOT NULL, \n\tcity VARCHAR NOT NULL, \n\tPRIMARY KEY (complex)\n)"}]
SQLitePluginTransaction.prototype.executeSql
success not null:SELECT * FROM bus_stops_complexes LIMIT 1
executeSql - add_to_transactionSELECT * FROM bus_stops_complexes LIMIT 1
SQLitePluginTransaction.prototype.complete
SQLitePluginTransaction.queryCompleteCallback
executeSql callback:[{"complex":"1001","city":"Warszawa","name":"Kijowska"}]
processMessage failed: Message: J SQLitePluginTransaction.queryCompleteCallback('1355429776541000','1355429776553000', [{"complex":"1001","city":"Warszawa","name":"Kijowska"}]);
processMessage failed: Error: TypeError: Cannot call method 'getFields' of null
processMessage failed: Stack: TypeError: Cannot call method 'getFields' of null
at [object Object].getDbFields (file:///android_asset/www/deps/touch/3rd-party/SqliteStorage.js:293:16)
at file:///android_asset/www/deps/touch/3rd-party/SqliteStorage.js:228:29
at file:///android_asset/www/deps/touch/3rd-party/SqliteStorage.js:217:10
at Object.callback (file:///android_asset/www/deps/SQLitePlugin.js:167:16)
at Function.queryCompleteCallback (file:///android_asset/www/deps/SQLitePlugin.js:82:60)
at eval at processMessage (file:///android_asset/www/deps/cordova-2.2.0.js:1062:26)
at processMessage (file:///android_asset/www/deps/cordova-2.2.0.js:1062:13)
at Function.processMessages (file:///android_asset/www/deps/cordova-2.2.0.js:1123:17)
at Object.androidExec [as exec] (file:///android_asset/www/deps/cordova-2.2.0.js:985:21)
at [object Object].complete (file:///android_asset/www/deps/SQLitePlugin.js:212:20)


Given that PhoneGap is available to use after 'deviceready' signal, I've postponed DB init:


onDatabaseReady: ->
console.log Ext.getDisplayName(arguments.callee)

busStopsComplexesStore = Ext.getStore 'BusStopsComplexes'
if on
# shepsii sqlite proxy:
busStopsComplexesStore.setProxy
type: 'sqlitestorage'
dbConfig:
tablename: 'bus_stops_complexes'
dbConn:
dbConn: GetEverywhere.utils.Globals.getDb()
reader:
type: 'array'
else
# Sencha built-in sqlite proxy:
busStopsComplexesStore.setProxy
type: 'ge-sql'
table: 'bus_stops_complexes'
database: GetEverywhere.utils.Globals.getDb()


What I'm trying simply to accomplish is populate list view with rows from given table. Any clues?

EDIT:
I've linked my app against ST2.0 and now it works just fine. Hopefully I won't be using any fancy functionality available from ST2.1 in the future.

vadimv
17 Dec 2012, 2:07 AM
My tests just showed that the best solution for SQLite within ST2.1 & Phonegap 2.2.0 is to use Phonegap plugin from https://github.com/brodyspark/PhoneGap-sqlitePlugin-iOS and default ST SQL proxy: Ext.data.proxy.SQL with the override:


Ext.define('Ext.data.proxy.SQLOverride', {
override: 'Ext.data.proxy.SQL',

getDatabaseObject: function() {
if(Ext.os.is.Android){
//TODO: add android part
}
else
if (Ext.os.is.iOS && !navigator.platform.toLowerCase().match(/simulator/)) {
return sqlitePlugin.openDatabase("data.sqlite");
}
return this.callParent();
}
});

jastako
17 Dec 2012, 8:17 AM
My tests just showed that the best solution for SQLite within ST2.1 & Phonegap 2.2.0 is to use Phonegap plugin from https://github.com/brodyspark/PhoneGap-sqlitePlugin-iOS and default ST SQL proxy: Ext.data.proxy.SQL with the override:


Ext.define('Ext.data.proxy.SQLOverride', {
override: 'Ext.data.proxy.SQL',

getDatabaseObject: function() {
if(Ext.os.is.Android){
//TODO: add android part
}
else
if (Ext.os.is.iOS && !navigator.platform.toLowerCase().match(/simulator/)) {
return sqlitePlugin.openDatabase("data.sqlite");
}
return this.callParent();
}
});


Thanks Vadimv. In fact I already tried ST's native SQLite proxy, but without success. Then I turned into Simon's proxy and found it work for me. Maybe someday I will refactor my code to use ST's proxy, because right now I'm mapping table columns to model fields on my own, while ST's does it for you.

Can you please provide us some snippets with real life usage of ST's SQL proxy? The only example I found is:
http://druckit.wordpress.com/2012/11/16/using-the-sencha-touch-2-1-sql-proxy/

I've no idea why, but ST2.1 started working in my configuration eventually, which is great, because it speeds up storage load significantly. But please, don't ask me how I did, cause no idea.

Gaspard22
21 Dec 2012, 3:00 AM
SQLite format 3@ -‚%
¸
R
?U
Rf!%%ÅtablepeoplepeopleCREATE TABLE people(name TEXT)É!yyÑAtable__WebKitDatabaseInfoTable____WebKitDatabaseInfoTable__CREATE TABLE __WebKitDatabaseInfoTable__ (key TEXT NOT NULL ON CONFLICT FAIL UNIQUE ON CONFLICT REPLACE,value TEXT NOT NULL ON CONFLICT FAIL)Å$!ÅEyindexsqlite_autoindex___WebKitDatabaseInfoTable___1__WebKitDatabaseInfoTable__
??;mWebKitDatabaseVersionKey1.19
ÃÃ3m WebKitDatabaseVersionKey


i got this launching the sqlitedemo, someone knows why ?

vadimv
21 Dec 2012, 6:11 AM
@jastako
T (http://www.sencha.com/forum/member.php?372091-jastako)hat example is ok. What problems did you have ?
@Gaspard22
W (http://www.sencha.com/forum/member.php?511029-Gaspard22)hy not to user ST's proxy, is much better written, bet is faster, easy to use, and has support.

Gaspard22
21 Dec 2012, 6:27 AM
correct me if i'm wrong
ST proxy is for websql, and websql have a 5 Mo limitation right ?


this project's for an native app in IOS.

vadimv
21 Dec 2012, 7:00 AM
correct me if i'm wrong
ST proxy is for websql, and websql have a 5 Mo limitation right ?


this project's for an native app in IOS.
You can use it for SQLite too, on native, see my previous post with the override.

vadimv
21 Dec 2012, 7:11 AM
Is the Sql proxy would work only with the plugin ?

Using phonegap 2.20 api not sufficient ?
yes, only with a phonegap plugin.

jastako
21 Dec 2012, 3:09 PM
@vadimv, here's my code:

First I define my proxy which returns native SQLite DB (not the WebSQL's one):



Ext.define 'MyApp.data.proxy.Sql',
extend: 'Ext.data.proxy.SQL'
alias: 'proxy.myapp-sql'

getDatabaseObject: ->
return Globals.getDb()


Globals.getDb() returns device's native SQLite DB obtained with Brody Spark's PhoneGap SQLite plugin (https://github.com/brodyspark/PhoneGap-sqlitePlugin-Android). This variabl

Then if assured, that DB is ready (right after PhoneGap's successful initialization) following method is executed:



setProxy: (store) ->
if not @databaseReady
# Some error handling here and exit.
return

table_name = 'some_table'

if on # Simon Shepherd's (3rd-party) SQLite proxy:
store.setProxy
type: 'sqlitestorage'
dbConfig:
tablename: table_name
dbConn:
dbConn: Globals.getDb()
else # ST's native SQLite proxy:
store.setProxy
type: 'myapp-sql'
table: table_name


And here's the thing. If I use Simon's proxy, everything works just fine, while ST's native one is not working for me whatsoever. List view, which is supposed to be populated by given store is empty in ST's proxy case. I even recently tried to rename DB table names to fit my model names instead of specifying table name in the code, but with no success.

I'm really happy to use Simon's proxy, but also really crazy about native solution.

vadimv
25 Dec 2012, 1:13 AM
I just add that override and the proxy config in the store define and that's it, don't set anything after phonegap ready, very simple. And yes you must map the database tables to models, the names must be exactly and the fields also. Also first test on websql and if works then for sure that on native will work too, with websql developing is much faster. And if you get sth strange, better to delete the databases, from time to time. Try to make a basic app with one model, once will work will be much easier for you with your app.

One more, first load ST code then Cordova.

EDIT: I see that you are on Android, probably aren't problems with its plugins, but as I'm working on iOS I can assure you that on iOS works.

jastako
25 Dec 2012, 8:17 AM
I used to develop using Chrome and its WebSQL native solution as it speeds up dev. process greatly. From time to time deploy to device to confirm everything works on target. Bearing this in mind I well tested ST's proxy in both environments. Will try as you suggested from simple hello world and then act accordingly.

Qumhieh
26 Dec 2012, 2:33 AM
Hey
what is the storage size limit? cuz my app stuck if i increase the storage size over than 50MB
(freeze in both webview and after i do the native packaging)
any help please

jastako
26 Dec 2012, 3:31 PM
Hey
what is the storage size limit? cuz my app stuck if i increase the storage size over than 50MB
(freeze in both webview and after i do the native packaging)
any help please

Have you allocated whole DB in memory? If so, you most likely exceeded memory limit per app. On Android it varies from 16 to 48 MB depending on OS version and device itself (how much RAM it is capable of). Don't know anything about iOS and how it can be overcome in both OSes, but I'm pretty sure Google knows something about it.

Gaspard22
27 Dec 2012, 6:56 AM
Hi Vadimv, i'm not certain about the config here

do i have to use the same proxy type , or create a new one ?
cause when i tried t create a new one everything stucks

Ext.define('CWFSE.store.Groups', {
extend: 'Ext.data.Store',
requires: ['CWFSE.model.Group', 'Ext.data.proxy.SQLOverride'],
xtype:'groupStore',
config: {
storeId: 'groupLst',
autoLoad: true,
autoSync: true,
model:'CWFSE.model.Group',
proxy: {
type: 'sql'
}

}
});



2nd question in the SqlOverride.js

do you have to refer to your db or set it as yours ?


return sqlitePlugin.openDatabase("data.sqlite");

vadimv
27 Dec 2012, 9:04 AM
1. your config is ok, remove from requires:
Ext.data.proxy.SQLOverride
2. you need to put your database name.

Vadim

Gaspard22
28 Dec 2012, 12:43 AM
Vadmiv

After applying your advice, i tried to add a new line to my group store.

I can see it in the proxy, but not in the sqlite database ....

Do you have a full example , to see what's going wrong with mine ?

vadimv
28 Dec 2012, 1:54 AM
have you made store sync or record save ?

Gaspard22
28 Dec 2012, 2:04 AM
yes, here's my code


var groupst=Ext.getStore('groupLst');
groupst.add([{
GRP_ID :'1',
ADR_ID :'1',
UPL_ID:'1',
TRD_ID:'1',
GRP_NAME:'TITO',
GRP_MAIL:'[email protected]',
GRP_TEL:'0411020304',
GRP_URL:'adresseurlx',
GRP_SHARE_SMS:'1',
GRP_NBR_WORKSTATION:'0',
GRP_TOKEN:'24544'

}]);
groupst.sync();


i'm doubting a little about the override ....

vadimv
28 Dec 2012, 7:12 AM
do you have an id field in model ? also check for panthom after sync if is false

Qumhieh
29 Dec 2012, 11:55 PM
hi again
here is the error i got when i increase DB size over than 50 MB in IPAD (v5)
"SECURITY_ERR:DOM Exception 18: An attempt was made to break through the security policy of the user agent"

i believe this is a privilege issue, but why its appear only when i increase the DB size? and how i can fix it?
(my application is for IOS (IPAD) only)

Gaspard22
3 Jan 2013, 12:40 AM
my model don't have any ID, it should ?

cause i tried with an idproperty , and an identifier and nothing have change ...

vadimv
3 Jan 2013, 3:30 AM
should have an id, otherwise won't sync&save.


Ext.define('App.model.MyModel', {
extend: 'Ext.data.Model',

config: {
fields: [
{ name: 'id', type: 'int' },
{ name: 'myfield', type: 'string' }
],
proxy: {
type: 'sql'
}
}
});

fairport
10 Jan 2013, 11:46 PM
You guys might have fixed this already, maybe not

When you send a function that returns a string to whereClause, the code we downloaded was adding a 'null' to the end of the WHERE clause.

eg StartDate BETWEEN '2013-01-01' AND '2013-02-01' null

Because of the last else in the construction of the sql string ... This code was the culprit

sql += ' ' + filter.getValue();

Anyhow ... by moving all the code
/* FROM HERE*/ if (!filter.getExactMatch() && /*... DOWN TO the "Cuplrit" */ up to

if(typeof filter.getFilterFn() == 'string'){ ... } else { /*MOVE TO HERE*/ }
... problem solved

billtallitsch
30 Jan 2013, 10:39 AM
Are the size limitations for storage limited to the amount of hard disk space on the user's device when using this solution?

When packaging with cordova, is this the right solution for storing and retrieving large video files (>500mb) on a users device for offline viewing? Maybe I need the Cordova SQLite plugins (for iOS)?

munder
21 Feb 2013, 1:23 AM
This proxy has brilliantly solved a lot of the problems I was having. Thank you.
I'm stuck, though, on one particular table, where I need to ensure that records are unique, and don't know how. This is my model:


Ext.define('LRS.model.Answers', {
extend: 'Ext.data.Model',


config: {
fields: [
{name: 'id', type:'int'},
{name:'distribution_respondent_id',type:'int'},
{name:'survey_id', type:'int'},
{name:'question_id',type:'int'},
{name:'question_response_value', type:'string'},
{name:'response_skipped', type:'int'},
{name:'response_skipreason', type:'string'},
{name:'response_comment',type:'string'},
{name:'date', type: 'long'},
{name:'synced', type:'string'}
]
},
identifier: 'uuid'
});

Ideally, I would like to have a composite primary key on the fields 'survey_id' and 'question_id', as that would guarantee each row's uniqueness, but I haven't been able to find out how to create a composite key in a model. Having 'id' as unique would clearly guarantee that each row is unique, by virtue of the uniqueness of 'id', but doesn't stop repeated combinations of 'survey_id' and 'question_id'. Does anybody have any ideas?

shepsii
21 Feb 2013, 1:28 AM
Maybe I can be of help....

Apologies if I'm wrong on the following assumption (would be embarrassing on my own code!) but I don't believe that each table actually has an ID column; I think this is actually set from the auto-increment __ROWID__ on each websql/sqlite table (http://www.sqlite.org/autoinc.html). In that case your concerns about overriding the ID column as the primary key wouldn't apply, because strictly that column is not even part of the table's schema, in fact if you inspect the tables using the webkit dev tools, it doesn't even show.

So theoretically, you should be able to modify the create table method on the proxy (perhaps extend the proxy and have an additional config for an array of primary keys?), modifying the create table statement should be fairly easy:
http://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns

Hope this helps!

shepsii
21 Feb 2013, 1:30 AM
Sorry for hideously late reply (to billtallitsch)

If you package to native, this will still use websql, therefore it's subject to the 5mb limit. In fact, asking for more space in a native app is not a good idea. webview's do not handle that request well.

If you want more than 5mb you'll need to go ahead and use the plugin. I believe there's one for android too.

munder
21 Feb 2013, 4:01 AM
Thanks for the information, shepsii, and for the suggestion. Excellent idea. I have a tendency to work with what I've got (i.e. lazily rely on other people's hard work), but modifying the proxy is a great suggestion.

NMP
21 Mar 2013, 1:47 AM
hi again
here is the error i got when i increase DB size over than 50 MB in IPAD (v5)
"SECURITY_ERR:DOM Exception 18: An attempt was made to break through the security policy of the user agent"

i believe this is a privilege issue, but why its appear only when i increase the DB size? and how i can fix it?
(my application is for IOS (IPAD) only)

Did you find a solution for this? I'm also trying to create a larger DB than 50MB...

badgerb1
21 Mar 2013, 8:02 AM
Did you find a solution for this? I'm also trying to create a larger DB than 50MB...

Safari IOS has a 50MB limit so if you're doing a browser based application that's all you have. Web Views, which is what most "Native" apps actually use, also has this limit.

If you are using Cordova/PhoneGap to create a native app, there's a plugin that looks like it allows larger limits. I haven't used anything like this however so I can't be sure.
https://github.com/brodyspark/PhoneGap-SQLitePlugin-iOS
(https://github.com/brodyspark/PhoneGap-SQLitePlugin-iOS)
Thanks
Bob

vn_service_desk
15 Apr 2013, 10:41 PM
Hi everyone,

I have a question with the sqlite proxy . Can the application's database be deleted if user delete the cookies, cache .. in iOS settings ?

Manoj Solanki
15 Apr 2013, 11:04 PM
No, the application database is not deleted or even effected when you clear cookies and data from "ios setting".

I have checked it in simulator.:)

vadimv
15 Apr 2013, 11:08 PM
No, the application database is not deleted or even effected when you clear cookies and data from "ios setting".

I have checked it in simulator.:)

But you check on device ;), you can delete websql databases by taping "clear cookies and data" button. If is native then yes, is not deleted.

shepsii
15 Apr 2013, 11:15 PM
You should be using the sencha touch 2.2 proxy if you're on 2.2 now as that works fine for me. It filters using queries on the database with the filters param rather than remoteFilters but this works just fine.

I would recommend people switch over.

As for persistancy, please be aware that sqlite and websql are different things. They happen to share, for the most part, their query language, but here is the main difference:
- websql is a browser based database implementation. Sometimes it works on a sqlite backend. The W3C have abandoned the specification for this since 2010, but it remains implemented in nearly all modern browsers. IT IS NOT TRULY PERSISTENT! Any user can delete the data from the browser at any time they wish.
- sqlite is a type of database that can be accessed on most platforms, and relevantly here iOS and Android. It is NOT websql, although websql may be working with a sqlite implementation. Sqlite itself is therefore only available through natively packaged apps, and you'll need something other than HTML and Javascript to implement it. I believe Sencha has implemented such a feature in 2.2 and there are cordova plugins available. If you use these, then the application that has control of the database is your app - not the browser. You can control where the sqlite file goes, therefore controlling its persistency (make sure you put it in the relevant place on iOS - this is determined by whether the data is user generated (needs to go in icloud folder) or data that could be redownloaded again from your servers).

Manoj Solanki
4 Jul 2013, 3:13 AM
Hello friends,

I have a table which having some records(Users). But I can not get store count in app.js launch function.

console.log(Ext.getStore('User_S').getCount());
console.log(Ext.getStore('User_S').getAllCount());

Store:
Ext.define('ListDemo.store.User_S',
extend: 'Ext.data.Store',
requires: ['ListDemo.model.User_M'],
config: {
model:'ListDemo.model.User_M',
autoLoad: true,
autoSync :true
}
});

Model:


Ext.define('ListDemo.model.User_M', {
extend: 'Ext.data.Model',
config: {
fields:[{name:'id',type:'int'},
{name:'name',type:'string'},
{name:'password',type:'string'},
{name:'email',type:'string'},
{name:'phone',type:'string'}],
proxy:{
type: 'sqlitestorage',
dbConfig: {
tablename: 'tblUser',
dbConn: ListDemo.util.InitSQLite.getConnection()
}
},
validations: [
{type: 'presence', field: 'password',message:'Password can not be blank'},
{type: 'length', field: 'name', min: 4, message:'Name must be greater than 4 character'},
]
}
});

Please help.

Thanks

jastako
4 Jul 2013, 6:37 AM
Hi @Manoy Solanki,

First of all, what are the logs?

If you are deploying your app on device and testing it against its native SQLite DB, which is stored in FS, rather than HTML5 WebSQL, you have to wait with populating store with data until DB is accessible. That is (in PhoneGap world) you would wait for "deviceready" signal and then query handle to DB (which API is compatible with HTML5 WebSQL). In your approach you make attempt to access DB on app launch, by specifying model's proxy. Wait with this, until "deviceready" is emitted and then assign such proxy to your S-T-O-R-E :).

However, if submitted snippet doesn't work on your local machine in the web browser with WebSQL DB support I would recommend you to assign this proxy to store rather than model. At least I tend to do so, such a way and it works all the time.

Hope it helps.

Cheers,
Kuba

Manoj Solanki
24 Jan 2014, 2:41 AM
Thanks Kuba,

You are correct at the time of launch function the connection with DB table is not established.

Thanks again. :)



Hi @Manoy Solanki,

First of all, what are the logs?

If you are deploying your app on device and testing it against its native SQLite DB, which is stored in FS, rather than HTML5 WebSQL, you have to wait with populating store with data until DB is accessible. That is (in PhoneGap world) you would wait for "deviceready" signal and then query handle to DB (which API is compatible with HTML5 WebSQL). In your approach you make attempt to access DB on app launch, by specifying model's proxy. Wait with this, until "deviceready" is emitted and then assign such proxy to your S-T-O-R-E :).

However, if submitted snippet doesn't work on your local machine in the web browser with WebSQL DB support I would recommend you to assign this proxy to store rather than model. At least I tend to do so, such a way and it works all the time.

Hope it helps.

Cheers,
Kuba

juanlubarreda
26 Aug 2014, 2:08 AM
My tests just showed that the best solution for SQLite within ST2.1 & Phonegap 2.2.0 is to use Phonegap plugin from https://github.com/brodyspark/PhoneGap-sqlitePlugin-iOS and default ST SQL proxy: Ext.data.proxy.SQL with the override:


Ext.define('Ext.data.proxy.SQLOverride', {
override: 'Ext.data.proxy.SQL',

getDatabaseObject: function() {
if(Ext.os.is.Android){
//TODO: add android part
}
else
if (Ext.os.is.iOS && !navigator.platform.toLowerCase().match(/simulator/)) {
return sqlitePlugin.openDatabase("data.sqlite");
}
return this.callParent();
}
});


What doi have to do to use this override?

In App.js add a require?
Where should be located the file with the code you specified?

I'm a little lost on overrides,

Thank you!