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 recursi
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 in
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, DIST
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 m
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-tran
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
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 Med
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 wi
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 tex
at 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
&g
>
>
> -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
&g
TIC 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
>
>
> -Origin
Y
> 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.o
|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
Subj
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 Folde
19 matches
Mail list logo