3 # Copyright 2013, Equinox Software, Inc.
5 # Author: Galen Charlton <gmc@esilibrary.com>
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.
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.
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.
28 use MARC::Charset qw/marc8_to_utf8/;
30 my $host = 'localhost';
31 my $sid = $ENV{ORACLE_SID};
39 my $column_prefix = '';
43 my $result = GetOptions(
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,
58 if ($show_help || !$result || !$out || !$sql || !$user || !$pw || !$table || !$pg_table) {
60 $0: dump contents of Oracle table to file for loading into PostgreSQL
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]
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'");
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';
81 export_table(uc $table, $outfh, $sqlfh, $out);
94 my $cols = get_columns($table);
95 my $query = 'SELECT ' . join(', ', map { $_->{name} } @$cols) . " FROM $table";
96 my $sth = $dbh->prepare($query);
98 while (my $row = $sth->fetchrow_arrayref()) {
99 my @data = map { normalize_value_for_tsv($_) } @$row;
100 my $str = join("\t", @data);
102 print $fh encode('utf8', "$str\n");
106 print $sqlfh "CREATE TABLE $pg_table (\n";
107 print $sqlfh join(",\n", map { $column_prefix . lc($_->{name}) . " $_->{type}" } @$cols);
109 print $sqlfh " INHERITS (${base_table})" if $base_table;
113 print $sqlfh "\\COPY $pg_table (" . join(", ", map { $column_prefix . lc($_->{name}) } @$cols) . ") FROM '$out'\n";
117 sub normalize_value_for_tsv {
121 $val =~ s/\0//g; # FIXME: not dealing with BLOBs for now
122 $val =~ s/[\b]/\\b/g;
129 if ($src_charset eq 'marc8') {
130 return marc8_to_utf8($val);
132 return decode($src_charset, $val);
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
148 $sth_cols->execute($table);
149 my @cols = map { { name => $_->{COLUMN_NAME}, type => get_pg_column_type($_) } }
150 @{ $sth_cols->fetchall_arrayref({}) };
155 sub get_pg_column_type {
156 my $column_def = shift;
158 if ($column_def->{DATA_TYPE} =~ /VARCHAR/) {
160 } elsif ($column_def->{DATA_TYPE} eq 'DATE') {
162 } elsif ($column_def->{DATA_TYPE} eq 'NUMBER') {
163 if (!defined($column_def->{DATA_SCALE}) || $column_def->{DATA_SCALE} == 0) {
166 $type = "NUMERIC($column_def->{DATA_PRECISION},$column_def->{DATA_SCALE})";
168 } elsif ($column_def->{DATA_TYPE} eq 'CHAR') {
169 $type = "CHAR($column_def->{DATA_LENGTH})";
172 $type .= " NOT NULL" if $column_def->{NULLABLE} eq 'N';