Encoded Columns and ActiveRecord

Rails
1

Hi all,

I am trying store IP addresses in a database. In my old PHP days when I
wrote the SQL i used the INET_ATON and INET_NTOA functions in MySQL to
to the conversions of the address when I inserted or pulled the rows
from the database. I would like to do something similar with my
ActiveRecord model in Rails as storing the IP as an integer is much more
efficient than in its dot format.

My question is how do I do it? Is there a way for me to setup my model
so that whenever it saves it converts the stored IP and when it finds a
record it automatically converts it back to the dot format? Is there a
way I can use the MySQL functions? Or could I just always keep it in
the integer state, but have accessors in the class convert it when I
access it?

Thanks

Peer

2

On Fri, 2007-06-01 at 22:47 +0200, Peer A. wrote:

My question is how do I do it? Is there a way for me to setup my model
so that whenever it saves it converts the stored IP and when it finds a
record it automatically converts it back to the dot format? Is there a
way I can use the MySQL functions? Or could I just always keep it in
the integer state, but have accessors in the class convert it when I
access it?

Yow, no. Wait, yes. You could use a activerecord callback for
before_save to change the IP before writing to the database:

http://railsmanual.com/module/ActiveRecord::Callbacks

but there is no callback for retrieval. Ahha, but we can just overwrite
the method in the model:

def ip
self.ip.gsub /2/, ‘3’
end

Would replace 2s with 3s.

But really, why not just store it as a string? Why do you want to store
it as a number? It isn’t a number, it’s an IP.

Good luck!

–
Matthew B. :: 607 227 0871
Resume & Portfolio @ http://madhatted.com

3

Hi Matthew,

Here are some of the reasons why I want to store the IP a a number:

http://forums.mysql.com/read.php?21,49094,49094#msg-49094

The table that I will be using will be getting large (a similar system
we have right now is growing at about 1 million records a month) and
using 4 bytes instead of 15 will really add up over time. (Yes, I know
how cheap storage is nowadays, but I would like to avoid the adventure
of a full drives as long as possible, been there done that!)

I was looking to see if there was a way if I could mimic the “serialize”
functionality, but I’m afraid I am going to have to study ActiveRecord a
lot more first.

Peer

Matthew B. wrote:

On Fri, 2007-06-01 at 22:47 +0200, Peer A. wrote:

My question is how do I do it? Is there a way for me to setup my model
so that whenever it saves it converts the stored IP and when it finds a
record it automatically converts it back to the dot format? Is there a
way I can use the MySQL functions? Or could I just always keep it in
the integer state, but have accessors in the class convert it when I
access it?

Yow, no. Wait, yes. You could use a activerecord callback for
before_save to change the IP before writing to the database:

http://railsmanual.com/module/ActiveRecord::Callbacks

but there is no callback for retrieval. Ahha, but we can just overwrite
the method in the model:

def ip
self.ip.gsub /2/, ‘3’
end

Would replace 2s with 3s.

But really, why not just store it as a string? Why do you want to store
it as a number? It isn’t a number, it’s an IP.

Good luck!

–
Matthew B. :: 607 227 0871
Resume & Portfolio @ http://madhatted.com

4

require ‘ipaddr’

INET_ATON

IPAddr.new(“192.168.0.10”).to_i # => 3232235530

INET_NTOA

IPAddr.new(3232235530, Socket::AF_INET).to_s # => “192.168.0.10”

HTH

  • H

On 6/2/07, Peer A. [email protected] wrote:

using 4 bytes instead of 15 will really add up over time. (Yes, I know

On Fri, 2007-06-01 at 22:47 +0200, Peer A. wrote:

–
Posted via http://www.ruby-forum.com/.

–
Husein C.
Yucca Intelligence Development

5

Hi Peer,

You can store the IP as an integer in the database and convert to and
from a string using Ruby. Check out this class:

http://ruby-doc.org/stdlib/libdoc/ipaddr/rdoc/classes/IPAddr.html

This seems to work:

class Address < ActiveRecord::Base

require ‘IPAddr’

def self.find_by_ip(value)
find :first, :conditions => {:ip => IPAddr.new(value).to_i}
end

Convert from integer - IPAddr doesn’t seem to have this method

def ip
[read_attribute(:ip)].pack(“N”).unpack(“C*”).join “.”
end

def ip=(value)
write_attribute(:ip, IPAddr.new(value).to_i)
end

end

Hope this helps.

-=nathan

6

Thanks Husein,

I think I will combining all the suggestions I have received on this
thread so that I end up with something like this:

class Address < ActiveRecord::Base

require ‘IPAddr’
before_save :convert_ip

def self.find_by_ip(value)
find :first, :conditions => {:ip => IPAddr.new(value).to_i}
end

def ip
convert_ip
IPAddr.new(read_attribute(:ip), Socket::AF_INET).to_s
end

def ip=(value)
write_attribute(:ip, IPAddr.new(value).to_i)
end

def convert_ip
if read_attribute(:ip) =~ /^(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})?$/
write_attribute(:ip, IPAddr.new(read_attribute(:ip)).to_i)
end
end

end

I just whipped this up so I obviously have not tested it in any way.

Peer

Husein C. wrote:

require ‘ipaddr’

INET_ATON

IPAddr.new(“192.168.0.10”).to_i # => 3232235530

INET_NTOA

IPAddr.new(3232235530, Socket::AF_INET).to_s # => “192.168.0.10”

HTH

  • H

On 6/2/07, Peer A. [email protected] wrote:

using 4 bytes instead of 15 will really add up over time. (Yes, I know

On Fri, 2007-06-01 at 22:47 +0200, Peer A. wrote:

–
Posted via http://www.ruby-forum.com/.

–
Husein C.
Yucca Intelligence Development
http://www.YuccaHQ.com