Re: modelling IPv6 as a number

On Wed, 13 Dec 2006 10:51:52 +1100, Geoff Muldoon <geoff.muldoon_at_trap.gmail.com> wrote:

>andy_at_andyh.co.uk says...
>> <geoff.muldoon_at_trap.gmail.com> wrote:
>> >maxwell.dana_at_gmail.com says...
>> >
>> >> If I'm not mistaken, IPv6 requires 39 significant digits to represent
>> >> each possible IP as an integer. Oracle's maximum is number(38). Has
>> >> anyone devised a scheme to store the integer value of IPv6 in oracle?
>> >
>> >Why ever would you store it as a number? It isn't an attribute to which
>> >you can naturally apply *numeric* functions, adding two IPv6 addresses
>> >together doesn't make much sense. Store it as a string.
>>
>> One situation where the fact that an IPv4 address is a number shows up is in
>> netmasks, for working out subnets and broadcast addresses. Presumably there are
>> some similar operations that still apply to IPv6 addresses?
>
>subnet ~ substring

 Well, only for "simple" subnets like 255.255.255.0.

>My general rule: if you add two values together and the result is
>meaningless, it's not a number, it's a string.
>
>Using that rule I win most arguments about also storing values such as
>phone numbers and postal/zip codes as strings instead of numbers.

 No argument at all about those.

 But with IP addresses you can also perhaps argue in a similar way to storing dates; a number _is_ the native form for IP addresses (e.g. in_addr in C), the string value is the human-readable formatted value. You don't store dates as formatted strings, you store them using the native date format.

 And whilst you can't meaningfully add two IPv4 addresses together, you can add 1 to get the adjacent address, and do a limited number of other numeric operations on it (e.g. netmasks again).

 (But looking back at the thread it seems I've basically just said what Maxim already posted, oh well I've typed it now :p)

 So storage as a varchar2 is probably fine unless the OP really needs these operations. The C struct in6_addr presents itself as 16 unsigned 8-bit values, so the OP could even go for 16 number(3) columns in a table; if he's working in C it might make it easier to load and save, although it'd be a weird table and probably no help for languages other than C!

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool