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