C# 使用 SQLite 的问题：内存：与 NHibernate

Problem using SQLite :memory: with NHibernate

提问by Chris Canal

I use NHibernate for my dataacess, and for awhile not I've been using SQLite for local integration tests. I've been using a file, but I thought I would out the :memory: option. When I fire up any of the integration tests, the database seems to be created (NHibernate spits out the table creation sql) but interfacting with the database causes an error.

我使用 NHibernate 进行数据访问，并且有一段时间我没有使用 SQLite 进行本地集成测试。我一直在使用一个文件，但我想我会退出 :memory: 选项。当我启动任何集成测试时，数据库似乎已创建（NHibernate 吐出表创建 sql），但与数据库交互会导致错误。

Has anyone every gotten NHibernate working with an in memory database? Is it even possible? The connection string I'm using is this:

有没有人让 NHibernate 使用内存数据库？甚至有可能吗？我使用的连接字符串是这样的：

Data Source=:memory:;Version=3;New=True

采纳答案by Sean Carpenter

A SQLite memory database only exists as long as the connection to it remains open. To use it in unit tests with NHibernate:
1. Open an ISession at the beginning of your test (maybe in a [SetUp] method).
2. Use the connection from that session in your SchemaExport call.
3. Use that same session in your tests.
4. Close the session at the end of your test (maybe in a [TearDown] method).

SQLite 内存数据库只有在与它的连接保持打开时才存在。要在 NHibernate 的单元测试中使用它：
1. 在测试开始时打开一个 ISession（可能在 [SetUp] 方法中）。
2. 在您的 SchemaExport 调用中使用来自该会话的连接。
3. 在您的测试中使用相同的会话。
4. 在测试结束时关闭会话（可能在 [TearDown] 方法中）。

回答by chills42

Just a wild guess, but is the sql output by NHibernate using a command unsupported by sqlite?

只是一个疯狂的猜测，但是 NHibernate 使用 sqlite 不支持的命令输出的 sql 是什么？

Also, What happens if you use a file instead of memory? (System.IO.Path.GetTempFileName() would work i think...)

另外，如果您使用文件而不是内存会发生什么？（我认为 System.IO.Path.GetTempFileName() 会起作用......）

回答by Stefan Steinegger

We are using SQLite in memory for all our database tests. We are using a single ADO connection for the tests that is reused for all NH sessions opened by the same test.

我们在内存中使用 SQLite 进行所有数据库测试。我们使用单个 ADO 连接进行测试，该连接可重用于同一测试打开的所有 NH 会话。

1. Before every test: create connection
2. Create schema on this connection
3. Run test. The same connection is used for all sessions
4. After test: close connection

1. 在每次测试之前：创建连接
2. 在此连接上创建架构
3. 运行测试。所有会话都使用相同的连接
4. 测试后：关闭连接

This allows also running tests with several sessions included. The SessionFactory is also created once for all tests, because the reading of the mapping files takes quite some time.

这也允许运行包含多个会话的测试。SessionFactory 也为所有测试创建一次，因为读取映射文件需要相当长的时间。

---

---

Edit

编辑

Use of the Shared Cache

共享缓存的使用

Since System.Data.Sqlite 1.0.82 (or Sqlite 3.7.13), there is a Shared Cache, which allows several connections to share the same data, also for In-Memory databases. This allows creation of the in-memory-database in one connection, and use it in another. (I didn't try it yet, but in theory, this should work):

从 System.Data.Sqlite 1.0.82（或Sqlite 3.7.13）开始，有一个Shared Cache，它允许多个连接共享相同的数据，也适用于In-Memory 数据库。这允许在一个连接中创建内存数据库，并在另一个连接中使用它。（我还没有尝试过，但理论上，这应该可行）：

• Change the connection string to file::memory:?cache=shared
• Open a connection and create the schema
• Keep this connection open until the end of the test
• Let NH create other connections (normal behavior) during the test.

• 将连接字符串更改为 file::memory:?cache=shared
• 打开连接并创建架构
• 保持此连接打开直到测试结束
• 让 NH 在测试期间创建其他连接（正常行为）。

回答by Tim Scott

I am doing it with Rhino Commons. If you don't want to use Rhino Commons you can study the source do see how it does it. The only problem I have had is that SQLite does not support nested transactions. This forced me to change my code to support integration testing. Integration testing with in memory database is so awesome, I decided it was a fair compromise.

我正在用Rhino Commons做这件事。如果您不想使用 Rhino Commons，您可以研究源代码，看看它是如何做到的。我遇到的唯一问题是 SQLite 不支持嵌套事务。这迫使我更改代码以支持集成测试。内存数据库的集成测试非常棒，我认为这是一个公平的妥协。

回答by Anders B

I hade alot off problems with SQLite memory database. So now we are using SQLite working with files on a ramdrive disk.

我在 SQLite 内存数据库方面遇到了很多问题。所以现在我们正在使用 SQLite 处理 ramdrive 磁盘上的文件。

回答by Julien Bérubé

I had similar problems that lasted even after opening the ISession as stated above, and adding "Pooling=True;Max Pool Size=1" to my connection string. It helped, but I still had some cases where the connection would close during a test (usually right after committing a transaction).

即使在如上所述打开 ISession 并将“Pooling=True;Max Pool Size=1”添加到我的连接字符串后，我也遇到了类似的问题。它有帮助，但我仍然有一些连接会在测试期间关闭的情况（通常在提交事务后立即关闭）。

What finally worked for me was setting the property "connection.release_mode" to "on_close" in my SessionFactory configuration.

最终对我有用的是在我的 SessionFactory 配置中将属性“connection.release_mode”设置为“on_close”。

My configuration in the app.config file now look likes this:

我在 app.config 文件中的配置现在看起来像这样：

  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <reflection-optimizer use="true" />
    <session-factory>
      <property name="connection.connection_string_name">testSqlLiteDB</property>
      <property name="connection.driver_class">NHibernate.Driver.SQLite20Driver</property>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="connection.release_mode">on_close</property>
      <property name="dialect">NHibernate.Dialect.SQLiteDialect</property>
      <property name="proxyfactory.factory_class">NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
      <property name="query.substitutions">true=1;false=0</property>
    </session-factory>
  </hibernate-configuration>

Hope it helps!

希望能帮助到你！

回答by decates

I was able to use a SQLite in-memory database and avoid having to rebuild the schema for each test by using SQLite's support for 'Shared Cache', which allows an in-memory database to be shared across connections.

我能够使用 SQLite 内存数据库，并通过使用 SQLite对 'Shared Cache'的支持来避免为每个测试重建架构，这允许跨连接共享内存数据库。

I did the following in AssemblyInitialize(I'm using MSTest):

我在AssemblyInitialize 中执行了以下操作（我使用的是 MSTest）：

• Configure NHibernate (Fluently) to use SQLite with the following connection string:

  FullUri=file:memorydb.db?mode=memory&cache=shared
  

• Use that configuration to create a hbm2ddl.SchemaExportobject, and execute it on a separate connection (but with that same connection string again).

• Leave that connection open, and referenced by a static field, until AssemblyCleanup, at which point it is closed and disposed of. This is because SQLite needs at least one active connection to be held on the in-memory database to know it's still required and avoid tidying up.

• 配置 NHibernate (Fluently) 以使用 SQLite 和以下连接字符串：

  FullUri=file:memorydb.db?mode=memory&cache=shared
  

• 使用该配置创建一个 hbm2ddl。SchemaExport对象，并在单独的连接上执行它（但再次使用相同的连接字符串）。

• 保持该连接打开并由静态字段引用，直到AssemblyCleanup，此时它被关闭并被处理。这是因为 SQLite 需要在内存数据库上至少保持一个活动连接，以了解它仍然需要并避免整理。

Before each test runs, a new session is created, and the test runs in a transaction which is rolled back at the end.

在每次测试运行之前，都会创建一个新会话，并且测试在最后回滚的事务中运行。

Here is an example of the test assembly-level code:

以下是测试汇编级代码的示例：

[TestClass]
public static class SampleAssemblySetup
{
    private const string ConnectionString = "FullUri=file:memorydb.db?mode=memory&cache=shared";
    private static SQLiteConnection _connection;

    [AssemblyInitialize]
    public static void AssemblyInit(TestContext context)
    {
        var configuration = Fluently.Configure()
                                       .Database(SQLiteConfiguration.Standard.ConnectionString(ConnectionString))
                                       .Mappings(m => m.FluentMappings.AddFromAssembly(Assembly.Load("MyMappingsAssembly")))
                                       .ExposeConfiguration(x => x.SetProperty("current_session_context_class", "call"))
                                       .BuildConfiguration();

        // Create the schema in the database
        // Because it's an in-memory database, we hold this connection open until all the tests are finished
        var schemaExport = new SchemaExport(configuration);
        _connection = new SQLiteConnection(ConnectionString);
        _connection.Open();
        schemaExport.Execute(false, true, false, _connection, null);
    }

    [AssemblyCleanup]
    public static void AssemblyTearDown()
    {
        if (_connection != null)
        {
            _connection.Dispose();
            _connection = null;
        }
    }
}

And a base class for each unit test class/fixture:

以及每个单元测试类/夹具的基类：

public class TestBase
{
    [TestInitialize]
    public virtual void Initialize()
    {
        NHibernateBootstrapper.InitializeSession();
        var transaction = SessionFactory.Current.GetCurrentSession().BeginTransaction();
    }

    [TestCleanup]
    public virtual void Cleanup()
    {
        var currentSession = SessionFactory.Current.GetCurrentSession();
        if (currentSession.Transaction != null)
        {
            currentSession.Transaction.Rollback();
            currentSession.Close();
        }

        NHibernateBootstrapper.CleanupSession();
    }
}

Resource management could improve, I admit, but these are unit tests after all (suggested improvements welcome!).

我承认资源管理可以改进，但这些毕竟是单元测试（欢迎提出改进建议！）。

回答by Drexter

Just want to thank decates. Been trying to solve this for a couple of months now and all I had to do was add

只想感谢decates。几个月来一直试图解决这个问题，我所要做的就是添加

FullUri=file:memorydb.db?mode=memory&cache=shared

to the connection string in my nhibernate config file. Also using just NHibernate with *.hbm.xml and not FNH and didn't really have to modify my code at all!

到我的 nhibernate 配置文件中的连接字符串。还只使用带有 *.hbm.xml 而不是 FNH 的 NHibernate 并且根本不需要修改我的代码！

回答by jenspo

I got the same error, when I forgot to import the SQLite Nuget package.

当我忘记导入 SQLite Nuget 包时，我遇到了同样的错误。