Amiga.org
Coffee House => Coffee House Boards => CH / Science and Technology => Topic started by: motorollin on November 04, 2009, 05:31:55 PM
-
Hi all
I needed an app to help me manage my (ever-expanding) to-do list, but none of the existing apps quite worked how I wanted. So I wrote my own. It currently runs off the MySQL database on my MacBook, which is fine.
I also want to write a web front-end so I can view and manage my to-do list on my iPhone. This means I need to host a copy of the database on my Web host's MySQL server, but I also want to keep the local copy on my MacBook so I can still use the application when I don't have an Internet connection.
So, I'm looking for ideas on how to keep the data synchronised. Specifically, I'm having trouble working out how the rows would be matched in order to compare them and then update one or the other. Perhaps an example would help. My table has 5 columns:
id (integer, primary key)
itemname (varchar)
duedate (integer)
note (varchar)
lastmodified (datetime)
Lets say that both copies of the database contain identical data comprising 5 rows. On my MacBook, I add one item (id=6). Later, on my iPhone, I add a different item (id=6 again). When I next go online with my MacBook, the two new entries both have the same value for id, but the one created on the iPhone is more recent, so would overwrite the one created on the MacBook. In actual fact, I want to keep them both.
Any thoughts on how I could get around this?
-
Sounds like you need MySQL cluster :lol:
Seriously though, don't ever run two concurrent modifiable instances of the same database if you intend to synchronise them, it really isn't a good idea.
In fact, what you could to do is forego the primary key alltogether on these tables and rely instead on a non-unique index. You might capture some other data (an identifier for the source of the row, eg your phone, macbook etc) that in conjunction with the non-unique index is used as a unique index.
-
Sounds like you need MySQL cluster :lol:
Overkill? ;)
Seriously though, don't ever run two concurrent modifiable instances of the same database if you intend to synchronise them, it really isn't a good idea.
I'm assuming it's at least possible, since this happens all the time with things like PDAs, which can have calendar data on both the PDA and the user's computer and then synchronise them. Maybe they use the solution you posted below...
In fact, what you could to do is forego the primary key alltogether on these tables and rely instead on a non-unique index. You might capture some other data (an identifier for the source of the row, eg your phone, macbook etc) that in conjunction with the non-unique index is used as a unique index.
That could work. I need to work through an example to make sure I understand the logic behind it. Would you then store a log of all of the MySQL queries carried out, and then run them on the other database when you sync?