SQLite Proxy for ST2 (advanced)

[shepsii]

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]

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]

Has anybody implemented an automatic mechanism to synchronize model changes to WebSql? It might do something like this on app startup for each model:

1. If table doesn't exist, create it. (that's all that happens in current implementation)
2. If table does exist, check for differences between model and table. If there are changes:
   1. If table has rows, clone to temporary table.
   2. Drop original table.
   3. Create new table according to latest model.
   4. 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]

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]

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]

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]

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]

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]

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]

Just startet to play around with your SQLite Proxy and I must say I'm impressed!

Nice work!

Greetings eryx