Re: [sqlite] Simplify multiple use of value in a trigger

[James K. Lowden]

On Sat, 7 Jan 2017 17:54:20 -0500
Gan Uesli Starling  wrote:

> As for the suggestion to 'normalize' my four tables to one ... yes, I 
> could  do that ... but it would make for a LOT of redundant data 
> duplicated in plural columns, which seems to me in my doubtless
> ignorant newbie status, as missing the point for a database. 

Hello Gan! 

It's been 15 years and counting.  Funny our paths should cross here.  

Regarding your table design, I wouldn't necessarly combine your table
regions, but I would change how you define your aggregations.  

I noticed in your SQL the comment "SQL doesn't support arrays".
Understanding *why* there are no arrays in SQL is a step in
understanding how to design and use SQL databases.  In SQL generally --
and in the relational model, strictly -- all data are dealt with as
*sets*.  Not lists, not arrays.  Set elements have no defined order.
That is, unlike an array, all attributes are explicitly expressed as
data.  No meaning attaches to the fact that an element is the Nth
element of the set. (You doubtless at some time have come across the
problem of dealing with an array element and not knowing where it stood
in the array.  In SQL, that information must be explicit in order to
exist!)  

The advantage of dealing with a single datatype is manifested in the
simplicity of the relational operators.  By virtue of adding just one
column, arrays become tables and are, yes, supported.  To a one,
flavors of SQL with array support do so with limitations, even though
they *only* add complexity.  There is no array operation that cannot be
expressed in SQL; ergo there is is no need for array notation in SQL.  

Once you think of your arrays as vertical instead of horizontal, if you
will, your tables become easier to design and manipulate.  For example,
instead of 

CREATE TABLE world (
iso CHAR(2) PRIMARY KEY,
un CHAR(3),
country VARCHAR(45),
count SMALLINT DEFAULT 0, /* Total Count, needs daily update */
_160m SMALLINT DEFAULT 0, /* Band Count, need daily update for
each */ _80m SMALLINT DEFAULT 0,
_60m SMALLINT DEFAULT 0,
...

I would suggest two tables: 

CREATE TABLE world (
iso CHAR(2) PRIMARY KEY,
un CHAR(3),
country VARCHAR(45)
);

Create Table Aggr ( 
iso CHAR(2) not NULL foreign key references world(iso), 
size int not NULL, 
total int not NULL,  -- not "count" because keyword
primary key (iso, size)
);

Now, it's not clear to me that the Aggr table is well advised.  I think
it might better be a view.  Definitely I would define it as a view and
try updating the table from that view.  If you're frequently
referencing whole chunks of Aggr, it might make sense to maintain the
table.  If you're usually referencing only a few rows of it, it
probably makes more sense to keep just the view.  

In any case, updating the above table is simpler and almost certainly
faster.  Instead of one update per column (which IIUC is the genesis of
this thread), 

UPDATE world SET _160m = (SELECT count from country_160m WHERE country
= world.country) WHERE country IN (SELECT country FROM country_160m);

use one update per table.  First, make a tiny static table Bands of the
interesting bands you want (160, 80, etc.).  Then: 

update Aggr set total = (
select count(*) from "something" 
-- not sure what the real source is
where size = Bands.size
and iso = Aggr.iso
and size = Aggr.size
) 
where exists (
select 1 from "something"
where iso = Aggr.iso
and size = Aggr.size
);

That updates all bands for all countries in one swell foop.  

If you prefer to see bands-by-country, as in your world table, create a
view that uses a technique known as "folding".  It's verbose (as SQL is
wont) but it can be surprisingly fast.  But don't be two hasty: except
for reports, most queries against the Aggr table won't need more than a
view rows.  It is more efficient and as easy to use "world" would be.
Instead of, 

select ... from world where _160m > 7;

you have 

select ... from Aggr where size = 160 and total > 7;

The SQL becomes more regular because you don't have to write different
queries depending on which size (band) you're interested in.  The
execution is faster because there's less I/O: the row is narrower, and
it's found by a binary search on the index supporting the primary key.
By contrast, your "world" table would need an index on every _nnnm
column which, besides being tedious to define, would increase the cost
of your inserts.  

One last word on aggregation and views.  

Many programmers seem to come to SQL with the instinct of pre-computing
their aggregates.  Most programming languages have no support for
computation with sets, no built-in support for aggregation and
quantification.  Furthermore, because most applications deal with a
static snapshot of the data, the instinct 

Re: [sqlite] Simplify multiple use of value in a trigger

[Simon Slavin]

On 8 Jan 2017, at 12:46pm, Gan Uesli Starling  wrote:

> But it seems theres no way to make that a single, all-in-one-go kind of query.

Are you aware of SELECT … JOIN … ?

SELECT state.this,country.that FROM state
JOIN country ON country.name = state.country
WHERE state.name = "Washington"

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

Re: [sqlite] Simplify multiple use of value in a trigger

[Gan Uesli Starling]

Okay, this trigger what works. Not really an answer, since there's no 
simplification achieved. Still thought I'd post it though, as  a 
conclusion-of-sorts nonetheless. It's neither clever nor elegant, but it 
does work. Hammer and tongs, is what it is. There's no decision tree for 
choosing when to update correctly, only updates that fail at the end for 
not having matched when the very last "=" comes up "nope".


Now having hammered past that (because I'm stubborn) now I feel free to 
attempt other ways, firstly a query to aggregate the same kind of data 
instead as a view. Already I have puzzled out a way involving several 
queries, one of which  builds a temporary table. But it seems theres no 
way to make that a single, all-in-one-go kind of query. At least not as 
yet. Good thing there's Perl. I know that I can cue (queue?) them with 
Perl. But just at the moment, that's not the point. I'm wanting to teach 
myself pure-SQL ways, where such exist. Thanks to all who offered advice.



-- Accumulate for 160m band, QSO-by-QSO.
DROP TRIGGER IF EXISTS _160m_USA_Increment;
CREATE TRIGGER _160m_USA_Increment
AFTER UPDATE ON qso WHEN NEW.freq LIKE '1.%'
BEGIN
UPDATE world
SET _160m = (SELECT _160m+1 FROM world WHERE country = (SELECT 
country FROM qth WHERE rowid = NEW.qth_other_id)),
count = (SELECT count+1 FROM world WHERE country = (SELECT 
country FROM qth WHERE rowid = NEW.qth_other_id))
WHERE country = (SELECT country FROM qth WHERE rowid = 
NEW.qth_other_id);

UPDATE usa_state
SET _160m = (SELECT _160m+1 FROM usa_state WHERE state = 
(SELECT state FROM qth WHERE rowid = NEW.qth_other_id)),
count = (SELECT count+1 FROM usa_state WHERE state = 
(SELECT state FROM qth WHERE rowid = NEW.qth_other_id))
WHERE state = (SELECT state FROM qth WHERE rowid = 
NEW.qth_other_id)
AND 'USA' = (SELECT country FROM qth WHERE rowid = 
NEW.qth_other_id);

UPDATE mx_state
SET _160m = (SELECT _160m+1 FROM mx_state WHERE state = (SELECT 
state FROM qth WHERE rowid = NEW.qth_other_id)),
count = (SELECT count+1 FROM mx_state WHERE state = (SELECT 
state FROM qth WHERE rowid = NEW.qth_other_id))
WHERE state = (SELECT state FROM qth WHERE rowid = 
NEW.qth_other_id)
AND 'Mexico' = (SELECT country FROM qth WHERE rowid = 
NEW.qth_other_id);

UPDATE ca_prov
SET _160m = (SELECT _160m+1 FROM ca_prov WHERE prov = (SELECT 
state FROM qth WHERE rowid = NEW.qth_other_id)),
count = (SELECT count+1 FROM ca_prov WHERE prov = (SELECT 
state FROM qth WHERE rowid = NEW.qth_other_id))
WHERE prov = (SELECT state FROM qth WHERE rowid = 
NEW.qth_other_id)
AND 'Canada' = (SELECT country FROM qth WHERE rowid = 
NEW.qth_other_id);

END;



--

 Mistera Sturno - Rarest Extinct Bird

 <(+)__   Ĝan Ŭesli Starling
  ((__/)=-Holland, MI, USA
   `||`
++http://starling.us
  http://esperanto.us
  http://ky8d.net

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

Re: [sqlite] Simplify multiple use of value in a trigger

[Simon Slavin]

On 7 Jan 2017, at 10:54pm, Gan Uesli Starling  wrote:

> As for the suggestion to 'normalize' my four tables to one ... yes, I could  
> do that ... but it would make for a LOT of redundant data duplicated in 
> plural columns, which seems to me in my doubtless ignorant newbie status, as 
> missing the point for a database.

The suggestion is that you that you retain your 'world' and 'country' tables, 
but merge all the 'state' tables together, adding a 'country' column.

That way you don’t have to have different code to handle States of the USA, 
Provinces of Canada, States of Mexico, Couhties of England, Cantons of 
Switzerland, etc..  It will save a great deal of programming.

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

Re: [sqlite] Simplify multiple use of value in a trigger

[Gan Uesli Starling]

Okay. Thank you everyone for the suggestions given thus far. I am SQLite 
version 3.15.2 if that's of import.


Know all that the whole database project is on-line here: ky8d.net/sql

As for the suggestion to 'normalize' my four tables to one ... yes, I 
could  do that ... but it would make for a LOT of redundant data 
duplicated in plural columns, which seems to me in my doubtless ignorant 
newbie status, as missing the point for a database. Wouldn't that would 
make it just one big flat file? As countries have a name, a 2-letter ISO 
code and a 3-letter UN code, that would be 50 redundant column entries 
for the USA, 31 for Mexico and 16 for Canada. So from the start, I 
thought to make it an exercise in avoidance of ANY redundant storage 
wherever I possibly can. So that's my reason, such as it is.


Anyhow, Ryan Smith's reply is most to the point. Thank you, Ryan, 
especially in that regard. That answer touches on areas of SQLite which 
I am wanting to learn. I'd really like to get it working.


I do get a syntax error near CASE in the trigger from Ryan's example. It 
is curious, as I have before tried using CASE in triggers and always 
they seem to flag syntax errors. Might someone have a clue on that?


--

 Mistera Sturno - Rarest Extinct Bird

 <(+)__   Ĝan Ŭesli Starling
  ((__/)=-Holland, MI, USA
   `||`
++http://starling.us
  http://esperanto.us
  http://ky8d.net

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

Re: [sqlite] Simplify multiple use of value in a trigger

[R Smith]

I'm not sure that CTE's are allowed inside triggers, but even so, it will need 
to be appended to each update. I think a real table (albeit a temporary one) 
will do much better - It should simply be a placeholder for variables, but has 
the benefit of being able to be joined to other queries, etc.

This SQL code should work, though I can't test it since I don't have the DB, 
you might need to check it for syntax, but the premise should definitely work 
well. The left-joins will leave NULLs where items do not match and so the 
updates will ignore them (or the COALESCE will ignore in case some states exist 
in both countries).


DROP TRIGGER IF EXISTS _160m_Increment;
CREATE TRIGGER _160m_Increment
AFTER UPDATE ON qso WHEN
NEW.freq LIKE '1.%'
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS t160 (c TEXT, s TEXT, i_usa INT, i_mex 
INT, i_can INT, i_wld INT);
DELETE FROM t160;
INSERT INTO t160 (c, s, i_usa, i_mex, i_can, i_wld)
   SELECT qth.country, qth.state, u._160m+1, m._160m+1, c._160m+1, w._160m+1
 FROM qth WHERE rowid = NEW.qth_other_id
 LEFT JOIN usa_state AS u ON u.state   = qth.state
 LEFT JOIN mx_state  AS m ON m.state   = qth.state
 LEFT JOIN ca_prov   AS c ON c.prov= qth.state
 LEFT JOIN world AS w ON w.country = qth.country
WHERE qth.rowid = NEW.qth_other_id LIMIT 1;
UPDATE usa_state SET _160m = COALESCE((SELECT i_usa FROM t_160), _160m) 
WHERE state   = (SELECT s FROM t160);
UPDATE mx_state  SET _160m = COALESCE((SELECT i_mex FROM t_160), _160m) 
WHERE state   = (SELECT s FROM t160);
UPDATE ca_prov   SET _160m = COALESCE((SELECT i_can FROM t_160), _160m) 
WHERE prov= (SELECT s FROM t160);
UPDATE world SET _160m = COALESCE((SELECT i_wld FROM t_160), _160m) 
WHERE country = (SELECT c FROM t160);
END;

We never drop that temp table, since it is faster to just re-use it, and it 
will get dropped automatically when you close the connection - but that means 
we need to truncate it on use, hence the DELETE FROM.

Good luck!
Ryan


On 2017/01/07 5:34 PM, Kees Nuyt wrote:

On Sat, 7 Jan 2017 09:27:57 -0500, Gan Uesli Starling
 wrote:


So I'm trying to accumulate data for state/prov inside of USA, Canada
and Mexico, and country for the rest of the world.

Since country and state from the same update are factors in incrementing
each of four tables, I was hoping to simplify it with a single select
with 'AS' and then re-use the AS-value in every case. This as opposed to
the redundancy of selecting the same thing anew every time. See as
below. It passes syntax but fails on exectute, saying there is no column
's'. Is there some way to do this? To hold the 'state' and 'country' and
reuse them like a variable from one query to the next for sequential
comparisons?

SQLite doesn't support variables in the way you try to use here.

You can try one of the following:

1- Replace every c and s in the UPDATE statements
by (SELECT ...)

2- prefix every UPDATE with a common tabel expression
that delivers the reuired values, and use them with
a (SELECT ...) as above

3- perhaps do something smart with an updateble view using an
INSTEAD OF trigger, and supply the values once to the
UPDATE myview statement.


-- Trigger for incrementing country & region counts for 160m band,
QSO-by-QSO.
DROP TRIGGER IF EXISTS _160m_Increment;
CREATE TRIGGER _160m_Increment
AFTER UPDATE ON qso WHEN
 NEW.freq LIKE '1.%'
BEGIN
 SELECT country AS c FROM qth WHERE rowid = NEW.qth_other_id;
 SELECT state   AS s FROM qth WHERE rowid = NEW.qth_other_id;
 UPDATE usa_state SET _160m = 1 + (SELECT _160m FROM usa_state WHERE
state = s)   WHERE state = s AND c = 'USA';
 UPDATE mx_state  SET _160m = 1 + (SELECT _160m FROM mx_state WHERE
state = s)   WHERE state = s AND c = 'Mexico';
 UPDATE ca_prov   SET _160m = 1 + (SELECT _160m FROM ca_prov WHERE
prov  = s)   WHERE prov  = s AND c = 'Canada';
 UPDATE world SET _160m = 1 + (SELECT _160m FROM world WHERE
country = c) WHERE country = c;
END;


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

Re: [sqlite] Simplify multiple use of value in a trigger

[Simon Slavin]

On 7 Jan 2017, at 2:27pm, Gan Uesli Starling  wrote:

> CREATE TRIGGER _160m_Increment
> AFTER UPDATE ON qso WHEN
>NEW.freq LIKE '1.%'
> BEGIN
>SELECT country AS c FROM qth WHERE rowid = NEW.qth_other_id;
>SELECT state   AS s FROM qth WHERE rowid = NEW.qth_other_id;
>UPDATE usa_state SET _160m = 1 + (SELECT _160m FROM usa_state WHERE state 
> = s) WHERE state = s AND c = 'USA';
>UPDATE mx_state  SET _160m = 1 + (SELECT _160m FROM mx_state WHERE state = 
> s) WHERE state = s AND c = 'Mexico';
>UPDATE ca_prov   SET _160m = 1 + (SELECT _160m FROM ca_prov WHERE prov  = 
> s)   WHERE prov  = s AND c = 'Canada';
>UPDATE world SET _160m = 1 + (SELECT _160m FROM world WHERE country = 
> c) WHERE country = c;
> END;

Other have told you that SQLite does not have variables.

But ask yourself why you are bothering to keep a running total.  Why not just 
have SQLite calculate these numbers when you need them ?  With the correct 
indexes the calculation should take very little time.

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

Re: [sqlite] Simplify multiple use of value in a trigger

[Doug Currie]

On Sat, Jan 7, 2017 at 9:27 AM, Gan Uesli Starling  wrote:

> So I'm trying to accumulate data for state/prov inside of USA, Canada and
> Mexico, and country for the rest of the world.
>
> Since country and state from the same update are factors in incrementing
> each of four tables,
>

You should ask yourself why you have four tables instead of one table. This
(poor database normalization) is the root cause of the problem.

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

Re: [sqlite] Simplify multiple use of value in a trigger

[Kees Nuyt]

On Sat, 7 Jan 2017 09:27:57 -0500, Gan Uesli Starling
 wrote:

> So I'm trying to accumulate data for state/prov inside of USA, Canada 
> and Mexico, and country for the rest of the world.
>
> Since country and state from the same update are factors in incrementing 
> each of four tables, I was hoping to simplify it with a single select 
> with 'AS' and then re-use the AS-value in every case. This as opposed to 
> the redundancy of selecting the same thing anew every time. See as 
> below. It passes syntax but fails on exectute, saying there is no column 
> 's'. Is there some way to do this? To hold the 'state' and 'country' and 
> reuse them like a variable from one query to the next for sequential 
> comparisons?

SQLite doesn't support variables in the way you try to use here.

You can try one of the following:

1- Replace every c and s in the UPDATE statements 
   by (SELECT ...)

2- prefix every UPDATE with a common tabel expression 
   that delivers the reuired values, and use them with
   a (SELECT ...) as above

3- perhaps do something smart with an updateble view using an 
   INSTEAD OF trigger, and supply the values once to the 
   UPDATE myview statement.

> -- Trigger for incrementing country & region counts for 160m band, 
> QSO-by-QSO.
> DROP TRIGGER IF EXISTS _160m_Increment;
> CREATE TRIGGER _160m_Increment
> AFTER UPDATE ON qso WHEN
> NEW.freq LIKE '1.%'
> BEGIN
> SELECT country AS c FROM qth WHERE rowid = NEW.qth_other_id;
> SELECT state   AS s FROM qth WHERE rowid = NEW.qth_other_id;
> UPDATE usa_state SET _160m = 1 + (SELECT _160m FROM usa_state WHERE 
> state = s)   WHERE state = s AND c = 'USA';
> UPDATE mx_state  SET _160m = 1 + (SELECT _160m FROM mx_state WHERE 
> state = s)   WHERE state = s AND c = 'Mexico';
> UPDATE ca_prov   SET _160m = 1 + (SELECT _160m FROM ca_prov WHERE 
> prov  = s)   WHERE prov  = s AND c = 'Canada';
> UPDATE world SET _160m = 1 + (SELECT _160m FROM world WHERE 
> country = c) WHERE country = c;
> END;

-- 
Regards,

Kees Nuyt

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

[sqlite] Simplify multiple use of value in a trigger

[Gan Uesli Starling]

So I'm trying to accumulate data for state/prov inside of USA, Canada 
and Mexico, and country for the rest of the world.


Since country and state from the same update are factors in incrementing 
each of four tables, I was hoping to simplify it with a single select 
with 'AS' and then re-use the AS-value in every case. This as opposed to 
the redundancy of selecting the same thing anew every time. See as 
below. It passes syntax but fails on exectute, saying there is no column 
's'. Is there some way to do this? To hold the 'state' and 'country' and 
reuse them like a variable from one query to the next for sequential 
comparisons?



-- Trigger for incrementing country & region counts for 160m band, 
QSO-by-QSO.

DROP TRIGGER IF EXISTS _160m_Increment;
CREATE TRIGGER _160m_Increment
AFTER UPDATE ON qso WHEN
NEW.freq LIKE '1.%'
BEGIN
SELECT country AS c FROM qth WHERE rowid = NEW.qth_other_id;
SELECT state   AS s FROM qth WHERE rowid = NEW.qth_other_id;
UPDATE usa_state SET _160m = 1 + (SELECT _160m FROM usa_state WHERE 
state = s)   WHERE state = s AND c = 'USA';
UPDATE mx_state  SET _160m = 1 + (SELECT _160m FROM mx_state WHERE 
state = s)   WHERE state = s AND c = 'Mexico';
UPDATE ca_prov   SET _160m = 1 + (SELECT _160m FROM ca_prov WHERE 
prov  = s)   WHERE prov  = s AND c = 'Canada';
UPDATE world SET _160m = 1 + (SELECT _160m FROM world WHERE 
country = c) WHERE country = c;

END;

--

 Mistera Sturno - Rarest Extinct Bird

 <(+)__   Ĝan Ŭesli Starling
  ((__/)=-Holland, MI, USA
   `||`
++http://starling.us
  http://esperanto.us
  http://ky8d.net

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