Dear list,
After trying a number of ways I'm at loss solving the seemingly simple
problem.
For a simplified example say I have a list of individual filesystem
directories with FK pointing to their parent:
PRAGMA foreign_keys=ON;
CREATE TABLE "Dirs" (
"DirID" INTEGER NOT NULL PRIMARY KEY,
"DirName" CHAR,
"ParentID" INTEGER NOT NULL CONSTRAINT "fkDirs" REFERENCES
"Dirs"("DirID")
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "Dirs" VALUES(0,'root',0);
INSERT INTO "Dirs" VALUES(1,'A',0);
INSERT INTO "Dirs" VALUES(2,'B',0);
INSERT INTO "Dirs" VALUES(3,'C',0);
INSERT INTO "Dirs" VALUES(4,'A1',1);
INSERT INTO "Dirs" VALUES(5,'A2',1);
INSERT INTO "Dirs" VALUES(6,'A3',1);
INSERT INTO "Dirs" VALUES(7,'B1',2);
INSERT INTO "Dirs" VALUES(8,'B2',2);
INSERT INTO "Dirs" VALUES(9,'C1',3);
INSERT INTO "Dirs" VALUES(10,'X1',3);
INSERT INTO "Dirs" VALUES(11,'Y1',10);
INSERT INTO "Dirs" VALUES(12,'Z1',11);
The depth of directories is (essentially) unbounded, just like is
actual filesystems.
I also have a list of files with FK pointing to their hosting directory:
CREATE TABLE "Files" (
"FileID" INTEGER NOT NULL PRIMARY KEY,
"FileName" CHAR NOT NULL,
"FileDirID" INTEGER NOT NULL CONSTRAINT "fkFileDir" REFERENCES
"Dirs"("DirID")
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "Files" VALUES(1,'aaa1',1);
INSERT INTO "Files" VALUES(2,'aaa2',1);
INSERT INTO "Files" VALUES(3,'bbb1',2);
INSERT INTO "Files" VALUES(4,'bbb2',2);
INSERT INTO "Files" VALUES(5,'bbb3',2);
INSERT INTO "Files" VALUES(6,'ccc1',3);
INSERT INTO "Files" VALUES(7,'zzz1',12);
Until now everything looks pretty simple, right.
What I want to obtain is the list of all files (in random order but
that's not the point) containing:
FileID
FileName
Directory path from root using some kind of group_concat(dir, '/')
other columns from table Files not mentionned in the example above.
I've a simple WITH RECURSIVE view able to get the wanted data, but only
for a given FileID (literally fixed in both recursive clause and final
select).
My myopia is how can I write a recursive clause (to group directories
from bottom up) which refers to a varying starting repository directory.
I guess there must be a clever join needed but how and where?
Note that I don't want a complete list of hierarchical directories and
files starting from root: just actual files with their path in natural
order.
TIA for your advices.
