Clever Geek Handbook

Choosing random in rails sqlite vs mysql

Hi guys I am trying to fetch random data from a database in Ruby on Rails. Unfortunately sqlite and mysql use different names for the "random" function. Mysql uses rand (), sqlite uses random (). So far, I am very happy with the use of sqlite in my development environment and I don't want to give it up.

So, I have a solution for this, but I'm not very happy with it. First, is there a cleaner abstraction in RoR for getting a random function? If not, what is the best way to get an "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...")
+2


source to share


3 answers


You can effectively use MySQL rand()

for the standard random()

by creating a function:



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


source


I wrote a small plugin that handles this problem:



http://github.com/norman/active_record_random

+4


source


I ran into this issue while developing locally using SQLite. Unfortunately, this is not the only difference between the databases you intend to run (for example, booleans are handled differently).

Is SQLite and MySQL support required? If not, I recommend switching to one database: the one you are deploying to production.

It takes a little longer to set up, but IMHO it will ultimately save you time and have confidence that your application works well in the database you will actually deploy it to.

+1


source






More articles:


All Articles