BotFu the Blog - a simple blog with lots of kick!

If it's worth doing...

May 9 2008

If it's worth doing, it's worth refactoring.

I'm not sure if that's a real saying or not, but it seems to be my theme of the day...and so I'm adopting (or coining it).

I spent some time adding in support for various last.fm feeds to the fubnub system. As I did that, I found a lot of little parts of the code that I could move around and clean up.

Hopefully all that refactoring will be worth it in the long term. But even in the short term it felt good to do the work, if for no other reason than to go through the experience of refactoring various Perl classes and making them more DRY (don't repeat yourself).

Tomorrow I've got to spend some time writing a script to convert one XML format into another for reviews.com. And soon I've got to get my player rankings together (since they are due to the magazine by next weekend).

So I'm not sure how much time I'll have over the weekend to push forward on fubnub; but heading into the weekend, I'm hoping to push everything forward at least a little bit. We'll see.

It works! It works! Well, sort of.

May 8 2008

I actually got the background stuff for fubnub'ing feeds working today!

What that means is that I can now wrap my twitter feeds and rss feeds up into single posts and publish those posts to a blog or email via a twitter post.

Say what?

OK - it's a lot harder to explain than it is to actually use...and that's probably going to be my next challenge, putting together some screen shots, demos, and examples to make it easy to understand (and use) the whole idea.

But since I don't have those together yet, let's see if I can break it down in bullet form for you.

First the orig. problem that prompted me to build this:

1. I want to use Twitter to send quick updates about the things I'm working on - basically as a checked off to-do list. This way no matter where I am or what I'm doing, I can use Twitter to keep my list updated (since you can Tweet from via SMS, email, web, etc it's a nice way to piggyback off their services).

2. Eventually I want to take those one-off Twitter postings and roll them up into one larger list...a "this is what I did today" kind of thing.

3. Then I want that to be published on demand to my blog or sent to me via email as a single post/email.

OK - so that was the initial idea. Of course it expanded almost immediately into a number of 'cool' things I could/should probably do if I'm going to build a system that solves my above problem...all of which I'll share more details on later...for now though, let's talk about how the system actually helps me achieve my orig. goal:

1. I created a template that each of my twitter posts will get applied to when I publish. (eventually you'll be able to pick templates from the fubnub.com web site as well as create your own - but right now I just manually entered a few into the database).

2. I set up the delivery options I wanted for when I publish (ie. I associated my twitter username to one of the templates, said that I wanted the data delivered via email when I publish, and provided the email address I wanted it sent to.) (again I did this by manually entering the information into the database, but real users will do this via the fubnub.com web site).

3. I follow fubnub via twitter (and since fubnub is a twitter bot, it should then auto-follow me as well). You follow fubnub by tweeting this from your Twitter account:

follow fubnub

4. Now all I have to do is Tweet away and when I'm ready to 'publish', I just send a direct tweet to fubnub that looks something like this:

d fubnub #pt I'm publishing with fubnub!

This command tells fubnub that it should grab my latest tweets (from my Twitter timeline), apply the template I choose to that data, and then send it via email to the address I specified.

The subject of the email will be everything after the #pt in my tweet...so in this case "I'm publishing with fubnub!".

Pretty cool eh? But there's already a little more you can do (and lots more in the works).

1. If I chose to publish to my blog instead, the stuff after the #pt would be used as my post title. (currently you can publish via xmlrpc to wordpress and other metaweblog supported xmlrpc clients)

2. If I want to wrap up a series of RSS feeds into one post, I can go through the set up process like I mentioned above and the only thing that needs to change (besides the template because we will be using different fields) is the #pt command to something like this:

d fubnub #pr I'm publishing with fubnub!

3. fubnub also keeps track of the latest Tweet and/or RSS link that was included when you published, so that the next time you say to publish, it will not repeat items. (there are a few gotchas in the system right now because of limitations on what you can get out of Twitter timelines and RSS feeds, but the basic idea is true that it will not repeat data that it has already published.)

Of course all of this will hopefully be broken down with images and examples on the actual fubnub.com site (just as soon as I get time to do that).

I'll probably also build in support so that you can publish via tools at the fubnub site or by sending a publish command via email directly to fubnub (for those that don't want to mess with Twitter).

I'm also going to be working on adding in support for other feed related sources like Tumblr and audioscribbler (last.fm stuff).

For now though, the back end is up and running. So I'll be moving on to the front end (the fubnub.com web site)...once I get enough of that together, I can actually try and get a few other people out there trying/using it...and then we'll see where it takes us!

The Perl DBI and MS SQL text fields

May 7 2008

So I'm putting in a crunch effort to get this latest fubnub idea together and, as is the case with most of these crunches, I'm hitting little problems at every corner.

Nothing show stopping, but a few of them have taken me some serious time to track down solutions for. So I thought, hey why not share solutions to some of the more annoying roadblocks I hit?

Who knows, maybe I can save the next person a little time?

Anyway, the first real problem I hit had to do with the Perl DBI, the DBD::ODBC driver, and MS SQL text fields. Basically I was hitting an error like this when I tried to load records with text or ntext fields of any real size:

Error: DBD::ODBC::st fetchrow_array failed: [Microsoft][ODBC SQL Server Driver]String data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too
small) err=-1)

Searching around online, I found a number of people suggesting that you set a few options so that your code looks something like this:

my $dbh = DBI->connect("dbi:ODBC:$dsn", $username, $password);
$dbh->{'LongTruncOk'} = 1;
$dbh->{'LongReadLen'} = 255;

The LongTruncOk tells the driver that it's OK to truncate text fields if it needs to. The LongReadLen sets the number of bytes a single select statement should grab from a text field.

For the most part, this was the extent of the solutions I found, so I guess it worked well enough for most people. However, I still had a couple of small problems -- OK big problems.

It really wasn't OK to truncate my data and I don't know how many bytes of data my text fields might end up holding (which is one of the reasons I'm using a text field in the first place).

So what to do? Well as it turns out, the ODBC driver for PHP that I use tends to have this same issue. As luck would have it the solution to the problem in PHP turns out to also work for this Perl DBD::ODBC situation.

You simply have to get the length in bytes of the data you are going to grab (using the datalength function in T-SQL) and then make multiple calls to the database grabbing a substring of your text field in byte chunks.

You just do this until you've loaded all your data into a variable. A code example shows this concept in action:

my $dbh = DBI->connect("dbi:ODBC:$dsn", $username, $password);
$dbh->{'LongTruncOk'} = 1;
$dbh->{'LongReadLen'} = 255;
my $sth = $dbh->prepare('SELECT datalength(mytextfield) dlength, mytextfield FROM tablewithtext WHERE id = ?') or die;
$sth->execute($id) or die;
my $text = "";
while (my @data = $sth->fetchrow_array()) {
  my $dlength = $data[0];
  if ($dlength > 255) {
    my $offset = 1;
    while ($offset < $dlength) {
      my $newoffset;
      if ($offset + 255 < $dlength) {
        $newoffset = 255;
      } else {
        $newoffset = ($dlength - $offset) + 1;
      }
      my $query = "SELECT SUBSTRING(mytextfield, $offset, $newoffset) AS txt FROM tablewithtext where id = $id";
      my $st1 = $dbh->prepare($query) or die;
      $st1->execute() or die;
      while (my @d1 = $st1->fetchrow_array()) {
        $text .= $d1[0];
      }
      $offset += 255;
    }
  } else {
    $text = $data[1];
  }
}

So a couple of interesting things to note about the code above...

First, I do actually use the two settings (LongTruncOK and LongReadLen) mentioned above.

I picked the value of 255 bytes for my default text field size because it should grab most cases just fine for me...and the larger you make that block, the fewer times you have to do the additional query to get all your data.

The downside of course is that you are using more memory the larger you set this value to.

Second in my initial query I don't just get the datalength, I actually also grab the text field as well.

This way, if my text field is smaller than the initial 255 byte chunk I told the driver to grab, I can just use the field directly like a normal select statement and avoid any extra database calls (remember database calls are ALWAYS expensive things you want to try and limit as much as possible).

If the data in my text field is bigger than 255 bytes, I start doing my additional queries grabbing the next 255 bytes of the text field each time until I've got all the data for the text field loaded.

Then I can finally move on without losing access to any of my data (I mean why would I put data into my database that I'm not actually going to need to get out and use in some way at some point?).

Again, the downside to all of this is the number of database calls that you might end up making just to load your data into memory (so you can do whatever it is that you really want to do with that data).

So just be mindful of how often you do this kind of thing, and try to design around having to do this as much as you can...especially in production and heavy load environments.

Thought dump...

May 6 2008

XML without a DTD or Schema is basically just a CSV file with slightly harder processing rules. Sure you can get a little more out of the relationship of the data, but honestly not that much more than a well designed CSV.

Creating Python byte code from Ruby is kind of cool...if for no other reason than the idea of it.

I wish I had time to attend things like the Make Fair...at least Guy posted some really cool pictures.

Andy Lester thinks about things the rest of us should - Perl is lucky to have a guy that cares so much.

BotFu fight is a lot of fun when you get even just a couple of people interested in it with you.

Ka'ching.

May 5 2008

I put in some good time on the front end stuff of fubnub.com tonight...still nothing really worth chatting about or showing off, but it's one step closer to getting there

I also did a few fixes to some BotFu html so that it doesn't look like total poo on Internet Explorer...but I now have to sit down and go through all of the site with Internet Explorer when I have time (yippie).

On other news, I got back in the pool tonight for the first time in many too many years...it's really fun...but it's also really sad how far my physical state has fallen. Hopefully I can get a routine together and keep it up...but for now it's just fun doing something (rather than my usual nothing).