如何使用 SQLitePCL 设置“PRAGMA foreign_keys = ON"语句 [英] How to set the 'PRAGMA foreign_keys = ON' statement with SQLitePCL

问题描述

我一直在使用 SQLitePCL(目前版本 3.8.7.2)，现在决定尝试删除和通过打开外键约束更新级联.我知道默认情况下禁用此功能，根据 SQLite 文档，必须启用约束分别针对每个数据库连接.

I have been using SQLitePCL (currently ver 3.8.7.2), and now decided to experiment with Delete and Update Cascade by turning on the Foreign Key constraint. I understand this feature is disabled by default and according to SQLite documentation, the constraint must be enabled separately for each database connection.

连接字符串只需要一个数据库路径(无论如何对于SQLitePCL)并且不允许更灵活的形式的复合连接字符串data source=d:\foo\bar\mySqlite.db;foreign keys=ON.如果我必须为每个连接打开约束，如下所示，如何打开约束?

The connection string only takes a database path (for SQLitePCL anyway) and doesn't allow more flexible composite connection string of the form data source=d:\foo\bar\mySqlite.db;foreign keys=ON. If I have to turn on the constaint for every connection as shown below, how to turn on the constraint?

我期待 ISQLiteStatement API 提供一些将 PRAGMA foreign_keys = ON 语句注入到我的 connection 语句 中的方法，但没有看到明显的[Intellisense] 方法或属性来实现这一点.探索 SQLiteConnection API 甚至不是入门，因为无论如何打开外键约束都是每个连接.

I was expecting the ISQLiteStatement API to provide some means of injecting the PRAGMA foreign_keys = ON statement into my connection statement but see no obvious [Intellisense] method or property to achieve this. Exploring the SQLiteConnection API is not even a starter as turning on the foreign key constraint is per connection anyway.

注意:DeleteItemByIdQuery() 和 BindIdToDeleteItemByIdQuery() 下面的方法返回 SQL 查询字符串，为简洁起见省略了详细信息.

Note: DeleteItemByIdQuery() and BindIdToDeleteItemByIdQuery() methods below return SQL Query strings and details omitted for brevity.

using (ISQLiteStatement statement = new SQLiteConnection("d:\foo\bar\mySqlite.db").Prepare(DeleteItemByIdQuery()))
{
    BindIdToDeleteItemByIdQuery(statement, id);
    SQLiteResult result = statement.Step();
}

我是否忽略了一些简单的事情，或者这是不可能的?帮助！

Am I overlooking something simple, or is this impossible? Help!

推荐答案

好的，我想通了:

保持 SQLiteConnection 作为外部 using 块并执行 PRAGMA 序列和其中的主语句.有趣的是，我以为我以前也尝试过，但没有得到现在得到的结果——当时可能还有其他错误.

Keep the SQLiteConnection as an outer using block and execute a sequence of PRAGMA and the main statement within it. Funny I thought I tried the same before and didn't get the result I'm getting now -- other mistakes may have been at work then.

using (var conn = new SQLiteConnection(@"d:\foo\bar\mySqlite.db"))
{
    // First turn ON the FK constraint

    using (var statement = conn.Prepare(@"PRAGMA foreign_keys = ON"))
    {
        SQLiteResult result = statement.Step();
    }

    // Then Delete item that will Cascade deletion in referencing table(s)

    using (var statement = conn.Prepare(SqlDeleteItemById()))
    {
        SqlBindIdToDeleteItemById(statement, id);
        SQLiteResult result = statement.Step();
    }
}

这篇关于如何使用 SQLitePCL 设置“PRAGMA foreign_keys = ON"语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！