FireDAC 下的 Sqlite [4] - 创建数据库_sql

概述建立数据库的代码: {建立内存数据库的一般代码:}begin FDConnection1.DriverName := 'SQLite'; //同 FDConnection1.Params.Add('DriverID=SQLite');// FDConnection1.Params.Add('Database=:memory:'); //可省略这行, FireDAC 的源码显示, if D 建立数据库的代码:

{建立内存数据库的一般代码:}begin FDConnection1.Drivername := 'sqlite'; //同 FDConnection1.Params.Add('DriverID=sqlite');// FDConnection1.Params.Add('Database=:memory:'); //可省略这行,FireDAC 的源码显示,if Database = '' then Database := ':memory:';// FDConnection1.Params.Add('sqliteAdvanced=page_size=4096'); //可指定内存页大小,这是默认值 FDConnection1.Connected := True;end{建立文件数据库的一般代码:}begin FDConnection1.Params.Add('DriverID=sqlite'); FDConnection1.Params.Add('Database=C:\Temp\New1.sdb'); //如果文件存在就打开,不存在就建立// FDConnection1.Params.Add('sqliteAdvanced=temp_store=Memory'); //可强制临时文件在内存以提高效率. 0:DEFAulT; 1:file; 2:MEMORY// FDConnection1.Params.Add('sqliteAdvanced=temp_store_directory=C:\Temp'); //默认的临时文件路径应该是 C:\documents and Settings\user-name\Local Settings\Temp\// FDConnection1.Params.Add('OpenMode=CreateUTF8'); //默认是 CreateUTF8,也可选择 CreateUTF16// FDConnection1.Params.Add('LockingMode=normal'); //默认是多用户模式,如果使用独占模式 LockingMod=Exclusive 会更有效率 FDConnection1.Connected := True;end;

所有建立参数参见: http://www.sqlite.org/pragma.html

先在空白窗体上添加: TFDConnection、TFDPhyssqliteDriverlink、TFDGUIxWaitCursor; 数据库的建立主要通过 TFDConnection 完成.
同时添加用于呈现数据的 TFDquery、TDataSource、TDBGrID,还要添加一个 TFDCommand 用于提交建表命令,然后调整如下属性:

FDquery1    . Connection = FDConnection1DataSource1 . DataSet    = FDquery1DBGrID1     . DataSource = DataSource1FDCommand1  . Connection = FDConnection1

你可以复制下面文本框中的内容,然后直接往窗体上贴,以快速完成以上的添加过程:
object FDConnection1: TFDConnection
left = 34
top = 24
end
object FDPhyssqliteDriverlink1: TFDPhyssqliteDriverlink
left = 143
top = 24
end
object FDGUIxWaitCursor1: TFDGUIxWaitCursor
ProvIDer = 'Forms'
left = 260
top = 24
end
object FDquery1: TFDquery
Connection = FDConnection1
left = 32
top = 80
end
object DataSource1: TDataSource
DataSet = FDquery1
left = 148
top = 80
end
object FDCommand1: TFDCommand
Connection = FDConnection1
left = 264
top = 88
end
object DBGrID1: TDBGrID
left = 24
top = 144
WIDth = 409
Height = 137
DataSource = DataSource1
Taborder = 0
@[email protected] = DEFAulT_CHARSET
@[email protected] = clWindowText
@[email protected] = -11
@[email protected] = 'Tahoma'
@[email protected] = []
end

测试代码:

procedure TForm1.FormCreate(Sender: TObject);const dbPath = 'C:\Temp\sqliteTest.sdb';begin if fileExists(dbPath) then Deletefile(dbPath); with FDConnection1 do begin Params.Add('DriverID=sqlite'); Params.Add('Database=' + dbPath); Connected := True; end; {创建一个名为 Mytable 的表,字段包括: ID,name,Age,Note,Picture} with FDCommand1.CommandText do begin Add('CREATE table Mytable('); Add('ID integer PRIMARY KEY,'); //Integer 类型,同时设为主键 Add('name string(10),'); //能容下 10 个字符的 String 类型 Add('Age byte,'); //Byte 类型 Add('Note text,'); //Memo 类型 Add('Picture blob'); //Blob(二进制)类型 Add(')'); end; FDCommand1.Active := True; {查看表} FDquery1.Open('SELECT * FROM Mytable');end;

效果图:

直接使用 TFDConnection 提交 DDL 命令更简单:

FDConnection1.Execsql('CREATE table Mytable(ID integer PRIMARY KEY,name string(10),Age byte,Note text,Picture blob)'); {查看表} FDquery1.Open('SELECT * FROM Mytable');end;

使用 sqlite 底层包装完成的建表提交(这样应该更有效率):

uses FireDAC.Phys.sqliteWrapper; //为使用 TsqliteStatement{使用 TsqliteStatement 完成的提交 sql 命令的函数}procedure MyExecsql(ACon: TFDConnection; const Asql: String);begin with TsqliteStatement.Create(ACon.CliObj) do try Prepare(Asql); Execute; while PrepareNextCommand do Execute; finally Free; end;end;procedure TForm1.FormCreate(Sender: TObject);const dbPath = 'C:\Temp\sqliteTest.sdb';begin if fileExists(dbPath) then Deletefile(dbPath); with FDConnection1 do begin Params.Add('DriverID=sqlite'); Params.Add('Database=' + dbPath); Connected := True; end; {创建一个名为 Mytable 的表,Picture} MyExecsql(FDConnection1,'CREATE table Mytable(ID integer PRIMARY KEY,Arial; Font-size:14px; line-height:18px">关于数据类型,sqlite 本身只支持(Null,Integer,Real,Text,Blob),但我们可以放心使用 Delphi 的大多数类型(也包括 Delphi 没有的),因为 FireDAC 幕后做了转换工作.
sqlite 到 FireDAC 数据类型映射表: ( http://docwiki.embarcadero.com/RADStudio/XE6/en/Using_SQLite_with_FireDAC )

Type name	Description
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,sans-serif; color:#000000">dtSByte / dtInt16 / dtInt32 / dtInt64 dtByte / dtUInt16 / dtUInt32 / dtUInt64 dtBCD / dtFmtBCD
money \| smallmoney \| currency \| financial [(p,sans-serif; color:#000000">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)]		nchar \| national char \| national character [(l)]	dtWIDeString,sans-serif; color:#000000">nvarchar \| nvarchar2 \| national char varying \| string [(l)]	raw \| tyniblob \| varbinary \| binary \| binary varying [(l)]	dtByteString,sans-serif; color:#000000">blob \| mediumblob \| image \| longblob \| long binary \| long raw \| longvarbinary \| general \| oleobject \| tinyblob	dtBlob
mediumtext \| longtext \| clob \| memo \| note \| long \| long text \| longchar \| longvarchar \| tinytext	dtMemo
text \| ntext \| wtext \| nclob \| nmemo \| long ntext \| long wtext \| national text \| longwchar \| longwvarchar \| HTML	dtWIDeMemo
xmldata \| xmltype \| xml	dtXML
guID \| uniqueIDentifIEr	dtGUID
other data types	dtWIDeString