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