1 /* Copyright © 2017 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 clds_service_cache (
126 invariant_service_id VARCHAR(36) NOT NULL,
127 service_id VARCHAR(36) NULL,
128 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
129 object_data MEDIUMBLOB NULL,
130 PRIMARY KEY (invariant_service_id)
131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
133 CREATE TABLE IF NOT EXISTS tosca_model (
134 tosca_model_id VARCHAR(36) NOT NULL,
135 tosca_model_name VARCHAR(80) NOT NULL,
136 policy_type VARCHAR(80) NULL,
138 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
139 PRIMARY KEY (tosca_model_id)
140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
142 CREATE TABLE IF NOT EXISTS tosca_model_revision (
143 tosca_model_revision_id VARCHAR(36) NOT NULL,
144 tosca_model_id VARCHAR(36) NOT NULL,
145 version DOUBLE NOT NULL DEFAULT 1,
146 tosca_model_yaml MEDIUMTEXT NULL,
147 tosca_model_json MEDIUMTEXT NULL,
149 createdTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
150 lastUpdatedTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
151 PRIMARY KEY (tosca_model_revision_id),
152 CONSTRAINT tosca_model_revision_ukey UNIQUE KEY (tosca_model_id, version),
153 CONSTRAINT tosca_model_revision_fkey01 FOREIGN KEY (tosca_model_id) REFERENCES tosca_model (tosca_model_id)
154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
156 CREATE TABLE IF NOT EXISTS dictionary (
157 dictionary_id VARCHAR(36) NOT NULL,
158 dictionary_name VARCHAR(80) NOT NULL,
159 created_by VARCHAR(80),
160 modified_by VARCHAR(80),
161 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
162 PRIMARY KEY (dictionary_id)
163 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
165 CREATE TABLE IF NOT EXISTS dictionary_elements (
166 dict_element_id VARCHAR(36) NOT NULL,
167 dictionary_id VARCHAR(36) NOT NULL,
168 dict_element_name VARCHAR(250) NOT NULL,
169 dict_element_short_name VARCHAR(80) NOT NULL,
170 dict_element_description VARCHAR(250),
171 dict_element_type VARCHAR(80) NOT NULL,
172 created_by VARCHAR(80),
173 modified_by VARCHAR(80),
174 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
175 PRIMARY KEY (dict_element_id),
176 CONSTRAINT dictionary_elements_ukey UNIQUE KEY (dict_element_name, dict_element_short_name),
177 CONSTRAINT dictionary_elements_ukey_fkey01 FOREIGN KEY (dictionary_id) REFERENCES dictionary (dictionary_id)
178 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
181 ADD CONSTRAINT template_bpmn_id_fkey01
182 FOREIGN KEY (template_bpmn_id)
183 REFERENCES template_bpmn (template_bpmn_id);
186 ADD CONSTRAINT template_image_id_fkey01
187 FOREIGN KEY (template_image_id)
188 REFERENCES template_image (template_image_id);
191 ADD CONSTRAINT template_doc_id_fkey01
192 FOREIGN KEY (template_doc_id)
193 REFERENCES template_doc (template_doc_id);
195 ALTER TABLE template_bpmn
196 ADD CONSTRAINT template_id_fkey02
197 FOREIGN KEY (template_id)
198 REFERENCES template (template_id);
200 ALTER TABLE template_image
201 ADD CONSTRAINT template_id_fkey03
202 FOREIGN KEY (template_id)
203 REFERENCES template (template_id);
205 ALTER TABLE template_doc
206 ADD CONSTRAINT template_id_fkey04
207 FOREIGN KEY (template_id)
208 REFERENCES template (template_id);
211 ADD CONSTRAINT template_id_fkey01
212 FOREIGN KEY (template_id)
213 REFERENCES template (template_id);
216 ADD CONSTRAINT model_prop_id_fkey01
217 FOREIGN KEY (model_prop_id)
218 REFERENCES model_properties (model_prop_id);
221 ADD CONSTRAINT model_blueprint_id_fkey01
222 FOREIGN KEY (model_blueprint_id)
223 REFERENCES model_blueprint (model_blueprint_id);
226 ADD CONSTRAINT event_id_fkey01
227 FOREIGN KEY (event_id)
228 REFERENCES event (event_id);
230 ALTER TABLE model_properties
231 ADD CONSTRAINT model_id_fkey01
232 FOREIGN KEY (model_id)
233 REFERENCES model (model_id);
235 ALTER TABLE model_blueprint
236 ADD CONSTRAINT model_id_fkey02
237 FOREIGN KEY (model_id)
238 REFERENCES model (model_id);
240 ALTER TABLE model_instance
241 ADD CONSTRAINT model_id_fkey04
242 FOREIGN KEY (model_id)
243 REFERENCES model (model_id);
246 ADD CONSTRAINT model_id_fkey03
247 FOREIGN KEY (model_id)
248 REFERENCES model (model_id);