Changeset: 975f76aedd79 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=975f76aedd79
Modified Files:
sql/backends/monet5/iot/Tests/ais/ais01.sql
sql/backends/monet5/iot/Tests/ais/ais02.sql
sql/backends/monet5/iot/Tests/ais/ais03.sql
sql/backends/monet5/iot/Tests/ais/ais04.sql
sql/backends/monet5/iot/Tests/ais/ais05.sql
sql/backends/monet5/iot/Tests/ais/ais06.sql
sql/backends/monet5/iot/Tests/ais/ais07.sql
sql/backends/monet5/iot/Tests/ais/ais08.sql
sql/backends/monet5/iot/Tests/ais/ais09.sql
sql/backends/monet5/iot/Tests/ais/ais10.sql
sql/backends/monet5/iot/Tests/ais/ais11.sql
Branch: iot
Log Message:
Updated ais tests
diffs (truncated from 358 to 300 lines):
diff --git a/sql/backends/monet5/iot/Tests/ais/ais01.sql
b/sql/backends/monet5/iot/Tests/ais/ais01.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais01.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais01.sql
@@ -10,18 +10,20 @@ CREATE STREAM TABLE vessels (implicit_ti
INSERT INTO iot.webserverstreams
SELECT tabl.id, 2 , 8, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'vessels' AND sch.name = 'ais';
--- We don't set the tumbling, so no tuple will be reused in the following
window
-CALL iot.heartbeat('ais', 'vessels', 8000);
-
--Q1 Calculate speed of ships per hour (in knots) -- Stream only
-CREATE STREAM TABLE ais01r (calc_time timestamp, mmsi int, sog real);
+CREATE TABLE ais01r (calc_time timestamp, mmsi int, sog real);
CREATE PROCEDURE ais01q()
-BEGIN
+BEGIN
INSERT INTO ais01r
- SELECT current_timestamp, mmsi, sog FROM vessels WHERE
(implicit_timestamp, mmsi) IN (SELECT max(implicit_timestamp), mmsi FROM
vessels GROUP BY mmsi);
+ WITH data_time AS (SELECT current_timestamp AS cur_time)
+ SELECT cur_time, mmsi, sog FROM vessels CROSS JOIN data_time
WHERE (implicit_timestamp, mmsi) IN (SELECT max(implicit_timestamp), mmsi FROM
vessels GROUP BY mmsi);
END;
CALL iot.query('ais', 'ais01q');
+CALL iot.pause();
+-- We don't set the tumbling, so no tuple will be reused in the following
window
+CALL iot.heartbeat('ais', 'vessels', 8000);
+CALL iot.resume();
diff --git a/sql/backends/monet5/iot/Tests/ais/ais02.sql
b/sql/backends/monet5/iot/Tests/ais/ais02.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais02.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais02.sql
@@ -10,12 +10,9 @@ CREATE STREAM TABLE vessels (implicit_ti
INSERT INTO iot.webserverstreams
SELECT tabl.id, 2 , 8, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'vessels' AND sch.name = 'ais';
--- We don't set the tumbling, so no tuple will be reused in the following
window
-CALL iot.heartbeat('ais', 'vessels', 8000);
-
--Q2 Number of ship per hour -- Stream only
-CREATE STREAM TABLE ais02r (calc_time timestamp, number_ships int);
+CREATE TABLE ais02r (calc_time timestamp, number_ships int);
CREATE PROCEDURE ais02q()
BEGIN
@@ -24,4 +21,8 @@ BEGIN
END;
CALL iot.query('ais', 'ais02q');
+CALL iot.pause();
+-- We don't set the tumbling, so no tuple will be reused in the following
window
+CALL iot.heartbeat('ais', 'vessels', 8000);
+CALL iot.resume();
diff --git a/sql/backends/monet5/iot/Tests/ais/ais03.sql
b/sql/backends/monet5/iot/Tests/ais/ais03.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais03.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais03.sql
@@ -10,18 +10,20 @@ CREATE STREAM TABLE vessels (implicit_ti
INSERT INTO iot.webserverstreams
SELECT tabl.id, 2 , 8, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'vessels' AND sch.name = 'ais';
--- We don't set the tumbling, so no tuple will be reused in the following
window
-CALL iot.heartbeat('ais', 'vessels', 8000);
-
--Q3 Currently anchorred ship -- Stream only
-CREATE STREAM TABLE ais03r (calc_time timestamp, mmsi int);
+CREATE TABLE ais03r (calc_time timestamp, mmsi int);
CREATE PROCEDURE ais03q()
BEGIN
INSERT INTO ais03r
- SELECT current_timestamp, mmsi FROM vessels WHERE nav_status =
1 AND (implicit_timestamp, mmsi) IN (SELECT max(implicit_timestamp), mmsi FROM
vessels GROUP BY mmsi);
+ WITH data_time AS (SELECT current_timestamp AS cur_time)
+ SELECT cur_time, mmsi FROM vessels CROSS JOIN data_time WHERE
nav_status = 1 AND (implicit_timestamp, mmsi) IN (SELECT
max(implicit_timestamp), mmsi FROM vessels GROUP BY mmsi);
END;
CALL iot.query('ais', 'ais03q');
+CALL iot.pause();
+-- We don't set the tumbling, so no tuple will be reused in the following
window
+CALL iot.heartbeat('ais', 'vessels', 8000);
+CALL iot.resume();
diff --git a/sql/backends/monet5/iot/Tests/ais/ais04.sql
b/sql/backends/monet5/iot/Tests/ais/ais04.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais04.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais04.sql
@@ -10,18 +10,20 @@ CREATE STREAM TABLE vessels (implicit_ti
INSERT INTO iot.webserverstreams
SELECT tabl.id, 2 , 8, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'vessels' AND sch.name = 'ais';
--- We don't set the tumbling, so no tuple will be reused in the following
window
-CALL iot.heartbeat('ais', 'vessels', 8000);
-
--Q4 Ship turning degree > 180 -- Stream only
-CREATE STREAM TABLE ais04r (calc_time timestamp, mmsi int);
+CREATE TABLE ais04r (calc_time timestamp, mmsi int);
CREATE PROCEDURE ais04q()
BEGIN
INSERT INTO ais04r
- SELECT current_timestamp, mmsi FROM vessels WHERE
sys.abs(rotais) > 180 AND (implicit_timestamp, mmsi) IN (SELECT
max(implicit_timestamp), mmsi FROM vessels GROUP BY mmsi);
+ WITH data_time AS (SELECT current_timestamp AS cur_time)
+ SELECT cur_time, mmsi FROM vessels CROSS JOIN data_time WHERE
sys.abs(rotais) > 180 AND (implicit_timestamp, mmsi) IN (SELECT
max(implicit_timestamp), mmsi FROM vessels GROUP BY mmsi);
END;
CALL iot.query('ais', 'ais04q');
+CALL iot.pause();
+-- We don't set the tumbling, so no tuple will be reused in the following
window
+CALL iot.heartbeat('ais', 'vessels', 8000);
+CALL iot.resume();
diff --git a/sql/backends/monet5/iot/Tests/ais/ais05.sql
b/sql/backends/monet5/iot/Tests/ais/ais05.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais05.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais05.sql
@@ -21,23 +21,22 @@ CREATE STREAM TABLE vessels (implicit_ti
INSERT INTO iot.webserverstreams
SELECT tabl.id, 2 , 8, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'vessels' AND sch.name = 'ais';
-INSERT INTO iot.webserverstreams
- SELECT tabl.id, 2 , 10, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'stations' AND sch.name =
'ais';
-
--- We don't set the tumbling, so no tuple will be reused in the following
window
-CALL iot.heartbeat('ais', 'vessels', 8000);
-
--Q5 Closest ship to each other -- Stream only
-CREATE STREAM TABLE ais05r (calc_time timestamp, mmsi1 int, mmsi2 int,
distance float);
+CREATE TABLE ais05r (calc_time timestamp, mmsi1 int, mmsi2 int, distance
float);
CREATE PROCEDURE ais05q()
BEGIN
INSERT INTO ais05r
WITH data AS (SELECT mmsi, lat, lon FROM vessels WHERE
(implicit_timestamp, mmsi) IN (SELECT max(implicit_timestamp), mmsi FROM
vessels GROUP BY mmsi)),
- distances AS (SELECT d1.mmsi AS mmsi1, d2.mmsi AS mmsi2,
km_distance(d1.lat, d1.lon, d2.lat, d2.lon) AS distance FROM data d1 CROSS JOIN
data d2 WHERE NOT d1.mmsi = d2.mmsi)
- SELECT current_timestamp, mmsi1, mmsi2, distance FROM distances
WHERE (mmsi1, distance) IN (SELECT mmsi1, min(distance) FROM distances GROUP BY
mmsi1);
+ distances AS (SELECT d1.mmsi AS mmsi1, d2.mmsi AS mmsi2,
km_distance(d1.lat, d1.lon, d2.lat, d2.lon) AS distance FROM data d1 CROSS JOIN
data d2 WHERE NOT d1.mmsi = d2.mmsi),
+ data_time AS (SELECT current_timestamp AS cur_time)
+ SELECT cur_time, mmsi1, mmsi2, distance FROM distances CROSS
JOIN data_time WHERE (mmsi1, distance) IN (SELECT mmsi1, min(distance) FROM
distances GROUP BY mmsi1);
END;
CALL iot.query('ais', 'ais05q');
+CALL iot.pause();
+-- We don't set the tumbling, so no tuple will be reused in the following
window
+CALL iot.heartbeat('ais', 'vessels', 8000);
+CALL iot.resume();
diff --git a/sql/backends/monet5/iot/Tests/ais/ais06.sql
b/sql/backends/monet5/iot/Tests/ais/ais06.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais06.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais06.sql
@@ -23,23 +23,27 @@ CREATE STREAM TABLE stations (implicit_t
INSERT INTO iot.webserverstreams
SELECT tabl.id, 2 , 8, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'vessels' AND sch.name = 'ais';
--- We don't set the tumbling, so no tuple will be reused in the following
window
-CALL iot.heartbeat('ais', 'vessels', 8000);
--- The stations send less often, therefore the heartbeat is 10 seconds
-CALL iot.heartbeat('ais', 'stations', 10000);
+INSERT INTO iot.webserverstreams
+ SELECT tabl.id, 2 , 10, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'stations' AND sch.name =
'ais';
--Q6 For each station calulate ship within a radios of 3 km -- Stream join
-CREATE STREAM TABLE ais06r (calc_time timestamp, smmsi int, vmmsi int,
distance float);
+CREATE TABLE ais06r (calc_time timestamp, smmsi int, vmmsi int, distance
float);
CREATE PROCEDURE ais06q()
BEGIN
INSERT INTO ais06r
WITH data1 AS (SELECT mmsi, lat, lon FROM vessels WHERE
(implicit_timestamp, mmsi) IN (SELECT max(implicit_timestamp), mmsi FROM
vessels GROUP BY mmsi)),
data2 AS (SELECT mmsi, lat, lon FROM stations WHERE
(implicit_timestamp, mmsi) IN (SELECT max(implicit_timestamp), mmsi FROM
stations GROUP BY mmsi)),
- calculations AS (SELECT d1.mmsi AS smmsi, d2.mmsi AS vmmsi,
km_distance(d1.lat, d1.lon, d2.lat, d2.lon) AS distance FROM data1 d1 CROSS
JOIN data2 d2)
- SELECT current_timestamp, smmsi, vmmsi, distance FROM
calculations WHERE distance < 3;
+ calculations AS (SELECT d1.mmsi AS smmsi, d2.mmsi AS vmmsi,
km_distance(d1.lat, d1.lon, d2.lat, d2.lon) AS distance FROM data1 d1 CROSS
JOIN data2 d2),
+ data_time AS (SELECT current_timestamp AS cur_time)
+ SELECT cur_time, smmsi, vmmsi, distance FROM calculations CROSS
JOIN data_time WHERE distance < 3;
END;
CALL iot.query('ais', 'ais06q');
+CALL iot.pause();
+-- We don't set the tumbling, so no tuple will be reused in the following
window
+CALL iot.heartbeat('ais', 'vessels', 8000);
+CALL iot.heartbeat('ais', 'stations', 10000);
+CALL iot.resume();
diff --git a/sql/backends/monet5/iot/Tests/ais/ais07.sql
b/sql/backends/monet5/iot/Tests/ais/ais07.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais07.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais07.sql
@@ -28,16 +28,13 @@ INSERT INTO static_locations VALUES ('We
INSERT INTO static_locations VALUES ('Mercuriushaven', 'POLYGON(
(3872.72450963 330.277569199 5048.02561402, 3873.1986563 329.657593618
5047.70235253, 3872.92006997 329.040734675 5047.95635149, 3873.11386675
328.884805095 5047.81782161, 3873.41352071 329.00959555 5047.57975505,
3873.61782996 329.465350531 5047.39323713, 3873.33890872 330.429316655
5047.54427072, 3872.9540041 330.381842104 5047.84271947, 3872.72450963
330.277569199 5048.02561402) )'); /* Mercuriushaven */
-- Vessels positions reports table based on AIS messages types 1, 2 and 3
-CREATE STREAM TABLE vessels (implicit_timestamp timestamp, mmsi int, lat real,
lon real, nav_status tinyint, sog real, rotais smallint);
+CREATE TABLE vessels (implicit_timestamp timestamp, mmsi int, lat real, lon
real, nav_status tinyint, sog real, rotais smallint);
-- Position reports are sent every 3-5 seconds so is resonable to consume the
tuples arrived on the last 8 seconds
-- Inserts for iot web server (providing time based flush of 8 seconds)
INSERT INTO iot.webserverstreams
SELECT tabl.id, 2 , 8, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'vessels' AND sch.name = 'ais';
--- We don't set the tumbling, so no tuple will be reused in the following
window
-CALL iot.heartbeat('ais', 'vessels', 8000);
-
--Q7 Which ship are currently anchored at the harbors -- Stream + static
CREATE STREAM TABLE ais07r (calc_time timestamp, harbor char(32), mmsi int);
@@ -45,8 +42,15 @@ CREATE STREAM TABLE ais07r (calc_time ti
CREATE PROCEDURE ais07q()
BEGIN
INSERT INTO ais07r
- WITH data AS (SELECT mmsi, geographic_to_cartesian(lat, lon) AS
calc_point FROM vessels WHERE (implicit_timestamp, mmsi) IN (SELECT
max(implicit_timestamp), mmsi FROM vessels WHERE nav_status = 1 GROUP BY mmsi))
- SELECT current_timestamp, harbor, mmsi FROM data CROSS JOIN
static_locations WHERE sys.st_contains(field, calc_point);
+ WITH data AS (SELECT mmsi, geographic_to_cartesian(lat, lon) AS
calc_point FROM vessels WHERE (implicit_timestamp, mmsi) IN (SELECT
max(implicit_timestamp), mmsi FROM vessels WHERE nav_status = 1 GROUP BY mmsi)),
+ results AS (SELECT harbor, mmsi FROM data CROSS JOIN
static_locations, data_time WHERE sys.st_contains(field, calc_point)),
+ data_time AS (SELECT current_timestamp AS cur_time)
+ SELECT cur_time, harbor, mmsi FROM results CROSS JOIN data_time;
END;
CALL iot.query('ais', 'ais07q');
+CALL iot.pause();
+-- We don't set the tumbling, so no tuple will be reused in the following
window
+CALL iot.heartbeat('ais', 'vessels', 8000);
+CALL iot.resume();
+
diff --git a/sql/backends/monet5/iot/Tests/ais/ais08.sql
b/sql/backends/monet5/iot/Tests/ais/ais08.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais08.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais08.sql
@@ -10,18 +10,20 @@ CREATE STREAM TABLE vessels (implicit_ti
INSERT INTO iot.webserverstreams
SELECT tabl.id, 2 , 8, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'vessels' AND sch.name = 'ais';
--- We don't set the tumbling, so no tuple will be reused in the following
window
-CALL iot.heartbeat('ais', 'vessels', 8000);
-
--Q8 Track the movements of a ship S -- Stream only
-CREATE STREAM TABLE ais08r (calc_time timestamp, mmsi int, implicit_timestamp
timestamp, latitude float, longitude float);
+CREATE TABLE ais08r (calc_time timestamp, mmsi int, implicit_timestamp
timestamp, latitude float, longitude float);
CREATE PROCEDURE ais08q()
BEGIN
INSERT INTO ais08r
- SELECT current_timestamp, mmsi, implicit_timestamp, lat, lon
FROM vessels;
+ WITH data_time AS (SELECT current_timestamp AS cur_time)
+ SELECT cur_time, mmsi, implicit_timestamp, lat, lon FROM
vessels CROSS JOIN data_time;
END;
CALL iot.query('ais', 'ais08q');
+CALL iot.pause();
+-- We don't set the tumbling, so no tuple will be reused in the following
window
+CALL iot.heartbeat('ais', 'vessels', 8000);
+CALL iot.resume();
diff --git a/sql/backends/monet5/iot/Tests/ais/ais09.sql
b/sql/backends/monet5/iot/Tests/ais/ais09.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais09.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais09.sql
@@ -28,16 +28,13 @@ INSERT INTO static_locations VALUES ('We
INSERT INTO static_locations VALUES ('Mercuriushaven', 'POLYGON(
(3872.72450963 330.277569199 5048.02561402, 3873.1986563 329.657593618
5047.70235253, 3872.92006997 329.040734675 5047.95635149, 3873.11386675
328.884805095 5047.81782161, 3873.41352071 329.00959555 5047.57975505,
3873.61782996 329.465350531 5047.39323713, 3873.33890872 330.429316655
5047.54427072, 3872.9540041 330.381842104 5047.84271947, 3872.72450963
330.277569199 5048.02561402) )'); /* Mercuriushaven */
-- Vessels positions reports table based on AIS messages types 1, 2 and 3
-CREATE STREAM TABLE vessels (implicit_timestamp timestamp, mmsi int, lat real,
lon real, nav_status tinyint, sog real, rotais smallint);
+CREATE TABLE vessels (implicit_timestamp timestamp, mmsi int, lat real, lon
real, nav_status tinyint, sog real, rotais smallint);
-- Position reports are sent every 3-5 seconds so is resonable to consume the
tuples arrived on the last 8 seconds
-- Inserts for iot web server (providing time based flush of 8 seconds)
INSERT INTO iot.webserverstreams
SELECT tabl.id, 2 , 8, 's' FROM sys.tables tabl INNER JOIN sys.schemas
sch ON tabl.schema_id = sch.id WHERE tabl.name = 'vessels' AND sch.name = 'ais';
--- We don't set the tumbling, so no tuple will be reused in the following
window
-CALL iot.heartbeat('ais', 'vessels', 8000);
-
--Q9 Notify when a ship S arrived at an harbor -- Stream + static
CREATE STREAM TABLE ais09r (calc_time timestamp, harbor char(32), mmsi int,
implicit_timestamp timestamp);
@@ -45,9 +42,15 @@ CREATE STREAM TABLE ais09r (calc_time ti
CREATE PROCEDURE ais09q()
BEGIN
INSERT INTO ais09r
- WITH data AS (SELECT mmsi, implicit_timestamp,
geographic_to_cartesian(lat, lon) AS calc_point FROM vessels)
- SELECT current_timestamp, harbor, mmsi, min(implicit_timestamp)
FROM data CROSS JOIN static_locations WHERE sys.st_contains(field, calc_point)
AND (harbor, mmsi) NOT IN (SELECT harbor, mmsi FROM ais09r) GROUP BY harbor,
mmsi;
+ WITH data AS (SELECT mmsi, implicit_timestamp,
geographic_to_cartesian(lat, lon) AS calc_point FROM vessels),
+ results AS (SELECT harbor, mmsi, min(implicit_timestamp) AS
calc_min FROM data CROSS JOIN static_locations WHERE sys.st_contains(field,
calc_point) AND (harbor, mmsi) NOT IN (SELECT harbor, mmsi FROM ais09r) GROUP
BY harbor, mmsi),
+ data_time AS (SELECT current_timestamp AS cur_time)
+ SELECT cur_time, harbor, mmsi, calc_min FROM results CROSS JOIN
data_time;
END;
CALL iot.query('ais', 'ais09q');
+CALL iot.pause();
+-- We don't set the tumbling, so no tuple will be reused in the following
window
+CALL iot.heartbeat('ais', 'vessels', 8000);
+CALL iot.resume();
diff --git a/sql/backends/monet5/iot/Tests/ais/ais10.sql
b/sql/backends/monet5/iot/Tests/ais/ais10.sql
--- a/sql/backends/monet5/iot/Tests/ais/ais10.sql
+++ b/sql/backends/monet5/iot/Tests/ais/ais10.sql
@@ -28,16 +28,13 @@ INSERT INTO static_locations VALUES ('We
INSERT INTO static_locations VALUES ('Mercuriushaven', 'POLYGON(
(3872.72450963 330.277569199 5048.02561402, 3873.1986563 329.657593618
5047.70235253, 3872.92006997 329.040734675 5047.95635149, 3873.11386675
328.884805095 5047.81782161, 3873.41352071 329.00959555 5047.57975505,
3873.61782996 329.465350531 5047.39323713, 3873.33890872 330.429316655
5047.54427072, 3872.9540041 330.381842104 5047.84271947, 3872.72450963
330.277569199 5048.02561402) )'); /* Mercuriushaven */
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
