-- DMS file-property and version-lifecycle database regression.
|
-- Prerequisite: DMS V2.3 plus upgrade_dms_file_properties_20260820.sql.
|
-- The entire test runs in one transaction and always rolls back its test data.
|
|
\set ON_ERROR_STOP on
|
|
SET lock_timeout = '5s';
|
SET statement_timeout = '60s';
|
|
BEGIN;
|
|
DO $dms_file_property_schema_assertions$
|
DECLARE
|
required_object TEXT;
|
BEGIN
|
FOREACH required_object IN ARRAY ARRAY[
|
'public.dms_version_states',
|
'public.dms_version_relations',
|
'public.uni_dms_documents_document_no',
|
'public.uni_dms_file_versions_document_label',
|
'public.uni_dms_file_versions_document_id_id',
|
'public.uni_dms_version_states_document_effective',
|
'public.idx_dms_version_states_document_status',
|
'public.idx_dms_version_states_pending_effective',
|
'public.idx_dms_version_states_review_due'
|
] LOOP
|
IF to_regclass(required_object) IS NULL THEN
|
RAISE EXCEPTION 'required DMS file-property object % is missing', required_object;
|
END IF;
|
END LOOP;
|
END;
|
$dms_file_property_schema_assertions$;
|
|
INSERT INTO public.dms_folders (
|
id, tenant_id, parent_id, name, created_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000001',
|
'dms-file-property-test',
|
NULL,
|
'File property regression root',
|
'dms-file-property-test'
|
);
|
|
INSERT INTO public.dms_permission_resources (
|
id, tenant_id, resource_type, source_type, source_id, registered_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000001',
|
'dms-file-property-test',
|
'FOLDER',
|
'MIGRATION',
|
'file-property-folder',
|
'dms-file-property-test'
|
);
|
|
-- Document A starts in the legacy/raw-upload state so one-time completion can be tested.
|
INSERT INTO public.dms_documents (
|
id, tenant_id, folder_id, name, created_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000002',
|
'dms-file-property-test',
|
'019d1000-0000-7000-8000-000000000001',
|
'Controlled document A',
|
'dms-file-property-test'
|
);
|
|
INSERT INTO public.dms_permission_resources (
|
id, tenant_id, resource_type, source_type, source_id, registered_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000002',
|
'dms-file-property-test',
|
'DOCUMENT',
|
'MIGRATION',
|
'file-property-document-a',
|
'dms-file-property-test'
|
);
|
|
INSERT INTO public.dms_file_versions (
|
id, tenant_id, document_id, version_no, file_name, created_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000003',
|
'dms-file-property-test',
|
'019d1000-0000-7000-8000-000000000002',
|
1,
|
'controlled-document-a-v1.docx',
|
'dms-file-property-test'
|
);
|
|
INSERT INTO public.dms_permission_resources (
|
id, tenant_id, resource_type, source_type, source_id, registered_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000003',
|
'dms-file-property-test',
|
'FILE_VERSION',
|
'MIGRATION',
|
'file-property-version-a1',
|
'dms-file-property-test'
|
);
|
|
DO $dms_incomplete_metadata_rejected$
|
BEGIN
|
BEGIN
|
INSERT INTO public.dms_version_states (
|
file_version_id, tenant_id, document_id, lifecycle_status,
|
approved_at, approved_by, archived_at, effective_from,
|
approval_source_type, approval_source_id, status_changed_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000003',
|
'dms-file-property-test',
|
'019d1000-0000-7000-8000-000000000002',
|
'EFFECTIVE',
|
CURRENT_TIMESTAMP - INTERVAL '3 days',
|
'quality-user',
|
CURRENT_TIMESTAMP - INTERVAL '2 days',
|
CURRENT_TIMESTAMP - INTERVAL '1 day',
|
'MIGRATION',
|
'incomplete-state-must-fail',
|
'quality-user'
|
);
|
RAISE EXCEPTION 'incomplete business metadata entered the lifecycle';
|
EXCEPTION
|
WHEN check_violation THEN NULL;
|
END;
|
END;
|
$dms_incomplete_metadata_rejected$;
|
|
UPDATE public.dms_documents
|
SET document_no = 'MM-R02',
|
document_type = 'QUALITY_MANUAL',
|
responsible_dept_id = 'dept-quality'
|
WHERE id = '019d1000-0000-7000-8000-000000000002';
|
|
UPDATE public.dms_file_versions
|
SET version_label = 'D(09)',
|
prepared_by = 'author-a',
|
prepared_on = DATE '2023-08-30'
|
WHERE id = '019d1000-0000-7000-8000-000000000003';
|
|
DO $dms_completed_metadata_immutable$
|
BEGIN
|
BEGIN
|
UPDATE public.dms_documents
|
SET document_no = 'MM-R03'
|
WHERE id = '019d1000-0000-7000-8000-000000000002';
|
RAISE EXCEPTION 'completed document number was changed';
|
EXCEPTION
|
WHEN object_not_in_prerequisite_state THEN NULL;
|
END;
|
|
BEGIN
|
UPDATE public.dms_file_versions
|
SET version_label = 'D(10)'
|
WHERE id = '019d1000-0000-7000-8000-000000000003';
|
RAISE EXCEPTION 'completed version label was changed';
|
EXCEPTION
|
WHEN object_not_in_prerequisite_state THEN NULL;
|
END;
|
END;
|
$dms_completed_metadata_immutable$;
|
|
INSERT INTO public.dms_version_states (
|
file_version_id, tenant_id, document_id, lifecycle_status,
|
approved_at, approved_by, archived_at, effective_from, effective_to,
|
review_cycle_months, next_review_on,
|
approval_source_type, approval_source_id, status_changed_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000003',
|
'dms-file-property-test',
|
'019d1000-0000-7000-8000-000000000002',
|
'EFFECTIVE',
|
CURRENT_TIMESTAMP - INTERVAL '3 days',
|
'quality-user',
|
CURRENT_TIMESTAMP - INTERVAL '2 days',
|
CURRENT_TIMESTAMP - INTERVAL '1 day',
|
CURRENT_TIMESTAMP + INTERVAL '365 days',
|
36,
|
CURRENT_DATE + 365,
|
'WORKFLOW',
|
'workflow-a-v1-approved',
|
'quality-user'
|
);
|
|
-- A second revision is already approved and due for promotion, but remains pending.
|
INSERT INTO public.dms_file_versions (
|
id, tenant_id, document_id, version_no, version_label,
|
file_name, prepared_by, prepared_on, created_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000004',
|
'dms-file-property-test',
|
'019d1000-0000-7000-8000-000000000002',
|
2,
|
'D(10)',
|
'controlled-document-a-v2.docx',
|
'author-b',
|
CURRENT_DATE - 10,
|
'dms-file-property-test'
|
);
|
|
INSERT INTO public.dms_permission_resources (
|
id, tenant_id, resource_type, source_type, source_id, registered_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000004',
|
'dms-file-property-test',
|
'FILE_VERSION',
|
'MIGRATION',
|
'file-property-version-a2',
|
'dms-file-property-test'
|
);
|
|
INSERT INTO public.dms_version_states (
|
file_version_id, tenant_id, document_id, lifecycle_status,
|
approved_at, approved_by, archived_at, effective_from,
|
approval_source_type, approval_source_id, status_changed_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000004',
|
'dms-file-property-test',
|
'019d1000-0000-7000-8000-000000000002',
|
'PENDING_EFFECTIVE',
|
CURRENT_TIMESTAMP - INTERVAL '3 hours',
|
'quality-user',
|
CURRENT_TIMESTAMP - INTERVAL '2 hours',
|
CURRENT_TIMESTAMP - INTERVAL '1 hour',
|
'WORKFLOW',
|
'workflow-a-v2-approved',
|
'quality-user'
|
);
|
|
DO $dms_current_pending_not_history$
|
DECLARE
|
current_count INTEGER;
|
pending_count INTEGER;
|
history_count INTEGER;
|
BEGIN
|
SELECT count(*) INTO current_count
|
FROM public.dms_version_states
|
WHERE tenant_id = 'dms-file-property-test'
|
AND document_id = '019d1000-0000-7000-8000-000000000002'
|
AND lifecycle_status = 'EFFECTIVE'
|
AND effective_from <= CURRENT_TIMESTAMP
|
AND (effective_to IS NULL OR CURRENT_TIMESTAMP < effective_to);
|
|
SELECT count(*) INTO pending_count
|
FROM public.dms_version_states
|
WHERE tenant_id = 'dms-file-property-test'
|
AND document_id = '019d1000-0000-7000-8000-000000000002'
|
AND lifecycle_status = 'PENDING_EFFECTIVE';
|
|
SELECT count(*) INTO history_count
|
FROM public.dms_version_states
|
WHERE tenant_id = 'dms-file-property-test'
|
AND document_id = '019d1000-0000-7000-8000-000000000002'
|
AND lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE');
|
|
IF current_count <> 1 OR pending_count <> 1 OR history_count <> 0 THEN
|
RAISE EXCEPTION 'unexpected classification: current %, pending %, history %',
|
current_count, pending_count, history_count;
|
END IF;
|
END;
|
$dms_current_pending_not_history$;
|
|
-- Promotion is one transaction: retire the current revision before activating the pending one.
|
UPDATE public.dms_version_states
|
SET lifecycle_status = 'SUPERSEDED',
|
effective_to = CURRENT_TIMESTAMP,
|
status_reason = 'Superseded by D(10)',
|
status_changed_by = 'dms-scheduler',
|
revision = revision + 1
|
WHERE file_version_id = '019d1000-0000-7000-8000-000000000003';
|
|
UPDATE public.dms_version_states
|
SET lifecycle_status = 'EFFECTIVE',
|
status_reason = 'Scheduled promotion',
|
status_changed_by = 'dms-scheduler',
|
revision = revision + 1
|
WHERE file_version_id = '019d1000-0000-7000-8000-000000000004';
|
|
-- Review extension changes the lifecycle row, not the immutable FileVersion.
|
UPDATE public.dms_version_states
|
SET review_cycle_months = 24,
|
last_reviewed_on = CURRENT_DATE,
|
next_review_on = CURRENT_DATE + 730,
|
effective_to = CURRENT_TIMESTAMP + INTERVAL '730 days',
|
status_reason = 'Periodic review passed',
|
status_changed_by = 'quality-user',
|
revision = revision + 1
|
WHERE file_version_id = '019d1000-0000-7000-8000-000000000004';
|
|
DO $dms_lifecycle_after_promotion$
|
BEGIN
|
IF (SELECT count(*) FROM public.dms_version_states
|
WHERE tenant_id = 'dms-file-property-test'
|
AND document_id = '019d1000-0000-7000-8000-000000000002'
|
AND lifecycle_status = 'EFFECTIVE') <> 1 THEN
|
RAISE EXCEPTION 'promotion did not leave exactly one current revision';
|
END IF;
|
|
IF (SELECT count(*) FROM public.dms_version_states
|
WHERE tenant_id = 'dms-file-property-test'
|
AND document_id = '019d1000-0000-7000-8000-000000000002'
|
AND lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE')) <> 1 THEN
|
RAISE EXCEPTION 'superseded revision was not classified as history';
|
END IF;
|
|
BEGIN
|
UPDATE public.dms_version_states
|
SET lifecycle_status = 'EFFECTIVE',
|
effective_to = CURRENT_TIMESTAMP + INTERVAL '30 days',
|
status_reason = 'Illegal rollback',
|
status_changed_by = 'quality-user',
|
revision = revision + 1
|
WHERE file_version_id = '019d1000-0000-7000-8000-000000000003';
|
RAISE EXCEPTION 'terminal lifecycle state was reversed';
|
EXCEPTION
|
WHEN object_not_in_prerequisite_state THEN NULL;
|
END;
|
END;
|
$dms_lifecycle_after_promotion$;
|
|
-- Document B is the target of a version-owned related-file declaration.
|
INSERT INTO public.dms_documents (
|
id, tenant_id, folder_id, document_no, name, document_type,
|
responsible_dept_id, created_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000005',
|
'dms-file-property-test',
|
'019d1000-0000-7000-8000-000000000001',
|
'SOP-R01',
|
'Related document B',
|
'SOP',
|
'dept-quality',
|
'dms-file-property-test'
|
);
|
|
INSERT INTO public.dms_permission_resources (
|
id, tenant_id, resource_type, source_type, source_id, registered_by
|
) VALUES (
|
'019d1000-0000-7000-8000-000000000005',
|
'dms-file-property-test',
|
'DOCUMENT',
|
'MIGRATION',
|
'file-property-document-b',
|
'dms-file-property-test'
|
);
|
|
INSERT INTO public.dms_version_relations (
|
tenant_id, document_id, file_version_id, related_document_id,
|
relation_type, created_by
|
) VALUES (
|
'dms-file-property-test',
|
'019d1000-0000-7000-8000-000000000002',
|
'019d1000-0000-7000-8000-000000000004',
|
'019d1000-0000-7000-8000-000000000005',
|
'REFERENCE',
|
'quality-user'
|
);
|
|
DO $dms_related_document_constraints$
|
BEGIN
|
BEGIN
|
INSERT INTO public.dms_version_relations (
|
tenant_id, document_id, file_version_id, related_document_id,
|
relation_type, created_by
|
) VALUES (
|
'dms-file-property-test',
|
'019d1000-0000-7000-8000-000000000002',
|
'019d1000-0000-7000-8000-000000000004',
|
'019d1000-0000-7000-8000-000000000002',
|
'REFERENCE',
|
'quality-user'
|
);
|
RAISE EXCEPTION 'self-related document was accepted';
|
EXCEPTION
|
WHEN check_violation THEN NULL;
|
END;
|
|
BEGIN
|
DELETE FROM public.dms_version_relations
|
WHERE tenant_id = 'dms-file-property-test';
|
RAISE EXCEPTION 'append-only version relation was deleted';
|
EXCEPTION
|
WHEN object_not_in_prerequisite_state THEN NULL;
|
END;
|
END;
|
$dms_related_document_constraints$;
|
|
SET CONSTRAINTS ALL IMMEDIATE;
|
ROLLBACK;
|
|
\echo 'DMS file-property and version-lifecycle regression passed.'
|
