Thanks, will try that.
> order by PATH
So, where is this path coming from?
RBS
On Thu, Jan 31, 2019 at 4:08 PM Jean-Luc Hainaut <jean-luc.hain...@unamur.be>
wrote:
> Recursive CTEs are the most obvious technique to solve this kind of
> problems.
> However, a less known technique can do the job: recursive triggers.
> Here is how the closure of FOLDERS can be computed. It will be stored in
> table CLOSURE:
>
> create table CLOSURE(PARENT_ID integer, ID integer, DIST integer);
>
> A trigger adds the children rows of each row that has been inserted into
> this table:
>
> create trigger CLOSURE_INS after insert on CLOSURE
> for each row
> begin
> insert into CLOSURE
> select new.PARENT_ID,ID,new.DIST+1 from FOLDERS
> where PARENT_ID = new.ID;
> end;
>
> To compute the closure, we just insert the root node:
>
> insert into CLOSURE select ID,ID,0 from FOLDERS where ID = 1;
>
> or all the nodes:
>
> insert into CLOSURE select ID,ID,0 from FOLDERS;
>
> To get the strict closure, we discard the initial rows (DIST = 0). And
> to display the node hierarchy:
>
> select *,substr(' ',1,2*DIST)||cast(ID as char) as Display
> from CLOSURE order by PATH;
>
> +-----------+----+------+---------+
> | PARENT_ID | ID | DIST | Display |
> +-----------+----+------+---------+
> | 1 | 1 | 0 | 1 |
> | 1 | 2 | 1 | 2 |
> | 1 | 5 | 2 | 5 |
> | 1 | 6 | 2 | 6 |
> | 1 | 3 | 1 | 3 |
> | 1 | 4 | 1 | 4 |
> +-----------+----+------+---------+
>
> The path of each folder is computed in the same way.
>
> J-L Hainaut
>
> > Working on an Android app and part of that is storing SQL in a virtual
> > folder system in SQLite. For this I want to use a so-called closure table
> > as explained nicely here:
> >
> > http://technobytz.com/closure_table_store_hierarchical_data.html
> >
> > I have a table holder the folder details:
> >
> > ID PARENT_ID Folder
> >
> > -------------------------------------
> > 1 0 Folder1
> > 2 1 Folder2
> > 3 1 Folder3
> > 4 1 Folder4
> > 5 2 Folder5
> > 6 2 Folder6
> >
> >
> > And then the closure table:
> >
> > PARENT_ID CHILD_ID DEPTH
> >
> > -----------------------------------------------
> > 1 1 0
> > 2 2 0
> > 3 3 0
> > 4 4 0
> > 5 5 0
> > 6 6 0
> > 1 2 1
> > 1 3 1
> > 1 4 1
> > 2 5 1
> > 1 5 2
> > 2 6 1
> > 1 6 2
> >
> > What should the SQL be to display the folders like this:
> >
> > Folder PARENT_ID
> > Folder1 0
> > Folder2 1
> > Folder5 2
> > Folder6 2
> > Folder3 1
> > Folder4 1
> >
> >
> > RBS
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
