1 #!/usr/bin/perl -w
   2 #
   3 # File: DBSchemaTablesToTextFiles.pl
   4 # Author: Manish Sud <msud@san.rr.com>
   5 #
   6 # Copyright (C) 2024 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