#!/usr/bin/perl -CSAD

use strict;
use warnings FATAL => 'all';

use Getopt::Long ();
use DBI ();

my $DATA_DIR = '/etc/sysconfig/rhn/schema-dump';

my ($db, $user, $password, $raw);

local *FILE;
open FILE, '<', '/etc/rhn/rhn.conf' or die "Error reading /etc/rhn/rhn.conf: $!\n";
while (<FILE>) {
	chomp;
	next if /^\s*(#|$)/;
	my ($key, $value) = /^\s*(\S+?)\s*=\s*(.+?)\s*$/;
	if (defined $key) {
		if ($key eq 'db_name') {
			$db = $value;
		} elsif ($key eq 'db_user') {
			$user = $value;
		} elsif ($key eq 'db_password') {
			$password = $value;
		} elsif ($key eq 'default_db') {
			($user, $password, $db) = ($value =~ m!^(.+?)/(.+?)\@(.+)$!);
			last;
		}
	}
}
close FILE;

sub usage {
	print "$0: [--db=SID --user=USERNAME --password=PASSWORD] [ --help ]\n";
	if ($@) {
		exit shift;
	}
	exit;
}

Getopt::Long::GetOptions (
	'db=s' => \$db,
	'user=s' => \$user,
	'password=s' => \$password,
	'raw' => \$raw,
	'help' => \&usage,
) or exit 1;

if (not $db && $user && $password) {
	usage();
}

$ENV{NLS_LANG} = 'AMERICAN_AMERICA.UTF8';
$ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';
$ENV{NLS_TIMESTAMP_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';
$ENV{NLS_TIMESTAMP_TZ_FORMAT} = 'YYYY-MM-DD HH24:MI:SS TZH:TZM';

my $dbh = DBI->connect("dbi:Oracle:$db", $user, $password, {
	RaiseError => 1,
	FetchHashKeyName => 'NAME_lc',
	LongReadLen => 20_000_000,
	LongTruncOk => 0,
	AutoCommit => 0,
});

$SIG{'PIPE'} = sub {
       die "SIGPIPE received.\n";
};

process_scripts($dbh, 'pre');

print <<'EOS';
\set ON_ERROR_STOP on
update pg_index
  set indisvalid = false,
      indisready = false
where indexrelid in (
      select pi.indexrelid
        from pg_index pi,
             pg_class pc,
             pg_namespace pn
       where pi.indexrelid = pc.oid and
             pc.relnamespace = pn.oid and
             pc.relkind = 'i'::"char" and
             pn.nspname = current_schema()
      );
EOS

my %ROWIDS = ();
my $SEQ = 1;
process_tables($dbh);
process_sequences($dbh);
if (not $raw) {
	my $dist = `rpm -qf --qf '%{release}\n' /etc/sysconfig/rhn/schema-dump`;
	chomp $dist;
	$dist =~ s/^.+(\.[^\.]+)$/$1/;
	for my $file (sort < $DATA_DIR/sql/* >) {
		local *FILE;
		open FILE, '<', $file or die "Error reading [$file]: $!\n";
		while (<FILE>) {
			s/\@\@DIST\@\@/$dist/;
			print;
		}
		close FILE;
	}
}

print <<'EOS';
update pg_index
  set indisvalid = true,
      indisready = true
where indexrelid in (
      select pi.indexrelid
        from pg_index pi,
             pg_class pc,
             pg_namespace pn
       where pi.indexrelid = pc.oid and
             pc.relnamespace = pn.oid and
             pc.relkind = 'i'::"char" and
             pn.nspname = current_schema()
      );
select pg_dblink_exec('reindex database "' || current_database() || '";');
EOS

exit 0;

END {
	return if (not defined $dbh);

	process_scripts($dbh, 'post');
	$dbh->disconnect();
}

sub get_table_scripts {
	my $filename = shift;

	return if (not -f $filename);

	local $_;
	local *FILE;
	open FILE, '<', $filename or die "Error reading [$filename]: $!\n";
	my $data = {};
	my $key;
	my $text;
	while (<FILE>) {
		chomp;
		if (/^\s*$/) {
			push @{$data->{$key}}, $text if ($text);
			$text = '';
		} elsif (/^\s/) {
			$text .= $_;
		} else {
			$key = join ',', sort split /,/, $_;
			$data->{$key} = ();
		}
	}
	push @{$data->{$key}}, $text if ($text);
	close FILE;
	return $data;
}

sub process_scripts {
	my $dbh = shift;
	my $stage = shift;

	return if ($raw or not -d "$DATA_DIR/$stage");

	my $tables = $dbh->selectall_hashref(q!
		select lower(table_name) table_name
		from user_tables
		where table_name not in ('PLAN_TABLE', 'PLAN_TABLE_9I') -- plan_table is not part of our schema
		order by table_name
	!, 'table_name');

	my %scripts;
	{
		local *DIR;
		opendir DIR, "$DATA_DIR/$stage" or die "Error reading [$DATA_DIR/$stage]: $!\n";
		while (defined($_ = readdir DIR)) {
			next if /^\.\.?$/ or not exists $tables->{$_};
			$scripts{$_} = get_table_scripts("$DATA_DIR/$stage/$_");
		}
		closedir DIR;
	}

	foreach my $table (keys %scripts) {
		next if (not defined $tables->{$table});

		my $sth = eval {
			local $dbh->{PrintError} = 0;
			$dbh->prepare("select * from $table");
		};

		my $columns;
		if (defined $sth and not $@) {
			$sth->execute();
			my $row = $sth->fetchrow_arrayref();
			$columns = join ',', sort @{$sth->{NAME_lc}};
		} else {
			die $@ if (not $DBI::err == 942);
		}

		next if (not exists $scripts{$table}{$columns});

		foreach my $script (@{$scripts{$table}{$columns}}) {
			$sth = $dbh->prepare($script);
			$sth->execute();
			$dbh->commit();
		}
	}
}

sub get_sequence_exception {
	local $_;
	my $name = shift;
	my $file = "$DATA_DIR/sequences/$name";
	if (not -f $file) {
		return;
	}
	local *EXCEPTION;
	open EXCEPTION, '<', $file or die "Error reading [$file]: $!\n";
	my $data = '';
	while (<EXCEPTION>) {
		if (/^skip\s*$/) {
			close EXCEPTION;
			return 'skip';
		}
		$data .= $_;
	}
	close EXCEPTION;
	return $data;
}

sub process_sequences {
	my $dbh = shift;
	my $sequences = $dbh->selectall_hashref(q!
		select lower(sequence_name) sequence_name, last_number
		from user_sequences
		order by sequence_name
	!, 'sequence_name');
	if (not $raw and -d "$DATA_DIR/sequences") {
		local *DIR;
		opendir DIR, "$DATA_DIR/sequences" or die "Error reading [$DATA_DIR/sequences]: $!\n";
		while (defined($_ = readdir DIR)) {
			next if /^\.\.?$/;
			$sequences->{$_} = 'missing' if not exists $sequences->{$_};
		}
		closedir DIR;
	}
	for (sort keys %$sequences) {
		my $last_number = $sequences->{$_}{last_number} if ref $sequences->{$_};;
		if (not $raw) {
			my $exception = get_sequence_exception($_);
			if (defined $exception) {
				if ($exception =~ /^missing sequence/) {
					if ($sequences->{$_} eq 'missing') {
						$last_number = $SEQ;
					}
				} elsif ($exception eq 'skip') {
					print "-- Skipping $_\n";
					next;
				} else {
					print "select pg_catalog.setval('$_', ( $exception )::bigint);\n";
					next;
				}
			}
		}
		$last_number = 1 if not defined $last_number;
		print "select pg_catalog.setval('$_', $last_number, false);\n";
	}
}

sub get_table_exception {
	local $_;
	my $name = shift;
	my $file = "$DATA_DIR/tables/$name";
	if (not -f $file) {
		return;
	}
	local *EXCEPTION;
	open EXCEPTION, '<', $file or die "Error reading [$file]: $!\n";
	my $data = {};
	my $key;
	while (<EXCEPTION>) {
		if (/^skip\s*$/) {
			close EXCEPTION;
			return 'skip';
		}
		if (/^\s/) {
			$data->{$key} .= $_;
		} else {
			chomp;
			$key = join ',', sort split /,/, $_;
		}
	}
	close EXCEPTION;
	return $data;
}

sub process_tables {
	my $dbh = shift;
	my $tables = $dbh->selectall_hashref(q!
		select lower(table_name) table_name
		from user_tables
		where table_name not in ('PLAN_TABLE', 'PLAN_TABLE_9I') -- plan_table is not part of our schema
		order by table_name
	!, 'table_name');
	if (not $raw and -d "$DATA_DIR/tables") {
		local *DIR;
		opendir DIR, "$DATA_DIR/tables" or die "Error reading [$DATA_DIR/tables]: $!\n";
		while (defined($_ = readdir DIR)) {
			next if /^\.\.?$/;
			$tables->{$_} = 'missing' if not exists $tables->{$_};
		}
		closedir DIR;
	}
	my %exceptions;
	for (sort keys %$tables) {
		if (not $raw) {
			my $exception = get_table_exception($_);
			if (defined $exception) {
				$exceptions{$_} = $exception;
				if ($tables->{$_} eq 'missing'
					and ref $exceptions{$_}) {
					if (exists $exceptions{$_}{'missing table'}) {
						$exceptions{$_} = { 'missing table' => $exceptions{$_}{'missing table'} };
					} else {
						$exception = $exceptions{$_} = 'skip';
					}
				}
				if ($exception eq 'skip') {
					print "-- Skipping $_\n";
					next;
				}
			}
		}

		print "alter table $_ disable trigger all;\n";
		print "alter table $_ set (autovacuum_enabled = false);\n";
	}
	for (sort keys %$tables) {
		if (exists $exceptions{$_} and $exceptions{$_} eq 'skip') {
			next;
		}
		print "delete from $_;\n";
	}
	for (sort keys %$tables) {
		if (exists $exceptions{$_} and $exceptions{$_} eq 'skip') {
			next;
		}
		next if /^qrtz_/;		# skip the quartz tables, they get regenerated anyway
		process_table($dbh, $_, $exceptions{$_});
	}
	for (sort keys %$tables) {
		if (exists $exceptions{$_} and $exceptions{$_} eq 'skip') {
			next;
		}
		print "alter table $_ enable trigger all;\n";
		print "alter table $_ set (autovacuum_enabled = true);\n";
	}
}

sub process_table {
	my ($dbh, $table, $exception) = @_;
	my ($row, $names, $the_names);
	my $sth = eval {
		local $dbh->{PrintError} = 0;
		$dbh->prepare("select * from $table");
	};
	my $sorted_the_names = '';
	if (defined $sth and not $@) {
		$sth->execute();
		$row = $sth->fetchrow_arrayref();
		$names = $sth->{NAME_lc};
		$the_names = join ',', @$names;
		$sorted_the_names = join ',', sort @$names;
	} else {
		if (not $DBI::err == 942) {
			die $@;
		}
		$sorted_the_names = $the_names = 'missing table';
	}
	if (ref $exception) {
		if (exists $exception->{$sorted_the_names}) {
			$sth = $dbh->prepare($exception->{$sorted_the_names});
			$sth->execute();
			$row = $sth->fetchrow_arrayref();
			$names = $sth->{NAME_lc};
			print "-- Original columns for $table: $the_names\n";
			$the_names = join ',', @$names;
		}
	}
	my @use_rowids = ();
	for (my $i = 0; $i < @$names; $i++) {
		if ($names->[$i] =~ s/^rowid_//) {
			$use_rowids[$i] = 1;
		}
	}
	if (@use_rowids) {
		$the_names = join ',', @$names;
	}
	my $types = $sth->{TYPE};
	my @types;
	for (my $i = 0; $i < @$types; $i++) {
		my $type = eval { $dbh->type_info($types->[$i])->{TYPE_NAME} };
		push @types, ( defined $type ? $type : 'unknown' );
	}
	print "-- Types for $table: @types\n";
	print qq!copy $table($the_names) from stdin;\n!;
	while ($row) {
		for (my $i = 0; $i < @$row; $i++) {
			print "\t" if $i;
			if (defined $use_rowids[$i]) {
				if (not defined $ROWIDS{$row->[$i]}) {
					$SEQ++;
					$ROWIDS{$row->[$i]} = $SEQ;
				}
				print $ROWIDS{$row->[$i]};
			} elsif (defined $row->[$i]) {
				if (ref $row->[$i] and ref $row->[$i] eq 'ARRAY') {	# user types
					no warnings 'uninitialized';
					my @r = @{$row->[$i]};
					map { s/,/\\\\,/g; } @r;
					$row->[$i] = "(@{[ join ',', @r ]})";
				} elsif ($types[$i] eq 'unknown' or $types[$i] eq 'BLOB') {	# blobs
					$row->[$i] =~ s!(.)! sprintf "\\\\%03o", ord($1) !seg;
				} else {
					utf8::encode($row->[$i]);
					$row->[$i] =~ s!([\x00-\x1f\x5c])! sprintf "\\x%02x", ord($1) !seg;
					utf8::decode($row->[$i]);
				}
				print $row->[$i];
			} else {
				print '\N';
			}
		}
		print "\n";
		$row = $sth->fetchrow_arrayref();
	}
	print "\\.\n";
}

1;

__END__

=head1 NAME

spacewalk-dump-schema

=head1 SYNOPSIS

	spacewalk-dump-schema --db=SID \
		--user=USERNAME --password=PASSWORD

	spacewalk-dump-schema --help

=head1 DESCRIPTION

The B<spacewalk-dump-schema> script dumps the content of Spacewalk's
Oracle database schema in format which can be fed to PostgreSQL's
B<psql>. Thus it can be used to convert Spacewalk with Oracle database
backend to the one which uses PostgreSQL database backend.

The script connects to the Oracle instance, so that one must be
running and accessible with given connect parameter.

The output is printed to the standard output. It can be piped to
B<psql> directly in which case the PostgreSQL server must also be
running, or you can redirect it to a file and use that file as
input for B<psql> later.

The database schema in PostgreSQL must already exist, created by
(probably) B<spacewalk-setup>. The output of this script does not
create any tables or other database objects, it only emits commands
to set sequences and table contents in the PostgreSQL database schema.
Any existing content in those PostgreSQL tables is discarded, data
is not appended.

The exit value is 0 upon success, non-zero value upon error.

=head1 EXAMPLE

	spacewalk-dump-schema --db=xe \
		--user=spacewalk --password=o9k2HInsl \
		| PGPASSWORD=o9k2HInsl psql -h localhost \
			-U spaceuser spaceschema

=head1 AUTHOR

Jan Pazdziora

=cut

