SET FOREIGN_KEY_CHECKS=1;
-use akraino;
+use akraino_bluvalui;
-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;
+DROP TABLE IF EXISTS blueprint_instance_blueprint_layer;
+DROP TABLE IF EXISTS blueprint_instance;
+DROP TABLE IF EXISTS blueprint_layer;
+DROP TABLE IF EXISTS blueprint;
+DROP TABLE IF EXISTS timeslot;
+DROP TABLE IF EXISTS blueprint_instance_timeslot;
+DROP TABLE IF EXISTS lab;
create table lab (
id bigint not NULL AUTO_INCREMENT,
- lab text not NULL unique,
+ lab varchar(255) not NULL unique,
+ silo varchar(255) not NULL unique,
CONSTRAINT id_pk PRIMARY KEY (id)
);
ON UPDATE NO ACTION ON DELETE NO ACTION
);
-create table silo (
+CREATE TABLE blueprint
+(
id bigint not NULL AUTO_INCREMENT,
- silo text 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
- ON UPDATE NO ACTION ON DELETE NO ACTION
+ blueprint_name varchar(255) not NULL unique,
+ CONSTRAINT id_pk PRIMARY KEY (id)
);
-CREATE TABLE blueprint
+CREATE TABLE blueprint_layer
(
id bigint not NULL AUTO_INCREMENT,
- blueprint_name varchar(20) not NULL unique,
+ layer varchar(255) not NULL unique,
CONSTRAINT id_pk PRIMARY KEY (id)
);
-CREATE TABLE blueprint_instance_for_validation
+CREATE TABLE blueprint_instance
(
id bigint not NULL AUTO_INCREMENT,
blueprint_id bigint not NULL,
- version text not NULL,
- layer text not NULL,
- layer_description text not NULL,
+ version varchar(255) not NULL,
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,
- unique (version, layer, blueprint_id)
+ unique (version, blueprint_id)
+);
+
+CREATE TABLE blueprint_instance_blueprint_layer
+(
+ blueprint_instance_id bigint not NULL,
+ blueprint_layer_id bigint not NULL,
+ CONSTRAINT blueprint_instance_id_fk2 FOREIGN KEY (blueprint_instance_id)
+ REFERENCES blueprint_instance (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT blueprint_layer_id_fk FOREIGN KEY (blueprint_layer_id)
+ REFERENCES blueprint_layer (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ unique (blueprint_instance_id, blueprint_layer_id)
+);
+
+CREATE TABLE blueprint_instance_timeslot
+(
+ blueprint_instance_id bigint not NULL,
+ timeslot_id bigint not NULL,
+ CONSTRAINT blueprint_instance_id_fk3 FOREIGN KEY (blueprint_instance_id)
+ REFERENCES blueprint_instance (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,
+ unique (blueprint_instance_id, timeslot_id)
);
CREATE TABLE submission
status text not NULL,
timeslot_id bigint not NULL,
CONSTRAINT id_pk PRIMARY KEY (id),
- CONSTRAINT timeslot_id_fk FOREIGN KEY (timeslot_id)
+ CONSTRAINT timeslot_id_fk2 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,
+ blueprint_instance_id bigint not NULL,
all_layers boolean,
+ lab_id bigint not NULL,
+ timestamp varchar(255),
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)
+ CONSTRAINT lab_id_fk2 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,
+ CONSTRAINT blueprint_instance_id_fk FOREIGN KEY (blueprint_instance_id)
+ REFERENCES blueprint_instance (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,
+ layer varchar(255) not NULL,
validation_test_result_id bigint not NULL,
robot_test_results LONGTEXT,
CONSTRAINT id_pk PRIMARY KEY (id),