package jnpf.dmsMapper;
|
|
import jnpf.dmsDatabase.DmsUuidTypeHandler;
|
import jnpf.dmsPermission.DmsFileCenterQuery;
|
import org.apache.ibatis.mapping.BoundSql;
|
import org.apache.ibatis.mapping.MappedStatement;
|
import org.apache.ibatis.session.Configuration;
|
import org.junit.jupiter.api.BeforeEach;
|
import org.junit.jupiter.api.Test;
|
|
import java.util.Arrays;
|
import java.util.UUID;
|
|
import static org.junit.jupiter.api.Assertions.assertEquals;
|
import static org.junit.jupiter.api.Assertions.assertFalse;
|
import static org.junit.jupiter.api.Assertions.assertTrue;
|
|
class DmsFileCenterMapperSqlTest {
|
private Configuration configuration;
|
|
@BeforeEach
|
void setUp() {
|
configuration = new Configuration();
|
configuration.getTypeHandlerRegistry().register(UUID.class, DmsUuidTypeHandler.class);
|
try (java.io.InputStream stream = getClass().getResourceAsStream("/mapper/DmsFileCenterMapper.xml")) {
|
new org.apache.ibatis.builder.xml.XMLMapperBuilder(stream, configuration,
|
"mapper/DmsFileCenterMapper.xml", configuration.getSqlFragments()).parse();
|
} catch (java.io.IOException e) { throw new java.io.UncheckedIOException(e); }
|
}
|
|
@Test
|
void immediateChildrenBranchIsLimitedToApprovalReadGrantsInListingsAndNavigation() {
|
var q = query(false); q.setImmediateParentOrgIds(java.util.List.of("parent"));
|
for (String method : java.util.List.of("selectFolders", "selectCurrentDocuments", "selectSharedDocuments", "selectHistoricalVersions")) {
|
String sql = sql(method, q);
|
assertTrue(sql.contains("p.approved_org_scope = 'CHILDREN'"));
|
assertTrue(sql.contains("p.granted_by_id = 'dms-approved-read'"));
|
assertTrue(sql.contains("p.source_type = 'WORKFLOW'"));
|
}
|
}
|
|
@Test
|
void ordinaryFolderPageFiltersPermissionsBeforeStableLimit() {
|
String sql = sql("selectFolders", query(false));
|
|
assertTrue(sql.contains("FROM public.dms_file_permissions p"));
|
assertTrue(sql.contains("authorized_navigation_seeds"));
|
assertTrue(sql.contains("authorized_navigation_ancestors"));
|
assertTrue(sql.contains("SELECT granted.id, granted.parent_id"));
|
assertTrue(sql.contains("SELECT document_folder.id, document_folder.parent_id"));
|
assertTrue(sql.contains("SELECT version_folder.id, version_folder.parent_id"));
|
assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
|
assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
|
assertTrue(sql.contains("dms_access_exclusions"));
|
assertTrue(sql.contains("UNION SELECT parent.id, parent.parent_id FROM authorized_navigation_ancestors child"));
|
assertTrue(sql.contains("OR f.id IN (SELECT id FROM authorized_navigation_ancestors)"));
|
assertTrue(sql.contains("p.principal_type = 'USER'"));
|
assertTrue(sql.contains("p.principal_type = 'ORG'"));
|
assertTrue(sql.contains("p.principal_type = 'POSITION'"));
|
assertTrue(sql.contains("p.principal_type = 'ROLE'"));
|
assertTrue(sql.contains("p.principal_type = 'GROUP'"));
|
assertTrue(sql.contains("p.resource_scope = 'SUBTREE'"));
|
assertTrue(sql.contains("f.f_delete_mark IS NULL"));
|
assertTrue(sql.contains("ORDER BY f.sort_code, f.id LIMIT ?"));
|
assertTrue(sql.indexOf("dms_file_permissions") < sql.indexOf("LIMIT ?"));
|
assertFalse(sql.contains("SELECT *"));
|
assertFalse(sql.contains("AS has_children"));
|
}
|
|
@Test
|
void treeExpansionUsesVisibleChildrenIncludingNavigationAndInheritedRead() {
|
DmsFileCenterQuery query = query(false);
|
query.setIncludeFolderChildren(true);
|
String sql = sql("selectFolders", query);
|
String expansion = sql.substring(sql.indexOf(", EXISTS (SELECT 1 FROM public.dms_folders child_folder"),
|
sql.indexOf(") AS has_children"));
|
assertTrue(expansion.contains("child_folder.tenant_id = ?"));
|
assertTrue(expansion.contains("child_folder.parent_id = f.id"));
|
assertTrue(expansion.contains("child_folder.f_delete_mark IS NULL"));
|
assertTrue(expansion.contains("child_folder.id IN (SELECT id FROM authorized_navigation_ancestors)"));
|
assertTrue(expansion.contains("p.resource_id = child_folder.id"));
|
assertTrue(expansion.contains("p.resource_scope = 'SUBTREE' AND (p.resource_id = f.id"));
|
assertTrue(expansion.contains("p.resource_id IN (SELECT id FROM parent_ancestors)"));
|
assertTrue(expansion.contains("p.action_code = 'READ'"));
|
assertTrue(expansion.contains("p.revoked_at IS NULL"));
|
assertTrue(expansion.contains("p.valid_from <= CURRENT_TIMESTAMP"));
|
assertTrue(expansion.contains("CURRENT_TIMESTAMP < p.valid_to"));
|
assertTrue(expansion.contains("p.used_count < p.max_use_count"));
|
for (String principal : Arrays.asList("USER", "ORG", "POSITION", "ROLE", "GROUP")) {
|
assertTrue(expansion.contains("p.principal_type = '" + principal + "'"));
|
}
|
}
|
|
@Test
|
void administratorTreeExpansionStillFiltersTenantAndDeletedChildren() {
|
DmsFileCenterQuery query = query(true);
|
query.setIncludeFolderChildren(true);
|
String sql = sql("selectFolders", query);
|
assertTrue(sql.contains("child_folder.tenant_id = ?"));
|
assertTrue(sql.contains("child_folder.f_delete_mark IS NULL"));
|
assertTrue(sql.contains(") AS has_children"));
|
assertFalse(sql.contains("dms_file_permissions"));
|
assertFalse(sql.contains("authorized_navigation_ancestors"));
|
}
|
|
@Test
|
void administratorFolderPageDoesNotRequireAllowRows() {
|
String sql = sql("selectFolders", query(true));
|
|
assertFalse(sql.contains("dms_file_permissions"));
|
assertFalse(sql.contains("authorized_navigation_seeds"));
|
assertFalse(sql.contains("authorized_navigation_ancestors"));
|
assertTrue(sql.contains("f.f_delete_mark IS NULL"));
|
assertTrue(sql.contains("ORDER BY f.sort_code, f.id LIMIT ?"));
|
}
|
|
@Test
|
void folderCursorUsesSortCodeAndIdBoundary() {
|
DmsFileCenterQuery query = query(true);
|
query.setCursorSortCode(2048L);
|
query.setCursorId(UUID.fromString("018f0000-0000-7000-8000-000000000603"));
|
|
String sql = sql("selectFolders", query);
|
|
assertTrue(sql.contains("f.sort_code > ?"));
|
assertTrue(sql.contains("f.sort_code = ? AND f.id > ?"));
|
assertFalse(sql.contains("f.name > ?"));
|
}
|
|
@Test
|
void currentDocumentPageUsesEffectiveWindowResourceReadAndExclusionPriority() {
|
String sql = sql("selectCurrentDocuments", query(false));
|
|
assertTrue(sql.contains("d.id, 'DOCUMENT' AS resource_type"));
|
assertTrue(sql.contains("d.main_document_id IS NULL"));
|
assertTrue(sql.contains("member.id=d.id OR member.main_document_id=d.id"));
|
assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
|
assertTrue(sql.contains("s.effective_from <= CURRENT_TIMESTAMP"));
|
assertTrue(sql.contains("CURRENT_TIMESTAMP < s.effective_to"));
|
assertTrue(sql.contains("NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e"));
|
assertTrue(sql.contains("p.resource_type = 'FOLDER'"));
|
assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
|
assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
|
assertTrue(sql.contains("ORDER BY COALESCE((SELECT initial_version.file_name"));
|
}
|
|
@Test
|
void currentFileSearchUsesUnifiedFieldsAcrossFolderSubtree() {
|
DmsFileCenterQuery query = query(false);
|
query.setKeyword("Report");
|
query.setCursorName("Document A");
|
query.setCursorDocumentId(UUID.fromString("018f0000-0000-7000-8000-000000000701"));
|
query.setCursorId(UUID.fromString("018f0000-0000-7000-8000-000000000702"));
|
|
String sql = sql("selectCurrentFiles", query);
|
|
assertTrue(sql.contains("SELECT v.id, 'FILE_VERSION' AS resource_type"));
|
assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
|
assertTrue(sql.contains("s.review_cycle_months"));
|
assertTrue(sql.contains("s.last_reviewed_on"));
|
assertTrue(sql.contains("s.next_review_on"));
|
assertTrue(sql.contains("WITH RECURSIVE folder_descendants"));
|
assertTrue(sql.contains("child.parent_id = parent.id"));
|
assertTrue(sql.contains("d.folder_id IN (SELECT id FROM folder_descendants)"));
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(v.file_name, '')), LOWER(?)) > 0"));
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.name, '')), LOWER(?)) > 0"));
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.document_no, '')), LOWER(?)) > 0"));
|
assertFalse(sql.contains("STARTS_WITH"));
|
assertTrue(sql.contains("d.id = ? AND v.id > ?"));
|
assertTrue(sql.contains("dms_access_exclusions"));
|
assertTrue(sql.contains("dms_file_permissions"));
|
assertTrue(sql.contains("SELECT ancestor_id FROM search_folder_ancestors WHERE folder_id = d.folder_id"));
|
assertTrue(sql.contains("ORDER BY d.name, d.id, v.id LIMIT ?"));
|
assertBalancedParentheses(sql);
|
}
|
|
@Test
|
void fileNameExistenceUsesDirectFolderExactNameAndCurrentVersions() {
|
String sql = sql("fileNameExists", query(false));
|
|
assertTrue(sql.contains("d.folder_id = ?"));
|
assertTrue(sql.contains("v.file_name = ?"));
|
assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
|
assertTrue(sql.contains("d.access_status = 'enabled'"));
|
assertTrue(sql.contains("d.tenant_id = ?"));
|
assertFalse(sql.contains("dms_access_exclusions"));
|
assertFalse(sql.contains("dms_file_permissions"));
|
assertFalse(sql.contains("folder_descendants"));
|
assertFalse(sql.contains("version_label ="));
|
assertBalancedParentheses(sql);
|
}
|
|
@Test
|
void administratorFileSearchKeepsBusinessScopeWithoutPermissionPredicates() {
|
DmsFileCenterQuery query = query(true);
|
query.setKeyword("玻思韬");
|
|
String sql = sql("selectCurrentFiles", query);
|
|
assertTrue(sql.contains("d.tenant_id = ?"));
|
assertTrue(sql.contains("d.folder_id IN (SELECT id FROM folder_descendants)"));
|
assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.name, '')), LOWER(?)) > 0"));
|
assertFalse(sql.contains("dms_file_permissions"));
|
assertFalse(sql.contains("dms_access_exclusions"));
|
assertBalancedParentheses(sql);
|
}
|
|
@Test
|
void historicalPageReturnsOneDocumentContainerForVisibleTerminalVersions() {
|
DmsFileCenterQuery query = query(false);
|
query.setCursorName("A");
|
query.setCursorDocumentId(UUID.fromString("018f0000-0000-7000-8000-000000000701"));
|
|
String sql = sql("selectHistoricalVersions", query);
|
|
assertTrue(sql.contains("d.id, 'DOCUMENT' AS resource_type"));
|
assertTrue(sql.contains("d.main_document_id IS NULL"));
|
assertTrue(sql.contains("member.id=d.id OR member.main_document_id=d.id"));
|
assertTrue(sql.contains("AND EXISTS (SELECT 1 FROM public.dms_documents member"));
|
assertHistoricalVisibility(sql);
|
assertFalse(sql.contains("PENDING_EFFECTIVE"));
|
assertFalse(sql.contains("v.version_no < ?"));
|
assertFalse(sql.contains("v.id < ?"));
|
assertTrue(sql.contains("p.resource_type = 'FOLDER'"));
|
assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
|
assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
|
assertTrue(sql.contains("ORDER BY COALESCE((SELECT initial_version.file_name"));
|
}
|
|
@Test
|
void historicalFileSearchReturnsOnlyMatchingTerminalFileVersions() {
|
DmsFileCenterQuery query = query(false);
|
query.setKeyword("Archive");
|
query.setCursorName("Document A");
|
query.setCursorDocumentId(UUID.fromString("018f0000-0000-7000-8000-000000000701"));
|
query.setCursorVersionNo(2L);
|
query.setCursorId(UUID.fromString("018f0000-0000-7000-8000-000000000702"));
|
|
String sql = sql("selectHistoricalFiles", query);
|
|
assertTrue(sql.contains("SELECT v.id, 'FILE_VERSION' AS resource_type"));
|
assertHistoricalVisibility(sql);
|
assertFalse(sql.contains("PENDING_EFFECTIVE"));
|
assertTrue(sql.contains("d.folder_id IN (SELECT id FROM folder_descendants)"));
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(v.file_name, '')), LOWER(?)) > 0"));
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.name, '')), LOWER(?)) > 0"));
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.document_no, '')), LOWER(?)) > 0"));
|
assertTrue(sql.contains("v.version_no < ?"));
|
assertTrue(sql.contains("dms_access_exclusions"));
|
assertTrue(sql.contains("dms_file_permissions"));
|
assertTrue(sql.contains("ORDER BY d.name, d.id, v.version_no DESC, v.id DESC LIMIT ?"));
|
assertBalancedParentheses(sql);
|
}
|
|
@Test
|
void currentDocumentDirectoryReturnsOnlyTheEffectiveVersion() {
|
String sql = sql("selectCurrentDocumentVersions", query(false));
|
|
assertTrue(sql.contains("'FILE_VERSION' AS resource_type"));
|
assertTrue(sql.contains("d.id = ?"));
|
assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
|
assertTrue(sql.contains("s.effective_from <= CURRENT_TIMESTAMP"));
|
assertTrue(sql.contains("CURRENT_TIMESTAMP < s.effective_to"));
|
assertTrue(sql.contains("p.resource_type = 'FOLDER'"));
|
assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
|
assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
|
assertTrue(sql.contains("ORDER BY v.version_no DESC, v.id DESC LIMIT ?"));
|
}
|
|
@Test
|
void documentVersionSearchUsesTheSameContainsFields() {
|
DmsFileCenterQuery query = query(true);
|
query.setKeyword("玻思韬");
|
|
String sql = sql("selectCurrentDocumentVersions", query);
|
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(v.file_name, '')), LOWER(?)) > 0"));
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.name, '')), LOWER(?)) > 0"));
|
assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.document_no, '')), LOWER(?)) > 0"));
|
assertFalse(sql.contains("LIKE CONCAT"));
|
assertFalse(sql.contains("STARTS_WITH"));
|
}
|
|
@Test
|
void historicalDocumentDirectoryIncludesElapsedVersionsBeforeWorkerRuns() {
|
String sql = sql("selectDocumentVersions", query(false));
|
|
assertTrue(sql.contains("'FILE_VERSION' AS resource_type"));
|
assertHistoricalVisibility(sql);
|
assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE' AND s.effective_to <= CURRENT_TIMESTAMP"));
|
}
|
|
@Test
|
void sharedPageUsesOnlyDirectDocumentOrVersionAllow() {
|
String sql = sql("selectSharedHistoricalVersions", query(false));
|
|
assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
|
assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
|
assertFalse(sql.contains("p.resource_type = 'FOLDER'"));
|
assertTrue(sql.contains("dms_access_exclusions"));
|
}
|
|
private void assertHistoricalVisibility(String sql) {
|
assertTrue(sql.contains("IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'OBSOLETE')"));
|
assertTrue(sql.contains("s.lifecycle_status = 'INVALIDATED' AND s.approved_at IS NOT NULL"));
|
assertFalse(sql.contains("PENDING_APPROVAL"));
|
}
|
|
private DmsFileCenterQuery query(boolean administrator) {
|
DmsFileCenterQuery query = new DmsFileCenterQuery();
|
query.setTenantId("tenant-1");
|
query.setUserId("user-1");
|
query.setAdministrator(administrator);
|
query.setParentId(UUID.fromString("018f0000-0000-7000-8000-000000000601"));
|
query.setFolderId(UUID.fromString("018f0000-0000-7000-8000-000000000601"));
|
query.setDocumentId(UUID.fromString("018f0000-0000-7000-8000-000000000602"));
|
query.setDirectOrgIds(Arrays.asList("org-1"));
|
query.setAncestorOrgIds(Arrays.asList("org-1", "org-root"));
|
query.setPositionIds(Arrays.asList("position-1"));
|
query.setRoleIds(Arrays.asList("role-1"));
|
query.setPermissionGroupIds(Arrays.asList("group-1"));
|
query.setLimit(51);
|
return query;
|
}
|
|
private String sql(String method, DmsFileCenterQuery query) {
|
MappedStatement statement = configuration.getMappedStatement(
|
DmsFileCenterMapper.class.getName() + "." + method);
|
BoundSql boundSql = statement.getBoundSql(java.util.Collections.singletonMap("q", query));
|
return boundSql.getSql().replaceAll("\\s+", " ").trim();
|
}
|
|
private void assertBalancedParentheses(String sql) {
|
long opening = sql.chars().filter(value -> value == '(').count();
|
long closing = sql.chars().filter(value -> value == ')').count();
|
assertEquals(opening, closing, sql);
|
}
|
}
|
