文件系统中目录路径的层次/树数据库

编程入门行业动态更新时间:2024-10-23 12:36:37

本文介绍了文件系统中目录路径的层次/树数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我想将目录(存在于磁盘上)存储到数据库中，并保持其层次结构/树结构.

I want to store the directories (present on the disk) into a database, maintaining their hierarchical/tree structure.

这是一个数字:

(ROOT) / \ Dir2 Dir3 / \ \ Dir4 Dir5 Dir6 / Dir7

我正在使用 SQLite 数据库.

请建议我

用于将上述结构存储在SQLite数据库中的SQL查询，以及

the SQL query to store above structure in SQLite database, and

a query to retrieve full path of the directory when I select one.

即假设我选择Dir7，那么我应该得到像ROOT/Dir2/Dir4/Dir7

i.e. suppose I select Dir7 then I should get the full path like ROOT/Dir2/Dir4/Dir7

推荐答案

以下是SQLite的快速关闭表示例.我没有包含将项目插入到现有树中的语句.相反，我只是手动创建了语句.您可以在分层数据模型幻灯片中找到插入和删除语句

Here's a quick closure table example for SQLite. I've not included the statements for inserting items into an existing tree. Instead, I've just created the statements manually. You can find the insert and delete statements in the Models for hierarchical data slides.

出于我的理智，在为目录插入ID时，我重命名了目录以匹配其ID:

For the sake of my sanity when inserting the IDs for the directories, I renamed the directories to match their IDs:

(ROOT) / \ Dir2 Dir3 / \ \ Dir4 Dir5 Dir6 / Dir7

创建表格

CREATE TABLE `filesystem` ( `id` INTEGER, `dirname` TEXT, PRIMARY KEY (`id`) ); CREATE TABLE `tree_path` ( `ancestor` INTEGER, `descendant` INTEGER, PRIMARY KEY (`ancestor`, `descendant`) );

将目录插入filesystem表

Insert directories into filesystem table

INSERT INTO filesystem (id, dirname) VALUES (1, 'ROOT'); INSERT INTO filesystem (id, dirname) VALUES (2, 'Dir2'); INSERT INTO filesystem (id, dirname) VALUES (3, 'Dir3'); INSERT INTO filesystem (id, dirname) VALUES (4, 'Dir4'); INSERT INTO filesystem (id, dirname) VALUES (5, 'Dir5'); INSERT INTO filesystem (id, dirname) VALUES (6, 'Dir6'); INSERT INTO filesystem (id, dirname) VALUES (7, 'Dir7');

创建闭合表路径

INSERT INTO tree_path (ancestor, descendant) VALUES (1, 1); INSERT INTO tree_path (ancestor, descendant) VALUES (1, 2); INSERT INTO tree_path (ancestor, descendant) VALUES (1, 3); INSERT INTO tree_path (ancestor, descendant) VALUES (1, 4); INSERT INTO tree_path (ancestor, descendant) VALUES (1, 5); INSERT INTO tree_path (ancestor, descendant) VALUES (1, 6); INSERT INTO tree_path (ancestor, descendant) VALUES (1, 7); INSERT INTO tree_path (ancestor, descendant) VALUES (2, 2); INSERT INTO tree_path (ancestor, descendant) VALUES (2, 4); INSERT INTO tree_path (ancestor, descendant) VALUES (2, 5); INSERT INTO tree_path (ancestor, descendant) VALUES (2, 7); INSERT INTO tree_path (ancestor, descendant) VALUES (3, 3); INSERT INTO tree_path (ancestor, descendant) VALUES (3, 6); INSERT INTO tree_path (ancestor, descendant) VALUES (4, 4); INSERT INTO tree_path (ancestor, descendant) VALUES (4, 7); INSERT INTO tree_path (ancestor, descendant) VALUES (5, 5); INSERT INTO tree_path (ancestor, descendant) VALUES (6, 6); INSERT INTO tree_path (ancestor, descendant) VALUES (7, 7);

运行一些查询

# (ROOT) and subdirectories SELECT f.id, f.dirname FROM filesystem f JOIN tree_path t ON t.descendant = f.id WHERE t.ancestor = 1; +----+---------+ | id | dirname | +----+---------+ | 1 | ROOT | | 2 | Dir2 | | 3 | Dir3 | | 4 | Dir4 | | 5 | Dir5 | | 6 | Dir6 | | 7 | Dir7 | +----+---------+ # Dir3 and subdirectories SELECT f.id, f.dirname FROM filesystem f JOIN tree_path t ON t.descendant = f.id WHERE t.ancestor = 3; +----+---------+ | id | dirname | +----+---------+ | 3 | Dir3 | | 6 | Dir6 | +----+---------+ # Dir5 and parent directories SELECT f.id, f.dirname FROM filesystem f JOIN tree_path t ON t.ancestor = f.id WHERE t.descendant = 5; +----+---------+ | id | dirname | +----+---------+ | 1 | ROOT | | 2 | Dir2 | | 5 | Dir5 | +----+---------+ # Dir7 and parent directories SELECT f.id, f.dirname FROM filesystem f JOIN tree_path t ON t.ancestor = f.id WHERE t.descendant = 7; +----+---------+ | id | dirname | +----+---------+ | 1 | ROOT | | 2 | Dir2 | | 4 | Dir4 | | 7 | Dir7 | +----+---------+ SELECT f.id, f.dirname FROM filesystem f JOIN tree_path t ON t.ancestor = f.id WHERE t.descendant = ( SELECT id FROM filesystem WHERE dirname LIKE '%7%' ); +----+---------+ | id | dirname | +----+---------+ | 1 | ROOT | | 2 | Dir2 | | 4 | Dir4 | | 7 | Dir7 | +----+---------+

更多推荐

文件系统中目录路径的层次/树数据库

本文发布于:2023-05-28 06:54:11，感谢您对本站的认可！

本文链接:https://www.elefans.com/category/jswz/34/315087.html