Find by SQL file¶ ↑

This plugin extends the API of ActiveRecord::Base#find_by_sql

A Warning¶ ↑

Besides the warnings on the ERB section below (don’t ignore those), this code has very little production track record. So there, beware.

Example Usage¶ ↑

Instead of passing the SQL statement as a string…

Elephant.find_by_sql "SELECT * FROM elephants WHERE weight='massive'"

You can pass a symbol that refers to a query file:

Elephant.find_by_sql :massive_weight

Query files are assumed to be save as:

Rails.root/app/queries/TABLE_NAME/SYMBOL.sql

Installation instructions¶ ↑

Via rubygems:

gem install find_by_sql_file

# Rails 2.x (config/environment.rb)
config.gem 'find_by_sql_file'

# Rails 3.x (Gemfile)
gem 'find_by_sql_file'

# Or, to use outside of Rails, for now (better API coming soon):
require 'active_record'
RAILS_ROOT = '/some/folder/'
require 'find_by_sql_file'

Motivation¶ ↑

The advantage of the external file approach is that the SQL file can be properly indented and commented (the indentation and comments are stripped from the logs.)

Features & Problems¶ ↑

Comment removal¶ ↑

As far as comment removal, only double-dash-space single-line comments are stripped, like so:

SELECT foo, -- We need this for X reason
       bar, -- and this for some Y reason
       bez,  # This comment will NOT be removed, and will be a problem
       duh  /* And neither will this one. Use -- style only */

       FROM table;

So, to clarify, the start-comment marker is ‘– ’ (two dashes and a space). That I know of, this marker works in MySQL, PostgreSQL, SQLite, Oracle, DB2, and SQL Server. While not all of these require the space after the dashes, it never hurts.

Bind variables¶ ↑

It’s possible to pass named bind variables, much like in the conditions parameter of ActiveRecord::Base.find, by passing a hash as the second parameter, like so:

Elephant.find_by_sql :specifics, :color => 'grey', :weight => 6800

ERB (be careful)¶ ↑

It is also possible to use ERB inside the query file, but beware! Unlike the named bind variables, any data passed in via the ERB method is not properly quoted by the database adapter, leaving open the possibility of *SQL injection*. 99.9% of the time, *you will NOT need this*.

Here’s an artificial (but easy to explain) example of how the (very dangerous!) ERB feature works:

Elephant.find_by_sql :single_value, :value   => 'grey',
                                    :inject! => { :field => 'color' }

The call above replaces the bind variable value inside the SQL file, but it also populates the instance variable field with “color”, which can then be used with the usual ERB syntax, like so:

SELECT <%= @field -%> FROM elephants WHERE <%= @field -%> = :value

Legal¶ ↑

Copyright © 2008..2010 Jordi Bunster, released under the MIT license