Repository: FORTH-ICS-INSPIRE/artemis (language: python)

Issue:
Drop and recreation of dataplane_msms table and view_dataplane_msms v…
This issue introduces changes in "dataplane_msms" table and "view_dataplane_msms" view, in order to support the new design of the "dataplane_view" module.


Discussion / hints:

Fix patch:
diff --git a/.env b/.env
index df33d0601..14068fab1 100644
--- a/.env
+++ b/.env
@@ -1,7 +1,7 @@
 # Docker specific configs
 # use only letters and numbers for the project name
 COMPOSE_PROJECT_NAME=artemis
-DB_VERSION=23
+DB_VERSION=24
 GUI_ENABLED=true
 SYSTEM_VERSION=latest
 HISTORIC=false
diff --git a/artemis-chart/templates/configmap.yaml b/artemis-chart/templates/configmap.yaml
index 6bda27007..3d9405577 100644
--- a/artemis-chart/templates/configmap.yaml
+++ b/artemis-chart/templates/configmap.yaml
@@ -17,7 +17,7 @@ data:
   risId: {{ .Values.risId | default "8522" | quote }}
   dbHost: {{ .Values.dbHost | default "postgres" }}
   dbPort: {{ .Values.dbPort | default "5432" | quote }}
-  dbVersion: {{ .Values.dbVersion | default "23" | quote }}
+  dbVersion: {{ .Values.dbVersion | default "24" | quote }}
   dbName: {{ .Values.dbName | default "artemis_db" | quote }}
   dbUser: {{ .Values.dbUser | default "artemis_user" | quote }}
   dbSchema: {{ .Values.dbSchema | default "public" | quote }}
diff --git a/artemis-chart/values.yaml b/artemis-chart/values.yaml
index 6f905f479..cb6e7ad49 100644
--- a/artemis-chart/values.yaml
+++ b/artemis-chart/values.yaml
@@ -21,7 +21,7 @@ risId: 8522
 # database
 dbHost: postgres
 dbPort: 5432
-dbVersion: 23
+dbVersion: 24
 dbName: artemis_db
 dbUser: artemis_user
 dbPass: Art3m1s
diff --git a/backend-services/database/hasura_init.json b/backend-services/database/hasura_init.json
index c9bf6ffdc..8d22f84ef 100644
--- a/backend-services/database/hasura_init.json
+++ b/backend-services/database/hasura_init.json
@@ -230,14 +230,16 @@
               "allow_aggregations": true,
               "columns": [
                 "hijack_key",
-                "valid_origin_as",
-                "hijack_as",
-                "dst_addr",
-                "msm_type",
+                "hijacked",
+                "hijacker_as",
                 "msm_id",
-                "msm_link",
+                "msm_protocol",
+                "msm_start_time",
+                "msm_stop_time",
+                "msm_type",
+                "num_of_probes",
                 "responding",
-                "hijacked"
+                "target_ip"
               ],
               "filter": {}
             }
diff --git a/backend-services/database/migrate/migrations/scripts/migration_24.sql b/backend-services/database/migrate/migrations/scripts/migration_24.sql
new file mode 100644
index 000000000..c956465f0
--- /dev/null
+++ b/backend-services/database/migrate/migrations/scripts/migration_24.sql
@@ -0,0 +1,21 @@
+DROP VIEW IF EXISTS view_dataplane_msms;
+DROP TABLE IF EXISTS dataplane_msms;
+
+CREATE TABLE IF NOT EXISTS dataplane_msms (
+    key VARCHAR (32) NOT NULL,
+    hijack_key VARCHAR(32) NOT NULL,
+    msm_id BIGINT NOT NULL,
+    msm_type VARCHAR(10),
+    msm_protocol VARCHAR(4),
+    msm_start_time TIMESTAMP,
+    msm_stop_time TIMESTAMP,
+    target_ip inet,
+    num_of_probes BIGINT,
+    hijacker_AS BIGINT,
+    responding VARCHAR(3) DEFAULT 'NA',
+    hijacked VARCHAR(3) DEFAULT 'NA',
+    PRIMARY KEY (key),
+    UNIQUE (key, hijack_key, msm_id)
+);
+
+CREATE OR REPLACE VIEW view_dataplane_msms AS SELECT hijack_key, msm_id, msm_type, msm_protocol, msm_start_time, msm_stop_time, target_ip, num_of_probes, hijacker_AS, responding, hijacked FROM dataplane_msms;
diff --git a/backend-services/database/migrate/migrations/target_steps.json b/backend-services/database/migrate/migrations/target_steps.json
index 230f03ce8..237074c95 100644
--- a/backend-services/database/migrate/migrations/target_steps.json
+++ b/backend-services/database/migrate/migrations/target_steps.json
@@ -137,6 +137,12 @@
             "db_version": "23",
             "description": "Altering hijacks TABLE possible_states constraints and extended intended_process_states and process_states name column to 63 chars",
             "file": "migration_23.sql"
+        },
+        "24": {
+            "id": "24",
+            "db_version": "24",
+            "description": "Drop and recreation of dataplane_msms table and view_dataplane_msms view, including new changes",
+            "file": "migration_24.sql"
         }
     }
 }
diff --git a/other/db/data/tables.sql b/other/db/data/tables.sql
index 9fef98ae2..5274db8c1 100644
--- a/other/db/data/tables.sql
+++ b/other/db/data/tables.sql
@@ -22,7 +22,7 @@ CREATE TRIGGER db_details_no_delete
 BEFORE DELETE ON db_details
 FOR EACH ROW EXECUTE PROCEDURE db_version_no_delete();
 
-INSERT INTO db_details (version, upgraded_on) VALUES (23, now());
+INSERT INTO db_details (version, upgraded_on) VALUES (24, now());
 
 CREATE TABLE IF NOT EXISTS bgp_updates (
     key VARCHAR ( 32 ) NOT NULL,
@@ -241,18 +241,20 @@ CREATE FUNCTION search_bgpupdates_by_as_path_and_hijack_key(key text, as_paths B
 $$ LANGUAGE sql STABLE;
 
 CREATE TABLE IF NOT EXISTS dataplane_msms (
-    key VARCHAR ( 32 ) NOT NULL,
+    key VARCHAR (32) NOT NULL,
     hijack_key VARCHAR(32) NOT NULL,
-    valid_origin_AS BIGINT,
-    hijack_AS BIGINT,
-    dst_addr inet,
-    msm_type VARCHAR(10),
     msm_id BIGINT NOT NULL,
-    msm_link text,
+    msm_type VARCHAR(10),
+    msm_protocol VARCHAR(4),
+    msm_start_time TIMESTAMP,
+    msm_stop_time TIMESTAMP,
+    target_ip inet,
+    num_of_probes BIGINT,
+    hijacker_AS BIGINT,
     responding VARCHAR(3) DEFAULT 'NA',
     hijacked VARCHAR(3) DEFAULT 'NA',
     PRIMARY KEY (key),
     UNIQUE (key, hijack_key, msm_id)
 );
 
-CREATE OR REPLACE VIEW view_dataplane_msms AS SELECT hijack_key, valid_origin_AS, hijack_AS, dst_addr, msm_type, msm_id, msm_link, responding, hijacked FROM dataplane_msms;
+CREATE OR REPLACE VIEW view_dataplane_msms AS SELECT hijack_key, msm_id, msm_type, msm_protocol, msm_start_time, msm_stop_time, target_ip, num_of_probes, hijacker_AS, responding, hijacked FROM dataplane_msms;


Test patch:
diff --git a/testing/detection/db/data/tables.sql b/testing/detection/db/data/tables.sql
index f6e78968e..5142ab8a0 100644
--- a/testing/detection/db/data/tables.sql
+++ b/testing/detection/db/data/tables.sql
@@ -22,7 +22,7 @@ CREATE TRIGGER db_details_no_delete
 BEFORE DELETE ON db_details
 FOR EACH ROW EXECUTE PROCEDURE db_version_no_delete();
 
-INSERT INTO db_details (version, upgraded_on) VALUES (23, now());
+INSERT INTO db_details (version, upgraded_on) VALUES (24, now());
 
 CREATE TABLE IF NOT EXISTS bgp_updates (
     key VARCHAR ( 32 ) NOT NULL,
@@ -225,21 +225,23 @@ CREATE OR REPLACE VIEW view_intended_process_states AS SELECT * FROM intended_pr
 CREATE OR REPLACE VIEW view_db_details AS SELECT version, upgraded_on FROM db_details;
 
 CREATE TABLE IF NOT EXISTS dataplane_msms (
-    key VARCHAR ( 32 ) NOT NULL,
+    key VARCHAR (32) NOT NULL,
     hijack_key VARCHAR(32) NOT NULL,
-    valid_origin_AS BIGINT,
-    hijack_AS BIGINT,
-    dst_addr inet,
-    msm_type VARCHAR(10),
     msm_id BIGINT NOT NULL,
-    msm_link text,
+    msm_type VARCHAR(10),
+    msm_protocol VARCHAR(4),
+    msm_start_time TIMESTAMP,
+    msm_stop_time TIMESTAMP,
+    target_ip inet,
+    num_of_probes BIGINT,
+    hijacker_AS BIGINT,
     responding VARCHAR(3) DEFAULT 'NA',
     hijacked VARCHAR(3) DEFAULT 'NA',
     PRIMARY KEY (key),
     UNIQUE (key, hijack_key, msm_id)
 );
 
-CREATE OR REPLACE VIEW view_dataplane_msms AS SELECT hijack_key, valid_origin_AS, hijack_AS, dst_addr, msm_type, msm_id, msm_link, responding, hijacked FROM dataplane_msms;
+CREATE OR REPLACE VIEW view_dataplane_msms AS SELECT hijack_key, msm_id, msm_type, msm_protocol, msm_start_time, msm_stop_time, target_ip, num_of_probes, hijacker_AS, responding, hijacked FROM dataplane_msms;
 
 CREATE FUNCTION search_bgpupdates_as_path(as_paths BIGINT[])
 RETURNS SETOF view_bgpupdates AS $$