|
??
FireDAC操作 SQLite
本文介绍了如何利用Delphi的firedac来开发SQLite数据库应用程序。
firedac是为Delphi提供的主流数据库访问引擎,其中特别为SQLite提供了强大的API功能。
SQLite是一个嵌入式SQL数据库引擎，在世界上广泛应用。
描述
1. 普通
参考文章有如下几段：
?介绍SQLite: 综述了SQLite的属性，没有的属性，可能的应用，不是SQLite的应用。
?使用 SQLite 数据库: 解释如何在Delphi应用程序中创建，连接和管理SQLite数据库。
?SQLite 加密数据库: 数据库加密是 SQLite 的一个重要功能. 本主题解释了它如何工作和控制.
?SQLite 数据类型: SQLite 有一个独特的数据类型系统. 不理解其工作原理,将很难有效地在Delphi应用程序中存储和检索数据.
?SQLite SQL 命令: SQLite SQL语言是为Delphi应用开发者提供的主要语言.
?SQLite 事务,锁和Cursors: 如何在SQLite环境中使用事务.
?扩展的 SQLite 引擎: 作为嵌入式数据库，SQLite引擎可通过Delphi应用程序代码来扩展。
?先进的SQLite技术：最后我们想SQLite介绍一些先进的SQLite概念，如更新日志和SQL授权。
本文需要对firedac基础知识和主库的API有一定和了解。对初学者，我们建议从入门篇看看firedac 例程开始 SQLite演示。
2. 介绍SQLite
2.1 SQLite 数据库
SQLite一个嵌入式的SQL数据库引擎,由SQLite团队开发.
这是一个应用最广泛的数据库管理系统,在世界上大约有5亿的安装部署。
你可以在所有iOS和Android移动设备中找到它，或在MAC OS和Linux桌面中。
它使用了Firefox，Skype和McAfee防毒。
2.2 SQLite 功能特性
资源列表:
?事务符合原子性,一致性,孤立性,(ACID) 即使系统崩溃或电源故障.
?零配置无需安装或管理。
?实现大多数SQL92。支持表的触发器和视图。
?存储在一个单一的跨平台的磁盘文件中的完整的数据库。
?支持(terabyte)太字节大小的数据库和(gigabyte)吉字节大小的字符串和数据块。
?比流行的大多数C/S数据库引擎的常规操作要快。
?自包含:没有外部依赖.
?跨平台：Windows，Linux，MAC OS X操作系统，iOS和Android都开箱支持。
?在公共领域开源。为任何目的使用都是免费的。
?非常强大的API，几乎在所有的领域都可以扩展引擎.
?SQLite通过Delphi应用程序使用的文件服务器和客户端服务器的数据库引擎,比其他嵌入式能实现最好的数据访问性能。
我们知道许多成功的多G数据库的应用。例如silwood技术或是建立SQLite，Delphi和firedac。
2.3 SQLite缺少的特性
根据经验，开发人员常常试图找到以下功能，但在SQLite中没有提供:
?存储过程。firedac提供自定义功能的API。
?内置函数。firedac预安装约50个标准函数。
?安全系统，包括一个用户和一个权限的概念。firedac提供密码保护的加密数据库，特殊的回调过滤用户行为。
?排序（只能用ASCII和二进制）。firedac允许定义自定义排序规则。
2.4 SQLite应用
资源列表:
?应用文件格式。SQLite作为对桌面应用的磁盘文件格式使用已经取得了巨大的成功。
?嵌入式设备应用程序。因为SQLite数据库不需要管理，SQLite是无人值守设备或服务的良好选择。
?网站。SQLite通常用于中低流量的网站数据库引擎（也就是说，所有网站的99.9%）。
?替代点对点磁盘文件。SQLite非常适合替换点对点数据文件。
?内部或临时数据库。对项目的筛选和不同的方式排序，可以更简单的将数据加载到内存中的SQLite数据库，使用S全部QL命令。
?代替演示或测试企业数据库。
?数据库教学。
2.5 不适合用SQLite的应用程序
类似资源列表:
?高并发。SQLite对整个数据库文件使用读/写锁。这意味着任何进程读取数据库的任何部分，其他所有进程被禁止写数据库任何部分。
同样，如果任何一个进程在写数据库，其他所有程序禁止读取数据库的任何其他部分。
?客户端/服务器应用程序。如果你有很多的客户端程序访问网络上的一个共同的数据库，
您应该考虑使用一个客户机/服务器数据库引擎来取代SQLite。SQLite工作在一个网络文件系统之上，
但由于大多数网络文件系统相关的延迟，性能不会很强大。
?非常大的数据集（N TB）。
3.使用SQLite数据库
3.1 Delphi应用程序连接SQLite数据库
连接FireDAC SQLite 驱动到 Delphi 应用,添加 TADPhysSQLiteDriverLink 到窗体或数据模块.
连接到SQLite 数据库,指定 SQLite 驱动参数,如下:
DriverID=SQLite
Database=<path to SQLite database>
默认情况下，所有SQLite的驱动程序设置为高性能单一连接来访问在一个稳定的环境数据库。
pragma命令允许配置SQLite。
许多firedac SQLite驱动参数对应于程序。
此外sqliteadvanced允许指定多个程序的分离";"作为一个单一的连接参数。
附加SQLite的使用情况：
1 读取大的数据库
设置 CacheSize 为页的高数,用来做数据的缓冲区大小.所有的缓冲区大小为 CacheSize * <db page size>.
2 独占更新数据库。
考虑设置JournalMode 成 WAL (more).
3 长更新事务。CacheSize设置到一个更高的页面的数量，这将允许运行事务与许多更新不超载的内存缓存脏页。
4 少量并发更新过程。
设置 LockingMode 为 Normal 启用共享数据库访问。
设置Synchronous 为 Normal 或 Full,提交数据使其它可见。
设置UpdateOptions.LockWait 为 True使等待锁定可用。
增加BusyTimeout值以提高锁等待时间。
考虑设置JournalMode 成 WAL (more).
5 多线程并发更新。
看（4）。设置SharedCache为false,最小化锁冲突。
6 少量并发更新事务。
看（4）或（5）。设置TxOptions.Isolation 为 xiSnapshot 或 xiSerializible 。尽可能避免事务死锁。
7安全性高。
设置Synchronous 为 Full 妨止提交数据丢失以保护数据库。看（3）。考虑到加密数据库提供完整。
8高保密性。加密数据库提供机密性和完整性。
9开发时间。
LockingMode 设置为 Normal 使IDE SQLite DB和调试程序能同时使用。
3.2 Delphi应用程序创建SQLite数据库
如果不存在SQLite数据库,将在建立连接后被创建。显式控制Delphi应用程序可以指定：
OpenMode=CreateUTF8 | CreateUTF16 | ReadWrite | ReadOnly
二值允许创建不同的编码用于新的数据库。我们也建议为多排表数据库集合page_size 4096以上。这可以通过指定在创建时做的：
SQLiteAdvanced=page_size=4096
考虑使用sqliteadvanced指定参数：
?auto_vacuum
?default_cache_size
注意，在创建数据库文件，它的大小为零。由于数据库编码，页面大小和其他持久性参数不记录在数据库中。
做出这样的参数持久性，应用程序应该至少创建一个表。
3.3在Delphi应用中使用SQLite内存数据库
下一个SQLite的独特的特点是在内存数据库的工作的能力。
这意味着，没有文件被创建用来存储数据库对象,所有将被保存在内存。
因此，更安全，更有效，只要求一个Delphi应用程序环境权即可。
创建和使用参数打开一个SQLite内存数据库：
DriverID=SQLite
Database=:memory:
或把数据库参数空：
driverid = SQLite
一个FireDAC客户在共享的网络资源上有一个SQLite数据库。这个数据库是一个有许多存储产品属性的只读产品目录。
要从根本上提高性能，用户可使用TADSQLiteBackup移动数据库成为内存中的数据库。示例代码：
ADConnection1.DriverName := 'SQLite';
ADConnection1.Open;
ADSQLiteBackup1.Database := 'srvdbdata.sdb';
ADSQLiteBackup1.DestDatabaSEObj := ADConnection1.CliObj;
ADSQLiteBackup1.DestMode := smCreate;
ADSQLiteBackup1.Backup;
3.4使用Unicode和SQLite数据库
Delphi（从Delphi 2009）firedac全面支持Unicode 。这意味着对SQLite：
?当使用Delphi 2009或更高,firedac自动设置SQLite数据库中的所有元数据交换为UTF-16编码。
在Delphi 2007或更早的版本,元数据是ANSI编码。
?数据定义和交换的详细描述是在“Mapping SQLite to FireDAC Data Types”章节中。
3.5在Delphi的应用中使用多个SQLite数据库
SQLite允许一个连接中使用多个数据库。一个数据库由数据库参数指定主数据库。
要附加数据库，Delphi应用程序应该使用附加命令。例如：
ADConnection1.ExecSQL('ATTACH ''c:hr.sdb'' AS hr');
ADConnection1.ExecSQL('ATTACH ''c:cust.sdb'' AS cust');
ADQuery1.Open('select * from "Orders" o ' +
'left join hr."Employees" e on o.EmployeeID = e.EmployeeID ' +
'left join cust."Customers" c on o.CustomerID = c.CustomerID');
注意，firedac把数据库名作为目录名称理解。
3.6Delphi应用程序管理SQLite数据库
一个好的Delphi（不仅）SQLite数据库的应用必须注意以下事实：
?SQLite数据库经过许多“硬”记录更新或删除会变得支离破碎和非最优。
TADSQLiteValidate.Sweep方法调用可优化数据库。
该方法对应 VACUUM 命令和 PRAGMA auto_vacuum 。
实例：
ADSQLiteValidate1.Database := 'c:db.sdb';
ADSQLiteValidate1.Sweep;
?SQLite查询优化器将生成一个更好的查询执行计划时，它有一个新的数据库统计。SQLite不自动更新统计。
TADSQLiteValidate.Analyze 方法调用收集它。该方法利用了 ANALYZE 指令。
一个应用程序可能为完整的数据库汇总统计：
ADSQLiteValidate1.Database := 'c:db.sdb';
ADSQLiteValidate1.Analyze;
?SQLite数据库可能已损坏或格式不正确。
为了验证它的完整性使用 TADSQLiteValidate.CheckOnly 方法。
注意，修复断裂的SQLite数据库，Delphi应用程序需要从备份中恢复。
onprogress checkonly方法使用事件处理程序通知有关问题。
此方法执行编译integrity_check命令。
procedure TForm1.ADSQLiteValidate1Progress(ASender: TADPhysDriverService; const AMessage: String);
begin
Memo1.Lines.Add(AMessage);
end;
ADSQLiteValidate1.Database := 'c:db.sdb';
ADSQLiteValidate1.OnProgress := Form1Progress;
ADSQLiteValidate1.CheckOnly;
?SQLite数据库必须定期备份保存的数据丢失。tadsqlitebackup组件的执行数据库备份。这是最简单的备份代码：
ADSQLiteBackup1.Database := 'c:db.sdb';
ADSQLiteBackup1.DestDatabase := 'c:db.backup';
ADSQLiteBackup1.DestMode := smCreate;
ADSQLiteBackup1.Backup;
4。sqlite数据库加密
4.1方法
一个显著特点是高速强SQLite数据库加密。它可以使数据库文件内容的保密和执行对数据库文件的完整性控制。
加密的数据库格式是不与其他类似的SQLite加密扩展兼容。
这意味着，你不能使用一个非firedac库加密的数据库加密。
如果你需要，然后你要解密数据库与原始的工具和加密与firedac。
加密是通过正式支持SQLite的方法与编解码器的代码和编译sqlite_has_codec自定义设置。
所有的加密程序是实现Delphi和嵌入程序代码。因此，正确处理加密：
?高度；
?真空；
?数据库恢复；
?数据库备份。
4.2加密模式
模式
描述
使用
aes-nnn
AES算法的NNN AES-CTR和aes-cbc-mac通用的组合物。
该组合物保证机密性和完整性，这意味着只有拥有正确的密码实体将能够读取和修改的加密数据库页。
这些算法将添加32字节每页线性开销你加密的数据库。
该算法将帮助您检测最恶意的试图注入到数据库中的数据，但这也不能阻止这样的尝试，它不会帮助你撤消修改。
它本质上是经常备份的一个补充，但是它比大多数其他的数据库加密方案更好的在提醒你，
当你受到攻击，现在是时候从备份恢复数据库。
请注意本身的AES算法，NNN，不将底数据库检测整个页面删除（但它会检测在数据库中删除），
也不能检测到攻击，包括在还原数据库到一个旧版本的加密使用相同的密码。
aes-nnn提供的最强大的保密性和完整性。但在价格的一些性能的降低，明显的在其他的加密模式。
aes-ctr-nnn
AES CTR NNN算法AES-CTR只。它将无法检测的修改数据库，但它将提供保密性对被动攻击。
那就是，只要攻击者不能访问您的密码，也不会试图修改数据库看你的应用程序如何修改的反应，
你的数据将保持为秘密作为你的应用程序允许。
不用说，该算法只会保护你的数据库文件对攻击者是无法进入你的应用程序使用例如firedac调试器和提取这样的密码。
以类似的方式，如果你在配置文件中或在软件本身经常保存你的密码，这将是微不足道的.
任何没有中等熟练的攻击者发现并危害您的安全。
aes-ctr-nnn提供的顶级保密性强，但不完整。那你会得到一个更好的性能。
aec-ecb-nnn
AES ECB NNN算法aes-ecb只。它将无法检测的修改数据库，它将不提供抵抗被动攻击保密性强，在对比和aes-ctr-nnn aes-nnn。
aes-ecb-nnn提供薄弱的保密性，完整性和不。但它具有最好的性能在其他加密模式。
有NNN是关键尺寸，这可能是128，192或256位。
4.3建立加密
加密可以控制：
?通过连接定义参数加密和密码，新密码；
?通过tadsqlitesecurity服务组件。
密码连接定义参数有形式：
【AES-128 AES-192 AES-256 | | | aes-ctr-128 | aes-ctr-192 | aes-ctr-256 |
aes-ecb-128 | aes-ecb-192 | aes-ecb-256：]密码
有“AES XXX NNN：“是一个可选的前缀，控制密码算法使用。如果未指定，则将使用：
?算法，通过加密参数指定的；
?AES-256如果没有被指定。
firedac支持加密操作：
Operation
Using parameters
Using TADSQLiteSecurity
Open encrypted database
Password=xxxx
---
Encrypt unencrypted database
NewPassword=xxxx
ADSQLiteSecurity1.Database := '...';
ADSQLiteSecurity1.Password := 'xxxx';
ADSQLiteSecurity1.SetPassword;
Change encrypted database password
Password=xxxx
NewPassword=yyyy
ADSQLiteSecurity1.Database := '...';
ADSQLiteSecurity1.Password := 'xxxx';
ADSQLiteSecurity1.ToPassword := 'yyyy';
ADSQLiteSecurity1.ChangePassword;
Decrypt encrypted database
Password=xxxx
NewPassword=
ADSQLiteSecurity1.Database := '...';
ADSQLiteSecurity1.Password := 'xxxx';
ADSQLiteSecurity1.RemovePassword;
Verify encryption database status
---
ADSQLiteSecurity1.Database := '...';
ADSQLiteSecurity1.Password := 'xxxx';
ShowMessage(ADSQLiteSecurity1.CheckEncryption);
The command got an extension. The full syntax of the ATTACH now is:
4.4 SQL的扩展
ATTACH命令有扩展。现在ATTACH完整的语法：
ATTACH [DATABASE] 'filename' [AS name] [KEY 'password']
当键被省略，那么密码的值将被从主数据库继承。要将未加密的数据库指定一个空的密码，用：
ATTACH 'D:tmptest.db' AS tst KEY ''
5。SQLite数据类型
5.1映射到数据类型SQLite firedac
SQLite有“无类型”的数据类型系统。实际上，这意味着，你可以使用任何标识符作为列的数据类型名称。例如“德尔菲法”也将工作并将对应的字符串数据类型。使SQLite的方法与其他数据库和Delphi更兼容，
更舒适的Delphi应用程序开发人员，数据类型名称firedac识别表中所述：
类型名称
描述
ROWID | _ROWID_ | OID
dtInt64,Attrs = [caSearchable,caAllowNull,caROWID]
BIT | BOOL | BOOLEAN | LOGICAL | YESNO
dtBoolean
TINYINT | SHORTINT | INT8 [UNSIGNED]
dtSByte / dtByte
BYTE | UINT8
dtByte
SMALLINT | INT16 [UNSIGNED]
dtInt16 / dtUInt16
WORD | UINT16 | YEAR
dtUInt16
MEDIUMINT | INTEGER | INT | INT32 [UNSIGNED]
dtInt32 / dtUInt32
LONGWORD | UINT32
dtUInt32
BIGINT | INT64 | COUNTER | AUTOINCREMENT | IDENTITY [UNSIGNED]
dtInt64 / dtUInt64
LONGLONGWORD | UINT64
dtUInt64
REAL | FLOAT | DOUBLE
dtDouble
SINGLE [PRECISION] [(P,S)]
dtSingle / dtBCD / dtFmtBCD
DECIMAL | DEC | NUMERIC | NUMBER [UNSIGNED] [(P,S)]
dtSByte / dtInt16 / dtInt32 / dtInt64
dtByte / dtUInt16 / dtUInt32 / dtUInt64
dtBCD / dtFmtBCD
MONEY | SMALLMONEY | CURRENCY | FINANCIAL [(P,S)]
dtCurrency
DATE | SMALLDATE
dtDate
DATETIME | SMALLDATETIME
dtDateTime
TIMESTAMP
dtDateTimeStamp
TIME
dtTime
CHAR | CHARACTER [(L)]
dtAnsiString,Len = L,Attrs = [caFixedLen]
VARCHAR | VARCHAR2 | TYNITEXT | CHARACTER VARYING | CHAR VARYING [(L)]
dtAnsiString,Len = L
NCHAR | NATIONAL CHAR | NATIONAL CHARACTER [(L)]
dtWideString,Attrs = [caFixedLen]
NVARCHAR | NVARCHAR2 | NATIONAL CHAR VARYING | STRING [(L)]
dtWideString,Len = L
RAW | TYNIBLOB | VARBINARY | BINARY | BINARY VARYING [(L)]
dtByteString,Len = L
BLOB | MEDIUMBLOB | IMAGE | LONGBLOB | LONG BINARY | LONG RAW | LONGVARBINARY | GENERAL | OLEOBJECT | TINYBLOB
dtBlob
TEXT | MEDIUMTEXT | LONGTEXT | CLOB | MEMO | NOTE | LONG | LONG TEXT | LONGCHAR | LONGVARCHAR | TINYTEXT
dtMemo
NTEXT | WTEXT | NCLOB | NMEMO | LONG NTEXT | LONG WTEXT | NATIONAL TEXT | LONGWCHAR | LONGWVARCHAR | HTML
dtWideMemo
XMLDATA | XMLTYPE | XML
dtXML
GUID | UNIQUEIDENTIFIER
dtGUID
其它
dtWideString
使用SQLite FormatOptions.StrsTrim 可用于所有的字符串类型.
5.2特殊数据类型。
定义表中字段属性为 INTEGER PRIMARY KEY AUTOINCREMENT 来给表添加一个自动递增的整数字段
这对应dtint32型,Attrs = [caAutoInc]。更多细节关于处理incrementing专栏“自读自增量字段”。
ROWID的列与_ _ ROWID的，或是被视为确定型的OID列的名称。
这对应dtint64类型， Attrs = [caSearchable,caROWID]。更多细节关于处理列读行识别独特的识别领域。
SQLite的rowid是获得一个特定行访问的最快方式。
SELECT * FROM Orders WHERE ROWID = :RID
5.3调整firedac映射
一些Delphi应用程序允许为SQLite驱动参数的调整数据的表示方法：
参数
描述
StringFormat = Choose | Unicode
When Unicode,then all dtAnsiString and dtMemo will be represented to a client as dtWideString and dtWideMemo.
GUIDFormat = String | Binary
When Binary,then dtGUID will be stored in a database as a TGUID binary value. When String,then as a string in {xxxxxxx} format. Binary requires less space in DB,String is more readable.
DateTimeFormat = String | Binary
When Binary,then dtDate,dtTime,dtDateTime will be stored in a database as a double value in Julian date format. When String,then as a character string in 'yyyy-mm-dd hh24:mi:ss' format. Binary requires less space in DB,String is more readable.
注意，当改变GUIDFormat 或 DateTimeFormat,当数据库不为空时，可能会导致firedac错误解析和阅读存储的值。
如果一个SELECT列表中的表达式没有指定类型名称信息。
当结果集不是空时，firedac将使用从第一条记录值的数据类型。
当结果集为空，将描述为dtWideString列。
对明确指定数据类型：追加 ::<类型名> 到字段别名
SELECT count(*) as "cnt::INT" FROM mytab
Delphi应用程序需要原生数据类型表示的SQLite数据库，可使用firedac映射规则。
例如，map TEXT columns to dtAnsiString and INT columns to dtInt64:
with ADQuery1.FormatOptions do begin
OwnMapRules := True;
with MapRules do begin
SourceDataType := dtMemo;
TargetDataType := dtAnsiString;
end;
with MapRules do begin
SourceDataType := dtInt32;
TargetDataType := dtInt64;
end;
end;
5.4。高精度数
由于类型系统中，长数（≥20位将被认可）的实型具有亲和性。
所以，值将被四舍五入到 REAL（Delphi与双型）型15位的精度。
解决这个问题的应用程序应该使用 TEXT 或类似的数据类型和映射规则的数据或类似的铸造dtansistring到dtfmtbcd型。
请注意，这不是一个问题，在firedac和differently矩不能分辨。
6。SQLite嵌入式数据库系统的SQL命令
虽然遵循ANSI SQL SQLite的功能和命令的部分92，但部分命令是不支持的，但也有强力增加。
你可以找到更多的信息关于SQLite的SQL.
?SQL As Understood By SQLite.
?SQLite / ANSI SQL Commands and Features Not Supported.
6.2数据库SQL批命令
该驱动程序支持的SQL批命令firedac SQLite。
分隔的SQL命令，必须用“;”。允许在任何命令和SQLite的DDL和DML语句批处理等。例如：
with ADQuery1.SQL do begin
SQL.Clear;
SQL.Add('create table dbms (id integer,name varchar(20));');
SQL.Add('insert into tab values (1,''sqlite'');');
SQL.Add('insert into tab values (2,''mysql'');');
SQL.Add('insert into tab values (3,''firebird'');');
SQL.Add('create table langs (id integer,''delphi'');');
SQL.Add('insert into tab values (2,''c'');');
SQL.Add('insert into tab values (3,''c++'');');
SQL.Add('select * from dbms;');
SQL.Add('select * from langs;');
end;
ADQuery1.Open;
// process here the DBMS list
ADQuery1.NextRecordSet;
// process here the programming languages list
6.3 dialect SQL脚本
firedac tadscript does not支持SQLite的脚本命令控制表，是从哪里开始。
6.4 dml阵列
从SQLite v 3.7.11开始支持insert命令多值。当Params.BindMode = pbByNumber,FireDac使用此特性来实现Array DML.
否则仿真Array DML.
如下:
// here ADQuery1.Params collection is filled by 3 parameters
ADQuery1.SQL.Text := 'insert into MyTab values (:p1,:p2,:p3)';
// set "by number" parameter binding mode
ADQuery1.Params.BindMode := pbByNumber;
ADQuery1.Params.ArraySize := 100;
for i := 0 to ADQuery1.Params.ArraySize - 1 do begin
ADQuery1.Params[0].AsIntegers[i] := i;
ADQuery1.Params[1].AsStrings[i] := 'qwe';
ADQuery1.Params[2].Clear(i);
end;
ADQuery1.Execute(ADQuery1.Params.ArraySize);
7。SQLite的事务，锁，线程和游标
7.1锁定和并发更新
阅读下面的原始SQLite文章：
?开始事务
?SQLite共享缓存模式
?Corruption Following Busy
SQLite作为文件服务器型的数据库管理系统，喜欢在更新时锁定数据库表。下面的设置会影响并发访问：
?当多个线程同时更新相同的数据库，设置sharedcache连接参数错误。这将有助于避免一些可能的死锁。
?当多个进程或线程同时更新相同的数据表，设置lockingmode正常启用并发访问表。
同时设置同步连接参数完全或正常。因此，SQLite数据库文件后将交易权更新完成。和其他的连接会看到在可预测的基础上更新。
?避免锁定冲突之间的联系，建立真正的和集updateoptions.lockwait busytimeout到一个更高的价值。
?避免锁定长期运行更新事务之间的冲突，来设置tadconnection.txoptions.isolation xisnapshot或xiserializible。
7.2事务和隔离模式
SQLite支持全部事务，包括检查点。进一步是一个隔离模式支持SQLite：
Mode
Corresponds to
xiDirtyRead
PRAGMA read_uncommitted = 1
xiReadCommitted
BEGIN TRANSACTION DEFERRED
xiRepeatableRead
The same as xiReadCommitted.
xiSnapshot
BEGIN TRANSACTION IMMEDIATE
xiSerializible
BEGIN TRANSACTION EXCLUSIVE
7.3 事务和 DML 命令
Surrounding writing commands into a transaction may radically improve SQLite performance.
That is notable at large data modifications.
The same is applicable to feature. So,surround the data modification code into a transaction to get the best performance:
将写命令包含进一个事务可以从根本上提高SQLite的性能。在大数据的修改方面是很明显的。
同样的情况也适用于FireDAC Array DML特性。因此，可将数据修改代码放到一个事务中以获得最佳性能：
ADConnection1.StartTransaction;
try
ADQuery1.SQL.Text := 'insert into tab values (:id,:name)';
ADQuery1.Params.ArraySize := 10;
for i := 0 to ADQuery1.Params.ArraySize - 1 do begin
ADQuery1.Params[0].AsIntegers[i] := i;
ADQuery1.Params[0].AsStrings[i] := 'name' + IntTostr(i);
end;
ADQuery1.Execute(ADQuery1.Params.ArraySize,0);
ADConnection1.Commit;
except
ADConnection1.Rollback;
raise;
end;
7.4事务和游标
SQLite不允许回滚事务，当有命令没有取结果集。解决方法是，firedac将从一个结果集取剩余的所有记录放在Rollback方法调用中。
看fetchoptions.autofetchall。
8。扩展SQLite引擎
8.1自定义函数
SQLite不支持存储过程或函数的概念，因为它允许使用主机的语言环境，扩展引擎功能。
SQLite允许注册主机的语言函数在SQLite引擎和使用SQL命令。
firedac简化引入了tadsqlitefunction组件。
建立一个函数，开发人员需要将函数名，参数个数,并创建事件处理程序 oncalculate。
设定Active to True会在SQLite引擎注册自定义函数。
例如：
procedure TForm1.ADSQLiteFunction1Calculate(AFunc: TSQLiteFunction;
AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
AOutput.AsInteger := AInputs[0].AsInteger * AInputs[1].AsInteger;
end;
ADSQLiteFunction1.DriverLink := ADPhysSQLiteDriverLink1;
ADSQLiteFunction1.FunctionName := 'XmY';
ADSQLiteFunction1.ArgumentsCount := 2;
ADSQLiteFunction1.OnCalculate := ADSQLiteFunction1Calculate;
ADSQLiteFunction1.Active := True;
自定义函数的使用:
ADQuery1.Open('select RegionID,XmY(RegionID,10) from "Region"');
函数可以调用firedac方法来查询数据库。
用默认的或不同的参数，您需要指定相同的函数名和不同数量的参数的数量创建一个自定义的函数。
那样可以在SQLite引擎注册一个重载的函数。
你可以在FireDACSamplesDBMS SpecificSQLiteUserFunc 文件夹中找到上述和其他功能的例子。
firedac实现和安装SQLite连接大约有50个函数，都是由firedac本地的表达式引擎实现的许多数据库管理系统都有的标准函数。
注意，当你在运行时创建数据库连接，你应该包括在“USES”条款uADStanExprFuncs单元，否则你会得到一个异常：
[FireDAC][Phys][SQLite] ERROR: no such function: UCASE.
让自定义函数在设计时可用，创建一个包含数据模块的自定义设计时包，使这个模块和组件设置的正确。
在单元初始化模块中创建模块，并在完成部分释放。然后安装您的软件包到Delphi IDE。
8.2自定义排序规则
SQLite存储和处理所有的字符数据非UTF8即UTF16，根据openmode连接参数。
当SQLite需要比较或字符数据类型，它必须知道使用什么样的规则。该规则被称为一个集合。
SQLite只有几个内建排序规则。
他们没有产生一个正确的德语，斯拉夫字母，阿拉伯排序，等短语。
你必须使用TADSQLiteCollation组件来构建自己的规则。
设置CollationName,Flags,Locale，然后设置 Active to True 注册到SQLite引擎。
For example:
ADSQLiteCollation1.DriverLink := ADPhysSQLiteDriverLink1;
ADSQLiteCollation1.CollationName := 'UTF16NoCase';
ADSQLiteCollation1.Flags := [sfIgnoreCase];
ADSQLiteCollation1.Active := True;
以上组件安装使用了默认的CollationKind=scCompareString实现了一个标准的Win32 Unicode排序规则.
应用程序可以使用 CollationKind=scCustomUTF16 或 scCustomUTF8 及实现 OnCompare 事件 来自定义排序规则.
如何使用建立的排序规则:
SELECT * FROM "Employees" ORDER BY LastName COLLATE UTF16NoCase
可以这样指定列的默认排序规则:
CREATE TABLE IF NOT EXISTS test_col (f1 VARCHAR(10) COLLATE UTF16NoCase)
注意,数据库或表都没有能力为连接指定默认排序规则
请看FireDACSamplesDBMS SpecificSQLiteUserCollation 文件夹.
如果不使用客制化排序规则,SQLite会默认使用二进制排序.
对于实时数据窗口模式 TADTable有相同的客户端和数据库的排序是很重要的。
设置FormatOptions.SortLocale = 0 打开客户端二进制排序.
8.3 数据库事件
firedac支持从一个SQLite数据库触发某些事件的Delphi应用程序通知，如数据的改变事件。
FireDAC使用事件类似于火鸟firedac方法和寄存器post_event自定义函数。
从一个触发器调用：
CREATE TRIGGER update_orders UPDATE ON "Orders"
BEGIN
SELECT POST_EVENT('Orders');
END;
Delphi应用程序使用 TADEventAlerter 组件接收一个事件通知.
For example:
ADEventAlerter1.Names.Text := 'Orders';
ADEventAlerter1.Options.Synchronize := True;
ADEventAlerter1.OnAlter := DoAlert;
ADEventAlerter1.Active := True;
procedure TForm1.DoAlert(ASender: TADCustomEventAlerter;
const AEventName: String; const AArgument: Variant);
begin
if CompareText(AEventName,'Orders') = 0 then
qryOrders.Refresh;
end;
8.4自定义数据源
本地SQL引擎允许你在SQL查询中使用TDataSet 派生类。firedac采用SQLite API实现本地SQL虚拟表。
9。先进的数据库技术
9.1连接数据库的更新
SQLite提供了一个独特的API允许监控所有更新到数据库。这个功能很有用，例如，记录所有更新到数据库。
用这个API工作，一个Delphi应用程序应该设置 TSQLiteDatabase 对象事件处理程序的OnUpdate，这是数据库连接的包装对象。
数据库连接打开后挂接这事件。例如：
procedure TForm1.DoUpdate(ADB: TSQLiteDatabase; AOper: Integer; const ADatabase,ATable: String; ARowid: sqlite3_int64);
begin
Memo1.Lines.Add(Format('%d - %s - %s - %u',[AOper,ADatabase,ATable,ARowid]));
end;
ADConnection1.Connected := True;
TSQLiteDatabase(ADConnection1.ConnectionIntf.CliObj).OnUpdate := DoUpdate;
This sample you can find in FireDACSamplesDBMS SpecificSQLiteOnUpdate folder.
9.2数据库访问控制权
SQLite是嵌入式数据库。 这意味着它是一个单用户数据库管理系统不需要允许权限等这样的概念。
但有一些应用程序可能要用到权限控制.
for example：
?应用可以限制的权利取决于终端用户许可。许可证演示少的可能性，充分授权-所有的可能性。
?多层数据访问框架，可以使用自己的用户的概念，使用一些通用的方法控制数据的访问权限。
再次，SQLite提供了一个独特的功能允许授权或没有SQL命令。
用这个API工作，一个Delphi应用程序的事件处理程序应设置onautorize tsqlitedatabase对象，
这是一个数据库连接的包装物。数据库连接打开后挂接这事件。例如：
procedure TForm1.DoAuthorize(ADB: TSQLiteDatabase; ACode: Integer; const AArg1,AArg2,AArg3,AArg4: String; var AResult: Integer);
begin
Memo1.Lines.Add(Format('%d - %s - %s - %s - %s',[ACode,AArg1,AArg4]));
// Deny any delete operation
if ACode = SQLITE_DELETE then
AResult := SQLITE_DENY
else
AResult := SQLITE_OK;
end;
ADConnection1.Connected := True;
TSQLiteDatabase(ADConnection1.ConnectionIntf.CliObj).OnAutorize := DoAuthorize;
This sample you can find in FireDACSamplesDBMS SpecificSQLiteOnAuthorize folder.
9.3使用SQLite的底层API
当你需要得到最大的SQLite数据访问的性能，那么你应该考虑使用SQLite API firedac包装类。
这是一种低级的面向对象的API，它是由firedac SQLite驱动应用。这个API是没有记录，没有官方支持的。
下面的示例演示如何使用参数化的SELECT命令控制事务和记录读取：
uses
uADPhysSQLiteWrapper;
procedure TForm1.FormCreate(Sender: TObject);
var
oDB: TSQLiteDatabase;
oTran: TSQLiteStatement;
oStmt: TSQLiteStatement;
i: Integer;
begin
ADConnection1.Connected := True;
oDB := TSQLiteDatabase(ADConnection1.CliObj);
oTran := TSQLiteStatement.Create(oDB);
try
// start transaction
oTran.Prepare('BEGIN');
oTran.Execute;
oStmt := TSQLiteStatement.Create(oDB);
try
// prepare statement
oStmt.Prepare('select * from "Orders" where OrderID > :ID1 and OrderID < :ID2');
// add bind variables (parameters)
for i := 1 to oStmt.ParamDefsCount do
TSQLiteBind.Create(oStmt.Params);
// add column variables (fields)
for i := 1 to oStmt.ColumnDefsCount do
TSQLiteColumn.Create(oStmt.Columns).Index := i - 1;
// set parameter values and execute
oStmt.Params[0].AsInteger := 11000;
oStmt.Params[1].AsInteger := 12000;
oStmt.Execute;
// fetch records and read columns
while oStmt.Fetch do
Memo1.Lines.Add(Format('OrderID: %d,CustomerID: %s',
[oStmt.Columns[0].AsInteger,oStmt.Columns[1].AsString]));
finally
oStmt.Free;
end;
// commit transaction
oTran.Unprepare;
oTran.Prepare('COMMIT');
oTran.Execute;
finally
oTran.Free;
end;
end;
10. 总结
本文给出了结合SQLite和firedac嵌入式数据库管理系统的解决方案的好处。 文章介绍了SQLite的驱动程序使用firedac的所有方面。 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
