X-Git-Url: https://osm.etsi.org/gitweb/?p=osm%2FRO.git;a=blobdiff_plain;f=database_utils%2Fmigrate_mano_db.sh;h=aa2e7186b211d18febe2e69728a16d8e4d156b23;hp=557f597862b36666cd8080d4a5f92ba3520ac7e7;hb=c5293def02d95ed4ee086dd8842437b76ec05c4e;hpb=44e24da51d3a1c371cb2bb7c06beec4ba47c949e

diff --git a/database_utils/migrate_mano_db.sh b/database_utils/migrate_mano_db.sh
index 557f5978..aa2e7186 100755
--- a/database_utils/migrate_mano_db.sh
+++ b/database_utils/migrate_mano_db.sh
@@ -1,7 +1,7 @@
 #!/bin/bash
 
 ##
-# Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
+# Copyright 2015 Telefonica Investigacion y Desarrollo, S.A.U.
 # This file is part of openmano
 # All Rights Reserved.
 #
@@ -24,6 +24,7 @@
 #
 #Upgrade/Downgrade openmano database preserving the content
 #
+DBUTILS="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
 
 DBUSER="mano"
 DBPASS=""
@@ -33,8 +34,8 @@ DBPORT="3306"
 DBNAME="mano_db"
 QUIET_MODE=""
 #TODO update it with the last database version
-LAST_DB_VERSION=25
- 
+LAST_DB_VERSION=34
+
 # Detect paths
 MYSQL=$(which mysql)
 AWK=$(which awk)
@@ -193,6 +194,15 @@ fi
 #[ $OPENMANO_VER_NUM -ge 5020 ] && DB_VERSION=23  #0.5.20 =>  23
 #[ $OPENMANO_VER_NUM -ge 5021 ] && DB_VERSION=24  #0.5.21 =>  24
 #[ $OPENMANO_VER_NUM -ge 5022 ] && DB_VERSION=25  #0.5.22 =>  25
+#[ $OPENMANO_VER_NUM -ge 5024 ] && DB_VERSION=26  #0.5.24 =>  26
+#[ $OPENMANO_VER_NUM -ge 5025 ] && DB_VERSION=27  #0.5.25 =>  27
+#[ $OPENMANO_VER_NUM -ge 5052 ] && DB_VERSION=28  #0.5.52 =>  28
+#[ $OPENMANO_VER_NUM -ge 5059 ] && DB_VERSION=29  #0.5.59 =>  29
+#[ $OPENMANO_VER_NUM -ge 5060 ] && DB_VERSION=30  #0.5.60 =>  30
+#[ $OPENMANO_VER_NUM -ge 5061 ] && DB_VERSION=31  #0.5.61 =>  31
+#[ $OPENMANO_VER_NUM -ge 5070 ] && DB_VERSION=32  #0.5.70 =>  32
+#[ $OPENMANO_VER_NUM -ge 5082 ] && DB_VERSION=33  #0.5.82 =>  33
+#[ $OPENMANO_VER_NUM -ge 6000 ] && DB_VERSION=34  #0.6.00 =>  34
 #TODO ... put next versions here
 
 function upgrade_to_1(){
@@ -214,8 +224,8 @@ function upgrade_to_1(){
 }
 function downgrade_from_1(){
     # echo "    downgrade database from version 0.1 to version 0.0"
-    echo "      DROP TABLE \`schema_version\`"
-    sql "DROP TABLE \`schema_version\`;"
+    echo "      DROP TABLE IF EXISTS \`schema_version\`"
+    sql "DROP TABLE IF EXISTS \`schema_version\`;"
 }
 function upgrade_to_2(){
     # echo "    upgrade database from version 0.1 to version 0.2"
@@ -296,11 +306,11 @@ function downgrade_from_2(){
     echo "      Delete columns 'user/passwd' from 'vim_tenants'"
     sql "ALTER TABLE vim_tenants DROP COLUMN user, DROP COLUMN passwd; "
     echo "        delete tables 'datacenter_images', 'images'"
-    sql "DROP TABLE \`datacenters_images\`;"
-    sql "DROP TABLE \`images\`;"
+    sql "DROP TABLE IF EXISTS \`datacenters_images\`;"
+    sql "DROP TABLE IF EXISTS \`images\`;"
     echo "        delete tables 'datacenter_flavors', 'flavors'"
-    sql "DROP TABLE \`datacenters_flavors\`;"
-    sql "DROP TABLE \`flavors\`;"
+    sql "DROP TABLE IF EXISTS \`datacenters_flavors\`;"
+    sql "DROP TABLE IF EXISTS \`flavors\`;"
     sql "DELETE FROM schema_version WHERE version_int='2';"
 }
 
@@ -614,7 +624,7 @@ function upgrade_to_12(){
 function downgrade_from_12(){
     # echo "    downgrade database from version 0.12 to version 0.11"
     echo "      delete ip_profiles table, and remove ip_address column in 'interfaces' and 'sce_interfaces'"
-    sql "DROP TABLE ip_profiles;"
+    sql "DROP TABLE IF EXISTS ip_profiles;"
     sql "ALTER TABLE interfaces DROP COLUMN ip_address;"
     sql "ALTER TABLE sce_interfaces DROP COLUMN ip_address;"
     sql "DELETE FROM schema_version WHERE version_int='12';"
@@ -806,6 +816,7 @@ function downgrade_from_23(){
 function upgrade_to_24(){
     # echo "    upgrade database from version 0.23 to version 0.24"
     echo "      Add 'count' to table 'vms'"
+
     sql "ALTER TABLE vms ADD COLUMN count SMALLINT NOT NULL DEFAULT '1' AFTER vnf_id;"
     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
          "VALUES (24, '0.24', '0.5.21', 'Added vnfd fields', '2017-08-29');"
@@ -850,6 +861,455 @@ function downgrade_from_25(){
     sql "DELETE FROM schema_version WHERE version_int='25';"
 }
 
+function upgrade_to_26(){
+    echo "      Add name to table datacenter_tenants"
+    sql "ALTER TABLE datacenter_tenants ADD COLUMN name VARCHAR(255) NULL AFTER uuid;"
+    sql "UPDATE datacenter_tenants as dt join datacenters as d on dt.datacenter_id = d.uuid set dt.name=d.name;"
+    echo "      Add 'SCHEDULED' to 'status' at tables 'instance_nets', 'instance_vms'"
+    sql "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE:NoMgmtIP','ACTIVE','INACTIVE','BUILD',"\
+         "'ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') "\
+         "NOT NULL DEFAULT 'BUILD';"
+    sql "ALTER TABLE instance_nets CHANGE COLUMN status status ENUM('ACTIVE','INACTIVE','DOWN','BUILD','ERROR',"\
+         "'VIM_ERROR','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD';"
+    echo "      Enlarge pci at instance_interfaces to allow extended pci for SDN por mapping"
+    sql "ALTER TABLE instance_interfaces CHANGE COLUMN pci pci VARCHAR(50) NULL DEFAULT NULL COMMENT 'PCI of the "\
+        "physical port in the host' AFTER compute_node;"
+
+    for t in flavor image; do
+        echo "      Change 'datacenters_${t}s' to point to datacenter_tenant, add status, vim_info"
+        sql "ALTER TABLE datacenters_${t}s ADD COLUMN datacenter_vim_id VARCHAR(36) NULL DEFAULT NULL AFTER "\
+            "datacenter_id, ADD COLUMN status ENUM('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','DELETED',"\
+            "'SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD' AFTER vim_id, ADD COLUMN vim_info "\
+            "TEXT NULL AFTER status;"
+        sql "UPDATE datacenters_${t}s as df left join datacenter_tenants as dt on dt.datacenter_id=df.datacenter_id "\
+            "set df.datacenter_vim_id=dt.uuid;"
+        sql "DELETE FROM datacenters_${t}s WHERE datacenter_vim_id is NULL;"
+        sql "ALTER TABLE datacenters_${t}s CHANGE COLUMN datacenter_vim_id datacenter_vim_id VARCHAR(36) NOT NULL;"
+        sql "ALTER TABLE datacenters_${t}s ADD CONSTRAINT FK_datacenters_${t}s_datacenter_tenants FOREIGN KEY "\
+            "(datacenter_vim_id) REFERENCES datacenter_tenants (uuid) ON UPDATE CASCADE ON DELETE CASCADE;"
+        sql "ALTER TABLE datacenters_${t}s DROP FOREIGN KEY FK__datacenters_${t:0:1};"
+        sql "ALTER TABLE datacenters_${t}s DROP COLUMN datacenter_id;"
+	done
+
+    echo "      Decoupling 'instance_interfaces' from scenarios/vnfs to allow scale actions"
+    sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(128) NULL DEFAULT NULL;"
+    sql "ALTER TABLE instance_interfaces CHANGE COLUMN interface_id interface_id VARCHAR(36) NULL DEFAULT NULL;"
+	sql "ALTER TABLE instance_interfaces DROP FOREIGN KEY FK_instance_ids"
+	sql "ALTER TABLE instance_interfaces ADD CONSTRAINT FK_instance_ids FOREIGN KEY (interface_id) "\
+	    "REFERENCES interfaces (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
+
+    echo "      Decoupling 'instance_vms' from scenarios/vnfs to allow scale actions"
+    sql "ALTER TABLE instance_vms CHANGE COLUMN vim_vm_id vim_vm_id VARCHAR(128) NULL DEFAULT NULL;"
+    sql "ALTER TABLE instance_vms CHANGE COLUMN vm_id vm_id VARCHAR(36) NULL DEFAULT NULL;"
+	sql "ALTER TABLE instance_vms DROP FOREIGN KEY FK_instance_vms_vms;"
+	sql "ALTER TABLE instance_vms ADD CONSTRAINT FK_instance_vms_vms FOREIGN KEY (vm_id) "\
+	    "REFERENCES vms (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
+
+    echo "      Decoupling 'instance_nets' from scenarios/vnfs to allow scale actions"
+    sql "ALTER TABLE instance_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(128) NULL DEFAULT NULL;"
+
+    echo "      Decoupling 'instance_scenarios' from scenarios"
+    sql "ALTER TABLE instance_scenarios CHANGE COLUMN scenario_id scenario_id VARCHAR(36) NULL DEFAULT NULL;"
+	sql "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_scenarios;"
+	sql "ALTER TABLE instance_scenarios ADD CONSTRAINT FK_instance_scenarios_scenarios FOREIGN KEY (scenario_id) "\
+	    "REFERENCES scenarios (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
+
+    echo "      Create table instance_actions, vim_actions"
+    sql "CREATE TABLE IF NOT EXISTS instance_actions (
+	    uuid VARCHAR(36) NOT NULL,
+	    tenant_id VARCHAR(36) NULL DEFAULT NULL,
+	    instance_id VARCHAR(36) NULL DEFAULT NULL,
+	    description VARCHAR(64) NULL DEFAULT NULL COMMENT 'CREATE, DELETE, SCALE OUT/IN, ...',
+	    number_tasks SMALLINT(6) NOT NULL DEFAULT '1',
+	    number_done SMALLINT(6) NOT NULL DEFAULT '0',
+	    number_failed SMALLINT(6) NOT NULL DEFAULT '0',
+	    created_at DOUBLE NOT NULL,
+	    modified_at DOUBLE NULL DEFAULT NULL,
+	    PRIMARY KEY (uuid),
+        INDEX FK_actions_tenants (tenant_id),
+    	CONSTRAINT FK_actions_tenant FOREIGN KEY (tenant_id) REFERENCES nfvo_tenants (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
+		COMMENT='Contains client actions over instances'
+        COLLATE='utf8_general_ci'
+        ENGINE=InnoDB;"  
+
+    sql "CREATE TABLE IF NOT EXISTS vim_actions (
+	    instance_action_id VARCHAR(36) NOT NULL,
+	    task_index INT(6) NOT NULL,
+	    datacenter_vim_id VARCHAR(36) NOT NULL,
+	    vim_id VARCHAR(64) NULL DEFAULT NULL,
+	    action VARCHAR(36) NOT NULL COMMENT 'CREATE,DELETE,START,STOP...',
+	    item ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces') NOT NULL COMMENT 'table where the item is stored',
+	    item_id VARCHAR(36) NULL DEFAULT NULL COMMENT 'uuid of the entry in the table',
+	    status ENUM('SCHEDULED', 'BUILD', 'DONE', 'FAILED', 'SUPERSEDED') NOT NULL DEFAULT 'SCHEDULED',
+	    extra TEXT NULL DEFAULT NULL COMMENT 'json with params:, depends_on: for the task',
+	    error_msg VARCHAR(1024) NULL DEFAULT NULL,
+	    created_at DOUBLE NOT NULL,
+	    modified_at DOUBLE NULL DEFAULT NULL,
+	    PRIMARY KEY (task_index, instance_action_id),
+        INDEX FK_actions_instance_actions (instance_action_id),
+    	CONSTRAINT FK_actions_instance_actions FOREIGN KEY (instance_action_id) REFERENCES instance_actions (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
+        INDEX FK_actions_vims (datacenter_vim_id),
+    	CONSTRAINT FK_actions_vims FOREIGN KEY (datacenter_vim_id) REFERENCES datacenter_tenants (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
+        COMMENT='Table with the individual VIM actions.'
+        COLLATE='utf8_general_ci'
+        ENGINE=InnoDB;"  
+
+    sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
+         "VALUES (26, '0.26', '0.5.23', 'Several changes', '2017-09-09');"
+}
+function downgrade_from_26(){
+    echo "      Remove name from table datacenter_tenants"
+    sql "ALTER TABLE datacenter_tenants DROP COLUMN name;"
+    echo "      Remove 'SCHEDULED' from the 'status' at tables 'instance_nets', 'instance_vms'"
+    sql "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE:NoMgmtIP','ACTIVE','INACTIVE','BUILD',"\
+         "'ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED') NOT NULL DEFAULT 'BUILD';"
+    sql "ALTER TABLE instance_nets CHANGE COLUMN status status ENUM('ACTIVE','DOWN','BUILD','ERROR','VIM_ERROR',"\
+         "'INACTIVE','DELETED') NOT NULL DEFAULT 'BUILD';"
+    echo "      Shorten back pci at instance_interfaces to allow extended pci for SDN por mapping"
+    sql "ALTER TABLE instance_interfaces CHANGE COLUMN pci pci VARCHAR(12) NULL DEFAULT NULL COMMENT 'PCI of the "\
+        "physical port in the host' AFTER compute_node;"
+
+    for t in flavor image; do
+        echo "      Restore back 'datacenters_${t}s'"
+        sql "ALTER TABLE datacenters_${t}s ADD COLUMN datacenter_id VARCHAR(36) NULL DEFAULT NULL AFTER "\
+            "${t}_id, DROP COLUMN status, DROP COLUMN vim_info ;"
+        sql "UPDATE datacenters_${t}s as df left join datacenter_tenants as dt on dt.uuid=df.datacenter_vim_id set "\
+            "df.datacenter_id=dt.datacenter_id;"
+        sql "ALTER TABLE datacenters_${t}s CHANGE COLUMN datacenter_id datacenter_id VARCHAR(36) NOT NULL;"
+        sql "ALTER TABLE datacenters_${t}s ADD CONSTRAINT FK__datacenters_${t:0:1} FOREIGN KEY "\
+            "(datacenter_id) REFERENCES datacenters (uuid), DROP FOREIGN KEY FK_datacenters_${t}s_datacenter_tenants, "\
+            "DROP COLUMN datacenter_vim_id;"
+    done
+
+    echo "      Restore back 'instance_interfaces' coupling to scenarios/vnfs"
+    sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(36) NULL DEFAULT NULL;"
+	sql "ALTER TABLE instance_interfaces DROP FOREIGN KEY FK_instance_ids"
+    sql "ALTER TABLE instance_interfaces CHANGE COLUMN interface_id interface_id VARCHAR(36) NOT NULL;"
+	sql "ALTER TABLE instance_interfaces ADD CONSTRAINT FK_instance_ids FOREIGN KEY (interface_id) "\
+	    "REFERENCES interfaces (uuid);"
+
+    echo "      Restore back 'instance_vms' coupling to scenarios/vnfs"
+    echo "      Decoupling 'instance vms' from scenarios/vnfs to allow scale actions"
+    sql "UPDATE instance_vms SET vim_vm_id='' WHERE vim_vm_id is NULL;"
+    sql "ALTER TABLE instance_vms CHANGE COLUMN vim_vm_id vim_vm_id VARCHAR(36) NOT NULL;"
+	sql "ALTER TABLE instance_vms DROP FOREIGN KEY FK_instance_vms_vms;"
+    sql "ALTER TABLE instance_vms CHANGE COLUMN vm_id vm_id VARCHAR(36) NOT NULL;"
+	sql "ALTER TABLE instance_vms ADD CONSTRAINT FK_instance_vms_vms FOREIGN KEY (vm_id) "\
+	    "REFERENCES vms (uuid);"
+
+    echo "      Restore back 'instance_nets' coupling to scenarios/vnfs"
+    sql "UPDATE instance_nets SET vim_net_id='' WHERE vim_net_id is NULL;"
+    sql "ALTER TABLE instance_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(36) NOT NULL;"
+
+    echo "      Restore back  'instance_scenarios' coupling to scenarios"
+	sql "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_scenarios;"
+    sql "ALTER TABLE instance_scenarios CHANGE COLUMN scenario_id scenario_id VARCHAR(36) NOT NULL;"
+	sql "ALTER TABLE instance_scenarios ADD CONSTRAINT FK_instance_scenarios_scenarios FOREIGN KEY (scenario_id) "\
+	    "REFERENCES scenarios (uuid);"
+
+    echo "      Delete table instance_actions"
+    sql "DROP TABLE IF EXISTS vim_actions"
+    sql "DROP TABLE IF EXISTS instance_actions"
+    sql "DELETE FROM schema_version WHERE version_int='26';"
+}
+
+function upgrade_to_27(){
+    echo "      Added 'encrypted_RO_priv_key','RO_pub_key' to table 'nfvo_tenants'"
+    sql "ALTER TABLE nfvo_tenants ADD COLUMN encrypted_RO_priv_key VARCHAR(2000) NULL AFTER description;"
+    sql "ALTER TABLE nfvo_tenants ADD COLUMN RO_pub_key VARCHAR(510) NULL AFTER encrypted_RO_priv_key;"
+
+    sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
+         "VALUES (27, '0.27', '0.5.25', 'Added encrypted_RO_priv_key,RO_pub_key to table nfvo_tenants', '2017-09-29');"
+}
+function downgrade_from_27(){
+    echo "      Remove 'encrypted_RO_priv_key','RO_pub_key' from table 'nfvo_tenants'"
+    sql "ALTER TABLE nfvo_tenants DROP COLUMN encrypted_RO_priv_key;"
+    sql "ALTER TABLE nfvo_tenants DROP COLUMN RO_pub_key;"
+    sql "DELETE FROM schema_version WHERE version_int='27';"
+}
+function upgrade_to_28(){
+    echo "      [Adding necessary tables for VNFFG]"
+    echo "      Adding sce_vnffgs"
+    sql "CREATE TABLE IF NOT EXISTS sce_vnffgs (
+            uuid VARCHAR(36) NOT NULL,
+            tenant_id VARCHAR(36) NULL DEFAULT NULL,
+            name VARCHAR(255) NOT NULL,
+            description VARCHAR(255) NULL DEFAULT NULL,
+            vendor VARCHAR(255) NULL DEFAULT NULL,
+            scenario_id VARCHAR(36) NOT NULL,
+            created_at DOUBLE NOT NULL,
+            modified_at DOUBLE NULL DEFAULT NULL,
+        PRIMARY KEY (uuid),
+        INDEX FK_scenarios_sce_vnffg (scenario_id),
+        CONSTRAINT FK_scenarios_vnffg FOREIGN KEY (tenant_id) REFERENCES scenarios (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
+        COLLATE='utf8_general_ci'
+        ENGINE=InnoDB;"
+    echo "      Adding sce_rsps"
+    sql "CREATE TABLE IF NOT EXISTS sce_rsps (
+            uuid VARCHAR(36) NOT NULL,
+            tenant_id VARCHAR(36) NULL DEFAULT NULL,
+            name VARCHAR(255) NOT NULL,
+            sce_vnffg_id VARCHAR(36) NOT NULL,
+            created_at DOUBLE NOT NULL,
+            modified_at DOUBLE NULL DEFAULT NULL,
+        PRIMARY KEY (uuid),
+        INDEX FK_sce_vnffgs_rsp (sce_vnffg_id),
+        CONSTRAINT FK_sce_vnffgs_rsp FOREIGN KEY (sce_vnffg_id) REFERENCES sce_vnffgs (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
+        COLLATE='utf8_general_ci'
+        ENGINE=InnoDB;"
+    echo "      Adding sce_rsp_hops"
+    sql "CREATE TABLE IF NOT EXISTS sce_rsp_hops (
+            uuid VARCHAR(36) NOT NULL,
+            if_order INT DEFAULT 0 NOT NULL,
+            interface_id VARCHAR(36) NOT NULL,
+            sce_vnf_id VARCHAR(36) NOT NULL,
+            sce_rsp_id VARCHAR(36) NOT NULL,
+            created_at DOUBLE NOT NULL,
+            modified_at DOUBLE NULL DEFAULT NULL,
+        PRIMARY KEY (uuid),
+        INDEX FK_interfaces_rsp_hop (interface_id),
+        INDEX FK_sce_vnfs_rsp_hop (sce_vnf_id),
+        INDEX FK_sce_rsps_rsp_hop (sce_rsp_id),
+        CONSTRAINT FK_interfaces_rsp_hop FOREIGN KEY (interface_id) REFERENCES interfaces (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
+        CONSTRAINT FK_sce_vnfs_rsp_hop FOREIGN KEY (sce_vnf_id) REFERENCES sce_vnfs (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
+        CONSTRAINT FK_sce_rsps_rsp_hop FOREIGN KEY (sce_rsp_id) REFERENCES sce_rsps (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
+        COLLATE='utf8_general_ci'
+        ENGINE=InnoDB;"
+    echo "      Adding sce_classifiers"
+    sql "CREATE TABLE IF NOT EXISTS sce_classifiers (
+            uuid VARCHAR(36) NOT NULL,
+            tenant_id VARCHAR(36) NULL DEFAULT NULL,
+            name VARCHAR(255) NOT NULL,
+            sce_vnffg_id VARCHAR(36) NOT NULL,
+            sce_rsp_id VARCHAR(36) NOT NULL,
+            sce_vnf_id VARCHAR(36) NOT NULL,
+            interface_id VARCHAR(36) NOT NULL,
+            created_at DOUBLE NOT NULL,
+            modified_at DOUBLE NULL DEFAULT NULL,
+        PRIMARY KEY (uuid),
+        INDEX FK_sce_vnffgs_classifier (sce_vnffg_id),
+        INDEX FK_sce_rsps_classifier (sce_rsp_id),
+        INDEX FK_sce_vnfs_classifier (sce_vnf_id),
+        INDEX FK_interfaces_classifier (interface_id),
+        CONSTRAINT FK_sce_vnffgs_classifier FOREIGN KEY (sce_vnffg_id) REFERENCES sce_vnffgs (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
+        CONSTRAINT FK_sce_rsps_classifier FOREIGN KEY (sce_rsp_id) REFERENCES sce_rsps (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
+        CONSTRAINT FK_sce_vnfs_classifier FOREIGN KEY (sce_vnf_id) REFERENCES sce_vnfs (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
+        CONSTRAINT FK_interfaces_classifier FOREIGN KEY (interface_id) REFERENCES interfaces (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
+        COLLATE='utf8_general_ci'
+        ENGINE=InnoDB;"
+    echo "      Adding sce_classifier_matches"
+    sql "CREATE TABLE IF NOT EXISTS sce_classifier_matches (
+            uuid VARCHAR(36) NOT NULL,
+            ip_proto VARCHAR(2) NOT NULL,
+            source_ip VARCHAR(16) NOT NULL,
+            destination_ip VARCHAR(16) NOT NULL,
+            source_port VARCHAR(5) NOT NULL,
+            destination_port VARCHAR(5) NOT NULL,
+            sce_classifier_id VARCHAR(36) NOT NULL,
+            created_at DOUBLE NOT NULL,
+            modified_at DOUBLE NULL DEFAULT NULL,
+        PRIMARY KEY (uuid),
+        INDEX FK_classifiers_classifier_match (sce_classifier_id),
+        CONSTRAINT FK_sce_classifiers_classifier_match FOREIGN KEY (sce_classifier_id) REFERENCES sce_classifiers (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
+        COLLATE='utf8_general_ci'
+        ENGINE=InnoDB;"
+
+    echo "      [Adding necessary tables for VNFFG-SFC instance mapping]"
+    echo "      Adding instance_sfis"
+    sql "CREATE TABLE IF NOT EXISTS instance_sfis (
+          uuid varchar(36) NOT NULL,
+          instance_scenario_id varchar(36) NOT NULL,
+          vim_sfi_id varchar(36) DEFAULT NULL,
+          sce_rsp_hop_id varchar(36) DEFAULT NULL,
+          datacenter_id varchar(36) DEFAULT NULL,
+          datacenter_tenant_id varchar(36) DEFAULT NULL,
+          status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
+          error_msg varchar(1024) DEFAULT NULL,
+          vim_info text,
+          created_at double NOT NULL,
+          modified_at double DEFAULT NULL,
+          PRIMARY KEY (uuid),
+      KEY FK_instance_sfis_instance_scenarios (instance_scenario_id),
+      KEY FK_instance_sfis_sce_rsp_hops (sce_rsp_hop_id),
+      KEY FK_instance_sfis_datacenters (datacenter_id),
+      KEY FK_instance_sfis_datacenter_tenants (datacenter_tenant_id),
+      CONSTRAINT FK_instance_sfis_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid),
+      CONSTRAINT FK_instance_sfis_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid),
+      CONSTRAINT FK_instance_sfis_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE,
+      CONSTRAINT FK_instance_sfis_sce_rsp_hops FOREIGN KEY (sce_rsp_hop_id) REFERENCES sce_rsp_hops (uuid) ON DELETE SET NULL ON UPDATE CASCADE)
+      COLLATE='utf8_general_ci'
+      ENGINE=InnoDB;"
+    echo "      Adding instance_sfs"
+    sql "CREATE TABLE IF NOT EXISTS instance_sfs (
+          uuid varchar(36) NOT NULL,
+          instance_scenario_id varchar(36) NOT NULL,
+          vim_sf_id varchar(36) DEFAULT NULL,
+          sce_rsp_hop_id varchar(36) DEFAULT NULL,
+          datacenter_id varchar(36) DEFAULT NULL,
+          datacenter_tenant_id varchar(36) DEFAULT NULL,
+          status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
+          error_msg varchar(1024) DEFAULT NULL,
+          vim_info text,
+          created_at double NOT NULL,
+          modified_at double DEFAULT NULL,
+      PRIMARY KEY (uuid),
+      KEY FK_instance_sfs_instance_scenarios (instance_scenario_id),
+      KEY FK_instance_sfs_sce_rsp_hops (sce_rsp_hop_id),
+      KEY FK_instance_sfs_datacenters (datacenter_id),
+      KEY FK_instance_sfs_datacenter_tenants (datacenter_tenant_id),
+      CONSTRAINT FK_instance_sfs_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid),
+      CONSTRAINT FK_instance_sfs_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid),
+      CONSTRAINT FK_instance_sfs_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE,
+      CONSTRAINT FK_instance_sfs_sce_rsp_hops FOREIGN KEY (sce_rsp_hop_id) REFERENCES sce_rsp_hops (uuid) ON DELETE SET NULL ON UPDATE CASCADE)
+      COLLATE='utf8_general_ci'
+      ENGINE=InnoDB;"
+    echo "      Adding instance_classifications"
+    sql "CREATE TABLE IF NOT EXISTS instance_classifications (
+          uuid varchar(36) NOT NULL,
+          instance_scenario_id varchar(36) NOT NULL,
+          vim_classification_id varchar(36) DEFAULT NULL,
+          sce_classifier_match_id varchar(36) DEFAULT NULL,
+          datacenter_id varchar(36) DEFAULT NULL,
+          datacenter_tenant_id varchar(36) DEFAULT NULL,
+          status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
+          error_msg varchar(1024) DEFAULT NULL,
+          vim_info text,
+          created_at double NOT NULL,
+          modified_at double DEFAULT NULL,
+      PRIMARY KEY (uuid),
+      KEY FK_instance_classifications_instance_scenarios (instance_scenario_id),
+      KEY FK_instance_classifications_sce_classifier_matches (sce_classifier_match_id),
+      KEY FK_instance_classifications_datacenters (datacenter_id),
+      KEY FK_instance_classifications_datacenter_tenants (datacenter_tenant_id),
+      CONSTRAINT FK_instance_classifications_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid),
+      CONSTRAINT FK_instance_classifications_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid),
+      CONSTRAINT FK_instance_classifications_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE,
+      CONSTRAINT FK_instance_classifications_sce_classifier_matches FOREIGN KEY (sce_classifier_match_id) REFERENCES sce_classifier_matches (uuid) ON DELETE SET NULL ON UPDATE CASCADE)
+      COLLATE='utf8_general_ci'
+      ENGINE=InnoDB;"
+    echo "      Adding instance_sfps"
+    sql "CREATE TABLE IF NOT EXISTS instance_sfps (
+          uuid varchar(36) NOT NULL,
+          instance_scenario_id varchar(36) NOT NULL,
+          vim_sfp_id varchar(36) DEFAULT NULL,
+          sce_rsp_id varchar(36) DEFAULT NULL,
+          datacenter_id varchar(36) DEFAULT NULL,
+          datacenter_tenant_id varchar(36) DEFAULT NULL,
+          status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
+          error_msg varchar(1024) DEFAULT NULL,
+          vim_info text,
+          created_at double NOT NULL,
+          modified_at double DEFAULT NULL,
+      PRIMARY KEY (uuid),
+      KEY FK_instance_sfps_instance_scenarios (instance_scenario_id),
+      KEY FK_instance_sfps_sce_rsps (sce_rsp_id),
+      KEY FK_instance_sfps_datacenters (datacenter_id),
+      KEY FK_instance_sfps_datacenter_tenants (datacenter_tenant_id),
+      CONSTRAINT FK_instance_sfps_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid),
+      CONSTRAINT FK_instance_sfps_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid),
+      CONSTRAINT FK_instance_sfps_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE,
+      CONSTRAINT FK_instance_sfps_sce_rsps FOREIGN KEY (sce_rsp_id) REFERENCES sce_rsps (uuid) ON DELETE SET NULL ON UPDATE CASCADE)
+      COLLATE='utf8_general_ci'
+      ENGINE=InnoDB;"
+
+
+    echo "      [Altering vim_actions table]"
+    sql "ALTER TABLE vim_actions MODIFY COLUMN item ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces','instance_sfis','instance_sfs','instance_classifications','instance_sfps') NOT NULL COMMENT 'table where the item is stored'"
+
+    sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
+         "VALUES (28, '0.28', '0.5.28', 'Adding VNFFG-related tables', '2017-11-20');"
+}
+function downgrade_from_28(){
+    echo "      [Undo adding the VNFFG tables]"
+    echo "      Dropping instance_sfps"
+    sql "DROP TABLE IF EXISTS instance_sfps;"
+    echo "      Dropping sce_classifications"
+    sql "DROP TABLE IF EXISTS instance_classifications;"
+    echo "      Dropping instance_sfs"
+    sql "DROP TABLE IF EXISTS instance_sfs;"
+    echo "      Dropping instance_sfis"
+    sql "DROP TABLE IF EXISTS instance_sfis;"
+    echo "      Dropping sce_classifier_matches"
+    echo "      [Undo adding the VNFFG-SFC instance mapping tables]"
+    sql "DROP TABLE IF EXISTS sce_classifier_matches;"
+    echo "      Dropping sce_classifiers"
+    sql "DROP TABLE IF EXISTS sce_classifiers;"
+    echo "      Dropping sce_rsp_hops"
+    sql "DROP TABLE IF EXISTS sce_rsp_hops;"
+    echo "      Dropping sce_rsps"
+    sql "DROP TABLE IF EXISTS sce_rsps;"
+    echo "      Dropping sce_vnffgs"
+    sql "DROP TABLE IF EXISTS sce_vnffgs;"
+    echo "      [Altering vim_actions table]"
+    sql "ALTER TABLE vim_actions MODIFY COLUMN item ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces') NOT NULL COMMENT 'table where the item is stored'"
+    sql "DELETE FROM schema_version WHERE version_int='28';"
+}
+function upgrade_to_29(){
+    echo "      Change 'member_vnf_index' from int to str at 'sce_vnfs'"
+    sql "ALTER TABLE sce_vnfs CHANGE COLUMN member_vnf_index member_vnf_index VARCHAR(255) NULL DEFAULT NULL AFTER uuid;"
+    echo "      Add osm_id to 'nets's and 'sce_nets'"
+    sql "ALTER TABLE nets ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;"
+    sql "ALTER TABLE sce_nets ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;"
+    sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
+         "VALUES (29, '0.29', '0.5.59', 'Change member_vnf_index to str accordingly to the model', '2018-04-11');"
+}
+function downgrade_from_29(){
+    echo "      Change back 'member_vnf_index' from str to int at 'sce_vnfs'"
+    sql "ALTER TABLE sce_vnfs CHANGE COLUMN member_vnf_index member_vnf_index SMALLINT NULL DEFAULT NULL AFTER uuid;"
+    echo "      Remove osm_id from 'nets's and 'sce_nets'"
+    sql "ALTER TABLE nets DROP COLUMN osm_id;"
+    sql "ALTER TABLE sce_nets DROP COLUMN osm_id;"
+    sql "DELETE FROM schema_version WHERE version_int='29';"
+}
+function upgrade_to_30(){
+    echo "      Add 'image_list' at 'vms' to allocate alternative images"
+    sql "ALTER TABLE vms ADD COLUMN image_list TEXT NULL COMMENT 'Alternative images' AFTER image_id;"
+    sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
+         "VALUES (30, '0.30', '0.5.60', 'Add image_list to vms', '2018-04-24');"
+}
+function downgrade_from_30(){
+    echo "      Remove back 'image_list' from 'vms' to allocate alternative images"
+    sql "ALTER TABLE vms DROP COLUMN image_list;"
+    sql "DELETE FROM schema_version WHERE version_int='30';"
+}
+function upgrade_to_31(){
+    echo "      Add 'vim_network_name' at 'sce_nets'"
+    sql "ALTER TABLE sce_nets ADD COLUMN vim_network_name VARCHAR(255) NULL DEFAULT NULL AFTER description;"
+    sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
+         "VALUES (31, '0.31', '0.5.61', 'Add vim_network_name to sce_nets', '2018-05-03');"
+}
+function downgrade_from_31(){
+    echo "      Remove back 'vim_network_name' from 'sce_nets'"
+    sql "ALTER TABLE sce_nets DROP COLUMN vim_network_name;"
+    sql "DELETE FROM schema_version WHERE version_int='31';"
+}
+function upgrade_to_32(){
+    echo "      Add 'vim_name' to 'instance_vms'"
+    sql "ALTER TABLE instance_vms ADD COLUMN vim_name VARCHAR(255) NULL DEFAULT NULL AFTER vim_vm_id;"
+    sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
+         "VALUES (32, '0.32', '0.5.70', 'Add vim_name to instance vms', '2018-06-28');"
+}
+function downgrade_from_32(){
+    echo "      Remove back 'vim_name' from 'instance_vms'"
+    sql "ALTER TABLE instance_vms DROP COLUMN vim_name;"
+    sql "DELETE FROM schema_version WHERE version_int='32';"
+}
+
+function upgrade_to_33(){
+    echo "      Add PDU information to 'vms"
+    sql "ALTER TABLE vms ADD COLUMN pdu_type VARCHAR(255) NULL DEFAULT NULL AFTER osm_id;"
+    sql "ALTER TABLE instance_nets ADD COLUMN vim_name VARCHAR(255) NULL DEFAULT NULL AFTER vim_net_id;"
+    sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
+         "VALUES (33, '0.33', '0.5.82', 'Add pdu information to vms', '2018-11-13');"
+}
+function downgrade_from_33(){
+    echo "      Remove back PDU information from' vms'"
+    sql "ALTER TABLE vms DROP COLUMN pdu_type;"
+    sql "ALTER TABLE instance_nets DROP COLUMN vim_name;"
+    sql "DELETE FROM schema_version WHERE version_int='33';"
+}
+
+
 function upgrade_to_X(){
     echo "      change 'datacenter_nets'"
     sql "ALTER TABLE datacenter_nets ADD COLUMN vim_tenant_id VARCHAR(36) NOT NULL AFTER datacenter_id, DROP INDEX name_datacenter_id, ADD UNIQUE INDEX name_datacenter_id (name, datacenter_id, vim_tenant_id);"
@@ -858,6 +1318,19 @@ function downgrade_from_X(){
     echo "      Change back 'datacenter_nets'"
     sql "ALTER TABLE datacenter_nets DROP COLUMN vim_tenant_id, DROP INDEX name_datacenter_id, ADD UNIQUE INDEX name_datacenter_id (name, datacenter_id);"
 }
+
+function upgrade_to_34() {
+    echo "      Create databases required for WIM features"
+    script="$(find "${DBUTILS}/migrations/up" -iname "34*.sql" | tail -1)"
+    sql "source ${script}"
+}
+
+function downgrade_from_34() {
+    echo "      Drop databases required for WIM features"
+    script="$(find "${DBUTILS}/migrations/down" -iname "34*.sql" | tail -1)"
+    sql "source ${script}"
+}
+
 #TODO ... put functions here
 
 # echo "db version = "${DATABASE_VER_NUM}
@@ -870,14 +1343,14 @@ mysqldump $DEF_EXTRA_FILE_PARAM --add-drop-table --add-drop-database --routines
 
 function rollback_db()
 {
-    cat $TEMPFILE2 | mysql $DEF_EXTRA_FILE_PARAM && echo "    Rollback database OK" ||
-        echo "    Rollback database FAIL"
+    cat $TEMPFILE2 | mysql $DEF_EXTRA_FILE_PARAM && echo "    Aborted! Rollback database OK" ||
+        echo "    Aborted! Rollback database FAIL"
     exit 1
 }
 
 function sql()    # send a sql command
 {
-    echo "$*" | $DBCMD || ! echo "    ERROR. Aborted!" || rollback_db
+    echo "$*" | $DBCMD || ! echo "    ERROR with command '$*'" || rollback_db
     return 0
 }