This project is archived and is in readonly mode.
rails3 : ActiveRecord sqlite3 : lost column type when using views
Reported by Duncan Mac-Vicar | May 6th, 2010 @ 09:23 PM
Originally I asked in Stackoverflow until I realized MySQL adapter does work correctly.
Rails 3.0.0.beta3 with ruby 1.9.2head (happens in 2.3.5 / ruby 1.8.7 too)
DATETIME columns in sqlite3 are interpreted as Time/TimeWithZone objects. However, when using a view over a table where the adapter already interprets the time column type correctly, you get a String instead:
#!/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
Produces output:
Time
Time
String
The same case in MySQL:
#!/usr/bin/env ruby
%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 output:
Time
Time
Time
Which is what I expect.
Comments and changes to this ticket
-
Duncan Mac-Vicar May 6th, 2010 @ 09:41 PM
The problem seems to come from the fact that the adapter 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||0However, as I said, it works in MySQL.
-
Duncan Mac-Vicar May 6th, 2010 @ 10:13 PM
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.
-
Duncan Mac-Vicar May 6th, 2010 @ 10:13 PM
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.
-
Dan Pickett May 9th, 2010 @ 06:47 PM
- Tag changed from “sqlite3 activerecord” to “activerecord, bugmash, sqlite3”
-
Diego Algorta May 15th, 2010 @ 09:31 PM
This is not a rails bug. It's a bug in sqlite3-ruby gem itself. I glanced at their code in current master and they're still using PRAGMA so I reported the bug in http://rubyforge.org/tracker/index.php?func=detail&aid=28203&am...
-
Aaron Patterson May 15th, 2010 @ 09:37 PM
- Assigned user changed from “Jeremy Kemper” to “Aaron Patterson”
-
Aaron Patterson June 17th, 2010 @ 12:42 AM
- State changed from “new” to “wontfix”
This can't be fixed. The sqlite3_column_type() function can't tell the difference between a column declared as a datetime vs a string as datetimes are actually stored as strings in sqlite. The pragma call is the only way to get the declared column types and it unfortunately doesn't work with views.
See the sqlite3 C api for sqlite3_column_type():
http://www.sqlite.org/capi3ref.html#sqlite3_column_blob
Also see my comments on the rubyforge ticket.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
Aaron Patterson
Duncan Mac-Vicar
Jeremy Kemper