1

I have a (conceptual) sqlite database on a server that is meant to be consistent with the sqlite database of an Android app that I'm going to develop.

I'm using an autoincrement integer primary key for each table named some variant of _id e.g. _entry_id or _goal_id.

I'm dealing with syncing by having a 'modifications' table for each table that details what actions have occurred to which table and which entries within that table.

The user has the CHOICE to sync with the server but it isn't necessary. This point is crucial and sadly the option to force people to sync with the server whenever they use the app is not possible.

I'm having the following problem on a conceptual level:

Say the server database has one table 'entry' with the fields:

  • '_entry_id' which is an autoincrement integer primary
  • 'title' which is a text field.

The database currently has no entries.

Say there are two devices that are running the app. The app and the server use the same database schema.

Say that device1 has the entries:

  • 0, dev1entry0
  • 1, dev1entry1

If device1 decides to sync, through some unimportant wizardry the system will decide that these two entries are new and will add them to the server database, so the server database now has the entries:

  • 0, dev1entry0
  • 1, dev1entry1

Now say that device2 decides NOT TO SYNC with the server database and then creates some new entries to its database and now has the entries:

  • 0, dev2entry0
  • 1, dev2entry1

If it now decides to sync then the system will decide that these are new entries and will add them to the database so that the database now has the entries:

  • 0, dev1entry0
  • 1, dev1entry1
  • 2, dev2entry0
  • 3, dev2entry1

And device2 now has the entries:

  • 0, dev2entry0
  • 1, dev2entry1
  • 2, dev1entry0
  • 3, dev1entry1

Now, any modifications made to the entries on device2 will modify the wrong entries on the server since the primary keys are incorrect.

I've thought of a way to combat this. Since this is (as far as I can see) only an issue when inserting new entries to a table. My idea is as follows:

Assume the same entries for the server database as above
Assume that a new device, device3, has an entry:
    - 0, dev3entry0

when an insert needs to occur when syncinc
  get the id that's been assigned to device3's entry: 0
  check the id against the largest id that's already been assigned to the server's db: 3
  if 0 == 3 + 1 (the next increment to be assigned)
    then insert the new entry to the database, the keys are the same
  otherwise
    duplicate the entry in device3 so that the key == 3 + 1
    delete the original entry

With this algorithm (when you don't take into account syncing to device3 as well) you should be left with the following in the server database:

  • 0, dev2entry0
  • 1, dev2entry1
  • 2, dev1entry0
  • 3, dev1entry1
  • 4, dev3entry0

And the following in device3's database:

  • 4, dev3entry0

Meaning all primary keys are consistent.

Is this algorithm possible in sqlite?

If not, or if this algorithm isn't the best, what is another way of dealing with this problem?

I hope I've been clear enough in my explanation.

Cheers.

timsworth
  • 231
  • 2
  • 10

1 Answers1

0

Not sure if you need this answer anymore, but going by thought of helping the community, here it goes:

I'm running through a similar problem with my app. I decided to use a Composite PK, as described in this question made by @user1843640.

Although the question seems to have no proper answer, the question itself helped me thinking a way through the problem. In two weeks tops, I'll be finishing my implementation and, as it is an academic work that I'm developing, I'll make the source available publicly in GitHub. I'll come back here when it's done :)

Community
  • 1
  • 1
Felipe Pelps Benutti
  • 23
  • 4