Re: [sqlite] Triggers and CTE's

2014-11-12 Thread James K. Lowden
On Tue, 11 Nov 2014 17:15:53 -0600
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". 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?

You really don't want to use triggers this way.  I know you think you
do.  :-)  Everyone faces the temptation sooner or later.  But it is a
misuse of triggers, and you'll have the devil to pay in due time.  

Rather than lifting everything back up into the application, I would
suggest moving the SQL into a prepared statement inside a transaction.
It is an example of why I'd like to see SQLite support user-defined
functions (not "stored procedures") for DML purposes.  Before I did
that, though, I would make sure I actually need to persist weeks.  I'm
pretty sure weeks can be computed in a view, and it's not obvious that
performance would be worse.  

If persistence is required, consider maintaining a single offset that
can be added to weeks and incremented separately, instead incrementing
each week.  For example, you could have the first row be week 0, and
each successive row as max(week) - 1, keeping e.g. 0, -1, -2 (where
zero is oldest) and offset of 2, making 2+0, 2+-1, 2+-2, i.e. 2, 1, 0.  

Best advice is to restrict the use of triggers to enforcement of
referential integrity.  For the most part they are obsolete now that
we have DRI (e.g. declared foreign keys) support.  

Why only that?  Triggers fire regardless of application, regardless of
business rule.  They can maintain the internal consistency of the
database.  If you put business rules in triggers, you cannot change
them without affecting all applications and future uses.  Business
rules are less logical and more volatile than RI rules, too, and thus
more prone to change.  

In your case you have some incrementation of week if Procedure = 6 AND
new.Flag = 1.  The database would not be *inconsistent* if that rule
were violated; it would only be inaccurate insofar as it failed to
reflect the world it models.  A different interpretation of world and
model could be used without changing the database or requiring that
particular row relationship. 

HTH.  

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


Re: [sqlite] Triggers and CTE's

2014-11-11 Thread 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


Re: [sqlite] Triggers and CTE's

2014-11-11 Thread 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

2014-11-11 Thread 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

2014-11-11 Thread 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

2014-11-11 Thread 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

2014-11-11 Thread 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

2014-11-11 Thread 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

2014-11-11 Thread 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


[sqlite] Triggers and CTE's

2014-11-11 Thread 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