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 PRIMARY KEY (model_id),
79 UNIQUE (control_name_uuid),
80 UNIQUE (service_type_id),
81 UNIQUE (deployment_id)
82 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
84 CREATE TABLE model_properties (
85 model_prop_id VARCHAR(36) NOT NULL,
86 model_id VARCHAR(36) NOT NULL,
87 model_prop_text MEDIUMTEXT NULL,
89 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
90 PRIMARY KEY (model_prop_id)
91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
93 CREATE TABLE model_blueprint (
94 model_blueprint_id VARCHAR(36) NOT NULL,
95 model_id VARCHAR(36) NOT NULL,
96 model_blueprint_text MEDIUMTEXT NULL,
98 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
99 PRIMARY KEY (model_blueprint_id)
100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
102 CREATE TABLE model_instance (
103 model_instance_id VARCHAR(36) NOT NULL,
104 model_id VARCHAR(36) NOT NULL,
105 vm_name VARCHAR(250) NOT NULL,
106 location VARCHAR(250) NULL,
107 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
108 PRIMARY KEY (model_instance_id),
109 UNIQUE (model_id, vm_name)
110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
113 event_id VARCHAR(36) NOT NULL,
114 model_id VARCHAR(36) NULL,
115 action_cd VARCHAR(80) NOT NULL,
116 action_state_cd VARCHAR(80) NULL,
117 prev_event_id VARCHAR(36) NULL,
118 process_instance_id VARCHAR(80) NULL,
119 user_id VARCHAR(80) NULL,
120 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
121 PRIMARY KEY (event_id)
122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
124 CREATE TABLE clds_service_cache (
125 invariant_service_id VARCHAR(36) NOT NULL,
126 service_id VARCHAR(36) NULL,
127 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
128 object_data MEDIUMBLOB NULL,
129 PRIMARY KEY (invariant_service_id)
130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
133 ADD CONSTRAINT template_bpmn_id_fkey01
134 FOREIGN KEY (template_bpmn_id)
135 REFERENCES template_bpmn (template_bpmn_id);
138 ADD CONSTRAINT template_image_id_fkey01
139 FOREIGN KEY (template_image_id)
140 REFERENCES template_image (template_image_id);
143 ADD CONSTRAINT template_doc_id_fkey01
144 FOREIGN KEY (template_doc_id)
145 REFERENCES template_doc (template_doc_id);
147 ALTER TABLE template_bpmn
148 ADD CONSTRAINT template_id_fkey02
149 FOREIGN KEY (template_id)
150 REFERENCES template (template_id);
152 ALTER TABLE template_image
153 ADD CONSTRAINT template_id_fkey03
154 FOREIGN KEY (template_id)
155 REFERENCES template (template_id);
157 ALTER TABLE template_doc
158 ADD CONSTRAINT template_id_fkey04
159 FOREIGN KEY (template_id)
160 REFERENCES template (template_id);
163 ADD CONSTRAINT template_id_fkey01
164 FOREIGN KEY (template_id)
165 REFERENCES template (template_id);
168 ADD CONSTRAINT model_prop_id_fkey01
169 FOREIGN KEY (model_prop_id)
170 REFERENCES model_properties (model_prop_id);
173 ADD CONSTRAINT model_blueprint_id_fkey01
174 FOREIGN KEY (model_blueprint_id)
175 REFERENCES model_blueprint (model_blueprint_id);
178 ADD CONSTRAINT event_id_fkey01
179 FOREIGN KEY (event_id)
180 REFERENCES event (event_id);
182 ALTER TABLE model_properties
183 ADD CONSTRAINT model_id_fkey01
184 FOREIGN KEY (model_id)
185 REFERENCES model (model_id);
187 ALTER TABLE model_blueprint
188 ADD CONSTRAINT model_id_fkey02
189 FOREIGN KEY (model_id)
190 REFERENCES model (model_id);
192 ALTER TABLE model_instance
193 ADD CONSTRAINT model_id_fkey04
194 FOREIGN KEY (model_id)
195 REFERENCES model (model_id);
198 ADD CONSTRAINT model_id_fkey03
199 FOREIGN KEY (model_id)
200 REFERENCES model (model_id);