Using the transitive_closure virtual table extension (closure.c) on your
original question (my sqlite3 has everything built-in already, so no need to
load the extension):
Note though that the AVL tree generated by the closure extension is generated
on the fly upon request and does not have a materialized backing store.
create table folders
(
id integer primary key,
parent_id integer references folders,
name text not null collate nocase,
check (not (parent_id is null and id != 1))
);
create index foldersparentid on folders (parent_id);
create virtual table Closure using transitive_closure;
create view folders_closure
as select folders.id as PARENT_ID,
Closure.id as CHILD_ID,
Closure.depth as DEPTH
from folders, Closure
where Closure.root == folders.id
and Closure.tablename = 'folders'
and Closure.idcolumn = 'id'
and Closure.parentcolumn = 'parent_id';
insert into folders values (1, null, 'Folder1'),
(2, 1, 'Folder2'),
(3, 1, 'Folder3'),
(4, 1, 'Folder4'),
(5, 2, 'Folder5'),
(6, 2, 'Folder6');
.head on
.mode column
.width 30 9 38
-- depth first
with foo (id, parent_id, name, level, path)
as (select folders.*, 0, folders.name
from folders
where parent_id is null
union all
select folders.*, level + 1, foo.path || '\' || folders.name
from foo, folders
where folders.parent_id = foo.id
order by 4
)
select substr(' ', 1, (level - 1) * 4) || name as Folder,
coalesce(parent_id, 0) as PARENT_ID,
path as FullPath
from foo;
-- breadth first
with foo (id, parent_id, name, level, path)
as (select folders.*, 0, folders.name
from folders
where parent_id is null
union all
select folders.*, level + 1, foo.path || '\' || folders.name
from foo, folders
where folders.parent_id = foo.id
order by 4 desc
)
select substr(' ', 1, (level - 1) * 4) || name as Folder,
coalesce(parent_id, 0) as PARENT_ID,
path as FullPath
from foo;
-- folders_closure
.width 9 9 9
select *
from folders_closure;
SQLite version 3.27.0 2019-01-31 02:42:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table folders
...> (
...> id integer primary key,
...> parent_id integer references folders,
...> name text not null collate nocase,
...> check (not (parent_id is null and id != 1))
...> );
sqlite> create index foldersparentid on folders (parent_id);
sqlite>
sqlite> create virtual table Closure using transitive_closure;
sqlite>
sqlite> create view folders_closure
...> as select folders.id as PARENT_ID,
...> Closure.id as CHILD_ID,
...> Closure.depth as DEPTH
...> from folders, Closure
...> where Closure.root == folders.id
...> and Closure.tablename = 'folders'
...> and Closure.idcolumn = 'id'
...> and Closure.parentcolumn = 'parent_id';
sqlite>
sqlite> insert into folders values (1, null, 'Folder1'),
...> (2, 1, 'Folder2'),
...> (3, 1, 'Folder3'),
...> (4, 1, 'Folder4'),
...> (5, 2, 'Folder5'),
...> (6, 2, 'Folder6');
sqlite> .head on
sqlite> .mode column
sqlite> .width 30 9 38
sqlite>
sqlite> -- depth first
sqlite>
sqlite> with foo (id, parent_id, name, level, path)
...> as (select folders.*, 0, folders.name
...> from folders
...> where parent_id is null
...> union all
...> select folders.*, level + 1, foo.path || '\' || folders.name
...> from foo, folders
...> where folders.parent_id = foo.id
...> order by 4
...> )
...> select substr(' ', 1, (level - 1) * 4) || name as
Folder,
...> coalesce(parent_id, 0) as PARENT_ID,
...> path as FullPath
...> from foo;
Folder PARENT_ID FullPath
------------------------------ ---------
--------------------------------------
Folder1 0 Folder1
Folder2 1 Folder1\Folder2
Folder3 1 Folder1\Folder3
Folder4 1 Folder1\Folder4
Folder5 2 Folder1\Folder2\Folder5
Folder6 2 Folder1\Folder2\Folder6
sqlite>
sqlite>
sqlite> -- breadth first
sqlite>
sqlite> with foo (id, parent_id, name, level, path)
...> as (select folders.*, 0, folders.name
...> from folders
...> where parent_id is null
...> union all
...> select folders.*, level + 1, foo.path || '\' || folders.name
...> from foo, folders
...> where folders.parent_id = foo.id
...> order by 4 desc
...> )
...> select substr(' ', 1, (level - 1) * 4) || name as
Folder,
...> coalesce(parent_id, 0) as PARENT_ID,
...> path as FullPath
...> from foo;
Folder PARENT_ID FullPath
------------------------------ ---------
--------------------------------------
Folder1 0 Folder1
Folder2 1 Folder1\Folder2
Folder5 2 Folder1\Folder2\Folder5
Folder6 2 Folder1\Folder2\Folder6
Folder3 1 Folder1\Folder3
Folder4 1 Folder1\Folder4
sqlite>
sqlite> -- folders_closure
sqlite>
sqlite> .width 9 9 9
sqlite>
sqlite> select *
...> from folders_closure;
PARENT_ID CHILD_ID DEPTH
--------- --------- ---------
1 1 0
1 2 1
1 3 1
1 4 1
1 5 2
1 6 2
2 2 0
2 5 1
2 6 1
3 3 0
4 4 0
5 5 0
6 6 0
sqlite>
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
