#!/usr/bin/perl -w
# PERL connecting to database via DBI, and using a Gtk2 GUI
use strict;      # a good idea for all non-trivial Perl scripts

use DBI;

#/* connecting to a PostgreSQL database  */

my $dbh =
DBI->connect("DBI:Pg:dbname=jensen;host=cs-linux;","mickey","MoUsE",{AutoCommit => 0})
or
die ("Connect failed");

my $statement = "select * from address order by name";
my $sth = $dbh->prepare($statement)
    or die "Can't prepare $statement: $dbh->errstr\n";

$sth->execute
      or die "can't execute the query: $sth->errstr";

# a statement handle for updates
my $uph = $dbh->prepare("update address set street=?, city=?, prov=?,postal_code=?,phone=? where name=?")
	or die "Can't prepare update statement: $dbh->errstr\n";

my $insh = $dbh->prepare("insert into address VALUES (?,?,?,?,?,?)")
	or die "Can't prepare update statement: $dbh->errstr\n";

my $delh = $dbh->prepare("delete from address where name=?")
	or die "Can't prepare update statement: $dbh->errstr\n";

my $oldname = '';		# use for update/insert test (should name be changed)

#-----------------------------------------------------------
# the GUI stuff, using Gimp ToolKit 2.0
#-----------------------------------------------------------	
use Gtk2 '-init';         # load the Gtk-Perl module

set_locale Gtk2;  # internationalize

# convenience variables for true and false
my $false = 0;
my $true = 1;

# widget creation
my $window = new Gtk2::Window( "toplevel" );

# containers, the widgets of type label, entry, and button will be packed into
my $vboxl = new Gtk2::VBox ($true, 3);	#true for equal spacing
my $vboxr = new Gtk2::VBox ($true, 3);
my $hbox = new Gtk2::HBox ($true, 3);	# will hold the 2 VBox's.

# Some buttons to perform actions
my $nbutton = new Gtk2::Button( "Next Record" );
my $ubutton = new Gtk2::Button( "Update" );
my $xbutton = new Gtk2::Button( "Close" );
my $rbutton = new Gtk2::Button( "Roll back Updates" );

# list of the fields of the database table
my @fields = ('name','street','city','prov','postal_code','phone');

#now associative arrays of labels and entry  fields for each
my %label;
my %entry;
foreach (@fields)
{	$entry{$_} = new Gtk2::Entry;
	$label{$_} = new Gtk2::Label($_);
	$entry{$_}->show();
	$vboxr->add($entry{$_});
	$label{$_}->show();
	$vboxl->add($label{$_});
}

# callback registration
$window->signal_connect( "delete_event", \&CloseAppWindow );   
$xbutton->signal_connect( "clicked", \&CloseAppWindow );
$nbutton->signal_connect( "clicked", \&nextrecord );
$ubutton->signal_connect( "clicked", \&update );
$rbutton->signal_connect( "clicked", \&rollback );

# show buttons
$nbutton->show();
$ubutton->show();
$xbutton->show();
$rbutton->show();

# add the buttons to the boxes
$vboxl->add($nbutton);
$vboxl->add($ubutton);

$vboxr->add($xbutton);
$vboxr->add($rbutton);

$vboxl->show();
$vboxr->show();

$hbox->add( $vboxl);
$hbox->add( $vboxr);
$hbox->show();
# set window attributes and show it
$window->add( $hbox );
$window->show();

# Gtk event loop
main Gtk2;

# Should never get here
exit( 0 );



### Callback function to close the window
sub CloseAppWindow
{
   $sth->finish;
   $dbh->commit();
   $dbh->disconnect;
   Gtk2->main_quit;
   return $true;
}

sub nextrecord
{
   my @row = $sth->fetchrow_array;
   my $i=0;
   if (@row)
     { $oldname = $row[0];			#save name, to test in case of insert
       foreach (@fields)
	{  $entry{$_}->set_text($row[$i++]);	   
	}
     }
   else
     {  $entry{'name'}->set_text("That's all FOLKS!");
        $oldname = '';				# no 'old row' to possibly delete!!!!
        return $false;
     }  
   return $true;
}

sub update
{	# if the name has changed, we want to insert, and delete the old one
	my @values;
	foreach (@fields)
	{
	     push(@values, $entry{$_}->get_text());
	}
	#now need name at end, in case of update
	my $name = $entry{'name'}->get_text();
	if ($name eq $oldname)
	{
	    ($name, @values) = @values;
	    push(@values, $name);
	    print "UPDATE @values\n";
	    $uph->execute(@values);
	}
	else
	{   if ($oldname)
		{$delh->execute($oldname); 	#DELETE old row on name change
		 print "DELETE $oldname\n";
		} 
	    $insh->execute(@values);		#INSERT new values from GUI
	    print "INSERT @values\n";
	}
}  

sub rollback
{
    $dbh->rollback;
    print "ROLLBACK all changes made so far\n";
} 


# END EXAMPLE PROGRAM