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