1 #!/usr/bin/perl -w
   2 #
   3 # $RCSfile: DBSchemaTablesToTextFiles.pl,v $
   4 # $Date: 2008/01/30 21:44:44 $
   5 # $Revision: 1.18 $
   6 #
   7 # Author: Manish Sud <msud@san.rr.com>
   8 #
   9 # Copyright (C) 2004-2008 Manish Sud. All rights reserved.
  10 #
  11 # This file is part of MayaChemTools.
  12 #
  13 # MayaChemTools is free software; you can redistribute it and/or modify it under
  14 # the terms of the GNU Lesser General Public License as published by the Free
  15 # Software Foundation; either version 3 of the License, or (at your option) any
  16 # later version.
  17 #
  18 # MayaChemTools is distributed in the hope that it will be useful, but without
  19 # any warranty; without even the implied warranty of merchantability of fitness
  20 # for a particular purpose.  See the GNU Lesser General Public License for more
  21 # details.
  22 #
  23 # You should have received a copy of the GNU Lesser General Public License
  24 # along with MayaChemTools; if not, see <http://www.gnu.org/licenses/> or
  25 # write to the Free Software Foundation Inc., 59 Temple Place, Suite 330,
  26 # Boston, MA, 02111-1307, USA.
  27 #
  28 
  29 use 5.006;
  30 use strict;
  31 use FindBin; use lib "$FindBin::Bin/../lib";
  32 use Getopt::Long;
  33 use File::Basename;
  34 use Text::ParseWords;
  35 use Benchmark;
  36 use FileUtil;
  37 use TextUtil;
  38 use DBUtil;
  39 
  40 my($ScriptName, %Options, $StartTime, $EndTime, $TotalTime);
  41 
  42 # Autoflush STDOUT
  43 $| = 1;
  44 
  45 # Starting message...
  46 $ScriptName = basename($0);
  47 print "\n$ScriptName: Starting...\n\n";
  48 $StartTime = new Benchmark;
  49 
  50 # Get the options and setup script...
  51 SetupScriptUsage();
  52 if ($Options{help} || @ARGV < 1) {
  53   die GetUsageFromPod("$FindBin::Bin/$ScriptName");
  54 }
  55 
  56 my($DBDriver, $DBHost, $DBName, $DBUser, $DBPassword, $DBMode, $ExportDataLabels, $ExportLOBs, $OutDelim, $OutQuote, $ReplaceNullStr);
  57 ProcessOptions();
  58 
  59 # Connect to database...
  60 my($DBHandle);
  61 print "Connecting to $DBDriver:database=$DBName as $DBUser...\n";
  62 $DBHandle = DBConnect($DBDriver, $DBName, $DBHost, $DBUser, $DBPassword);
  63 
  64 # Collect input parameters information...
  65 print "Checking input parameter(s)...\n";
  66 my(@DBSchemaNames, @DBTableNames, @DBSQLStatements, @DBTextFiles, $SingleTextFileName);
  67 RetrieveDBInfo();
  68 
  69 if ($Options{numoutfiles} =~ /^single$/i ) {
  70   GenerateSingleTextFile();
  71 }
  72 else {
  73   GenerateMultipleTextFiles();
  74 }
  75 print "\nDisconnecting from  $DBDriver:database=$DBName...\n";
  76 DBDisconnect($DBHandle);
  77 
  78 print "$ScriptName:Done...\n\n";
  79 
  80 $EndTime = new Benchmark;
  81 $TotalTime = timediff ($EndTime, $StartTime);
  82 print "Total time: ", timestr($TotalTime), "\n";
  83 
  84 ###############################################################################
  85 
  86 # Generate multiple text files...
  87 sub GenerateMultipleTextFiles {
  88   my($Index, $TextFile, $SQL);
  89   if (@DBTextFiles > 1) {
  90     print "Generating text files...\n";
  91   }
  92  TEXTFILE: for $Index (0 .. $#DBTextFiles) {
  93     $TextFile = $DBTextFiles[$Index];
  94     $SQL = $DBSQLStatements[$Index];
  95 
  96     if (@DBTextFiles > 1) {
  97       print "\nGenerating text file $TextFile...\n";
  98     }
  99     else {
 100       print "Generating text file $TextFile...\n";
 101     }
 102 
 103     if (!open TEXTFILE, ">$TextFile") {
 104       warn "Warning: Abandoning $TextFile generation: Couldn't open it: $! \n";
 105       next TEXTFILE;
 106     }
 107     print "Processing SQL statement \"$SQL\"...\n";
 108 
 109     if (DBSQLToTextFile($DBHandle, $SQL, \*TEXTFILE, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr)) {
 110       warn "Warning: Abandoning $TextFile generation...\n";
 111       next TEXTFILE;
 112     }
 113     close TEXTFILE;
 114   }
 115 }
 116 
 117 # Generate single text file...
 118 sub GenerateSingleTextFile {
 119   my($Index, $TextFile, $SQL, $SchemaName, $TableName);
 120 
 121   open TEXTFILE, ">$SingleTextFileName" or die "Couldn't open $SingleTextFileName: $! \n";
 122   print "Generating text file $SingleTextFileName...\n";
 123 
 124   SQL: for $Index (0 .. $#DBSQLStatements) {
 125     $SchemaName = $DBSchemaNames[$Index];
 126     $TableName = $DBTableNames[$Index];
 127     $SQL = $DBSQLStatements[$Index];
 128 
 129     $TableName = qq($SchemaName.$TableName);
 130     $TableName = QuoteAWord($TableName, $OutQuote);
 131     print TEXTFILE "\n\n$TableName\n";
 132 
 133     if (DBSQLToTextFile($DBHandle, $SQL, \*TEXTFILE, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr)) {
 134       warn "Warning: Abandoning table $TableName ...\n";
 135       next SQL;
 136     }
 137   }
 138   close TEXTFILE;
 139 }
 140 
 141 # Collect input parameters information...
 142 sub RetrieveDBInfo {
 143   my($FileExt, $UserFileName, $FileDBPrefix);
 144 
 145   # Setup out file ext...
 146   $FileExt = ($Options{outdelim} =~ /^tab$/i) ? "tsv" : "csv";
 147   $FileDBPrefix = ($DBMode =~ /^exportdata$/i) ? "Export" : "Describe";
 148 
 149   # Get user specified information...
 150   $UserFileName = "";
 151   if ($Options{root} && (@ARGV == 1)) {
 152     my($RootFileDir, $RootFileName, $RootFileExt) = ParseFileName($Options{root});
 153     if ($RootFileName && $RootFileExt) {
 154       $UserFileName = $RootFileName;
 155     }
 156     else {
 157       $UserFileName = $Options{root};
 158     }
 159   }
 160   $SingleTextFileName = "";
 161   if ($Options{numoutfiles} =~ /^single$/i) {
 162     $SingleTextFileName = $UserFileName ? $UserFileName : ("$FileDBPrefix" . "SchemaTables");
 163     $SingleTextFileName .= ".$FileExt";
 164   }
 165 
 166   # For each input schema name: collect all the table names, set up appropriate
 167   # SQL statements, and output file names...
 168   #
 169   my($SchemaName, $SQL, $FileName, @SchemaTableNames, $TableName);
 170   @DBSchemaNames = ();
 171   @DBTableNames = ();
 172   @DBSQLStatements = ();
 173   @DBTextFiles = ();
 174   @SchemaTableNames = ();
 175   SCHEMANAME: for $SchemaName (@ARGV) {
 176     $SchemaName = uc $SchemaName;
 177     if (!(@SchemaTableNames = DBFetchSchemaTableNames($DBDriver, $DBHandle, $SchemaName))) {
 178       warn "Warning: Ignoring schema $SchemaName...\n";
 179       next SCHEMANAME;
 180     }
 181     # Prepare SQL statement for each table.
 182     for $TableName (@SchemaTableNames) {
 183       push @DBSchemaNames, $SchemaName;
 184       push @DBTableNames, $TableName;
 185       $SQL = ($DBMode =~ /^exportdata$/i) ? DBSetupSelectSQL($DBDriver, $TableName, $SchemaName) : DBSetupDescribeSQL($DBDriver, $TableName, $SchemaName);
 186       push @DBSQLStatements, $SQL;
 187       if ($Options{numoutfiles} =~ /^multiple$/i) {
 188 	$FileName = $UserFileName ? ("$UserFileName" . "$TableName") : ("$FileDBPrefix" . "$SchemaName" . "$TableName");
 189 	$FileName .= ".$FileExt";
 190 	if (!$Options{overwrite}) {
 191 	  if (-e $FileName) {
 192 	    die "Error: The file $FileName already exists.\n";
 193 	  }
 194 	}
 195 	push @DBTextFiles, $FileName;
 196       }
 197     }
 198   }
 199 }
 200 
 201 # Process option values...
 202 sub ProcessOptions {
 203 
 204   $DBDriver = $Options{dbdriver} ? $Options{dbdriver} : (exists $ENV{DBI_DRIVER} ? $ENV{DBI_DRIVER} : "") ;
 205   if ($DBDriver) {
 206     if ($DBDriver =~ /^oracle$/i) {
 207       $DBDriver = "Oracle";
 208     }
 209     elsif ($DBDriver =~ /^mysql$/i) {
 210       $DBDriver = "mysql";
 211     }
 212     else {
 213       if ($Options{dbdriver}) {
 214 	die "Error: The value specified, $DBDriver, for option \"-d --dbdriver\" is not valid. Allowed values: MySQL or Oracle\n";
 215       }
 216       else {
 217 	die "Error: The value specified, $DBDriver, using environment variable DBI_DRIVER not valid. Allowed values: MySQL or Oracle\n";
 218       }
 219     }
 220   }
 221   else {
 222     $DBDriver = "mysql";
 223   }
 224   $DBHost = $Options{dbhost} ? $Options{dbhost} : "127.0.0.1";
 225   $DBName = $Options{dbname} ? $Options{dbname} : "";
 226   if (!$DBName) {
 227     if ($DBDriver =~ /^mysql$/i) {
 228       $DBName = "mysql";
 229     }
 230     elsif ($DBDriver =~ /^Oracle$/i) {
 231       $DBName = exists $ENV{ORACLE_SID} ? $ENV{ORACLE_SID} : "";
 232     }
 233   }
 234   $DBUser = $Options{dbusername} ? $Options{dbusername} : (exists $ENV{DBI_USER} ? $ENV{DBI_USER} : "") ;
 235   if (!$DBUser) {
 236     die "Error: No database username specified. Use \"--dbusername\" option or environment variable DBI_USER to enter a valid value.\n";
 237   }
 238   $DBPassword = $Options{dbpassword} ? $Options{dbpassword} : (exists $ENV{DBI_PASS} ? $ENV{DBI_PASS} : "") ;
 239   if (!$DBPassword) {
 240     die "Error: No database password specified. Use \"--dbpassword\" option or environment variable DBI_PASS to enter a valid value.\n";
 241   }
 242   $DBMode = $Options{mode};
 243   $ExportLOBs = ($Options{exportlobs} =~ /^yes$/i) ? 1 : 0;
 244   $ExportDataLabels = ($DBMode =~ /^describetable$/i) ? 1 : (($Options{exportdatalabels} =~ /^yes$/i) ? 1 : 0);
 245 
 246   $OutDelim = ($Options{outdelim} =~ /^tab$/i ) ? "\t" : (($Options{outdelim} =~ /^semicolon$/i) ? "\;" : "\,");
 247   $OutQuote = ($Options{quote} =~ /^yes$/i) ? 1 : 0;
 248 
 249   $ReplaceNullStr = (defined($Options{replacenullstr}) && length($Options{replacenullstr})) ? $Options{replacenullstr} : "";
 250 }
 251 
 252 # Setup script usage  and retrieve command line arguments specified using various options...
 253 sub SetupScriptUsage {
 254 
 255   # Retrieve all the options...
 256   %Options = ();
 257   $Options{mode} = "exportdata";
 258   $Options{exportlobs} = "no";
 259   $Options{exportdatalabels} = "yes";
 260   $Options{numoutfiles} = "single";
 261   $Options{outdelim} = "comma";
 262   $Options{quote} = "yes";
 263 
 264   if (!GetOptions(\%Options, "dbdriver|d=s", "dbhost=s", "dbname=s", "dbpassword=s", "dbusername=s", "exportdatalabels=s", "exportlobs=s", "help|h", "mode|m=s", "numoutfiles|n=s", "outdelim=s", "overwrite|o", "quote|q=s", "root|r=s", "replacenullstr=s", "workingdir|w=s")) {
 265     die "\nTo get a list of valid options and their values, use \"$ScriptName -h\" or\n\"perl -S $ScriptName -h\" command and try again...\n";
 266   }
 267   if ($Options{workingdir}) {
 268     if (! -d $Options{workingdir}) {
 269       die "Error: The value specified, $Options{workingdir}, for option \"-w --workingdir\" is not a directory name.\n";
 270     }
 271     chdir $Options{workingdir} or die "Error: Couldn't chdir $Options{workingdir}: $! \n";
 272   }
 273   if ($Options{exportdatalabels} !~ /(^(yes|no)$)/i) {
 274     die "Error: The value specified, $Options{exportlobs}, for option \"--exportdatalabels\" is not valid. Allowed values: yes or no\n";
 275   }
 276   if ($Options{exportlobs} !~ /(^(yes|no)$)/i) {
 277     die "Error: The value specified, $Options{exportlobs}, for option \"--exportlobs\" is not valid. Allowed values: yes or no\n";
 278   }
 279   if ($Options{numoutfiles} !~ /(^(single|multiple)$)/i) {
 280     die "Error: The value specified, $Options{mode}, for option \"-n --numoutfiles\" is not valid. Allowed values: single or multiple\n";
 281   }
 282   if ($Options{mode} !~ /(^(exportdata|describetable)$)/i) {
 283     die "Error: The value specified, $Options{mode}, for option \"-m --mode\" is not valid. Allowed values: exportdata or describetable\n";
 284   }
 285   if ($Options{outdelim} !~ /^(comma|semicolon|tab)$/i) {
 286     die "Error: The value specified, $Options{outdelim}, for option \"--outdelim\" is not valid. Allowed values: comma, tab, or semicolon\n";
 287   }
 288   if ($Options{quote} !~ /^(yes|no)$/i) {
 289     die "Error: The value specified, $Options{quote}, for option \"-q --quote\" is not valid. Allowed values: yes or no\n";
 290   }
 291 }
 292