Selecting random in rails sqlite vs mysql

Question

Hey guys, I'm trying to select random data from the database in Ruby on Rails. Unfortunately, sqlite and mysql use different names for the "random" function. Mysql uses rand(), sqlite use random(). I've been pretty happy using sqlite in my development environments so far, and I don't want to give it up for just this.

So I have a solution for it, but I'm not very happy with it. First, is there a cleaner abstraction in RoR for getting the random function? And if not, is this the best way to get the "adapter"?

# FIXME: There has to be a better way...
adapter = Rails.configuration.database_configuration[Rails.configuration.environment]["adapter"]
if adapter == "sqlite3"
  # sqllite calls it rand
  random = "random"
else
  # mysql calls it rand
  random = "rand"
end

query.push("SELECT *, (" +  random + "() * (0.1 * value)) AS weighted_random_value...")

Answer 1

You can effectively alias MySQL's rand() to the standard random() by creating a function:

CREATE FUNCTION random() RETURNS FLOAT NO SQL SQL SECURITY INVOKER RETURN rand();

Answer 2

I ran into this problem when developing locally using SQLite. Unfortunately, this is not the only difference between the databases you're going to run into (booleans are also handled differently for instance).

Is it a requirement that you support both SQLite and MySQL? If not I recommend switching to a single database: the one you're deploying on in production.

This takes a bit more time to set up but IMHO in the long run it will save you time, and you will have confidence that your app works well on the database that you'll actually be deploying it with.

Answer 3

I wrote a small plugin that handles this problem:

http://github.com/norman/active%5Frecord%5Frandom