跳转至

DuckDB 存储参考

生成来源:Server/schema/init.sql、Server/schema/rebuild_fts.sql。字段契约以 docs/reference/protocol.md 为准;本页是 DDL 的结构化投影。

schema_meta

key value
schema_version 2.0
fingerprint_dims 16

assets

列 类型 说明
asset_path VARCHAR PRIMARY KEY
logical_id VARCHAR NOT NULL UNIQUE e.g. AnimMontage:AM_Knight_HeavySlash_02
asset_class VARCHAR NOT NULL
parent_class VARCHAR NOT NULL
taxonomy VARCHAR[] ['Asset', 'Animation', 'Montage']
tokens VARCHAR camel/path/parent-context terms, fed to FTS
content_hash VARCHAR NOT NULL change detection ONLY, never used for dedup
duration FLOAT DEFAULT 0.0
bounds_size FLOAT[3] [SizeX, SizeY, SizeZ]
skeleton_path VARCHAR
dominant_limb VARCHAR
fingerprint_vec FLOAT[16]
interface_spec MAP(VARCHAR, VARCHAR)
domain_spec MAP(VARCHAR, VARCHAR)
yaml_cache VARCHAR pre-rendered annotated YAML spec
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

asset_edges

列 类型 说明
source_path VARCHAR NOT NULL
target_path VARCHAR NOT NULL
edge_type VARCHAR NOT NULL 'DEPENDS_ON' | 'CO_OCCURS'
weight FLOAT DEFAULT 1.0

v_search 投影视图

把 domain_spec 中的键投影为可过滤列,避免在物理表中新增列。

CREATE OR REPLACE VIEW v_search AS
SELECT a.*,
       COALESCE(TRY_CAST(a.domain_spec['forward_cm'] AS DOUBLE), 0.0) AS forward_cm,
       COALESCE(a.domain_spec['strike_window'], '')                    AS strike_window,
       COALESCE(a.domain_spec['hint'], '')                             AS hint
FROM assets a;

FTS 索引(快照语义)

DuckDB 的 FTS 索引不会随 INSERT 自动更新,每次批量灌库后必须重建:

PRAGMA drop_fts_index('assets');
PRAGMA create_fts_index('assets', 'asset_path', 'tokens', 'logical_id');

初始化时执行的建索引语句:

PRAGMA create_fts_index('assets', 'asset_path', 'tokens', 'logical_id');

查重查询

ual-cli lint-duplicates 使用 db.duplicate_pairs() 的余弦自连接,核心表达式为 array_cosine_similarity(a.fingerprint_vec, b.fingerprint_vec),并要求 DuckDB >= 1.1(已在 2.0.0 实测)。完整 SQL 见 Server/ual_common/db.py。