MySQL auto-Tweet

While running thebandmind.com, I thought it would be fun to have a Twitter account which automatically is updated twice a day with a random quote from the database of quotes. This Perl script is run from Cron.

MySQL Database:

CREATE TABLE IF NOT EXISTS `quotes` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP 
    on update CURRENT_TIMESTAMP,
  `twitter` varchar(140) default NULL,
  `tweets` int(5) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Perl Script:

#!/usr/bin/perl

use lib "/home/sceneki/perl-libs";
use Data::Dumper;
use Net::Twitter::Lite;
#########################
# Settings
our($cfg);
$cfg->{'sqlserv'}       = 'localhost';
$cfg->{'sqluser'}       = 'user';
$cfg->{'sqlpass'}       = 'password';
$cfg->{'sqlbase'}       = 'database';
$cfg->{'twitteruser'}   = 'twitteruser';
$cfg->{'twitterpass'}   = 'twitterpass';

use DBI;

my $dbh = DBI->connect("dbi:mysql:".$cfg->{'sqlbase'}.':'.$cfg->{'sqlserv'}.":3306",
        "$cfg->{sqluser}", "$cfg->{sqlpass}") or die $DBI::errstr; # connect to db
my $sth = $dbh->prepare("SELECT `twitter`,`ID` FROM `quotes` WHERE ID >= (SELECT FLOOR( MAX(ID) * RAND()) FROM `q
uotes` ) AND `twitter` IS NOT NULL ORDER BY `tweets` ASC, RAND() LIMIT 1;") or die "Couldn't prepare statement: "
 . $dbh->errstr;
$sth->execute()or die "Couldn't execute statement: " . $sth->errstr;
while (my $dbpull = $sth->fetchrow_hashref()) {$current=$dbpull;}

$dbh->do("UPDATE `quotes` SET `tweets` = `tweets`+1 where `ID` = '$current->{'ID'}'")
  or die("Can't find record to update, died");

$status = $current->{'twitter'};
$status =~ s/\\(\'|\"|\\)/$1/g;

#print "Posting: ".$current->{'ID'}."\t".length($status)."\t".$status."\n";

my $nt = Net::Twitter::Lite->new(source => 'web');
$nt->credentials("$cfg->{'twitteruser'}","$cfg->{'twitterpass'}");
eval { $nt->update($status) };
if ( $@ ) {
  warn "update failed because: $@\n";
}
$dbh->disconnect() if ($dbh);