2 * Copyright (c) 2019 AT&T Intellectual Property. All rights reserved.
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
8 * http://www.apache.org/licenses/LICENSE-2.0
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
17 SET FOREIGN_KEY_CHECKS=1;
21 DROP TABLE IF EXISTS w_robot_test_result;
22 DROP TABLE IF EXISTS validation_test_result;
23 DROP TABLE IF EXISTS submission;
24 DROP TABLE IF EXISTS blueprint_instance_blueprint_layer;
25 DROP TABLE IF EXISTS blueprint_instance;
26 DROP TABLE IF EXISTS blueprint_layer;
27 DROP TABLE IF EXISTS blueprint;
28 DROP TABLE IF EXISTS timeslot;
29 DROP TABLE IF EXISTS lab;
32 id bigint not NULL AUTO_INCREMENT,
33 lab text not NULL unique,
34 silo text not NULL unique,
35 CONSTRAINT id_pk PRIMARY KEY (id)
38 create table timeslot (
39 id bigint not NULL AUTO_INCREMENT,
42 lab_id bigint not NULL,
43 CONSTRAINT id_pk PRIMARY KEY (id),
44 CONSTRAINT lab_id_fk FOREIGN KEY (lab_id)
45 REFERENCES lab (id) MATCH SIMPLE
46 ON UPDATE NO ACTION ON DELETE NO ACTION,
47 unique (start_date_time, lab_id)
50 CREATE TABLE blueprint
52 id bigint not NULL AUTO_INCREMENT,
53 blueprint_name varchar(20) not NULL unique,
54 CONSTRAINT id_pk PRIMARY KEY (id)
57 CREATE TABLE blueprint_layer
59 id bigint not NULL AUTO_INCREMENT,
60 layer text not NULL unique,
61 CONSTRAINT id_pk PRIMARY KEY (id)
64 CREATE TABLE blueprint_instance
66 id bigint not NULL AUTO_INCREMENT,
67 blueprint_id bigint not NULL,
68 version text not NULL,
69 CONSTRAINT id_pk PRIMARY KEY (id),
70 CONSTRAINT blueprint_id_fk FOREIGN KEY (blueprint_id)
71 REFERENCES blueprint (id) MATCH SIMPLE
72 ON UPDATE NO ACTION ON DELETE NO ACTION,
73 unique (version, blueprint_id)
76 CREATE TABLE blueprint_instance_blueprint_layer
78 blueprint_instance_id bigint not NULL,
79 blueprint_layer_id bigint not NULL,
80 CONSTRAINT blueprint_instance_id_fk2 FOREIGN KEY (blueprint_instance_id)
81 REFERENCES blueprint_instance (id) MATCH SIMPLE
82 ON UPDATE NO ACTION ON DELETE NO ACTION,
83 CONSTRAINT blueprint_layer_id_fk FOREIGN KEY (blueprint_layer_id)
84 REFERENCES blueprint_layer (id) MATCH SIMPLE
85 ON UPDATE NO ACTION ON DELETE NO ACTION,
86 unique (blueprint_instance_id, blueprint_layer_id)
89 CREATE TABLE submission
91 id bigint not NULL AUTO_INCREMENT,
93 timeslot_id bigint not NULL,
94 CONSTRAINT id_pk PRIMARY KEY (id),
95 CONSTRAINT timeslot_id_fk FOREIGN KEY (timeslot_id)
96 REFERENCES timeslot (id) MATCH SIMPLE
97 ON UPDATE NO ACTION ON DELETE NO ACTION
100 CREATE TABLE validation_test_result
102 id bigint not NULL AUTO_INCREMENT,
103 blueprint_instance_id bigint not NULL,
105 lab_id bigint not NULL,
109 submission_id bigint,
110 date_of_storage text,
111 CONSTRAINT id_pk PRIMARY KEY (id),
112 CONSTRAINT lab_id_fk3 FOREIGN KEY (lab_id)
113 REFERENCES lab (id) MATCH SIMPLE
114 ON UPDATE NO ACTION ON DELETE NO ACTION,
115 CONSTRAINT submission_id_fk FOREIGN KEY (submission_id)
116 REFERENCES submission (id) MATCH SIMPLE
117 ON UPDATE NO ACTION ON DELETE NO ACTION,
118 CONSTRAINT blueprint_instance_id_fk FOREIGN KEY (blueprint_instance_id)
119 REFERENCES blueprint_instance (id) MATCH SIMPLE
120 ON UPDATE NO ACTION ON DELETE NO ACTION,
121 unique (timestamp, lab_id)
124 CREATE TABLE w_robot_test_result
126 id bigint not NULL AUTO_INCREMENT,
128 validation_test_result_id bigint not NULL,
129 robot_test_results LONGTEXT,
130 CONSTRAINT id_pk PRIMARY KEY (id),
131 CONSTRAINT validation_test_result_id_fk FOREIGN KEY (validation_test_result_id)
132 REFERENCES validation_test_result (id) MATCH SIMPLE
133 ON UPDATE NO ACTION ON DELETE NO ACTION,
134 unique (layer, validation_test_result_id)