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

[Jorge Sousa]

Hi,

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.

So in summary instead of :

- sql = "INSERT INTO projects (name, id) VALUES('Active  Record', '1')"
and calling <adapter>.[execute | insert | select_one](sql)

one would use
sql = "INSERT INTO projects (name, id) VALUES(?, ?)" 
params = ['Active Record', 1]

and calling <adapter>.[execute | insert | select_one](sql, params) which would bind the parameters to the statement.


I've been playing around with a native ruby FirebirdSQL driver build upon IBPP lib (pre-alpha stuff)  and for working with BLOB field the only way to be able to use them is by using parameters.

Another issue with AR is it doesn't like quoted field names, (eg: "First Name"), i know i can use (first_name) but some databases convert unquoted field names to uppercase. But i can live with that...

Jorge

----- Original Message ----- 
From: "David Heinemeier Hansson" <david at loudthinking.com>
To: <rails at lists.rubyonrails.org>; "Joey Gibson" <joey at joeygibson.com>; "Jim Weirich" <jim at weirichhouse.org>; "Maik Schmidt" <contact at maik-schmidt.de>; "Eric Ocean" <eric.ocean at ampede.com>
Sent: Wednesday, December 01, 2004 2:54 PM
Subject: [Rails] How to implement more database adapters for Active Record


Hi guys,

There is now no less than four people working on new database adapters 
for Active Record. Allow me to introduce them:

* Joey Gibson: Has an almost complete MS SQL Server adapter using DBI 
and ADO ready to be included with Active Record. Just a few tidbits 
remaining.

* Maik Schmidt: Have a lot of stuff up and running for the DB2 adapter.

* Jim Weirich: Has plans to or has just started developing an Oracle 
adapter.

* Eric Ocean: Working on a FrontBase adapter.

One of the biggest problems with bringing these databases to Active 
Record is that they interpret quoting and types much more stringent 
than MySQL, SQLite, and  PostgreSQL. What do do about that?

Eric Ocean wrote me with this suggestion:

> FrontBase is SQL92-conformant, almost to a fault, and simply won't 
> accept SQL that's not to the standard. Right now, ActiveRecord isn't 
> maintaining enough information about types to output them correctly. 
> The clean solution, IMHO, is to add a SQL type member to each 
> ActiveRecord object, and set it to the exact SQL type (e.g. :tinyint) 
> coming from the database schema. This can, of course, be done lazily, 
> and the information should be cached in the class. AR should support 
> the union of sql types from all database supported by AR, and new 
> adaptors should be able to add their own. It would be the adaptor's 
> responsibility to map from their database-native SQL type to the type 
> used internally by AR.
>
> The other change is to give more control over SQL generation for each 
> kind of SQL statement (INSERT, UPDATE, SELECT, etc.) so that quoting 
> can be controlled on a per statement basis. Existing adaptors would 
> just alias the various methods to the current implementations, so no 
> extra work for them. More sophisticated databases like FrontBase and 
> Oracle can use the SQL type along with more info about the context of 
> the quote generation to produce valid SQL.
>
> I suspect that SQL types would also ease the implementation of CLOBs 
> and BLOBs which require special handling for each kind of database.

Maik Schmidt wrote about his problems with DB2:

> A bigger problems is DB2 itself (at least Version 7.1, which is the 
> version I have to deal with). ActiveRecord assumes that it's possible 
> to quote every value and you can find a lot of ' characters in the 
> code. For example, in class HasAndBelongsToManyAssociation there's a 
> method that looks like this:
>
> def insert_record(record)
>   if @options[:insert_sql]
>     @owner.connection.execute(
>       interpolate_sql(@options[:insert_sql], record)
>     )
>   else
>     sql = "INSERT INTO #{@join_table}
>         (#{@association_class_primary_key_name},
>          #{@association_foreign_key}) VALUES
>         ('#{@owner.id}','#{record.id}')"
>           @owner.connection.execute(sql)
>   end
> end
>
> Obviously, that's fine SQL for a lot of databases, but ugly old DB2 
> complains like this:
>
> ERROR From DB2: INSERT INTO projects (name, id) VALUES('Active 
> Record', '1')
> DB2 return code is SQL_ERROR
> [IBM][CLI Driver][DB2/LINUX] SQL0408N  A value is not compatible with 
> the data type of its assignment target.  Target name is "ID". 
> SQLSTATE=42821E
>
> The column 'id' has been declared as integer and '1' is no integer 
> literal, so DB2 refuses to do its dirty work. What should we do now? 
> As far as I can see, there are some places, where the quotes have to 
> be replaced, but we should do something better to prevent surprises 
> while integrating the next database.

So it seems like something must indeed be done. One of the easy ways 
would of course be to make sure that every time a value is needed any 
where in an SQL statement generated by Active Record, we use a quote 
method that can be overwritten by each adapter.

There is already some underpinnings for this, but it may not be enough 
to satisfy all the databases involved.

Anyway, I just wanted to open up the floor for discussions so that we 
can all work in the same direction. It would be exceptionally cool to 
have all these adapters done and help Active Record and Rails 
tremendously.

Let's get a solution and move forward ASAP.
--
David Heinemeier Hansson,
http://www.basecamphq.com/   -- Web-based Project Management
http://www.rubyonrails.org/  -- Web-application framework for Ruby
http://macromates.com/       -- TextMate: Code and markup editor (OS X)
http://www.loudthinking.com/ -- Broadcasting Brain

_______________________________________________
Rails mailing list
Rails at lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails