Re-delete Bug in Sync Module

[MarkMYoung]

Successive deletes produce redundant entries in the _SyncRemovedObject table.

var SongEntity = persistence.define( 'Song', 
{ 
    number:"INT", 
    artist:"TEXT", 
    title:"TEXT", 
    duration:"INT", 
}); 
SongEntity.index(['number'], {unique:true}); 
SongEntity.enableSync( 'server/synchronize.php?entity=Song' );
persistence.schemaSync( function()
{
    for( var i = 0; i < 4; ++i )
    {
        var songEntity = new SongEntity({ ... }); 
        persistence.add( songEntity ).flush( function()
        {
            persistence.remove( songEntity ).flush();
        });
    }
});

 CREATE TABLE IF NOT EXISTS `Song` (`number` INT, `artist` TEXT, 
 `title` TEXT, `duration` INT, `_lastChange` BIGINT, `id` VARCHAR(32) 
 PRIMARY KEY) null 
 persistence.store.websql.js:78CREATE UNIQUE INDEX IF NOT EXISTS 
 `Song__number` ON `Song` (`number`) null 
 persistence.store.websql.js:78CREATE TABLE IF NOT EXISTS 
 `_SyncRemovedObject` (`entity` VARCHAR(255), `objectId` VARCHAR(32), 
 `id` VARCHAR(32) PRIMARY KEY) null 
 persistence.store.websql.js:78CREATE TABLE IF NOT EXISTS `_Sync` 
 (`entity` VARCHAR(255), `localDate` BIGINT, `serverDate` BIGINT, 
 `serverPushDate` BIGINT, `id` VARCHAR(32) PRIMARY KEY) null 
persistence.store.websql.js:78SELECT `root`.id AS Song_id, 
 `root`.`number` AS `Song_number`, `root`.`artist` AS `Song_artist`, 
 `root`.`title` AS `Song_title`, `root`.`duration` AS `Song_duration`, 
 `root`.`_lastChange` AS `Song__lastChange` FROM `Song` AS `root` 
 WHERE 1=1 ORDER BY `Song_artist` ASC, `Song_title` ASC [] 

One record removed first time, one _SyncRemovedObject record created:

 persistence.store.websql.js:78INSERT INTO `Song` (`_lastChange`, 
 `number`, `artist`, `title`, `duration`, id) VALUES (?, ?, ?, ?, ?, ?) 
 [1326310366172, 0, "", "", 0, "C26F48BB016F46CB8B7DC618DDDD0AA6"] 
 persistence.store.websql.js:78DELETE FROM `Song` WHERE id = 
 '6B7496D2C97C41C2AAEF69E0CDBD406C' null 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "6B7496D2C97C41C2AAEF69E0CDBD406C", 
 "EAD416C0FB9446DC8059211501C09BE6"] 

One record removed second time, two _SyncRemovedObject records created:

 persistence.store.websql.js:78INSERT INTO `Song` (`_lastChange`, 
 `number`, `artist`, `title`, `duration`, id) VALUES (?, ?, ?, ?, ?, ?) 
 [1326310369167, 0, "", "", 0, "712CDFFE8A13430083FBE81A0A747CA7"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "6B7496D2C97C41C2AAEF69E0CDBD406C", 
 "01EEC8F8FD7C40B28A1411A68B18C261"] 
 persistence.store.websql.js:78DELETE FROM `Song` WHERE id = 
 '8399A07307C14CA3BD03440D0C5E0885' null 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "6B7496D2C97C41C2AAEF69E0CDBD406C", 
 "B539AD905DE7444C813E7D5D24648AEE"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "8399A07307C14CA3BD03440D0C5E0885", 
 "3E13FD0DC41E4B51930D117239BEAD6A"] 

One record removed third time, three _SyncRemovedObject records created:

 persistence.store.websql.js:78INSERT INTO `Song` (`_lastChange`, 
 `number`, `artist`, `title`, `duration`, id) VALUES (?, ?, ?, ?, ?, ?) 
 [1326310371783, 0, "", "", 0, "AA62B15DF9184F578C767718650E49CA"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "6B7496D2C97C41C2AAEF69E0CDBD406C", 
 "9756633B10C7459A8043C1A109AC2A1D"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "8399A07307C14CA3BD03440D0C5E0885", 
 "27B18BC25F6346A983088B1BC6D5E48A"] 
 persistence.store.websql.js:78DELETE FROM `Song` WHERE id = 
 '85090A8ACA834991973F960D0F1260C4' null 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "6B7496D2C97C41C2AAEF69E0CDBD406C", 
 "113C466AE36D4C8C87266830CFB79938"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "8399A07307C14CA3BD03440D0C5E0885", 
 "DE309C861070402F92BBB826C026F5F2"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "85090A8ACA834991973F960D0F1260C4", 
 "FF886DECF8B3467D955D5891D7AC0E83"] 

One record removed fourth time, four _SyncRemovedObject records created:

 persistence.store.websql.js:78INSERT INTO `Song` (`_lastChange`, 
 `number`, `artist`, `title`, `duration`, id) VALUES (?, ?, ?, ?, ?, ?) 
 [1326310374808, 0, "", "", 0, "F03B48F5CCF54849AA48225FA6F6726B"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "6B7496D2C97C41C2AAEF69E0CDBD406C", 
 "AD7CA02039CD4D44885E060BEE84A831"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "8399A07307C14CA3BD03440D0C5E0885", 
 "667F1EB1979C401190A7DAFA397608D0"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "85090A8ACA834991973F960D0F1260C4", 
 "A4FC3E8A7BA4439B937B8492BBD0F947"] 
 persistence.store.websql.js:78DELETE FROM `Song` WHERE id = 
 'CE9994918FAD4AFF87ED70D53344270C' null 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "6B7496D2C97C41C2AAEF69E0CDBD406C", 
 "30FF2E3B9ACB4B1B823D06F87A23A627"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "8399A07307C14CA3BD03440D0C5E0885", 
 "38B3DF8EEBE94270825547C7CB4DF513"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "85090A8ACA834991973F960D0F1260C4", 
 "1C65BF43CDFF428B92580DA73F18EE12"] 
 persistence.store.websql.js:78INSERT INTO `_SyncRemovedObject` 
 (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Song", 
 "CE9994918FAD4AFF87ED70D53344270C", 
 "5D27169751214472820C4E1ED793F974"] 

[rhuitl]

I have the same problem, did you find a solution?

I start with a new database, insert a couple of items, and then delete the first item:

INSERT INTO `_SyncRemovedObject` (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Device", "5CD42CA1AE7C4868BABDBE48750CB6AA", "3F269763244B4B889AFFA05AB0CF54D8"]
DELETE FROM `Device` WHERE id = '5CD42CA1AE7C4868BABDBE48750CB6AA' null

Then delete the second item:

INSERT INTO `_SyncRemovedObject` (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Device", "5CD42CA1AE7C4868BABDBE48750CB6AA", "EA0D44D922384C5486C8B6D1340C5B4D"]
INSERT INTO `_SyncRemovedObject` (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Device", "59DC76C346FE46E7806963F2A14B7CEF", "7C2443ED24494E8A81AC56ECB34C7E87"]
DELETE FROM `Device` WHERE id = '59DC76C346FE46E7806963F2A14B7CEF' null`

And so on, every time I delete an item, the previously deleted items are added with a different id than the ones before.

[MarkMYoung]

Okay, I took some time today and think I've tracked down the problem. When I got really close to the issue, I added a unique constraint to persistence.sync.RemovedObject.

persistence.sync.RemovedObject.index(['entity', 'objectId'], {unique:true});

I don't think that's the real solution though. I think in persistence.sync.js, almost at the very bottom, where it says

if(meta.enableSync) {
  session.add(new persistence.sync.RemovedObject({entity: rec.entity, objectId: rec.id}));
}

It should say something like

if(meta.enableSync
  && !(rec.id in persistence.trackedObjects
    && persistence.trackedObjects[rec.id]._type === rec.entity
  )
) {
  session.add(new persistence.sync.RemovedObject({entity: rec.entity, objectId: rec.id}));
}

The issue is that persistence.add doesn't know to inspect RemovedObjects' meta data to determine whether it's already been added (and I don't think persistence.add should be specialized to inspect that). I don't have a good place to test this. Please test this (the modified if-statement, not the unique index) and let me/us know whether it worked.

[rhuitl]

Nope, doesn't work. With this change, the object is not added to the _SyncRemovedObject list when flush() is called, as the object is still in the trackedObjects list. When I call flush() a second time, the object is gone and the _SyncRemovedObject item is added. However, subsequent deletes of other items still insert more and more RemovedObjects:

remove Device1
flush 1
DELETE FROM `Device` WHERE id = 'FDE4C0BB2D62425E97A521871F4A8F20' null
flush 2
INSERT INTO `_SyncRemovedObject` (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Device", "FDE4C0BB2D62425E97A521871F4A8F20", "7E41D8832D694C258F9C0D5A0148CDBE"]

remove Device2
flush 1
INSERT INTO `_SyncRemovedObject` (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Device", "FDE4C0BB2D62425E97A521871F4A8F20", "93B1E54354CB4CF2813989C73AE2F0A8"]
DELETE FROM `Device` WHERE id = 'CEDB48BE98874AC7869B5309315E1453' null
flush 2
INSERT INTO `_SyncRemovedObject` (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Device", "FDE4C0BB2D62425E97A521871F4A8F20", "2D71F65A8F6844D995E50D9F945C981B"]
INSERT INTO `_SyncRemovedObject` (`entity`, `objectId`, id) VALUES (?, ?, ?) ["Device", "CEDB48BE98874AC7869B5309315E1453", "C5AE9521E0024CF693B04A9DDC043CA2"]

I noticed that persistence.trackedObjects[?]._data.objectId equals rec.id for the _SyncRemovedObject types. Could we check on that to avoid duplicating the items?

persistence.trackedObjects
Object
  (...)
  C5AE9521E0024CF693B04A9DDC043CA2: Entity
    _data: Object
      entity: "Device"
      objectId: "CEDB48BE98874AC7869B5309315E1453"
  _data_obj: Object
  _dirtyProperties: Object
  _new: false
  _session: Object
  _type: "_SyncRemovedObject"
  id: "C5AE9521E0024CF693B04A9DDC043CA2"

[pixelcort]

I think I fixed this by clearing out objectsRemoved after using it in this function.