1. #1
    Sencha User
    Join Date
    Jan 2012
    Location
    London, UK
    Posts
    508
    Vote Rating
    62
    shepsii is a jewel in the rough shepsii is a jewel in the rough shepsii is a jewel in the rough

      0  

    Default SQLite Proxy for ST2 (advanced)

    SQLite Proxy for ST2 (advanced)


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

    Hi guys,

    I was playing around with the proxy tomalex0 put together (http://www.sencha.com/forum/showthre...eProxy-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

  2. #2
    Sencha User
    Join Date
    Mar 2012
    Posts
    28
    Vote Rating
    0
    gcw07 is on a distinguished road

      0  

    Default


    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.

  3. #3
    Sencha User
    Join Date
    Jan 2012
    Location
    London, UK
    Posts
    508
    Vote Rating
    62
    shepsii is a jewel in the rough shepsii is a jewel in the rough shepsii is a jewel in the rough

      0  

    Default


    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.

  4. #4
    Sencha User
    Join Date
    Mar 2012
    Posts
    8
    Vote Rating
    0
    eeldwin is on a distinguished road

      0  

    Default Association

    Association


    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?

  5. #5
    Sencha User
    Join Date
    Jan 2012
    Location
    London, UK
    Posts
    508
    Vote Rating
    62
    shepsii is a jewel in the rough shepsii is a jewel in the rough shepsii is a jewel in the rough

      0  

    Default


    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.

  6. #6
    Sencha User
    Join Date
    Mar 2012
    Posts
    8
    Vote Rating
    0
    eeldwin is on a distinguished road

      0  

    Default


    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.

  7. #7
    Sencha User
    Join Date
    Jan 2012
    Location
    London, UK
    Posts
    508
    Vote Rating
    62
    shepsii is a jewel in the rough shepsii is a jewel in the rough shepsii is a jewel in the rough

      0  

    Default


    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:

    Code:
    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:

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

  8. #8
    Sencha User
    Join Date
    Apr 2012
    Posts
    3
    Vote Rating
    0
    martv is on a distinguished road

      0  

    Default Build with Sencha SDK

    Build with Sencha SDK


    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:

    Code:
    [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?

  9. #9
    Sencha User
    Join Date
    Jan 2012
    Location
    London, UK
    Posts
    508
    Vote Rating
    62
    shepsii is a jewel in the rough shepsii is a jewel in the rough shepsii is a jewel in the rough

      0  

    Default


    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
    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.

  10. #10
    Sencha User
    Join Date
    Apr 2012
    Posts
    3
    Vote Rating
    0
    martv is on a distinguished road

      0  

    Default


    Quote Originally Posted by shepsii View Post
    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.