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。