#!/bin/sh

case "$1" in
gmysql)
    dbtype=MySQL
    ;;
gpgsql)
    dbtype=PostgreSQL
    ;;
*)
    echo "usage: $0 [ gmysql | gpgsql ]"
    exit 1
    ;;
esac

while [ ".$db" = . ]; do
echo "Please enter the name of the database to be created:"
read db
done

while [ ".$root" = . ]; do
echo "Please enter the name of the database administrator account:"
read root
done

while [ ".$pdnsadm" = . ]; do
echo "Please enter the name of the DNS administrator account:"
read pdnsadm
done

while [ ".$pdns" = . ]; do
echo "Please enter the name of the DNS user account:"
read pdns
done

echo ""
echo "The $dbtype database '$db' will be created by '$root'."
echo "Access is granted to the DNS administrator '$pdnsadm'"
echo "and the DNS user '$pdns'."
echo ""

case "$1" in
gmysql)
    echo "Please log in as the $dbtype database administrator ($root)"
    mysql -u$root -p mysql <<EOFEOF
    DROP DATABASE $db;
    CREATE DATABASE $db;
    GRANT ALL ON $db TO $pdnsadm WITH GRANT OPTION
EOFEOF
    if [ $? -gt 0 ]; then exit 1; fi
    echo "The database has been created"
    echo ""
    echo "Please log in as the $dbtype DNS administrator ($pdnsadm)"
    mysql -u$pdnsadm -p $db <<EOFEOF
    CREATE TABLE domains (
     id      INT auto_increment,
     name        VARCHAR(255) NOT NULL,
     master      VARCHAR(20) DEFAULT NULL,
     last_check  INT DEFAULT NULL,
     type        VARCHAR(6) NOT NULL,
     notified_serial INT DEFAULT NULL,
     account         VARCHAR(40) DEFAULT NULL,
     primary key (id)
    )type=InnoDB;

    CREATE UNIQUE INDEX name_index ON domains(name);

    CREATE TABLE records (
     id              INT auto_increment,
     domain_id       INT DEFAULT NULL,
     name            VARCHAR(255) DEFAULT NULL,
     type            VARCHAR(6) DEFAULT NULL,
     content         VARCHAR(255) DEFAULT NULL,
     ttl             INT DEFAULT NULL,
     prio            INT DEFAULT NULL,
     change_date     INT DEFAULT NULL,
     primary key(id)
    )type=InnoDB;

    CREATE INDEX rec_name_index ON records(name);
    CREATE INDEX nametype_index ON records(name,type);
    CREATE INDEX domain_id ON records(domain_id);

    CREATE TABLE supermasters (
      ip VARCHAR(25) NOT NULL,
      nameserver VARCHAR(255) NOT NULL,
      account VARCHAR(40) DEFAULT NULL
    );

    GRANT SELECT ON supermasters TO $pdns;
    GRANT ALL ON domains TO $pdns;
EOFEOF
    if [ $? -gt 0 ]; then exit 1; fi
    echo "The database has been populated"
    echo ""
    ;;
gpgsql)
    echo "Please log in as the $dbtype database administrator ($root)"
    psql -U $root $db <<EOFEOF
    DROP DATABASE $db;
    CREATE DATABASE $db;
    GRANT ALL ON $db TO $pdnsadm WITH GRANT OPTION;
EOFEOF
    if [ $? -gt 0 ]; then exit 1; fi
    echo "The database has been created"
    echo ""
    echo "Please log in as the $dbtype DNS administrator ($pdnsadm)"
    psql -U $pdnsadm $db <<EOFEOF
    CREATE TABLE domains (
     id      SERIAL PRIMARY KEY,
     name        VARCHAR(255) NOT NULL,
     master      VARCHAR(20) DEFAULT NULL,
     last_check  INT DEFAULT NULL,
     type        VARCHAR(6) NOT NULL,
     notified_serial INT DEFAULT NULL,
     account         VARCHAR(40) DEFAULT NULL
    );
    CREATE UNIQUE INDEX name_index ON domains(name);

    CREATE TABLE records (
     id              SERIAL PRIMARY KEY,
     domain_id       INT DEFAULT NULL,
     name            VARCHAR(255) DEFAULT NULL,
     type            VARCHAR(6) DEFAULT NULL,
     content         VARCHAR(255) DEFAULT NULL,
     ttl             INT DEFAULT NULL,
     prio            INT DEFAULT NULL,
     change_date     INT DEFAULT NULL,
     CONSTRAINT domain_exists
     FOREIGN KEY(domain_id) REFERENCES domains(id)
     ON DELETE CASCADE
    );

    CREATE INDEX rec_name_index ON records(name);
    CREATE INDEX nametype_index ON records(name,type);
    CREATE INDEX domain_id ON records(domain_id);

    CREATE TABLE supermasters (
     ip VARCHAR(25) NOT NULL,
     nameserver VARCHAR(255) NOT NULL,
     account VARCHAR(40) DEFAULT NULL
    );

    GRANT SELECT ON supermasters TO $pdns;
    GRANT ALL ON domains TO $pdns;
    GRANT ALL ON domains_id_seq TO $pdns;
    GRANT ALL ON records TO $pdns;
    GRANT ALL ON records_id_seq TO $pdns;
EOFEOF
    if [ $? -gt 0 ]; then exit 1; fi
    echo "The database has been populated"
    echo ""
    ;;
esac