35 lines
1.8 KiB
MySQL
35 lines
1.8 KiB
MySQL
|
-- Phase 2.1: 模型发布日期证据元数据
|
|||
|
|
-- 区分一级官方发布日期与二级权威佐证日期，避免混淆 source_url 与发布日期证据层级
|
|||
|
|
|
|||
|
|
DO $$
|
|||
|
|
BEGIN
|
|||
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='models' AND column_name='date_confidence') THEN
|
|||
|
|
ALTER TABLE models ADD COLUMN date_confidence TEXT NOT NULL DEFAULT 'unknown';
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='models' AND column_name='date_source_kind') THEN
|
|||
|
|
ALTER TABLE models ADD COLUMN date_source_kind TEXT NOT NULL DEFAULT 'unknown';
|
|||
|
|
END IF;
|
|||
|
|
END $$;
|
|||
|
|
|
|||
|
|
DO $$
|
|||
|
|
BEGIN
|
|||
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname='chk_models_date_confidence') THEN
|
|||
|
|
ALTER TABLE models
|
|||
|
|
ADD CONSTRAINT chk_models_date_confidence
|
|||
|
|
CHECK (date_confidence IN ('official_primary', 'secondary_authoritative', 'inferred', 'unknown'));
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname='chk_models_date_source_kind') THEN
|
|||
|
|
ALTER TABLE models
|
|||
|
|
ADD CONSTRAINT chk_models_date_source_kind
|
|||
|
|
CHECK (date_source_kind IN ('official_announcement', 'official_product_page', 'secondary_authoritative_report', 'catalog_backfill', 'unknown'));
|
|||
|
|
END IF;
|
|||
|
|
END $$;
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_models_date_confidence ON models(date_confidence);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_models_date_source_kind ON models(date_source_kind);
|
|||
|
|
|
|||
|
|
COMMENT ON COLUMN models.date_confidence IS '发布日期证据置信度：official_primary / secondary_authoritative / inferred / unknown';
|
|||
|
|
COMMENT ON COLUMN models.date_source_kind IS '发布日期证据来源类型：official_announcement / official_product_page / secondary_authoritative_report / catalog_backfill / unknown';
|