+ sql "ALTER TABLE images DROP COLUMN checksum;"
+ sql "DELETE FROM schema_version WHERE version_int = '8';"
+}
+
+function upgrade_to_9(){
+ # echo " upgrade database from version 0.8 to version 0.9"
+ echo " change length of columns 'path' and 'name' to 255 in table 'images', and change length of column "\
+ "'name' to 255 in table 'flavors'"
+ sql "ALTER TABLE images CHANGE COLUMN path path VARCHAR(255) NOT NULL AFTER uuid, CHANGE COLUMN name name "\
+ "VARCHAR(255) NOT NULL AFTER path;"
+ sql "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (9, '0.9', '0.5.1','increase length of columns path and name to 255 in table images, "\
+ "and change length of column name to 255 in table flavors', '2017-01-10');"
+}
+function downgrade_from_9(){
+ # echo " downgrade database from version 0.9 to version 0.8"
+ echo " change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
+ sql "ALTER TABLE images CHANGE COLUMN path path VARCHAR(100) NOT NULL AFTER uuid, CHANGE COLUMN name name "\
+ "VARCHAR(64) NOT NULL AFTER path;"
+ sql "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;"
+ sql "DELETE FROM schema_version WHERE version_int = '9';"
+}
+
+function upgrade_to_10(){
+ # echo " upgrade database from version 0.9 to version 0.10"
+ echo " change types at 'ports'"
+ sql "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external','instance:ovs',"\
+ "'controller:ovs') NOT NULL DEFAULT 'instance:bridge' AFTER status;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (10, '0.10', '0.5.2', 'change ports type, adding instance:ovs', '2017-02-01');"
+}
+function downgrade_from_10(){
+ # echo " downgrade database from version 0.10 to version 0.9"
+ echo " change back types at 'ports'"
+ sql "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external') "\
+ "NOT NULL DEFAULT 'instance:bridge' AFTER status;"
+ sql "DELETE FROM schema_version WHERE version_int = '10';"
+}
+
+function upgrade_to_11(){
+ # echo " upgrade database from version 0.10 to version 0.11"
+ echo " Add gateway_ip colum to 'nets'"
+ sql "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (11, '0.11', '0.5.4', 'Add gateway_ip colum to nets', '2017-02-13');"
+}
+function downgrade_from_11(){
+ # echo " downgrade database from version 0.11 to version 0.10"
+ echo " Delete gateway_ip colum from 'nets'"
+ sql "ALTER TABLE nets DROP COLUMN gateway_ip;"
+ sql "DELETE FROM schema_version WHERE version_int = '11';"
+}
+function upgrade_to_12(){
+ # echo " upgrade database from version 0.11 to version 0.12"
+ echo " Create of_controller table "
+ sql "CREATE TABLE ofcs (
+ uuid VARCHAR(36) NOT NULL,
+ name VARCHAR(255) NOT NULL,
+ dpid VARCHAR(64) NOT NULL,
+ ip VARCHAR(64) NOT NULL,
+ port INT(5) NOT NULL,
+ type VARCHAR(64) NOT NULL,
+ version VARCHAR(12) NULL DEFAULT NULL,
+ user VARCHAR(64) NULL DEFAULT NULL,
+ password VARCHAR(64) NULL DEFAULT NULL,
+ PRIMARY KEY (uuid)
+ )
+ COLLATE='utf8_general_ci'
+ ENGINE=InnoDB;"
+ echo " Modify user_at for uuids table"
+ sql "ALTER TABLE uuids CHANGE COLUMN used_at used_at VARCHAR(64) NULL DEFAULT NULL "\
+ "COMMENT 'Table that uses this UUID' ;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (12, '0.12', '0.5.5', 'Add of_controller table', '2017-02-17');"
+}
+
+function downgrade_from_12(){
+ # echo " downgrade database from version 0.12 to version 0.11"
+ echo " Delete ofcs table"
+ sql "DROP TABLE ofcs;"
+ sql "ALTER TABLE uuids CHANGE COLUMN used_at used_at ENUM('flavors', 'hosts', 'images', 'instances', "\
+ "'nets', 'ports', 'tenants') NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;"
+ 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 " Create of_port_mapings table "
+ sql "CREATE TABLE of_port_mappings (
+ uuid VARCHAR(36) NOT NULL,
+ ofc_id VARCHAR(36) NULL DEFAULT NULL,
+ region VARCHAR(64) NULL DEFAULT NULL,
+ compute_node VARCHAR(64) NULL DEFAULT NULL,
+ pci VARCHAR(50) NULL DEFAULT NULL,
+ switch_dpid VARCHAR(64) NULL DEFAULT NULL,
+ switch_port VARCHAR(64) NULL DEFAULT NULL,
+ switch_mac CHAR(18) NULL DEFAULT NULL,
+ UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
+ UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
+ UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
+ INDEX FK_of_port_mappings_ofcs (ofc_id),
+ CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE
+ ON DELETE CASCADE)
+ COLLATE='utf8_general_ci'
+ ENGINE=InnoDB;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (13, '0.13', '0.5.6', 'Add of_port_mapings table', '2017-03-09');"
+}
+
+function downgrade_from_13(){
+ # echo " downgrade database from version 0.13 to version 0.12"
+ echo " Delete of_port_mappings table"
+ sql "DROP TABLE of_port_mappings;"
+ 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 " Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
+ sql "ALTER TABLE ports
+ ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
+ ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
+ ADD CONSTRAINT FK_port_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
+ sql "ALTER TABLE resources_port
+ ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
+ ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
+ ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (14, '0.14', '0.5.7', 'Add switch_mac, ofc_id colum to ports and resources_port tables', '2017-03-09');"
+}
+
+function downgrade_from_14(){
+ # echo " downgrade database from version 0.14 to version 0.13"
+ echo " Delete switch_mac, ofc_id colum to 'ports'"
+ sql "ALTER TABLE ports DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_port_ofc_id;"
+ sql "ALTER TABLE resources_port DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_resource_ofc_id;"
+ 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 ofc_id colum to 'of_flows'"
+ sql "ALTER TABLE of_flows ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id, ADD CONSTRAINT "\
+ "FK_of_flows_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (15, '0.15', '0.5.8', 'Add ofc_id colum to of_flows', '2017-03-15');"
+}
+
+function downgrade_from_15(){
+ # echo " downgrade database from version 0.15 to version 0.14"
+ echo " Delete ofc_id to 'of_flows'"
+ sql "ALTER TABLE of_flows DROP COLUMN ofc_id, DROP FOREIGN KEY FK_of_flows_ofcs;"
+ 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 last_error and status colum to 'ofcs'"
+ sql "ALTER TABLE ofcs ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password, "\
+ "ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (16, '0.16', '0.5.9', 'Add last_error and status colum to ofcs', '2017-03-17');"
+}
+
+function downgrade_from_16(){
+ # echo " downgrade database from version 0.16 to version 0.15"
+ echo " Delete last_error and status colum to 'ofcs'"
+ sql "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status;"
+ 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 pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
+ sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
+ "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);"
+ sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
+ "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);"
+ echo " Add nets_with_same_vlan to table ofcs"
+ sql "ALTER TABLE ofcs ADD COLUMN nets_with_same_vlan ENUM('true','false') NOT NULL DEFAULT 'false' AFTER status;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (17, '0.17', '0.5.10', 'Add pci to unique index dpid port/mac at of_port_mappings', '2017-04-05');"
+}
+
+function downgrade_from_17(){
+ # echo " downgrade database from version 0.17 to version 0.16"
+ echo " Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
+ sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
+ "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);"
+ sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
+ "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);"
+ echo " Remove nets_with_same_vlan from table ofcs"
+ sql "ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;"
+ sql "DELETE FROM schema_version WHERE version_int = '17';"
+}
+
+function upgrade_to_18(){
+ echo " Add 'region' at 'nets' and change unique index vlan+region"
+ sql "ALTER TABLE nets ADD COLUMN region VARCHAR(64) NULL DEFAULT NULL AFTER admin_state_up, DROP INDEX type_vlan;"
+ echo " Fill 'region' with __OVS__/__DATA__ for OVS/openflow provider at nets"
+ sql "UPDATE nets set region='__OVS__' where provider like 'OVS%';"
+ sql "UPDATE nets set region='__DATA__' where type='data' or type='ptp';"
+ echo " Create new index region_vlan at nets"
+ sql "ALTER TABLE nets ADD UNIQUE INDEX region_vlan (region, vlan);"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (18, '0.18', '0.5.13', 'Add region to nets, change vlan unique index', '2017-05-03');"
+}
+
+function downgrade_from_18(){
+ echo " Delete 'region' at 'nets' and change back unique index vlan+type"
+ sql "ALTER TABLE nets DROP INDEX region_vlan, DROP COLUMN region;"
+ echo " Create back index type_vlan at nets"
+ sql "ALTER TABLE nets ADD UNIQUE INDEX type_vlan (type, vlan);"
+ sql "DELETE FROM schema_version WHERE version_int = '18';"
+}
+
+function upgrade_to_19(){
+ echo " Add 'keyfile' to 'hosts'"
+ sql "ALTER TABLE hosts ADD COLUMN keyfile VARCHAR(255) NULL DEFAULT NULL AFTER password;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (19, '0.19', '0.5.15', 'Add keyfile to hosts', '2017-05-23');"
+}
+
+function downgrade_from_19(){
+ echo " Delete 'keyfile' from 'hosts'"
+ sql "ALTER TABLE hosts DROP COLUMN keyfile;"
+ sql "DELETE FROM schema_version WHERE version_int = '19';"
+}
+
+function upgrade_to_20(){
+ echo " Add 'image_size' to 'instance_devices'"
+ sql "ALTER TABLE instance_devices ADD COLUMN image_size INT NULL DEFAULT NULL AFTER dev;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (20, '0.20', '0.5.17', 'Add image_size to instance_devices', '2017-06-01');"
+}
+
+function downgrade_from_20(){
+ echo " Delete 'image_size' from 'instance_devices'"
+ sql "ALTER TABLE instance_devices DROP COLUMN image_size;"
+ sql "DELETE FROM schema_version WHERE version_int = '20';"
+}
+
+function upgrade_to_21(){
+ echo " Add 'routes', 'links' and 'dns' to 'nets'"
+ sql "ALTER TABLE nets ADD COLUMN dns VARCHAR(255) NULL AFTER gateway_ip, "\
+ "ADD COLUMN links TEXT(2000) NULL AFTER dns, "\
+ "ADD COLUMN routes TEXT(2000) NULL AFTER links;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (21, '0.21', '0.5.18', 'Add routes, links and dns to inets', '2017-06-21');"
+}
+
+function downgrade_from_21(){
+ echo " Delete 'routes', 'links' and 'dns' to 'nets'"
+ sql "ALTER TABLE nets DROP COLUMN dns, DROP COLUMN links, DROP COLUMN routes;"
+ sql "DELETE FROM schema_version WHERE version_int = '21';"
+}
+
+function upgrade_to_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, openvim_ver, comments, date) "\
+ "VALUES (22, '0.22', '0.5.21', 'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-11-14');"
+}
+
+function downgrade_from_22(){
+ 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';"