PL/SQL & CIDR?
David Andersen
dga at cs.cmu.edu
Sat Mar 13 02:46:53 UTC 2010
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
More information about the NANOG
mailing list