Initial openvim v0.4.6 upload
Signed-off-by: tierno <alfonso.tiernosepulveda@telefonica.com>
diff --git a/database_utils/dump_db.sh b/database_utils/dump_db.sh
new file mode 100755
index 0000000..67d6823
--- /dev/null
+++ b/database_utils/dump_db.sh
@@ -0,0 +1,147 @@
+#!/bin/bash
+
+##
+# Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
+# This file is part of openmano
+# All Rights Reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License"); you may
+# not use this file except in compliance with the License. You may obtain
+# a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+# License for the specific language governing permissions and limitations
+# under the License.
+#
+# For those usages not covered by the Apache License, Version 2.0 please
+# contact with: nfvlabs@tid.es
+##
+
+LICENSE_HEAD='/**
+* Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
+* This file is part of openmano
+* All Rights Reserved.
+*
+* Licensed under the Apache License, Version 2.0 (the "License"); you may
+* not use this file except in compliance with the License. You may obtain
+* a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+* License for the specific language governing permissions and limitations
+* under the License.
+*
+* For those usages not covered by the Apache License, Version 2.0 please
+* contact with: nfvlabs@tid.es
+**/
+'
+
+
+DBUSER="vim"
+DBPASS=""
+DBHOST="localhost"
+DBPORT="3306"
+DBNAME="vim_db"
+
+# Detect paths
+MYSQL=$(which mysql)
+AWK=$(which awk)
+GREP=$(which grep)
+DIRNAME=`dirname $0`
+
+function usage(){
+ echo -e "Usage: $0 OPTIONS"
+ echo -e " Dumps openvim database content"
+ echo -e " OPTIONS"
+ echo -e " -u USER database user. '$DBUSER' by default. Prompts if DB access fails"
+ echo -e " -p PASS database password. 'No password' by default. Prompts if DB access fails"
+ echo -e " -P PORT database port. '$DBPORT' by default"
+ echo -e " -h HOST database host. '$DBHOST' by default"
+ echo -e " -d NAME database name. '$DBNAME' by default. Prompts if DB access fails"
+ echo -e " --help shows this help"
+}
+
+while getopts ":u:p:P:h:-:" o; do
+ case "${o}" in
+ u)
+ DBUSER="$OPTARG"
+ ;;
+ p)
+ DBPASS="$OPTARG"
+ ;;
+ P)
+ DBPORT="$OPTARG"
+ ;;
+ d)
+ DBNAME="$OPTARG"
+ ;;
+ h)
+ DBHOST="$OPTARG"
+ ;;
+ -)
+ [ "${OPTARG}" == "help" ] && usage && exit 0
+ echo "Invalid option: --$OPTARG" >&2 && usage >&2
+ exit 1
+ ;;
+ \?)
+ echo "Invalid option: -$OPTARG" >&2 && usage >&2
+ exit 1
+ ;;
+ :)
+ echo "Option -$OPTARG requires an argument." >&2 && usage >&2
+ exit 1
+ ;;
+ *)
+ usage >&2
+ exit -1
+ ;;
+ esac
+done
+shift $((OPTIND-1))
+
+#check and ask for database user password
+DBUSER_="-u$DBUSER"
+DBPASS_=""
+[ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
+DBHOST_="-h$DBHOST"
+DBPORT_="-P$DBPORT"
+while ! echo ";" | mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME >/dev/null 2>&1
+do
+ [ -n "$logintry" ] && echo -e "\nInvalid database credentials!!!. Try again (Ctrl+c to abort)"
+ [ -z "$logintry" ] && echo -e "\nProvide database name and credentials"
+ read -e -p "mysql database name($DBNAME): " KK
+ [ -n "$KK" ] && DBNAME="$KK"
+ read -e -p "mysql user($DBUSER): " KK
+ [ -n "$KK" ] && DBUSER="$KK" && DBUSER_="-u$DBUSER"
+ read -e -s -p "mysql password: " DBPASS
+ [ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
+ [ -z "$DBPASS" ] && DBPASS_=""
+ logintry="yes"
+ echo
+done
+
+
+#echo structure, including the content of schema_version
+echo "$LICENSE_HEAD" > ${DIRNAME}/${DBNAME}_structure.sql
+mysqldump $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ --no-data --add-drop-table --add-drop-database --routines --databases $DBNAME >> ${DIRNAME}/${DBNAME}_structure.sql
+echo -e "\n\n\n\n" >> ${DIRNAME}/${DBNAME}_structure.sql
+mysqldump $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ --no-create-info $DBNAME --tables schema_version 2>/dev/null >> ${DIRNAME}/${DBNAME}_structure.sql
+echo " ${DIRNAME}/${DBNAME}_structure.sql"
+
+#echo only data
+echo "$LICENSE_HEAD" > ${DIRNAME}/${DBNAME}_data.sql #copy my own header
+mysqldump $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ --no-create-info $DBNAME >> ${DIRNAME}/${DBNAME}_data.sql
+echo " ${DIRNAME}/${DBNAME}_data.sql"
+
+#echo all
+echo "$LICENSE_HEAD" > ${DIRNAME}/${DBNAME}_all.sql #copy my own header
+mysqldump $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ --add-drop-table --add-drop-database --routines --databases $DBNAME >> ${DIRNAME}/${DBNAME}_all.sql
+echo " ${DIRNAME}/${DBNAME}_all.sql"
+
diff --git a/database_utils/host_ranking.sql b/database_utils/host_ranking.sql
new file mode 100644
index 0000000..5e229bf
--- /dev/null
+++ b/database_utils/host_ranking.sql
@@ -0,0 +1,43 @@
+/*
+##
+# Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
+# This file is part of openmano
+# All Rights Reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License"); you may
+# not use this file except in compliance with the License. You may obtain
+# a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+# License for the specific language governing permissions and limitations
+# under the License.
+#
+# For those usages not covered by the Apache License, Version 2.0 please
+# contact with: nfvlabs@tid.es
+##
+*/
+
+/* This table contains a list of processor ranking
+ The larger ranking the better performance
+ All physical host models must be included in this table
+ before being adding to openvim
+ processor information is obtained with commnand cat /proc/cpuinfo
+ NOTE: Current version of openvim ignores the ranking
+*/
+
+
+LOCK TABLES `host_ranking` WRITE;
+/*!40000 ALTER TABLE `host_ranking` DISABLE KEYS */;
+INSERT INTO `host_ranking`
+ (family, manufacturer, version, description, ranking)
+VALUES
+ ('Xeon','Intel','Intel(R) Xeon(R) CPU E5-2680 0 @ 2.70GHz','sandy bridge',170),
+ ('Xeon','Intel','Intel(R) Xeon(R) CPU E5-4620 0 @ 2.20GHz','sandy bridge',200),
+ ('Xeon','Intel','Intel(R) Xeon(R) CPU E5-2697 v2 @ 2.70GHz','ivy bridge',300),
+ ('Xeon','Intel','Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz','ivy bridge',310); /*last entry ends with ';' */
+
+UNLOCK TABLES;
diff --git a/database_utils/init_vim_db.sh b/database_utils/init_vim_db.sh
new file mode 100755
index 0000000..a2dcdea
--- /dev/null
+++ b/database_utils/init_vim_db.sh
@@ -0,0 +1,124 @@
+#!/bin/bash
+
+##
+# Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
+# This file is part of openmano
+# All Rights Reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License"); you may
+# not use this file except in compliance with the License. You may obtain
+# a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+# License for the specific language governing permissions and limitations
+# under the License.
+#
+# For those usages not covered by the Apache License, Version 2.0 please
+# contact with: nfvlabs@tid.es
+##
+
+DBUSER="vim"
+DBPASS=""
+DBHOST="localhost"
+DBPORT="3306"
+DBNAME="vim_db"
+
+# Detect paths
+MYSQL=$(which mysql)
+AWK=$(which awk)
+GREP=$(which grep)
+DIRNAME=`dirname $0`
+
+function usage(){
+ echo -e "Usage: $0 OPTIONS [{openvim_version}]"
+ echo -e " Inits openvim database; deletes previous one and loads from ${DBNAME}_structure.sql"
+ echo -e " and data from host_ranking.sql, nets.sql, of_ports_pci_correspondece*.sql"
+ echo -e " If openvim_version is not provided it tries to get from openvimd.py using relative path"
+ echo -e " OPTIONS"
+ echo -e " -u USER database user. '$DBUSER' by default. Prompts if DB access fails"
+ echo -e " -p PASS database password. 'No password' by default. Prompts if DB access fails"
+ echo -e " -P PORT database port. '$DBPORT' by default"
+ echo -e " -h HOST database host. '$DBHOST' by default"
+ echo -e " -d NAME database name. '$DBNAME' by default. Prompts if DB access fails"
+ echo -e " --help shows this help"
+}
+
+while getopts ":u:p:P:h:d:-:" o; do
+ case "${o}" in
+ u)
+ DBUSER="$OPTARG"
+ ;;
+ p)
+ DBPASS="$OPTARG"
+ ;;
+ P)
+ DBPORT="$OPTARG"
+ ;;
+ d)
+ DBNAME="$OPTARG"
+ ;;
+ h)
+ DBHOST="$OPTARG"
+ ;;
+ -)
+ [ "${OPTARG}" == "help" ] && usage && exit 0
+ echo "Invalid option: --$OPTARG" >&2 && usage >&2
+ exit 1
+ ;;
+ \?)
+ echo "Invalid option: -$OPTARG" >&2 && usage >&2
+ exit 1
+ ;;
+ :)
+ echo "Option -$OPTARG requires an argument." >&2 && usage >&2
+ exit 1
+ ;;
+ *)
+ usage >&2
+ exit -1
+ ;;
+ esac
+done
+shift $((OPTIND-1))
+
+#check and ask for database user password
+DBUSER_="-u$DBUSER"
+DBPASS_=""
+[ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
+DBHOST_="-h$DBHOST"
+DBPORT_="-P$DBPORT"
+while ! echo ";" | mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME >/dev/null 2>&1
+do
+ [ -n "$logintry" ] && echo -e "\nInvalid database credentials!!!. Try again (Ctrl+c to abort)"
+ [ -z "$logintry" ] && echo -e "\nProvide database name and credentials"
+ read -e -p "mysql database name($DBNAME): " KK
+ [ -n "$KK" ] && DBNAME="$KK"
+ read -e -p "mysql user($DBUSER): " KK
+ [ -n "$KK" ] && DBUSER="$KK" && DBUSER_="-u$DBUSER"
+ read -e -s -p "mysql password: " DBPASS
+ [ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
+ [ -z "$DBPASS" ] && DBPASS_=""
+ logintry="yes":
+ echo
+done
+
+echo " loading ${DIRNAME}/vim_db_structure.sql"
+sed -e "s/vim_db/$DBNAME/" ${DIRNAME}/vim_db_structure.sql | mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_
+
+echo " migrage database version"
+${DIRNAME}/migrate_vim_db.sh $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ -d$DBNAME $1
+
+echo " loading ${DIRNAME}/host_ranking.sql"
+mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME < ${DIRNAME}/host_ranking.sql
+
+echo " loading ${DIRNAME}/of_ports_pci_correspondence.sql"
+mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME < ${DIRNAME}/of_ports_pci_correspondence.sql
+#mysql -h $HOST -P $PORT -u $MUSER -p$MPASS $MDB < ${DIRNAME}/of_ports_pci_correspondence_centos.sql
+
+echo " loading ${DIRNAME}/nets.sql"
+mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME < ${DIRNAME}/nets.sql
+
diff --git a/database_utils/migrate_vim_db.sh b/database_utils/migrate_vim_db.sh
new file mode 100755
index 0000000..a8fec3e
--- /dev/null
+++ b/database_utils/migrate_vim_db.sh
@@ -0,0 +1,450 @@
+#!/bin/bash
+
+##
+# Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
+# This file is part of openmano
+# All Rights Reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License"); you may
+# not use this file except in compliance with the License. You may obtain
+# a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+# License for the specific language governing permissions and limitations
+# under the License.
+#
+# For those usages not covered by the Apache License, Version 2.0 please
+# contact with: nfvlabs@tid.es
+##
+
+#
+#Upgrade/Downgrade openvim database preserving the content
+#
+
+DBUSER="vim"
+DBPASS=""
+DBHOST="localhost"
+DBPORT="3306"
+DBNAME="vim_db"
+
+# Detect paths
+MYSQL=$(which mysql)
+AWK=$(which awk)
+GREP=$(which grep)
+DIRNAME=`dirname $0`
+
+function usage(){
+ echo -e "Usage: $0 OPTIONS [{openvim_version}]"
+ echo -e " Upgrades/Downgrades openvim database preserving the content"
+ echo -e " if openvim_version is not provided it tries to get from openvimd.py using relative path"
+ echo -e " OPTIONS"
+ echo -e " -u USER database user. '$DBUSER' by default. Prompts if DB access fails"
+ echo -e " -p PASS database password. 'No password' by default. Prompts if DB access fails"
+ echo -e " -P PORT database port. '$DBPORT' by default"
+ echo -e " -h HOST database host. '$DBHOST' by default"
+ echo -e " -d NAME database name. '$DBNAME' by default. Prompts if DB access fails"
+ echo -e " --help shows this help"
+}
+
+while getopts ":u:p:P:h:d:-:" o; do
+ case "${o}" in
+ u)
+ DBUSER="$OPTARG"
+ ;;
+ p)
+ DBPASS="$OPTARG"
+ ;;
+ P)
+ DBPORT="$OPTARG"
+ ;;
+ d)
+ DBNAME="$OPTARG"
+ ;;
+ h)
+ DBHOST="$OPTARG"
+ ;;
+ -)
+ [ "${OPTARG}" == "help" ] && usage && exit 0
+ echo "Invalid option: --$OPTARG" >&2 && usage >&2
+ exit 1
+ ;;
+ \?)
+ echo "Invalid option: -$OPTARG" >&2 && usage >&2
+ exit 1
+ ;;
+ :)
+ echo "Option -$OPTARG requires an argument." >&2 && usage >&2
+ exit 1
+ ;;
+ *)
+ usage >&2
+ exit -1
+ ;;
+ esac
+done
+shift $((OPTIND-1))
+
+
+#GET OPENVIM VERSION
+OPENVIM_VER="$1"
+if [ -z "$OPENVIM_VER" ]
+then
+ OPENVIM_VER=`${DIRNAME}/../openvimd.py -v`
+ OPENVIM_VER=${OPENVIM_VER%%-r*}
+ OPENVIM_VER=${OPENVIM_VER##*version }
+ echo " Detected openvim version $OPENVIM_VER"
+fi
+VERSION_1=`echo $OPENVIM_VER | cut -f 1 -d"."`
+VERSION_2=`echo $OPENVIM_VER | cut -f 2 -d"."`
+VERSION_3=`echo $OPENVIM_VER | cut -f 3 -d"."`
+if ! [ "$VERSION_1" -ge 0 -a "$VERSION_2" -ge 0 -a "$VERSION_3" -ge 0 ] 2>/dev/null
+then
+ [ -n "$1" ] && echo "Invalid openvim version '$1', expected 'X.X.X'" >&2
+ [ -z "$1" ] && echo "Can not get openvim version" >&2
+ exit -1
+fi
+OPENVIM_VER_NUM=`printf "%d%03d%03d" ${VERSION_1} ${VERSION_2} ${VERSION_3}`
+
+#check and ask for database user password
+DBUSER_="-u$DBUSER"
+[ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
+DBHOST_="-h$DBHOST"
+DBPORT_="-P$DBPORT"
+while ! echo ";" | mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME >/dev/null 2>&1
+do
+ [ -n "$logintry" ] && echo -e "\nInvalid database credentials!!!. Try again (Ctrl+c to abort)"
+ [ -z "$logintry" ] && echo -e "\nProvide database name and credentials"
+ read -e -p "mysql database name($DBNAME): " KK
+ [ -n "$KK" ] && DBNAME="$KK"
+ read -e -p "mysql user($DBUSER): " KK
+ [ -n "$KK" ] && DBUSER="$KK" && DBUSER_="-u$DBUSER"
+ read -e -s -p "mysql password: " DBPASS
+ [ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
+ [ -z "$DBPASS" ] && DBPASS_=""
+ logintry="yes"
+ echo
+done
+
+DBCMD="mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME"
+#echo DBCMD $DBCMD
+
+#GET DATABASE VERSION
+#check that the database seems a openvim database
+if ! echo -e "show create table instances;\nshow create table numas" | $DBCMD >/dev/null 2>&1
+then
+ echo " database $DBNAME does not seem to be an openvim database" >&2
+ exit -1;
+fi
+
+if ! echo 'show create table schema_version;' | $DBCMD >/dev/null 2>&1
+then
+ DATABASE_VER="0.0"
+ DATABASE_VER_NUM=0
+else
+ DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
+ DATABASE_VER=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
+ [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] && echo " Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
+ #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
+fi
+
+
+#GET DATABASE TARGET VERSION
+DATABASE_TARGET_VER_NUM=0
+[ $OPENVIM_VER_NUM -gt 1091 ] && DATABASE_TARGET_VER_NUM=1 #>0.1.91 => 1
+[ $OPENVIM_VER_NUM -ge 2003 ] && DATABASE_TARGET_VER_NUM=2 #0.2.03 => 2
+[ $OPENVIM_VER_NUM -ge 2005 ] && DATABASE_TARGET_VER_NUM=3 #0.2.5 => 3
+[ $OPENVIM_VER_NUM -ge 3001 ] && DATABASE_TARGET_VER_NUM=4 #0.3.1 => 4
+[ $OPENVIM_VER_NUM -ge 4001 ] && DATABASE_TARGET_VER_NUM=5 #0.4.1 => 5
+[ $OPENVIM_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM=6 #0.4.2 => 6
+[ $OPENVIM_VER_NUM -ge 4005 ] && DATABASE_TARGET_VER_NUM=7 #0.4.5 => 7
+#TODO ... put next versions here
+
+
+function upgrade_to_1(){
+ echo " upgrade database from version 0.0 to version 0.1"
+ echo " CREATE TABLE \`schema_version\`"
+ echo "CREATE TABLE \`schema_version\` (
+ \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
+ \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
+ \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
+ \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
+ \`date\` DATE NULL,
+ PRIMARY KEY (\`version_int\`)
+ )
+ COMMENT='database schema control version'
+ COLLATE='utf8_general_ci'
+ ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
+ VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" | $DBCMD
+ echo " ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
+ echo "ALTER TABLE \`nets\`
+ ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+function downgrade_from_1(){
+ echo " downgrade database from version 0.1 to version 0.0"
+ echo " ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
+ echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo " DROP TABLE \`schema_version\`"
+ echo "DROP TABLE \`schema_version\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+function upgrade_to_2(){
+ echo " upgrade database from version 0.1 to version 0.2"
+ echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
+ for table in of_ports_pci_correspondence resources_port ports
+ do
+ echo "ALTER TABLE \`${table}\`
+ ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ [ $table == of_ports_pci_correspondence ] ||
+ 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
+ done
+ echo " UPDATE procedure UpdateSwitchPort"
+ echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
+ delimiter //
+ CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
+ COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
+ BEGIN
+ #DELETES switch_port entry before writing, because if not it fails for key constrains
+ UPDATE ports
+ RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
+ INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
+ INNER JOIN numas on RP.numa_id=numas.id
+ INNER JOIN hosts on numas.host_id=hosts.uuid
+ INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
+ SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
+ #write switch_port into resources_port and ports
+ UPDATE ports
+ RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
+ INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
+ INNER JOIN numas on RP.numa_id=numas.id
+ INNER JOIN hosts on numas.host_id=hosts.uuid
+ INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
+ 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;
+ END//
+ delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
+ VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+function upgrade_to_3(){
+ echo " upgrade database from version 0.2 to version 0.3"
+ echo " change size of source_name at table resources_port"
+ 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
+ echo " CREATE PROCEDURE GetAllAvailablePorts"
+ echo "delimiter //
+ CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
+ COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
+ BEGIN
+ SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
+ FROM
+ (
+ SELECT id as port_id, Mbps, pci, switch_port, mac
+ FROM resources_port
+ WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
+ ) as A
+ INNER JOIN
+ (
+ SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
+ FROM resources_port
+ WHERE numa_id = Numa AND status = 'ok'
+ GROUP BY root_id
+ ) as B
+ ON A.port_id = B.root_id
+ LEFT JOIN
+ (
+ SELECT root_id, COUNT(id) as usedSRIOV
+ FROM resources_port
+ WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
+ GROUP BY root_id
+ ) as C
+ ON A.port_id = C.root_id
+ ORDER BY Mbps_free, availableSRIOV, pci;
+ END//
+ delimiter ;"| $DBCMD || ! ! echo "ERROR. Aborted!" || exit -1
+ 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
+}
+
+function upgrade_to_4(){
+ echo " upgrade database from version 0.3 to version 0.4"
+ echo " remove unique VLAN index at 'resources_port', 'ports'"
+ echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "ALTER TABLE ports DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo " change table 'ports'"
+ 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
+ echo "ALTER TABLE ports DROP COLUMN vlan_changed;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "ALTER TABLE resources_port DROP COLUMN vlan;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ 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
+}
+
+function upgrade_to_X(){
+ #TODO, this change of foreign key does not work
+ echo " upgrade database from version 0.X to version 0.X"
+ echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
+ DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "ALTER TABLE instances
+ ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
+ ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+
+function downgrade_from_2(){
+ echo " downgrade database from version 0.2 to version 0.1"
+ echo " UPDATE procedure UpdateSwitchPort"
+ echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
+ delimiter //
+ CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
+ BEGIN
+ UPDATE
+ resources_port INNER JOIN (
+ SELECT resources_port.id,KK.switch_port
+ FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
+ INNER JOIN hosts on numas.host_id=hosts.uuid
+ INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
+ ) as TABLA
+ ON resources_port.root_id=TABLA.id
+ SET resources_port.switch_port=TABLA.switch_port
+ WHERE resources_port.root_id=TABLA.id;
+ END//
+ delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo " ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
+ for table in of_ports_pci_correspondence resources_port ports
+ do
+ [ $table == of_ports_pci_correspondence ] ||
+ echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ switch_port_size=12
+ [ $table == of_ports_pci_correspondence ] && switch_port_size=50
+ echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ done
+ echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+function downgrade_from_3(){
+ echo " downgrade database from version 0.3 to version 0.2"
+ echo " change back size of source_name at table resources_port"
+ 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
+ echo " DROP PROCEDURE GetAllAvailablePorts"
+ echo "DROP PROCEDURE GetAllAvailablePorts;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "DELETE FROM schema_version WHERE version_int = '3';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+function downgrade_from_4(){
+ echo " downgrade database from version 0.4 to version 0.3"
+ echo " adding back unique index VLAN at 'resources_port','ports'"
+ echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER Mbps_used;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "ALTER TABLE ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo " change back table 'ports'"
+ echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ 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
+ echo "DELETE FROM schema_version WHERE version_int = '4';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+
+
+function upgrade_to_5(){
+ echo " upgrade database from version 0.4 to version 0.5"
+ echo " add 'ip_address' to ports'"
+ echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ 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
+}
+function downgrade_from_5(){
+ echo " downgrade database from version 0.5 to version 0.4"
+ echo " removing 'ip_address' from 'ports'"
+ echo "ALTER TABLE ports DROP COLUMN ip_address;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo "DELETE FROM schema_version WHERE version_int = '5';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+
+function upgrade_to_6(){
+ echo " upgrade database from version 0.5 to version 0.6"
+ echo " Change enalarge name, description to 255 at all database"
+ for table in flavors images instances tenants
+ do
+ name_length=255
+ [[ $table == tenants ]] || name_length=64
+ echo -en " $table \r"
+ 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
+ done
+ echo -en " hosts \r"
+ 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
+ echo -en " nets \r"
+ 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
+ echo -en " instances \r"
+ echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo -en " ports \r"
+ 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
+ echo -en " of_flows \r"
+ 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
+ echo -en " of_ports_pci_cor... \r"
+ 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
+ echo -en " resources_port \r"
+ 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
+ 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
+}
+function downgrade_from_6(){
+ echo " downgrade database from version 0.6 to version 0.5"
+ echo " Change back name,description to shorter length at all database"
+ for table in flavors images instances tenants
+ do
+ name_length=50
+ [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
+ echo -en " $table \r"
+ 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
+ done
+ echo -en " hosts \r"
+ 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
+ echo -en " nets \r"
+ 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
+ echo -en " instances \r"
+ echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ echo -en " ports \r"
+ 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
+ echo -en " of_flows \r"
+ 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
+ echo -en " of_ports_pci_cor... \r"
+ 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
+ echo -en " resources_port \r"
+ 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
+ echo "DELETE FROM schema_version WHERE version_int='6';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+function upgrade_to_7(){
+ echo " upgrade database from version 0.6 to version 0.7"
+ echo " add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
+ 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
+ echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ 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
+ 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
+}
+function downgrade_from_7(){
+ echo " downgrade database from version 0.7 to version 0.6"
+ echo " removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
+ echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+ 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
+ echo "DELETE FROM schema_version WHERE version_int = '7';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
+}
+#TODO ... put funtions here
+
+
+[ $DATABASE_TARGET_VER_NUM -eq $DATABASE_VER_NUM ] && echo " current database version $DATABASE_VER is ok"
+#UPGRADE DATABASE step by step
+while [ $DATABASE_TARGET_VER_NUM -gt $DATABASE_VER_NUM ]
+do
+ DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
+ upgrade_to_${DATABASE_VER_NUM}
+ #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
+ #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
+ #$FILE_ || exit -1 # if fail return
+done
+
+#DOWNGRADE DATABASE step by step
+while [ $DATABASE_TARGET_VER_NUM -lt $DATABASE_VER_NUM ]
+do
+ #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
+ #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
+ #$FILE_ || exit -1 # if fail return
+ downgrade_from_${DATABASE_VER_NUM}
+ DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
+done
+
+#echo done
+
diff --git a/database_utils/nets.sql b/database_utils/nets.sql
new file mode 100644
index 0000000..654d0d1
--- /dev/null
+++ b/database_utils/nets.sql
@@ -0,0 +1,74 @@
+##
+# Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
+# This file is part of openmano
+# All Rights Reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License"); you may
+# not use this file except in compliance with the License. You may obtain
+# a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+# License for the specific language governing permissions and limitations
+# under the License.
+#
+# For those usages not covered by the Apache License, Version 2.0 please
+# contact with: nfvlabs@tid.es
+##
+
+/*
+ This table contains a list of networks created from the begining
+ The following fields are needed
+ uuid: provide a valid uuid format
+ type: ptp, data (point to point, or point to multipoint) are openflow dadaplane nets
+ bridge_man, bridge_data are virtio/bridge controlplane nets
+ name: useful human readable name
+ shared: by default true
+ vlan: default vlan of the dataplane net
+ bind: for control plane:
+ default: default network
+ macvtap:host_iface. Connect to a direct macvtap host interface
+ bridge:bridge_name. Connect to this host bridge_name interface
+ for dataplane: NULL, because the binding is done with a external port
+*/
+
+
+LOCK TABLES `nets` WRITE;
+/*
+INSERT INTO `nets`
+ (uuid, `type`, name, shared, vlan, bind)
+VALUES
+ ('00000000-0000-0000-0000-000000000000','bridge_man', 'default', 'true', NULL, 'default'),
+ ('11111111-1111-1111-1111-111111111111','bridge_man', 'direct:em1','true', NULL, 'macvtap:em1'),
+ ('aaaaaaaa-1111-aaaa-aaaa-aaaaaaaaaaaa','data', 'coreIPv4', 'true', 702, NULL),
+ ('aaaaaaaa-aaaa-0000-1111-aaaaaaaaaaaa','bridge_data','virbrMan2', 'true', 2002, 'bridge:virbrMan2') # last row without ','
+;
+*/
+
+UNLOCK TABLES;
+
+/* External PORTS are necessary to connect a dataplane network to an external switch port
+ The following fields are needed
+ uuid: provide a valid uuid format
+ name: useful human readable name
+ net_id: uuid of the net where this port must be connected
+ Mbps: only informative, indicates the expected bandwidth in megabits/s
+ type: only external has meaning here
+ vlan: if the traffic at that port must be vlan tagged
+ switch_port: port name at switch:
+*/
+
+LOCK TABLES `ports` WRITE;
+/*
+INSERT INTO `ports`
+ (uuid, name, net_id, Mbps, type, vlan, switch_port)
+VALUES
+ ('6d536a80-52e9-11e4-9e31-5254006d6777','CoreIPv4', 'aaaaaaaa-1111-aaaa-aaaa-aaaaaaaaaaaa',10000,'external',702, 'Te0/47') # last row without ','
+;
+*/
+
+UNLOCK TABLES;
+
diff --git a/database_utils/of_ports_pci_correspondence.sql b/database_utils/of_ports_pci_correspondence.sql
new file mode 100644
index 0000000..b9aa846
--- /dev/null
+++ b/database_utils/of_ports_pci_correspondence.sql
@@ -0,0 +1,76 @@
+##
+# Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
+# This file is part of openmano
+# All Rights Reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License"); you may
+# not use this file except in compliance with the License. You may obtain
+# a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+# License for the specific language governing permissions and limitations
+# under the License.
+#
+# For those usages not covered by the Apache License, Version 2.0 please
+# contact with: nfvlabs@tid.es
+##
+
+/*
+ READ THIS please:
+ This table contains the matching between dataplane host ports
+ and openflow switch ports.
+ The two first column identifies the host and the pci bus
+ command ethtool -i provides the pci bus port (at host)
+ command ethtool -p makes this port blinking (at host)
+ Last column identifies the switch port name
+ openvim prints at starting the openflow ports naming
+ NOTE: if a host has already been inserted, you must execute
+ UpdateSwitchPort database procedure to associate ports with
+ the switch connection
+*/
+
+LOCK TABLES `of_ports_pci_correspondence` WRITE;
+
+/* DATA for fakehost examples*/
+INSERT INTO `of_ports_pci_correspondence`
+ (ip_name, pci, switch_port)
+VALUES
+ ('fake-host-0', '0000:06:00.0', 'port0/0'),
+ ('fake-host-0', '0000:06:00.1', 'port0/1'),
+ ('fake-host-0', '0000:08:00.0', 'port0/2'),
+ ('fake-host-0', '0000:08:00.1', 'port0/3'),
+
+ ('fake-host-1', '0000:44:00.0', 'port0/4'),
+ ('fake-host-1', '0000:44:00.1', 'port0/5'),
+ ('fake-host-1', '0000:43:00.0', 'port0/6'),
+ ('fake-host-1', '0000:43:00.1', 'port0/7'),
+ ('fake-host-1', '0000:04:00.0', 'port0/8'),
+ ('fake-host-1', '0000:04:00.1', 'port0/9'),
+ ('fake-host-1', '0000:06:00.0', 'port0/10'),
+ ('fake-host-1', '0000:06:00.1', 'port0/11'),
+
+ ('fake-host-2', '0000:44:00.0', 'port0/12'),
+ ('fake-host-2', '0000:44:00.1', 'port0/13'),
+ ('fake-host-2', '0000:43:00.0', 'port0/14'),
+ ('fake-host-2', '0000:43:00.1', 'port0/15'),
+ ('fake-host-2', '0000:04:00.0', 'port0/16'),
+ ('fake-host-2', '0000:04:00.1', 'port0/17'),
+ ('fake-host-2', '0000:06:00.0', 'port0/18'),
+ ('fake-host-2', '0000:06:00.1', 'port0/19'),
+
+ ('fake-host-3', '0000:44:00.0', 'port1/0'),
+ ('fake-host-3', '0000:44:00.1', 'port1/1'),
+ ('fake-host-3', '0000:43:00.0', 'port1/2'),
+ ('fake-host-3', '0000:43:00.1', 'port1/3'),
+ ('fake-host-3', '0000:04:00.0', 'port1/4'),
+ ('fake-host-3', '0000:04:00.1', 'port1/5'),
+ ('fake-host-3', '0000:06:00.0', 'port1/6'),
+ ('fake-host-3', '0000:06:00.1', 'port1/7')
+;
+
+
+UNLOCK TABLES;
diff --git a/database_utils/vim_db_structure.sql b/database_utils/vim_db_structure.sql
new file mode 100644
index 0000000..1499171
--- /dev/null
+++ b/database_utils/vim_db_structure.sql
@@ -0,0 +1,962 @@
+/**
+* Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
+* This file is part of openmano
+* All Rights Reserved.
+*
+* Licensed under the Apache License, Version 2.0 (the "License"); you may
+* not use this file except in compliance with the License. You may obtain
+* a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+* License for the specific language governing permissions and limitations
+* under the License.
+*
+* For those usages not covered by the Apache License, Version 2.0 please
+* contact with: nfvlabs@tid.es
+**/
+
+-- MySQL dump 10.13 Distrib 5.5.43, for debian-linux-gnu (x86_64)
+--
+-- Host: localhost Database: vim_db
+-- ------------------------------------------------------
+-- Server version 5.5.43-0ubuntu0.14.04.1
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Current Database: `vim_db`
+--
+
+/*!40000 DROP DATABASE IF EXISTS `vim_db`*/;
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vim_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
+
+USE `vim_db`;
+
+--
+-- Table structure for table `flavors`
+--
+
+DROP TABLE IF EXISTS `flavors`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `flavors` (
+ `uuid` varchar(36) NOT NULL,
+ `name` varchar(64) NOT NULL,
+ `description` varchar(255) DEFAULT NULL,
+ `disk` smallint(5) unsigned DEFAULT NULL,
+ `ram` smallint(5) unsigned DEFAULT NULL,
+ `vcpus` smallint(5) unsigned DEFAULT NULL,
+ `extended` varchar(2000) DEFAULT NULL COMMENT 'Extra description yaml format of needed resources and pining, orginized in sets per numa',
+ `public` enum('yes','no') NOT NULL DEFAULT 'no',
+ `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ PRIMARY KEY (`uuid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='flavors with extra vnfcd info';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `host_ranking`
+--
+
+DROP TABLE IF EXISTS `host_ranking`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `host_ranking` (
+ `id` int(10) NOT NULL AUTO_INCREMENT,
+ `family` varchar(50) NOT NULL,
+ `manufacturer` varchar(50) NOT NULL,
+ `version` varchar(50) NOT NULL,
+ `description` varchar(50) DEFAULT NULL,
+ `ranking` smallint(4) unsigned NOT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `family_manufacturer_version` (`family`,`manufacturer`,`version`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `hosts`
+--
+
+DROP TABLE IF EXISTS `hosts`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `hosts` (
+ `uuid` varchar(36) NOT NULL,
+ `name` varchar(255) NOT NULL,
+ `ip_name` varchar(64) NOT NULL,
+ `description` varchar(255) DEFAULT NULL,
+ `status` enum('ok','error','notused') NOT NULL DEFAULT 'ok',
+ `ranking` smallint(6) NOT NULL DEFAULT '0',
+ `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ `features` varchar(255) DEFAULT NULL,
+ `user` varchar(64) NOT NULL,
+ `password` varchar(64) DEFAULT NULL,
+ `admin_state_up` enum('true','false') NOT NULL DEFAULT 'true',
+ `RAM` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'Host memory in MB not used as hugepages',
+ `cpus` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Host threads(or cores) not isolated from OS',
+ PRIMARY KEY (`uuid`),
+ UNIQUE KEY `ip_name` (`ip_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='hosts information';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `images`
+--
+
+DROP TABLE IF EXISTS `images`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `images` (
+ `uuid` varchar(36) NOT NULL,
+ `path` varchar(100) NOT NULL,
+ `name` varchar(64) NOT NULL,
+ `description` varchar(255) DEFAULT NULL,
+ `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ `modified_at` timestamp NULL DEFAULT NULL,
+ `public` enum('yes','no') NOT NULL DEFAULT 'no',
+ `progress` tinyint(3) unsigned NOT NULL DEFAULT '100',
+ `status` enum('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'ACTIVE',
+ `metadata` varchar(2000) DEFAULT NULL COMMENT 'Metatdata in json text format',
+ PRIMARY KEY (`uuid`),
+ UNIQUE KEY `path` (`path`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `instance_devices`
+--
+
+DROP TABLE IF EXISTS `instance_devices`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `instance_devices` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `type` enum('usb','disk','cdrom','xml') NOT NULL,
+ `xml` varchar(1000) DEFAULT NULL COMMENT 'libvirt XML format for aditional device',
+ `instance_id` varchar(36) NOT NULL,
+ `image_id` varchar(36) DEFAULT NULL COMMENT 'Used in case type is disk',
+ `vpci` char(12) DEFAULT NULL COMMENT 'format XXXX:XX:XX.X',
+ `dev` varchar(12) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ KEY `FK_instance_devices_instances` (`instance_id`),
+ KEY `FK_instance_devices_images` (`image_id`),
+ CONSTRAINT `FK_instance_devices_images` FOREIGN KEY (`image_id`) REFERENCES `tenants_images` (`image_id`),
+ CONSTRAINT `FK_instance_devices_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`) ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `instances`
+--
+
+DROP TABLE IF EXISTS `instances`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `instances` (
+ `uuid` varchar(36) NOT NULL,
+ `flavor_id` varchar(36) NOT NULL,
+ `image_id` varchar(36) NOT NULL,
+ `name` varchar(64) NOT NULL,
+ `description` varchar(255) DEFAULT NULL,
+ `last_error` varchar(255) DEFAULT NULL,
+ `progress` tinyint(3) unsigned NOT NULL DEFAULT '0',
+ `tenant_id` varchar(36) NOT NULL,
+ `status` enum('ACTIVE','PAUSED','INACTIVE','CREATING','ERROR','DELETING') NOT NULL DEFAULT 'ACTIVE',
+ `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ `modified_at` timestamp NULL DEFAULT NULL,
+ `host_id` varchar(36) NOT NULL COMMENT 'HOST where is allocated',
+ `ram` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'used non-hugepages memory in MB',
+ `vcpus` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'used non-isolated CPUs',
+ PRIMARY KEY (`uuid`),
+ KEY `FK_instances_tenants` (`tenant_id`),
+ KEY `FK_instances_flavors` (`flavor_id`),
+ KEY `FK_instances_images` (`image_id`),
+ KEY `FK_instances_hosts` (`host_id`),
+ CONSTRAINT `FK_instances_flavors` FOREIGN KEY (`flavor_id`) REFERENCES `tenants_flavors` (`flavor_id`),
+ CONSTRAINT `FK_instances_hosts` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`uuid`),
+ CONSTRAINT `FK_instances_images` FOREIGN KEY (`image_id`) REFERENCES `tenants_images` (`image_id`),
+ CONSTRAINT `FK_instances_tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='VM instances';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `logs`
+--
+
+DROP TABLE IF EXISTS `logs`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `logs` (
+ `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ `tenant_id` varchar(36) DEFAULT NULL,
+ `related` enum('hosts','images','flavors','tenants','ports','instances','nets') DEFAULT NULL,
+ `uuid` varchar(36) DEFAULT NULL COMMENT 'uuid of host, image, etc that log relates to',
+ `level` enum('panic','error','info','debug','verbose') NOT NULL,
+ `description` varchar(200) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `nets`
+--
+
+DROP TABLE IF EXISTS `nets`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `nets` (
+ `uuid` varchar(36) NOT NULL,
+ `tenant_id` varchar(36) DEFAULT NULL,
+ `type` enum('ptp','data','bridge_data','bridge_man') NOT NULL DEFAULT 'bridge_man',
+ `status` enum('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'ACTIVE',
+ `last_error` varchar(255) DEFAULT NULL,
+ `name` varchar(255) NOT NULL,
+ `shared` enum('true','false') NOT NULL DEFAULT 'false',
+ `admin_state_up` enum('true','false') NOT NULL DEFAULT 'true',
+ `vlan` smallint(6) DEFAULT NULL,
+ `provider` varchar(36) DEFAULT NULL,
+ `bind_net` varchar(36) DEFAULT NULL COMMENT 'To connect with other net',
+ `bind_type` varchar(36) DEFAULT NULL COMMENT 'VLAN:<tag> to insert/remove',
+ `cidr` varchar(64) DEFAULT NULL,
+ `enable_dhcp` enum('true','false') NOT NULL DEFAULT 'false',
+ `dhcp_first_ip` varchar(64) DEFAULT NULL,
+ `dhcp_last_ip` varchar(64) DEFAULT NULL,
+ PRIMARY KEY (`uuid`),
+ UNIQUE KEY `type_vlan` (`type`,`vlan`),
+ UNIQUE KEY `physical` (`provider`),
+ KEY `FK_nets_tenants` (`tenant_id`),
+ CONSTRAINT `FK_nets_tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `numas`
+--
+
+DROP TABLE IF EXISTS `numas`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `numas` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `host_id` varchar(36) NOT NULL,
+ `numa_socket` tinyint(3) unsigned NOT NULL DEFAULT '0',
+ `hugepages` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Available memory for guest in GB',
+ `status` enum('ok','error','notused') NOT NULL DEFAULT 'ok',
+ `memory` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'total memry in GB, not all available for guests',
+ `admin_state_up` enum('true','false') NOT NULL DEFAULT 'true',
+ PRIMARY KEY (`id`),
+ KEY `FK_numas_hosts` (`host_id`),
+ CONSTRAINT `FK_numas_hosts` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `of_flows`
+--
+
+DROP TABLE IF EXISTS `of_flows`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `of_flows` (
+ `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ `name` varchar(64) NOT NULL,
+ `net_id` varchar(36) DEFAULT NULL,
+ `priority` int(10) unsigned DEFAULT NULL,
+ `vlan_id` smallint(5) unsigned DEFAULT NULL,
+ `ingress_port` varchar(10) DEFAULT NULL,
+ `src_mac` varchar(50) DEFAULT NULL,
+ `dst_mac` varchar(50) DEFAULT NULL,
+ `actions` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name` (`name`),
+ KEY `FK_of_flows_nets` (`net_id`),
+ CONSTRAINT `FK_of_flows_nets` FOREIGN KEY (`net_id`) REFERENCES `nets` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `of_ports_pci_correspondence`
+--
+
+DROP TABLE IF EXISTS `of_ports_pci_correspondence`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `of_ports_pci_correspondence` (
+ `id` int(10) NOT NULL AUTO_INCREMENT,
+ `ip_name` varchar(64) DEFAULT NULL,
+ `pci` varchar(50) DEFAULT NULL,
+ `switch_port` varchar(64) DEFAULT NULL,
+ `switch_dpid` varchar(64) DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `ports`
+--
+
+DROP TABLE IF EXISTS `ports`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `ports` (
+ `uuid` varchar(36) NOT NULL,
+ `name` varchar(64) NOT NULL,
+ `instance_id` varchar(36) DEFAULT NULL,
+ `tenant_id` varchar(36) DEFAULT NULL,
+ `net_id` varchar(36) DEFAULT NULL,
+ `vpci` char(12) DEFAULT NULL,
+ `Mbps` mediumint(8) unsigned DEFAULT NULL COMMENT 'In Mbits/s',
+ `admin_state_up` enum('true','false') NOT NULL DEFAULT 'true',
+ `status` enum('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'ACTIVE',
+ `type` enum('instance:bridge','instance:data','external') NOT NULL DEFAULT 'instance:bridge',
+ `vlan` smallint(5) DEFAULT NULL COMMENT 'vlan of this SRIOV, or external port',
+ `switch_port` varchar(64) DEFAULT NULL,
+ `switch_dpid` varchar(64) DEFAULT NULL,
+ `mac` char(18) DEFAULT NULL COMMENT 'mac address format XX:XX:XX:XX:XX:XX',
+ `ip_address` varchar(64) DEFAULT NULL,
+ `model` varchar(12) DEFAULT NULL COMMENT 'driver model for bridge ifaces; PF,VF,VFnotShared for data ifaces',
+ PRIMARY KEY (`uuid`),
+ UNIQUE KEY `mac` (`mac`),
+ KEY `FK_instance_ifaces_instances` (`instance_id`),
+ KEY `FK_instance_ifaces_nets` (`net_id`),
+ KEY `FK_ports_tenants` (`tenant_id`),
+ CONSTRAINT `FK_instance_ifaces_nets` FOREIGN KEY (`net_id`) REFERENCES `nets` (`uuid`),
+ CONSTRAINT `FK_ports_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `FK_ports_tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Bridge interfaces used by instances';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `resources_core`
+--
+
+DROP TABLE IF EXISTS `resources_core`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `resources_core` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `numa_id` int(11) DEFAULT NULL,
+ `core_id` smallint(5) unsigned NOT NULL,
+ `thread_id` smallint(5) unsigned NOT NULL,
+ `instance_id` varchar(36) DEFAULT NULL COMMENT 'instance that consume this resource',
+ `v_thread_id` smallint(6) DEFAULT NULL COMMENT 'name used by virtual machine; -1 if this thread is not used because core is asigned completely',
+ `status` enum('ok','error','notused','noteligible') NOT NULL DEFAULT 'ok' COMMENT '''error'': resource not available becasue an error at deployment; ''notused'': admin marked as not available, ''noteligible'': used by host and not available for guests',
+ `paired` enum('Y','N') NOT NULL DEFAULT 'N',
+ PRIMARY KEY (`id`),
+ KEY `FK_resources_core_instances` (`instance_id`),
+ KEY `FK_resources_core_numas` (`numa_id`),
+ CONSTRAINT `FK_resources_core_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`),
+ CONSTRAINT `FK_resources_core_numas` FOREIGN KEY (`numa_id`) REFERENCES `numas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Contain an entry by thread (two entries per core) of all available cores. Threy will be free if instance_id is NULL';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `resources_mem`
+--
+
+DROP TABLE IF EXISTS `resources_mem`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `resources_mem` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `numa_id` int(11) NOT NULL DEFAULT '0',
+ `instance_id` varchar(36) DEFAULT '0' COMMENT 'NULL is allowed in order to allow some memory not used',
+ `consumed` int(3) unsigned NOT NULL DEFAULT '0' COMMENT 'In GB',
+ PRIMARY KEY (`id`),
+ KEY `FK_resources_mem_instances` (`instance_id`),
+ KEY `FK_resources_mem_numas` (`numa_id`),
+ CONSTRAINT `FK_resources_mem_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`) ON DELETE CASCADE,
+ CONSTRAINT `FK_resources_mem_numas` FOREIGN KEY (`numa_id`) REFERENCES `numas` (`id`) ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Include the hugepages memory used by one instance (VM) in one host NUMA.';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `resources_port`
+--
+
+DROP TABLE IF EXISTS `resources_port`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `resources_port` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `numa_id` int(11) NOT NULL DEFAULT '0',
+ `instance_id` varchar(36) DEFAULT NULL COMMENT 'Contain instance that use this resource completely. NULL if this resource is free or partially used (resources_port_SRIOV)',
+ `port_id` varchar(36) DEFAULT NULL COMMENT 'When resource is used, this point to the ports table',
+ `source_name` varchar(64) DEFAULT NULL,
+ `pci` char(12) NOT NULL DEFAULT '0' COMMENT 'Host physical pci bus. Format XXXX:XX:XX.X',
+ `Mbps` smallint(5) unsigned DEFAULT '10' COMMENT 'Nominal Port speed ',
+ `root_id` int(11) DEFAULT NULL COMMENT 'NULL for physical port entries; =id for SRIOV port',
+ `status` enum('ok','error','notused') NOT NULL DEFAULT 'ok',
+ `Mbps_used` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Speed bandwidth used when asigned',
+ `switch_port` varchar(64) DEFAULT NULL,
+ `switch_dpid` varchar(64) DEFAULT NULL,
+ `mac` char(18) DEFAULT NULL COMMENT 'mac address format XX:XX:XX:XX:XX:XX',
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `mac` (`mac`),
+ UNIQUE KEY `port_id` (`port_id`),
+ KEY `FK_resources_port_numas` (`numa_id`),
+ KEY `FK_resources_port_instances` (`instance_id`),
+ CONSTRAINT `FK_resources_port_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`),
+ CONSTRAINT `FK_resources_port_numas` FOREIGN KEY (`numa_id`) REFERENCES `numas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `FK_resources_port_ports` FOREIGN KEY (`port_id`) REFERENCES `ports` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Contain NIC ports SRIOV and availabes, and current use. Every port contain several entries, one per port (root_id=NULL) and all posible SRIOV (root_id=id of port)';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `schema_version`
+--
+
+DROP TABLE IF EXISTS `schema_version`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `schema_version` (
+ `version_int` int(11) NOT NULL COMMENT 'version as a number. Must not contain gaps',
+ `version` varchar(20) NOT NULL COMMENT 'version as a text',
+ `openvim_ver` varchar(20) NOT NULL COMMENT 'openvim version',
+ `comments` varchar(2000) DEFAULT NULL COMMENT 'changes to database',
+ `date` date DEFAULT NULL,
+ PRIMARY KEY (`version_int`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='database schema control version';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `tenants`
+--
+
+DROP TABLE IF EXISTS `tenants`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `tenants` (
+ `uuid` varchar(36) NOT NULL,
+ `name` varchar(255) NOT NULL,
+ `description` varchar(255) DEFAULT NULL,
+ `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ `enabled` enum('true','false') NOT NULL DEFAULT 'true',
+ PRIMARY KEY (`uuid`),
+ UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='tenants information';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `tenants_flavors`
+--
+
+DROP TABLE IF EXISTS `tenants_flavors`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `tenants_flavors` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `flavor_id` varchar(36) NOT NULL,
+ `tenant_id` varchar(36) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `FK__tenants` (`tenant_id`),
+ KEY `FK__flavors` (`flavor_id`),
+ CONSTRAINT `FK__flavors` FOREIGN KEY (`flavor_id`) REFERENCES `flavors` (`uuid`),
+ CONSTRAINT `FK__tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `tenants_images`
+--
+
+DROP TABLE IF EXISTS `tenants_images`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `tenants_images` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `image_id` varchar(36) NOT NULL,
+ `tenant_id` varchar(36) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `FK_tenants_images_tenants` (`tenant_id`),
+ KEY `FK_tenants_images_images` (`image_id`),
+ CONSTRAINT `FK_tenants_images_images` FOREIGN KEY (`image_id`) REFERENCES `images` (`uuid`),
+ CONSTRAINT `FK_tenants_images_tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `uuids`
+--
+
+DROP TABLE IF EXISTS `uuids`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `uuids` (
+ `uuid` varchar(36) NOT NULL,
+ `root_uuid` varchar(36) DEFAULT NULL COMMENT 'Some related UUIDs can be grouped by this field, so that they can be deleted at once',
+ `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ `used_at` enum('flavors','hosts','images','instances','nets','ports','tenants') DEFAULT NULL COMMENT 'Table that uses this UUID',
+ PRIMARY KEY (`uuid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used to avoid UUID repetitions';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping routines for database 'vim_db'
+--
+/*!50003 DROP PROCEDURE IF EXISTS `GetAllAvailablePorts` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetAllAvailablePorts`(IN Numa INT)
+ SQL SECURITY INVOKER
+ COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
+BEGIN
+ SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
+ FROM
+ (
+ SELECT id as port_id, Mbps, pci, switch_port, mac
+ FROM resources_port
+ WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
+ ) as A
+ INNER JOIN
+ (
+ SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
+ FROM resources_port
+ WHERE numa_id = Numa AND status = 'ok'
+ GROUP BY root_id
+ ) as B
+ ON A.port_id = B.root_id
+ LEFT JOIN
+ (
+ SELECT root_id, COUNT(id) as usedSRIOV
+ FROM resources_port
+ WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
+ GROUP BY root_id
+ ) as C
+ ON A.port_id = C.root_id
+ ORDER BY Mbps_free, availableSRIOV, pci;
+ END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `GetAvailablePorts` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetAvailablePorts`(IN `Numa` INT)
+ DETERMINISTIC
+ SQL SECURITY INVOKER
+BEGIN
+SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
+FROM
+ (
+ SELECT id as port_id, Mbps, pci, switch_port, mac
+ FROM resources_port
+ WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND switch_port is not Null AND instance_id IS NULL
+ ) as A
+ INNER JOIN
+ (
+ SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
+ FROM resources_port
+ WHERE numa_id = Numa AND status = 'ok'
+ GROUP BY root_id
+ ) as B
+ ON A.port_id = B.root_id
+ LEFT JOIN
+ (
+ SELECT root_id, COUNT(id) as usedSRIOV
+ FROM resources_port
+ WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL AND switch_port is not Null
+ GROUP BY root_id
+ ) as C
+ ON A.port_id = C.root_id
+
+ORDER BY Mbps_free, availableSRIOV, pci
+;
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `GetHostByMemCpu` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetHostByMemCpu`(IN `Needed_mem` INT, IN `Needed_cpus` INT)
+ SQL SECURITY INVOKER
+ COMMENT 'Obtain those hosts with the available free Memory(Non HugePages) and CPUS (Non isolated)'
+BEGIN
+
+SELECT *
+FROM hosts as H
+LEFT JOIN (
+ SELECT sum(ram) as used_ram, sum(vcpus) as used_cpus, host_id
+ FROM instances
+ GROUP BY host_id
+) as U ON U.host_id = H.uuid
+WHERE Needed_mem<=H.RAM-coalesce(U.used_ram,0) AND Needed_cpus<=H.cpus-coalesce(U.used_cpus,0) AND H.admin_state_up = 'true'
+ORDER BY RAM-coalesce(U.used_ram,0), cpus-coalesce(U.used_cpus,0)
+
+;
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `GetIfaces` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetIfaces`()
+ SQL SECURITY INVOKER
+ COMMENT 'Used for the http get ports'
+BEGIN
+
+SELECT *, 'ACTIVE' as status,'true' as admin_state_up FROM
+(
+ (
+ SELECT ifa.uuid as id, ifa.name as name, instance_id as device_id, net_id, tenant_id
+ FROM instance_ifaces AS ifa JOIN instances AS i on ifa.instance_id=i.uuid
+ )
+ UNION
+ (
+ SELECT iface_uuid as id, ifa.name as name, instance_id as device_id, net_id,tenant_id
+ FROM resources_port AS ifa JOIN instances AS i on ifa.instance_id=i.uuid
+ WHERE iface_uuid is not NULL
+ )
+ UNION
+ (
+ SELECT uuid as id, name, Null as device_id, net_id, Null as tenant_id
+ FROM external_ports
+ )
+) as B
+;
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `GetNextAutoIncrement` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetNextAutoIncrement`()
+ SQL SECURITY INVOKER
+BEGIN
+SELECT table_name, AUTO_INCREMENT
+FROM information_schema.tables
+WHERE table_name = 'resources_port'
+AND table_schema = DATABASE( ) ;
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `GetNumaByCore` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetNumaByCore`(IN `Needed_cores` SMALLINT)
+ SQL SECURITY INVOKER
+ COMMENT 'Obtain Numas with a concrete number of available cores, with bot'
+BEGIN
+
+SELECT numa_id, host_id, numa_socket, freecores FROM
+(
+ SELECT numa_id, COUNT(core_id) as freecores FROM
+ (
+ SELECT numa_id, core_id, COUNT(thread_id) AS freethreads
+ FROM resources_core
+ WHERE instance_id IS NULL AND status = 'ok'
+ GROUP BY numa_id, core_id
+ ) AS FREECORES_TABLE
+ WHERE FREECORES_TABLE.freethreads = 2
+ GROUP BY numa_id
+) AS NBCORES_TABLE
+INNER JOIN numas ON numas.id = NBCORES_TABLE.numa_id
+INNER JOIN hosts ON numas.host_id = hosts.uuid
+
+WHERE NBCORES_TABLE.freecores >= Needed_cores AND numas.status = 'ok' AND numas.admin_state_up = 'true' AND hosts.admin_state_up = 'true'
+ORDER BY NBCORES_TABLE.freecores
+;
+
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `GetNumaByMemory` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetNumaByMemory`(IN `Needed_mem` SMALLINT)
+ DETERMINISTIC
+ SQL SECURITY INVOKER
+ COMMENT 'Obtain numas with a free quantity of memory, passed by parameter'
+BEGIN
+SELECT * FROM
+( SELECT numas.id as numa_id, numas.host_id, numas.numa_socket, numas.hugepages, numas.hugepages - sum(coalesce(resources_mem.consumed,0)) AS freemem
+ FROM numas
+ LEFT JOIN resources_mem ON numas.id = resources_mem.numa_id
+ JOIN hosts ON numas.host_id = hosts.uuid
+ WHERE numas.status = 'ok' AND numas.admin_state_up = 'true' AND hosts.admin_state_up = 'true'
+ GROUP BY numas.id
+) AS COMBINED
+
+WHERE COMBINED.freemem >= Needed_mem
+ORDER BY COMBINED.freemem
+;
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `GetNumaByPort` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetNumaByPort`(IN `Needed_speed` SMALLINT, IN `Needed_ports` SMALLINT)
+ SQL SECURITY INVOKER
+ COMMENT 'Busca Numas con N puertos fisicos LIBRES de X velocidad'
+BEGIN
+
+SELECT numa_id, COUNT(id) AS number_ports
+FROM
+(
+ SELECT root_id AS id, status, numa_id, Mbps, SUM(Mbps_used) AS Consumed
+ FROM resources_port
+ GROUP BY root_id
+) AS P
+WHERE status = 'ok' AND switch_port is not Null AND Consumed = 0 AND Mbps >= Needed_speed
+GROUP BY numa_id
+HAVING number_ports >= Needed_ports
+;
+
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `GetNumaByThread` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetNumaByThread`(IN `Needed_threads` SMALLINT)
+ SQL SECURITY INVOKER
+BEGIN
+
+SELECT numa_id, host_id, numa_socket, freethreads
+FROM
+(
+ SELECT numa_id, COUNT(thread_id) AS freethreads
+ FROM resources_core
+ WHERE instance_id IS NULL AND status = 'ok'
+ GROUP BY numa_id
+) AS NBCORES_TABLE
+INNER JOIN numas ON numas.id = NBCORES_TABLE.numa_id
+INNER JOIN hosts ON numas.host_id = hosts.uuid
+
+WHERE NBCORES_TABLE.freethreads >= Needed_threads AND numas.status = 'ok' AND numas.admin_state_up = 'true' AND hosts.admin_state_up = 'true'
+ORDER BY NBCORES_TABLE.freethreads
+;
+
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `GetPortsFromNuma` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `GetPortsFromNuma`(IN `Numa` INT)
+ NO SQL
+ SQL SECURITY INVOKER
+BEGIN
+SELECT Mbps, pci, status, Mbps_consumed
+FROM
+(
+ SELECT id, Mbps, pci, status
+ FROM resources_port
+ WHERE numa_id = Numa AND id=root_id AND status='ok' AND switch_port is not Null
+) as A
+INNER JOIN
+(
+ SELECT root_id, sum(Mbps_used) as Mbps_consumed
+ FROM resources_port
+ WHERE numa_id = Numa
+ GROUP BY root_id
+) as B
+ON A.id = B.root_id
+;
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `UpdateSwitchPort` */;
+/*!50003 SET @saved_cs_client = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
+/*!50003 SET sql_mode = '' */ ;
+DELIMITER ;;
+CREATE PROCEDURE `UpdateSwitchPort`()
+ MODIFIES SQL DATA
+ SQL SECURITY INVOKER
+ COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
+BEGIN
+
+ UPDATE ports
+ RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
+ INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
+ INNER JOIN numas on RP.numa_id=numas.id
+ INNER JOIN hosts on numas.host_id=hosts.uuid
+ INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
+ SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
+
+ UPDATE ports
+ RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
+ INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
+ INNER JOIN numas on RP.numa_id=numas.id
+ INNER JOIN hosts on numas.host_id=hosts.uuid
+ INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
+ 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;
+ END ;;
+DELIMITER ;
+/*!50003 SET sql_mode = @saved_sql_mode */ ;
+/*!50003 SET character_set_client = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection = @saved_col_connection */ ;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+-- Dump completed on 2016-05-13 12:52:19
+
+
+
+
+
+-- MySQL dump 10.13 Distrib 5.5.43, for debian-linux-gnu (x86_64)
+--
+-- Host: localhost Database: vim_db
+-- ------------------------------------------------------
+-- Server version 5.5.43-0ubuntu0.14.04.1
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Dumping data for table `schema_version`
+--
+
+LOCK TABLES `schema_version` WRITE;
+/*!40000 ALTER TABLE `schema_version` DISABLE KEYS */;
+INSERT INTO `schema_version` VALUES (1,'0.1','0.2.00','insert schema_version; alter nets with last_error column','2015-05-05'),(2,'0.2','0.2.03','update Procedure UpdateSwitchPort','2015-05-06'),(3,'0.3','0.2.5','New Procedure GetAllAvailablePorts','2015-07-09'),(4,'0.4','0.3.1','Remove unique index VLAN at resources_port','2015-09-04'),(5,'0.5','0.4.1','Add ip_address to ports','2015-09-04'),(6,'0.6','0.4.2','Enlarging name at database','2016-02-01'),(7,'0.7','0.4.4','Add bind_net to net table','2016-02-12');
+/*!40000 ALTER TABLE `schema_version` ENABLE KEYS */;
+UNLOCK TABLES;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+-- Dump completed on 2016-05-13 12:52:19