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' or 'vimpw' 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 #Creating temporary file
113 TEMPFILE
="$(mktemp -q --tmpdir "migratemanodb.XXXXXX
")"
114 trap 'rm -f "$TEMPFILE"' EXIT
115 chmod 0600 "$TEMPFILE"
117 #if password is missing, before prompting for it try without password and with "manopw"
120 DEF_EXTRA_FILE_PARAM
="--defaults-extra-file=$TEMPFILE"
121 if [ -z "${DBPASS}" ]
124 echo -e "[client]\nuser='${DBUSER}'\npassword='vimpw'" > "$TEMPFILE"
125 mysql
--defaults-extra-file="$TEMPFILE" $DBHOST_ $DBPORT_ $DBNAME -e "quit" >/dev
/null
2>&1 && DBPASS
="vimpw"
126 echo -e "[client]\nuser='${DBUSER}'\npassword=''" > "$TEMPFILE"
127 mysql
--defaults-extra-file="$TEMPFILE" $DBHOST_ $DBPORT_ $DBNAME -e "quit" >/dev
/null
2>&1 && DBPASS
=""
129 echo -e "[client]\nuser='${DBUSER}'\npassword='${DBPASS}'" > "$TEMPFILE"
131 #check and ask for database user password
132 while ! mysql
"$DEF_EXTRA_FILE_PARAM" $DBHOST_ $DBPORT_ $DBNAME -e "quit" >/dev
/null
2>&1
134 [ -n "$logintry" ] && echo -e "\nInvalid database credentials!!!. Try again (Ctrl+c to abort)"
135 [ -z "$logintry" ] && echo -e "\nProvide database name and credentials"
136 read -e -p "mysql database name($DBNAME): " KK
137 [ -n "$KK" ] && DBNAME
="$KK"
138 read -e -p "mysql user($DBUSER): " KK
139 [ -n "$KK" ] && DBUSER
="$KK"
140 read -e -s -p "mysql password: " DBPASS
141 echo -e "[client]\nuser='${DBUSER}'\npassword='${DBPASS}'" > "$TEMPFILE"
146 DBCMD
="mysql $DEF_EXTRA_FILE_PARAM $DBHOST_ $DBPORT_ $DBNAME"
149 #GET DATABASE VERSION
150 #check that the database seems a openvim database
151 if ! echo -e "show create table instances;\nshow create table numas" |
$DBCMD >/dev
/null
2>&1
153 echo " database $DBNAME does not seem to be an openvim database" >&2
157 if ! echo 'show create table schema_version;' |
$DBCMD >/dev
/null
2>&1
162 DATABASE_VER_NUM
=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
163 DATABASE_VER
=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
164 [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] && echo " Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
165 #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
169 #GET DATABASE TARGET VERSION
170 DATABASE_TARGET_VER_NUM
=0
171 [ $OPENVIM_VER_NUM -gt 1091 ] && DATABASE_TARGET_VER_NUM
=1 #>0.1.91 => 1
172 [ $OPENVIM_VER_NUM -ge 2003 ] && DATABASE_TARGET_VER_NUM
=2 #0.2.03 => 2
173 [ $OPENVIM_VER_NUM -ge 2005 ] && DATABASE_TARGET_VER_NUM
=3 #0.2.5 => 3
174 [ $OPENVIM_VER_NUM -ge 3001 ] && DATABASE_TARGET_VER_NUM
=4 #0.3.1 => 4
175 [ $OPENVIM_VER_NUM -ge 4001 ] && DATABASE_TARGET_VER_NUM
=5 #0.4.1 => 5
176 [ $OPENVIM_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM
=6 #0.4.2 => 6
177 [ $OPENVIM_VER_NUM -ge 4005 ] && DATABASE_TARGET_VER_NUM
=7 #0.4.5 => 7
178 [ $OPENVIM_VER_NUM -ge 4010 ] && DATABASE_TARGET_VER_NUM
=8 #0.4.10 => 8
179 [ $OPENVIM_VER_NUM -ge 5001 ] && DATABASE_TARGET_VER_NUM
=9 #0.5.1 => 9
180 [ $OPENVIM_VER_NUM -ge 5002 ] && DATABASE_TARGET_VER_NUM
=10 #0.5.2 => 10
181 #TODO ... put next versions here
184 function upgrade_to_1
(){
185 echo " upgrade database from version 0.0 to version 0.1"
186 echo " CREATE TABLE \`schema_version\`"
187 echo "CREATE TABLE \`schema_version\` (
188 \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
189 \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
190 \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
191 \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
193 PRIMARY KEY (\`version_int\`)
195 COMMENT='database schema control version'
196 COLLATE='utf8_general_ci'
197 ENGINE=InnoDB;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
198 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
199 VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" |
$DBCMD
200 echo " ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
201 echo "ALTER TABLE \`nets\`
202 ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
204 function downgrade_from_1
(){
205 echo " downgrade database from version 0.1 to version 0.0"
206 echo " ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
207 echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
208 echo " DROP TABLE \`schema_version\`"
209 echo "DROP TABLE \`schema_version\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
211 function upgrade_to_2
(){
212 echo " upgrade database from version 0.1 to version 0.2"
213 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
214 for table
in of_ports_pci_correspondence resources_port ports
216 echo "ALTER TABLE \`${table}\`
217 ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
218 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
219 [ $table == of_ports_pci_correspondence
] ||
220 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
222 echo " UPDATE procedure UpdateSwitchPort"
223 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
225 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
226 COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
228 #DELETES switch_port entry before writing, because if not it fails for key constrains
230 RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
231 INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
232 INNER JOIN numas on RP.numa_id=numas.id
233 INNER JOIN hosts on numas.host_id=hosts.uuid
234 INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
235 SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
236 #write switch_port into resources_port and ports
238 RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
239 INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
240 INNER JOIN numas on RP.numa_id=numas.id
241 INNER JOIN hosts on numas.host_id=hosts.uuid
242 INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
243 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;
245 delimiter ;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
246 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
247 VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
249 function upgrade_to_3
(){
250 echo " upgrade database from version 0.2 to version 0.3"
251 echo " change size of source_name at table resources_port"
252 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
253 echo " CREATE PROCEDURE GetAllAvailablePorts"
255 CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
256 COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
258 SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
261 SELECT id as port_id, Mbps, pci, switch_port, mac
263 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
267 SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
269 WHERE numa_id = Numa AND status = 'ok'
272 ON A.port_id = B.root_id
275 SELECT root_id, COUNT(id) as usedSRIOV
277 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
280 ON A.port_id = C.root_id
281 ORDER BY Mbps_free, availableSRIOV, pci;
283 delimiter ;"|
$DBCMD ||
! ! echo "ERROR. Aborted!" ||
exit -1
284 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
287 function upgrade_to_4
(){
288 echo " upgrade database from version 0.3 to version 0.4"
289 echo " remove unique VLAN index at 'resources_port', 'ports'"
290 echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
291 echo "ALTER TABLE ports DROP INDEX vlan_switch_port;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
292 echo " change table 'ports'"
293 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
294 echo "ALTER TABLE ports DROP COLUMN vlan_changed;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
295 echo "ALTER TABLE resources_port DROP COLUMN vlan;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
296 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
299 function upgrade_to_X
(){
300 #TODO, this change of foreign key does not work
301 echo " upgrade database from version 0.X to version 0.X"
302 echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
303 DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
304 echo "ALTER TABLE instances
305 ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
306 ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
309 function downgrade_from_2
(){
310 echo " downgrade database from version 0.2 to version 0.1"
311 echo " UPDATE procedure UpdateSwitchPort"
312 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
314 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
317 resources_port INNER JOIN (
318 SELECT resources_port.id,KK.switch_port
319 FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
320 INNER JOIN hosts on numas.host_id=hosts.uuid
321 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
323 ON resources_port.root_id=TABLA.id
324 SET resources_port.switch_port=TABLA.switch_port
325 WHERE resources_port.root_id=TABLA.id;
327 delimiter ;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
328 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
329 for table
in of_ports_pci_correspondence resources_port ports
331 [ $table == of_ports_pci_correspondence
] ||
332 echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
333 echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
335 [ $table == of_ports_pci_correspondence
] && switch_port_size
=50
336 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
338 echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
340 function downgrade_from_3
(){
341 echo " downgrade database from version 0.3 to version 0.2"
342 echo " change back size of source_name at table resources_port"
343 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
344 echo " DROP PROCEDURE GetAllAvailablePorts"
345 echo "DROP PROCEDURE GetAllAvailablePorts;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
346 echo "DELETE FROM schema_version WHERE version_int = '3';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
348 function downgrade_from_4
(){
349 echo " downgrade database from version 0.4 to version 0.3"
350 echo " adding back unique index VLAN at 'resources_port','ports'"
351 echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER Mbps_used;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
352 echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
353 echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
354 echo "ALTER TABLE ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
355 echo " change back table 'ports'"
356 echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
357 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
358 echo "DELETE FROM schema_version WHERE version_int = '4';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
362 function upgrade_to_5
(){
363 echo " upgrade database from version 0.4 to version 0.5"
364 echo " add 'ip_address' to ports'"
365 echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
366 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
368 function downgrade_from_5
(){
369 echo " downgrade database from version 0.5 to version 0.4"
370 echo " removing 'ip_address' from 'ports'"
371 echo "ALTER TABLE ports DROP COLUMN ip_address;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
372 echo "DELETE FROM schema_version WHERE version_int = '5';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
375 function upgrade_to_6
(){
376 echo " upgrade database from version 0.5 to version 0.6"
377 echo " Change enalarge name, description to 255 at all database"
378 for table
in flavors images instances tenants
381 [[ $table == tenants
]] || name_length
=64
382 echo -en " $table \r"
383 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
386 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
388 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
389 echo -en " instances \r"
390 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
392 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
393 echo -en " of_flows \r"
394 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
395 echo -en " of_ports_pci_cor... \r"
396 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
397 echo -en " resources_port \r"
398 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
399 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
401 function downgrade_from_6
(){
402 echo " downgrade database from version 0.6 to version 0.5"
403 echo " Change back name,description to shorter length at all database"
404 for table
in flavors images instances tenants
407 [[ $table == flavors
]] ||
[[ $table == images
]] || name_length
=36
408 echo -en " $table \r"
409 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
412 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
414 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
415 echo -en " instances \r"
416 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
418 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
419 echo -en " of_flows \r"
420 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
421 echo -en " of_ports_pci_cor... \r"
422 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
423 echo -en " resources_port \r"
424 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
425 echo "DELETE FROM schema_version WHERE version_int='6';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
428 function upgrade_to_7
(){
429 echo " upgrade database from version 0.6 to version 0.7"
430 echo " add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
431 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
432 echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
433 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
434 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
436 function downgrade_from_7
(){
437 echo " downgrade database from version 0.7 to version 0.6"
438 echo " removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
439 echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
440 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
441 echo "DELETE FROM schema_version WHERE version_int = '7';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
444 function upgrade_to_8
(){
445 echo " upgrade database from version 0.7 to version 0.8"
446 echo " add column 'checksum' to 'images'"
447 echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
448 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
450 function downgrade_from_8
(){
451 echo " downgrade database from version 0.8 to version 0.7"
452 echo " remove column 'checksum' from 'images'"
453 echo "ALTER TABLE images DROP COLUMN checksum;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
454 echo "DELETE FROM schema_version WHERE version_int = '8';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
457 function upgrade_to_9
(){
458 echo " upgrade database from version 0.8 to version 0.9"
459 echo " change length of columns 'path' and 'name' to 255 in table 'images', and change length of column 'name' to 255 in table 'flavors'"
460 echo "ALTER TABLE images CHANGE COLUMN path path VARCHAR(255) NOT NULL AFTER uuid, CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER path;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
461 echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
462 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (9, '0.9', '0.5.1', 'increase length of columns path and name to 255 in table images, and change length of column name to 255 in table flavors', '2017-01-10');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
464 function downgrade_from_9
(){
465 echo " downgrade database from version 0.9 to version 0.8"
466 echo " change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
467 echo "ALTER TABLE images CHANGE COLUMN path path VARCHAR(100) NOT NULL AFTER uuid, CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER path;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
468 echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
469 echo "DELETE FROM schema_version WHERE version_int = '9';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
472 function upgrade_to_10
(){
473 echo " upgrade database from version 0.9 to version 0.10"
474 echo " change types at 'ports'"
475 echo "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external','instance:ovs','controller:ovs') NOT NULL DEFAULT 'instance:bridge' AFTER status;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
476 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (10, '0.10', '0.5.2', 'change ports type, adding instance:ovs', '2017-02-01');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
478 function downgrade_from_10
(){
479 echo " downgrade database from version 0.10 to version 0.9"
480 echo " change back types at 'ports'"
481 echo "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external') NOT NULL DEFAULT 'instance:bridge' AFTER status;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
482 echo "DELETE FROM schema_version WHERE version_int = '10';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
484 #TODO ... put funtions here
487 [ $DATABASE_TARGET_VER_NUM -eq $DATABASE_VER_NUM ] && echo " current database version $DATABASE_VER is ok"
488 #UPGRADE DATABASE step by step
489 while [ $DATABASE_TARGET_VER_NUM -gt $DATABASE_VER_NUM ]
491 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM
+1))
492 upgrade_to_
${DATABASE_VER_NUM}
493 #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
494 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
495 #$FILE_ || exit -1 # if fail return
498 #DOWNGRADE DATABASE step by step
499 while [ $DATABASE_TARGET_VER_NUM -lt $DATABASE_VER_NUM ]
501 #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
502 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
503 #$FILE_ || exit -1 # if fail return
504 downgrade_from_
${DATABASE_VER_NUM}
505 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM-1
))