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)
);
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
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;