31 August 2010

Mindful Reading

Sometimes, while Bob is reading, a word, a sentence, or an idea on the page will trigger a memory, or a fantasy, or another idea, and quickly Bob's mind is lost in a chain-reaction of these distractions. This might be a minor irritation, but the text-tracking portion of Bob's brain keeps running, on auto-pilot, following words, sentences, paragraphs, even whole pages, while the rest of his brain is away on holiday.

All of a sudden Bob remembers that he was supposed to be reading, it's why he's there after all, and he wanted to do it. But now it takes energy to read backwards and identify the point where his consciousness took the wrong turn, off the page. Then it takes even more energy to re-read the subsequent text, text that his tracker thinks it has already read.

Poor Bob. Lucky for him, he's beginning to practise mindfulness, having been inspired by Mindfulness in Plain English, a short book describing the technique. Mindfulness is the habit of being aware of your immediate environment at all times, including most importantly being aware of what's going on inside your head.

Bob's able to catch himself sooner when a distraction arises, and is able to consciously decide whether to return to the page, or stop reading and pursue the distraction instead (and maybe even write a blog entry about it). Now, he's beginning to notice that he gets more read in less time, and the experience of reading is more satisfactory.

I'll let you know how it goes for him.

27 August 2010

Active Record objects backed by Database Views

"Views" are a powerful feature of relational databases - a view presents the result of a query as if it was a simple table. You can select from a view or join it in another query. You can even use a view in the definition of another view. Using a view, you can hide some nasty, complicated, but useful SQL behind what appears to be a plain old table.

Here's a simple example, using mysql:

CREATE TABLE users (id integer primary key,
                    username varchar(16) not null,
                    crypted_password varchar(128) not null,
                    etc etc);

CREATE TABLE posts (id integer primary key,
                    user_id integer not null,
                    content text);

Suppose you like to keep track of posts-per-user, but you want users.username, not users.id in your results, because you are not the database and you like humanly recognisable names.

SELECT select u.username AS username, 
       (select count(*) from posts p where p.user_id = u.id) as total 
  from users u;

Depending on your specific needs, you might tack on "order by total desc limit 10" or "where u.username = 'me_again_999". It's an awful lot of stuff to type each time. Try this instead:

CREATE VIEW post_count AS
     select u.username AS username, 
            (select count(*) from posts p where p.user_id = u.id) as total
       from users u;

And now:

mysql> select * from post_count where username = 'me_again_999';
+--------------+-------+
| username     | total |
+--------------+-------+
| me_again_999 |    16 |
+--------------+-------+


mysql> select * from post_count order by total desc limit 3;
+--------------+-------+
| username     | total |
+--------------+-------+
| verbose      |   256 |
| eloquacious  |    64 |
| me_again_999 |    16 |
+--------------+-------+

Easier, no?

Now, the cool thing is that this works no matter how ugly, incomprehensible, or complicated your query is. And the über-cool thing is: you can create a Rails model on top of your view, ActiveRecord doesn't care that it's not a table.

We need a view that contains user_id instead of username so that active_record can make the join:

CREATE VIEW `post_count` AS 
      select u.id AS user_id, 
             (select count(*) from posts p where p.user_id = u.id) as total
        from users u;

class PostCount < ActiveRecord::Base
  belongs_to :user
end

class User < ActiveRecord::Base
  has_one :post_count
end

And now:

User.all.each { |u| puts "#{u.username} #{u.post_count.total}"}
verbose 256
eloquacious 64
me_again_999 16

So is that über-cool or what?