Changeset: 18b6191d7c09 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=18b6191d7c09
Modified Files:
sql/backends/monet5/datacell/Tests/emili.sql
Branch: default
Log Message:
Avoid assignments of multiple values
In high-rate event arrival there is no guarantee that
you have a single location to update.
diffs (70 lines):
diff --git a/sql/backends/monet5/datacell/Tests/emili.sql
b/sql/backends/monet5/datacell/Tests/emili.sql
--- a/sql/backends/monet5/datacell/Tests/emili.sql
+++ b/sql/backends/monet5/datacell/Tests/emili.sql
@@ -46,14 +46,14 @@ CALL datacell.receptor('datacell.observa
CREATE PROCEDURE datacell.enrich()
BEGIN
DECLARE cnt INTEGER;
- SET cnt = (SELECT count(*) FROM datacell.area A, datacell.istream I
WHERE A.location = substring(I.location,0,3) ) ;
+ SET cnt = (SELECT count(distinct I.ip) FROM datacell.area A,
datacell.istream I WHERE A.location = substring(I.location,0,3) ) ;
INSERT INTO datacell.sensors(ip, location, kind,value)
SELECT ip, substring(location,0,3), kind, value FROM
datacell.istream;
IF cnt = 0
THEN
INSERT INTO datacell.area SELECT ip, substring(location,0,3)
FROM datacell.istream;
END IF;
- SET cnt = (SELECT count(*) FROM datacell.states A, datacell.istream I
WHERE A.location = substring(I.location,0,3) ) ;
+ SET cnt = (SELECT count(distinct I.ip) FROM datacell.states A,
datacell.istream I WHERE A.location = substring(I.location,0,3) ) ;
IF cnt = 0
THEN
INSERT INTO datacell.states SELECT substring(location,0,3),
now(), 'normal' FROM datacell.istream;
@@ -98,21 +98,18 @@ call datacell.query('datacell.splitter')
CREATE PROCEDURE datacell.firewarning()
BEGIN
DECLARE cnt INTEGER;
- DECLARE loc varchar(5);
SET cnt = ( SELECT count(*)
FROM datacell.states S, datacell.area A, datacell.hotsensors1 H
WHERE S.status ='normal' AND A.ip = H.ip and S.location =
A.location);
- SET loc = ( SELECT A.location
- FROM datacell.states S, datacell.area A, datacell.hotsensors1 H
- WHERE S.status ='normal' AND A.ip = H.ip and S.location =
A.location);
-
IF cnt =1
THEN
UPDATE datacell.states
SET status = 'unconfirmed', time = now()
- WHERE location = loc;
+ WHERE location IN (SELECT A.location
+ FROM datacell.states S, datacell.area A,
datacell.hotsensors1 H
+ WHERE S.status ='normal' AND A.ip = H.ip and
S.location = A.location));
END IF;
END;
CALL datacell.query('datacell.firewarning');
@@ -121,21 +118,18 @@ CALL datacell.query('datacell.firewarnin
CREATE PROCEDURE datacell.firespotted()
BEGIN
DECLARE cnt INTEGER;
- DECLARE loc varchar(5);
SET cnt = ( SELECT count(*)
FROM datacell.area A, datacell.states S, datacell.area B,
datacell.hotsensors2 H
WHERE S.status ='unconfirmed' AND A.ip <> H.ip AND B.ip = H.ip
AND A.ip <> B.ip AND S.location = A.location);
- SET loc = ( SELECT A.location
- FROM datacell.area A, datacell.states S, datacell.area B,
datacell.hotsensors2 H
- WHERE S.status ='unconfirmed' AND A.ip <> H.ip AND B.ip = H.ip
AND A.ip <> B.ip AND S.location = A.location);
-
IF cnt =1
THEN
UPDATE datacell.states
SET status = 'confirmed', time = now()
- WHERE location = loc;
+ WHERE location IN (SELECT A.location
+ FROM datacell.states S, datacell.area A,
datacell.hotsensors1 H
+ WHERE S.status ='unconfirmed' AND A.ip = H.ip
and S.location = A.location));
END IF;
END;
CALL datacell.query('datacell.firespotted');
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
