#!/usr/local/bin/perl
use strict;
use warnings;
use DBI;
use Graph;
use Graph::Undirected;
use Graph::Directed;

my $data_source = q/dbi:ODBC:driver={SQL Server};server=MYCOOLSQLSERVER2008R2;database=MYDEVDATABASE;Regional=No/;
my $user = q/myuser/;
my $password = q/mypassword/;
my $dbh;
my $sql_string;
 
eval {
   unless ($dbh = DBI->connect($data_source, $user, $password))
     {
       if ($@ ne "")	# There was an error	
         {
           print "\nError Description:$@";
           exit;
         }	
     }
};
 
$sql_string = qq/SELECT f.name AS ForeignKey,
                 OBJECT_NAME(f.parent_object_id) AS TableName,
                 COL_NAME(fc.parent_object_id,
                 fc.parent_column_id) AS ColumnName,
                 OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
                 COL_NAME(fc.referenced_object_id,
                 fc.referenced_column_id) AS ReferenceColumnName
                 FROM sys.foreign_keys AS f
                 INNER JOIN sys.foreign_key_columns AS fc
                 ON f.OBJECT_ID = fc.constraint_object_id
                 ORDER BY TableName ASC/;
                 
$dbh->{LongReadLen} = 100000000;
$dbh->{LongTruncOk} = 0;

my $sth = $dbh->prepare($sql_string) or die "Can't prepare statement: $DBI::errstr\n";
$sth->execute ();
my @spresults = ();
my %tableSchema = ();
my $num = 0;

my $graph = Graph->new(refvertexed => 1, undirected=>1);

while(my ($a, $b, $c, $d, $e) = $sth->fetchrow_array) {
        $graph->add_weighted_edge($b, $d, 1);
}

while (@spresults = $sth->fetchrow_array){

    my $key = $spresults[1];
    
    if (exists $tableSchema{$key}){
        my @tmpArray = ();
        my @i = $tableSchema{$key};
        $num += scalar(@i);
        
        for (my $cnt = 0; $cnt < $num; $cnt++){
            if (defined $tableSchema{$key}->[$cnt]){
                push @tmpArray, $tableSchema{$key}->[$cnt];
            }
        }
        push @tmpArray,"$spresults[2],$spresults[3],$spresults[4]";
        $tableSchema{$key} = \@tmpArray;
    }
    else{
        $num = 0;
        my @relationshipArray = ();
        push @relationshipArray, "$spresults[2],$spresults[3],$spresults[4]";
        $tableSchema{$key} = \@relationshipArray;
    }
    # print $spresults[0]." - ".$spresults[1]." - ".$spresults[2]." - ".$spresults[3]." - ".$spresults[4]."\n";
}
print "\n";



#for my $key (keys %tableSchema){
#
#    my $x = scalar(@{$tableSchema{$key}});
#    for (my $g = 0; $g < $x; $g++)
#    {
#        my @elements = split (',',$tableSchema{$key}->[$g]);
#        $graph->add_edge($key, $elements[1]);
#    }    
#}

print "\nThe graph is $graph\n";
print "\n";

foreach my $a ($graph->vertices){
    foreach my $b ($graph->vertices){
        next if $a eq $b;
        my @nodes = $graph->SPT_Dijkstra($a,$b);

    }
}
$graph->SPT_Dijkstra_clear_cache;
print "\n";