Re: modelling IPv6 as a number

Sorry to be so slow to respond, I've been distracted by another project lately.

My reasons for storing the IPs as numbers is for searching purposes. I have a list of ip addresses (numeric) and subnets (start and end IPs in numeric) it's now easy for me to search (where '1234' between startIP and endIP) or even perform a join. Aside from uniqueness issues associated with NAT, this works great for IPv4. But IPv6 creates a new problem.

My only solution so far is to create an extra column for the left most digit but now every query get more cumbersome because 2 columns are involved and if I'm ranging across two or more left most digits...the query becomes a pain that is more error prone.

I've heard that "Oracle Spatial" allows you to create your own data types. Does anybody know if that's true and if I can use it to create the equivalent of a number(39)?

Thanks

Dana

On Dec 12, 8:28 pm, Andy Hassall <a..._at_andyh.co.uk> wrote:
> On Wed, 13 Dec 2006 10:51:52 +1100, Geoff Muldoon
>
>
>
> <geoff.muld..._at_trap.gmail.com> wrote:
> >a..._at_andyh.co.uk says...
> >> <geoff.muld..._at_trap.gmail.com> wrote:
> >> >maxwell.d..._at_gmail.com says...
>
> >> >> If I'm not mistaken,IPv6requires 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 ofIPv6in oracle?
>
> >> >Why ever would you store it as a number? It isn't an attribute to which
> >> >you can naturally apply *numeric* functions, adding twoIPv6addresses
> >> >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 toIPv6addresses?
>
> >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 :: a...@andyh.co.uk ::http://www.andyh.co.ukhttp://www.andyhsoftware.co.uk/space:: disk and FTP usage analysis tool
Received on Wed Dec 20 2006 - 12:00:00 CST