Sqlite的应用程序内数据库迁移的最佳实践 [英] Best practices for in-app database migration for Sqlite

问题描述

我正在为我的iphone使用sqlite，我预计数据库架构可能会随着时间而改变。每次成功迁移时需要注意哪些问题，命名约定和注意事项？

I am using sqlite for my iphone and I anticipate the database schema might change over time. What are the gotchas, naming conventions and things to watch out for to do a successful migration each time?

例如，我想过将一个版本附加到数据库名称（例如Database_v1）。

For example, I have thought of appending a version to the database name (e.g. Database_v1).

推荐答案

我维护一个定期需要更新sqlite数据库并将旧数据库迁移到新模式的应用程序这就是我的工作：

I maintain an application that periodically needs to update a sqlite database and migrate old databases to the new schema and here's what I do:

为了跟踪数据库版本，我使用sqlite提供的内置用户版本变量（sqlite对此变量不做任何处理，你是免费的但是请你使用它。它从0开始，您可以使用以下sqlite语句获取/设置此变量：

For tracking the database version, I use the built in user-version variable that sqlite provides (sqlite does nothing with this variable, you are free to use it however you please). It starts at 0, and you can get/set this variable with the following sqlite statements:

> PRAGMA user_version;  
> PRAGMA user_version = 1;

当应用程序启动时，我会检查当前的用户版本，应用所需的任何更改架构是最新的，然后更新用户版本。我将更新包装在一个事务中，这样如果出现任何问题，就不会提交更改。

When the app starts, I check the current user-version, apply any changes that are needed to bring the schema up to date, and then update the user-version. I wrap the updates in a transaction so that if anything goes wrong, the changes aren't committed.

为了进行模式更改，sqlite支持ALTER TABLE语法操作（重命名表或添加列）。这是一种现场更新现有表的简便方法。请参阅此处的文档： http://www.sqlite.org/lang_altertable.html 。要删除ALTER TABLE语法不支持的列或其他更改，我创建一个新表，将日期迁移到其中，删除旧表，并将新表重命名为原始名称。

For making schema changes, sqlite supports "ALTER TABLE" syntax for certain operations (renaming the table or adding a column). This is an easy way to update existing tables in-place. See the documentation here: http://www.sqlite.org/lang_altertable.html. For deleting columns or other changes that aren't supported by the "ALTER TABLE" syntax, I create a new table, migrate date into it, drop the old table, and rename the new table to the original name.

这篇关于Sqlite的应用程序内数据库迁移的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！