Microsoft.Data.Sqlite overview阅读笔记。

Microsoft.Data.Sqlite overview

需要安装料包:Microsoft.Data.Sqlite

Connection strings

连接描述遵循https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-strings

Keywords

  • Data Source
    • DataSource和Filename用于指示数据库的路径
    • 可以相对于当前工作目录
    • 如果为空,则会使用临时的硬盘数据库
    • 如果为:memory:,则会使用临时的内存数据库
    • |DataDirectory|开头的会被当做相对路径处理
    • 也支持https://www.sqlite.org/uri.html作为路径、
  • 连接模式
    • ReadWriteCreate
    • ReadWrite
    • ReadOnly
    • Memory
  • Cache
    • Default
    • Private,每个连接都是私有的缓存
    • Shared,多连接共享缓存
  • Password
    • 若指定,在连接后会发送PRAGMA key
  • Foreign Keys
    • True,在连接后会发送PRAGMA foreign_keys = 1
    • False,在连接后会发送PRAGMA foreign_keys = 0
    • (empty),不发送PRAGMA foreign_keys
  • Recursive Triggers
    • True,在连接后会发送`PRAGMA recursive_triggers·
    • False,不发送,为默认选项
  • Default Timeout
    • Command Timeout 是此别名
    • 默认为30
  • Pooling
    • True,支持池化链接,默认项
    • False,

Connection string builder

可以用https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqliteconnectionstringbuilder创建连接描述。

var connectionString = new SqliteConnectionStringBuilder(baseConnectionString)
{
    Mode = SqliteOpenMode.ReadWriteCreate,
    Password = password
}.ToString();

Examples

基本的

Data Source=Application.db;Cache=Shared

加密的

Data Source=Encrypted.db;Password=MyEncryptionKey

只读的

Data Source=Reference.db;Mode=ReadOnly

驻内存的

Data Source=:memory:

驻内存且共享的

Data Source=Sharable;Mode=Memory;Cache=Shared

Data types

SQLite基本数据类型为NTEGER, REAL, TEXT, 和 BLOB。与之对应的.NET数据类型较丰富。具体查表。

Alternative types

备选数据类型。

Column types

SQLite中的类型是关联到取出的量值上的,而不是数据库的表列上的。

但是表列的类型会影响到https://www.sqlite.org/datatype3.html#type_affinity。只推荐使用INTEGER, REAL, TEXT, 以及 BLOB这四种初始类型。

SQLite允许指定类型的细面,比如长度、精度、以及缩放等等。但是数据库引擎不对这些做强制。

Parameters

参数可以用来保证输入的内容被当作量值处理,而不作为SQL的一部分处理。

参数可以以:@$开头。

Truncation

可以使用Size辖属来截断TEXT以及BLOB值。

// Truncate name to 30 characters
command.Parameters.AddWithValue("$name", name).Size = 30;

Alternative types

例子:

command.CommandText =
@"
    SELECT count(*)
    FROM task
    WHERE finished IS NULL
        AND julianday('now') - julianday(started) > $expected
";
// Convert TimeSpan to days instead of text
command.Parameters.AddWithValue("$expected", expected).SqliteType = SqliteType.Real;

Output parameters

SQLite不支持输出参数。可以直接使用查询结果中的返回值。

Database errors

与SQLite交互时可能会抛出SqliteException类型的异常,其中的错误信息有SQLite提供。SqliteErrorCode 和SqliteExtendedErrorCode 给出SQLite的https://www.sqlite.org/rescode.html

Locking, retries, and timeouts

多连接访问同一个数据库的时候容易冲突。很多时候,这些冲突可以通过https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/connection-strings#cachehttps://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/async避免。

当Microsoft.Data.Sqlite遇到繁忙或者锁上的时候,会自动尝试,直到超时。

可以设置https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqlitecommand.commandtimeout以提高超时时间,默认为30秒,设为0表示立即超时。

有些时候,比如BeginTransaction过程中,Microsoft.Data.Sqlite要创建一个隐式的命令客件。对这些命令设置超时,需要使用https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqliteconnection.defaulttimeout

Transactions

事务可以批处理SQL语句,并在失败的时候进行回滚。

Concurrency

SQLite,一次只能有一个正在进行的事务。所以,已有事务的情况下,执行BeginTransaction以及Execute有可能超时。

Isolation levels

事务默认是序列化的,但是在使用共享缓存的时候,SQLite支持read uncommitted

  • dirty read,吊起在某个事务中的改动吖在此事务之外的询查中返回
  • nonrepeatable read,一个事务询查了某个表行两次,但是返回不同的结果,因为中间其他事务对此表行做了改动
  • Phantoms,哪些为了应付where句段,而添加或改变的表行。

传给BeginTransaction的IsolationLevel所指示的是最小等级,具体的等级会提升到ReadUncommitted或serializable。

Deferred transactions

从5.0开始,事务可以被延后,也就是当事务在其第一条命令执行时才创建。 同时,事务可以首先是读取型的,然后按需升级到写入型的。

Savepoints

从6.0开始,引入了savepoints,可以用于创建嵌套的事务。

Batching

SQLite默认不支持批处理,但是为了支持ADO.NET,Microsoft.Data.Sqlite为批处理提供了适配。

Metadata (Sqlite)

ADO.NE有两个命令可以获取元数据,一个获取查询结果的元信息,另一个获取数据库廓图的元素据。

Query result metadata

SqliteDataReader的https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqlitedatareader.getschematable可以用于获取查询结果的元数据。

Schema metadata

获取数据廓图的元数据需要查询https://www.sqlite.org/fileformat.html#storage_of_the_sql_database_schema

SQL features

Encryption

默认情况下SQLite不提供加密功能,可以通过额外的版本来支持,比如SEE, SQLCipher, SQLiteCrypt, wxSQLite3, SQLiteMultiCipher等等。

文章以SQLCipher为例讲解。

Custom SQLite versions

Microsoft.Data.Sqlite构建于SQLitePCLRaw之上,可对SQLitePCLRaw进行自定义。

Bundles

Microsoft.Data.Sqlite导入了SQLitePCLRaw.bundle_e_sqlite3作为默认的SQLite3。使用不同的绑垛,安装Microsoft.Data.Sqlite.Core,然后带上你要的绑垛:

  • SQLitePCLRaw.bundle_e_sqlite3
  • SQLitePCLRaw.bundle_e_sqlcipher
  • SQLitePCLRaw.bundle_green
    • Same as bundle_e_sqlite3, except on iOS where it uses the system SQLite library.
  • SQLitePCLRaw.bundle_sqlite3
    • Uses the system SQLite library.
  • SQLitePCLRaw.bundle_winsqlite3
    • Uses winsqlite3.dll, the system SQLite library on Windows 10
  • SQLitePCLRaw.bundle_zetetic
    • Uses the official SQLCipher builds from Zetetic (not included).

例子:

dotnet add package Microsoft.Data.Sqlite.Core
dotnet add package SQLitePCLRaw.bundle_e_sqlcipher

SQLitePCLRaw available providers

如果不使用绑垛,那么可以使用下面的SQLite提供者:

  • SQLitePCLRaw.provider.dynamic
  • SQLitePCLRaw.provider.e_sqlite3
    • The e_sqlite3 is the default provider.
  • SQLitePCLRaw.provider.e_sqlcipher
  • SQLitePCLRaw.provider.sqlite3
    • The sqlite3 provider is a system-provided SQLite for iOS, macOS, and Linux.
  • SQLitePCLRaw.provider.sqlcipher
    • The sqlcipher provider is for official SQLCipher builds from Zetetic.
  • SQLitePCLRaw.provider.winsqlite3
    • The winsqlite3 provider is for Windows 10 environments.

例子:

dotnet add package Microsoft.Data.Sqlite.Core
dotnet add package SQLitePCLRaw.core
dotnet add package SQLitePCLRaw.provider.sqlite3

代码示例:

            SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());

            using var connection = new SqliteConnection();
            Console.WriteLine($"System SQLite version: {connection.ServerVersion}");

Use the dynamic provider

通过SQLitePCLRaw.provider.dynamic_cdecl可以使用自编译的SQLite版本。

首先需要实现IGetFunctionPointer:

class NativeLibraryAdapter : IGetFunctionPointer
{
    readonly IntPtr _library;

    public NativeLibraryAdapter(string name)
        => _library = NativeLibrary.Load(name);

    public IntPtr GetFunctionPointer(string name)
        => NativeLibrary.TryGetExport(_library, name, out var address)
            ? address
            : IntPtr.Zero;
}

其次需要配置SQLitePCLRaw:

SQLite3Provider_dynamic_cdecl
    .Setup("sqlite3", new NativeLibraryAdapter("sqlite3"));
SQLitePCL.raw.SetProvider(new SQLite3Provider_dynamic_cdecl());

Limitations

Comparison to System.Data.SQLite

System.Data.SQLite是Robert Simpson于2005年创建的ADO.NET 2.0的SQLite适配器。后来分叉出Mono.Data.Sqlite项目。

n 2017, .NET Core 2.0 experienced a change in strategy. It was decided that compatibility with .NET Framework was vital to the success of .NET Core. Many of the removed APIs, including the DataSet APIs, were added back.

两者最大的差别在对数据类型的处理上。Microsoft.Data.Sqlite暴露了INTEGER, REAL, TEXT, 以及 BLOB等四种初始类型。

Microsoft.Data.Sqlite的连接描述符有更少的关键字,可以直接向数据库发PRAGMA嘛。

Microsoft.Data.Sqlite没有为SQLite的authorization callback提供用编口,参考https://github.com/dotnet/efcore/issues/13835

Microsoft.Data.Sqlite没有为SQLite的data change notifications提供用编口,具体参考https://github.com/dotnet/efcore/issues/13827

Microsoft.Data.Sqlite没有为SQLite的virtual table提供用编口,具体参考https://github.com/dotnet/efcore/issues/13823

其他

参考连接:

(此处是底部)