Re: [sqlite] Custom aggregate functions in Tcl
I read some more about window functions and now see more clearly that they
are an extension to aggregate functions. Now I understand why it makes
sense to have a method name for both aggregate and window functions. I'll
also go ahead and put window function support in my code next chance I get,
rather than wait until I'm totally solid on aggregate functions.
On Wed, Jan 30, 2019, 13:59 Richard Hipp It seems that you distinguish between the xStep and xFinal methods by
> the number of argments. xStep as 1+N argument (where N is the number
> of function parameters) and xFinal has just 1.
>
> Dan suggests (and I agree) that this will not extend well to window
> functions. It might be better to have an initial argument that is the
> "method" name. xStep would 2+N arguments where the first argument is
> the string "step" and xFinal has 2 arguments where the first argument
> is "final". Then when you go to add the xValue and xInverse routines
> for window functions, you will have a convenient way to distinguish
> those calls from xStep and xFinal.
>
> On 1/30/19, Andy Goth wrote:
> > On 1/29/19 1:15 AM, Andy Goth wrote:
> >> I wish to define custom aggregate functions in Tcl
> >
> > Initial implementation:
> >
> >
> https://chiselapp.com/user/andy/repository/sqlite-andy/info/e0689f05d1f8792d
> >
> > Sample program, intended to be run from the root of a built SQLite tree:
> >
> > #!/usr/bin/env tclsh
> >
> > load .libs/libtclsqlite3.so
> >
> > sqlite3 db
> >
> > db function incr -deterministic {
> >
> > apply {{arg} {
> >
> > incr arg
> >
> > }}
> >
> > }
> >
> > db function list -deterministic -aggregate {
> >
> > apply {{state args} {
> >
> > concat $state $args
> >
> > }}
> >
> > }
> >
> > puts [db eval {
> >
> > SELECT list(column1, ''), incr(42)
> >
> >FROM (VALUES ('#'), ('#'), (' c '), ('\'), ('\\'), ('{ xx '),
> > ('}'))
> > }]
> >
> > db close
> >
> > # vim: set sts=4 sw=4 tw=80 et ft=tcl:
> >
> >
> > Here's the output:
> >
> > {{#} {} # {} { c } {} \\ {} {\\} {} \{\ xx\ {} \} {}} 43
> >
> >
> > Notice that the first # element is brace-quoted and the second one is
> > not. This demonstrates TIP 401 compliance (http://tip.tcl.tk/401) in
> > recent Tcl.
> >
> > Before I can call this project finished, I need to update the test suite
> > and documentation. But I didn't want to wait for that to release the
> > code I've already written, so here you go.
> >
> > As a separate project, I would like to improve the Tcl script dispatch.
> > For several reasons, safeToUseEvalObjv() doesn't really meet its stated
> > goal. I have a lot to say about this but should wait until another time
> > to go into detail. Let's finish the aggregate function project first.
> >
> > Another good follow-on project would be adding support for window
> > functions. Before I can take that on, I need to gain some experience
> > using, let alone writing, window functions.
> >
> > --
> > Andy Goth |
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Pickling" an in-memory SQLite database.
If sqlite3_serialize and sqlite3_deserialize are not exposed in your bindings, I suppose you can do something like with NamedTemporaryFile(mode='rb', prefix=prefix) as tmp: c = sqlite3.connect(tmp.name) # Do stuff. c.close() serialized = tmp.file.read() where "prefix" is a RAM filesystem. sqlite3_serialize and sqlite3_deserialize are better though. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Pickling" an in-memory SQLite database.
sqlite3_serialize() and deserialize would work: https://www.sqlite.org/c3ref/serialize.html On Thu, Jan 31, 2019, 4:47 PM Randall Smith Hi, guys. > > I have an application that allows me to embed a (potentially large) block > of data into its application file at close, and read the block back on > open. It would be convenient and attractive for me, for a plugin I am > writing for this application, to be able to use an in-memory SQLite > database, and then squirrel the DB away with everything else in the > application file. That way, everything would be stored under one roof, so > to speak. > > My question: Is there a good way to "pickle" a SQLite database into a > block of bytes for storage, and then "unpickle" it later for use? My > fantasy is that this could all be done in memory so the file system does > not have to become involved. > > I feel like I am missing something obvious here, but can't quite come up > with it. Thanks for any ideas or suggestions. > > Randall. > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "Pickling" an in-memory SQLite database.
Hi, guys. I have an application that allows me to embed a (potentially large) block of data into its application file at close, and read the block back on open. It would be convenient and attractive for me, for a plugin I am writing for this application, to be able to use an in-memory SQLite database, and then squirrel the DB away with everything else in the application file. That way, everything would be stored under one roof, so to speak. My question: Is there a good way to "pickle" a SQLite database into a block of bytes for storage, and then "unpickle" it later for use? My fantasy is that this could all be done in memory so the file system does not have to become involved. I feel like I am missing something obvious here, but can't quite come up with it. Thanks for any ideas or suggestions. Randall. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?
On 31 Jan 2019, at 6:21pm, Scott wrote: > Figured it out! I had set the column Deleted to "CHAR" but all the fields > without 'X' were null. If I replaced null with a valid character it worked. > Thanks for your time. Ah, JOINing on NULL. Well done. For future reference, SQLite doesn't have a CHAR type. For clarity you might want to use TEXT instead. SQLite does assume TEXT when you specify CHAR, but you might be depending on a specifically CHAR behaviour, like truncation to one character, and SQLite will ignore it. Well done for solving your problem. The magic of posting. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying hierarchical structure
On 31/01/2019 17:59, Bart Smissaert wrote:
Thanks, will try that.
order by PATH
So, where is this path coming from?
Simple, from a discrepancy between the script I have tested and the
contents of this mail!
Here is the complete (tested) script:
create table CLOSURE(PARENT_ID integer,ID integer,PATH text,DIST integer);
create trigger CLOSURE_INS after insert on CLOSURE
for each row
begin
insert into CLOSURE
select new.PARENT_ID,ID,new.PATH||'/'||cast(ID as char),new.DIST+1
from FOLDERS
where PARENT_ID = new.ID; end;
insert into CLOSURE select ID,ID,'1',0 from FOLDERS where ID = 1;
select *,substr(' ',1,2*DIST)||cast(ID as char) as Display from
CLOSURE order by PATH;
+---++---+--+-+
| PARENT_ID | ID | PATH | DIST | Display |
+---++---+--+-+
| 1 | 1 | 1 | 0| 1 |
| 1 | 2 | 1/2 | 1| 2 |
| 1 | 5 | 1/2/5 | 2| 5 |
| 1 | 6 | 1/2/6 | 2| 6 |
| 1 | 3 | 1/3 | 1| 3 |
| 1 | 4 | 1/4 | 1| 4 |
+---++---+--+-+
JL
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?
Figured it out! I had set the column Deleted to "CHAR" but all the fields without 'X' were null. If I replaced null with a valid character it worked. Thanks for your time. Scott ValleryEcclesiastes 4:9-10 On Thursday, January 31, 2019, 12:46:34 PM EST, Scott wrote: I can return results successfully from the t.Topic and n.Deleted columns separately, but when I try to use AND I receive no results. I'm not sure what I may be doing wrong. This is my first exhaustive work with a database project, so I've had to learn some syntax along the way, but has me stumped. SELECT n.NoteID, s.SourceType, s.Title, c.Summary FROM Comment as c LEFT JOIN Notes as n ON n.CommentID = c.CommentID LEFT JOIN Source as s ON n.SourceID = s.SourceID LEFT JOIN Topic as t ON n.TopicID = t.TopicID WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X') I've tried and even with INNER JOIN: (WHERE t.Topic = 'Manuscript Copies') AND n.Deleted <> 'X')WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X') WHERE t.Topic = 'Manuscript Copies' AND n.Deleted <> 'X' Thanks, Scott ValleryEcclesiastes 4:9-10 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?
On 31 Jan 2019, at 5:46pm, Scott wrote: > I can return results successfully from the t.Topic and n.Deleted columns > separately, but when I try to use AND I receive no results. There was an optimization bug that looked like your example in some previous version of SQLite. Are you running an up-to-date version of SQLite ? Are you doing this in your own code or in the SQLite CLI tool ? If possible, please test in the CLI tool. Please execute "PRAGMA schema.integrity_check" just in case your database is corrupt. Try removing everything that doesn't cause the problem. So remove the JOIN to the Source table. Swap the order of the conditions around the "AND". Swap the order of the JOIN clauses. Some combination of the above should tell you more about the problem. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to do I get an 'AND' condition to work in an SQLite query?
I can return results successfully from the t.Topic and n.Deleted columns separately, but when I try to use AND I receive no results. I'm not sure what I may be doing wrong. This is my first exhaustive work with a database project, so I've had to learn some syntax along the way, but has me stumped. SELECT n.NoteID, s.SourceType, s.Title, c.Summary FROM Comment as c LEFT JOIN Notes as n ON n.CommentID = c.CommentID LEFT JOIN Source as s ON n.SourceID = s.SourceID LEFT JOIN Topic as t ON n.TopicID = t.TopicID WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X') I've tried and even with INNER JOIN: (WHERE t.Topic = 'Manuscript Copies') AND n.Deleted <> 'X')WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X') WHERE t.Topic = 'Manuscript Copies' AND n.Deleted <> 'X' Thanks, Scott ValleryEcclesiastes 4:9-10 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying hierarchical structure
Thanks, will try that.
> order by PATH
So, where is this path coming from?
RBS
On Thu, Jan 31, 2019 at 4:08 PM Jean-Luc Hainaut
wrote:
> Recursive CTEs are the most obvious technique to solve this kind of
> problems.
> However, a less known technique can do the job: recursive triggers.
> Here is how the closure of FOLDERS can be computed. It will be stored in
> table CLOSURE:
>
> create table CLOSURE(PARENT_ID integer, ID integer, DIST integer);
>
> A trigger adds the children rows of each row that has been inserted into
> this table:
>
> create trigger CLOSURE_INS after insert on CLOSURE
> for each row
> begin
>insert into CLOSURE
>select new.PARENT_ID,ID,new.DIST+1 from FOLDERS
>where PARENT_ID = new.ID;
> end;
>
> To compute the closure, we just insert the root node:
>
> insert into CLOSURE select ID,ID,0 from FOLDERS where ID = 1;
>
> or all the nodes:
>
> insert into CLOSURE select ID,ID,0 from FOLDERS;
>
> To get the strict closure, we discard the initial rows (DIST = 0). And
> to display the node hierarchy:
>
> select *,substr(' ',1,2*DIST)||cast(ID as char) as Display
> from CLOSURE order by PATH;
>
> +---++--+-+
> | PARENT_ID | ID | DIST | Display |
> +---++--+-+
> | 1 | 1 | 0| 1 |
> | 1 | 2 | 1| 2 |
> | 1 | 5 | 2| 5 |
> | 1 | 6 | 2| 6 |
> | 1 | 3 | 1| 3 |
> | 1 | 4 | 1| 4 |
> +---++--+-+
>
> The path of each folder is computed in the same way.
>
> J-L Hainaut
>
> > Working on an Android app and part of that is storing SQL in a virtual
> > folder system in SQLite. For this I want to use a so-called closure table
> > as explained nicely here:
> >
> > http://technobytz.com/closure_table_store_hierarchical_data.html
> >
> > I have a table holder the folder details:
> >
> > ID PARENT_ID Folder
> >
> > -
> > 1 0 Folder1
> > 2 1 Folder2
> > 3 1 Folder3
> > 4 1 Folder4
> > 5 2 Folder5
> > 6 2 Folder6
> >
> >
> > And then the closure table:
> >
> > PARENT_ID CHILD_ID DEPTH
> >
> > ---
> > 1 10
> > 2 20
> > 3 30
> > 4 40
> > 5 50
> > 6 60
> > 1 21
> > 1 31
> > 1 41
> > 2 51
> > 1 52
> > 2 61
> > 1 62
> >
> > What should the SQL be to display the folders like this:
> >
> > FolderPARENT_ID
> > Folder1 0
> > Folder2 1
> > Folder5 2
> > Folder6 2
> > Folder3 1
> > Folder4 1
> >
> >
> > RBS
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying hierarchical structure
Recursive CTEs are the most obvious technique to solve this kind of
problems.
However, a less known technique can do the job: recursive triggers.
Here is how the closure of FOLDERS can be computed. It will be stored in
table CLOSURE:
create table CLOSURE(PARENT_ID integer, ID integer, DIST integer);
A trigger adds the children rows of each row that has been inserted into
this table:
create trigger CLOSURE_INS after insert on CLOSURE
for each row
begin
insert into CLOSURE
select new.PARENT_ID,ID,new.DIST+1 from FOLDERS
where PARENT_ID = new.ID;
end;
To compute the closure, we just insert the root node:
insert into CLOSURE select ID,ID,0 from FOLDERS where ID = 1;
or all the nodes:
insert into CLOSURE select ID,ID,0 from FOLDERS;
To get the strict closure, we discard the initial rows (DIST = 0). And
to display the node hierarchy:
select *,substr(' ',1,2*DIST)||cast(ID as char) as Display
from CLOSURE order by PATH;
+---++--+-+
| PARENT_ID | ID | DIST | Display |
+---++--+-+
| 1 | 1 | 0| 1 |
| 1 | 2 | 1| 2 |
| 1 | 5 | 2| 5 |
| 1 | 6 | 2| 6 |
| 1 | 3 | 1| 3 |
| 1 | 4 | 1| 4 |
+---++--+-+
The path of each folder is computed in the same way.
J-L Hainaut
Working on an Android app and part of that is storing SQL in a virtual
folder system in SQLite. For this I want to use a so-called closure table
as explained nicely here:
http://technobytz.com/closure_table_store_hierarchical_data.html
I have a table holder the folder details:
ID PARENT_ID Folder
-
1 0 Folder1
2 1 Folder2
3 1 Folder3
4 1 Folder4
5 2 Folder5
6 2 Folder6
And then the closure table:
PARENT_ID CHILD_ID DEPTH
---
1 10
2 20
3 30
4 40
5 50
6 60
1 21
1 31
1 41
2 51
1 52
2 61
1 62
What should the SQL be to display the folders like this:
FolderPARENT_ID
Folder1 0
Folder2 1
Folder5 2
Folder6 2
Folder3 1
Folder4 1
RBS
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?
On Mon, Jan 28, 2019 at 9:26 AM Vladimir Barbu < vladimir.ba...@schneider-electric-dms.com> wrote: > This vulnerability has been addressed in SQLite 3.26.0. When could we > expect new version (official) of System.Data.SQLite which uses 3.26.0? > That would also make it much easier to use new features from .NET, such as window functions and *a lot* of bugfixes since 3.24. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying hierarchical structure
Using the transitive_closure virtual table extension (closure.c) on your
original question (my sqlite3 has everything built-in already, so no need to
load the extension):
Note though that the AVL tree generated by the closure extension is generated
on the fly upon request and does not have a materialized backing store.
create table folders
(
idinteger primary key,
parent_id integer references folders,
name text not null collate nocase,
check (not (parent_id is null and id != 1))
);
create index foldersparentid on folders (parent_id);
create virtual table Closure using transitive_closure;
create view folders_closure
as select folders.id as PARENT_ID,
Closure.id as CHILD_ID,
Closure.depth as DEPTH
from folders, Closure
where Closure.root == folders.id
and Closure.tablename = 'folders'
and Closure.idcolumn = 'id'
and Closure.parentcolumn = 'parent_id';
insert into folders values (1, null, 'Folder1'),
(2, 1, 'Folder2'),
(3, 1, 'Folder3'),
(4, 1, 'Folder4'),
(5, 2, 'Folder5'),
(6, 2, 'Folder6');
.head on
.mode column
.width 30 9 38
-- depth first
with foo (id, parent_id, name, level, path)
as (select folders.*, 0, folders.name
from folders
where parent_id is null
union all
select folders.*, level + 1, foo.path || '\' || folders.name
from foo, folders
where folders.parent_id = foo.id
order by 4
)
select substr('', 1, (level - 1) * 4) || name as Folder,
coalesce(parent_id, 0) as PARENT_ID,
path as FullPath
from foo;
-- breadth first
with foo (id, parent_id, name, level, path)
as (select folders.*, 0, folders.name
from folders
where parent_id is null
union all
select folders.*, level + 1, foo.path || '\' || folders.name
from foo, folders
where folders.parent_id = foo.id
order by 4 desc
)
select substr('', 1, (level - 1) * 4) || name as Folder,
coalesce(parent_id, 0) as PARENT_ID,
path as FullPath
from foo;
-- folders_closure
.width 9 9 9
select *
from folders_closure;
SQLite version 3.27.0 2019-01-31 02:42:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table folders
...> (
...>idinteger primary key,
...>parent_id integer references folders,
...>name text not null collate nocase,
...>check (not (parent_id is null and id != 1))
...> );
sqlite> create index foldersparentid on folders (parent_id);
sqlite>
sqlite> create virtual table Closure using transitive_closure;
sqlite>
sqlite> create view folders_closure
...> as select folders.id as PARENT_ID,
...> Closure.id as CHILD_ID,
...> Closure.depth as DEPTH
...> from folders, Closure
...> where Closure.root == folders.id
...>and Closure.tablename = 'folders'
...>and Closure.idcolumn = 'id'
...>and Closure.parentcolumn = 'parent_id';
sqlite>
sqlite> insert into folders values (1, null, 'Folder1'),
...>(2, 1, 'Folder2'),
...>(3, 1, 'Folder3'),
...>(4, 1, 'Folder4'),
...>(5, 2, 'Folder5'),
...>(6, 2, 'Folder6');
sqlite> .head on
sqlite> .mode column
sqlite> .width 30 9 38
sqlite>
sqlite> -- depth first
sqlite>
sqlite> with foo (id, parent_id, name, level, path)
...> as (select folders.*, 0, folders.name
...> from folders
...>where parent_id is null
...>union all
...> select folders.*, level + 1, foo.path || '\' || folders.name
...> from foo, folders
...>where folders.parent_id = foo.id
...> order by 4
...> )
...> select substr('', 1, (level - 1) * 4) || name as
Folder,
...>coalesce(parent_id, 0) as PARENT_ID,
...>path as FullPath
...> from foo;
Folder PARENT_ID FullPath
-- -
--
Folder1 0 Folder1
Folder2 1 Folder1\Folder2
Folder3 1 Folder1\Folder3
Folder4 1 Folder1\Folder4
Folder5 2 Folder1\Folder2\Folder5
Folder6 2 Folder1\Folder2\Folder6
sqlite>
sqlite>
sqlite> -- breadth first
sqlite>
sqlite> with foo (id, parent_id, name, level, path)
...> as (select folders.*, 0, folders.name
...>
Re: [sqlite] Displaying hierarchical structure
Thanks, second link regarding the extension looks interesting. RBS On Thu, Jan 31, 2019 at 8:32 AM Peter Johnson wrote: > some relevant links: > > http://dwhoman.com/blog/sql-transitive-closure.html > > > http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ > > On Wed, 30 Jan 2019, 4:52 AM Bart Smissaert wrote: > > > Working on an Android app and part of that is storing SQL in a virtual > > folder system in SQLite. For this I want to use a so-called closure table > > as explained nicely here: > > > > http://technobytz.com/closure_table_store_hierarchical_data.html > > > > I have a table holder the folder details: > > > > ID PARENT_ID Folder > > > > - > > 1 0 Folder1 > > 2 1 Folder2 > > 3 1 Folder3 > > 4 1 Folder4 > > 5 2 Folder5 > > 6 2 Folder6 > > > > > > And then the closure table: > > > > PARENT_ID CHILD_ID DEPTH > > > > --- > > 1 10 > > 2 20 > > 3 30 > > 4 40 > > 5 50 > > 6 60 > > 1 21 > > 1 31 > > 1 41 > > 2 51 > > 1 52 > > 2 61 > > 1 62 > > > > What should the SQL be to display the folders like this: > > > > FolderPARENT_ID > > Folder1 0 > > Folder2 1 > > Folder5 2 > > Folder6 2 > > Folder3 1 > > Folder4 1 > > > > > > RBS > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying hierarchical structure
some relevant links: http://dwhoman.com/blog/sql-transitive-closure.html http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ On Wed, 30 Jan 2019, 4:52 AM Bart Smissaert Working on an Android app and part of that is storing SQL in a virtual > folder system in SQLite. For this I want to use a so-called closure table > as explained nicely here: > > http://technobytz.com/closure_table_store_hierarchical_data.html > > I have a table holder the folder details: > > ID PARENT_ID Folder > > - > 1 0 Folder1 > 2 1 Folder2 > 3 1 Folder3 > 4 1 Folder4 > 5 2 Folder5 > 6 2 Folder6 > > > And then the closure table: > > PARENT_ID CHILD_ID DEPTH > > --- > 1 10 > 2 20 > 3 30 > 4 40 > 5 50 > 6 60 > 1 21 > 1 31 > 1 41 > 2 51 > 1 52 > 2 61 > 1 62 > > What should the SQL be to display the folders like this: > > FolderPARENT_ID > Folder1 0 > Folder2 1 > Folder5 2 > Folder6 2 > Folder3 1 > Folder4 1 > > > RBS > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying hierarchical structure
This looks a nice and simple way to display the tree in the right order
without recursive SQL:
https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
Will do some testing on large numbers to see how the 2 methods compare
speed-wise.
RBS
On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf wrote:
>
> See https://sqlite.org/lang_with.html
>
> which includes how to traverse the recursive tree in either depth-first or
> breadth-first order.
>
> Why do you need the closure table at all?
>
>
> create table folders
> (
>idinteger primary key,
>parent_id integer references folders,
>name text not null collate nocase,
>check (not (parent_id is null and id != 1))
> );
>
> insert into folders values (1, null, 'Folder1'),
>(2, 1, 'Folder2'),
>(3, 1, 'Folder3'),
>(4, 1, 'Folder4'),
>(5, 2, 'Folder5'),
>(6, 2, 'Folder6');
> .head on
> .mode column
> .width 30 9 38
>
> -- depth first
>
> with foo (id, parent_id, name, level, path)
> as (select folders.*, 0, folders.name
> from folders
>where parent_id is null
>union all
> select folders.*, level + 1, foo.path || '\' || folders.name
> from foo, folders
>where folders.parent_id = foo.id
> order by 4
> )
> select substr('', 1, (level - 1) * 4) || name as
> Folder,
>coalesce(parent_id, 0) as PARENT_ID,
>path as FullPath
> from foo;
>
>
> -- breadth first
>
> with foo (id, parent_id, name, level, path)
> as (select folders.*, 0, folders.name
> from folders
>where parent_id is null
>union all
> select folders.*, level + 1, foo.path || '\' || folders.name
> from foo, folders
>where folders.parent_id = foo.id
> order by 4 desc
> )
> select substr('', 1, (level - 1) * 4) || name as
> Folder,
>coalesce(parent_id, 0) as PARENT_ID,
>path as FullPath
> from foo;
>
>
>
> SQLite version 3.27.0 2019-01-28 00:42:06
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table folders
>...> (
>...>idinteger primary key,
>...>parent_id integer references folders,
>...>name text not null collate nocase,
>...>check (not (parent_id is null and id != 1))
>...> );
> sqlite>
> sqlite> insert into folders values (1, null, 'Folder1'),
>...>(2, 1, 'Folder2'),
>...>(3, 1, 'Folder3'),
>...>(4, 1, 'Folder4'),
>...>(5, 2, 'Folder5'),
>...>(6, 2, 'Folder6');
> sqlite> .head on
> sqlite> .mode column
> sqlite> .width 30 9 38
> sqlite>
> sqlite> -- depth first
> sqlite>
> sqlite> with foo (id, parent_id, name, level, path)
>...> as (select folders.*, 0, folders.name
>...> from folders
>...>where parent_id is null
>...>union all
>...> select folders.*, level + 1, foo.path || '\' ||
> folders.name
>...> from foo, folders
>...>where folders.parent_id = foo.id
>...> order by 4
>...> )
>...> select substr('', 1, (level - 1) * 4) || name
> as Folder,
>...>coalesce(parent_id, 0) as PARENT_ID,
>...>path as FullPath
>...> from foo;
> Folder PARENT_ID FullPath
> -- -
> --
> Folder1 0 Folder1
> Folder2 1 Folder1\Folder2
> Folder3 1 Folder1\Folder3
> Folder4 1 Folder1\Folder4
> Folder5 2 Folder1\Folder2\Folder5
> Folder6 2 Folder1\Folder2\Folder6
> sqlite>
> sqlite>
> sqlite> -- breadth first
> sqlite>
> sqlite> with foo (id, parent_id, name, level, path)
>...> as (select folders.*, 0, folders.name
>...> from folders
>...>where parent_id is null
>...>union all
>...> select folders.*, level + 1, foo.path || '\' ||
> folders.name
>...> from foo, folders
>...>where folders.parent_id = foo.id
>...> order by 4 desc
>...> )
>...> select substr('', 1, (level - 1) * 4) || name
> as Folder,
>...>coalesce(parent_id, 0) as PARENT_ID,
>...>path as FullPath
>...> from foo;
> Folder PARENT_ID FullPath
>
