SQLite/zh CN

From Free Pascal wiki
Revision as of 13:06, 15 July 2023 by Iruanp (talk | contribs)
Jump to navigationJump to search

│ English (en) │ español (es) │ français (fr) │ 日本語 (ja) │ polski (pl) │ русский (ru) │ 中文（中国大陆）‎ (zh_CN) │

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

SQLite 和 FPC/Lazarus 支持

SQLite 是一个嵌入式的 (非服务器的) 、单用户的、可以在 FPC 和 Lazarus 应用程序中使用的数据库。许多驱动可以用于在FPC/Lazarus程序中访问SQLite数据库。 所有的驱动都需要SQLite库/dll在可执行目录（可以是你的项目目录，或者例如 (projectdir)/lib/architecture/ ，这取决于你的Lazarus项目设置）（并与你的可执行文件一起发行）以正常运作。

这可能也需要悲包含在你的 Lazarus IDE 目录. 详见 [1] 并继续阅读 TSQLite3Dataset 和 TSQLiteDataset See below

大多数 Linux发行版默认安装有 sqlite3 (例如 libsqlite3.so.0)，但至少基于Debian的发行版也需要匹配的Dev包才能获得 libsqlite3.so。两者都应通过系统包管理器安装并标记为依赖项，而不是与应用程序一起分发。

Win64: 请看 这里 关于不使用某些 FPC/Lazarus Win64 版本的警告

直接访问 SQLite

你可以使用一种简单的方式在 Lazarus 上连接到 SQLite。名为 LiteDAC 的组件。SQLite 数据访问组件(LiteDAC)是一个提供原生的SQLite连接的组件库，在 Windows, macOS, iOS, Android, Linux 和 FreeBSD 的32位及64位上的的Lazarus(和 Free Pascal)。LiteDAC 为编程者开发真正跨平台的桌面和移动的 SQLite 数据库应用程序而无需部署其它额外的库而设计。

你可以在这里下载一份这个商业产品的适用版本 Lazarus components.

内建的 SQLDB

FPC/Lazarus 提供内建的 SQLDB 组件，它提供 SQLite 数据库 (TSQLite3Connection) 从 Component Palette 的 SQLdb tab，它允许你例如使用形如 Component Palette 的数据库组件来创建图形界面。使用 SQLDB 的有点是它可以很容易地更换到一个不同的数据库例如 FireBird 或 PostgreSQL，而不必太多修改你的程序。详情请见下文。


Spatialite support

Spatialite are GIS extensions to SQLite which you can use from within SQLDB. See Spatialite.

SQLite 加密的支持

在近期的 FPC 版本 (从 FPC 3.0.0 开始, 在 2012年3月 实现), SQLDB 包括了对 SQLite3 的某些扩展版本的支持，这些版本使用 AES 算法加密 SQLite 数据库文件。 使用“Password”属性设置加密密钥。

示例:

根据您使用的内容，可能还需要在“5.0 C interface”处单独启用加密如此处所写， 在访问加密数据库之前，您需要使用产品密钥调用 sqlite3_activate_see() 。

sqlite3backup

sqlite3backup 是 FPC 提供的一个单元（不在 Lazarus 中，但可以通过编程方式使用），为 SQLite3 提供备份/恢复功能。 它使用 SQLDB 的 sqlite3conn。

Zeos

Zeos

SQLitePass

SQLitePass 组件. 2010年最后一次编码. 上次社区活跃在2011年.

TSQLite3Dataset 和 TSQLiteDataset

还有单独的TSQLiteDataset（单元 sqlites）和TSQLite3Dataset（单元 sqlite3ds）包； 有关如何使用它们的说明，请参阅下文。 访问 sqlite4fpc 主页 查找 API 参考和更多教程。

TSqliteDataset 和 TSqlite3Dataset 是 TDataSet 后代，分别访问 2.8.x 和 3.x.x sqlite 数据库。 对于新项目，您可能会使用 TSQlite3Dataset，因为 SQLite 3.x 是当前版本。

下面列出了与其他 FPC/Lazarus SQLite 驱动程序/访问方法相比的主要优点和缺点：

优点:

  • 灵活：程序员可以选择使用或不使用 SQL 语言，允许他们使用简单的表布局或 SQL/sqlite 允许的任何复杂布局

缺点:

  • 更改为其他数据库比使用 SQLDB 或 Zeos 组件更困难

Template:注意

使用SQLdb组件以使用SQLite

这些说明重点关注 SQLite 的 SQLDB (TSQLite3Connection) 细节。 有关一般概述，请查看 SqlDBHowto，其中包含有关 SQLdb 组件的一些有用信息。

有关创建为 SQLite/SQLDB（以及 Firebird/SQLDB、PostgreSQL/SQLDB，基本上任何 RDBMS SQLDB 支持）编写的支持 GUI 数据库的程序的教程，请参阅 SQLdb_Tutorial1。

我们将使用 Lazarus SQLdb 选项卡中的三个组件的组合：TSQLite3Connection、TSQLTransaction 和 TSQLQuery。 TSQLQuery 充当我们的 TDataset； 在最简单的情况下，它仅代表我们的一张表。 为了简单起见：确保您已经拥有一个现有的 SQLite 数据库文件，并且现在不需要创建新的文件。 如果您想自己声明或者在 FreePascal 中工作，TSQLite3Connection 可以在“sqlite3conn”单元中找到。

这三个组件像平常一样相互连接：在 TSQLQuery 中设置属性 Database 和 Transaction，在 TSQLTransaction 中设置属性 Database。 Transaction 和 Connection 组件中没有太多可做的事情，大部分有趣的事情将在 TSQLQuery 中完成。 配置组件如下：

TSQLite3Connection:

  • DatabaseName：将此属性设置为 SQLite 文件的文件名（绝对路径！）。 不幸的是，您不能简单地使用在设计时和运行时都保持不变的相对路径***这仍然是真的吗？ 难道您不能在构建后的 shell 脚本中复制 db 文件或对其进行符号链接吗？***。 您应该确保在应用程序启动时始终以编程方式设置文件的正确路径，无论设计时包含什么内容。

注意：要设置完整的库路径（如果您将 sqlite dll/so/dylib 放置在操作系统找不到它的地方，例如 Linux/OSX 上的应用程序目录），您可以设置 SQLiteLibraryName 属性（在建立任何连接之前，例如在主窗体的 OnCreate 事件中），如下所示： 

SQLiteLibraryName:='./sqlite3.so';

TSQLQuery:

  • SQL：将其设置为对您的表之一进行一些简单的选择查询。 例如，如果您有一个表“foo”并希望此数据集代表该表，则只需使用以下内容： 
    SELECT * FROM foo
  • Active：在 IDE 中将其设置为 True 以测试其是否全部设置正确。 这也将自动激活事务和连接对象。 如果收到错误，则连接的 DatabaseName 不正确或 SQL 查询错误。 稍后，当我们添加完字段（见下文）后，再次将它们全部设置为非活动状态，我们不希望 IDE 在测试应用程序时锁定 SQLite 数据库（单用户！）。
  • 可能不需要正确操作 - 需要检查（2012 年 6 月） 现在我们可以将字段添加到 TSQLQuery 中。 当组件仍设置为活动状态时，右键单击并“编辑字段...”。 单击“+”按钮并添加字段。 它将列出您的 SQL 查询返回的所有字段。 添加您需要的每个字段，您还可以在此处添加查找字段； 在这种情况下，只需确保您已经在其他数据集中定义了所有需要的字段，然后再开始添加引用它们的查找字段。 如果您的表有很多列并且您不需要全部列，您可以将它们省略，您还可以使您的 SQL 更具体一些。
  • 在您的代码中，您需要调用 SQLQuery.ApplyUpdates 和 SQLTransaction.Commit，当与数据感知控件一起使用时，TSQLQuery.AfterPost 和 AfterInsert 事件是执行此操作的好地方，但当然您也可以将这些调用推迟到以后的时间。 如果您不调用它们，数据库将不会更新。
  • "Database is locked" ：IDE 可能仍在锁定数据库（SQLite 是单用户数据库），您可能忘记在定义完 TSQLQuery 对象的所有字段后将组件设置为非活动状态并再次断开连接。 使用窗口的 OnCreate 事件设置路径并仅在运行时激活对象。 您在 IDE 中的 TSQLQuery 中设置的大多数内容不需要（有些甚至不允许）它们在设计时处于活动状态，唯一的例外是定义要读取表设计的字段， 所以设计时不活动应该是正常状态。
  • 您的表都应该有一个主键，并且您必须确保相应的字段具有 pfInKey 并且其 PoviderFlags 中没有其他内容（这些标志控制在自动构建更新和删除查询时如何以及在何处使用该字段）。
  • 如果您使用查找字段
    • 确保查找字段的 ProviderFlags 完全为空，这样它就不会尝试在更新查询中使用其名称。 查找字段本身不是数据字段，它只作用于另一个字段的值，即相应的关键字段，并且只有这个关键字段稍后才会在更新查询中使用。 您可以将关键字段设置为隐藏，因为通常您不希望在 DBGrid 中看到它，但需要定义它。
    • LookupCache 必须设置为 True。 在撰写本文时，由于某种原因，查找字段不会显示任何其他内容（但仍然有效），奇怪的是，使用 TSQLite3Dataset 或其他 TXXXDataset 组件时的情况恰恰相反，此处必须将其设置为 False。 我还不确定这是有意的行为还是错误。

正确设置上述所有内容后，您现在应该能够像任何其他 TDataset 一样使用 TSQLQuery，方法是通过以编程方式操作其数据，或者将 TDatasouce 放置在表单上，将其连接到 TSQLQuery，然后使用 TDBGrid 等数据控件。

创建一个数据库

从父类继承的TSQLite3Connection.CreateDB方法实际上什么也不做； 要在尚不存在文件的情况下创建数据库，您只需编写表数据，如下例所示：

（从 Lazarus 1.3 及以上版本附带的 sqlite_encryption_pragma 示例中提取的代码） 

var
  newFile : Boolean;
begin

  SQLite3Connection1.Close; // 确保启动时连接已关闭

  try
    // 由于我们是第一次创建这个数据库，
    // 检查文件是否已经存在
    newFile := not FileExists(SQLite3Connection1.DatabaseName);

    if newFile then
    begin
      // 创建数据库和表
      try
        SQLite3Connection1.Open;
        SQLTransaction1.Active := true;

        // 这里我们在新数据库中设置一个名为“DATA”的表
        SQLite3Connection1.ExecuteDirect('CREATE TABLE "DATA"('+
                    ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
                    ' "Current_Time" DateTime NOT NULL,'+
                    ' "User_Name" Char(128) NOT NULL,'+
                    ' "Info" Char(128) NOT NULL);');

        // 根据DATA表中的id创建索引
        SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "id" );');

        SQLTransaction1.Commit;

        ShowMessage('Succesfully created database.');
      except
        ShowMessage('Unable to Create new Database');
      end;
    end;
  except
    ShowMessage('Unable to check if database file exists');
  end;
 end;

创建用户定义的排序规则

// utf8 区分大小写的比较回调函数
function UTF8xCompare(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
var S1, S2: AnsiString;
begin
  SetString(S1, data1, len1);
  SetString(S2, data2, len2);
  Result := UnicodeCompareStr(UTF8Decode(S1), UTF8Decode(S2));
end;

// utf8 不区分大小写的比较回调函数
function UTF8xCompare_CI(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
var S1, S2: AnsiString;
begin
  SetString(S1, data1, len1);
  SetString(S2, data2, len2);
  Result := UnicodeCompareText(UTF8Decode(S1), UTF8Decode(S2));
end;

// 使用 SQLite3 API 注册排序规则（需要 sqlite3dyn 单元）：
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
// 或使用 TSQLite3Connection 的方法：
CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI);  

// 现在我们可以在 SQL 中使用不区分大小写的比较，例如：
// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'á'

// 但这对于 LIKE 运算符不起作用
// 为了也支持 LIKE 运算符，我们必须使用 sqlite3_create_function() 重载默认的 LIKE 函数
// http://www.sqlite.org/lang_corefunc.html#like

创建用户定义的函数

// 使用用户提供的函数重载默认 LOWER() 函数的示例
// 要运行此演示，您必须将单元'sqlite3dyn'和'ctypes'添加到您的uses-clause中
// 并添加一个值为 $800 的 const 'SQLITE_DETERMINISTIC'

procedure UTF8xLower(ctx: psqlite3_context; N: cint; V: ppsqlite3_value); cdecl;
var S: AnsiString;
begin
  SetString(S, sqlite3_value_text(V[0]), sqlite3_value_bytes(V[0]));
  S := UTF8Encode(AnsiLowerCase(UTF8Decode(S)));
  sqlite3_result_text(ctx, PAnsiChar(S), Length(S), sqlite3_destructor_type(SQLITE_TRANSIENT));
end;

// 注册函数 LOWER() 使用 SQLite3 API (requires sqlite3dyn unit):
sqlite3_create_function(SQLite3.Handle, 'lower', 1, SQLITE_UTF8 or SQLITE_DETERMINISTIC, nil, @UTF8xLower, nil, nil);

SQLite3 与日期

  • SQLite 3 不将日期存储为特殊的 DateTime 值。 它可以将它们存储为字符串、双精度数或整数 - 请参阅

http://www.sqlite.org/datatype3.html#datetime。

  • 在字符串中，根据 SQL 标准/ISO 8601，日期分隔符为“-”。因此，如果您使用内置 DATE 函数执行 INSERT，它会将其存储为“YYYY-MM-DD”之类的内容。
  • 如果数据集存储为字符串，则读取 DateTime 值可能会导致数据集出现问题：.AsDateTime 限定符可能会在 SQLite“字符串日期”上停止，但这可以通过使用类似 strftime(%d/% m/%Y,recdate) AS sqlite3recdate 在 SQL SELECT 语句中，强制 SQLite3 以指定格式返回日期记录。 （格式字符串 %d/%m/%d 对应于 .AsDateTime 能够理解的区域设置日期格式） ==> 请打开错误报告，其中包含演示问题的示例应用程序（如果是这种情况）
  • 当比较存储为字符串的日期时（例如使用 BETWEEN 函数），请记住比较将始终是字符串比较，因此取决于您存储日期值的方式。

默认值采用当地时间而不是 UTC

CURRENT_TIME、CURRENT_DATE 和 CURRENT_TIMESTAMP 返回当前 UTC 日期和/或时间。 对于本地日期和/或时间，我们可以使用： 

  DEFAULT (datetime('now','localtime')) 对于格式为 YYYY-MM-DD HH:MM:SS 的日期时间值
  DEFAULT (date('now','localtime')) 对于格式为 YYYY-MM-DD 的日期值
  DEFAULT (time('now','localtime')) 格式为 HH:MM:SS 的时间值

SQLDB 和 SQLite 故障排除

  • 请记住，为了使设计时支持（字段等）发挥作用，Lazarus 也必须找到 sqlite3.dll。
  • 数据库文件名也是如此。 如果您使用组件来提取，请始终使用绝对路径，例如 设计时的字段名称。 否则 IDE 将在其目录中创建一个空文件。 如果出现问题，请检查 lazarus/ 目录是否不包含数据库文件的零字节副本。
  • 如果您有主/从关系，则需要在每次插入后刷新主数据集，以便获取从数据集外键字段的值。 您可以在主数据集的 AfterPost 事件中通过调用以下重载过程之一来执行此操作：
interface
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
 
implementation
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
//此过程刷新数据集并将光标定位到最后一条记录
//如果数据集不能保证按自动增量主键排序，则使用
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  //获取数据库中最后插入的ID
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  //Now come back to respective row
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //刷新并返回相应行
    pDataset.Locate(pKeyField,vLastID,[]);
  end;
end;
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
//此过程刷新数据集并将光标定位到最后一条记录
//仅在保证DataSet按自动增量主键排序时使用
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //危
    pDataSet.Last;
  end;
end;

procedure TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery); //如果你的数据集是按主键排序的
end;  

procedure TDataModule1.SQLQuery2AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery, 'ID'); //如果你不确定数据集总是按主键排序
end;

真空和其他必须在事务外完成的操作

SQLDB 似乎总是需要connection，但某些操作（例如 Pragma 和 Vacuum）必须在transaction之外完成。 诀窍是结束transaction，执行你必须做的事情然后在此开始transaction(这样sqldb就不会混淆:) 

  // 提交任何挂起的操作或使用“新”sql连接
  Conn.ExecuteDirect('End Transaction');  // 结束由 SQLdb 启动的transaction
  Conn.ExecuteDirect('Vacuum');
  Conn.ExecuteDirect('Begin Transaction'); // 启动一个transaction供 SQLdb 使用

使用 TSQLite3Dataset

本节详细介绍如何使用 TSQLite2Dataset 和 TSQLite3Dataset 组件访问 SQlite 数据库。 by Luiz Américo luizmed(at)oi(dot)com(dot)br


要求

  • 对于 sqlite2 数据库 (遗留):
    • FPC 2.0.0 或更高
    • Lazarus 0.9.10 或更高
    • SQLite 运行库 2.8.15 或更高*
  • Sqlite2 不再被维护，二进制文件在sqlite网站找不到
  • 对于 sqlite3 数据库:
    • FPC 2.0.2 或更高
    • Lazarus 0.9.11 (svn revision 8443) 或更高
    • sqlite 运行库 3.2.1 或更高 (从这里获取： www.sqlite.org)

在初始化一个Lazarus项目之前, 确保:

  • sqlite库是
    • 在系统的PATH 或
    • 在可执行输出目录和Lazarus(或当前项目)目录 - 这个选项可能只在Windows上有效
  • 在Linux下, 将cmem作为主程序uses子句中的第一个单元
    • 在 Debian、Ubuntu 和其他类似 Debian 的发行版中，为了构建 Lazarus IDE，您必须安装软件包 libsqlite-dev/libsqlite3-dev，而不仅仅是 sqlite/sqlite3（也适用于 OpenSuSe）

怎样使用 (基础用法)

安装在 /components/sqlite 目录中找到的包 (见说明： here)

在设计时，设置以下属性：

  • FileName: sqlite 文件的路径 [必填]
  • TableName: sql语句中使用的表名[必填]
  • SQL：一条SQL select语句[可选]
  • SaveOnClose：默认值为 false，表示不保存更改。 可以将其更改为 true。[可选]
  • Active：需要在设计时或程序启动时设置。 [必填]

创建表 (Dataset)

双击组件图标或使用单击鼠标右键时出现的弹出菜单中的“创建表”项。 将显示一个简单的不言自明的表格编辑器。

以下是 TSqliteDataset 和 TSqlite3Dataset 支持的所有字段类型：

  • Integer
  • AutoInc
  • String
  • Memo
  • Bool
  • Float
  • Word
  • DateTime
  • Date
  • Time
  • LargeInt
  • Currency

检索数据

创建表后或使用之前创建的表，使用 Open 方法打开数据集。 如果未设置 SQL 属性，则将检索所有字段中的所有记录，如果将 SQL 设置为： 

SQL := 'Select * from TABLENAME';

将更改应用到基础数据文件

要使用ApplyUpdates函数，数据集必须至少包含一个满足主键要求的字段（值必须是UNIQUE且不为NULL）

可以通过两种方式做到这一点：

  • 将 PrimaryKey 属性设置为主键字段的名称
  • 添加一个 AutoInc 字段（这更容易，因为 TSqliteDataSet 自动将其作为主键处理）

如果设置了两个条件之一，则只需调用 

ApplyUpdates;

Template:注意

Template:注意

大纲/详细信息示例

主/详细关系的各种示例（例如客户和订单之间的关系）：

评论

  • 尽管已经用 10,000 条记录进行了测试并且工作正常，但 TSqliteDataset 将所有数据保留在内存中，因此请记住仅检索必要的数据（尤其是备注字段）。
  • 同一数据文件（Filename 属性）可以托管多个表/数据集
  • 可以使用同一个表同时创建多个数据集（不同的字段组合）
  • 可以使用sql中的WHERE语句过滤数据，关闭并重新打开数据集（或调用RefetchData方法）。 但在这种情况下，字段的顺序和数量必须保持不变
  • 也可以使用复杂的 SQL 语句，在多个表中使用别名、联接、视图（记住它们必须驻留在同一个数据文件中），但在这种情况下，ApplyUpdates 将不起作用。 如果有人想要使用复杂的查询并将更新应用到数据文件，请发邮件给我，我将给出一些如何做到这一点的提示
  • 允许将文件名设置为不是由 TSqliteDataset 创建的 sqlite 数据文件并打开它，但某些字段不会检测到正确的字段类型。 这些将被视为字符串字段。

通用示例可以在 fpc/fcl-db/src/sqlite SVN 目录中找到

也可以看看

Retrieved from "http://wiki.freepascal.org/index.php?title=SQLite/zh_CN&oldid=156906"