# Updates the Netdot database

use warnings;
use strict;
use lib "../lib";
use DBUTIL;
use Netdot;
use Data::Dumper;

my %CONFIG;
$CONFIG{debug}        = 1;
$CONFIG{CONFIG_DIR}   = "../etc";
$CONFIG{SCHEMA_FILE}  = "$CONFIG{CONFIG_DIR}/netdot.meta";
$CONFIG{DEFAULT_DATA} = "$CONFIG{CONFIG_DIR}/default_data";

my $netdot_config = Netdot::Config->new(config_dir => $CONFIG{CONFIG_DIR});
my $dbms = $netdot_config->get('DB_TYPE');
my $netdot_user = $netdot_config->get("DB_NETDOT_USER");
my $dbh = &dbconnect();

# Get the current schema version from DB
my ($schema_version) = $dbh->selectrow_array("SELECT version 
                                              FROM   schemainfo 
                                              WHERE  id=1;");

die "Schema version unknown\n" unless $schema_version;

print "Upgrading Netdot schema from version $schema_version\n";

my @statements;

if ( $schema_version eq '1.0.1' ){
    
    &upg_101_102();
    &upg_102_103();
    &upg_103_104();
    &upg_104_105();
    &upg_105_106();
    &upg_106_107();
    
}elsif ( $schema_version eq '1.0.2' ){
    
    &upg_102_103();
    &upg_103_104();
    &upg_104_105();
    &upg_105_106();
    &upg_106_107();
    
}elsif ( $schema_version eq '1.0.3' ){
    
    &upg_103_104();
    &upg_104_105();
    &upg_105_106();
    &upg_106_107();

}elsif ( $schema_version eq '1.0.4' ){
    
    &upg_104_105();
    &upg_105_106();
    &upg_106_107();
    
}elsif ( $schema_version eq '1.0.5' ){
    
    &upg_105_106();
    &upg_106_107();
    
}elsif ( $schema_version eq '1.0.6' ){
    
    &upg_106_107();
    
}else{
    die "Unsupported version for this upgrade: $schema_version\n";
}

&processdata(\@statements);
&dbdisconnect();


#########################################################################
sub upg_101_102 {

    if ( $dbms eq 'mysql' ) {
	
	# Try to speed up the conversion as much as possible
	push @statements, 
	"SET FOREIGN_KEY_CHECKS = 0;",
	"SET AUTOCOMMIT = 0;";
	
	# Add a new unique constraint to avoid devices with same name. 
	# Requires renaming existing indexes to be consistent with newly generated 
	# schema in this version
	push @statements, 

	'ALTER TABLE device DROP foreign key fk_used_by;',
	'ALTER TABLE device DROP foreign key fk_owner_1;',

	'ALTER TABLE device DROP INDEX `Device2`;',
	'ALTER TABLE device DROP INDEX `Device3`;',
	'ALTER TABLE device DROP INDEX `Device4`;',
	'ALTER TABLE device DROP INDEX `Device5`;',
	'ALTER TABLE device DROP INDEX `Device6`;',
	'ALTER TABLE device DROP INDEX `Device7`;',
	'ALTER TABLE device DROP INDEX `Device8`;',

	'ALTER TABLE device ADD UNIQUE INDEX `device2` (`name`);',
	
	'ALTER TABLE device ADD INDEX `Device3` (`used_by`);',
	'ALTER TABLE device ADD INDEX `Device4` (`owner`);',
	'ALTER TABLE device ADD INDEX `Device5` (`os`);',
	'ALTER TABLE device ADD INDEX `Device6` (`sysname`);',
	'ALTER TABLE device ADD INDEX `Device7` (`down_from`);',
	'ALTER TABLE device ADD INDEX `Device8` (`down_until`);',
	'ALTER TABLE device ADD INDEX `Device9` (`extension`);',

	'ALTER TABLE device ADD CONSTRAINT `fk_owner_1` FOREIGN KEY (`owner`) REFERENCES `entity` (`id`);',
	'ALTER TABLE device ADD CONSTRAINT `fk_used_by` FOREIGN KEY (`used_by`) REFERENCES `entity` (`id`);',

	"UPDATE schemainfo SET version='1.0.2' WHERE id=1;",
	"DELETE FROM datacache;";
	
	# Restore checks
	push @statements, "SET FOREIGN_KEY_CHECKS = 1;", 
	"COMMIT;",
	"SET AUTOCOMMIT=1;";
	
    }elsif ( $dbms eq 'Pg' ){
	
	# IDs were erroneously created as "serial" instead of "bigserial"
	foreach my $table_name ( $dbh->tables('', 'public', '%', '') ){
	    push @statements, "ALTER TABLE $table_name ALTER COLUMN id TYPE bigint;";
	}
	
	# Add a new unique constraint to avoid devices with same name. 
	# Requires renaming existing indexes to be consistent with newly generated 
	# schema in this version
	push @statements, 
	'DROP INDEX "Device2";',
	'DROP INDEX "Device3";',
	'DROP INDEX "Device4";',
	'DROP INDEX "Device5";',
	'DROP INDEX "Device6";',
	'DROP INDEX "Device7";',
	'DROP INDEX "Device8";',
	
	'ALTER TABLE device ADD CONSTRAINT "device2" UNIQUE ("name");',
	
	'CREATE INDEX "Device3" on "device" ("used_by");',
	'CREATE INDEX "Device4" on "device" ("owner");',
	'CREATE INDEX "Device5" on "device" ("os");',
	'CREATE INDEX "Device6" on "device" ("sysname");',
	'CREATE INDEX "Device7" on "device" ("down_from");',
	'CREATE INDEX "Device8" on "device" ("down_until");',
	'CREATE INDEX "Device9" on "device" ("extension");',

	"UPDATE schemainfo SET version='1.0.2' WHERE id=1;",
	"DELETE FROM datacache;";
	
    }else{
	die "Unsupported DB type: $dbms\n";
    }

}

#########################################################################
sub upg_102_103 {
    push @statements, "UPDATE schemainfo SET version='1.0.3' WHERE id=1;",
}

#########################################################################
sub upg_103_104 {

    if ( $dbms eq 'mysql' ) {

	push @statements, "ALTER TABLE ipblock ADD COLUMN use_network_broadcast bool NOT NULL;";

    }elsif ( $dbms eq 'Pg' ){

	push @statements, 
	"ALTER TABLE ipblock ADD COLUMN use_network_broadcast bool NOT NULL default 'f';";

    }
    
    push @statements, "UPDATE schemainfo SET version='1.0.4' WHERE id=1;";

}

#########################################################################
sub upg_104_105 {

    if ( $dbms eq 'mysql' ) {

	push @statements, "ALTER TABLE device ADD COLUMN oobname_2 varchar(255);";
	push @statements, "ALTER TABLE device ADD COLUMN oobnumber_2 varchar(32);";
	push @statements, "ALTER TABLE device ADD COLUMN power_outlet varchar(255);";
	push @statements, "ALTER TABLE device ADD COLUMN power_outlet_2 varchar(255);";

	push @statements, "ALTER TABLE ipblock ADD COLUMN monitored bool NOT NULL;";
	push @statements, "ALTER TABLE ipblock ADD COLUMN rir varchar(255);";

	push @statements, "CREATE TABLE `asn` (
                             `description` varchar(255) NULL,
                             `id` bigint NOT NULL auto_increment,
                             `info` text NULL,
                             `number` bigint NOT NULL,
                             `rir` varchar(255) NULL,
                              UNIQUE INDEX `asn1` (`number`),
                              INDEX `ASN2` (`rir`),
                              PRIMARY KEY (`id`)
                           ) ENGINE=InnoDB;
                           ";

	push @statements, "ALTER TABLE ipblock ADD COLUMN asn bigint;";
	push @statements, "ALTER TABLE ipblock ADD CONSTRAINT `fk_asn` FOREIGN KEY (`asn`) ".
	    "REFERENCES `asn` (`id`);";
	push @statements, "CREATE INDEX asn ON ipblock (asn);";

	push @statements, "INSERT INTO asn (number) SELECT DISTINCT bgplocalas FROM device WHERE bgplocalas IS NOT NULL;";

	push @statements, "ALTER TABLE device MODIFY bgplocalas bigint;";
	push @statements, "CREATE INDEX bgplocalas ON device (bgplocalas);";

	push @statements, "UPDATE device,asn SET device.bgplocalas=asn.id WHERE device.bgplocalas=asn.number;"; 

	push @statements, "ALTER TABLE device ADD CONSTRAINT `fk_bgplocalas` FOREIGN KEY (`bgplocalas`) ".
	    "REFERENCES `asn` (`id`);";

	push @statements, "ALTER TABLE bgppeering ADD COLUMN contactlist bigint;";
	push @statements, "ALTER TABLE bgppeering ADD CONSTRAINT `fk_contactlist_bgppeering` FOREIGN KEY (`contactlist`) ".
	    "REFERENCES `contactlist` (`id`);";
	push @statements, "CREATE INDEX contactlist ON bgppeering (contactlist);";
	push @statements, "ALTER TABLE bgppeering ADD COLUMN last_changed timestamp;";
	push @statements, "ALTER TABLE bgppeering ADD COLUMN peer_group varchar(255);";
	push @statements, "ALTER TABLE bgppeering ADD COLUMN state varchar(255);";
	push @statements, "ALTER TABLE bgppeering DROP FOREIGN KEY `fk_monitorstatus`;";
	push @statements, "ALTER TABLE bgppeering DROP INDEX monitorstatus;";
	push @statements, "ALTER TABLE bgppeering DROP COLUMN monitorstatus;";

	push @statements, "ALTER TABLE rrds DROP FOREIGN KEY `fk_rr_2`;";
	push @statements, "ALTER TABLE rrds DROP INDEX `rrds1`;";
	push @statements, "CREATE UNIQUE INDEX rrds1 ON rrds (rr, key_tag, algorithm, digest_type);";
	push @statements, "ALTER TABLE rrds ADD CONSTRAINT `fk_rr_2` FOREIGN KEY (`rr`) REFERENCES `rr` (`id`);";

	push @statements, "ALTER TABLE device ADD COLUMN monitoring_template varchar(255);";

	push @statements, "ALTER TABLE device ADD COLUMN host_device bigint;";
	push @statements, "ALTER TABLE device ADD CONSTRAINT `fk_host_device` FOREIGN KEY (`host_device`) ".
	    "REFERENCES `device` (`id`);";
	push @statements, "CREATE INDEX host_device ON device (host_device);";

	push @statements, "DROP TABLE backbonecable_history;";
	push @statements, "DROP TABLE cablestrand_history;";
	push @statements, "DROP TABLE circuit_history;";
	push @statements, "DROP TABLE contact_history;";
	push @statements, "DROP TABLE device_history;";
	push @statements, "DROP TABLE entity_history;";
	push @statements, "DROP TABLE interface_history;";
	push @statements, "DROP TABLE person_history;";
	push @statements, "DROP TABLE product_history;";
	push @statements, "DROP TABLE site_history;";
	push @statements, "DROP TABLE sitelink_history;";
	push @statements, "DROP TABLE vlangroup_history;";

    }elsif ( $dbms eq 'Pg' ){

	push @statements, "ALTER TABLE device ADD COLUMN oobname_2 character varying(255);";
	push @statements, "ALTER TABLE device ADD COLUMN oobnumber_2 character varying(32);";
	push @statements, "ALTER TABLE device ADD COLUMN power_outlet character varying(255);";
	push @statements, "ALTER TABLE device ADD COLUMN power_outlet_2 character varying(255);";

	push @statements, "ALTER TABLE ipblock ADD COLUMN monitored bool NOT NULL default 'f';";
	push @statements, "ALTER TABLE ipblock ADD COLUMN rir character varying(255);";

	push @statements, 'CREATE TABLE "asn" (
                             "description" character varying(255),
                             "id" bigserial NOT NULL,
                             "info" text,
                             "number" bigint NOT NULL,
                             "rir" character varying(255),
                             CONSTRAINT "asn1" UNIQUE ("number"),
                             CONSTRAINT "pk_asn" PRIMARY KEY ("id")
                              );
                           CREATE INDEX "ASN2" on "asn" ("rir");';

	push @statements, "GRANT SELECT,INSERT,UPDATE,DELETE ON asn TO $netdot_user;";

 	push @statements, "ALTER TABLE ipblock ADD COLUMN asn bigint;";
	push @statements, "ALTER TABLE ipblock ADD CONSTRAINT \"fk_asn\" FOREIGN KEY (\"asn\") ".
	    "REFERENCES \"asn\" (\"id\") DEFERRABLE;";
	
	push @statements, "INSERT INTO asn (number) SELECT DISTINCT bgplocalas FROM device WHERE bgplocalas IS NOT NULL;";

	push @statements, "ALTER TABLE device ALTER COLUMN bgplocalas TYPE bigint;";
	push @statements, "ALTER TABLE device ADD CONSTRAINT \"fk_bgplocalas\" FOREIGN KEY (\"bgplocalas\") ".
	    "REFERENCES \"asn\" (\"id\") DEFERRABLE;";

	push @statements, "UPDATE device SET bgplocalas=asn.id FROM asn WHERE device.bgplocalas=asn.number;"; 
	
	push @statements, "ALTER TABLE bgppeering ADD COLUMN contactlist bigint;";
	push @statements, "ALTER TABLE \"bgppeering\" ADD CONSTRAINT \"fk_contactlist\" FOREIGN KEY (\"contactlist\") ".
	    "REFERENCES \"contactlist\" (\"id\") DEFERRABLE;";
	push @statements, "ALTER TABLE bgppeering ADD COLUMN last_changed timestamp;";
	push @statements, "ALTER TABLE bgppeering ADD COLUMN peer_group character varying(255);";
	push @statements, "ALTER TABLE bgppeering ADD COLUMN state character varying(255);";
	push @statements, "ALTER TABLE bgppeering DROP COLUMN monitorstatus;";

	push @statements, 'ALTER TABLE rrds DROP CONSTRAINT rrds1;';
	push @statements, 'ALTER TABLE rrds ADD CONSTRAINT rrds1 UNIQUE (rr, key_tag, algorithm, digest_type);';

	push @statements, "ALTER TABLE device ADD COLUMN monitoring_template character varying(255);";

	push @statements, "ALTER TABLE device ADD COLUMN host_device bigint;";
	push @statements, "ALTER TABLE device ADD CONSTRAINT \"fk_host_device\" FOREIGN KEY (\"host_device\") ".
	    "REFERENCES \"device\" (\"id\") DEFERRABLE;";

	push @statements, "DROP TABLE backbonecable_history;";
	push @statements, "DROP TABLE cablestrand_history;";
	push @statements, "DROP TABLE circuit_history;";
	push @statements, "DROP TABLE contact_history;";
	push @statements, "DROP TABLE device_history;";
	push @statements, "DROP TABLE entity_history;";
	push @statements, "DROP TABLE interface_history;";
	push @statements, "DROP TABLE person_history;";
	push @statements, "DROP TABLE product_history;";
	push @statements, "DROP TABLE site_history;";
	push @statements, "DROP TABLE sitelink_history;";
	push @statements, "DROP TABLE vlangroup_history;";

    }
  
    push @statements, "UPDATE schemainfo SET version='1.0.5' WHERE id=1;";

}

#########################################################################
sub upg_105_106 {
    push @statements, "UPDATE schemainfo SET version='1.0.6' WHERE id=1;",
}

#########################################################################
sub upg_106_107 {
    push @statements, "UPDATE schemainfo SET version='1.0.7' WHERE id=1;",
}
