CODEWITHSUNDEEP
nhibernatesqliteforeign-keys
ppiotrowicz
ppiotrowicz

Reputation: 4614

enabling SQLite foreignkeys in nhibernate

I've learned that the only way to enable FK constraints in sqlite is to turn on this pragma:

PRAGMA foreign_keys = ON;

But unfortunately, if I understood correctly, I will have to execute that query in the beginning of every session. Am I correct? How can I do it "automatically" with NHibernate? Is there a way to hook into NH and execute that right after opening session? I'm using NH 3.0.

Upvotes: 2

Views: 874

Answers (1)

ppiotrowicz
ppiotrowicz

Reputation: 4614

Ok I've found the answer that works for me. Credits goes to James Kovacs (similar question)

I've created my own Driver based on SQLite20Driver and overloaded CreateConnection method (code is from James' GitHub)

public override IDbConnection CreateConnection()
    {
        DbConnection connection = (DbConnection)base.CreateConnection();
        connection.StateChange += Connection_StateChange;
        return connection;
    }

    private static void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        if ((e.OriginalState == ConnectionState.Broken || e.OriginalState == ConnectionState.Closed || e.OriginalState == ConnectionState.Connecting) &&
            e.CurrentState == ConnectionState.Open)
        {
            DbConnection connection = (DbConnection)sender;
            using (DbCommand command = connection.CreateCommand())
            {
                // Activated foreign keys if supported by SQLite.  Unknown pragmas are ignored.
                command.CommandText = "PRAGMA foreign_keys = ON";
                command.ExecuteNonQuery();
            }
        }
    }

Works like a charm :).

Upvotes: 8

Related Questions