Oracle 表的 SQLite 提取工具、方法和陷阱

在SQLite的数据库是一个非常成功的和无处不在的软件包，它主要是未知的更大的IT社区。SQLite 的第三个主要修订版由 Richard Hipp 博士设计和编码，为细分市场中对软件质量有关键要求的许多用户提供服务，SQLite 符合DO-178B航空电子设备标准。除了在航空航天和汽车领域的强大影响力之外，大多数主要操作系统供应商（包括甲骨文、微软、苹果、谷歌和红帽）都将 SQLite 作为核心操作系统组件。

有一些怪癖可能会使用户从其他 RDBMS 环境中绊倒。SQLite 被称为“灵活类型”数据库，不像 Oracle 严格执行列数据类型；可以将字符值插入到声明为整数的 SQLite 列中而不会出错（尽管检查约束如果需要，可以加强 SQLite 类型的刚性）。虽然允许许多并发进程从 SQLite 数据库中读取数据，但在任何时候都只允许一个进程具有写权限（需要并发写入器的应用程序应谨慎使用 SQLite）。没有网络接口，所有连接都通过文件系统进行；SQLite 没有实现客户端-服务器模型。没有“时间点恢复”，备份操作基本上是 Oracle 7 风格ALTER DATAFILE BEGIN BACKUP，它制作整个数据库的事务一致性副本。GRANT和REVOKE未在 SQLite 中实现，它使用文件系统权限进行所有访问控制。没有后台进程，新连接的客户端可能会发现自己延迟并负责在这个“零管理数据库”中在后台悄悄执行的事务恢复、统计收集或其他管理功能。SQLite 的一些历史和架构可以在Hipp 博士讨论的音频和视频记录中找到。

尽管有这些怪癖，与 CSV、XML 甚至 JSON 相比，SQLite 可能是一种更高级的数据交换格式，因为可以包含索引，使接收者能够在 SQL92 中执行高速查询，而无需任何预处理、许可或激活。SQLite 保守的编码风格和评论旨在使“尚未出生的未来程序员”受益，并且磁盘数据库格式已进一步被美国国会图书馆定义为长期存储标准。

将介绍三种将 Oracle 数据库表复制到 SQLite 的方法：CSV 交换、ODBC 数据库链接和 PHP 脚本。CSV 交换可以使用现有工具和 POSIX shell 完成，除了具有目标数据的可访问帐户外，无需任何管理干预。unixODBC 方法需要管理员编译、安装和配置 SQLite ODBC数据源，然后使用 Oracle 的dg4odbcOracle 服务器 ODBC 接口附加到它们。PHP 脚本方法允许移动 LOB 类型，但可能需要编译器和开发环境来准备它。

将 Oracle 数据库内容提取到 SQLite 中有些问题，因为基本数据类型并不完全一致。在这里，我将介绍强制对齐的工具，并通过适当的转换进行提取。

假设用户有权访问 Oracle 数据库和 Oracle 的sqlplus命令行工具，并了解它们的使用（也可以使用SQLcl）。

SQLite 安全性

SQLite 专注于将数据库源代码编译成自己的应用程序的开发人员，而系统级工具似乎受到了一些忽视。sqlite3工具集合中为 Linux 提供了用于管理数据库的命令行实用程序。最新版本提供了引人注目的新功能，但编译后的二进制文件存在足够多的问题，我建议不要使用它。

• 该实用程序仅针对 32 位 Linux x86 环境编译，并且依赖于共享库，存在两个问题：
  • 许多现代 AMD64/x86_64 Linux 发行版libc默认不包含 32 位或其他依赖库，需要管理访问权限才能安装。
  • 32 位 Linux 允许程序最多使用 3 GB 的内存，这可能会不合理地限制排序和缓存，当有更多物理 RAM 可用时可以在 64 位模式下使用。
• sqlite3正如EPEL 中的hardening-check实用程序所报告的那样，工具集合中的分布式未启用任何编译器安全功能。

这些是 SQLite 分发的 Linux 工具实用程序的强化检查结果：

$ 强化检查 sqlite3; 文件 sqlite3
sqlite3：
 位置独立可执行文件：不，普通可执行文件！
 堆栈保护：不，未找到！
 Fortify Source 函数：不，仅找到未受保护的函数！
 只读重定位：不，未找到！
 立即绑定：不，未找到！
sqlite3：ELF 32 位 LSB 可执行文件，Intel 80386，版本 1 (SYSV)，动态链接（使用共享库），用于 GNU/Linux 4.3.0，已剥离

我建议下载源代码，并在具有当前编译器安全控制的现代操作系统上准备：

CFLAGS='-O3 -D_FORTIFY_SOURCE=2 -fstack-protector-strong -fpic -pie' \
LDFLAGS='-Wl,-z,relro,-z,now -Wl,-z,now' ./configure

假设这样做了，所有编译器安全控制将被启用：

$ 强化检查 sqlite3; 文件 sqlite3
sqlite3：
 位置独立可执行文件：是
 堆栈保护：是
 Fortify Source 功能：是（找到了一些受保护的功能）
 只读重定位：是
 立即绑定：是
sqlite3：ELF 64 位 LSB 共享对象，x86-64，版本 1 (SYSV)，动态链接（使用共享库），用于 GNU/Linux 2.6.32，BuildID[sha1]=34ffa395a5f985b16f6ac2e6c7c3ad5126e05bed

SQLite 是基础设施，虽然我们希望它是无懈可击的，但它应该遵守现代编译器的安全性。任何可以利用这些选项的编译器都应该。

数据线

SQLite 允许为表的列定义以下基本数据关联。组成列的行可以是NULLNUMERIC 以外的任何类型：

• TEXT - 文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
• BLOB - 数据块，完全按照输入存储。
• NUMERIC - 泛型数，尝试转化为整数或实数。
• INTEGER - 有符号，根据值的大小存储在 1、2、3、4、6 或 8 个字节中。
• REAL - 浮点值，以 8 字节 IEEE-754 格式存储。

这些类型并不适合 Oracle。

OracleNUMBER类型用于保存整数和实数，具体取决于“数据比例”的使用。Oracle 的CHAR和VARCHAR2映射到TEXT类型。DATESQLite 不直接支持该类型，我们暂时将其视为 an INTEGER。

在检查源代码后，SQLITE 数字关联实际上并不存在。只检查列数据类型的前几个字符；如果无法识别，则为其分配数字类型。展示：

$ sqlite3
SQLite 版本 3.36.0 2021-06-18 18:36:39
输入“.help”以获得使用提示。
连接到瞬态内存数据库。
使用“.open FILENAME”在持久数据库上重新打开。

sqlite> CREATE TABLE foo(bar razzamataz,ch text,fl real,whl int,mess blob);

sqlite> .dump foo
PRAGMA foreign_keys=OFF;
开始交易；
创建表 foo(bar razzamataz,ch text,fl real,whl int,mess blob);
犯罪;

sqlite> create table bar as select * from foo where 1=0;

sqlite> .dump 栏
PRAGMA foreign_keys=OFF;
开始交易；
创建表格栏（
  条 NUM，
  ch文本，
  真正的，
  whl INT，
  混乱
);
犯罪;

上面的CREATE TABLE AS SELECT(CTAS) 操作调用了 C 函数createTableStmt，它提取列亲和性。未知类型“razzamataz”被默默分配为数字，而 blob 类型被完全省略。条形表中的 NUM 关键字也无法识别。这是我在 SQLite 中找到的最直接的方法来确定列的真实数据亲和性。

源代码中定义的实际 SQLite 列关联是：

** 子串 | 亲和力
** --------------------------------
** 'INT' | SQLITE_AFF_INTEGER
** '字符' | SQLITE_AFF_TEXT
** 'CLOB' | SQLITE_AFF_TEXT
** '文本' | SQLITE_AFF_TEXT
** 'BLOB' | SQLITE_AFF_BLOB
** '真实' | SQLITE_AFF_REAL
** '浮游' | SQLITE_AFF_REAL
** '双重' | SQLITE_AFF_REAL
** 如果没有找到上表中的子串，
** 返回 SQLITE_AFF_NUMERIC。

Oracle 的二进制类型（LONG、BLOB、RAW 等）只能使用更强大的工具（如 PHP）移动，本文档稍后将对此进行讨论。任何其他奇特的 Oracle 类型都留给读者作为练习。

以下是 Oracle 的表 DDL（数据定义语言）提取器脚本sqlplus，可用于在 SQLite 中创建兼容表：

$猫texttract_ddl.sql
设置页面 50000 lin 32767 验证关闭标题关闭反馈关闭新页面无
变量自己的 varchar2(128);
变量 nam varchar2(128);
执行 :own := upper('&1');
执行 :nam := upper('&2');
选择空，'创建表' || :南|| ' (' 来自双;
选择 sep, substr(col,1,cl) col, dtype, num,
如果滞后（num）超过（按num排序）= num - 1 then null else '*' end chk from
(
  当 COLUMN_ID = 1 时选择 case 然后 null else ',' end sep, COLUMN_NAME col,
                                        'TEXT --' dtype, COLUMN_ID num,
  （从 ALL_TAB_COLUMNS 中选择 max(length(COLUMN_NAME))
    其中 OWNER = :own 和 TABLE_NAME = :nam) cl
  来自 ALL_TAB_COLUMNS
  其中所有者 = :own
  和 TABLE_NAME = :nam
  和 DATA_TYPE IN ('CHAR', 'VARCHAR', 'VARCHAR2') --'CLOB', 'NCLOB' ...
  联合所有
  当 COLUMN_ID = 1 时选择 case 然后 null else ',' end sep, COLUMN_NAME col,
         当 data_scale = 0 然后 'INT --'
              当 data_scale <> 0 然后 'REAL --'
                                   else 'NUM --' end dtype, COLUMN_ID num,
  （从 ALL_TAB_COLUMNS 中选择 max(length(COLUMN_NAME))
    其中 OWNER = :own 和 TABLE_NAME = :nam) cl
  来自 ALL_TAB_COLUMNS
  其中所有者 = :own
  和 TABLE_NAME = :nam
  和 DATA_TYPE = 'NUMBER'
  联合所有
  当 COLUMN_ID = 1 时选择 case 然后 null else ',' end sep, COLUMN_NAME col,
                                        'INT --date' dtype，COLUMN_ID 编号，
  （从 ALL_TAB_COLUMNS 中选择 max(length(COLUMN_NAME))
    其中 OWNER = :own 和 TABLE_NAME = :nam) cl
  来自 ALL_TAB_COLUMNS
  其中所有者 = :own
  和 TABLE_NAME = :nam
  和 DATA_TYPE = '日期'
  按编号排序
)
union all select null, ');', null, null, null from dual;

为了演示使用此脚本时的失败情况，我们可以将它ALL_VIEWS用于通常可以被任何连接的用户看到的视图。此视图包含一个LONG需要更强大的客户端（例如 PHP）的列。

尝试在sqlplus或 中运行脚本会SQLcl导致标记（和跳过）列：

SQL> 开始 textract_ddl sys all_views

  创建表 all_views (
  所有者文本 -- 1 *
, VIEW_NAME 文本 -- 2
, TEXT_LENGTH NUM -- 3
, TYPE_TEXT_LENGTH NUM -- 5 *
, TYPE_TEXT 文本 -- 6
, OID_TEXT_LENGTH NUM -- 7
, OID_TEXT 文本 -- 8
, VIEW_TYPE_OWNER 文本 -- 9
, VIEW_TYPE 文本 -- 10
, SUPERVIEW_NAME 文本 -- 11
, EDITIONING_VIEW 文本 -- 12
, 只读文本 -- 13
  );

上面标记的列，数字 1 和 5，表示该行上的 LAG 窗口函数失败。在任何运行中，第 1 列将始终被标记，但任何其他标志表示未处理的数据类型（在这种情况下，第LONG4 列中的a ）。