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.

Railsconf was pretty sweet ...

Posted by Phantom Mongoose Thu, 24 May 2007 12:21:00 GMT

… and I learned a lot.

I believe I have already forgotten more than I’ve retained. A lot of genuinely interesting information locked in a life-and-death struggle to be crystallized with what meager neurons are available to do any meaningful processing.

Things I know for sure:

  1. Rails 2.0 sounds neat
    1. it will have a cool debugger
    2. it will give you a lot of stuff for free if you don’t mind its REST implementation or REST in general.
  2. The kids love memached
  3. The Rails community (myself included) is comprised of arrogant bastards.
  4. Thinking for yourself is a good idea
  5. Smalltalk is neat in a weird way.
  6. I need to get better acquainted with TDD
  7. By keeping clean and simple, the latest site we’ve developed for our customers has a mobile version of the fancy ajax application … for practically free. Rails makes this possible and practical.
  8. The Rails community has a higher percentage of Macs then the Developer community as a whole, but a lower percentage of women. Why are nice laptops and breasts mutually exclusive? Correlation == Causation as everybody with half a brain will tell you. Don’t believe those bleeding heart libtards who taught Statistics / Psychology / Sociology 101. A great example of an Arc Mass1.
  9. zefrank made an awesome programmer joke:
    bq. “There is nothing to fear but fear itself. Come on—you guys are programmers … that’s recursion … infinite fear!”

Things I think I know:

  1. Dave Thomas seems to think that things are only cyclical in software development, as opposed to the entire universe.
  2. Some people are getting into Rails because of the buzz; not because they love Ruby—I highly suggest everyone program in the language they love to program in.
    1. They’ll be happier
    2. They’ll be more productive
    3. Whatever that language is, I’m sure you’ll find a very similar web framework, as no one ignores a good idea.
      1. Perl:
        1. Catalyst
      2. Python:
        1. TurboGears
        2. Pylons
        3. Django
      3. PHP:
        1. Too many to list.
      4. Etc.

Things I think:

  1. Sam Ruby needs to change his name or his field. When I’m googling programming endeavors and Ruby, I mean the language; not this fellow who I am sure is not out to get me. Yet.
    1. Even worse he has started to dabble in Ruby (the language).
    2. That reminds me I need to invent a language called Mongoose and a unix shell called mosh.
  2. The internet …
    1. … needs to be saved
    2. … with hate
    3. … and you can help here.
  3. I need to follow up and finish my search for the NAS quest.

1 That is an anagram for sarcasm, you sly pin loser. Stop losing those pins.