Add hook for sending pragmas before opening connection
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
SQLite and SQLCipher have pragma's that need to be set before "normal" queries are executed.
However when the password is set in the connection string, the "normal" query `SELECT COUNT(*) FROM sqlite_master;` (SqliteConnectionInternal.cs:120) is executed, preventing execution of these special pragma.
```C#
var builder = new SqliteConnectionStringBuilder
{
DataSource = Path.GetTempFileName(),
Cache = SqliteCacheMode.Private,
Mode = SqliteOpenMode.ReadWriteCreate,
Pooling = false,
Password = Guid.NewGuid().ToString()
};
for(int i = 0; i < 2; i++)
{
using var conn = new SqliteConnection(builder.ConnectionString);
conn.Open(); // Fails 2nd time with Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 26: 'file is not a database'.'
using (var command = conn.CreateCommand())
{
command.CommandText = "PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA256;";
command.ExecuteNonQuery();
}
using (var command = conn.CreateCommand())
{
command.CommandText = "PRAGMA journal_mode = WAL";
command.ExecuteNonQuery();
}
}
```
[ConsoleApp11.zip](https://github.com/dotnet/efcore/files/9633065/ConsoleApp11.zip)
A few pragma's need to be set before querying sqlite_master, for example:
- journal_mode
- kdf_iter
- cipher_kdf_algorithm
- cipher_page_size
It would be nicer if I can somehow define PRAGMAs in a SQLiteConnection before opening the connection. This would also work better with connection pooling because I don't have to manually execute the PRAGMA every time when opening a connection.
### Include version information
Microsoft.Data.Sqlite version: 6.0.9
SQLitePCLRaw: 2.1.2
Target framework: .NET 6
Operating system: Windows & Android
Contributor guide
Assessment
This issue has not been assessed yet.