Files

T

cheney fe28ddeaac feat: HSM main/slave certificate comparison with batch SQL generation

- compare usercert_tab by subject raw bytes + certtype
- generate single annotated CSV report
- generate merge-<timestamp>.sql in current directory for DBA execution
- preserve GBK DN bytes via ATTACH + INSERT OR REPLACE / UPDATE
- compatible with sqlite3 3.6.20 and JDK 1.8

2026-11-04 15:18:42 +08:00

48 KiB

Raw Permalink Blame History

HSM 主备数据库证书比对工具设计文档

项目	内容
文档版本	v0.4
编写日期	2026-11-04
状态	已更新（批量 SQL 方案）
关联文档	`需求文档.md`
目标环境	172.1.41.139（CentOS 6.5 / JDK 1.8 / SQLite）
测试数据	`/app/cert-compare/main.db`（4 条）、`/app/cert-compare/slave.db`（15 条）

1. 设计概述

工具采用命令行 + 交互式 Shell 的形态，读取主库、从库中的 usercert_tab，以 (subject 原始字节, certtype) 为身份键进行比对，输出一份包含三类差异的 CSV 报表：

主库多（仅主库存在）；
从库多（仅从库存在）；
不一致（两边都有，比较字段不同）。

报表中预留“合并选择”列，由业务人员填写“主”或“备”。sql 读取标注后的报表，生成在主库执行的批量 SQL 脚本：

以主库为基准；
从库多的证书全部补入；
不一致的证书按标注选择主库或从库版本；
主库多的证书自然保留；
工具只生成脚本，由 DBA 在主库执行；执行前备份，从库只读引用。

设计重点：

编码安全：全程以原始字节处理 subject/issuer，禁止使用 JDBC getString() 读取 GBK 字段；
身份键正确：DN + certtype，避免双证场景误判；
报表驱动合并：合并决策来自人工标注，不在代码里写死主库优先；
字节级脚本：ATTACH 从库后由 SQLite 内部复制行，脚本中只出现 hex(subject)，不出现 DN 文本；
非破坏性：工具不写任何数据库；脚本使用事务，失败可回滚，执行前备份主库；
无日志文件：运行信息、错误只输出到控制台；
老环境兼容：JDK 8 字节码、sqlite-jdbc 3.36.0.3、无外网、无 Maven，脚本兼容 sqlite3 3.6.20。

2. 环境验证报告

验证时间：2026-11-03，验证账号：root@172.1.41.139。以下结论均为实际执行结果。

2.1 服务器基础环境

项目	实测结果
操作系统	Red Hat Enterprise Linux Server release 6.5 (Santiago)
内核	Linux 2.6.32-431.el6.x86_64
架构	x86_64
glibc	2.12
CPU	8 核
内存	7.8 GB 总量，约 7.1 GB 可用
磁盘	`/` 117 GB，剩余 63 GB
时区/时间	CST（UTC+8）
语言环境	`LANG=zh_CN.UTF-8`
`/app/cert-compare`	root 可读写，验证通过
`/tmp`	可写，sqlite-jdbc 可解压并加载本地库

2.2 Java 与构建工具

项目	实测结果
`java -version`	1.8.0_152（HotSpot 64-Bit）
`javac -version`	1.8.0_152
默认 `file.encoding`	UTF-8
`sun.jnu.encoding`	UTF-8
Maven / Gradle	服务器未安装
本地构建环境	JDK 8 + Maven 3.8.5 可用

结论：服务器只需 JRE 8 即可运行；在本地构建 Java 8 目标字节码的 fat jar 后上传部署。

2.3 SQLite 与 JDBC 驱动

项目	实测结果
`sqlite3 --version`	3.6.20（CentOS 6.5 自带，较旧）
`PRAGMA journal_mode`	`delete`（非 WAL）
`PRAGMA encoding`	`UTF-8`（仅文件头声明，见 2.5）
`PRAGMA integrity_check`	main.db / slave.db 均为 `ok`
sqlite-jdbc 3.36.0.3	在服务器加载成功，读写正常，选用
sqlite-jdbc 3.8.11.2	可运行，但版本过旧，不选
sqlite-jdbc 3.45.3.0	启动报 `NoClassDefFoundError: org/slf4j/LoggerFactory`，需额外 slf4j 依赖，不选
内置 SQLite 版本（3.36.0.3）	3.36.0
`VACUUM INTO`（JDBC）	3.36.0 支持，但本方案已不使用
`VACUUM INTO`（系统 CLI）	3.6.20 不支持，报 `near "INTO": syntax error`

结论：使用 org.xerial:sqlite-jdbc:3.36.0.3，其原生库可在 glibc 2.12 上运行；生成的脚本不得使用 3.6.20 不支持的新语法（CTE、UPSERT、VACUUM INTO、row-value 等）。

2.4 数据库与数据特征

项目	main.db	slave.db
文件大小	459,776 字节	459,776 字节
表数量	13	13
`usercert_tab` 记录数	4	15
certtype 分布	0:1、1:1、2:1、5:1	0:5、2:3、4:1、5:6
不一致测试数据	3 组	3 组
重复身份键 `(subject,certtype)`	无	无
同一 DN 多证书	无	有：`sge` 的 certtype=0 与 2
重复 SN	无	无
日期格式异常	无	无
完整性检查	ok	ok
文件占用	无进程持有	无进程持有

usercert_tab 主键：

PRIMARY KEY (`subject`, `certtype`)

关键字段：organid, subject, issuer, sn, pubkey_alg, pubkey, sign_alg, signinfo, not_before, not_after, certfile, certtype, opertime, stat。

2.5 编码验证（关键）

现象：SQLite 头部声明 UTF-8，但 subject/issuer 实际是 GBK 字节。

以 main.db 唯一记录为例：

JDBC getString("subject") 长度：109（包含替换字符 U+FFFD）
CAST(subject AS BLOB) 长度：110 字节
原始十六进制：... 40 B3 C2 B3 AC 40 ...
GB18030 解码：... @陈超@ ...

B3 C2 B3 AC 是“陈超”的 GBK 编码；
直接用 getString() 会把 B3 C2 等非法 UTF-8 字节替换为 U+FFFD，导致不同 DN 可能被错误合并；
通过 CAST(subject AS BLOB) / hex(subject) 获取原始字节后，按 GB18030 解码显示完全正确。

设计结论：

比对、键计算全部使用原始字节；
仅在展示和 CSV 输出时按 GB18030 解码；
数据写入由 SQLite 内部完成，不经过 Java String。

2.6 批量 SQL 机制验证

主备 DN 以 GBK/GB18030 字节存储在 TEXT 列中，直接 getString() 会产生 U+FFFD。因此合并改为生成由 DBA 在主库执行的 SQL 脚本；脚本以 ATTACH 引用从库并按 hex(subject) + certtype 定位，复制过程完全在 SQLite 内部完成。

早期在 main.db 副本上的机制验证结果：

ATTACH DATABASE ... AS cert_src 成功；
INSERT OR REPLACE ... SELECT ... FROM cert_src.usercert_tab 成功；
插入前后 hex(subject) 完全一致；
typeof(subject) 保持 text，不是 blob；
事务回滚后记录数恢复，回滚正常；
说明 ATTACH + INSERT ... SELECT 可在不接触 Java 字符串的情况下保持 GBK 字节和字段类型。

服务器端（sqlite3 3.6.20）对生成脚本的执行验证：

rows|16
ok

服务器实际脚本（dn.match=EXACT）为 1 条 INSERT OR REPLACE ... SELECT，覆盖 13 个身份键；
dn.match=TRIM 且主备 DN 字节不同的场景由单元测试覆盖，生成 UPDATE ... SET col = (SELECT ...)；
重复执行后仍为 16 条（幂等）；
执行前后 main.db / slave.db 的 SHA-256 不变；
“陈超”记录 hex(subject) 与主库逐字节一致（221 字节），iconv 解码中文正常。

设计结论：sql 只生成脚本，不直接写数据库；脚本使用 ATTACH + INSERT OR REPLACE / UPDATE，由 SQLite 内部按原始字节复制，从而彻底规避 Java 侧编码问题。此前的 VACUUM INTO 快照方案已取消。

2.7 字段与证书一致性验证

使用 cryptography 解析原有 13 条记录的 PEM 证书，与表字段对比：

校验项	结果
表 `sn` 与 DER 序列号	13/13 一致（含前导 0 归一化后）
表 `not_before` 与证书生效时间	13/13 一致
表 `not_after` 与证书失效时间	13/13 一致
表 `subject` 与证书主体	逻辑一致，但表内顺序为 `C..CN`，PEM 为 `CN..C`
PEM 主体字符编码	个别证书内部主体字符串不是合法 UTF-8，工具显示替换符

设计结论：

以表字段为唯一比对依据，不从 PEM 反推 DN/SN；
表内 DN 顺序与 RFC4514 输出不同，不做 RFC 规范化，默认按原始字节比较；
不提供证书密码学验证功能。

2.8 环境验证结论

结论	说明
可实施	Java 8 + sqlite-jdbc 3.36.0.3 + GBK 字节处理方案已在目标服务器验证
主要风险	编码误用、生产库并发写、报表人工编辑错误、DN 近似匹配、certtype 业务含义未知
构建方式	本地 Maven 构建 fat jar，上传服务器运行；服务器不安装 Maven
测试数据	主库 4 条、从库 15 条；预期主库多 1、从库多 12、不一致 3

3. 总体架构

+----------------------------------------------------------+
| cert-compare.sh                                          |
| java -Dfile.encoding=UTF-8 -Xmx256m -jar cert-compare.jar|
+----------------------------+-----------------------------+
                             |
                    +--------v---------+
                    | CertCompareApp   |
                    | 启动/模式选择     |
                    +--------+---------+
                             |
                   +---------+---------+
                   |                   |
            +------v-------+   +-------v--------+
            | Shell        |   | 一次性命令      |
            | 交互循环      |   | compare / sql  |
            +------+-------+   +-------+--------+
                   |                   |
                   +---------+---------+
                             |
                    +--------v---------+
                    | Operations       |
                    | compare / sql    |
                    +--------+---------+
                             |
          +------------------+-------------------+
          |                                      |
   +------v-------+                      +-------v--------+
   | Compare      |                      | ReportReader   |
   | Service      |                      | ReportValidator|
   +------+-------+                      +-------+--------+
          |                                      |
   +------v-------+                      +-------v--------+
   | ReportWriter |                      | SqlScript      |
   | 单份 CSV     |                      | Service        |
   +------+-------+                      +-------+--------+
          |                                      |
          |                              +-------v--------+
          |                              | merge-*.sql    |
          |                              | 由 DBA 执行     |
          |                              +----------------+
          |                                      |
          +------------------+-------------------+
                             |
                    +--------v---------+
                    | DbManager        |
                    | sqlite-jdbc      |
                    +--------+---------+
                             |
                  +----------+----------+
                  |                     |
            +-----v-----+         +-----v-----+
            | main.db   |         | slave.db  |
            | (只读)     |         | (只读)     |
            +-----------+         +-----------+

分层职责：

层	职责
CLI	交互循环、命令分词、参数解析、帮助、退出码
Operations	组织 compare / sql 流程，控制台输出
Service	比对、报表生成、报表读取与校验、批量 SQL 生成
Dao	数据库打开、Schema 校验、只读查询
Report	单份 CSV 写入、编码识别、CSV 解析、标注校验
Util	GB18030、十六进制、路径、时间

4. 模块与类设计

包名：com.sunyard.certcompare。实际源码结构：

src/main/java/com/sunyard/certcompare/
  CertCompareApp.java              main 入口，交互/一次性模式
  AppConfig.java                   配置模型与默认值
  Operations.java                  compare / sql 公共流程
  UserException.java               业务异常 + 退出码
  cli/
    Shell.java                     交互循环与命令分发
    CommandTokenizer.java          引号感知分词
  compare/
    CompareService.java            比对主流程（分类、字段差异、异常提示）
  db/
    DbManager.java                 连接、Schema 校验、hex 读取、SQL 字面量
  model/
    CertRecord.java                投影记录（DN/issuer 为十六进制原始字节）
    CertKey.java                   身份键（subjectHex + certtype）
    CompareCategory.java           一致/主库多/从库多/不一致
    CompareResult.java             分类结果与统计
    CompareRow.java                报表行数据
    FieldDiff.java                 字段差异
    MergeChoice.java               人工选择：主/备
  report/
    Csv.java                       RFC4180 转义与解析
    ReportWriter.java              单份 CSV 报表
    ReportReader.java              报表读取、编码识别
    ReportRow.java                 报表行
    ReportData.java                报表数据
    ReportValidator.java           报表过期与标注校验
  sql/
    SqlPlan.java                   生成计划与统计
    SqlScriptService.java          批量 SQL 脚本生成
  util/
    Text.java                      GB18030 解码、hex 工具、CN 提取
    SnNormalizer.java              SN 归一化
    DateUtil.java                  14 位时间校验

4.1 核心类职责

类	职责
`CertCompareApp`	解析全局参数（`--config`、`--debug`），选择交互/一次性模式
`AppConfig`	读取 UTF-8 properties；路径解析；默认值；校验编码与匹配方式
`Operations`	compare / sql 流程编排、控制台汇总与执行提示
`DbManager`	建立只读连接、`busy_timeout`、Schema 校验、`hex()` 投影读取
`CertKey`	不可变身份键，`equals/hashCode` 基于字节十六进制 + certtype
`SnNormalizer`	去空白、大写、去前导 0；保留原始值用于输出
`CompareService`	读取两库、构造 Map、分类、生成结果与提示
`ReportWriter`	生成单份 CSV，预填默认合并选择
`ReportReader`	识别 UTF-8/GB18030、解析 CSV、读取标注
`ReportValidator`	校验报表与当前比对结果一致、标注合法
`SqlScriptService`	生成 ATTACH + INSERT OR REPLACE / UPDATE 的批量脚本
`SqlPlan`	生成计划统计与控制台展示

5. 配置设计

5.1 配置文件示例

config.properties（UTF-8）：

# 数据库路径
db.main=/app/cert-compare/main.db
db.slave=/app/cert-compare/slave.db

# 文本编码：subject/issuer 实际为 GBK/GB18030 字节
db.text.encoding=GB18030

# DN 匹配：EXACT（默认，按原始字节）| TRIM（去除整体首尾空白）
dn.match=EXACT

# SN 归一化：HEX（大写+去前导0）| EXACT
sn.normalize=HEX

# 报表
report.dir=./reports
report.csv.bom=true

批量 SQL 脚本不通过配置指定目录，直接写入运行时的当前工作目录。

5.2 优先级

命令行参数  >  配置文件  >  内置默认值

5.3 配置校验

主库、从库必须存在、是普通文件、可读、是合法 SQLite 文件；
SQL 脚本写入当前工作目录；目录不可写时生成失败并提示；
编码必须是 JVM 支持的字符集（默认 GB18030）；
dn.match、sn.normalize 仅接受枚举值；
报表目录不存在时自动创建，创建失败则报错；
配置错误退出码 2，交互模式仅提示不退出；
不生成任何日志文件，不读取日志配置。

6. CLI 设计

6.1 启动方式

# 交互模式
java -Dfile.encoding=UTF-8 -jar cert-compare.jar
java -Dfile.encoding=UTF-8 -jar cert-compare.jar --config /app/cert-compare/config.properties

# 一次性模式
java -Dfile.encoding=UTF-8 -jar cert-compare.jar compare --config /app/cert-compare/config.properties
java -Dfile.encoding=UTF-8 -jar cert-compare.jar sql \
  --config /app/cert-compare/config.properties \
  --report /app/cert-compare/reports/compare-report-20261104-213307-annotated.csv

6.2 交互命令语法

set main <path>
set slave <path>
set main | set slave                    # 无参数：显示当前值
status
compare
sql [--report <path>]
help [command]
quit | exit

6.3 命令行为

命令	行为
`set main`	校验并设置主库，打印大小/数量
`set slave`	校验并设置从库
`status`	显示主库/从库、当前工作目录（SQL 输出位置）、数量、结构兼容性、最近报表/脚本
`compare`	比对、生成单份报表、控制台打印汇总
`sql`	读取标注报表，校验后生成批量 SQL 脚本并打印执行命令
`help`	命令/参数帮助
`quit`	退出，返回 0

交互实现：

BufferedReader 读取标准输入；
CommandTokenizer 支持 "..."、'...' 和反斜杠转义；
命令名大小写不敏感；
TERM=dumb 时不使用 ANSI 颜色；
每条命令独立捕获异常，打印错误后继续；
不长期持有数据库连接，命令结束后关闭，避免锁库；
不写日志文件。

6.4 compare 参数

compare 不接受业务筛选、查询、过滤参数。所有差异一次性进入同一份报表，由使用者在报表中自行过滤。

6.5 sql 参数

参数	说明
`--report <path>`	指定已标注的报表；缺省使用本次会话最近一次 compare 的报表，若没有则使用报表目录中最新的 `compare-report-*.csv`

sql 不写数据库、不改源库，只在当前工作目录生成 merge-<yyyyMMdd-HHmmss>.sql，因此不需要确认参数。

6.6 退出码

码	含义
0	成功
1	未预期内部错误
2	参数/配置错误
4	数据库/结构/完整性错误
5	报表过期、标注非法、SQL 脚本生成/写入失败

7. 数据访问与编码设计

7.1 连接

jdbc:sqlite:/app/cert-compare/main.db

连接后执行：

PRAGMA busy_timeout = 5000;

说明：

sqlite-jdbc 3.36.0.3 内部 SQLite 3.36.0，仅用于只读查询；
源库默认不执行任何写操作；
compare 可设置 PRAGMA query_only = ON 进一步防误写；
若生产库被占用，返回明确的 SQLITE_BUSY 提示，建议维护窗口执行。

7.2 读取投影

默认比较不读取大字段：

SELECT rowid,
       hex(subject)  AS subject_hex,
       hex(issuer)   AS issuer_hex,
       hex(organid)  AS organid_hex,
       sn, pubkey_alg, sign_alg,
       not_before, not_after, certtype, opertime, stat
FROM usercert_tab

约束：

不查询 subject/issuer 的字符串形态；
不使用 getString() 读取上述字段；
生成脚本时只读取 hex(subject)，数据复制在 SQLite 内部完成，不经过 Java 字符串。

7.3 编码工具

public final class Gb18030 {
    public static final Charset CHARSET = Charset.forName("GB18030");

    public static String decode(byte[] raw) {
        if (raw == null) return "";
        return new String(raw, CHARSET);
    }

    public static byte[] encode(String text) {
        return text.getBytes(CHARSET);
    }
}

展示策略：

正常：GB18030 解码；
异常：输出 hex=... 与可打印 ASCII，避免抛出；
控制台/CSV 统一 UTF-8 输出；
CSV 首部写入 BOM \uFEFF。

7.4 身份键

final class CertKey {
    final String subjectHex; // 大写十六进制，原始字节
    final int certtype;
}

键字符串形式：subjectHex + "|" + certtype。

默认 dn.match=EXACT：subjectHex 完全一致；
TRIM：仅去除 subject 整体首尾的 ASCII 空白后再取十六进制；
不做大小写归一化、不做属性顺序重排、不做 RFC4514 规范化；
报表中的 DN_HEX 加 HEX: 前缀，读取时去掉；键计算始终用裸十六进制。

7.5 SN 归一化

raw = trim(sn)
normalized = raw.toUpperCase(Locale.ROOT)
while (normalized.length() > 1 && normalized.charAt(0) == '0') remove first char

仅当 sn.normalize=HEX 时启用；
非十六进制字符时退化为 trim + uppercase 并记录控制台警告；
展示与 CSV 均使用原始 sn。

7.6 时间校验

长度必须为 14；
必须全部为数字；
可按 yyyyMMddHHmmss 解析；
not_before <= not_after；
校验失败标记 INVALID_DATE，在 compare 控制台“提示”中列出（HEX + CERTTYPE），不阻止 compare；sql 仍按报表标注生成脚本，并在控制台提示该记录风险。

8. 比对设计

8.1 流程

1. 校验配置与两库结构
2. 从 main 读取投影记录 -> List<CertRecord>
3. 从 slave 读取投影记录 -> List<CertRecord>
4. 分别构建 Map<CertKey, List<CertRecord>>
5. 检测库内重复键、重复 SN、非法日期（诊断信息）
6. 遍历两库键的并集：
     - 仅 main        -> ONLY_MAIN（主库多）
     - 仅 slave       -> ONLY_SLAVE（从库多）
     - 两边都有       -> DiffEngine 比较
                         无差异 -> EQUAL（仅计数）
                         有差异 -> DIFF（不一致）
7. 汇总统计
8. 控制台打印汇总；仅将 ONLY_MAIN/ONLY_SLAVE/DIFF 写入一份 CSV

8.2 分类矩阵

main	slave	分类	是否进报表	默认合并选择
有	无	ONLY_MAIN（主库多）	是	主
无	有	ONLY_SLAVE（从库多）	是	备
有	有，字段相同	EQUAL（一致）	否	-
有	有，字段不同	DIFF（不一致）	是	空，待人工填写
库内重复键	-	诊断告警	按实际分类	-
日期非法	-	诊断告警	按实际分类	-

8.3 字段比较

默认比较字段：

字段	比较方式
`sn`	归一化后字符串相等
`issuer`	原始字节相等
`not_before`	原始字符串相等
`not_after`	原始字符串相等
`stat`	整数相等

opertime、organid、公钥、证书 PEM 等不参与默认比较，避免操作时间等元数据造成大量误报。

FieldDiff 输出：字段名、主库值、从库值。二进制字段输出十六进制或哈希，避免控制台/报表乱码。

8.4 数据异常

异常	判定	处理
`DUPLICATE_KEY`	同一库内身份键出现多次	理论上被主键阻止；如出现，直接报错
`DUPLICATE_SN`	同一库内归一化 SN 重复但身份键不同	控制台“提示”告警，不阻止生成脚本
`INVALID_DATE`	非 14 位/非法时间/起止倒置	控制台“提示”告警，仍按标注生成脚本
`EMPTY_DN` / `EMPTY_SN`	空值	控制台“提示”告警
`DECODE_ERROR`	GB18030 解码异常	展示十六进制；不影响字节比对

8.5 复杂度

时间：O(n + m)，n/m 为两库记录数；
空间：O(n + m)，默认不加载大字段；
3000 条规模下内存占用预计 < 20 MB（不含 JVM 基础开销）。

9. 报表设计

9.1 文件

每次 compare 生成一份 CSV：<report.dir>/compare-report-yyyyMMdd-HHmmss.csv；
不生成 slave-extra、main-missing、field-diff、summary 等额外文件；
汇总信息只打印到控制台；
报表包含三类差异记录，不包含一致记录；
若两库完全一致，生成只有表头的空报表，仍打印汇总。

9.2 列定义

列	说明
序号	报表内序号
类型	主库多 / 从库多 / 不一致
DN	GB18030 解码后的展示值
DN_HEX	`HEX:` + 原始字节大写十六进制
CN	从 DN 提取的 CN，仅展示
CERTTYPE	证书类型
主库SN / 从库SN	两边 SN，不存在的一侧留空
主库生效 / 主库失效	主库有效期，不存在的一侧留空
从库生效 / 从库失效	从库有效期，不存在的一侧留空
主库状态 / 从库状态	两边 `stat`，不存在的一侧留空
差异字段	不一致时列出，如 `sn;not_after`
合并选择	最后一列，人工填写：主 / 备

合并选择 固定放在最后一列，方便在 Excel 中直接向右填写；
不设“备注”列；日期非法、有效期倒置、SN/DN 为空、重复 SN 等异常改在 compare 控制台“提示”中输出；
读取端按表头列名定位，不依赖列顺序，因此列顺序调整不影响旧报表读取。

9.3 默认标注

类型	默认值	允许值
主库多	主	仅主
从库多	备	仅备
不一致	空	主 / 备

默认标注让业务人员只需要处理“不一致”行；主库多、从库多已按唯一可行来源预填。

9.4 CSV 规格

编码：UTF-8 + BOM；
分隔符：逗号；
换行：\n；
字段内逗号、引号、换行按 RFC4180 转义；
DN_HEX 加 HEX: 前缀，防止 Excel 将长十六进制转为科学计数法；
DN 用双引号包裹（包含逗号）；
报表生成后控制台打印绝对路径。

9.5 人工编辑建议

只修改最后一列“合并选择”，不要改动 DN、DN_HEX、CERTTYPE、类型等定位列；
Excel 保存时选择“CSV UTF-8（逗号分隔）”，或直接保存为 GBK CSV；
不要增删行、不要调整列顺序；
修改后用 sql 校验；校验失败会指出具体行号/列名。

9.6 报表读取兼容性

CompareReportReader 按以下顺序识别编码：

UTF-8 BOM；
严格 UTF-8 解码；
失败则按 GB18030 解码（兼容 Excel 另存为 ANSI/GBK）。

CSV 解析器支持：

引号包裹字段；
字段内逗号、换行；
双引号转义 ""；
空字段；
表头前可有空行（容错）。

9.7 报表一致性校验

sql 不信任报表中的业务字段，只信任“合并选择”。生成脚本前重新执行一次 compare，并校验：

报表行集合与当前差异键集合完全相同；
每行的“类型”与当前分类一致；
主库多、从库多的身份键分别只在一侧存在；不一致行的身份键两边都存在；
报表中的 SN、有效期等字段仅供人工阅读，脚本数据始终取自当前数据库，不取自报表文本；
无重复行、无缺失行、无额外行；
每条记录标注合法。

任一不满足即判定报表过期或被修改，拒绝生成脚本并提示重新 compare。

10. 批量 SQL 设计

10.1 前置条件

主库、从库均已通过 Schema 校验；
两库必需字段一致；
报表存在且通过 9.7 校验；
报表中所有“不一致”行已填写“主”或“备”；
当前工作目录可写（脚本直接写入当前目录）；
脚本执行前由 DBA 备份主库（脚本头注释中给出命令）。

10.2 合并语义

报表类型	合并选择	脚本动作
主库多	主	无语句（主库已有）
从库多	备	`INSERT OR REPLACE ... SELECT`
不一致（主备 DN 字节相同）	主	无语句（保留主库版本）
不一致（主备 DN 字节相同）	备	`INSERT OR REPLACE ... SELECT`（整行替换）
不一致（主备 DN 字节不同，TRIM）	主	无语句（保留主库版本）
不一致（主备 DN 字节不同，TRIM）	备	`UPDATE ... SET col = (SELECT ...)`（按主库字节定位）
一致	-	不在报表中；主库已有

结果：执行后主库 = 原主库全部证书 + 从库多证书；不一致记录按标注取其一；不删除任何证书。

10.3 生成步骤

1. 计算 CompareResult
2. 读取报表并校验标注（9.7）
3. 分流：
   - 从库多                       → INSERT 集合
   - 不一致 + 选备 + DN 字节相同   → INSERT 集合
   - 不一致 + 选备 + DN 字节不同   → UPDATE 集合
   - 不一致 + 选主 / 主库多        → 不需要语句
4. 生成 SqlPlan（统计 + 输出路径）
5. 在当前工作目录创建 merge-<yyyyMMdd-HHmmss>.sql（UTF-8 无 BOM）
6. 写注释头：主从路径、执行方式、字节保持说明、统计
7. ATTACH DATABASE '<从库>' AS cert_src
8. BEGIN IMMEDIATE
9. 每 2000 个身份键一组，生成 INSERT OR REPLACE ... SELECT
10. 每条 TRIM 场景生成一条 UPDATE
11. COMMIT
12. DETACH DATABASE cert_src
13. SELECT 'rows', count(*) + PRAGMA integrity_check 自检
14. 控制台打印计划与 DBA 执行命令

说明：

身份键 = hex(subject) + certtype，脚本中只出现十六进制，不出现 DN 文本；
主备 DN 字节相同才用 INSERT OR REPLACE 整行替换；
主备 DN 字节不同（dn.match=TRIM）时改用 UPDATE：按主库字节定位，按从库字节取新值，避免“按从库字节删不到主库行”导致丢数据；
单条 INSERT 最多 2000 个身份键（KEYS_PER_STATEMENT），避免 SQL 文本过长；
脚本使用 BEGIN IMMEDIATE / COMMIT 包裹，失败可整体回滚；
报表文本中的 SN、有效期等只用于人工阅读，脚本数据始终取自当前数据库。

10.4 关键 SQL

包装：

PRAGMA foreign_keys = OFF;

ATTACH DATABASE '/app/cert-compare/slave.db' AS cert_src;

BEGIN IMMEDIATE;

从库多 / 不一致选备（主备 DN 字节相同）：

INSERT OR REPLACE INTO usercert_tab
       (organid, subject, issuer, sn, pubkey_alg, pubkey, sign_alg, signinfo,
        not_before, not_after, certfile, certtype, opertime, stat)
SELECT organid, subject, issuer, sn, pubkey_alg, pubkey, sign_alg, signinfo,
       not_before, not_after, certfile, certtype, opertime, stat
  FROM cert_src.usercert_tab
 WHERE (hex(subject) = '434E3D646966662D736E2D3031' AND certtype = 0)
    OR (hex(subject) = 'B3C2B3AC' AND certtype = 2);

不一致选备（主备 DN 字节不同，dn.match=TRIM）：

UPDATE usercert_tab SET
       organid    = (SELECT organid    FROM cert_src.usercert_tab WHERE hex(subject) = '<从库DN_HEX>' AND certtype = 1),
       ...
       stat       = (SELECT stat       FROM cert_src.usercert_tab WHERE hex(subject) = '<从库DN_HEX>' AND certtype = 1)
 WHERE hex(subject) = '<主库DN_HEX>' AND certtype = 1;

末尾自检：

COMMIT;

DETACH DATABASE cert_src;

SELECT 'rows' AS item, count(*) AS value FROM usercert_tab;
PRAGMA integrity_check;

注意：

subject 为 TEXT，SQLite 默认 BINARY 比较，即按原始字节比较；
使用 hex() 作为身份键，避免 BLOB/TEXT 类型比较陷阱；
不使用 COLLATE NOCASE，避免改变 DN 语义；
若从库存在库内重复键，主键冲突会让事务失败并回滚；
SQL 语句与字符串字面量全部为 ASCII，仅注释包含中文；文件为 UTF-8 无 BOM。

10.5 生成计划

控制台示例：

SQL 生成计划:
  主库证书数                4
  从库证书数               15
  一致                   0
  主库多                  1
  从库多                 12
  不一致                  3
  写入(从库多+选备)           13
  不一致选主                2
  不一致选备                1
  执行后主库记录数             16
  从库(只读引用)             /app/cert-compare/slave.db
  SQL 文件               /app/cert-compare/merge-20261104-213307.sql

10.6 执行与结果校验

DBA 在主库执行：

cp -p /app/cert-compare/main.db /app/cert-compare/main.db.bak-$(date +%Y%m%d-%H%M%S)
sqlite3 /app/cert-compare/main.db < /app/cert-compare/merge-20261104-213307.sql

校验项	方法
脚本退出码	`sqlite3` 返回 0
文件完整性	`PRAGMA integrity_check` = ok
总数	`SELECT 'rows'` = `mainCount + count(从库多)`
从库多已插入	按身份键检查存在，且关键字段与从库一致
不一致选主	关键字段与主库一致
不一致选备	关键字段与从库一致
主库记录未变	未在选择“备”集合中的主库记录保持原值
从库未变	执行前后从库 SHA-256 一致
字节保持	`hex(subject)` 与来源库一致，`typeof(subject) = 'text'`

10.7 备份、回滚与脚本保留

工具只生成脚本，不打开、不修改任何数据库文件；
执行前必须备份主库，脚本头注释给出 cp -p 备份命令；
脚本使用 BEGIN IMMEDIATE / COMMIT，中途报错时事务不提交；
需要回滚时用备份覆盖主库：cp -p main.db.bak-<时间戳> main.db；
历史脚本保留在当前工作目录，不自动删除、不覆盖（同名时追加 -2、-3）；
脚本文件权限遵循系统 umask。

10.8 幂等与重复执行

脚本是幂等的：源库相同、标注相同时重复执行结果一致。

INSERT OR REPLACE 按主键 (subject, certtype) 覆盖，不会累积重复记录；
UPDATE 按主键定位并覆盖字段，重复执行结果不变；
已执行过的脚本再次执行仍得到同一记录集（实测两次执行后均为 16 条）；
若两次执行之间源库或标注发生变化，需重新 compare 并生成新脚本。

11. 控制台输出、错误与退出码

11.1 compare 输出

主库: /app/cert-compare/main.db    证书数: 4
从库: /app/cert-compare/slave.db   证书数: 15

汇总:
  一致        0
  主库多      1
  从库多     12
  不一致      3
  差异合计   16

报表:
  /app/cert-compare/reports/compare-report-20261104-213307.csv

请按“合并选择”列标注：主 或 备，然后执行 sql 生成批量脚本。

11.2 sql 输出

SQL 生成计划:
  主库证书数                4
  从库证书数               15
  一致                   0
  主库多                  1
  从库多                 12
  不一致                  3
  写入(从库多+选备)           13
  不一致选主                2
  不一致选备                1
  执行后主库记录数             16
  从库(只读引用)             /app/cert-compare/slave.db
  SQL 文件               /app/cert-compare/merge-20261104-213307.sql

请在主库上执行（执行前先备份）：
  cp -p /app/cert-compare/main.db /app/cert-compare/main.db.bak-$(date +%Y%m%d-%H%M%S)
  sqlite3 /app/cert-compare/main.db < /app/cert-compare/merge-20261104-213307.sql

执行后主库记录数应为 16 条；脚本末尾会输出 rows 与 integrity_check 结果。

11.3 异常分类

异常	提示	退出码
参数缺失/未知参数	用法 + 示例	2
配置文件错误	具体键与原因	2
文件不存在/不可读	路径 + 权限建议	2/4
非 SQLite 文件	文件头校验失败	4
表/字段缺失	缺失项列表	4
数据库被占用	SQLITE_BUSY + 建议维护窗口	4
完整性校验失败	具体结果	4
报表过期/标注非法	行号 + 期望值 + 重新 compare 建议	5
SQL 脚本生成/写入失败	路径 + 磁盘空间 + 权限建议	5

11.4 日志

不生成日志文件；
不创建 logs/ 目录；
运行信息、警告、错误只输出到控制台（stdout/stderr）；
--debug 时在控制台输出异常堆栈；
不输出证书 PEM、公钥、签名等敏感内容。

11.5 安全

工具默认只读源库，不直接修改任何数据库；
合并由 DBA 执行生成的 SQL 脚本完成，执行前须备份主库；
脚本中的路径、十六进制字面量均经过单引号转义；
脚本不包含 DN 文本，仅包含 hex(subject)；
不在控制台/报表中输出证书大字段。

12. 性能与容量

项目	设计目标	依据
3000 条 compare	≤ 3s	两次全表扫描 + 内存 Map
3000 条 sql	≤ 3s	只生成脚本，不连接数据库写入
生成脚本数据量	3000 条	每 2000 个身份键一组 INSERT，约数百 KB
内存	≤ 256MB	默认不加载 certfile/pubkey
磁盘	主库大小 + 脚本大小	工具不生成数据库副本
报表	3000 行以内	仅差异记录，通常远小于全量
脚本执行（人工）	视数据量，3000 条约数秒	`ATTACH` + 集合 SQL，SQLite 内部复制

优化点：

单次 SELECT 读取全部投影字段；
使用 HashMap，初始容量按记录数预估；
脚本使用集合 SQL（一条语句覆盖多个身份键），不逐行往返；
报表流式写入，不拼接大字符串；
报表读取一次性载入内存（差异通常远小于 3000）；
脚本生成不打开数据库，避免锁库与一致性开销。

13. 测试设计

13.1 测试分层

层级	内容
单元测试	编码、SN 归一化、时间校验、身份键、DiffEngine、CSV 转义/解析、SQL 脚本生成
集成测试	复制测试库到临时目录，执行 compare/标注/sql，并执行生成的脚本
回归测试	GBK 十六进制、报表过期、非法标注、脚本幂等性、源库哈希不变
性能测试	生成 3000 条含中文 DN 的数据

13.2 测试夹具

夹具	构造	预期
A 现有测试库	main 4 条 / slave 15 条	主库多 1、从库多 12、不一致 3、一致 0；报表 16 行
B 完全一致	slave 复制 main	报表只有表头；脚本无写入语句
C 双证	同一 DN，certtype 0/2	视为两张证书，不合并、不误报
D 字段差异	同 DN+certtype，SN/时间/状态不同	分类不一致；选主/选备分别验证
E 报表异常	空标注、非法标注、旧报表、缺行、多行	sql 拒绝并提示行号/原因
F 3000 条	生成中文 DN、分布 certtype/状态	性能达标；执行后字节不变
G 编码回归	含“陈超”“北京”“信雅达”等 GBK 字符	`hex(subject)` 执行前后一致
H 无日志	执行完整流程	目录中无 `*.log`、无 `logs/`

13.3 关键断言

hex(subject) 执行脚本前后逐条一致；
typeof(subject) = 'text'；
生成脚本前后 main.db / slave.db 的 SHA-256 不变；
执行后总数 = 主库数 + 从库多；
选主的不一致记录等于主库版本，选备的等于从库版本；
相同源库与标注重复执行脚本，结果行数与字段保持一致；
脚本正文（除注释）全部为 ASCII，不含 DN 文本；
报表 UTF-8 BOM 正确，Excel 打开无乱码；
GBK 保存的报表可被读取并通过校验；
脚本可在 sqlite3 3.6.20 上一次执行成功；
不生成日志文件，不产生 .tmp 文件。

13.4 手工验收命令

cd /app/cert-compare
./cert-compare.sh
# set main /app/cert-compare/main.db
# set slave /app/cert-compare/slave.db
# compare
# （编辑报表“合并选择”列）
# sql --report reports/compare-report-....csv

# 由 DBA 备份后执行（脚本生成在当前目录）
cp -p main.db main.db.bak-$(date +%Y%m%d-%H%M%S)
sqlite3 main.db < merge-*.sql

14. 构建与部署

14.1 本地构建

pom.xml 关键配置：

<properties>
  <maven.compiler.source>1.8</maven.compiler.source>
  <maven.compiler.target>1.8</maven.compiler.target>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>

<dependencies>
  <dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.36.0.3</version>
  </dependency>
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
  </dependency>
</dependencies>

打包：

mvn clean package
# 产物：target/cert-compare.jar（shade fat jar）

要求：

maven-shade-plugin 合并依赖并设置 Main-Class；
保留 META-INF/services/java.sql.Driver；
不压缩/不破坏 sqlite-jdbc 原生库资源；
字节码目标 1.8；
jar 内包含 org.sqlite 原生库。

14.2 部署目录

/app/cert-compare/
  cert-compare.jar
  config.properties
  cert-compare.sh
  reports/         # compare 报表
  merge-*.sql      # 由 sql 生成到当前目录（启动脚本已 cd 到此目录）
  main.db          # 现有
  slave.db         # 现有
  main.db.bak-*    # 执行脚本前由 DBA 备份

启动脚本：

#!/bin/sh
cd "$(dirname "$0")" || exit 1
exec java -Dfile.encoding=UTF-8 -Xmx256m -jar cert-compare.jar "$@"

14.3 部署步骤

# 本地构建后上传
scp target/cert-compare.jar root@172.1.41.139:/app/cert-compare/
scp config.properties root@172.1.41.139:/app/cert-compare/
scp scripts/cert-compare.sh root@172.1.41.139:/app/cert-compare/
ssh root@172.1.41.139
cd /app/cert-compare
chmod +x cert-compare.sh
./cert-compare.sh

14.4 运行注意

sqlite-jdbc 会把原生库解压到 java.io.tmpdir，当前 /tmp 可写且可执行；
若生产环境 /tmp 挂载 noexec，需通过 -Dorg.sqlite.tmpdir=/app/cert-compare/tmp 指定可执行目录；
生成的脚本只使用 sqlite3 3.6.20 已支持的语法（ATTACH、INSERT OR REPLACE ... SELECT、BEGIN IMMEDIATE、DETACH、PRAGMA integrity_check）；
生产执行脚本建议在维护窗口进行，或确认 HSM 应用无写入；
执行脚本前必须用 cp -p 备份主库；
不创建日志目录/日志文件。

14.5 部署与实测结果（2026-11-04）

项目	结果
构建产物	`target/cert-compare.jar`，9,799,848 字节，Java 8 字节码（major 52）
单元测试	12 个用例全部通过（含 `SqlScriptTest` 2 个）
部署路径	`/app/cert-compare/{cert-compare.jar,config.properties,cert-compare.sh,reports/}`
服务器运行	JDK 1.8.0_152，sqlite-jdbc 3.36.0.3 原生库加载正常
测试数据	main.db 4 条、slave.db 15 条（新增 3 组不一致）
compare	一致 0、主库多 1、从库多 12、不一致 3、差异合计 16
报表列	无“备注”列；“合并选择”为最后一列
脚本输出位置	在 `/tmp/cert-restore-test` 下运行，脚本生成到该当前目录（`merge-20261104-215550.sql`，4,154 字节）
sql	1 条 INSERT（13 个键）；从库多 12 + 不一致选备 1
脚本执行	在 main.db 副本上以 sqlite3 3.6.20 执行，输出 `rows
中文恢复	从库 2 条中文 DN（“北京 / 信雅达”）恢复后逐字段一致，`hex(subject)` 完全相同
中文解码	恢复库中文 DN 经 GB18030→UTF-8 解码为 `C=CN,ST=北京,L=北京,O=信雅达,...`
主库中文保持	主库“陈超”记录执行脚本后 `hex(subject)`、SN 均未变化
选主校验	`CN=diff-sn-01` 保留主库 SN `0A0B0C01`；`CN=diff-stat-03` 保留主库 `stat=0`
选备校验	`CN=diff-time-02` 使用从库失效时间 `20311231235959`
字节保持	执行后“陈超”记录 `hex(subject)` 与主库完全一致（221 字节），`typeof(subject)=text`，iconv 解码正常
源库只读	生成/执行前后 main.db、slave.db SHA-256 均不变
幂等	同一脚本重复执行两次，记录数均为 16，`integrity_check=ok`
报表兼容	UTF-8 BOM 与 GB18030 保存的报表均可读取并通过校验
旧报表兼容	含“备注”、合并选择在中间的旧报表仍可读取并生成脚本
异常校验	空标注/非法标注/缺失报表均拒绝，退出码 5
交互模式	`set main`、`set slave`、`status`、`sql --report`、`quit` 正常，退出码 0
日志	未生成 `*.log`，未创建 `logs/`，无 `.tmp` 残留

测试数据脚本：scripts/test-data-main.sql、scripts/test-data-slave.sql；执行前已备份为 main.db.bak-*、slave.db.bak-*。

15. 风险与对策

风险	影响	对策
GBK 字段被按 UTF-8 读取	DN 乱码、错误匹配	强制 `hex` 读取；脚本走 SQL 内部复制；编码回归测试
报表被 Excel 改坏	标注丢失、DN_HEX 变科学计数法	`HEX:` 前缀；重新 compare；严格校验报表与当前库一致
报表保存为 GBK	读取乱码	读取器自动识别 UTF-8/GB18030
报表过期	脚本内容与当前库不符	sql 前重新 compare 并逐项校验，失败即拒绝
不一致行未标注	无法决定版本	默认留空；sql 拒绝并提示行号
标注写错（主/备）	选错证书版本	计划中展示选主/选备数量；执行后校验字段与所选源一致
脚本执行出错	主库部分变更	`BEGIN IMMEDIATE` / `COMMIT` 事务；执行前备份；失败按备份回滚
生产库正在写入	脚本执行锁等待、`SQLITE_BUSY`	维护窗口；脚本使用 `BEGIN IMMEDIATE`
从库路径变化	脚本 ATTACH 失败	生成脚本时写入绝对路径；执行前确认从库未移动
仅按 DN 比对	双证被误判	身份键加入 certtype
DN 属性大小写/空格差异	逻辑同一 DN 被当作两张	默认严格匹配，预留 TRIM；报表自行过滤
TRIM 场景主备 DN 字节不同	按从库字节删除会丢数据	改用 `UPDATE`，按主库字节定位
SN 前导 0 差异	误报不一致	默认 HEX 归一化，展示保留原值
certtype 含义未知	报表可读性差	本期显示数值，预留映射配置
单条 SQL 过长	sqlite3 解析慢或失败	每 2000 个身份键拆分一条语句
测试数据仅 19 条（主 4 + 从 15）	性能/边界覆盖不足	构造 3000 条及异常夹具
`/tmp` noexec	原生库加载失败	支持 `org.sqlite.tmpdir`

16. 待确认事项

certtype 取值 0/1/2/4/5 的业务名称，是否需要在报表中显示中文；
生产环境是否允许维护窗口/停写，或必须在线只读；
SQL 脚本命名（当前 merge-<时间戳>.sql）与历史脚本保留策略；
主库多/从库多的“合并选择”是否允许改成空（跳过）；本期按“所有证书都需要”固定为主/备；
SN 前导 0 归一化是否与现网业务查询完全一致；
是否需要 DN 近似匹配（大小写、属性顺序）作为可选模式；
报表是否需要 XLSX 格式（当前 CSV，Excel 可直接编辑）。

17. 附录

17.1 表结构（测试库）

CREATE TABLE "usercert_tab" (
  `organid` varchar(32) NOT NULL,
  `subject` varchar(256) NOT NULL,
  `issuer` varchar(256) NOT NULL,
  `sn` varchar(64) NOT NULL,
  `pubkey_alg` int(4) NOT NULL,
  `pubkey` varchar(1032) NOT NULL,
  `sign_alg` int(4) NOT NULL,
  `signinfo` varchar(512) NOT NULL,
  `not_before` varchar(14) NOT NULL,
  `not_after` varchar(14) NOT NULL,
  `certfile` varchar(2048) NOT NULL,
  `certtype` smallint(4) NOT NULL,
  `opertime` varchar(14) NOT NULL,
  `stat` smallint(4) NOT NULL,
  PRIMARY KEY (`subject`, `certtype`)
);

17.2 测试库分类预期

分类	数量	说明
ONLY_MAIN（主库多）	1	主库“陈超”证书不在从库
ONLY_SLAVE（从库多）	12	从库原有 12 张证书不在主库
DIFF（不一致）	3	3 组构造数据：SN、失效时间、状态各一组
EQUAL（一致）	0	无相同身份键且字段全同
报表记录	16	1 + 12 + 3
执行脚本后主库总数	16	4（主库）+ 12（从库多）

17.3 已验证的关键命令

# 版本与环境
java -version
sqlite3 --version
cat /etc/redhat-release

# 完整性
sqlite3 main.db 'pragma integrity_check;'
sqlite3 slave.db 'pragma integrity_check;'

# 编码证据
sqlite3 main.db "select hex(subject) from usercert_tab;"

# JDBC 兼容性（服务器）
java -cp '.:sqlite-jdbc-3.36.0.3.jar' JdbcProbe /app/cert-compare/main.db

# 生成脚本并在主库副本上执行
./cert-compare.sh sql --report reports/compare-report-....csv
cp -p main.db test-main.db
sqlite3 test-main.db < merge-....sql
sqlite3 test-main.db "select count(*) from usercert_tab; pragma integrity_check;"

17.4 环境验证证据摘要

证据	结果
JDBC 读取原始字节	`hex=...B3C2B3AC...`，GB18030 解码为“陈超”
JDBC `getString`	长度 109，含 `U+FFFD`，不可用于比对
ATTACH INSERT SELECT	插入后 `hex` 相同、`typeof=text`
事务回滚	记录数恢复，回滚成功
脚本执行（sqlite3 3.6.20）	`rows
sqlite-jdbc 3.36	在 glibc 2.12 加载成功
sqlite-jdbc 3.45	缺 slf4j，不选
证书字段一致性	13/13 SN、有效期与 DER 一致

18. 后续实施顺序

搭建 Maven 工程与 fat jar 构建；
实现配置、数据库校验、set/status；
实现字节级 DAO 与身份键、compare 引擎；
实现单份 CSV 报表（含“合并选择”列）；
实现报表读取、编码识别、标注校验；
实现 SQL 生成计划、ATTACH + INSERT OR REPLACE / UPDATE 脚本；
编写测试夹具（含 3000 条生成器）并验收；
部署到 /app/cert-compare，完成真实库只读比对与脚本执行演练。

48 KiB Raw Permalink Blame History Unescape Escape

HSM 主备数据库证书比对工具 设计文档