6

Creating in-memory databases using SQLite in Dart and Flutter

 3 years ago
source link: http://www.mikechambers.com/blog/2020/10/29/creating-in-memory-databases-using-sqlite-in-dart-and-flutter/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Creating in-memory databases using SQLite in Dart and Flutter

Thursday, October 29, 2020

I have been playing around with the Alpha version of Flutter Desktop lately, and needed to use a SQLite database which I may need to update at runtime. This was causing an an issue, as once I loaded the database file into SQLite it was holding a lock on the file (and thus preventing me from overwriting it).

Since I was only reading from the database, and never writing, I wanted to just completely load the database into memory and disconnect it from the file. SQLite does allow you to create an new in-memory database using inMemoryDatapasePath, but I could not find a simple way to load an existing database directly into memory (i’m using the sqflite_common_ffi library).

The workaround that I came up with was to load the database from the file, and then copy the tables I needed into a new database created in memory:

First, create a new in-memory SQLite database:

_db = await databaseFactoryFfi.openDatabase(inMemoryDatabasePath,
      options: OpenDatabaseOptions(singleInstance: false));

Then, we can attach the file based database to the in-memory database, and copy over the tables we need.

await _db.rawQuery("ATTACH DATABASE ? as tmpDb", [dbFile.path]);

String table = "TABLE_NAME";
//copy the table from the database file to in memory data (can copy multiple)
await _db.rawQuery("CREATE TABLE $table AS SELECT * FROM tmpDb.$table");

//we need to detach it or else it will maintain a connection to the db file
//on the file system
await _db.rawQuery("DETACH DATABASE tmpDb");

Note that when we are done, we need to detach the file based database from the in-memory database, or else it may maintain a lock on the file.

Here is the complete example:

Database _db;

Future<void> initialize({@required File dbFile}) async {
  sqfliteFfiInit();

  //close the existing db if it exists
  _closeDb(_db);

  //create a new database in memory using the inMemoryDatabasePath constant
  _db = await databaseFactoryFfi.openDatabase(inMemoryDatabasePath,
      options: OpenDatabaseOptions(singleInstance: false));

  try {
    //attach the databse file to our in memory database
    await _db.rawQuery("ATTACH DATABASE ? as tmpDb", [dbFile.path]);

    String table = "TABLE_NAME";
    //copy the table from the database file to in memory data (can copy multiple)
    await _db.rawQuery("CREATE TABLE $table AS SELECT * FROM tmpDb.$table");

    //we need to detach it or else it will maintain a connection to the db file
    //on the file system
    await _db.rawQuery("DETACH DATABASE tmpDb");

  } catch (e) {
    print("Error creating database");

    //if anything goes wrong, make sure we clean up
    _closeDb(_db);
    _db = null;
    throw (e);
  }
}

Future<void> _closeDb(Database db) async {
  if (db != null) {
    try {
      if (db.isOpen) {
        await db.close();
      }
    } catch (e) {
      print("Could not close database");
    }
  }
}

This works well, and ensures that the database file wont be locked. It also allows me to just copy over the data that I need. If need need to update the database, I can just overwrite the file, and then reinitialize the new database from the updated file.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK