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

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

{建立内存数据库的一般代码:}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 TitleFont.Charset = DEFAulT_CHARSET TitleFont.color = clWindowText TitleFont.Height = -11 TitleFont.name = 'Tahoma' TitleFont.Style = [] 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 命令更简单:

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}  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,Picture blob)');  {查看表}  FDquery1.Open('SELECT * FROM Mytable');end;

关于数据类型,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,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
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

总结

以上是内存溢出为你收集整理的FireDAC 下的 Sqlite [4] - 创建数据库全部内容，希望文章能够帮你解决FireDAC 下的 Sqlite [4] - 创建数据库所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。