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 `camundabpm`;
23 CREATE USER 'camunda';
24 GRANT ALL on camundabpm.* to 'camunda' identified by 'ndMSpw4CAM' with GRANT OPTION;
27 CREATE DATABASE `cldsdb4`;
31 GRANT ALL on cldsdb4.* to 'clds' identified by 'sidnnd83K' with GRANT OPTION;
32 GRANT SELECT on mysql.proc TO 'clds';
36 CREATE TABLE template (
37 template_id VARCHAR(36) NOT NULL,
38 template_name VARCHAR(80) NOT NULL,
39 template_bpmn_id VARCHAR(36) NULL,
40 template_image_id VARCHAR(36) NULL,
41 template_doc_id VARCHAR(36) NULL,
42 PRIMARY KEY (template_id),
43 UNIQUE (template_name)
44 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
46 CREATE TABLE template_bpmn (
47 template_bpmn_id VARCHAR(36) NOT NULL,
48 template_id VARCHAR(36) NOT NULL,
49 template_bpmn_text MEDIUMTEXT NOT NULL,
51 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
52 PRIMARY KEY (template_bpmn_id)
53 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
55 CREATE TABLE template_image (
56 template_image_id VARCHAR(36) NOT NULL,
57 template_id VARCHAR(36) NOT NULL,
58 template_image_text MEDIUMTEXT NULL,
60 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
61 PRIMARY KEY (template_image_id)
62 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
64 CREATE TABLE template_doc (
65 template_doc_id VARCHAR(36) NOT NULL,
66 template_id VARCHAR(36) NOT NULL,
67 template_doc_text MEDIUMTEXT NULL,
69 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
70 PRIMARY KEY (template_doc_id)
71 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
74 model_id VARCHAR(36) NOT NULL,
75 model_name VARCHAR(80) NOT NULL,
76 template_id VARCHAR(36) NULL,
77 model_prop_id VARCHAR(36) NULL,
78 model_blueprint_id VARCHAR(36) NULL,
79 event_id VARCHAR(36) NULL,
80 control_name_prefix VARCHAR(80) NULL,
81 control_name_uuid VARCHAR(36) NOT NULL,
82 service_type_id VARCHAR(80) NULL,
83 deployment_id VARCHAR(80) NULL,
84 PRIMARY KEY (model_id),
86 UNIQUE (control_name_uuid),
87 UNIQUE (service_type_id),
88 UNIQUE (deployment_id)
89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
91 CREATE TABLE model_properties (
92 model_prop_id VARCHAR(36) NOT NULL,
93 model_id VARCHAR(36) NOT NULL,
94 model_prop_text MEDIUMTEXT NULL,
96 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
97 PRIMARY KEY (model_prop_id)
98 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
100 CREATE TABLE model_blueprint (
101 model_blueprint_id VARCHAR(36) NOT NULL,
102 model_id VARCHAR(36) NOT NULL,
103 model_blueprint_text MEDIUMTEXT NULL,
105 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
106 PRIMARY KEY (model_blueprint_id)
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
109 CREATE TABLE model_instance (
110 model_instance_id VARCHAR(36) NOT NULL,
111 model_id VARCHAR(36) NOT NULL,
112 vm_name VARCHAR(250) NOT NULL,
113 location VARCHAR(250) NULL,
114 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
115 PRIMARY KEY (model_instance_id),
116 UNIQUE (model_id, vm_name)
117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
120 event_id VARCHAR(36) NOT NULL,
121 model_id VARCHAR(36) NULL,
122 action_cd VARCHAR(80) NOT NULL,
123 action_state_cd VARCHAR(80) NULL,
124 prev_event_id VARCHAR(36) NULL,
125 process_instance_id VARCHAR(80) NULL,
126 user_id VARCHAR(80) NULL,
127 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
128 PRIMARY KEY (event_id)
129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
131 CREATE TABLE clds_service_cache (
132 invariant_service_id VARCHAR(36) NOT NULL,
133 service_id VARCHAR(36) NULL,
134 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
135 object_data MEDIUMBLOB NULL,
136 PRIMARY KEY (invariant_service_id)
137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
140 ADD CONSTRAINT template_bpmn_id_fkey01
141 FOREIGN KEY (template_bpmn_id)
142 REFERENCES template_bpmn (template_bpmn_id);
145 ADD CONSTRAINT template_image_id_fkey01
146 FOREIGN KEY (template_image_id)
147 REFERENCES template_image (template_image_id);
150 ADD CONSTRAINT template_doc_id_fkey01
151 FOREIGN KEY (template_doc_id)
152 REFERENCES template_doc (template_doc_id);
154 ALTER TABLE template_bpmn
155 ADD CONSTRAINT template_id_fkey02
156 FOREIGN KEY (template_id)
157 REFERENCES template (template_id);
159 ALTER TABLE template_image
160 ADD CONSTRAINT template_id_fkey03
161 FOREIGN KEY (template_id)
162 REFERENCES template (template_id);
164 ALTER TABLE template_doc
165 ADD CONSTRAINT template_id_fkey04
166 FOREIGN KEY (template_id)
167 REFERENCES template (template_id);
170 ADD CONSTRAINT template_id_fkey01
171 FOREIGN KEY (template_id)
172 REFERENCES template (template_id);
175 ADD CONSTRAINT model_prop_id_fkey01
176 FOREIGN KEY (model_prop_id)
177 REFERENCES model_properties (model_prop_id);
180 ADD CONSTRAINT model_blueprint_id_fkey01
181 FOREIGN KEY (model_blueprint_id)
182 REFERENCES model_blueprint (model_blueprint_id);
185 ADD CONSTRAINT event_id_fkey01
186 FOREIGN KEY (event_id)
187 REFERENCES event (event_id);
189 ALTER TABLE model_properties
190 ADD CONSTRAINT model_id_fkey01
191 FOREIGN KEY (model_id)
192 REFERENCES model (model_id);
194 ALTER TABLE model_blueprint
195 ADD CONSTRAINT model_id_fkey02
196 FOREIGN KEY (model_id)
197 REFERENCES model (model_id);
199 ALTER TABLE model_instance
200 ADD CONSTRAINT model_id_fkey04
201 FOREIGN KEY (model_id)
202 REFERENCES model (model_id);
205 ADD CONSTRAINT model_id_fkey03
206 FOREIGN KEY (model_id)
207 REFERENCES model (model_id);