文件夹方案设计

概述

应用与文件夹属于树形结构关系

  • 文件夹可嵌套子文件夹

  • 文件夹下可包含应用或子文件夹

  • 应用可以不归属于任意文件夹

方案对比

路径枚举 (Path Enumeration / LTREE)

在文件夹表中增加一个 path 字段，存储从根节点到当前节点的完整路径（如 1.2.5）。PostgreSQL 官方提供了一个强大的扩展插件 ltree。

表结构： path ltree 索引字段。

查询是否可删： 检查是否存在 path <@ '1.2.4'（表示路径以 1.2.4 开头的文件夹）关联的应用。

优点： 查询极其简单，无需递归，性能极高。

缺点： 移动文件夹（修改父级）时，需要递归更新所有子节点的 path。

闭包表 (Closure Table)

创建一张独立的关联表 folder_relations，记录树中所有节点之间的祖先-后代关系（不仅仅是父子，而是跨级记录）。

表结构： ancestor_id, descendant_id, depth。

查询是否可删： 只需要一条简单的 Join：SELECT 1 FROM apps a JOIN folder_relations r ON a.folder_id = r.descendant_id WHERE r.ancestor_id = 4 LIMIT 1。

优点： 彻底消除递归，在处理极深、极广的树时性能最稳。

缺点： 存储开销大（$N$ 个节点可能产生 $N^2$ 条关系记录），维护逻辑复杂。

嵌套集合 (Nested Sets)

通过两个数字 lft (左值) 和 rgt (右值) 标记节点的范围。子节点的范围一定在父节点的范围内。

表结构： lft INTEGER, rgt INTEGER。

查询是否可删： 查找 folder_id 对应的应用，其文件夹的 lft 必须在目标文件夹的 [lft, rgt] 之间。

优点： 极其擅长查询整个子树。

缺点： 插入或移动节点会导致全表大量数据的 lft/rgt 重新计算，写性能很差。

方案横评

维度 递归 CTE (邻接表) 路径枚举 (LTREE) 闭包表 (Closure) 嵌套集合 (Nested Sets)
查询子树性能 中（依赖树深度） 高 极高 极高
判定可删(短路) 较好 极好 极好 极好
增删改性能 极高（只改父ID） 中（需更新子路径） 低（需维护关联表） 极低（需重算全表）
存储开销 极小 中 高 小
实现复杂度 简单（纯 SQL） 中（需插件支持） 复杂（需触发器/业务逻辑） 极复杂
应用场景 通用型/频繁重命名 目录树/文件系统 深度社交关系/超大规模树 极少变动的分类目录

结论

拟定采用 PostgreSQL 的递归 CTE（WITH RECURSIVE）实现树形结构的查询

文件夹查询

  • 需要返回文件夹是否可删除标志字段给前端，如：can_delete

  • 查询文件夹下所有子孙节点（含应用 / 子文件夹）

  • 查询应用所属的完整路径

查询限制

  • 应用和文件夹是单独的界面（独立的接口，各自翻页）或者混合展示（各自查询 page_size 条，根据更新时间倒排组合应用和文件夹数据，记录应用和文件夹的更新时间，翻页时根据本次查询得到应用和文件夹的更新时间或者 None 进行查询，寻找早于此次应用和文件夹的更新时间的数据）

  • 返回的数据按照更新时间倒序

  • 条数限制：暂定 100

文件夹更新

仅在新增或者删除应用/文件夹时同步更新当前文件夹的更新时间

本期设计不联动更新上级文件夹更新时间

文件夹删除

仅允许删除不含应用的空文件夹，允许含有空文件夹嵌套

应用文件夹方案设计

简易 demo

数据构造

新建数据库 test ，然后建表进行验证

外键的使用需要权衡，考虑高并发场景不建议使用外键

PostgreSQL 外键的默认行为是 RESTRICT，具体表现为：

  • ON DELETE RESTRICT：如果尝试删除被引用的文件夹（folders 表中的记录），但该文件夹仍被子文件夹（parent_id 关联）或应用（apps 表的 folder_id 关联）引用，数据库会直接拒绝删除操作，并抛出外键约束冲突错误。

  • ON UPDATE RESTRICT：如果尝试修改被引用的 folders.id（主键），但该 ID 仍被其他记录引用，同样会被拒绝。

```Plain Text – 1. 文件夹表：自关联结构 CREATE TABLE folders ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, parent_id INTEGER REFERENCES folders(id), created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP );

– 2. 应用表：关联文件夹 CREATE TABLE apps ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, folder_id INTEGER REFERENCES folders(id), created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP );

– 为常用查询路径建立索引 CREATE INDEX idx_folders_parent ON folders(parent_id); CREATE INDEX idx_apps_folder ON apps(folder_id);




```Plain Text
-- 插入文件夹（5级嵌套）
INSERT INTO folders (id, name, parent_id) VALUES
(1, '研发部', NULL),         -- Level 1
(2, '中间件组', 1),          -- Level 2
(3, '数据库组', 2),          -- Level 3
(4, 'PostgreSQL小组', 3),    -- Level 4
(5, '插件开发支队', 4),      -- Level 5 (包含应用：不可删)
(6, '备份脚本支队', 4),      -- Level 5 (空文件夹：可删)
(7, '废弃归档区', NULL),     -- Level 1 (空文件夹：可删)
(8, '嵌套空目录A', 7),       -- Level 2
(9, '嵌套空目录B', 8);       -- Level 3

-- 插入应用
INSERT INTO apps (name, folder_id) VALUES
('看板应用', NULL),          -- 不属于任何文件夹
('监控系统', 1),             -- 研发部下的应用
('Pg_Stat_Tool', 5),         -- 5层深处的应用
('Test_Runner', 5),          -- 5层深处的应用（用于测试同级重名约束）
('video', 4),
('music', 4);

查询语句

查询当前文件夹下包含的应用和文件夹

查询树形应用和文件夹

递归查询指定文件夹下的所有子文件夹

并关联查出这些文件夹下的所有应用

```Plain Text WITH RECURSIVE sub_tree AS ( – 初始节点：指定查询的文件夹 SELECT id, name, parent_id, 1 as level FROM folders WHERE id = 1 – 假设查询【研发部】

UNION ALL

-- 递归下钻
SELECT f.id, f.name, f.parent_id, st.level + 1
FROM folders f
INNER JOIN sub_tree st ON f.parent_id = st.id ) -- 汇总结果：包含子文件夹信息和它们关联的应用 SELECT 'folder' as type, id, name, parent_id, level FROM sub_tree UNION ALL SELECT 'app' as type, a.id, a.name, a.folder_id, st.level FROM apps a JOIN sub_tree st ON a.folder_id = st.id; ```

判断文件夹是否可删

查询指定文件夹 id 是否存在应用，返回文件夹是否可删除标志

知识库同理

短路判定：文件夹是否可删除

只要递归搜索到该文件夹或其任意深度的子文件夹中存在 1条应用记录

立即停止递归返回

Plain Text SELECT NOT EXISTS ( -- 外层 EXISTS 保证了只要内层返回一行，整个查询立即停止 SELECT 1 FROM ( WITH RECURSIVE folder_tree AS ( -- 只查询 ID，内存占用极小 SELECT id FROM folders WHERE id = 4 UNION ALL SELECT f.id FROM folders f JOIN folder_tree ft ON f.parent_id = ft.id ) SELECT 1 FROM folder_tree ft -- 核心：将应用检查放在这里 -- 数据库会针对每一个查到的 ft.id 去 apps 表索引里探测 WHERE EXISTS (SELECT 1 FROM apps a WHERE a.folder_id = ft.id) -- 一旦找到一个有应用的文件夹，立即返回 1 给外层 EXISTS LIMIT 1 ) search_result ) AS is_deletable;

参考文档

  • https://www.postgresql.org/docs/15/queries-with.html