Phantom Mongoose

ActiveRecord, Mysql Views, and You.

Posted by Phantom Mongoose Thu, 07 Feb 2008 09:19:00 GMT

It’s no secret that with Ruby on Rails you can change the database connection an individual model uses.

Why would you want to do this? As on example say that you have two applications that you want to use a single sign-on. You could set your user model for one of the applications to point at the other applications database. This way both applications are using the same set of users, but all of their other tables are separated into their individual databases.

This works for simple cases, but in more complex cases, it may get difficult to manage. And I don’t think Rails will be able to optimize more complex queries due to the different models having different connections.

So what’s a better way to handle more complex situations?

SQL views is the answer, which Mysql 5 supports. At its simplest level, a view can be considered an alias. Consider:

    CREATE VIEW sessions
    AS SELECT * 
    FROM other_database.sessions

This creates a view in the your database which is for all intents and purposes an alias to the sessions table in other_database.

You can select from sessions, you can update sessions, you can insert into sessions, etc.

But there is more to views this simple case. Consider this contrived example:

forum_database

     user model ( has_one profile )
     id, login, password

     profile model ( belongs_to user )
     id, first_name, last_name, user_id

other_database

     CREATE VIEW users
     AS SELECT u.id, login, password,
     COALESCE(CONCAT(p.first_name, " ", p.last_name),login) as display_name
     FROM forum_database.users u
     LEFT OUTER JOIN forum_database.profiles p
     ON u.id = p.user_id

So what will that view do? It’ll act like a table with the columns id, login, password, and display name. The display name will either be first name plus space plus last name like ‘Mister T’ or it’ll be their login name if they don’t have a profile.

The outer join will select all users, regardless of whether they have a profile, concat always returns null if any of the items is null, and coalesce returns the first non null item.

Some complex problems can be solved easily by handing off the problem to a specialized service you are already using. In this case, we let Mysql do the heavy lifting.