ATTACH/DETACH database

[rodydavis]

Curious if ATTACH/DETACH is planned for this, it is nice to use in sqlite3 since you can have a readonly database attached to one or many user/file databases

https://www.sqlite.org/lang_attach.html

[rkistner]

While we don't have an explicit API for that, you can still use ATTACH / DETACH directly in queries.

Since we're using a connection pool, ATTACH needs to be called on every connection you want to use it from. One way to do that is using a SqliteOpenFactory, such as in this example.

Then add this in the open call:

    db.execute("ATTACH ? AS other", ['other.db']);

If you only want to attach temporarily, you can do so within a connection lock or transaction, which only uses a single connection at a time:

  await db.readLock((ctx) async {
    // Everything inside this lock uses the same database connection
    try {
      await ctx.getAll("ATTACH ? AS other", ['other.db']);
      print(await ctx.getAll('SELECT * from other.mytable'));
    } finally {
      await ctx.getAll("DETACH other");
    }
  });

Another approach is to just use the write connection for the attached table, although that prevents you from taking advantage of concurrent reads:

  // Only attaches on the write connection
  await db.execute("ATTACH ? AS other", ['other.db']);
  // "execute" instead of "getAll" here, so that the write connection is used for the query
  print(await db.execute('SELECT * from other.mytable'));

Do you have any other scenarios not covered by these?