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 [ $OPENVIM_VER_NUM -ge 5004 ] && DATABASE_TARGET_VER_NUM
=11 #0.5.4 => 11
182 [ $OPENVIM_VER_NUM -ge 5005 ] && DATABASE_TARGET_VER_NUM
=12 #0.5.5 => 12
183 [ $OPENVIM_VER_NUM -ge 5006 ] && DATABASE_TARGET_VER_NUM
=13 #0.5.6 => 12
184 #TODO ... put next versions here
186 function upgrade_to_1
(){
187 echo " upgrade database from version 0.0 to version 0.1"
188 echo " CREATE TABLE \`schema_version\`"
189 echo "CREATE TABLE \`schema_version\` (
190 \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
191 \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
192 \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
193 \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
195 PRIMARY KEY (\`version_int\`)
197 COMMENT='database schema control version'
198 COLLATE='utf8_general_ci'
199 ENGINE=InnoDB;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
200 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
201 VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" |
$DBCMD
202 echo " ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
203 echo "ALTER TABLE \`nets\`
204 ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
206 function downgrade_from_1
(){
207 echo " downgrade database from version 0.1 to version 0.0"
208 echo " ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
209 echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
210 echo " DROP TABLE \`schema_version\`"
211 echo "DROP TABLE \`schema_version\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
213 function upgrade_to_2
(){
214 echo " upgrade database from version 0.1 to version 0.2"
215 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
216 for table
in of_ports_pci_correspondence resources_port ports
218 echo "ALTER TABLE \`${table}\`
219 ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
220 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
221 [ $table == of_ports_pci_correspondence
] ||
222 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
224 echo " UPDATE procedure UpdateSwitchPort"
225 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
227 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
228 COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
230 #DELETES switch_port entry before writing, because if not it fails for key constrains
232 RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
233 INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
234 INNER JOIN numas on RP.numa_id=numas.id
235 INNER JOIN hosts on numas.host_id=hosts.uuid
236 INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
237 SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
238 #write switch_port into resources_port and ports
240 RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
241 INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
242 INNER JOIN numas on RP.numa_id=numas.id
243 INNER JOIN hosts on numas.host_id=hosts.uuid
244 INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
245 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;
247 delimiter ;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
248 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
249 VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
251 function upgrade_to_3
(){
252 echo " upgrade database from version 0.2 to version 0.3"
253 echo " change size of source_name at table resources_port"
254 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
255 echo " CREATE PROCEDURE GetAllAvailablePorts"
257 CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
258 COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
260 SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
263 SELECT id as port_id, Mbps, pci, switch_port, mac
265 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
269 SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
271 WHERE numa_id = Numa AND status = 'ok'
274 ON A.port_id = B.root_id
277 SELECT root_id, COUNT(id) as usedSRIOV
279 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
282 ON A.port_id = C.root_id
283 ORDER BY Mbps_free, availableSRIOV, pci;
285 delimiter ;"|
$DBCMD ||
! ! echo "ERROR. Aborted!" ||
exit -1
286 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
289 function upgrade_to_4
(){
290 echo " upgrade database from version 0.3 to version 0.4"
291 echo " remove unique VLAN index at 'resources_port', 'ports'"
292 echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
293 echo "ALTER TABLE ports DROP INDEX vlan_switch_port;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
294 echo " change table 'ports'"
295 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
296 echo "ALTER TABLE ports DROP COLUMN vlan_changed;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
297 echo "ALTER TABLE resources_port DROP COLUMN vlan;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
298 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
301 function upgrade_to_X
(){
302 #TODO, this change of foreign key does not work
303 echo " upgrade database from version 0.X to version 0.X"
304 echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
305 DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
306 echo "ALTER TABLE instances
307 ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
308 ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
311 function downgrade_from_2
(){
312 echo " downgrade database from version 0.2 to version 0.1"
313 echo " UPDATE procedure UpdateSwitchPort"
314 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
316 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
319 resources_port INNER JOIN (
320 SELECT resources_port.id,KK.switch_port
321 FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
322 INNER JOIN hosts on numas.host_id=hosts.uuid
323 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
325 ON resources_port.root_id=TABLA.id
326 SET resources_port.switch_port=TABLA.switch_port
327 WHERE resources_port.root_id=TABLA.id;
329 delimiter ;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
330 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
331 for table
in of_ports_pci_correspondence resources_port ports
333 [ $table == of_ports_pci_correspondence
] ||
334 echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
335 echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
337 [ $table == of_ports_pci_correspondence
] && switch_port_size
=50
338 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
340 echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
342 function downgrade_from_3
(){
343 echo " downgrade database from version 0.3 to version 0.2"
344 echo " change back size of source_name at table resources_port"
345 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
346 echo " DROP PROCEDURE GetAllAvailablePorts"
347 echo "DROP PROCEDURE GetAllAvailablePorts;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
348 echo "DELETE FROM schema_version WHERE version_int = '3';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
350 function downgrade_from_4
(){
351 echo " downgrade database from version 0.4 to version 0.3"
352 echo " adding back unique index VLAN at 'resources_port','ports'"
353 echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER Mbps_used;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
354 echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
355 echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
356 echo "ALTER TABLE ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
357 echo " change back table 'ports'"
358 echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
359 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
360 echo "DELETE FROM schema_version WHERE version_int = '4';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
364 function upgrade_to_5
(){
365 echo " upgrade database from version 0.4 to version 0.5"
366 echo " add 'ip_address' to ports'"
367 echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
368 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
370 function downgrade_from_5
(){
371 echo " downgrade database from version 0.5 to version 0.4"
372 echo " removing 'ip_address' from 'ports'"
373 echo "ALTER TABLE ports DROP COLUMN ip_address;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
374 echo "DELETE FROM schema_version WHERE version_int = '5';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
377 function upgrade_to_6
(){
378 echo " upgrade database from version 0.5 to version 0.6"
379 echo " Change enalarge name, description to 255 at all database"
380 for table
in flavors images instances tenants
383 [[ $table == tenants
]] || name_length
=64
384 echo -en " $table \r"
385 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
388 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
390 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
391 echo -en " instances \r"
392 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
394 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
395 echo -en " of_flows \r"
396 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
397 echo -en " of_ports_pci_cor... \r"
398 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
399 echo -en " resources_port \r"
400 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
401 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
403 function downgrade_from_6
(){
404 echo " downgrade database from version 0.6 to version 0.5"
405 echo " Change back name,description to shorter length at all database"
406 for table
in flavors images instances tenants
409 [[ $table == flavors
]] ||
[[ $table == images
]] || name_length
=36
410 echo -en " $table \r"
411 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
414 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
416 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
417 echo -en " instances \r"
418 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
420 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
421 echo -en " of_flows \r"
422 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
423 echo -en " of_ports_pci_cor... \r"
424 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
425 echo -en " resources_port \r"
426 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
427 echo "DELETE FROM schema_version WHERE version_int='6';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
430 function upgrade_to_7
(){
431 echo " upgrade database from version 0.6 to version 0.7"
432 echo " add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
433 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
434 echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
435 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
436 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
438 function downgrade_from_7
(){
439 echo " downgrade database from version 0.7 to version 0.6"
440 echo " removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
441 echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
442 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
443 echo "DELETE FROM schema_version WHERE version_int = '7';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
446 function upgrade_to_8
(){
447 echo " upgrade database from version 0.7 to version 0.8"
448 echo " add column 'checksum' to 'images'"
449 echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
450 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
452 function downgrade_from_8
(){
453 echo " downgrade database from version 0.8 to version 0.7"
454 echo " remove column 'checksum' from 'images'"
455 echo "ALTER TABLE images DROP COLUMN checksum;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
456 echo "DELETE FROM schema_version WHERE version_int = '8';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
459 function upgrade_to_9
(){
460 echo " upgrade database from version 0.8 to version 0.9"
461 echo " change length of columns 'path' and 'name' to 255 in table 'images', and change length of column 'name' to 255 in table 'flavors'"
462 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
463 echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
464 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
466 function downgrade_from_9
(){
467 echo " downgrade database from version 0.9 to version 0.8"
468 echo " change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
469 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
470 echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
471 echo "DELETE FROM schema_version WHERE version_int = '9';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
474 function upgrade_to_10
(){
475 echo " upgrade database from version 0.9 to version 0.10"
476 echo " change types at 'ports'"
477 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
478 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
480 function downgrade_from_10
(){
481 echo " downgrade database from version 0.10 to version 0.9"
482 echo " change back types at 'ports'"
483 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
484 echo "DELETE FROM schema_version WHERE version_int = '10';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
487 function upgrade_to_11
(){
488 echo " upgrade database from version 0.10 to version 0.11"
489 echo " Add gateway_ip colum to 'nets'"
490 echo "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
491 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (11, '0.11', '0.5.4', 'Add gateway_ip colum to nets', '2017-02-13');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
493 function downgrade_from_11
(){
494 echo " downgrade database from version 0.11 to version 0.10"
495 echo " Delete gateway_ip colum from 'nets'"
496 echo "ALTER TABLE nets DROP COLUMN gateway_ip;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
497 echo "DELETE FROM schema_version WHERE version_int = '11';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
499 function upgrade_to_12
(){
500 echo " upgrade database from version 0.11 to version 0.12"
501 echo " Create of_controller table "
502 echo "CREATE TABLE ofcs (
503 uuid VARCHAR(36) NOT NULL,
504 name VARCHAR(255) NOT NULL,
505 dpid VARCHAR(64) NOT NULL,
506 ip VARCHAR(64) NOT NULL,
507 port INT(5) NOT NULL,
508 type VARCHAR(64) NOT NULL,
509 version VARCHAR(12) NULL DEFAULT NULL,
510 user VARCHAR(64) NULL DEFAULT NULL,
511 password VARCHAR(64) NULL DEFAULT NULL,
514 COLLATE='utf8_general_ci'
515 ENGINE=InnoDB;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
516 echo " Modify user_at for uuids table"
517 echo "ALTER TABLE uuids CHANGE COLUMN used_at used_at VARCHAR(64) NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
518 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (12, '0.12', '0.5.5', 'Add of_controller table', '2017-02-17');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
521 function downgrade_from_12
(){
522 echo " downgrade database from version 0.12 to version 0.11"
523 echo " Delete ofcs table"
524 echo "DROP TABLE ofcs;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
525 echo "ALTER TABLE uuids CHANGE COLUMN used_at used_at ENUM('flavors', 'hosts', 'images', 'instances', 'nets', 'ports', 'tenants') NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
526 echo "DELETE FROM schema_version WHERE version_int = '12';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
528 function upgrade_to_13
(){
529 echo " upgrade database from version 0.12 to version 0.13"
530 echo " Create of_port_mapings table "
531 echo "CREATE TABLE of_port_mappings (
532 uuid VARCHAR(36) NOT NULL,
533 ofc_id VARCHAR(36) NULL DEFAULT NULL,
534 region VARCHAR(64) NULL DEFAULT NULL,
535 compute_node VARCHAR(64) NULL DEFAULT NULL,
536 pci VARCHAR(50) NULL DEFAULT NULL,
537 switch_dpid VARCHAR(64) NULL DEFAULT NULL,
538 switch_port VARCHAR(64) NULL DEFAULT NULL,
539 switch_mac CHAR(18) NULL DEFAULT NULL,
540 UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
541 UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
542 UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
543 INDEX FK_of_port_mappings_ofcs (ofc_id),
544 CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
545 COLLATE='utf8_general_ci'
546 ENGINE=InnoDB;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
547 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (13, '0.13', '0.5.6', 'Add of_port_mapings table', '2017-03-09');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
550 function downgrade_from_13
(){
551 echo " downgrade database from version 0.13 to version 0.12"
552 echo " Delete of_port_mappings table"
553 echo "DROP TABLE of_port_mappings;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
554 echo "DELETE FROM schema_version WHERE version_int = '13';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
556 #TODO ... put funtions here
558 echo "db version = "${DATABASE_VER_NUM}
559 [ $DATABASE_TARGET_VER_NUM -eq $DATABASE_VER_NUM ] && echo " current database version $DATABASE_VER is ok"
560 #UPGRADE DATABASE step by step
561 while [ $DATABASE_TARGET_VER_NUM -gt $DATABASE_VER_NUM ]
563 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM
+1))
565 upgrade_to_
${DATABASE_VER_NUM}
566 #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
567 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
568 #$FILE_ || exit -1 # if fail return
571 #DOWNGRADE DATABASE step by step
572 while [ $DATABASE_TARGET_VER_NUM -lt $DATABASE_VER_NUM ]
574 #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
575 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
576 #$FILE_ || exit -1 # if fail return
577 downgrade_from_
${DATABASE_VER_NUM}
578 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM-1
))