SQLite Proxy for ST2 (advanced)

[shepsii]

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]

Wow, thank you so much for your great help :-)
This is awesome :-)

Thanks shepsii !!!!!! :-)

[joeshke]

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]

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]

Was just about to post that for you! It's useful for others to have in the thread anyways!

[zmagyar]

Do you plan a fix for this please?

[fayway]

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]

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]

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

Thanks anyway

[BoomerBrian]

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.