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
- True,在连接后会发送
- 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#cache和https://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。
其他
参考连接:
- Microsoft.Data.Sqlite Namespace
- System.Data.SQLite vs Microsoft.Data.Sqlite
- How do you use LINQ with Sqlite
- SQLite LINQ Provider Data Context
- https://github.com/linq2db/linq2db
- LINQ to SQL
- Using SQLite database in .NET with LINQ to SQL and Entity Framework 6
- https://www.devart.com/
- https://linq2db.github.io/
(此处是底部)