Rails model with conditional scope depending on database

Question

I'm implementing a scope for a model based on this railscast. The condition in the scope uses the binary AND operator &, like this:

scope :with_role, lambda { |role| 
  {:conditions => "roles_mask & #{2**ROLES.index(role.to_s)} > 0"} 
}

Since the database I'm currently targetting is Oracle, which uses the BITAND function instead of the & operator, I've rewritten the condition like this:

{:conditions => "BITAND(roles_mask, #{2**ROLES.index(role.to_s)}) > 0"}

My problem is, I would like to keep my code as database agnostic as possible, since in the future we are planning on targetting other databases. My current solution has been to check whether I'm using Oracle and define the scope accordingly, like this (using_oracle is a boolean that I calculate elsewhere):

if using_oracle 
  scope :with_role, lambda { |role| 
    {:conditions => "BITAND(roles_mask, #{2**ROLES.index(role.to_s)}) > 0"}
  }
else
  scope :with_role, lambda { |role| 
    {:conditions => "roles_mask & #{2**ROLES.index(role.to_s)} > 0"}
  }
end

This works, but doesn't strike me as particularly elegant or ruby/rails-like. Could anyone kindly suggest better alternatives?

You could grab [postgres_ext](https://github.com/dockyard/postgres_ext) and see how it adds support for [PostgreSQL-specific datatypes](https://github.com/dockyard/postgres_ext/blob/master/docs/querying.md); then you should be able to patch in the database-specific stuff in one place and write to your API additions. Yes, that is PostgreSQL-specific but it is the only thing that came to mind and the techniques are what you're interested in anyway. — mu is too short, Mar 08 '13 at 23:58
@mu is too short - Certainly extending ActiveRecord looks like a more adequate way to tackle Oracle's particularities. Thank you! — laffinkippah, Mar 09 '13 at 12:43

mdesantis · Accepted Answer · 2013-03-10T14:53:23.653

I think there should be a better way to extend Arel: I struggled to reach this result.

Anyway; this solution uses Model#extending:

module BitOperations
  def bitwise_and_sql
    @bitwise_and_sql ||=
      case connection.adapter_name
      when 'Oracle' # probably wrong!
        "BITAND(%s, %s)"
      else
        "%s & %s"
      end
  end
  def bitwise_and(i, j)
    where(bitwise_and_sql % [i, j])
  end
  def bitmask(i, j)
    where('%s > 0' % scoped.bitwise_and(i, j).wheres.to_a.last.to_sql)
  end
end

p User.scoped.extending(BitOperations).bitwise_and(1, 2).bitmask(3, 4).to_sql
#=> "SELECT \"users\".* FROM \"users\"  WHERE (1 & 2) AND ((3 & 4) > 0)"

.wheres contains the Arel relations; it includes Enumerable, so we can retrieve the last relation converting it to array and taking the last element. I used it in order to get the sql of bitwise_and(i, j) in order to use it in bitmask(i, j). I wonder if there is a better way to get the sql from a where...

.wheres raises a warning about wheres deprecation, which can be ignored at the moment (It works on Rails 4 beta too).

You can define class methods for User:

class User
  def self.scope_with_bit_operations
    @scope_with_bit_operations ||= scoped.extending(BitOperations)    
  end
  def self.bitwise_and(i, j)
    scope_with_bit_operations.bitwise_and(i, j)
  end
  def self.bitmask(i, j)
    scope_with_bit_operations.bitmask(i, j)
  end
end

p User.bitwise_and(1, 2).bitmask(3, 4).to_sql
#=> "SELECT \"users\".* FROM \"users\"  WHERE (1 & 2) AND ((3 & 4) > 0)"

or for all your models:

class ActiveRecord::Base
  def self.scope_with_bit_operations
    @scope_with_bit_operations ||= scoped.extending(BitOperations)    
  end
  def self.bitwise_and(i, j)
    scope_with_bit_operations.bitwise_and(i, j)
  end
  def self.bitmask(i, j)
    scope_with_bit_operations.bitmask(i, j)
  end
end

p Post.bitwise_and(1, 2).bitmask(3, 4).to_sql
#=> "SELECT \"posts\".* FROM \"posts\"  WHERE (1 & 2) AND ((3 & 4) > 0)"

And finally you can implement a slightly more elegant with_role scope:

class User < ActiveRecord::Base
  ROLES = %w[admin moderator author]

  scope :with_role, ->(role) do
    # I'm a fan of quoting everything :-P
    bitmask connection.quote_column_name(:roles_mask),
            connection.quote(2**ROLES.index(role.to_s))
  end
end

p User.with_role('admin').to_sql
#=> "SELECT \"users\".* FROM \"users\"  WHERE ((\"roles_mask\" & 1) > 0)"

I must say that IMO this is more a proof-of-concept: if you don't plan to reuse bitwise_and and bitmask in other models you don't need to abstract them, so probably you're good to go with something similar to your scope, f.e. something like this:

class User < ActiveRecord::Base
  ROLES = %w[admin moderator author]

  BITMASK_SQL =
    case connection.adapter_name
    when 'Oracle' # probably wrong!
      "BITAND(%s, %s) > 0"
    else
      "%s & %s > 0"
    end

  scope :with_role, ->(role) do
    where BITMASK_SQL % [ connection.quote_column_name(:roles_mask), 
                          connection.quote(2**ROLES.index(role.to_s)) ]
  end
end

p User.with_role('admin').to_sql
#=> "SELECT \"users\".* FROM \"users\"  WHERE (\"roles_mask\" & 1 > 0)"

I think the rule is add abstractions when you need of them, do not when you don't need of (I don't know if the english of this phrase is correct :-) )

I want to say another thing: since you are new to Ruby/Rails, I suggest you to read a lot of Rails & c. code; IMO it is the best way to learn how Rails works (this is the reason I spent my time to answer to your question: because I was curious about Rails management of Arel relations :-) ).

But could you push the `extending` business into an initializer and turn the `case connection.adapter_name` inside out so that the check is only done once? — mu is too short, Mar 09 '13 at 18:26
Since I'm fairly new to Ruby and Rails, you have no idea how educative your code is to me. I'll still have to put some work into it in order to understand everything you are doing there, and why you do it that way, but just going over it and checking it out with the pry console I've learned quite a few useful things. Thank you! — laffinkippah, Mar 09 '13 at 23:15
@muistooshort I updated the answer; do you mean something like this? laffinkippah you're welcome ;-) I updated the answer with other thoughts — mdesantis, Mar 10 '13 at 14:54
@ProGNOMmers - I agree with you that for my current needs the "simple" solution is clearer and probably more adequate (as it keeps everything in one place and is, well, simpler). Most enlightening of all to me is your use of `connection.quote_column_name`, as in the beginning I was struggling to do just that and didn't know how. — laffinkippah, Mar 10 '13 at 22:07
@ProGNOMmers - I'm not a native English speaker but I guess we can leave it at _add abstractions only when you need of them_. ;) Also, yes, I'm reading lots and lots of Ruby code and find it fascitanting. I started learning Ruby barely a month ago, and it feels like I've already made more progress than in a year struggling with Java. I'm delighted! — laffinkippah, Mar 10 '13 at 22:13
Yeah, that `ActiveRecord::Base` monkey patch was pretty much what I was thinking. If you had a whole bunch of database-specific hackery then you could make one module per database and then patch in the necessary one during initialization. — mu is too short, Mar 10 '13 at 23:41
@muistooshort holy words laffinkippah the trilogy of quoting things for the database is completed with `connection.quote_table_name` ;-) — mdesantis, Mar 11 '13 at 09:16

Rails model with conditional scope depending on database

1 Answers1