#!/usr/bin/perl -w

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

use Getopt::Std;
use DBI;

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

if (-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;
$port = $opt_p;
$options = $opt_o;

my $doupgrade;
my $attr;

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

my $dsn = "DBI:Pg:dbname=$database";

if ($host eq "") {
    $host = 'localhost';
}

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

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

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

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

# Connect to PostgreSQL
my $dbh = DBI->connect($dsn, "postgres", "$adminpass",
			{"RaiseError" => 1});

# Upgrade user_table
$doupgrade = 1;
$attr = $dbh->func('user_table', 'table_attributes');

# Check whether the database has already been modified
foreach my $i (@$attr) {
    if ($i->{NAME} eq 'password_user') {
	if (($i->{TYPE} eq 'varchar') && ($i->{SIZE} == 40)) {
	    $doupgrade = 0;
	    last;
	}
    }
}

if ($doupgrade == 1) {
    # Modify user_table
    $dbh->do("SELECT * FROM user_table INTO user_table_save");
	$dbh->do("DROP TABLE user_table");
    $dbh->do("CREATE TABLE user_table (".
		   "email_user varchar (100) NOT NULL,".
		   "gecos_user varchar (150),".
		   "cookie_delay_user int4,".
		   "password_user varchar (40),".
		   "lang_user varchar (10),".
		   "CONSTRAINT ind_user PRIMARY KEY (email_user))")};

    $dbh->do("INSERT INTO user_table SELECT * FROM user_table_save");

    $dbh->do("DROP TABLE user_table_save");
}

# Upgrade subscriber_table
$doupgrade = 1;
$attr = $dbh->func('subscriber_table', 'table_attributes');

# Search for comment_subscriber
foreach my $i (@$attr) {
    if ($i->{NAME} eq 'comment_subscriber') {
	# Do not upgrade if the field is present
	$doupgrade = 0;
	last;
    }
}

# 3.3.3 to 3.3.5
foreach my $i (@$attr) {
    if ($i->{NAME} eq 'bounce_subscriber') {
	if (($i->{TYPE} eq 'varchar') && ($i->{SIZE} < 35)) {
	    # Upgrade if the size is not correct
	    $doupgrade = 1;
	}
	last;
    }
}

# 3.3.5 to 3.4.3
for my $col (qw(subscribed_subscriber included_subscriber include_sources_subscriber)) {
	unless (grep {$_->{NAME} eq $col} @$attr) {
		# Upgrade if field is missing
		$doupgrade = 1;
		last;
	}
}

# Modify subscriber_table
if ($doupgrade == 1) {
    $dbh->do("SELECT * FROM subscriber_table INTO subscriber_table_save");
    $dbh->do("DROP TABLE subscriber_table");
    $dbh->do("CREATE TABLE subscriber_table (".
		   "list_subscriber       	varchar (50) NOT NULL,".
		   "user_subscriber		varchar (100) NOT NULL,".
		   "date_subscriber		timestamp with time zone NOT NULL,".
		   "update_subscriber 	timestamp with time zone,".
		   "visibility_subscriber	varchar (20),".
		   "reception_subscriber	varchar (20),".
		   "bounce_subscriber	varchar (35),".
		   "comment_subscriber      varchar (150),".
		   "subscribed_subscriber   bit(1),".
		   "included_subscriber     bit(1),".
		   "include_sources_subscriber varchar(50),".
		   "CONSTRAINT ind_subscriber PRIMARY KEY (list_subscriber, user_subscriber)".
		   ")");

    $dbh->do("INSERT INTO subscriber_table SELECT * FROM subscriber_table_save");
    
    $dbh->do("DROP TABLE subscriber_table_save");
}

$dbh->do("CREATE INDEX subscriber_idx ON subscriber_table (user_subscriber,list_subscriber)");

$dbh->disconnect();


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