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
  86 exit 0;
  87
  88 sub export_table {
  89     my $table = shift;
  90     my $fh = shift;
  91     my $sqlfh = shift;
  92     my $out = shift;
  93     my $cols = get_columns($table);
  94     my $query = 'SELECT ' . join(', ', map { $_->{name} } @$cols) . " FROM $table";
  95     my $sth = $dbh->prepare($query);
  96     $sth->execute();
  97     while (my $row = $sth->fetchrow_arrayref()) {
  98         my @data = map { normalize_value_for_tsv($_) } @$row;
  99         my $str = join("\t", @data);
 100         $str =~ s/\0//g;
 101         print $fh encode('utf8', "$str\n");
 102     }
 103     $sth->finish();
 104
 105     print $sqlfh "CREATE TABLE $pg_table (\n";
 106     print $sqlfh join(",\n", map { $column_prefix . lc($_->{name}) . " $_->{type}" } @$cols);
 107     print $sqlfh "\n)";
 108     print $sqlfh " INHERITS (${base_table})" if $base_table;
 109     print $sqlfh ";\n";
 110     my $out2 = $out;
 111     $out2 =~ s!.*/!!;
 112     print $sqlfh "\\COPY $pg_table (" . join(", ", map { $column_prefix . lc($_->{name}) } @$cols) . ") FROM '$out'\n";
 113     return;
 114 }
 115
 116 sub normalize_value_for_tsv {
 117     my $val = shift;
 118     if (defined $val) {
 119         $val =~ s/\\/\\\\/g;
 120         $val =~ s/\0//g;     # FIXME: not dealing with BLOBs for now
 121         $val =~ s/[\b]/\\b/g;
 122         $val =~ s/\f/\\f/g;
 123         $val =~ s/\r/\\r/g;
 124         $val =~ s/\n/\\n/g;
 125         $val =~ s/\t/\\t/g;
 126         $val =~ s/\v/\\v/g;
 127         if ($src_charset) {
 128             if ($src_charset eq 'marc8') {
 129                 return marc8_to_utf8($val);
 130             } else {
 131                 return decode($src_charset, $val);
 132             }
 133         } else {
 134             return $val;
 135         }
 136     } else {
 137         return '\N';
 138     }
 139 }
 140
 141 sub get_columns {
 142     my $table = shift;
 143     my $sth_cols = $dbh->prepare('
 144         SELECT column_name, data_type, data_precision, data_scale, data_length, nullable
 145         FROM user_tab_columns WHERE table_name = ? ORDER BY column_id
 146     ');
 147     $sth_cols->execute($table);
 148     my @cols = map { { name => $_->{COLUMN_NAME}, type => get_pg_column_type($_) } }
 149                @{ $sth_cols->fetchall_arrayref({}) };
 150     $sth_cols->finish();
 151     return \@cols;
 152 }
 153
 154 sub get_pg_column_type {
 155     my $column_def = shift;
 156     my $type;
 157     if ($column_def->{DATA_TYPE} =~ /VARCHAR/) {
 158         $type = 'TEXT';
 159     } elsif ($column_def->{DATA_TYPE} eq 'DATE') {
 160         $type = 'TIMESTAMP';
 161     } elsif ($column_def->{DATA_TYPE} eq 'NUMBER') {
 162         if (!defined($column_def->{DATA_SCALE}) || $column_def->{DATA_SCALE} == 0) {
 163             $type = 'INTEGER';
 164         } else {
 165             $type = "NUMERIC($column_def->{DATA_PRECISION},$column_def->{DATA_SCALE})";
 166         }
 167     } elsif ($column_def->{DATA_TYPE} eq 'CHAR') {
 168         $type = "CHAR($column_def->{DATA_LENGTH})";
 169     }
 170     if (defined $type) {
 171         $type .= " NOT NULL" if $column_def->{NULLABLE} eq 'N';
 172         return $type;
 173     } else {
 174         return 'UNKNOWN';
 175     }
 176 }