Using Active Record in the Console

I’ve just watched this Screencast by Ryan Bates and thought I’d have a play around with the new version of Active Record (An ORM) that will ship with Rails 3.

I’m going to be using mainly the console. The console is really useful for accessing your app without having to use a web front end. Lots of people think that it is only available in Rails, but its actually available with any Ruby installation using IRB (Interactive Ruby). All you need to do is pass the -r option, followed by the ruby file you want to load:

irb -r main.rb

This will then give you access to all the methods in the main.rb file as well as being able to interact with the database if one is defined in main.rb.

To demonstrate this I’m going to create a database of Manchester United football team players. First of all I need to install the latest version of Active Record. This is a pre-release and has some dependencies that also need instaling. Entering the following code in the console does the trick:

sudo gem install tzinfo,memcache-client,arel,activerecord --pre

Now I need some code that will set the database up and fill it with some data. Here is the code in full:

require 'rubygems'
require 'sinatra'
require 'active_record'

configure do

  ActiveRecord::Base.establish_connection(
      :adapter => 'sqlite3',
      :database => 'manutd.sqlite3')

  begin
    ActiveRecord::Schema.define do
      create_table :players do |t|
        t.string :name
        t.string :position
        t.string :country
        t.integer :goals
      end
    end
  rescue ActiveRecord::StatementInvalid
    # Do nothing - schema already exists
  end

class Player < ActiveRecord::Base
  scope :foreign, where("country != ?", "England")
end

Player.create(:name=>"Edwin Van Der    Sar",:position=>"goalkeeper",:goals=>0,:country=>"Netherlands")
Player.create(:name=>"Gary Neville",:position=>"defender",:goals=>7,:country=>"England")
Player.create(:name=>"Patrice Evra",:position=>"defender",:goals=>2,:country=>"France")
Player.create(:name=>"Owen Hargreaves",:position=>"midfielder",:goals=>2,:country=>"England")
Player.create(:name=>"Rio Ferdinand",:position=>"defender",:goals=>7,:country=>"England")
Player.create(:name=>"Wes Brown",:position=>"defender",:goals=>4,:country=>"England")
Player.create(:name=>"Michael Owen",:position=>"forward",:goals=>9,:country=>"England")
Player.create(:name=>"Anderson",:position=>"midfielder",:goals=>1,:country=>"Brazil")
Player.create(:name=>"Dimitar Berbatov",:position=>"forward",:goals=>26,:country=>"Bulgaria")
Player.create(:name=>"Wayne Rooney",:position=>"forward",:goals=>131,:country=>"England")
Player.create(:name=>"Ryan Giggs",:position=>"forward",:goals=>152,:country=>"Wales")
Player.create(:name=>"Ben Foster",:position=>"goalkeeper",:goals=>0,:country=>"England")
Player.create(:name=>"Ji-Sung Park",:position=>"midfielder",:goals=>15,:country=>"South Korea")
Player.create(:name=>"Paul Scholes",:position=>"forward",:goals=>149,:country=>"England")    
end  

get '/' do
team = Player.all.inject("<ul>\n"){|output,p| output << "<li>" + p.name + "</li>\n"}
team + "</ul>"
end

There’s a lot of code here, but the majority of it just creates the data. The important bit is here:

configure do

  ActiveRecord::Base.establish_connection(
      :adapter => 'sqlite3',
      :database => 'manutd.sqlite3')

  begin
    ActiveRecord::Schema.define do
      create_table :players do |t|
        t.string :name, :null => false
        t.string :position
        t.string :country
        t.integer :goals
        t.timestamps
      end
    end
  rescue ActiveRecord::StatementInvalid
    # Do nothing - schema already exists
  end


end

This sets up a database for using Active Record. The first part creates a new database using Sqlite. In this case it will be a file called ‘manutd.sqlite3’:

  ActiveRecord::Base.establish_connection(
      :adapter => 'sqlite3',
      :database => 'manutd.sqlite3')

The next part creates a table called players with the table rows representing properties of the Player model:

    ActiveRecord::Schema.define do
      create_table :players do |t|
        t.string :name
        t.string :position
        t.string :country
        t.integer :goals
        t.timestamps
      end
    end
  rescue ActiveRecord::StatementInvalid
    # Do nothing - schema already exists
  end

I’m going to put the name and position of each player into the database as well as the country they play for and how many goals they have scored for Manchester United.

After this there are lots of lines creating each player.

At the end is a Sinatra handler that I’ll discuss at the end. In fact you don’t actually need Sinatra to interact with the database at all, the following lines at the beginning are only needed if you plan to make this into a web app:

require 'rubygems'
require 'sinatra'

Let’s see what Active Record is made of:

Player.all

This will produce an array of all the players, which is a bit too long, so instead, let’s test out the count method:

Player.all.count
=> 14

Now let’s try some queries. How many English players are there?

Player.where(:country => "England").count
=> 8

Who are the goalkeepers?

Player.where(:position => "goalkeeper")
=> [#<Player id: 1, name: "Edwin Van Der Sar", position: "goalkeeper", country: "Netherlands", goals: 0>, #<Player id: 12, name: "Ben Foster", position: "goalkeeper", country: "England", goals: 0>]

Who is the top goalscorer?

Player.order(:goals).last
=> #<Player id: 11, name: "Ryan Giggs", position: "forward", country: "Wales", goals: 152>

Who are the top three goalscorers?

Player.order("goals desc").limit(3)
=> [#<Player id: 11, name: "Ryan Giggs", position: "forward", country: "Wales", goals: 152>, #<Player id: 14, name: "Paul Scholes", position: "forward", country: "England", goals: 149>, #<Player id: 10, name: "Wayne Rooney", position: "forward", country: "England", goals: 131>]

What is the average number of goals scored?

Player.average(:goals).to_i
=> 36

What is the average number of goals for English players?

Player.where(:country=>"England").average(:goals).to_i
=> 38

To get the players that are not English is a bit verbose:

Player.where("country != ?", "England")

So for that reason I created a named scope:

class Player < ActiveRecord::Base
  scope :foreign, where("country != ?", "England")
end

This means I can easily find out the average goals scored by all of the non-English players:

Player.foreign.average(:goals).to_i
=> 32

And there you have it - a nice way to play around with your database. I was quite impressed with the new way of performing method chaining in Active Record, although it seemed a lot like what DataMapper already does. You can read more about the new methods over at Pratik’s blog.

Finally I decided to just show that this is easy to turn into a web app with a simple handler at the end. This is a little bit of Ruby that finds all the players and puts them into a list.

get '/' do
team = Player.all.inject("<ul>\n"){|output,p| output << "<li>" + p.name + "</li>\n"}
team + "</ul>"
end

If you point your browser at ‘http://localhost:4567’ after starting the server, you should see all the players.

blog comments powered by Disqus