# 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 在主库执行；执行前备份，从库只读引用。

设计重点：

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

```sql
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 唯一记录为例：

```text
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）对生成脚本的执行验证：

```text
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. 总体架构

```text
+----------------------------------------------------------+
| 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`。实际源码结构：

```text
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）：

```properties
# 数据库路径
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 优先级

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

### 5.3 配置校验

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

---

## 6. CLI 设计

### 6.1 启动方式

```bash
# 交互模式
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 交互命令语法

```text
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 连接

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

连接后执行：

```sql
PRAGMA busy_timeout = 5000;
```

说明：

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

### 7.2 读取投影

默认比较不读取大字段：

```sql
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 编码工具

```java
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 身份键

```java
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 归一化

```text
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 流程

```text
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` 按以下顺序识别编码：

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

CSV 解析器支持：

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

### 9.7 报表一致性校验

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

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

任一不满足即判定报表过期或被修改，拒绝生成脚本并提示重新 `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 生成步骤

```text
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

包装：

```sql
PRAGMA foreign_keys = OFF;

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

BEGIN IMMEDIATE;
```

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

```sql
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`）：

```sql
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;
```

末尾自检：

```sql
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 生成计划

控制台示例：

```text
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 在主库执行：

```bash
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 输出

```text
主库: /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 输出

```text
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 关键断言

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

### 13.4 手工验收命令

```bash
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` 关键配置：

```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>
```

打包：

```bash
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 部署目录

```text
/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 备份
```

启动脚本：

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

### 14.3 部署步骤

```bash
# 本地构建后上传
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|16`、`ok` |
| 中文恢复 | 从库 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. 待确认事项

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

---

## 17. 附录

### 17.1 表结构（测试库）

```sql
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 已验证的关键命令

```bash
# 版本与环境
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|16`、`ok`，重复执行仍为 16 条 |
| sqlite-jdbc 3.36 | 在 glibc 2.12 加载成功 |
| sqlite-jdbc 3.45 | 缺 slf4j，不选 |
| 证书字段一致性 | 13/13 SN、有效期与 DER 一致 |

---

## 18. 后续实施顺序

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