-- drop table file, folder;
CREATE TABLE "folder" (
"id" INT PRIMARY key GENERATED ALWAYS AS IDENTITY,
"name" VARCHAR NOT NULL,
"parent_id" INTEGER REFERENCES "folder"("id")--,
--constraint folder_name UNIQUE NULLS NOT DISTINCT (name, parent_id)
);
CREATE TABLE "file" (
"id" INT PRIMARY key GENERATED ALWAYS AS IDENTITY,
"name" VARCHAR NOT NULL,
"parent_id" INTEGER REFERENCES "folder"("id"),
"contents" TEXT--,
--constraint folder_name UNIQUE NULLS NOT DISTINCT (name, parent_id)
);
insert into folder (name, parent_id) values
('dir1', null),
('dir2', null),
('dir3', null),
('dir4', null);
insert into folder (name, parent_id)
select t.name, folder.id
from (values
('subdir1'),
('subdir2'),
('subdir3'),
('subdir4')
) AS t (name)
inner join folder on true;
insert into folder (name, parent_id)
select t.name, folder.parent_id
from (values
('subsubdir1'),
('subsubdir2'),
('subsubdir3'),
('subsubdir4')
) AS t (name)
inner join folder on folder.parent_id is not null;
insert into file(name, parent_id, contents)
select t.name, folder.id, ''
from (values
('file1.txt'),
('t.test')
) AS t (name)
inner join folder on true;
insert into file(name, parent_id, contents)
values ('root.txt', null, '');
update file
set name = id || '-' || name;
WITH RECURSIVE tree(id, parent_id, name) AS (
select id, parent_id, array[name] from folder
UNION ALL
SELECT tree.id, folder.parent_id, folder.name || tree.name
from tree
inner join folder on folder.id = tree.parent_id
)
select
file.id,
coalesce(array_to_string(tree.name || file.name, '/'), file.name) as path
from file
left join tree on tree.parent_id is null and tree.id = file.parent_id
order by 2