[sqlite] With recursive question

[Jean-Christophe Deschamps]

At 04:05 01/03/2015, you wrote:

>On 2/28/2015 7:22 PM, Jean-Christophe Deschamps wrote:
>>Once again thank you very much Igor. I was making my life miserable
>>trying to scan the "tree" from the other end.
>
>That, too, could be arranged. Something along these lines:
>
>with recursive FileDirs as (
>   select FileId, FileDirID ancestor, '' as path from Files
>union all
>   select FileId, ParentID, '/' || DirName || path
>   from FileDirs join Dirs on (ancestor = DirId)
>   where DirID != 0
>)
>select FileId, FileName, path
>from FileDirs join Files using (FileId)
>where ancestor = 0;

Thanks again Igor for turning on the light!

--
jcd at antichoc.net  

[sqlite] With recursive question

[Jean-Christophe Deschamps]

Once again thank you very much Igor. I was making my life miserable 
trying to scan the "tree" from the other end.

At 00:37 01/03/2015, you wrote:

>It's a bit unfortunate that you made the root a parent of itself. 
>Forces the query to make an extra check to avoid infinite recursion.
`---

Yeah I know it was bad, it was only a quick & dirty sample. My use case 
is pretty more complex but your advice works like a charm, which isn't 
surprising.

Without abusing your time and patience, do you see a simple solution 
more fitted to a case where there are zillions "directories" at varying 
depths but only very few "files", that is without listing all the 
possible paths first and finally matching them the the files' directories?

[sqlite] With recursive question (addendum)

[Jean-Christophe Deschamps]

Forgot to mention that I sees the issue as fundamentally distinct from 
the Mandelbrot example in the SQLite docs: it uses a construct 
-outlined in procedural statements- like:

For x in xRange
   For y in yRange
 compute something like f(x, y) -- here, y is independant of x
   Next y
Next x

Put in table form:
build a table for varying x's
build a table for varying y's
compute f(x, y) for the cross join

My issue is that yRange depends on the current x for its starting 
point, so in SQL WITH parlance, I can't have a table x built before 
entering the computation.

It must be simple but I lamentably fail to see the light.

[sqlite] With recursive question

[Jean-Christophe Deschamps]

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.

[sqlite] With recursive question

[Igor Tandetnik]

On 2/28/2015 7:22 PM, Jean-Christophe Deschamps wrote:
> Once again thank you very much Igor. I was making my life miserable
> trying to scan the "tree" from the other end.

That, too, could be arranged. Something along these lines:

with recursive FileDirs as (
   select FileId, FileDirID ancestor, '' as path from Files
union all
   select FileId, ParentID, '/' || DirName || path
   from FileDirs join Dirs on (ancestor = DirId)
   where DirID != 0
)
select FileId, FileName, path
from FileDirs join Files using (FileId)
where ancestor = 0;

-- 
Igor Tandetnik

[sqlite] With recursive question

[Igor Tandetnik]

On 2/28/2015 6:02 PM, Jean-Christophe Deschamps wrote:
> 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, '/')

with recursive DirTree as (
   select DirID, '' as path from Dirs where DirName='root'

   union all

   select d.DirID, t.path || '/' || d.DirName
   from Dirs d join DirTree t on (d.ParentID=t.DirID)
   where d.DirName != 'root'
)
select FileId, FileName, path
from Files join DirTree on (FileDirID=DirID);

It's a bit unfortunate that you made the root a parent of itself. Forces 
the query to make an extra check to avoid infinite recursion.
-- 
Igor Tandetnik