Does anyone know of a library, sample code, etc. to help Oracle PL/SQL do CIDR math?
Not in Oracle, but PostgreSQL has a very robust implementation for CIDR,
including not only datatypes but also a host of operators to deal with them.
Being opensource, it always seemed plausible to me to port the functionality
into Oracle, learning from their implementation. Never got to actual
development, though.
hth,
cl.
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
Seth Mattinen wrote:
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