1 package DBUtil;
   2 #
   3 # $RCSfile: DBUtil.pm,v $
   4 # $Date: 2011/12/16 00:04:11 $
   5 # $Revision: 1.28 $
   6 #
   7 # Author: Manish Sud <msud@san.rr.com>
   8 #
   9 # Copyright (C) 2004-2012 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 Exporter;
  31 use Carp;
  32 use DBI;
  33 use TextUtil;
  34 
  35 use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
  36 
  37 @ISA = qw(Exporter);
  38 @EXPORT = qw(DBConnect DBDisconnect DBFetchSchemaTableNames DBSetupDescribeSQL DBSetupSelectSQL DBSQLToTextFile);
  39 @EXPORT_OK = qw();
  40 %EXPORT_TAGS = (all  => [@EXPORT, @EXPORT_OK]);
  41 
  42 # Connect to a specified database...
  43 sub DBConnect {
  44   my($DBDriver, $DBName, $DBHost, $DBUser, $DBPassword) = @_;
  45   my($DBHandle, $DataSource);
  46 
  47   if ($DBDriver eq "Oracle") {
  48     $DataSource = ($DBName && $DBHost) ? (qq(DBI:$DBDriver:sid=$DBName;host=$DBHost)) : (qq(DBI:$DBDriver:$DBHost));
  49   }
  50   else {
  51     $DataSource = qq(DBI:$DBDriver:database=$DBName);
  52     if ($DBHost) {
  53       $DataSource .= qq(;host=$DBHost);
  54     }
  55   }
  56 
  57   # Don't raise the error; otherwise, DBI functions termiates on encountering an error.
  58   # All terminations decisions are made outside of DBI functions...
  59   $DBHandle = DBI->connect($DataSource, $DBUser, $DBPassword, { RaiseError => 0, AutoCommit => 0 }) or croak "Couldn't connect to database...";
  60 
  61   return $DBHandle;
  62 }
  63 
  64 # Disconnect from a database...
  65 sub DBDisconnect {
  66   my($DBHandle) = @_;
  67 
  68   $DBHandle->disconnect or carp "Couldn't disconnect from a database...";
  69 }
  70 
  71 # Fetch all table name for a database schema...
  72 sub DBFetchSchemaTableNames {
  73   my($DBDriver, $DBHandle, $SchemaName) = @_;
  74   my(@SchemaTableNames, $SQL, $SQLHandle);
  75 
  76   @SchemaTableNames = ();
  77 
  78   $SchemaName = (defined $SchemaName && length $SchemaName) ? uc $SchemaName : "";
  79 
  80   if ($DBDriver eq "mysql") {
  81     # Switch schemas...
  82     $SQL = qq(USE $SchemaName);
  83     $SQLHandle = $DBHandle->prepare($SQL) or return @SchemaTableNames;
  84     $SQLHandle->execute or return @SchemaTableNames;
  85     $SQLHandle->finish or return @SchemaTableNames;
  86 
  87     # Setup to fetch table names...
  88     $SQL = qq(SHOW TABLES);
  89   }
  90   elsif ($DBDriver eq "Oracle") {
  91     $SQL = qq(SELECT SEGMENT_NAME FROM DBA_SEGMENTS WHERE OWNER = '$SchemaName' AND SEGMENT_TYPE = 'TABLE' ORDER BY SEGMENT_NAME);
  92   }
  93   $SQLHandle = $DBHandle->prepare($SQL) or return @SchemaTableNames;
  94   $SQLHandle->execute or return @SchemaTableNames;
  95 
  96   my(@RowValues, $TableName);
  97   while (@RowValues = $SQLHandle->fetchrow_array) {
  98     $TableName = uc $RowValues[0];
  99     if (defined $TableName && length $TableName) {
 100       push @SchemaTableNames, $TableName;
 101     }
 102   }
 103   $SQLHandle->finish or return @SchemaTableNames;
 104 
 105   return @SchemaTableNames;
 106 }
 107 
 108 # Setup describe SQL statement...
 109 sub DBSetupDescribeSQL {
 110   my($DBDriver, $TableName, $SchemaName);
 111   my($DescribeSQL);
 112 
 113   $DBDriver = ""; $TableName = ""; $SchemaName = "";
 114   if (@_ == 3) {
 115     ($DBDriver, $TableName, $SchemaName) = @_;
 116   }
 117   else {
 118     ($DBDriver, $TableName) = @_;
 119   }
 120   $TableName = (defined $TableName && length $TableName) ? uc $TableName : "";
 121   $SchemaName = (defined $SchemaName && length $SchemaName) ? uc $SchemaName : "";
 122 
 123   $DescribeSQL = ($SchemaName) ? ("DESCRIBE " . "$SchemaName" . ".$TableName") : "DESCRIBE $TableName";
 124 
 125   if ($DBDriver eq "Oracle") {
 126     $DescribeSQL = qq(SELECT COLUMN_NAME "Column_Name", DECODE(NULLABLE, 'N','Not Null','Y','Null') "Null", DATA_TYPE "Data_Type", DATA_LENGTH "Data_Length", DATA_PRECISION "Data_Precision" FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = '$TableName');
 127     if ($SchemaName) {
 128       $DescribeSQL .= qq( AND OWNER = '$SchemaName');
 129     }
 130     $DescribeSQL .= qq( ORDER BY COLUMN_ID);
 131   }
 132 
 133   return $DescribeSQL;
 134 }
 135 
 136 # Setup describe SQL statement...
 137 sub DBSetupSelectSQL {
 138   my($DBDriver, $TableName, $SchemaName);
 139   my($SelectSQL);
 140 
 141   $DBDriver = ""; $TableName = ""; $SchemaName = "";
 142   if (@_ == 3) {
 143     ($DBDriver, $TableName, $SchemaName) = @_;
 144   }
 145   else {
 146     ($DBDriver, $TableName) = @_;
 147   }
 148   $TableName = (defined $TableName && length $TableName) ? uc $TableName : "";
 149   $SchemaName = (defined $SchemaName && length $SchemaName) ? uc $SchemaName : "";
 150 
 151   $SelectSQL = ($SchemaName) ? ("SELECT * FROM " . "$SchemaName" . ".$TableName") : "SELECT * FROM $TableName";
 152 
 153   return $SelectSQL;
 154 }
 155 
 156 # Prepare and execute a SQL statement and write out results into
 157 # a text file.
 158 sub DBSQLToTextFile {
 159   my($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr);
 160   my($SQLHandle, $Status);
 161 
 162   $Status = 1;
 163   $ExportDataLabels = 1;
 164   $ExportLOBs = 0;
 165   $ReplaceNullStr = "";
 166   if (@_ == 8) {
 167     ($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr) = @_;
 168   }
 169   elsif (@_ == 7) {
 170     ($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs) = @_;
 171   }
 172   elsif (@_ == 6) {
 173     ($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote, $ExportDataLabels) = @_;
 174   }
 175   else {
 176     ($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote) = @_;
 177   }
 178 
 179   # Execute SQL statement...
 180   $SQLHandle = $DBHandle->prepare($SQL) or return $Status;
 181   $SQLHandle->execute() or return $Status;
 182 
 183   my($FieldsNum, @FieldNames, @RowValues, @ColNumsToExport, @ColLabels, $ColNum, $ColLabelsLine, @Values, $Value, $ValuesLine);
 184 
 185   $Status = 0;
 186   # Figure out which column numbers need to be exported...
 187   $FieldsNum = $SQLHandle->{NUM_OF_FIELDS};
 188   @FieldNames = @{$SQLHandle->{NAME}};
 189   @ColNumsToExport = ();
 190   if ($ExportLOBs) {
 191     @ColNumsToExport = (0 .. $#FieldNames);
 192   }
 193   else {
 194     my(@FieldTypes, @FieldTypeNames, $Type, $TypeName);
 195     @FieldTypes = @{$SQLHandle->{TYPE}};
 196     @FieldTypeNames = map { scalar $DBHandle->type_info($_)->{TYPE_NAME} } @FieldTypes;
 197     for $ColNum (0 .. $#FieldNames) {
 198       if ($FieldTypeNames[$ColNum] !~ /lob/i ) {
 199         push @ColNumsToExport, $ColNum;
 200       }
 201     }
 202   }
 203 
 204   if ($ExportDataLabels) {
 205     # Print out column labels...
 206     @ColLabels = ();
 207     for $ColNum (@ColNumsToExport) {
 208       push @ColLabels, $FieldNames[$ColNum];
 209     }
 210     $ColLabelsLine = JoinWords(\@ColLabels, $OutDelim, $OutQuote);
 211     print $TextFile "$ColLabelsLine\n";
 212   }
 213   # Print out row values...
 214   while (@RowValues = $SQLHandle->fetchrow_array) {
 215     @Values = ();
 216     for $ColNum (@ColNumsToExport) {
 217       if (defined($RowValues[$ColNum]) && length($RowValues[$ColNum])) {
 218         $Value = $RowValues[$ColNum];
 219       }
 220       else {
 221         $Value = $ReplaceNullStr ? $ReplaceNullStr : "";
 222       }
 223       push @Values, $Value;
 224     }
 225     $ValuesLine = JoinWords(\@Values, $OutDelim, $OutQuote);
 226     print $TextFile "$ValuesLine\n";
 227   }
 228   $SQLHandle->finish or return $Status;
 229   $Status = 0;
 230 
 231   return $Status;
 232 }
 233