changed/added license heading. Moved some json files to yaml
[osm/openvim.git] / database_utils / migrate_vim_db.sh
1 #!/bin/bash
2
3 ##
4 # Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
5 # This file is part of openvim
6 # All Rights Reserved.
7 #
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
11 #
12 # http://www.apache.org/licenses/LICENSE-2.0
13 #
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
18 # under the License.
19 #
20 # For those usages not covered by the Apache License, Version 2.0 please
21 # contact with: nfvlabs@tid.es
22 ##
23
24 #
25 #Upgrade/Downgrade openvim database preserving the content
26 #
27
28 DBUSER="vim"
29 DBPASS=""
30 DBHOST="localhost"
31 DBPORT="3306"
32 DBNAME="vim_db"
33
34 # Detect paths
35 MYSQL=$(which mysql)
36 AWK=$(which awk)
37 GREP=$(which grep)
38 DIRNAME=`dirname $0`
39
40 function usage(){
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"
44 echo -e " OPTIONS"
45 echo -e " -u USER database user. '$DBUSER' by default. Prompts if DB access fails"
46 echo -e " -p PASS database password. 'No password' by default. Prompts if DB access fails"
47 echo -e " -P PORT database port. '$DBPORT' by default"
48 echo -e " -h HOST database host. '$DBHOST' by default"
49 echo -e " -d NAME database name. '$DBNAME' by default. Prompts if DB access fails"
50 echo -e " --help shows this help"
51 }
52
53 while getopts ":u:p:P:h:d:-:" o; do
54 case "${o}" in
55 u)
56 DBUSER="$OPTARG"
57 ;;
58 p)
59 DBPASS="$OPTARG"
60 ;;
61 P)
62 DBPORT="$OPTARG"
63 ;;
64 d)
65 DBNAME="$OPTARG"
66 ;;
67 h)
68 DBHOST="$OPTARG"
69 ;;
70 -)
71 [ "${OPTARG}" == "help" ] && usage && exit 0
72 echo "Invalid option: --$OPTARG" >&2 && usage >&2
73 exit 1
74 ;;
75 \?)
76 echo "Invalid option: -$OPTARG" >&2 && usage >&2
77 exit 1
78 ;;
79 :)
80 echo "Option -$OPTARG requires an argument." >&2 && usage >&2
81 exit 1
82 ;;
83 *)
84 usage >&2
85 exit -1
86 ;;
87 esac
88 done
89 shift $((OPTIND-1))
90
91
92 #GET OPENVIM VERSION
93 OPENVIM_VER="$1"
94 if [ -z "$OPENVIM_VER" ]
95 then
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"
100 fi
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
105 then
106 [ -n "$1" ] && echo "Invalid openvim version '$1', expected 'X.X.X'" >&2
107 [ -z "$1" ] && echo "Can not get openvim version" >&2
108 exit -1
109 fi
110 OPENVIM_VER_NUM=`printf "%d%03d%03d" ${VERSION_1} ${VERSION_2} ${VERSION_3}`
111
112 #check and ask for database user password
113 DBUSER_="-u$DBUSER"
114 [ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
115 DBHOST_="-h$DBHOST"
116 DBPORT_="-P$DBPORT"
117 while ! echo ";" | mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME >/dev/null 2>&1
118 do
119 [ -n "$logintry" ] && echo -e "\nInvalid database credentials!!!. Try again (Ctrl+c to abort)"
120 [ -z "$logintry" ] && echo -e "\nProvide database name and credentials"
121 read -e -p "mysql database name($DBNAME): " KK
122 [ -n "$KK" ] && DBNAME="$KK"
123 read -e -p "mysql user($DBUSER): " KK
124 [ -n "$KK" ] && DBUSER="$KK" && DBUSER_="-u$DBUSER"
125 read -e -s -p "mysql password: " DBPASS
126 [ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
127 [ -z "$DBPASS" ] && DBPASS_=""
128 logintry="yes"
129 echo
130 done
131
132 DBCMD="mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME"
133 #echo DBCMD $DBCMD
134
135 #GET DATABASE VERSION
136 #check that the database seems a openvim database
137 if ! echo -e "show create table instances;\nshow create table numas" | $DBCMD >/dev/null 2>&1
138 then
139 echo " database $DBNAME does not seem to be an openvim database" >&2
140 exit -1;
141 fi
142
143 if ! echo 'show create table schema_version;' | $DBCMD >/dev/null 2>&1
144 then
145 DATABASE_VER="0.0"
146 DATABASE_VER_NUM=0
147 else
148 DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
149 DATABASE_VER=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
150 [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] && echo " Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
151 #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
152 fi
153
154
155 #GET DATABASE TARGET VERSION
156 DATABASE_TARGET_VER_NUM=0
157 [ $OPENVIM_VER_NUM -gt 1091 ] && DATABASE_TARGET_VER_NUM=1 #>0.1.91 => 1
158 [ $OPENVIM_VER_NUM -ge 2003 ] && DATABASE_TARGET_VER_NUM=2 #0.2.03 => 2
159 [ $OPENVIM_VER_NUM -ge 2005 ] && DATABASE_TARGET_VER_NUM=3 #0.2.5 => 3
160 [ $OPENVIM_VER_NUM -ge 3001 ] && DATABASE_TARGET_VER_NUM=4 #0.3.1 => 4
161 [ $OPENVIM_VER_NUM -ge 4001 ] && DATABASE_TARGET_VER_NUM=5 #0.4.1 => 5
162 [ $OPENVIM_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM=6 #0.4.2 => 6
163 [ $OPENVIM_VER_NUM -ge 4005 ] && DATABASE_TARGET_VER_NUM=7 #0.4.5 => 7
164 #TODO ... put next versions here
165
166
167 function upgrade_to_1(){
168 echo " upgrade database from version 0.0 to version 0.1"
169 echo " CREATE TABLE \`schema_version\`"
170 echo "CREATE TABLE \`schema_version\` (
171 \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
172 \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
173 \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
174 \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
175 \`date\` DATE NULL,
176 PRIMARY KEY (\`version_int\`)
177 )
178 COMMENT='database schema control version'
179 COLLATE='utf8_general_ci'
180 ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
181 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
182 VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" | $DBCMD
183 echo " ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
184 echo "ALTER TABLE \`nets\`
185 ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
186 }
187 function downgrade_from_1(){
188 echo " downgrade database from version 0.1 to version 0.0"
189 echo " ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
190 echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
191 echo " DROP TABLE \`schema_version\`"
192 echo "DROP TABLE \`schema_version\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
193 }
194 function upgrade_to_2(){
195 echo " upgrade database from version 0.1 to version 0.2"
196 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
197 for table in of_ports_pci_correspondence resources_port ports
198 do
199 echo "ALTER TABLE \`${table}\`
200 ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
201 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
202 [ $table == of_ports_pci_correspondence ] ||
203 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
204 done
205 echo " UPDATE procedure UpdateSwitchPort"
206 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
207 delimiter //
208 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
209 COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
210 BEGIN
211 #DELETES switch_port entry before writing, because if not it fails for key constrains
212 UPDATE ports
213 RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
214 INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
215 INNER JOIN numas on RP.numa_id=numas.id
216 INNER JOIN hosts on numas.host_id=hosts.uuid
217 INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
218 SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
219 #write switch_port into resources_port and ports
220 UPDATE ports
221 RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
222 INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
223 INNER JOIN numas on RP.numa_id=numas.id
224 INNER JOIN hosts on numas.host_id=hosts.uuid
225 INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
226 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;
227 END//
228 delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
229 echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
230 VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
231 }
232 function upgrade_to_3(){
233 echo " upgrade database from version 0.2 to version 0.3"
234 echo " change size of source_name at table resources_port"
235 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
236 echo " CREATE PROCEDURE GetAllAvailablePorts"
237 echo "delimiter //
238 CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
239 COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
240 BEGIN
241 SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
242 FROM
243 (
244 SELECT id as port_id, Mbps, pci, switch_port, mac
245 FROM resources_port
246 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
247 ) as A
248 INNER JOIN
249 (
250 SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
251 FROM resources_port
252 WHERE numa_id = Numa AND status = 'ok'
253 GROUP BY root_id
254 ) as B
255 ON A.port_id = B.root_id
256 LEFT JOIN
257 (
258 SELECT root_id, COUNT(id) as usedSRIOV
259 FROM resources_port
260 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
261 GROUP BY root_id
262 ) as C
263 ON A.port_id = C.root_id
264 ORDER BY Mbps_free, availableSRIOV, pci;
265 END//
266 delimiter ;"| $DBCMD || ! ! echo "ERROR. Aborted!" || exit -1
267 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
268 }
269
270 function upgrade_to_4(){
271 echo " upgrade database from version 0.3 to version 0.4"
272 echo " remove unique VLAN index at 'resources_port', 'ports'"
273 echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
274 echo "ALTER TABLE ports DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
275 echo " change table 'ports'"
276 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
277 echo "ALTER TABLE ports DROP COLUMN vlan_changed;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
278 echo "ALTER TABLE resources_port DROP COLUMN vlan;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
279 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
280 }
281
282 function upgrade_to_X(){
283 #TODO, this change of foreign key does not work
284 echo " upgrade database from version 0.X to version 0.X"
285 echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
286 DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
287 echo "ALTER TABLE instances
288 ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
289 ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
290 }
291
292 function downgrade_from_2(){
293 echo " downgrade database from version 0.2 to version 0.1"
294 echo " UPDATE procedure UpdateSwitchPort"
295 echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
296 delimiter //
297 CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
298 BEGIN
299 UPDATE
300 resources_port INNER JOIN (
301 SELECT resources_port.id,KK.switch_port
302 FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
303 INNER JOIN hosts on numas.host_id=hosts.uuid
304 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
305 ) as TABLA
306 ON resources_port.root_id=TABLA.id
307 SET resources_port.switch_port=TABLA.switch_port
308 WHERE resources_port.root_id=TABLA.id;
309 END//
310 delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
311 echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
312 for table in of_ports_pci_correspondence resources_port ports
313 do
314 [ $table == of_ports_pci_correspondence ] ||
315 echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
316 echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
317 switch_port_size=12
318 [ $table == of_ports_pci_correspondence ] && switch_port_size=50
319 echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
320 done
321 echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
322 }
323 function downgrade_from_3(){
324 echo " downgrade database from version 0.3 to version 0.2"
325 echo " change back size of source_name at table resources_port"
326 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
327 echo " DROP PROCEDURE GetAllAvailablePorts"
328 echo "DROP PROCEDURE GetAllAvailablePorts;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
329 echo "DELETE FROM schema_version WHERE version_int = '3';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
330 }
331 function downgrade_from_4(){
332 echo " downgrade database from version 0.4 to version 0.3"
333 echo " adding back unique index VLAN at 'resources_port','ports'"
334 echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER Mbps_used;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
335 echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
336 echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
337 echo "ALTER TABLE ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
338 echo " change back table 'ports'"
339 echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
340 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
341 echo "DELETE FROM schema_version WHERE version_int = '4';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
342 }
343
344
345 function upgrade_to_5(){
346 echo " upgrade database from version 0.4 to version 0.5"
347 echo " add 'ip_address' to ports'"
348 echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
349 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
350 }
351 function downgrade_from_5(){
352 echo " downgrade database from version 0.5 to version 0.4"
353 echo " removing 'ip_address' from 'ports'"
354 echo "ALTER TABLE ports DROP COLUMN ip_address;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
355 echo "DELETE FROM schema_version WHERE version_int = '5';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
356 }
357
358 function upgrade_to_6(){
359 echo " upgrade database from version 0.5 to version 0.6"
360 echo " Change enalarge name, description to 255 at all database"
361 for table in flavors images instances tenants
362 do
363 name_length=255
364 [[ $table == tenants ]] || name_length=64
365 echo -en " $table \r"
366 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
367 done
368 echo -en " hosts \r"
369 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
370 echo -en " nets \r"
371 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
372 echo -en " instances \r"
373 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
374 echo -en " ports \r"
375 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
376 echo -en " of_flows \r"
377 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
378 echo -en " of_ports_pci_cor... \r"
379 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
380 echo -en " resources_port \r"
381 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
382 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
383 }
384 function downgrade_from_6(){
385 echo " downgrade database from version 0.6 to version 0.5"
386 echo " Change back name,description to shorter length at all database"
387 for table in flavors images instances tenants
388 do
389 name_length=50
390 [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
391 echo -en " $table \r"
392 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
393 done
394 echo -en " hosts \r"
395 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
396 echo -en " nets \r"
397 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
398 echo -en " instances \r"
399 echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
400 echo -en " ports \r"
401 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
402 echo -en " of_flows \r"
403 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
404 echo -en " of_ports_pci_cor... \r"
405 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
406 echo -en " resources_port \r"
407 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
408 echo "DELETE FROM schema_version WHERE version_int='6';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
409 }
410 function upgrade_to_7(){
411 echo " upgrade database from version 0.6 to version 0.7"
412 echo " add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
413 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
414 echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
415 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
416 echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (7, '0.7', '0.4.4', 'Add bind_net to net table', '2016-02-12');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
417 }
418 function downgrade_from_7(){
419 echo " downgrade database from version 0.7 to version 0.6"
420 echo " removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
421 echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
422 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
423 echo "DELETE FROM schema_version WHERE version_int = '7';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
424 }
425 #TODO ... put funtions here
426
427
428 [ $DATABASE_TARGET_VER_NUM -eq $DATABASE_VER_NUM ] && echo " current database version $DATABASE_VER is ok"
429 #UPGRADE DATABASE step by step
430 while [ $DATABASE_TARGET_VER_NUM -gt $DATABASE_VER_NUM ]
431 do
432 DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
433 upgrade_to_${DATABASE_VER_NUM}
434 #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
435 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
436 #$FILE_ || exit -1 # if fail return
437 done
438
439 #DOWNGRADE DATABASE step by step
440 while [ $DATABASE_TARGET_VER_NUM -lt $DATABASE_VER_NUM ]
441 do
442 #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
443 #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
444 #$FILE_ || exit -1 # if fail return
445 downgrade_from_${DATABASE_VER_NUM}
446 DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
447 done
448
449 #echo done
450