Using Ramaze and Sequel Together

In this post, we'll begin to use Ramaze and Sequel together. This is based, as is usual, on a previous post Using a Simple Model, but here we'll actually use a database backed model with Sequel. We'll start out by creating a database using a Sequel migration which was discussed more fully here. I've put the migration in a subdirectory called dbMigration. Here's the code:

# dbMigration/001_LoginMigration.rb
# Run:
# sequel -m dbMigration -M 1 sqlite://accounts.db
# from the top level directory to create the :accounts table with login and
# password columns.
#
class CreateAccountsTable < Sequel::Migration

    # For the up we want to create the three tables.
    def up
        # Create the accounts table with a primary key and a title.
        create_table(:accounts) do
            primary_key :id
            String :login, :unique=>true 
            String :password
        end
    end

    # For the down we want to remove the accounts tables.
    def down
        drop_table(:accounts)
    end
end

To create simply run:

sequel -m dbMigration -M 1 sqlite://accounts.db

which should create a database accounts.db in the main directory with a table accounts with columns for a login and password. Here we're going to cheat just a bit (OK, quite a bit) to keep things simpler. Normally, in an application like this one, we'd have a registration page that would allow us to create new accounts (and we'll probably add that in a future post), but here we're just going to put a new account in using some code. Here's the dbload.rb file that puts in a single account with loginid "hello" and password "world":

# dbload.rb
#
# Run this file after the running the database migration
# to create a login/password. This is a quick hack since we
# don't have a page to add them with.
require 'rubygems'
require 'sequel'
DB = Sequel.sqlite("accounts.db") # Open the accounts database
require 'models/account'
Account.create(:login => 'hello', :password => 'world')

Now let's look at start.rb which starts things off. In this version of our code, I've moved the controller into its own directory in the same way that the models and views have their own directories. Here's the code:

# start.rb
#
# This is the main program for the example. It loads the Sequel database,
# loads the controller and model, and then starts up Ramaze. 
#
# The database should have been set up using the database migrations in
# the dbMigration directory. Since there's currently no way to add login/password
# pairs, we're going to cheat and run the dbload.rb file which will create a 
# login "hello" with a password "world". Normally, this would be handled with a 
# registration page.
require 'rubygems'
require 'ramaze'
require 'sequel'

# Open the accounts database. This must be done before we access the models
# that use it.
DB = Sequel.sqlite("accounts.db") 

# Load the controllers and models (one of each in this case).
require 'controllers/main_controller'
require 'models/account'

Ramaze.start

This is all pretty straight forward. The only mildly tricky part is that we need to open the database before we bring in the model. The database we open is the one we created with the database migration and loaded with the dbload. Finally, as is normal, we start Ramaze.

Our model is very simple. It only contains the Account which is derived from the Sequel::Model. Here's the code:

# models/account.rb
#
# This is the model for the Account and is backed by the :accounts table in the
# database. For this simple example we don't need anything but the definition.
require 'rubygems'
require 'sequel'

# Create the Account model. 
class Account < Sequel::Model 
end

The controller class in this example is where all of the real work is done. We still have our two methods index and logged_in from before. The index logs a user into the system and the logged_in happens after the user is authorized. Before either of these though is some new code that is used to protect our pages from users that are not logged in. In our earlier examples, you could actually go to the logged_in page without having been authorized. What the new code, helper aspect / before, does is prevent that. We check the session variable for a loginID not equal to nil and redirect to the index (login) page if it is nil. If it's not, we'll just continue on with normal processing. This code happens before the logged_in page is called. If we add additional pages, they can be protected by simply adding them in the before() list. There is also an equivalent after call that can be used in a similar way as well as before_all and after_all that can be used without a list of methods. Here's the controller:

# controllers/main_controller.rb
#
# This example is based on the previous "Using Models" example.  It has two
# methods (index and logged_in). The index method will take data from a form
# and call the Account.find method. If the Account.find method returns
# something (we aren't actually going to use what's returned), we will set the
# session variable, and redirect to the logged_in method.  If it returns false
# we will set the flash variable and stay on the index page.
class MainController < Ramaze::Controller
    # Use page.xhtml in the view directory for layout
    layout :page

    # Set up a helper to check if we're logged in and only allow access
    # to the :logged_in page if we are. This is probably the hard way to 
    # do this for only the single page but will make much more sense if 
    # we add more pages as we'd do in a real application.
    helper :aspect
    before(:logged_in) { 
        unless session[:loginID]
            # Set the flash message which will only be available in the next
            # screen. In this case that will be the logged_in screen.
            flash[:message] = "You must log in before accessing the requested page."
            redirect Rs(:index)
        end
    }

    # You can access it now with http://localhost:7000/
    # This should display a form with a login and a password as 
    # well as a "Login" button in your browser.
    def index
        # Make sure we're getting here from a post request.
        if request.post?
            # Check the login and password.
            # if we find the Account based on the login and password. If we find it
            # we'll save the login ID in the session variable and we can use that
            # to show if the Account is currently logged in or not. If we can't
            # find the Account, we'll set the flash message, set the session to nil
            # and just stay on this page.
            if Account.find(:login => request[:loginID], :password => request[:pw2])
                # Use the name= portion of the input form to grab the data
                # from the request variable and save it in the session
                # hash table.
                session[:loginID] = request[:loginID]

                # Redirect to the logged_in screen.
                redirect Rs(:logged_in)
            else
                # The login could not be authorized. Set the flash message
                # and stay on this page (index/login). Set the session loginID 
                # to nil also. This will effectively log the user out. This would 
                # be reasonable if they are logged in and then try to log in with
                # a new login/password.
                flash[:message] = "Incorrect password, please try again!!!"
                session[:loginID] = nil

                # Stay on the login page.
                redirect Rs(:index)
            end
        end
    end

    # Set the login ID variable so we can issue a 
    # welcome message on the logged_in page.
    def logged_in
        @loginID = session[:loginID]
    end
end

The only other interesting thing in the controller is the use of the model Account to determine if an account exists for a given login/password. We use the find method and pass in the values from the form, as we've done in previous posts, to check if this is a valid account. If it is, we set the session variable with the loginID and redirect to the logged_in page. This will trigger the before check that we discussed earlier. If we could not find this user (and in this case there will only be one), we set the flash variable and redirect back to the index page to let the user try again. We also set the session/loginID value to nil here to clear it out.

Here's the view pages starting with view/page.xhtml:

<html>
    <head> <title>Using Sequel and Ramaze Together</title> </head>
    <body>
        #@content
        <h5> Powered by Ramaze </h5>
    </body>
</html>

Here's the view/index.xhtml which contains our login form:

#{flashbox}
<form id="login" method="post">
    <div>
        <!-- for= goes with id=, the name= is placed in the request variable. -->
        <label for="nick">Login:</label>
        <input id="nick" name="loginID" type="text" />
        <br/>
        <label for="pw1">Password:</label>
        <input id="pw1" name="pw2" type="password" />
        <br/>
        <input type="submit" value="Login" />
    </div>
</form>

Finally, view/logged_in.xhtml which shows a welcome message based on the loginID set in the logged_in method of the main controller:

#{flashbox}
Welcome to Our Site: #@loginID

As always, please post your questions and comments.

Sequel Migrations

Migrations are one of the nicer things in the database world these days. I first saw migrations in ActiveRecord (Ruby on Rails) and was completely blown away by what an elegant solution they were. Sequel also has migrations and we'll take a look here at how to use them in the very simplest cases. To run these, you're going going to need to have Sequel version 2.10 or later. To find out what version you're using do a sequel --version. If you're below 2.10, do a sudo gem update sequel (leave out the sudo on Windows) to get the latest version which is currently 2.11.0. The other thing you're going to need is the SQLite Manager Firefox plugin (assuming you're using sqlite and Firefox. If not, you're on your own here.). You can get this plugin here. You don't strictly need this, but it will help to check to make sure everything works as advertised.

We'll use the book/author example from our previous post. The first file will be called 001_BookAuthormigration.rb. It is important that the file start with a number (the migration number) and have an "_" after the number. This is how sequel will know what to migrate. Here's the file:

#
# Create three tables, the books table, the authors table, and the authors_books table.
#
class CreateBooksAuthorsTable < Sequel::Migration

    # For the up we want to create the three tables.
    def up
        # Create the books table with a primary key and a title.
        create_table(:books) do
            primary_key :id
            String :title, :unique=>true 
        end

        # Create the authors table with a primary key and a name.
        create_table(:authors) do
            primary_key :id
            String :name, :unique=>true 
        end

        # Create the authors_books table with a primary key and two foreign keys.
        create_table(:authors_books) do
            primary_key :id
            foreign_key :book_id, :books
            foreign_key :author_id, :authors
        end
    end

    # For the down we want to remove the three tables.
    def down
        drop_table(:books)
        drop_table(:authors)
        drop_table(:authors_books)
    end
end

There are two methods in the class, an up and a down. When we want to add this particular migration, we call the up method and when we want to remove a migration, the down method. Here's how we would run the first migration:

sequel -m . -M 1 sqlite://bookauthor.db

the "-m ." says to run the migration on the local directory and the "-M 1" says to go to migration 1. The final piece, sqlite://bookauthor.db says to use the sqlite database and call it bookauthor.db. If you set this up correctly and then run the sequel command, you should be able to go to Firefox/Tools/Sqlite Manager and then open the bookauthor.db. In the window you should see under tables, the three tables we created (there's also two more that we didn't) and each of the tables should have the correct columns in them.

Assuming the above worked for you, you can then create the second migration, 002_BookAuthorMigration.rb

#
# Add an ISBN column to the books table
#
class AddISBNColumn < Sequel::Migration
    # For the up, alter the table to add the isbn number.
    def up
        alter_table :books do
            add_column :isbn_number, String
        end
    end

    # For the up, alter the table to remove the isbn number.
    def down
        alter_table :books do
            drop_column :isbn_number
        end
    end
end

This uses the alter_table and add_column to add an ISBN number to the books table. To add this to the database, run the following

sequel -m . -M 2 sqlite://bookauthor.db

If you still have the Sqlite Manager open, simply hit the refresh button and then you should see the new ISBN column in the books table.

Finally, if you decide that you don't want the ISBN column again and need to roll it back, then just rerun the

sequel -m . -M 1 sqlite://bookauthor.db

command and it will run the drop method in the 002_BookAuthorMigration.rb file to remove the column and roll back the database to our version 1.

Once again thanks to the sequel-talk mail list in general and Jeremy Evans in particular for helping me work through all of this. Let me know if you have questions or comments. It looks like I'm going to be going to the LA Ruby Conference, so if you're also going, be sure and say "hi".

Sequel Models many_to_many / many_to_many

In our last post yesterday, we discussed Sequel one_to_many/many_to_one models. In this post we'll take a look at many_to_many / many_to_many models. You would use this in cases (as we will here) where an author can have many books and a book can have many authors. Another example is in a school example where a teacher would have many students and a student would have many teachers (at least past elementary school here in the U.S. YMMV). Here's some code for a book/author example:

require 'rubygems'
require 'sequel'

DB = Sequel.sqlite # Create an in-memory database

# Create the books table with a title only.
DB.create_table :books do 
    primary_key :id
    column :title, :text, :unique=>true
end

# Create the authors table with the author's name.
DB.create_table :authors do
    primary_key :id
    column :name, :name
end

# Create the table where we'll cross reference authors and books. This
# has foreign keys going both directions and these reference both the 
# ID and the table name.
DB.create_table :authors_books do
    primary_key :id
    foreign_key :book_id, :books
    foreign_key :author_id, :authors
end

# Create the Book model. Note the plural in the 
# many_to_many "authors".
class Book < Sequel::Model
    many_to_many :authors
end

# Create the Author model. Note the plural in the 
# many_to_many "books".
class Author < Sequel::Model 
    many_to_many :books
end

# Is this needed? No it's not, but it won't hurt anything either.
# class Author_Book < Sequel::Model; end

# Create a couple of books (odd that we'd use C/Unix books and not Ruby, but at
# least it's not Rails ;-)) ...
cProgrammingLanguage = Book.create(:title => 'The C Programming Language')
unixProgrammingEnvironment = Book.create(:title => 'Unix Programming Environment')

# and some authors
kernighan = Author.create(:name => 'Brian Kernighan')
ritchie = Author.create(:name => 'Dennis Ritchie')
pike = Author.create(:name => 'Rob Pike')

# We only need to do one of these (add authors to books) ...
cProgrammingLanguage.add_author(kernighan)
cProgrammingLanguage.add_author(ritchie)
unixProgrammingEnvironment.add_author(kernighan)
unixProgrammingEnvironment.add_author(pike)

# or these (add books to authors). If you do both, they will appear twice below.
#kernighan.add_book(cProgrammingLanguage)
#ritchie.add_book(cProgrammingLanguage)
#kernighan.add_book(unixProgrammingEnvironment)
#pike.add_book(unixProgrammingEnvironment)

# We can get either a dataset ...
cProgrammingLanguageAuthors = cProgrammingLanguage.authors_dataset
cProgrammingLanguageAuthors.each {|author| puts "C Programming Language Author: #{author[:name]}"}
brianKernighanBooks = kernighan.books_dataset
brianKernighanBooks.each {|book| puts "Brian Kernighan Books: #{book[:title]}"}

# or arrays.
authors = cProgrammingLanguage.authors
authors.each {|author| puts "C Programming Language Author: #{author[:name]}"}

books = kernighan.books
books.each {|book| puts "Brian Kernighan Books: #{book[:title]}"}

We start out creating our database, once again just using an in-memory database rather than persisting it. We then create book and author tables with title and name respectively. Finally, we create a cross reference table that links books to authors and vice versa. Here the name of the table must be the name of the two tables alphabetically listed with an underscore between. It will have foreign keys for both books and authors using the id and the name of the table (as Jeremy Evans from Sequel-Talk noted, the table name isn't strictly necessary for sqlite, but will be for other databases, so go ahead and put them in. I didn't in yesterday's example). After the tables are created, we create the models for the books and authors (note the singular in the class name compared to the plural in the table name). In these, we put the many_to_many tag to link the two. We can also create a model for hte Author_Book, but it's not necessary and here I've shown it but left it commented out.

Now we can go ahead and start using the models. We'll create a couple of books (classics I might add), the C Programming Language and the Unix Programming Environment). Then we create the authors for these books, Brian Kernighan, Dennis Ritchie, and Rob Pike. Finally, we add the authors to the books. Note we could have just as easily added the books to the authors to the same end. Now, we can get the books for each author or the authors for each book either as a dataset (which means we can filter using the techniques from our first post on Sequel or just as an array.

Once again thanks to the Sequel-Talk group. Let me know if you have questions or comments on anything here.

Sequel Models many_to_one / one_to_many

In our last post, we discussed simple Sequel models. In this post, we'll talk about and show some more complex models using a many_to_one / one_to_many relationship. Here, we're going to revisit the countries we used earlier and add cities to the countries. In this case a country can have many cities, but a city will belong to only a single country. Other examples of this many_to_one / one_to_many relationship are customers and orders (a customer might have many orders, but an order will belong to only one customer) or car models and car makers (a car maker will make many models, but each model will belong to a single car maker). Here's some code illustrating the one_to_many / many_to_one relationship using countries and cities and sequel models.

require 'rubygems'
require 'sequel'

DB = Sequel.sqlite # Create an in-memory database

# Create a new Country table with columns of
# id, name, and population.
DB.create_table :countries do 
    primary_key :id
    column :name, :text, :unique=>true
    column :population, :integer
end

# Create a cities table that links to the 
# country with a foreign_key
DB.create_table :cities do
    primary_key :id
    column :name, :text
    foreign_key :country_id
end

# Create a Country model stating that we'll have many cities.
class Country < Sequel::Model; 
    one_to_many :cities
end

# Create a City model stating that a city will belong to one country.
class City < Sequel::Model
    many_to_one :country
end

# Create the USA country.
usa = Country.create(:name => 'U.S.A.', :population => 250000000)

# Create two cities, NY and LA.
ny = City.create(:name => 'New York')
la = City.create(:name => 'Los Angeles')

# Add the cities to the USA.
usa.add_city(ny)
usa.add_city(la)

# Get all of the cities in the US as a dataset. If we do this then
# we have options for filtering.
usaCities = usa.cities_dataset

# Put out each city from the dataset.
usaCities.each{|city| puts "City #{city[:name]}" }

# Or it can be done as an array.
usa.cities.each{|city| puts "City #{city[:name]}" }

In this example, unlike our last one, we use an in-memory database rather than one that's permanent. Feel free to make this change if you like. Next we create our two tables, countries (the same as in our previous post) and a new one cities. The cities table has the primary_key :id, the name of the city, and a foreign_key, :country_id. This foreign key will point back to the :id of the countries table (all of this will be managed behind the scenes by Sequel). Next we'll construct models to match the tables. These should be named singular to the model's plural versions (countries/Country and cities/City). In the model, we need to add the one_to_many :cities in the Country model to let Sequel know that this relationship exists (one country / many cities). Also in the City model, we'll add many_to_one :country to express the opposite relationship. After this, we can go ahead and start using the models. Here, we've created a country, the USA, with two cities, New York and LA. Then we add the cities to the country. Finally, we can get the data back out again either as a dataset (which we looked at a bit in our last post) or as an array.

Next post we'll take a look at the many_to_many relationships. For now though, if you have questions, leave them in the comments.

Finally, I'd like to thank the folks on sequel-talk for helping me understand some of this and also the many_to_many relationships also. If you're at all interested in Sequel, I'd strongly recommend signing up, listening in, and learning from everyone there.