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屋！