从Flutter App的资产中导入SQFlite数据库，并使用RawQuery显示特定行

Question

我已经构建了一个使用颤音的应用程序。其功能的一部分是，用户可以搜索应用程序资产区域中的数据。该数据最初是以JSON格式的，尽管我已将其转换为SQLite数据库以节省存储空间。这实际上帮助我节省了约90％，这很棒。问题是，搜索委托不再有效。它只是返回一个空列表，尽管控制台中没有产生错误。

我创建了一个模型类，以帮助从SQLite数据库表中读取数据，该数据看起来像这样：

/// Class to handle the country data in the database
class CountriesDB {
  /// Defining the variables to be pulled from the json file
  late int id;
  late String continent;
  late String continentISO;
  late String country;
  late String countryISO;
  late String flagIconLocation;

  CountriesDB({
    required this.id,
    required this.continent,
    required this.continentISO,
    required this.country,
    required this.countryISO,
    required this.flagIconLocation,
  });

  CountriesDB.fromMap(dynamic obj) {
    this.id = obj[id];
    this.continent = obj[continent];
    this.continentISO = obj[continentISO];
    this.country = obj[country];
    this.countryISO = obj[countryISO];
    this.flagIconLocation = obj[flagIconLocation];
  }

  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      'id': id,
      'continent': continent,
      'continentISO': continentISO,
      'country': country,
      'countryISO': countryISO,
      'flagIconLocation': flagIconLocation,
    };
    return map;
  }
}

据我所知，读取存储在应用程序资产文件夹中的数据库中的数据，我需要从编程中进行程序上将其转换为工作数据库。我已经编写了以下代码，以对：

  /// Creating the database values
  static final DatabaseClientData instance = DatabaseClientData._init();
  static Database? _database;
  DatabaseClientData._init();

  /// Calling the database
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('databaseWorking.db');
    return _database!;
  }

  /// Future function to open the database
  Future<Database> _initDB(String filePath) async {
    /// Getting the data from the database in 'assets'
    var databasesPath = await getDatabasesPath();
    var path = join(databasesPath, filePath);

    /// Check if the database exists
    var exists = await databaseExists(path);

    if (!exists) {
      /// Should happen only the first time the application is launched
      print('Creating new copy from asset');

      /// Make sure the parent directory exists
      try {
        await Directory(dirname(path)).create(recursive: true);
      } catch (_) {}

      /// Copy from the asset
      ByteData data =
          await rootBundle.load('assets/data/database.db');
      List<int> bytes =
          data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);

      /// Write and flush the bytes written
      await File(path).writeAsBytes(bytes, flush: true);
    } else {
      print('Opening existing database');
    }
    return await openDatabase(path, readOnly: true);
  }

我所做的下一件事是创建一个未来的函数，该功能使用rawQuery来搜索数据库。为此，代码是：

  /// Functions to search for specific database entries
  /// Countries
  static Future<List<CountriesDB>> searchCountries(String keyword) async {
    final db = await instance.database;
    List<Map<String, dynamic>> allCountries = await db.rawQuery(
        'SELECT * FROM availableISOCountries WHERE continent=? OR continentISO=? OR country=? OR countryISO=?',
        ['%keyword%']);
    List<CountriesDB> countries =
        allCountries.map((country) => CountriesDB.fromMap(country)).toList();
    return countries;
  }

最后，我使用的是Flutter Search代表类，允许用户与数据库进行交互并搜索特定的行。这是我为此构建的小部件：

  /// Checks to see if suggestions can be made and returns error if not
  Widget buildSuggestions(BuildContext context) => Container(
        color: Color(0xFFF7F7F7),
        child: FutureBuilder<List<CountriesDB>>(
          future: DatabaseClientData.searchCountries(query),
          builder: (context, snapshot) {
            switch (snapshot.connectionState) {
              case ConnectionState.waiting:
                return Center(
                    child: PlatformCircularProgressIndicator(
                  material: (_, __) => MaterialProgressIndicatorData(
                    color: Color(0xFF287AD3),
                  ),
                  cupertino: (_, __) => CupertinoProgressIndicatorData(),
                ));
              default:
                if (query.isEmpty) {
                  return buildAllSuggestionsNoSearch(snapshot.data!);
                } else if (snapshot.hasError || snapshot.data!.isEmpty) {
                  return buildNoSuggestionsError(context);
                } else {
                  return buildSuggestionsSuccess(snapshot.data!);
                }
            }
          },
        ),
      );

想法是，我构建的功能将在用户搜索之前返回整个列表，并且一旦用户开始键入，只会显示任何与搜索查询匹配的行。当我使用JSON数据时，这很好，但是它正在返回一个空列表，但控制台中没有任何错误。这使得很难知道我的代码在哪里出错。

我的代码在哪里出了问题，以至于它没有返回任何数据？我该如何纠正？谢谢！