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
30 DEFAULT_DBPASS
="vimpw"
35 #TODO update it with the last database version
44 echo -e "Usage: $0 OPTIONS [version]"
45 echo -e " Upgrades/Downgrades openvim database preserving the content."\
46 "If [version] is not provided, it is upgraded to the last version"
48 echo -e " -u USER database user. '$DBUSER' by default. Prompts if DB access fails"
49 echo -e " -p PASS database password. If missing it tries without and '$DEFAULT_DBPASS' password before prompting"
50 echo -e " -P PORT database port. '$DBPORT' by default"
51 echo -e " -h HOST database host. 'localhost' by default"
52 echo -e " -d NAME database name. '$DBNAME' by default. Prompts if DB access fails"
53 echo -e " -q --quiet: Do not prompt for credentials and exit if cannot access to database"
54 echo -e " --help shows this help"
57 while getopts ":u:p:P:h:d:q-:" o
; do
78 [ "${OPTARG}" == "help" ] && usage
&& exit 0
79 [ "${OPTARG}" == "quiet" ] && export QUIET_MODE
=yes && continue
80 echo "Invalid option: '--$OPTARG'. Type --help for more information" >&2
84 echo "Invalid option: '-$OPTARG'. Type --help for more information" >&2
88 echo "Option '-$OPTARG' requires an argument. Type --help for more information" >&2
101 if [ -n "$DB_VERSION" ] ; then
102 # check it is a number and an allowed one
103 [ "$DB_VERSION" -eq "$DB_VERSION" ] 2>/dev
/null ||
104 ! echo "parameter 'version' requires a integer value" >&2 ||
exit 1
105 if [ "$DB_VERSION" -lt 0 ] ||
[ "$DB_VERSION" -gt "$LAST_DB_VERSION" ] ; then
106 echo "parameter 'version' requires a valid database version between '0' and '$LAST_DB_VERSION'"\
107 "If you need an upper version, get a newer version of this script '$0'" >&2
111 DB_VERSION
="$LAST_DB_VERSION"
114 # Creating temporary file
115 TEMPFILE
="$(mktemp -q --tmpdir "migratevimdb.XXXXXX
")"
116 trap 'rm -f "$TEMPFILE"' EXIT
117 chmod 0600 "$TEMPFILE"
118 DEF_EXTRA_FILE_PARAM
="--defaults-extra-file=$TEMPFILE"
119 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
121 # Check and ask for database user password
123 while ! DB_ERROR
=`mysql "$DEF_EXTRA_FILE_PARAM" $DBNAME -e "quit" 2>&1 >/dev/null`
125 # if password is not provided, try silently with $DEFAULT_DBPASS before exit or prompt for credentials
126 [[ -n "$FIRST_TRY" ]] && [[ -z "$DBPASS" ]] && DBPASS
="$DEFAULT_DBPASS" &&
127 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE" &&
130 [[ -n "$QUIET_MODE" ]] && echo -e "Invalid database credentials!!!" >&2 && exit 1
131 echo -e "Provide database name and credentials (Ctrl+c to abort):"
132 read -e -p " mysql database name($DBNAME): " KK
133 [ -n "$KK" ] && DBNAME
="$KK"
134 read -e -p " mysql user($DBUSER): " KK
135 [ -n "$KK" ] && DBUSER
="$KK"
136 read -e -s -p " mysql password: " DBPASS
137 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
142 DBCMD
="mysql $DEF_EXTRA_FILE_PARAM $DBNAME"
145 #GET DATABASE VERSION
146 # check that the database seems a openvim database
147 if ! echo -e "show create table instances;\nshow create table numas" |
$DBCMD >/dev
/null
2>&1
149 echo " database $DBNAME does not seem to be an openvim database" >&2
153 if ! echo 'show create table schema_version;' |
$DBCMD >/dev
/null
2>&1
158 DATABASE_VER_NUM
=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
159 DATABASE_VER
=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
160 [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] &&
161 echo " Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
162 #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
165 [ "$DATABASE_VER_NUM" -gt "$LAST_DB_VERSION" ] &&
166 echo "Database has been upgraded with a newer version of this script. Use this version to downgrade" >&2 &&
169 #GET DATABASE TARGET VERSION
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 => 13
184 #[ $OPENVIM_VER_NUM -ge 5007 ] && DATABASE_TARGET_VER_NUM=14 #0.5.7 => 14
185 #[ $OPENVIM_VER_NUM -ge 5008 ] && DATABASE_TARGET_VER_NUM=15 #0.5.8 => 15
186 #[ $OPENVIM_VER_NUM -ge 5009 ] && DATABASE_TARGET_VER_NUM=16 #0.5.9 => 16
187 #[ $OPENVIM_VER_NUM -ge 5010 ] && DATABASE_TARGET_VER_NUM=17 #0.5.10 => 17
188 #[ $OPENVIM_VER_NUM -ge 5013 ] && DATABASE_TARGET_VER_NUM=18 #0.5.13 => 18
189 #[ $OPENVIM_VER_NUM -ge 5015 ] && DATABASE_TARGET_VER_NUM=19 #0.5.15 => 19
190 #[ $OPENVIM_VER_NUM -ge 5017 ] && DATABASE_TARGET_VER_NUM=20 #0.5.17 => 20
191 #[ $OPENVIM_VER_NUM -ge 5018 ] && DATABASE_TARGET_VER_NUM=21 #0.5.18 => 21
192 # TODO ... put next versions here
194 function upgrade_to_1
(){
195 # echo " upgrade database from version 0.0 to version 0.1"
196 echo " CREATE TABLE \`schema_version\`"
197 echo "CREATE TABLE \`schema_version\` (
198 \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
199 \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
200 \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
201 \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
203 PRIMARY KEY (\`version_int\`)
205 COMMENT='database schema control version'
206 COLLATE='utf8_general_ci'
207 ENGINE=InnoDB;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
208 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
209 VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" |
$DBCMD
210 echo " ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
211 echo "ALTER TABLE \`nets\`
212 ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
214 function downgrade_from_1
(){
215 # echo " downgrade database from version 0.1 to version 0.0"
216 echo " ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
217 echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
218 echo " DROP TABLE \`schema_version\`"
219 echo "DROP TABLE \`schema_version\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
221 function upgrade_to_2
(){
222 # echo " upgrade database from version 0.1 to version 0.2"
223 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
224 for table
in of_ports_pci_correspondence resources_port ports
226 echo "ALTER TABLE \`${table}\`
227 ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
228 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
229 [ $table == of_ports_pci_correspondence
] ||
230 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
232 echo " UPDATE procedure UpdateSwitchPort"
233 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
235 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
236 COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
238 #DELETES switch_port entry before writing, because if not it fails for key constrains
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=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
246 #write switch_port into resources_port and ports
248 RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
249 INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
250 INNER JOIN numas on RP.numa_id=numas.id
251 INNER JOIN hosts on numas.host_id=hosts.uuid
252 INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
253 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;
255 delimiter ;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
256 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
257 VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
259 function upgrade_to_3
(){
260 # echo " upgrade database from version 0.2 to version 0.3"
261 echo " change size of source_name at table resources_port"
262 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
263 echo " CREATE PROCEDURE GetAllAvailablePorts"
265 CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
266 COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
268 SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
271 SELECT id as port_id, Mbps, pci, switch_port, mac
273 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
277 SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
279 WHERE numa_id = Numa AND status = 'ok'
282 ON A.port_id = B.root_id
285 SELECT root_id, COUNT(id) as usedSRIOV
287 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
290 ON A.port_id = C.root_id
291 ORDER BY Mbps_free, availableSRIOV, pci;
293 delimiter ;"|
$DBCMD ||
! ! echo "ERROR. Aborted!" ||
exit -1
294 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
297 function upgrade_to_4
(){
298 # echo " upgrade database from version 0.3 to version 0.4"
299 echo " remove unique VLAN index at 'resources_port', 'ports'"
300 echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
301 echo "ALTER TABLE ports DROP INDEX vlan_switch_port;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
302 echo " change table 'ports'"
303 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
304 echo "ALTER TABLE ports DROP COLUMN vlan_changed;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
305 echo "ALTER TABLE resources_port DROP COLUMN vlan;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
306 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
309 function upgrade_to_X
(){
310 #TODO, this change of foreign key does not work
311 # echo " upgrade database from version 0.X to version 0.X"
312 echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
313 DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
314 echo "ALTER TABLE instances
315 ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
316 ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
319 function downgrade_from_2
(){
320 # echo " downgrade database from version 0.2 to version 0.1"
321 echo " UPDATE procedure UpdateSwitchPort"
322 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
324 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
327 resources_port INNER JOIN (
328 SELECT resources_port.id,KK.switch_port
329 FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
330 INNER JOIN hosts on numas.host_id=hosts.uuid
331 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
333 ON resources_port.root_id=TABLA.id
334 SET resources_port.switch_port=TABLA.switch_port
335 WHERE resources_port.root_id=TABLA.id;
337 delimiter ;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
338 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
339 for table
in of_ports_pci_correspondence resources_port ports
341 [ $table == of_ports_pci_correspondence
] ||
342 echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
343 echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
345 [ $table == of_ports_pci_correspondence
] && switch_port_size
=50
346 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
348 echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
350 function downgrade_from_3
(){
351 # echo " downgrade database from version 0.3 to version 0.2"
352 echo " change back size of source_name at table resources_port"
353 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
354 echo " DROP PROCEDURE GetAllAvailablePorts"
355 echo "DROP PROCEDURE GetAllAvailablePorts;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
356 echo "DELETE FROM schema_version WHERE version_int = '3';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
358 function downgrade_from_4
(){
359 # echo " downgrade database from version 0.4 to version 0.3"
360 echo " adding back unique index VLAN at 'resources_port','ports'"
361 echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER Mbps_used;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
362 echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
363 echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
364 echo "ALTER TABLE ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
365 echo " change back table 'ports'"
366 echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
367 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
368 echo "DELETE FROM schema_version WHERE version_int = '4';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
372 function upgrade_to_5
(){
373 # echo " upgrade database from version 0.4 to version 0.5"
374 echo " add 'ip_address' to ports'"
375 echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
376 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
378 function downgrade_from_5
(){
379 # echo " downgrade database from version 0.5 to version 0.4"
380 echo " removing 'ip_address' from 'ports'"
381 echo "ALTER TABLE ports DROP COLUMN ip_address;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
382 echo "DELETE FROM schema_version WHERE version_int = '5';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
385 function upgrade_to_6
(){
386 # echo " upgrade database from version 0.5 to version 0.6"
387 echo " Change enalarge name, description to 255 at all database"
388 for table
in flavors images instances tenants
391 [[ $table == tenants
]] || name_length
=64
392 echo -en " $table \r"
393 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
396 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
398 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
399 echo -en " instances \r"
400 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
402 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
403 echo -en " of_flows \r"
404 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
405 echo -en " of_ports_pci_cor... \r"
406 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
407 echo -en " resources_port \r"
408 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
409 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
411 function downgrade_from_6
(){
412 # echo " downgrade database from version 0.6 to version 0.5"
413 echo " Change back name,description to shorter length at all database"
414 for table
in flavors images instances tenants
417 [[ $table == flavors
]] ||
[[ $table == images
]] || name_length
=36
418 echo -en " $table \r"
419 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
422 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
424 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
425 echo -en " instances \r"
426 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
428 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
429 echo -en " of_flows \r"
430 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
431 echo -en " of_ports_pci_cor... \r"
432 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
433 echo -en " resources_port \r"
434 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
435 echo "DELETE FROM schema_version WHERE version_int='6';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
438 function upgrade_to_7
(){
439 # echo " upgrade database from version 0.6 to version 0.7"
440 echo " add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
441 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
442 echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
443 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
444 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
446 function downgrade_from_7
(){
447 # echo " downgrade database from version 0.7 to version 0.6"
448 echo " removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
449 echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
450 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
451 echo "DELETE FROM schema_version WHERE version_int = '7';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
454 function upgrade_to_8
(){
455 # echo " upgrade database from version 0.7 to version 0.8"
456 echo " add column 'checksum' to 'images'"
457 echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
458 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
460 function downgrade_from_8
(){
461 # echo " downgrade database from version 0.8 to version 0.7"
462 echo " remove column 'checksum' from 'images'"
463 echo "ALTER TABLE images DROP COLUMN checksum;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
464 echo "DELETE FROM schema_version WHERE version_int = '8';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
467 function upgrade_to_9
(){
468 # echo " upgrade database from version 0.8 to version 0.9"
469 echo " change length of columns 'path' and 'name' to 255 in table 'images', and change length of column 'name' to 255 in table 'flavors'"
470 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
471 echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
472 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
474 function downgrade_from_9
(){
475 # echo " downgrade database from version 0.9 to version 0.8"
476 echo " change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
477 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
478 echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
479 echo "DELETE FROM schema_version WHERE version_int = '9';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
482 function upgrade_to_10
(){
483 # echo " upgrade database from version 0.9 to version 0.10"
484 echo " change types at 'ports'"
485 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
486 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
488 function downgrade_from_10
(){
489 # echo " downgrade database from version 0.10 to version 0.9"
490 echo " change back types at 'ports'"
491 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
492 echo "DELETE FROM schema_version WHERE version_int = '10';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
495 function upgrade_to_11
(){
496 # echo " upgrade database from version 0.10 to version 0.11"
497 echo " Add gateway_ip colum to 'nets'"
498 echo "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
499 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
501 function downgrade_from_11
(){
502 # echo " downgrade database from version 0.11 to version 0.10"
503 echo " Delete gateway_ip colum from 'nets'"
504 echo "ALTER TABLE nets DROP COLUMN gateway_ip;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
505 echo "DELETE FROM schema_version WHERE version_int = '11';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
507 function upgrade_to_12
(){
508 # echo " upgrade database from version 0.11 to version 0.12"
509 echo " Create of_controller table "
510 echo "CREATE TABLE ofcs (
511 uuid VARCHAR(36) NOT NULL,
512 name VARCHAR(255) NOT NULL,
513 dpid VARCHAR(64) NOT NULL,
514 ip VARCHAR(64) NOT NULL,
515 port INT(5) NOT NULL,
516 type VARCHAR(64) NOT NULL,
517 version VARCHAR(12) NULL DEFAULT NULL,
518 user VARCHAR(64) NULL DEFAULT NULL,
519 password VARCHAR(64) NULL DEFAULT NULL,
522 COLLATE='utf8_general_ci'
523 ENGINE=InnoDB;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
524 echo " Modify user_at for uuids table"
525 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
526 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
529 function downgrade_from_12
(){
530 # echo " downgrade database from version 0.12 to version 0.11"
531 echo " Delete ofcs table"
532 echo "DROP TABLE ofcs;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
533 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
534 echo "DELETE FROM schema_version WHERE version_int = '12';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
537 function upgrade_to_13
(){
538 # echo " upgrade database from version 0.12 to version 0.13"
539 echo " Create of_port_mapings table "
540 echo "CREATE TABLE of_port_mappings (
541 uuid VARCHAR(36) NOT NULL,
542 ofc_id VARCHAR(36) NULL DEFAULT NULL,
543 region VARCHAR(64) NULL DEFAULT NULL,
544 compute_node VARCHAR(64) NULL DEFAULT NULL,
545 pci VARCHAR(50) NULL DEFAULT NULL,
546 switch_dpid VARCHAR(64) NULL DEFAULT NULL,
547 switch_port VARCHAR(64) NULL DEFAULT NULL,
548 switch_mac CHAR(18) NULL DEFAULT NULL,
549 UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
550 UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
551 UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
552 INDEX FK_of_port_mappings_ofcs (ofc_id),
553 CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
554 COLLATE='utf8_general_ci'
555 ENGINE=InnoDB;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
556 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
559 function downgrade_from_13
(){
560 # echo " downgrade database from version 0.13 to version 0.12"
561 echo " Delete of_port_mappings table"
562 echo "DROP TABLE of_port_mappings;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
563 echo "DELETE FROM schema_version WHERE version_int = '13';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
566 function upgrade_to_14
(){
567 # echo " upgrade database from version 0.13 to version 0.14"
568 echo " Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
569 echo "ALTER TABLE ports
570 ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
571 ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
572 ADD CONSTRAINT FK_port_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
573 echo "ALTER TABLE resources_port
574 ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
575 ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
576 ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
577 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (14, '0.14', '0.5.7', 'Add switch_mac, ofc_id colum to ports and resources_port tables', '2017-03-09');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
580 function downgrade_from_14
(){
581 # echo " downgrade database from version 0.14 to version 0.13"
582 echo " Delete switch_mac, ofc_id colum to 'ports'"
583 echo "ALTER TABLE ports
584 DROP COLUMN switch_mac,
586 DROP FOREIGN KEY FK_port_ofc_id;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
587 echo "ALTER TABLE resources_port
588 DROP COLUMN switch_mac,
590 DROP FOREIGN KEY FK_resource_ofc_id;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
591 echo "DELETE FROM schema_version WHERE version_int = '14';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
594 function upgrade_to_15
(){
595 # echo " upgrade database from version 0.14 to version 0.15"
596 echo " Add ofc_id colum to 'of_flows'"
597 echo "ALTER TABLE of_flows
598 ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id,
599 ADD CONSTRAINT FK_of_flows_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
600 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (15, '0.15', '0.5.8', 'Add ofc_id colum to of_flows', '2017-03-15');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
603 function downgrade_from_15
(){
604 # echo " downgrade database from version 0.15 to version 0.14"
605 echo " Delete ofc_id to 'of_flows'"
606 echo "ALTER TABLE of_flows
608 DROP FOREIGN KEY FK_of_flows_ofcs;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
609 echo "DELETE FROM schema_version WHERE version_int = '15';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
613 function upgrade_to_16
(){
614 # echo " upgrade database from version 0.15 to version 0.16"
615 echo " Add last_error and status colum to 'ofcs'"
616 echo "ALTER TABLE ofcs
617 ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password,
618 ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
619 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (16, '0.16', '0.5.9', 'Add last_error and status colum to ofcs', '2017-03-17');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
622 function downgrade_from_16
(){
623 # echo " downgrade database from version 0.16 to version 0.15"
624 echo " Delete last_error and status colum to 'ofcs'"
625 echo "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status; " |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
626 echo "DELETE FROM schema_version WHERE version_int = '16';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
629 function upgrade_to_17
(){
630 # echo " upgrade database from version 0.16 to version 0.17"
631 echo " Add pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
632 echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
633 "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
634 echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
635 "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
636 echo " Add nets_with_same_vlan to table ofcs"
637 echo "ALTER TABLE ofcs ADD COLUMN nets_with_same_vlan ENUM('true','false') NOT NULL DEFAULT 'false' AFTER status;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
638 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (17, '0.17', '0.5.10', 'Add pci to unique index dpid port/mac at of_port_mappings', '2017-04-05');"|
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
641 function downgrade_from_17
(){
642 # echo " downgrade database from version 0.17 to version 0.16"
643 echo " Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
644 echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
645 "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
646 echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
647 "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
648 echo " Remove nets_with_same_vlan from table ofcs"
649 echo "ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
650 echo "DELETE FROM schema_version WHERE version_int = '17';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
653 function upgrade_to_18
(){
654 echo " Add 'region' at 'nets' and change unique index vlan+region"
655 echo "ALTER TABLE nets ADD COLUMN region VARCHAR(64) NULL DEFAULT NULL AFTER admin_state_up, " \
656 "DROP INDEX type_vlan;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
657 echo " Fill 'region' with __OVS__/__DATA__ for OVS/openflow provider at nets"
658 echo "UPDATE nets set region='__OVS__' where provider like 'OVS%';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
659 echo "UPDATE nets set region='__DATA__' where type='data' or type='ptp';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
661 echo " Create new index region_vlan at nets"
662 echo "ALTER TABLE nets ADD UNIQUE INDEX region_vlan (region, vlan);" \
663 |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
664 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
665 "VALUES (18, '0.18', '0.5.13', 'Add region to nets, change vlan unique index', '2017-05-03');"\
666 |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
669 function downgrade_from_18
(){
670 echo " Delete 'region' at 'nets' and change back unique index vlan+type"
671 echo "ALTER TABLE nets DROP INDEX region_vlan, DROP COLUMN region;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
672 echo " Create back index type_vlan at nets"
673 echo "ALTER TABLE nets ADD UNIQUE INDEX type_vlan (type, vlan);" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
674 echo "DELETE FROM schema_version WHERE version_int = '18';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
677 function upgrade_to_19
(){
678 echo " Add 'keyfile' to 'hosts'"
679 echo "ALTER TABLE hosts ADD COLUMN keyfile VARCHAR(255) NULL DEFAULT NULL AFTER password;" \
680 |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
681 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
682 "VALUES (19, '0.19', '0.5.15', 'Add keyfile to hosts', '2017-05-23');"\
683 |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
686 function downgrade_from_19
(){
687 echo " Delete 'keyfile' from 'hosts'"
688 echo "ALTER TABLE hosts DROP COLUMN keyfile;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
689 echo "DELETE FROM schema_version WHERE version_int = '19';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
692 function upgrade_to_20
(){
693 echo " Add 'image_size' to 'instance_devices'"
694 echo "ALTER TABLE instance_devices ADD COLUMN image_size INT NULL DEFAULT NULL AFTER dev;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
695 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (20, '0.20', '0.5.17', 'Add image_size to instance_devices', '2017-06-01');"\
696 |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
701 function downgrade_from_20
(){
702 echo " Delete 'image_size' from 'instance_devices'"
703 echo "ALTER TABLE instance_devices DROP COLUMN image_size;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
704 echo "DELETE FROM schema_version WHERE version_int = '20';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
707 function upgrade_to_21
(){
708 echo " Add 'routes', 'links' and 'dns' to 'nets'"
709 echo "ALTER TABLE nets ADD COLUMN dns VARCHAR(255) NULL AFTER gateway_ip,
710 ADD COLUMN links TEXT(2000) NULL AFTER dns,
711 ADD COLUMN routes TEXT(2000) NULL AFTER links;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
712 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (21, '0.21', '0.5.18', 'Add routes, links and dns to inets', '2017-06-21');"\
713 |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
716 function downgrade_from_21
(){
717 echo " Delete 'routes', 'links' and 'dns' to 'nets'"
718 echo "ALTER TABLE nets DROP COLUMN dns, DROP COLUMN links, DROP COLUMN routes;" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
719 echo "DELETE FROM schema_version WHERE version_int = '21';" |
$DBCMD ||
! echo "ERROR. Aborted!" ||
exit -1
723 #TODO ... put funtions here
725 # echo "db version = "${DATABASE_VER_NUM}
726 [ $DB_VERSION -eq $DATABASE_VER_NUM ] && echo " current database version '$DATABASE_VER_NUM' is ok"
727 #UPGRADE DATABASE step by step
728 while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
730 echo " upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
731 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM
+1))
732 upgrade_to_
${DATABASE_VER_NUM}
733 #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
734 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
735 #$FILE_ || exit -1 # if fail return
738 #DOWNGRADE DATABASE step by step
739 while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
741 echo " downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
742 #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
743 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
744 #$FILE_ || exit -1 # if fail return
745 downgrade_from_
${DATABASE_VER_NUM}
746 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM-1
))