use strict;
use warnings;
use DBI;

my $db = new ();

$db->AddBuildManager ('fred');
print "Id is $db->{myId}\n";


sub new {
    my $dbh = DBI->connect("dbi:SQLite2:ManSched.sqlite2", "", "");

    $dbh->{AutoCommit} = 0;
    $dbh->{RaiseError} = 1;

    eval {
        my $sth = $dbh->table_info (undef, 'public', 'managers', 'TABLE');
        my @tables = $sth->fetchrow_array ();
    
        die if @tables;
        
        $dbh->do ("CREATE TABLE managers(name VARCHAR(20) not null, id VARCHAR(24) not null primary key)");
        $dbh->commit ();
    };
    
    return bless {dbh => $dbh};
}


sub DESTROY {
    my $self = shift;
    my $dbh = $self->{dbh};
    
    return if ! defined $self->{myId};
    $dbh->do ("DELETE FROM managers WHERE id = '$self->{myId}'");
    $dbh->disconnect ();
}


sub AddBuildManager {
    my ($self, $managerName) = @_;
    my $dbh = $self->{dbh};
    my $sth = $dbh->prepare ("SELECT id FROM managers WHERE name = '$managerName'");
    
    $sth->execute ();
    my @managers = sort $sth->fetchrow_array ();

    for my $index (0 .. @managers) {
        eval {
            my $id = sprintf "%s-%03d", $managerName, $index + 1;
            
            die if exists ($managers[$index]) and $managers[$index] eq $id;

            $dbh->do ("INSERT INTO managers(name, id) VALUES ('$managerName', '$id')");
            $dbh->commit ();
            $self->{myId} = $id;
        };

        return $self->{myId} if ! $@ and defined $self->{myId};

        eval {$self->{dbh}->rollback ()};
    }
    
    return;
}