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
n...@mailinglists.sqlite.org]
> On Behalf Of Bart Smissaert
> Sent: Tuesday, January 29, 2019 10:52 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Displaying hierarchical structure
>
> Working on an Android app and part of that is storing SQL in a virtual
> fold
ORDER BY
> id|parent_id|name
> 1||Folder1
> 2|1|Folder1\Folder2
> 5|2|Folder1\Folder2\Folder5
> 6|2|Folder1\Folder2\Folder6
> 3|1|Folder1\Folder3
> 4|1|Folder1\Folder4
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sql
lder2\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
1\Folder4
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Bart Smissaert
Sent: Tuesday, January 29, 2019 10:52 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Displaying hierarchical structure
Working on an Android app
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
19 matches
Mail list logo
