date:20200307

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 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;
>>
>>

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] Bug in SQLite version 3.31.1 and 3.32?

2020-03-07 Thread Joe Mucchiello

 I just want to point something out that might help the original poster.

On Saturday, March 7, 2020, 7:00:21 AM EST, 
sqlite-users-requ...@mailinglists.sqlite.org 
 wrote:  > 
> 1.  NULL is NULL = Yes, True,
> 2.  NULL is FALSE = Nope, False.
> 3.  NULL is TRUE  = Nope, False.
> 4.  NULL is NOT NULL = Nope, False,
> 5.  NULL is NOT FALSE = Yep, True.
> 6.  NULL is NOT TRUE = Yep, True.
> 7. TRUE is FALSE  = Nope, False.
> 8. TRUE is NOT FALSE = Yep, True.
> 9. FALSE is NOT TRUE = Yep, True.
This explanation 100% correct and probably 80% confusing without the following, 
especially because "is" is not capitalized:IS and IS NOT are logical operators 
in SQL. NOT is not a unary operator when preceded by IS.

In most non-relational languages "NULL IS NOT TRUE" is parsed as:

value(NULL) operator(IS) (operator(NOT) value(TRUE)). 

That is NOT how SQL works. In SQL, it is:

value(NULL) operator(IS NOT) value(TRUE).


  
___
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] speeding up an FTS5 search with a JOIN

2020-03-07 Thread Dan Kennedy



On 7/3/63 14:58, P Kishor wrote:

I asked this question on Stackoverflow with not much success, and a suggestion 
to ask it on the list. So here I am. I have two tables, t1(id, t1Id, … other 
cols …, fullText) and a FTS5 virtual table vt1(t1Id, fullText)

```
sqlite> EXPLAIN QUERY PLAN
...> SELECT Count(*) as num FROM t1 WHERE deleted = 0;

QUERY
PLAN
--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=?)

sqlite> SELECT Count(*) as num FROM t1 WHERE deleted = 0;
308498

Run Time
: real 0.043 user 0.023668 sys 0.009005
```


As can be see above, the actual query takes ~43ms

```
sqlite> EXPLAIN QUERY PLAN
...> SELECT Count(*) as num FROM vt1 WHERE vt1 MATCH 'foo';

QUERY
PLAN
--SCAN TABLE vt1 VIRTUAL TABLE INDEX 131073:

sqlite> SELECT Count(*) as num FROM vt1 WHERE vt1 MATCH 'foo';
80789

Run Time
: real 0.047 user 0.008021 sys 0.009640
```

The actual query, in this case, takes ~47ms. So far so good. But the problem 
occurs when I join the two tables

```
sqlite> EXPLAIN QUERY PLAN
...> SELECT Count(*) as num
...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
...> WHERE t1.deleted = 0 AND vt1 MATCH 'foo';

QUERY
PLAN
|--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
  --SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (t1Id=?)

sqlite> SELECT Count(*) as num
...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
...> WHERE t1.deleted = 0 AND vt1 MATCH 'foo';
80789



That does seem slow. Are there many rows in table "t1" with t1.deleted 
set to something other than 0? What does:


  SELECT count(*) AS num FROM t1 JOIN vt1 ON t1.t1Id=vt1.t1Id WHERE vt1 
MATCH 'foo';


return?

Dan.






Run Time
: real 26.218 user 1.396376 sys 5.413630
```

The answer is correct but the query takes more than 26 seconds! Of course, I 
would like to speed up this query by several orders of magnitude, but I would 
also like to understand why this join is causing the slowdown.

Now, the reason I have constructed a query like this is because users can add 
further constraints for the table t1. For example,

```
sqlite> SELECT Count(*) as num
...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
...> WHERE t1.deleted = 0 AND
...> WHERE t1.frob = ‘bar' AND
...> WHERE t1.nob = ‘baz' AND
...> vt1 MATCH 'foo’;
```

Also, in every operation, for every given constraint, two queries are 
performed, one that returns the count and the other that returns the actual 
columns. And, finally, only a subset of the results are returned using LIMIT 
and OFFSET but *after* a sort ORDER has been prescribed. So, in reality, the 
last constraint above would result in the following

```
sqlite> SELECT Count(*) as num
...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
...> WHERE t1.deleted = 0 AND
...> WHERE t1.frob = ‘bar' AND
...> WHERE t1.nob = ‘baz' AND
...> vt1 MATCH 'foo’;
20367

sqlite> SELECT t1.id, t1.t1Id, … other cols …,
...> snippet(vt1, 1, "", "", "", 50) AS context,
...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
...> WHERE t1.deleted = 0 AND
...> WHERE t1.frob = ‘bar' AND
...> WHERE t1.nob = ‘baz' AND
...> vt1 MATCH ‘foo’
...> ORDER BY 
...> LIMIT 30 OFFSET ;
```

When no t1 columns are prescribed in the constraint, the default count (shown 
above) and default cols are returned with the FTS search

```
sqlite> SELECT Count(*) as num
...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
...> WHERE t1.deleted = 0
...> vt1 MATCH 'foo’;
20367

sqlite> SELECT t1.id, t1.t1Id, … other cols …,
...> snippet(vt1, 1, "", "", "", 50) AS context,
...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
...> WHERE t1.deleted = 0
...> vt1 MATCH ‘foo’
...> ORDER BY 
...> LIMIT 30 OFFSET 0;
```
___
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

[sqlite] speeding up an FTS5 search with a JOIN

2020-03-07 Thread P Kishor

I asked this question on Stackoverflow with not much success, and a suggestion 
to ask it on the list. So here I am. I have two tables, t1(id, t1Id, … other 
cols …, fullText) and a FTS5 virtual table vt1(t1Id, fullText)

```
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT Count(*) as num FROM t1 WHERE deleted = 0;

QUERY 
PLAN
--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=?)

sqlite> SELECT Count(*) as num FROM t1 WHERE deleted = 0;
308498

Run Time
: real 0.043 user 0.023668 sys 0.009005
```


As can be see above, the actual query takes ~43ms

```
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT Count(*) as num FROM vt1 WHERE vt1 MATCH 'foo';

QUERY 
PLAN
--SCAN TABLE vt1 VIRTUAL TABLE INDEX 131073:

sqlite> SELECT Count(*) as num FROM vt1 WHERE vt1 MATCH 'foo';
80789

Run Time
: real 0.047 user 0.008021 sys 0.009640
```

The actual query, in this case, takes ~47ms. So far so good. But the problem 
occurs when I join the two tables

```
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT Count(*) as num
   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
   ...> WHERE t1.deleted = 0 AND vt1 MATCH 'foo';

QUERY 
PLAN
|--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
 --SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (t1Id=?)

sqlite> SELECT Count(*) as num
   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
   ...> WHERE t1.deleted = 0 AND vt1 MATCH 'foo';
80789

Run Time
: real 26.218 user 1.396376 sys 5.413630
```

The answer is correct but the query takes more than 26 seconds! Of course, I 
would like to speed up this query by several orders of magnitude, but I would 
also like to understand why this join is causing the slowdown.

Now, the reason I have constructed a query like this is because users can add 
further constraints for the table t1. For example, 

```
sqlite> SELECT Count(*) as num
   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
   ...> WHERE t1.deleted = 0 AND 
   ...> WHERE t1.frob = ‘bar' AND 
   ...> WHERE t1.nob = ‘baz' AND 
   ...> vt1 MATCH 'foo’;
```

Also, in every operation, for every given constraint, two queries are 
performed, one that returns the count and the other that returns the actual 
columns. And, finally, only a subset of the results are returned using LIMIT 
and OFFSET but *after* a sort ORDER has been prescribed. So, in reality, the 
last constraint above would result in the following

```
sqlite> SELECT Count(*) as num
   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
   ...> WHERE t1.deleted = 0 AND 
   ...> WHERE t1.frob = ‘bar' AND 
   ...> WHERE t1.nob = ‘baz' AND 
   ...> vt1 MATCH 'foo’;
20367

sqlite> SELECT t1.id, t1.t1Id, … other cols …, 
   ...> snippet(vt1, 1, "", "", "", 50) AS context,
   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
   ...> WHERE t1.deleted = 0 AND 
   ...> WHERE t1.frob = ‘bar' AND 
   ...> WHERE t1.nob = ‘baz' AND 
   ...> vt1 MATCH ‘foo’
   ...> ORDER BY 
   ...> LIMIT 30 OFFSET ;
```

When no t1 columns are prescribed in the constraint, the default count (shown 
above) and default cols are returned with the FTS search

```
sqlite> SELECT Count(*) as num
   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
   ...> WHERE t1.deleted = 0 
   ...> vt1 MATCH 'foo’;
20367

sqlite> SELECT t1.id, t1.t1Id, … other cols …, 
   ...> snippet(vt1, 1, "", "", "", 50) AS context,
   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
   ...> WHERE t1.deleted = 0 
   ...> vt1 MATCH ‘foo’
   ...> ORDER BY 
   ...> LIMIT 30 OFFSET 0;
```
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger name missing

Re: [sqlite] Trigger name missing

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

Re: [sqlite] Trigger name missing

Re: [sqlite] speeding up an FTS5 search with a JOIN

[sqlite] speeding up an FTS5 search with a JOIN

6 matches

Site Navigation

Mail list logo

Footer information