Hey all, I am working on a project that is functional under MySQL but breaks under PostgreSQL. I''ve tracked the problem down to the usage of My''s inet_aton function [1] which PostgreSQL does not implement. I''ve been digging through google for awhile, and while I found somone else with the same problem [2], I didn''t find a viable solution that will work with both DBs. I am thinking that this might be best fixed by reimplementing the inet_aton functionality. I''m not sure, though, if this should eventually go to the project''s libraries, the postgreSQL ruby adapter, the SQL translater library (whatever that is called), Rails, or who knows where. Thoughts? -Jeff [1] http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html [2] http://www.issociate.de/board/post/44118/inet_aton_in_mysql,_how_to_convert_it_to_postresql?.html
> I am working on a project that is functional under MySQL but breaks > under PostgreSQL. I''ve tracked the problem down to the usage of My''s > inet_aton function [1] which PostgreSQL does not implement. I''ve been > digging through google for awhile, and while I found somone else with > the same problem [2], I didn''t find a viable solution that will work > with both DBs. > > I am thinking that this might be best fixed by reimplementing the > inet_aton functionality. I''m not sure, though, if this should > eventually go to the project''s libraries, the postgreSQL ruby adapter, > the SQL translater library (whatever that is called), Rails, or who > knows where. > > Thoughts? > -JeffI ran into a similar issue with mysql date functions. I was using UTC_TIMESTAMP() to do all querying, but that''s not implemented in postgresql. I simply just added a small stored procedure to do what I wanted for postgresql without touching my code. Another option is to put this in the connection adapters. # lib/connection_adapters.rb class ActiveRecord::ConnectionAdapters::AbstractAdapter.class_eval def inet_aton(value = ''?'') "inet_aton(#{value})" end end class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval def inet_aton(value = ''?'') "pgsql_inet_aton(#{value})" end end Add a require_dependency ''connection_adapters'' to environment.rb and you''re set to go! find :all, :conditions => ["#{connection.inet_aton} = ?", a, b] -- rick http://techno-weenie.net
Rick, Thanks for the tip. After some reflection I thought to myself "Ruby must have some built-in IP handling." A quick glance through the pickaxe and I found what I needed. The original (MySQL-only) method: def self.find_by_ip_address(ip_address) find (:first, :conditions => ["IP_FROM <= inet_aton(?) AND IP_TO >= inet_aton(?)", ip_address, ip_address]) end The database-agnostic solution: def self.find_by_ip_address(ip_address) ip = IPAddr.new(ip_address) find(:first, :conditions => ["IP_FROM <= ? AND IP_TO >= ?", ip.to_i, ip.to_i]) end The .to_i functionality is the same as inet_aton, namely converting an ip of the form 000.000.000.000 to an integer representation. One for the archives, Jeff Rick Olson wrote:> I ran into a similar issue with mysql date functions. I was using > >UTC_TIMESTAMP() to do all querying, but that''s not implemented in >postgresql. I simply just added a small stored procedure to do what I >wanted for postgresql without touching my code. > >Another option is to put this in the connection adapters. > ># lib/connection_adapters.rb >class ActiveRecord::ConnectionAdapters::AbstractAdapter.class_eval > def inet_aton(value = ''?'') > "inet_aton(#{value})" > end >end > >class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval > def inet_aton(value = ''?'') > "pgsql_inet_aton(#{value})" > end >end > >Add a require_dependency ''connection_adapters'' to environment.rb and >you''re set to go! > >find :all, :conditions => ["#{connection.inet_aton} = ?", a, b] > >
Jeff Casimir wrote:> Hey all, > > I am working on a project that is functional under MySQL but breaks > under PostgreSQL. I''ve tracked the problem down to the usage of My''s > inet_aton function [1] which PostgreSQL does not implement. I''ve been > digging through google for awhile, and while I found somone else with > the same problem [2], I didn''t find a viable solution that will work > with both DBs. >If this is just a function, write the function and define it inside the databse. If it is a special datatype, you can define those too.