执行同步/更新SQLite数据库 [英] Synchronise/update sqlite databases

问题描述

我们已经有了一个Android应用程序和iPhone应用程序（相同的功能）使用SQLite，让本地数据存储。该应用程序最初都没有数据，然后在他们收到的数据从远程服务器上的第一次运行，并将其存储在一个SQLite数据库。 SQLite数据库是由服务器创建和应用程序下载它作为一个文件，然后将其用于购买应用程序。该数据库文件不是很大按照今天的标准，但不是一个很小的一个哪一方面 - 约5-6 MB

We've got an android app and an iPhone app (same functionality) that use sqlite for local data storage. The apps initially come with no data, then on the first run they receive data from a remote server and store it in a sqlite database. The sqlite database is created by the server and the apps download it as one file, which is then used buy the apps. The database file is not very large by today's standards, but not a tiny one either - about 5-6 MB.

现在，曾经在一段时间，这些应用程序需要从服务器刷新数据。有几种方法我能想到的：

Now, once in a while, the apps need to refresh the data from the server. There a few approaches I can think of:

1. 从服务器上下载新的完整数据库，并取代现有的。这一次听起来像对付这个问题如果不是重复5-6 MB的下载最简单的方法。该应用程序做提示用户是否要下载更新，所以这可能不是太大的问题。

1. Download a new full database from the server and replace the existing one. This one sounds like the simplest way to deal with the problem were it not for a repeated 5-6 MB downloads. The apps do prompt the user whether they want to download the updates, so this may not be too much of a problem.

从服务器上下载增量数据库，只包含了新的/修改的记录，并在什么记录删除某些形式的信息。这将导致一个更小的下载大小，但在客户端的工作更加复杂。我需要阅读一个数据库，并根据所读，更新另一个。尽我所知，有没有办法使用SQLite像做插入db1.table1（SELECT * FROM db2.table1），其中 DB1 和 DB2 都含有相同结构的表1 双SQLite数据库。 （完整的SQLite数据库，其中包含可能包含约500个记录左右，最大的一个约10个表。）

Download a delta database from the server, containing only the new/modified records and in some form information about what records to delete. This would lead to a much smaller download size, but the work on the client side is more complicated. I would need to read one database and, based on what is read, update another one. To the best of my knowledge, there's not way with sqlite to do something like insert into db1.table1 (select * from db2.table1) where db1 and db2 are two sqlite databases containing table1 of the same structure. (The full sqlite database contains about 10 tables with the largest one probably containing about 500 records or so.)

在其他格式（JSON，XML等）中的数据下载三角洲和使用该信息来更新应用程序数据库。和以前一样：不要在服务器端多大的问题，更小的下载大小比完整的数据库，但相当痛苦的过程做了更新

Download delta of the data in some other format (json, xml, etc.) and use this info to update the database in the app. Same as before: not to much problem on the server side, smaller download size than the full database, but quite a painful process to do the updates.

这三种方法，你推荐的？或者，也许还有另一种方式，我错过了吗？

Which of the three approaches you recommend? Or maybe there's yet another way that I missed?

在预先感谢。

推荐答案

经过一番考虑和尝试 - 和 - 错误，我去了选项（2）和组合（3）。

After much considerations and tries-and-errors, I went for a combination of options (2) and (3).

1. 如果没有数据是present的话，那么该应用程序从服务器下载完整的数据库文件。

1. If no data is present at all, then the app downloads a full database file from the server.

如果数据是present和更新是必需的，应用程序下载的从服务器中某些数据库。并检查一个特定值的在一个特定的表的内容。该值将说明新数据库是否要取代原有的或是否包含删除/更新/插入

If data is present and an update is required, the app downloads some database from the server. And checks the content of a particular value in a particular table. That value will state whether the new database is to replace the original or whether it contains deletions/updates/inserts

这真可谓是最快的方式（性能明智），并留下所有繁重（确定是否把一切都在一个数据库或只是一个更新）到服务器。此外，使用这种方法，如果我需要修改算法，比如，随时下载完整的数据库，它只会在服务器上的改变，而不需要重新编译和重新分配的应用程序。

This turns out to be the fastest way (performance-wise) and leaves all the heavy lifting (determining whether to put everything into one database or just an update) to the server. Further, with this approach, if I need to modify the algorithm to, say, always download the full database, it would only be a change on the server without the need to re-compile and re-distribute the app.

这篇关于执行同步/更新SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！