Flutter数据库迁移插件sqflite_migration_plan的使用

简介

sqflite_migration_plan 是一个用于管理 Flutter 应用中 sqflite 数据库迁移的插件。它提供了灵活的升级和降级功能，允许你通过简洁且易读的语法来修改表结构、插入初始数据等。

快速开始

定义迁移计划

首先，你需要定义一个 MigrationPlan，其中包含每个版本的迁移操作。以下是一个示例：

import 'package:sqflite_migration_plan/migration/sql.dart';
import 'package:sqflite_migration_plan/sqflite_migration_plan.dart';

final _table = 'my_table';
final _columnId = {'name': 'id', 'def': 'INTEGER PRIMARY KEY AUTOINCREMENT'};
final _columnName = {'name': 'name', 'def': 'TEXT NOT NULL'};
final _columnDesc = {'name': 'desc', 'def': 'TEXT NOT NULL'};
final _columnHome = {'name': 'home', 'def': 'TEXT'};

MigrationPlan myMigrationPlan = MigrationPlan({
  2: [
    SqlMigration('''CREATE TABLE $_table (
        ${_columnId['name']} ${_columnId['def']},
        ${_columnName['name']} ${_columnName['def']},
        ${_columnDesc['name']} ${_columnDesc['def']}
        )''',
        reverseSql: 'DROP TABLE $_table')
  ],
  3: [
    Migration(Operation((db) async {
      _insertRecord(Entity(null, "Mordecai", "Pitcher"), db);
      _insertRecord(Entity(null, "Tommy", "Pitcher"), db);
      _insertRecord(Entity(null, "Max", "Center Field"), db);
    }), reverse: Operation((db) async => db.execute('DELETE FROM $_table')))
  ],
  4: [
    AddColumnMigration(_table, _columnHome['name']!, _columnHome['def']!,
        [_columnId, _columnName, _columnDesc]),
    Migration.fromOperationFunctions((db) async =>
        db.execute('UPDATE $_table SET ${_columnHome['name']} = \'Terre Haute\''))
  ]
});

Future<void> _insertRecord(Entity e, Database db) async {
  return db.insert(
    _table,
    {_columnName['name']!: e.name, _columnDesc['name']!: e.position},
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
}

打开数据库

在打开数据库时，将迁移计划传递给 onCreate、onUpgrade 和 onDowngrade 参数：

import 'package:flutter/material.dart';
import 'package:path/path.dart' show join;
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

class MyRepository {
  static final _databaseVersion = 4;
  static final _resetDB = false;
  static final _databaseName = "my_database.db";

  MyRepository._privateConstructor();

  static final MyRepository instance = MyRepository._privateConstructor();

  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    if (_resetDB)
      await deleteDatabase(join(documentsDirectory.path, _databaseName));

    try {
      await Sqflite.setDebugModeOn(true);
      Database db = await openDatabase(
        join(documentsDirectory.path, _databaseName),
        version: _databaseVersion,
        onCreate: myMigrationPlan,
        onUpgrade: myMigrationPlan,
        onDowngrade: myMigrationPlan,
      );

      int currentVersion = await db.getVersion();
      print("Database opened at version $currentVersion");

      // 检查迁移结果
      SetColumnDefaultOperation updateOp =
          myMigrationPlan[4][1].forward as SetColumnDefaultOperation;
      print("Num records set to initial value: ${updateOp.result ?? 0}");

      return db;
    } on DatabaseMigrationException catch (err) {
      print("Migration failed at v${err.problemVersion}: ${err.cause}");
      // 处理迁移错误
      MigrationPlan recoveryCourse = MigrationPlan({
        _databaseVersion: [Migration(Operation(noop))]
      });

      Database db = await openDatabase(
        join(documentsDirectory.path, _databaseName),
        version: _databaseVersion,
        onCreate: recoveryCourse,
        onUpgrade: recoveryCourse,
        onDowngrade: recoveryCourse,
      );
      print("Database version forcibly set to $_databaseVersion");
      return db;
    } catch (err) {
      print("Unknown Error opening database: $err");
    }
  }

  Future<List<Entity>> getEntities() async {
    Database db = await database;
    final List<Map<String, dynamic>> maps = await db.query(_table);

    return List.generate(maps.length, (i) {
      return Entity(
        maps[i][_columnId['name']],
        maps[i][_columnName['name']],
        maps[i][_columnDesc['name']],
        maps[i][_columnHome['name']],
      );
    });
  }
}

实体类

定义一个简单的实体类来表示表中的记录：

class Entity {
  final int? id;
  final String name;
  final String position;
  final String? hometown;

  Entity(this.id, this.name, this.position, [this.hometown]);
}

使用示例

在 Flutter 应用中使用上述代码：

import 'package:flutter/material.dart';
import 'package:sqflite_migration_plan_example/repository.dart';

void main() {
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: MyHomePage(title: 'Flutter Demo Home Page'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  final String title;

  MyHomePage({Key? key, this.title = "My App"}) : super(key: key);

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  @override
  Widget build(BuildContext context) {
    // 获取数据库中的实体
    Future<List<Entity>> _entities = Future.delayed(
        Duration(milliseconds: 500), MyRepository.instance.getEntities);

    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Center(
        child: FutureBuilder<List<Entity>>(
          future: _entities,
          builder: (BuildContext context, AsyncSnapshot<List<Entity>> snapshot) {
            List<Widget> children;
            if (snapshot.hasData) {
              children = <Widget>[
                if ((snapshot.data?.length ?? 0) > 0)
                  Expanded(
                    child: ListView.separated(
                      padding: const EdgeInsets.all(24),
                      itemCount: snapshot.data?.length ?? 0,
                      itemBuilder: (BuildContext context, int index) {
                        return Container(
                          padding: EdgeInsets.all(8),
                          color: Colors.blue,
                          child: Center(
                            child: Column(
                              children: [
                                Text(
                                  snapshot.data![index].name,
                                  textScaleFactor: 1.3,
                                  style: TextStyle(color: Colors.white),
                                ),
                                Row(
                                  mainAxisAlignment: MainAxisAlignment.spaceBetween,
                                  children: [
                                    Container(
                                      padding: EdgeInsets.all(4),
                                      color: Colors.amberAccent,
                                      child: Text(
                                        'Position: ${snapshot.data![index].position}',
                                      ),
                                    ),
                                    if (snapshot.data![index].hometown != null)
                                      Container(
                                        padding: EdgeInsets.all(4),
                                        color: Colors.tealAccent,
                                        child: Text(
                                          'Home: ${snapshot.data![index].hometown}',
                                          textAlign: TextAlign.right,
                                        ),
                                      ),
                                  ],
                                ),
                              ],
                            ),
                          ),
                        );
                      },
                      separatorBuilder: (BuildContext context, int index) => const Divider(),
                    ),
                  )
                else
                  Text("Database is empty")
              ];
            } else if (snapshot.hasError) {
              children = <Widget>[
                Icon(
                  Icons.error_outline,
                  color: Colors.red,
                  size: 60,
                ),
                if (snapshot.error is DatabaseException &&
                    (snapshot.error as DatabaseException).isNoSuchTableError())
                  Padding(
                    padding: const EdgeInsets.only(top: 16),
                    child: Text('Error: Table does not exist!'),
                  )
                else
                  Padding(
                    padding: const EdgeInsets.only(top: 16),
                    child: Text('Error: ${snapshot.error}'),
                  )
              ];
            } else {
              children = <Widget>[
                SizedBox(
                  child: CircularProgressIndicator(),
                  width: 60,
                  height: 60,
                ),
                const Padding(
                  padding: EdgeInsets.only(top: 16),
                  child: Text('Awaiting result...'),
                )
              ];
            }
            return Center(
              child: Column(
                mainAxisAlignment: MainAxisAlignment.center,
                crossAxisAlignment: CrossAxisAlignment.center,
                children: children,
              ),
            );
          },
        ),
      ),
    );
  }
}

概念

MigrationPlan 和 Migration

• MigrationPlan：一个映射，键为数据库版本，值为该版本对应的迁移操作列表。
• Migration：一对函数，一个用于前向操作（如创建表），另一个用于反向操作（如删除表）。

构建迁移计划

通过构造函数创建 MigrationPlan，并关联每个版本的迁移操作列表。每个迁移操作可以使用构造函数或自定义类来定义。

错误处理

• Throw：默认策略，捕获异常并封装在 DatabaseMigrationException 中。
• Ignore：忽略异常，继续执行后续操作。

降级

降级操作应尽量避免，但在开发过程中可能有用。例如，快速切换数据库版本进行测试。

未来增强

未来可能会增加更多常见的迁移操作，如添加列、重命名列/表等。

希望这个示例能帮助你更好地理解和使用 sqflite_migration_plan 插件。如果有任何问题或建议，欢迎随时提出！