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