Christof Meerwald wrote:
>
> does SQLite have anything similar to PostgreSQL's cidr data type (see
> http://www.postgresql.org/docs/8.3/interactive/datatype-net-types.html).
>
> I would be particularly interested in being able to use a "contained in"
> operator in selects - in PostgreSQL you are able to do:
>
> select inet '192.168.18.52' << cidr '192.168.0.0/16';
>
> What would be the preferred way to do it in SQLite?
>
> Currently, I am thinking of storing start and end IP addresses as a blob in
> the database - that way I would be able to use the "between" operator in
> selects, e.g.
>
> select X'c0a81234' between X'c0a80000' and X'c0a8ffff';
>
>
> Are there any other/better ideas?
>
I would store the IP addresses, network addresses, and subnet width as
integers. Then create a few custom function to manipulate them.
You will need functions to convert between integer and standard dotted
quad text format. You will also need a function to do the containedIn
range check.
This last function can be implemented using bit manipulation operators
in SQL or in a custom function in C.
containedIn(ip_addr, network_addr, network_size)
can be replaced by
nework_addr == (ip_addr & (-1 << network_size))
which will be true if the IP address is in the network.
This can be done in a custom function as well which may be more efficient.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
