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:

1
2
3
4
5
6
7
8
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/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);