Sqlite 应用程序内数据库迁移的最佳实践

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

例如，我考虑过在数据库名称后面添加一个版本(例如 Database _ v1)。

54307 次浏览
小开

如果你改变了数据库模式和所有在 lockstep 中使用它的代码，就像在嵌入式和手机定位应用程序中可能发生的情况一样，那么问题实际上已经得到了很好的控制(没有什么比得上一个企业数据库的噩梦，这个模式迁移可能正在服务数百个应用程序——也不是所有的应用程序都在数据库管理局的控制之下; ——)。

小开
最佳答案

我维护一个需要定期更新 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;

当应用程序启动时，我检查当前用户版本，应用任何需要更新模式的更改，然后更新用户版本。我将更新包装在一个事务中，这样如果出现任何错误，就不会提交更改。

为了进行模式更改，sqlite 支持特定操作(重命名表或添加列)的“ ALTERTABLE”语法。这是就地更新现有表的简单方法。请参阅这里的文档: http://www.sqlite.org/lang_altertable.html。为了删除“ ALTER TABLE”语法不支持的列或其他更改，我创建了一个新表，将日期迁移到其中，删除旧表，并将新表重命名为原始名称。

小开

《只是好奇》给出的答案是完全正确的(你明白我的意思!)，它是我们用来跟踪当前应用程序中的数据库模式的版本。

To run through the migrations that need to occur to get user_version matching the app's expected schema version, we use a switch statement. Here's a cut-up example of what this look like in our app Strip:

- (void) migrateToSchemaFromVersion:(NSInteger)fromVersion toVersion:(NSInteger)toVersion {
// allow migrations to fall thru switch cases to do a complete run
// start with current version + 1
[self beginTransaction];
switch (fromVersion + 1) {
case 3:
// change pin type to mode 'pin' for keyboard handling changes
// removing types from previous schema
sqlite3_exec(db, "DELETE FROM types;", NULL, NULL, NULL);
NSLog(@"installing current types");
[self loadInitialData];
case 4:
//adds support for recent view tracking
sqlite3_exec(db, "ALTER TABLE entries ADD COLUMN touched_at TEXT;", NULL, NULL, NULL);
case 5:
{
sqlite3_exec(db, "ALTER TABLE categories ADD COLUMN image TEXT;", NULL, NULL, NULL);
sqlite3_exec(db, "ALTER TABLE categories ADD COLUMN entry_count INTEGER;", NULL, NULL, NULL);
sqlite3_exec(db, "CREATE INDEX IF NOT EXISTS categories_id_idx ON categories(id);", NULL, NULL, NULL);
sqlite3_exec(db, "CREATE INDEX IF NOT EXISTS categories_name_id ON categories(name);", NULL, NULL, NULL);
sqlite3_exec(db, "CREATE INDEX IF NOT EXISTS entries_id_idx ON entries(id);", NULL, NULL, NULL);


// etc...
}
}


[self setSchemaVersion];
[self endTransaction];
}
小开

IMO 的最佳解决方案是建立一个 SQLite 升级框架。我遇到了同样的问题(在 C # 世界中) ，并且我自己构建了这样的框架。你可以阅读它 给你。它的工作完美，使我(以前噩梦般的)升级工作与最小的努力在我的一边。

虽然这个库是用 C # 实现的，但是这里提供的思想在您的情况下也应该能够很好地工作。

小开

Some tips...

1)我建议将所有迁移数据库的代码放到 NSOperation 中，并在后台线程中运行它。在迁移数据库时，可以使用 spinner 显示自定义 UIAlertView。

2)确保你正在将数据库从捆绑包复制到应用的文档中，并从该位置使用它，否则你只会用每次应用更新覆盖整个数据库，然后迁移新的空数据库。

3) FMDB 很棒，但是由于某些原因，它的 ExecuteQuery 方法不能执行 PRAGMA 查询。如果希望使用 PRAGMA user _ version 检查模式版本，则需要编写自己的直接使用 sqlite3的方法。

4)这个代码结构将确保你的更新按顺序执行，并且所有的更新都被执行，不管用户在应用程序更新之间花费了多长时间。它可以进一步重构，但这是一种非常简单的看待它的方式。这个方法可以在每次实例化数据单例时安全地运行，并且只需要一个很小的 db 查询，如果正确地设置了数据单例，每个会话只需要执行一次。

- (void)upgradeDatabaseIfNeeded {
if ([self databaseSchemaVersion] < 3)
{
if ([self databaseSchemaVersion] < 2)
{
if ([self databaseSchemaVersion] < 1)
{
// run statements to upgrade from 0 to 1
}
// run statements to upgrade from 1 to 2
}
// run statements to upgrade from 2 to 3


// and so on...


// set this to the latest version number
[self setDatabaseSchemaVersion:3];
}
}
小开

让我与 FMDB 和 MBProgressHUD 分享一些迁移代码。

下面是如何读写模式版本号(这可能是模型类的一部分，在我的例子中是一个名为 Database 的单例类) :

- (int)databaseSchemaVersion {
FMResultSet *resultSet = [[self database] executeQuery:@"PRAGMA user_version"];
int version = 0;
if ([resultSet next]) {
version = [resultSet intForColumnIndex:0];
}
return version;
}


- (void)setDatabaseSchemaVersion:(int)version {
// FMDB cannot execute this query because FMDB tries to use prepared statements
sqlite3_exec([self database].sqliteHandle, [[NSString stringWithFormat:@"PRAGMA user_version = %d", DatabaseSchemaVersionLatest] UTF8String], NULL, NULL, NULL);
}

下面是延迟打开数据库的 [self database]方法:

- (FMDatabase *)database {
if (!_databaseOpen) {
_databaseOpen = YES;


NSString *documentsDir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *databaseName = [NSString stringWithFormat:@"userdata.sqlite"];


_database = [[FMDatabase alloc] initWithPath:[documentsDir stringByAppendingPathComponent:databaseName]];
_database.logsErrors = YES;


if (![_database openWithFlags:SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FILEPROTECTION_COMPLETE]) {
_database = nil;
} else {
NSLog(@"Database schema version is %d", [self databaseSchemaVersion]);
}
}
return _database;
}

下面是从视图控制器调用的迁移方法:

- (BOOL)databaseNeedsMigration {
return [self databaseSchemaVersion] < databaseSchemaVersionLatest;
}


- (void)migrateDatabase {
int version = [self databaseSchemaVersion];
if (version >= databaseSchemaVersionLatest)
return;


NSLog(@"Migrating database schema from version %d to version %d", version, databaseSchemaVersionLatest);


// ...the actual migration code...
if (version < 1) {
[[self database] executeUpdate:@"CREATE TABLE foo (...)"];
}


[self setDatabaseSchemaVersion:DatabaseSchemaVersionLatest];
NSLog(@"Database schema version after migration is %d", [self databaseSchemaVersion]);
}

下面是调用迁移的根视图控制器代码，使用 MBProgressHUD 显示进度框:

- (void)viewDidAppear {
[super viewDidAppear];
if ([[Database sharedDatabase] userDatabaseNeedsMigration]) {
MBProgressHUD *hud = [[MBProgressHUD alloc] initWithView:self.view.window];
[self.view.window addSubview:hud];
hud.removeFromSuperViewOnHide = YES;
hud.graceTime = 0.2;
hud.minShowTime = 0.5;
hud.labelText = @"Upgrading data";
hud.taskInProgress = YES;
[[UIApplication sharedApplication] beginIgnoringInteractionEvents];


[hud showAnimated:YES whileExecutingBlock:^{
[[Database sharedDatabase] migrateUserDatabase];
} onQueue:dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_BACKGROUND, 0) completionBlock:^{
[[UIApplication sharedApplication] endIgnoringInteractionEvents];
}];
}
}
小开

使用基于 SQL 的迁移列表创建 /migrations文件夹，其中每个迁移看起来如下:

/migrations/001-categories.sql

-- Up
CREATE TABLE Category (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO Category (id, name) VALUES (1, 'Test');


-- Down
DROP TABLE User;

/migrations/002-posts.sql

-- Up
CREATE TABLE Post (id INTEGER PRIMARY KEY, categoryId INTEGER, text TEXT);


-- Down
DROP TABLE Post;

2. Create db table containing the list of applied migrations, for example:

CREATE TABLE Migration (name TEXT);

3. Update application bootstrap logic so that before it starts, it grabs the list of migrations from the /migrations folder and runs the migrations that have not yet been applied.

Here is an example implemented with JavaScript: 用于 Node.js 应用程序的 SQLite 客户端

小开

对于.net，你可以使用 lib:

实体框架核心。 Sqlite。迁移

它很简单，因此对于任何其他平台，您都可以轻松地实现与 lib 中相同的行为。

小开

In my article SQLite 的简单声明性模式迁移 we work out the schema changes automatically by creating a pristine in-memory database, and comparing the schema against your current database by querying the "sqlite_schema" tables from both. Then we follow the 12 step procedure from the SQLite documentation to safely modify the tables.

您可以按照自己的喜好来定义模式(ORM 或者普通的 SQL“ CREATE TABLE”语句等) ，只要您可以使用它来创建一个新的内存数据库。这意味着您只需要在一个地方维护模式，并且在应用程序启动时自动应用更改。

当然也有局限性ーー特别是它不处理数据迁移，只处理模式迁移; 新列必须允许 null 或指定默认值。但总的来说，和它一起工作是一种乐趣。


提交答案