X-Git-Url: https://gerrit.akraino.org/r/gitweb?p=validation.git;a=blobdiff_plain;f=ui%2Fdb-scripts%2Fakraino_blueprint_validation_db.sql;h=6b94c2c9c2f48907236f012a62893814bdc27cbb;hp=06aa4a85f92387c97b55e6431c7ae24c3243d3c6;hb=2eba847ebb6acb2686be08eb1cdafc1b12071e7d;hpb=f86b9715d156238532fcb0bf464bd72e9cf7ce96

diff --git a/ui/db-scripts/akraino_blueprint_validation_db.sql b/ui/db-scripts/akraino_blueprint_validation_db.sql
index 06aa4a8..6b94c2c 100644
--- a/ui/db-scripts/akraino_blueprint_validation_db.sql
+++ b/ui/db-scripts/akraino_blueprint_validation_db.sql
@@ -18,16 +18,18 @@ SET FOREIGN_KEY_CHECKS=1;
 
 use akraino;
 
-DROP TABLE IF EXISTS submission;
 DROP TABLE IF EXISTS blueprint_instance_for_validation;
 DROP TABLE IF EXISTS blueprint;
 DROP TABLE IF EXISTS silo;
 DROP TABLE IF EXISTS timeslot;
 DROP TABLE IF EXISTS lab;
+DROP TABLE IF EXISTS w_robot_test_result;
+DROP TABLE IF EXISTS validation_test_result;
+DROP TABLE IF EXISTS submission;
 
 create table lab (
    id bigint not NULL AUTO_INCREMENT,
-   lab text not NULL,
+   lab text not NULL unique,
    CONSTRAINT id_pk PRIMARY KEY (id)
 );
 
@@ -45,7 +47,7 @@ create table timeslot (
 create table silo (
    id bigint not NULL AUTO_INCREMENT,
    silo text not NULL,
-   lab_id bigint not NULL,
+   lab_id bigint not NULL unique,
    CONSTRAINT id_pk PRIMARY KEY (id),
    CONSTRAINT lab_id_fk2 FOREIGN KEY (lab_id)
       REFERENCES lab (id) MATCH SIMPLE
@@ -69,24 +71,54 @@ CREATE TABLE blueprint_instance_for_validation
    CONSTRAINT id_pk PRIMARY KEY (id),
    CONSTRAINT blueprint_id_fk FOREIGN KEY (blueprint_id)
       REFERENCES blueprint (id) MATCH SIMPLE
-      ON UPDATE NO ACTION ON DELETE NO ACTION
+      ON UPDATE NO ACTION ON DELETE NO ACTION,
+   unique (version, layer, blueprint_id)
 );
 
 CREATE TABLE submission
 (
    id bigint not NULL AUTO_INCREMENT,
    status text not NULL,
-   jenkins_queue_job_item_url text,
-   nexus_result_url text,
-   blueprint_instance_for_validation_id bigint not NULL,
    timeslot_id bigint not NULL,
    CONSTRAINT id_pk PRIMARY KEY (id),
-   CONSTRAINT blueprint_instance_for_validation_id_fk FOREIGN KEY (blueprint_instance_for_validation_id)
-      REFERENCES blueprint_instance_for_validation (id) MATCH SIMPLE
-      ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT timeslot_id_fk FOREIGN KEY (timeslot_id)
       REFERENCES timeslot (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
 );
 
+CREATE TABLE validation_test_result
+(
+   id bigint not NULL AUTO_INCREMENT,
+   blueprint_name varchar(20) not NULL,
+   version text not NULL,
+   lab_id bigint not NULL,
+   timestamp text,
+   all_layers boolean,
+   optional boolean,
+   result boolean,
+   submission_id bigint,
+   date_of_storage text,
+   CONSTRAINT id_pk PRIMARY KEY (id),
+   CONSTRAINT lab_id_fk3 FOREIGN KEY (lab_id)
+      REFERENCES lab (id) MATCH SIMPLE
+      ON UPDATE NO ACTION ON DELETE NO ACTION,
+   CONSTRAINT submission_id_fk FOREIGN KEY (submission_id)
+      REFERENCES submission (id) MATCH SIMPLE
+      ON UPDATE NO ACTION ON DELETE NO ACTION,
+   unique (timestamp, lab_id)
+);
+
+CREATE TABLE w_robot_test_result
+(
+   id bigint not NULL AUTO_INCREMENT,
+   layer text not NULL,
+   validation_test_result_id bigint not NULL,
+   robot_test_results LONGTEXT,
+   CONSTRAINT id_pk PRIMARY KEY (id),
+   CONSTRAINT validation_test_result_id_fk FOREIGN KEY (validation_test_result_id)
+      REFERENCES validation_test_result (id) MATCH SIMPLE
+      ON UPDATE NO ACTION ON DELETE NO ACTION,
+   unique (layer, validation_test_result_id)
+);
+
 commit;