Flutter - 集成三方库:数据库(sqflite)

数据库

$ flutter pub add sqlite
$ flutter pub get

$ flutter run

运行失败，看是编译报错,打开Xcode工程 ⌘ + B 编译

对比 GSYGithubAppFlutter 的Xcode工程Build Phases > [CP] Embed Pods Frameworks 有sqfite.framework。本地默认的Flutter工程默认未生成Podfile

然后查看 GSYGithubAppFlutter

...
require File.expand_path(File.join('packages', 'flutter_tools', 'bin', 'podhelper'), flutter_root)

flutter_ios_podfile_setup

target 'Runner' do
  use_frameworks!
  use_modular_headers!

  flutter_install_all_ios_pods File.dirname(File.realpath(__FILE__))
end
...

看代码是引入了Flutter提供的工具的,从flutter的安装目录下找到podhelper.rb这个文件

# 方法: flutter_install_all_ios_pods
# 安装Flutter在iOS平台上的引擎和插件
def flutter_install_all_ios_pods(ios_application_path = nil)
  # 创建Flutter引擎的.podspec文件
  flutter_install_ios_engine_pod(ios_application_path)
  flutter_install_plugin_pods(ios_application_path, '.symlinks', 'ios')
end

# 方法: flutter_install_plugin_pods
def flutter_install_plugin_pods(application_path = nil, relative_symlink_dir, platform)
  # CocoaPods定义了 defined_in_file，获取应用路径,未获取到就中断
  application_path ||= File.dirname(defined_in_file.realpath) if respond_to?(:defined_in_file)
  raise 'Could not find application path' unless application_path

  # Prepare symlinks folder. We use symlinks to avoid having Podfile.lock
  # referring to absolute paths on developers' machines.
  # 使用符号链接,避免使用Podfile.lock这个文件
  # Flutter是在ios目录下创建.symlinks目录,里面有软链接指向Flutter下载包的位置,这样只需要一份即可。
  # 先删除,再创建对应的目录
  symlink_dir = File.expand_path(relative_symlink_dir, application_path)
  system('rm', '-rf', symlink_dir) 

  symlink_plugins_dir = File.expand_path('plugins', symlink_dir)
  system('mkdir', '-p', symlink_plugins_dir)

  plugins_file = File.join(application_path, '..', '.flutter-plugins-dependencies')
  dependencies_hash = flutter_parse_plugins_file(plugins_file)
  plugin_pods = flutter_get_plugins_list(dependencies_hash, platform)
  swift_package_manager_enabled = flutter_get_swift_package_manager_enabled(dependencies_hash, platform)

  plugin_pods.each do |plugin_hash|
    plugin_name = plugin_hash['name']
    plugin_path = plugin_hash['path']
    ...
    # 使用path: 的方式本地依赖需要的三方库
    # 手动添加打印确认下
    # print "plugin_name:#{plugin_name}\n"
    pod plugin_name, path: File.join(relative, platform_directory)
  end
end

$ pod update --verbose

因此Podfile里的target部分就依赖了sqflite_darwin

target 'Runner' do
  use_frameworks!
  use_modular_headers!
  ...
  pod 'sqflite_darwin', path:.symlinks/plugins/sqflite_darwin/darwin
end

使用

打开/关闭/删除数据库

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'finger.db');

/// 打开数据库
Database database = await openDatabase(path, version: 1,
    onCreate: (Database db, int version) async {
  /// 当创建数据库时创建table
  await db.execute(
      'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
});

/// 关闭数据库
await db.close();

/// 删除数据库
await deleteDatabase(path);

/// 添加表
await database.execute(
      "CREATE TABLE Test2(id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)",
    );
      
/// 删除表
await database.execute('DROP TABLE Test2');

使用SQL语句

/// 添加数据
await database.transaction((txn) async {
  int id1 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
  int id2 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
      ['another name', 12345678, 3.1416]);
});

/// 删除数据
count = await database
    .rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);

/// 更新数据
int count = await database.rawUpdate(
    'UPDATE Test SET name = ?, value = ? WHERE name = ?',
    ['updated name', '9876', 'some name']);

/// 查询数据
List<Map> list = await database.rawQuery('SELECT * FROM Test');
print(list)

使用工具方法

使用Sqflite提供的工具方法来执行数据库操作，而不是直接使用SQL语句

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

final String tName = 'company';
final String columnId = "_id";
final String columnName = "name";

class Company {
  int? id;
  String? name;
  Company();

  Map<String, Object?> toMap() {
    var map = <String, Object?>{columnName: name};
    if (id != null) {
      map[columnId] = id;
    }
    return map;
  }

  Company.fromMap(Map map) {
    id = map[columnId];
    name = map[columnName];
  }
}

class CompanyProvider {
  Database? db;

  Future<Database?> open() async {
    if (db == null) {
      var databasesPath = await getDatabasesPath();
      String path = join(databasesPath, 'demo.db');
      db = await openDatabase(
        path,
        version: 1,
        onCreate: (Database db, int version) async {
          await db.execute('''
            create table $tName (
            $columnId integer primary key autoincrement,
            $columnName text not null)
        ''');
        },
      );
    }
    return db;
  }

  /// 注册企业
  Future insert(Company company) async {
    /// 工具方法: 传表名 + 列信息添加数据到数据库
    company.id = await db?.insert(tName, company.toMap());
    return company;
  }

  /// 查找企业
  Future findById(int id) async {
    List<Map> maps = await db!.query(
      tName, /// 表名
      columns: [columnId, columnName], /// 查找的列
      where: '$columnId = ?', /// 查找条件
      whereArgs: [id], /// 每个问号填充的值
    );
    if (maps.isNotEmpty) {
      return Company.fromMap(maps.first);
    }
    return null;
  }
  
  /// 查找所有的企业
  Future<List<Company>> find() async {
    List<Company> companys = [];
    List<Map> maps = await db!.query(tName, columns: [columnId, columnName]);
    for (var map in maps) {
      Company c = Company.fromMap(map);
      companys.add(c);
    }
    return companys;
  }

  /// 删除企业
  Future delete(int id) async {
    /// 根据id列删除企业
    return await db?.delete(tName, where: '$columnId = ?', whereArgs: [id]);
  }

  /// 更新企业信息
  Future update(Company company) async {
    return await db?.update(
      tName,
      company.toMap(),
      where: '$columnId = ?',
      whereArgs: [company.id],
    );
  }
}

void test() async {
    /// 添加2条测试数据
    CompanyProvider cp = CompanyProvider();
    await cp.open();
    List<Map> maps = [
      {"name": "Google"},
      {"name": "Apple"},
    ];

    /// 新增数据
    int firstId = 0;
    for (int i = 0; i < maps.length; ++i) {
      Company c = Company.fromMap(maps[i]);
      cp.insert(c);
    }

    /// 查找数据
    List<Company> companys = await cp.find();
    if (companys.isNotEmpty) {
      firstId = companys.first.id!;
    }

    if (firstId > 0) {
      Company firstCompany = await cp.findById(firstId);
      print(firstCompany.toMap());

      /// 更新数据
      Company chgCompany = Company();
      chgCompany.id = firstId;
      chgCompany.name = DateTime.now().microsecondsSinceEpoch.toString();
      cp.update(chgCompany);

      firstCompany = await cp.findById(firstId);
      print(firstCompany.toMap());

      /// 删除数据
      cp.delete(firstId);
    }
  }

数据库迁移

随着功能迭代,需要对数据库的表结构进行修改时,比如增加新字段时,需要对表的结构进行更新。

Future<Database?> open() async {
    if (db == null) {
      var databasesPath = await getDatabasesPath();
      String path = join(databasesPath, 'demo.db');
      db = await openDatabase(
        path,
        version: 2,

        /// 1.新版本发布时改成2
        onCreate: (db, version) async {
          /// 2.新安装设备触发onCreate，所以这里添加新的字段
          await db.execute('''
            create table $tName (
            $columnId integer primary key autoincrement,
            $columnName text not null,
            $columnDesc text)
        ''');
        },
        onUpgrade: (db, oldVersion, newVersion) async {
          var batch = db.batch();
          /// [onUpgrade] is called if either of 
          /// the following conditions are met:

          /// 1. [onCreate] is not specified
          /// 2. The database already exists and [version] is higher than the last database version
          /// onUpgrade回调在未指定onCreate回调或者数据库已经存在同时version字段高于已安装的版本,执行完onUpgrade回调后应该会更新关联的版本,设置断点让onUpgrade执行中断,下次还会会执行这个方法
          
          /// 3.对旧版本的设备:判断安装设备已创建的数据库版本
          if (oldVersion == 1) {
            _updateTableCompanyV1toV2(batch);
          }
          await batch.commit();
        },
      );
    }
    return db;
  }

/// 4.添加description字段
void _updateTableCompanyV1toV2(Batch batch) {
    batch.execute('ALTER TABLE Company ADD description TEXT');
}

/// 其它的一些处理
final String columnDesc = "description";
...

class Company {
  int? id;
  String? name;

  /// 5.模型增加对应字段 + 列
  String? description;
  ...
  
  /// 6. 更新map和对象的转换方法
  Map<String, Object?> toMap() {
    var map = <String, Object?>{columnName: name, columnDesc: description};
    if (id != null) {
    ...

/// 调用
...
firstCompany.description = "版本2新增的字段";
print(firstCompany.toMap());

事务

数据库的增删改查可能会失败,导致数据与预期的不一致,为了保证在执行前后的数据一致性，引入了事务。事务具有ACID这4个特性：原子性、一致性、隔离性和持久性。

在事务中不要使用数据库,而只需要使用事务对象访问数据库。

await database.transaction((txn) async {
  // 正确
  await txn.execute('CREATE TABLE Test1 (id INTEGER PRIMARY KEY)');
  
  // 不要在事务中使用数据库
  // 下面会导致死锁
  await database.execute('CREATE TABLE Test2 (id INTEGER PRIMARY KEY)');
});

try {
   await database.transaction((txn) async {
      await txn.update('TABLE', {'foo': 'bar'});
   });
   
   // No error, the transaction is committed
   // 1. 未报错,则事务被提交
   
   // cancel the transaction (any error will do)
   // 2. 取消或执行时报错,则抛出异常在,catch中被捕获
   // throw StateError('cancel transaction');
} catch (e, st) {
   // this reliably catch if there is a key conflict
   // We know that the transaction is rolled back.
   // 3. 事务被回滚,执行业务相关的操作,比如提示报错
}

批处理

使用 Batch,即批处理,来避免在 Dart 和原生代码之间的反复切换。

batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
/// 批处理统一提交
results = await batch.commit();

在事务中,批处理的commit会等到事务提交后

await database.transaction((txn) async {
  var batch = txn.batch();
  
  // ...
  
  // commit but the actual commit will happen when the transaction is committed
  // however the data is available in this transaction
  /// 当事务被提交时才会真正的提交
  await batch.commit();
  
  //  ...
});

/// 设置批处理出现错误依然提交
await batch.commit(continueOnError: true);

表名和列名

SQLite的关键词,要避免使用作为实体(Entity)名。

"add","all","alter","and","as","autoincrement","between","case","check","collate","commit","constraint","create","default","deferrable","delete","distinct","drop","else","escape","except","exists","foreign","from","group","having","if","in","index","insert","intersect","into","is","isnull","join","limit","not","notnull","null","on","or","order","primary","references","select","set","table","then","to","transaction","union","unique","update","using","values","when","where"

sqflite的工具方法会进行处理,避免与关键字的冲突

db.query('table')
/// 等价于
db.rawQuery('SELECT * FROM "table"');

其它问题

VSCode 无法调试

Error connecting to the service protocol: failed to connect to http://127.0.0.1:51020/Kra7fZnYjeI=/ Error: Failed to register service methods on attached VM Service: registerService: (-32000) Service connection disposed

原来有成功过，后面发现一直都会有问题,前段时间突然不行,在长时间运行后就会报这个错误,但是单独在VSCode外部用flutter run命令能正常运行。

发现终端可以是把本地的端口转发的代理给去掉了。然后发现VSCode的代理有这样的说明,若未设置则会继承环境变量中的http_proxy和https_proxy，我把代理加到.zshrc中，所以VSCode的默认会用代理,但是运行在真机上,手机没有代理，应该是这样影响了网络环境。

1. .zshrc去掉代理的配置
2. 重新打开VSCode && 运行 => 能正常调试

参考

1. SQLite CRUD operations in Flutter
2. sqflite-doc
3. sqflite Migration example