4 # Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
5 # This file is part of openvim
8 # Licensed under the Apache License, Version 2.0 (the "License"); you may
9 # not use this file except in compliance with the License. You may obtain
10 # 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, WITHOUT
16 # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
17 # License for the specific language governing permissions and limitations
20 # For those usages not covered by the Apache License, Version 2.0 please
21 # contact with: nfvlabs@tid.es
25 #Upgrade/Downgrade openvim database preserving the content
41 echo -e "Usage: $0 OPTIONS [{openvim_version}]"
42 echo -e " Upgrades/Downgrades openvim database preserving the content"
43 echo -e " if openvim_version is not provided it tries to get from openvimd.py using relative path"
45 echo -e " -u USER database user. '$DBUSER' by default. Prompts if DB access fails"
46 echo -e " -p PASS database password. 'No password' by default. Prompts if DB access fails"
47 echo -e " -P PORT database port. '$DBPORT' by default"
48 echo -e " -h HOST database host. '$DBHOST' by default"
49 echo -e " -d NAME database name. '$DBNAME' by default. Prompts if DB access fails"
50 echo -e " --help shows this help"
53 while getopts ":u:p:P:h:d:-:" o
; do
71 [ "${OPTARG}" == "help" ] && usage
&& exit 0
72 echo "Invalid option: --$OPTARG" >&2 && usage
>&2
76 echo "Invalid option: -$OPTARG" >&2 && usage
>&2
80 echo "Option -$OPTARG requires an argument." >&2 && usage
>&2
94 if [ -z "$OPENVIM_VER" ]
96 OPENVIM_VER
=`${DIRNAME}/../openvimd.py -v`
97 OPENVIM_VER
=${OPENVIM_VER%%-r*}
98 OPENVIM_VER
=${OPENVIM_VER##*version }
99 echo " Detected openvim version $OPENVIM_VER"
101 VERSION_1
=`echo $OPENVIM_VER | cut -f 1 -d"."`
102 VERSION_2
=`echo $OPENVIM_VER | cut -f 2 -d"."`
103 VERSION_3
=`echo $OPENVIM_VER | cut -f 3 -d"."`
104 if ! [ "$VERSION_1" -ge 0 -a "$VERSION_2" -ge 0 -a "$VERSION_3" -ge 0 ] 2>/dev
/null
106 [ -n "$1" ] && echo "Invalid openvim version '$1', expected 'X.X.X'" >&2
107 [ -z "$1" ] && echo "Can not get openvim version" >&2
110 OPENVIM_VER_NUM
=`printf "%d%03d%03d" ${VERSION_1} ${VERSION_2} ${VERSION_3}`
112 #check and ask for database user password
114 [ -n "$DBPASS" ] && DBPASS_
="-p$DBPASS"
117 while ! echo ";" | mysql
$DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME >/dev
/null
2>&1
119 [ -n "$logintry" ] && echo -e "\nInvalid database credentials!!!. Try again (Ctrl+c to abort)"
120 [ -z "$logintry" ] && echo -e "\nProvide database name and credentials"
121 read -e -p "mysql database name($DBNAME): " KK
122 [ -n "$KK" ] && DBNAME
="$KK"
123 read -e -p "mysql user($DBUSER): " KK
124 [ -n "$KK" ] && DBUSER
="$KK" && DBUSER_
="-u$DBUSER"
125 read -e -s -p "mysql password: " DBPASS
126 [ -n "$DBPASS" ] && DBPASS_
="-p$DBPASS"
127 [ -z "$DBPASS" ] && DBPASS_
=""
132 DBCMD
="mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME"
135 #GET DATABASE VERSION
136 #check that the database seems a openvim database
137 if ! echo -e "show create table instances;\nshow create table numas" |
$DBCMD >/dev
/null
2>&1
139 echo " database $DBNAME does not seem to be an openvim database" >&2
143 if ! echo 'show create table schema_version;' |
$DBCMD >/dev
/null
2>&1
148 DATABASE_VER_NUM
=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
149 DATABASE_VER
=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
150 [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] && echo " Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
151 #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
155 #GET DATABASE TARGET VERSION
156 DATABASE_TARGET_VER_NUM
=0
157 [ $OPENVIM_VER_NUM -gt 1091 ] && DATABASE_TARGET_VER_NUM
=1 #>0.1.91 => 1
158 [ $OPENVIM_VER_NUM -ge 2003 ] && DATABASE_TARGET_VER_NUM
=2 #0.2.03 => 2
159 [ $OPENVIM_VER_NUM -ge 2005 ] && DATABASE_TARGET_VER_NUM
=3 #0.2.5 => 3
160 [ $OPENVIM_VER_NUM -ge 3001 ] && DATABASE_TARGET_VER_NUM
=4 #0.3.1 => 4
161 [ $OPENVIM_VER_NUM -ge 4001 ] && DATABASE_TARGET_VER_NUM
=5 #0.4.1 => 5
162 [ $OPENVIM_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM
=6 #0.4.2 => 6
163 [ $OPENVIM_VER_NUM -ge 4005 ] && DATABASE_TARGET_VER_NUM
=7 #0.4.5 => 7
164 [ $OPENVIM_VER_NUM -ge 4010 ] && DATABASE_TARGET_VER_NUM
=8 #0.4.10 => 8
165 #TODO ... put next versions here
168 function upgrade_to_1
(){
169 echo " upgrade database from version 0.0 to version 0.1"
170 echo " CREATE TABLE \`schema_version\`"
171 echo "CREATE TABLE \`schema_version\` (
172 \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
173 \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
174 \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
175 \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
177 PRIMARY KEY (\`version_int\`)
179 COMMENT='database schema control version'
180 COLLATE='utf8_general_ci'
181 ENGINE=InnoDB;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
182 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
183 VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" |
$DBCMD
184 echo " ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
185 echo "ALTER TABLE \`nets\`
186 ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
188 function downgrade_from_1
(){
189 echo " downgrade database from version 0.1 to version 0.0"
190 echo " ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
191 echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
192 echo " DROP TABLE \`schema_version\`"
193 echo "DROP TABLE \`schema_version\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
195 function upgrade_to_2
(){
196 echo " upgrade database from version 0.1 to version 0.2"
197 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
198 for table
in of_ports_pci_correspondence resources_port ports
200 echo "ALTER TABLE \`${table}\`
201 ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
202 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
203 [ $table == of_ports_pci_correspondence
] ||
204 echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
206 echo " UPDATE procedure UpdateSwitchPort"
207 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
209 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
210 COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
212 #DELETES switch_port entry before writing, because if not it fails for key constrains
214 RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
215 INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
216 INNER JOIN numas on RP.numa_id=numas.id
217 INNER JOIN hosts on numas.host_id=hosts.uuid
218 INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
219 SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
220 #write switch_port into resources_port and ports
222 RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
223 INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
224 INNER JOIN numas on RP.numa_id=numas.id
225 INNER JOIN hosts on numas.host_id=hosts.uuid
226 INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
227 SET ports.switch_port=PC.switch_port, ports.switch_dpid=PC.switch_dpid, RP.switch_port=PC.switch_port, RP.switch_dpid=PC.switch_dpid;
229 delimiter ;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
230 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
231 VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
233 function upgrade_to_3
(){
234 echo " upgrade database from version 0.2 to version 0.3"
235 echo " change size of source_name at table resources_port"
236 echo "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL AFTER port_id;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
237 echo " CREATE PROCEDURE GetAllAvailablePorts"
239 CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
240 COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
242 SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
245 SELECT id as port_id, Mbps, pci, switch_port, mac
247 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
251 SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
253 WHERE numa_id = Numa AND status = 'ok'
256 ON A.port_id = B.root_id
259 SELECT root_id, COUNT(id) as usedSRIOV
261 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
264 ON A.port_id = C.root_id
265 ORDER BY Mbps_free, availableSRIOV, pci;
267 delimiter ;"|
$DBCMD ||
! ! echo "ERROR. Aborted!" ||
exit -1
268 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (3, '0.3', '0.2.5', 'New Procedure GetAllAvailablePorts', '2015-07-09');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
271 function upgrade_to_4
(){
272 echo " upgrade database from version 0.3 to version 0.4"
273 echo " remove unique VLAN index at 'resources_port', 'ports'"
274 echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
275 echo "ALTER TABLE ports DROP INDEX vlan_switch_port;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
276 echo " change table 'ports'"
277 echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL COMMENT 'driver model for bridge ifaces; PF,VF,VFnotShared for data ifaces' AFTER mac;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
278 echo "ALTER TABLE ports DROP COLUMN vlan_changed;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
279 echo "ALTER TABLE resources_port DROP COLUMN vlan;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
280 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (4, '0.4', '0.3.1', 'Remove unique index VLAN at resources_port', '2015-09-04');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
283 function upgrade_to_X
(){
284 #TODO, this change of foreign key does not work
285 echo " upgrade database from version 0.X to version 0.X"
286 echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
287 DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
288 echo "ALTER TABLE instances
289 ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
290 ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
293 function downgrade_from_2
(){
294 echo " downgrade database from version 0.2 to version 0.1"
295 echo " UPDATE procedure UpdateSwitchPort"
296 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
298 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
301 resources_port INNER JOIN (
302 SELECT resources_port.id,KK.switch_port
303 FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
304 INNER JOIN hosts on numas.host_id=hosts.uuid
305 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
307 ON resources_port.root_id=TABLA.id
308 SET resources_port.switch_port=TABLA.switch_port
309 WHERE resources_port.root_id=TABLA.id;
311 delimiter ;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
312 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
313 for table
in of_ports_pci_correspondence resources_port ports
315 [ $table == of_ports_pci_correspondence
] ||
316 echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
317 echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
319 [ $table == of_ports_pci_correspondence
] && switch_port_size
=50
320 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
322 echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
324 function downgrade_from_3
(){
325 echo " downgrade database from version 0.3 to version 0.2"
326 echo " change back size of source_name at table resources_port"
327 echo "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(20) NULL DEFAULT NULL AFTER port_id;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
328 echo " DROP PROCEDURE GetAllAvailablePorts"
329 echo "DROP PROCEDURE GetAllAvailablePorts;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
330 echo "DELETE FROM schema_version WHERE version_int = '3';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
332 function downgrade_from_4
(){
333 echo " downgrade database from version 0.4 to version 0.3"
334 echo " adding back unique index VLAN at 'resources_port','ports'"
335 echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER Mbps_used;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
336 echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
337 echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
338 echo "ALTER TABLE ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
339 echo " change back table 'ports'"
340 echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
341 echo "ALTER TABLE ports ADD COLUMN vlan_changed SMALLINT(5) NULL DEFAULT NULL COMMENT '!=NULL when original vlan have been changed to match a pmp net with all ports in the same vlan' AFTER switch_port;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
342 echo "DELETE FROM schema_version WHERE version_int = '4';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
346 function upgrade_to_5
(){
347 echo " upgrade database from version 0.4 to version 0.5"
348 echo " add 'ip_address' to ports'"
349 echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
350 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (5, '0.5', '0.4.1', 'Add ip_address to ports', '2015-09-04');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
352 function downgrade_from_5
(){
353 echo " downgrade database from version 0.5 to version 0.4"
354 echo " removing 'ip_address' from 'ports'"
355 echo "ALTER TABLE ports DROP COLUMN ip_address;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
356 echo "DELETE FROM schema_version WHERE version_int = '5';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
359 function upgrade_to_6
(){
360 echo " upgrade database from version 0.5 to version 0.6"
361 echo " Change enalarge name, description to 255 at all database"
362 for table
in flavors images instances tenants
365 [[ $table == tenants
]] || name_length
=64
366 echo -en " $table \r"
367 echo "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
370 echo "ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(64) NOT NULL, CHANGE COLUMN user user VARCHAR(64) NOT NULL, CHANGE COLUMN password password VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN features features VARCHAR(255) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
372 echo "ALTER TABLE nets CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
373 echo -en " instances \r"
374 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
376 echo "ALTER TABLE ports CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(64) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
377 echo -en " of_flows \r"
378 echo "ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN net_id net_id VARCHAR(36) NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(255) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
379 echo -en " of_ports_pci_cor... \r"
380 echo "ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(64) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
381 echo -en " resources_port \r"
382 echo "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(64) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
383 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (6, '0.6', '0.4.2', 'Enlarging name at database', '2016-02-01');" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
385 function downgrade_from_6
(){
386 echo " downgrade database from version 0.6 to version 0.5"
387 echo " Change back name,description to shorter length at all database"
388 for table
in flavors images instances tenants
391 [[ $table == flavors
]] ||
[[ $table == images
]] || name_length
=36
392 echo -en " $table \r"
393 echo "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
396 echo "ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(36) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(36) NOT NULL, CHANGE COLUMN user user VARCHAR(36) NOT NULL, CHANGE COLUMN password password VARCHAR(36) NULL DEFAULT NULL, CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL, CHANGE COLUMN features features VARCHAR(50) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
398 echo "ALTER TABLE nets CHANGE COLUMN name name VARCHAR(50) NOT NULL, CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
399 echo -en " instances \r"
400 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
402 echo "ALTER TABLE ports CHANGE COLUMN name name VARCHAR(25) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(23) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
403 echo -en " of_flows \r"
404 echo "ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN net_id net_id VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(100) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
405 echo -en " of_ports_pci_cor... \r"
406 echo "ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(23) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
407 echo -en " resources_port \r"
408 echo "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(23) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
409 echo "DELETE FROM schema_version WHERE version_int='6';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
412 function upgrade_to_7
(){
413 echo " upgrade database from version 0.6 to version 0.7"
414 echo " add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
415 echo "ALTER TABLE nets ADD COLUMN cidr VARCHAR(64) NULL DEFAULT NULL AFTER bind, ADD COLUMN enable_dhcp ENUM('true','false') NOT NULL DEFAULT 'false' after cidr, ADD COLUMN dhcp_first_ip VARCHAR(64) NULL DEFAULT NULL AFTER enable_dhcp, ADD COLUMN dhcp_last_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_first_ip;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
416 echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
417 echo "ALTER TABLE nets ADD COLUMN bind_net VARCHAR(36) NULL DEFAULT NULL COMMENT 'To connect with other net' AFTER provider, ADD COLUMN bind_type VARCHAR(36) NULL DEFAULT NULL COMMENT 'VLAN:<tag> to insert/remove' after bind_net;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
418 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (7, '0.7', '0.4.5', 'Add bind_net to net table', '2016-02-12');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
420 function downgrade_from_7
(){
421 echo " downgrade database from version 0.7 to version 0.6"
422 echo " removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
423 echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
424 echo "ALTER TABLE nets DROP COLUMN cidr, DROP COLUMN enable_dhcp, DROP COLUMN bind_net, DROP COLUMN bind_type, DROP COLUMN dhcp_first_ip, DROP COLUMN dhcp_last_ip;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
425 echo "DELETE FROM schema_version WHERE version_int = '7';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
428 function upgrade_to_8
(){
429 echo " upgrade database from version 0.7 to version 0.8"
430 echo " add column 'checksum' to 'images'"
431 echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
432 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (8, '0.8', '0.4.10', 'add column checksum to images', '2016-09-30');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
434 function downgrade_from_8
(){
435 echo " downgrade database from version 0.8 to version 0.7"
436 echo " remove column 'checksum' from 'images'"
437 echo "ALTER TABLE images DROP COLUMN checksum;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
438 echo "DELETE FROM schema_version WHERE version_int = '8';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
441 #TODO ... put funtions here
444 [ $DATABASE_TARGET_VER_NUM -eq $DATABASE_VER_NUM ] && echo " current database version $DATABASE_VER is ok"
445 #UPGRADE DATABASE step by step
446 while [ $DATABASE_TARGET_VER_NUM -gt $DATABASE_VER_NUM ]
448 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM
+1))
449 upgrade_to_
${DATABASE_VER_NUM}
450 #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
451 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
452 #$FILE_ || exit -1 # if fail return
455 #DOWNGRADE DATABASE step by step
456 while [ $DATABASE_TARGET_VER_NUM -lt $DATABASE_VER_NUM ]
458 #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
459 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
460 #$FILE_ || exit -1 # if fail return
461 downgrade_from_
${DATABASE_VER_NUM}
462 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM-1
))