Changeset: 5bc31cb13c0a for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5bc31cb13c0a
Added Files:
sql/backends/monet5/Tests/cquery01.sql
Removed Files:
sql/backends/monet5/timetrails/Tests/ais/Tests/All
sql/backends/monet5/timetrails/Tests/ais/Tests/ais01.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais02.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais03.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais04.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais05.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais06.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais07.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais08.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais09.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais10.sql
sql/backends/monet5/timetrails/Tests/ais/Tests/ais11.sql
sql/backends/monet5/timetrails/Tests/cquery00.sql
sql/backends/monet5/timetrails/Tests/logger.sql
sql/backends/monet5/timetrails/Tests/receptor00.sql
sql/backends/monet5/timetrails/Tests/receptor01.sql
sql/backends/monet5/timetrails/Tests/webtest.sql
sql/backends/monet5/timetrails/Tests/x.ql
Modified Files:
sql/backends/monet5/Tests/All
sql/backends/monet5/timetrails/Tests/All
Branch: timetrails
Log Message:
Cleanup
diffs (truncated from 807 to 300 lines):
diff --git a/sql/backends/monet5/Tests/All b/sql/backends/monet5/Tests/All
--- a/sql/backends/monet5/Tests/All
+++ b/sql/backends/monet5/Tests/All
@@ -76,3 +76,5 @@ limithack
shutdown
HAVE_HGE?int_notation_1e5
+
+cquery01
diff --git a/sql/backends/monet5/Tests/cquery01.sql
b/sql/backends/monet5/Tests/cquery01.sql
new file mode 100644
--- /dev/null
+++ b/sql/backends/monet5/Tests/cquery01.sql
@@ -0,0 +1,2 @@
+-- inspect the status of all stream relations
+select * from streams.baskets();
diff --git a/sql/backends/monet5/timetrails/Tests/All
b/sql/backends/monet5/timetrails/Tests/All
--- a/sql/backends/monet5/timetrails/Tests/All
+++ b/sql/backends/monet5/timetrails/Tests/All
@@ -1,23 +1,3 @@
demoschema
loaddata
-iot00
-iot01
-iot02
-iot03
-iot04
-iot06
-iot10
-iot11
-iot12
-iot13
-iot14
-#iot15
-iot16
-receptor00
-receptor01
-#petrinet00
-webtest
-inputoutput
-export00
-logger
diff --git a/sql/backends/monet5/timetrails/Tests/ais/Tests/All
b/sql/backends/monet5/timetrails/Tests/ais/Tests/All
deleted file mode 100644
--- a/sql/backends/monet5/timetrails/Tests/ais/Tests/All
+++ /dev/null
@@ -1,11 +0,0 @@
-ais01
-ais02
-ais03
-ais04
-ais05
-ais06
-ais07
-ais08
-ais09
-ais10
-ais11
diff --git a/sql/backends/monet5/timetrails/Tests/ais/Tests/ais01.sql
b/sql/backends/monet5/timetrails/Tests/ais/Tests/ais01.sql
deleted file mode 100644
--- a/sql/backends/monet5/timetrails/Tests/ais/Tests/ais01.sql
+++ /dev/null
@@ -1,34 +0,0 @@
-CREATE SCHEMA ais;
-SET SCHEMA ais;
-SET optimizer = 'iot_pipe';
-
--- Vessels positions reports table based on AIS messages types 1, 2 and 3
-CREATE STREAM TABLE vessels1 (implicit_timestamp timestamp, mmsi int, lat
real, lon real, nav_status smallint, 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 =
'vessels1' AND sch.name = 'ais';
-
---Q1 Calculate speed of ships (in knots) -- Stream only
-
-CREATE STREAM TABLE ais01r (calc_time timestamp, mmsi int, sog real);
-
-CREATE PROCEDURE ais01q()
-BEGIN
- INSERT INTO ais01r
- WITH data_time AS (SELECT current_timestamp AS cur_time)
- 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', '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/timetrails/Tests/ais/Tests/ais02.sql
b/sql/backends/monet5/timetrails/Tests/ais/Tests/ais02.sql
deleted file mode 100644
--- a/sql/backends/monet5/timetrails/Tests/ais/Tests/ais02.sql
+++ /dev/null
@@ -1,32 +0,0 @@
-SET SCHEMA ais;
-SET optimizer = 'iot_pipe';
-
--- Vessels positions reports table based on AIS messages types 1, 2 and 3
-CREATE STREAM TABLE vessels2 (implicit_timestamp timestamp, mmsi int, lat
real, lon real, nav_status smallint, 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 =
'vessels2' AND sch.name = 'ais';
-
---Q2 Number of distinct ship in the last 8 seconds -- Stream only
-
-CREATE TABLE ais02r (calc_time timestamp, number_ships int);
-
-CREATE PROCEDURE ais02q()
-BEGIN
- INSERT INTO ais02r
- 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', '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/timetrails/Tests/ais/Tests/ais03.sql
b/sql/backends/monet5/timetrails/Tests/ais/Tests/ais03.sql
deleted file mode 100644
--- a/sql/backends/monet5/timetrails/Tests/ais/Tests/ais03.sql
+++ /dev/null
@@ -1,33 +0,0 @@
-SET SCHEMA ais;
-SET optimizer = 'iot_pipe';
-
--- Vessels positions reports table based on AIS messages types 1, 2 and 3
-CREATE STREAM TABLE vessels3 (implicit_timestamp timestamp, mmsi int, lat
real, lon real, nav_status smallint, 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 =
'vessels3' AND sch.name = 'ais';
-
---Q3 Currently anchorred ship -- Stream only
-
-CREATE TABLE ais03r (calc_time timestamp, mmsi int);
-
-CREATE PROCEDURE ais03q()
-BEGIN
- INSERT INTO ais03r
- WITH data_time AS (SELECT current_timestamp AS cur_time)
- 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', '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/timetrails/Tests/ais/Tests/ais04.sql
b/sql/backends/monet5/timetrails/Tests/ais/Tests/ais04.sql
deleted file mode 100644
--- a/sql/backends/monet5/timetrails/Tests/ais/Tests/ais04.sql
+++ /dev/null
@@ -1,33 +0,0 @@
-SET SCHEMA ais;
-SET optimizer = 'iot_pipe';
-
--- Vessels positions reports table based on AIS messages types 1, 2 and 3
-CREATE STREAM TABLE vessels4 (implicit_timestamp timestamp, mmsi int, lat
real, lon real, nav_status smallint, 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 =
'vessels4' AND sch.name = 'ais';
-
---Q4 Ship turning degree > 180 -- Stream only
-
-CREATE STREAM TABLE ais04r (calc_time timestamp, mmsi int);
-
-CREATE PROCEDURE ais04q()
-BEGIN
- INSERT INTO ais04r
- WITH data_time AS (SELECT current_timestamp AS cur_time)
- 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', '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/timetrails/Tests/ais/Tests/ais05.sql
b/sql/backends/monet5/timetrails/Tests/ais/Tests/ais05.sql
deleted file mode 100644
--- a/sql/backends/monet5/timetrails/Tests/ais/Tests/ais05.sql
+++ /dev/null
@@ -1,47 +0,0 @@
-SET SCHEMA ais;
-SET optimizer = 'iot_pipe';
-
--- Calculate distance in kms between two coordinates:
http://www.movable-type.co.uk/scripts/latlong.html
--- Therefore we don't need to create a geometry element when working only with
stream data (the iot web server doesn't support geom types yet)
-CREATE FUNCTION km_distance(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT)
RETURNS FLOAT
-BEGIN
- DECLARE deg_to_rad FLOAT, deg_to_rad_div FLOAT, aux FLOAT;
- SET deg_to_rad = pi() / 180;
- SET deg_to_rad_div = deg_to_rad / 2;
- SET aux = sys.power(sys.sin((lat2 - lat1) * deg_to_rad_div), 2) +
sys.cos(lat1 * deg_to_rad) * sys.cos(lat2 * deg_to_rad) *
sys.power(sys.sin((lon2 - lon1) * deg_to_rad_div), 2);
- RETURN 12742 * sys.atan(sys.sqrt(aux), sys.sqrt(1 - aux));
-END;
-
--- Vessels positions reports table based on AIS messages types 1, 2 and 3
-CREATE STREAM TABLE vessels5 (implicit_timestamp timestamp, mmsi int, lat
real, lon real, nav_status smallint, 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 =
'vessels5' AND sch.name = 'ais';
-
---Q5 Closest ship to each other -- Stream only
-
-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 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 INNER JOIN
data d2 ON 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', '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/timetrails/Tests/ais/Tests/ais06.sql
b/sql/backends/monet5/timetrails/Tests/ais/Tests/ais06.sql
deleted file mode 100644
--- a/sql/backends/monet5/timetrails/Tests/ais/Tests/ais06.sql
+++ /dev/null
@@ -1,54 +0,0 @@
-SET SCHEMA ais;
-SET optimizer = 'iot_pipe';
-
--- Calculate distance in kms between two coordinates:
http://www.movable-type.co.uk/scripts/latlong.html
--- Therefore we don't need to create a geometry element when working only with
stream data (the iot web server doesn't support geom types yet)
-CREATE FUNCTION km_distance(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT)
RETURNS FLOAT
-BEGIN
- DECLARE deg_to_rad FLOAT, deg_to_rad_div FLOAT, aux FLOAT;
- SET deg_to_rad = pi() / 180;
- SET deg_to_rad_div = deg_to_rad / 2;
- SET aux = sys.power(sys.sin((lat2 - lat1) * deg_to_rad_div), 2) +
sys.cos(lat1 * deg_to_rad) * sys.cos(lat2 * deg_to_rad) *
sys.power(sys.sin((lon2 - lon1) * deg_to_rad_div), 2);
- RETURN 12742 * sys.atan(sys.sqrt(aux), sys.sqrt(1 - aux));
-END;
-
--- Vessels positions reports table based on AIS messages types 1, 2 and 3
-CREATE STREAM TABLE vessels6 (implicit_timestamp timestamp, mmsi int, lat
real, lon real, nav_status smallint, 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 =
'vessels6' 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 =
'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 PROCEDURE ais06q()
-BEGIN
- INSERT INTO ais06r
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
