You want me to schedule what? »

OK - my weekend of fun was, well, fun! But my friends have all gone home now and it's time to get back to work.

Tops on my plate right now (or at least tops on my brain) is cleaning up the last few important parts to have a real beta version of fubnub.com.

As I saw it, to achieve this goal I had to get over one big road block: The ability to schedule your feeds for automatic fubnub'ing.

Actually, I've had this challenge on my to-do list from the start of the project, but I kept putting it off because my brain was thinking it was going to be a fairly annoying thing to implement. When I say annoying, I mean in the sense that it was going to take some solid design and serious thought before I sat at my keyboard and starting hacking. That is, if I wanted to create a solution that had the potential to scale over time.

Here's what I came up with:

1. Ultimately a scheduled task will run once an hour to fubnub any feeds that should be. Since I'm doing the backend of the rest of this project in Perl, I decided that this scheduled task should also be a Perl program.

The biggest challenge with this step is actually providing information to the users about how the system is works in the backend (which I haven't done yet).

The trick is that users can only have feeds published by the hour, the day, the week, or the month...so the first time you publish, you might say to publish every hour or month but it may publish the first feed almost immediately (because you have never published and you just set it up before the program started to run).

After the first time the feed gets processed, it should then be on the proper schedule and so I'm hoping it's not a big deal. But it may create some confusion for people as they get started. Knowing that up front allows me to prepare for handling all the confusion.

2. To actually accomplish scheduled posting, I created one table that stores the details about what feed you want to have automatically posted. This table includes information about what the interval for posting should be as well as when the last time the feed was published.

The user populates this table via the web site when they set up the schedule, but it's really the Perl program in the first step that uses this table.

Here's a brief rundown of the table structure:

scheduled_id - int
scheduled_title - varchar
scheduled_frequency - varchar
scheduled_time - int
account_id - int
scheduled_lastpublished - datetime
delivery_id - int

select
  scheduled_id,
  scheduled_title,
  scheduled.account_id,
  scheduled_lastpublished,
  command
from
  scheduled,
  delivery,
  commands
where
  case lower(scheduled_frequency)
    when 'hours' then dateadd(hour, -scheduled_time, getdate())
    when 'days' then dateadd(day, -scheduled_time, getdate())
    when 'weeks' then dateadd(week, -scheduled_time, getdate())
    when 'months' then dateadd(month, -scheduled_time, getdate())
  end > isnull(scheduled_lastpublished, dateadd(month, -50, getdate())) and
  delivery.delivery_id = scheduled.delivery_id and
  delivery.feedtype = commands.feedtype