On 31/01/2019 17:59, Bart Smissaert wrote:
Thanks, will try that.
order by PATH
So, where is this path coming from?
Simple, from a discrepancy between the script I have tested and the
contents of this mail!
Here is the complete (tested) script:
create table CLOSURE(PARENT_ID integer,ID integer,PATH text,DIST integer);
create trigger CLOSURE_INS after insert on CLOSURE
for each row
begin
insert into CLOSURE
select new.PARENT_ID,ID,new.PATH||'/'||cast(ID as char),new.DIST+1
from FOLDERS
where PARENT_ID = new.ID; end;
insert into CLOSURE select ID,ID,'1',0 from FOLDERS where ID = 1;
select *,substr(' ',1,2*DIST)||cast(ID as char) as Display from
CLOSURE order by PATH;
+-----------+----+-------+------+---------+
| PARENT_ID | ID | PATH | DIST | Display |
+-----------+----+-------+------+---------+
| 1 | 1 | 1 | 0 | 1 |
| 1 | 2 | 1/2 | 1 | 2 |
| 1 | 5 | 1/2/5 | 2 | 5 |
| 1 | 6 | 1/2/6 | 2 | 6 |
| 1 | 3 | 1/3 | 1 | 3 |
| 1 | 4 | 1/4 | 1 | 4 |
+-----------+----+-------+------+---------+
JL
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
