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 PRIMARY KEY (model_id),
64 UNIQUE (control_name_uuid),
65 UNIQUE (service_type_id),
66 UNIQUE (deployment_id)
67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
69 CREATE TABLE model_properties (
70 model_prop_id VARCHAR(36) NOT NULL,
71 model_id VARCHAR(36) NOT NULL,
72 model_prop_text MEDIUMTEXT NULL,
74 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
75 PRIMARY KEY (model_prop_id)
76 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
78 CREATE TABLE model_blueprint (
79 model_blueprint_id VARCHAR(36) NOT NULL,
80 model_id VARCHAR(36) NOT NULL,
81 model_blueprint_text MEDIUMTEXT NULL,
83 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
84 PRIMARY KEY (model_blueprint_id)
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
87 CREATE TABLE model_instance (
88 model_instance_id VARCHAR(36) NOT NULL,
89 model_id VARCHAR(36) NOT NULL,
90 vm_name VARCHAR(250) NOT NULL,
91 location VARCHAR(250) NULL,
92 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
93 PRIMARY KEY (model_instance_id),
94 UNIQUE (model_id, vm_name)
95 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
98 event_id VARCHAR(36) NOT NULL,
99 model_id VARCHAR(36) NULL,
100 action_cd VARCHAR(80) NOT NULL,
101 action_state_cd VARCHAR(80) NULL,
102 prev_event_id VARCHAR(36) NULL,
103 process_instance_id VARCHAR(80) NULL,
104 user_id VARCHAR(80) NULL,
105 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
106 PRIMARY KEY (event_id)
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
109 CREATE TABLE clds_service_cache (
110 invariant_service_id VARCHAR(36) NOT NULL,
111 service_id VARCHAR(36) NULL,
112 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
113 object_data MEDIUMBLOB NULL,
114 PRIMARY KEY (invariant_service_id)
115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
118 ADD CONSTRAINT template_bpmn_id_fkey01
119 FOREIGN KEY (template_bpmn_id)
120 REFERENCES template_bpmn (template_bpmn_id);
123 ADD CONSTRAINT template_image_id_fkey01
124 FOREIGN KEY (template_image_id)
125 REFERENCES template_image (template_image_id);
128 ADD CONSTRAINT template_doc_id_fkey01
129 FOREIGN KEY (template_doc_id)
130 REFERENCES template_doc (template_doc_id);
132 ALTER TABLE template_bpmn
133 ADD CONSTRAINT template_id_fkey02
134 FOREIGN KEY (template_id)
135 REFERENCES template (template_id);
137 ALTER TABLE template_image
138 ADD CONSTRAINT template_id_fkey03
139 FOREIGN KEY (template_id)
140 REFERENCES template (template_id);
142 ALTER TABLE template_doc
143 ADD CONSTRAINT template_id_fkey04
144 FOREIGN KEY (template_id)
145 REFERENCES template (template_id);
148 ADD CONSTRAINT template_id_fkey01
149 FOREIGN KEY (template_id)
150 REFERENCES template (template_id);
153 ADD CONSTRAINT model_prop_id_fkey01
154 FOREIGN KEY (model_prop_id)
155 REFERENCES model_properties (model_prop_id);
158 ADD CONSTRAINT model_blueprint_id_fkey01
159 FOREIGN KEY (model_blueprint_id)
160 REFERENCES model_blueprint (model_blueprint_id);
163 ADD CONSTRAINT event_id_fkey01
164 FOREIGN KEY (event_id)
165 REFERENCES event (event_id);
167 ALTER TABLE model_properties
168 ADD CONSTRAINT model_id_fkey01
169 FOREIGN KEY (model_id)
170 REFERENCES model (model_id);
172 ALTER TABLE model_blueprint
173 ADD CONSTRAINT model_id_fkey02
174 FOREIGN KEY (model_id)
175 REFERENCES model (model_id);
177 ALTER TABLE model_instance
178 ADD CONSTRAINT model_id_fkey04
179 FOREIGN KEY (model_id)
180 REFERENCES model (model_id);
183 ADD CONSTRAINT model_id_fkey03
184 FOREIGN KEY (model_id)
185 REFERENCES model (model_id);