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

2023-08-18 18:32:40

建立数据库的代码:

{建立内存数据库的一般代码:}

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 = FDConnection1

DataSource1 . DataSet    = FDQuery1

DBGrid1     . DataSource = DataSource1

FDCommand1  . 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 的表, 字段包括: ID, Name, Age, Note, 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 的表, 字段包括: ID, Name, Age, Note, Picture}

  MyExecSQL(FDConnection1, 'CREATE TABLE MyTable(ID integer PRIMARY KEY, Name string(10), Age byte, Note text, 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, Len = L, 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

码农公寓

相关文章