aboutsummaryrefslogtreecommitdiffstats
path: root/csit/config/clamp/policy-clamp-create-tables.sql
blob: 5d655ad507d7da67842beb41496fa60c8f680976 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
/*
 * ============LICENSE_START=======================================================
 *  Copyright (C) 2022 Nordix Foundation
 *  ================================================================================
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *        http://www.apache.org/licenses/LICENSE-2.0
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 *
 *  SPDX-License-Identifier: Apache-2.0
 *  ============LICENSE_END=========================================================
 */

create table if not exists dictionary (
    name varchar(255) not null,
    created_by varchar(255),
    created_timestamp datetime(6) not null,
    updated_by varchar(255),
    updated_timestamp datetime(6) not null,
    dictionary_second_level integer,
    dictionary_type varchar(255),
    primary key (name)
    ) engine=InnoDB;

create table if not exists dictionary_elements (
    short_name varchar(255) not null,
    created_by varchar(255),
    created_timestamp datetime(6) not null,
    updated_by varchar(255),
    updated_timestamp datetime(6) not null,
    description varchar(255) not null,
    name varchar(255) not null,
    subdictionary_name varchar(255),
    type varchar(255) not null,
    primary key (short_name)
    ) engine=InnoDB;

create table if not exists dictionary_to_dictionaryelements (
    dictionary_name varchar(255) not null,
    dictionary_element_short_name varchar(255) not null,
    primary key (dictionary_name, dictionary_element_short_name)
    ) engine=InnoDB;

create table if not exists hibernate_sequence (
    next_val bigint
) engine=InnoDB;

insert into hibernate_sequence values ( 1 );

create table if not exists loop_element_models (
    name varchar(255) not null,
    created_by varchar(255),
    created_timestamp datetime(6) not null,
    updated_by varchar(255),
    updated_timestamp datetime(6) not null,
    blueprint_yaml MEDIUMTEXT,
    dcae_blueprint_id varchar(255),
    loop_element_type varchar(255) not null,
    short_name varchar(255),
    primary key (name)
    ) engine=InnoDB;

create table if not exists loop_logs (
                                         id bigint not null,
                                         log_component varchar(255) not null,
    log_instant datetime(6) not null,
    log_type varchar(255) not null,
    message MEDIUMTEXT not null,
    loop_id varchar(255) not null,
    primary key (id)
    ) engine=InnoDB;

create table if not exists loop_templates (
    name varchar(255) not null,
    created_by varchar(255),
    created_timestamp datetime(6) not null,
    updated_by varchar(255),
    updated_timestamp datetime(6) not null,
    allowed_loop_type varchar(255),
    blueprint_yaml MEDIUMTEXT,
    dcae_blueprint_id varchar(255),
    maximum_instances_allowed integer,
    svg_representation MEDIUMTEXT,
    unique_blueprint boolean default false,
    service_uuid varchar(255),
    primary key (name)
    ) engine=InnoDB;

create table if not exists loopelementmodels_to_policymodels (
    loop_element_name varchar(255) not null,
    policy_model_type varchar(255) not null,
    policy_model_version varchar(255) not null,
    primary key (loop_element_name, policy_model_type, policy_model_version)
    ) engine=InnoDB;

create table if not exists loops (
    name varchar(255) not null,
    created_by varchar(255),
    created_timestamp datetime(6) not null,
    updated_by varchar(255),
    updated_timestamp datetime(6) not null,
    dcae_deployment_id varchar(255),
    dcae_deployment_status_url varchar(255),
    global_properties_json json,
    last_computed_state varchar(255) not null,
    svg_representation MEDIUMTEXT,
    loop_template_name varchar(255) not null,
    service_uuid varchar(255),
    primary key (name)
    ) engine=InnoDB;

create table if not exists loops_to_microservicepolicies (
    loop_name varchar(255) not null,
    microservicepolicy_name varchar(255) not null,
    primary key (loop_name, microservicepolicy_name)
    ) engine=InnoDB;

create table if not exists looptemplates_to_loopelementmodels (
    loop_element_model_name varchar(255) not null,
    loop_template_name varchar(255) not null,
    flow_order integer not null,
    primary key (loop_element_model_name, loop_template_name)
    ) engine=InnoDB;

create table if not exists micro_service_policies (
    name varchar(255) not null,
    created_by varchar(255),
    created_timestamp datetime(6) not null,
    updated_by varchar(255),
    updated_timestamp datetime(6) not null,
    configurations_json json,
    json_representation json not null,
    pdp_group varchar(255),
    pdp_sub_group varchar(255),
    context varchar(255),
    dcae_blueprint_id varchar(255),
    dcae_deployment_id varchar(255),
    dcae_deployment_status_url varchar(255),
    device_type_scope varchar(255),
    shared bit not null,
    loop_element_model_id varchar(255),
    policy_model_type varchar(255),
    policy_model_version varchar(255),
    primary key (name)
    ) engine=InnoDB;

create table if not exists operational_policies (
    name varchar(255) not null,
    created_by varchar(255),
    created_timestamp datetime(6) not null,
    updated_by varchar(255),
    updated_timestamp datetime(6) not null,
    configurations_json json,
    json_representation json not null,
    pdp_group varchar(255),
    pdp_sub_group varchar(255),
    loop_element_model_id varchar(255),
    policy_model_type varchar(255),
    policy_model_version varchar(255),
    loop_id varchar(255) not null,
    primary key (name)
    ) engine=InnoDB;

create table if not exists policy_models (
    policy_model_type varchar(255) not null,
    version varchar(255) not null,
    created_by varchar(255),
    created_timestamp datetime(6) not null,
    updated_by varchar(255),
    updated_timestamp datetime(6) not null,
    policy_acronym varchar(255),
    policy_tosca MEDIUMTEXT,
    policy_pdp_group json,
    primary key (policy_model_type, version)
    ) engine=InnoDB;

create table if not exists services (
    service_uuid varchar(255) not null,
    name varchar(255) not null,
    resource_details json,
    service_details json,
    version varchar(255),
    primary key (service_uuid)
    ) engine=InnoDB;

alter table dictionary_to_dictionaryelements
    add constraint FK68hjjinnm8nte2owstd0xwp23
        foreign key (dictionary_element_short_name)
            references dictionary_elements (short_name);

alter table dictionary_to_dictionaryelements
    add constraint FKtqfxg46gsxwlm2gkl6ne3cxfe
        foreign key (dictionary_name)
            references dictionary (name);

alter table loop_logs
    add constraint FK1j0cda46aickcaoxqoo34khg2
        foreign key (loop_id)
            references loops (name);

alter table loop_templates
    add constraint FKn692dk6281wvp1o95074uacn6
        foreign key (service_uuid)
            references services (service_uuid);

alter table loopelementmodels_to_policymodels
    add constraint FK23j2q74v6kaexefy0tdabsnda
        foreign key (policy_model_type, policy_model_version)
            references policy_models (policy_model_type, version);

alter table loopelementmodels_to_policymodels
    add constraint FKjag1iu0olojfwryfkvb5o0rk5
        foreign key (loop_element_name)
            references loop_element_models (name);

alter table loops
    add constraint FK844uwy82wt0l66jljkjqembpj
        foreign key (loop_template_name)
            references loop_templates (name);

alter table loops
    add constraint FK4b9wnqopxogwek014i1shqw7w
        foreign key (service_uuid)
            references services (service_uuid);

alter table loops_to_microservicepolicies
    add constraint FKle255jmi7b065fwbvmwbiehtb
        foreign key (microservicepolicy_name)
            references micro_service_policies (name);

alter table loops_to_microservicepolicies
    add constraint FK8avfqaf7xl71l7sn7a5eri68d
        foreign key (loop_name)
            references loops (name);

alter table looptemplates_to_loopelementmodels
    add constraint FK1k7nbrbugvqa0xfxkq3cj1yn9
        foreign key (loop_element_model_name)
            references loop_element_models (name);

alter table looptemplates_to_loopelementmodels
    add constraint FKj29yxyw0x7ue6mwgi6d3qg748
        foreign key (loop_template_name)
            references loop_templates (name);

alter table micro_service_policies
    add constraint FKqvvdypacbww07fuv8xvlvdjgl
        foreign key (loop_element_model_id)
            references loop_element_models (name);

alter table micro_service_policies
    add constraint FKn17j9ufmyhqicb6cvr1dbjvkt
        foreign key (policy_model_type, policy_model_version)
            references policy_models (policy_model_type, version);

alter table operational_policies
    add constraint FKi9kh7my40737xeuaye9xwbnko
        foreign key (loop_element_model_id)
            references loop_element_models (name);

alter table operational_policies
    add constraint FKlsyhfkoqvkwj78ofepxhoctip
        foreign key (policy_model_type, policy_model_version)
            references policy_models (policy_model_type, version);

alter table operational_policies
    add constraint FK1ddoggk9ni2bnqighv6ecmuwu
        foreign key (loop_id)
            references loops (name);