#!/usr/bin/env perl
#
# dumpDBIToCDF.pl: Dump a DBI (e.g. MySQL) datasource to CDF format.
# 
# Author: Joshua Shaffer <jbs36@cs.drexel.edu>
#
# Date: Wed Oct 31 16:05:58 EDT 2007
#
# Version: 1.0
#
# Note: Only tested with DBD::mysql, and only with numeric data.
#
# Behavior: This program connects to a database server, enumerates all the attributes for a given 
#               relation, creates the CDF file structure, and writes all values from the relation 
#               to the CDF file.
# 
# 
# Usage: dumpDBIToCDF.pl outputFileName DBDriver hostAddress DatabaseName UserName Password Table
#               
#               outputFileName: Output File without ".cdf" suffix
#               DBDriver: The driver Perl's DBI module uses (e.g., "mysql")
#               hostAddress:  Database Server
#               DatabaseName: Database Name
#               UserName: User Name
#               Password: Password
#               Table: Table Name
#
# Example Usage:  ./dumpDBIToCDF.pl profileOutput mysql circket.cs.drexel.edu profileData jbs36 passwort Stats
# 
# Licence: BSD License 


use strict;
use warnings;

use DBI;
use CDF;

my $N_DIMS = 1;
my $encoding = NETWORK_ENCODING;
my $actual_encoding = NETWORK_ENCODING;
my $majority = COLUMN_MAJOR;
my $numDims = $N_DIMS;

# Usage...
if(scalar(@ARGV) < 7) {
        die("Usage: Output.cdf dbiDriver hostAddress databaseName userName password table");
}
my $tableName = $ARGV[6];

# Set to signal handlers ...
$SIG{'INT'} = 'RESOURCECLEANUP';
$SIG{'KILL'} = 'RESOURCECLEANUP';
$SIG{'HUP'} = 'RESOURCECLEANUP';


# Connect to process design database ...
my $databaseHandel = DBI->connect(sprintf("DBI:%s:database=%s;host=%s",$ARGV[1],$ARGV[3],$ARGV[2]),
        $ARGV[4], $ARGV[5],
        {'RaiseError' => 1}) or die("Unable to connect to database");


# Find number of records ...
print  "Getting Record Count...\n";
my $searchHandel = $databaseHandel->prepare("select count(*) as cnt from $tableName")
        or die("Query Prepare Failed");
$searchHandel->execute() or die("Query Execute Failed");
my $DIM_0_SIZE = -1;
while((my $resultHandel = $searchHandel->fetchrow_hashref())) {
        $DIM_0_SIZE = $$resultHandel{'cnt'};
}
die("Invalid Row Count") if($DIM_0_SIZE < 0);
my @dimSizes = ( $DIM_0_SIZE, 2);



# Get Column Names and Types ...
print  "Getting Variable Names...\n";
$searchHandel = $databaseHandel->prepare("SELECT * FROM $tableName LIMIT 1")
        or die("Query Prepare Failed");
$searchHandel->execute() or die("Query Execute Failed");
my $searchAttributeNames =  $$searchHandel{'NAME'};
my $searchAttributeNamesCount =  $$searchHandel{'NUM_OF_FIELDS'};
my @variableData;
for(my $indexAttribute = 0; $indexAttribute < $searchAttributeNamesCount; ++$indexAttribute) {
        #print $$searchAttributeNames[$indexAttribute] . " ";
        push @variableData, {
                name => $$searchAttributeNames[$indexAttribute], 
                numElements => 1,
                dataType => CDF_REAL8,
                recVariances => VARY,
                dimVariances => [VARY, VARY],
                dimention => [1],
                numOut => undef
        };
}


# Open Output File...
my $id; my $status;
$status = CDF::CDFlib(CREATE_, CDF_, $ARGV[0],$numDims,\@dimSizes,\$id,
        PUT_, CDF_ENCODING_, $encoding,
        CDF_MAJORITY_, $majority,
        NULL_);
die("Unable to Open Output CDF File. Does the file already Exist?") if($status < CDF_OK);

print  "Creating CDF Variables...\n";
foreach my $variableIndex (0..(scalar(@variableData)-1)) {
        $status = CDF::CDFvarCreate($id, 
                                $variableData[$variableIndex]{'name'}, 
                                $variableData[$variableIndex]{'dataType'}, 
                                $variableData[$variableIndex]{'numElements'},
                                $variableData[$variableIndex]{'recVariances'},
                                \@{$variableData[$variableIndex]{'dimVariances'}},
                                \$variableData[$variableIndex]{'numOut'});
        die("Unable to Create Variable (1)") if($status < CDF_OK);
}




# Retrieve all records ...
$searchHandel = $databaseHandel->prepare("lock table $tableName write")
        or die("Query Prepare Failed");
$searchHandel->execute() or die("Query Execute Failed");
print  "Issuing Query...\n";
$searchHandel = $databaseHandel->prepare("select * from $tableName LIMIT $DIM_0_SIZE")
        or die("Query Prepare Failed");
$searchHandel->execute() or die("Query Execute Failed");
my ($x0, $x1, $i, @indices, $temp1, $varNumber, $percentDone, $lastPercentDone);
print "Start dump...\n";
$x0 = 0;
$lastPercentDone = 0.0;
while((my $resultHandel = $searchHandel->fetchrow_hashref())) {
        $percentDone = ($x0/($DIM_0_SIZE*1.0))*100.0;
        if($percentDone - $lastPercentDone > .1) {
                print "Percent Done: $percentDone\n";
                $lastPercentDone = $percentDone;
        }
        $indices[0] = $x0;
        $indices[1] = 0;

        # Foreach Attribute...
        for(my $indexAttribute = 0; $indexAttribute < $searchAttributeNamesCount; ++$indexAttribute) {
                #print $$searchAttributeNames[$indexAttribute] . " ";
                $varNumber = CDF::CDFvarNum($id, $variableData[$indexAttribute]{'name'});
                die("Unable to Get Variable($indexAttribute): " . $variableData[$indexAttribute]{'name'} ) if($status < CDF_OK);
                

                # Get Data...
                $temp1 = $$resultHandel{$variableData[$indexAttribute]{'name'}};

                # Write to CDF ...
                $status = CDF::CDFvarPut($id, $varNumber, 0, \@indices, \$temp1);
                die("Unable to Get Variable($indexAttribute): " . $variableData[$indexAttribute]{'name'} ) if($status < CDF_OK);
        }

        $x0++;
}
print "End dump.\n";
$searchHandel = $databaseHandel->prepare("unlock tables")
        or die("Query Prepare Failed");
$searchHandel->execute() or die("Query Execute Failed");


print "Close CDF.\n";
$status = CDF::CDFlib (CLOSE_, CDF_, NULL_);
die("Unable to Close Output CDF File") if($status < CDF_OK);


# Release Database Connections.
sub RESOURCECLEANUP {
        $databaseHandel->disconnect();
        print "Aborted.\n";
        exit(1);
}