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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users