+ 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';"
+}
+
+function upgrade_to_13(){
+ # echo " upgrade database from version 0.12 to version 0.13"
+ echo " add cloud_config at 'scenarios', 'instance_scenarios'"
+ sql "ALTER TABLE scenarios ADD COLUMN cloud_config MEDIUMTEXT NULL DEFAULT NULL AFTER descriptor;"
+ sql "ALTER TABLE instance_scenarios ADD COLUMN cloud_config MEDIUMTEXT NULL DEFAULT NULL AFTER modified_at;"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (13, '0.13', '0.4.47', 'insert cloud-config at scenarios,instance_scenarios', '2016-08-30');"
+}
+function downgrade_from_13(){
+ # echo " downgrade database from version 0.13 to version 0.12"
+ echo " remove cloud_config at 'scenarios', 'instance_scenarios'"
+ sql "ALTER TABLE scenarios DROP COLUMN cloud_config;"
+ sql "ALTER TABLE instance_scenarios DROP COLUMN cloud_config;"
+ sql "DELETE FROM schema_version WHERE version_int='13';"
+}
+
+function upgrade_to_14(){
+ # echo " upgrade database from version 0.13 to version 0.14"
+ echo " remove unique index vim_net_id, instance_scenario_id at table 'instance_nets'"
+ sql "ALTER TABLE instance_nets DROP INDEX vim_net_id_instance_scenario_id;"
+ sql "ALTER TABLE instance_nets CHANGE COLUMN external created ENUM('true','false') NOT NULL DEFAULT 'false' COMMENT 'Created or already exists at VIM' AFTER multipoint;"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (14, '0.14', '0.4.57', 'remove unique index vim_net_id, instance_scenario_id', '2016-09-26');"
+}
+function downgrade_from_14(){
+ # echo " downgrade database from version 0.14 to version 0.13"
+ echo " remove cloud_config at 'scenarios', 'instance_scenarios'"
+ sql "ALTER TABLE instance_nets ADD UNIQUE INDEX vim_net_id_instance_scenario_id (vim_net_id, instance_scenario_id);"
+ sql "ALTER TABLE instance_nets CHANGE COLUMN created external ENUM('true','false') NOT NULL DEFAULT 'false' COMMENT 'If external, means that it already exists at VIM' AFTER multipoint;"
+ sql "DELETE FROM schema_version WHERE version_int='14';"
+}
+
+function upgrade_to_15(){
+ # echo " upgrade database from version 0.14 to version 0.15"
+ echo " add columns 'universal_name' and 'checksum' at table 'images', add unique index universal_name_checksum, and change location to allow NULL; change column 'image_path' in table 'vms' to allow NULL"
+ sql "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL DEFAULT NULL AFTER name;"
+ sql "ALTER TABLE images ALTER location DROP DEFAULT;"
+ sql "ALTER TABLE images ADD COLUMN universal_name VARCHAR(255) NULL AFTER name, CHANGE COLUMN location location VARCHAR(200) NULL AFTER checksum, ADD UNIQUE INDEX universal_name_checksum (universal_name, checksum);"
+ sql "ALTER TABLE vms ALTER image_path DROP DEFAULT;"
+ sql "ALTER TABLE vms CHANGE COLUMN image_path image_path VARCHAR(100) NULL COMMENT 'Path where the image of the VM is located' AFTER image_id;"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (15, '0.15', '0.4.59', 'add columns universal_name and checksum at table images, add unique index universal_name_checksum, and change location to allow NULL; change column image_path in table vms to allow NULL', '2016-09-27');"
+}
+function downgrade_from_15(){
+ # echo " downgrade database from version 0.15 to version 0.14"
+ echo " remove columns 'universal_name' and 'checksum' from table 'images', remove index universal_name_checksum, change location NOT NULL; change column 'image_path' in table 'vms' to NOT NULL"
+ sql "ALTER TABLE images DROP INDEX universal_name_checksum;"
+ sql "ALTER TABLE images ALTER location DROP DEFAULT;"
+ sql "ALTER TABLE images CHANGE COLUMN location location VARCHAR(200) NOT NULL AFTER checksum;"
+ sql "ALTER TABLE images DROP COLUMN universal_name;"
+ sql "ALTER TABLE images DROP COLUMN checksum;"
+ sql "ALTER TABLE vms ALTER image_path DROP DEFAULT;"
+ sql "ALTER TABLE vms CHANGE COLUMN image_path image_path VARCHAR(100) NOT NULL COMMENT 'Path where the image of the VM is located' AFTER image_id;"
+ sql "DELETE FROM schema_version WHERE version_int='15';"
+}
+
+function upgrade_to_16(){
+ # echo " upgrade database from version 0.15 to version 0.16"
+ echo " add column 'config' at table 'datacenter_tenants', enlarge 'vim_tenant_name/id'"
+ sql "ALTER TABLE datacenter_tenants ADD COLUMN config VARCHAR(4000) NULL DEFAULT NULL AFTER passwd;"
+ sql "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(256) NULL DEFAULT NULL AFTER datacenter_id;"
+ sql "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_id vim_tenant_id VARCHAR(256) NULL DEFAULT NULL COMMENT 'Tenant ID at VIM' AFTER vim_tenant_name;"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (16, '0.16', '0.5.2', 'enlarge vim_tenant_name and id. New config at datacenter_tenants', '2016-10-11');"
+}
+function downgrade_from_16(){
+ # echo " downgrade database from version 0.16 to version 0.15"
+ echo " remove column 'config' at table 'datacenter_tenants', restoring lenght 'vim_tenant_name/id'"
+ sql "ALTER TABLE datacenter_tenants DROP COLUMN config;"
+ sql "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(64) NULL DEFAULT NULL AFTER datacenter_id;"
+ sql "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_id vim_tenant_id VARCHAR(36) NULL DEFAULT NULL COMMENT 'Tenant ID at VIM' AFTER vim_tenant_name;"
+ sql "DELETE FROM schema_version WHERE version_int='16';"
+}
+
+function upgrade_to_17(){
+ # echo " upgrade database from version 0.16 to version 0.17"
+ echo " add column 'extended' at table 'datacenter_flavors'"
+ sql "ALTER TABLE datacenters_flavors ADD extended varchar(2000) NULL COMMENT 'Extra description json format of additional devices';"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (17, '0.17', '0.5.3', 'Extra description json format of additional devices in datacenter_flavors', '2016-12-20');"
+}
+function downgrade_from_17(){
+ # echo " downgrade database from version 0.17 to version 0.16"
+ echo " remove column 'extended' from table 'datacenter_flavors'"
+ sql "ALTER TABLE datacenters_flavors DROP COLUMN extended;"
+ sql "DELETE FROM schema_version WHERE version_int='17';"
+}
+
+function upgrade_to_18(){
+ # echo " upgrade database from version 0.17 to version 0.18"
+ echo " add columns 'floating_ip' and 'port_security' at tables 'interfaces' and 'instance_interfaces'"
+ sql "ALTER TABLE interfaces ADD floating_ip BOOL DEFAULT 0 NOT NULL COMMENT 'Indicates if a floating_ip must be associated to this interface';"
+ sql "ALTER TABLE interfaces ADD port_security BOOL DEFAULT 1 NOT NULL COMMENT 'Indicates if port security must be enabled or disabled. By default it is enabled';"
+ sql "ALTER TABLE instance_interfaces ADD floating_ip BOOL DEFAULT 0 NOT NULL COMMENT 'Indicates if a floating_ip must be associated to this interface';"
+ sql "ALTER TABLE instance_interfaces ADD port_security BOOL DEFAULT 1 NOT NULL COMMENT 'Indicates if port security must be enabled or disabled. By default it is enabled';"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (18, '0.18', '0.5.4', 'Add columns \'floating_ip\' and \'port_security\' at tables \'interfaces\' and \'instance_interfaces\'', '2017-01-09');"
+}
+function downgrade_from_18(){
+ # echo " downgrade database from version 0.18 to version 0.17"
+ echo " remove columns 'floating_ip' and 'port_security' from tables 'interfaces' and 'instance_interfaces'"
+ sql "ALTER TABLE interfaces DROP COLUMN floating_ip;"
+ sql "ALTER TABLE interfaces DROP COLUMN port_security;"
+ sql "ALTER TABLE instance_interfaces DROP COLUMN floating_ip;"
+ sql "ALTER TABLE instance_interfaces DROP COLUMN port_security;"
+ sql "DELETE FROM schema_version WHERE version_int='18';"
+}
+
+function upgrade_to_19(){
+ # echo " upgrade database from version 0.18 to version 0.19"
+ echo " add column 'boot_data' at table 'vms'"
+ sql "ALTER TABLE vms ADD COLUMN boot_data TEXT NULL DEFAULT NULL AFTER image_path;"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (19, '0.19', '0.5.5', 'Extra Boot-data content at VNFC (vms)', '2017-01-11');"
+}
+function downgrade_from_19(){
+ # echo " downgrade database from version 0.19 to version 0.18"
+ echo " remove column 'boot_data' from table 'vms'"
+ sql "ALTER TABLE vms DROP COLUMN boot_data;"
+ sql "DELETE FROM schema_version WHERE version_int='19';"
+}
+
+function upgrade_to_20(){
+ # echo " upgrade database from version 0.19 to version 0.20"
+ echo " add column 'sdn_net_id' at table 'instance_nets' and columns 'sdn_port_id', 'compute_node', 'pci' and 'vlan' to table 'instance_interfaces'"
+ sql "ALTER TABLE instance_nets ADD sdn_net_id varchar(36) DEFAULT NULL NULL COMMENT 'Network id in ovim';"
+ sql "ALTER TABLE instance_interfaces ADD sdn_port_id varchar(36) DEFAULT NULL NULL COMMENT 'Port id in ovim';"
+ sql "ALTER TABLE instance_interfaces ADD compute_node varchar(100) DEFAULT NULL NULL COMMENT 'Compute node id used to specify the SDN port mapping';"
+ sql "ALTER TABLE instance_interfaces ADD pci varchar(12) DEFAULT NULL NULL COMMENT 'PCI of the physical port in the host';"
+ sql "ALTER TABLE instance_interfaces ADD vlan SMALLINT UNSIGNED DEFAULT NULL NULL COMMENT 'VLAN tag used by the port';"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (20, '0.20', '0.5.9', 'Added columns to store dataplane connectivity info', '2017-03-13');"
+}
+function downgrade_from_20(){
+ # echo " downgrade database from version 0.20 to version 0.19"
+ echo " remove column 'sdn_net_id' at table 'instance_nets' and columns 'sdn_port_id', 'compute_node', 'pci' and 'vlan' to table 'instance_interfaces'"
+ sql "ALTER TABLE instance_nets DROP COLUMN sdn_net_id;"
+ sql "ALTER TABLE instance_interfaces DROP COLUMN vlan;"
+ sql "ALTER TABLE instance_interfaces DROP COLUMN pci;"
+ sql "ALTER TABLE instance_interfaces DROP COLUMN compute_node;"
+ sql "ALTER TABLE instance_interfaces DROP COLUMN sdn_port_id;"
+ sql "DELETE FROM schema_version WHERE version_int='20';"
+}
+
+function upgrade_to_21(){
+ # echo " upgrade database from version 0.20 to version 0.21"
+ echo " edit 'instance_nets' to allow instance_scenario_id=None"
+ sql "ALTER TABLE instance_nets MODIFY COLUMN instance_scenario_id varchar(36) NULL;"
+ echo " enlarge column 'dns_address' at table 'ip_profiles'"
+ sql "ALTER TABLE ip_profiles MODIFY dns_address varchar(255) DEFAULT NULL NULL "\
+ "comment 'dns ip list separated by semicolon';"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (21, '0.21', '0.5.15', 'Edit instance_nets to allow instance_scenario_id=None and enlarge column dns_address at table ip_profiles', '2017-06-02');"
+}
+function downgrade_from_21(){
+ # echo " downgrade database from version 0.21 to version 0.20"
+ echo " edit 'instance_nets' to disallow instance_scenario_id=None"
+ #Delete all lines with a instance_scenario_id=NULL in order to disable this option
+ sql "DELETE FROM instance_nets WHERE instance_scenario_id IS NULL;"
+ sql "ALTER TABLE instance_nets MODIFY COLUMN instance_scenario_id varchar(36) NOT NULL;"
+ echo " shorten column 'dns_address' at table 'ip_profiles'"
+ sql "ALTER TABLE ip_profiles MODIFY dns_address varchar(64) DEFAULT NULL NULL;"
+ sql "DELETE FROM schema_version WHERE version_int='21';"
+}
+
+function upgrade_to_22(){
+ # echo " upgrade database from version 0.21 to version 0.22"
+ echo " Changed type of ram in 'flavors' from SMALLINT to MEDIUMINT"
+ sql "ALTER TABLE flavors CHANGE COLUMN ram ram MEDIUMINT(7) UNSIGNED NULL DEFAULT NULL AFTER disk;"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (22, '0.22', '0.5.16', 'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-06-02');"
+}
+function downgrade_from_22(){
+ # echo " downgrade database from version 0.22 to version 0.21"
+ echo " Changed type of ram in 'flavors' from MEDIUMINT to SMALLINT"
+ sql "ALTER TABLE flavors CHANGE COLUMN ram ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER disk;"
+ sql "DELETE FROM schema_version WHERE version_int='22';"
+}
+
+function upgrade_to_23(){
+ # echo " upgrade database from version 0.22 to version 0.23"
+ echo " add column 'availability_zone' at table 'vms'"
+ sql "ALTER TABLE mano_db.vms ADD COLUMN availability_zone VARCHAR(255) NULL AFTER modified_at;"
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (23, '0.23', '0.5.20',"\
+ "'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-08-29');"
+}
+function downgrade_from_23(){
+ # echo " downgrade database from version 0.23 to version 0.22"
+ echo " remove column 'availability_zone' from table 'vms'"
+ sql "ALTER TABLE mano_db.vms DROP COLUMN availability_zone;"
+ sql "DELETE FROM schema_version WHERE version_int='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');"
+}
+function downgrade_from_24(){
+ # echo " downgrade database from version 0.24 to version 0.23"
+ echo " Remove 'count' from table 'vms'"
+ sql "ALTER TABLE vms DROP COLUMN count;"
+ sql "DELETE FROM schema_version WHERE version_int='24';"
+}
+function upgrade_to_25(){
+ # echo " upgrade database from version 0.24 to version 0.25"
+ echo " Add 'osm_id','short_name','vendor' to tables 'vnfs', 'scenarios'"
+ for table in vnfs scenarios; do
+ sql "ALTER TABLE $table ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid, "\
+ "ADD UNIQUE INDEX osm_id_tenant_id (osm_id, tenant_id), "\
+ "ADD COLUMN short_name VARCHAR(255) NULL AFTER name, "\
+ "ADD COLUMN vendor VARCHAR(255) NULL AFTER description;"
+ done
+ sql "ALTER TABLE vnfs ADD COLUMN mgmt_access VARCHAR(2000) NULL AFTER vendor;"
+ sql "ALTER TABLE vms ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;"
+ sql "ALTER TABLE sce_vnfs ADD COLUMN member_vnf_index SMALLINT(6) NULL DEFAULT NULL AFTER uuid;"
+ echo " Add 'security_group' to table 'ip_profiles'"
+ sql "ALTER TABLE ip_profiles ADD COLUMN security_group VARCHAR(255) NULL DEFAULT NULL AFTER dhcp_count;"
+
+ sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
+ "VALUES (25, '0.25', '0.5.22', 'Added osm_id to vnfs,scenarios', '2017-09-01');"
+}
+function downgrade_from_25(){
+ # echo " downgrade database from version 0.25 to version 0.24"
+ echo " Remove 'osm_id','short_name','vendor' from tables 'vnfs', 'scenarios'"
+ for table in vnfs scenarios; do
+ sql "ALTER TABLE $table DROP INDEX osm_id_tenant_id, DROP COLUMN osm_id, "\
+ "DROP COLUMN short_name, DROP COLUMN vendor;"
+ done
+ sql "ALTER TABLE vnfs DROP COLUMN mgmt_access;"
+ sql "ALTER TABLE vms DROP COLUMN osm_id;"
+ sql "ALTER TABLE sce_vnfs DROP COLUMN member_vnf_index;"
+ echo " Remove 'security_group' from table 'ip_profiles'"
+ sql "ALTER TABLE ip_profiles DROP COLUMN security_group;"
+
+ 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';"