2 # Create CLDS database objects (tables, etc.)
5 CREATE DATABASE `cldsdb4`;
9 GRANT ALL on cldsdb4.* to 'clds' identified by 'sidnnd83K' with GRANT OPTION;
10 GRANT SELECT on mysql.proc TO 'clds';
14 CREATE TABLE template (
15 template_id VARCHAR(36) NOT NULL,
16 template_name VARCHAR(80) NOT NULL,
17 template_bpmn_id VARCHAR(36) NULL,
18 template_image_id VARCHAR(36) NULL,
19 template_doc_id VARCHAR(36) NULL,
20 PRIMARY KEY (template_id),
21 UNIQUE (template_name)
22 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
24 CREATE TABLE template_bpmn (
25 template_bpmn_id VARCHAR(36) NOT NULL,
26 template_id VARCHAR(36) NOT NULL,
27 template_bpmn_text MEDIUMTEXT NOT NULL,
29 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
30 PRIMARY KEY (template_bpmn_id)
31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
33 CREATE TABLE template_image (
34 template_image_id VARCHAR(36) NOT NULL,
35 template_id VARCHAR(36) NOT NULL,
36 template_image_text MEDIUMTEXT NULL,
38 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
39 PRIMARY KEY (template_image_id)
40 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
42 CREATE TABLE template_doc (
43 template_doc_id VARCHAR(36) NOT NULL,
44 template_id VARCHAR(36) NOT NULL,
45 template_doc_text MEDIUMTEXT NULL,
47 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
48 PRIMARY KEY (template_doc_id)
49 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
52 model_id VARCHAR(36) NOT NULL,
53 model_name VARCHAR(80) NOT NULL,
54 template_id VARCHAR(36) NULL,
55 model_prop_id VARCHAR(36) NULL,
56 model_blueprint_id VARCHAR(36) NULL,
57 event_id VARCHAR(36) NULL,
58 control_name_prefix VARCHAR(80) NULL,
59 control_name_uuid VARCHAR(36) NOT NULL,
60 service_type_id VARCHAR(80) NULL,
61 deployment_id VARCHAR(80) NULL,
62 deployment_status_url VARCHAR(300) NULL,
63 PRIMARY KEY (model_id),
65 UNIQUE (control_name_uuid),
66 UNIQUE (service_type_id),
67 UNIQUE (deployment_id)
68 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
70 CREATE TABLE model_properties (
71 model_prop_id VARCHAR(36) NOT NULL,
72 model_id VARCHAR(36) NOT NULL,
73 model_prop_text MEDIUMTEXT NULL,
75 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
76 PRIMARY KEY (model_prop_id)
77 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
79 CREATE TABLE model_blueprint (
80 model_blueprint_id VARCHAR(36) NOT NULL,
81 model_id VARCHAR(36) NOT NULL,
82 model_blueprint_text MEDIUMTEXT NULL,
84 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
85 PRIMARY KEY (model_blueprint_id)
86 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
88 CREATE TABLE model_instance (
89 model_instance_id VARCHAR(36) NOT NULL,
90 model_id VARCHAR(36) NOT NULL,
91 vm_name VARCHAR(250) NOT NULL,
92 location VARCHAR(250) NULL,
93 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
94 PRIMARY KEY (model_instance_id),
95 UNIQUE (model_id, vm_name)
96 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
99 event_id VARCHAR(36) NOT NULL,
100 model_id VARCHAR(36) NULL,
101 action_cd VARCHAR(80) NOT NULL,
102 action_state_cd VARCHAR(80) NULL,
103 prev_event_id VARCHAR(36) NULL,
104 process_instance_id VARCHAR(80) NULL,
105 user_id VARCHAR(80) NULL,
106 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
107 PRIMARY KEY (event_id)
108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
110 CREATE TABLE clds_service_cache (
111 invariant_service_id VARCHAR(36) NOT NULL,
112 service_id VARCHAR(36) NULL,
113 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
114 object_data MEDIUMBLOB NULL,
115 PRIMARY KEY (invariant_service_id)
116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
118 CREATE TABLE IF NOT EXISTS tosca_model (
119 tosca_model_id VARCHAR(36) NOT NULL,
120 tosca_model_name VARCHAR(80) NOT NULL,
121 policy_type VARCHAR(80) NULL,
123 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
124 PRIMARY KEY (tosca_model_id)
125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
127 CREATE TABLE IF NOT EXISTS tosca_model_revision (
128 tosca_model_revision_id VARCHAR(36) NOT NULL,
129 tosca_model_id VARCHAR(36) NOT NULL,
130 version DOUBLE NOT NULL DEFAULT 1,
131 tosca_model_yaml MEDIUMTEXT NULL,
132 tosca_model_json MEDIUMTEXT NULL,
134 createdTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
135 lastUpdatedTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
136 PRIMARY KEY (tosca_model_revision_id),
137 CONSTRAINT tosca_model_revision_ukey UNIQUE KEY (tosca_model_id, version),
138 CONSTRAINT tosca_model_revision_fkey01 FOREIGN KEY (tosca_model_id) REFERENCES tosca_model (tosca_model_id)
139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
141 CREATE TABLE IF NOT EXISTS dictionary (
142 dictionary_id VARCHAR(36) NOT NULL,
143 dictionary_name VARCHAR(80) NOT NULL,
144 created_by VARCHAR(80),
145 modified_by VARCHAR(80),
146 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
147 PRIMARY KEY (dictionary_id)
148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
150 CREATE TABLE IF NOT EXISTS dictionary_elements (
151 dict_element_id VARCHAR(36) NOT NULL,
152 dictionary_id VARCHAR(36) NOT NULL,
153 dict_element_name VARCHAR(250) NOT NULL,
154 dict_element_short_name VARCHAR(80) NOT NULL,
155 dict_element_description VARCHAR(250),
156 dict_element_type VARCHAR(80) NOT NULL,
157 created_by VARCHAR(80),
158 modified_by VARCHAR(80),
159 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
160 PRIMARY KEY (dict_element_id),
161 CONSTRAINT dictionary_elements_ukey UNIQUE KEY (dict_element_name, dict_element_short_name),
162 CONSTRAINT dictionary_elements_ukey_fkey01 FOREIGN KEY (dictionary_id) REFERENCES dictionary (dictionary_id)
163 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
166 ADD CONSTRAINT template_bpmn_id_fkey01
167 FOREIGN KEY (template_bpmn_id)
168 REFERENCES template_bpmn (template_bpmn_id);
171 ADD CONSTRAINT template_image_id_fkey01
172 FOREIGN KEY (template_image_id)
173 REFERENCES template_image (template_image_id);
176 ADD CONSTRAINT template_doc_id_fkey01
177 FOREIGN KEY (template_doc_id)
178 REFERENCES template_doc (template_doc_id);
180 ALTER TABLE template_bpmn
181 ADD CONSTRAINT template_id_fkey02
182 FOREIGN KEY (template_id)
183 REFERENCES template (template_id);
185 ALTER TABLE template_image
186 ADD CONSTRAINT template_id_fkey03
187 FOREIGN KEY (template_id)
188 REFERENCES template (template_id);
190 ALTER TABLE template_doc
191 ADD CONSTRAINT template_id_fkey04
192 FOREIGN KEY (template_id)
193 REFERENCES template (template_id);
196 ADD CONSTRAINT template_id_fkey01
197 FOREIGN KEY (template_id)
198 REFERENCES template (template_id);
201 ADD CONSTRAINT model_prop_id_fkey01
202 FOREIGN KEY (model_prop_id)
203 REFERENCES model_properties (model_prop_id);
206 ADD CONSTRAINT model_blueprint_id_fkey01
207 FOREIGN KEY (model_blueprint_id)
208 REFERENCES model_blueprint (model_blueprint_id);
211 ADD CONSTRAINT event_id_fkey01
212 FOREIGN KEY (event_id)
213 REFERENCES event (event_id);
215 ALTER TABLE model_properties
216 ADD CONSTRAINT model_id_fkey01
217 FOREIGN KEY (model_id)
218 REFERENCES model (model_id);
220 ALTER TABLE model_blueprint
221 ADD CONSTRAINT model_id_fkey02
222 FOREIGN KEY (model_id)
223 REFERENCES model (model_id);
225 ALTER TABLE model_instance
226 ADD CONSTRAINT model_id_fkey04
227 FOREIGN KEY (model_id)
228 REFERENCES model (model_id);
231 ADD CONSTRAINT model_id_fkey03
232 FOREIGN KEY (model_id)
233 REFERENCES model (model_id);