Changeset: e0e57cb5831d for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=e0e57cb5831d
Modified Files:
clients/iotclient/requirements.txt
sql/backends/monet5/iot/Tests/ais/Tests/ais01.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais02.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais03.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais04.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais05.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais06.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais07.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais08.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais09.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais10.sql
sql/backends/monet5/iot/Tests/ais/Tests/ais11.sql
Branch: iot
Log Message:
Updated ais tests
diffs (truncated from 477 to 300 lines):
diff --git a/clients/iotclient/requirements.txt
b/clients/iotclient/requirements.txt
--- a/clients/iotclient/requirements.txt
+++ b/clients/iotclient/requirements.txt
@@ -1,4 +1,4 @@
-fake-factory>=0.5.8
+fake-factory>=0.5.9
Flask-RESTful>=0.3.5
IPy>=0.83
jsonschema>=2.5.1
diff --git a/sql/backends/monet5/iot/Tests/ais/Tests/ais01.sql
b/sql/backends/monet5/iot/Tests/ais/Tests/ais01.sql
--- a/sql/backends/monet5/iot/Tests/ais/Tests/ais01.sql
+++ b/sql/backends/monet5/iot/Tests/ais/Tests/ais01.sql
@@ -3,12 +3,11 @@ SET SCHEMA ais;
SET optimizer = 'iot_pipe';
-- 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 STREAM TABLE vessels1 (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';
+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 =
'vessels1' AND sch.name = 'ais';
--Q1 Calculate speed of ships per hour (in knots) -- Stream only
@@ -18,12 +17,18 @@ CREATE PROCEDURE ais01q()
BEGIN
INSERT INTO ais01r
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);
+ SELECT cur_time, mmsi, sog FROM vessels1 CROSS JOIN data_time
WHERE (implicit_timestamp, mmsi) IN (SELECT max(implicit_timestamp), mmsi FROM
vessels1 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.heartbeat('ais', 'vessels1', 8000);
CALL iot.resume();
+CALL iot.pause();
+DELETE FROM iot.webserverstreams;
+DROP PROCEDURE ais01q;
+DROP TABLE vessels1;
+DROP TABLE ais01r;
+
diff --git a/sql/backends/monet5/iot/Tests/ais/Tests/ais02.sql
b/sql/backends/monet5/iot/Tests/ais/Tests/ais02.sql
--- a/sql/backends/monet5/iot/Tests/ais/Tests/ais02.sql
+++ b/sql/backends/monet5/iot/Tests/ais/Tests/ais02.sql
@@ -2,12 +2,11 @@ SET SCHEMA ais;
SET optimizer = 'iot_pipe';
-- 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 STREAM TABLE vessels2 (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';
+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 =
'vessels2' AND sch.name = 'ais';
--Q2 Number of ship per hour -- Stream only
@@ -16,12 +15,18 @@ CREATE TABLE ais02r (calc_time timestamp
CREATE PROCEDURE ais02q()
BEGIN
INSERT INTO ais02r
- SELECT current_timestamp, count(DISTINCT mmsi) FROM vessels;
+ SELECT current_timestamp, count(DISTINCT mmsi) FROM vessels2;
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.heartbeat('ais', 'vessels2', 8000);
CALL iot.resume();
+CALL iot.pause();
+DELETE FROM iot.webserverstreams;
+DROP PROCEDURE ais02q;
+DROP TABLE vessels2;
+DROP TABLE ais02r;
+
diff --git a/sql/backends/monet5/iot/Tests/ais/Tests/ais03.sql
b/sql/backends/monet5/iot/Tests/ais/Tests/ais03.sql
--- a/sql/backends/monet5/iot/Tests/ais/Tests/ais03.sql
+++ b/sql/backends/monet5/iot/Tests/ais/Tests/ais03.sql
@@ -2,12 +2,11 @@ SET SCHEMA ais;
SET optimizer = 'iot_pipe';
-- 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 STREAM TABLE vessels3 (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';
+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 =
'vessels3' AND sch.name = 'ais';
--Q3 Currently anchorred ship -- Stream only
@@ -17,12 +16,18 @@ CREATE PROCEDURE ais03q()
BEGIN
INSERT INTO ais03r
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);
+ SELECT cur_time, mmsi FROM vessels3 CROSS JOIN data_time WHERE
nav_status = 1 AND (implicit_timestamp, mmsi) IN (SELECT
max(implicit_timestamp), mmsi FROM vessels3 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.heartbeat('ais', 'vessels3', 8000);
CALL iot.resume();
+CALL iot.stop();
+DELETE FROM iot.webserverstreams;
+DROP PROCEDURE ais03q;
+DROP TABLE vessels3;
+DROP TABLE ais03r;
+
diff --git a/sql/backends/monet5/iot/Tests/ais/Tests/ais04.sql
b/sql/backends/monet5/iot/Tests/ais/Tests/ais04.sql
--- a/sql/backends/monet5/iot/Tests/ais/Tests/ais04.sql
+++ b/sql/backends/monet5/iot/Tests/ais/Tests/ais04.sql
@@ -2,12 +2,11 @@ SET SCHEMA ais;
SET optimizer = 'iot_pipe';
-- 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 STREAM TABLE vessels4 (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';
+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 =
'vessels4' AND sch.name = 'ais';
--Q4 Ship turning degree > 180 -- Stream only
@@ -17,12 +16,18 @@ CREATE PROCEDURE ais04q()
BEGIN
INSERT INTO ais04r
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);
+ SELECT cur_time, mmsi FROM vessels4 CROSS JOIN data_time WHERE
sys.abs(rotais) > 180 AND (implicit_timestamp, mmsi) IN (SELECT
max(implicit_timestamp), mmsi FROM vessels4 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.heartbeat('ais', 'vessels4', 8000);
CALL iot.resume();
+CALL iot.pause();
+DELETE FROM iot.webserverstreams;
+DROP PROCEDURE ais04q;
+DROP TABLE vessels4;
+DROP TABLE ais04r;
+
diff --git a/sql/backends/monet5/iot/Tests/ais/Tests/ais05.sql
b/sql/backends/monet5/iot/Tests/ais/Tests/ais05.sql
--- a/sql/backends/monet5/iot/Tests/ais/Tests/ais05.sql
+++ b/sql/backends/monet5/iot/Tests/ais/Tests/ais05.sql
@@ -13,12 +13,11 @@ BEGIN
END;
-- 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 STREAM TABLE vessels5 (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';
+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 =
'vessels5' AND sch.name = 'ais';
--Q5 Closest ship to each other -- Stream only
@@ -27,7 +26,7 @@ CREATE TABLE ais05r (calc_time timestamp
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)),
+ WITH data AS (SELECT mmsi, lat, lon FROM vessels5 WHERE
(implicit_timestamp, mmsi) IN (SELECT max(implicit_timestamp), mmsi FROM
vessels5 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),
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);
@@ -36,6 +35,13 @@ 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.heartbeat('ais', 'vessels5', 8000);
CALL iot.resume();
+CALL iot.pause();
+DELETE FROM iot.webserverstreams;
+DROP PROCEDURE ais05q;
+DROP FUNCTION km_distance;
+DROP TABLE vessels5;
+DROP TABLE ais05r;
+
diff --git a/sql/backends/monet5/iot/Tests/ais/Tests/ais06.sql
b/sql/backends/monet5/iot/Tests/ais/Tests/ais06.sql
--- a/sql/backends/monet5/iot/Tests/ais/Tests/ais06.sql
+++ b/sql/backends/monet5/iot/Tests/ais/Tests/ais06.sql
@@ -13,17 +13,15 @@ BEGIN
END;
-- 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 STREAM TABLE vessels6 (implicit_timestamp timestamp, mmsi int, lat
real, lon real, nav_status tinyint, sog real, rotais smallint);
-- Stations positions reports table based on AIS message type 4
CREATE STREAM TABLE stations (implicit_timestamp timestamp, mmsi int, lat
real, lon real);
-- 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';
+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 =
'vessels6' 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';
+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
@@ -32,7 +30,7 @@ CREATE TABLE ais06r (calc_time timestamp
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)),
+ WITH data1 AS (SELECT mmsi, lat, lon FROM vessels6 WHERE
(implicit_timestamp, mmsi) IN (SELECT max(implicit_timestamp), mmsi FROM
vessels6 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),
data_time AS (SELECT current_timestamp AS cur_time)
@@ -42,7 +40,15 @@ 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', 'vessels6', 8000);
CALL iot.heartbeat('ais', 'stations', 10000);
CALL iot.resume();
+CALL iot.pause();
+DELETE FROM iot.webserverstreams;
+DROP PROCEDURE ais06q;
+DROP FUNCTION km_distance;
+DROP TABLE stations;
+DROP TABLE vessels6;
+DROP TABLE ais06r;
+
diff --git a/sql/backends/monet5/iot/Tests/ais/Tests/ais07.sql
b/sql/backends/monet5/iot/Tests/ais/Tests/ais07.sql
--- a/sql/backends/monet5/iot/Tests/ais/Tests/ais07.sql
+++ b/sql/backends/monet5/iot/Tests/ais/Tests/ais07.sql
@@ -28,12 +28,11 @@ 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 TABLE vessels (implicit_timestamp timestamp, mmsi int, lat real, lon
real, nav_status tinyint, sog real, rotais smallint);
+CREATE TABLE vessels7 (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';
+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 =
'vessels7' AND sch.name = 'ais';
--Q7 Which ship are currently anchored at the harbors -- Stream + static
@@ -42,7 +41,7 @@ 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)),
+ WITH data AS (SELECT mmsi, geographic_to_cartesian(lat, lon) AS
calc_point FROM vessels7 WHERE (implicit_timestamp, mmsi) IN (SELECT
max(implicit_timestamp), mmsi FROM vessels7 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;
@@ -51,6 +50,14 @@ 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.heartbeat('ais', 'vessels7', 8000);
CALL iot.resume();
+CALL iot.pause();
+DELETE FROM iot.webserverstreams;
+DROP PROCEDURE ais07q;
+DROP FUNCTION geographic_to_cartesian;
+DROP TABLE static_locations;
+DROP TABLE vessels7;
+DROP TABLE ais07r;
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
