Re: PL/SQL & CIDR?

[David Andersen <dga () cs cmu edu>]

On Mar 12, 2010, at 9:36 PM, Matthew Kaufman wrote:

> Seth Mattinen wrote:
> > On 3/12/2010 09:13, J.D. Falk wrote:
> >  
> > > Does anyone know of a library, sample code, etc. to help Oracle PL/SQL do CIDR math?
> > >
> > >    
> >
> >
> > Not exactly sample code, but: I do that with MySQL by storing the IP as
> > its integer value and using simple comparisons to see if that stored
> > value is within the range of values that a given CIDR mask represents.
> > Works great for IPv4 and IPv6 addresses.
> >
> > ~Seth
> >
> >  
> I do it in MySQL by storing the IP as an integer and the mask as an integer and using bitwise operators in the SELECT.
>
> Just something to think about...

To expand upon this, we do this in pure SQL as Matthew suggested by generating the sql automatically.  e.g., to find 
all routes in a BGP table that equal or contain a particular prefix:

SELECT * from table 
WHERE (prefix = x AND mask=32)
   OR (prefix = x & 0xfffffffe AND mask=31)
   OR ...

(we store BGP entries as prefix, mask).

You can write a user defined function to do this for you in many languages.

We chose that expansion because it worked well with the indexes defined on prefix/mask.  You can also express it as 
CIDR math as bitwise operators, though we've found that doing so tends to destroy any indexing you've created:

SELECT mask, prefix from t1
  WHERE (search_prefix & ((!0) << (32 - t1.mask)) = t1.prefix)
  ...

I can probably cough up a few more examples from our codebase if you want them.

  -Dave