Starting Sequel

We're going to take a bit of a detour here from our Ramaze studies to learn about Sequel. We'll use Sequel in future Ramaze posts for hooking up to a database, but as normal, I want to strip this down to the bare bones. First, you'll need to install two things, the database sqlite and the gem for Sequel. You can get sqlite here and then install Sequel by doing a gem install sequel. Once you've done that, you should be able to run the following code (an update of the example code on the Sequel site):

require 'rubygems'
require 'sequel'

# Open the sqlite database countries.db if it exists
# or create it if it doesn't
DB = Sequel.sqlite("countries.db")

# Check if the countries table exists in the database
# and create it if it doens't. Add a primary key "id"
# and two other columns name and population. The name
# is a text field and must be unique while the population
# is an integer field.
if !DB.table_exists? :countries
    DB.create_table :countries do # Create a new table
        primary_key :id
        column :name, :text, :unique=>true
        column :population, :integer
    end

else
    puts "Countries database already exists."
end

# Create a new dataset with the countries and populations that are
# currently in the database.
countries = DB[:countries] 

# Populate the table with some countries and fake populations initially.  If we
# can't add them (we get an error), assume that they've already been added.
begin
    countries << {:name => 'U.S.A',  :population => 250000000}
    countries << {:name => 'Mexico', :population => 251000000}
    countries << {:name => 'Canada', :population => 252000000}
rescue
    puts "Countries already added to database."
end

# Print out the initial countries/population in 
# reverse order (i.e. largest to smallest).
puts "Initial countries / population"
countries.reverse_order(:population).print

# Input additional country population pairs separated by white space. You can
# type "exit" or "quit" to stop inputting the pairs. There is no error checking
# here so don't make any.
puts "Put in country / population pairs separated by white space. Type exit or quit to stop."
print "country population >> "
while ((line = gets.chomp!) !~ /(exit|quit)/i) do
    begin
        country, population = line.split
        countries << {:name => country, :population => population}
        print "country population >> "
    rescue
        # There was an error here. This will happen if there's a 
        # duplicate country for one thing.
        puts "Could not add country/population"
    end
end

# Print out the number of countries currently
# in the database.
puts "Country count: #{countries.count}"

# Print out the records in descending order by population.
countries.reverse_order(:population).print

# Print out the average population.
puts "The average population is: #{countries.avg(:population)}"

I've tried to document the code so that it's easily understandable. We first open (or create) the countries.db database and add a table with three columns, id, name, and population. Then we open a dataset where we can add our country/population data and then add three countries and populations (I have no idea how close or far these numbers are). If we get an error, we simply print the fact that we've already populated the table (the most likely error). After that, we print out everything in the database in order of largest to smallest population. We then allow the user to add their own countries/populations (as many as they like) followed by an "exit" or "quit". Finally we print out the number of countries and their populations largest to smallest again.

In the next post, we'll use the Sequel model to map ruby code to database tables.

Let me know of any questions.