#!/usr/bin/perl

# MySQL database upgrade script
# Copyright 2000 Jrme Marant <jerome@debian.org>
# Copyright 2003,2004 Stefan Hornburg (Racke) <racke@linuxia.de>

use strict;
use warnings;

use Getopt::Std;
use DBI;

use vars qw($opt_a $opt_d $opt_h $opt_p $opt_o);

my ($database, $host, $port, $options, $adminpass);

getopt('a:d:h:p:o:');

$adminpass = '';
if ($opt_a && -f $opt_a) {
	open(PWD, $opt_a) || die "$0: Couldn't open file $opt_a: $!\n";
	chomp($adminpass = <PWD>);
	close(PWD);
}
$database = $opt_d;
$host = $opt_h || 'localhost';
$port = $opt_p;
$options = $opt_o;

usage() if ($database eq "");

my $dsn = "DBI:mysql:database=$database";

# Set the hostname
if ($host ne 'localhost') {
    $dsn .= ";host=$host";

    # Set the port in case of a TCP connection.
    if ($port eq '') {
	$port = "3306";
    }

    $dsn .= ";port=$port";
}

if ($options ne "") {
    $dsn .= ";$options";
}

# Connect to mysql
my ($sth, $href, %indices, %dbcols, @cols);
my $dbh = DBI->connect($dsn, "root", "$adminpass",
			{"RaiseError" => 1});

# Determine columns for both tables
for (qw(subscriber_table user_table)) {
	$dbcols{$_} = {};
	$sth = $dbh->prepare ("select * from $_ where 0 = 1");
	$sth->execute();
	for my $col (@{$sth->{NAME}}) {
		$dbcols{$_}->{$col} = 1;
	}
}

# Alter the database
$dbh->do("ALTER TABLE user_table CHANGE password_user password_user varchar (40)");

unless (exists $dbcols{subscriber_table}->{comment_subscriber}) {
	$dbh->do("ALTER TABLE subscriber_table ADD comment_subscriber varchar (150)");
}

# Determine excess indices and remove them, add primary key if missing
$sth = $dbh->prepare('show index from subscriber_table');
$sth->execute();
while ($href = $sth->fetchrow_hashref()) {
	push (@{$indices{$href->{Key_name}}}, $href->{Column_name});
}
for (keys %indices) {
	@cols = @{$indices{$_}};
	unless (($cols[0] eq 'list_subscriber' && $cols[1] eq 'user_subscriber')
			|| ($cols[0] eq 'user_subscriber' && $cols[1] eq 'list_subscriber')) {
		delete $indices{$_};
		next;
	}
	next if $_ eq 'PRIMARY';
	
	# remove non primary keys for list_subscriber/user_subscriber
	$dbh->do("ALTER TABLE subscriber_table DROP INDEX $_");
}

unless ($indices{'PRIMARY'}) {
	$dbh->do("ALTER TABLE subscriber_table ADD PRIMARY KEY (user_subscriber,list_subscriber)");
}

# 3.3.3 to 3.3.5
$dbh->do("ALTER TABLE subscriber_table CHANGE bounce_subscriber bounce_subscriber varchar (35)");

# 3.3.5 to 3.4.3
unless (exists $dbcols{subscriber_table}->{subscribed_subscriber}) {
	$dbh->do("ALTER TABLE subscriber_table ADD subscribed_subscriber 	enum ('0','1')");
}
unless (exists $dbcols{subscriber_table}->{included_subscriber}) {
	$dbh->do("ALTER TABLE subscriber_table ADD included_subscriber 	enum ('0','1')");
}
unless (exists $dbcols{subscriber_table}->{include_sources_subscriber}) {
	$dbh->do("ALTER TABLE subscriber_table ADD include_sources_subscriber varchar(50)");
}

# 3.4.3 to 4.0
unless (exists $dbcols{subscriber_table}->{bounce_score_subscriber}) {
	$dbh->do("ALTER TABLE subscriber_table ADD bounce_score_subscriber smallint(6)");
}

$dbh->disconnect();

sub usage {
    die "Usage: upgrade-mysql-db.pl -d <database> [-a <adminpass>] [-h <hostname>] [-p <port>]\n";
}
