Is it possible to check/validate a database has the correct schema?

[asmith26]

Hi there,

I'm hoping to allow users to restore/upload a sqflite database file to my app. Of course my app requires a specific database format/schema, hence I was wondering if it is possible to check/validate an uploaded file has the correct schema for my app?

My only thought so far is to compare an uploaded file to the required schema using something like print(await db.query("sqlite_master"));

Many thanks for any help, and for this amazing lib! :)

[Originally asked: https://github.com//issues/849]

[alextekartik]

Querying sqflite_master is a reasonable solution. On top of that, you could create a table with a very specific name. By querying whether this table exists (sqflite_master), or a specific row in this table and checking the user version (that you control) you could have a good clue whether this is a valid database for you app or if it needs to be upgraded or if it is not valid at all.

(I've close the bug as I think it is more appropriate here, thanks for asking)

[asmith26]

Thanks very much for the information @alextekartik, this gave me the confidence to explore this further - the following seems to be working quite nicely:

class SqliteMasterFields {
  static const String type = 'type';
  static const String name = 'name';
  static const String tbl_name = 'tbl_name';
  static const String rootpage = 'rootpage';
  static const String sql = 'sql';
}

class SqliteMaster {
  final String type;
  final String name;
  final String tbl_name;
  final int rootpage;
  final String sql;

  const SqliteMaster({
    required this.type,
    required this.name,
    required this.tbl_name,
    required this.rootpage,
    required this.sql,
  });

  static SqliteMaster fromJson(Map<String, Object?> json) => SqliteMaster(
        type: json[SqliteMasterFields.type] as String,
        name: json[SqliteMasterFields.name] as String,
        tbl_name: json[SqliteMasterFields.tbl_name] as String,
        rootpage: json[SqliteMasterFields.rootpage] as int,
        sql: json[SqliteMasterFields.sql] as String,
      );

  static const expectedSql = {
    "android_metadata": "CREATE TABLE android_metadata (locale TEXT)",
    "sqlite_sequence": "CREATE TABLE sqlite_sequence(name,seq)",
    "my_cool_table": "CREATE TABLE...",
  };

  static bool validate(List<SqliteMaster> tables) {
    if (tables.length != 3) {
      return false;
    }
    for (SqliteMaster table in tables) {
       if (expectedSql[table.name] != table.sql) {
        return false;
      }
    }
    return true;
  }
}

And then:

...
isValid = SqliteMaster.validate((await db.query("sqlite_master"))
        .map((json) => SqliteMaster.fromJson(json))
        .toList());
...

[asmith26]

I've now noticed that this code doesn't handle the case when the provided file is not a database - instead I see logs like:

E/SQLiteLog(19845): (26) file is encrypted or is not a database
E/DefaultDatabaseErrorHandler(19845): !@ Corruption reported by sqlite (26) on database: /data/user/.../my_database.db
E/DefaultDatabaseErrorHandler(19845): !@ dbObj has been closed
E/DefaultDatabaseErrorHandler(19845): !@ DB Corruption has happened before this

@alextekartik not sure if you know of a way to throw/catch errors like this when calling await openDatabase(path, version: 1, onCreate: _onCreate); - many thanks again for any help! :)

---

UPDATE: think I've found what I'm after: https://github.com/tekartik/sqflite/blob/master/sqflite/doc/opening_db.md#handle-corruption

[alextekartik]

Indeed one solution is to surround the openDatabase call with a try/catch