Rails core - Jul 2012 - Any comment on adding #cast and #cast! to ActiveRecord::Relation?

Not getting any love (tender or otherwise) on my pull
request<https://github.com/rails/rails/pull/7035>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'')
      .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.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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


-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On Tue, Jul 17, 2012 at 8:26 PM, Ken Collins <ken@metaskills.net>
wrote:
>
> 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?
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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<https://github.com/rails/rails/blob/master/activerecord/lib/active_record/attribute_methods/read.rb#L72>
   )
   - Under Postgres, when attributes have associated OID information
i.e.<https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L819>

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<https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L823>
.

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


On Tue, Jul 17, 2012 at 8:53 PM, Jeremy Evans
<jeremyevans0@gmail.com>wrote:
> On Tue, Jul 17, 2012 at 8:26 PM, Ken Collins <ken@metaskills.net>
wrote:
> >
> > 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?
>
> 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
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Core" group.
> To post to this group, send email to rubyonrails-core@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-core+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-core?hl=en.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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

On Wed, Jul 18, 2012 at 2:12 PM, Ben Woosley <ben.woosley@gmail.com>
wrote:
> 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<https://github.com/rails/rails/blob/master/activerecord/lib/active_record/attribute_methods/read.rb#L72>
>    )
>    - Under Postgres, when attributes have associated OID information
i.e.<https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L819>
>
> 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<https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L823>
> .
>
> 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
>
>
> On Tue, Jul 17, 2012 at 8:53 PM, Jeremy Evans
<jeremyevans0@gmail.com>wrote:
>
>> On Tue, Jul 17, 2012 at 8:26 PM, Ken Collins <ken@metaskills.net>
wrote:
>> >
>> > 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?
>>
>> 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
>>
>> --
>> You received this message because you are subscribed to the Google
Groups
>> "Ruby on Rails: Core" group.
>> To post to this group, send email to rubyonrails-core@googlegroups.com.
>> To unsubscribe from this group, send email to
>> rubyonrails-core+unsubscribe@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/rubyonrails-core?hl=en.
>>
>>
>  --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Core" group.
> To post to this group, send email to rubyonrails-core@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-core+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-core?hl=en.
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On Tue, Jul 17, 2012 at 9:42 PM, Ben Woosley <ben.woosley@gmail.com>
wrote:
> 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.
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.