ActiveRecord/sqlite3 column type lost in table view?

Question

I have the following ActiveRecord testcase that mimics my problem. I have a People table with one attribute being a date. I create a view over that table adding one column which is just that date plus 20 minutes:

#!/usr/bin/env ruby

%w|pp rubygems active_record irb active_support date|.each {|lib| require lib}

ActiveRecord::Base.establish_connection(
:adapter => "sqlite3",
:database => "test.db"
)

ActiveRecord::Schema.define do
  create_table :people, :force => true do |t|
    t.column :name, :string
    t.column :born_at, :datetime
  end

  execute "create view clowns as select p.name, p.born_at, datetime(p.born_at, '+' || '20' || ' minutes') as twenty_after_born_at from people p;"

end

class Person < ActiveRecord::Base
  validates_presence_of :name
end

class Clown < ActiveRecord::Base
end

Person.create(:name => "John", :born_at => DateTime.now)

pp Person.all.first.born_at.class
pp Clown.all.first.born_at.class
pp Clown.all.first.twenty_after_born_at.class

The problem is, the output is

Time
Time
String

When I expect the new datetime attribute of the view to be also a Time or DateTime in the ruby world. Any ideas?

I also tried:

create view clowns as select p.name, p.born_at, CAST(datetime(p.born_at, '+' || '20' || ' minutes') as datetime) as twenty_after_born_at from people p;

With the same result.

Answer 1

Well, after more investigation, I found that:

MySQL works:

%w|pp rubygems active_record irb active_support date|.each {|lib| require lib}

ActiveRecord::Base.establish_connection(
    :adapter => "mysql",
    :username => "root",
    :database => "test2"
)

ActiveRecord::Schema.define do
  create_table :people, :force => true do |t|
    t.column :name, :string
    t.column :born_at, :datetime
  end

  execute "create view clowns as select p.name, p.born_at, (p.born_at + INTERVAL 20 MINUTE) as twenty_after_born_at from people p;"
end

class Person < ActiveRecord::Base
  validates_presence_of :name
end

class Clown < ActiveRecord::Base
end

Person.create(:name => "John", :born_at => DateTime.now)

pp Person.all.first.born_at.class
pp Clown.all.first.born_at.class
pp Clown.all.first.twenty_after_born_at.class

Produces:

Time
Time
Time

Reading the sqlite3 adapter source code, I found out that it uses PRAGMA table_info(table_name) to get the type information, and that does not return the types for views:

sqlite> pragma table_info('people');
0|id|INTEGER|1||1
1|name|varchar(255)|0||0
2|born_at|datetime|0||0
sqlite> pragma table_info('clowns');
0|name|varchar(255)|0||0
1|born_at|datetime|0||0
2|twenty_after_born_at||0||0

Therefore it may be a limitation of the adapter or just a sqlite3's views limitation. I have opened a ticket for ActiveRecord:

Also, quoting this mail in sqlite-users:

RoR should be using the sqlite3_column_type() API to determine the type of the values returned from a query. Other APIs like sqlite3_column_decltype() and pragma table_info are returning other information, not the type of the result value.

Answer 2

Well, basically there is no datatime type in SQLite as opposed to MySQL. In your example you explicitly define types for the table but do not specify types for the view. That might be the problem. Can not check it since I have never touched ruby.