Dear Perl Monks,

I'm seeking for your wisdom to use the Graphs Module in a meaningful manner per my current needs.

Basically, I have a database that contains 30+ tables. I want to find the quickest path to join 2 or more tables using the Dijkstra Algorithm.

As far as I can tell, the Graphs Module already has a Dijkstra functionality out of the box that can be used for this purpose.

In below code, I am trying to pull the information about my database (the foreign keys and, thus, dependent tables) of my schema.

I want to accomplish two things: 1) Assuming that I have two or more tables given by the user (either through STDIN or any other form), I want my Perl Script to tell me which other tables from my DB schema need to be added to join the original tables together (to create the INNER JOINs, etc), 2) I want to find the quickest path between the originally selected tables

.

However, when I call on the my @nodes = $graph->SPT_Dijkstra($a,$b); method below, I get the following error: "Not an ARRAY reference at C:\Perl\site\lib/Heap/Elem.pm line 31."

1) Does anybody know why this error is happening?

2) Does anybody have a better example as to how this can be accomplished?

Here is my current code: 

#!/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=MYCOOLSQLSERVE
+R2008R2;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 ReferenceTabl
+eName,
                 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],$spresul
+ts[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";

[download]
Thank you in advance for your wisdom...

In reply to Using Perl Graphs (Data Structure) and SPT_Dijkstra Routine by PerlApprentice

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.