[Rails] Re: How to implement more database adapters for Active Record

[Eric Ocean]

On Dec 1, 2004, at 9:07 AM, Steve Woodcock wrote:

> Jorge Sousa <jhsousa at ...> writes:
>> Why instead of generating inline SQL statements like AR is doing and 
>> passing
> them throught the respective
>> adapter to be executed, AR could generete SQL statements with 
>> parameters and
> send these statements along
>> with an array of parameters to be binded by the adapters. That way the
> quotings could be eliminated i guess.
>
> This would allow using prepared statements and/or bind variables for 
> those dbs
> which support them, which is a good thing

Agreed.

In FrontBase at least, the query planner only uses multi-column indexes 
if they
were originally defined in the same order as they are presented in a 
SELECT.
Getting rid of inline SQL would allow the FrontBase adapter to 
construct queries
that could be executed optimally by the database. In addition, a 
default order
could be defined in AR, such as defining all indexes in alphabetical 
order by
column. Then the optimizer would work in the default case, and other 
database
adapters and database admins could rely on that convention as well.

To further enhance things, we could define an :index_on [ id, column_1, 
columnt_4 ]
configuration that would provide non-alphabetic-order index information 
to the
adapter. (It's also possible, in FrontBase at least, to pull this 
directly out of
the database. That might be better all the way around. At any rate, AR 
can support
both.)

Although I suspect there is already broad agreement on this, I'll point 
out that
in ActiveRecord, we can't ignore database optimization. Every database 
is
_designed_ to be optimized to get acceptable performance. Unlike program
optimization, it's generally not optional. It would be great if Rails 
could define
conventions that facilitated useful database optimization with minimal 
code changes
and certainly without falling back to rolling your own SQL.

Best, Eric