2 -- ============LICENSE_START=======================================================
4 -- ================================================================================
5 -- Copyright (C) 2017 ONAP Intellectual Property. All rights
7 -- ================================================================================
8 -- Licensed under the Apache License, Version 2.0 (the "License");
9 -- you may not use this file except in compliance with the License.
10 -- You may obtain a copy of the License at
12 -- http://www.apache.org/licenses/LICENSE-2.0
14 -- Unless required by applicable law or agreed to in writing, software
15 -- distributed under the License is distributed on an "AS IS" BASIS,
16 -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 -- See the License for the specific language governing permissions and
18 -- limitations under the License.
19 -- ============LICENSE_END=========================================================
22 CREATE TABLE RESOURCE_LOCK (
23 resource_lock_id SERIAL PRIMARY KEY,
24 resource_name VARCHAR(50) NOT NULL UNIQUE,
25 lock_holder VARCHAR(100) NOT NULL,
26 lock_count SMALLINT NOT NULL,
27 lock_time DATETIME NOT NULL,
28 expiration_time DATETIME NOT NULL
31 CREATE TABLE RESOURCE (
32 resource_id SERIAL PRIMARY KEY,
33 asset_id VARCHAR(50) NOT NULL,
34 resource_name VARCHAR(50) NOT NULL,
35 resource_type VARCHAR(10) NOT NULL,
38 ll_reference_count SMALLINT,
42 ALTER TABLE RESOURCE ADD CONSTRAINT c1_resource CHECK (resource_type IN ('Limit', 'Label', 'Range'));
43 CREATE UNIQUE INDEX ak1_resource ON RESOURCE (asset_id, resource_name);
45 CREATE TABLE RESOURCE_LOAD (
46 resource_load_id SERIAL PRIMARY KEY,
47 resource_id BIGINT NOT NULL REFERENCES resource (resource_id),
48 application_id VARCHAR(20) NOT NULL,
49 resource_load_time DATETIME NOT NULL,
50 resource_expiration_time DATETIME
53 CREATE INDEX i1_resource_load ON RESOURCE_LOAD (resource_id);
54 CREATE UNIQUE INDEX ak1_resource_load ON RESOURCE_LOAD (resource_id, application_id);
56 CREATE TABLE ALLOCATION_ITEM (
57 allocation_item_id SERIAL PRIMARY KEY,
58 resource_id BIGINT NOT NULL REFERENCES resource (resource_id),
59 application_id VARCHAR(50) NOT NULL,
60 resource_set_id VARCHAR(50) NOT NULL,
61 resource_union_id VARCHAR(50) NOT NULL,
62 resource_share_group_list VARCHAR(200),
66 allocation_time DATETIME NOT NULL
69 CREATE INDEX i1_allocation_item ON allocation_item (resource_id);
70 CREATE UNIQUE INDEX ak1_allocation_item ON allocation_item (resource_id, resource_set_id);
72 CREATE TABLE RESOURCE_RULE (
73 resource_rule_id SERIAL PRIMARY KEY,
74 resource_name VARCHAR(50) NOT NULL,
75 service_model VARCHAR(50) NOT NULL,
76 end_point_position VARCHAR(50) NOT NULL,
77 service_expression VARCHAR(2000) NOT NULL,
78 equipment_level VARCHAR(50) NOT NULL,
79 equipment_expression VARCHAR(2000) NOT NULL,
80 allocation_expression VARCHAR(2000) NOT NULL,
81 soft_limit_expression VARCHAR(2000) NOT NULL,
82 hard_limit_expression VARCHAR(2000) NOT NULL
85 CREATE TABLE RESOURCE_THRESHOLD (
86 resource_threshold_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
87 resource_rule_id bigint(20) NOT NULL,
88 threshold_expression varchar(2000) NOT NULL,
89 threshold_message varchar(2000) NOT NULL,
90 PRIMARY KEY (resource_threshold_id)
93 CREATE TABLE RANGE_RULE (
94 range_rule_id SERIAL PRIMARY KEY,
95 range_name VARCHAR(50) NOT NULL,
96 service_model VARCHAR(50) NOT NULL,
97 end_point_position VARCHAR(50) NOT NULL,
98 equipment_level VARCHAR(50) NOT NULL,
99 min_value INT NOT NULL,
100 max_value INT NOT NULL
103 CREATE TABLE MAX_PORT_SPEED (
104 max_port_speed_id SERIAL PRIMARY KEY,
105 image_file_name VARCHAR(50) NOT NULL,
106 end_point_position VARCHAR(50) NOT NULL,
107 interface_name VARCHAR(100) NOT NULL,
108 max_speed BIGINT NOT NULL,
109 unit VARCHAR(10) NOT NULL
112 CREATE TABLE MAX_SERVER_SPEED (
113 max_server_speed_id SERIAL PRIMARY KEY,
114 server_model VARCHAR(50) NOT NULL,
115 evc_count SMALLINT NOT NULL,
116 max_speed BIGINT NOT NULL,
117 unit VARCHAR(10) NOT NULL,
118 description VARCHAR(500)
121 CREATE TABLE SERVICE_RESOURCE (
122 service_resource_id SERIAL PRIMARY KEY,
123 service_instance_id VARCHAR(80) NOT NULL,
124 service_status VARCHAR(10) NOT NULL,
125 service_change_number SMALLINT NOT NULL,
126 resource_set_id VARCHAR(100) NOT NULL,
127 resource_union_id VARCHAR(100) NOT NULL,
130 ALTER TABLE SERVICE_RESOURCE ADD CONSTRAINT C1_SERVICE_RESOURCE CHECK (service_status IN ('Active', 'Pending'));
131 CREATE INDEX i1_service_resource ON SERVICE_RESOURCE (service_instance_id);
132 CREATE UNIQUE INDEX ak1_service_resource ON SERVICE_RESOURCE (service_instance_id, service_change_number);
134 CREATE TABLE VPE_POOL (
135 vpe_name VARCHAR(20) NOT NULL,
136 ipv4_oam_address VARCHAR(20) NOT NULL,
137 loopback0_ipv4_address VARCHAR(20) NOT NULL,
138 provisioning_status VARCHAR(10) NOT NULL,
139 aic_site_id VARCHAR(100) NOT NULL,
140 availability_zone VARCHAR(100) NOT NULL,
141 vlan_id_outer VARCHAR(20) NOT NULL,
142 vendor VARCHAR(20) NOT NULL,
143 physical_intf_name VARCHAR(40) NOT NULL,
144 physical_intf_speed VARCHAR(20) NOT NULL,
145 physical_intf_units VARCHAR(20) NOT NULL,
146 vpe_uuid VARCHAR(80) DEFAULT NULL,
147 vpe_id VARCHAR(80) DEFAULT NULL,
148 image_filename VARCHAR(100) DEFAULT NULL,
149 PRIMARY KEY (aic_site_id, vpe_name, availability_zone)
152 CREATE TABLE VPLSPE_POOL (
153 vplspe_name varchar(20) NOT NULL,
154 aic_site_id varchar(100) NOT NULL,
155 availability_zone varchar(100) NOT NULL,
156 physical_intf_name varchar(40) NOT NULL,
157 physical_intf_speed varchar(20) NOT NULL,
158 physical_intf_units varchar(20) NOT NULL,
159 loopback0_ipv4_address varchar(20) NOT NULL,
160 vlan_id_outer varchar(20) NOT NULL,
161 vplspe_uuid varchar(80) DEFAULT NULL,
162 image_filename varchar(100) DEFAULT NULL,
163 provisioning_status varchar(10) DEFAULT NULL,
164 vendor varchar(20) DEFAULT NULL,
165 PRIMARY KEY (vplspe_name, aic_site_id, availability_zone, physical_intf_name)
168 CREATE TABLE VPE_LOCK (
169 vpe_name varchar(20) NOT NULL,
170 vpn_lock varchar(20) NOT NULL,
171 PRIMARY KEY (vpe_name)
174 CREATE TABLE PARAMETERS (
175 name varchar(100) PRIMARY KEY,
176 value varchar(24) NOT NULL,
177 category varchar(24) NOT NULL,
181 CREATE TABLE PSERVER (
182 hostname varchar(255) NOT NULL,
183 ptnii_equip_name varchar(255),
184 number_of_cpus varchar(255),
185 disk_in_gigabytes varchar(255),
186 ram_in_megabytes varchar(255),
187 equip_type varchar(255),
188 equip_vendor varchar(255),
189 equip_model varchar(255),
191 pserver_selflink varchar(255),
192 ipv4_oam_address varchar(15),
193 serial_number varchar(255),
194 pserver_id varchar(255),
195 internet_topology varchar(40),
196 aic_site_id varchar(100),
198 pserver_name2 varchar(255),
199 purpose varchar(255),
200 PRIMARY KEY (hostname)