Displaying hierarchical structure

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Displaying hierarchical structure

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                   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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

David Raymond
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:[hidden email]] 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                   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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Bart Smissaert
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 <[hidden email]>
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:[hidden email]]
> 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                   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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Bart Smissaert
In reply to this post by David Raymond
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 <[hidden email]>
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:[hidden email]]
> 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                   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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

David Raymond
Yup, you're right. Will have to think some more on that then.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] 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 <[hidden email]>
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:[hidden email]]
> 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                   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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

J Decker
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 <[hidden email]>
wrote:

> Yup, you're right. Will have to think some more on that then.
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> 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 <[hidden email]>
> 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:[hidden email]]
> > 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                   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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Keith Medcalf
In reply to this post by Bart Smissaert

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
(
   id        integer 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
   ...> (
   ...>    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>
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 volume.





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Bart Smissaert
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 <[hidden email]> 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
> (
>    id        integer 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
>    ...> (
>    ...>    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>
> 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 volume.
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Bart Smissaert
In reply to this post by Keith Medcalf
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 <[hidden email]> 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
> (
>    id        integer 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
>    ...> (
>    ...>    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>
> 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 volume.
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Peter Johnson
In reply to this post by Bart Smissaert
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 <[hidden email] 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                   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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Bart Smissaert
Thanks, second link regarding the extension looks interesting.

RBS

On Thu, Jan 31, 2019 at 8:32 AM Peter Johnson <[hidden email]>
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 <[hidden email]
> 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                   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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Keith Medcalf

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Jean-Luc Hainaut
In reply to this post by Bart Smissaert
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Bart Smissaert
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 <[hidden email]>
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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Jean-Luc Hainaut
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Bart Smissaert
In reply to this post by Bart Smissaert
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                   Main        000000001
2   1                   CC           000000001-0000000002
3   1                   BB           000000001-0000000003
4   1                   AA           000000001-0000000004
5   2                   B             000000001-0000000002-0000000005
6   2                   A             000000001-0000000002-0000000006

What SQL should I use to update the field RANK if the first row is known to
be 0000000001, 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 <[hidden email]>
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 <[hidden email]> 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
>> (
>>    id        integer 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
>>    ...> (
>>    ...>    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>
>> 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 volume.
>>
>>
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Clemens Ladisch
Bart Smissaert wrote:

> ID PARENT_ID FOLDER RANK
> ---------------------------------------------------------------
> 1   0                   Main        000000001
> 2   1                   CC           000000001-0000000002
> 3   1                   BB           000000001-0000000003
> 4   1                   AA           000000001-0000000004
> 5   2                   B             000000001-0000000002-0000000005
> 6   2                   A             000000001-0000000002-0000000006
>
> What SQL should I use to update the field RANK if the first row is known to
> be 0000000001, 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Bart Smissaert
In reply to this post by Bart Smissaert
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('000000000', length(folders.id)) || folders.id from folders join
paths
      where folders.parent_id = paths.id)
select replace(path, 'Main', '0000000001') 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 000000001
directly from the ID.

Any idea how to manage these two?

RBS

On Mon, Feb 4, 2019 at 10:41 PM Bart Smissaert <[hidden email]>
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                   Main        000000001
> 2   1                   CC           000000001-0000000002
> 3   1                   BB           000000001-0000000003
> 4   1                   AA           000000001-0000000004
> 5   2                   B             000000001-0000000002-0000000005
> 6   2                   A             000000001-0000000002-0000000006
>
> What SQL should I use to update the field RANK if the first row is known
> to be 0000000001, 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 <[hidden email]>
> 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 <[hidden email]>
>> 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
>>> (
>>>    id        integer 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
>>>    ...> (
>>>    ...>    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>
>>> 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 volume.
>>>
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying hierarchical structure

Bart Smissaert
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 <[hidden email]>
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('000000000', length(folders.id)) || folders.id from folders join
> paths
>       where folders.parent_id = paths.id)
> select replace(path, 'Main', '0000000001') 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 000000001
> directly from the ID.
>
> Any idea how to manage these two?
>
> RBS
>
> On Mon, Feb 4, 2019 at 10:41 PM Bart Smissaert <[hidden email]>
> 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                   Main        000000001
>> 2   1                   CC           000000001-0000000002
>> 3   1                   BB           000000001-0000000003
>> 4   1                   AA           000000001-0000000004
>> 5   2                   B             000000001-0000000002-0000000005
>> 6   2                   A             000000001-0000000002-0000000006
>>
>> What SQL should I use to update the field RANK if the first row is known
>> to be 0000000001, 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 <[hidden email]>
>> 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 <[hidden email]>
>>> 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
>>>> (
>>>>    id        integer 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
>>>>    ...> (
>>>>    ...>    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>
>>>> 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 volume.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users