#!/usr/bin/perl -w

# PostgreSQL database upgrade script
# Copyright 2000, 2001, 2002 Jrme Marant <jerome@debian.org>
#

use Getopt::Std;
use DBI;

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

$adminpass = $opt_a;
$database = $opt_d;
$host = $opt_h;
$port = $opt_p;
$options = $opt_o;

my $doupgrade;
my $attr;

usage() if (($adminpass eq "") || ($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
    eval {$dbh->do("SELECT * FROM user_table INTO user_table_save")};
    eval {$dbh->do("DROP TABLE user_table")};
    eval {$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))")};

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

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

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

foreach my $i (@$attr) {
    if ($i->{NAME} eq 'comment_subscriber') {
	$doupgrade = 0;
	last;
    }
}

# Modify subscriber_table
if ($doupgrade == 1) {
    eval {$dbh->do("ALTER TABLE subscriber_table ADD comment_subscriber varchar (150)")};
}

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

$dbh->disconnect();


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