Any comment on adding #cast and #cast! to ActiveRecord::Relation?

rubyonrails-core
1

Not getting any love (tender or otherwise) on my pull request so I thought you folks might like to chip in. What do you think?

#cast and #cast! allow you to declare a mapping from generated column names to simplified database column types (i.e. those used in migrations). This way scopes with columns generated in their select lists can also include the information as to what

form that data takes. This saves the user from having to translate the data from a sql string wherever it’s used.

Here’s an example of where I’d use this:

class Topic < ActiveRecord::Base
  has_many :replies
  scope :by_most_recently_replied, joins(:replies).group('[topics.id](http://topics.id)')
      .select('topics.*, MAX(replies_topics.written_on) AS latest_reply_written_on')
      .order('latest_reply_written_on DESC').cast(latest_reply_written_on: :datetime)
end

>> topic = Topic.by_most_recently_replied.first
>> topic.latest_reply_written_on.class
=> Time

The cast is applied to postgres columns only when there is no OID info for the column.

What do you guys think? Remaining work is to test handling of binary data - not quite sure how to go about that but I don’t expect any surprises.

2

Is this a limitation of PostgreSQL or the ruby library that is used by said adapter's #raw_connection? Specifically, computed column support? I know in SQL Server that `MAX(datetime_column)` would return the proper datatype and the low level connection ruby library, in this case TinyTDS, would do the legwork before handing things to the adapter then ActiveRecord.

Could the same be achieved doing a CAST() in your SQL string for the select?

- Ken

3

It's not necessarily a limitation of the PostgreSQL ruby library. pg, postgres, and postgres-pr all return fields as ruby Strings, but make the type oid metadata available for all returned fields (doesn't matter if they are computed fields or not). You shouldn't even need an SQL cast (as max(timestamp_field) should be of type timestamp), you just need to know how to map PostgreSQL type oids to ruby classes. Sequel does this, and I've read that ActiveRecord 4 will as well (and it looks like it does, see below).

Maybe what needs to be fixed is replies_topics.written_on is a timestamptz field, and ActiveRecord currently treats that as a string instead of a datetime value (https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb#L228)

Jeremy

4

Agree that propagating the data-type directly from the database is ideal. That’s what’s being done in the 2 places I found where type-casting was being performed:

  • For attributes drawn from schema columns (see #read_attribute @columns_hash)
  • Under Postgres, when attributes have associated OID information i.e. In fact as I recall the example I posted above works under Postgres on master, thanks to OID support, but not on sqlite or (presumably) mysql.

I tried SQL casts unsuccessfully, though not systematically.

There may be plenty more to this picture I’m not aware of, but it seems that there are definite gaps in the picture for DBs other than postgres, and for cases where information isn’t understood or available.

In any case it seems I could look more into establishing the current behavior. Can anyone describe more of the casting picture, apart from the 2 points above?

-Ben

5

Hi,

The problem is with the ActiveRecord PostgreSQLAdapter which doesn’t typecast the returned strings from Postgres to Ruby types.

I wrote a monkey patch for older version of the PostgreSQLAdapter to add some type casts, but this will not work correctly for the current adapter without other changes. (Especially since code in the current Adapter expects the string response for Boolean database types when evaluating if a column allows null).

https://gist.github.com/1624535

The correct fix is to use the “idea” in that gist to make a proper fix the the PostgreSQLAdapter class and identify where it does use the String values and change it to use the correct types.

I ended up removing this from my code and not pursuing it on the concerns around other third-parties depending on this untyped behaviour of the PostgreSQLAdapter

Regards,

Anthony Richardson

6

On SQLite, computed columns are unlikely to ever return datetime values, due to SQLite's type system not supporting them. MySQL provides type id information similar to PostgreSQL. For the mysql adapter, ActiveRecord master already uses this information (https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb#L228). For the mysql2 adapter, the typecasting is done internally, so ActiveRecord doesn't need to worry about it.

In terms of your patch to add cast! and cast methods, the only database I see that helping is SQLite. All other databases in common use support real timestamp types and should not need manually overrides at the ruby level.

Jeremy