dump_oracle_table_for_pg

   1 #!/usr/bin/perl
   2
   3 # Copyright 2013, Equinox Software, Inc.
   4
   5 # Author: Galen Charlton <gmc@esilibrary.com>
   6 #
   7 # This program is free software; you can redistribute it and/or
   8 # modify it under the terms of the GNU General Public License
   9 # as published by the Free Software Foundation; either version 2
  10 # of the License, or (at your option) any later version.
  11 #
  12 # This program is distributed in the hope that it will be useful,
  13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 # GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with this program; if not, write to the Free Software
  19 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  20
  21 use strict;
  22 use warnings;
  23
  24 use Carp;
  25 use DBI;
  26 use Getopt::Long;
  27 use Encode;
  28 use MARC::Charset qw/marc8_to_utf8/;
  29
  30 my $host = 'localhost';
  31 my $sid = $ENV{ORACLE_SID};
  32 my $user;
  33 my $pw;
  34 my $out;
  35 my $sql;
  36 my $table;
  37 my $pg_table;
  38 my $base_table;
  39 my $column_prefix = '';
  40 my $show_help;
  41 my $src_charset;
  42
  43 my $result = GetOptions(
  44     'sid=s'             => \$sid,
  45     'host=s'            => \$host,
  46     'user=s'            => \$user,
  47     'pw=s'              => \$pw,
  48     'out=s'             => \$out,
  49     'sql=s'             => \$sql,
  50     'table=s'           => \$table,
  51     'pg-table=s'        => \$pg_table,
  52     'column-prefix=s'   => \$column_prefix,
  53     'inherits-from=s'   => \$base_table,
  54     'source-charset=s'  => \$src_charset,
  55     'help'              => \$show_help,
  56 );
  57
  58 if ($show_help || !$result || !$out || !$sql || !$user || !$pw || !$table || !$pg_table) {
  59     print <<_USAGE_;
  60 $0: dump contents of Oracle table to file for loading into PostgreSQL
  61
  62 Usage: $0 \\
  63     [--sid oracle_sid] [--host oracle_host] --user oracle_user --pw oracle_password \\
  64     --table oracle_table_name \\
  65     --pg-table destination_pg_table_name \\
  66     --out output_tsv_file --sql output_table_create_sql_file \\
  67     [--column-prefix column_prefix] [--inherits-from base_pg_table] [--help]
  68
  69 _USAGE_
  70     exit 1;
  71 }
  72
  73 my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $pw) or croak "Cannot connect to the database";
  74 $dbh->do("ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'");
  75
  76 open my $outfh, '>', $out or croak "Cannot open output file $out: $!\n";
  77 binmode $outfh, ':raw';
  78 open my $sqlfh, '>', $sql or croak "Cannot open output file $sql: $!\n";
  79 binmode $sqlfh, ':raw';
  80
  81 export_table(uc $table, $outfh, $sqlfh, $out);
  82
  83 close $outfh;
  84 close $sqlfh;
  85 $dbh->disconnect;
  86
  87 exit 0;
  88
  89 sub export_table {
  90     my $table = shift;
  91     my $fh = shift;
  92     my $sqlfh = shift;
  93     my $out = shift;
  94     my $cols = get_columns($table);
  95     my $query = 'SELECT ' . join(', ', map { $_->{name} } @$cols) . " FROM $table";
  96     my $sth = $dbh->prepare($query);
  97     $sth->execute();
  98     while (my $row = $sth->fetchrow_arrayref()) {
  99         my @data = map { normalize_value_for_tsv($_) } @$row;
 100         my $str = join("\t", @data);
 101         $str =~ s/\0//g;
 102         print $fh encode('utf8', "$str\n");
 103     }
 104     $sth->finish();
 105
 106     print $sqlfh "CREATE TABLE $pg_table (\n";
 107     print $sqlfh join(",\n", map { $column_prefix . lc($_->{name}) . " $_->{type}" } @$cols);
 108     print $sqlfh "\n)";
 109     print $sqlfh " INHERITS (${base_table})" if $base_table;
 110     print $sqlfh ";\n";
 111     my $out2 = $out;
 112     $out2 =~ s!.*/!!;
 113     print $sqlfh "\\COPY $pg_table (" . join(", ", map { $column_prefix . lc($_->{name}) } @$cols) . ") FROM '$out'\n";
 114     return;
 115 }
 116
 117 sub normalize_value_for_tsv {
 118     my $val = shift;
 119     if (defined $val) {
 120         $val =~ s/\\/\\\\/g;
 121         $val =~ s/\0//g;     # FIXME: not dealing with BLOBs for now
 122         $val =~ s/[\b]/\\b/g;
 123         $val =~ s/\f/\\f/g;
 124         $val =~ s/\r/\\r/g;
 125         $val =~ s/\n/\\n/g;
 126         $val =~ s/\t/\\t/g;
 127         $val =~ s/\v/\\v/g;
 128         if ($src_charset) {
 129             if ($src_charset eq 'marc8') {
 130                 return marc8_to_utf8($val);
 131             } else {
 132                 return decode($src_charset, $val);
 133             }
 134         } else {
 135             return $val;
 136         }
 137     } else {
 138         return '\N';
 139     }
 140 }
 141
 142 sub get_columns {
 143     my $table = shift;
 144     my $sth_cols = $dbh->prepare('
 145         SELECT column_name, data_type, data_precision, data_scale, data_length, nullable
 146         FROM user_tab_columns WHERE table_name = ? ORDER BY column_id
 147     ');
 148     $sth_cols->execute($table);
 149     my @cols = map { { name => $_->{COLUMN_NAME}, type => get_pg_column_type($_) } }
 150                @{ $sth_cols->fetchall_arrayref({}) };
 151     $sth_cols->finish();
 152     return \@cols;
 153 }
 154
 155 sub get_pg_column_type {
 156     my $column_def = shift;
 157     my $type;
 158     if ($column_def->{DATA_TYPE} =~ /VARCHAR/) {
 159         $type = 'TEXT';
 160     } elsif ($column_def->{DATA_TYPE} eq 'DATE') {
 161         $type = 'TIMESTAMP';
 162     } elsif ($column_def->{DATA_TYPE} eq 'NUMBER') {
 163         if (!defined($column_def->{DATA_SCALE}) || $column_def->{DATA_SCALE} == 0) {
 164             $type = 'INTEGER';
 165         } else {
 166             $type = "NUMERIC($column_def->{DATA_PRECISION},$column_def->{DATA_SCALE})";
 167         }
 168     } elsif ($column_def->{DATA_TYPE} eq 'CHAR') {
 169         $type = "CHAR($column_def->{DATA_LENGTH})";
 170     }
 171     if (defined $type) {
 172         $type .= " NOT NULL" if $column_def->{NULLABLE} eq 'N';
 173         return $type;
 174     } else {
 175         return 'UNKNOWN';
 176     }
 177 }