在C#中如何将命名内存SQLite数据库附加到物理数据库连接

问题背景 #

我有一个磁盘上的物理SQLite数据库和一个命名的内存数据库，需要对它们执行联合查询。查阅文档时看到“当以这种方式命名内存数据库时，它仅会与使用完全相同名称的另一个连接共享缓存”，不确定是否可行。测试代码执行时出现异常：'m.Mem'表不存在。

测试代码如下：

var dbFile = @"D:\Downloads\test.db3";
if (File.Exists(dbFile))
    File.Delete(dbFile);

// create connection to physical db
var physicalCon = new SqliteConnection(new SqliteConnectionStringBuilder
{
    DataSource = dbFile,
    Mode = SqliteOpenMode.ReadWriteCreate,
    Cache = SqliteCacheMode.Shared,
    Pooling = false
}.ToString());
physicalCon.Open();

// create connection to in-memory db
var memoryCon = new SqliteConnection(new SqliteConnectionStringBuilder
{
    DataSource = "memtest",
    Mode = SqliteOpenMode.Memory,
    Cache = SqliteCacheMode.Shared,
    Pooling = false
}.ToString());
memoryCon.Open();

try
{
    // create some tables in physical db, insert values, and test read
    using (var p1Cmd = physicalCon.CreateCommand())
    {
        p1Cmd.CommandText = "CREATE TABLE Phys (Val TEXT);";
        p1Cmd.ExecuteNonQuery();
        p1Cmd.CommandText = "INSERT INTO Phys (Val) Values ('Phys1'), ('Phys2');";
        p1Cmd.ExecuteNonQuery();
        p1Cmd.CommandText = "SELECT * FROM Phys;";
        using var pReader = p1Cmd.ExecuteReader();
        while (pReader.Read())
        {
            Debug.WriteLine("P : " + pReader.GetString(0));
        }
    }

    // create some tables in memory db, insert values, and test read
    using (var m1Cmd = memoryCon.CreateCommand())
    {
        m1Cmd.CommandText = "CREATE TABLE Mem (Val TEXT);";
        m1Cmd.ExecuteNonQuery();
        m1Cmd.CommandText = "INSERT INTO Mem (Val) Values ('Mem1'), ('Mem2');";
        m1Cmd.ExecuteNonQuery();
        m1Cmd.CommandText = "SELECT * FROM Mem;";
        using var mReader = m1Cmd.ExecuteReader();
        while (mReader.Read())
        {
            Debug.WriteLine("M : " + mReader.GetString(0));
        }
    }

    // attach memory db to physical db connection
    using (var attach = physicalCon.CreateCommand())
    {
        attach.CommandText = "ATTACH DATABASE 'file:memtest?mode=memory&cache=shared' AS m;";
        attach.ExecuteNonQuery();
    }

    // read memory db values from physical db connection
    using (var testCmd = physicalCon.CreateCommand())
    {
        testCmd.CommandText = "SELECT * FROM m.Mem;"; // exception 'm.Mem' table does not exist
        using var reader = testCmd.ExecuteReader();
        while (reader.Read())
        {
            Debug.WriteLine("T : " + reader.GetString(0));
        }
    }
}
finally
{
    physicalCon.Close();
    memoryCon.Close();
}

问题原因 #

当前ATTACH语句使用的URI格式和内存数据库的创建方式不匹配。命名内存数据库的共享需要通过相同的数据源名称而非URI来关联，直接用file:memtest?mode=memory&cache=shared会创建一个新的内存数据库实例，而非连接到已存在的memtest内存库。

正确实现方案 #

核心要点 #

1. 命名内存数据库的共享依赖Cache = SqliteCacheMode.Shared配置+相同的DataSource名称，不同连接才能访问同一内存缓存实例
2. 已存在的命名内存库，其他连接只需通过内存库名称ATTACH，无需使用URI格式
3. 先初始化内存库，再让物理库连接关联，避免实例未就绪问题

修正后的代码 #

var dbFile = @"D:\Downloads\test.db3";
if (File.Exists(dbFile))
    File.Delete(dbFile);

// 先创建并初始化内存数据库
var memoryCon = new SqliteConnection(new SqliteConnectionStringBuilder
{
    DataSource = "memtest",
    Mode = SqliteOpenMode.Memory,
    Cache = SqliteCacheMode.Shared,
    Pooling = false
}.ToString());
memoryCon.Open();

using (var m1Cmd = memoryCon.CreateCommand())
{
    m1Cmd.CommandText = "CREATE TABLE Mem (Val TEXT);";
    m1Cmd.ExecuteNonQuery();
    m1Cmd.CommandText = "INSERT INTO Mem (Val) Values ('Mem1'), ('Mem2');";
    m1Cmd.ExecuteNonQuery();
}

// 创建物理数据库连接，并关联已存在的命名内存库
var physicalCon = new SqliteConnection(new SqliteConnectionStringBuilder
{
    DataSource = dbFile,
    Mode = SqliteOpenMode.ReadWriteCreate,
    Cache = SqliteCacheMode.Shared,
    Pooling = false
}.ToString());
physicalCon.Open();

try
{
    // 初始化物理数据库
    using (var p1Cmd = physicalCon.CreateCommand())
    {
        p1Cmd.CommandText = "CREATE TABLE Phys (Val TEXT);";
        p1Cmd.ExecuteNonQuery();
        p1Cmd.CommandText = "INSERT INTO Phys (Val) Values ('Phys1'), ('Phys2');";
        p1Cmd.ExecuteNonQuery();
    }

    // ATTACH命名内存数据库，直接使用内存库名称
    using (var attach = physicalCon.CreateCommand())
    {
        attach.CommandText = "ATTACH DATABASE 'memtest' AS m;";
        attach.ExecuteNonQuery();
    }

    // 执行联合查询
    using (var testCmd = physicalCon.CreateCommand())
    {
        testCmd.CommandText = "SELECT p.Val AS PhysVal, m.Val AS MemVal FROM Phys p CROSS JOIN m.Mem;";
        using var reader = testCmd.ExecuteReader();
        while (reader.Read())
        {
            Debug.WriteLine($"Phys: {reader.GetString(0)}, Mem: {reader.GetString(1)}");
        }
    }
}
finally
{
    physicalCon.Close();
    memoryCon.Close();
}

内容的提问来源于stack exchange，提问作者jo phul