On Fri, Apr 18, 2008 at 11:08:24PM +0200, 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).
No. > 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? Write some user-defined functions and a user-defined collation. You could have them use text in CIDR notation, but that could be pretty slow. You could have functions to convert to/from display notation, and then the internal storage format could be an integer, or even as a bit string encoded in text (10/8 -> '00001010', 10.128/9 -> '0000010101') -- not space efficient, but you don't have to write a collation function this way, and you can cut down on the number of user-defined functions you need to write to, I think, just the conversion functions). Incidentally, I find it interesting that there's no way (or did I miss it) to enter numeric literals in bases other than 10. Also, CAST(<blob> AS INTEGER) always returns 0. If, in addition to BLOBs, SQLite had: a native BIT STRING type that differs from BLOBs in that it can be an arbitrary string of bits, rather than bytes, and a way to cast bit strings into integers (where they fit, and/or multi-precision integers), then dealing with CIDR might be easier still. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
