#!/usr/bin/perl

use strict;
use DBI;
  
my $NUMBUGS      = 300_000;
my $STRFIELDS    = 10;
my $INTFIELDS    =  2;
my $METAFRACTION = .01;  # fraction of random strings with "meta" inserted

open WORDS, '/usr/share/dict/words'
    or die "Can't open /usr/share/dict/words: $!\n";
chomp(my @words = <WORDS>);
close WORDS;

# Connect to database using parameters in localconfig or hardcoded here.
our $db_host = "localhost";         # where is the database?
our $db_port = 3306;                # which port to use
our $db_sock = '';
our $db_name = "bugs_test";              # name of the MySQL database
our $db_user = "root";              # user to attach to the MySQL database
our $db_pass = '';
do "localconfig" or die "couldn't do localconfig: $!\n" if -e "localconfig";
my $dsn = "DBI:mysql:host=$db_host;database=$db_name;port=$db_port";
$dsn .= ";mysql_socket=$db_sock" if $db_sock;
my $dbh = DBI->connect($dsn, $db_user, $db_pass);

my $strfieldname  = 'aa';
my @strfieldnames;
for (1 .. $STRFIELDS) { push(@strfieldnames, $strfieldname++) }

my $intfieldindex = 0;

my $strcols = join ", ",
              map "cf_" . $_ . " TINYTEXT",
              @strfieldnames;

my $intcols = join ", ",
              map "cf_int" . ++$intfieldindex . " INTEGER",
              1 ..  $INTFIELDS;

$dbh->do(qq{
    CREATE TABLE realcol (bug_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
                          $strcols, $intcols)
});

$dbh->do(qq{
    CREATE TABLE distcol_str (bug_id INTEGER NOT NULL,
                              field_id INTEGER NOT NULL,
                              value TINYTEXT,
                              PRIMARY KEY (bug_id, field_id),
                              INDEX (field_id))
});

$dbh->do(qq{
    CREATE TABLE distcol_int (bug_id INTEGER NOT NULL,
                              field_id INTEGER NOT NULL,
                              value INTEGER,
                              PRIMARY KEY (bug_id, field_id),
                              INDEX (field_id))
});

# Create real-distributed tables.
foreach my $field (@strfieldnames) {
  $dbh->do("CREATE TABLE cf_$field (bug_id INTEGER NOT NULL, cf_$field TINYTEXT NOT NULL)");
}


my $placeholders = join ',', ('?') x ($STRFIELDS + $INTFIELDS);

my $insert1 = $dbh->prepare(qq{
    INSERT INTO realcol VALUES (NULL, $placeholders)
});

my $insert2 = $dbh->prepare(qq{
    INSERT INTO distcol_str VALUES (?, ?, ?)
});

my $insert3 = $dbh->prepare(qq{
    INSERT INTO distcol_int VALUES (?, ?, ?)
});

# Prepare INSERT statements for real-distributed tables.
my @redi;
for ( my $i = 0; $i < scalar(@strfieldnames) ; $i++ ) {
  $redi[$i] = $dbh->prepare(qq{
      INSERT INTO cf_$strfieldnames[$i] VALUES (?, ?)
  });
}

my $lastins = $dbh->prepare(qq{
    SELECT LAST_INSERT_ID()
});

my $i = 0;
$| = 1;
for (1 .. $NUMBUGS) {
    my @strings = map random_string(), 1 .. $STRFIELDS;
    my @ints    = map int(rand(1000)), 1 .. $INTFIELDS;
    $insert1->execute(@strings, @ints);
    $lastins->execute();
    my $bugid = $lastins->fetchrow_array();
    $insert2->execute($bugid, $_, $strings[$_])
        for 0 .. $STRFIELDS - 1;
    $insert3->execute($bugid, $_ + $STRFIELDS, $ints[$_])
        for 0 .. $INTFIELDS - 1;
    for ( $i = 0; $i < scalar(@strfieldnames) ; $i++ ) {
      $redi[$i]->execute($bugid, $strings[$i]);
    }
    print "Generated $_/$NUMBUGS records.\n" if $_ % 1000 == 0;
}

# Fix the status whiteboard field (converting empty strings to nulls).
$dbh->do("ALTER TABLE bugs CHANGE status_whiteboard status_whiteboard MEDIUMTEXT NULL");
$dbh->do("UPDATE bugs SET status_whiteboard = NULL WHERE status_whiteboard = ''");

# Copy status whiteboard field data into distributed and real-distributed tables.
$dbh->do("CREATE TABLE status_whiteboard (bug_id MEDIUMINT NOT NULL, status_whiteboard TINYTEXT NOT NULL)");
$dbh->do("INSERT INTO distcol_str (bug_id, field_id, value) SELECT bug_id, 11, status_whiteboard FROM bugs WHERE status_whiteboard IS NOT NULL");
$dbh->do("INSERT INTO status_whiteboard (bug_id, status_whiteboard) SELECT bug_id, status_whiteboard FROM bugs WHERE status_whiteboard IS NOT NULL");

# Add fulltext indexes to text columns.
foreach my $field (@strfieldnames) {
  print "Adding fulltext index to realcol.cf_$field.\n";
  $dbh->do("ALTER TABLE realcol ADD FULLTEXT(cf_$field)");
  # For the moment, don't add indexes to all the real-distributed tables,
  # since each one takes a while to add, and we're only testing cf_aa so far
  # (for which we add the index specifically below--make sure to remove
  # the specific version if you uncomment this line, or you'll create
  # the index for cf_aa.cf_aa twice).
  #$dbh->do("ALTER TABLE cf_$field ADD FULLTEXT(cf_$field)");
}
print "Adding fulltext index to cf_aa.cf_aa.\n";
$dbh->do("ALTER TABLE cf_aa ADD FULLTEXT(cf_aa)");
print "Adding fulltext index to bugs.status_whiteboard.\n";
$dbh->do("ALTER TABLE bugs ADD FULLTEXT(status_whiteboard)");
print "Adding fulltext index to status_whiteboard.status_whiteboard.\n";
$dbh->do("ALTER TABLE status_whiteboard ADD FULLTEXT(status_whiteboard)");
print "Adding fulltext index to distcol_str.value.\n";
$dbh->do("ALTER TABLE distcol_str ADD FULLTEXT(value)");

print "Done.\n";

sub random_string {
    my $string = join " ", map $words[ rand $#words ], 1 .. 10;
    if (rand() < $METAFRACTION) {
        substr($string, rand(length($string)), 0) = 'meta'
    }
    return $string;
}

