Read my latest article: Ezra Zygmuntowicz -- Farewell, Friend. (posted Mon, 01 Dec 2014 17:53:00 GMT)

Using BETWEEN for SQL comparisons

Posted by Sat, 14 Nov 2009 20:55:00 GMT

Recently, Carlos, suggested that I should start sharing some basic SQL tips that help with performance and/or general usage. I recently came across some code that I didn’t like to read and/or write. For example, let’s take the following…


SELECT * FROM brochures WHERE published_at <= now() AND archived_at >= now()

Essentially, this is pulling back some data WHERE the the brochures are considered published. (We have a project that allows people to manage their brochure launch dates ahead of time.) In fact, in this project, we have no less than 6-8 dates in the database that we’re comparing data on and it’s easy to get lost in the logic when trying to understand it.

Now, there isn’t anything inheriently wrong with how this condition is constuctued. As a matter of personal taste, I find it annoying to mentally parse. Also, I find having to write now() more than once in a WHERE clause to feel like I’m repeating myself.

Read it outloud…

“WHERE the brochures published at date is less than and/or equal to right now AND the archived date is greater than and/or equal to now.”

Who talks like that?

Luckily, there is a better and in my opinion, a more readable way to express this is with the BETWEEN construct in SQL. (postgresql docs, mysql docs)


SELECT * FROM brochures WHERE now() BETWEEN published_at AND archived_at

Let’s read this outloud…

“WHERE the current date is between the published at and archived at dates.”

This sounds more natural to me.

Additionally, you can also do the inverse with NOT.


SELECT ... WHERE now() NOT BETWEEN brochures.published_at AND brochures.archive_at

Remember kids, “code is for humans first and computers second.”—Martin Fowler

The 8-Hour Rails Code Audit

Posted by Tue, 20 Oct 2009 12:13:00 GMT

While our team is typically focused on larger client and internal projects, we do get an opportunity to assist businesses on a much smaller scale. Whether this be through retainer-based consulting or through code audits, we have seen a lot of Ruby on Rails code over what has nearly been… five years!? We’ve been able to compile a fairly extensive checklist that we use in our code audit process that we’ve decided to streamline it into a smaller product.

Historically, this service has ranged anywhere from $2000-6000, depending the size and scope of the projects, but we want to help smaller startups1 and projects outline a roadmap for how they can begin to refactor and optimize their existing code base so that they can be more efficient at the start of 2010. So, we’ve scaled things down into an extremely affordable flat-rate package where we work off of a pre-defined number of hours.[2]

Through the end of 2009, we’re now offering the 8-Hour Rails Code Audit package for just $1000 USD (details).

We’re currently limiting this service to just two projects per week, so reserve your spot now.

1 Larger projects are welcome to benefit from this service and custom quotes are available upon request.

2 As always, we’re happy to discuss longer engagements.

Related Posts

Flash Message Conductor now a Gem

Posted by Tue, 13 Oct 2009 15:30:00 GMT

We’ve been doing some early (or late… if you’re a half-full kind of person) spring cleaning on some of our projects. One of the small projects, flash_message_conductor, which we released last year as a plugin is now a gem. We’ve been moving away from using plugins in favor of gems as we like locking in specific released versions and being able to specify them in our environment.rb file is quite convenient.

To install, just run the following:


  sudo gem install flash-message-conductor --source=http://gemcutter.org
  Successfully installed flash-message-conductor-1.0.0
  1 gem installed
  Installing ri documentation for flash-message-conductor-1.0.0...
  Installing RDoc documentation for flash-message-conductor-1.0.0...

You’ll then just need to include the following in your config/environment.rb file.

Rails::Initializer.run do |config|
  # ...
  config.gem 'flash-message-conductor', :lib => 'flash_message_conductor', :source => "http://gemcutter.org"
end

You can take a peak at the README for usage examples.

We’ll be packaging up a handful of our various plugins that we reuse on projects and moving them to gems. Stay tuned… :-)

Planting the seeds

Posted by Sat, 05 Sep 2009 13:00:00 GMT

Yesterday, the Rails team released 2.3.4, which includes standardized way for loading seed data into your application so that you didn’t have to clutter your database migrations.

I noticed a few comments on some blogs where people were asking how to use this new feature, so here is a quick runthrough a few ways that you can use it.

Populating Seed Data Approaches

The db/seeds.rb file is your playground. We’ve been evolving our seed file on a new project and it’s been great at allowing us to populate a really large data. Here are a few approaches that we’ve taken to diversify our data so that when we’re working on UI, we can have some diversified content.

Basic example

Any code that add to db/seeds.rb is going to executed when you run rake db:seed. You can do something as simple as:

# db/seeds.rb

Article.create(:title => 'My article title', :body => 'Lorem ipsum dolor sit amet, consectetur adipisicing elit')

Just create database records like you would in your Rails application or in script/console. Simple enough, right? Let’s play with a few other approaches that we’ve begun to use.

Use the names of real people

We’re using the Octopi gem to connect to github, collect all the names of people that follow me there, and using their names to seed our development database.

@robby_on_github = Octopi::User.find('robbyrussell')

# add a bunch of semi-real users
@robby_on_github.followers.each do |follower|
  github_person = Octopi::User.find(follower)
  next if github_person.name.nil?

  # split their name in half... good enough (like the goonies)
  first_name = github_person.name.split(' ')[0]
  last_name = github_person.name.split(' ')[1]
  new_person = Person.create(:first_name => first_name, :last_name => last_name, :email => Faker::Internet.email,
                             :password => 'secret', :password_confirmation => 'secret',
                             :github_username => follower, :website_url => github_person.blog)
  # ...
end

We do this with a few sources (twitter, github, etc..) to pull in the names of real people. If you want to be part of my seed data, you might consider following me on Github. ;-)

Use Faker for Fake data

You may have noticed in the previous code sample, that I used Faker in that code. We are using this a bunch in our seed data file. With Faker, you can generate a ton of fake data really easy.

person.links.create(:title => Faker::Lorem.words(rand(7)+1).join(' ').capitalize,
                    :url => "http://#{Faker::Internet.domain_name}/",
                    :description => Faker::Lorem.sentences(rand(4)+1).join(' '))

We might toss something like that into a method so that we can do the following:

@people = Person.find(:all)

500.times do
  generate_link_for(@people.sort_by{rand}[0])
end

...and we’ll get 500 links added randomly across all of the people we added to our system. You can get fairly creative here.

For example, we might even wanted random amounts of comments added to our links.

def generate_link_for(person)
  link = person.links.create(:title => Faker::Lorem.words(rand(7)+1).join(' ').capitalize,
                             :url => "http://#{Faker::Internet.domain_name}/",
                             :description => Faker::Lorem.sentences(rand(4)+1).join(' '))

  # let's randomly add some comments...
  if link.valid?
    rand(5).times do
      link.comments.create(:person_id => @people.sort_by{rand}[0].id,
                           :body => Faker::Lorem.paragraph(rand(3)+1))
    end
  end
end

It’s not beautiful, but it gets the job done. It makes navigating around the application really easy so that we aren’t having to constantly input new data all the time. As mentioned, it really helps when we’re working on the UI.

Your ideas?

We’re trying a handful of various approaches to seed our database. If you have some fun ways of populating your development database with data, we’d love to hear about it.

Using model constants for project sanity

Posted by Tue, 23 Jun 2009 06:39:00 GMT

On one of our larger client projects (approx. 160 models and growing…) we have a specific model that we refer to quite a bit throughout our code. This model contains less than 10 records, but each of them sits on top of an insanely large and complex set of data. Each record refers to a each of their regions that our client does business in.

For example… we have, Australia, United Kingdom, Canada, United States, and so forth. Each of these regional divisions has their own company code, which are barely distinguishable from the next. They make sense to our client, but when we’re not interacting with those codes on a regular basis, we have to look constantly look them up again to make sure we’re dealing with the right record.

I wanted to share something that we did to make this easier for our team to work around these codes, which we should have thought of long ago.

Let’s take the following mode, Division. We only have about 10 records in our database, but have conditional code throughout the site that are dependent upon which divisions specific actions are being triggered within. Each division has various business logic that we have to maintain.

Prior to our change, we’d come across a lot of code like:

# For all divisions except Canada, invoices are sent via email
# In Canada, invoices are sent via XML to a 3rd-party service
def process_invoices_for(division)
  if division.code == 'XIUHR12'
    # trigger method to send invoices to 3rd party service
    # ...
  else
    # batch up invoices and send via email
    # ...
  end
end

An alternative that we’d also find ourselves using was.

if division.name == 'Canada'

Hell, I think I’ve even seen if division.id == 2 somewhere in the code before. To be fair to ourselves, we did inherit this project a few years ago. ;-)

Throughout the code base, you’ll find business rules like this. Our developers all agreed that this was far from friendly and/or efficient and worst of all, it was extremely error-prone. There have been a few incidents where we read the code wrong and/or got them confused with one another. We were lacking a convention that we could all begin to rely on and use.

So, we decided to implement the following change.

Model Constants

You might already use constants in your Ruby on Rails application. It’s not uncommon to add a few into config/environment.rb and call it a day, but you might also consider scoping them within your models. (makes it much easier for you to maintain them as well)

In our scenario, we decided to add the following constants to our division model.

class Division < ActiveRecord::Base
  AFRICA      = self.find_by_code('XYU238')
  ASIA        = self.find_by_code('XIUHR73')
  AUSTRALIA   = self.find_by_code('XIUHR152')
  CANADA      = self.find_by_code('XIUHR12')
  USA         = self.find_by_code('XIUHR389')
  # etc..
end

What this will do is load up ech of these constants with the corresponding object. It’s basically the equivallent of us doing:

if division == Division.find_by_code('XIUHR389')

But, with this approach, we can stop worrying about their codes and use the division names that we’re talking about with our clients. Our client usually approaches us with, “In Australia, we need to do X,Y,Z differently than we do in the other divisions due to new government regulations.”

if division == Division::CANADA
  # ...
end

case division
  when Division::AFRICA
    #
  when Division::AUSTRALIA
    # ...
end

We are finding this to be much easier to read and maintain. When we’re dealing with a lot of complex business logic in the application, little changes like this can make a big difference.

If you have any alternative solutions, we’d love to hear them. Until then, we’ve been quite pleased with this approach. Perhaps you’ll find some value in it as well.

Subdomain accounts with Ruby on Rails explained

Posted by Sun, 11 Jan 2009 23:30:00 GMT

DHH recently posted, How to do Basecamp-style subdomains in Rails on SvN and it just happens that I was implementing some similar stuff this last week for a project we’re developing internally.

In our project, not everything needs to be scoped per-account as we are building a namespace for administrators of the application and also want a promotional site for the product. Three different interfaces, with some overlap between them all.

Let’s walk through a few quick steps that you can follow to setup the two interfaces within the same application.

Suppose that we’re going to build a new web-based product and have the following requirements initially.

  • We need a promotional site for sign-ups, frequently-asked-questions, support requests, etc.
  • When people sign-up for an account, they’ll should have their own unique sub-domain
  • There are two different visual layouts (promotional site and the account)

Note: I use RSpec and am going to skip the TDD process here and let you conquer that for yourself. Am using the default Rails commands in this tutorial.

Account model / Database

We’re going to generate a new model for Account, which will be responsible for scoping sub-domains and individual accounts.

  account-demo : ruby script/generate model Account subdomain:string 
       create  app/models/
       create  test/unit/
       create  test/fixtures/
       create  app/models/account.rb
       create  test/unit/account_test.rb
       create  test/fixtures/accounts.yml
       exists  db/migrate
       create  db/migrate/20090111220627_create_accounts.rb

Great, let’s migrate our application.

   account-demo : rake db:migrate
  ==  CreateAccounts: migrating =================================================
  -- create_table(:accounts)
     -> 0.0045s
  ==  CreateAccounts: migrated (0.0052s) ========================================

Before we get too far, let’s make sure that we’re adding an index on this table for the subdomain, as it’ll improve performance in the database as the subdomain will used in SQL conditions quite often.

account-demo : ruby script/generate migration AddIndexToAccountSubdomain
     exists  db/migrate
     create  db/migrate/20090111221009_add_index_to_account_subdomain.rb

Let’s open up this new migration file and toss in a UNIQUE INDEX on subdomain.

  class AddIndexToAccountSubdomain < ActiveRecord::Migration
    def self.up
      add_index :accounts, :subdomain, :unique => true
    end

    def self.down
      remove_index :accounts, :subdomain
    end
  end  

Okay, let’s migrate this bad boy.

   account-demo : rake db:migrate
  ==  AddIndexToAccountSubdomain: migrating =====================================
  -- add_index(:accounts, :subdomain, {:unique=>true})
     -> 0.0047s
  ==  AddIndexToAccountSubdomain: migrated (0.0050s) ============================  

Great, we’re now ready to move on to the fun stuff.

Let’s open up app/models/account.rb and throw some sugar in it.

Data Validation

Because we’re going to be dealing with subdomains, we need to make sure that we’re only allowing people to sign-up with valid data otherwise, there could be issues. URLs need to fit within certain conventions and we need to make it as graceful as possible for our customers.

Let’s make a quick list of what we need to enforce for the subdomain attributes. This can easily be expanded, but let’s cover the basics.

  • Each account should have a subdomain
  • Each subdomain should be unique within the application
  • A subdomain should be alpha-numeric with no characters or spaces with the exception of a dash (my requirement)
  • A subdomain should be stored as lowercase

So, let’s update the following default Account model….

  class Account < ActiveRecord::Base
  end  

..and add some basic validations.

  class Account < ActiveRecord::Base
    validates_presence_of :subdomain
    validates_format_of :subdomain, :with => /^[A-Za-z0-9-]+$/, :message => 'The subdomain can only contain alphanumeric characters and dashes.', :allow_blank => true
    validates_uniqueness_of :subdomain, :case_sensitive => false

    before_validation :downcase_subdomain

    protected

      def downcase_subdomain
        self.subdomain.downcase! if attribute_present?("subdomain")
      end
  end  

Reserved subdomains

In the project that our team is working on, we wanted to reserve several subdomains so that we could use them later on. We tossed in the following validation as well.

  validates_exclusion_of :subdomain, :in => %w( support blog www billing help api ), :message => "The subdomain <strong>{{value}}</strong> is reserved and unavailable."

This will prevent people from using those when they sign up.

Controller / Handling Requests

Let’s now think about how we’ll handle requests so that we can scope the application to the current account when a subdomain is being referenced in the URL.

For example, let’s say that our application is going to be: http://purplecowapp.com/ [1]

Customers will get to sign-up and reserve http://customer-name.purplecowapp.com/. I want my account subdomain to be green.purplecowapp.com and everything under this subdomain should be related to my instance of the application.

I’ve begun working on my own module, which is inspired mostly by the account_location plugin with some additions to meet some of our product’s requirements.

Here is my attempt to simplify it for you (removed some other project-specific references) and have put this into a Gist for you.

  #
  # Inspired by
  # http://dev.rubyonrails.org/svn/rails/plugins/account_location/lib/account_location.rb
  #
  module SubdomainAccounts
    def self.included( controller )
      controller.helper_method(:account_domain, :account_subdomain, :account_url, :current_account, :default_account_subdomain, :default_account_url)
    end

    protected

      # TODO: need to handle www as well
      def default_account_subdomain
        ''
      end

      def account_url( account_subdomain = default_account_subdomain, use_ssl = request.ssl? )
        http_protocol(use_ssl) + account_host(account_subdomain)
      end

      def account_host( subdomain )
        account_host = ''
        account_host << subdomain + '.'
        account_host << account_domain
      end

      def account_domain
        account_domain = ''
        account_domain << request.domain + request.port_string
      end

      def account_subdomain
        request.subdomains.first || ''
      end

      def default_account_url( use_ssl = request.ssl? )
        http_protocol(use_ssl) + account_domain
      end

      def current_account
        Account.find_by_subdomain(account_subdomain)
      end

      def http_protocol( use_ssl = request.ssl? )
        (use_ssl ? "https://" : "http://")
      end
  end  

View gist here (embed wasn’t working right when I tried)

Just include this into your lib/ directory and require it in config/environment.rb. (if people think it’s worth moving into a plugin, I could do that)

Including AccountSubdomains

In the main application controller (app/controllers/application.rb), just include this submodule.

  class ApplicationController < ActionController::Base
    include SubdomainAccounts

    ...
  end  

Now, we’ll want to add a check to verify that the requested subdomain is a valid account. (our code also checks for status on paid memberships, etc… but I’ll just show a basic version without that)

Let’s add in the following to app/controllers/application.rb. This will only check on the status of the account (via subdomain) if the current subdomain is not the default. For example: purplecowapp.com is just our promotion site, so we won’t look up the account status and/or worry about the subdomain. Otherwise, we’ll check on the status.

  before_filter :check_account_status

  protected
    def check_account_status
      unless account_subdomain == default_account_subdomain
        # TODO: this is where we could check to see if the account is active as well (paid, etc...)
        redirect_to default_account_url if current_account.nil? 
      end
    end  

Current Account meets Project model

When requests are made to an account’s subdomain, we want to be able to scope our controller actions.

WARNING: I’m going to gloss over the following steps because this is just standard Rails development stuff and I want to focus on how to scope your Rails code to account subdomains.

I’ll just say that this product gives each account many projects to do stuff within. I’ll assume that you’ll know how to handle all that and we’ll assume you have a Project model already.

What you will need is to add a foreign key to your table (projects in this example) that references Account. So, make sure that your model has an account_id attribute with and that the database table column has an INDEX.

We’ll add our associations in the models so that they can reference each other.

  # app/models/account.rb
  class Account < ActiveRecord::Base
    has_many :projects
    # ...
  end

  # app/models/project.rb
  class Project < ActiveRecord::Base
    belongs_to :account
    # ...
  end  

Okay great… back to our controllers. The SubdomainAccounts module provides you with the current_account variable, which you can use within your controllers/views. This allows us to do the following in our controllers. For example, if we had a ProjectsController.

  class ProjectsController < ApplicationController
    def index
      @projects = current_account.projects.find(:all)
    end

    def new
      @project = current_account.projects.new
    end 

    def show
      @project = current_account.projects.find(params[:id])
    end

    # ...
  end

See, this wasn’t so hard, was it?

Handling layouts

I wanted to highlight one other thing here because I suspect that most projects that fit this will likely need a promotional/resource site where people will sign-up from. In our application, we have two application layouts. One for the main application that customers will interact with via their subdomain and the promotional site layout.

The default layout is just app/views/layouts/application.html.erb and we have our promotional site layout at app/views/layouts/promo_site.html.erb. A few of our controllers are specifically for the promotional site while the rest are for the application itself and in some cases, there is some overlap down to individual action within a controller.

What we did was add a few more before filters to our application controller to a) define the proper layout to render, and b) skip login_required on the promo site.

To have the proper layout get rendered, we’re just checking whether the current request was made to the promotional site or not.

class ApplicationController < ActionController::Base
  # ...
  layout :current_layout_name # sets the proper layout, for promo_site or application

protected

  def promo_site?
    account_subdomain == default_account_subdomain
  end

  def current_layout_name
    promo_site? ? 'promo_site' : 'application'
  end

  # ...
end

Our application is using Restful Authentication and we just want to check to see if the current request is made to the promotional site or not. If it is, we’ll skip the login_required filter. Let’s assume that you have the following before_filter set.

class ApplicationController < ActionController::Base
  # ...
  before_filter :login_required

We’ll just change this to:

class ApplicationController < ActionController::Base
  # ..
  before_filter :check_if_login_is_required 

  protected
    def promo_site?
      account_subdomain == default_account_subdomain
    end

    def current_layout_name
      promo_site? ? 'promo_site' : 'application'
    end    

    def check_if_login_is_required
      login_required unless promo_site?
    end

    # ...

There we go. We can now render the proper layout given the request and only handle authentication when necessary.

Development with account subdomains

When you begin developing an application like this, you need to move beyond using http://locahost:3000 as we need to be able to develop and test with subdomains. You can open up your /etc/hosts (within a Unix-based O/S) file and add the following.


127.0.0.1 purplecowapp.dev
127.0.0.1 green.purplecowapp.dev
127.0.0.1 sample.purplecowapp.dev
127.0.0.1 planetargon.purplecowapp.dev
127.0.0.1 lollipops.purplecowapp.dev
127.0.0.1 help.purplecowapp.dev
127.0.0.1 support.purplecowapp.dev

After you edit that file (with root permissions), you can flush your dns cache with dscacheutil -flushcache (Mac OS X). This will let you make requests to http://purplecowapp.dev:3000/ and http://green.purplecowapp.dev:3000. This is a convention that our team has begun using for our own projects (TLD ending in .dev). It’s important to remember that the subdomain must be specified here in order to work for local requests. Unfortunately, hosts files don’t support wildcards (’*’).

Update

You can also use Ghost, which is a gem for managing DNS entries locally with Mac OS X. Read Get to know a gem: Ghost

Summary

I know that I glossed over some sections, but was hoping that the code itself would be the most beneficial for you. Feel free to leave any questions and/or provide some feedback on our approach. Perhaps you have some suggestions that I could incorporate into this so that we can improve on this pattern.

1 yeah, I’ve been reading more Seth Godin recently…

Older posts: 1 2 3