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_permission_v2_3_test.sql | 388 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 388 insertions(+), 0 deletions(-) diff --git a/jnpf-dms/jnpf-dms-biz/src/test/resources/sql/dms_permission_v2_3_test.sql b/jnpf-dms/jnpf-dms-biz/src/test/resources/sql/dms_permission_v2_3_test.sql new file mode 100644 index 0000000..8d88628 --- /dev/null +++ b/jnpf-dms/jnpf-dms-biz/src/test/resources/sql/dms_permission_v2_3_test.sql @@ -0,0 +1,388 @@ +-- DMS permission V2.3 database regression. +-- Prerequisite: V2.2 schema followed by upgrade_dms_permission_v2_2_to_v2_3_20260817.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_v23_schema_assertions$ +DECLARE + required_object TEXT; +BEGIN + FOREACH required_object IN ARRAY ARRAY[ + 'public.dms_access_exclusions', + 'public.dms_permission_revisions', + 'public.idx_dms_folders_parent_name', + 'public.idx_dms_documents_folder_name', + 'public.idx_dms_file_versions_document_id' + ] LOOP + IF to_regclass(required_object) IS NULL THEN + RAISE EXCEPTION 'required V2.3 object % is missing', required_object; + END IF; + END LOOP; + + IF EXISTS ( + SELECT 1 + FROM pg_catalog.pg_index i + WHERE i.indexrelid IN ( + 'public.idx_dms_folders_parent_name'::REGCLASS, + 'public.idx_dms_documents_folder_name'::REGCLASS, + 'public.idx_dms_file_versions_document_id'::REGCLASS + ) + AND (NOT i.indisvalid OR NOT i.indisready) + ) THEN + RAISE EXCEPTION 'one or more DMS V2.3 paging indexes are invalid'; + END IF; +END; +$dms_v23_schema_assertions$; + +INSERT INTO public.dms_folders ( + id, tenant_id, parent_id, name, created_by +) VALUES ( + '019d0000-0000-7000-8000-000000000001', + 'dms-v23-regression', + NULL, + 'V2.3 regression root', + 'dms-v23-test' +); + +INSERT INTO public.dms_permission_resources ( + id, tenant_id, resource_type, source_type, source_id, registered_by +) VALUES ( + '019d0000-0000-7000-8000-000000000001', + 'dms-v23-regression', + 'FOLDER', + 'MIGRATION', + 'dms-v23-regression-folder', + 'dms-v23-test' +); + +INSERT INTO public.dms_documents ( + id, tenant_id, folder_id, name, created_by +) VALUES ( + '019d0000-0000-7000-8000-000000000002', + 'dms-v23-regression', + '019d0000-0000-7000-8000-000000000001', + 'V2.3 regression document', + 'dms-v23-test' +); + +INSERT INTO public.dms_permission_resources ( + id, tenant_id, resource_type, source_type, source_id, registered_by +) VALUES ( + '019d0000-0000-7000-8000-000000000002', + 'dms-v23-regression', + 'DOCUMENT', + 'MIGRATION', + 'dms-v23-regression-document', + 'dms-v23-test' +); + +INSERT INTO public.dms_file_versions ( + id, tenant_id, document_id, version_no, file_name, created_by +) VALUES ( + '019d0000-0000-7000-8000-000000000003', + 'dms-v23-regression', + '019d0000-0000-7000-8000-000000000002', + 1, + 'v2.3-regression.txt', + 'dms-v23-test' +); + +INSERT INTO public.dms_permission_resources ( + id, tenant_id, resource_type, source_type, source_id, registered_by +) VALUES ( + '019d0000-0000-7000-8000-000000000003', + 'dms-v23-regression', + 'FILE_VERSION', + 'MIGRATION', + 'dms-v23-regression-version', + 'dms-v23-test' +); + +INSERT INTO public.dms_file_permissions ( + id, tenant_id, resource_type, resource_id, resource_scope, + principal_type, principal_id, include_child_orgs, action_code, + source_type, source_id, max_use_count, grant_reason, + granted_by_type, granted_by_id +) VALUES ( + '019d0000-0000-7000-8000-000000000100', + 'dms-v23-regression', + 'DOCUMENT', + '019d0000-0000-7000-8000-000000000002', + 'SELF', + 'USER', + 'dms-v23-user', + FALSE, + 'READ', + 'MIGRATION', + 'dms-v23-regression-permission', + 5, + 'DMS V2.3 database regression', + 'SERVICE', + 'dms-v23-test' +); + +DO $dms_v23_permission_insert_revision$ +BEGIN + IF (SELECT revision FROM public.dms_permission_revisions + WHERE tenant_id = 'dms-v23-regression') <> 1 THEN + RAISE EXCEPTION 'one permission INSERT statement must initialize tenant revision to 1'; + END IF; +END; +$dms_v23_permission_insert_revision$; + +-- Empty statements fire statement-level triggers but must not change the tenant revision. +INSERT INTO public.dms_file_permissions ( + tenant_id, resource_type, resource_id, resource_scope, + principal_type, principal_id, action_code, source_type, source_id, + granted_by_type, granted_by_id +) +SELECT + 'dms-v23-regression', + 'DOCUMENT', + '019d0000-0000-7000-8000-000000000002', + 'SELF', + 'USER', + 'nobody', + 'READ', + 'MIGRATION', + 'dms-v23-empty-permission', + 'SERVICE', + 'dms-v23-test' +WHERE FALSE; + +DO $dms_v23_empty_permission_revision$ +BEGIN + IF (SELECT revision FROM public.dms_permission_revisions + WHERE tenant_id = 'dms-v23-regression') <> 1 THEN + RAISE EXCEPTION 'empty permission INSERT must not change tenant revision'; + END IF; +END; +$dms_v23_empty_permission_revision$; + +-- A usage changes the grant row revision but must not change the tenant permission revision. +INSERT INTO public.dms_permission_usages ( + tenant_id, permission_id, user_id, action_code, + actual_resource_type, actual_resource_id, operation_id +) VALUES ( + 'dms-v23-regression', + '019d0000-0000-7000-8000-000000000100', + 'dms-v23-user', + 'READ', + 'FILE_VERSION', + '019d0000-0000-7000-8000-000000000003', + '019d0000-0000-7000-8000-000000000200' +); + +DO $dms_v23_usage_revision$ +BEGIN + IF (SELECT revision FROM public.dms_permission_revisions + WHERE tenant_id = 'dms-v23-regression') <> 1 THEN + RAISE EXCEPTION 'permission usage must not change tenant revision'; + END IF; + IF (SELECT used_count FROM public.dms_file_permissions + WHERE id = '019d0000-0000-7000-8000-000000000100') <> 1 THEN + RAISE EXCEPTION 'permission usage did not increment used_count'; + END IF; +END; +$dms_v23_usage_revision$; + +-- Two exclusions in one statement must bump the tenant revision only once. +INSERT INTO public.dms_access_exclusions ( + id, tenant_id, resource_type, resource_id, user_id, action_code, + source_type, source_id, exclude_reason, excluded_by_type, excluded_by_id +) VALUES +( + '019d0000-0000-7000-8000-000000000301', + 'dms-v23-regression', + 'DOCUMENT', + '019d0000-0000-7000-8000-000000000002', + 'dms-v23-user', + 'READ', + 'MIGRATION', + 'dms-v23-regression-exclusions', + 'Document exclusion regression', + 'SERVICE', + 'dms-v23-test' +), +( + '019d0000-0000-7000-8000-000000000302', + 'dms-v23-regression', + 'FILE_VERSION', + '019d0000-0000-7000-8000-000000000003', + 'dms-v23-user', + 'READ', + 'MIGRATION', + 'dms-v23-regression-exclusions', + 'FileVersion exclusion regression', + 'SERVICE', + 'dms-v23-test' +); + +DO $dms_v23_exclusion_insert_revision$ +BEGIN + IF (SELECT revision FROM public.dms_permission_revisions + WHERE tenant_id = 'dms-v23-regression') <> 2 THEN + RAISE EXCEPTION 'one exclusion INSERT statement must bump tenant revision exactly once'; + END IF; +END; +$dms_v23_exclusion_insert_revision$; + +INSERT INTO public.dms_access_exclusions ( + tenant_id, resource_type, resource_id, user_id, + source_type, source_id, excluded_by_type, excluded_by_id +) +SELECT + 'dms-v23-regression', + 'DOCUMENT', + '019d0000-0000-7000-8000-000000000002', + 'nobody', + 'MIGRATION', + 'dms-v23-empty-exclusion', + 'SERVICE', + 'dms-v23-test' +WHERE FALSE; + +DO $dms_v23_empty_exclusion_revision$ +BEGIN + IF (SELECT revision FROM public.dms_permission_revisions + WHERE tenant_id = 'dms-v23-regression') <> 2 THEN + RAISE EXCEPTION 'empty exclusion INSERT must not change tenant revision'; + END IF; +END; +$dms_v23_empty_exclusion_revision$; + +UPDATE public.dms_access_exclusions +SET revoked_at = CURRENT_TIMESTAMP, + revoked_by_type = 'SERVICE', + revoked_by_id = 'dms-v23-test', + revoke_reason = 'DMS V2.3 regression revocation', + revision = revision + 1 +WHERE tenant_id = 'dms-v23-regression'; + +DO $dms_v23_exclusion_revoke_revision$ +BEGIN + IF (SELECT revision FROM public.dms_permission_revisions + WHERE tenant_id = 'dms-v23-regression') <> 3 THEN + RAISE EXCEPTION 'one exclusion revocation statement must bump tenant revision exactly once'; + END IF; +END; +$dms_v23_exclusion_revoke_revision$; + +UPDATE public.dms_file_permissions +SET revoked_at = CURRENT_TIMESTAMP, + revoked_by_type = 'SERVICE', + revoked_by_id = 'dms-v23-test', + revoke_reason = 'DMS V2.3 regression revocation', + revision = revision + 1 +WHERE id = '019d0000-0000-7000-8000-000000000100'; + +DO $dms_v23_permission_revoke_revision$ +BEGIN + IF (SELECT revision FROM public.dms_permission_revisions + WHERE tenant_id = 'dms-v23-regression') <> 4 THEN + RAISE EXCEPTION 'permission revocation must bump tenant revision exactly once'; + END IF; +END; +$dms_v23_permission_revoke_revision$; + +DO $dms_v23_immutable_assertions$ +BEGIN + BEGIN + UPDATE public.dms_file_permissions + SET id = '019d0000-0000-7000-8000-000000000101', + revision = revision + 1 + WHERE id = '019d0000-0000-7000-8000-000000000100'; + RAISE EXCEPTION 'permission id update unexpectedly succeeded'; + EXCEPTION + WHEN SQLSTATE '55000' THEN NULL; + END; + + BEGIN + UPDATE public.dms_access_exclusions + SET revoked_at = CURRENT_TIMESTAMP, + revoked_by_type = 'SERVICE', + revoked_by_id = 'dms-v23-test', + revoke_reason = 'Repeated revocation', + revision = revision + 1 + WHERE id = '019d0000-0000-7000-8000-000000000301'; + RAISE EXCEPTION 'repeated exclusion revocation unexpectedly succeeded'; + EXCEPTION + WHEN SQLSTATE '55000' THEN NULL; + END; + + BEGIN + DELETE FROM public.dms_access_exclusions + WHERE tenant_id = 'dms-v23-regression'; + RAISE EXCEPTION 'physical exclusion delete unexpectedly succeeded'; + EXCEPTION + WHEN SQLSTATE '55000' THEN NULL; + END; + + BEGIN + TRUNCATE TABLE public.dms_access_exclusions; + RAISE EXCEPTION 'physical exclusion truncate unexpectedly succeeded'; + EXCEPTION + WHEN SQLSTATE '55000' THEN NULL; + END; + + BEGIN + DELETE FROM public.dms_permission_revisions + WHERE tenant_id = 'dms-v23-regression'; + RAISE EXCEPTION 'physical permission revision delete unexpectedly succeeded'; + EXCEPTION + WHEN SQLSTATE '55000' THEN NULL; + END; +END; +$dms_v23_immutable_assertions$; + +DO $dms_v23_constraint_assertions$ +BEGIN + BEGIN + INSERT INTO public.dms_access_exclusions ( + tenant_id, resource_type, resource_id, user_id, + source_type, source_id, excluded_by_type, excluded_by_id + ) VALUES ( + 'dms-v23-regression', + 'FOLDER', + '019d0000-0000-7000-8000-000000000001', + 'dms-v23-user', + 'MIGRATION', + 'dms-v23-invalid-folder-exclusion', + 'SERVICE', + 'dms-v23-test' + ); + RAISE EXCEPTION 'FOLDER exclusion unexpectedly succeeded'; + EXCEPTION + WHEN check_violation THEN NULL; + END; + + BEGIN + INSERT INTO public.dms_access_exclusions ( + tenant_id, resource_type, resource_id, user_id, + source_type, source_id, excluded_by_type, excluded_by_id + ) VALUES ( + 'another-tenant', + 'DOCUMENT', + '019d0000-0000-7000-8000-000000000002', + 'dms-v23-user', + 'MIGRATION', + 'dms-v23-cross-tenant-exclusion', + 'SERVICE', + 'dms-v23-test' + ); + RAISE EXCEPTION 'cross-tenant exclusion unexpectedly succeeded'; + EXCEPTION + WHEN foreign_key_violation THEN NULL; + END; +END; +$dms_v23_constraint_assertions$; + +ROLLBACK; + +SELECT 'DMS permission V2.3 database regression passed' AS result; -- Gitblit v1.8.0