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 IF NOT EXISTS tosca_model (
111 tosca_model_id VARCHAR(36) NOT NULL,
112 tosca_model_name VARCHAR(80) NOT NULL,
113 policy_type VARCHAR(80) NULL,
115 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
116 PRIMARY KEY (tosca_model_id)
117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
119 CREATE TABLE IF NOT EXISTS tosca_model_revision (
120 tosca_model_revision_id VARCHAR(36) NOT NULL,
121 tosca_model_id VARCHAR(36) NOT NULL,
122 version DOUBLE NOT NULL DEFAULT 1,
123 tosca_model_yaml MEDIUMTEXT NULL,
124 tosca_model_json MEDIUMTEXT NULL,
126 createdTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
127 lastUpdatedTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
128 PRIMARY KEY (tosca_model_revision_id),
129 CONSTRAINT tosca_model_revision_ukey UNIQUE KEY (tosca_model_id, version),
130 CONSTRAINT tosca_model_revision_fkey01 FOREIGN KEY (tosca_model_id) REFERENCES tosca_model (tosca_model_id)
131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
133 CREATE TABLE IF NOT EXISTS dictionary (
134 dictionary_id VARCHAR(36) NOT NULL,
135 dictionary_name VARCHAR(80) NOT NULL,
136 created_by VARCHAR(80),
137 modified_by VARCHAR(80),
138 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
139 PRIMARY KEY (dictionary_id)
140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
142 CREATE TABLE IF NOT EXISTS dictionary_elements (
143 dict_element_id VARCHAR(36) NOT NULL,
144 dictionary_id VARCHAR(36) NOT NULL,
145 dict_element_name VARCHAR(250) NOT NULL,
146 dict_element_short_name VARCHAR(80) NOT NULL,
147 dict_element_description VARCHAR(250),
148 dict_element_type VARCHAR(80) NOT NULL,
149 created_by VARCHAR(80),
150 modified_by VARCHAR(80),
151 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
152 PRIMARY KEY (dict_element_id),
153 CONSTRAINT dictionary_elements_ukey UNIQUE KEY (dict_element_name, dict_element_short_name),
154 CONSTRAINT dictionary_elements_ukey_fkey01 FOREIGN KEY (dictionary_id) REFERENCES dictionary (dictionary_id)
155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
158 ADD CONSTRAINT template_bpmn_id_fkey01
159 FOREIGN KEY (template_bpmn_id)
160 REFERENCES template_bpmn (template_bpmn_id);
163 ADD CONSTRAINT template_image_id_fkey01
164 FOREIGN KEY (template_image_id)
165 REFERENCES template_image (template_image_id);
168 ADD CONSTRAINT template_doc_id_fkey01
169 FOREIGN KEY (template_doc_id)
170 REFERENCES template_doc (template_doc_id);
172 ALTER TABLE template_bpmn
173 ADD CONSTRAINT template_id_fkey02
174 FOREIGN KEY (template_id)
175 REFERENCES template (template_id);
177 ALTER TABLE template_image
178 ADD CONSTRAINT template_id_fkey03
179 FOREIGN KEY (template_id)
180 REFERENCES template (template_id);
182 ALTER TABLE template_doc
183 ADD CONSTRAINT template_id_fkey04
184 FOREIGN KEY (template_id)
185 REFERENCES template (template_id);
188 ADD CONSTRAINT template_id_fkey01
189 FOREIGN KEY (template_id)
190 REFERENCES template (template_id);
193 ADD CONSTRAINT model_prop_id_fkey01
194 FOREIGN KEY (model_prop_id)
195 REFERENCES model_properties (model_prop_id);
198 ADD CONSTRAINT model_blueprint_id_fkey01
199 FOREIGN KEY (model_blueprint_id)
200 REFERENCES model_blueprint (model_blueprint_id);
203 ADD CONSTRAINT event_id_fkey01
204 FOREIGN KEY (event_id)
205 REFERENCES event (event_id);
207 ALTER TABLE model_properties
208 ADD CONSTRAINT model_id_fkey01
209 FOREIGN KEY (model_id)
210 REFERENCES model (model_id);
212 ALTER TABLE model_blueprint
213 ADD CONSTRAINT model_id_fkey02
214 FOREIGN KEY (model_id)
215 REFERENCES model (model_id);
217 ALTER TABLE model_instance
218 ADD CONSTRAINT model_id_fkey04
219 FOREIGN KEY (model_id)
220 REFERENCES model (model_id);
223 ADD CONSTRAINT model_id_fkey03
224 FOREIGN KEY (model_id)
225 REFERENCES model (model_id);