0

I have a list I need to synchronize with a MySQL database. Here's an example procedure:

I have a table with metadata:

+-------+
| value |
+-------+
| test  |
| val   |
| smtn  |
| foo   |
+-------+

When my application starts these values are all loaded from the database and added to a List. Then at some point in my app, a new value is added to the list locally (without updating the database). At some other point a random value is removed from the list (also locally).

After a while the app closes and tries to synchronize the list with the database. The list might look like this at this point: ["test", "smtn", "added", "also_added"]. As you can see, some values were removed (compared to the initial table values) and some were added. None of the values changed. Only additions and removals.

Now, to synchronize the list with the database I could delete all rows and insert the updated list to the database, but this seems inefficient.

Is there a better way to do this?

XLordalX
  • 584
  • 1
  • 7
  • 25
  • If it's a list, it needs an index. Your current table is a `Set`. If you have an index, you can update changed values. If you have a `Set` you _must_ delete and re-add. See every ORM framework ever. [e.g.](https://docs.jboss.org/hibernate/stable/core.old/reference/en/html/performance-collections.html) – Boris the Spider Oct 14 '17 at 17:10
  • Possible duplicate of [On duplicate key ignore?](https://stackoverflow.com/questions/2366813/on-duplicate-key-ignore) – sskoko Oct 14 '17 at 17:11
  • 1
    The values don't change. They're either added or removed, never changed. – XLordalX Oct 14 '17 at 17:27
  • What about tracking inserts and deletes on your local list, then generate the appropriate database statements. – jeff Oct 14 '17 at 17:37

1 Answers1

0

There are two approaches that I know of those are push and pull.

For push you would have a database trigger for when that tables update and you would either push the changes or all the data to some end point your app exposes.

For pull you would have your app on some interval or action go query the database for any updates.

Both have advantages and disadvantages.

Sam Orozco
  • 1,258
  • 1
  • 13
  • 27