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

[murrah]

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:

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:

var pk = Ext.uniqueId(true);
var rec = new WholeMealCafe.memberRec({
    membId: pk,
    membParentId: parentId,
    membCode: 'TEST',
    membFirstname: '',
    membLastname: '',
    membEmail: '',
    isFolder: false
}, pk)

rec.phantom = true;

WholeMealCafe.MemberStore.add(rec); 

The problem seems to be the following block in the Ext.data.SQLiteProxy.createCallback() method:

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:

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:

o = {
    records: _this.readResults(res),
    insertId: res ? res.lastInsertRowID : null,
    affectedRows: res ? res.rowsAffected : null,
    error: e.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

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

[makana]

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

[murrah]

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

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:

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(100, this));
}.defer(100, this)); 

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

[murrah]

Here is more information:

My Store:

WholeMealCafe.classMemberStore = Ext.extend(Ext.data.SQLiteStore, {
    autoSave: true,

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

WholeMealCafe.MemberStore = new WholeMealCafe.classMemberStore();

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

WholeMealCafe.MemberStore.on('write', function (store, action, result, res, rs) {
    air.trace('onWrite');
});

WholeMealCafe.MemberStore.on('update', function (store, record, operation) {
    air.trace('onUpdate ');
}); 

Here is the test of adding 3 records:

for (var i = 0; i < 3; i++) {
    var pk = Ext.uniqueId(true);
    var rec = new WholeMealCafe.memberRec({
        membId: pk,
        membParentId: parentId,
        membCode: 'TEST' + i,
        membFirstname: '',
        membLastname: '',
        membEmail: '',
        isFolder: false
    }, pk)
    rec.phantom = true;
    WholeMealCafe.MemberStore.add(rec);
}; 

Here is the console output:

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

[makana]

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.

[murrah]

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.

onAdd membId=2114824134RZR, TEST0

onAdd membId=2114824301H7R, TEST1

onAdd membId=211482431Z2OU, TEST2

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:

onAdd membId=211081603JVOW, TEST0

callback for 211081603JVOW

onUpdate op=commit, id=211081603JVOW

onWrite action=create, membId=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.

onAdd membId=212225095TBMZ, TEST0

onAdd membId=21222512393CG, TEST1

onSave membId=(no data)

callback for 212225095TBMZ

onUpdate op=commit, id=212225095TBMZ

onWrite action=create, membId=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

// 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({
    membId: pk,
    membParentId: parentId,
    membCode: 'TEST1',
    membFirstname: '',
    membLastname: '',
    membEmail: '',
    isFolder: false
}, 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({
        membId: pk,
        membParentId: parentId,
        membCode: 'TEST2',
        membFirstname: '',
        membLastname: '',
        membEmail: '',
        isFolder: false
    }, 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(1000, this));
}.defer(1000, this)); 

The console:

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

[makana]

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.

[murrah]

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