1 /* Copyright © 2017-2019 AT&T, Amdocs, Bell Canada
3 * Licensed under the Apache License, Version 2.0 (the "License");
4 * you may not use this file except in compliance with the License.
5 * You may obtain a copy of the License at
7 * http://www.apache.org/licenses/LICENSE-2.0
9 * Unless required by applicable law or agreed to in writing, software
10 * distributed under the License is distributed on an "AS IS" BASIS,
11 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12 * See the License for the specific language governing permissions and
13 * limitations under the License.
17 # Create CLDS database objects (tables, etc.)
20 CREATE DATABASE `cldsdb4`;
24 GRANT ALL on cldsdb4.* to 'clds' identified by 'sidnnd83K' with GRANT OPTION;
25 GRANT SELECT on mysql.proc TO 'clds';
29 CREATE TABLE template (
30 template_id VARCHAR(36) NOT NULL,
31 template_name VARCHAR(80) NOT NULL,
32 template_bpmn_id VARCHAR(36) NULL,
33 template_image_id VARCHAR(36) NULL,
34 template_doc_id VARCHAR(36) NULL,
35 PRIMARY KEY (template_id),
36 UNIQUE (template_name)
37 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
39 CREATE TABLE template_bpmn (
40 template_bpmn_id VARCHAR(36) NOT NULL,
41 template_id VARCHAR(36) NOT NULL,
42 template_bpmn_text MEDIUMTEXT NOT NULL,
44 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
45 PRIMARY KEY (template_bpmn_id)
46 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
48 CREATE TABLE template_image (
49 template_image_id VARCHAR(36) NOT NULL,
50 template_id VARCHAR(36) NOT NULL,
51 template_image_text MEDIUMTEXT NULL,
53 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
54 PRIMARY KEY (template_image_id)
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
57 CREATE TABLE template_doc (
58 template_doc_id VARCHAR(36) NOT NULL,
59 template_id VARCHAR(36) NOT NULL,
60 template_doc_text MEDIUMTEXT NULL,
62 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
63 PRIMARY KEY (template_doc_id)
64 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
67 model_id VARCHAR(36) NOT NULL,
68 model_name VARCHAR(80) NOT NULL,
69 template_id VARCHAR(36) NULL,
70 model_prop_id VARCHAR(36) NULL,
71 model_blueprint_id VARCHAR(36) NULL,
72 event_id VARCHAR(36) NULL,
73 control_name_prefix VARCHAR(80) NULL,
74 control_name_uuid VARCHAR(36) NOT NULL,
75 service_type_id VARCHAR(80) NULL,
76 deployment_id VARCHAR(80) NULL,
77 deployment_status_url VARCHAR(300) NULL,
78 PRIMARY KEY (model_id),
80 UNIQUE (control_name_uuid),
81 UNIQUE (service_type_id),
82 UNIQUE (deployment_id)
83 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
85 CREATE TABLE model_properties (
86 model_prop_id VARCHAR(36) NOT NULL,
87 model_id VARCHAR(36) NOT NULL,
88 model_prop_text MEDIUMTEXT NULL,
90 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
91 PRIMARY KEY (model_prop_id)
92 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
94 CREATE TABLE model_blueprint (
95 model_blueprint_id VARCHAR(36) NOT NULL,
96 model_id VARCHAR(36) NOT NULL,
97 model_blueprint_text MEDIUMTEXT NULL,
99 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
100 PRIMARY KEY (model_blueprint_id)
101 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
103 CREATE TABLE model_instance (
104 model_instance_id VARCHAR(36) NOT NULL,
105 model_id VARCHAR(36) NOT NULL,
106 vm_name VARCHAR(250) NOT NULL,
107 location VARCHAR(250) NULL,
108 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
109 PRIMARY KEY (model_instance_id),
110 UNIQUE (model_id, vm_name)
111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
114 event_id VARCHAR(36) NOT NULL,
115 model_id VARCHAR(36) NULL,
116 action_cd VARCHAR(80) NOT NULL,
117 action_state_cd VARCHAR(80) NULL,
118 prev_event_id VARCHAR(36) NULL,
119 process_instance_id VARCHAR(80) NULL,
120 user_id VARCHAR(80) NULL,
121 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
122 PRIMARY KEY (event_id)
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
125 CREATE TABLE IF NOT EXISTS tosca_model (
126 tosca_model_id VARCHAR(36) NOT NULL,
127 tosca_model_name VARCHAR(80) NOT NULL,
128 policy_type VARCHAR(80) NULL,
130 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
131 PRIMARY KEY (tosca_model_id)
132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
134 CREATE TABLE IF NOT EXISTS tosca_model_revision (
135 tosca_model_revision_id VARCHAR(36) NOT NULL,
136 tosca_model_id VARCHAR(36) NOT NULL,
137 version DOUBLE NOT NULL DEFAULT 1,
138 tosca_model_yaml MEDIUMTEXT NULL,
139 tosca_model_json MEDIUMTEXT NULL,
141 createdTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
142 lastUpdatedTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
143 PRIMARY KEY (tosca_model_revision_id),
144 CONSTRAINT tosca_model_revision_ukey UNIQUE KEY (tosca_model_id, version),
145 CONSTRAINT tosca_model_revision_fkey01 FOREIGN KEY (tosca_model_id) REFERENCES tosca_model (tosca_model_id)
146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
148 CREATE TABLE IF NOT EXISTS dictionary (
149 dictionary_id VARCHAR(36) NOT NULL,
150 dictionary_name VARCHAR(80) NOT NULL,
151 created_by VARCHAR(80),
152 modified_by VARCHAR(80),
153 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
154 PRIMARY KEY (dictionary_id)
155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
157 CREATE TABLE IF NOT EXISTS dictionary_elements (
158 dict_element_id VARCHAR(36) NOT NULL,
159 dictionary_id VARCHAR(36) NOT NULL,
160 dict_element_name VARCHAR(250) NOT NULL,
161 dict_element_short_name VARCHAR(80) NOT NULL,
162 dict_element_description VARCHAR(250),
163 dict_element_type VARCHAR(80) NOT NULL,
164 created_by VARCHAR(80),
165 modified_by VARCHAR(80),
166 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
167 PRIMARY KEY (dict_element_id),
168 CONSTRAINT dictionary_elements_ukey UNIQUE KEY (dict_element_name, dict_element_short_name),
169 CONSTRAINT dictionary_elements_ukey_fkey01 FOREIGN KEY (dictionary_id) REFERENCES dictionary (dictionary_id)
170 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
173 ADD CONSTRAINT template_bpmn_id_fkey01
174 FOREIGN KEY (template_bpmn_id)
175 REFERENCES template_bpmn (template_bpmn_id);
178 ADD CONSTRAINT template_image_id_fkey01
179 FOREIGN KEY (template_image_id)
180 REFERENCES template_image (template_image_id);
183 ADD CONSTRAINT template_doc_id_fkey01
184 FOREIGN KEY (template_doc_id)
185 REFERENCES template_doc (template_doc_id);
187 ALTER TABLE template_bpmn
188 ADD CONSTRAINT template_id_fkey02
189 FOREIGN KEY (template_id)
190 REFERENCES template (template_id);
192 ALTER TABLE template_image
193 ADD CONSTRAINT template_id_fkey03
194 FOREIGN KEY (template_id)
195 REFERENCES template (template_id);
197 ALTER TABLE template_doc
198 ADD CONSTRAINT template_id_fkey04
199 FOREIGN KEY (template_id)
200 REFERENCES template (template_id);
203 ADD CONSTRAINT template_id_fkey01
204 FOREIGN KEY (template_id)
205 REFERENCES template (template_id);
208 ADD CONSTRAINT model_prop_id_fkey01
209 FOREIGN KEY (model_prop_id)
210 REFERENCES model_properties (model_prop_id);
213 ADD CONSTRAINT model_blueprint_id_fkey01
214 FOREIGN KEY (model_blueprint_id)
215 REFERENCES model_blueprint (model_blueprint_id);
218 ADD CONSTRAINT event_id_fkey01
219 FOREIGN KEY (event_id)
220 REFERENCES event (event_id);
222 ALTER TABLE model_properties
223 ADD CONSTRAINT model_id_fkey01
224 FOREIGN KEY (model_id)
225 REFERENCES model (model_id);
227 ALTER TABLE model_blueprint
228 ADD CONSTRAINT model_id_fkey02
229 FOREIGN KEY (model_id)
230 REFERENCES model (model_id);
232 ALTER TABLE model_instance
233 ADD CONSTRAINT model_id_fkey04
234 FOREIGN KEY (model_id)
235 REFERENCES model (model_id);
238 ADD CONSTRAINT model_id_fkey03
239 FOREIGN KEY (model_id)
240 REFERENCES model (model_id);