Changeset: 4eed77d2ea52 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=4eed77d2ea52
Modified Files:
sql/backends/monet5/datacell/Tests/linearroad.sql
Branch: default
Log Message:
SQL version of linear road benchmark
Originally developed by Erietta Liarou for studying streaming
behavior. Moved here as a complex test case to be run at some point.
(detected one system crash already)
diffs (truncated from 519 to 300 lines):
diff --git a/sql/backends/monet5/datacell/Tests/linearroad.sql
b/sql/backends/monet5/datacell/Tests/linearroad.sql
new file mode 100644
--- /dev/null
+++ b/sql/backends/monet5/datacell/Tests/linearroad.sql
@@ -0,0 +1,514 @@
+-- The base line implementation
+-- Table structures.
+create schema lrbm;
+set optimizer ='datacell_pipe';
+
+create table lrbm.input (
+ "type" int,
+ "time" int,
+ "carid" int,
+ "speed" int,
+ "xway" int,
+ "lane" int,
+ "dir" int,
+ "seg" int,
+ "pos" int,
+ "qid" int,
+ "m_init" int,
+ "m_end" int,
+ "dow" int,
+ "tod" int,
+ "day2" int);
+
+call datacell.receptor('lrbm.input','localhost',50599);
+
+CREATE TABLE lrbm.preAccident1(time integer, carid integer, lane integer, dir
integer, seg integer, pos integer);
+call datacell.basket('lrbm.preAccident1');
+
+CREATE TABLE lrbm.preAccident2 (carid1 integer,carid2 integer,time integer,dir
integer,seg integer, pos integer);
+call datacell.basket('lrbm.preAccident2');
+
+CREATE TABLE lrbm.accident(carid1 integer, carid2 integer, firstMinute
integer, lastMinute integer, dir integer, seg integer, pos integer);
+call datacell.basket('lrbm.accident');
+
+CREATE TABLE lrbm.statistics(dir int,seg int,time_minute int,numvehicles
int,lav float,toll int,accident int,accidentSeg int);
+call datacell.basket('lrbm.statistics');
+
+CREATE TABLE lrbm.preVehicle (dir int,seg int,time_minute2 int,numvehicles
int);
+call datacell.basket('lrbm.preVehicle');
+
+CREATE TABLE lrbm.preLav( dir int,seg int,time_minute3 int,lav float);
+call datacell.basket('lrbm.preLav');
+
+CREATE TABLE lrbm.TollAccAlerts(
+ time INTEGER,
+ carid INTEGER,
+ dir INTEGER,
+ seg INTEGER,
+ lav INTEGER,
+ toll INTEGER,
+ accidentSeg INTEGER);
+
+CREATE TABLE lrbm.dailyExpenditureRequests ( "type" int, "time" int, "carid"
int, "xway" int, "qid" int, "day2" int);
+CREATE TABLE lrbm.dailyExpenditureanswer ( "carid" int, "day2" int, "bal" int,
"qid" int );
+CREATE TABLE lrbm.completehistory ( "carid" int, "day2" int, "xway" int,
"toll" int);
+CREATE TABLE lrbm.accountBalanceRequests ( "type" int, "time" int, "carid"
int, "xway" int, "qid" int, "day2" int);
+
+CREATE PROCEDURE lrbm.accidents()
+BEGIN
+ INSERT INTO lrbm.preAccident1 (time, carid, lane, dir, seg, pos)
+ SELECT time, carid, lane, dir, seg, pos
+ FROM lrbm.input
+ WHERE speed = 0 AND type = 0;
+
+ INSERT INTO lrbm.preAccident2 (carid1 ,carid2 , time , dir , seg , pos)
+ SELECT in1.carid, in2.carid, in2.time, in1.dir, in1.seg, in1.pos
+ FROM lrbm.preAccident1 AS in1,
+ lrbm.preAccident1 AS in2,
+ lrbm.preAccident1 AS in11,
+ lrbm.preAccident1 AS in22
+ WHERE in2.carid <> in1.carid AND
+ in2.pos = in1.pos AND
+ in2.lane = in1.lane AND
+ in2.dir = in1.dir AND
+ in2.time >= in1.time AND
+ in2.time <= in1.time + 30 AND
+ in11.carid = in1.carid AND
+ in11.pos = in1.pos AND
+ in11.lane = in1.lane AND
+ in11.dir = in1.dir AND
+ in11.time = in1.time + 120 AND
+ in22.carid = in2.carid AND
+ in22.pos = in1.pos AND
+ in22.lane = in1.lane AND
+ in22.dir = in1.dir AND
+ in22.time = in2.time + 120;
+
+ INSERT INTO lrbm.accident
+ SELECT min(carid1),
+ max(carid2),
+ ((min(time) + 90)/60) + 1,
+ ((max(time) + 120)/60) + 1,
+ dir, seg, pos
+ FROM lrbm.preAccident2
+ GROUP BY dir,seg,pos;
+END;
+
+CREATE PROCEDURE lrbm.statistics()
+BEGIN
+ INSERT INTO lrbm.statistics
(dir,seg,time_minute,numvehicles,lav,toll,accident,accidentSeg)
+ SELECT dir, seg, tme+1, 0,null,null,null,null
+ FROM
+ (SELECT dir AS dir , seg AS seg, (time/60) AS tme FROM
lrbm.input WHERE type = 0) AS tmpT
+ GROUP BY dir,seg,tme;
+END;
+
+CREATE PROCEDURE lrbm.extractNumVehicles()
+BEGIN
+ INSERT INTO lrbm.preVehicle (dir,seg,time_minute2,numvehicles)
+ SELECT dir, seg, tme+2, count (distinct cnt)
+ FROM (select dir AS dir ,
+ seg AS seg,
+ (time/60) AS tme,
+ carid AS cnt
+ FROM lrbm.input
+ WHERE type = 0) AS tmpT
+ GROUP BY dir,seg,tme;
+
+
+ UPDATE lrbm.statistics SET numVehicles = (
+ SELECT numVehicles
+ FROM lrbm.preVehicle
+ WHERE statistics.dir = preVehicle.dir AND
+ statistics.seg = preVehicle.seg AND
+ statistics.time_minute = preVehicle.time_minute2);
+
+ UPDATE lrbm.statistics SET numVehicles = 0 WHERE numVehicles IS
NULL;
+END;
+
+CREATE PROCEDURE lrbm.extractLav()
+BEGIN
+ INSERT INTO lrbm.prelav(dir, seg, time_minute3, lav )
+ SELECT dir, seg, tme, speed
+ FROM ( SELECT dir, seg, tme, avg(speed) AS speed
+ FROM ( SELECT dir,seg,carid,tme,avg(speed) AS speed
+ FROM (SELECT dir AS dir, seg AS seg, carid as
carid, (time/60)+1 AS tme, speed AS speed
+ FROM lrbm.input
+ WHERE type=0) AS temp_A
+ GROUP BY dir,seg,carid,tme ) AS temp_B
+ GROUP BY dir,seg,tme) AS temp_C;
+
+
+
+ UPDATE lrbm.statistics SET lav = (
+ SELECT floor(avg(prelav.lav))
+ FROM lrbm.prelav
+ WHERE statistics.dir = prelav.dir AND
+ statistics.seg = prelav.seg AND
+ prelav.time_minute3 <= statistics.time_minute - 1 AND
+ prelav.time_minute3 >= statistics.time_minute - 5);
+
+
+
+ UPDATE lrbm.statistics SET lav = -2 WHERE lav IS NULL;
+
+ UPDATE lrbm.statistics SET toll = -3 WHERE toll IS NULL;
+ UPDATE lrbm.statistics SET accident = -3 WHERE accident IS NULL;
+ UPDATE lrbm.statistics SET accidentseg = -3 WHERE accidentseg IS
NULL;
+END;
+
+CREATE PROCEDURE lrbm.calculateToll()
+BEGIN
+-- 5a
+ UPDATE lrbm.statistics SET toll = 0 WHERE lav >= 40 OR numVehicles
<= 50;
+
+-- 5b
+ ## the following query does not generate the correct mal query plan
+ # check 'where exists'
+-- IT CRASHES THE SYSTEM
+ UPDATE lrbm.statistics
+ SET toll = 0, accident = 1
+ WHERE EXISTS(
+ SELECT acc.seg
+ FROM lrbm.accident AS acc
+ WHERE acc.dir = statistics.dir AND
+ acc.firstMinute + 1 <= statistics.time_minute AND
+ acc.lastMinute + 1 >= statistics.time_minute AND
+ (
+ (
+ (acc.dir = 0) AND
+ (acc.seg >= statistics.seg) AND
+ (acc.seg <= statistics.seg + 4)
+ )
+ OR
+ (
+ (acc.dir <> 0) AND
+ (acc.seg <= statistics.seg) AND
+ (acc.seg >= statistics.seg - 4)
+ )
+ )
+ );
+
+--5c
+ UPDATE lrbm.statistics SET toll = (2 * (numVehicles - 50) *
(numVehicles - 50)) WHERE toll IS NULL;
+
+-- 5d
+-- toll = 0, accident = 1,
+ UPDATE lrbm.statistics
+ SET
+ accidentSeg =
+ (SELECT acc.seg
+ FROM lrbm.accident AS acc
+ WHERE acc.dir = statistics.dir AND
+ acc.firstMinute + 1 <= statistics.time_minute AND
+ acc.lastMinute + 1 >= statistics.time_minute AND
+ (
+ (
+ (acc.dir = 0) AND
+ (acc.seg >= statistics.seg) AND
+ (acc.seg <= statistics.seg + 4)
+ )
+ OR
+ (
+ (acc.dir <> 0) AND
+ (acc.seg <= statistics.seg) AND
+ (acc.seg >= statistics.seg - 1)
+ )
+ )
+ )
+ WHERE statistics.accident = 1;
+
+--5e
+ UPDATE lrbm.statistics SET accident = 0, accidentSeg = -1 WHERE
accident=-3;
+
+-- Note: 5b and 5d contain the same subquery
+
+ SELECT acc.seg
+ FROM lrbm.accident AS acc
+ WHERE acc.dir = statistics.dir AND
+ acc.firstMinute + 1 <= statistics.time_minute AND
+ acc.lastMinute + 1 >= statistics.time_minute AND
+ (
+ (
+ (acc.dir = 0) AND
+ (acc.seg >= statistics.seg) AND
+ (acc.seg <= statistics.seg + 4)
+ )
+ OR
+ (
+ (acc.dir <> 0) AND
+ (acc.seg <= statistics.seg) AND
+ (acc.seg >= statistics.seg - 4)
+ )
+ )
+
+-- We have implemented the nested selection just once
+-- and then we have added the appropriate code to also support 5d
+
+END;
+
+CREATE PROCEDURE createAlerts()
+BEGIN
+ INSERT INTO lrbm.tollAccAlerts(time, carid, dir, seg)
+ SELECT min(time),
+ carid,
+ dir,
+ seg
+ FROM input
+ WHERE type = 0 AND
+ lane <> 4
+ GROUP BY dir, seg, carid;
+
+ UPDATE lrbm.tollAccAlerts
+ SET lav =
+ (SELECT CAST(statistics.lav AS integer) AS lav
+ FROM lrbm.statistics
+ WHERE statistics.dir = tollAccAlerts.dir AND
+ statistics.seg = tollAccAlerts.seg AND
+ statistics.time_minute = (tollAccAlerts.time/60) + 1);
+
+ UPDATE lrbm.tollAccAlerts
+ SET toll =
+ (SELECT statistics.toll
+ FROM lrbm.statistics
+ WHERE statistics.dir = tollAccAlerts.dir AND
+ statistics.seg = tollAccAlerts.seg AND
+ statistics.time_minute = tollAccAlerts.time/60 + 1);
+
+ UPDATE lrbm.tollAccAlerts
+ SET accidentSeg =
+ (SELECT statistics.accidentSeg
+ FROM lrbm.statistics
+ WHERE statistics.dir = tollAccAlerts.dir AND
+ statistics.seg = tollAccAlerts.seg AND
+ statistics.time_minute = tollAccAlerts.time/60 + 1);
+END;
+
+CREATE PROCEDURE splitByType()
+BEGIN
+ INSERT INTO lrbm.dailyExpenditureRequests (type,time,carid,xway, qid,
day2)
+ SELECT type, time, carid, xway, qid, day2
+ FROM lrbm.input
+ WHERE type=3;
+
+ INSERT INTO lrbm.accountBalanceRequests (type,time,carid,xway, qid,
day2)
+ SELECT type, time, carid, xway, qid, day2
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
