# MySQL database
my $hostname = "localhost";     # where can we find MySQL? Try not to change this
                                # (or adapt the mysql*Handle functions below)
my $gridDb = "opensim_grid";    # the name of the grid-db
my $regionDb = "opensim_region";# the name of the region-db
my $user = "<username>";        # the username for db-access
my $password = "<password>";    # the password for db-access


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

sub mysqlGridDBHandle {
  return DBI->connect("DBI:mysql:database=$gridDb;host=$hostname", $user, $password,
                       { RaiseError => 1, AutoCommit => 0 }) or
    die "$0: exit due to DB problems\n";
}

sub mysqlRegionDBHandle {
  return DBI->connect("DBI:mysql:database=$regionDb;host=$hostname", $user, $password,
                       { RaiseError => 1, AutoCommit => 0 }) or
    die "$0: exit due to DB problems\n";
}

sub formatUUID {
  my $uuid = shift;
  $uuid =~ s#(.{8})(.{4})(.{4})(.{4})(.{12})#$1-$2-$3-$4-$5#;
  return $uuid;
}

sub deleteContents {
  my $db = shift;
  my $table = shift;

  $db->do("DELETE FROM $table") or
    die "$0: exit due to DB problems\n";
}


sub transfer {
  my $db1 = shift;
  my $db2 = shift;
  my $table = shift;
  my $nameList = shift;
  my $sub = shift;
  my $destTable = $table;

  my $names = join(", ", @$nameList);

  my $placeholders;
  # HACK: "users" table is a special case
  # HACK: "useragents" table is anoterh special case
  if($table eq "users") {
    $placeholders = join(", ", ("?") x $#$nameList);  # $# gives us length - 1
  }
  elsif($table eq "useragents") {
    $destTable = "agents";
    $placeholders = join(", ", ("?") x ($#$nameList - 3));
  }
  else {
    $placeholders = join(", ", ("?") x @{$nameList});
  }

  print "Transferring contents of table $table\n";
  print "Read ".($#$nameList + 1)." columns.\n";

  # Statement for fetching from SQLite3. Need to pay attention to the different
  # order in MySQL.
  my $st1 = $db1->prepare("SELECT DISTINCT $names FROM $table") or
    die "$0: exit due to DB problems\n";

  # Statement for inserting into MySQL
  my $st2 = $db2->prepare("INSERT INTO $destTable VALUES ($placeholders)") or
    die "$0: exit due to DB problems\n";

  # Do the select
  $st1->execute;

  my $idx = 0;
  # get the rows, one by one
  while(my @row = $st1->fetchrow_array) {
    if($table eq "users") {
      # HACK ALERT: This expects that you don't change the order of fields in the
      # call to transfer. We have to compute the regionHandle
      my $x = Math::BigInt->new($row[5])->blsft(8);  # multiply with 256
      my $y = Math::BigInt->new($row[6])->blsft(8);  # multiply with 256
      my $handle = $x->blsft(32)->bior($y);
      splice @row, 5, 2, ($handle);
    }
    elsif($table eq "useragents") {
      # HACK ALERT: Don't change the order of the fields!
      my $pos = "<$row[10], $row[11], $row[12]>";
      my $lookat = "<$row[13], $row[14], $row[15]>";
      splice @row, 10, 3, ($pos);
      splice @row, 11, 3, ($lookat);
    }
    elsif($table eq "userfriends") {
      # HACK: Another special case: datatimestamp is nullable in SQLite, but not in MySQL
      $row[3] = 0 unless defined($row[3]);
    }

    &$sub(\@row) if defined $sub;

    # insert one row into MySQL and complain bitterly if something went wrong
    $st2->execute(@row) or
      die "$0: exit due to DB problems\n";

    if(++$idx % 100 == 0) {
      print "  Transferred $idx entries\n"; 
    }
  }

  print "Finished transferring $idx entries for table $table.\n";
}