#!/usr/bin/perl -w

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

use Getopt::Std;
use DBI;

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

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

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

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

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

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

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

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

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

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

# Create database
eval {$dbh->do("CREATE DATABASE $database")};

# Create user sympa
$dbh->do("DELETE FROM pg_shadow WHERE usename='sympa'");

# Get a sysid 
my $usesysid = $dbh->do("SELECT MAX(usesysid) + 1 FROM pg_shadow");

$dbh->do("INSERT INTO pg_shadow".
         "(usename, usesysid, usecreatedb, usetrace, usesuper, usecatupd, passwd)".
	 "VALUES".
         "('sympa', $usesysid, 't', 't', 't', 't', '$userpass')");

$dbh->disconnect();

# Connect to $database
$dsn =~ s/dbname=template1/dbname=sympa/;

$dbh = DBI->connect($dsn,
		   "postgres", $adminpass,
		   {'RaiseError' => 1});

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("CREATE TABLE subscriber_table (".
	       "list_subscriber       	varchar (50) NOT NULL,".
	       "user_subscriber		varchar (100) NOT NULL,".
	       "date_subscriber		datetime NOT NULL,".
	       "update_subscriber 	datetime,".
	       "visibility_subscriber	varchar (20),".
	       "reception_subscriber	varchar (20),".
	       "bounce_subscriber	varchar (30),".
	       "comment_subscriber      varchar (150),".
	       "CONSTRAINT ind_subscriber PRIMARY KEY (list_subscriber, user_subscriber)".
	       ")")};

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

$dbh->disconnect();


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