#!/usr/bin/perl
use strict;
use Getopt::Long;
use DBI;
use DBD::mysql;

my %args = (
            dbhost => "localhost",
            dbname => "mogilefs",
            dbrootuser => "root",
            dbrootpass => "",
            dbuser => "mog",
            dbpass => "mogpass",
            );

my $opt_help;
my $opt_verbose = 0;
my $opt_yes = 0;

usage()
    unless GetOptions(
                      "dbhost=s"         => \$args{dbhost},
                      "dbname=s"         => \$args{dbname},
                      "dbrootuser=s"     => \$args{dbrootuser},
                      "dbrootpassword=s" => \$args{dbrootpass},
                      "dbuser=s"         => \$args{dbuser},
                      "dbpassword=s"     => \$args{dbpass},
                      "help"             => \$opt_help,
                      "verbose"          => \$opt_verbose,
                      "yes"              => \$opt_yes,
                      );

usage() if $opt_help;

sub usage {
    die <<USAGE;
Usage: mogdbsetup [opts]

Options:

                  Default      Description
                  ============ ===========================================
 --verbose        <off>        Be verbose about what\'s happening.

 --dbhost=        localhost    hostname or IP to MySQL server

 --dbname=        mogilefs     database name to create/upgrade

 --dbrootuser=    root         MySQL administrator username.  Only needed
                               for initial setup, not subsequent upgrades

 --dbrootpass=    <blank>      MySQL administrator password.  Only needed
                               for initial setup, not subsequent upgrades

 --dbuser=        mog          Regular MySQL user to create and/or use
                               for MogileFS database.  This is what the
                               mogilefsd trackers connect as.

 --dbpass=        mogpass      You should change this, especially if your
                               MySQL servers are accessible to other users
                               on the network.  But they shouldn't be
                               if you're running MogileFS, because MogileFS
                               assumes your network is closed.

  --yes                        Run without questions.

USAGE
}

confirm("This will attempt to setup or upgrade your MogileFS database.\nIt won't destroy existing data.\nRun with --help for more information.  Run with --yes to shut up these prompts.\n\nContinue?", 0);

my $dbh = normal_conn();
if (! $dbh) {
    status("couldn't connect to database as mogilefs user.  trying root...");
    my $rdbh = root_conn();

    confirm("Create database name '$args{dbname}'?");
    $rdbh->do("CREATE DATABASE IF NOT EXISTS $args{dbname}")
        or die "Failed to create database '$args{dbname}': " . $rdbh->errstr . "\n";

    confirm("Grant all privileges to user '$args{dbuser}', connecting from anywhere, to the mogilefs database?");
    $rdbh->do("GRANT ALL PRIVILEGES ON $args{dbname}.* TO $args{dbuser}\@'\%' IDENTIFIED BY ?",
             undef, $args{dbpass})
        or die "Failed to grant privileges: " . $rdbh->errstr . "\n";
    $rdbh->do("GRANT ALL PRIVILEGES ON $args{dbname}.* TO $args{dbuser}\@'localhost' IDENTIFIED BY ?",
             undef, $args{dbpass})
        or die "Failed to grant privileges: " . $rdbh->errstr . "\n";

    $dbh = normal_conn() or
        die "Failed to connect to MySQL w/ normal rights, as user $args{dbuser}: " . $DBI::errstr;
}

upgrade_database($args{dbhost}, $args{dbname}, $args{dbuser}, $args{dbpass})
    or die "Database upgrade failed.\n";

warn "Done.\n" if $opt_verbose;
exit 0;

############################################################################

sub confirm {
    my $q = shift;
    my $def = shift;
    $def = 1 unless defined $def;

    return if $opt_yes;
    my $deftext = $def ? "[Y/n]" : "[N/y]";

    print "\n$q $deftext: ";
    my $ans = <STDIN>;
    if ($ans =~ /^\s*$/) {
        die "Stopped.\n" unless $def;
        return;
    }
    return if $ans =~ /^y/i;
    die "Stopped.\n";
}

sub status {
    warn "$_[0]\n" if $opt_verbose;
}

sub root_conn {
    my $dbh;
    $dbh = DBI->connect("DBI:mysql:mysql;host=" . $args{dbhost}, $args{dbrootuser}, $args{dbrootpass})
        or die "Failed to connect to MySQL w/ admin rights, as user $args{dbrootuser}: " . $dbh->errstr;
    status("connected to database as admin user: $args{dbrootuser}");
    $dbh->{PrintError} = 0;
    return $dbh;
}

sub normal_conn {
    my $dbh;
    $dbh = DBI->connect("DBI:mysql:$args{dbname};host=$args{dbhost}",
                        $args{dbuser}, $args{dbpass}, {
                            PrintError => 0,
                        })
        or return undef;
    $dbh->{PrintError} = 0;
    status("connected to database as regular user: $args{dbuser}");
    $dbh->{RaiseError} = 1;
    return $dbh;
}

sub upgrade_database {
    my ($host, $name, $user, $pass) = @_;

    my $SCHEMA_VERSION = 6;
    my $curver = schema_version();

    if ($curver == $SCHEMA_VERSION) {
        status("Schema already up-to-date at version $curver.");
        return 1;
    }

    if ($curver > $SCHEMA_VERSION) {
        die "Your current schema version is $curver, but this version of mogdbsetup only knows up to $SCHEMA_VERSION.  Aborting to be safe.\n";
    }

    if ($curver) {
        confirm("Install/upgrade your schema from version $curver to version $SCHEMA_VERSION?");
    }

    my $run = sub {
        my $sql = shift;

        # skip a printing it in verbose mode if table exists
        if ($sql =~ /^CREATE TABLE IF NOT EXISTS (\w+)/i) {
            my $table = $1;
            if (table_exists($table)) {
                status("Table $table already exists.");
                return;
            }
        }

        status("Running SQL: $sql;");
        $dbh->do($sql);
    };

    # classes are tied to domains.  domains can have classes of items
    # with different mindevcounts.
    #
    # a minimum devcount is the number of copies the system tries to
    # maintain for files in that class
    #
    # unspecified classname means classid=0 (implicit class), and that
    # implies mindevcount=2
    $run->("CREATE TABLE IF NOT EXISTS domain (
   dmid         SMALLINT UNSIGNED NOT NULL,
   PRIMARY KEY  (dmid),
   namespace    VARCHAR(255),
   UNIQUE (namespace))");

    $run->("CREATE TABLE IF NOT EXISTS class (
      dmid          SMALLINT UNSIGNED NOT NULL,
      classid       TINYINT UNSIGNED NOT NULL,
      PRIMARY KEY (dmid,classid),
      classname     VARCHAR(50),
      UNIQUE      (dmid,classname),
      mindevcount   TINYINT UNSIGNED NOT NULL
)");

# the length field is only here for easy verifications of content
# integrity when copying around.  no sums or content types or other
# metadata here.  application can handle that.
#
# classid is what class of file this belongs to.  for instance, on fotobilder
# there will be a class for original pictures (the ones the user uploaded)
# and a class for derived images (scaled down versions, thumbnails, greyscale, etc)
# each domain can setup classes and assign the minimum redundancy level for
# each class.  fotobilder will use a 2 or 3 minimum copy redundancy for original
# photos and and a 1 minimum for derived images (which means the sole device
# for a derived image can die, bringing devcount to 0 for that file, but
# the application can recreate it from its original)
    $run->("CREATE TABLE IF NOT EXISTS file (
   fid          INT UNSIGNED NOT NULL,
   PRIMARY KEY  (fid),

   dmid          SMALLINT UNSIGNED NOT NULL,
   dkey           VARCHAR(255),     # domain-defined
   UNIQUE dkey  (dmid, dkey),

   length        INT UNSIGNED,        # 4GB limit

   classid       TINYINT UNSIGNED NOT NULL,
   devcount      TINYINT UNSIGNED NOT NULL,
   INDEX devcount (dmid,classid,devcount)
)");

    $run->("CREATE TABLE IF NOT EXISTS tempfile (
   fid          INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY  (fid),

   createtime   INT UNSIGNED NOT NULL,
   classid      TINYINT UNSIGNED NOT NULL,
   dmid          SMALLINT UNSIGNED NOT NULL,
   dkey           VARCHAR(255),
   devids       VARCHAR(60)
)");

# files marked for death when their key is overwritten.  then they get a new
# fid, but since the old row (with the old fid) had to be deleted immediately,
# we need a place to store the fid so an async job can delete the file from
# all devices.
    $run->("CREATE TABLE IF NOT EXISTS file_to_delete (
   fid  INT UNSIGNED NOT NULL,
   PRIMARY KEY (fid)
)");

# if the replicator notices that a fid has no sources, that file gets inserted
# into the unreachable_fids table.  it is up to the application to actually
# handle fids stored in this table.
    $run->("CREATE TABLE IF NOT EXISTS unreachable_fids (
   fid        INT UNSIGNED NOT NULL,
   lastupdate INT UNSIGNED NOT NULL,
   PRIMARY KEY (fid),
   INDEX (lastupdate)
)");

# what files are on what devices?  (most likely physical devices,
# as logical devices of RAID arrays would be costly, and mogilefs
# already handles redundancy)
#
# the devid index lets us answer "What files were on this now-dead disk?"
#
    $run->("CREATE TABLE IF NOT EXISTS file_on (
   fid          INT UNSIGNED NOT NULL,
   devid        MEDIUMINT UNSIGNED NOT NULL,
   PRIMARY KEY (fid, devid),
   INDEX (devid)
)");

# if application or framework detects an error in one of the duplicate files
# for whatever reason, it can register its complaint and the framework
# will do some verifications and fix things up w/ an async job
# MAYBE: let application tell us the SHA1/MD5 of the file for us to check
#        on the other devices?
     $run->("CREATE TABLE IF NOT EXISTS file_on_corrupt (
   fid          INT UNSIGNED NOT NULL,
   devid        MEDIUMINT UNSIGNED NOT NULL,
   PRIMARY KEY (fid, devid)
)");

# hosts (which contain devices...)
    $run->("CREATE TABLE IF NOT EXISTS host (
   hostid     MEDIUMINT UNSIGNED NOT NULL,
   PRIMARY KEY (hostid),

   status     ENUM('alive','dead','down'),
   http_port  MEDIUMINT UNSIGNED DEFAULT 7500,
   http_get_port MEDIUMINT UNSIGNED,

   hostname   VARCHAR(40),
   UNIQUE     (hostname),
   hostip     VARCHAR(15),
   UNIQUE     (hostip),
   altip      VARCHAR(15),
   UNIQUE     (altip),
   altmask    VARCHAR(18),

   remoteroot  VARCHAR(60)
)");

# disks
    $run->("CREATE TABLE IF NOT EXISTS device (
   devid   MEDIUMINT UNSIGNED NOT NULL,
   PRIMARY KEY (devid),

   hostid     MEDIUMINT UNSIGNED NOT NULL,

   status  ENUM('alive','dead','down'),
   INDEX   (status),

   mb_total   MEDIUMINT UNSIGNED,
   mb_used    MEDIUMINT UNSIGNED,
   mb_asof    INT UNSIGNED
)");

        # see if they have the get port, else update it
    unless (column_type("host", "http_get_port")) {
        $run->("ALTER TABLE host ADD COLUMN http_get_port MEDIUMINT UNSIGNED AFTER http_port");
    }

    unless (column_type("host", "altip")) {
        $run->("ALTER TABLE host ADD COLUMN altip VARCHAR(15) AFTER hostip");
        $run->("ALTER TABLE host ADD COLUMN altmask VARCHAR(18) AFTER altip");
        $run->("ALTER TABLE host ADD UNIQUE altip (altip)");
    }

    unless (column_type("device", "mb_asof")) {
        $run->("ALTER TABLE device ADD COLUMN mb_asof INT(10) UNSIGNED AFTER mb_used");
    }

    unless (column_type("device", "weight")) {
        $run->("ALTER TABLE device ADD COLUMN weight MEDIUMINT DEFAULT 100 AFTER status");
    }

    unless (column_type("device", "status") =~ /readonly/) {
        $run->("ALTER TABLE device MODIFY COLUMN status ENUM('alive', 'dead', 'down', 'readonly')");
    }


    $run->("CREATE TABLE IF NOT EXISTS server_settings (
   field   VARCHAR(50) PRIMARY KEY,
   value   VARCHAR(255))");

    $run->("REPLACE INTO server_settings SET field='schema_version', value='$SCHEMA_VERSION'");

    # nexttry is time to try to replicate it next.
    #   0 means immediate.  it's only on one host.
    #   1 means lower priority.  it's on 2+ but isn't happy where it's at.
    #   unixtimestamp means at/after that time.  some previous error occurred.
    # fromdevid, if not null, means which devid we should replicate from.  perhaps it's the only non-corrupt one.  otherwise, wherever.
    # failcount.  how many times we've failed, just for doing backoff of nexttry.
    # flags.  reserved for future use.
    $run->("CREATE TABLE IF NOT EXISTS file_to_replicate (
   fid        INT UNSIGNED NOT NULL PRIMARY KEY,
   nexttry    INT UNSIGNED NOT NULL,
   INDEX (nexttry),
   fromdevid  INT UNSIGNED,
   failcount  TINYINT UNSIGNED NOT NULL DEFAULT 0,
   flags      SMALLINT UNSIGNED NOT NULL DEFAULT 0
   )");

    return 1;
}

sub schema_version {
    return eval {
        $dbh->selectrow_array("SELECT value FROM server_settings WHERE field='schema_version'") || 0;
    } || 0;
}

sub column_type {
    my ($table, $col) = @_;
    my $sth = $dbh->prepare("DESCRIBE $table");
    $sth->execute;
    while (my $rec = $sth->fetchrow_hashref) {
        if ($rec->{Field} eq $col) {
            $sth->finish;
            return $rec->{Type};
        }
    }
    return undef;
}


sub table_exists {
    my $table = shift;
    return eval {
        my $sth = $dbh->prepare("DESCRIBE $table");
        $sth->execute;
        my $rec = $sth->fetchrow_hashref;
        return $rec ? 1 : 0;
    };
}