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

2017-06-05 Thread 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

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

2017-06-05 Thread Shane Dev
ar(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-us

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

2017-11-26 Thread 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

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

2017-11-26 Thread Shane Dev
ciated. 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 r

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

2017-11-26 Thread 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

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

2017-11-21 Thread 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

Re: [sqlite] Can I recursively concatenate strings?

2017-11-23 Thread Shane Dev
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 >> ... >> &g

Re: [sqlite] Can I recursively concatenate strings?

2017-11-23 Thread 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

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

2017-11-22 Thread 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

[sqlite] Can I recursively concatenate strings?

2017-11-22 Thread 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 ...

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

2017-12-14 Thread 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

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

2017-12-14 Thread 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

[sqlite] Can a trigger recursively update a table?

2017-12-12 Thread 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

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

2017-12-20 Thread 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

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

2017-12-20 Thread Shane Dev
gt; >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 <dev

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

2017-12-19 Thread 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

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

2017-12-12 Thread Shane Dev
l 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

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

2017-11-20 Thread Shane Dev
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 sa

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

2017-11-20 Thread 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

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

2017-11-05 Thread 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

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

2017-10-28 Thread 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

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

2017-12-20 Thread Shane Dev
|| 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 M

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

2017-12-31 Thread 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 -

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

2018-01-04 Thread Shane Dev
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_

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

2018-01-05 Thread Shane Dev
> > 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: [sq

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

2017-12-24 Thread 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

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

2017-12-24 Thread 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

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

2018-01-07 Thread Shane Dev
Y 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

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

2018-01-08 Thread 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,

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

2018-01-07 Thread 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

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

2018-01-08 Thread Shane Dev
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

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

2018-01-08 Thread 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

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

2018-01-10 Thread 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: &

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

2018-01-10 Thread Shane Dev
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: &g

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

2018-01-10 Thread Shane Dev
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 wrot

[sqlite] Can error messages be concatenated in the raise function?

2018-01-10 Thread 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

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

2018-01-10 Thread 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

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

2018-01-10 Thread Shane Dev
eate 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,

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

2018-01-08 Thread 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

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

2018-01-16 Thread Shane Dev
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 perfo

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

2018-01-18 Thread 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

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

2018-01-16 Thread 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",

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

2018-01-14 Thread Shane Dev
> 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: >

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

2018-01-15 Thread 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

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

2018-01-20 Thread 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

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

2018-01-18 Thread 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

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

2018-01-18 Thread 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

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

2018-01-18 Thread Shane Dev
n 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

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

2018-01-16 Thread Shane Dev
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>

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

2018-01-17 Thread Shane Dev
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? > &

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

2018-01-17 Thread 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

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

2018-01-15 Thread Shane Dev
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 sh

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

2018-01-13 Thread Shane Dev
estion? 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 Andr

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

2018-01-13 Thread Shane Dev
. 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 <de

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

2018-01-13 Thread 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

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

2018-01-13 Thread Shane Dev
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 / &g

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

2018-01-15 Thread 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 self

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

2018-01-15 Thread 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, Sha

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

2018-01-12 Thread Shane Dev
2 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

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

2018-01-11 Thread 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

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

2018-01-02 Thread Shane Dev
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]

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

2017-12-21 Thread 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

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

2017-12-21 Thread Shane Dev
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] How to detect cycles in a hierarchical table?

2017-12-20 Thread 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

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

2018-01-03 Thread Shane Dev
* 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 &

[sqlite] Query optimizer and recursive common table expressions

2018-01-03 Thread 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

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

2018-01-01 Thread Shane Dev
mpute, 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, descriptio

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

2018-01-01 Thread 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));

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

2018-01-01 Thread Shane Dev
gt; > >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,

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

2018-01-12 Thread Shane Dev
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> wro

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

2018-01-17 Thread 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

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

2018-01-18 Thread 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

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

2018-11-20 Thread 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

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

2019-03-31 Thread Shane Dev
ange 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 >

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

2019-03-31 Thread 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