Re: [sqlite] Displaying hierarchical structure
Ignore this.
Was mailed over 1w ago and only came through and I have figured this out
after
studying the CTE documentation on the SQLite site.
RBS
On Wed, Feb 6, 2019 at 7:24 PM Bart Smissaert
wrote:
>
> I can select the rank as in the previous e-mail with this recursive query:
>
> with recursive paths(id, folder, path) as
> (select id, folder, folder from folders where parent_id is null union
> select folders.id, folders.folder, paths.path || '-' ||
> substr('0', length(folders.id)) || folders.id from folders join
> paths
> where folders.parent_id = paths.id)
> select replace(path, 'Main', '01') as path from paths order by path
>
> Not managed yet though to use this to update the rank column in the table
> Folders.
> Also not sure how to avoid the replace and get the rank value 1
> directly from the ID.
>
> Any idea how to manage these two?
>
> RBS
>
> On Mon, Feb 4, 2019 at 10:41 PM Bart Smissaert
> wrote:
>
>> Looking at this approach of a hierarchical system:
>> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>>
>> Given a table like this:
>>
>> ID PARENT_ID FOLDER RANK
>> ---
>> 1 0 Main1
>> 2 1 CC 1-02
>> 3 1 BB 1-03
>> 4 1 AA 1-04
>> 5 2 B 1-02-05
>> 6 2 A 1-02-06
>>
>> What SQL should I use to update the field RANK if the first row is known
>> to be 01, but all the
>> next rows are null? I tried with a non-recursive query, but couldn't work
>> it out.
>>
>> RBS
>>
>>
>>
>> On Thu, Jan 31, 2019 at 8:02 AM Bart Smissaert
>> wrote:
>>
>>> This looks a nice and simple way to display the tree in the right order
>>> without recursive SQL:
>>>
>>> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>>>
>>> Will do some testing on large numbers to see how the 2 methods compare
>>> speed-wise.
>>>
>>> RBS
>>>
>>> On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf
>>> wrote:
>>>
See https://sqlite.org/lang_with.html
which includes how to traverse the recursive tree in either depth-first
or breadth-first order.
Why do you need the closure table at all?
create table folders
(
idinteger primary key,
parent_id integer references folders,
name text not null collate nocase,
check (not (parent_id is null and id != 1))
);
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;
SQLite version 3.27.0 2019-01-28 00:42:06
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
...> (
...>idinteger primary key,
...>parent_id integer references folders,
...>name text not null collate nocase,
...>check (not (parent_id is null and id != 1))
...> );
sqlite>
sqlite> insert into folders values (1, null, 'Folder1'),
...>(2, 1, 'Folder2'),
...>(3, 1, 'Folder3'),
...>
Re: [sqlite] Displaying hierarchical structure
I can select the rank as in the previous e-mail with this recursive query:
with recursive paths(id, folder, path) as
(select id, folder, folder from folders where parent_id is null union
select folders.id, folders.folder, paths.path || '-' ||
substr('0', length(folders.id)) || folders.id from folders join
paths
where folders.parent_id = paths.id)
select replace(path, 'Main', '01') as path from paths order by path
Not managed yet though to use this to update the rank column in the table
Folders.
Also not sure how to avoid the replace and get the rank value 1
directly from the ID.
Any idea how to manage these two?
RBS
On Mon, Feb 4, 2019 at 10:41 PM Bart Smissaert
wrote:
> Looking at this approach of a hierarchical system:
> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>
> Given a table like this:
>
> ID PARENT_ID FOLDER RANK
> ---
> 1 0 Main1
> 2 1 CC 1-02
> 3 1 BB 1-03
> 4 1 AA 1-04
> 5 2 B 1-02-05
> 6 2 A 1-02-06
>
> What SQL should I use to update the field RANK if the first row is known
> to be 01, but all the
> next rows are null? I tried with a non-recursive query, but couldn't work
> it out.
>
> RBS
>
>
>
> On Thu, Jan 31, 2019 at 8:02 AM Bart Smissaert
> wrote:
>
>> This looks a nice and simple way to display the tree in the right order
>> without recursive SQL:
>>
>> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>>
>> Will do some testing on large numbers to see how the 2 methods compare
>> speed-wise.
>>
>> RBS
>>
>> On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf
>> wrote:
>>
>>>
>>> See https://sqlite.org/lang_with.html
>>>
>>> which includes how to traverse the recursive tree in either depth-first
>>> or breadth-first order.
>>>
>>> Why do you need the closure table at all?
>>>
>>>
>>> create table folders
>>> (
>>>idinteger primary key,
>>>parent_id integer references folders,
>>>name text not null collate nocase,
>>>check (not (parent_id is null and id != 1))
>>> );
>>>
>>> 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;
>>>
>>>
>>>
>>> SQLite version 3.27.0 2019-01-28 00:42:06
>>> 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
>>>...> (
>>>...>idinteger primary key,
>>>...>parent_id integer references folders,
>>>...>name text not null collate nocase,
>>>...>check (not (parent_id is null and id != 1))
>>>...> );
>>> 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,
Re: [sqlite] Displaying hierarchical structure
Bart Smissaert wrote:
> ID PARENT_ID FOLDER RANK
> ---
> 1 0 Main1
> 2 1 CC 1-02
> 3 1 BB 1-03
> 4 1 AA 1-04
> 5 2 B 1-02-05
> 6 2 A 1-02-06
>
> What SQL should I use to update the field RANK if the first row is known to
> be 01, but all the next rows are null? I tried with a non-recursive
> query,
> but couldn't work it out.
You want to append an entry's rank to its parent's rank, but only for
entries whose rank is still empty, and whose parent has a rank:
UPDATE MyTable
SET Rank = (SELECT Rank
FROM MyTable AS Parent
WHERE MyTable.Parent_ID = Parent.ID
) || printf('-%09d', ID)
WHERE Rank IS NULL
AND Parent_ID IN (SELECT ID
FROM MyTable
WHERE Rank IS NOT NULL);
Repeat until no empty rows are left.
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying hierarchical structure
Looking at this approach of a hierarchical system:
https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
Given a table like this:
ID PARENT_ID FOLDER RANK
---
1 0 Main1
2 1 CC 1-02
3 1 BB 1-03
4 1 AA 1-04
5 2 B 1-02-05
6 2 A 1-02-06
What SQL should I use to update the field RANK if the first row is known to
be 01, but all the
next rows are null? I tried with a non-recursive query, but couldn't work
it out.
RBS
On Thu, Jan 31, 2019 at 8:02 AM Bart Smissaert
wrote:
> This looks a nice and simple way to display the tree in the right order
> without recursive SQL:
>
> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>
> Will do some testing on large numbers to see how the 2 methods compare
> speed-wise.
>
> RBS
>
> On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf wrote:
>
>>
>> See https://sqlite.org/lang_with.html
>>
>> which includes how to traverse the recursive tree in either depth-first
>> or breadth-first order.
>>
>> Why do you need the closure table at all?
>>
>>
>> create table folders
>> (
>>idinteger primary key,
>>parent_id integer references folders,
>>name text not null collate nocase,
>>check (not (parent_id is null and id != 1))
>> );
>>
>> 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;
>>
>>
>>
>> SQLite version 3.27.0 2019-01-28 00:42:06
>> 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
>>...> (
>>...>idinteger primary key,
>>...>parent_id integer references folders,
>>...>name text not null collate nocase,
>>...>check (not (parent_id is null and id != 1))
>>...> );
>> 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
Re: [sqlite] Displaying hierarchical structure
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
Re: [sqlite] Displaying hierarchical structure
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
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 10
> > 2 20
> > 3 30
> > 4 40
> > 5 50
> > 6 60
> > 1 21
> > 1 31
> > 1 41
> > 2 51
> > 1 52
> > 2 61
> > 1 62
> >
> > What should the SQL be to display the folders like this:
> >
> > FolderPARENT_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
Re: [sqlite] Displaying hierarchical structure
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 10
2 20
3 30
4 40
5 50
6 60
1 21
1 31
1 41
2 51
1 52
2 61
1 62
What should the SQL be to display the folders like this:
FolderPARENT_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
Re: [sqlite] Displaying hierarchical structure
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
(
idinteger 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
...> (
...>idinteger 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
...>
Re: [sqlite] Displaying hierarchical structure
Thanks, second link regarding the extension looks interesting. RBS On Thu, Jan 31, 2019 at 8:32 AM Peter Johnson wrote: > some relevant links: > > http://dwhoman.com/blog/sql-transitive-closure.html > > > http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ > > On Wed, 30 Jan 2019, 4:52 AM Bart Smissaert wrote: > > > 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 10 > > 2 20 > > 3 30 > > 4 40 > > 5 50 > > 6 60 > > 1 21 > > 1 31 > > 1 41 > > 2 51 > > 1 52 > > 2 61 > > 1 62 > > > > What should the SQL be to display the folders like this: > > > > FolderPARENT_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
Re: [sqlite] Displaying hierarchical structure
some relevant links: http://dwhoman.com/blog/sql-transitive-closure.html http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ On Wed, 30 Jan 2019, 4:52 AM Bart Smissaert 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 10 > 2 20 > 3 30 > 4 40 > 5 50 > 6 60 > 1 21 > 1 31 > 1 41 > 2 51 > 1 52 > 2 61 > 1 62 > > What should the SQL be to display the folders like this: > > FolderPARENT_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
Re: [sqlite] Displaying hierarchical structure
This looks a nice and simple way to display the tree in the right order
without recursive SQL:
https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
Will do some testing on large numbers to see how the 2 methods compare
speed-wise.
RBS
On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf wrote:
>
> See https://sqlite.org/lang_with.html
>
> which includes how to traverse the recursive tree in either depth-first or
> breadth-first order.
>
> Why do you need the closure table at all?
>
>
> create table folders
> (
>idinteger primary key,
>parent_id integer references folders,
>name text not null collate nocase,
>check (not (parent_id is null and id != 1))
> );
>
> 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;
>
>
>
> SQLite version 3.27.0 2019-01-28 00:42:06
> 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
>...> (
>...>idinteger primary key,
>...>parent_id integer references folders,
>...>name text not null collate nocase,
>...>check (not (parent_id is null and id != 1))
>...> );
> 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
>
Re: [sqlite] Displaying hierarchical structure
Yes, thanks, -- breadth first does the job nicely indeed.
Not sure the closure table is needed. There are some complex tasks though,
that need
thinking of, eg copying one folder into an other folder.
I am not familiar with recursive queries and it looks complex to me.
There might arise a problem with speed if there are many folders, although
for now
that will be long way off.
Will study the mentioned link.
RBS
On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf wrote:
>
> See https://sqlite.org/lang_with.html
>
> which includes how to traverse the recursive tree in either depth-first or
> breadth-first order.
>
> Why do you need the closure table at all?
>
>
> create table folders
> (
>idinteger primary key,
>parent_id integer references folders,
>name text not null collate nocase,
>check (not (parent_id is null and id != 1))
> );
>
> 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;
>
>
>
> SQLite version 3.27.0 2019-01-28 00:42:06
> 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
>...> (
>...>idinteger primary key,
>...>parent_id integer references folders,
>...>name text not null collate nocase,
>...>check (not (parent_id is null and id != 1))
>...> );
> 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,
>...>
Re: [sqlite] Displaying hierarchical structure
See https://sqlite.org/lang_with.html
which includes how to traverse the recursive tree in either depth-first or
breadth-first order.
Why do you need the closure table at all?
create table folders
(
idinteger primary key,
parent_id integer references folders,
name text not null collate nocase,
check (not (parent_id is null and id != 1))
);
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;
SQLite version 3.27.0 2019-01-28 00:42:06
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
...> (
...>idinteger primary key,
...>parent_id integer references folders,
...>name text not null collate nocase,
...>check (not (parent_id is null and id != 1))
...> );
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>
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic
Re: [sqlite] Displaying hierarchical structure
Probably just the order by just do 'order by parent_id,name' to group folders together and then alphabetcal? (was expanding it... I would also start with where parent_id=0 ) with recursive foo (id, parent_id, name) as ( select * from folders where parent_id = 0 union all select folders.id, folders.parent_id, foo.name || '\' || folders.name from folders inner join foo on folders.parent_id = foo.id ) select * from foo order by parent_id,name; On Tue, Jan 29, 2019 at 10:43 AM David Raymond wrote: > Yup, you're right. Will have to think some more on that then. > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Tuesday, January 29, 2019 1:06 PM > To: SQLite mailing list > Subject: Re: [sqlite] Displaying hierarchical structure > > Had another look at your solution and not sure now it is quite OK. > The output comes out fine, but that seems to be due to the lucky fact that > it just sort OK by folder. If I change the folder names then the output is > not OK. > > RBS > > On Tue, Jan 29, 2019 at 5:09 PM David Raymond > wrote: > > > I may be missing something, but with recursive CTE's, why do you need the > > second table? I'm assuming it's just to speed things up once the counts > get > > large? > > > > > > sqlite> create table folders (id integer primary key, parent_id int > > references folders, name text not null collate nocase, check (not > > (parent_id is null and id != 1))); > > QUERY PLAN > > `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) > > > > > > sqlite> insert into folders values (1, null, 'Folder1'), (2, 1, > > 'Folder2'), (3, 1, 'Folder3'), (4, 1, 'Folder4'), (5, 2, 'Folder5'), (6, > 2, > > 'Folder6'); > > QUERY PLAN > > |--SCAN 6 CONSTANT ROWS > > `--SCAN TABLE folders > > > > > > Gonna use backslashes here rather than spaces. So admitidly not exactly > > what you were looking for. But some playing around with string functions > > should be able to get there. > > > > > > sqlite> with recursive foo (id, parent_id, name) as (select * from > folders > > where id = 1 union all select folders.id, folders.parent_id, foo.name || > > '\' || folders.name from folders inner join foo on folders.parent_id = > > foo.id) select * from foo order by name; > > QUERY PLAN > > |--CO-ROUTINE 2 > > | |--SETUP > > | | `--SEARCH TABLE folders USING INTEGER PRIMARY KEY (rowid=?) > > | `--RECURSIVE STEP > > | |--SCAN TABLE foo > > | `--SEARCH TABLE folders USING AUTOMATIC COVERING INDEX > (parent_id=?) > > |--SCAN SUBQUERY 2 > > `--USE TEMP B-TREE FOR ORDER BY > > id|parent_id|name > > 1||Folder1 > > 2|1|Folder1\Folder2 > > 5|2|Folder1\Folder2\Folder5 > > 6|2|Folder1\Folder2\Folder6 > > 3|1|Folder1\Folder3 > > 4|1|Folder1\Folder4 > > > > > > -Original Message- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Bart Smissaert > > Sent: Tuesday, January 29, 2019 10:52 AM > > To: General Discussion of SQLite Database > > Subject: [sqlite] Displaying hierarchical structure > > > > 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 10 > > 2 20 > > 3 30 > > 4 40 > > 5 50 > > 6 60 > > 1 21 > > 1 31 > > 1 41 > > 2 51 > > 1 52 > > 2 61 > > 1 62 > > > > What should the SQL be to display the folders
Re: [sqlite] Displaying hierarchical structure
Yup, you're right. Will have to think some more on that then. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert Sent: Tuesday, January 29, 2019 1:06 PM To: SQLite mailing list Subject: Re: [sqlite] Displaying hierarchical structure Had another look at your solution and not sure now it is quite OK. The output comes out fine, but that seems to be due to the lucky fact that it just sort OK by folder. If I change the folder names then the output is not OK. RBS On Tue, Jan 29, 2019 at 5:09 PM David Raymond wrote: > I may be missing something, but with recursive CTE's, why do you need the > second table? I'm assuming it's just to speed things up once the counts get > large? > > > sqlite> create table folders (id integer primary key, parent_id int > references folders, name text not null collate nocase, check (not > (parent_id is null and id != 1))); > QUERY PLAN > `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) > > > sqlite> insert into folders values (1, null, 'Folder1'), (2, 1, > 'Folder2'), (3, 1, 'Folder3'), (4, 1, 'Folder4'), (5, 2, 'Folder5'), (6, 2, > 'Folder6'); > QUERY PLAN > |--SCAN 6 CONSTANT ROWS > `--SCAN TABLE folders > > > Gonna use backslashes here rather than spaces. So admitidly not exactly > what you were looking for. But some playing around with string functions > should be able to get there. > > > sqlite> with recursive foo (id, parent_id, name) as (select * from folders > where id = 1 union all select folders.id, folders.parent_id, foo.name || > '\' || folders.name from folders inner join foo on folders.parent_id = > foo.id) select * from foo order by name; > QUERY PLAN > |--CO-ROUTINE 2 > | |--SETUP > | | `--SEARCH TABLE folders USING INTEGER PRIMARY KEY (rowid=?) > | `--RECURSIVE STEP > | |--SCAN TABLE foo > | `--SEARCH TABLE folders USING AUTOMATIC COVERING INDEX (parent_id=?) > |--SCAN SUBQUERY 2 > `--USE TEMP B-TREE FOR ORDER BY > id|parent_id|name > 1||Folder1 > 2|1|Folder1\Folder2 > 5|2|Folder1\Folder2\Folder5 > 6|2|Folder1\Folder2\Folder6 > 3|1|Folder1\Folder3 > 4|1|Folder1\Folder4 > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Tuesday, January 29, 2019 10:52 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Displaying hierarchical structure > > 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 10 > 2 20 > 3 30 > 4 40 > 5 50 > 6 60 > 1 21 > 1 31 > 1 41 > 2 51 > 1 52 > 2 61 > 1 62 > > What should the SQL be to display the folders like this: > > FolderPARENT_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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying hierarchical structure
Had another look at your solution and not sure now it is quite OK. The output comes out fine, but that seems to be due to the lucky fact that it just sort OK by folder. If I change the folder names then the output is not OK. RBS On Tue, Jan 29, 2019 at 5:09 PM David Raymond wrote: > I may be missing something, but with recursive CTE's, why do you need the > second table? I'm assuming it's just to speed things up once the counts get > large? > > > sqlite> create table folders (id integer primary key, parent_id int > references folders, name text not null collate nocase, check (not > (parent_id is null and id != 1))); > QUERY PLAN > `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) > > > sqlite> insert into folders values (1, null, 'Folder1'), (2, 1, > 'Folder2'), (3, 1, 'Folder3'), (4, 1, 'Folder4'), (5, 2, 'Folder5'), (6, 2, > 'Folder6'); > QUERY PLAN > |--SCAN 6 CONSTANT ROWS > `--SCAN TABLE folders > > > Gonna use backslashes here rather than spaces. So admitidly not exactly > what you were looking for. But some playing around with string functions > should be able to get there. > > > sqlite> with recursive foo (id, parent_id, name) as (select * from folders > where id = 1 union all select folders.id, folders.parent_id, foo.name || > '\' || folders.name from folders inner join foo on folders.parent_id = > foo.id) select * from foo order by name; > QUERY PLAN > |--CO-ROUTINE 2 > | |--SETUP > | | `--SEARCH TABLE folders USING INTEGER PRIMARY KEY (rowid=?) > | `--RECURSIVE STEP > | |--SCAN TABLE foo > | `--SEARCH TABLE folders USING AUTOMATIC COVERING INDEX (parent_id=?) > |--SCAN SUBQUERY 2 > `--USE TEMP B-TREE FOR ORDER BY > id|parent_id|name > 1||Folder1 > 2|1|Folder1\Folder2 > 5|2|Folder1\Folder2\Folder5 > 6|2|Folder1\Folder2\Folder6 > 3|1|Folder1\Folder3 > 4|1|Folder1\Folder4 > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Tuesday, January 29, 2019 10:52 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Displaying hierarchical structure > > 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 10 > 2 20 > 3 30 > 4 40 > 5 50 > 6 60 > 1 21 > 1 31 > 1 41 > 2 51 > 1 52 > 2 61 > 1 62 > > What should the SQL be to display the folders like this: > > FolderPARENT_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
Re: [sqlite] Displaying hierarchical structure
Thanks for that, very nice indeed. The second table is used for other purposes. I think the depth column speeds up certain queries. RBS On Tue, Jan 29, 2019 at 5:09 PM David Raymond wrote: > I may be missing something, but with recursive CTE's, why do you need the > second table? I'm assuming it's just to speed things up once the counts get > large? > > > sqlite> create table folders (id integer primary key, parent_id int > references folders, name text not null collate nocase, check (not > (parent_id is null and id != 1))); > QUERY PLAN > `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) > > > sqlite> insert into folders values (1, null, 'Folder1'), (2, 1, > 'Folder2'), (3, 1, 'Folder3'), (4, 1, 'Folder4'), (5, 2, 'Folder5'), (6, 2, > 'Folder6'); > QUERY PLAN > |--SCAN 6 CONSTANT ROWS > `--SCAN TABLE folders > > > Gonna use backslashes here rather than spaces. So admitidly not exactly > what you were looking for. But some playing around with string functions > should be able to get there. > > > sqlite> with recursive foo (id, parent_id, name) as (select * from folders > where id = 1 union all select folders.id, folders.parent_id, foo.name || > '\' || folders.name from folders inner join foo on folders.parent_id = > foo.id) select * from foo order by name; > QUERY PLAN > |--CO-ROUTINE 2 > | |--SETUP > | | `--SEARCH TABLE folders USING INTEGER PRIMARY KEY (rowid=?) > | `--RECURSIVE STEP > | |--SCAN TABLE foo > | `--SEARCH TABLE folders USING AUTOMATIC COVERING INDEX (parent_id=?) > |--SCAN SUBQUERY 2 > `--USE TEMP B-TREE FOR ORDER BY > id|parent_id|name > 1||Folder1 > 2|1|Folder1\Folder2 > 5|2|Folder1\Folder2\Folder5 > 6|2|Folder1\Folder2\Folder6 > 3|1|Folder1\Folder3 > 4|1|Folder1\Folder4 > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Tuesday, January 29, 2019 10:52 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Displaying hierarchical structure > > 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 10 > 2 20 > 3 30 > 4 40 > 5 50 > 6 60 > 1 21 > 1 31 > 1 41 > 2 51 > 1 52 > 2 61 > 1 62 > > What should the SQL be to display the folders like this: > > FolderPARENT_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
Re: [sqlite] Displaying hierarchical structure
I may be missing something, but with recursive CTE's, why do you need the second table? I'm assuming it's just to speed things up once the counts get large? sqlite> create table folders (id integer primary key, parent_id int references folders, name text not null collate nocase, check (not (parent_id is null and id != 1))); QUERY PLAN `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) sqlite> insert into folders values (1, null, 'Folder1'), (2, 1, 'Folder2'), (3, 1, 'Folder3'), (4, 1, 'Folder4'), (5, 2, 'Folder5'), (6, 2, 'Folder6'); QUERY PLAN |--SCAN 6 CONSTANT ROWS `--SCAN TABLE folders Gonna use backslashes here rather than spaces. So admitidly not exactly what you were looking for. But some playing around with string functions should be able to get there. sqlite> with recursive foo (id, parent_id, name) as (select * from folders where id = 1 union all select folders.id, folders.parent_id, foo.name || '\' || folders.name from folders inner join foo on folders.parent_id = foo.id) select * from foo order by name; QUERY PLAN |--CO-ROUTINE 2 | |--SETUP | | `--SEARCH TABLE folders USING INTEGER PRIMARY KEY (rowid=?) | `--RECURSIVE STEP | |--SCAN TABLE foo | `--SEARCH TABLE folders USING AUTOMATIC COVERING INDEX (parent_id=?) |--SCAN SUBQUERY 2 `--USE TEMP B-TREE FOR ORDER BY id|parent_id|name 1||Folder1 2|1|Folder1\Folder2 5|2|Folder1\Folder2\Folder5 6|2|Folder1\Folder2\Folder6 3|1|Folder1\Folder3 4|1|Folder1\Folder4 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert Sent: Tuesday, January 29, 2019 10:52 AM To: General Discussion of SQLite Database Subject: [sqlite] Displaying hierarchical structure 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 10 2 20 3 30 4 40 5 50 6 60 1 21 1 31 1 41 2 51 1 52 2 61 1 62 What should the SQL be to display the folders like this: FolderPARENT_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] Displaying hierarchical structure
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 10 2 20 3 30 4 40 5 50 6 60 1 21 1 31 1 41 2 51 1 52 2 61 1 62 What should the SQL be to display the folders like this: FolderPARENT_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
