ActiveRecord, Mysql Views, and You.
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.sessionsThis 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_idother_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_idSo 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.
