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
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)) ||
Bart Smissaert wrote:
> ID PARENT_ID FOLDER RANK
> ---
> 1 0 Main1
> 2 1 CC 1-02
> 3 1 BB 1-03
> 4 1
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
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
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
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,
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
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
>
>
>
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
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
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
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
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
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
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
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
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
18 matches
Mail list logo
