CODEWITHSUNDEEP
databasefluttermigrationsqflite
thgehr
thgehr

Reputation: 81

Open asset database with migration strategy

I followed this guide Opening an asset database to open an asset database and to copy it to my file system but with "readOnly: true" as I want the user to modify the database inside the app. 

  initDB() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "TEST.db");

    var exists = await databaseExists(path);

    if (!exists) {
      // Should happen only the first time you launch your application
      print("Creating new copy from asset");

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

      // Copy from asset
      ByteData data = await rootBundle.load(join("assets", "test.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");
    }
    // open the database
    return await openDatabase(path, version: 1, onUpgrade: _onUpgrade);
  }

And this works perfectly.

But later I want to modify the asset database, e.g. adding new rows, columns or tables or even change the values from a specific already existing column. When I do that I want to update the copied database in the file system with the modified asset database. For that I use onUpgrade.

  _onUpgrade(Database db, int oldVersion, int newVersion) async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "TEST.db");

    // Delete old database and load new asset database
    await deleteDatabase(path);
    try {
      await Directory(dirname(path)).create(recursive: true);
    } catch (_) {}

    ByteData data = await rootBundle.load(join("assets", "test.db"));
    List<int> bytes =
        data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
    await new File(path).writeAsBytes(bytes, flush: true);

    // Add new table

    // Add new row or column

    // Update column
  }

I only know how to delete the database in the file system after a version change, but I don't want to delete the changes in the database the user did. How can I merge the new asset database with the database in the file system? How can I add a new table, column or row? And how can I replace a column?

Upvotes: 1

Views: 156

Answers (1)

live-love
live-love

Reputation: 52386

You will have to open it with readOnly = false.

Then when you call onUpgrade you will have to run sql queries to alter the tables with the ALTER TABLE command.

final Future<Database> database = openDatabase(
  // Set the path to the database.
  join(await getDatabasesPath(), 'mydatabase.db'),

  onUpgrade: (db, version ...) {
    return db.execute(
      "ALTER TABLE ... ADD COLUMN ...",
    );
  },
  // Set the version to upgrade
  version: 2,
);

Upvotes: 1

Related Questions