From 34981c30a78e8bbd7791131059a9210f9928b62c Mon Sep 17 00:00:00 2001
From: 刘光辉 <347230014@qq.com>
Date: 星期四, 17 九月 2026 09:24:09 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master' into master
---
jnpf-dms/jnpf-dms-biz/src/test/resources/sql/dms_file_properties_test.sql | 388 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 388 insertions(+), 0 deletions(-)
diff --git a/jnpf-dms/jnpf-dms-biz/src/test/resources/sql/dms_file_properties_test.sql b/jnpf-dms/jnpf-dms-biz/src/test/resources/sql/dms_file_properties_test.sql
new file mode 100644
index 0000000..2e3faaf
--- /dev/null
+++ b/jnpf-dms/jnpf-dms-biz/src/test/resources/sql/dms_file_properties_test.sql
@@ -0,0 +1,388 @@
+-- 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.'
--
Gitblit v1.8.0