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
