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

      0  

    Default


    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:

    Code:
    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') }]
        });
      }
      
    });
    Code:
    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:

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

  2. #122
    Sencha User
    Join Date
    May 2012
    Posts
    131
    Vote Rating
    6
    klaus777 is on a distinguished road

      0  

    Default


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

    Thanks shepsii !!!!!! :-)

  3. #123
    Sencha User
    Join Date
    Jul 2012
    Posts
    7
    Vote Rating
    0
    joeshke is on a distinguished road

      0  

    Default


    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

  4. #124
    Sencha User
    Join Date
    Jul 2012
    Posts
    7
    Vote Rating
    0
    joeshke is on a distinguished road

      0  

    Default


    Quote Originally Posted by joeshke View Post
    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!

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

      0  

    Default


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

  6. #126
    Sencha Premium Member zmagyar's Avatar
    Join Date
    Mar 2012
    Location
    Budapest
    Posts
    91
    Vote Rating
    2
    zmagyar is on a distinguished road

      0  

    Default


    Do you plan a fix for this please?

  7. #127
    Sencha User
    Join Date
    Feb 2009
    Posts
    5
    Vote Rating
    0
    fayway is on a distinguished road

      0  

    Default How to pre-populate a table using SQLite Proxy ?

    How to pre-populate a table using SQLite Proxy ?


    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.

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

  8. #128
    Sencha User
    Join Date
    Jan 2012
    Location
    London, UK
    Posts
    502
    Vote Rating
    57
    shepsii is a jewel in the rough shepsii is a jewel in the rough shepsii is a jewel in the rough

      0  

    Default


    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)

  9. #129
    Sencha User
    Join Date
    Feb 2009
    Posts
    5
    Vote Rating
    0
    fayway is on a distinguished road

      0  

    Default


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

  10. #130
    Sencha User
    Join Date
    Dec 2011
    Posts
    9
    Vote Rating
    0
    BoomerBrian is on a distinguished road

      0  

    Default


    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.