Hi,
IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of the four parts is always three digits long.
IPv4 addresses are 32bit unsigned integers internally. The dotted -quad notation is 4 8bit unsigned integers that get concatenated together. If you store them as a 32bit integer in SQLite then you can use the regular arithmetic operators such as < and > to work out if a given address is in a particular subnet. You can also use the BETWEEN clause.
Over Christmas I was working on an encoding for arbitrary struct-style datatypes that outputs valid UTF-8. It has the interesting property that the encoded values sort in the same order as the decoded values. i.e. the integer 2 sorts before 10 rather than what you'd get if you made the naive conversion to text. The primitive types it knows about include boolean, number, text and blob. The number type is really cool because it can represent any rational number, and the sorting property holds. The primitive types can be combined into compound types and you can control the relative order that different compound types sort in.
I'm using this to implement a database-style system where the data may be in different backends (i.e. SQLite, Postgres or CSV) at different times but I need the ordering and the available types to be stable.
The downside is that it's not "fast" to encode or decode and it's not as compact as a machine native encoding. Having said that, there are operations that can be done directly on the encoded data such as sorting, comparison and composition.
It's not quite on topic for this list but if anyone's interested I can provide more information and links to implementations in Scheme and Javascript.
andy...@ashurst.eu.org http://www.ashurst.eu.org/ http://www.gonumber.com/andyjpb 0x7EBA75FF _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
