+ 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');"