Re: [sqlite] Trigger name missing

2020-03-07 Thread Keith Medcalf 

You mean like take a "boo" at the defined triggers?

select name, tbl_name, sql from sqlite_master where type='trigger';

would pretty much make clear that the defined trigger is not what you thought 
it was ...

create table x(x);
create trigger after insert on x begin select 1; end;

select name, tbl_name, sql from sqlite_master where type='trigger';
after|x|CREATE TRIGGER after insert on x begin select 1; end

seems pretty clear that the sql statement creates a trigger called "after" on a 
table called "x" and that the after was parsed as the trigger name.


-- 
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  On
>Behalf Of John G
>Sent: Saturday, 7 March, 2020 09:49
>To: SQLite mailing list 
>Subject: Re: [sqlite] Trigger name missing
>
>Would it be possible to create an SQL verification program, which just
>like
>'sqlite3_analyzer' and 'sqldiff' could be run separately?
>It could *warn* about apparently incompletely defined triggers and other
>possible pitfalls.
>
>Then developers could use it before installing the next version of
>SQLite3.
>
>John G
>
>On Wed, 26 Feb 2020 at 19:09, Jean-Luc Hainaut luc.hain...@unamur.be>
>wrote:
>
>> On 26/02/2020 12:18, Richard Hipp wrote:
>> > On 2/26/20, Jean-Luc Hainaut  wrote:
>> >> Hi all,
>> >>
>> >> It seems that SQLite (version 31.1) accepts a trigger declaration in
>> >> which the name is missing. When fired, this trigger doesn't crashes
>but
>> >> exhibits a strange behaviour. In particular, while expression
>> >> "new." in an "insert" trigger returns the correct value,
>the
>> >> equivalent expression "select  from T where Id = new.Id"
>always
>> >> returns null (column "Id" is the PK of table "T"). Similarly,
>"update T
>> >> set  =   where Id = new.Id" (silently)
>fails.
>> >>
>> > What is the text of your trigger?
>>
>> This trigger belongs to a small experimental application I'm writting
>to
>> study the extent to what application code (initially in Java, Python,
>> etc.) can be integrated into SQL, notably through triggers. In short,
>> can one convert a standard 3-tier business application into just a GUI
>+
>> an active database, without the standard application program between
>them?
>> The following trigger controls the registration of a customer order
>> [insert into CUSTORDER(OrdID, CustID,ItemID,Qty,...) values (...)] when
>> the available quantity (Qavail) of the requested item is sufficient.
>> If the name 'CORD_INS1' is missing, this trigger (among others):
>>- updates the ITEM table. [successfully]
>>- completes the customer order (Price and State in CUSTORDER).
>[fails]
>>- creates an invoice (in CUSTINVOICE) and prints it in a text file.
>> [successfully]
>>
>> After reading all your explanations and comments, my interpretation is
>> as follows:
>> 1. The SQLite syntax tells me that the "before/after/instead of"
>keyword
>> can be missing, in which case (I guess) "before" is assumed.
>> 2. So, my "name-less" trigger is valid and must be read:
>> create trigger "after" before insert on CUSTORDER ...
>> 3. In a "before" trigger, the current row cannot be updated, since it
>> doesn't exist yet (though several RDBMS have a specific syntax for
>that).
>> 4. This explains why SQLite legitimely ignores the second update.
>> Am I right?
>> If I am, this behaviour is "not a bug but a feature". It could be
>useful
>> to precise these facts in the documentation.
>>
>> Thanks to all
>>
>> Jean-Luc Hainaut
>>
>> create table CUSTOMER (CustID,Name,Address,City,Account,...);
>> create table ITEM
>> (ItemID,Description,Price,QonHand,Qord,Qavail,Qmargin,...);
>> create table CUSTORDER
>(OrdID,DateOrd,CustID,ItemID,Price,Qty,State,...);
>> create table CUSTINVOICE
>> (InvID,DateInv,OrdID,CustID,ItemID,Price,Qty,Amount,State,...);
>> create table SUPPLIER (SuppID,Name,City,...);
>> create table OFFER (SuppID,ItemID,Price,Delay,...);
>> create table SUPPORDER
>(OrdID,DateOrd,ItemID,SuppID,Price,Qty,State,...);
>>
>> create trigger CORD_INS1
>> after insert on CUSTORDER
>> for each row
>> when new.Qty <= (select Qavail from ITEM where ItemID = new.ItemID)
>> and  not exists (select * from CUSTINVOICE where

Re: [sqlite] Trigger name missing

2020-03-07 Thread Simon Slavin 
On 7 Mar 2020, at 4:49pm, John G  wrote:

> Would it be possible to create an SQL verification program, which just like 
> 'sqlite3_analyzer' and 'sqldiff' could be run separately?
> It could *warn* about apparently incompletely defined triggers and other 
> possible pitfalls.

The shell tool has this ability, by executing the following PRAGMAs:





Used in the above order it will spot several kinds of corruption.  It can be 
scripted from your OS command-line shell:


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

Re: [sqlite] Trigger name missing

2020-03-07 Thread John G 
Would it be possible to create an SQL verification program, which just like
'sqlite3_analyzer' and 'sqldiff' could be run separately?
It could *warn* about apparently incompletely defined triggers and other
possible pitfalls.

Then developers could use it before installing the next version of SQLite3.

John G

On Wed, 26 Feb 2020 at 19:09, Jean-Luc Hainaut 
wrote:

> On 26/02/2020 12:18, Richard Hipp wrote:
> > On 2/26/20, Jean-Luc Hainaut  wrote:
> >> Hi all,
> >>
> >> It seems that SQLite (version 31.1) accepts a trigger declaration in
> >> which the name is missing. When fired, this trigger doesn't crashes but
> >> exhibits a strange behaviour. In particular, while expression
> >> "new." in an "insert" trigger returns the correct value, the
> >> equivalent expression "select  from T where Id = new.Id" always
> >> returns null (column "Id" is the PK of table "T"). Similarly, "update T
> >> set  =   where Id = new.Id" (silently) fails.
> >>
> > What is the text of your trigger?
>
> This trigger belongs to a small experimental application I'm writting to
> study the extent to what application code (initially in Java, Python,
> etc.) can be integrated into SQL, notably through triggers. In short,
> can one convert a standard 3-tier business application into just a GUI +
> an active database, without the standard application program between them?
> The following trigger controls the registration of a customer order
> [insert into CUSTORDER(OrdID, CustID,ItemID,Qty,...) values (...)] when
> the available quantity (Qavail) of the requested item is sufficient.
> If the name 'CORD_INS1' is missing, this trigger (among others):
>- updates the ITEM table. [successfully]
>- completes the customer order (Price and State in CUSTORDER). [fails]
>- creates an invoice (in CUSTINVOICE) and prints it in a text file.
> [successfully]
>
> After reading all your explanations and comments, my interpretation is
> as follows:
> 1. The SQLite syntax tells me that the "before/after/instead of" keyword
> can be missing, in which case (I guess) "before" is assumed.
> 2. So, my "name-less" trigger is valid and must be read:
> create trigger "after" before insert on CUSTORDER ...
> 3. In a "before" trigger, the current row cannot be updated, since it
> doesn't exist yet (though several RDBMS have a specific syntax for that).
> 4. This explains why SQLite legitimely ignores the second update.
> Am I right?
> If I am, this behaviour is "not a bug but a feature". It could be useful
> to precise these facts in the documentation.
>
> Thanks to all
>
> Jean-Luc Hainaut
>
> create table CUSTOMER (CustID,Name,Address,City,Account,...);
> create table ITEM
> (ItemID,Description,Price,QonHand,Qord,Qavail,Qmargin,...);
> create table CUSTORDER (OrdID,DateOrd,CustID,ItemID,Price,Qty,State,...);
> create table CUSTINVOICE
> (InvID,DateInv,OrdID,CustID,ItemID,Price,Qty,Amount,State,...);
> create table SUPPLIER (SuppID,Name,City,...);
> create table OFFER (SuppID,ItemID,Price,Delay,...);
> create table SUPPORDER (OrdID,DateOrd,ItemID,SuppID,Price,Qty,State,...);
>
> create trigger CORD_INS1
> after insert on CUSTORDER
> for each row
> when new.Qty <= (select Qavail from ITEM where ItemID = new.ItemID)
> and  not exists (select * from CUSTINVOICE where OrdID = new.OrdID)
> begin
> --
> -- Subtract Qty from Qavail:
> update ITEM
> set   Qavail = Qavail - new.Qty
> where ItemID = new.ItemID;
> --
> --...
> -- Set CUSTORDER.State to 'invoiced' or 'pending'
> update CUSTORDER
> set   Price = (select Price from ITEM where ItemID = new.ItemID),
>   State = case when new.Qty <= (select QonHand from ITEM where
> ItemID = new.ItemID)
>then 'invoiced'
>else 'pending'
>   end
> where OrdID = new.OrdID;
> --
> -- Create an invoice and print it:
> insert into CUSTINVOICE(...);
> --
> 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] Trigger name missing

2020-02-26 Thread Jean-Luc Hainaut 

On 26/02/2020 12:18, Richard Hipp wrote:

On 2/26/20, Jean-Luc Hainaut  wrote:

Hi all,

It seems that SQLite (version 31.1) accepts a trigger declaration in
which the name is missing. When fired, this trigger doesn't crashes but
exhibits a strange behaviour. In particular, while expression
"new." in an "insert" trigger returns the correct value, the
equivalent expression "select  from T where Id = new.Id" always
returns null (column "Id" is the PK of table "T"). Similarly, "update T
set  =   where Id = new.Id" (silently) fails.


What is the text of your trigger?


This trigger belongs to a small experimental application I'm writting to 
study the extent to what application code (initially in Java, Python, 
etc.) can be integrated into SQL, notably through triggers. In short, 
can one convert a standard 3-tier business application into just a GUI + 
an active database, without the standard application program between them?
The following trigger controls the registration of a customer order 
[insert into CUSTORDER(OrdID, CustID,ItemID,Qty,...) values (...)] when 
the available quantity (Qavail) of the requested item is sufficient.

If the name 'CORD_INS1' is missing, this trigger (among others):
  - updates the ITEM table. [successfully]
  - completes the customer order (Price and State in CUSTORDER). [fails]
  - creates an invoice (in CUSTINVOICE) and prints it in a text file. 
[successfully]


After reading all your explanations and comments, my interpretation is 
as follows:
1. The SQLite syntax tells me that the "before/after/instead of" keyword 
can be missing, in which case (I guess) "before" is assumed.

2. So, my "name-less" trigger is valid and must be read:
   create trigger "after" before insert on CUSTORDER ...
3. In a "before" trigger, the current row cannot be updated, since it 
doesn't exist yet (though several RDBMS have a specific syntax for that).

4. This explains why SQLite legitimely ignores the second update.
Am I right?
If I am, this behaviour is "not a bug but a feature". It could be useful 
to precise these facts in the documentation.


Thanks to all

Jean-Luc Hainaut

create table CUSTOMER (CustID,Name,Address,City,Account,...);
create table ITEM 
(ItemID,Description,Price,QonHand,Qord,Qavail,Qmargin,...);

create table CUSTORDER (OrdID,DateOrd,CustID,ItemID,Price,Qty,State,...);
create table CUSTINVOICE 
(InvID,DateInv,OrdID,CustID,ItemID,Price,Qty,Amount,State,...);

create table SUPPLIER (SuppID,Name,City,...);
create table OFFER (SuppID,ItemID,Price,Delay,...);
create table SUPPORDER (OrdID,DateOrd,ItemID,SuppID,Price,Qty,State,...);

create trigger CORD_INS1
after insert on CUSTORDER
for each row
when new.Qty <= (select Qavail from ITEM where ItemID = new.ItemID)
and  not exists (select * from CUSTINVOICE where OrdID = new.OrdID)
begin
   --
   -- Subtract Qty from Qavail:
   update ITEM
   set   Qavail = Qavail - new.Qty
   where ItemID = new.ItemID;
   --
   --...
   -- Set CUSTORDER.State to 'invoiced' or 'pending'
   update CUSTORDER
   set   Price = (select Price from ITEM where ItemID = new.ItemID),
 State = case when new.Qty <= (select QonHand from ITEM where 
ItemID = new.ItemID)

  then 'invoiced'
  else 'pending'
 end
   where OrdID = new.OrdID;
   --
   -- Create an invoice and print it:
   insert into CUSTINVOICE(...);
   --
end;

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

Re: [sqlite] Trigger name missing

2020-02-26 Thread Jose Isaias Cabrera 

SQLite is even better than I thought...


From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Wednesday, February 26, 2020 11:44 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Trigger name missing

On 2/26/20, Simon Slavin  wrote:
>
> Backward compatibility ?  Do you think anyone who used the word AFTER
> really wants a BEFORE trigger ?  More likely to be a bug they should know
> about.

We have seen triggers like this in the wild, that work as intended.
If we change it to throw an error, the applications that use these
kinds of triggers will suddenly start failing.  Some of them (no
doubt) are unmaintained.  The source code has gone missing for some of
them, perhaps.  That much breakage is not worth it.

The issue arises from the forgiving nature of the SQLite parser.  The
parser is designed so that we can add new keywords to the language
(for example:  "GENERATED" and "ALWAYS" in the most recent release, in
support of generated columns) without breaking legacy schemas that use
those keywords as table or column names.

Consider what happens in the example Dan provide:

  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
...
  END;

The parser is bebooping along, parsing tokens one by one.  The first
token is the keyword CREATE.  The second token is the keyword TRIGGER.
All good so far.  The third token is the keyword AFTER.  But the
grammar does not recognize the keyword AFTER in that context, and so
the parser converts it into an identifier with the value of "AFTER".
That does work, and so the parse continues, using "AFTER" as the name
of the trigger.  That example is a little confusing.  But what if,
instead, the trigger has been this:

  CREATE TRIGGER generated INSERT ON t1 BEGIN
...
  END;

With strict enforcement of keywords, this trigger would have worked
fine for all versions of SQLite through 3.30.1 and then started
failing in version 3.31.0, because it was in that release that
GENERATED became a keyword.  But with the "fallback" mechanism in
SQLite, the trigger continues to work as it always has.  That is why
the mechanism exists - to prevent unnecessary breakage when new
keywords are added to the language.

There are literally millions of applications that use SQLite.  Some
fraction of those are unmaintained.  And some additional fraction of
those will break, probably to never work again, whenever we add a
keyword, except for the keyword fallback mechanism.
--
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] Trigger name missing

2020-02-26 Thread Richard Hipp 
On 2/26/20, Simon Slavin  wrote:
>
> Backward compatibility ?  Do you think anyone who used the word AFTER
> really wants a BEFORE trigger ?  More likely to be a bug they should know
> about.

We have seen triggers like this in the wild, that work as intended.
If we change it to throw an error, the applications that use these
kinds of triggers will suddenly start failing.  Some of them (no
doubt) are unmaintained.  The source code has gone missing for some of
them, perhaps.  That much breakage is not worth it.

The issue arises from the forgiving nature of the SQLite parser.  The
parser is designed so that we can add new keywords to the language
(for example:  "GENERATED" and "ALWAYS" in the most recent release, in
support of generated columns) without breaking legacy schemas that use
those keywords as table or column names.

Consider what happens in the example Dan provide:

  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
...
  END;

The parser is bebooping along, parsing tokens one by one.  The first
token is the keyword CREATE.  The second token is the keyword TRIGGER.
All good so far.  The third token is the keyword AFTER.  But the
grammar does not recognize the keyword AFTER in that context, and so
the parser converts it into an identifier with the value of "AFTER".
That does work, and so the parse continues, using "AFTER" as the name
of the trigger.  That example is a little confusing.  But what if,
instead, the trigger has been this:

  CREATE TRIGGER generated INSERT ON t1 BEGIN
...
  END;

With strict enforcement of keywords, this trigger would have worked
fine for all versions of SQLite through 3.30.1 and then started
failing in version 3.31.0, because it was in that release that
GENERATED became a keyword.  But with the "fallback" mechanism in
SQLite, the trigger continues to work as it always has.  That is why
the mechanism exists - to prevent unnecessary breakage when new
keywords are added to the language.

There are literally millions of applications that use SQLite.  Some
fraction of those are unmaintained.  And some additional fraction of
those will break, probably to never work again, whenever we add a
keyword, except for the keyword fallback mechanism.
-- 
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

Re: [sqlite] Trigger name missing

2020-02-26 Thread Simon Slavin 
On 26 Feb 2020, at 2:15pm, Dan Kennedy  wrote:

> A statement like the following creates a "BEFORE" trigger named "AFTER". Does 
> that explain things?
> 
>   CREATE TRIGGER AFTER INSERT ON t1 BEGIN
> ...
>   END;
> 
> I find I fall into this trap about once every 18 months...

If only you knew someone with the power to make this generate SQLITE_MISUSE so 
that you, and other programmers, don't have this problem.  Oh well.

(Backward compatibility ?  Do you think anyone who used the word AFTER really 
wants a BEFORE trigger ?  More likely to be a bug they should know about.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger name missing

2020-02-26 Thread Dan Kennedy 


On 26/2/63 16:31, Jean-Luc Hainaut wrote:

Hi all,

It seems that SQLite (version 31.1) accepts a trigger declaration in 
which the name is missing. When fired, this trigger doesn't crashes 
but exhibits a strange behaviour. In particular, while expression 
"new." in an "insert" trigger returns the correct value, the 
equivalent expression "select  from T where Id = new.Id" 
always returns null (column "Id" is the PK of table "T"). Similarly, 
"update T set  =   where Id = new.Id" 
(silently) fails.



A statement like the following creates a "BEFORE" trigger named "AFTER". 
Does that explain things?


  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
    ...
  END;

I find I fall into this trap about once every 18 months...

Dan.





Not critical but annoying if you are, like me, absent minded when 
typing code!


Regards

Jean-Luc Hainaut

___
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] Trigger name missing

2020-02-26 Thread Richard Hipp 
On 2/26/20, Jean-Luc Hainaut  wrote:
> Hi all,
>
> It seems that SQLite (version 31.1) accepts a trigger declaration in
> which the name is missing. When fired, this trigger doesn't crashes but
> exhibits a strange behaviour. In particular, while expression
> "new." in an "insert" trigger returns the correct value, the
> equivalent expression "select  from T where Id = new.Id" always
> returns null (column "Id" is the PK of table "T"). Similarly, "update T
> set  =   where Id = new.Id" (silently) fails.
>

What is the text of your trigger?
-- 
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] Trigger name missing

2020-02-26 Thread Jean-Luc Hainaut 

Hi all,

It seems that SQLite (version 31.1) accepts a trigger declaration in 
which the name is missing. When fired, this trigger doesn't crashes but 
exhibits a strange behaviour. In particular, while expression 
"new." in an "insert" trigger returns the correct value, the 
equivalent expression "select  from T where Id = new.Id" always 
returns null (column "Id" is the PK of table "T"). Similarly, "update T 
set  =   where Id = new.Id" (silently) fails.


Not critical but annoying if you are, like me, absent minded when typing 
code!


Regards

Jean-Luc Hainaut

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