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
  29 my $host = 'localhost';
  30 my $sid = $ENV{ORACLE_SID};
  31 my $user;
  32 my $pw;
  33 my $out;
  34 my $sql;
  35 my $table;
  36 my $pg_table;
  37 my $base_table;
  38 my $column_prefix = '';
  39 my $show_help;
  40 my $src_charset;
  41
  42 my $result = GetOptions(
  43     'sid=s'             => \$sid,
  44     'host=s'            => \$host,
  45     'user=s'            => \$user,
  46     'pw=s'              => \$pw,
  47     'out=s'             => \$out,
  48     'sql=s'             => \$sql,
  49     'table=s'           => \$table,
  50     'pg-table=s'        => \$pg_table,
  51     'column-prefix=s'   => \$column_prefix,
  52     'inherits-from=s'   => \$base_table,
  53     'source-charset=s'  => \$src_charset,
  54     'help'              => \$show_help,
  55 );
  56
  57 if ($show_help || !$result || !$out || !$sql || !$user || !$pw || !$table || !$pg_table) {
  58     print <<_USAGE_;
  59 $0: dump contents of Oracle table to file for loading into PostgreSQL
  60
  61 Usage: $0 \\
  62     [--sid oracle_sid] [--host oracle_host] --user oracle_user --pw oracle_password \\
  63     --table oracle_table_name \\
  64     --pg-table destination_pg_table_name \\
  65     --out output_tsv_file --sql output_table_create_sql_file \\
  66     [--column-prefix column_prefix] [--inherits-from base_pg_table] [--help]
  67
  68 _USAGE_
  69     exit 1;
  70 }
  71
  72 my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $pw) or croak "Cannot connect to the database";
  73 $dbh->do("ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'");
  74
  75 open my $outfh, '>', $out or croak "Cannot open output file $out: $!\n";
  76 binmode $outfh, ':raw';
  77 open my $sqlfh, '>', $sql or croak "Cannot open output file $sql: $!\n";
  78 binmode $sqlfh, ':raw';
  79
  80 export_table(uc $table, $outfh, $sqlfh, $out);
  81
  82 close $outfh;
  83 close $sqlfh;
  84
  85 exit 0;
  86
  87 sub export_table {
  88     my $table = shift;
  89     my $fh = shift;
  90     my $sqlfh = shift;
  91     my $out = shift;
  92     my $cols = get_columns($table);
  93     my $query = 'SELECT ' . join(', ', map { $_->{name} } @$cols) . " FROM $table";
  94     my $sth = $dbh->prepare($query);
  95     $sth->execute();
  96     while (my $row = $sth->fetchrow_arrayref()) {
  97         my @data = map { normalize_value_for_tsv($_) } @$row;
  98         my $str = join("\t", @data);
  99         $str =~ s/\0//g;
 100         print $fh encode('utf8', "$str\n");
 101     }
 102     $sth->finish();
 103
 104     print $sqlfh "CREATE TABLE $pg_table (\n";
 105     print $sqlfh join(",\n", map { $column_prefix . lc($_->{name}) . " $_->{type}" } @$cols);
 106     print $sqlfh "\n)";
 107     print $sqlfh " INHERITS (${base_table})" if $base_table;
 108     print $sqlfh ";\n";
 109     my $out2 = $out;
 110     $out2 =~ s!.*/!!;
 111     print $sqlfh "\\COPY $pg_table (" . join(", ", map { $column_prefix . lc($_->{name}) } @$cols) . ") FROM '$out'\n";
 112     return;
 113 }
 114
 115 sub normalize_value_for_tsv {
 116     my $val = shift;
 117     if (defined $val) {
 118         $val =~ s/\\/\\\\/g;
 119         $val =~ s/\0//g;     # FIXME: not dealing with BLOBs for now
 120         $val =~ s/[\b]/\\b/g;
 121         $val =~ s/\f/\\f/g;
 122         $val =~ s/\r/\\r/g;
 123         $val =~ s/\n/\\n/g;
 124         $val =~ s/\t/\\t/g;
 125         $val =~ s/\v/\\v/g;
 126         if ($src_charset) {
 127             return decode($src_charset, $val);
 128         } else {
 129             return $val;
 130         }
 131     } else {
 132         return '\N';
 133     }
 134 }
 135
 136 sub get_columns {
 137     my $table = shift;
 138     my $sth_cols = $dbh->prepare('
 139         SELECT column_name, data_type, data_precision, data_scale, data_length, nullable
 140         FROM user_tab_columns WHERE table_name = ? ORDER BY column_id
 141     ');
 142     $sth_cols->execute($table);
 143     my @cols = map { { name => $_->{COLUMN_NAME}, type => get_pg_column_type($_) } }
 144                @{ $sth_cols->fetchall_arrayref({}) };
 145     $sth_cols->finish();
 146     return \@cols;
 147 }
 148
 149 sub get_pg_column_type {
 150     my $column_def = shift;
 151     my $type;
 152     if ($column_def->{DATA_TYPE} =~ /VARCHAR/) {
 153         $type = 'TEXT';
 154     } elsif ($column_def->{DATA_TYPE} eq 'DATE') {
 155         $type = 'TIMESTAMP';
 156     } elsif ($column_def->{DATA_TYPE} eq 'NUMBER') {
 157         if (!defined($column_def->{DATA_SCALE}) || $column_def->{DATA_SCALE} == 0) {
 158             $type = 'INTEGER';
 159         } else {
 160             $type = "NUMERIC($column_def->{DATA_PRECISION},$column_def->{DATA_SCALE})";
 161         }
 162     } elsif ($column_def->{DATA_TYPE} eq 'CHAR') {
 163         $type = "CHAR($column_def->{DATA_LENGTH})";
 164     }
 165     if (defined $type) {
 166         $type .= " NOT NULL" if $column_def->{NULLABLE} eq 'N';
 167         return $type;
 168     } else {
 169         return 'UNKNOWN';
 170     }
 171 }