[123691] in North American Network Operators' Group
Re: PL/SQL & CIDR?
daemon@ATHENA.MIT.EDU (David Andersen)
Fri Mar 12 21:47:11 2010
From: David Andersen <dga@cs.cmu.edu>
In-Reply-To: <4B9AFA18.5090701@matthew.at>
Date: Fri, 12 Mar 2010 21:46:53 -0500
To: matthew@matthew.at
Cc: nanog@nanog.org
Errors-To: nanog-bounces+nanog.discuss=bloom-picayune.mit.edu@nanog.org
On Mar 12, 2010, at 9:36 PM, Matthew Kaufman wrote:
> Seth Mattinen wrote:
>> On 3/12/2010 09:13, J.D. Falk wrote:
>> =20
>>> Does anyone know of a library, sample code, etc. to help Oracle =
PL/SQL do CIDR math?
>>>=20
>>> =20
>>=20
>>=20
>> 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.
>>=20
>> ~Seth
>>=20
>> =20
> 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.
>=20
> 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=20
WHERE (prefix =3D x AND mask=3D32)
OR (prefix =3D x & 0xfffffffe AND mask=3D31)
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)) =3D t1.prefix)
...
I can probably cough up a few more examples from our codebase if you =
want them.
-Dave
