[sqlite] Fwd: How to correctly display unicode characters in Windows 10 / cmd.exe / sqlite3.exe?

[Shane Dev]

Hello,

After logging in to Windows 10, I open a command prompt (cmd.exe) and
change the code page to Unicode (UTF-8)

>chcp 65001
Active code page: 65001

then I test this with a UTF-8 file -

>type utf8test.txt
néo66€

next I execute sqlite-tools-win32-x86-3190200\sqlite3.exe and check the
encoding

sqlite> pragma encoding;
UTF-8

then I try to print some characters to screen -

sqlite> select char(0x006e); --Unicode LATIN SMALL LETTER N
n
sqlite> select char(0x00e9); --Unicode LATIN SMALL LETTER E WITH ACUTE

sqlite> select char(0x006f); --Unicode LATIN SMALL LETTER O
o
sqlite> select char(0x20ac); --Unicode EURO SIGN
?

only the ascii characters are displayed correctly. Next I test outputting
the euro sign to a file -

sqlite> .once eurotest.txt
sqlite> select char(0x20ac); --unicode EURO SIGN
sqlite> .quit

.. and from the command prompt -

>type eurotest.txt
€

Why can't I display these Unicode characters from sqlite3 command line
utility?

P.S I have a similar in powershell but not in Ubuntu.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SPAM] Fwd: How to correctly display unicode characters in Windows 10 / cmd.exe / sqlite3.exe?

[Shane Dev]

Hi David,

I am already using Lucida Console font. Without chcp 65001, sqlite3.exe
will display LATIN SMALL LETTER E WITH ACUTE (U+00E9) correctly. But I
can't figure out how to display EURO SIGN (U+20AC). Lucida Console
definitely has the glyph, otherwise my test "type utf8test.txt" would have
failed. Also REGISTERED SIGN (U+00AE) is displayed as "r" (i.e. not
enclosed in a circle). Simon Slavin pointed out that Windows Console
doesn’t support multibyte characters. Despite that, other console programs
I use are able to display these characters correctly with code page 65001
active - even a simple c program with printf("héllo wo®ld€"); works.

Does anyone know how to display characters like these in the sqlite3.exe
command line utility under Windows 10 (either cmd.exe or powershell.exe)?


On 5 June 2017 at 18:10, David Raymond <david.raym...@tomtom.com> wrote:

> For the command prompt best I can suggest is changing the font to one of
> the TrueType fonts listed there like Lucida Console, then not touching the
> code page. Doing chcp 65001  to help, but then in some release
> something got added to the CLI to try and help with that automatically.
> With the examples you gave I'm still not seeing the Euro sign, but at least
> I'm seeing the é.
>
> I haven't messed with it in a while, but also note that you can
> potentially get issues when using the alt+4numbers to enter something on
> the command line if you've messed with the code page. You can do alt+0233
> to get the é for example, and the glyph on the screen will show the accent,
> and if you do a select statement it will show the same glyph with the
> accent, but the data that actually got inserted was something different.
> The code page just interpreted the "incorrect" raw data to be what you were
> expecting to see. If there's ever a doubt you can use the unicode()
> function to get a code point value. If the console can't display the
> resulting 0-9 numerals ok, then there's a bigger problem to worry about.
>
>
> D:\>chcp
> Active code page: 437
>
> D:\>sqlite3
> SQLite version 3.19.2 2017-05-25 16:50:27
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
>
> sqlite> select char(233);
> char(233)
> é
>
> sqlite> select unicode(char(233));
> unicode(char(233))
> 233
>
> sqlite> select unicode('é');--input with alt+0233
> unicode('é')
> 233
>
> sqlite> .exit
>
> D:\>chcp 65001
> Active code page: 65001
>
> D:\>sqlite3
> SQLite version 3.19.2 2017-05-25 16:50:27
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
>
> sqlite> select char(233);
> char(233)
> �
>
> sqlite> select unicode(char(233));
> unicode(char(233))
> 233
>
> sqlite> select unicode('é');--input with alt+0233, crashes back to command
> prompt
>
>
> D:\>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Shane Dev
> Sent: Sunday, June 04, 2017 4:06 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [SPAM] [sqlite] Fwd: How to correctly display unicode characters
> in Windows 10 / cmd.exe / sqlite3.exe?
> Importance: Low
>
> Hello,
>
> After logging in to Windows 10, I open a command prompt (cmd.exe) and
> change the code page to Unicode (UTF-8)
>
> >chcp 65001
> Active code page: 65001
>
> then I test this with a UTF-8 file -
>
> >type utf8test.txt
> néo66€
>
> next I execute sqlite-tools-win32-x86-3190200\sqlite3.exe and check the
> encoding
>
> sqlite> pragma encoding;
> UTF-8
>
> then I try to print some characters to screen -
>
> sqlite> select char(0x006e); --Unicode LATIN SMALL LETTER N
> n
> sqlite> select char(0x00e9); --Unicode LATIN SMALL LETTER E WITH ACUTE
>
> sqlite> select char(0x006f); --Unicode LATIN SMALL LETTER O
> o
> sqlite> select char(0x20ac); --Unicode EURO SIGN
> ?
>
> only the ascii characters are displayed correctly. Next I test outputting
> the euro sign to a file -
>
> sqlite> .once eurotest.txt
> sqlite> select char(0x20ac); --unicode EURO SIGN
> sqlite> .quit
>
> .. and from the command prompt -
>
> >type eurotest.txt
> €
>
> Why can't I display these Unicode characters from sqlite3 command line
> utility?
>
> P.S I have a similar in powershell but not in Ubuntu.
> ___
> 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

[sqlite] How do I combine these to 2 views ...

[Shane Dev]

Hello,

I am try to combine the following 2 views - vtag and vparent_closetag

sqlite> select id, level, line from vtag;
id|lev|line
id  level   line
1   0   
2   1
3   1
4   2 
5   1
6   2 
7   3  
8   2 

sqlite> select id, level, line from vparent_closetag;
id  level   line
1   0   
3   1
5   1
6   2 

desired result-

order   level   line
1   0   
2   1
3   1
4   2 
5   1
6   1
7   2 
8   3  
9   2 
10  2 
11  1
12  0   

Any ideas to achieve this?

-Shane
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I combine these to 2 views ...

[Shane Dev]

Yes, the parent ID column (par) is available in both views -

sqlite> select id, par, line from vtag;
id  par lev line
1   0   
2   1   1
3   1   1
4   3   2 
5   1   1
6   5   2 
7   6   3  
8   5   2 

sqlite> select id, par, line from vparent_closetag;
id  par lev line
1   0   
3   1   1
5   1   1
6   5   2 

I have tried many different UNION and WITH statements but I can't get the
desired result -

order   level   line
1   0   
2   1
3   1
4   2 
5   1
6   1
7   2 
8   3  
9   2 
10  2 
11  1
12  0   

Any help would be appreciated.

On 26 November 2017 at 11:30, Clemens Ladisch <clem...@ladisch.de> wrote:

> Simon Slavin wrote:
> > On 26 Nov 2017, at 8:02am, Shane Dev <devshan...@gmail.com> wrote:
> >> Any ideas to achieve this?
> >
> > Use the UNION keyword to combine the results of the two SELECT commands
>
> That would not order the close tags correctly.
>
> >> Any ideas to achieve this?
>
> Would it be possible to have parentid fields?
>
> <http://www.sqlite.org/lang_with.html#withorderby>
>
>
> Regards,
> Clemens
> ___
> 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] How do I combine these to 2 views ...

[Shane Dev]

So simple in hindsight, just add a second sort column 'close' to the union
and then traverse the tree - thanks.


On 26 November 2017 at 15:44, Clemens Ladisch <clem...@ladisch.de> wrote:

> Shane Dev wrote:
> > Any ideas to achieve this?
>
> Use another CTE to bring all rows into the correct order.
> Then a standard tree walk:
>
> WITH RECURSIVE
> data AS (
>   SELECT id, line, par, 0 AS close
>   FROM vtag
>   UNION ALL
>   SELECT id, line, par, 1
>   FROM vparent_closetag
>   ORDER BY id, close  -- this relies on the ids being equal
> ),
> t AS (
>   SELECT id, line, 0 AS lev, close
>   FROM data
>   WHERE par IS NULL
>
>   UNION ALL
>
>   SELECT data.id, data.line, t.lev + 1, data.close
>   FROM t
>   JOIN data ON t.id = data.par
>AND t.close = 0
>   ORDER BY lev DESC
> )
> SELECT lev, line FROM t;
>
>
> Regards,
> Clemens
> ___
> 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] how into insert row into middle of table with integer primary key

[Shane Dev]

Hi Igor,

Homework exercise? No, this is purely a hobby project in my free time. My
goal is see how much logic can moved from application code to the database.

Why do I want store ID numbers whose values may change? Why not. Obviously,
this would be bad idea if the ID column was referenced by other column /
table. In that case, I would have created a different table such as

sqlite> .sch fruit
CREATE TABLE fruit(id integer primary key, sort integer unique, name text);

However, this just moves the problem from the id to the sort column. I
still have to consider how to manage changes to values in the sort column.
Apparently there is no single SQL statement which can insert a record in to
any arbitrary sort position. Even if I use the stepped approach (fruit.sort
= 100, 200, 300 ...) or define sort as real unique, I will still need to
determine if it is necessary to reset the gaps between sort column values.
Peter Nichvolodov's trigger solution (
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html)
is elegant, but might be slow if the table had many entries.


On 22 November 2017 at 00:11, Igor Korot  wrote:

> Simon,
>
> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> wrote:
> >
> >
> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> >
> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> >>>
> >>> That assumes you are not starting from an integer part (like 4000) and
> hitting the exact same relative insert spot every time, which /can/ happen,
> but is hugely unlikely.
> >>
> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> say you sort rows by date. You’ve already got some entries from 2015 in
> your database, and some from 2017. Someone now inserts 60 entries from
> 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
> this immediately hits that case.
> >
> > Yes, if you use this method, you do need to renumber them every so
> often.  You assess this when you’re working out (before + after) / 2, and
> you do it using something like the double-UPDATE command someone came up
> with earlier.
> >
> > But that just brings us back to the question of why OP wants to store ID
> numbers which might change.
>
> Homework exercise?
> Stupid requirements?
>
> Thank you.
>
> >
> > Simon.
> > ___
> > 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] Can I recursively concatenate strings?

[Shane Dev]

Perfect! I guessed this could be achieved with a recursive CTE but I could
not find one that would produce my desired view. Your CTE is simply a table
of strings keyed by the length and then you join it with the stringlengths
table to create the final view. Thanks

On 22 November 2017 at 23:55, R Smith <rsm...@rsweb.co.za> wrote:

>
> On 2017/11/22 11:56 PM, Shane Dev wrote:
>
>> Let's say I have a table of stringlengths -
>>
>> sqlite>select * from stringlengths;
>> length
>> 4
>> 1
>> 9
>> ...
>>
>> Can I create a view xstrings containing strings (for example of char 'x')
>> with the lengths specified in stringlengths?
>>
>
> Pretty easily:
>
>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> version 2.0.2.4.
>
>   -- Script Items: 5  Parameter Count: 0
>   -- 2017-11-23 00:53:19.542  |  [Info]   Script Initialized, Started
> executing...
>   -- 
> 
>
>
> CREATE TABLE SL(id INTEGER PRIMARY KEY, sLength INT);
>
> INSERT INTO SL(sLength) VALUES (4),(1),(9),(72),(5);
>
> SELECT * FROM SL;
>
>   --  id  | sLength
>   --  | ---
>   --   1  |4
>   --   2  |1
>   --   3  |9
>   --   4  |72
>   --   5  |5
>
>
> CREATE VIEW xstrings AS
> WITH SB(i,xs) AS (
>   SELECT 0, ''
>   UNION ALL
>   SELECT i+1, xs||'x' FROM SB WHERE i<=(SELECT MAX(sLength) FROM SL)
> )
> SELECT xs
>   FROM SL,SB
>  WHERE SB.i = SL.sLength
>  ORDER BY SL.id
> ;
>
> SELECT * FROM xstrings;
>
>
>   -- xs
>   -- 
> 
>   -- 
>   -- x
>   -- x
>   -- 
> 
>   -- x
>
>   --   Script Stats: Total Script Execution Time: 0d 00h 00m and
> 00.031s
>   -- Total Script Query Time: 0d 00h 00m and
> 00.001s
>   -- Total Database Rows Changed: 5
>   -- Total Virtual-Machine Steps: 2250
>   -- Last executed Item Index:5
>   -- Last Script Error:
>   -- 
> 
>
>
>
> ___
> 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] Can I recursively concatenate strings?

[Shane Dev]

>
>
> PS: If you do like the SQlite features and CTEs (which is one of my
> favourite additions ever), I could post you the CTE example tutorials made
> to accompany an sqlite DB manager (which I made very long ago, after the
> introduction in 3.8 I think) - they have some nifty stuff, like splitting
> CSV data from a column, drawing graphs etc. (or you can just get it from
> sqlitespeed yourself - but it's only windows for now, sadly)
>
> Previously I used SQL for solely for routine data manipulation and
queries. Then I discovered recursive CTEs by accident while reading
wikipedia https://en.wikipedia.org/wiki/Turing_completeness#Examples
"Turing completeness in declarative SQL is implemented through recursive
common table expressions"
That piqued my interest. https://sqlite.org/lang_with.html became one of my
favourite sqlite pages. I would like to see your CTE example tutorials.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Shane Dev]

On 22 November 2017 at 17:08, Igor Korot  wrote:

> Hi, Shane,
>
>
> What I don't understand is why do you need to do that?
>

Imagine I have a GUI element with a drop down list of fruit. The source of
the list is my fruit table and it may have many entries. It might more
convenient to list the popular fruit near the top. In that case the
fruit.sort_order could represent relative popularity of the fruit entries.

Database idea is to store the data and then retrieve them in any way
> you want at any given time.
>
> So all you need to know that there is a record inserted into the table
> "fruit" on the schema
> "garden".
> Then when the time comes by you can retrieve the records with the
> "ORDER BY" clause.
> Whether you will sort the data by alphabet - fruit_name" or by number
> increment - "Sort_order"
> doesn't really matter.
> Inserting the record is an implementation detail which shouldn't
> bother you at all.
>

Actually, it interests me. If I knew insertions and updates in the fruit
table were mostly for unpopular fruits, then  Peter Nichvolodov's trigger
solution (
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html)
might be the best choice. Otherwise, Clemens Ladisch's linked list in SQL
solution (
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106865.html)
might more be efficient. However, querying may be slower.


> Unless you can sow us that the time required to retrieve the
> sorting data will SIGNIICANTLY
> differ in both cases.
>
> I am ready to hear arguments against this approach. ;-)
>
> Thank you.
>
> >
> >
> > On 22 November 2017 at 00:11, Igor Korot  wrote:
> >
> >> Simon,
> >>
> >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> >> wrote:
> >> >
> >> >
> >> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> >> >
> >> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> >> >>>
> >> >>> That assumes you are not starting from an integer part (like 4000)
> and
> >> hitting the exact same relative insert spot every time, which /can/
> happen,
> >> but is hugely unlikely.
> >> >>
> >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> >> say you sort rows by date. You’ve already got some entries from 2015 in
> >> your database, and some from 2017. Someone now inserts 60 entries from
> >> 2016, and to be ‘helpful’, they insert them in chronological order.
> Wham,
> >> this immediately hits that case.
> >> >
> >> > Yes, if you use this method, you do need to renumber them every so
> >> often.  You assess this when you’re working out (before + after) / 2,
> and
> >> you do it using something like the double-UPDATE command someone came up
> >> with earlier.
> >> >
> >> > But that just brings us back to the question of why OP wants to store
> ID
> >> numbers which might change.
> >>
> >> Homework exercise?
> >> Stupid requirements?
> >>
> >> Thank you.
> >>
> >> >
> >> > Simon.
> >> > ___
> >> > 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
> ___
> 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] Can I recursively concatenate strings?

[Shane Dev]

Let's say I have a table of stringlengths -

sqlite>select * from stringlengths;
length
4
1
9
...

Can I create a view xstrings containing strings (for example of char 'x')
with the lengths specified in stringlengths?

desired result -

sqlite>select * from xstrings;
string

x

...

P.S I know that substr('x', 1, stringlengths.length) would work in
this particular case but then I must know maximum value of
stringlengths.length at the point of time when I construct the query. Is
there a more flexible way?


..
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a trigger recursively update a table?

[Shane Dev]

Hi Clemens,

With your solution, how would you define the DELETE ON VHIERARCHY trigger?

On 14 December 2017 at 12:59, Clemens Ladisch <clem...@ladisch.de> wrote:

> Shane Dev wrote:
> > Can we conclude there is no single CTE or other SQL statement which can
> > update a branch of the tree starting with a flexibly specified node?
>
> That should be possible when you enable recursive triggers:
>
>   begin
> update hierarchy set status = null where id = old.id;
> delete from vhierarchy where parent = old.id;
>   end;
>
>
> Regards,
> Clemens
> ___
> 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] Can a trigger recursively update a table?

[Shane Dev]

brilliant! - it works - thanks

On 14 December 2017 at 19:07, Clemens Ladisch <clem...@ladisch.de> wrote:

> Shane Dev wrote:
> > On 14 December 2017 at 12:59, Clemens Ladisch <clem...@ladisch.de>
> wrote:
> >> Shane Dev wrote:
> >>> Can we conclude there is no single CTE or other SQL statement which can
> >>> update a branch of the tree starting with a flexibly specified node?
> >>
> >> That should be possible when you enable recursive triggers:
> >>
> >>   begin
> >> update hierarchy set status = null where id = old.id;
> >> delete from vhierarchy where parent = old.id;
> >>   end;
> >
> > With your solution, how would you define the DELETE ON VHIERARCHY
> trigger?
>
> That is the trigger.
>
>
> Regards,
> Clemens
> ___
> 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] Can a trigger recursively update a table?

[Shane Dev]

Hi,

I have a hierarchical table -

sqlite> .sch hierarchy
CREATE TABLE hierarchy(id integer primary key, parent references hierarchy,
descrip text, status text);

with some entries -

sqlite> select * from hierarchy;
id  parent  descrip status
1   rootopen
2   1   branch1 open
3   1   branch2 open
4   3   branch22open
5   4   branch222   open
6   1   branch3 open

I can clear the status field of the second branch (i.e id = 3, 4 and 5)
with -

with recursive cte(lev, id, parent, status) as (select 0, id, parent,
status from hierarchy where id=3 union all select lev+1, h.id, h.parent,
h.status from hierarchy as h, cte where h.parent=cte.id) update hierarchy
set status=null where id in (select id from cte);

sqlite> select * from hierarchy;
id  parent  descrip status
1   rootopen
2   1   branch1 open
3   1   branch2
4   3   branch22
5   4   branch222
6   1   branch3 open

However, if I try to create a trigger with this statement -

sqlite> create view vhierarchy as select * from hierarchy;
sqlite> create trigger thierarchy instead of delete on vhierarchy begin
with recursive cte(lev, id, parent, status) as (select 0, id, parent,
status from hierarchy where id=old.id union all select lev+1, h.id,
h.parent, h.status from hierarchy as h, cte where h.parent=cte.id) update
hierarchy set status=null where id in (select id from cte); end;

I get an error -

Error: near "update": syntax error

Can anyone see why?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

[Shane Dev]

Nice solution!

CREATE TABLE edges(parent references nodes, child references nodes check
(parent<>child));

seems to be an equivalent but shorter statement.

On 20 December 2017 at 07:49, Simon Slavin  wrote:

>
>
> Yes !
>
> CREATE TABLE edges(
> parent INTEGER references nodes,
> child INTEGER references nodes,
> CONSTRAINT NotMyOwnGrandpa CHECK (child <> parent)
> );
>
> You’ll get a failure result code from the INSERT.
>
> Simon.
> ___
> 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] Can we create a table where the value of one particular column <> another column?

[Shane Dev]

Now I using the following -

CREATE TABLE nodes(id integer primary key, description text);
CREATE TABLE edges(parent references nodes not null, child references nodes
not null check (parent<>child), primary key(parent, child));

This seems to prevent the insertion of duplicate and parent=child records.
I don't see any benefit in adding unique constraints or triggers.

I want to allow a child to have more than one parents. I also created an
index which will hopefully speed up filters on edges.child -

CREATE INDEX iedges on edges(child, parent);

Now I will try to enhance the statement to prevent cycles
https://en.wikipedia.org/wiki/Cycle_(graph_theory)#Cycle_detection




On 20 December 2017 at 08:23, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> And of course you will need an index on edges.parent and one on
> edges.child since you need indexes on foreign keys.  (unless you do not
> intend to enforce them and they are merely for "documentation" of intent to
> perhaps have a consistent database mayhaps perchance).
>
> You will probably also want a unique constraint (index) on edges (parent,
> chaild) so you do not have multiple edges going from the same parent to the
> same child.
>
> You might also need a trigger to make sure that you do not have
> "duplicate" edges if your edges are "bidirectional" rather than directional.
> This will probably require a unique (child, parent) index as well.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> >Sent: Tuesday, 19 December, 2017 23:50
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Can we create a table where the value of one
> >particular column <> another column?
> >
> >
> >
> >On 20 Dec 2017, at 6:30am, Shane Dev <devshan...@gmail.com> wrote:
> >
> >> Let's say we have nodes and edges tables -
> >>
> >> sqlite> .sch nodes
> >> CREATE TABLE nodes(id integer primary key, description text);
> >> sqlite> .sch edges
> >> CREATE TABLE edges(parent references nodes, child references
> >nodes);
> >>
> >> Can we restrict the edges table so that inserting or updating a row
> >where
> >> edges.parent = edges.child is not allowed and ideally would produce
> >an
> >> error message?
> >>
> >> sqlite> insert into nodes select 1, 'node1';
> >> sqlite> insert into nodes select 2, 'node2';
> >> sqlite> insert into edges select 1, 2;
> >> sqlite> insert into edges select 1, 1;
> >> -- should be an error here
> >
> >Yes !
> >
> >CREATE TABLE edges(
> >parent INTEGER references nodes,
> >child INTEGER references nodes,
> >CONSTRAINT NotMyOwnGrandpa CHECK (child <> parent)
> >);
> >
> >You’ll get a failure result code from the INSERT.
> >
> >Simon.
> >___
> >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

[sqlite] Can we create a table where the value of one particular column <> another column?

[Shane Dev]

Let's say we have nodes and edges tables -

sqlite> .sch nodes
CREATE TABLE nodes(id integer primary key, description text);
sqlite> .sch edges
CREATE TABLE edges(parent references nodes, child references nodes);

Can we restrict the edges table so that inserting or updating a row where
edges.parent = edges.child is not allowed and ideally would produce an
error message?

sqlite> insert into nodes select 1, 'node1';
sqlite> insert into nodes select 2, 'node2';
sqlite> insert into edges select 1, 2;
sqlite> insert into edges select 1, 1;
-- should be an error here
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a trigger recursively update a table?

[Shane Dev]

Thanks for your answers, I missed that part of the create trigger
documentation.

Can we conclude there is no single CTE or other SQL statement which can
update a branch of the tree starting with a flexibly specified node?

i.e. I have to "hard-code" the starting node (top of branch) in my CTE like
this -

with recursive cte(lev, id, parent, status) as (select 0, id, parent, status
from hierarchy
-- change the start node in the line below
where id=3
union all select lev+1, h.id, h.parent, h.status from hierarchy as h, cte
where h.parent=cte.id) update
hierarchy set status=null where id in (select id from cte);

On 13 December 2017 at 00:59, J. King <jk...@jkingweb.ca> wrote:

> CTEs cannot be used inside triggers for UPDATE statements. See near the
> bottom of:
> <http://sqlite.org/lang_createtrigger.html>
>
> On December 12, 2017 6:44:35 PM EST, Shane Dev <devshan...@gmail.com>
> wrote:
> >Hi,
> >
> >I have a hierarchical table -
> >
> >sqlite> .sch hierarchy
> >CREATE TABLE hierarchy(id integer primary key, parent references
> >hierarchy,
> >descrip text, status text);
> >
> >with some entries -
> >
> >sqlite> select * from hierarchy;
> >id  parent  descrip status
> >1   rootopen
> >2   1   branch1 open
> >3   1   branch2 open
> >4   3   branch22open
> >5   4   branch222   open
> >6   1   branch3 open
> >
> >I can clear the status field of the second branch (i.e id = 3, 4 and 5)
> >with -
> >
> >with recursive cte(lev, id, parent, status) as (select 0, id, parent,
> >status from hierarchy where id=3 union all select lev+1, h.id,
> >h.parent,
> >h.status from hierarchy as h, cte where h.parent=cte.id) update
> >hierarchy
> >set status=null where id in (select id from cte);
> >
> >sqlite> select * from hierarchy;
> >id  parent  descrip status
> >1   rootopen
> >2   1   branch1 open
> >3   1   branch2
> >4   3   branch22
> >5   4   branch222
> >6   1   branch3 open
> >
> >However, if I try to create a trigger with this statement -
> >
> >sqlite> create view vhierarchy as select * from hierarchy;
> >sqlite> create trigger thierarchy instead of delete on vhierarchy begin
> >with recursive cte(lev, id, parent, status) as (select 0, id, parent,
> >status from hierarchy where id=old.id union all select lev+1, h.id,
> >h.parent, h.status from hierarchy as h, cte where h.parent=cte.id)
> >update
> >hierarchy set status=null where id in (select id from cte); end;
> >
> >I get an error -
> >
> >Error: near "update": syntax error
> >
> >Can anyone see why?
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> ___
> 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] how into insert row into middle of table with integer primary key

[Shane Dev]

Hi Ryan,

Nice trick - changing the sign of ID. I agree that changing an existing
record's ID value would cause problems for any other field / table that
referenced this key.

If I used your idea of adding a SortOrder column incremented in steps of x,
as you suggested, the gaps would start to disappear after many inserts
between existing records. I suppose the gaps could be reset by writing
program to call sqlite3_exec with

select * from fruit order by SortOrder;

and increment a RowCount variable each time the callback was triggered,
then update fruit with something like

update fruit set SortOrder = RowCount*x where id = [id of the row when it's
callback was triggered];

I would to prefer to avoid this solution because it involves mutable state
(the RowCount variable) which is the "root of all evil" (bugs). Is there an
SQL statement which could reset the gaps back to x?


On 20 November 2017 at 17:12, R Smith <rsm...@rsweb.co.za> wrote:

> This question pops up from time to time.
>
> I will show a correct query script to achieve this below, but I want to
> emphasize what others have said: Data in an RDBMS has no intrinsic order,
> it's all SETs, and if you artificially bestow order to the data itself (as
> opposed to the eventual output) then you are doing something that's very
> bad in database design.
>
> To be specific, if the fruit in your DB needs ORDER as a property, best is
> to add a column called  SortOrder or FruitOrder or the like. In this column
> you can then assign the values automatically in steps of 10 or 100, so you
> end up with a table like:
> id  |  fruit  | SortOrder
> 1  |  Apple  |  100
> 2  |  Pear  |  200
> 3  |  Kiwi  |  300  etc...
>
>
> Then inserting:
> INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);
>
> is simply trivial. (The 150 can be computed from splitting the difference
> between the precedent and decedent). Non-Integer is best.
>
> Eventually though, you might need to do maintenance and reset the gaps or
> such.
>
> Anyway, enough preaching - this query script will fix your Situation in
> SQLite very fast:
>
> UPDATE fruit SET id = -id-1 WHERE id >= 2;
> UPDATE fruit SET id = -id WHERE id < 0;
> INSERT INTO fruit (2,'Banana');
>
>
> Another way:
>
> UPDATE fruit SET id = -(id * 100);
> UPDATE fruit SET id = -id WHERE id < 0;
> INSERT INTO fruit (150,'Banana');
>
>
> The reason why this is bad? Mostly a primary Key serves as a lookup for
> other tables linking to a very specific record. Imagine your query that
> added fruit to recipes where needed has the fruit's primary keys shuffled,
> the next day will see some really weird recipes when Banana ends up where
> Pear was intended.  Next you'll want to insert Watermelon...  :)
>
> Cheers,
> Ryan
>
>
> On 2017/11/19 10:37 PM, Shane Dev wrote:
>
>> Let's say I have a table of fruit -
>>
>> sqlite> .sch fruit
>> CREATE TABLE fruit(id integer primary key, name text);
>>
>> with some entries -
>>
>> sqlite> select * from fruit;
>> id|name
>> 1|apple
>> 2|pear
>> 3|kiwi
>>
>> Is there an easy way to insert 'banana' between apple and pear while still
>> maintaining a consistent order of the ID field?
>>
>> desired result -
>>
>> sqlite> select * from fruit;
>> 1|apple
>> 2|banana
>> 3|pear
>> 4|kiwi
>> ___
>> 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

[sqlite] how into insert row into middle of table with integer primary key

[Shane Dev]

Let's say I have a table of fruit -

sqlite> .sch fruit
CREATE TABLE fruit(id integer primary key, name text);

with some entries -

sqlite> select * from fruit;
id|name
1|apple
2|pear
3|kiwi

Is there an easy way to insert 'banana' between apple and pear while still
maintaining a consistent order of the ID field?

desired result -

sqlite> select * from fruit;
1|apple
2|banana
3|pear
4|kiwi
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unexpected result from SELECT * FROM (subquery);

[Shane Dev]

Hello,

In sqlite3, I executed the following statements -

sqlite> select name from tabs where rowid=1;
tab1
sqlite> select * from tab1;
first rec

sqlite> select * from (select name from tabs where rowid=1);
tab1

I expected the last statement to evaluate the subquery first to be 'tab1'
and then execute SELECT * FROM tab1 to yield 'first rec'. Why didn't this
happen?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What is the most flexible way to exact the table name from a SQL statement

[Shane Dev]

Hello,

Let's say I have a table containing of SQL statements, for example

sqlite> .schema sql
CREATE TABLE sql(statement text);

sqlite> select * from sql;
insert into tab1 select 'example text';
update tab2 set col2 = 123 where col2 = 1;
delete from tab3 where col1 = 2;

For the first row, I could build a query using instr and substr functions
to extract the first word after INSERT INTO. That would work for most
simple INSERT statements, but it would fail if (for example) the statement
was prepended with a WITH clause which happened to contain the text "INSERT
INTO". Is there more generalized way of achieving this?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to detect cycles in a hierarchical table?

[Shane Dev]

Hi David,

Yes, parent and child are integers. I hadn't thought of building a string
path, very clever. After a little testing, I have not found a case where
the queries fail.

On 20 December 2017 at 23:18, David Raymond <david.raym...@tomtom.com>
wrote:

> Well, if your parent and child are going to be integers, then you can do
> some magic with strings. (This is with the assumption that an edge can't go
> from and to the same node)
>
> Here's something to get the non-looping paths:
>
> with recursive x (parent, path, child) as (
> select parent, cast(parent as text) || ' => ' || cast(child as text),
> child from edges
> union
> select x.parent, x.path || ' => ' || cast(edges.child as text),
> edges.child from x
> inner join edges on x.child = edges.parent
> where x.path not like ('%' || cast(edges.child as text) || ' => %'))
> select * from x;
>
>
> ...and after a bit of playing around, the loops...
>
>
> with recursive x (parent, path, child) as (
> select parent, cast(parent as text) || ' => ' || cast(child as text),
> child from edges
> union
> select x.parent, x.path || ' => ' || cast(edges.child as text),
> edges.child from x
> inner join edges on x.child = edges.parent
> where x.path not like ('%' || cast(x.child as text) || ' => %'))
> select * from x where parent = child;
>
>
> Let me know if those work ok for you.
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Shane Dev
> Sent: Wednesday, December 20, 2017 4:32 PM
> To: SQLite mailing list
> Subject: [sqlite] How to detect cycles in a hierarchical table?
>
> Hello,
>
> I have an edges table -
>
> sqlite> .sch edges
> CREATE TABLE edges(parent, child);
>
> sqlite> select * from edges;
> parent  child
> 1   2
> 1   3
> 2   4
> 3   1
> 4   5
> 5   2
>
> Here we have two cycles -
>
> 1) 1 => 3 => 1 (length 1)
> 2) 2 => 4 => 5 => 2 (length 3)
>
> Cycles cause recursive common table expression queries to become infinite
> loops. Is there a query which can detect all cycles regardless of length?
> ___
> 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

[sqlite] Can select * from table where not exists (subquery) be optimized?

[Shane Dev]

Hello,

I have a directed acyclic graph defined as follows -

sqlite> .sch
CREATE TABLE nodes(id integer primary key, description text);
CREATE TABLE edges(parent not null references nodes, child not null
references nodes, primary key(parent, child));

Now I want to find the "roots" of the graph - i.e nodes which are not
children of other nodes -

select * from nodes where not exists (select * from edges where child=
nodes.id);

This works but is very slow when there are a million nodes and edges.

Looking at the query plan -

sqlite> explain query plan select * from nodes where not exists (select *
from edges where child=nodes.id);
selectidorder   fromdetail
0   0   0   SCAN TABLE nodes
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   0   SCAN TABLE edges

I thought an index on edges(child) might help

sqlite> CREATE INDEX iedges on edges(child);

but it didn't -

sqlite> explain query plan select * from nodes where not exists (select *
from edges where child=nodes.id);
selectidorder   fromdetail
0   0   0   SCAN TABLE nodes
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   0   SCAN TABLE edges

Is there any way to speed it up?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient query to count number of leaves in a DAG.

[Shane Dev]

Hi David

I recommend using longer names than 1 letter for your aliases, what you
> save in typing you lose a couple times over again when wondering what "r"
> is or why "t" has anything to do with "nodes"
>

Fair enough. I tend to use shorts names to reduce the risk of typos. My
original node table was called "tasks". I tried to simplify the query for
this forum post but neglected to change the alias.

>
> In your CTE you're doing a 3 table join. There's no need to include the
> nodes table in there at all, you can get the node ID from the edge table.
> ...union all select e.child, top from r, edges as e where e.parent = r.id
> )...
>

You're right in this case. My original node table "tasks" had more columns
which I wanted in the final result set.

>
> The big thing though is in the where clause.
> where...and id not in (select parent from edges where parent = id)...
>

That was a sloppy mistake, I changed it to  ..and id not in (select parent
from edges)... but it was still very slow

>
> Old:
> sqlite> explain query plan with recursive r (id, top) as (select id, id
> from nodes union all select t.id, top from nodes as t, edges as e, r
> where e.parent = r.id and t.id = e.child) select top, count(*) from r
> where top != id and id not in (select parent from edges where parent = id)
> group by top;
>

CREATE VIEW v_count_leaves as with recursive r (id, top) as (select id, id
from nodes union all select t.id, top from nodes as t, edges as e, r where
e.parent = r.id and t.id = e.child) select top, count(*) from r where top
!= id and id not in (select parent from edges where parent = id) group by
top;

sqlite> select * from v_count_leaves where top=679;
top count(*)
679 2
Run Time: real 73.365 user 73.328125 sys 0.00


> New:
> sqlite> explain query plan with recursive r (id, top) as (select id, id
> from nodes union all select e.child, top from edges as e, r where e.parent
> = r.id) select top, count(*) from r where top != id and id not in (select
> parent from edges) group by top;
> Now give your modified query a go and let me know how it compares to what
> I came up with.
>

CREATE VIEW v_count_leaves_new as with recursive r (id, top) as (select id,
id from nodes union all select e.child, top from edges as e, r where
e.parent = r.id) select top, count(*) from r where top != id and id not in
(select parent from edges) group by top;

sqlite> select * from v_count_leaves_new where top=679;
top count(*)
679 2
Run Time: real 45.099 user 45.093750 sys 0.00

faster, but about 8 times slower than your query -

sqlite> select * from leafcounts where parent=679;
parent  leafCount
679 2
Run Time: real 5.639 user 5.640625 sys 0.00

and that is without the reverseEdges index.

I still don't understand why "leafcounts" is so much faster than
"v_count_leaves_new"




> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Shane Dev
> Sent: Wednesday, January 03, 2018 12:45 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Efficient query to count number of leaves in a DAG.
>
> Hi David,
>
> Nice work! your query is far quicker than mine- even without the
> reverseEdges index. I think you are right about the problem of potentially
> double counting leaves. There weren't any multi-parent nodes in my test
> data so I didn't notice this mistake.
>
> Could you please explain why your query is so much faster?
>
> On 2 January 2018 at 17:50, David Raymond <david.raym...@tomtom.com>
> wrote:
>
> > I think you need a union there instead of a union all. Otherwise you're
> > double (or more) counting leaves where there is more than 1 path to get
> to
> > the leaf.
> >
> > I don't have a large dataset to test it on, but how about something like:
> >
> > create table nodes
> > (
> >   id integer primary key,
> >   description text
> > );
> >
> > create table edges
> > (
> >   parent int not null references nodes,
> >   child int not null references nodes,
> >   primary key (parent, child),
> >   check (parent != child)
> > ) without rowid;
> > create index reverseEdges on edges (child, parent);
> >
> > create view leafCounts as with recursive
> > leaves (id) as (
> >   select nodes.id
> >   from nodes left outer join edges
> >   on nodes.id = edges.parent
> >   where edges.parent is null
> > ),
> > paths (parent, child) as (
> >   select parent, child from edges
> >   union
> >   select paths.parent, edges.child
> >   from paths inner join edges
> >   on paths.child = edges.parent
> > )
>

Re: [sqlite] Efficient query to count number of leaves in a DAG.

[Shane Dev]

Hi David,

According to https://sqlite.org/lang_with.html, "Optimization note: ...if
the example had used UNION instead of UNION ALL, then SQLite would have had
to keep around all previously generated content in order to check for
duplicates. For this reason, programmers should strive to use UNION ALL
instead of UNION when feasible."

Despite that, your RCTE with UNION is much faster than mine.

sqlite> select count(*) from nodes;
count(*)
1
sqlite> select count(*) from edges;
count(*)
9990

Here is how create my test data -

sqlite> .sch v_generate_nodes
-- Generates an infinite series of x, 'nodex' records where x = 1, 2, 3 ...
CREATE VIEW v_generate_nodes as with recursive rcte(id, description) as
(select 1, 'node1' union all select id+1, 'node'||(id+1) from rcte) select
* from rcte;
sqlite> insert into nodes select from v_generate_nodes limit 1;

sqlite> .sch v_generate_edges
-- Randomly generates edges between entries in the nodes table.
---Assumption : node ids are 1, 2, 3...n without gaps
-- Each node will have 0 or 1 parents and 0, 1, 2, ... children
CREATE VIEW v_generate_edges as with rcte(parent, child) as (select
cast(abs(random())/9223372036854775808 as integer), 1 union all select
cast(abs(random())/9223372036854775808*(child+1) as integer), child+1 from
rcte where child <= (select count(*) from nodes) limit (select count(*)
from nodes)) select * from rcte where parent>0;
sqlite> insert into edges select * from v_generate_edges;



On 5 January 2018 at 18:32, David Raymond <david.raym...@tomtom.com> wrote:

> Hmm. Maybe try yours with union instead of union all? Though if there's
> only 1 path between any pair of nodes that shouldn't make too much
> difference. Otherwise I'm getting low on ideas.
>
> What're the record counts for nodes and edges?
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Shane Dev
> Sent: Thursday, January 04, 2018 5:20 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Efficient query to count number of leaves in a DAG.
>
> Hi David
>
> I recommend using longer names than 1 letter for your aliases, what you
> > save in typing you lose a couple times over again when wondering what "r"
> > is or why "t" has anything to do with "nodes"
> >
>
> Fair enough. I tend to use shorts names to reduce the risk of typos. My
> original node table was called "tasks". I tried to simplify the query for
> this forum post but neglected to change the alias.
>
> >
> > In your CTE you're doing a 3 table join. There's no need to include the
> > nodes table in there at all, you can get the node ID from the edge table.
> > ...union all select e.child, top from r, edges as e where e.parent =
> r.id
> > )...
> >
>
> You're right in this case. My original node table "tasks" had more columns
> which I wanted in the final result set.
>
> >
> > The big thing though is in the where clause.
> > where...and id not in (select parent from edges where parent = id)...
> >
>
> That was a sloppy mistake, I changed it to  ..and id not in (select parent
> from edges)... but it was still very slow
>
> >
> > Old:
> > sqlite> explain query plan with recursive r (id, top) as (select id, id
> > from nodes union all select t.id, top from nodes as t, edges as e, r
> > where e.parent = r.id and t.id = e.child) select top, count(*) from r
> > where top != id and id not in (select parent from edges where parent =
> id)
> > group by top;
> >
>
> CREATE VIEW v_count_leaves as with recursive r (id, top) as (select id, id
> from nodes union all select t.id, top from nodes as t, edges as e, r where
> e.parent = r.id and t.id = e.child) select top, count(*) from r where top
> != id and id not in (select parent from edges where parent = id) group by
> top;
>
> sqlite> select * from v_count_leaves where top=679;
> top count(*)
> 679 2
> Run Time: real 73.365 user 73.328125 sys 0.00
>
>
> > New:
> > sqlite> explain query plan with recursive r (id, top) as (select id, id
> > from nodes union all select e.child, top from edges as e, r where
> e.parent
> > = r.id) select top, count(*) from r where top != id and id not in
> (select
> > parent from edges) group by top;
> > Now give your modified query a go and let me know how it compares to what
> > I came up with.
> >
>
> CREATE VIEW v_count_leaves_new as with recursive r (id, top) as (select id,
> id from nodes union all select e.child, top from edges as e, r where
> e.parent = r.id) select top, count(*) from r where top != id and id not in
> (select parent from edges)

[sqlite] How to prevent the insertion of cycles into a hierarchical table?

[Shane Dev]

Related to my previous question
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg107527.html,
I want to prevent the client from inserting a cycle.

For example -

sqlite> .sch edges
CREATE TABLE edges(parent integer not null, child integer not null,
constraint self_reference check (parent<>child));

sqlite> select * from edges;
parent  child
1   2
1   3
2   4

insert into edges select 2, 5; -- ok
insert into edges select 2, 1; -- should not be allowed.
insert into edges select 4, 1; -- should not be allowed.

Many kinds of insertions can be prevented using triggers. Existing cycles
can be detected using a recurisve common table expression. However, since
CTEs are not supported inside triggers, I assume they can't be used for
this purpose. Is there another way?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to prevent the insertion of cycles into a hierarchical table?

[Shane Dev]

Thanks for the wonderfully simple and concise solution. I see now triggers
do support CTEs if they SELECT a RAISE() function. I never thought of using
a BEFORE trigger.

Fijne kerstdagen

On 24 December 2017 at 17:17, E.Pasma <pasm...@concepts.nl> wrote:

> On 24/12/2017 11:56, Shane Dev wrote:
>
> Related to my previous question
>> https://www.mail-archive.com/sqlite-users@mailinglists.sqlit
>> e.org/msg107527.html,
>> I want to prevent the client from inserting a cycle.
>>
>> For example -
>>
>> sqlite> .sch edges
>> CREATE TABLE edges(parent integer not null, child integer not null,
>> constraint self_reference check (parent<>child));
>>
>> sqlite> select * from edges;
>> parent  child
>> 1   2
>> 1   3
>> 2   4
>>
>> insert into edges select 2, 5; -- ok
>> insert into edges select 2, 1; -- should not be allowed.
>> insert into edges select 4, 1; -- should not be allowed.
>>
>> Many kinds of insertions can be prevented using triggers. Existing cycles
>> can be detected using a recurisve common table expression. However, since
>> CTEs are not supported inside triggers, I assume they can't be used for
>> this purpose. Is there another way?
>>
>>
>
> Sorry for ignoring the two earlier repiies, but it looks that WITH can be
> used inside triggers. Like
>
> create trigger ins_edges before insert on edges
> begin
> with recursive r as (
> select  new.child
> union all
> select  edges.child
> fromr
> joinedges on edges.parent=r.child
> )
> select raise (FAIL, 'example error')
> fromr where child=new.parent;
> end
> ;
>
>
>
> ___
> 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] Efficient query to count number of leaves in a DAG.

[Shane Dev]

Hi David,

I started from scratch with a new database and confirmed your findings -
v_count_leaves_new is actually faster than  leafCounts.

My error in the original database was neglecting to specify type of the
columns in the EDGES table -

CREATE TABLE edges(parent not null references nodes, child not null
references nodes, primary key(parent, child)) without rowid;

I had assumed the EDGES columns would inherit the affinity of the
referenced table key (NODES.id). However after correcting the table
definition to

CREATE TABLE edges(parent *int* not null references nodes, child *int* not
null references nodes, primary key(parent, child)) without rowid;

queries over 1 nodes and edges dropped from 45 to 0.1 sec. A valuable
lesson for me!

On 6 January 2018 at 00:28, David Raymond <david.raym...@tomtom.com> wrote:

> Something is seriously funky here. I'm getting the opposite, where your
> query appears to be going faster than mine. I used your queries there to
> populate nodes and edges, based on 1,000,000 nodes. I even added in the
> extra index which turns out isn't used anyway. With it all in memory my
> version is taking 59 seconds, whereas your _new version is taking 28
> seconds and the old version is only 34. So apparently I should be taking
> query advice from you.
>
> If I change my union into a union all it goes down to 31 seconds, so
> closer to yours.
> If I change your union all into a union the time jumps to 156 seconds.
> I think I was thinking of a graph with possible loops or multiple paths to
> get from A to B, which is why I went with the union.
>
> So my next question is: what SQLite version are you using, and what
> hardware are you on?
>
> Are you query plans looking like what I'm seeing here?
>
> sqlite> select * from v_count_leaves_new where top = 777;
> --EQP-- 3,0,0,SCAN TABLE nodes
> --EQP-- 4,0,1,SCAN TABLE r
> --EQP-- 4,1,0,SEARCH TABLE edges AS e USING COVERING INDEX
> sqlite_autoindex_edges_1 (parent=?)
> --EQP-- 2,0,0,COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
> --EQP-- 1,0,0,SCAN SUBQUERY 2
> --EQP-- 0,0,0,USING INDEX sqlite_autoindex_edges_1 FOR IN-OPERATOR
> --EQP-- 0,0,0,SCAN SUBQUERY 1
> top|count(*)
> 777|314
> Run Time: real 28.502 user 28.454582 sys 0.00
>
> --now with union all
> sqlite> select * from leafCounts2 where parent = 777;
> --EQP-- 3,0,0,SCAN TABLE edges
> --EQP-- 4,0,0,SCAN TABLE paths
> --EQP-- 4,1,1,SEARCH TABLE edges USING COVERING INDEX
> sqlite_autoindex_edges_1 (parent=?)
> --EQP-- 2,0,0,COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
> --EQP-- 1,0,0,SCAN SUBQUERY 2
> --EQP-- 1,0,0,EXECUTE LIST SUBQUERY 5
> --EQP-- 5,0,0,SCAN TABLE nodes
> --EQP-- 5,1,1,SEARCH TABLE edges USING COVERING INDEX
> sqlite_autoindex_edges_1 (parent=?)
> --EQP-- 0,0,0,SCAN SUBQUERY 1
> parent|leafCount
> 777|314
> Run Time: real 31.590 user 31.434202 sys 0.00
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Shane Dev
> Sent: Friday, January 05, 2018 4:57 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Efficient query to count number of leaves in a DAG.
>
> Hi David,
>
> According to https://sqlite.org/lang_with.html, "Optimization note: ...if
> the example had used UNION instead of UNION ALL, then SQLite would have had
> to keep around all previously generated content in order to check for
> duplicates. For this reason, programmers should strive to use UNION ALL
> instead of UNION when feasible."
>
> Despite that, your RCTE with UNION is much faster than mine.
>
> sqlite> select count(*) from nodes;
> count(*)
> 1
> sqlite> select count(*) from edges;
> count(*)
> 9990
>
> Here is how create my test data -
>
> sqlite> .sch v_generate_nodes
> -- Generates an infinite series of x, 'nodex' records where x = 1, 2, 3 ...
> CREATE VIEW v_generate_nodes as with recursive rcte(id, description) as
> (select 1, 'node1' union all select id+1, 'node'||(id+1) from rcte) select
> * from rcte;
> sqlite> insert into nodes select from v_generate_nodes limit 1;
>
> sqlite> .sch v_generate_edges
> -- Randomly generates edges between entries in the nodes table.
> ---Assumption : node ids are 1, 2, 3...n without gaps
> -- Each node will have 0 or 1 parents and 0, 1, 2, ... children
> CREATE VIEW v_generate_edges as with rcte(parent, child) as (select
> cast(abs(random())/9223372036854775808 as integer), 1 union all select
> cast(abs(random())/9223372036854775808*(child+1) as integer), child+1 from
> rcte where child <= (select count(*) from nodes) limit (select count(*)
> from nodes)) select * from rcte where parent>0;
> sqlite> insert into edges select * from v_generate_edges;
>

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

On 8 January 2018 at 09:19, petern  wrote:

> Your inner CTE will have to examine every generated row and count only
> matches toward "running_num".
>
>
Good idea, that works -

sqlite> with r(num, rand, running_num) as (select 1,
cast(round(abs(random())/9223372036854775808) as int), 1 union all select
num+1, cast(round(abs(random())/9223372036854775808) as int), case rand
when 1 then running_num+1 else running_num end from r) select running_num,
num from r where rand=1 limit 3;
running_num num
1   1
2   2
3   6
sqlite>

P.S one reason I am using SQL instead the main application to perform such
calculations is precisely to avoid using variables (and hence the evils of
mutable state). Why do you say it is more efficient?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

Hello,

The view VRAND below generates a series of 3 randomly chosen integers -

CREATE VIEW vrand as with r(num, rand) as (
select 1, cast(round(abs(random())/9223372036854775808) as int)
union all
select num+1, cast(round(abs(random())/9223372036854775808) as int) from r)
select num from r where rand=1 limit 3;

sqlite> select * from vrand;
num
1
2
4

sqlite> select count(*) from vrand;
count(*)
3

Now I would like to add a "running number" column with a result set like -

running_num, num
1, 1
2, 2
3, 4

However, the follow statement seems to trigger an infinite loop -

select (select count(*) from vrand where num <= v.num), num from vrand as v;

How can a running number column be added to this view?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

Hi Ryan,

Do you mean it would be more efficient to generate entries such as these
(with randomly chosen integers and running numbers) in the application and
then committing them to the database with sqlite3_exec or similar?

For a large number of entries, I assumed there would be greater overhead in
preparing and binding the values than both generating and storing them with
a single RCTE.

On 8 January 2018 at 10:23, R Smith <ryansmit...@gmail.com> wrote:

>
> On 2018/01/08 11:17 AM, Shane Dev wrote:
>
>>
>> P.S one reason I am using SQL instead the main application to perform such
>> calculations is precisely to avoid using variables (and hence the evils of
>> mutable state). Why do you say it is more efficient?
>>
>
> Because it is much more efficient, memory used in stead of computations
> through the DB engine. It's even vastly more efficient when you do these
> things in your programming language (Zero parsing to start with)...
>
> That however doesn't mean you should only do it this way. There are
> obviously merits (such as mutable state evility, if there is such a word)
> to using a less efficient method.
>
> ___
> 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] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

On 8 January 2018 at 21:58, Simon Slavin  wrote:
>
>
> num+1, cast(round(abs(random())/9223372036854775808) as int) from
>
> you’ve probably looking at sanity in the rear view mirror.  Suppose
> someone has to read your code and figure out what it’s meant to do.  If you
> expect your code to be read by others, the amount of documentation you'll
> have to write takes longer than writing the software properly.


The statement -

select cast(round(abs(random())/9223372036854775808) as int)

means I want sqlite to toss a virtual coin and return 0 or 1. It looks like
a kludge but I could find neither a simpler nor clearer way of expressing
this, can you?


>
> > For a large number of entries, I assumed there would be greater overhead
> in
> > preparing and binding the values than both generating and storing them
> with
> > a single RCTE.
>
> SQLite is just software written in C.  There’s no reason to believe it’ll
> be any more efficiently than your own C code.  Even your respect for the
> SQLite development team shouldn’t outweigh the advantage of working with
> code you write yourself.
>
>
I think Ryan was suggesting to calculate the entries in the main
application code and then use sqlite to perform millions of inserts - which
would imply a call to sqlite3_exec with an extremely long *sql string
(or sqlite3_prepare_v2()
with millions of call to sqlite3_bind_int64, etc).

Wouldn't it be more efficient call one sqlite3_exec with *sql being a
single RCTE which does all the work? An additional advantage is that it
relies on sqlite's own c code which I would hope is more
thoroughly debugged and portable that my own.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to conditionally insert a record?

[Shane Dev]

Thanks, that works

On 11 January 2018 at 06:40, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 01/11/2018 03:41 AM, Shane Dev wrote:
>
>> Hi Dan,
>>
>> Your statement seems to insert a NULL into max_value
>>
>
> So it does. How about this then:
>
>   INSERT INTO max_value SELECT max FROM (
> SELECT max(value) AS max FROM source_table
>   ) WHERE EXISTS (SELECT 1 FROM source_table);
>
> Dan.
>
>
>
>
>
>> sqlite> delete from source_table;
>> sqlite> delete from max_value;
>> sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
>> EXISTS (SELECT 1 FROM source_table);
>> sqlite> select * from max_value;
>> max_value
>>
>> sqlite>
>>
>> Erik Nelson's solution works -
>>
>> sqlite> delete from max_value;
>> sqlite> delete from source_table;
>> sqlite> insert into max_value select value from source_table order by
>> value
>> desc limit 1;
>> sqlite> select * from max_value;
>> sqlite> insert into source_table select 6;
>> sqlite> insert into max_value select value from source_table order by
>> value
>> desc limit 1;
>> sqlite> select * from max_value;
>> max_value
>> 6
>> sqlite>
>>
>> According to https://www.sqlite.org/lang_aggfunc.html -
>>
>> max(X)
>>
>> The max() aggregate function returns the maximum value of all values in
>> the
>> group. The maximum value is the value that would be returned last in an
>> ORDER BY on the same column. Aggregate max() returns NULL if and only if
>> there are no non-NULL values in the group.
>>
>> despite that -
>>
>> sqlite> delete from source_table;
>> sqlite> select * from source_table order by value;
>> sqlite> select max(value) from source_table;
>> max(value)
>>
>> sqlite>
>>
>> The behavior of SELECT max(X) from an empty table appears to contradict
>> the
>> documentation, or have I misunderstood something?
>>
>>
>>
>>
>> On 10 January 2018 at 19:38, Dan Kennedy <danielk1...@gmail.com> wrote:
>>
>> On 01/10/2018 11:48 PM, Shane Dev wrote:
>>>
>>> Hello,
>>>>
>>>> sqlite> create table source_table(value);
>>>> sqlite> create table max_value(max_value);
>>>> sqlite> insert into max_value select max(value) from source_table;
>>>> sqlite> select * from table_max_value;
>>>> max_value
>>>>
>>>> sqlite>
>>>>
>>>>
>>>> How can the maximum value of column source_table.value be inserted into
>>>> max_value only if there are records in source_table? (If source_table is
>>>> empty, nothing should be inserted into max_value, not even a NULL)
>>>>
>>>> You could add a WHERE clause to your SELECT.
>>>
>>>INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT
>>> 1
>>> FROM src);
>>>
>>> Or similar.
>>>
>>> ___
>>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to conditionally insert a record?

[Shane Dev]

Hi Peter,

Your solution quite simple and obvious in hindsight. Just to be clear - I
am using the sqlite3 shell exclusively at the moment and only I post
questions when I am stuck with a problem or observe behavior which appears
not to be documented. I appreciate the time spent by yourself and other
list members answering my questions.

On 11 January 2018 at 00:21, petern <peter.nichvolo...@gmail.com> wrote:

> Functions and aggregates have to return a scalar value or NULL.  Please
> recall functions as a basic concept from early high school:
> https://en.wikipedia.org/wiki/Function
>
> The query below will never return a NULL max(value) row from the
> empty_table table:
>
> WITH empty_table(value) AS (SELECT 1 WHERE 0) SELECT * FROM (SELECT
> max(value)max_value FROM empty_table) WHERE max_value NOT NULL;
>
> Also, INSERTing zero rows is conditioned by having no rows in the source
> SELECT like the following pattern:
>
> INSERT INTO ...  SELECT ... FROM ... WHERE   rows>
>
> Shane.  I encourage you experiment on your own in the shell of SQLite to
> improve your intuition about SQL.  Everything isn't a special case to be
> learned by rote.   There are are a few general patterns that, once
> mastered, do explain what to expect most of the time.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Wed, Jan 10, 2018 at 1:20 PM, Shane Dev <devshan...@gmail.com> wrote:
>
> > Hi Ryan,
> >
> > Nice! I have never used IGNORE before.
> >
> > Would you agree the documentation is wrong for the case of SELECT max(X)
> > FROM [an empty table or subquery]?
> >
> > max(X)
> >
> > The max() aggregate function returns the maximum value of all values in
> the
> > group. The maximum value is the value that would be returned last in an
> > ORDER BY on the same column. Aggregate max() returns NULL if and only if
> > there are no non-NULL values in the group.
> >
> > https://www.sqlite.org/lang_aggfunc.html
> >
> >
> > On 10 January 2018 at 21:44, R Smith <ryansmit...@gmail.com> wrote:
> >
> > > Perhaps like this:
> > >
> > >   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> > > version 2.0.2.4.
> > >   -- Script Items: 4  Parameter Count: 0
> > >   -- 
> > > 
> > >
> > > create table source_table(value);
> > >
> > > create table max_value(max_value NOT NULL);
> > >
> > > insert OR IGNORE into max_value select max(value) from source_table;
> > >
> > > select * from max_value;
> > >
> > >
> > >   --   Script Stats: Total Script Execution Time: 0d 00h 00m and
> > > 00.031s
> > >
> > >
> > >
> > > On 2018/01/10 6:48 PM, Shane Dev wrote:
> > >
> > >> Hello,
> > >>
> > >> sqlite> create table source_table(value);
> > >> sqlite> create table max_value(max_value);
> > >> sqlite> insert into max_value select max(value) from source_table;
> > >> sqlite> select * from table_max_value;
> > >> max_value
> > >>
> > >> sqlite>
> > >>
> > >>
> > >> How can the maximum value of column source_table.value be inserted
> into
> > >> max_value only if there are records in source_table? (If source_table
> is
> > >> empty, nothing should be inserted into max_value, not even a NULL)
> > >> ___
> > >> 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
> >
> ___
> 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] Is it possible to conditionally insert a record?

[Shane Dev]

Hi Dan,

Your statement seems to insert a NULL into max_value

sqlite> delete from source_table;
sqlite> delete from max_value;
sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
EXISTS (SELECT 1 FROM source_table);
sqlite> select * from max_value;
max_value

sqlite>

Erik Nelson's solution works -

sqlite> delete from max_value;
sqlite> delete from source_table;
sqlite> insert into max_value select value from source_table order by value
desc limit 1;
sqlite> select * from max_value;
sqlite> insert into source_table select 6;
sqlite> insert into max_value select value from source_table order by value
desc limit 1;
sqlite> select * from max_value;
max_value
6
sqlite>

According to https://www.sqlite.org/lang_aggfunc.html -

max(X)

The max() aggregate function returns the maximum value of all values in the
group. The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL if and only if
there are no non-NULL values in the group.

despite that -

sqlite> delete from source_table;
sqlite> select * from source_table order by value;
sqlite> select max(value) from source_table;
max(value)

sqlite>

The behavior of SELECT max(X) from an empty table appears to contradict the
documentation, or have I misunderstood something?




On 10 January 2018 at 19:38, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 01/10/2018 11:48 PM, Shane Dev wrote:
>
>> Hello,
>>
>> sqlite> create table source_table(value);
>> sqlite> create table max_value(max_value);
>> sqlite> insert into max_value select max(value) from source_table;
>> sqlite> select * from table_max_value;
>> max_value
>>
>> sqlite>
>>
>>
>> How can the maximum value of column source_table.value be inserted into
>> max_value only if there are records in source_table? (If source_table is
>> empty, nothing should be inserted into max_value, not even a NULL)
>>
>
> You could add a WHERE clause to your SELECT.
>
>   INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT 1
> FROM src);
>
> Or similar.
>
> ___
> 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] Can error messages be concatenated in the raise function?

[Shane Dev]

Hello,

From the documentation https://www.sqlite.org/syntax/raise-function.html,
it is not clear to me if the 'error-message' must be a fixed string. Is
there a way to raise a concatenated message?

For example, a fix string error message -

CREATE TABLE readonly(num int);
CREATE TRIGGER ti_readonly before insert on readonly begin select
raise(FAIL, 'inserts not allowed in table readonly'); end;

sqlite> insert into readonly select 4;
Error: inserts not allowed in table readonly

Defining an empty table where inserts are prohibited is more or less
pointless but its purpose is only to demonstrate the raise function.

However the following trigger definition with a concatenated error gives me
an error message -

sqlite> drop trigger ti_readonly;
sqlite> CREATE TRIGGER ti_readonly before insert on readonly begin select
raise(FAIL, 'Not allowed to insert '||new.num||' into table readonly'); end;
Error: near "||": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible to conditionally insert a record?

[Shane Dev]

Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to conditionally insert a record?

[Shane Dev]

Hi Ryan,

Nice! I have never used IGNORE before.

Would you agree the documentation is wrong for the case of SELECT max(X)
FROM [an empty table or subquery]?

max(X)

The max() aggregate function returns the maximum value of all values in the
group. The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL if and only if
there are no non-NULL values in the group.

https://www.sqlite.org/lang_aggfunc.html


On 10 January 2018 at 21:44, R Smith <ryansmit...@gmail.com> wrote:

> Perhaps like this:
>
>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> version 2.0.2.4.
>   -- Script Items: 4  Parameter Count: 0
>   -- 
> 
>
> create table source_table(value);
>
> create table max_value(max_value NOT NULL);
>
> insert OR IGNORE into max_value select max(value) from source_table;
>
> select * from max_value;
>
>
>   --   Script Stats: Total Script Execution Time: 0d 00h 00m and
> 00.031s
>
>
>
> On 2018/01/10 6:48 PM, Shane Dev wrote:
>
>> Hello,
>>
>> sqlite> create table source_table(value);
>> sqlite> create table max_value(max_value);
>> sqlite> insert into max_value select max(value) from source_table;
>> sqlite> select * from table_max_value;
>> max_value
>>
>> sqlite>
>>
>>
>> How can the maximum value of column source_table.value be inserted into
>> max_value only if there are records in source_table? (If source_table is
>> empty, nothing should be inserted into max_value, not even a NULL)
>> ___
>> 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] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

Thanks Donald. So simple in hindsight

On 8 January 2018 at 23:20, Donald Griggs  wrote:

>
>  select random() > 0;  -- random zero or one
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

Thanks Peter,

That saved me hours of work.

According to the comments -

/* Read a single field of CSV text.  Compatible with rfc4180 and extended
** with the option of having a separator other than ",".

I tried -

sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv');

where test.tsv is a tab separated table. However

select count(*) from t1;

goes into an infinite loop. Do you how to specify a separator other than
","?



On 16 January 2018 at 17:29, petern <peter.nichvolo...@gmail.com> wrote:

> FYI. csv.c is already a separate C program which imports CSV files without
> necessity of the SQLite shell:
>
> https://sqlite.org/csv.html
>
> On Tue, Jan 16, 2018 at 12:47 AM, Shane Dev <devshan...@gmail.com> wrote:
>
> > Hi,
> >
> > I am looking for an efficient way to write a c program which performs the
> > same function as the SQLite shell command ".import"
> >
> > My initial strategy is to include the sqlite library source files and
> copy
> > the control block from shell.c that begins after
> >
> > if( c=='i' && strncmp(azArg[0], "import", n)==0 ){
> >
> > up to and the line -
> >
> > if( needCommit ) sqlite3_exec(p->db, "COMMIT", 0, 0, 0);
> >
> > (i.e lines 5858-6052 in version sqlite-amalgamation-321 of shell.c )
> >
> > Is this a reasonable approach? Is there a better way?
> > ___
> > 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

[sqlite] Can a record count column be added to this random hierarchical view?

[Shane Dev]

Hello,

Here is a view which assigns randomly chosen parents to a sequence of
children -

CREATE VIEW v_random_hierarchy as with r(parent, child) as (select null, 1
union all select abs(random())%child+1, child+1 from r) select * from r
limit 5;

sqlite> select * from v_random_hierarchy;
parent  child
1
1   2
1   3
1   4
4   5

Can a column be added to this view which counts the number of children for
each parent?

Here is my failed attempt -

sqlite> select v1.parent, v1.child, (select count(*) from
v_random_hierarchy as v2 where v2.parent=v1.parent) as
number_children_of_parent from v_random_hierarchy as v1;
parent  child   number_children_of_parent
1   0
1   2   3
2   3   0
3   4   0
3   5   1

in this case, it should be -

parent  child   number_children_of_parent
1   1
1   2   1
2   3   1
3   4   2
3   5   2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

Hi,

I am looking for an efficient way to write a c program which performs the
same function as the SQLite shell command ".import"

My initial strategy is to include the sqlite library source files and copy
the control block from shell.c that begins after

if( c=='i' && strncmp(azArg[0], "import", n)==0 ){

up to and the line -

if( needCommit ) sqlite3_exec(p->db, "COMMIT", 0, 0, 0);

(i.e lines 5858-6052 in version sqlite-amalgamation-321 of shell.c )

Is this a reasonable approach? Is there a better way?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Simon,

I have found a way achieve this purely in the SQLite shell. The trick is to
make all rows in tcout1 SQL statements and then execute them.

sqlite> CREATE TABLE tcout1(sql text);
sqlite> CREATE TABLE tcout2(sql text);
sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
'.headers off';";
sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
'.once tc'||strftime('%s','now');";
sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
'select * from tc;';";
sqlite> .once tcout1.sql
sqlite> select * from tcout1;
sqlite> .read tcout1.sql
sqlite> select * from tcout2;
.headers off
.once tc1515968593
select * from tc;


On 13 January 2018 at 19:57, Simon Slavin <slav...@bigfraud.org> wrote:

> On 13 Jan 2018, at 6:48pm, Shane Dev <devshan...@gmail.com> wrote:
>
> > Is there a way to execute the contents of certain rows (the second row in
> > this example) and replace it with its own result to create second table /
> > view which could interpreted by the sqlite shell?
>
> Not inside the SQLite shell.
>
> Looks like you need to learn programming.  Or at least how to script your
> OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which shell
> are you using ?
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[Shane Dev]

Interesting. SQLite is written in ANSI C. Objective-C is a strict superset
of ANSI C. Objective-C can be used to write software for OS X and iOS.

Did the Apple engineers tell you why it is not possible to compile and run
the SQLite shell on iOS?

On 15 January 2018 at 02:16, Richard Hipp  wrote:

> On 1/14/18, Simon Slavin  wrote:
> >
> > I’ve never seen anyone compile sqlite3 (the shell tool) for iOS.
> Android I
> > have even less idea about.  Can you run command-line tools on Android ?
> Has
> > anyone compiled sqlite3 for it ?
> >
>
> You can easily compile and run all of the SQLite command-line tools
> for Android.  There are simple instructions at the bottom of the
> https://www.sqlite.org/fasterthanfs.html document.
>
> The engineers at Apple tell me this is not possible on iOS.
> --
> 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] Defect: Redundant CTE table materialization gives wrong answer.

[Shane Dev]

The following statement executes the random() function twice -

sqlite> select random() union all select random();
random()
2678358683566407062
-5528866137931448843
sqlite> explain select random() union all select random();
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 1 000
1 Function0  0 0 1 random(0)  00
2 ResultRow  1 1 000
3 Function0  0 0 1 random(0)  00
4 ResultRow  1 1 000
5 Halt   0 0 000

but if random() is replaced with round(1.1) -

sqlite> select round(1.1) union all select round(1.1);
round(1.1)
1.0
1.0
sqlite> explain select round(1.1) union all select round(1.1);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 6 000
1 Copy   2 1 000
2 ResultRow  1 1 000
3 Copy   2 1 000
4 ResultRow  1 1 000
5 Halt   0 0 000
6 Real   0 3 0 1.100
7 Function0  1 3 2 round(1)   01
8 Goto   0 1 000

It seems to be executed once only. Does this happen because random() is
flagged non-deterministic?


On 19 January 2018 at 09:10, Clemens Ladisch  wrote:

> petern wrote:
> > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> > sum(s),"(SELECT sum(s) FROM flips)"
> > 1,3
> > --Expected output is 1,1.
> >
> > Why isn't the constant notional table table [flips] materialized just
> once
> > per CTE?
> >
> > FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users
>
> Its documentation says:
> 
> | A useful property of WITH queries is that they are evaluated only once
> | per execution of the parent query, even if they are referred to more
> | than once by the parent query or sibling WITH queries. Thus, expensive
> | calculations that are needed in multiple places can be placed within
> | a WITH query to avoid redundant work. Another possible application is
> | to prevent unwanted multiple evaluations of functions with side-
> | effects. However, the other side of this coin is that the optimizer is
> | less able to push restrictions from the parent query down into a WITH
> | query than an ordinary subquery.
>
> This is an implementation detail of Postgres, and it is not required by
> the SQL specification.  SQLite chose the other side of the coin.
>
>
> Regards,
> Clemens
> ___
> 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] Defect: Redundant CTE table materialization gives wrong answer.

[Shane Dev]

Good question

On 19 January 2018 at 06:04, petern  wrote:

> WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> sum(s),"(SELECT sum(s) FROM flips)"
> 1,3
> --Expected output is 1,1.
>
> Why isn't the constant notional table table [flips] materialized just once
> per CTE?
>
> FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE:
>
> WITH flips(s) AS (VALUES (random()), (random()), (random()))
> SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> sum sum
> 1.503042308613658 1.503042308613658
>
> Also FYI, double checked:  random() is indeed run 3 times by PostgreSQL.
>
> WITH flips(s) AS (VALUES (random()), (random()), (random()))
> SELECT * FROM flips;
>
> s
> 0.760850821621716
> 0.9941047639586031
> 0.48273737309500575
>
> Peter
> ___
> 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] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Shane Dev]

Hello,

The following SQL works as I expect -

sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
child));
sqlite> insert into edges select null, 1;
sqlite> select * from edges;
parent  child
1
sqlite>

but if I remove the superfluous rowid column from the table definition -

sqlite> drop table edges;
sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
child)) without rowid;
sqlite> insert into edges select null, 1;
Error: NOT NULL constraint failed: edges.parent
sqlite>

Why do I get this error?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Shane Dev]

I missed that part of the documentation, thanks

On 19 January 2018 at 07:51, Rowan Worth <row...@dug.com> wrote:

> https://www.sqlite.org/withoutrowid.html
>
> "NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID
> table."
>
> It goes on to say that NOT NULL is supposed to be enforced on all PRIMARY
> KEY columns of _every_ table according to the SQL standard, but an early
> version of sqlite included a bug which allowed NULLs and as a result sqlite
> does not enforce this for ROWID tables.
>
> -Rowan
>
> On 19 January 2018 at 14:32, Shane Dev <devshan...@gmail.com> wrote:
>
> > Hello,
> >
> > The following SQL works as I expect -
> >
> > sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> > child));
> > sqlite> insert into edges select null, 1;
> > sqlite> select * from edges;
> > parent  child
> > 1
> > sqlite>
> >
> > but if I remove the superfluous rowid column from the table definition -
> >
> > sqlite> drop table edges;
> > sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> > child)) without rowid;
> > sqlite> insert into edges select null, 1;
> > Error: NOT NULL constraint failed: edges.parent
> > sqlite>
> >
> > Why do I get this error?
> > ___
> > 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] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

Apparently the CSV virtual table supports neither changes (INSERT, UPDATE,
DELETE), nor reading single column csv files.

What I really want is the functionality of .import and .output SQLite shell
commands. Maybe a better strategy would be to compile shell.c with my c
program and call the function do_meta_command(char *zLine, ShellState *p).

To those familiar with shell.c, is this a reasonable approach?

On 17 January 2018 at 00:15, Richard Hipp <d...@sqlite.org> wrote:

> On 1/16/18, Shane Dev <devshan...@gmail.com> wrote:
> > I tried -
> >
> > sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv');
> >
> > where test.tsv is a tab separated table. However
> >
> > select count(*) from t1;
> >
> > goes into an infinite loop. Do you how to specify a separator other than
> > ","?
>
> The "C" in CSV stands for "Comma".  That is the only separator
> supported.  But, you can probably edit the source code to do something
> different.
>
> --
> 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] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

In my day job, I am an SAP consultant - for over 20 years. Production
quality code? Yes, but only within the companies where I have worked - tax,
banking, inventory, procurement, sales, etc.

My interest in SQLite is a personal hobby project at the moment. I have a
couple of ideas for end user applications - a game (tentatively called
"Canibal Ants") and a planning tool. Both of them would modeled with graphs
(as in graph theory). Given the choice of complex core application code or
a complex DB schema, I prefer the latter.

At this stage, I am trying to understand the strategies used by experienced
SQLite library users to solve common programming problems. I will
investigate the shell_callback function.

On 17 January 2018 at 19:21, petern <peter.nichvolo...@gmail.com> wrote:

> Take a look at the function shell_callback for hints. See the MODE_Csv
> case.
> You could start by cribbing the functions MODE_Csv uses for your own row
> handler and then see what you'll have to figure out yourself.
>
> Typically, if you are a serious product developer at a frontier in the
> market, you will have to fairly expertly code and deploy your own
> program(s) for every target platform on which you want your application to
> get off the ground.  If what you're doing is worthwhile, then you will have
> to somehow develop the code to make it happen.  That's the development
> process.
>
> Let me ask some questions anybody reading your posts is definitely
> wondering about.
>
> What is your background?  Have you done production quality software
> development work before?
>
> Is your application worthwhile?  If you can say, what does your application
> do for the end user that they couldn't do without it?
>
>
>
>
>
>
>
>
> On Wed, Jan 17, 2018 at 2:54 AM, Shane Dev <devshan...@gmail.com> wrote:
>
> > On 17 January 2018 at 08:45, petern <peter.nichvolo...@gmail.com> wrote:
> >
> > > Shane. Expect to do a lot of hacking on shell.c.  It's not intended as
> a
> > > library but as the main program of a console application.
> >
> >
> > That's a shame. I try very hard not to reinvent the wheel especially when
> > the wheel question (shell.c) is widely used, flexible and presumably
> > thoroughly debugged.
> >
> > However, I can't be the only one trying to programmatically exchange data
> > between SQLite and a delimited text file. For importing, the CSV virtual
> > table works well for multi-column CSVs, thanks again for the tip. For
> > exporting, I could retrieve the data using sqlite3_exec and build a
> string
> > from the 3rd and 4th parameters of the callback function. Then I would
> need
> > to code logic to insert the column and line separators and handle edge
> > cases (fields containing separators or double quotes, single column
> tables,
> > etc) and finally write the string to a file.
> >
> > Is this most efficient approach?
> > ___
> > 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] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

>  Any practical realtime video game using SQLite is probably
> doing so only to save and restore the game board between games.


and perhaps calculating the initial "maze" or other non time sensitive data
processing


> Even a cursory look into production
> quality video game development will tell you that a database is the wrong
> technology to base a video game engine on.
>
>
Fully agree, I would use another library for that part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Peter,

Ideally, I would like to execute a series of "test cases" from within the
SQLite shell without dependency on an external scripting language. These
would not normally be executed by the application end user. Initially, I
planned to implement this with triggers but I see now that the results of
executing a given statement from within a trigger can be different from
executing the same statement via the SQLite shell (which I assume is
calling sqlite3_step or sqlite3_exec)

For conditional logic, case expressions are currently sufficient for my
needs. So far, I have not needed to execute a script periodically but it
might useful in the future.

Are you the developer / maintainer of the SQLite shell?

On 15 January 2018 at 01:30, petern <peter.nichvolo...@gmail.com> wrote:

> Shane.  That's very interesting considering the effort to make the one
> thing happen exactly once without external software dependency.
> Does the capability to write specially named local files but not have a
> periodic loop nor network capability somehow get your application off the
> ground?
> Based on your problem statement, the user would have to initiate your
> script and know when/if it is required to be run...
>
> I had in mind adding periodic and conditional dot commands to SQLite shell
> - to simulate continuous operation of application code.  If you've figured
> out a way around needing such things to make a useful standalone SQLite
> application, I would be very interested to understand how that works.
>
> Peter
>
>
>
>
>
> On Sun, Jan 14, 2018 at 2:33 PM, Shane Dev <devshan...@gmail.com> wrote:
>
> > Hi Simon,
> >
> > I have found a way achieve this purely in the SQLite shell. The trick is
> to
> > make all rows in tcout1 SQL statements and then execute them.
> >
> > sqlite> CREATE TABLE tcout1(sql text);
> > sqlite> CREATE TABLE tcout2(sql text);
> > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > '.headers off';";
> > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > '.once tc'||strftime('%s','now');";
> > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > 'select * from tc;';";
> > sqlite> .once tcout1.sql
> > sqlite> select * from tcout1;
> > sqlite> .read tcout1.sql
> > sqlite> select * from tcout2;
> > .headers off
> > .once tc1515968593
> > select * from tc;
> >
> >
> > On 13 January 2018 at 19:57, Simon Slavin <slav...@bigfraud.org> wrote:
> >
> > > On 13 Jan 2018, at 6:48pm, Shane Dev <devshan...@gmail.com> wrote:
> > >
> > > > Is there a way to execute the contents of certain rows (the second
> row
> > in
> > > > this example) and replace it with its own result to create second
> > table /
> > > > view which could interpreted by the sqlite shell?
> > >
> > > Not inside the SQLite shell.
> > >
> > > Looks like you need to learn programming.  Or at least how to script
> your
> > > OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which
> shell
> > > are you using ?
> > >
> > > Simon.
> > > ___
> > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Simion,

What do you mean by 'indirect phase'?

The results of execution -

sqlite> select '.once tc'||strftime('%s','now');
.once tc1515872821
sqlite>

obviously the numeric part of the file name will change depending on the
time of statement execution - or do I misunderstand your question?


On 13 January 2018 at 20:40, Simon Slavin <slav...@bigfraud.org> wrote:

>
>
> On 13 Jan 2018, at 7:33pm, Shane Dev <devshan...@gmail.com> wrote:
>
> > I use mainly Linux (bash) and Windows (powershell) but my target
> platforms
> > also include Android, iOS, IoT (anything that can link against the sqlite
> > library). At this stage, I am trying to prototype as much as possible
> using
> > only the SQLite shell.
>
> Your theory is sound.  You should be able to build a SQL command using the
> results from another SQL command.  But there must be a way to express your
> SELECT in a way which doesn’t require the indirect phase, and your command
> looks especially simple.
>
> Can you post what the results of
>
> select '.once tc'||strftime('%s','now');
>
> are expected to look like ?
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[Shane Dev]

Yes, I want to create a timestamp in the file name. My goal is to test the
prototype on at least Windows, Linux, iOS and Android. Of course this kind
of string building is easily done in bash, powershell, python, etc but no
single scripting environment is available on every target platform. It
could be done in C/C++ on every target platform but I was hoping to avoid
the complexities of the compiler toolchain and system programming languages
at this stage.

On 13 January 2018 at 21:09, Simon Slavin <slav...@bigfraud.org> wrote:

>
>
> On 13 Jan 2018, at 7:54pm, Shane Dev <devshan...@gmail.com> wrote:
>
> > What do you mean by 'indirect phase'?
>
> Having to execute a command to find the command you want to execute.
>
> > The results of execution -
> >
> > sqlite> select '.once tc'||strftime('%s','now');
> > .once tc1515872821
> > sqlite>
> >
> > obviously the numeric part of the file name will change depending on the
> > time of statement execution - or do I misunderstand your question?
>
> So the purpose of this is to find compose a filename which includes a
> timestamp ?
>
> For prototype purposes you should be able to do this in whatever shell
> you’re using to run the SQLite shell tool.  For real project uses you
> should be doing it in whatever language your programming in, of course.
>
> You can’t use the SQLite shell tool for real project purposes on multiple
> platforms.  It won’t run on many IoT devices, of course.
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[Shane Dev]

Hello,

I have a table of dot commands and SQL -

sqlite> select sql from tcout1;
sql
.headers off
select '.once tc'||strftime('%s','now'); --first execute this SQL statement
and replace this line with its own result
select * from tc;

Is there a way to execute the contents of certain rows (the second row in
this example) and replace it with its own result to create second table /
view which could interpreted by the sqlite shell?

sqlite> select sql from tcout2;
sql
.headers off
.once tc1515867712
select * from tc;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Simon,

I use mainly Linux (bash) and Windows (powershell) but my target platforms
also include Android, iOS, IoT (anything that can link against the sqlite
library). At this stage, I am trying to prototype as much as possible using
only the SQLite shell.



On 13 January 2018 at 19:57, Simon Slavin <slav...@bigfraud.org> wrote:

> On 13 Jan 2018, at 6:48pm, Shane Dev <devshan...@gmail.com> wrote:
>
> > Is there a way to execute the contents of certain rows (the second row in
> > this example) and replace it with its own result to create second table /
> > view which could interpreted by the sqlite shell?
>
> Not inside the SQLite shell.
>
> Looks like you need to learn programming.  Or at least how to script your
> OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which shell
> are you using ?
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[Shane Dev]

Sorry, false alarm, the text is correct

On 15 January 2018 at 13:36, Shane Dev <devshan...@gmail.com> wrote:

> Hi Simon,
>
> .selftest looks interesting
>
> I think there is a typo in section 13 -
>
> The .selftest command reads the rows of the selftest table in selftest.tno
> order.
>
> On 15 January 2018 at 12:06, Simon Slavin <slav...@bigfraud.org> wrote:
>
>>
>>
>> On 15 Jan 2018, at 10:08am, Shane Dev <devshan...@gmail.com> wrote:
>>
>> > Ideally, I would like to execute a series of "test cases" from within
>> the
>> > SQLite shell without dependency on an external scripting language. These
>> > would not normally be executed by the application end user.
>>
>> Commands suited to that were added in a recent update to the shell tool.
>> Documentation is here:
>>
>> <https://sqlite.org/cli.html>
>>
>> See especially
>>
>> .check
>> .selftest
>> .sha3sum
>> .testcase
>>
>> and section 13.
>>
>> Simon.
>> ___
>> 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] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Simon,

.selftest looks interesting

I think there is a typo in section 13 -

The .selftest command reads the rows of the selftest table in selftest.tno
order.

On 15 January 2018 at 12:06, Simon Slavin <slav...@bigfraud.org> wrote:

>
>
> On 15 Jan 2018, at 10:08am, Shane Dev <devshan...@gmail.com> wrote:
>
> > Ideally, I would like to execute a series of "test cases" from within the
> > SQLite shell without dependency on an external scripting language. These
> > would not normally be executed by the application end user.
>
> Commands suited to that were added in a recent update to the shell tool.
> Documentation is here:
>
> <https://sqlite.org/cli.html>
>
> See especially
>
> .check
> .selftest
> .sha3sum
> .testcase
>
> and section 13.
>
> Simon.
> ___
> 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] Is foreign key support disabled within triggers with raise functions?

[Shane Dev]

Hello,

Perhaps it would be clearer if I ask the question in a different way. Why
does the following statement -

insert into vtrig select 5;

fail to insert a record in Test 1 below (as expected) but succeeds in Test
2 (despite the foreign key constraint)?

sqlite> CREATE TABLE reftab(id integer primary key);
sqlite> CREATE TABLE deptab(id integer primary key, ref int references
reftab);
sqlite> pragma foreign_keys;
foreign_keys
1
sqlite> CREATE VIEW vtrig as select 1;

Test 1
=

sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
   ...> insert into deptab(ref) select 2;
   ...> end;
sqlite> insert into vtrig select 5;
Error: FOREIGN KEY constraint failed
sqlite> select * from deptab;
sqlite>


Test 2
=

sqlite> drop trigger ttrig;
sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
   ...> insert into deptab(ref) select 2;
   ...> select raise(FAIL, 'this statement seems to temporarily disable
foreign
   ...> support');
   ...> end;
sqlite> insert into vtrig select 5;
Error: this statement seems to temporarily disable foreign
support
sqlite> select * from deptab;
id  ref
1   2


On 12 January 2018 at 00:48, Richard Hipp <d...@sqlite.org> wrote:

> On 1/11/18, Shane Dev <devshan...@gmail.com> wrote:
> >
> > CREATE VIEW vtrig as select 1;
> > CREATE TRIGGER ttrig instead of insert on vtrig begin
> >   delete from deptab;
> >   delete from reftab;
> >   insert into deptab(ref) select 2;
> >   select raise(FAIL, 'this statement seems to temporarily disable foreign
> > support') where (select count(*) > 0 from deptab);
> > end;
> >
> > sqlite> insert into vtrig select 5;
> > Error: this statement seems to temporarily disable foreign support
> > sqlite> select * from reftab;
> > sqlite> select * from deptab;
> > id  ref
> > 1   2
> > sqlite> pragma foreign_keys;
> > foreign_keys
> > 1
> >
> > Can we conclude foreign key support is disabled within triggers with
> raise
> > functions?
>
> I don't what you mean by "disabled".  When you hit a raise(FAIL)
> SQLite stops whatever it was doing and leave the database in whatever
> half-way completed state it was in at the moment.  That's what
> raise(FAIL) is suppose to do.
>
> Did you mean to do "raise(ABORT)" instead, which should do what I
> think you are trying to accomplish.
>
> --
> 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

[sqlite] Is foreign key support disabled within triggers with raise functions?

[Shane Dev]

Hello,

Table deptab has a foreign key relationship with table reftab -

sqlite> .sch
CREATE TABLE reftab(id integer primary key);
CREATE TABLE deptab(id integer primary key, ref int references reftab);

foreign key support is enabled -

sqlite> pragma foreign_keys;
foreign_keys
1

the referenced table is empty -

sqlite> select * from reftab;
sqlite>

so the following insert fails and deptab remains empty as expected -

sqlite> insert into deptab(ref) select 1;
Error: FOREIGN KEY constraint failed
sqlite> select * from deptab;
sqlite>

Now I define the following view and trigger -

CREATE VIEW vtrig as select 1;
CREATE TRIGGER ttrig instead of insert on vtrig begin
  delete from deptab;
  delete from reftab;
  insert into deptab(ref) select 2;
  select raise(FAIL, 'this statement seems to temporarily disable foreign
support') where (select count(*) > 0 from deptab);
end;

sqlite> insert into vtrig select 5;
Error: this statement seems to temporarily disable foreign support
sqlite> select * from reftab;
sqlite> select * from deptab;
id  ref
1   2
sqlite> pragma foreign_keys;
foreign_keys
1

Can we conclude foreign key support is disabled within triggers with raise
functions?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient query to count number of leaves in a DAG.

[Shane Dev]

Hi David,

Nice work! your query is far quicker than mine- even without the
reverseEdges index. I think you are right about the problem of potentially
double counting leaves. There weren't any multi-parent nodes in my test
data so I didn't notice this mistake.

Could you please explain why your query is so much faster?

On 2 January 2018 at 17:50, David Raymond <david.raym...@tomtom.com> wrote:

> I think you need a union there instead of a union all. Otherwise you're
> double (or more) counting leaves where there is more than 1 path to get to
> the leaf.
>
> I don't have a large dataset to test it on, but how about something like:
>
> create table nodes
> (
>   id integer primary key,
>   description text
> );
>
> create table edges
> (
>   parent int not null references nodes,
>   child int not null references nodes,
>   primary key (parent, child),
>   check (parent != child)
> ) without rowid;
> create index reverseEdges on edges (child, parent);
>
> create view leafCounts as with recursive
> leaves (id) as (
>   select nodes.id
>   from nodes left outer join edges
>   on nodes.id = edges.parent
>   where edges.parent is null
> ),
> paths (parent, child) as (
>   select parent, child from edges
>   union
>   select paths.parent, edges.child
>   from paths inner join edges
>   on paths.child = edges.parent
> )
> select parent, count(*) as leafCount
> from paths
> where child in leaves
> group by parent;
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Shane Dev
> Sent: Monday, January 01, 2018 11:14 AM
> To: SQLite mailing list
> Subject: [sqlite] Efficient query to count number of leaves in a DAG.
>
> Hi,
>
> I want to the count the number of leaves (descendants without children) for
> each node in a DAG
>
> DAG definition -
>
> CREATE TABLE nodes(id integer primary key, description text);
> CREATE TABLE edges(parent not null references nodes, child not null
> references nodes, primary key(parent, child));
>
> My query -
>
> CREATE VIEW v_count_leaves as with recursive r(id, top) as (
> select id, id from nodes
> union all
> select t.id, top from nodes as t, edges as e, r where e.parent=r.id and
> t.id
> =e.child)
> select top, count(*) from r where top<>id and id not in (select parent from
> edges where parent=id) group by top;
>
> It seems to work but is complex to understand and debug despite my aim to
> keep it simple as possible, but more importantly - it is very slow when
> there are more than a few thousand nodes and edges.
>
> It there a more efficient (and ideally simpler) way?
> ___
> 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

[sqlite] Can a SELECT statement be used within a trigger?

[Shane Dev]

Hello

The syntax diagram at the top of
https://www.sqlite.org/lang_createtrigger.html implies a SELECT statement
can be used between the BEGIN and END key words.

For example -

sqlite> CREATE TABLE stuff(thing text);
sqlite> CREATE VIEW vstuff as select * from stuff;
sqlite> CREATE TRIGGER tstuff instead of insert on vstuff begin insert into
stuff select new.thing; select * from stuff; end;
sqlite> insert into vstuff select 'object';
-- no output
sqlite> select * from stuff;
thing
object

Here we see the INSERT statement was triggered but not the SELECT. Have I
misunderstood the syntax diagram?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to detect cycles in a hierarchical table?

[Shane Dev]

I always followed the advice on https://sqlite.org/lang_with.html and use
UNION ALL in the compound select statement. This is why cycles trigger
infinite looping. In the case of my edges table, it does not make sense to
have cycles so my goal is to develop INSERT and UPDATE triggers that
prevent this possibility.

On 21 December 2017 at 12:11, Lifepillar <lifepil...@lifepillar.me> wrote:

> On 20/12/2017 22:31, Shane Dev wrote:
>
>> Hello,
>>
>> I have an edges table -
>>
>> sqlite> .sch edges
>> CREATE TABLE edges(parent, child);
>>
>> sqlite> select * from edges;
>> parent  child
>> 1   2
>> 1   3
>> 2   4
>> 3   1
>> 4   5
>> 5   2
>>
>> Here we have two cycles -
>>
>> 1) 1 => 3 => 1 (length 1)
>> 2) 2 => 4 => 5 => 2 (length 3)
>>
>> Cycles cause recursive common table expression queries to become infinite
>> loops.
>>
> Maybe you could show an example of such queries? This:
>
>   with recursive Visit(node) as (
> select parent from Edges where parent = 1
> union
> select child from Edges join Visit on parent = node
>   )
>   select node from Visit;
>
> returns a finite result (note that use of 'union' rather than 'union
> all').
>
> Life.
>
> ___
> 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] How to detect cycles in a hierarchical table?

[Shane Dev]

Hello,

I have an edges table -

sqlite> .sch edges
CREATE TABLE edges(parent, child);

sqlite> select * from edges;
parent  child
1   2
1   3
2   4
3   1
4   5
5   2

Here we have two cycles -

1) 1 => 3 => 1 (length 1)
2) 2 => 4 => 5 => 2 (length 3)

Cycles cause recursive common table expression queries to become infinite
loops. Is there a query which can detect all cycles regardless of length?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer and recursive common table expressions

[Shane Dev]

I have just spotted a couple of typos in my email below. The first two
common table expressions should have been as follows -

with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 3)
select * from cnt;
with recursive cnt(x) as (select 1 union all select x+1 from cnt) select *
from cnt limit 3;

On 3 January 2018 at 23:24, Shane Dev <devshan...@gmail.com> wrote:

> Hi,
>
> This simple recursive common table expression returns all integers from 1
> to 3 as expected -
>
> sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt
> limit 3) select * from cnt where x;
> x
> 1
> 2
> 3
> sqlite>
>
> If the LIMIT constraint is moved from the compound SELECT to the
> subsequent SELECT, it works the same -
>
> sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt)
> select * from cnt where x limit 3;
> x
> 1
> 2
> 3
> sqlite>
>
> If the LIMIT constraint is replaced with a WHERE constraint in the
> compound SELECT, it still works the same -
>
> sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt
> where x < 3) select * from cnt;
> x
> 1
> 2
> 3
> sqlite>
>
> However if the WHERE constraint is moved from the compound SELECT to the
> subsequent SELECT and adjusted slightly, it selects correct results but
> then hangs indefinitely -
>
> sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt)
> select * from cnt where x <= 3;
> x
> 1
> 2
> 3
> [no sqlite> prompt, CPU utilization 25%]
>
> I assume sqlite is recursively adding rows to the queue without
> considering that the subsequent SELECT only needs the first 3 of them.
>
> Can we conclude the query planner is unable to optimize the compound
> SELECT (the part in brackets) based on the WHERE constraint of the
> subsequent SELECT statement?
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query optimizer and recursive common table expressions

[Shane Dev]

Hi,

This simple recursive common table expression returns all integers from 1
to 3 as expected -

sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt
limit 3) select * from cnt where x;
x
1
2
3
sqlite>

If the LIMIT constraint is moved from the compound SELECT to the subsequent
SELECT, it works the same -

sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt)
select * from cnt where x limit 3;
x
1
2
3
sqlite>

If the LIMIT constraint is replaced with a WHERE constraint in the compound
SELECT, it still works the same -

sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt
where x < 3) select * from cnt;
x
1
2
3
sqlite>

However if the WHERE constraint is moved from the compound SELECT to the
subsequent SELECT and adjusted slightly, it selects correct results but
then hangs indefinitely -

sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt)
select * from cnt where x <= 3;
x
1
2
3
[no sqlite> prompt, CPU utilization 25%]

I assume sqlite is recursively adding rows to the queue without considering
that the subsequent SELECT only needs the first 3 of them.

Can we conclude the query planner is unable to optimize the compound
SELECT (the part in brackets) based on the WHERE constraint of the
subsequent SELECT statement?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

[Shane Dev]

Hi Clemens,

Your query is much faster on my system - thanks!

Apart from visual inspection and testing, is there anyway to be sure your
query selects the same results as my query?

From https://sqlite.org/queryplanner.html "When programming in SQL you tell
the system what you want to compute, not how to compute it". Is this an
exception to the rule where the query planner must be told how to compute
the result?




On 1 January 2018 at 10:58, Clemens Ladisch <clem...@ladisch.de> wrote:

> Shane Dev wrote:
> > CREATE TABLE nodes(id integer primary key, description text);
> > CREATE TABLE edges(parent not null references nodes, child not null
> references nodes, primary key(parent, child));
> >
> > select * from nodes where not exists (select * from edges where child=
> nodes.id);
> >
> > This works but is very slow when there are a million nodes and edges.
> > I thought an index on edges(child) might help
> > but it didn't
>
> The index has the wrong affinity; the edges.child column must be integer.
>
> The following query would be simpler, and does not need the index (because
> SQLite
> always creates a temporary index for the lookup anyway):
>
>   select * from nodes where id not in (select child from edges);
>
>
> Regards,
> Clemens
> ___
> 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] Efficient query to count number of leaves in a DAG.

[Shane Dev]

Hi,

I want to the count the number of leaves (descendants without children) for
each node in a DAG

DAG definition -

CREATE TABLE nodes(id integer primary key, description text);
CREATE TABLE edges(parent not null references nodes, child not null
references nodes, primary key(parent, child));

My query -

CREATE VIEW v_count_leaves as with recursive r(id, top) as (
select id, id from nodes
union all
select t.id, top from nodes as t, edges as e, r where e.parent=r.id and t.id
=e.child)
select top, count(*) from r where top<>id and id not in (select parent from
edges where parent=id) group by top;

It seems to work but is complex to understand and debug despite my aim to
keep it simple as possible, but more importantly - it is very slow when
there are more than a few thousand nodes and edges.

It there a more efficient (and ideally simpler) way?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient query to count number of leaves in a DAG.

[Shane Dev]

Hi Peter,

By "schema changes Clemens suggested" I assume you mean replacing the
constraint -

not exists (select * from edges where child=nodes.id);

with

...where id not in (select child from edges);

For this leaf count query, I need to constrain the result set to exclude
nodes which are parents -

sqlite> CREATE VIEW v_count_leaves as with recursive r(id, top) as (select
id, id from nodes union all select t.id, top from nodes as t, edges as e, r
where e.parent=r.id and t.id=e.child) select top, count(*) from r where
top<>id and id not in (select parent from edges) group by top;
sqlite> .timer on
sqlite> select * from v_count_leaves where top=4465;
44651
Run Time: real 75.678 user 75.671875 sys 0.00

sqlite> select count(*) from nodes;
1
sqlite> select count(*) from edges;
9986

It is still very slow or did you mean something else?


On 1 January 2018 at 18:04, petern <peter.nichvolo...@gmail.com> wrote:

> Shane.  I sent you a query to work with the crippled schema and index you
> proposed for TABLE edges.
> Clemens then explicitly suggested you correct the schema to have use of
> automatic covering index.
>
> >CREATE TABLE edges(parent not null references nodes, child not null
> >references nodes, primary key(parent, child));
>
> Try your leaf counter again - after making the schema changes Clemens
> suggested.
>
> Peter
>
>
> On Mon, Jan 1, 2018 at 8:13 AM, Shane Dev <devshan...@gmail.com> wrote:
>
> > Hi,
> >
> > I want to the count the number of leaves (descendants without children)
> for
> > each node in a DAG
> >
> > DAG definition -
> >
> > CREATE TABLE nodes(id integer primary key, description text);
> > CREATE TABLE edges(parent not null references nodes, child not null
> > references nodes, primary key(parent, child));
> >
> > My query -
> >
> > CREATE VIEW v_count_leaves as with recursive r(id, top) as (
> > select id, id from nodes
> > union all
> > select t.id, top from nodes as t, edges as e, r where e.parent=r.id and
> > t.id
> > =e.child)
> > select top, count(*) from r where top<>id and id not in (select parent
> from
> > edges where parent=id) group by top;
> >
> > It seems to work but is complex to understand and debug despite my aim to
> > keep it simple as possible, but more importantly - it is very slow when
> > there are more than a few thousand nodes and edges.
> >
> > It there a more efficient (and ideally simpler) way?
> > ___
> > 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] Is foreign key support disabled within triggers with raise functions?

[Shane Dev]

Thanks for the explanation. Does that mean FK constraints are only checked
if processing reaches the end of the trigger?

On 12 January 2018 at 13:29, Richard Hipp <d...@sqlite.org> wrote:

> Test 1 makes the changes, the checks the FK constraints, sees that the
> FK constraints are violated and hence runs ABORT, which backs out the
> change.
>
> Test 2 makes the change, then runs FAIL, which stops all further
> processing.  The FK constraints are never checked, and the changes are
> not backed out.
>
> On 1/12/18, Shane Dev <devshan...@gmail.com> wrote:
> > Hello,
> >
> > Perhaps it would be clearer if I ask the question in a different way. Why
> > does the following statement -
> >
> > insert into vtrig select 5;
> >
> > fail to insert a record in Test 1 below (as expected) but succeeds in
> Test
> > 2 (despite the foreign key constraint)?
> >
> > sqlite> CREATE TABLE reftab(id integer primary key);
> > sqlite> CREATE TABLE deptab(id integer primary key, ref int references
> > reftab);
> > sqlite> pragma foreign_keys;
> > foreign_keys
> > 1
> > sqlite> CREATE VIEW vtrig as select 1;
> >
> > Test 1
> > =
> >
> > sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
> >...> insert into deptab(ref) select 2;
> >...> end;
> > sqlite> insert into vtrig select 5;
> > Error: FOREIGN KEY constraint failed
> > sqlite> select * from deptab;
> > sqlite>
> >
> >
> > Test 2
> > =
> >
> > sqlite> drop trigger ttrig;
> > sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
> >...> insert into deptab(ref) select 2;
> >...> select raise(FAIL, 'this statement seems to temporarily disable
> > foreign
> >    ...> support');
> >...> end;
> > sqlite> insert into vtrig select 5;
> > Error: this statement seems to temporarily disable foreign
> > support
> > sqlite> select * from deptab;
> > id  ref
> > 1   2
> >
> >
> > On 12 January 2018 at 00:48, Richard Hipp <d...@sqlite.org> wrote:
> >
> >> On 1/11/18, Shane Dev <devshan...@gmail.com> wrote:
> >> >
> >> > CREATE VIEW vtrig as select 1;
> >> > CREATE TRIGGER ttrig instead of insert on vtrig begin
> >> >   delete from deptab;
> >> >   delete from reftab;
> >> >   insert into deptab(ref) select 2;
> >> >   select raise(FAIL, 'this statement seems to temporarily disable
> >> > foreign
> >> > support') where (select count(*) > 0 from deptab);
> >> > end;
> >> >
> >> > sqlite> insert into vtrig select 5;
> >> > Error: this statement seems to temporarily disable foreign support
> >> > sqlite> select * from reftab;
> >> > sqlite> select * from deptab;
> >> > id  ref
> >> > 1   2
> >> > sqlite> pragma foreign_keys;
> >> > foreign_keys
> >> > 1
> >> >
> >> > Can we conclude foreign key support is disabled within triggers with
> >> raise
> >> > functions?
> >>
> >> I don't what you mean by "disabled".  When you hit a raise(FAIL)
> >> SQLite stops whatever it was doing and leave the database in whatever
> >> half-way completed state it was in at the moment.  That's what
> >> raise(FAIL) is suppose to do.
> >>
> >> Did you mean to do "raise(ABORT)" instead, which should do what I
> >> think you are trying to accomplish.
> >>
> >> --
> >> 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
> >
>
>
> --
> 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] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

On 17 January 2018 at 08:45, petern  wrote:

> Shane. Expect to do a lot of hacking on shell.c.  It's not intended as a
> library but as the main program of a console application.


That's a shame. I try very hard not to reinvent the wheel especially when
the wheel question (shell.c) is widely used, flexible and presumably
thoroughly debugged.

However, I can't be the only one trying to programmatically exchange data
between SQLite and a delimited text file. For importing, the CSV virtual
table works well for multi-column CSVs, thanks again for the tip. For
exporting, I could retrieve the data using sqlite3_exec and build a string
from the 3rd and 4th parameters of the callback function. Then I would need
to code logic to insert the column and line separators and handle edge
cases (fields containing separators or double quotes, single column tables,
etc) and finally write the string to a file.

Is this most efficient approach?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a record count column be added to this random hierarchical view?

[Shane Dev]

On 19 January 2018 at 05:41, petern  wrote:

> Were you expecting random() to return the same sequence when the view
> materialized again in the subquery?
>

I was hoping to find a way to force the query planner to evaluate
v_random_hierarchy
only once. Perhaps this is not possible since it uses the non-deterministic
random() function


> Your ultimate query works fine when the random view is materialized once
> into a table.


Correct, but I work hoping to avoid the I/O penalty of this strategy when
generating a large hierarchy


>
>
FYI. as demonstrated above, to also count the NULL parent, use "IS" instead
> of "=".
>
>
I didn't know IS also worked with integers, good tip.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible to concatenate an arbitrary number of columns into one?

[Shane Dev]

Hello,

An asterisk in the result-column represents all columns from the FROM
clause without explicitly naming them,
https://www.sqlite.org/syntax/result-column.html Is there an SQL statement
to concatenate all columns into a single column without explicitly naming
them? If it existed, I could imagine something like - select concat(*) from
table/view/query/etc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to transpose a table using SQL?

[Shane Dev]

Hi Keith, Jean-Luc

I should have mentioned my shell is configured to display column headers
Product/Region|Belgium|France|USA for table t1 and
Product/Region|Oil_filter|Spark_plug|Coolent for view v1. By "dynamically
changing table", I meant the number of columns and rows could could change
after the dependant view was created. it appears this is impossible using
only SQL

On Mon, 1 Apr 2019 at 02:38, Keith Medcalf  wrote:

>
> On Sunday, 31 March, 2019 14:07, Shane Dev  wrote:
>
> >Is it possible to create a view which switches rows and columns of a
> >dynamically changing table?
>
> >For example, imagine we have table t1 where both columns and rows
> >could change after the view has been created
>
> >sqlite> select * from t1;
> >Product/Region|Belgium|France|USA
> >Oil_filter|1|2|3
> >Spark_plug|4|5|6
> >Coolent|7|8|9
>
> >Could view v1 be created such that
>
> >sqlite> select * from v1;
> >Product/Region|Oil_filter|Spark_plug|Coolent
> >Belgium|1|4|7
> >France|2|5|8
> >USA|3|6|9
>
> You example is ambiguous.
>
> For example, is the table T1 thus:
>
> create table T1("Product/Region" text not null, Belgium integer not null,
> France integer not null, USA integer not null);
> insert into T1 values ('Oil_filter', 1, 2, 3);
> insert into T1 values ('Spark_plug', 4, 5, 6);
> insert into T2 values ('Coolent', 7, 8, 9);
>
> or so:
>
> create table T1(c0, c1, c2, c3);
> insert into T1 values ('Product/Region', 'Belgium', 'France', 'USA');
> insert into T1 values ('Oil_filter', 1, 2, 3);
> insert into T1 values ('Spark_plug', 4, 5, 6);
> insert into T2 values ('Coolent', 7, 8, 9);
>
> If "so", how do you know which column/row is the proposed row/column
> names?  Or do you just want to transpose the matrix?
>
> Please explain what you mean by "dynamically changing table" ... what
> exactly is dynamically changing?  The number of columns?  The number of
> rows?
>
> Note this is probably relatively simple for kiddie sized data but would be
> far more efficient if you did it at the application level.  It would be
> even simpler if the data were properly normalized.
>
> ---
> 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
> 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] Is it possible to transpose a table using SQL?

[Shane Dev]

Hello,

Is it possible to create a view which switches rows and columns of a
dynamically changing table?

For example, imagine we have table t1 where both columns and rows could
change after the view has been created

sqlite> select * from t1;
Product/Region|Belgium|France|USA
Oil_filter|1|2|3
Spark_plug|4|5|6
Coolent|7|8|9

Could view v1 be created such that

sqlite> select * from v1;
Product/Region|Oil_filter|Spark_plug|Coolent
Belgium|1|4|7
France|2|5|8
USA|3|6|9
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users