Sqlite进阶之--附加数据库关联查询以及Pragma的相关使用

江左书生

数据库连接

基本的
Data Source=c:\mydb.db;Version=3;
此类库不支持版本 2。内存数据库

Data Source=:memory:;Version=3;New=True;
SQLite 数据库通常存储在磁盘上，但数据库也可以存储在内存中。在此处阅读有关SQLite 内存数据库的更多信息。使用 UTF16

Data Source=c:\mydb.db;Version=3;UseUTF16Encoding=True;有密码

Data Source=c:\mydb.db;Version=3;Password=myPassword;使用 3.3x 之前的数据库格式

Data Source=c:\mydb.db;Version=3;Legacy Format=True;使用连接池

Data Source=c:\mydb.db;Version=3;Pooling=True;Max Pool Size=100;
默认情况下不启用连接池。使用以下参数来控制连接池机制。只读连接

Data Source=c:\mydb.db;Version=3;Read Only=True;使用 DateTime.Ticks 作为日期时间格式

Data Source=c:\mydb.db;Version=3;DateTimeFormat=Ticks;
默认值为 ISO8601，它激活了 ISO8601 日期时间格式的使用
将 GUID 存储为文本

Data Source=c:\mydb.db;Version=3;BinaryGUID=False;
通常，GUID 以二进制格式存储。使用此连接字符串将 GUID 存储为文本。
请注意，将 GUID 存储为文本会占用数据库中的更多空间。指定缓存大小

Data Source=c:\mydb.db;Version=3;Cache Size=2000;
以字节为单位的缓存大小值
指定页面大小

Data Source=c:\mydb.db;Version=3;Page Size=1024;
以字节为单位测量的页面大小值在分布式事务中禁用登记

Data Source=c:\mydb.db;Version=3;Enlist=N;
禁用创建数据库行为

Data Source=c:\mydb.db;Version=3;FailIfMissing=True;
如果数据库文件不存在，默认行为是创建一个新文件。使用以下参数来引发错误，而不是创建新的数据库文件。
限制数据库大小

Data Source=c:\mydb.db;Version=3;Max Page Count=5000;
最大页数以页为单位。该参数限制数据库的最大页数。
禁用日志文件

Data Source=c:\mydb.db;Version=3;Journal Mode=Off;
这个完全禁用回滚日志。
保留日志文件

Data Source=c:\mydb.db;Version=3;Journal Mode=Persist;
这个是空白的，并在提交后将日志文件留在磁盘上。默认行为是在每次提交后删除日志文件。
控制文件刷新

Data Source=c:\mydb.db;Version=3;Synchronous=Full;
Full 指定在每次写入后完全刷新以执行操作。正常是默认值。Off 意味着底层操作系统刷新 I/O。清理、压缩数据库（磁盘中freeList）

vacuum; --针对主数据库

vacuum attachDb_Name; --针对附属、吸附的数据库 需要带上数据库别名（这个在使用Attach时别名是一致的）Attach之多个数据库关联表查询

模板:
Attach database fileName as 'Alisa';
Query Sql;案例:




image.png


如图所示 peopelmanage.sqlite是主数据库 ，ken.sqlite是吸附数据库。
string sql = @"Attach database 'F:\project_info\csharp\personal-manage\personal-manage.UI\bin\x64\Debug\database\ken.sqlite' as ken; ";

sql += "\r\n select KT.*,SYS_FILE_INFO.FILE_DISP_NAME from ken.T_TEST as KT ";
sql+= " left join SYS_FILE_INFO on SYS_FILE_INFO.RELATED_PAGE = KT.PAGE ";

  DataTable dataTable = baseDAL.SelectData(sql);ps:需要把Attach语句放在最前面，因为每次连接时Attach语句生效，这个文件数据库才会绑定（成功吸附）；一旦关闭连接，就丧失了吸附关系。
查询sqlite版本


sqlite_source_id()

sqlite_source_id() 函数返回一个字符串，该字符串标识用于构建 SQLite 库的源代码的特定版本。




image.png

sqlite_version()

sqlite_version() 函数返回正在运行的 SQLite 库的版本字符串。此函数是sqlite3_libversion() C 接口的 SQL 包装器。



image.png

PRAGMA 的用法

ps:下面的schema（模式），一般使用sqlite的文件名（不用带后缀）即可，当然schema也可以省略
查看索引

PRAGMA index_info('索引名');--

PRAGMA pragma_index_list('表名');

PRAGMA index_xinfo('索引名'); --查看索引信息


select * from pragma_index_list('表名'); --查询具体表的索引

SELECT * FROM pragma_index_info('索引名称'); -- 查看索引
--index_xinfo pragma 的输出列如下：

索引中列的排名。（0 表示最左边。关键列在辅助列之前。）
被索引的表中列的排名，如果 index-column 是被索引的表的rowid ，则为 -1，如果索引在表达式上，则为-2 。

被索引的列的名称，如果 index-column 是被索引的表的rowid或 表达式，则为 NULL 。

如果索引列按索引以反向 (DESC) 顺序排序，则为 1，否则为 0。

用于比较索引列中的值 的整理序列的名称。

如果索引列是键列，则为 1；如果索引列是辅助列，则为 0。
获取或修改应用程序ID

PRAGMA schema.application_id; --应用程序ID设置自动清理（压缩）数据库


ps : 当 auto-vacuum 模式为 1 或“full”时，仅当数据库存储了一些允许每个数据库页面向后追溯到其引用者的附加信息时，才可能进行自动清理。因此，必须在创建任何表之前打开自动清理。创建表后无法启用或禁用自动清理。
PRAGMA schema.auto_vacuum;
--查询或设置数据库的自动清空状态  ‘0’.禁止自动清理  ‘1’.启用基础清理  ‘none’:禁止自动清理  ‘2’或者full”.启动完全清理   ‘INCREMENTAL’：增量

当 auto-vacuum 模式为 1 或“full”时，freelist 页面被移动到数据库文件的末尾，并且数据库文件被截断以在每次事务提交时删除 freelist 页面。但是请注意，自动清理只会截断文件中的空闲列表页面。Auto-vacuum 不会像 VACUUM命令那样对数据库进行碎片整理，也不会重新打包单个数据库页面。事实上，因为它在文件中移动页面，所以自动真空实际上会使碎片变得更糟

当 auto-vacuum 的值为 2 或“INCREMENTAL”时（在增量模式下， 必须调用单独的incremental_vacuum pragma 以才能自动清理。）---已验证已经建表了 需要如下操作 才能更改auto_vacuum的值
PRAGMA main.auto_vacuum =2;
VACUUM;
设置增量清理

PRAGMA schema.incremental_vacuum(N);
PRAGMA schema.incremental_vacuum;incremental_vacuum pragma 导致从freelist中删除最多_N_个页面。数据库文件被截断相同的数量。如果数据库未处于 auto_vacuum=incremental模式或 freelist 上没有页面，则 incremental_vacuum pragma 无效。如果freelist 上的页数少于 N ，或者N_小于 1，或者如果省略了 "( _N )" 参数，则清除整个 freelist。

检查外键

PRAGMA foreign_key_check('表名');查看数据版本

PRAGMA data_version; --查看数据版本获取数据库列表 （能查看吸附的相关表）

PRAGMA database_list; -- 将用于列出了所有的数据库连接 main为主数据库 temp一般为临时数据库 详见附加数据库设置/获取 数据库编码

PRAGMA encoding; --一旦数据库设置了编码 就不能更改  由ATTACH命令创建的数据库始终使用与主数据库相同的编码数据库完整性检查

PRAGMA shemech.integrity_check; --对数据库进行低级格式化和一致性检查

此 pragma 对数据库进行低级格式化和一致性检查。完整性检查杂注寻找：
乱序的表或索引条目
格式错误的记录
缺少页面
缺少或多余的索引条目
UNIQUE、CHECK 和 NOT NULL 约束错误
freelist的完整性
多次使用或根本不使用的数据库部分数据库优化

1.PRAGMA  totalParse.optimize;

--为了获得最佳的长期查询性能而不需要对应用程序模式和 SQL 进行详细的工程分析，建议应用程序在关闭每个数据库连接之前运行“PRAGMA optimize”（不带参数） 。长时间运行的应用程序也可能受益于设置计时器以每隔几个小时运行一次“PRAGMA 优化”。

2.PRAGMA  totalParse.optimize(ANALYZE); //带参数快速检查;

类似于完整性检查
PRAGMA quick_check;
--- 快速检查; 类似于完整性检查，只是它不验证 UNIQUE 约束，也不验证索引内容是否与表内容匹配。通过跳过 UNIQUE 和索引一致性检查，quick_check 能够运行得更快。
PRAGMA quick_check('表名');返回有关模式中的表和视图的信息

PRAGMA schema.table_list; ---返回有关模式中的表和视图的信息，每行输出一个表,版本不低于 3.37.0
PRAGMA table_list('表名');查询架构版本

PRAGMA schema.schema_version; ---查询架构版本设置/获取用户版本

PRAGMA user_version; -- 获取或设置 用户版本整数的值查询或更改当前数据库连接的上限限制的值。(针对3以上版本)

此限制设置允许准备好的语句启动以协助查询的辅助线程数的上限。默认限制为 0，除非使用SQLITE_DEFAULT_WORKER_THREADS 编译时选项进行更改。当限制为零时，这意味着不会启动辅助线程
PRAGMA threads;
PRAGMA threads = N;

查询或更改当前数据库连接的 sqlite3_limit (db, SQLITE_LIMIT_WORKER_THREADS ,...) 限制的值。此限制设置允许准备好的语句启动以协助查询的辅助线程数的上限。默认限制为 0，除非使用SQLITE_DEFAULT_WORKER_THREADS 编译时选项进行更改。当限制为零时，这意味着不会启动辅助线程。

这个 pragma 是 sqlite3_limit (db, SQLITE_LIMIT_WORKER_THREADS ,...) 接口的薄包装。查询数据库所有表信息

如创建表sql 、表名等等
SELECT * FROM sqlite_master WHERE type='table' ORDER BY name;