Trigger tweets with cron and mysql

Adventskalender are a popular German countdown to christmas. Every morning, you open a little door with a gift or chocolate behind it.

 

Our local Lions Club, sells such a calendar with prizes behind the little doors. Each one has a unique number. The numbers get chosen randomly every day. They are published in the local newspaper and on our website.

 

It is basically a lottery. Lots of people look up the numbers.

 

This year, I wanted to also publish them on twitter, so calendar-owners simply follow @lionswerne and get the numbers every morning. Manually tweeting every morning would not be fun.

 

Here is how I wrote a little script, so my Linux-Computer takes care of this automatically. If you are doing something similar, you can follow my instructions. Let me know, if it was of any help.

Install twidge

First, we need a commandline tool to push twitter updates without all that gui-stuff. Unfortunately, the internet is full of instructions, that do not work any more. Twitter has recently changed its API.

 

But twidge ist a great tool, that works in its most recent version (1.0.6). Ubuntu still provides 1.0.2 which does not work, any more.

 

Get it here: 

twidge-1.0.6-linux-i386-bin

After installing it (I moved it to /usr/bin/twidge and made it executable with chmod u+x twidge), you simply run

 

twidge setup

 

It will give you an url and ask for a numer. Open the url in your browser. If your browser is already logged into twitter, it will give you a number. If not, it will first ask you for username and password. Type the number into your terminal and you are all set.

 

Test it by calling

 

twidge lsrecent

 

You should see your most recent tweets.

 

To learn more, type twidge lscommands

Datasource MySQL

The winning numbers are already in a mysql-database on our webserver, where a php-page takes care of publishing them on our website.

 

I am a PostgreSQL-user, but my webserver only supports mysql.

 

I got the dump from the the webserver, installed mysql-server on my local Ubuntu-machine and used the sql-dump to easily create and poulate the table. The column names are not really important. There is a date, a numer and two colums about the prize (what is it? and who sponsored it)

 

The colums could be different. But we need a column with the date on which to publish the record.

The bash command

Now we need a command that first gets the numbers and the prize of the current day from the MySQL-Table, construct a text-message and call twidge to push it into Twitter.

 

I spare you more than half an hour of tinkering and present you with the working one-liner.

 

echo "SELECT concat('twidge update \"TEST. Kein echter Gewinn:', losnummer, ' ', gewinn, ' von ', spender, '\"') FROM weihnachtskalender WHERE datum = DATE(NOW());" | mysql -u root --password=iamnottellingyou --skip-column-names mysql | iconv -f utf8 -t latin9 -c -- | bash

 

Yes, this is all one line. The SQL-SELECT-statement will result in a twidge command with the correct text. It is piped into the the mysql command-line client which pipes the command into bash.

 

In my example, I had to insert an iconv command, because my Ubuntu-machine uses UTF8 natively. Twitter seems to expect latin9 as its encoding. The -c means that any character that can not be converted into latin9 will simply be removed. It prevents the command from throwing an error. If your Umlauts show up correctly, or you have a language that can do without such fancy letters, you do not need the iconv-command.

 

As alwasy, I have a feeling that there is a more elegant way to do it. Let me know in the comments, if you have a suggestion. But right now, this works and that is what counts, isn't it?

Add it to your crontab

Now, all we have to do is call the above command daily.

 

The unix-tool cron can do that for us.

 

sudo joe /etc/crontab (or use an editor of your choice. I just like joe. It is so much like good old wordstar...)

 

and add the line

 

10 5    * 12 *  jan     echo "SELECT concat('twidge update \"Nr ', losnummer, ': ', gewinn, ' von ', spender, '\"') FROM weihnachtskalender WHERE datum = DATE(NOW());" | mysql -u root --password=iamstillnottellingyou --skip-column-names mysql | iconv -f utf8 -t latin9 -c -- | bash

 

It will call the script every morning at 10 Minutes after 5 in December.

 

Replace jan with your username and be sure to use your mysql-password.

Wait for it...

Now I am waiting for December to come. Then I will see if it really works.

 

So this Howto might get updated, if something does not work as advertised.

Write a comment

Comments: 0