更新本地 Android 数据库 [英] Update local Android database

问题描述

我目前在 Android 上遇到 SQLite 数据库问题.

I'm currently in trouble with a SQLite database problem on Android.

我的应用程序在 apk 文件中有一个本地数据库.当应用程序启动时，它会检查新版本，并在可用时下载全新的数据库(尽管在两个数据库版本之间，变化很小).但是现在数据库太大了.所以新数据库可用时需要很长时间.那么这个问题有什么解决方案吗?

My application have an local database which inside an apk file. When the application starts it will check for the new version, and download entirely new database if available (although between two database version, changes are very little). But the database is too large now. So it takes very long time when new database available. So any solution for this problem?

推荐答案

基本思路

这就是我要做的.我在这里假设客户端应用程序不会对本地数据库进行更改(除非它下载了新版本)，因此只有几个可能的数据库版本存在(每次你一个在服务器端进行了更改).

Basic idea

Here's how I'd do it. I'm assuming here that the client app doesn't make changes to the local database (except when it downloads a new version), so that there are only a few possible versions of the database in existence (one for every time you've made a change at the server end).

1. 向每个名为 LastModified 的表添加一列，默认值为 NOW().这意味着每次向主副本添加内容时，它都会获得更新的 LastModified 设置.您必须确保您的更新(而不是插入)也更改了 LastModified 字段.
2. 在数据库中的某处(Settings 表或其他东西)存储一个字段，用于跟踪该版本的数据库在服务器上发布的日期(称之为 PublishDate).
3. 当客户端想要检查新版本时，它会将其 PublishDate 发送到服务器.然后服务器检查每个表，并找到 LastModified 出现在 PublishDate 之后的每一行.它向客户端发送 SQL 以在客户端上插入或更新这些行.它还发送新的 PublishDate，以便客户端可以在其本地副本中更新它.

1. Add a column to every table called LastModified, with a default value of NOW(). That means that every time you add something to your master copy, it'll get an updated LastModified setting. You will have to make sure your updates (rather than inserts) change the LastModified field too.
2. Store somewhere in the database (a Settings table or something) a field that tracks the date that this version of the database was published on the server (call it PublishDate).
3. When the client wants to check for a new version, it sends its PublishDate to the server. The server then checks each table, and finds every row where LastModified comes after PublishDate. It sends SQL to the client to insert or update these rows on the client. It also sends the new PublishDate so that the client can update that in its local copy.

这涉及插入和更新.它不处理删除.在您的情况下，它们可能不是问题；如果是:

This deals with inserts and updates. It doesn't deal with deletions. It might be that they aren't an issue in your case; if they are:

1. 添加一个单独的表来记录删除，您还可以在其中跟踪LastModified，以便您可以告诉客户端要删除哪些行；或者最好有一个设置，您实际上不会删除任何行，而只是将它们更新为标记为已删除".

1. Add either a separate table to log deletions, where you also track LastModified, so that you can tell the client which rows to delete; or preferably have a setup where you don't ever actually delete any rows, but just update them to be marked as "deleted".

最后，这不会处理架构更改.同样，希望这对您来说不是问题:希望您有一个稳定的架构.但是，如果您确实需要添加或删除表或索引或其他内容，则必须单独完成:

Finally, this won't handle schema changes. Again, hopefully that isn't an issue in your case: hopefully you have a stable schema. But if you do need to add or drop tables or indexes or something, that will have to be done separately:

1. 在你的master上创建一个SchemaChanges表，每当你进行结构更改时，将相关细节放入SchemaChanges表中，连同一个LastModified日期，以便您也可以根据要求将其发送给客户.如果您这样做，您需要先将架构更改发送给客户端，因为它们可能会影响其他更改的含义.

1. Create a SchemaChanges table on your master, and whenever you make structural changes, put the relevant details into the SchemaChanges table, along with a LastModified date, so that you can send this to the client on request too. If you're doing this, you'll want to send schema changes to the client first, because they might affect the meaning of other changes.

现在这样做的好处是您可以在服务器上预处理所有内容(因为只有几个版本存在).对于每个旧版本，您可以计算将旧版本升级到新版本的更改(基于上述详细信息)，然后将生成的 SQL 存储在服务器上.如果你这样做，你就不需要动态生成 SQL:当客户端发送 PublishDate 时，你只需查找你已经计算出的 SQL，该 SQL 转换了该 PublishDate 到最新版本.

Now the nice thing about doing it this way is that you can pre-process everything on the server (because there are only a few versions in existence). For every old version, you can calculate the changes (based on the details above) that would take that old version up to the new version, and then store the resulting SQL on the server. If you do that, you avoid the need for generating the SQL on the fly: when the client sends the PublishDate, you just look up the SQL you've already calculated that transforms the version from that PublishDate to the latest version.

有一种很好且简单的方法可以推动上述方案为您提供的更改，即使稍微简化不需要 LastModified 次，也不需要对现有结构进行任何更改.在服务器端，您已经拥有旧版本(因为您拥有所有旧版本)和新版本，您创建两个数据库的 SQL 转储，然后对它们运行 diff 以生成您可以发送到客户端应用程序的补丁文件.客户端应用程序将使用相同的 Java 库生成旧版本的 SQL 转储，然后将 diff 补丁应用于它以创建新版本的完整 SQL 转储.此时，它可以删除旧数据库并从 SQL 转储中创建新数据库.

There is a nice and easy way of pushing the changes that the above scheme gives you, even with a slight simplification that doesn't require LastModified times, or indeed any changes to your existing structure. At the server end, where you already have the old version (because you have all the old versions) and the new version, you create an SQL dump of both databases, and then run diff over them to generate a patch file that you can send to the client app. The client app will use the same Java library to generate the SQL dump of the old version, and will then apply the diff patch to it to create a full SQL dump for the new version. At that point, it can drop the old database and create the new one from the SQL dump.

如果更改不是大规模更改，这将非常有效(在这种情况下，您最好只推送新的 .db 文件).

This will be very efficient if the changes aren't wholesale changes (in which case you might as well just push the new .db file).

通过调用 SQLite 二进制文件来创建转储，可以很容易地做到这一点.根据 this，您需要稍微修改 Android 的方法 执行外部命令的方式.

It's fairly easy to do this by invoking the SQLite binary to create the dumps. You will need to modify the approach slightly for Android, according to this way of executing an external command.

您可以使用这个谷歌库来计算差异补丁服务端应用到客户端.

You can use this Google library to calculate the diff patches at the server end and apply them at the client end.

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