使用 Mode=ReadWriteCreate 在某些情况下不会在 Microsoft.Data.Sqlite 中创建数据库

Question

目前，我正试图完全删除一个Sqlite数据库，并在检测到数据库损坏时重新创建它。

但是，当I:

1. 使用Mode=ReadWriteCreate
创建数据库
2. 删除
3. 重新创建，与步骤1相同

数据库文件没有重新创建。更具体地说，是下面的代码:

using System;
using System.IO;
using Microsoft.Data.Sqlite;
namespace PotentialSqliteBug
{
class Program
{
private static string DbPath = "/Users/jbickle/Downloads/SomeDatabase.sqlite";
private static string ConnectionString = $"Data Source={DbPath};Mode=ReadWriteCreate";

static void Main(string[] args)
{
using var connection = new SqliteConnection(ConnectionString);
connection.Open();
if (File.Exists(DbPath))
Console.WriteLine("The database exists after connection was opened.");
else
{
Console.WriteLine("The database DOES NOT exist after connection was opened.");
return;
}
connection.Close();

File.Delete(DbPath);
if (File.Exists(DbPath))
Console.WriteLine("The database unexpectedly exists after attempting to delete it.");
else
Console.WriteLine("The database no longer exists after attempting to delete it, as expected.");

using var secondConnection = new SqliteConnection(ConnectionString);
secondConnection.Open();
if (File.Exists(DbPath))
Console.WriteLine("The database exists after connection was opened.");
else
Console.WriteLine("The database DOES NOT exist after connection was opened.");
}
}
}

产生以下输出:

The database exists after connection was opened.
The database no longer exists after attempting to delete it, as expected.
The database DOES NOT exist after connection was opened.

这是一个问题，因为尝试执行查询或创建事务之后会导致Sqlite错误26:"文件不是数据库"。

所以我的问题是:我是否误解了如何在Microsoft.Data.Sqlite中创建数据库?我如何重新创建一个损坏的Sqlite数据库，而不会产生这样的副作用?

一个额外的困惑:如果我删除connection.Close()行，数据库将按预期重新创建。显然，这不是一个很好的解决方案，因为永远挂起数据库连接并不理想。

如果它是重要的，这段代码是在macOS 12.3.1上执行的。net 6.0.402 SDK

Answer 1

这可能与您的第一个连接保持在作用域内有关，即使您关闭了它。您可以尝试使用connection.Dispose()显式地处理它，或者将其包装在using语句中:

using (var connection = new SqliteConnection(ConnectionString))
{
connection.Open();
if (File.Exists(DbPath))
Console.WriteLine("The database exists after connection was opened.");
else
{
Console.WriteLine("The database DOES NOT exist after connection was opened.");
return;
}
}

Answer 2

原来这是SQLite的一个副本，即使在连接关闭后也会保持数据库锁定。如果您想在关闭连接后与数据库文件进行交互，则需要调用SQLiteConnection.ClearAllPools()。处理连接是不够的。

在Windows上执行这段代码会抛出一个异常，声称当试图删除它时文件被锁定，但在macOS(可能还有Linux)中不是这种情况。