wren
76 lines
3.1 KiB
PL/PgSQL
76 lines
3.1 KiB
PL/PgSQL
BEGIN;
|
||
|
||
ALTER TABLE leaudit_documents
|
||
ADD COLUMN IF NOT EXISTS version_group_key VARCHAR(64),
|
||
ADD COLUMN IF NOT EXISTS version_no INTEGER NOT NULL DEFAULT 1,
|
||
ADD COLUMN IF NOT EXISTS previous_version_id BIGINT,
|
||
ADD COLUMN IF NOT EXISTS root_version_id BIGINT,
|
||
ADD COLUMN IF NOT EXISTS is_latest_version BOOLEAN NOT NULL DEFAULT true,
|
||
ADD COLUMN IF NOT EXISTS normalized_name VARCHAR(512);
|
||
|
||
COMMENT ON COLUMN leaudit_documents.biz_document_id IS '平台内部追踪号（兼容旧字段名）';
|
||
COMMENT ON COLUMN leaudit_documents.version_group_key IS '文档版本归档组键，同一名称版本链共用';
|
||
COMMENT ON COLUMN leaudit_documents.version_no IS '同一文档系列中的版本号，从 1 开始';
|
||
COMMENT ON COLUMN leaudit_documents.previous_version_id IS '上一版本文档ID';
|
||
COMMENT ON COLUMN leaudit_documents.root_version_id IS '首版本文档ID';
|
||
COMMENT ON COLUMN leaudit_documents.is_latest_version IS '是否当前最新版本';
|
||
COMMENT ON COLUMN leaudit_documents.normalized_name IS '归一化文件名（不含扩展名），用于版本匹配';
|
||
|
||
UPDATE leaudit_documents d
|
||
SET
|
||
version_group_key = COALESCE(d.version_group_key, 'legacy-' || d.id::text),
|
||
version_no = COALESCE(d.version_no, 1),
|
||
root_version_id = COALESCE(d.root_version_id, d.id),
|
||
is_latest_version = COALESCE(d.is_latest_version, true),
|
||
normalized_name = COALESCE(
|
||
d.normalized_name,
|
||
NULLIF(
|
||
trim(
|
||
regexp_replace(
|
||
lower(
|
||
regexp_replace(
|
||
regexp_replace(
|
||
COALESCE(
|
||
(
|
||
SELECT f.file_name
|
||
FROM leaudit_document_files f
|
||
WHERE f.document_id = d.id
|
||
ORDER BY f.is_active DESC, f.id DESC
|
||
LIMIT 1
|
||
),
|
||
''
|
||
),
|
||
'\.[^.]+$',
|
||
'',
|
||
''
|
||
),
|
||
'[[:space:]_-]+',
|
||
' ',
|
||
'g'
|
||
)
|
||
),
|
||
'(?:\(|（)\d+(?:\)|）)$|(?:[-_\s]*副本|[-_\s]*copy)$',
|
||
'',
|
||
'g'
|
||
)
|
||
),
|
||
''
|
||
)
|
||
)
|
||
WHERE d.version_group_key IS NULL
|
||
OR d.root_version_id IS NULL
|
||
OR d.normalized_name IS NULL;
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_leaudit_documents_version_group
|
||
ON leaudit_documents(version_group_key);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_leaudit_documents_name_match
|
||
ON leaudit_documents(type_id, region, normalized_name, is_latest_version)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_leaudit_documents_latest_version_group
|
||
ON leaudit_documents(version_group_key)
|
||
WHERE is_latest_version = true AND deleted_at IS NULL;
|
||
|
||
COMMIT;
|