#!/usr/bin/perl -wT
# transactions.pl - how to make mistakes that aren't permenant
#
# basic premise: run code in eval{}; commit at the end of evals,
# rollback on errors. RaiseError is useful because it automates
# the dying process (it is an apoptotic signal). 

use strict;
use diagnostics;
use DBI;

my $dbh = DBI->connect('DBI:Pg:dbname=local_medline',
                       'dewey', '', {AutoCommit=>0, RaiseError=>1, PrintError=>0})
  or die "Problem connecting: $DBI::errstr\n";

eval {
  my $sql = "INSERT INTO refs (pubmed_id, title) VALUES (10975878, 'new title')";
  my $sth = $dbh->prepare($sql);
  $sth->execute;
  $dbh->commit;
};
if ($@) {
  warn "The transaction didn't work: $@\n";
  my $rv = $dbh->rollback();
}

print "Press <enter>\n";
<>;

print "\nCitations before deletion\n";
&print_citations;

<>;

eval {
  # hmm... a DELETE without a WHERE clause...
  my $sql = "DELETE FROM refs";
  my $affected_rows = $dbh->do($sql);
  print "\n$affected_rows rows deleted\n\n";
  print "\nCitations now\n";
  &print_citations;
  # realize the error of your ways
  die "I didn't mean it!\n";

  $dbh->commit;
};
if ($@) {
  warn "The transaction was cancelled: $@\n";
  $dbh->rollback;
  <>;
}

print "\nCitations after rollback:\n";
&print_citations;

$dbh->disconnect;

sub print_citations {
  print "---------------------------------------\n";
  my $select_citations_sql = 'SELECT citation FROM refs';
  my $sth = $dbh->prepare($select_citations_sql);
  $sth->execute;
  while (my @row = $sth->fetchrow_array) {
    print "@row\n";
  }
  print "---------------------------------------\n";
}