[sqlite] Autoincrement with rollback

[Koen Van Exem]

Hi,

Is it a bug or feature that the autoincrement
value is being reused when a rollback is issued?


--- Begin ---

sqlite> drop table if exists demo;
sqlite> create table demo (id integer primary key autoincrement, value
text);
sqlite> begin transaction;
sqlite> insert into demo (value) VALUES ('value');
sqlite> select last_insert_rowid();
1
sqlite> rollback;
sqlite> begin transaction;
sqlite> insert into demo (value) VALUES ('value');
sqlite> select last_insert_rowid();
1

--- End ---

The documentation on https://www.sqlite.org/autoinc.html is a bit unclear

On an INSERT , if the ROWID or
INTEGER PRIMARY KEY column is not explicitly given a value, then it will be
filled automatically with an unused integer, usually the one more than the
largest ROWID currently in use. This is true regardless of whether or not
the AUTOINCREMENT keyword is used.

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that
changes the automatic ROWID assignment algorithm to prevent the reuse of
ROWIDs over the lifetime of the database. In other words, the purpose of
AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted
rows.

In bullet 1 it says that it takes a value higher than the largest ROWID.
In bullet 2 it says it prevents reuse of ROWIDs from previously deleted
rows.

Can someone please clarify?

Thanks,
Koen

-- 
Koen Van Exem
+32 2335 2335
k...@allors.com
http://www.allors.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement with rollback

[Clemens Ladisch]

Koen Van Exem wrote:
> Is it a bug or feature that the autoincrement
> value is being reused when a rollback is issued?
>
> The documentation on https://www.sqlite.org/autoinc.html is a bit unclear
>
> ... it says it prevents reuse of ROWIDs from previously deleted rows.

Only a DELETE statement results in "deleted rows".  When an INSERT is
rolled back, the database is in a state as if the INSERT never happened.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement with rollback

[Koen Van Exem]

--- Begin ---

sqlite> drop table if exists demo;
sqlite> create table demo (id integer primary key autoincrement, value
text);
sqlite> begin transaction;
sqlite> insert into demo (value) VALUES ('value');
sqlite> select last_insert_rowid();
1
sqlite> delete from demo where id = 1;
sqlite> rollback;
sqlite> begin transaction;
sqlite> insert into demo (value) VALUES ('value');
sqlite> select last_insert_rowid();
1

--- End ---

Using a delete statement before the rollback
doesn't help.

I really want sqlite to only issue an id once.

Is this possible?

Thanks,
Koen


2014-11-11 10:35 GMT+01:00 Clemens Ladisch :

> Koen Van Exem wrote:
> > Is it a bug or feature that the autoincrement
> > value is being reused when a rollback is issued?
> >
> > The documentation on https://www.sqlite.org/autoinc.html is a bit
> unclear
> >
> > ... it says it prevents reuse of ROWIDs from previously deleted rows.
>
> Only a DELETE statement results in "deleted rows".  When an INSERT is
> rolled back, the database is in a state as if the INSERT never happened.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Koen Van Exem
+32 3301 3301
+32 498 51
k...@allors.com
http://www.allors.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement with rollback

[Andy Ling]

> --- Begin ---
> 
> sqlite> drop table if exists demo;
> sqlite> create table demo (id integer primary key autoincrement, value
> text);
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
> sqlite> delete from demo where id = 1;
> sqlite> rollback;
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
> 
> --- End ---
> 
> Using a delete statement before the rollback
> doesn't help.
> 
> I really want sqlite to only issue an id once.
> 

Surely the whole point of rollback is that it puts the database back to the
state it was in before the transaction started. So everything should be
just as if no inserts (or deletes or anything else)had happened, including
any changes to autoincrement values.

Regards

Andy Ling

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

Re: [sqlite] Autoincrement with rollback

[J Decker]

delete it and commit it...
rollback is 'undo' and if anything was differen't it wouldn't be a very
good undo.
assign the key yourself?  on failure keep incrementing?
if it's supposed to have been inserted and deleted... then rollback is not
the correct solution.

On Tue, Nov 11, 2014 at 2:04 AM, Koen Van Exem  wrote:

> --- Begin ---
>
> sqlite> drop table if exists demo;
> sqlite> create table demo (id integer primary key autoincrement, value
> text);
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
> sqlite> delete from demo where id = 1;
> sqlite> rollback;
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
>
> --- End ---
>
> Using a delete statement before the rollback
> doesn't help.
>
> I really want sqlite to only issue an id once.
>
> Is this possible?
>
> Thanks,
> Koen
>
>
> 2014-11-11 10:35 GMT+01:00 Clemens Ladisch :
>
> > Koen Van Exem wrote:
> > > Is it a bug or feature that the autoincrement
> > > value is being reused when a rollback is issued?
> > >
> > > The documentation on https://www.sqlite.org/autoinc.html is a bit
> > unclear
> > >
> > > ... it says it prevents reuse of ROWIDs from previously deleted rows.
> >
> > Only a DELETE statement results in "deleted rows".  When an INSERT is
> > rolled back, the database is in a state as if the INSERT never happened.
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Koen Van Exem
> +32 3301 3301
> +32 498 51
> k...@allors.com
> http://www.allors.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement with rollback

[Koen Van Exem]

I find it a bit confusing because when you create
a PRIMARY KEY AUTOINCREMENT then a
table named sqlite_sequence is created.

According to the SQL (2003) standard multiple sessions are
guaranteed to allocate distinct sequence values.
(even when rollbacks are involved)


2014-11-11 11:14 GMT+01:00 Andy Ling :

> > --- Begin ---
> >
> > sqlite> drop table if exists demo;
> > sqlite> create table demo (id integer primary key autoincrement, value
> > text);
> > sqlite> begin transaction;
> > sqlite> insert into demo (value) VALUES ('value');
> > sqlite> select last_insert_rowid();
> > 1
> > sqlite> delete from demo where id = 1;
> > sqlite> rollback;
> > sqlite> begin transaction;
> > sqlite> insert into demo (value) VALUES ('value');
> > sqlite> select last_insert_rowid();
> > 1
> >
> > --- End ---
> >
> > Using a delete statement before the rollback
> > doesn't help.
> >
> > I really want sqlite to only issue an id once.
> >
>
> Surely the whole point of rollback is that it puts the database back to the
> state it was in before the transaction started. So everything should be
> just as if no inserts (or deletes or anything else)had happened, including
> any changes to autoincrement values.
>
> Regards
>
> Andy Ling
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Koen Van Exem
+32 3301 3301
+32 498 51
k...@allors.com
http://www.allors.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement with rollback

[Darren Duncan]

On 2014-11-11 2:41 AM, Koen Van Exem wrote:

I find it a bit confusing because when you create
a PRIMARY KEY AUTOINCREMENT then a
table named sqlite_sequence is created.

According to the SQL (2003) standard multiple sessions are
guaranteed to allocate distinct sequence values.
(even when rollbacks are involved)


See, this is the source of your confusion.  I will explain.

1.  Conceptually a sequence generator is just a database table with a single row 
and single column whose value is the integer.  When the generator produces the 
next value, it is like these 2 statements being done as an atomic unit: "update 
seqgentbl set theint = theint + 1" and "select theint from seqgentbl".


2.  The semantics that the SQL standard defines, and is commonplace with other 
SQL DBMSs, is that the aforementioned read+update of seqgentbl happens in its 
own autonomous database transaction that commits immediately, and serially prior 
to the main transaction that called upon the sequence generator.  This is why in 
those cases a rollback of the main transaction doesn't rollback the sequence 
generator, because semantically that happened prior to the current transaction 
and successfully committed.


3.  SQLite is different such that its read_update of seqgentbl happens within 
the current main transaction rather than a separate one, and therefore its 
actions rollback like anything else.


So SQLite is purposefully being different than the SQL standard.  Partly this is 
because supporting the standard means having to support multiple concurrent 
transactions trying to write the database, in contrast to what SQLite actually 
does which is only supporting one writing transaction at a time.


If you want to use SQLite like the SQL standard, then invoke the sequence 
generator first in its own transaction and remember the value, then use that 
remembered value in your main transaction that you explicitly do afterwards.


Do you understand what's going on now?

-- Darren Duncan

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

[sqlite] Multiple threads reading different data

[Daniel Polski]

I'm accessing a database from multiple threads, where each thread has a 
separate database connection.


When some specific event happens, I want the threads to evaluate the 
situation and act accordingly.


I have setup a temporary trigger which fires at the event and which in 
turn calls a user defined function updating a timestamp with the time of 
the event.


The threads compare this timestamp with a local timstamp storing the 
last synchronization, and if they mismatch they fetch data from the 
database and process it.


Now here's where it starts to seem a little tricky to me. It seems like 
the separate connections don't "see" the update made to the database 
from another thread if the fetching is done quickly after the update (as 
soon as the timestamps mismatch). I'm thinking that this maybe has to do 
with me using WAL mode, and that the update is not yet "processed 
enough" for the other threads to fetch the new data (so they still 
selects the old data), even though the trigger is set to fire "after 
update". Is this maybe the case? If so, any suggestions how to solve it?


Regards,
Daniel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement with rollback

[Koen Van Exem]

Hi Darren,

Thanks for explaining the internals.

I already assumed by my 2 small experiments
that this was the case but it's nice to have it confirmed.

With kind regards,
Koen



2014-11-11 12:05 GMT+01:00 Darren Duncan :

> On 2014-11-11 2:41 AM, Koen Van Exem wrote:
>
>> I find it a bit confusing because when you create
>> a PRIMARY KEY AUTOINCREMENT then a
>> table named sqlite_sequence is created.
>>
>> According to the SQL (2003) standard multiple sessions are
>> guaranteed to allocate distinct sequence values.
>> (even when rollbacks are involved)
>>
>
> See, this is the source of your confusion.  I will explain.
>
> 1.  Conceptually a sequence generator is just a database table with a
> single row and single column whose value is the integer.  When the
> generator produces the next value, it is like these 2 statements being done
> as an atomic unit: "update seqgentbl set theint = theint + 1" and "select
> theint from seqgentbl".
>
> 2.  The semantics that the SQL standard defines, and is commonplace with
> other SQL DBMSs, is that the aforementioned read+update of seqgentbl
> happens in its own autonomous database transaction that commits
> immediately, and serially prior to the main transaction that called upon
> the sequence generator.  This is why in those cases a rollback of the main
> transaction doesn't rollback the sequence generator, because semantically
> that happened prior to the current transaction and successfully committed.
>
> 3.  SQLite is different such that its read_update of seqgentbl happens
> within the current main transaction rather than a separate one, and
> therefore its actions rollback like anything else.
>
> So SQLite is purposefully being different than the SQL standard.  Partly
> this is because supporting the standard means having to support multiple
> concurrent transactions trying to write the database, in contrast to what
> SQLite actually does which is only supporting one writing transaction at a
> time.
>
> If you want to use SQLite like the SQL standard, then invoke the sequence
> generator first in its own transaction and remember the value, then use
> that remembered value in your main transaction that you explicitly do
> afterwards.
>
> Do you understand what's going on now?
>
> -- Darren Duncan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Koen Van Exem
+32 3301 3301
+32 498 51
k...@allors.com
http://www.allors.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple threads reading different data

[Clemens Ladisch]

Daniel Polski wrote:
> this maybe has to do with me using WAL mode, and that the update is
> not yet "processed enough" for the other threads to fetch the new
> data (so they still selects the old data), even though the trigger is
> set to fire "after update". Is this maybe the case?

Yes; other connections read the old data as long as the transaction has
not yet committed.

> If so, any suggestions how to solve it?

SQLite has not callback that runs after a commit has completed (it has
a commit hook, but it's too early).

Either
- fire the callback from your own code after the commit, or
- in the reading thread, execute BEGIN IMMEDIATE, which will not succeed
  until the writing transaction has finished.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple threads reading different data

[Hick Gunter]

This is the I (Isolation) in ACID.

WAL mode allows the writer to pretend that no transactions are outstanding and 
begin and even commit a write transaction. This change sits in the Wal file 
until all prior transactions have been completed and the change can be copied 
to the db. As long as your threads are within a transaction begun before the 
change happened, they will continue to see the old state.

Maybe your threads have unfinished statements (missing call to sqlite3_reset() 
or sqlite3_finalize() functions), which cause the implicit transaction to be 
very much longer than expected.

-Ursprüngliche Nachricht-
Von: Daniel Polski [mailto:dan...@agelektronik.se]
Gesendet: Dienstag, 11. November 2014 12:59
An: General Discussion of SQLite Database
Betreff: [sqlite] Multiple threads reading different data


I'm accessing a database from multiple threads, where each thread has a 
separate database connection.

When some specific event happens, I want the threads to evaluate the situation 
and act accordingly.

I have setup a temporary trigger which fires at the event and which in turn 
calls a user defined function updating a timestamp with the time of the event.

The threads compare this timestamp with a local timstamp storing the last 
synchronization, and if they mismatch they fetch data from the database and 
process it.

Now here's where it starts to seem a little tricky to me. It seems like the 
separate connections don't "see" the update made to the database from another 
thread if the fetching is done quickly after the update (as soon as the 
timestamps mismatch). I'm thinking that this maybe has to do with me using WAL 
mode, and that the update is not yet "processed enough" for the other threads 
to fetch the new data (so they still selects the old data), even though the 
trigger is set to fire "after update". Is this maybe the case? If so, any 
suggestions how to solve it?

Regards,
Daniel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[RP McMurphy]

> 

Okay, for my simplified example analyze does improve the times. But for our 
application this does not help, and it also harmed a couple of other queries. 
I'll have to see if I can make a better example schema showing the problem 
because I can't upload the entire 900MB database.

RP


On Sun, 11/9/14, RP McMurphy  wrote:

 Subject: Discrepancy with indexing and WHERE clause with AND/OR
 To: sqlite-users@sqlite.org
 Date: Sunday, November 9, 2014, 2:35 AM
 
 If there is a large table and we need
 to select a subset of values using a WHERE clause with an
 AND/OR construct sqlite has trouble finding the answer in a
 reasonable time. Breaking the queries down into separate
 SELECT statements speeds up the process exponentially.
 
 For example the following takes a few seconds to return the
 answer 1334. Note that the index (w) is a "low quality"
 index with the arguments in the wrong order. The reason for
 this is explained further down:
 
     with recursive cnt(x) as (select 1 union
 all select x+1 from cnt limit 1000)
     insert into v select x % 3,x from cnt;
 
     create index w on v(z,y);
     select count(*) from v
 where    z = 0 and
            
     (    y between 100 and
 1001000 
            
     or    y between 200
 and 2001000
            
     or    y between 300
 and 3001000
            
     or    y between 400
 and 4001000);
 
 The reason the the "low quality" index is because this data
 is also accessed in a different manner. Namely like this:
 
     select count(*) from v group by z;
 
 Both of the above queries each take about 3 seconds to run.
 I don't think the second query can be made faster, but the
 first query can certainly be much faster even with the "low
 quality" index. Thus:
 
     select 
     (select count(*) from v where z = 0 and y
 between 100 and 1001000) +
     (select count(*) from v where z = 0 and y
 between 200 and 2001000) +
     (select count(*) from v where z = 0 and y
 between 300 and 3001000) +
     (select count(*) from v where z = 0 and y
 between 400 and 4001000);
 
 Now the query returns the result 1334 almost immediately.
 The only difference is that the WHERE clause has been
 manually flattened and broken into separate SELECT
 portions.
 
 When we change the index to "high quality" (u) and put the
 arguments in the other order.
 
     drop index w;
     create index u on v(y,z);
 
 And rerun the query:
 
     select count(*) from v
 where    z = 0 and
            
     (    y between 100 and
 1001000 
            
     or    y between 200
 and 2001000
            
     or    y between 300
 and 3001000
            
     or    y between 400
 and 4001000);
 
 The answer 1334 is returned almost immediately. And
 flattening this query gives no advantage. But now the second
 type of query runs terribly slow because the index is very
 poor for this type of query:
 
     select count(*) from v group by z;
 
 And takes more then 30 seconds to finish.
 
 Trying to make two indices in the hope that sqlite will find
 the optimal one by itself gives back result times the same
 as if only the w index is present:
 
     create index w on v(z,y);
     create index u on v(y,z);
 
 Both queries now take a few seconds to run. It is as if the
 u index does not exist.
 
 In our application we have only created the w index (since
 the u index is awful for the grouping query) and manually
 generate the WHERE/AND/OR flattened queries in a loop. This
 solution is unsatisfying to us and I think we must be doing
 something wrong. Is there a way we can make the w index work
 with both queries and not have to run external loops to
 flatten all the WHERE clauses?
 
 RP
 
 PS: Below is the text in one unit that can be copied and
 pasted into a shell session running sqlite3.exe:
 
 create table times(idx,j);
 create table v(z,y);
 
 with recursive cnt(x) as (select 1 union all select x+1 from
 cnt limit 1000)
 insert into v select x % 3,x from cnt;
 
 create index w on v(z,y);
 select 'With index (z,y)';
 insert into times select 0,julianday('now');
 select 
 (select count(*) from v where z = 0 and y between 100
 and 1001000) +
 (select count(*) from v where z = 0 and y between 200
 and 2001000) +
 (select count(*) from v where z = 0 and y between 300
 and 3001000) +
 (select count(*) from v where z = 0 and y between 400
 and 4001000);
 insert into times select 1,julianday('now');
 select strftime('%f',(select j from times where
 idx=1)-(select j from times where idx=0));
 select count(*) from v where    z = 0 and
            
 (    y between 100 and 1001000 
            
 or    y between 200 and 2001000
            
 or    y between 300 and 3001000
            
 or    y between 400 and 4001000);
 insert into times select 2,julianday('now');
 select strftime('%f',(select j from times where

Re: [sqlite] Change UPDATE with JOIN sintax

[dylan666]

I works like a charm!
Thanks for the answer and for the explanation



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Change-UPDATE-with-JOIN-sintax-tp79105p79153.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[Simon Slavin]

On 10 Nov 2014, at 10:55pm, RP McMurphy  wrote:

>> 
> 
> Okay, for my simplified example analyze does improve the times. But for our 
> application this does not help, and it also harmed a couple of other queries.

If you can provide any examples where ANALYZE makes a query slower, I suspect 
the developer team would like to see them.

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

[sqlite] Triggers and CTE's

[Ben Newberg]

All,

Is it possible to have CTE's within triggers? The way I read the 'SQL As
Understood By SQLite', one can, but I could be misinterpreting it.

My DDL for my trigger is as follows:

CREATE TRIGGER t_populate_zweeks
AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1
BEGIN
DELETE FROM zWeeks;
WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks
limit 10)
INSERT INTO zWeeks (Week) select wk from Weeks;
END;

When I run this, I get an error message from sqlite3_errmsg() saying syntax
error near "INSERT". However, when I run the DELETE, WITH and INSERT
statements above separately without the Create Trigger DDL, the query runs
successfully and populates my zWeeks table with values 1 through 10.

Do triggers not support this behavior, or is my syntax incorrect?

Thanks,
BEN
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers and CTE's

[Simon Slavin]

On 11 Nov 2014, at 11:15pm, Ben Newberg  wrote:

> WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks
> limit 10)
> INSERT INTO zWeeks (Week) select wk from Weeks;

Just use 10 INSERT commands.

I don't know what's causing your error message, but your code will be simpler 
if you just don't use RECURSIVE unnecessarily.

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

Re: [sqlite] Triggers and CTE's

[Ben Newberg]

Thanks Simon.

The 10 is just an arbitrary value I chose for this example. The user
actually determines the value at run-time, so this value could be any
integer. I have a way to settle that, if only I could figure out how I can
get this trigger working.

BEN

On Tue, Nov 11, 2014 at 5:41 PM, Simon Slavin  wrote:

>
> On 11 Nov 2014, at 11:15pm, Ben Newberg  wrote:
>
> > WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks
> > limit 10)
> > INSERT INTO zWeeks (Week) select wk from Weeks;
>
> Just use 10 INSERT commands.
>
> I don't know what's causing your error message, but your code will be
> simpler if you just don't use RECURSIVE unnecessarily.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers and CTE's

[Igor Tandetnik]

On 11/11/2014 6:15 PM, Ben Newberg wrote:

CREATE TRIGGER t_populate_zweeks
AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1
BEGIN
DELETE FROM zWeeks;
WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks
limit 10)
INSERT INTO zWeeks (Week) select wk from Weeks;
END;

When I run this, I get an error message from sqlite3_errmsg() saying syntax
error near "INSERT".


Looks like a bug to me. The statement works standalone, but not within a 
trigger.


As a workaround, make it

INSERT INTO zWeeks (Week)
WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks 
limit 10)

select wk from Weeks;

This works both ways.
--
Igor Tandetnik

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

Re: [sqlite] Triggers and CTE's

[Simon Slavin]

On 11 Nov 2014, at 11:59pm, Ben Newberg  wrote:

> The 10 is just an arbitrary value I chose for this example. The user
> actually determines the value at run-time, so this value could be any
> integer. I have a way to settle that, if only I could figure out how I can
> get this trigger working.

Sorry, I see no way to do it.  Two alternatives:

1) Do it in your programming language.

2) Don't create the rows with default values.  Have your software assume that 
if the row doesn't exist, it has default values.

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

Re: [sqlite] Triggers and CTE's

[Richard Hipp]

On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik  wrote:

On 11/11/2014 6:15 PM, Ben Newberg wrote:
>
> Looks like a bug to me. The statement works standalone, but not within a
> trigger.
>

There are many limitations and restrictions on the statements inside of
triggers.  See the trigger documentation (
https://www.sqlite.org/lang_createtrigger.html) for details.  It looks like
I need to add "no CTEs" to the list of restrictions.

This is not a bug - it is an intentional omission.  A lot of extra code
would need to be added to support this and that is not something we want to
do right now.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers and CTE's

[Ben Newberg]

Thanks everyone. I will have the programming language do the work on this
one instead of going the trigger route.
On Nov 11, 2014 7:39 PM, "Richard Hipp"  wrote:

> On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik 
> wrote:
>
> On 11/11/2014 6:15 PM, Ben Newberg wrote:
> >
> > Looks like a bug to me. The statement works standalone, but not within a
> > trigger.
> >
>
> There are many limitations and restrictions on the statements inside of
> triggers.  See the trigger documentation (
> https://www.sqlite.org/lang_createtrigger.html) for details.  It looks
> like
> I need to add "no CTEs" to the list of restrictions.
>
> This is not a bug - it is an intentional omission.  A lot of extra code
> would need to be added to support this and that is not something we want to
> do right now.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers and CTE's

[Igor Tandetnik]

On 11/11/2014 8:37 PM, Richard Hipp wrote:

On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik  wrote:

On 11/11/2014 6:15 PM, Ben Newberg wrote:


Looks like a bug to me. The statement works standalone, but not within a
trigger.



There are many limitations and restrictions on the statements inside of
triggers.  See the trigger documentation (
https://www.sqlite.org/lang_createtrigger.html) for details.  It looks like
I need to add "no CTEs" to the list of restrictions.

This is not a bug - it is an intentional omission.


INSERT INTO ... WITH RECURSIVE ... SELECT does work though. Is this just 
a happy accident?

--
Igor Tandetnik

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

Re: [sqlite] Triggers and CTE's

[Richard Hipp]

On Tue, Nov 11, 2014 at 9:01 PM, Igor Tandetnik  wrote:

> On 11/11/2014 8:37 PM, Richard Hipp wrote:
>
>> On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik 
>> wrote:
>>
>> On 11/11/2014 6:15 PM, Ben Newberg wrote:
>>
>>>
>>> Looks like a bug to me. The statement works standalone, but not within a
>>> trigger.
>>>
>>>
>> There are many limitations and restrictions on the statements inside of
>> triggers.  See the trigger documentation (
>> https://www.sqlite.org/lang_createtrigger.html) for details.  It looks
>> like
>> I need to add "no CTEs" to the list of restrictions.
>>
>> This is not a bug - it is an intentional omission.
>>
>
> INSERT INTO ... WITH RECURSIVE ... SELECT does work though. Is this just a
> happy accident?
>

Yes.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users