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
