#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use GraphViz;
use Data::Dumper;
use Getopt::Long;
use Log::Log4perl;
use Pod::Usage;

my $conf   = undef;
my $log    = undef;
my $help   = undef;
my $db     = undef;
my $dbuser = undef;
my $dbpass = undef;


$conf = q(
  log4perl.category.Script                 = DEBUG, ScreenAppender
  log4perl.appender.ScreenAppender         = Log::Log4perl::Appender::Screen
  log4perl.appender.ScreenAppender.stderr  = 1
  log4perl.appender.ScreenAppender.layout  = PatternLayout
  log4perl.appender.ScreenAppender.layout.ConversionPattern=[%p] %d %F:%L - %m%n
);
Log::Log4perl::init( \$conf );
$log = Log::Log4perl::->get_logger(q(Script));

$log->debug("start") if $log->is_debug();

# GetOptions
GetOptions("help|?" => \$help,
           "db=s"     => \$db,
           "dbuser=s" => \$dbuser,
           "dbpass=s" => \$dbpass) or pod2usage(2);
pod2usage(1) if $help;
pod2usage(1) if !$db;
pod2usage(1) if !$dbuser;
pod2usage(1) if !$dbpass;

my $dbh = DBI->connect ('DBI:mysql:' . $db, $dbuser, $dbpass);
my $sth = $dbh->prepare( q{select name, friend from friends} );
$sth->execute() or $log->logdie($dbh->errstr);

my %friends = ();
while (my $hashref = $sth->fetchrow_hashref()) {
  my ($name, $friend) = ($hashref->{name}, $hashref->{friend});
  $log->debug(qq{$name, $friend}) if $log->is_debug();
  push @{$friends{$hashref->{name}}}, $friend;
}
$sth->finish();

$log->debug(Dumper(\%friends)) if $log->is_debug();


my $g = GraphViz->new(layout => 'fdp', ratio => 'compress');

# 1st iteration : Add nodes
foreach my $name (keys %friends) {
  $g->add_node(
               "$name",
               label     => $name,
               shape     => 'box',
               style     => 'filled',
              ) or $log->logdie("add_node failed");
}

# 2nd iteration : Add edges
foreach my $name (keys %friends) {
  foreach my $friend ( @{$friends{$name}}) {
    $log->debug(qq{$name => $friend}) if $log->is_debug();
    $g->add_edge("$name" => "$friend", label => q{loves})
      or $log->logdie("add_edge failed");
  }
}

# Write to file
my $fh = undef;
open($fh, "> friends.png") or die "open failed:$!";
print $fh $g->as_png;
close $fh or die "close failed:$!";

__END__

=head1 NAME

simpsons - Graphviz DBI example

=head1 SYNOPSIS

  ./simpsons.pl --db DB --dbuser DBUSER --dbpass DBPASS

=head1 MySQL DDL, DML

create table friends (name varchar(32) not null, friend varchar(32) not null);
alter table friends add unique key (name, friend);
insert into friends values ('Homer', 'Marge'), ('Homer', 'Bart'),
('Homer', 'Lisa'),('Marge', 'Homer'),('Bart', 'Santa\'s Little
Helper'),('Lisa', 'Snowball II');

=cut