SQlite同步方案
我知道今天是圣诞节前夕，所以这是在线寻找铁杆程序员的最佳时机:)。
我有一个包含超过 10 K 记录的 sqlite 数据库文件，我从 mysql 数据库生成数据库，我已经按照通常的方式在我的 iphone 应用程序中构建了 sqlite 数据库。
这些记录包含有关产品及其价格、商店等的信息，这些信息当然不是静态的，我使用自动方案来填充并不断更新我的 mysql 数据库。
现在，如何使用 mysql 数据库中可用的新信息更新 iphoen app sqlite 数据库，数据库结构仍然相同，但记录包含新信息。
谢谢。 阿赫德
信息： libsqlite3.0, iPhone操作系统3.1， mysql 2005， Mac OS X 10.6.2
I know it is xmas eve, so it is a perfect time to find hardcore programers online :).
I have a sqlite db fiel that contains over 10 K record, I generate the db from a mysql database, I have built the sqlite db within my iphone application the usual way.
The records contains information about products and their prices, shops and the like, this info of course is not static, I use an automatic scheme to populate and keep updating my mysql db.
Now, how can I update the iphoen app sqlite database with the new information available in the mysql db, the db structure is still the same, but the records contains new information.
Thanks.
Ahed
info:
libsqlite3.0,
iphone OS 3.1,
mysql 2005,
Mac OS X 10.6.2
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
有一个问题你需要先回答； 如何确定 MySQL 数据库中已更改记录的集合？
或者，更具体地说，假设 MySQL 数据库处于状态 A，发生了一些事务，现在处于状态 B，您如何确定知道A和B之间发生了什么变化吗？
底线；你需要 MySQL 中的一个模式来实现这一点。一旦您回答了这个问题，您就可以回答“如何同步问题？”。
There is a question you need to answer first; How do you determine the set of changed records in your MySQL database?
Or, more specifically, given that the MySQL database is in state A, some transactions occur and now it is in state B, how do you know what changed between A and B?
Bottom line; you need a schema in MySQL that enables this. Once you have answered that question, then you can answer the "how do I sync problem?".
我有一个类似的应用程序。
我使用推送通知让我的用户知道有新的或更新的数据可用。
每次更新服务器上的记录时，我都会在记录旁边存储一个连续的记录号。
每个注册的 UDID 都有一个与其关联的“最后更新”编号，其中包含它曾经下载过的最高记录编号。
当任何给定设备获取其更新时，所有大于服务器上存储的 UDID 最后更新记录号的数据库记录都会发送到该设备。如果一切正常，则 UDID 的最后更新记录号将设置为发送的最后记录号。
如果用户认为需要将其设备同步到整个数据库，则可以选择获取所有记录并刷新其数据库。
似乎运作良好。
-t
I have a similar application.
I am using Push Notification to let my users know there is new or updated data available.
Each time a record on the server is updated, I store a sequential record-number alongside the record.
Each UDID that's registered has a "last updated" number associated with it that contains the highest record-number it has ever downloaded.
When any given device comes to get it's updates, all database records greater than the UDID's last updated record-number as stored on the server are sent to the device. If everything goes OK, the last updated record-number for the UDID is set to the last record number sent.
The user has the option to fetch all records and refresh his database if he feels any need to sync his device to the entire database.
Seems to be working well.
-t
您可以通过搜索“iphone同步”找到许多其他类似的问题：
https://stackoverflow.com/search?q=iphone +同步
我假设数据仅从 mysql 到 sqlite，而不是相反的方向。
三种是我可以想象的几种方法。第一个是重新下载
每次更新期间的整个数据库。我在下面描述的另一种方法是创建一个“日志”表来记录对主表的修改，然后在执行更新时仅下载新日志。
我将在 SQL 数据库中创建一个新的“日志”表，以记录对需要同步的表的更改。日志可以包含一个“修订”列来跟踪更改的顺序，一个“类型”列来指定它是插入、更新还是删除，一个行 ID（如果受影响的行），最后有主表中的整组列。
您可以使用存储过程作为包装器来自动创建日志条目来修改主表。
由于只有 10k 条记录，我不会期望这个日志表会增长到那么大。
然后，您可以在 sqlite 中跟踪从 mysql 下载的最新版本。要更新表，您需要下载最新更新后的所有日志条目，然后将它们应用到您的 sqlite 表。
You can find many other similar questions by searching for "iphone synchronization":
https://stackoverflow.com/search?q=iphone+synchronization
I'm going to assume that the data is going only from mysql to sqlite, and not the reverse direction.
Three are a few ways that I could imagine doing this. The first is to just redownload
the entire database during every update. Another way, which I'm describing below, would be to create a "log" table to record the modifications to your master table, and then download just the new logs when doing the update.
I would creat a new "log" table in your SQL database to log changes to the table needing synchronization. The log could contain a "revision" column to track in what order changes were made, a "type" column to specify if it was a insert, update, or or delete, a the row-id if your affected row, and finally have the entire set of columns from your master table.
You could automate the creation of log entries by using stored procedures as wrappers to modify your master table.
With only 10k records, I wouldn't expect this log table to grow to be that huge.
You would then in sqlite keep track of the latest revision downloaded from mysql. To update the table, you would download all log entries after the latest update, and then apply them to your sqlite table.