manager/setupdb

#!/usr/local/bin/perl

use DBI;
$hostname = 'localhost';          # Host that serves the mSQL Database
$dbname = 'snmp';                 # mySQL Database name
$doit = 1;

sub usage {
    print "$0 [-H sqlhost] [-u user] [-p password] [-d] [-n]\n";
    exit 0;
}

while ($#ARGV > -1 && $ARGV[0] =~ /^-/) {
    $_ = shift @ARGV;
    usage if (/-h/);
    $hostname = shift if (/-H/);
    $user = shift if (/-u/);
    $pass = shift if (/-p/);
    $delete = 1 if (/-d/);
    $verbose = 1 if (/-v/);
    $doit = 0 if (/-n/);
}

( $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$hostname", $user, $pass))
    or die "\tConnect not ok: $DBI::errstr\n";

# run a whole ton of setup stuff

if ($delete) {
    $dbh->do("drop database if exists $dbname");
}

# here we go

$dbh->do("create database $dbname");
$dbh->do("use $dbname");
$dbh->do("create table hosts (id integer auto_increment primary key, 
                        name varchar(16), 
                        community varchar(32))");
$dbh->do("create table hosterrors (id integer auto_increment primary key, 
                       host varchar(16),
                       errormsg varchar(128))");
$dbh->do("create table hosttables (id integer auto_increment primary key, 
                       host varchar(16), tablename varchar(64),
		       groupname varchar(32),
                       keephistory integer(2))");
$dbh->do("create table usergroups (id integer auto_increment primary key, 
                       user varchar(16),
                       groupname varchar(32),
		       isadmin enum('N','Y') default 'N')");
$dbh->do("create table userprefs (id integer auto_increment primary key, 
                            user varchar(16),
                            groupname varchar(32),
                            tablename varchar(64),
                            columnname varchar(64),
                            displayit enum('N','Y') default 'Y')");
$dbh->do("create table hostgroups (id integer auto_increment primary key, 
                             host varchar(16), 
                             groupname varchar(32))");

$dbh->do("create table oncall (id integer auto_increment primary key, 
                               user varchar(16),
                               groupname varchar(32),
                               email varchar(64),
                               days  varchar(64),
                               hours varchar(64))");

$dbh->do("create table errorexpressions (id integer auto_increment primary key, 
                                   tablename varchar(64), 
                                   expression varchar(255),
                                   returnfield varchar(64))");

# insert the standard ucd-snmp expressions 
$dbh->do("insert into errorexpressions(tablename, expression, returnfield)
                values('prEntry', 'prErrorFlag > 0', 'prErrMessage')");
$dbh->do("insert into errorexpressions(tablename, expression, returnfield)
                values('extEntry', 'extResult > 0', 'extOutput')");
$dbh->do("insert into errorexpressions(tablename, expression, returnfield)
                values('dskEntry', 'dskErrorFlag > 0', 'dskErrMessage')");
$dbh->do("insert into errorexpressions(tablename, expression, returnfield)
                values('laEntry', 'laErrorFlag > 0', 'laErrMessage')");
$dbh->do("insert into errorexpressions(tablename, expression, returnfield)
                values('fileEntry', 'fileErrorFlag > 0', 'fileErrMessage')");
$dbh->do("insert into errorexpressions(tablename, expression, returnfield)
                values('snmperrs', 'snmperrErrorFlag > 0', 'snmperrErrMessage')");
$dbh->do("insert into errorexpressions(tablename, expression, returnfield)
                values('memory', 'memSwapError > 0', 'memSwapErrMessage')");

$dbh->disconnect();