find_by_sql_file

= 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 (c) 2008..2010 Jordi Bunster, released under the MIT license