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 blueprint_instance_for_validation;
22 DROP TABLE IF EXISTS blueprint;
23 DROP TABLE IF EXISTS silo;
24 DROP TABLE IF EXISTS timeslot;
25 DROP TABLE IF EXISTS lab;
26 DROP TABLE IF EXISTS w_robot_test_result;
27 DROP TABLE IF EXISTS validation_test_result;
28 DROP TABLE IF EXISTS submission;
31 id bigint not NULL AUTO_INCREMENT,
32 lab text not NULL unique,
33 CONSTRAINT id_pk PRIMARY KEY (id)
36 create table timeslot (
37 id bigint not NULL AUTO_INCREMENT,
40 lab_id bigint not NULL,
41 CONSTRAINT id_pk PRIMARY KEY (id),
42 CONSTRAINT lab_id_fk FOREIGN KEY (lab_id)
43 REFERENCES lab (id) MATCH SIMPLE
44 ON UPDATE NO ACTION ON DELETE NO ACTION
48 id bigint not NULL AUTO_INCREMENT,
50 lab_id bigint not NULL unique,
51 CONSTRAINT id_pk PRIMARY KEY (id),
52 CONSTRAINT lab_id_fk2 FOREIGN KEY (lab_id)
53 REFERENCES lab (id) MATCH SIMPLE
54 ON UPDATE NO ACTION ON DELETE NO ACTION
57 CREATE TABLE blueprint
59 id bigint not NULL AUTO_INCREMENT,
60 blueprint_name varchar(20) not NULL unique,
61 CONSTRAINT id_pk PRIMARY KEY (id)
64 CREATE TABLE blueprint_instance_for_validation
66 id bigint not NULL AUTO_INCREMENT,
67 blueprint_id bigint not NULL,
68 version text not NULL,
70 layer_description text not NULL,
71 CONSTRAINT id_pk PRIMARY KEY (id),
72 CONSTRAINT blueprint_id_fk FOREIGN KEY (blueprint_id)
73 REFERENCES blueprint (id) MATCH SIMPLE
74 ON UPDATE NO ACTION ON DELETE NO ACTION,
75 unique (version, layer, blueprint_id)
78 CREATE TABLE submission
80 id bigint not NULL AUTO_INCREMENT,
82 timeslot_id bigint not NULL,
83 CONSTRAINT id_pk PRIMARY KEY (id),
84 CONSTRAINT timeslot_id_fk FOREIGN KEY (timeslot_id)
85 REFERENCES timeslot (id) MATCH SIMPLE
86 ON UPDATE NO ACTION ON DELETE NO ACTION
89 CREATE TABLE validation_test_result
91 id bigint not NULL AUTO_INCREMENT,
92 blueprint_name varchar(20) not NULL,
93 version text not NULL,
94 lab_id bigint not NULL,
100 date_of_storage text,
101 CONSTRAINT id_pk PRIMARY KEY (id),
102 CONSTRAINT lab_id_fk3 FOREIGN KEY (lab_id)
103 REFERENCES lab (id) MATCH SIMPLE
104 ON UPDATE NO ACTION ON DELETE NO ACTION,
105 CONSTRAINT submission_id_fk FOREIGN KEY (submission_id)
106 REFERENCES submission (id) MATCH SIMPLE
107 ON UPDATE NO ACTION ON DELETE NO ACTION,
108 unique (timestamp, lab_id)
111 CREATE TABLE w_robot_test_result
113 id bigint not NULL AUTO_INCREMENT,
115 validation_test_result_id bigint not NULL,
116 robot_test_results LONGTEXT,
117 CONSTRAINT id_pk PRIMARY KEY (id),
118 CONSTRAINT validation_test_result_id_fk FOREIGN KEY (validation_test_result_id)
119 REFERENCES validation_test_result (id) MATCH SIMPLE
120 ON UPDATE NO ACTION ON DELETE NO ACTION,
121 unique (layer, validation_test_result_id)