SQLite database verification after nhibernate schema generation

Question

What is the simplest most effective way to verify that your SQLite db is actually out there after using NHib's schema generation tool?

Cheers,
Berryl

EDIT

I am hoping there is something tied to the ISession (like the connection property) that can be tested; sometimes when running a series of tests it seems like a good session (IsOpen & IsConnected are true) but the db is not there (a query against it gets an error like 'no such table').

EDIT - WHAT I AM DOING NOW

Connection string & other cfg properties

public static Configuration GetSQLiteConfig()
    {
        return new Configuration()
            .SetProperty(ENV.Dialect, typeof (SQLiteDialect).AssemblyQualifiedName)
            .SetProperty(ENV.ConnectionDriver, typeof (SQLite20Driver).AssemblyQualifiedName)
            .SetProperty(ENV.ConnectionString, "Data Source=:memory:;Version=3;New=True;Pooling=True;Max Pool Size=1")
            .SetProperty(ENV.ProxyFactoryFactoryClass, typeof (ProxyFactoryFactory).AssemblyQualifiedName)
            .SetProperty(ENV.ReleaseConnections, "on_close")
            .SetProperty(ENV.CurrentSessionContextClass, typeof (ThreadStaticSessionContext).AssemblyQualifiedName);
    }

How I test the db now, for lack of something 'better' (this tests the mappings)

        public static void VerifyAllMappings(ISessionFactory sessionFactory, ISession session)
    {
        Check.RequireNotNull<ISessionFactory>(sessionFactory);
        Check.Require(session.IsOpen && session.IsConnected);

        _verifyMappings(sessionFactory, session);
    }

    private static void _verifyMappings(ISessionFactory sessionFactory, ISession session) {
        try {
            foreach (var entry in sessionFactory.GetAllClassMetadata())
            {
                session.CreateCriteria(entry.Value.GetMappedClass(EntityMode.Poco))
                    .SetMaxResults(0).List();
            }
        }
        catch (Exception ex) {
            Console.WriteLine(ex);
            throw;
        }
    }

        public static void VerifyAllMappings(ISessionFactory sessionFactory, ISession session)
    {
        Check.Require(!sessionFactory.IsClosed);
        Check.Require(session.IsOpen && session.IsConnected);

        try {
            foreach (var entry in sessionFactory.GetAllClassMetadata())
            {
                session.CreateCriteria(entry.Value.GetMappedClass(EntityMode.Poco))
                    .SetMaxResults(0).List();
            }
        }
        catch (Exception ex) {
            Debug.WriteLine(ex);
            throw;
        }
    }

I generate the schema in a session provider whenever a new session is opened:

        public ISession Session
    {
        get
        {
            var session = (ISession)CallContext.GetData(_lookupSessionKey);
            try
            {
                if (session == null)
                {
                    _log.Debug("Opening new Session for this context.");

                    session = FactoryContext.Factory.OpenSession();

                    if(RunTypeBehaviorQualifier != RunType.Production)
                        SchemaManager.GenerateNewDb(FactoryContext.Cfg, session.Connection);
                    CallContext.SetData(_lookupSessionKey, session);
                }
            }
            catch (HibernateException ex)
            {
                throw new InfrastructureException(ex);
            }
            return session;
        }
    }

Now this is all probably way over engineered, but I need multiple database connections and I've been having trouble keeping it simpler & working. It's also a lot of info for one question, but maybe someone else has actually got this all down to a science. The test below runs fine within it's own test fixture, but not in conjunction with other tests.

    [Test]
    public void Schema_CanGenerateNewDbWithSchemaApplied()
    {
        DbMappingTestHelpers.VerifyAllMappings(_dbContext.FactoryContext.Factory, _dbContext.Session);
    }

Answer 1

Berryl,

As far as I can see you're strugling against mapped entities because you are using different connections. Is there any requirement that obligates you to use more than one "real" DB connection? I mean, can your tests share the same session (logically)? If not, you can simply configure your DB as:

      <property name="connection.connection_string">Data Source=NonTransactionalDB.txt;Version=3;New=True;Pooling=True;Max Pool Size=1;</property>

The important part of it are the pooling options. As every session will aways use the same connection, you won't have problems with recreating the schema everytime.

It's important to remeber, though, that it introduces to you some limitations about transactions. As SQLite can't handle more than one transaction per connection, running your tests in parallel can bring you problems (something like a "database file is locked" Exception").

Cheers,

Filipe

Answer 2

Berryl, just to make it easir to visualize, I'll post as another answer. Feel free to give me another up if it helps you. :)

Below is the code that I use to check if my NH configuration object was properly configured.

        // assert: verify some properties just to see if connection properties were applyed ane entities mapped
        Assert.AreEqual<string>(cfg.Properties["connection.connection_string"], @"Server=localhost;Initial Catalog=MoveFrameworkDataNHibernate;User Id=sa;Password=sa");
        Assert.AreEqual<string>(cfg.Properties["dialect"], "NHibernate.Dialect.MsSql2000Dialect");
        Assert.IsNotNull(cfg.GetClassMapping(typeof(MappedEntity)));

Sincerely, I don't fell safe too that the DB is available checking the configuration object, but that's a way to know: yeah, my entities are there and I'm pointing to the right DB.

I understand that you are afraid of using a second SQLite connection and the DB was exposed in a previous one, so you will get undesired exceptions, but as far as I can see, the only other option to check if your entities are there would be something like the code below. As it refers to the SessionFactory, though, it helps nothing more than the previous option.

        tx.Session.SessionFactory.GetClassMetadata(typeof(MappedEntity)) != null

The last option that I can think, in this case, would be to execute a SQL directly to your DB with an EXISTS check. I don't know how agnostic the EXISTS command is between ALL DBs implementations, but to a simple check like we're talking here it shouldn't be a big problem.

Hope this helps!

BTW: it's jfneis. Neis is a surname. Nothing to do with fries, french fries or something like. :)

Cheers.

Filipe