- Add sql/00_schema.sql: create dedicated ProductionDataBaseSync schema (idempotent)
- Move SyncQueue and usp_SyncApply from dbo into ProductionDataBaseSync
- Add sql/03_sync_log_archive.sql: permanent, append-only SyncLogArchive that
records both OriginalOperateType and ProcessedOperateType plus the Access log
OriginalTime, so pipeline divergences (e.g. Insert applied as Delete) stay
reconstructible forever (SyncQueue is transient and only keeps processed type)
- config.py: inject sync_queue_table / archive_table / apply_proc (default to the
new schema); SqlWriter takes these names instead of hardcoding dbo
- sql_writer.py: add ArchiveRow + insert_archive_row (dedup on source keys),
parametrize queue/archive/proc names throughout
- capture.py: archive every consumed log row before enqueue (preserves evidence
before cleanup deletes the Access log)
- service.py: pass the three names into SqlWriter
- tests: read queue/proc names from config instead of hardcoding dbo.SyncQueue
- sql/01_sync_queue.sql: idempotent DDL for dbo.SyncQueue (PK + unique
dedup index + pending lookup index), safe to re-run.
- sql/02_sync_apply.sql: dbo.usp_SyncApply (@MaxRetries INT=5). Per
distinct (TargetSchema,TargetTable) it builds column projections from
sys.columns (excludes ID key/computed/identity/rowversion) and runs a
dynamic-SQL MERGE (last-write-wins via ROW_NUMBER over SourceLogID DESC)
for Insert/Update plus a DELETE for the last op = Delete.
SET IDENTITY_INSERT ON preserves Access PKs.
- tests/conftest.py: sql_conn fixture reads conn_str from gitignored
config.yaml via load_config; skipped without RUN_INTEGRATION=1.
- tests/test_apply_proc.py: integration test covering IDENTITY-preserving
INSERT, last-write-wins UPDATE, BIT conversion, and DELETE; cleans up.
Deviation from the brief's procedure (root-cause fix, design preserved):
every JSON path key is quoted ('$."col"') so non-ASCII column names
(e.g. Chinese 名字/数量) parse correctly. Without quoting, JSON_VALUE
raises "JSON path format is not correct" on Chinese columns, which is the
real target schema for this Access->SQL Server sync.
Co-Authored-By: Claude <noreply@anthropic.com>
