Friday, July 5, 2013

Office Quote Friday: Managing Expectations

My fellow employees have unusual standards. I present the following trio of genuine* quotations to illustrate this fact.

Quote the first, in which I very nearly solve a problem, and then choose not to. And in which that is regarded as impressive:
Designer, handing over an envelope: “Hey Walrus, how much do you think this weighs?”
Me: “How the heck should I know?”
Designer: “Just give me your best guess!”
Me, thinking aloud: “Ok... the internet says my phone weighs 4.8 ounces...”
CFO, shouting from next room: “Time for a new phone!”
Me: “My phone is definitely heavier than your envelope, so I’m going to say... less than 4.8 ounces.”
Designer: “That’s awesome!”

Quote the second, in which it is made clear that my employer has a unique view of employment:

Company President: “Man, it’s a beautiful day outside. What are we doing here inside?”
Me: “Working.”
Company President: “But why?”
Me: “Because you pay us.”
Company President: “Well, yeah, but I’d still pay you if you didn’t come in.”

Quote the third and last, in which high standards of intra-office communication are asserted and maintained:

Me: “Didn’t there used to be three monitors on this desk?”
CFO: “Yeah. [Researcher] took one.”
Me: “And... why did we let him do that?”
CFO: “Because I didn’t want to talk to him.”

*Certified 100% actually said by people I work with.

Wednesday, July 3, 2013

Fun with case statements and the splat operator

Hello and welcome back, presumed viewer. There's little evidence that anyone is reading this, but that's fine. That means no one noticed that I went six months without posting anything. Time now for my triumphant return! Ok, not really. It's just been a while since I found a code trick I thought was worth a post. So, check out this cool thing:

So, you're writing a case statement. But it many of the cases are actually the same:

case foo
when 'a'
when 'b'
when 'c'
when 'd'
when 'e'

This could clearly be a lot DRYer - there's actually only two cases here, one in which you do_something, and one in which you do_something_else. So you clean it up:

case foo
when 'a', 'b' ,'c'
when 'd', 'e'

Which is great. Except that 'a', 'b', and so on aren't actually static values. They're something that gets calculated in real time. Maybe they're entries from a database. Or the result of user input. Or something calculated by a method such staggering complexity that you've abstracted away into its own class, for the safety of all involved. The point is, you can't just write them into the code, because they're going to (potentially) be different every time it runs. So, how do you make one 'when' statement apply if the variable being switched on is any element of an array? Answer: Our old friend, the splat operator. I'm not actually sure if the ability to use it in this way is part of the improvements in Ruby 1.9, but it is definitely cool:

thing_keys = Model.where(:thingummy => true).pluck(:key)
unthing_keys = MagicLetters.generate(2,
case foo
when *thing_keys
when *unthing_keys

I told you it would be cool.

Wednesday, December 5, 2012

Hashes and Arrays in Rails and PostgreSQL

You probably already know that PostGreSQL supports array types (if not the docs are here). What you may not know is that it also supports hashes - that is, unordered collections of key-value pairs, thought the hstore extension. We make pretty heavy use of both data structures, both in our SQL and our Ruby code.
This brings up the difficulty - formatting of both types is significantly different in SQL than in Ruby, and if you happen to want to pass one from your Rails code into your database query, it's non-trivial to fix the formatting.
To address this, I've added a used an initializer in our app to monkey-patch a couple of helper methods into the Array and Hash types:


class Hash
  # Turn hash into the string that will duplicate it as an hstore if passed to ::hstore.
  # For now, this only handles keys which are underscored words and values which are numbers, and does not cope with nested hashes at all. It's fine.
  def to_hstore{|k,v| "#{k} => #{v}"}.join(', ')

class Array
  # Turn array into the string that will duplicate it as a sql array when passed to ::x[] for x matching it's type.
  def to_sql_array
    "{#{self.join(', ')}}"

These helpers turn the collection into their string representation in SQL:

> h = {:foo => 1.23, :bar => 2, :baz => 43, :quux => 0.01} => {:foo=>1.23, :bar=>2, :baz=>43, :quux=>0.01} > h.to_hstore => "foo => 1.23, bar => 2, baz => 43, quux => 0.01" 

> a = [:foo, :bar, :baz] => [:foo, :bar, :baz] > a.to_sql_array => "{foo, bar, baz}" 

Thus, it's easy to interpolate them into AREL:

User.where("username = magic_function_taking_hstore_argument(:hash_argument::hstore)", :hash_argument => h.to_hstore)

which executes:

SELECT "users".* FROM "users" WHERE (username = magic_function_taking_hstore_argument('foo => 1.23, bar => 2, baz => 43, quux => 0.01'::hstore))

Obviously, these are just a first effort, as they really only support a very specific format:

  1. Hstore keys must be strings without whitespace
  2. Hstore values must be strings without whitespace or numbers
  3. Array members must be all strings or all numbers
  4. Nested collections aren't supported
Additionally, when interpolating these values, the type of the array has to be specified in the query. But overall, this is still vastly easier and DRYer than making the conversion manually each time.

Friday, November 30, 2012

Office Quote Friday: Hostility

Designer: "We can't both be browsing [random web page]."
Programmer: "I'm gonna stab you in the forehead!"

CTO: "I meant that as a compliment, really."
Programmer: "Ah, you just don't want me to stab you."

First Programmer: "So that'll be fast?"
Second Programmer: "Yeah."
First Programmer: "Soon?"
Second Programmer: "Yeah."
First Programmer: "If it's not, we're going to feed you to the pigs."

Designer: "Geoff wants to teach my dog to Google."
Programmer: "I will kill you."

Programmer: "So suck it, Harvard people!"

First Programmer: "You should merge that code."
Second Programmer: "Only if I wanted like, three people to leap over and knife me in the face."

CTO: "I should be whacked in the head with a hammer. Do something useful."

Researcher: "Wanna use the conference room?"
CTO: "Check out the new chairs!"
Programmer: "Wait, I don't think you can sit in those chairs. You need to build your own."
Marketter: "Oh, I think we'll just sit in the ones you built"
Programmer: "No you won't! I have a boxcutter."
Marketter: "You gonna fight for the chairs?"
Programmer: "I will cut you!"
Programmer, in terrible Italian accent: "I'm just sayin', those chairs won't look so good with your blood on them."

CTO: "Hey Designer, have you got a minute to help me with a CSS problem?"
Programmer: "I just threw you under the bus. So you know."

Brogrammer: "Shoot you with a sword!"

First Programmer: "I hate working on this."
Second Programmer: "You're such a negative person."
<long pause>
First Programmer: "I will literally kill you."

First Programmer: "Why aren't we friends on Facebook?"
Second Programmer: "I'm not sure we're friends in real life."

First Programmer: "Could you punch the Designer for me?"
Second Programmer: -throws sandal at Designer-
First Programmer: "Yeah, ok, that works too."

Programmer: "I hate it when the Designer talks."

Wednesday, November 28, 2012

Escaping and Executing SQL in models

I constantly find that I would like access to ActiveRecord's ability to sanitize and execute arbitrary SQL  in model code. Most often, this occurs when a particular task is resource- and time-intensive in Ruby, but relatively simple to do directly in the database. For this reason, I find it immensely helpful to have the following helpers available.

I typically place them in an initializer file(/config/initializers/active_record.rb):

class ActiveRecord::Base
  def self.escape_sql(clause, *rest)
    self.send(:sanitize_sql_array, rest.empty? ? clause : ([clause] + rest))

  def self.execute_sql(conditions, *rest)
    self.connection.execute(escape_sql(conditions, *rest))

Now, I can use either of them, on any ActiveRecord model:

query = User.escape_sql("WHERE user_id = ? AND validated = ?",, true)


User.execute_sql("UPDATE posts SET comment_limit = :limit", :limit => 4)

Amazingly handy.

Wednesday, November 21, 2012

PostGreSQL: Initializing a counter cache

Rails's counter caches are conveniently easy to use. But that's not why we use them. We use them because it's efficient. When loading large numbers of Posts, say, knowing how many Comments each one has is darn useful, but loading all the comments, or counting the comments for each post, is inefficient.

In practice, this means that we frequently add counter caches after the fact - a particular model suddenly needs to display this sort of value, and it's too slow. The traditional way to initialize a counter cache, though, is one of the least efficient imaginable: Load every Post, then iterate over them, load up all their comments, count them, and insert that value. Now, you can get some basic improvements by counting the Comments without loading them (post.comments.count), and a few more by eager loading the comments (Post.includes(:comments).each do |post| etc.). But if you have a lot of these things, even those get pretty darn slow. This is exactly the sort of thing your database should be doing for you. So, how does one do that in SQL?

First, create a temporary table that contains the values you want:

      create temp table post_comment_counts as
        select, count(
          from posts
          left join comments 
            on comments.post_id =
          group by;

If you have a lot of Posts, the update will run a lot faster if you index that temporary table:

      create index on post_comment_counts(id);

Now, update the posts table, using your temp table:

      update posts
        set comments_count = post_comment_counts.count
        from post_comment_counts
        where =;

You don't need the temp table any more. It'll go away on it's own eventually, but no need to leave it lying around:

      drop table post_comment_counts;

One of these days, I'll get around to generalizing this process and submitting it as a pull request on Rails. Any day now, really.

Friday, November 16, 2012

Office Quote Friday: Food, part 2

CTO: "Well, they honored my request… to put lots of hot sauce on. And I am on fire, baby!"

Me: "So, what do you want for lunch?"
Second Programmer: "So, when you're married…<begins extensive rant about food selection for families>"
Me, eventually interrupting: "I thought I was just asking what he wanted for lunch."
Second Programmer: "No, see, after this you'll understand to stop asking me that."
Third Programmer: "I think we understand that now."

Programmer, sadly: "It's no good with only one string bean."

Programmer: "What did you get?"
Designer: "I dunno… fire? And beef…. Mostly fire."

Director of Marketing: “So, everyone is having salads today.”
Graphic Artist: “No. I’m hungry, I want a sandwich.”
Director of Marketing, wheedling: “You can get a salad with steak tips...”
Graphic Artist: “I only eat steak tips if they’re between two pieces of bread.
Director of Marketing: “Well... everyone else, welcome to salad Wednesday.

First Programmer: "How do you have nine grams of sugar in tomato soup?"Second Programmer: "Tomatoes are sneaky, dude."

First Programmer, finishing his lunch: "I win!"Second Programmer, still eating: "I have food and you don't. How do you win?"First Programmer: "I have food too, it's just down here!" *points at stomach*

Designer: "What the heck is Gar…gon… zoler?"First Programmer: "Gorgonzola?"Second Programmer: "It's a kind of cheese."
*several weeks later*
Designer: “What is that?Second Programmer: “It’s a scallion pancake...”Designer: “Are those good?”