4 # Copyright 2015 Telefonica Investigacion 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
27 DBUTILS
="$(cd "$
(dirname "${BASH_SOURCE[0]}")" && pwd)"
31 DEFAULT_DBPASS
="vimpw"
38 #TODO update it with the last database version
47 echo -e "Usage: $0 OPTIONS [version]"
48 echo -e " Upgrades/Downgrades openvim database preserving the content."\
49 "If [version] is not provided, it is upgraded to the last version"
51 echo -e " -u USER database user. '$DBUSER' by default. Prompts if DB access fails"
52 echo -e " -p PASS database password. If missing it tries without and '$DEFAULT_DBPASS' password before prompting"
53 echo -e " -P PORT database port. '$DBPORT' by default"
54 echo -e " -h HOST database host. 'localhost' by default"
55 echo -e " -d NAME database name. '$DBNAME' by default. Prompts if DB access fails"
56 echo -e " -b DIR backup folder where to create rollback backup file"
57 echo -e " -q --quiet: Do not prompt for credentials and exit if cannot access to database"
58 echo -e " --help shows this help"
61 while getopts ":u:p:b:P:h:d:q-:" o
; do
85 [ "${OPTARG}" == "help" ] && usage
&& exit 0
86 [ "${OPTARG}" == "quiet" ] && export QUIET_MODE
=yes && continue
87 echo "Invalid option: '--$OPTARG'. Type --help for more information" >&2
91 echo "Invalid option: '-$OPTARG'. Type --help for more information" >&2
95 echo "Option '-$OPTARG' requires an argument. Type --help for more information" >&2
108 if [ -n "$DB_VERSION" ] ; then
109 # check it is a number and an allowed one
110 [ "$DB_VERSION" -eq "$DB_VERSION" ] 2>/dev
/null ||
111 ! echo "parameter 'version' requires a integer value" >&2 ||
exit 1
112 if [ "$DB_VERSION" -lt 0 ] ||
[ "$DB_VERSION" -gt "$LAST_DB_VERSION" ] ; then
113 echo "parameter 'version' requires a valid database version between '0' and '$LAST_DB_VERSION'"\
114 "If you need an upper version, get a newer version of this script '$0'" >&2
118 DB_VERSION
="$LAST_DB_VERSION"
121 # Creating temporary file
122 TEMPFILE
="$(mktemp -q --tmpdir "migratevimdb.XXXXXX
")"
123 trap 'rm -f "$TEMPFILE"' EXIT
124 chmod 0600 "$TEMPFILE"
125 DEF_EXTRA_FILE_PARAM
="--defaults-extra-file=$TEMPFILE"
126 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
128 # Check and ask for database user password
130 while ! DB_ERROR
=`mysql "$DEF_EXTRA_FILE_PARAM" $DBNAME -e "quit" 2>&1 >/dev/null`
132 # if password is not provided, try silently with $DEFAULT_DBPASS before exit or prompt for credentials
133 [[ -n "$FIRST_TRY" ]] && [[ -z "$DBPASS" ]] && DBPASS
="$DEFAULT_DBPASS" &&
134 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE" &&
137 [[ -n "$QUIET_MODE" ]] && echo -e "Invalid database credentials!!!" >&2 && exit 1
138 echo -e "Provide database name and credentials (Ctrl+c to abort):"
139 read -e -p " mysql database name($DBNAME): " KK
140 [ -n "$KK" ] && DBNAME
="$KK"
141 read -e -p " mysql user($DBUSER): " KK
142 [ -n "$KK" ] && DBUSER
="$KK"
143 read -e -s -p " mysql password: " DBPASS
144 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
149 DBCMD
="mysql $DEF_EXTRA_FILE_PARAM $DBNAME"
152 # check that the database seems a openvim database
153 if ! echo -e "show create table instances;\nshow create table numas" |
$DBCMD >/dev
/null
2>&1
155 echo " database $DBNAME does not seem to be an openvim database" >&2
159 #GET DATABASE TARGET VERSION
161 #[ $OPENVIM_VER_NUM -gt 1091 ] && DATABASE_TARGET_VER_NUM=1 #>0.1.91 => 1
162 #[ $OPENVIM_VER_NUM -ge 2003 ] && DATABASE_TARGET_VER_NUM=2 #0.2.03 => 2
163 #[ $OPENVIM_VER_NUM -ge 2005 ] && DATABASE_TARGET_VER_NUM=3 #0.2.5 => 3
164 #[ $OPENVIM_VER_NUM -ge 3001 ] && DATABASE_TARGET_VER_NUM=4 #0.3.1 => 4
165 #[ $OPENVIM_VER_NUM -ge 4001 ] && DATABASE_TARGET_VER_NUM=5 #0.4.1 => 5
166 #[ $OPENVIM_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM=6 #0.4.2 => 6
167 #[ $OPENVIM_VER_NUM -ge 4005 ] && DATABASE_TARGET_VER_NUM=7 #0.4.5 => 7
168 #[ $OPENVIM_VER_NUM -ge 4010 ] && DATABASE_TARGET_VER_NUM=8 #0.4.10 => 8
169 #[ $OPENVIM_VER_NUM -ge 5001 ] && DATABASE_TARGET_VER_NUM=9 #0.5.1 => 9
170 #[ $OPENVIM_VER_NUM -ge 5002 ] && DATABASE_TARGET_VER_NUM=10 #0.5.2 => 10
171 #[ $OPENVIM_VER_NUM -ge 5004 ] && DATABASE_TARGET_VER_NUM=11 #0.5.4 => 11
172 #[ $OPENVIM_VER_NUM -ge 5005 ] && DATABASE_TARGET_VER_NUM=12 #0.5.5 => 12
173 #[ $OPENVIM_VER_NUM -ge 5006 ] && DATABASE_TARGET_VER_NUM=13 #0.5.6 => 13
174 #[ $OPENVIM_VER_NUM -ge 5007 ] && DATABASE_TARGET_VER_NUM=14 #0.5.7 => 14
175 #[ $OPENVIM_VER_NUM -ge 5008 ] && DATABASE_TARGET_VER_NUM=15 #0.5.8 => 15
176 #[ $OPENVIM_VER_NUM -ge 5009 ] && DATABASE_TARGET_VER_NUM=16 #0.5.9 => 16
177 #[ $OPENVIM_VER_NUM -ge 5010 ] && DATABASE_TARGET_VER_NUM=17 #0.5.10 => 17
178 #[ $OPENVIM_VER_NUM -ge 5013 ] && DATABASE_TARGET_VER_NUM=18 #0.5.13 => 18
179 #[ $OPENVIM_VER_NUM -ge 5015 ] && DATABASE_TARGET_VER_NUM=19 #0.5.15 => 19
180 #[ $OPENVIM_VER_NUM -ge 5017 ] && DATABASE_TARGET_VER_NUM=20 #0.5.17 => 20
181 #[ $OPENVIM_VER_NUM -ge 5018 ] && DATABASE_TARGET_VER_NUM=21 #0.5.18 => 21
182 #[ $OPENVIM_VER_NUM -ge 5021 ] && DATABASE_TARGET_VER_NUM=22 #0.5.21 => 22
183 #[ $OPENVIM_VER_NUM -ge 5024 ] && DATABASE_TARGET_VER_NUM=23 #0.5.24 => 23
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 sql
"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'
200 sql
"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');"
202 echo " ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
203 sql
"ALTER TABLE \`nets\` ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;"
205 function downgrade_from_1
(){
206 # echo " downgrade database from version 0.1 to version 0.0"
207 echo " ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
208 sql
"ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;"
209 echo " DROP TABLE \`schema_version\`"
210 sql
"DROP TABLE \`schema_version\`;"
212 function upgrade_to_2
(){
213 # echo " upgrade database from version 0.1 to version 0.2"
214 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
215 for table
in of_ports_pci_correspondence resources_port ports
217 sql
"ALTER TABLE \`${table}\` "\
218 "ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; "
219 sql
"ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;"
220 [ $table == of_ports_pci_correspondence
] ||
221 sql
"ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port "\
222 "(vlan, switch_port, switch_dpid);"
224 echo " UPDATE procedure UpdateSwitchPort"
225 sql
"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, "\
246 "RP.switch_dpid=PC.switch_dpid;
249 sql
"INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`) "\
250 "VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');"
252 function upgrade_to_3
(){
253 # echo " upgrade database from version 0.2 to version 0.3"
254 echo " change size of source_name at table resources_port"
255 sql
"ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL AFTER port_id;"
256 echo " CREATE PROCEDURE GetAllAvailablePorts"
258 CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
259 COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
261 SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as
262 availableSRIOV, switch_port, mac
265 SELECT id as port_id, Mbps, pci, switch_port, mac
267 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
271 SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
273 WHERE numa_id = Numa AND status = 'ok'
276 ON A.port_id = B.root_id
279 SELECT root_id, COUNT(id) as usedSRIOV
281 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
284 ON A.port_id = C.root_id
285 ORDER BY Mbps_free, availableSRIOV, pci;
288 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
289 "VALUES (3, '0.3', '0.2.5', 'New Procedure GetAllAvailablePorts', '2015-07-09');"
292 function upgrade_to_4
(){
293 # echo " upgrade database from version 0.3 to version 0.4"
294 echo " remove unique VLAN index at 'resources_port', 'ports'"
295 sql
"ALTER TABLE resources_port DROP INDEX vlan_switch_port;"
296 sql
"ALTER TABLE ports DROP INDEX vlan_switch_port;"
297 echo " change table 'ports'"
298 sql
"ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL COMMENT 'driver model for bridge "\
299 "ifaces; PF,VF,VFnotShared for data ifaces' AFTER mac;"
300 sql
"ALTER TABLE ports DROP COLUMN vlan_changed;"
301 sql
"ALTER TABLE resources_port DROP COLUMN vlan;"
302 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
303 "VALUES (4, '0.4', '0.3.1', 'Remove unique index VLAN at resources_port', '2015-09-04');"
306 function upgrade_to_X
(){
307 #TODO, this change of foreign key does not work
308 # echo " upgrade database from version 0.X to version 0.X"
309 sql
"ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors, "\
310 "DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"
311 sql
"ALTER TABLE instances ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) "\
312 "REFERENCES tenants_flavors (flavor_id, tenant_id), ADD CONSTRAINT FK_instances_images FOREIGN KEY "\
313 "(image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);"
316 function downgrade_from_2
(){
317 # echo " downgrade database from version 0.2 to version 0.1"
318 echo " UPDATE procedure UpdateSwitchPort"
319 sql
"DROP PROCEDURE IF EXISTS UpdateSwitchPort;
321 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
324 resources_port INNER JOIN (
325 SELECT resources_port.id,KK.switch_port
326 FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
327 INNER JOIN hosts on numas.host_id=hosts.uuid
328 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
330 ON resources_port.root_id=TABLA.id
331 SET resources_port.switch_port=TABLA.switch_port
332 WHERE resources_port.root_id=TABLA.id;
335 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
336 for table
in of_ports_pci_correspondence resources_port ports
338 [ $table == of_ports_pci_correspondence
] ||
339 sql
"ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);"
340 sql
"ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;"
342 [ $table == of_ports_pci_correspondence
] && switch_port_size
=50
343 sql
"ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;"
345 sql
"DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';"
347 function downgrade_from_3
(){
348 # echo " downgrade database from version 0.3 to version 0.2"
349 echo " change back size of source_name at table resources_port"
350 sql
"ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(20) NULL DEFAULT NULL AFTER port_id;"
351 echo " DROP PROCEDURE GetAllAvailablePorts"
352 sql
"DROP PROCEDURE GetAllAvailablePorts;"
353 sql
"DELETE FROM schema_version WHERE version_int = '3';"
355 function downgrade_from_4
(){
356 # echo " downgrade database from version 0.4 to version 0.3"
357 echo " adding back unique index VLAN at 'resources_port','ports'"
358 sql
"ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER Mbps_used;"
359 sql
"UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"
360 sql
"ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);"
361 sql
"ALTER TABLE ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);"
362 echo " change back table 'ports'"
363 sql
"ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"
364 sql
"ALTER TABLE ports ADD COLUMN vlan_changed SMALLINT(5) NULL DEFAULT NULL COMMENT '!=NULL when original vlan "\
365 "have been changed to match a pmp net with all ports in the same vlan' AFTER switch_port;"
366 sql
"DELETE FROM schema_version WHERE version_int = '4';"
370 function upgrade_to_5
(){
371 # echo " upgrade database from version 0.4 to version 0.5"
372 echo " add 'ip_address' to ports'"
373 sql
"ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;"
374 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
375 "VALUES (5, '0.5', '0.4.1', 'Add ip_address to ports', '2015-09-04');"
377 function downgrade_from_5
(){
378 # echo " downgrade database from version 0.5 to version 0.4"
379 echo " removing 'ip_address' from 'ports'"
380 sql
"ALTER TABLE ports DROP COLUMN ip_address;"
381 sql
"DELETE FROM schema_version WHERE version_int = '5';"
384 function upgrade_to_6
(){
385 # echo " upgrade database from version 0.5 to version 0.6"
386 echo " Enlarge name, description to 255 at all database"
387 for table
in flavors images instances tenants
390 [[ $table == tenants
]] || name_length
=64
391 echo -en " $table \r"
392 sql
"ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description "\
393 "description VARCHAR(255) NULL DEFAULT NULL;"
396 sql
"ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(64) "\
397 "NOT NULL, CHANGE COLUMN user user VARCHAR(64) NOT NULL, CHANGE COLUMN password password VARCHAR(64) NULL "\
398 "DEFAULT NULL, CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN features "\
399 "features VARCHAR(255) NULL DEFAULT NULL;"
401 sql
"ALTER TABLE nets CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN last_error last_error "\
402 "VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;"
403 echo -en " instances \r"
404 sql
"ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;"
406 sql
"ALTER TABLE ports CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN switch_port switch_port "\
407 "VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(64) NULL DEFAULT NULL;"
408 echo -en " of_flows \r"
409 sql
"ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN net_id net_id VARCHAR(36) "\
410 "NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(255) NULL DEFAULT NULL;"
411 echo -en " of_ports_pci_cor... \r"
412 sql
"ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(64) NULL DEFAULT NULL, CHANGE "\
413 "COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
414 "VARCHAR(64) NULL DEFAULT NULL;"
415 echo -en " resources_port \r"
416 sql
"ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(64) NULL DEFAULT NULL, CHANGE "\
417 "COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
418 "VARCHAR(64) NULL DEFAULT NULL;"
419 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES "\
420 "(6, '0.6', '0.4.2', 'Enlarging name at database', '2016-02-01');"
422 function downgrade_from_6
(){
423 # echo " downgrade database from version 0.6 to version 0.5"
424 echo " Change back name,description to shorter length at all database"
425 for table
in flavors images instances tenants
428 [[ $table == flavors
]] ||
[[ $table == images
]] || name_length
=36
429 echo -en " $table \r"
430 sql
"ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description "\
431 "description VARCHAR(100) NULL DEFAULT NULL;"
434 sql
"ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(36) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(36) "\
435 "NOT NULL, CHANGE COLUMN user user VARCHAR(36) NOT NULL, CHANGE COLUMN password password VARCHAR(36) NULL "\
436 "DEFAULT NULL, CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL, CHANGE COLUMN features "\
437 "features VARCHAR(50) NULL DEFAULT NULL;"
439 sql
"ALTER TABLE nets CHANGE COLUMN name name VARCHAR(50) NOT NULL, CHANGE COLUMN last_error last_error "\
440 "VARCHAR(200) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;"
441 echo -en " instances \r"
442 sql
"ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;"
444 sql
"ALTER TABLE ports CHANGE COLUMN name name VARCHAR(25) NULL DEFAULT NULL, CHANGE COLUMN switch_port "\
445 "switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(23) NULL DEFAULT NULL;"
446 echo -en " of_flows \r"
447 sql
"ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN net_id net_id "\
448 "VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(100) NULL DEFAULT NULL;"
449 echo -en " of_ports_pci_cor... \r"
450 sql
"ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(50) NULL DEFAULT NULL, CHANGE "\
451 "COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
452 "VARCHAR(23) NULL DEFAULT NULL;"
453 echo -en " resources_port \r"
454 sql
"ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL, CHANGE "\
455 "COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
456 "VARCHAR(23) NULL DEFAULT NULL;"
457 sql
"DELETE FROM schema_version WHERE version_int='6';"
460 function upgrade_to_7
(){
461 # echo " upgrade database from version 0.6 to version 0.7"
462 echo " add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
463 sql
"ALTER TABLE nets ADD COLUMN cidr VARCHAR(64) NULL DEFAULT NULL AFTER bind, ADD COLUMN enable_dhcp "\
464 "ENUM('true','false') NOT NULL DEFAULT 'false' after cidr, ADD COLUMN dhcp_first_ip VARCHAR(64) NULL "\
465 "DEFAULT NULL AFTER enable_dhcp, ADD COLUMN dhcp_last_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_first_ip;"
466 sql
"ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;"
467 sql
"ALTER TABLE nets ADD COLUMN bind_net VARCHAR(36) NULL DEFAULT NULL COMMENT 'To connect with other net' "\
468 "AFTER provider, ADD COLUMN bind_type VARCHAR(36) NULL DEFAULT NULL COMMENT 'VLAN:<tag> to insert/remove' "\
470 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
471 "VALUES (7, '0.7', '0.4.5', 'Add bind_net to net table', '2016-02-12');"
473 function downgrade_from_7
(){
474 # echo " downgrade database from version 0.7 to version 0.6"
475 echo " removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
476 sql
"ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;"
477 sql
"ALTER TABLE nets DROP COLUMN cidr, DROP COLUMN enable_dhcp, DROP COLUMN bind_net, DROP COLUMN bind_type, "\
478 "DROP COLUMN dhcp_first_ip, DROP COLUMN dhcp_last_ip;"
479 sql
"DELETE FROM schema_version WHERE version_int = '7';"
482 function upgrade_to_8
(){
483 # echo " upgrade database from version 0.7 to version 0.8"
484 echo " add column 'checksum' to 'images'"
485 sql
"ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;"
486 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
487 "VALUES (8, '0.8', '0.4.10', 'add column checksum to images', '2016-09-30');"
489 function downgrade_from_8
(){
490 # echo " downgrade database from version 0.8 to version 0.7"
491 echo " remove column 'checksum' from 'images'"
492 sql
"ALTER TABLE images DROP COLUMN checksum;"
493 sql
"DELETE FROM schema_version WHERE version_int = '8';"
496 function upgrade_to_9
(){
497 # echo " upgrade database from version 0.8 to version 0.9"
498 echo " change length of columns 'path' and 'name' to 255 in table 'images', and change length of column "\
499 "'name' to 255 in table 'flavors'"
500 sql
"ALTER TABLE images CHANGE COLUMN path path VARCHAR(255) NOT NULL AFTER uuid, CHANGE COLUMN name name "\
501 "VARCHAR(255) NOT NULL AFTER path;"
502 sql
"ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;"
503 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
504 "VALUES (9, '0.9', '0.5.1','increase length of columns path and name to 255 in table images, "\
505 "and change length of column name to 255 in table flavors', '2017-01-10');"
507 function downgrade_from_9
(){
508 # echo " downgrade database from version 0.9 to version 0.8"
509 echo " change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
510 sql
"ALTER TABLE images CHANGE COLUMN path path VARCHAR(100) NOT NULL AFTER uuid, CHANGE COLUMN name name "\
511 "VARCHAR(64) NOT NULL AFTER path;"
512 sql
"ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;"
513 sql
"DELETE FROM schema_version WHERE version_int = '9';"
516 function upgrade_to_10
(){
517 # echo " upgrade database from version 0.9 to version 0.10"
518 echo " change types at 'ports'"
519 sql
"ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external','instance:ovs',"\
520 "'controller:ovs') NOT NULL DEFAULT 'instance:bridge' AFTER status;"
521 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
522 "VALUES (10, '0.10', '0.5.2', 'change ports type, adding instance:ovs', '2017-02-01');"
524 function downgrade_from_10
(){
525 # echo " downgrade database from version 0.10 to version 0.9"
526 echo " change back types at 'ports'"
527 sql
"ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external') "\
528 "NOT NULL DEFAULT 'instance:bridge' AFTER status;"
529 sql
"DELETE FROM schema_version WHERE version_int = '10';"
532 function upgrade_to_11
(){
533 # echo " upgrade database from version 0.10 to version 0.11"
534 echo " Add gateway_ip colum to 'nets'"
535 sql
"ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;"
536 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
537 "VALUES (11, '0.11', '0.5.4', 'Add gateway_ip colum to nets', '2017-02-13');"
539 function downgrade_from_11
(){
540 # echo " downgrade database from version 0.11 to version 0.10"
541 echo " Delete gateway_ip colum from 'nets'"
542 sql
"ALTER TABLE nets DROP COLUMN gateway_ip;"
543 sql
"DELETE FROM schema_version WHERE version_int = '11';"
545 function upgrade_to_12
(){
546 # echo " upgrade database from version 0.11 to version 0.12"
547 echo " Create of_controller table "
548 sql
"CREATE TABLE ofcs (
549 uuid VARCHAR(36) NOT NULL,
550 name VARCHAR(255) NOT NULL,
551 dpid VARCHAR(64) NOT NULL,
552 ip VARCHAR(64) NOT NULL,
553 port INT(5) NOT NULL,
554 type VARCHAR(64) NOT NULL,
555 version VARCHAR(12) NULL DEFAULT NULL,
556 user VARCHAR(64) NULL DEFAULT NULL,
557 password VARCHAR(64) NULL DEFAULT NULL,
560 COLLATE='utf8_general_ci'
562 echo " Modify user_at for uuids table"
563 sql
"ALTER TABLE uuids CHANGE COLUMN used_at used_at VARCHAR(64) NULL DEFAULT NULL "\
564 "COMMENT 'Table that uses this UUID' ;"
565 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
566 "VALUES (12, '0.12', '0.5.5', 'Add of_controller table', '2017-02-17');"
569 function downgrade_from_12
(){
570 # echo " downgrade database from version 0.12 to version 0.11"
571 echo " Delete ofcs table"
572 sql
"DROP TABLE ofcs;"
573 sql
"ALTER TABLE uuids CHANGE COLUMN used_at used_at ENUM('flavors', 'hosts', 'images', 'instances', "\
574 "'nets', 'ports', 'tenants') NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;"
575 sql
"DELETE FROM schema_version WHERE version_int = '12';"
578 function upgrade_to_13
(){
579 # echo " upgrade database from version 0.12 to version 0.13"
580 echo " Create of_port_mapings table "
581 sql
"CREATE TABLE of_port_mappings (
582 uuid VARCHAR(36) NOT NULL,
583 ofc_id VARCHAR(36) NULL DEFAULT NULL,
584 region VARCHAR(64) NULL DEFAULT NULL,
585 compute_node VARCHAR(64) NULL DEFAULT NULL,
586 pci VARCHAR(50) NULL DEFAULT NULL,
587 switch_dpid VARCHAR(64) NULL DEFAULT NULL,
588 switch_port VARCHAR(64) NULL DEFAULT NULL,
589 switch_mac CHAR(18) NULL DEFAULT NULL,
590 UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
591 UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
592 UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
593 INDEX FK_of_port_mappings_ofcs (ofc_id),
594 CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE
596 COLLATE='utf8_general_ci'
598 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
599 "VALUES (13, '0.13', '0.5.6', 'Add of_port_mapings table', '2017-03-09');"
602 function downgrade_from_13
(){
603 # echo " downgrade database from version 0.13 to version 0.12"
604 echo " Delete of_port_mappings table"
605 sql
"DROP TABLE of_port_mappings;"
606 sql
"DELETE FROM schema_version WHERE version_int = '13';"
609 function upgrade_to_14
(){
610 # echo " upgrade database from version 0.13 to version 0.14"
611 echo " Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
612 sql
"ALTER TABLE ports
613 ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
614 ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
615 ADD CONSTRAINT FK_port_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
616 sql
"ALTER TABLE resources_port
617 ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
618 ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
619 ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
620 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
621 "VALUES (14, '0.14', '0.5.7', 'Add switch_mac, ofc_id colum to ports and resources_port tables', '2017-03-09');"
624 function downgrade_from_14
(){
625 # echo " downgrade database from version 0.14 to version 0.13"
626 echo " Delete switch_mac, ofc_id colum to 'ports'"
627 sql
"ALTER TABLE ports DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_port_ofc_id;"
628 sql
"ALTER TABLE resources_port DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_resource_ofc_id;"
629 sql
"DELETE FROM schema_version WHERE version_int = '14';"
632 function upgrade_to_15
(){
633 # echo " upgrade database from version 0.14 to version 0.15"
634 echo " Add ofc_id colum to 'of_flows'"
635 sql
"ALTER TABLE of_flows ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id, ADD CONSTRAINT "\
636 "FK_of_flows_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
637 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
638 "VALUES (15, '0.15', '0.5.8', 'Add ofc_id colum to of_flows', '2017-03-15');"
641 function downgrade_from_15
(){
642 # echo " downgrade database from version 0.15 to version 0.14"
643 echo " Delete ofc_id to 'of_flows'"
644 sql
"ALTER TABLE of_flows DROP COLUMN ofc_id, DROP FOREIGN KEY FK_of_flows_ofcs;"
645 sql
"DELETE FROM schema_version WHERE version_int = '15';"
649 function upgrade_to_16
(){
650 # echo " upgrade database from version 0.15 to version 0.16"
651 echo " Add last_error and status colum to 'ofcs'"
652 sql
"ALTER TABLE ofcs ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password, "\
653 "ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"
654 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
655 "VALUES (16, '0.16', '0.5.9', 'Add last_error and status colum to ofcs', '2017-03-17');"
658 function downgrade_from_16
(){
659 # echo " downgrade database from version 0.16 to version 0.15"
660 echo " Delete last_error and status colum to 'ofcs'"
661 sql
"ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status;"
662 sql
"DELETE FROM schema_version WHERE version_int = '16';"
665 function upgrade_to_17
(){
666 # echo " upgrade database from version 0.16 to version 0.17"
667 echo " Add pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
668 sql
"ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
669 "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);"
670 sql
"ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
671 "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);"
672 echo " Add nets_with_same_vlan to table ofcs"
673 sql
"ALTER TABLE ofcs ADD COLUMN nets_with_same_vlan ENUM('true','false') NOT NULL DEFAULT 'false' AFTER status;"
674 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
675 "VALUES (17, '0.17', '0.5.10', 'Add pci to unique index dpid port/mac at of_port_mappings', '2017-04-05');"
678 function downgrade_from_17
(){
679 # echo " downgrade database from version 0.17 to version 0.16"
680 echo " Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
681 sql
"ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
682 "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);"
683 sql
"ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
684 "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);"
685 echo " Remove nets_with_same_vlan from table ofcs"
686 sql
"ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;"
687 sql
"DELETE FROM schema_version WHERE version_int = '17';"
690 function upgrade_to_18
(){
691 echo " Add 'region' at 'nets' and change unique index vlan+region"
692 sql
"ALTER TABLE nets ADD COLUMN region VARCHAR(64) NULL DEFAULT NULL AFTER admin_state_up, DROP INDEX type_vlan;"
693 echo " Fill 'region' with __OVS__/__DATA__ for OVS/openflow provider at nets"
694 sql
"UPDATE nets set region='__OVS__' where provider like 'OVS%';"
695 sql
"UPDATE nets set region='__DATA__' where type='data' or type='ptp';"
696 echo " Create new index region_vlan at nets"
697 sql
"ALTER TABLE nets ADD UNIQUE INDEX region_vlan (region, vlan);"
698 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
699 "VALUES (18, '0.18', '0.5.13', 'Add region to nets, change vlan unique index', '2017-05-03');"
702 function downgrade_from_18
(){
703 echo " Delete 'region' at 'nets' and change back unique index vlan+type"
704 sql
"ALTER TABLE nets DROP INDEX region_vlan, DROP COLUMN region;"
705 echo " Create back index type_vlan at nets"
706 sql
"ALTER TABLE nets ADD UNIQUE INDEX type_vlan (type, vlan);"
707 sql
"DELETE FROM schema_version WHERE version_int = '18';"
710 function upgrade_to_19
(){
711 echo " Add 'keyfile' to 'hosts'"
712 sql
"ALTER TABLE hosts ADD COLUMN keyfile VARCHAR(255) NULL DEFAULT NULL AFTER password;"
713 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
714 "VALUES (19, '0.19', '0.5.15', 'Add keyfile to hosts', '2017-05-23');"
717 function downgrade_from_19
(){
718 echo " Delete 'keyfile' from 'hosts'"
719 sql
"ALTER TABLE hosts DROP COLUMN keyfile;"
720 sql
"DELETE FROM schema_version WHERE version_int = '19';"
723 function upgrade_to_20
(){
724 echo " Add 'image_size' to 'instance_devices'"
725 sql
"ALTER TABLE instance_devices ADD COLUMN image_size INT NULL DEFAULT NULL AFTER dev;"
726 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
727 "VALUES (20, '0.20', '0.5.17', 'Add image_size to instance_devices', '2017-06-01');"
730 function downgrade_from_20
(){
731 echo " Delete 'image_size' from 'instance_devices'"
732 sql
"ALTER TABLE instance_devices DROP COLUMN image_size;"
733 sql
"DELETE FROM schema_version WHERE version_int = '20';"
736 function upgrade_to_21
(){
737 echo " Add 'routes', 'links' and 'dns' to 'nets'"
738 sql
"ALTER TABLE nets ADD COLUMN dns VARCHAR(255) NULL AFTER gateway_ip, "\
739 "ADD COLUMN links TEXT(2000) NULL AFTER dns, "\
740 "ADD COLUMN routes TEXT(2000) NULL AFTER links;"
741 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
742 "VALUES (21, '0.21', '0.5.18', 'Add routes, links and dns to inets', '2017-06-21');"
745 function downgrade_from_21
(){
746 echo " Delete 'routes', 'links' and 'dns' to 'nets'"
747 sql
"ALTER TABLE nets DROP COLUMN dns, DROP COLUMN links, DROP COLUMN routes;"
748 sql
"DELETE FROM schema_version WHERE version_int = '21';"
751 function upgrade_to_22
(){
752 echo " Changed type of ram in 'flavors' from SMALLINT to MEDIUMINT"
753 sql
"ALTER TABLE flavors CHANGE COLUMN ram ram MEDIUMINT(7) UNSIGNED NULL DEFAULT NULL AFTER disk;"
754 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
755 "VALUES (22, '0.22', '0.5.21', 'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-11-14');"
758 function downgrade_from_22
(){
759 echo " Changed type of ram in 'flavors' from MEDIUMINT to SMALLINT"
760 sql
"ALTER TABLE flavors CHANGE COLUMN ram ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER disk;"
761 sql
"DELETE FROM schema_version WHERE version_int = '22';"
764 function upgrade_to_23
(){
765 echo " Add 'hypervisor' and 'os_type' column to 'instances' table"
766 sql
"ALTER TABLE instances ADD COLUMN hypervisor enum('kvm','xen-unik','xenhvm') NOT NULL DEFAULT 'kvm' AFTER flavor_id;"
767 sql
"ALTER TABLE instances ADD COLUMN os_image_type VARCHAR(24) NOT NULL DEFAULT 'other' AFTER hypervisor;"
768 echo " Add 'hypervisors' column to 'hosts' table"
769 sql
"ALTER TABLE hosts ADD COLUMN hypervisors VARCHAR(255) NOT NULL DEFAULT 'kvm' AFTER features;"
770 sql
"INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
771 "VALUES (23, '0.23', '0.5.24', 'Add hypervisor, os_type to instances and add hypervisors to hosts', '2018-03-20');"
774 function downgrade_from_23
(){
775 echo " Remove 'hypervisor' and 'os_type' column from 'instances' table"
776 sql
"ALTER TABLE instances DROP COLUMN hypervisor;"
777 sql
"ALTER TABLE instances DROP COLUMN os_image_type;"
778 echo " Remove 'hypervisors' column from 'hosts' table"
779 sql
"ALTER TABLE hosts DROP COLUMN hypervisors;"
780 sql
"DELETE FROM schema_version WHERE version_int = '23';"
783 # TODO ... put functions here
786 function del_schema_version_process
()
788 echo "DELETE FROM schema_version WHERE version_int='0';" |
$DBCMD ||
789 ! echo " ERROR writing on schema_version" >&2 ||
exit 1
792 function set_schema_version_process
()
794 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES "\
795 "(0, '0.0', '0.0.0', 'migration from $DATABASE_VER_NUM to $DB_VERSION backup: $BACKUP_FILE',"\
796 "'$(date +%Y-%m-%d)');" |
$DBCMD ||
797 ! echo " Cannot set database at migration process writing into schema_version" >&2 ||
exit 1
801 function rollback_db
()
803 if echo $DATABASE_PROCESS |
grep -q init
; then # Empty database. No backup needed
804 echo " Aborted! Rollback database not needed" && exit 1
805 else # migration a non empty database or Recovering a migration process
806 cat $BACKUP_FILE | mysql
$DEF_EXTRA_FILE_PARAM && echo " Aborted! Rollback database OK" &&
807 del_schema_version_process
&& rm -f "$BACKUP_FILE" && exit 1
808 echo " Aborted! Rollback database FAIL" && exit 1
812 function sql
() # send a sql command
814 echo "$*" |
$DBCMD ||
! echo " ERROR with command '$*'" || rollback_db
820 #UPGRADE DATABASE step by step
821 while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
823 echo " upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
824 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM
+1))
825 upgrade_to_
${DATABASE_VER_NUM}
826 #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
827 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
828 #$FILE_ || exit -1 # if fail return
831 #DOWNGRADE DATABASE step by step
832 while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
834 echo " downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
835 #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
836 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
837 #$FILE_ || exit -1 # if fail return
838 downgrade_from_
${DATABASE_VER_NUM}
839 DATABASE_VER_NUM
=$
((DATABASE_VER_NUM-1
))
844 # check if current database is ok
845 function check_migration_needed
()
847 DATABASE_VER_NUM
=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2` ||
848 ! echo " ERROR cannot read from schema_version" ||
exit 1
850 if [[ -z "$DATABASE_VER_NUM" ]] ||
[[ "$DATABASE_VER_NUM" -lt 0 ]] ||
[[ "$DATABASE_VER_NUM" -gt 100 ]] ; then
851 echo " Error can not get database version ($DATABASE_VER_NUM?)" >&2
855 [[ $DB_VERSION -eq $DATABASE_VER_NUM ]] && echo " current database version '$DATABASE_VER_NUM' is ok" && return 1
856 [[ "$DATABASE_VER_NUM" -gt "$LAST_DB_VERSION" ]] &&
857 echo "Database has been upgraded with a newer version of this script. Use this version to downgrade" >&2 &&
862 DATABASE_PROCESS
=`echo "select comments from schema_version where version_int=0;" | $DBCMD | tail -n+2` ||
863 ! echo " ERROR cannot read from schema_version" ||
exit 1
864 if [[ -z "$DATABASE_PROCESS" ]] ; then # migration a non empty database
865 check_migration_needed ||
exit 0
866 # Create a backup database content
867 [[ -n "$BACKUP_DIR" ]] && BACKUP_FILE
="$(mktemp -q "${BACKUP_DIR}/backupdb.XXXXXX.sql
")"
868 [[ -z "$BACKUP_DIR" ]] && BACKUP_FILE
="$(mktemp -q --tmpdir "backupdb.XXXXXX.sql
")"
869 mysqldump
$DEF_EXTRA_FILE_PARAM --add-drop-table --add-drop-database --routines --databases $DBNAME > $BACKUP_FILE ||
870 ! echo "Cannot create Backup file '$BACKUP_FILE'" >&2 ||
exit 1
871 echo " Backup file '$BACKUP_FILE' created"
873 set_schema_version_process
875 del_schema_version_process
877 elif echo $DATABASE_PROCESS |
grep -q init
; then # Empty database. No backup needed
878 echo " Migrating an empty database"
879 if check_migration_needed
; then
882 del_schema_version_process
884 else # Recover Migration process
885 BACKUP_FILE
=${DATABASE_PROCESS##*backup: }
886 [[ -f "$BACKUP_FILE" ]] ||
! echo "Previous migration process fail and cannot recover backup file '$BACKUP_FILE'" >&2 ||
888 echo " Previous migration was killed. Restoring database from rollback file'$BACKUP_FILE'"
889 cat $BACKUP_FILE | mysql
$DEF_EXTRA_FILE_PARAM ||
! echo " Cannot load backup file '$BACKUP_FILE'" >&2 ||
exit 1
890 if check_migration_needed
; then
891 set_schema_version_process
894 del_schema_version_process