SQLite 4.9的虚拟表机制(十四)

原创

已于 2024-04-09 11:07:15 修改 · 2.1k 阅读

标签

#sql #sqlite3 #sqlite #c++ #数据库

于 2024-04-07 09:21:37 首次发布

本文围绕SQLite虚拟表展开，介绍了其用法，如通过CREATE VIRTUAL TABLE语句创建，存在临时、同名等类型；阐述了实现所需的C级对象及注册方法；还提及虚拟表和共享缓存的关系。此外，详细讲解了虚拟表的多种方法，如xCreate、xConnect等。

1. 引言

虚拟表是向打开的 SQLite 数据库连接注册的对象。从SQL语句的角度来看，虚拟表对象与任何其他表或视图类似。但在幕后，虚拟表上的查询和更新调用虚拟表对象的回调方法，而不是读取和写入数据库文件。

虚拟表机制允许应用程序发布可从 SQL 语句访问的接口，就像它们是表。SQL 语句几乎可以对他们可以对真实表执行的虚拟表，具有以下功能异常：

不能在虚拟表上创建触发器。
不能在虚拟表上创建其他索引。（虚拟表可以有索引，但必须内置到虚拟表实现。无法添加索引单独使用 CREATE INDEX 语句。
无法运行 ALTER TABLE ...针对虚拟表的 ADD COLUMN 命令。

单个虚拟表实现可能会施加额外的约束。例如，某些虚拟实现可能提供只读表。或者，某些虚拟表实现可能允许 INSERT 或 DELETE，但不允许 UPDATE。或者一些虚拟表实现可能会限制可以进行的更新类型。

虚拟表可能表示内存中数据结构。或者，它可能表示磁盘上不在 SQLite 格式。或者，应用程序可能会计算按需虚拟表。

以下是虚拟表的一些现有和假定用途：

全文搜索界面
使用 R 树的空间索引
自检 SQLite 数据库文件的磁盘内容（dbstat 虚拟表)
读取和/或写入逗号分隔值（CSV）的内容文件
像访问数据库表一样访问主机的文件系统
在统计信息包（如 R）中启用数据的 SQL 操作

请参阅虚拟表列表页面，查看实际的更长列表虚拟表实现。

1.1. 用法

虚拟表是使用 CREATE VIRTUAL TABLE 语句创建的。

创建虚拟表-stmt： 隐藏

创造虚拟桌子如果不存在架构名称.表名用模块名称(module-参数),

CREATE VIRTUAL TABLE 语句创建一个新表调用 table-name，派生自类 module-name。module-name 是为 virtual table 注册的名称 sqlite3_create_module() 接口。

CREATE VIRTUAL TABLE tablename USING modulename;

还可以为以下模块提供逗号分隔的参数模块名称：

CREATE VIRTUAL TABLE tablename USING modulename(arg1, arg2, ...);

模块参数的格式非常通用。每个模块参数可以包含关键字、字符串文本、标识符、数字和标点。每个模块参数都传递为写入（作为文本）到虚拟表实现的构造函数方法中当虚拟 table 已创建，该构造函数负责解析和解释论点。参数语法足够通用如果需要，虚拟表实现可以解释其参数作为普通 CREATE TABLE 语句中的列定义。实施还可以对参数。

创建虚拟表后，可以像使用任何其他表一样使用它表，具有上述例外情况，并由特定虚拟表实现。使用普通的 DROP TABLE 语法销毁虚拟表。

1.1.1. 临时虚拟表

没有“CREATE TEMP VIRTUAL TABLE”语句。要创建临时虚拟表，添加“临时”架构在虚拟表名称之前。

CREATE VIRTUAL TABLE temp.tablename USING module(arg1, ...);

1.1.2. 同名虚拟表

一些虚拟表自动存在于每个数据库连接，其中模块已注册，即使没有 CREATE VIRTUAL TABLE 语句。这种虚拟表称为“同名虚拟表”。要使用同名虚拟表，只需使用模块名称，就好像它是一个表一样。同名虚拟表仅存在于“主”架构中，因此它们将如果以不同的架构名称为前缀，则不起作用。

同名虚拟表的一个示例是 dbstat 虚拟表。要将 dbstat 虚拟表用作同名虚拟表，只需针对“dbstat”进行查询模块名称，就好像它是一个普通的表一样。（请注意，SQLite 必须使用SQLITE_ENABLE_DBSTAT_VTAB选项进行编译，以包括生成中的 dbstat 虚拟表。

SELECT * FROM dbstat;

如果虚拟表的 xCreate 方法完全相同，则该虚拟表是同名的函数作为 xConnect 方法，或者如果 xCreate 方法为 NULL。首次创建虚拟表时调用 xCreate 方法使用 CREATE VIRTUAL TABLE 语句。xConnect 方法每当调用时数据库连接附加到架构或重新分析架构。当这两种方法相同，表示虚拟表没有持久性需要创建和销毁的状态。

1.1.3. 仅限同名的虚拟表

如果 xCreate 方法为 NULL，则禁止对该虚拟表使用 CREATE VIRTUAL TABLE 语句。虚拟表是“仅限同名的虚拟表”。仅限同名的虚拟表可用作表值函数。

请注意，在版本 3.9.0 （2015-10-14）之前， SQLite 未检查 xCreate 方法 for NULL 在调用它之前。因此，如果仅同名虚拟表是注册SQLite版本3.8.11.1 （2015-07-29）或更早版本，并尝试对该虚拟表模块执行 CREATE VIRTUAL TABLE 命令，跳转到 NULL 将出现指针，从而导致崩溃。

1.2. 实现

虚拟表实现使用了几个新的 C 级对象：

typedef struct sqlite3_vtab sqlite3_vtab;
typedef struct sqlite3_index_info sqlite3_index_info;
typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
typedef struct sqlite3_module sqlite3_module;

sqlite3_module结构定义用于实现的模块对象虚拟表。将模块视为一个类，从中可以构造多个具有相似属性的虚拟表。例如可能有一个模块，该模块提供对以下内容的只读访问磁盘上的逗号分隔值（CSV）文件。然后，该模块可以是用于创建多个虚拟表，其中每个虚拟表引用添加到其他 CSV 文件。

模块结构包含 SQLite 调用的方法在虚拟表上执行各种操作，例如创建新的虚拟表的实例或销毁旧表的实例，读取和写入数据、搜索和删除、更新或插入行。下面将更详细地解释模块结构。

每个虚拟表实例都由一个sqlite3_vtab结构表示。 sqlite3_vtab结构如下所示：

struct sqlite3_vtab {
  const sqlite3_module *pModule;
  int nRef;
  char *zErrMsg;
};

虚拟表实现通常会将此结构子类化以添加其他私有字段和特定于实现的字段。 nRef 字段由 SQLite 核心在内部使用，不应由虚拟表实现更改。虚拟表实现可以通过将 zErrMsg 中的错误消息字符串。保存此错误消息字符串的空间必须从 SQLite内存分配函数，例如sqlite3_mprintf()或sqlite3_malloc()。在为 zErrMsg 分配新值之前，虚拟表实现必须使用 sqlite3_free（）释放 zErrMsg 的任何预先存在的内容。否则将导致内存泄漏。 SQLite 核心将在 zErrMsg 的内容释放和归零时将错误消息文本传递到客户端应用程序或何时它会破坏虚拟表。仅限虚拟表实现需要担心在覆盖 zErrMsg 内容时释放它包含新的不同错误消息的内容。

sqlite3_vtab_cursor结构表示指向特定虚拟表的行。这是sqlite3_vtab_cursor的样子：

struct sqlite3_vtab_cursor {
  sqlite3_vtab *pVtab;
};

再一次，实际实现可能会对此进行细分结构以添加其他私有字段。

sqlite3_index_info结构用于将信息传递到并从实现虚拟表。

在运行 CREATE VIRTUAL TABLE 语句之前，模块该语句中指定的必须注册到数据库连接。这是使用 sqlite3_create_module() 或 sqlite3_create_module_v2() 接口之一完成的：

int sqlite3_create_module(
  sqlite3 *db,               /* SQLite connection to register module with */
  const char *zName,         /* Name of the module */
  const sqlite3_module *,    /* Methods for the module */
  void *                     /* Client data for xCreate/xConnect */
);
int sqlite3_create_module_v2(
  sqlite3 *db,               /* SQLite connection to register module with */
  const char *zName,         /* Name of the module */
  const sqlite3_module *,    /* Methods for the module */
  void *,                    /* Client data for xCreate/xConnect */
  void(*xDestroy)(void*)     /* Client data destructor function */
);

sqlite3_create_module() 和 sqlite3_create_module_v2（）例程将模块名称与一个sqlite3_module结构和一个特定的单独客户端数据到每个模块。这两种方法之间的唯一区别create_module 是 _v2 方法包含一个额外的参数，该参数指定客户端数据指针的析构函数。模块结构定义虚拟表的行为。模块结构如下所示：

struct sqlite3_module {
  int iVersion;
  int (*xCreate)(sqlite3*, void *pAux,
               int argc, char *const*argv,
               sqlite3_vtab **ppVTab,
               char **pzErr);
  int (*xConnect)(sqlite3*, void *pAux,
               int argc, char *const*argv,
               sqlite3_vtab **ppVTab,
               char **pzErr);
  int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
  int (*xDisconnect)(sqlite3_vtab *pVTab);
  int (*xDestroy)(sqlite3_vtab *pVTab);
  int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
  int (*xClose)(sqlite3_vtab_cursor*);
  int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
                int argc, sqlite3_value **argv);
  int (*xNext)(sqlite3_vtab_cursor*);
  int (*xEof)(sqlite3_vtab_cursor*);
  int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
  int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);
  int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *);
  int (*xBegin)(sqlite3_vtab *pVTab);
  int (*xSync)(sqlite3_vtab *pVTab);
  int (*xCommit)(sqlite3_vtab *pVTab);
  int (*xRollback)(sqlite3_vtab *pVTab);
  int (*xFindFunction)(sqlite3_vtab *pVtab, int nArg, const char *zName,
                     void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
                     void **ppArg);
  int (*xRename)(sqlite3_vtab *pVtab, const char *zNew);
  /* The methods above are in version 1 of the sqlite_module object. Those 
  ** below are for version 2 and greater. */
  int (*xSavepoint)(sqlite3_vtab *pVTab, int);
  int (*xRelease)(sqlite3_vtab *pVTab, int);
  int (*xRollbackTo)(sqlite3_vtab *pVTab, int);
  /* The methods above are in versions 1 and 2 of the sqlite_module object.
  ** Those below are for version 3 and greater. */
  int (*xShadowName)(const char*);
  /* The methods above are in versions 1 through 3 of the sqlite_module object.
  ** Those below are for version 4 and greater. */
  int (*xIntegrity)(sqlite3_vtab *pVTab, const char *zSchema,
                    const char *zTabName, int mFlags, char **pzErr);
};

模块结构定义了每个虚拟的所有方法 table 对象。模块结构还包含 iVersion 字段，该字段定义模块表结构的特定版本。现在 iVersion 始终为 4 或更少，但在 SQLite 的未来版本中，该模块结构定义可以使用其他方法进行扩展，并在在这种情况下，最大 iVersion 值将增加。

模块结构的其余部分由用于实现的方法组成虚拟表的各种功能。详细介绍了这些内容续集中提供了方法。

1.3. 虚拟表和共享缓存

在 SQLite 版本 3.6.17 （2009-08-10）之前，虚拟表机制假定每个数据库连接都保留它自己的数据库架构副本。因此，虚拟表机制不能在启用了共享缓存模式的数据库中使用。如果启用了共享缓存模式，sqlite3_create_module() 接口将返回错误。这一限制被放宽了从 SQLite 版本 3.6.17 开始。