Vyššie na [Platon] / scripts / perl / oracle

Comments removed from default value

#!/usr/bin/perl

# $Platon: scripts/perl/oracle/oracledump.pl,v 1.35 2008-08-23 20:40:12 nepto Exp $

use strict;

use DBI;
use Getopt::Long 2.16;
use POSIX;
use Term::ReadKey 2.14;


#***********************************************************************
# Local subroutines
#***********************************************************************

sub escape_data($$);        # escapes data for an SQL statement

#***********************************************************************
# Package 'main' variables set by the mod_perl CGI environment
#***********************************************************************

$ENV{NLS_LANG}    = 'SLOVAK_SLOVAKIA.EE8ISO8859P2';
$ENV{ORACLE_HOME} = '/usr/oracle/app/oracle/product/8.0.5'
    if (! exists $ENV{ORACLE_HOME});

my $ORA_HOSTNAME;
my $ORA_VERSION;

my ($MAJOR_VERSION, $MINOR_VERSION) = q$Revision: 1.36 $ =~ /(\d+)\.(\d+)/;
my $VERSION = sprintf("%d.%02d", $MAJOR_VERSION - 1, $MINOR_VERSION);

my $dbh;        # database handle
my $sth;        # statement handle
my $table;        # foreach loop iterator
my $query;        # current query

my $CM = '--';        # comment marker for output; default is '--'


#***********************************************************************
# Parse command-line switches
#***********************************************************************

my $opt_database;        # database
my @opt_tables;            # tables

my $opt_debug;            # --debug
my $opt_help;            # -?|--help
my $opt_comment_marker;        # --comment-marker
my $opt_complete_insert;    # -c|--complete-insert
my $opt_compress;        # -C|--compress
my $opt_extend_insert_insert;    # -e|--extended_insert-insert
my $opt_add_drop_table;        # --add-drop-table
my $opt_add_locks;        # --add-locks
my $opt_add_transaction;    # --add-transaction
my $opt_allow_keywords;        # --allow-keywords
my $opt_flush_logs;        # -F|--flush-logs
my $opt_force;            # -f|--force
my $opt_host;            # -h|--host
my $opt_lock_tables;        # -l|--lock-tables
my $opt_no_create_info;        # -t|--no-create-info
my $opt_no_data;        # -d|--no-data
my $opt_owner;            # -o|--owner
my %opt_set_variable;        # -O|--set-variable
my $opt_opt;            # --opt
my $opt_password;        # -p|--password
my $opt_port;            # -P|--port
my $opt_quick;            # -q|--quick
my $opt_socket;            # -S|--socket
my $opt_tab;            # -T|--tab
my $opt_user;            # -u|--user
my $opt_verbose;        # -v|--verbose
my $opt_version;        # -V|--version
my @opt_where;            # -w|--where
my $opt_fields_terminated_by;    # --fields-terminated-by
my $opt_fields_enclosed_by;    # --fields-enclosed-by
my $opt_fields_optionally_enclosed_by;    # --fields-optionally-enclosed-by
my $opt_fields_escaped_by;    # --fields-escaped-by
my $opt_lines_terminated_by;    # --lines-terminated-by
my $opt_mysql_compliant_dump;    # --mysql-compliant-dump

my $help = <<MARK;    # help statement

oracledump v$VERSION

Dumping definition and data for an Oracle database or table
Usage: oracledump [options] database [tables]

* -- Only switches with an asterisk are currently supported.

  --debug=...           Output debug log. Often this is 'd:t:o,filename`
* -?, --help            Displays this help and exits.
* --comment-marker=...  Set comment marker to string; default is '--'
  -c, --complete-insert Use complete insert statements.
  -C, --compress        Use compression in server/client protocol
  -e, --extended_insert-insert Allows utilization of the new, much faster
                        INSERT syntax
* --add-drop-table      Add a 'drop table' before each create
  --add-locks           Add a locks around insert statements
* --add-transaction     Add SQL to turn autocommit off before INSERT 
                        statements and to add a commit statement after
  --allow-keywords      Allow creation of column names that are keywords
  -F  --flush-logs      Flush logs file in server before starting dump
  -f, --force           Continue even if we get an sql-error.
  -h, --host=...        Connect to host.
  -l, --lock-tables     Lock all tables for read.
* -t, --no-create-info  Don't write table creation info.
* -d, --no-data         No row information.
* -o, --owner=#         Owner of tables if not user.
  -O, --set-variable var=option
                        give a variable an value. --help lists variables
  --opt                 Same as --quick --add-drop-table --add-locks
                        --extended_insert-insert --use-locks
* -p, --password[=...]  Password to use when connecting to server.
                        If password is not given it's asked from the tty.
  -P, --port=...        Port number to use for connection.
  -q, --quick           Don't buffer query, dump directly to stdout.
  -S, --socket=...      Socket file to use for connection.
  -T, --tab=...         Creates tab separated textfile for each table to
                        given path. (creates .sql and .txt files)

                        NOTE: This only works if mysqldump is run on
                              the same machine as the mysqld daemon
* -u, --user=#          User for login if not current user.
  -v, --verbose         Print info about the various stages.
* -V, --version         Output version information and exit.
* -w, --where=          dump only selected records; QUOTES mandatory!
  EXAMPLES: "--where=user='jimf'" "-wuserid>1" "-wuserid<1"
  Use -T (--tab=...) with --fields-...
  --fields-terminated-by=...
                        Fields in the textfile are terminated by ...
  --fields-enclosed-by=...
                        Fields in the importfile are enclosed by ...
  --fields-optionally-enclosed-by=...
                        Fields in the i.file are opt. enclosed by ...
  --fields-escaped-by=...
                        Fields in the i.file are escaped by ...
  --lines-terminated-by=...
                        Lines in the i.file are terminated by ...

  --mysql-compliant-dump
                        Creates MySQL compliant SQL dump

Possible variables for option --set-variable (-O) are:
<none>
MARK


## We want exact matches to the switches

Getopt::Long::config('no_auto_abbrev', 'no_ignore_case');


my $rc = GetOptions(
        "debug=s"        => \$opt_debug,
        "help|?"        => \$opt_help,
        "comment-marker=s"    => \$opt_comment_marker,
        "c|complete-insert"    => \$opt_complete_insert,
        "C|compress"        => \$opt_compress,
        "e|extended_insert-insert"=> \$opt_extend_insert_insert,
        "add-drop-table"    => \$opt_add_drop_table,
        "add-locks"        => \$opt_add_locks,
        "add-transaction"    => \$opt_add_transaction,
        "allow-keywords"    => \$opt_allow_keywords,
        "F|flush-logs"        => \$opt_flush_logs,
        "f|force"        => \$opt_force,
        "h|host=s"        => \$opt_host,
        "l|lock-tables"        => \$opt_lock_tables,
        "t|no-create-info"    => \$opt_no_create_info,
        "d|no-data"        => \$opt_no_data,
        "o|owner=s"        => \$opt_owner,
        "O|set-variable=s%"    => \%opt_set_variable,
        "opt"            => \$opt_opt,
        "p|password:s"        => \$opt_password,
        "P|port=i"        => \$opt_port,
        "q|quick"        => \$opt_quick,
        "S|socket=i"        => \$opt_socket,
        "T|tab=s"        => \$opt_tab,
        "u|user=s"        => \$opt_user,
        "v|verbose"        => \$opt_verbose,
        "V|version"        => \$opt_version,
        "w|where=s@"        => \@opt_where,
        "fields-terminated-by=s"    => \$opt_fields_terminated_by,
        "fields-enclosed-by=s"    => \$opt_fields_enclosed_by,
        "fields-optionally-enclosed-by=s"    => \$opt_fields_optionally_enclosed_by,
        "fields-escaped-by=s"    => \$opt_fields_escaped_by,
        "lines-terminated-by=s"    => \$opt_lines_terminated_by,
        "mysql-compliant-dump" => \$opt_mysql_compliant_dump
           );

$opt_database = shift @ARGV;    # grab database name

@opt_tables = splice(@ARGV, 0);    # grab optional list of tables

# checking for user/pass@database
if ($opt_database =~ /^(.+)@(.+)$/) {
    $opt_database = $2;
    $opt_user     = $1;
    if ($opt_user =~ /^(.+)\/(.*)$/) {
        $opt_password = $2;
        $opt_user     = $1;
    }

}

#***********************************************************************
# Process command-line switches
#***********************************************************************

if (defined $opt_version)
{
    print STDERR "oracledump v$VERSION\n";
    exit 0;
}

if ( !$rc || defined $opt_help || !defined $opt_database )
{
    print STDERR $help;
    exit 1;
}

## Define $opt_user if it wasn't set

if (! defined $opt_user)
{
    ($opt_user) = getpwuid($<);
}


## Alter $opt_owner

if (defined $opt_owner)
{
    $opt_owner = uc $opt_owner;
}


## Read password into $opt_password if it wasn't set

if (! defined $opt_password || $opt_password eq "")
{
    print STDERR "Username: $opt_user\n";
    ReadMode 'noecho';
    print STDERR "Password: ";
    chop($opt_password = <STDIN>);
    print STDERR "\n";
    ReadMode 'restore';
}


## Set comment character if --comment-marker is set

if (defined $opt_comment_marker && length($opt_comment_marker) > 0)
{
    $CM = $opt_comment_marker;
}


#***********************************************************************
# Connect to the database with $dbh
#***********************************************************************

$dbh = DBI->connect("dbi:Oracle:$opt_database",
                    $opt_user,
                    $opt_password)
    || die "Cannot connect to database: $DBI::errstr";

#***********************************************************************
# Turn autocommit *off* and set transaction to *read only*
#***********************************************************************

$dbh->{AutoCommit}  = 0;
$dbh->{LongReadLen} = 5000000;

$dbh->do("set transaction read only")
    || die "Cannot set transaction to read only: $DBI::errstr";


#***********************************************************************
# Get list of tables if @opt_tables isn't set
#***********************************************************************

if (scalar @opt_tables < 1)
{
    my @row;
    my $query;

    if ( defined $opt_owner ) {
        $query = 'SELECT table_name FROM all_tables'
          . ' WHERE owner = \'' . $opt_owner . '\'';
    } else {
        $query = 'SELECT table_name FROM user_tables';
    }

    $sth = $dbh->prepare($query);
    $sth->execute() || die $DBI::errstr;

    while ( @row = $sth->fetchrow_array() )
    {
        push ( @opt_tables, @row );
    }

    $sth->finish();
}


#***********************************************************************
# Print out the header
#***********************************************************************

## Determine if we have access to the V$INSTANCE table

$sth = $dbh->prepare("SELECT TABLE_NAME
                        FROM DICTIONARY
                       WHERE TABLE_NAME = \'V\$INSTANCE\'");
$sth->execute() || die $DBI::errstr;
($table) = $sth->fetchrow_array();
$sth->finish();

if ($table eq "V\$INSTANCE")
{
    ## Query Oracle for its instance hostname

    $sth = $dbh->prepare("SELECT HOST_NAME
                            FROM V\$INSTANCE");
    if (defined $sth) {
        $sth->execute() || die $DBI::errstr;
        ($ORA_HOSTNAME) = $sth->fetchrow_array();
        $sth->finish();
    }
}

if (not defined $ORA_HOSTNAME)
{
    $ORA_HOSTNAME = '<V$INSTANCE.HOST_NAME inaccessible>';
}


## Query Oracle for its version

$sth = $dbh->prepare("SELECT banner
                        FROM V\$VERSION
                       WHERE UPPER(banner) LIKE 'ORA%'");

$sth->execute() || die $DBI::errstr;

($ORA_VERSION) = $sth->fetchrow_array();

$sth->finish();

my $additional_header_info = '';

if ( defined $opt_mysql_compliant_dump ) {

    #
    # Preparing NLS_DATE_FORMAT
    #
    $sth = $dbh->prepare('ALTER SESSION SET NLS_DATE_FORMAT = \'YYYY-MM-DD HH24:MI:SS\'')
      or die "DBI::prepare(): $DBI::errstr";

    #
    # Altering session
    #
    $sth->execute() or die "DBI::execute(): " . $sth->errstr;
    $sth->finish();

    $additional_header_info = ' (MySQL compliant dump)';
}



## Print header

print <<MARK;
$CM Oracle dump $VERSION$additional_header_info
$CM
$CM Host: $ORA_HOSTNAME     Database: $opt_database
$CM ------------------------------------------------------------
$CM Server version: $ORA_VERSION
MARK


#***********************************************************************
# Iterative over all tables, printing out requested information
#***********************************************************************

foreach $table (@opt_tables)
{
    my @column_names;
    my @column_types;
    my @row;
    my $table_out;
    my $table_for_select;
    my $record_count;


    $table_for_select = $table;
    if (defined $opt_owner) {
        $table_for_select = "$opt_owner.$table";
    }

    ## Check number of record

    $query = 'SELECT COUNT(*) FROM '.$table_for_select;
    $sth = $dbh->prepare($query);
    $sth->execute() || die $DBI::errstr;
    while (@row = $sth->fetchrow_array()) {
        $record_count = $row[0];
    }

    $table_out = $table;
    $table     = uc $table;
    if (defined $opt_mysql_compliant_dump) {
        $table_out = lc $table_out;
        $table_out = "`$table_out`";
    }
    

    ## Print table structure

    if (! defined $opt_no_create_info)
    {

    ## Print the per-table header

    print <<MARK;

$CM
$CM Table structure for table: $table_out
$CM
MARK
    ## Print drop table if requested

    if ( defined $opt_add_drop_table )
    {
        if ( defined $opt_mysql_compliant_dump ) {
            print <<MARK;
DROP TABLE IF EXISTS $table_out;
MARK
        } else {
            print <<MARK;
DROP TABLE $table_out;
MARK
        }
    }

    ## Print create table

    print <<MARK;
CREATE TABLE $table_out (
MARK

    }

    ## We always need this information to output data below

    if ( defined $opt_owner ) {
        $query = 'SELECT column_name,
                         data_type,
                         data_length,
                         data_precision,
                         data_scale,
                         data_default,
                         nullable
                    FROM all_tab_columns
                   WHERE data_type != \'XMLTYPE\'
                     AND data_type != \'AQ$_SUBSCRIBERS\'
                     AND UPPER(table_name) = \''.$table.'\'
                     AND UPPER(owner) = \''.$opt_owner.'\'';
    } else {
        $query = 'SELECT column_name,
                         data_type,
                         data_length,
                         data_precision,
                         data_scale,
                         data_default,
                         nullable
                    FROM user_tab_columns
                   WHERE data_type != \'XMLTYPE\'
                     AND data_type != \'AQ$_SUBSCRIBERS\'
                     AND UPPER(table_name) = \''.$table.'\'';
    }

    $sth = $dbh->prepare($query);
    $sth->execute() || die $DBI::errstr;

    my $c = 0;
    while (@row = $sth->fetchrow_array())
    {
        $c++;

        if ( !defined $opt_no_create_info ) {
            if ($opt_mysql_compliant_dump) {
                if ($row[5] =~ /^to_date/i or $row[5] =~ /^(USER|UID)/i or $row[5] =~ /\(\)/ or $row[5] =~ /--/) {
                    undef $row[5];
                } elsif ($row[5] =~ /\((.*)\)/) {
                    $row[5] = $1;
                } elsif ($row[5] == 'NULL' and $row[6] ne 'Y') {
                    # default NULL for NOT NULL column
                    undef $row[5];
                }
                printf( " %1s%-32s %-15s %-21s %-8s\n",
                    ( $c != 1 ? ',' : ' ' ),
                    '`'.lc( $row[0] ).'`',
                    ( $row[1] eq 'NUMBER'
                        ? ( int($row[4]) > 0
                            ? 'DECIMAL(' . $row[3] . ', ' . $row[4] . ')'
                            : ( int( $row[3] ) <= 11
                                ? 'INT' : 'INT(' . $row[3] . ')' )
                          )
                          : ( (($row[1] eq 'VARCHAR2') or ($row[1] eq 'NVARCHAR2') or ($row[1] eq 'ROWID'))
                              ? ( $row[2] > 255
                                  ? 'TEXT' : 'VARCHAR(' . $row[2] . ')' )
                              : ( $row[1] eq 'DATE'
                                  ? 'DATETIME'
                                  : ( (($row[1] eq 'CLOB') or ($row[1] eq 'NCLOB') or ($row[1] eq 'PRACFOND'))
                                      ? 'BLOB'
                                      : ($row[1] =~ /RAW/i
                                          ? 'BLOB' # some MySQL's RAW equivalent should be here
                                          : $row[1]) ) ) ) ),
                    ( defined $row[5]
                        ? ((($row[5] =~ /^SYSDATE/i) or ($row[1] =~ /RAW/i)) ? '' : 'DEFAULT ' . $row[5])
                        : '' ),
                    ( $row[6] eq 'Y'
                        ? ''
                        : 'NOT NULL' )
                );
            } else {
                printf( " %1s%-22s %-15s %-21s %-8s\n",
                    ( $c != 1 ? ',' : ' ' ),
                    lc( $row[0] ),
                    ( $row[1] eq 'NUMBER'
                        ? $row[1] . '(' . $row[3] . ', ' . $row[4] . ')'
                        : ( $row[1] eq 'VARCHAR2'
                            ? $row[1] . '(' . $row[2] . ')'
                            : $row[1] ) ),
                    ( defined $row[5]
                        ? 'DEFAULT ' . $row[5]
                        : '' ),
                    ( $row[6] eq 'Y'
                        ? ''
                        : 'NOT NULL' )
                );
            }
        }
        push ( @column_names, $row[0] );
        push ( @column_types, $row[1] );
    }

    $sth->finish();

    if (! defined $opt_no_create_info)
    {
    print <<MARK;
) TYPE=MyISAM CHARSET=UTF8;
MARK
    }


    ## Dump data for table

    if(! defined $opt_no_data)
    {

    ## Print the per-table header

    print <<MARK;

$CM
$CM Dumping data for table: $table_out
$CM Records: $record_count
@{[ (scalar @opt_where > 0)
    ? "$CM WHERE:  " . join(' AND ', @opt_where) . "\n$CM\n"
    : "$CM\n" ]}
MARK


    ## Begin transaction processing

    if (defined $opt_add_transaction)
    {
        print <<MARK;
SET AUTOCOMMIT OFF;

MARK
    }

    ## Query for data then output SQL

    $query = sprintf( 'SELECT "%s" FROM %s',
        join ( '","', @column_names ), $table_for_select);
    $query .= ' WHERE ' . join ( ' AND ', @opt_where ) if ( scalar @opt_where > 0 );

    $sth = $dbh->prepare($query);
    $sth->execute() || die $DBI::errstr;

    if ($opt_mysql_compliant_dump) {
        foreach (@column_names) {
            $_ = '`'.$_.'`';
        }
    }

    while (@row = $sth->fetchrow_array())
    {
        print <<MARK;
INSERT INTO $table_out (@{[ join(', ', @column_names) ]})
     VALUES (@{[ join(',', map(escape_data($column_types[$_], $row[$_]), 0..$#column_names)) ]});
MARK
    }

    $sth->finish();


    ## End transaction processing

    if (defined $opt_add_transaction)
    {
        print <<MARK;

COMMIT;

MARK
    }
    }
}


#***********************************************************************
# Buh-bye!
#***********************************************************************

$dbh->disconnect();

exit 0;


#***********************************************************************
# Define local subroutines
#***********************************************************************

sub escape_data($$)
{
    my $type  = shift;
    my $value = shift;

    if ( $type eq 'NUMBER' or $type eq 'FLOAT') {
        return "''" if (length $value <= 0);
        $value =~ s/,/./g;
        return $value;
    } elsif ( $type eq 'NVARCHAR2' or $type eq 'VARCHAR2' or $type eq 'CHAR' or $type eq 'CLOB' or $type eq 'BLOB' or $type eq 'LONG' or $type =~ /RAW/ or $type eq 'PRACFOND' or $type eq 'ROWID') {
        if ( !defined $value ) {
            return "NULL";
        } else {
            $value =~ s/\\/\\\\/gm;
            $value =~ s/'/\\'/gm;
            $value =~ s/"/\\"/gm;
            $value =~ s/\x00/<--N--U--L-->/gm;
            return "'$value'";
        }
    } elsif ($type eq 'DATE' or $type eq 'TIMESTAMP' or $type eq 'TIMESTAMP(6)') {
        if ( !defined $value ) {
            return "NULL";
        } else {
            return "'$value'";
        }
    } else {
        die "escape_data(): unknown type: $type\n";
    }
}


__END__


=pod

=head1 NAME

oracledump - dumps table data from Oracle in SQL script format

=head1 SYNOPSIS

oracledump [options] <database> <table> [<table> ...]

=head1 DESCRIPTION

Dumps table data from Oracle in the format of an SQL script.  Think
I<mysqldump> for Oracle.

=over 4

=item <database>

This is the Oracle SID for the database, not the hostname.

=item <table> [<table> ...]

This is a list of one or more table names.  If no table names are
listed, a list of all tables will be generated and used.

=back

=head1 TO DO

The next major enhancement will be to dump SQL to recreate constraints,
sequences, triggers and other table metadata.

Please send comments, patches and suggestions to the email address
below.

=head1 SEE ALSO

L<mysqldump>, L<perl>

=head1 COPYRIGHT

Copyright (C) 1999 David Kilzer.

This library is free software; you can redistribute it and/or
modify it under the same terms as Perl itself.

=head1 AUTHOR

David Kilzer <ddkilzer@yahoo.com>

=cut