depends

Sometimes in an app you need to do something in SQL and not in your app’s language. At t-bot we’re strictly MySql. And occasionally, MySql specific functions/syntax creeps into an app.

First its:

has_many :published_posts,
  :conditions => 'published_on < now()'

From an app that allowed users to future date their posts.

Then its:

  @post = Post.find :first,
  :conditions => ['year(created_on) = ? and ' +
                  'month(created_on) = ? and ' +
                  'day(created_on) = ? and ' +
                  'keyword = ?']
                  params[:year], params[:month],
                  params[:day], params[:keyword]]

From an app that needed ‘pretty’ urls e.g. ‘posts/2007/1/15/some-blog-post’

Here’s a beauty doing some timezone logic in SQL instead of Ruby. This was done for performance reasons.

Event.find :all,
  :select => 'events.*',
  :joins => 'inner join localities on localities.id = events.locality_id ' +
    'inner join timezones on timezones.id = localities.timezone_id',
  :conditions => ['locality_id = ? and ' +
    'month(timestampadd(HOUR, timezones.utc_offset, start_date)) = ? and ' +
    'year(timestampadd(HOUR, timezones.utc_offset, start_date)) = ?',
    locality.id,
    locality.timezone.now.month,
    locality.timezone.now.year]

timestampadd. That one scared me a bit.

Something even crazier from an app that need to ‘weigh’ comments depending on when they were made. Also done in SQL for performance reasons. This is from an ActiveRecord::Base#find call.

:select => "revisions.*,
  SUM(
    IF(comments.created_on > DATE(NOW())-INTERVAL 1 DAY, 5,
    IF(comments.created_on > DATE(NOW())-INTERVAL 1 WEEK, 3,
    IF(comments.created_on > DATE(NOW())-INTERVAL 1 MONTH, 1,
    0)))
  ) as comment_score",

I always hesitate when I have to dip into MySql because I don’t want to develop a dependency on it. Why? Because years of using generic database independent interfaces and an insistance on the ability to change databases without changing a line of code. The fact is I’ve never had to change a database vendor on an app ever. And to me that argument gets weaker and weaker every day.

If the time comes that you need to switch to PostgreSQL then tell your client that you’re going to need some time to account for any dependencies your app has developed on its old database. However, I doubt this is ever going to happen.

So stop feeling guilty about using timestampadd().