       1 package DBUtil;
       2 #
       3 # File: DBUtil.pm
       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 Exporter;
      28 use Carp;
      29 use DBI;
      30 use TextUtil;
      31
      32 use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
      33
      34 @ISA = qw(Exporter);
      35 @EXPORT = qw(DBConnect DBDisconnect DBFetchSchemaTableNames DBSetupDescribeSQL DBSetupSelectSQL DBSQLToTextFile);
      36 @EXPORT_OK = qw();
      37 %EXPORT_TAGS = (all  => [@EXPORT, @EXPORT_OK]);
      38
      39 # Connect to a specified database...
      40 sub DBConnect {
      41   my($DBDriver, $DBName, $DBHost, $DBUser, $DBPassword) = @_;
      42   my($DBHandle, $DataSource);
      43
      44   if ($DBDriver eq "Oracle") {
      45     $DataSource = qq(DBI:$DBDriver:$DBHost);
      46   }
      47   else {
      48     $DataSource = qq(DBI:$DBDriver:database=$DBName);
      49     if ($DBHost) {
      50       $DataSource .= qq(;host=$DBHost);
      51     }
      52   }
      53
      54   # Don't raise the error; otherwise, DBI functions termiates on encountering an error.
      55   # All terminations decisions are made outside of DBI functions...
      56   $DBHandle = DBI->connect($DataSource, $DBUser, $DBPassword, { RaiseError => 0, AutoCommit => 0 }) or croak "Couldn't connect to database...";
      57
      58   return $DBHandle;
      59 }
      60
      61 # Disconnect from a database...
      62 sub DBDisconnect {
      63   my($DBHandle) = @_;
      64
      65   $DBHandle->disconnect or carp "Couldn't disconnect from a database...";
      66 }
      67
      68 # Fetch all table name for a database schema...
      69 sub DBFetchSchemaTableNames {
      70   my($DBDriver, $DBHandle, $SchemaName) = @_;
      71   my(@SchemaTableNames, $SQL, $SQLHandle);
      72
      73   @SchemaTableNames = ();
      74
      75   $SchemaName = (defined $SchemaName && length $SchemaName) ? $SchemaName : "";
      76
      77   if ($DBDriver eq "mysql") {
      78     # Switch schemas...
      79     $SQL = qq(USE $SchemaName);
      80     $SQLHandle = $DBHandle->prepare($SQL) or return @SchemaTableNames;
      81     $SQLHandle->execute or return @SchemaTableNames;
      82     $SQLHandle->finish or return @SchemaTableNames;
      83
      84     # Setup to fetch table names...
      85     $SQL = qq(SHOW TABLES);
      86   }
      87   elsif ($DBDriver eq "Oracle") {
      88     $SQL = qq(SELECT SEGMENT_NAME FROM DBA_SEGMENTS WHERE OWNER = '$SchemaName' AND SEGMENT_TYPE = 'TABLE' ORDER BY SEGMENT_NAME);
      89   }
      90   elsif ($DBDriver =~ /^(Pg|Postgres)$/i) {
      91     $SQL = qq(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$SchemaName');
      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 = ($DBDriver =~ /^(mysql|Oracle)$/i) ? uc($RowValues[0]) : $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) ? $TableName : "";
     121   $SchemaName = (defined $SchemaName && length $SchemaName) ? $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   elsif ($DBDriver =~ /^(Pg|Postgres)$/i) {
     133     $DescribeSQL = qq(SELECT COLUMN_NAME "Column_Name", data_type "Data_Type" FROM information_schema.columns WHERE table_name ='$TableName');
     134     if ($SchemaName) {
     135       $DescribeSQL .= " and table_schema = '$SchemaName'";
     136     }
     137   }
     138
     139   return $DescribeSQL;
     140 }
     141
     142 # Setup describe SQL statement...
     143 sub DBSetupSelectSQL {
     144   my($DBDriver, $TableName, $SchemaName);
     145   my($SelectSQL);
     146
     147   $DBDriver = ""; $TableName = ""; $SchemaName = "";
     148   if (@_ == 3) {
     149     ($DBDriver, $TableName, $SchemaName) = @_;
     150   }
     151   else {
     152     ($DBDriver, $TableName) = @_;
     153   }
     154   $TableName = (defined $TableName && length $TableName) ? $TableName : "";
     155   $SchemaName = (defined $SchemaName && length $SchemaName) ? $SchemaName : "";
     156
     157   $SelectSQL = ($SchemaName) ? ("SELECT * FROM " . "$SchemaName" . ".$TableName") : "SELECT * FROM $TableName";
     158
     159   return $SelectSQL;
     160 }
     161
     162 # Prepare and execute a SQL statement and write out results into
     163 # a text file.
     164 sub DBSQLToTextFile {
     165   my($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr);
     166   my($SQLHandle, $Status);
     167
     168   $Status = 1;
     169   $ExportDataLabels = 1;
     170   $ExportLOBs = 0;
     171   $ReplaceNullStr = "";
     172   if (@_ == 8) {
     173     ($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr) = @_;
     174   }
     175   elsif (@_ == 7) {
     176     ($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs) = @_;
     177   }
     178   elsif (@_ == 6) {
     179     ($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote, $ExportDataLabels) = @_;
     180   }
     181   else {
     182     ($DBHandle, $SQL, $TextFile, $OutDelim, $OutQuote) = @_;
     183   }
     184
     185   # Execute SQL statement...
     186   $SQLHandle = $DBHandle->prepare($SQL) or return $Status;
     187   $SQLHandle->execute() or return $Status;
     188
     189   my($FieldsNum, @FieldNames, @RowValues, @ColNumsToExport, @ColLabels, $ColNum, $ColLabelsLine, @Values, $Value, $ValuesLine);
     190
     191   $Status = 0;
     192   # Figure out which column numbers need to be exported...
     193   $FieldsNum = $SQLHandle->{NUM_OF_FIELDS};
     194   @FieldNames = @{$SQLHandle->{NAME}};
     195   @ColNumsToExport = ();
     196   if ($ExportLOBs) {
     197     @ColNumsToExport = (0 .. $#FieldNames);
     198   }
     199   else {
     200     my(@FieldTypes, @FieldTypeNames, $Type, $TypeName);
     201     @FieldTypes = @{$SQLHandle->{TYPE}};
     202     @FieldTypeNames = map { scalar $DBHandle->type_info($_)->{TYPE_NAME} } @FieldTypes;
     203     for $ColNum (0 .. $#FieldNames) {
     204       if ($FieldTypeNames[$ColNum] !~ /lob|bytea/i ) {
     205         push @ColNumsToExport, $ColNum;
     206       }
     207     }
     208   }
     209
     210   if ($ExportDataLabels) {
     211     # Print out column labels...
     212     @ColLabels = ();
     213     for $ColNum (@ColNumsToExport) {
     214       push @ColLabels, $FieldNames[$ColNum];
     215     }
     216     $ColLabelsLine = JoinWords(\@ColLabels, $OutDelim, $OutQuote);
     217     print $TextFile "$ColLabelsLine\n";
     218   }
     219   # Print out row values...
     220   while (@RowValues = $SQLHandle->fetchrow_array) {
     221     @Values = ();
     222     for $ColNum (@ColNumsToExport) {
     223       if (defined($RowValues[$ColNum]) && length($RowValues[$ColNum])) {
     224         $Value = $RowValues[$ColNum];
     225       }
     226       else {
     227         $Value = $ReplaceNullStr ? $ReplaceNullStr : "";
     228       }
     229       push @Values, $Value;
     230     }
     231     $ValuesLine = JoinWords(\@Values, $OutDelim, $OutQuote);
     232     print $TextFile "$ValuesLine\n";
     233   }
     234   $SQLHandle->finish or return $Status;
     235   $Status = 0;
     236
     237   return $Status;
     238 }
     239