no diff event triggered when a row is deleted from table

[sallespro]

i have a mysql instance running and the example https://github.com/numtel/reactive-mysql-example in place. whenever i execute an INSERT the event is shown properly, but when a row is DELETED it doesn't.

[numtel]

Works fine for me. I have also just now updated the example to use the latest version of this package, 1.0.3.

[jbaxleyiii]

@numtel can you go into more details on what the diff.removed values are in reference to? When a DELETE happens on a row, I get back an array of {_index: <value>}. What does the _index value represent?

Also, thanks for the awesome work on this package!

[numtel]

_index is the row number in the result set, starting with 1
On Oct 19, 2015 6:34 PM, "jbaxleyiii" [email protected] wrote:

@numtel https://github.com/numtel can you go into more details on what
the diff.removed values are in reference to? When a DELETE happens on a
row, I get back an array of {_index: }. What does the _index value
represent?

Also, thanks for the awesome work on this package!

—
Reply to this email directly or view it on GitHub
#19 (comment)
.

[jbaxleyiii]

@numtel thanks for the quick response. I'm having a hard time understanding how to get the deleted row.

For instance, this is what I get when I do a lookup (filtered to just show unique ID and the _index value).

// diff (filtered)
{ 
  removed: [],
  moved: null,
  copied: null,
  added: [ 
    { entry_id: 1957, _index: 1 },
    { entry_id: 913, _index: 2 } 
  ] 
}

// rows (filtered to just show entry_ids)
[ 1957, 913 ]

After I do a DELETE on the row for 1957, this is the return

// diff (filtered)
{
  removed: [
     { _index: 2 }
  ],
  moved: [ 
    { old_index: 2, new_index: 1 }
  ],
  copied: null,
  added: [
     { entry_id: 9957, _index: 2 } 
  ]
 }

// rows (filtered to just show entry_ids)
[ 913, 9957 ]

What I'm wondering is how to know what entry_id was deleted from the second update. Thanks for any and all help!

If I add more results in (currently limited to 2), the deleted row _index is always the length of the total result. (e.g 2 if there are 2 results, 1000 if there are 1000 results)

[Koleok]

I am dealing with this currently as well. Technically if you know the index of what has been deleted and you have a clone of the database that can still be queried, you could run

SELECT * FROM <table> LIMIT <index-1>, <index>

(where <index> is the returned value from the live-query delete event) and get the record, from which you could get whatever other data you need, buuuut you obviously can't run that query against the db with the live connection because the record you care about was just deleted.

In my case, I am using this as a way to keep a mysql database and a graph database in sync without polling every few seconds, it works with the exception of DELETE operations for this reason. In the graph database (orientDB) there is no analogous concept of index or row number like in mysql. I could probably find a way to store mysql_index: this as a property during writes, or do some math to figure it out on the fly, but it seems like a lot of tangential effort for something that should be very easy.

Any suggestions?

[jbaxleyiii]

@Koleok We ended up spying on the condition field to see deleted items in order to trigger a remove. See this https://github.com/NewSpring/heighliner-expression-engine/blob/master/lib/sync.js#L61-L74

[wj32]

I've forked mysql-live-select and meteor-mysql and completely changed the interface so that diffing is performed with respect to keys. See these:

https://github.com/wj32/mysql-live-select
https://github.com/wj32/meteor-mysql
https://atmospherejs.com/wj32/mysql

[numtel]

Interesting. Are you going to update the test cases? It looks like you are now able to use a minimongo collection on the client?

I've started a fork of pg-live-select that will unify the postgres and mysql interfaces.

https://github.com/numtel/pg-live-select/tree/LiveSelectBase

The meteor package will then be a generic subscribed array.

https://github.com/numtel/meteor-subscribed-array

I haven't got the diff algorithm moved to the subscribed array repo yet. The plan is to support diff algorithms pluggable by further packages.

[wj32]

I was planning to update the test cases but I ran into some problems trying to get nodeunit working properly. And yes, you now subscribe by creating a Mongo.Collection on the client and using Meteor.subscribe.

[Koleok]

@wj32 that is pretty nice, moves it more in the direction of db swapping without changing application code

[Fabbok1x]

Neverminded.