The integer columns in my DB tables are returned as strings when ActiveRecord gets involved. This seems annoying and unnecessary to me. Surely the database adapter knows, or can know, what data types are used. Is it ActiveRecord''s policy to ignore this metadata? Thanks, Gavin
On Wed, 24 Nov 2004 16:51:21 +1100, Gavin Sinclair <gsinclair-81uBx+iSpXA0n/F98K4Iww@public.gmane.org> wrote:> The integer columns in my DB tables are returned as strings when > ActiveRecord gets involved. This seems annoying and unnecessary to > me. Surely the database adapter knows, or can know, what data types > are used. Is it ActiveRecord''s policy to ignore this metadata?Strange... integer columns work just fine for me on MySQL with ActiveRecord. -- Chris Brooks http://www.chrisbrooks.org
For me they come out of the session with strings for id. But they work just fine direct from the database. On Tue, 23 Nov 2004 22:27:11 -0800, Chris Brooks <brookscl-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Wed, 24 Nov 2004 16:51:21 +1100, Gavin Sinclair > <gsinclair-81uBx+iSpXA0n/F98K4Iww@public.gmane.org> wrote: > > The integer columns in my DB tables are returned as strings when > > ActiveRecord gets involved. This seems annoying and unnecessary to > > me. Surely the database adapter knows, or can know, what data types > > are used. Is it ActiveRecord''s policy to ignore this metadata? > > Strange... integer columns work just fine for me on MySQL with ActiveRecord. > > -- > Chris Brooks > http://www.chrisbrooks.org > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
David Heinemeier Hansson
2004-Nov-24 09:50 UTC
Re: MySQL adapter returns integers as strings?
> The integer columns in my DB tables are returned as strings when > ActiveRecord gets involved. This seems annoying and unnecessary to > me. Surely the database adapter knows, or can know, what data types > are used. Is it ActiveRecord''s policy to ignore this metadata?Active Record does type conversion on real columns where it will have queried the database in advanced for the types (using SHOW FIELDS). It doesn''t convert values pulled when you grab the connection yourself nor does it convert piggy-back columns, aggregates, or results from functions. -- 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
On Wednesday, November 24, 2004, 8:50:47 PM, David wrote:>> The integer columns in my DB tables are returned as strings when >> ActiveRecord gets involved. This seems annoying and unnecessary to >> me. Surely the database adapter knows, or can know, what data types >> are used. Is it ActiveRecord''s policy to ignore this metadata?> Active Record does type conversion on real columns where it will have > queried the database in advanced for the types (using SHOW FIELDS). It > doesn''t convert values pulled when you grab the connection yourself nor > does it convert piggy-back columns, aggregates, or results from > functions.Hmmmm... I present the following IRB transcript: >> require ''user'' >> User.find(1) => #<User:0x320f468 @attributes={"name"=>"Jim Jones", "username"=>"jim", "id"=>"1", "password"=>"feral", "email"=>"jim-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org"}> The ID is a string. >> require ''game'' >> User.find(1).games[0] => #<Game:0x31ffbb0 @attributes={"platform"=>"PC", "title"=>"The Sims", "price"=>"25", "is_for_trade"=>"1", "barcode"=>"1023425", "id"=>"1", "condition"=>"Very good", "user_id"=>"1", "game_id"=>"1"}> The attributes "price", "is_for_trade", "user_id", and "game_id" are all strings. The database schema? From a phpMyAdmin export: DROP TABLE IF EXISTS `games`; CREATE TABLE `games` ( `id` int(10) unsigned NOT NULL auto_increment, `barcode` varchar(20) NOT NULL default '''', `title` varchar(100) NOT NULL default '''', `platform` varchar(10) NOT NULL default '''', PRIMARY KEY (`id`), UNIQUE KEY `barcode` (`barcode`) ) TYPE=MyISAM; DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(20) NOT NULL default '''', `password` varchar(20) NOT NULL default '''', `name` varchar(50) NOT NULL default '''', `email` varchar(30) NOT NULL default '''', PRIMARY KEY (`id`) ) TYPE=MyISAM; DROP TABLE IF EXISTS `users_games`; CREATE TABLE `users_games` ( `user_id` int(11) unsigned NOT NULL default ''0'', `game_id` int(11) unsigned NOT NULL default ''0'', `is_for_trade` tinyint(4) NOT NULL default ''0'', `price` int(11) default NULL, `condition` varchar(20) default NULL, PRIMARY KEY (`user_id`,`game_id`) ) TYPE=MyISAM; Notice that the IDs are all integers, and so are "price" and "is_for_trade". Any ideas? Gavin
Try remove the single quotes around the 0''s in your user_games table? On Wed, 24 Nov 2004 21:34:11 +1100, Gavin Sinclair <gsinclair-81uBx+iSpXA0n/F98K4Iww@public.gmane.org> wrote:> On Wednesday, November 24, 2004, 8:50:47 PM, David wrote: > > > > >> The integer columns in my DB tables are returned as strings when > >> ActiveRecord gets involved. This seems annoying and unnecessary to > >> me. Surely the database adapter knows, or can know, what data types > >> are used. Is it ActiveRecord''s policy to ignore this metadata? > > > Active Record does type conversion on real columns where it will have > > queried the database in advanced for the types (using SHOW FIELDS). It > > doesn''t convert values pulled when you grab the connection yourself nor > > does it convert piggy-back columns, aggregates, or results from > > functions. > > Hmmmm... I present the following IRB transcript: > > >> require ''user'' > >> User.find(1) > => #<User:0x320f468 @attributes={"name"=>"Jim Jones", "username"=>"jim", > "id"=>"1", "password"=>"feral", "email"=>"jim-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org"}> > > The ID is a string. > > >> require ''game'' > >> User.find(1).games[0] > => #<Game:0x31ffbb0 @attributes={"platform"=>"PC", "title"=>"The Sims", > "price"=>"25", "is_for_trade"=>"1", "barcode"=>"1023425", "id"=>"1", > "condition"=>"Very good", "user_id"=>"1", "game_id"=>"1"}> > > The attributes "price", "is_for_trade", "user_id", and "game_id" are all > strings. > > The database schema? From a phpMyAdmin export: > > DROP TABLE IF EXISTS `games`; > CREATE TABLE `games` ( > `id` int(10) unsigned NOT NULL auto_increment, > `barcode` varchar(20) NOT NULL default '''', > `title` varchar(100) NOT NULL default '''', > `platform` varchar(10) NOT NULL default '''', > PRIMARY KEY (`id`), > UNIQUE KEY `barcode` (`barcode`) > ) TYPE=MyISAM; > > DROP TABLE IF EXISTS `users`; > CREATE TABLE `users` ( > `id` int(10) unsigned NOT NULL auto_increment, > `username` varchar(20) NOT NULL default '''', > `password` varchar(20) NOT NULL default '''', > `name` varchar(50) NOT NULL default '''', > `email` varchar(30) NOT NULL default '''', > PRIMARY KEY (`id`) > ) TYPE=MyISAM; > > DROP TABLE IF EXISTS `users_games`; > CREATE TABLE `users_games` ( > `user_id` int(11) unsigned NOT NULL default ''0'', > `game_id` int(11) unsigned NOT NULL default ''0'', > `is_for_trade` tinyint(4) NOT NULL default ''0'', > `price` int(11) default NULL, > `condition` varchar(20) default NULL, > PRIMARY KEY (`user_id`,`game_id`) > ) TYPE=MyISAM; > > Notice that the IDs are all integers, and so are "price" and > "is_for_trade". > > Any ideas? > > Gavin > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Vincent Foley-Bourgon Blog: http://www.livejournal.com/~gnuvince RSS: http://www.livejournal.com/~gnuvince/data/rss
On Wednesday, November 24, 2004, 11:26:44 PM, Vincent wrote:> Try remove the single quotes around the 0''s in your user_games table?Unfortunately that didn''t work. I removed all DEFAULT clauses from the table definitions, recreated the database, and re-executed those statements in IRB. Same result. Then I tried setting one column to DEFAULT 0, like you suggested. Same result. Interestingly, the exports from phpMyAdmin at all times showed DEFAULT ''0''. So while the quotes around the 0 may be related to the problem, there doesn''t seem to be anything I can do about it. More likely, it''s just what Jeremy Kemper wrote in another thread: Active Record doesn''t pull the join table column types (yet) so it won''t typecast these attributes for you. If that''s the case, I''ll raise a trac ticket like he suggested. Should it be considered a bug? David? Note, however, that Jeremy''s comment does not explain the lack of typecasting for the ID column. Gavin> On Wed, 24 Nov 2004 21:34:11 +1100, Gavin Sinclair > <gsinclair-81uBx+iSpXA0n/F98K4Iww@public.gmane.org> wrote: >> On Wednesday, November 24, 2004, 8:50:47 PM, David wrote: >> >> >> >> >> The integer columns in my DB tables are returned as strings when >> >> ActiveRecord gets involved. This seems annoying and unnecessary to >> >> me. Surely the database adapter knows, or can know, what data types >> >> are used. Is it ActiveRecord''s policy to ignore this metadata? >> >> > Active Record does type conversion on real columns where it will have >> > queried the database in advanced for the types (using SHOW FIELDS). It >> > doesn''t convert values pulled when you grab the connection yourself nor >> > does it convert piggy-back columns, aggregates, or results from >> > functions. >> >> Hmmmm... I present the following IRB transcript: >> >> >> require ''user'' >> >> User.find(1) >> => #<User:0x320f468 @attributes={"name"=>"Jim Jones", "username"=>"jim", >> "id"=>"1", "password"=>"feral", "email"=>"jim-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org"}> >> >> The ID is a string. >> >> >> require ''game'' >> >> User.find(1).games[0] >> => #<Game:0x31ffbb0 @attributes={"platform"=>"PC", "title"=>"The Sims", >> "price"=>"25", "is_for_trade"=>"1", "barcode"=>"1023425", "id"=>"1", >> "condition"=>"Very good", "user_id"=>"1", "game_id"=>"1"}> >> >> The attributes "price", "is_for_trade", "user_id", and "game_id" are all >> strings. >> >> [...]
On Wed, Nov 24, 2004 at 09:34:11PM +1100, Gavin Sinclair wrote:> On Wednesday, November 24, 2004, 8:50:47 PM, David wrote: > > >> The integer columns in my DB tables are returned as strings when > >> ActiveRecord gets involved. This seems annoying and unnecessary to > >> me. Surely the database adapter knows, or can know, what data types > >> are used. Is it ActiveRecord''s policy to ignore this metadata? > > > Active Record does type conversion on real columns where it will have > > queried the database in advanced for the types (using SHOW FIELDS). It > > doesn''t convert values pulled when you grab the connection yourself nor > > does it convert piggy-back columns, aggregates, or results from > > functions. > > Hmmmm... I present the following IRB transcript: > > >> require ''user'' > >> User.find(1) > => #<User:0x320f468 @attributes={"name"=>"Jim Jones", "username"=>"jim", > "id"=>"1", "password"=>"feral", "email"=>"jim-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org"}> > > The ID is a string.This is how it''s printed out in irb, but what does it say when you do: => #<User:0x1441a50 @attributes={"name"=>"Scott M", "level"=>"3", "username"=>"xxx", "id"=>"8", "department_id"=>"2", "address"=>"xxx@xxx"}> irb(main):010:0> u.id.class => Fixnum You''ll see that irb prints the results with quotes, but it is really a Fixnum. Does that work for you? -Scott
On Thursday, November 25, 2004, 12:35:28 AM, Scott wrote:>> Hmmmm... I present the following IRB transcript: >> >> >> require ''user'' >> >> User.find(1) >> => #<User:0x320f468 @attributes={"name"=>"Jim Jones", "username"=>"jim", >> "id"=>"1", "password"=>"feral", "email"=>"jim-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org"}> >> >> The ID is a string.> This is how it''s printed out in irb, but what does it say when you do:=>> #<User:0x1441a50 @attributes={"name"=>"Scott M", "level"=>"3",> "username"=>"xxx", "id"=>"8", "department_id"=>"2", > "address"=>"xxx@xxx"}>irb(main):010:0>> u.id.class =>> Fixnum> You''ll see that irb prints the results with quotes, but it is really a > Fixnum. Does that work for you?Indeed. Thanks for that. That is bizarre, though. Why on earth is it different? Hmmm.... think I might have answered my own question. The attribute hash holds raw values, and the typecasting is done by method_missing when you call the #id "method". That must be the answer. Gavin
David Heinemeier Hansson
2004-Nov-24 17:32 UTC
Re: MySQL adapter returns integers as strings?
> Hmmm.... think I might have answered my own question. The attribute > hash holds raw values, and the typecasting is done by method_missing > when you call the #id "method". That must be the answer.Active Record uses lazy type casting because it''s pretty expensive to cast everything eagerly. Especially if you''re fetching 100 records and you need to typecast 10 fields on each. This is part of the optimization from 0.8, I think it was, where AR became 300% faster. -- 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
Is there a "production" mode where you don''t expect the database structure to change at all, so there is no need for introspection for every request where the structure of the AR table could be cached until an appserver restart(like under mod_ruby) ? This would save quite a few cpu cycles I would think. Would that help performance much? David Heinemeier Hansson wrote:>> Hmmm.... think I might have answered my own question. The attribute >> hash holds raw values, and the typecasting is done by method_missing >> when you call the #id "method". That must be the answer. > > > Active Record uses lazy type casting because it''s pretty expensive to > cast everything eagerly. Especially if you''re fetching 100 records and > you need to typecast 10 fields on each. This is part of the optimization > from 0.8, I think it was, where AR became 300% faster. > -- > 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-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
David Heinemeier Hansson
2004-Nov-24 17:56 UTC
Re: MySQL adapter returns integers as strings?
> Is there a "production" mode where you don''t expect the database > structure to change at all, so there is no need for introspection for > every request where the structure of the AR table could be cached > until an appserver restart(like under mod_ruby) ? This would save > quite a few cpu cycles I would think.This happens automatically. With FCGI/mod_ruby/cached WEBrick, the "SHOW FIELDS" is only called once per process, then the result is cached. -- 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
David Heinemeier Hansson wrote:> This happens automatically. With FCGI/mod_ruby/cached WEBrick, the "SHOW > FIELDS" is only called once per process, then the result is cached.Ahhhh - nice> -- > 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-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails