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 blueprint_instance_timeslot;
30 DROP TABLE IF EXISTS lab;
33 id bigint not NULL AUTO_INCREMENT,
34 lab text not NULL unique,
35 silo text not NULL unique,
36 CONSTRAINT id_pk PRIMARY KEY (id)
39 create table timeslot (
40 id bigint not NULL AUTO_INCREMENT,
43 lab_id bigint not NULL,
44 CONSTRAINT id_pk PRIMARY KEY (id),
45 CONSTRAINT lab_id_fk FOREIGN KEY (lab_id)
46 REFERENCES lab (id) MATCH SIMPLE
47 ON UPDATE NO ACTION ON DELETE NO ACTION
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 blueprint_instance_timeslot
91 blueprint_instance_id bigint not NULL,
92 timeslot_id bigint not NULL,
93 CONSTRAINT blueprint_instance_id_fk3 FOREIGN KEY (blueprint_instance_id)
94 REFERENCES blueprint_instance (id) MATCH SIMPLE
95 ON UPDATE NO ACTION ON DELETE NO ACTION,
96 CONSTRAINT timeslot_id_fk FOREIGN KEY (timeslot_id)
97 REFERENCES timeslot (id) MATCH SIMPLE
98 ON UPDATE NO ACTION ON DELETE NO ACTION,
99 unique (blueprint_instance_id, timeslot_id)
102 CREATE TABLE submission
104 id bigint not NULL AUTO_INCREMENT,
105 status text not NULL,
106 timeslot_id bigint not NULL,
107 CONSTRAINT id_pk PRIMARY KEY (id),
108 CONSTRAINT timeslot_id_fk2 FOREIGN KEY (timeslot_id)
109 REFERENCES timeslot (id) MATCH SIMPLE
110 ON UPDATE NO ACTION ON DELETE NO ACTION
113 CREATE TABLE validation_test_result
115 id bigint not NULL AUTO_INCREMENT,
116 blueprint_instance_id bigint not NULL,
118 lab_id bigint not NULL,
122 submission_id bigint,
123 date_of_storage text,
124 CONSTRAINT id_pk PRIMARY KEY (id),
125 CONSTRAINT lab_id_fk2 FOREIGN KEY (lab_id)
126 REFERENCES lab (id) MATCH SIMPLE
127 ON UPDATE NO ACTION ON DELETE NO ACTION,
128 CONSTRAINT submission_id_fk FOREIGN KEY (submission_id)
129 REFERENCES submission (id) MATCH SIMPLE
130 ON UPDATE NO ACTION ON DELETE NO ACTION,
131 CONSTRAINT blueprint_instance_id_fk FOREIGN KEY (blueprint_instance_id)
132 REFERENCES blueprint_instance (id) MATCH SIMPLE
133 ON UPDATE NO ACTION ON DELETE NO ACTION,
134 unique (timestamp, lab_id)
137 CREATE TABLE w_robot_test_result
139 id bigint not NULL AUTO_INCREMENT,
141 validation_test_result_id bigint not NULL,
142 robot_test_results LONGTEXT,
143 CONSTRAINT id_pk PRIMARY KEY (id),
144 CONSTRAINT validation_test_result_id_fk FOREIGN KEY (validation_test_result_id)
145 REFERENCES validation_test_result (id) MATCH SIMPLE
146 ON UPDATE NO ACTION ON DELETE NO ACTION,
147 unique (layer, validation_test_result_id)