1. #1
    Sencha User murrah's Avatar
    Join Date
    Oct 2007
    Location
    Katoomba, Blue Mountains, west of Sydney, Australia
    Posts
    381
    Vote Rating
    11
    murrah will become famous soon enough

      0  

    Default [SOLVED] Ext.data.SQLiteProxy: Problem with primary key after inserting a new record

    [SOLVED] Ext.data.SQLiteProxy: Problem with primary key after inserting a new record


    Hi,

    I am adding a new record to a SQLiteStore using the following sample code. My primary key is membId and I am providing it's value as shown below (a string). However, after the record is added, the membId field in the store record contains the RowID in the table, not the key I passed it!

    My record definition:
    PHP Code:
    WholeMealCafe.memberRec Ext.data.Record.create([
        {
    name'membId'type:'string'},
        {
    name'membParentId'type:'string'},
        {
    name'membCode'type:'string'},
        {
    name'membFirstname'type:'string'},
        {
    name'membLastname'type:'string'},
        {
    name'membEmail'type:'string'},
        {
    name'isFolder'type:'boolean'}
    ]); 
    My add test functionality:
    PHP Code:
    var pk Ext.uniqueId(true);
    var 
    rec = new WholeMealCafe.memberRec({
        
    membIdpk,
        
    membParentIdparentId,
        
    membCode'TEST',
        
    membFirstname'',
        
    membLastname'',
        
    membEmail'',
        
    isFolderfalse
    }, pk)

    rec.phantom true;

    WholeMealCafe.MemberStore.add(rec); 
    The problem seems to be the following block in the Ext.data.SQLiteProxy.createCallback() method:
    PHP Code:
    if (action === Ext.data.Api.actions.create) {
        
    air.trace('create callback=' data[reader.meta.idProperty]) // I added this

        
    data[reader.meta.idProperty] = result.insertId;

        
    air.trace('create callback new=' data[reader.meta.idProperty]) // I added this

    and a sample output looks like:
    Code:
    create callback=092413758KWX1
    
    create callback new=12
    where 12 is the rowID in the table after inserting.

    Now, result.insertId is passed by the Ext.data.SQLiteDB.createCallback() method:
    PHP Code:
    = {
        
    records_this.readResults(res),
        
    insertIdres res.lastInsertRowID null,
        
    affectedRowsres res.rowsAffected null,
        
    errore.error
    }; 
    It makes sense to me that res.lastInsertRowID is passed back to the caller. However, I am wondering why it is then used to set the primary key field without first checking whether a value is already in the record?

    As a test I changed the block to
    PHP Code:
    if (action === Ext.data.Api.actions.create) {
        if (
    data[reader.meta.idProperty] == '') {
            
    data[reader.meta.idProperty] = result.insertId;
        }

    and my problem was solved. However, that works for my setup but may break other use cases.

    So, is that a bug or am I doing something wrong?

    Thanks,
    Murray

  2. #2
    Sencha User makana's Avatar
    Join Date
    Apr 2008
    Location
    Dresden, Germany
    Posts
    527
    Vote Rating
    19
    makana has a spectacular aura about makana has a spectacular aura about

      0  

    Default


    Hello Murray,

    it seems like a bug.
    You're absolutely right that I have to check the property first.
    That's the typical "integer primary key" thing with SQLite.
    There are several issues with Ext.data.SQLiteProxy that will be fixed with the next release.
    I asked Jamie Avins to build a new air package and he will do with the next extjs release (3.3.2), that actually should be available since this week. I think we have to wait a little bit longer...

    I will fix the issue as soon as possible. For now please use the your override to the createCallback function.
    Many thanks for the hint!

    makana
    Programming today is a race between software engineers striving to build bigger and better іdiot-proof programs, and the universe striving to produce bigger and better idiots. So far, the universe is winning. (Rick Cook)

    Enhanced ExtJS adapter for Adobe AIR

  3. #3
    Sencha User murrah's Avatar
    Join Date
    Oct 2007
    Location
    Katoomba, Blue Mountains, west of Sydney, Australia
    Posts
    381
    Vote Rating
    11
    murrah will become famous soon enough

      0  

    Default


    Hi Makana,

    Thanks for your reply. I have been away from Ext for a while and am now back again.

    I am building a small app that uses your updated SQLite db methods. One of the changes you made was to make the db access async rather than sync. A problem I have encountered with this is as follows. My SQLite store is for tree nodes. Once I add a new record to the store for a leaf node (as per the example in this post above), I want to set the node of the parent to be a folder and then reload the grandparent so that the changes to the tree are visible to the user. There may be a better way to do that but for now this quick way is sufficient for this application and not relevant to the problem at hand.

    Eg
    PHP Code:
    var node WholeMealCafe.memberTree.getNodeById(parentId);
    var 
    memberRec WholeMealCafe.MemberStore.getById(node.id);
    memberRec.set('isFolder'ToFolder);
    if (
    node.isRoot) {
        
    node.reload();
    } else 
    node.parentNode.reload(); 
    The problem is that because of the async nature of the database, I need to use defer() for the process to work. If I dont use defer, the record doesnt even make it to the database table, and the reload doesnt work either.

    So, I need to do this for the process to work:
    PHP Code:
    var node WholeMealCafe.memberTree.getNodeById(parentId);
    (function () {
        var 
    memberRec WholeMealCafe.MemberStore.getById(node.id);
        
    memberRec.set('isFolder'ToFolder);
        (function () {
            if (
    node.isRoot) {
                
    node.reload();
            } else 
    node.parentNode.reload();
        }.
    defer(100this));
    }.
    defer(100this)); 
    Now, to make it more complicated, in my app I need to load 10 childnodes in the same operation and am trying to develop some extra code to track when all the adds are actually complete because the store obviously does not work properly until all the callbacks have completed. The db writes are async but the store is not (from the users point of view). The onAdd listener for each record fires before the create callback, so that wont work. The records are not always added to the database and that is presumably why the store's onWrite() listener doesnt fire.

    Is there is an easy way to add multiple records via the store and know when they have all fully completed so that I can use the store with confidence that all the data is actually there? Am I missing something that I should do?

    I am also curious about the advantages of async vs sync database interaction.

    I have spent way too many hours on this and wonder whether the upcoming update addresses any of these timing issues - if, indeed, they are issues and not just my ignorance about how to use the classes.

    Thanks again for all your work,
    Murray

  4. #4
    Sencha User murrah's Avatar
    Join Date
    Oct 2007
    Location
    Katoomba, Blue Mountains, west of Sydney, Australia
    Posts
    381
    Vote Rating
    11
    murrah will become famous soon enough

      0  

    Default


    Here is more information:

    My Store:
    PHP Code:
    WholeMealCafe.classMemberStore Ext.extend(Ext.data.SQLiteStore, {
        
    autoSavetrue,

        
    constructor: function () {
            
    WholeMealCafe.classMemberStore.superclass.constructor.call(this, {
                
    sortInfo: {
                    
    field'membCode',
                    
    direction'ASC'
                
    },
                
    fieldsWholeMealCafe.memberRec,
                
    connWholeMealCafe.conn,
                
    tableName'members',
                
    idProperty'membId'
            
    });
        }
    });

    WholeMealCafe.MemberStore = new WholeMealCafe.classMemberStore();

    WholeMealCafe.MemberStore.on('add', function (storerecordsindex) {
        
    air.trace('onAdd membId=' records[0].data.membId ', ' records[0].data.membCode);
    });

    WholeMealCafe.MemberStore.on('write', function (storeactionresultresrs) {
        
    air.trace('onWrite');
    });

    WholeMealCafe.MemberStore.on('update', function (storerecordoperation) {
        
    air.trace('onUpdate ');
    }); 
    Here is the test of adding 3 records:
    PHP Code:
    for (var 03i++) {
        var 
    pk Ext.uniqueId(true);
        var 
    rec = new WholeMealCafe.memberRec({
            
    membIdpk,
            
    membParentIdparentId,
            
    membCode'TEST' i,
            
    membFirstname'',
            
    membLastname'',
            
    membEmail'',
            
    isFolderfalse
        
    }, pk)
        
    rec.phantom true;
        
    WholeMealCafe.MemberStore.add(rec);
    }; 
    Here is the console output:
    Code:
    onAdd membId=133021831Z6G6, TEST0
    
    onAdd membId=1330218842PON, TEST1
    
    onAdd membId=133021889WR2U, TEST2
    
    callback for 133021831Z6G6
    The last line is set in Ext.data.SQLiteProxy.createCallback() (as per the earlier post). Note that it is only called for the first Add. And, none of the records turn up in the database table, but they are in the store.

    I dont think it is a timing issue because I just added a 3 second delay between each Add and the output was the same.

    Before I spend more time rummaging about in the proxy code, is there anything obvious that I am missing here?

    Thanks,
    Murray

  5. #5
    Sencha User makana's Avatar
    Join Date
    Apr 2008
    Location
    Dresden, Germany
    Posts
    527
    Vote Rating
    19
    makana has a spectacular aura about makana has a spectacular aura about

      0  

    Default


    Hi Murray,

    you have to listen for the "save" event of the store!

    Normally, that's all you need.

    The "write" event should work, too.
    Programming today is a race between software engineers striving to build bigger and better іdiot-proof programs, and the universe striving to produce bigger and better idiots. So far, the universe is winning. (Rick Cook)

    Enhanced ExtJS adapter for Adobe AIR

  6. #6
    Sencha User murrah's Avatar
    Join Date
    Oct 2007
    Location
    Katoomba, Blue Mountains, west of Sydney, Australia
    Posts
    381
    Vote Rating
    11
    murrah will become famous soon enough

      0  

    Default


    Hi Makana,

    Thanks for that. I added the save listener and repeated the test. It fires 3 times as you can see. However, the problem remains that the records are *not* in the table after the add and the proxy callback is only firing for the first Add and is, I assume, dying sometime after that. Hence, no records. The onSave data parameter is an empty array.

    This is the console output.
    PHP Code:
    onAdd membId=2114824134RZRTEST0

    onAdd membId
    =2114824301H7RTEST1

    onAdd membId
    =211482431Z2OUTEST2

    onSave membId
    =(no data)

    callback for 2114824134RZR

    onSave membId
    =(no data)

    onSave membId=(no data
    If I change the for loop to only add one record, it all works perfectly and the record is added to the table:
    PHP Code:
    onAdd membId=211081603JVOWTEST0

    callback 
    for 211081603JVOW

    onUpdate op
    =commitid=211081603JVOW

    onWrite action
    =createmembId=211081603JVOW

    onSave membId
    =211081603JVOW 
    Comparing the two, it seems that the commit handler is not being reached.

    If I add two records instead of 3 the results are slightly different in that the first record is added to the table, but the second is not. Also note that the onSave for the second record is called before the callback for the first.
    PHP Code:
    onAdd membId=212225095TBMZTEST0

    onAdd membId
    =21222512393CGTEST1

    onSave membId
    =(no data)

    callback for 212225095TBMZ

    onUpdate op
    =commitid=212225095TBMZ

    onWrite action
    =createmembId=212225095TBMZ

    onSave membId
    =212225095TBMZ 
    Finally, I tested adding two record and setting a store field with a one second defer between each operation and it all works correctly!!!! The records are in the table and the parent field's isFolder is set on it's database record (and the store too, of course).

    My test code
    PHP Code:
    // Get the selecyed tree node to add children to
    var selNode WholeMealCafe.memberTreeSelModel.getSelectedNode();
    var 
    parentId = (selNode) ? selNode.id 0;
    air.trace('sel node=' parentId)

    // Add first record
    var pk Ext.uniqueId(true);
    var 
    rec = new WholeMealCafe.memberRec({
        
    membIdpk,
        
    membParentIdparentId,
        
    membCode'TEST1',
        
    membFirstname'',
        
    membLastname'',
        
    membEmail'',
        
    isFolderfalse
    }, pk)
    rec.phantom true;
    WholeMealCafe.MemberStore.add(rec);

    // Add second record after 1 second
    (function () {
        var 
    pk Ext.uniqueId(true);
        var 
    rec = new WholeMealCafe.memberRec({
            
    membIdpk,
            
    membParentIdparentId,
            
    membCode'TEST2',
            
    membFirstname'',
            
    membLastname'',
            
    membEmail'',
            
    isFolderfalse
        
    }, pk)
        
    rec.phantom true;
        
    WholeMealCafe.MemberStore.add(rec);

        
    // Now set the isFolder field on the parent after one second
        
    (function () {
            var 
    memberRec WholeMealCafe.MemberStore.getById(parentId);
            
    air.trace('set isFolder for ' parentId)
            
    memberRec.set('isFolder'true);
        }.
    defer(1000this));
    }.
    defer(1000this)); 
    The console:
    Code:
    sel node=045860986QSRI
    
    
    
    onAdd membId=2139960144YYI, TEST1
    
    callback for 2139960144YYI
    
    onUpdate op=commit, id=2139960144YYI
    
    onWrite action=create, membId=2139960144YYI
    
    onSave membId=2139960144YYI
    
    
    
    onAdd membId=213997115W81Q, TEST2
    
    callback for 213997115W81Q
    
    onUpdate op=commit, id=213997115W81Q
    
    onWrite action=create, membId=213997115W81Q
    
    onSave membId=213997115W81Q
    
    
    
    set isFolder for 045860986QSRI
    
    onUpdate op=edit, id=045860986QSRI
    
    onUpdate op=commit, id=045860986QSRI
    
    onWrite action=update, membId=045860986QSRI
    
    onSave membId=(no data)
    So, it seems that the problem is, indeed, a timing issue.

    I am using the official air package from the downloads page.

    Cheers,
    Murray

  7. #7
    Sencha User makana's Avatar
    Join Date
    Apr 2008
    Location
    Dresden, Germany
    Posts
    527
    Vote Rating
    19
    makana has a spectacular aura about makana has a spectacular aura about

      0  

    Default


    It seems like the problem I got recently.
    All should work fine, if you do only one action (insert/update/delete), but not if you do 2 or all three at a time, right? I fixed this issue in svn already.
    I will ask Jamie again to provide a build on the download site as soon as possible.
    Programming today is a race between software engineers striving to build bigger and better іdiot-proof programs, and the universe striving to produce bigger and better idiots. So far, the universe is winning. (Rick Cook)

    Enhanced ExtJS adapter for Adobe AIR

  8. #8
    Sencha User murrah's Avatar
    Join Date
    Oct 2007
    Location
    Katoomba, Blue Mountains, west of Sydney, Australia
    Posts
    381
    Vote Rating
    11
    murrah will become famous soon enough

      0  

    Default


    Thanks Makana, that would be great! Is there somewhere I can look to see what has already been fixed and what bugs are known in the AIR adapter? I spent a lot of time trying to work out if it was a bug or if it was me. Is there a way to get early access to the current build so I can test it?

    Thanks again,
    Murray

Similar Threads

  1. Two Primary key
    By vahid4134 in forum Ext 3.x: Help & Discussion
    Replies: 0
    Last Post: 22 Dec 2009, 2:54 AM
  2. Composite primary key - N:M relationship - reader problem
    By javaman in forum Ext 3.x: Help & Discussion
    Replies: 8
    Last Post: 30 Jul 2009, 2:37 PM
  3. Inserting new nested data in to an existing Record
    By andycramb in forum Ext 2.x: Help & Discussion
    Replies: 2
    Last Post: 7 Jan 2009, 12:03 PM
  4. Ext.data.Store with composite Primary Key
    By ebald in forum Ext 2.x: Help & Discussion
    Replies: 4
    Last Post: 25 Feb 2008, 7:49 AM
  5. Inserting a record in a store and saving the data in the database
    By cadudecastroalves in forum Ext 2.x: Help & Discussion
    Replies: 0
    Last Post: 11 Feb 2008, 10:59 AM

Thread Participants: 1