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