package jnpf.dmsMapper;
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import jnpf.dmsEntity.permission.entity.DmsFolderEntity;
|
import jnpf.dmsEntity.permission.model.DmsFolderAncestorRow;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
import org.apache.ibatis.annotations.Update;
|
|
import java.util.List;
|
import java.util.UUID;
|
|
@Mapper
|
public interface DmsFolderMapper extends BaseMapper<DmsFolderEntity> {
|
String COLUMNS = "id, tenant_id, parent_id, name, created_by, created_at, sort_code, revision, "
|
+ "updated_by, updated_at, f_delete_mark, deleted_by, deleted_at";
|
|
@Select("SELECT " + COLUMNS + " FROM public.dms_folders "
|
+ "WHERE tenant_id = #{tenantId} AND id = #{id} AND f_delete_mark IS NULL")
|
DmsFolderEntity selectByTenantAndId(@Param("tenantId") String tenantId, @Param("id") UUID id);
|
|
@Select("SELECT " + COLUMNS + " FROM public.dms_folders "
|
+ "WHERE tenant_id = #{tenantId} AND id = #{id} AND f_delete_mark IS NULL FOR UPDATE")
|
DmsFolderEntity selectByTenantAndIdForUpdate(@Param("tenantId") String tenantId,
|
@Param("id") UUID id);
|
|
@Select({"<script>",
|
"SELECT " + COLUMNS,
|
"FROM public.dms_folders WHERE tenant_id = #{tenantId} AND f_delete_mark IS NULL AND id IN",
|
"<foreach collection='ids' item='id' open='(' separator=',' close=')'>#{id}</foreach>",
|
"</script>"})
|
List<DmsFolderEntity> selectByTenantAndIds(@Param("tenantId") String tenantId,
|
@Param("ids") List<UUID> ids);
|
|
@Select("WITH RECURSIVE folder_path(id, parent_id, name, depth) AS ("
|
+ "SELECT id, parent_id, name, 0 FROM public.dms_folders WHERE tenant_id = #{tenantId} "
|
+ "AND id = #{id} AND f_delete_mark IS NULL UNION ALL "
|
+ "SELECT parent.id, parent.parent_id, parent.name, child.depth + 1 FROM folder_path child "
|
+ "JOIN public.dms_folders parent ON parent.tenant_id = #{tenantId} "
|
+ "AND parent.id = child.parent_id AND parent.f_delete_mark IS NULL) "
|
+ "SELECT '/' || string_agg(name, '/' ORDER BY depth DESC) FROM folder_path")
|
String selectPath(@Param("tenantId") String tenantId, @Param("id") UUID id);
|
|
@Select({"<script>",
|
"WITH RECURSIVE ancestors(start_folder_id, ancestor_folder_id, parent_id, depth) AS (",
|
"SELECT f.id, f.id, f.parent_id, 0 FROM public.dms_folders f",
|
"WHERE f.tenant_id = #{tenantId} AND f.f_delete_mark IS NULL AND f.id IN",
|
"<foreach collection='folderIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>",
|
"UNION ALL",
|
"SELECT a.start_folder_id, p.id, p.parent_id, a.depth + 1",
|
"FROM ancestors a JOIN public.dms_folders p",
|
"ON p.tenant_id = #{tenantId} AND p.id = a.parent_id AND p.f_delete_mark IS NULL",
|
")",
|
"SELECT start_folder_id, ancestor_folder_id, depth FROM ancestors",
|
"ORDER BY start_folder_id, depth",
|
"</script>"})
|
List<DmsFolderAncestorRow> selectAncestorRows(@Param("tenantId") String tenantId,
|
@Param("folderIds") List<UUID> folderIds);
|
|
@Select("SELECT 1 FROM (SELECT pg_advisory_xact_lock(hashtextextended(#{tenantId} "
|
+ "|| ':folder-siblings:' || COALESCE(CAST(#{parentId} AS TEXT), 'root'), 0))) sibling_lock")
|
Integer lockSiblingSet(@Param("tenantId") String tenantId, @Param("parentId") UUID parentId);
|
|
@Select("SELECT COALESCE(MAX(sort_code), 0) + 1024 FROM public.dms_folders "
|
+ "WHERE tenant_id = #{tenantId} AND parent_id IS NOT DISTINCT FROM #{parentId} "
|
+ "AND f_delete_mark IS NULL")
|
long nextSortCode(@Param("tenantId") String tenantId, @Param("parentId") UUID parentId);
|
|
@Select({"<script>",
|
"SELECT EXISTS (SELECT 1 FROM public.dms_folders",
|
"WHERE tenant_id = #{tenantId} AND parent_id IS NOT DISTINCT FROM #{parentId}",
|
"AND f_delete_mark IS NULL AND lower(name) = lower(#{name})",
|
"<if test='excludeId != null'>AND id != #{excludeId}</if>)",
|
"</script>"})
|
boolean siblingNameExists(@Param("tenantId") String tenantId,
|
@Param("parentId") UUID parentId,
|
@Param("name") String name,
|
@Param("excludeId") UUID excludeId);
|
|
@Select("SELECT " + COLUMNS + " FROM public.dms_folders "
|
+ "WHERE tenant_id = #{tenantId} AND parent_id IS NOT DISTINCT FROM #{parentId} "
|
+ "AND f_delete_mark IS NULL AND (sort_code, id) < (#{sortCode}, #{id}) "
|
+ "ORDER BY sort_code DESC, id DESC LIMIT 1 FOR UPDATE")
|
DmsFolderEntity selectPreviousForUpdate(@Param("tenantId") String tenantId,
|
@Param("parentId") UUID parentId,
|
@Param("sortCode") long sortCode,
|
@Param("id") UUID id);
|
|
@Select("SELECT " + COLUMNS + " FROM public.dms_folders "
|
+ "WHERE tenant_id = #{tenantId} AND parent_id IS NOT DISTINCT FROM #{parentId} "
|
+ "AND f_delete_mark IS NULL AND (sort_code, id) > (#{sortCode}, #{id}) "
|
+ "ORDER BY sort_code, id LIMIT 1 FOR UPDATE")
|
DmsFolderEntity selectNextForUpdate(@Param("tenantId") String tenantId,
|
@Param("parentId") UUID parentId,
|
@Param("sortCode") long sortCode,
|
@Param("id") UUID id);
|
|
@Update("UPDATE public.dms_folders SET name = #{name}, revision = revision + 1, "
|
+ "updated_by = #{actorId}, updated_at = CURRENT_TIMESTAMP "
|
+ "WHERE tenant_id = #{tenantId} AND id = #{id} AND revision = #{revision} AND f_delete_mark IS NULL")
|
int rename(@Param("tenantId") String tenantId, @Param("id") UUID id,
|
@Param("name") String name, @Param("revision") long revision,
|
@Param("actorId") String actorId);
|
|
@Update("UPDATE public.dms_folders SET sort_code = #{sortCode}, revision = revision + 1, "
|
+ "updated_by = #{actorId}, updated_at = CURRENT_TIMESTAMP "
|
+ "WHERE tenant_id = #{tenantId} AND id = #{id} AND revision = #{revision} AND f_delete_mark IS NULL")
|
int reorder(@Param("tenantId") String tenantId, @Param("id") UUID id,
|
@Param("sortCode") long sortCode, @Param("revision") long revision,
|
@Param("actorId") String actorId);
|
|
@Update("UPDATE public.dms_folders SET parent_id = #{parentId}, sort_code = #{sortCode}, "
|
+ "revision = revision + 1, updated_by = #{actorId}, updated_at = CURRENT_TIMESTAMP "
|
+ "WHERE tenant_id = #{tenantId} AND id = #{id} AND revision = #{revision} AND f_delete_mark IS NULL")
|
int move(@Param("tenantId") String tenantId, @Param("id") UUID id,
|
@Param("parentId") UUID parentId, @Param("sortCode") long sortCode,
|
@Param("revision") long revision, @Param("actorId") String actorId);
|
|
@Update("UPDATE public.dms_folders SET f_delete_mark = 1, deleted_by = #{actorId}, "
|
+ "deleted_at = CURRENT_TIMESTAMP, revision = revision + 1, "
|
+ "updated_by = #{actorId}, updated_at = CURRENT_TIMESTAMP "
|
+ "WHERE tenant_id = #{tenantId} AND id = #{id} AND revision = #{revision} AND f_delete_mark IS NULL")
|
int softDelete(@Param("tenantId") String tenantId, @Param("id") UUID id,
|
@Param("revision") long revision, @Param("actorId") String actorId);
|
|
@Select("SELECT EXISTS (SELECT 1 FROM public.dms_folders child "
|
+ "WHERE child.tenant_id = #{tenantId} AND child.parent_id = #{id} AND child.f_delete_mark IS NULL) "
|
+ "OR EXISTS (SELECT 1 FROM public.dms_documents document "
|
+ "WHERE document.tenant_id = #{tenantId} AND document.folder_id = #{id})")
|
boolean hasContents(@Param("tenantId") String tenantId, @Param("id") UUID id);
|
|
@Select("WITH RECURSIVE ancestors(id, parent_id) AS ("
|
+ "SELECT id, parent_id FROM public.dms_folders WHERE tenant_id = #{tenantId} "
|
+ "AND id = #{targetId} AND f_delete_mark IS NULL UNION ALL "
|
+ "SELECT parent.id, parent.parent_id FROM ancestors child "
|
+ "JOIN public.dms_folders parent ON parent.tenant_id = #{tenantId} "
|
+ "AND parent.id = child.parent_id AND parent.f_delete_mark IS NULL) "
|
+ "SELECT EXISTS (SELECT 1 FROM ancestors WHERE id = #{sourceId})")
|
boolean targetIsSelfOrDescendant(@Param("tenantId") String tenantId,
|
@Param("sourceId") UUID sourceId,
|
@Param("targetId") UUID targetId);
|
|
@Select("WITH RECURSIVE subtree AS ("
|
+ "SELECT f.*, 0 AS depth FROM public.dms_folders f WHERE f.tenant_id = #{tenantId} "
|
+ "AND f.id = #{id} AND f.f_delete_mark IS NULL UNION ALL "
|
+ "SELECT child.*, parent.depth + 1 FROM subtree parent "
|
+ "JOIN public.dms_folders child ON child.tenant_id = #{tenantId} "
|
+ "AND child.parent_id = parent.id AND child.f_delete_mark IS NULL) "
|
+ "SELECT " + COLUMNS + " FROM subtree ORDER BY depth, sort_code, id")
|
List<DmsFolderEntity> selectSubtree(@Param("tenantId") String tenantId, @Param("id") UUID id);
|
}
|
