#!/usr/bin/perl -w

# Migration script for OTRS 6.x from MySQL to Oracle (XE)
#
# You will need these packages from Oracle (newer versions may be ok):
# oracle-instantclient12.2-basiclite-12.2.0.1.0-1.x86_64.rpm
# oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
# oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
#
# From CPAN:
# DBD-Oracle-1.74.tar.gz
# 
# Run these scripts against the Oracle database:
# 1. otrs-schema.oracle.sql
# 2. mysql2oracle.pl (this script)
# 3. otrs-schema-post.oracle.sql

use strict;
use warnings;
use DBI;

my %ignore_tables = map { $_ => undef } qw(
  cip_allocate
  configitem
  configitem_counter
  configitem_definition
  configitem_history
  configitem_history_type
  configitem_version
  faq_attachment
  faq_category
  faq_category_group
  faq_history
  faq_item
  faq_language
  faq_log
  faq_state
  faq_state_type
  faq_voting
  general_catalog
  general_catalog_preferences
);

my $srcdbh = DBI->connect("DBI:mysql:dbname=otrs;host=192.168.0.9", "otrs", "password");
$srcdbh->do("SET NAMES 'utf8'");

my $dstdbh = DBI->connect("DBI:Oracle://192.168.0.8:1521/XE", "otrs", "password", {
  ora_charset => "AL32UTF8"
});
$dstdbh->begin_work;
$dstdbh->do("ALTER SESSION SET nls_date_format = 'yyyy/mm/dd hh24:mi:ss'");

my $tables = $srcdbh->selectall_arrayref("SHOW TABLES");

foreach my $table(map { $_->[0] } @$tables) {
  next if exists $ignore_tables{$table};

  my $srcsth = $srcdbh->prepare("SELECT * FROM $table");
  $srcsth->execute;

  my $dststh = $dstdbh->prepare("SELECT * FROM $table");
  $dststh->execute;
  my @cols = map { lc } sort @{$dststh->{NAME}};
  my $sql = "INSERT INTO $table (" . join(",", @cols) .
            ") VALUES (" . ("?," x $#cols) . "?)";
  $dststh->finish;
  $dststh = $dstdbh->prepare($sql);

  while (my $row = $srcsth->fetchrow_hashref) {
    $dststh->execute(map { $row->{$_} } @cols);
  }

  $dststh->finish if $dststh;
  $srcsth->finish;
}

$dstdbh->commit;
$dstdbh->disconnect;
$srcdbh->disconnect;