#!/usr/bin/perl -w
use strict;
use vars qw ( @ARGV $dbh $db_database $db_user $db_password
	      $default_precision $default_scale $default_index_length
	      $default_tnsnames $with_table_comments $with_col_comments
	      $no_data $extended_inserts $complete_inserts $add_drop_table 
	      $add_locks $insert_delayed $lock_tables $nls_date_format
	      $nls_time_format $nls_timestamp_format $verbose );
use DBI;

#############################################
#
# oracledump.pl - Dumps table(s) from an Oracle database to MySQL format
#
# Use ./oracledump.pl without args to see usage info.
# You may also change internal default settings below
#
#
# Written by Johan Andersson <johan@andersson.net>, May 2001
# 
# 
#
#
### SETTINGS #################################
#
# All settings here are default settings that are used if they're
# not specified as argument.
#

# DATABASE SETUP ####
$db_database = $ENV{'ORACLE_SID'} if defined($ENV{'ORACLE_SID'}); # Default Oracle SID
$db_user     = getlogin(); # Default username
$db_password = ''; # Default password

# Oracle session parameters for date/time formats. Do not change if you not
# know exactly what you're doing!
$nls_date_format = 'RRRR-MM-DD';
$nls_time_format = 'HH24:MI:SSXFF';
$nls_timestamp_format = "$nls_date_format $nls_time_format";


# FILE SETTINGS ####
# Set default path to the tnsnames.ora file
$default_tnsnames = $ENV{'ORACLE_HOME'}."/network/admin/tnsnames.ora";

# DATA TYPE SETTINGS ####
$default_precision    = 18; # Default when precision is missing on NUMBER/FLOAT
$default_scale        = 0;  #   - " " -    scale     - " " -  - " " -  - " " -
$default_index_length = 10; # This only affects indexes that contain BLOB fields.
                            # Sets how big in bytes the index should be for those columns.

# MISC. SETTINGS
$no_data             = 0; # Sets whether to retrieve table data or just the table structure
$with_col_comments   = 1; # Enable comments to be included for each column (if they exist in Oracle)
$with_table_comments = 1; # Same, but for comments for each table.
$extended_inserts    = 0; # Use extended INSERT INTO syntax to insert 
                          # multiple rows within one statement (MySQL)
$complete_inserts    = 0; # Use of complete inserts adds list of table column
                          # names used, after the INTO word in INSERT INTO 
                          # statements (MySQL)
                          # Eg.:
                          # complete: INSERT INTO foo (col1, col2) VALUES (1,2);
                          # compact (default): INSERT INTO foo VALUES (1,2);
$add_drop_table      = 0; # Specifies if we are going to add DROP TABLE
                          # statements in the output
$add_locks           = 0; # Specifies whether to use locks around insert
                          # statements or not (MySQL)
$insert_delayed      = 0; # Specified wheter to use INSERT DELAYED or not (MySQL)
$lock_tables         = 0; # If true, all tables will be locked for read before
                          # fetching anything from them (Oracle)

$verbose             = 0; # If enabled, program activities are printed out on STDERR
##############################################

# Trap some useful signals to avoid seg fault, DBI handles left open etc.
$SIG{'TERM'} = *interrupt;
$SIG{'QUIT'} = *interrupt;
$SIG{'INT'} = *interrupt;

# Put auto-flush on
$| = 1;

# int main( void )
# Main sub routine
sub main {
    
    # Parse arguments and get tables..
    my @arg_tables = parseArgs();

    # Create a database handle
    $dbh = db_connect($db_database, $db_user, $db_password);
    my $sth;

    # Use the ALTER SESSION command to change the date format used
    $dbh->do("ALTER SESSION SET NLS_DATE_FORMAT = '$nls_date_format'");
    $dbh->do("ALTER SESSION SET NLS_TIME_FORMAT = '$nls_time_format'");
    $dbh->do("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = '$nls_timestamp_format'");

    # Store tables in an array
    my @tables;

    # Check if any tables are specified as argument(s)
    if($#arg_tables < 0) {
	# Fetch all tables from USER_TABLES
	$sth = $dbh->prepare("SELECT TABLE_NAME FROM USER_TABLES");
	$sth->execute();

	my @row;
	while(@row = $sth->fetchrow_array()) {
	    push @tables, $row[0];
	}
	$sth->finish();
    }
    else {
	while(<@arg_tables>) {
	    # Check if table exists
	    $sth = $dbh->prepare("SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = ?");
	    $sth->execute($_);
	    
	    if($sth->fetchrow_array()) {
		push @tables, $_;
	    }
	    else {
		warn "Table $_ does not exist\n"; 
	    }
	    $sth->finish();
	}
    }

    my $table;
    foreach $table ( @tables ) {
	print STDERR "* Checking table structure for table $table\n" if $verbose;

	my @cols = describe_table($dbh, $table);
	my %blobcols = (); # Keeps track on blob columns in indexes
	my @colslist = ();
	my $output = "";
	my @quotecol = ();
	my $lastcomment;

	$output .= "\nDROP TABLE IF EXISTS $table;" if $add_drop_table;
	$output .= "\nCREATE TABLE $table (";
	for ( my $i = 0; $i <= $#cols; $i++ ) {
	    my(undef, undef, $datatype) = convert_dt($cols[$i]{'TYPE'}, $cols[$i]{'LENGTH'});
	    $output .= "\n\t".$cols[$i]{'NAME'}."\t".$datatype;
	    $output .= " DEFAULT '".$cols[$i]{'DEFAULT'}."'" if defined($cols[$i]{'DEFAULT'});
	    $output .= " NOT NULL" if $cols[$i]{'NULL'} eq 'N';
	    $output .= "," if $i != $#cols;
	    $output .= "\t# ".$cols[$i]{'COMMENT'} if defined($cols[$i]{'COMMENT'}) && $with_col_comments && $i != $#cols;

	    # We must save the last comment if such exists, otherwise we may get
	    # an error later since a separating comma may be printed on the wrong place
	    $lastcomment = $cols[$i]{'COMMENT'} if defined($cols[$i]{'COMMENT'}) && $with_col_comments && $i == $#cols;

	    # Save the insert values string
	    if($cols[$i]{'TYPE'} =~ /^(VAR)?CHAR2?|LONG(RAW)?|RAW|DATE$/) {
		$quotecol[$i] = 1;
	    }
	    else {
		$quotecol[$i] = 0;
	    }

	    # Keep track on blob columns for indexes
	    if($datatype eq 'TEXT') {
		$blobcols{$cols[$i]{'NAME'}} = 1;
	    }

	    # Keep a list of the columns to use when fetching data later
	    push @colslist, $cols[$i]{'NAME'};
	}

	# Get keys
	print STDERR "* Fetching keys for table $table\n" if $verbose;
	my %keys = get_keys($dbh, $table);

	# Get primary key
	my @pkcols = @{ $keys{'PRIMARY_KEY'} };
	my $pkstr = "";

	if($#pkcols > 0) {
	    $output .= ",";

	    # print last column comment if such exist
	    if(defined($lastcomment)) {
		$output .= "\t# $lastcomment";
		$lastcomment = undef;
	    }

	    $output .= "\n\tPRIMARY KEY (";
	
	    for(my $i=0; $i<=$#pkcols; $i++) {
		# Keep track on columns in the PK in a string format (used later with indexes)
		$pkstr .= $pkcols[$i]."-";

		$output .= $pkcols[$i];
		# Add index size if column is a blob column. Required by MySQL
		$output .= "(".$default_index_length.")" if defined($blobcols{$pkcols[$i]});
		$output .= "," unless $i == $#pkcols;
	    }
	    $output .= ")";
	}
	# Primary key done!

	# Get indexes (non-unique and unique)
	my $type;
	my @idxcols;
	my $idxstr;
	my $tmp_cols;
	
	print STDERR "* Fetching indexes for table $table\n" if $verbose;

	for $type ( 'UNIQUE', 'INDEX' ) {
	    my %indexes = %{ $keys{$type} };
	
	    while( my($key) = each %indexes ) {
		@idxcols = @{ $indexes{$key} };

		$idxstr = "\n\t".$type." ".$key." (";
		$tmp_cols = "";

		for(my $i=0; $i<=$#idxcols; $i++) {
		    # Keep track on index columns in a string format
		    $tmp_cols .= $idxcols[$i]."-";

		    $idxstr .= $idxcols[$i];
		    $idxstr .= "(".$default_index_length.")" if defined($blobcols{$idxcols[$i]});
		    $idxstr .= "," if $i != $#idxcols;
		}
		
		$idxstr .= ")";

		# Check if this index is the PK index
		# Since the PK already is specified above we should not
		# include that index here.. But if the type not is UNIQUE
		# this index is not the PK (just a non-unique duplicate of the
		# PK index)
		# 
		# You shouldn't be able to create such a index in Oracle
		# (it would return an ORA-01408 error), but since MySQL may
		# have duplicate indexes of different uniquenesses we do it
		# the MySQL way to be sure..
		if($tmp_cols ne $pkstr || $type eq 'INDEX') {
		    $output .= ",";

		    # Append last column comment if exists
		    if(defined($lastcomment)) {
			$output .= "\t# $lastcomment";
			$lastcomment = undef;
		    }

		    $output .= $idxstr;
		}
	    }
	}

	# Append last column comment if exists
	if(defined($lastcomment)) {
	    $output .= "\t# $lastcomment";
	    $lastcomment = undef;
	}


	$output .= "\n);\n";
	print STDERR "* Printing CREATE TABLE statement for table $table\n" if $verbose;
	print $output;

	if(!$no_data) {
	    # Fetch the data
	    print STDERR "* Obtaining locks on table $table on Oracle\n" if $verbose && $lock_tables;
	    $dbh->do("LOCK TABLE $table IN SHARE MODE NOWAIT;") if $lock_tables;

	    print STDERR "* Preparing SELECT statement to fetch data from table $table\n" if $verbose;
	    $sth = $dbh->prepare("SELECT ".join(", ", @colslist)." FROM $table");
	    $sth->execute();

	    # Put together the insert statement. Take care of complete/"compact" options
	    my $insert_stmt = "INSERT ";
	    $insert_stmt .= "DELAYED " if $insert_delayed;
	    $insert_stmt .= "INTO $table ";
	    $insert_stmt .= "(".join(", ", @colslist).") " if $complete_inserts;
	    $insert_stmt .= "VALUES ";

	    my $istmt_nprinted = 1;
	    my $firstrow = 1;
	
	    print STDERR "* Printing LOCK TABLES statement for table $table\n" if $verbose && $add_locks;
	    print "LOCK TABLES $table WRITE;\n" if $add_locks;
	    
	    print STDERR "* Starting to print INSERT INTO statement(s)\n" if $verbose;
	    my $rowcount = 0;
	    while(my @row = $sth->fetchrow_array()) {
		if($extended_inserts && $istmt_nprinted || !$extended_inserts) {
		    print $insert_stmt;
		    $istmt_nprinted = 0;
		}
		elsif($extended_inserts && !$firstrow) {
		    print ",";
		}
		print "(";
		
		for(my $i=0; $i<=$#colslist; $i++) {
		    if(defined($row[$i]) && $quotecol[$i]) {
			print $dbh->quote(db_escape($row[$i]));
		    }
		    elsif(defined($row[$i]) && !$quotecol[$i]) {
			print $row[$i];
		    }
		    else {
			print "NULL";
		    }
		    print "," if $i != $#colslist;
		}
		print $extended_inserts ? ")" : ");\n";
		$firstrow = 0;
		$rowcount++ if $verbose;
	    }
	    print ";" if $extended_inserts == 1;
	    
	    print "UNLOCK TABLES;\n" if $add_locks;
	    $sth->finish();
	    print STDERR "* Done! Printed $rowcount number of rows for table $table\n" if $verbose;
	}
    }

    $dbh->disconnect();
    return 1;
}

# void interrupt ( void )
# This sub-routine is taking care of signals sent to the program
sub interrupt {
    my ($sig) = @_;
    print STDERR "Got $sig-signal; Issuing database disconnect and program exit\n";
    print STDERR "Warning: Active statements cannot be closed, exiting program now would cause a seg fault.\nWarning: Quiting \"safely\" _without_ disconnecting from the database (to avoid seg. fault)\n" if $dbh->{'ActiveKids'};

    $dbh->disconnect() if ! $dbh->{'ActiveKids'};

    exit(3);
}

# array describe_table( database_handle dbh, scalar table_name )
# Returns an array of hashes with info about the columns in the given table
sub describe_table {
    my($dbh, $table_name) = @_;

    # Get columns for the specified table
    my $sth = $dbh->prepare("SELECT a.COLUMN_ID,
                                    a.COLUMN_NAME,
                                    a.DATA_TYPE,
                                    a.DATA_LENGTH,
                                    a.DATA_PRECISION,
                                    a.DATA_SCALE,
                                    a.NULLABLE,
                                    a.DATA_DEFAULT,
                                    b.COMMENTS
                             FROM USER_TAB_COLUMNS a, USER_COL_COMMENTS b
                             WHERE a.TABLE_NAME = ? AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME");
    $sth->execute($table_name);

    my @row;
    my @columns;
    my $length;
    while(@row = $sth->fetchrow_array()) {
	if($row[2] eq "NUMBER" || $row[2] eq "FLOAT") {
	    # Set length to default precision/scale values when information
	    # is missing in the data
	    $length = (defined($row[4])) ? $row[4] : $default_precision;
	    $length .= ",";
	    $length .= (defined($row[5])) ? $row[5] : $default_scale;
	}
	else {
	    $length = $row[3];
	}

	# Trim default data (and remove quotes that oracle stores from CREATE/ALTER command)
	$row[7] =~ s/^'([^']+)'\s*$/$1/ if defined($row[7]);

        # Strip linebreaks in comments
        $row[8] =~ s/\n// if defined($row[8]);

	# Build hash of info about this column
	my %column = ( 'NAME'    => $row[1],
		       'TYPE'    => $row[2],
		       'LENGTH'  => $length,
		       'NULL'    => $row[6],
		       'DEFAULT' => $row[7],
                       'COMMENT' => $row[8] );
	# Add the info about this column to the @columns array,
	# set the index to the COLUMN_ID to get the "original" order
	$columns[--$row[0]] = { %column };
    }
    $sth->finish();

    # Return list of hashes
    return @columns;
}

# hash get_keys( database_handle dbh, scalar table_name )
# returns hash of hashes with information of the constraints in the given table
#
# hash = {
#     PRIMARY_KEY = [ COLNAME1, COLNAME2, ... ],
#     UNIQUE = {
#                KEYNAME => [ COLNAME1, COLNAME2, ... ]
#     },
#     INDEX  = {
#                KEYNAME => [ COLNAME1, COLNAME2, ... ]
#     }
# }
# Since MySQL ignores FOREIGN KEY statements they will not be included. 
sub get_keys {
    my($dbh, $table_name) = @_;

    my %keys = (
		'PRIMARY_KEY' => [],
		'UNIQUE'      => {},
		'INDEX'       => {}
		);
    my ( @row, $type, $sth );

    # Fetch primary key
    $sth = $dbh->prepare("SELECT a.constraint_name, b.constraint_type, a.column_name, a.position
                             FROM user_cons_columns a, user_constraints b
                             WHERE a.table_name=? AND a.constraint_name=b.constraint_name AND
                             b.constraint_type='P'
                             ORDER BY a.table_name, a.constraint_name, a.position");
    $sth->execute($table_name);

    while( @row = $sth->fetchrow_array()) {
	$type = undef;
	$type = 'PRIMARY_KEY' if $row[1] eq 'P';
	$type = 'UNIQUE' if $row[1] eq 'U';

	if(defined($type)) {
	    if($type eq 'PRIMARY_KEY') {
		$keys{$type}[--$row[3]] = $row[2];
	    }
	    else {
		if(!defined($keys{$type}{$row[0]})) {
		    $keys{$type}{$row[0]} = ();
		}
		$keys{$type}{$row[0]}[--$row[3]] = $row[2];
	    }
	}

    }
    $sth->finish();
    # Primary key done!

    # Fetch indexes
    $sth = $dbh->prepare("SELECT a.index_name, a.uniqueness, b.column_name, b.column_position
                             FROM user_indexes a, user_ind_columns b
                             WHERE a.table_name = ? AND b.table_name = a.table_name AND b.index_name = a.index_name
                             ORDER BY a.uniqueness, a.index_name");
    $sth->execute($table_name);

    while( @row = $sth->fetchrow_array() ) {
	# Get index type; UNIQUE or INDEX (non-unique of course)
	$type = "UNIQUE" if $row[1] eq 'UNIQUE';
	$type = "INDEX" if $row[1] eq 'NONUNIQUE';

	$keys{$type}{$row[0]}[--$row[3]] = $row[2];
    }
    $sth->finish();

    return %keys;
}

# scalar db_escape ( scalar string )
# Escapes a string to be used within a SQL statement
sub db_escape {
    my($str) = @_;

    my $newstr = "";
    for(my $i=0; $i<length($str); $i++) {
	my $c = substr($str, $i, 1);
	
	if(ord($c) == 10) {
	    $newstr .= chr(92).'n';
	}
	elsif(ord($c) == 13) {
	    $newstr .= chr(92).'r';
	}
	elsif(ord($c) == 9) {
	    $newstr .= chr(92).'t';
	}
	elsif(ord($c) == 34 || ord($c) == 92) {
	    $newstr .= chr(92).chr(ord($c));
        }
	else {
	    $newstr .= chr(ord($c));
	}
    }

    return $newstr;
}

# list convert_dt( scalar datatype, scalar datalength )
# Converts datatype to mysql format and returns it
sub convert_dt {
    my($datatype, $datalength) = @_;

    ############################################
    # Rules are..:
    # Datatype (ORA)   Length  Returns (MySQL)
    # NUMBER           Any     NUMERIC
    # DEC              Any     NUMERIC
    # DECIMAL          Any     NUMERIC
    # NUMERIC          Any     NUMERIC
    # DOUBLE PRECISION Any     NUMERIC
    # FLOAT            Any     NUMERIC
    # REAL             Any     NUMERIC
    # SMALLINT         Any     SMALLINT
    # VARCHAR          <256    VARCHAR
    # VARCHAR2         <256    VARCHAR
    # CHAR             <256    CHAR
    # VARCHAR2         >255    TEXT
    # VARCHAR          >255    TEXT
    # CHAR             >255    TEXT
    # LONG             <256    VARCHAR
    # LONGRAW          <256    VARCHAR
    # RAW              <256    VARCHAR
    # LONG             >255    TEXT
    # LONGRAW          >255    TEXT
    # RAW              >255    TEXT
    # DATE             -       DATETIME (Since DATE in oracle can include time information!)
    #
    # List return consists of:
    # 1. Datatype name
    # 2. Datalength
    # 3. Complete datatype spec. to be used within CREATE TABLE statement
    #############################################
    if($datalength eq '0') {
	$datalength = 32767;
    }

    return ('NUMERIC', $datalength, 'NUMERIC('.$datalength.')') if $datatype =~ /^DEC(IMAL)?|NUMERIC|NUMBER|DOUBLE PRECISION|FLOAT|REAL$/;
    return ('INTEGER', 38, 'INTEGER(38)') if $datatype =~ /^(SMALL)?INT(EGER)?$/;
    return ('VARCHAR', $datalength, 'VARCHAR('.$datalength.')') if $datatype =~ /^N?VARCHAR2?|LONG( RAW)?$/ && $datalength <= 255;
    return ('TEXT', '', 'TEXT') if $datatype =~ /^N?(VAR)?CHAR2?|LONG( RAW)?$/ && $datalength > 255;
    return ('CHAR', $datalength, 'CHAR('.$datalength.')') if $datatype =~ /^N?CHAR$/ && $datalength <= 255;
    return ('DATETIME', '', 'DATETIME') if $datatype eq 'DATE';
}

# db_connect( scalar database, scalar user, scalar password )
# database_handle connects to the database
sub db_connect {
    my($database, $user, $password) = @_;

    return DBI->connect( "dbi:Oracle:$database", $user, $password,
   { AutoCommit => 0, RaiseError => 0, PrintError => 0, LongReadLen => 0, LongTruncOk => 1 } )
   or die $DBI::errstr;
}

# array parseArgs( void )
# returns array of tables given as argument or invokes the printError() sub-routine
# if some error occurs, which exits the program
sub parseArgs {

    if($#ARGV < 0) {
	printUsage();
	exit;
    }

    my $tmpcmd = undef;
    my @arg_tables;
    my $got_db = 0;
    my $show_config_only = 0;
    my $use_default_db = 0;
    
    while(<@ARGV>) {
	my $arg = $_;
	if(defined($tmpcmd)) {
	  SWITCH: for ($tmpcmd) {
	      /^user$/ && do {
		  $db_user = $arg;
		  $tmpcmd = undef;
		  last SWITCH;
	      };
	      /^password$/ && do {
		  $db_password = $arg;
		  $tmpcmd = undef;
		  last SWITCH;
	      };
	      /^tnsnames$/ && do {
		  $default_tnsnames = $arg;
		  $tmpcmd = undef;
		  last SWITCH;
	      }
	  }
	}
	else {
	  SWITCH: {
	      /^-h|--help$/ && do {
		  printUsage();
		  exit 0;
	      };
	      /^-u|--user=(.+)$/ && do {
		  if(defined($1)) {
		      $db_user = $1;
		  }
		  else {
		      $tmpcmd = 'user';
		  }
		  last SWITCH;
	      };
	      /^-p|--password=(.+)$/ && do {
		  if(defined($1)) {
		      $db_password = $1;
		  }
		  else {
		      $tmpcmd = 'password';
		  }
		  last SWITCH;
	      };
	      /^-t|--tnsnames=(.+)$/ && do {
		  if(defined($1)) {
		      $default_tnsnames = $1;
		  }
		  else {
		      $tmpcmd = 'tnsnames';
		  }
		  last SWITCH;
	      };
	      /^-v|--verbose$/ && do {
		  $verbose = 1;
		  last;
	      };
	      /^-D|--default-sid$/ && do {
		  # Set got_db to use default database
		  $use_default_db = 1;
		  $got_db = 1;
		  last SWITCH;
	      };
	      /^-x|--explain$/ && do {
		  # Just print config after args are parsed
		  $show_config_only = 1;
		  last SWITCH;
	      };
	      /^--with(out)?-comments$/ && do {
		  $with_col_comments = (defined($1)) ? 0 : 1;
		  $with_table_comments = (defined($1)) ? 0 : 1;
		  last;
	      };
	      /^--with(out)?-column-comments$/ && do {
		  $with_col_comments = (defined($1)) ? 0 : 1;
		  print $with_col_comments;
		  last;
	      };
	      /^--with(out)?-table-comments$/ && do {
		  $with_table_comments = (defined($1)) ? 0 : 1;
		  last;
	      };
	      /^-e|--extended-insert$/ && do {
		  $extended_inserts = 1;
		  last;
	      };
	      /^-c|--complete-insert$/ && do {
		  $complete_inserts = 1;
		  last;
	      };
	      /^-l|--lock-tables$/ && do {
		  $lock_tables = 1;
		  last;
	      };
	      /^--no-data$/ && do {
		  $no_data = 1;
		  last;
	      };
	      /^--add-drop-table$/ && do {
		  $add_drop_table = 1;
		  last;
	      };
	      /^--add-locks$/ && do {
		  $add_locks = 1;
		  last;
	      };
	      /^--default-scale=(.+)$/ && do {
		  if(defined($1)) {
		      if($1 =~ /^\d+$/) {
			  $default_scale = $1;
		      }
		      else {
			  printError("Error: You must specify a number when using --default-scale");
		      }
		  }
		  else {
		      printError("Error: --default-scale were specified, but with no value. Please specify a integer value");
		  }
	      };
	      /^--default-precision=(.+)$/ && do {
		  if(defined($1)) {
		      if($1 =~ /^\d+$/) {
			  $default_precision = $1;
		      }
		      else {
			  printError("Error: You must specify a number when using --default-precision");
		      }
		  }
		  else {
		      printError("Error: --default-precision were specified, but with no value. Please specify a integer value");
		  }
	      };

	  
	      if($got_db) {
		  push @arg_tables, $_;
	      }
	      else {
		  $db_database = $_;
		  $got_db = 1;
	      }
	  }
	}
    }

    if(defined($tmpcmd)) {
	printError("You have some incomplete option specified in your command line.");
    }
    elsif($db_user eq '' || checkSID($db_database)) {
	printError("You must specify a valid login and database name!");
    }
    elsif($show_config_only) {
	my $password = ($db_password eq '') ? "<empty>" : $db_password;
	my $tables = ($#arg_tables < 0) ? "All tables" : join(", ", @arg_tables);
	print <<EOF;
Configuration: (remove --explain/-x option to run with this configuration)

  Database SID: $db_database
  Database user: $db_user
  Database password: $password
	
  Tables: $tables
  
  Options:
EOF
        print "    --default-database        Use default database ($db_database)\n" if $use_default_db;
	print "    --with-table-comments     Include table comments\n" if $with_table_comments;
	print "    --without-table-comments  Discard table comments\n" if ! $with_table_comments;
	print "    --with-column-comments    Include column comments\n" if $with_col_comments;
	print "    --without-column-comments Discard column comments\n" if ! $with_col_comments;
print "    --default-precision       Set to $default_precision\n";
	print "    --default-scale           Set to $default_scale\n";
	print "    --extended-insert         Use the faster extended insert statement\n" if $extended_inserts;
	print "    --complete-insert         Includes list of column names in insert statements" if $complete_inserts;
	print "    --add-locks               Add locks around tables in output\n" if $add_locks;
	print "    --add-drop-table          Adds DROP TABLE just before CREATE TABLE statements in output\n" if $add_drop_table;
	print "    --lock-tables             Lock all tables for read in Oracle before doing any SELECTs\n" if $lock_tables;
	print "    --verbose                 Be verbose / enable logging to STDERR or logfile (combined with --log-file)\n" if $verbose;
	print "    --no-data                 Do not include any INSERT INTO statements\n" if $no_data;
	print "\n";

	exit;
    }
    else {
	return @arg_tables;
    }
}

# void checkSID( void )
# Checks SID (database instance) if its valid or not
# Exits program through printError() on error
sub checkSID {
    my($SID) = @_;
    $SID = uc($SID);

    if( -e $default_tnsnames ) {
	if( -r $default_tnsnames ) {
	    # Grep for the SID in the tnsnames.ora file, throw STDERR away and get first part of the line (The SID)
	    my $tmp = `grep "$SID =" $default_tnsnames 2>/dev/null | cut -d " " -f1`;
	    $tmp =~ s/\n//;

	    # Compare SID in file with the SID found in tnsnames file
	    if($tmp ne $SID) {
		printError("Error: Could not find the specified SID in your tnsnames.ora file. Please check your settings.\n\nSID: $SID\nPath to tnsnames.ora: $default_tnsnames\nNote that default database is got from \$ORACLE_SID from your environment!");
	    }
	}
	else {
	    printError("Error: Could not validate your Oracle SID\n(tnsnames.ora file could not be read from the given path $default_tnsnames )");
	}
    }
    else {
	printError("Error: Could not validate your Oracle SID\n(tnsnames.ora file could not be found on the given path $default_tnsnames )");
    }
}

# void setDefaultScale ( scalar number )
# Sets default scale value or exits with errormessage if scalar isn't a number
sub setDefaultScale {
    my($number) = @_;
    
    if($number =~ /^\d+$/) {
	$default_scale = $number;
    }
    else {
	printError("Error: You must specify a number when using -ds or --default-scale option!");
    }
}

# void setDefaultPrecision ( scalar number )
# Sets default precision value or exits with errormessage if scalar isn't a number
sub setDefaultPrecision {
    my($number) = @_;
    
    if($number =~ /^\d+$/) {
	$default_precision = $number;
    }
    else {
	printError("Error: You must specify a number when using -dp or --default-precision option!");
    }
}

# void printError()
# prints an error
sub printError {
    my($message) = @_;

    print STDERR "$message\n" if defined($message);
    print STDERR "\n(Give -h as option if you want to see the usage info)\n";

    exit(2);
}

sub printUsage {
    print <<EOF;
Usage: $0 [OPTIONS] database [table1] [tableN...]
OR     $0 [OPTIONS] -D [table1] [tableN...]

If no tables are specified, all tables in the given database will be used.
The database name must be a valid SID specified in the tnsnames.ora file.

  -c, --complete-insert Will make INSERT statements be complete with the list
                        of column names
  -D, --default-sid     Uses SID FROM \$ORACLE_SID if present
  -e, --extended-insert The faster extended INSERT statement will be used

  -h, --help            Print this message
  -d, --no-data         Will not include row information
  -l, --lock-tables     Lock all tables for read
  -p, --password        The password. If not given in command line, an empty
                        password will be used
  -t, --tnsnames        Specifies a path to a tnsnames.ora file not in the 
                        \$ORACLE_BASE directory tree
  -u, --user            If left out, current username will be used
  -v, --verbose         Tells you what's going on printed on STDERR

  --add-drop-table      Adds drop table statement before each table
  --add-locks           Add locks around insert statements
  --delayed-insert      Delay inserts with INSERT DELAYED

  --with-comments
  --without-comments
  --with-table-comments
  --without-table-comments
  --with-column-comments
  --without-column-comments
                        Enables or disables comments on tables/columns
                        Default is --with-comments
  -x, --explain         Does not execute anything, just prints info about the 
                        configuration given by command line

EOF
}

# Run main subr. and return 0 if main succeeds.
exit((&main) ? 0 : 1);