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
> >dynam
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
Spar
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 imag
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
---
s 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
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 definitio
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 t
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 th
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 ch
> 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
y
> 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 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
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 wrote:
> On 1/16/18, Shane Dev wrote:
> > I tried -
> >
> > sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename=&#
hout
> necessity of the SQLite shell:
>
> https://sqlite.org/csv.html
>
> On Tue, Jan 16, 2018 at 12:47 AM, Shane Dev wrote:
>
> > Hi,
> >
> > I am looking for an efficient way to write a c program which performs the
> > same function as the SQLite shell
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",
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:
Sorry, false alarm, the text is correct
On 15 January 2018 at 13:36, Shane Dev 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.
>
&g
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 wrote:
>
>
> On 15 Jan 2018, at 10:08am, Shane Dev wrote:
>
> > Ideall
very interested to understand how that works.
>
> Peter
>
>
>
>
>
> On Sun, Jan 14, 2018 at 2:33 PM, Shane Dev wrote:
>
> > Hi Simon,
> >
> > I have found a way achieve this purely in the SQLite shell. The trick is
> to
> > make all rows in
;';";
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 wrote:
> On 13 Jan 2018, at 6:48pm, Shane Dev wrote:
>
&g
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 wrote:
>
>
> On 13 Jan 2018, at 7:54pm, Shane Dev wrote:
>
> > What do you
xecution - or do I misunderstand your question?
On 13 January 2018 at 20:40, Simon Slavin wrote:
>
>
> On 13 Jan 2018, at 7:33pm, Shane Dev wrote:
>
> > I use mainly Linux (bash) and Windows (powershell) but my target
> platforms
> > also include Android, iOS, IoT (an
Slavin wrote:
> On 13 Jan 2018, at 6:48pm, Shane Dev 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?
&
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 sec
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 wrote:
> > Hello,
> >
> > Perhaps i
On 12 January 2018 at 00:48, Richard Hipp wrote:
> On 1/11/18, Shane Dev 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(r
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 t
Thanks, that works
On 11 January 2018 at 06:40, Dan Kennedy 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 SE
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 wrote:
>
> > Hi Ryan,
> >
> > Nice! I ha
e 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:
>
>>
ax(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 wrote:
> On 01/10/2018 11:48 PM, Shane Dev wrote:
>
>> Hell
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_val
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_readon
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/ma
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 t
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 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
>> c
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 s
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
,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
>
>
w 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
> S
r, 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
&
*
from cnt limit 3;
On 3 January 2018 at 23:24, Shane Dev 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
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 subsequen
om 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] Efficie
ges(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 wrote:
>
> > Hi,
>
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
, 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 wrote:
> Shane Dev wrote:
> > CREATE TABLE nodes(id integer primary key, description text);
> > CREATE TA
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 -
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 wrote:
> On 24/12/2017 11:56, Shane Dev wrote:
>
> Rela
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 che
this possibility.
On 21 December 2017 at 12:11, Lifepillar 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 *
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
elect 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
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
uesday, 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 wrote:
> >
> >>
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 IN
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.paren
brilliant! - it works - thanks
On 14 December 2017 at 19:07, Clemens Ladisch wrote:
> Shane Dev wrote:
> > On 14 December 2017 at 12:59, Clemens Ladisch
> wrote:
> >> Shane Dev wrote:
> >>> Can we conclude there is no single CTE or other SQL statement which
Hi Clemens,
With your solution, how would you define the DELETE ON VHIERARCHY trigger?
On 14 December 2017 at 12:59, Clemens Ladisch 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
here id in (select id from cte);
On 13 December 2017 at 00:59, J. King 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
> 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 branc
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 wrote:
> Shane Dev wrote:
> > Any ideas to achieve this?
>
> Use another CTE to bring all rows into the correct
ciated.
On 26 November 2017 at 11:30, Clemens Ladisch wrote:
> Simon Slavin wrote:
> > On 26 Nov 2017, at 8:02am, Shane Dev wrote:
> >> Any ideas to achieve this?
> >
> > Use the UNION keyword to combine the results of the two SELECT commands
>
> That would
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
>
>
> 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 splitti
Smith 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
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
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 pop
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 othe
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
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
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 e
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 cou
; 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-
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
encodi
75 matches
Mail list logo
