68 lines
3.2 KiB
SQL
68 lines
3.2 KiB
SQL
-- 2026-10-12-01 下线 pHash 去重 + 启用 image_embeddings 存 FashionSigLIP 向量
|
||
--
|
||
-- 对应规格：docs/superpowers/specs/2026-10-12-fashionclip-siglip-vision-service-design.md §5.2 / §8
|
||
--
|
||
-- 不可逆：DROP COLUMN / DROP TABLE 无回滚手段，执行前务必确认 db/backups/ 有备份。
|
||
--
|
||
-- 应用后请重新导出结构：
|
||
-- ./bin/dbtool dump -clean -out db/backups/db_dump.sql
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- ① 下线自动去重：DROP 三列 + 两个 HNSW 索引
|
||
--
|
||
-- 注意：两个图片视图为 `SELECT i.*`，因此**必须先删视图**，删列后再按 `i.*` 重建——
|
||
-- 否则 DROP COLUMN 会报 "cannot drop column ... because other objects depend on it"。
|
||
-- 重建后 `i.*` 展开的列集自动不含已删的三列，无需显式枚举。
|
||
-- （与 db/migrations/2026-09-24-01-runway-parent-image-id.sql 处理 look_index 的做法一致。）
|
||
-- ---------------------------------------------------------------------------
|
||
DROP VIEW IF EXISTS public_brand_runway_images;
|
||
DROP VIEW IF EXISTS public_street_snap_images;
|
||
|
||
DROP INDEX IF EXISTS brand_runway_images_phash_hnsw;
|
||
DROP INDEX IF EXISTS street_snap_images_phash_hnsw;
|
||
|
||
ALTER TABLE brand_runway_images
|
||
DROP COLUMN IF EXISTS phash,
|
||
DROP COLUMN IF EXISTS is_duplicate,
|
||
DROP COLUMN IF EXISTS dup_of;
|
||
|
||
ALTER TABLE street_snap_images
|
||
DROP COLUMN IF EXISTS phash,
|
||
DROP COLUMN IF EXISTS is_duplicate,
|
||
DROP COLUMN IF EXISTS dup_of;
|
||
|
||
CREATE VIEW public_brand_runway_images AS
|
||
SELECT i.* FROM brand_runway_images i
|
||
JOIN brand_runways r ON r.id = i.runway_id
|
||
WHERE i.is_deleted = 0 AND r.status = 'published' AND r.is_deleted = 0;
|
||
|
||
CREATE VIEW public_street_snap_images AS
|
||
SELECT i.* FROM street_snap_images i
|
||
JOIN street_snaps s ON s.id = i.snap_id
|
||
WHERE i.is_deleted = 0 AND s.status = 'published' AND s.is_deleted = 0;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- ② 删死表 image_duplicates（2026-09-21 建表后全仓零引用）
|
||
-- ---------------------------------------------------------------------------
|
||
DROP TABLE IF EXISTS image_duplicates;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- ③ image_embeddings 改造为可用状态
|
||
-- 该表 2026-09-21 建表后一直零引用、零数据，此处原地改造：
|
||
-- - kind: smallint -> varchar(8)，对齐 dto.IngestKind（"runway" / "street"）
|
||
-- - embedding: vector(512) -> vector(768)，M0 实测模型输出 768 维
|
||
-- - 补 (kind, image_id) 唯一索引：原表只有自增主键，
|
||
-- 不加唯一约束会导致同一张图重复写入多行
|
||
-- ---------------------------------------------------------------------------
|
||
ALTER TABLE image_embeddings
|
||
ALTER COLUMN kind TYPE varchar(8) USING kind::varchar(8),
|
||
ALTER COLUMN embedding TYPE vector(768);
|
||
|
||
-- 旧向量维度与新维度不同，重建该列上的 HNSW 索引（pgvector 索引绑定列的 typmod）
|
||
DROP INDEX IF EXISTS image_embeddings_vec_idx;
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_image_embeddings_kind_image
|
||
ON image_embeddings (kind, image_id);
|
||
|
||
CREATE INDEX IF NOT EXISTS image_embeddings_vec_idx
|
||
ON image_embeddings USING hnsw (embedding vector_cosine_ops); |