Rails Active Record Lameness
I know this is sacrilegious, but there is some serious lameness going on in ActiveRecord I’ve dealt with lately. Maybe I’m drawing outside of the Rails lines (going off the tracks?), but ActiveRecord seems to go out of its way to make things a pain in the ass.
AR::Base#sanitize_sql being a ‘protected’ method has always been a burr in people’s sides. This means you can’t call it yourself on your own piece of SQL. Presumably it is done this way so people HAVE to do it the Rails way, whether that means duplicating a bunch of code, or taking a lot more time for a one-off project, etc.
Currently I’m working a report generator for Flex, where the Flex app handles the SQL generation and passes back an XML version of the sql options like:
<query>
<name>
</name>
<sql>
<select>
<![CDATA[Date(created_at) as date]]>
</select>
<select>
<![CDATA[count(id) as total]]>
</select>
<from>
</from>
<conditions>
</conditions>
<group>
<![CDATA[date]]>
</group>
<having>
<![CDATA[date >= :start_date and date <= :end_date]]>
</having>
</sql>
</query> Now we can argue all day about the best way to do this, but the reality is that only Admin authenticated people are going to see these reports, so the fact that someone could send arbitrary sql against the database is outweighed by the Flex-ibility of being able to dynamically adjust the query values in Flex without having to create a custom server-side method for each report. It is easier in my case to let more readily available/cheaper Flex programmers handle this than more expensive Ruby coders.
Getting this accomplished in Rails led to 5 workarounds in the code:
def replace_named_bind_variables_no_quotes(statement, bind_vars) #:nodoc:
statement.gsub!(/:(\w+)/) do
match = $1.to_sym
if bind_vars.include?(match)
bind_vars[match]
else
raise ActiveRecord::PreparedStatementInvalid, "missing value for :#{match} in #{statement}"
end
end
end
def query
# from_xml puts it in something like { queries => {query => [{name, sql}, {name,sql}....] }}
queries = Hash.from_xml(params[:queries])['queries']['query']
# logger.dbg queries.inspect
# generate the report structure
report = []
queries.each { |data|
query = data['sql']
logger.dbg query.inspect
# Now we need to get around a bunch of ActiveRecord lameness....
# Presumably it is done this way to satisy someone's idea of 'how you should do things'
# rather than, 'let's help them do it, no matter how they want to get it done'
# first, AR doesn't do bind variables for anything but conditions...
replace_named_bind_variables_no_quotes(query['group'], params) if query['group']
replace_named_bind_variables_no_quotes(query['having'], params) if(query['having'])
# second, we need to join the select clauses, as :select doesn't accept an array...
query['select'] = query['select'].join(', ')
# third, AR doesn't support a separate HAVING clause, you have to attach it to GROUP BY
if query['having'] and query['group'] # you always have both...
query['group'] = query['group'] + " HAVING " + query['having']
query.delete('having')
end
# fourth, we need to intern the keys so that they pass 'inspection' by AR
interned_query = {}
query.each { |key, value|
interned_query[key.intern] = value if(value != nil) # AR doesnt like :conditions => nil either...
}
logger.dbg interned_query.inspect
# fifth, we need to use Creative instead of just ActiveRecord::Base because there is a bug/weirdness in reset_table_name
# where it can't find the abstract_class
report << [data['name'], Creative.find(:all, interned_query)]
}
# output the results xml
str = ''
xml = Builder::XmlMarkup.new(:target => str, :indent => 1)
xml.result {
report.each { |query|
xml.query {
keys = []
xml.name query[0]
xml.cols {
exemplar = query[1].first
exemplar.attributes.each { |key, value|
xml.col key
keys << key
}
}
xml.rows {
query[1].each { |row|
xml.r {
keys.each { |key|
xml.v row.attributes_before_type_cast[key]
}
}
}
}
}
}
}
render :xml => str
end