Super simple tweet script in Ruby

I was trying to work out how to get my social network coverage of Skylines Australia and I decided I'd write a small script that would tweet any popular topics.

Obtaining the popular topics was a piece of pie, an alter to my topics table to flag a topic as 'already popular' and a query that grabbed any topics with more than X posts with the last post in the last 24hrs;

SELECT tid, title, posts FROM ibf_topics WHERE (last_post BETWEEN unix_timestamp()-14400 AND unix_timestamp()) AND posts > 50 AND sau_is_popular = 0 LIMIT 1

Easy enough.

But, tweeting them was more difficult. There are lots and lots of gems for Twitter but not all of them have been updated to work with Twitters OAuth. When I found one, the documentation was a little lacking and it was not clear how to obtain the credentials for the OAuth.

Thats when this awesome command line script came to my aid.

The end result is a simple little ruby script on a 4 hourly cron that tweets popular topics. Sweet!

require "rubygems"
require "mysql"
require "twitter"
require "cgi"
require "yajl"

dbh = Mysql.real_connect("localhost", "user", "pass", "db")

res = dbh.query("SELECT tid, title, posts FROM ibf_topics WHERE (last_post BETWEEN unix_timestamp()-14400 AND unix_timestamp()) AND posts > 50 AND sau_is_popular = 0 LIMIT 1")

TWITTER_CONSUMER_KEY = 'x'
TWITTER_CONSUMER_SECRET = 'x'
TWITTER_ACCESS_TOKEN = 'x'
TWITTER_ACCESS_SECRET = 'x'

Twitter.configure do |config|
    config.consumer_key = TWITTER_CONSUMER_KEY
    config.consumer_secret = TWITTER_CONSUMER_SECRET
    config.oauth_token = TWITTER_ACCESS_TOKEN
    config.oauth_token_secret = TWITTER_ACCESS_SECRET
end

client = Twitter::Client.new

while row = res.fetch_hash do
    status = "Skylines Australia trending topic: "
    topic = CGI::unescapeHTML(row["title"])

    # We have 60 chars in our status, so that leaves us with up to 100 for the topic title
    if (topic.length > 100)
        topic = topic[0,97] + "..."
    end

    status = status + topic + " http://skya.us/st=" + row["tid"]

    # Post a status update
    client.update(status)

    dbh.query("UPDATE ibf_topics SET sau_is_popular = 1 WHERE tid = " + row["tid"])
end

res.free

Hopefully this helps you.

You can see the status updates at Twitter.com/skyaus

By the way, I wrote a small URL shortener which helps also but you could use the bit.ly api or something else if you'd like.