Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On 11 Jul 2012, at 18:36, Valentin Davydov wrote: > This is for IPv4 at least. No. This is for IPv4 ONLY. That doesn't meet my needs. /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On Wed, Jul 11, 2012 at 12:36 PM, Valentin Davydov wrote: > Individual IP addresses are nicely supported in the form of unsigned > integers, and prefixes/ranges - as contiguous ranges of such integers. > For example, to determine whether given IP address belongs to a

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On Mon, Jul 02, 2012 at 05:05:25PM +0100, Niall O'Reilly wrote: > > On 2 Jul 2012, at 16:13, Nico Williams wrote: > > > That reminds me: it'd be nice to have a bit string type, since the > > correct way to sort IPv4 CIDR blocks is as bit strings. > > Nice, definitely! > > > This is also

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On Mon, Jul 2, 2012 at 1:14 PM, Simon Slavin wrote: > Your argument is for SQLite to allow users to implement their own affinities > (datatypes ?) must like SQLite3 allows users to implement collation > algorithms and functions. But I don't think you're going to get

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On 2 Jul 2012, at 6:27pm, Nico Williams wrote: > A pair of built-in functions could take care of the user-friendliness > aspect to some degree, but built-in literals for bit string and IPv4/6 > CIDR notation would so much more user-friendly... IMO it's worth > doing.

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On Mon, Jul 2, 2012 at 12:11 PM, Niall O'Reilly wrote: > On 2 Jul 2012, at 17:52, Nico Williams wrote: >> So an IPv4 CIDR block like 10.2.93.128/25 would encode as x'0A025D81' >> and 10.2.93.128/26 as x'0A025D82', and so on, with 10.2.93.128/32 >> encoded as x'0A025D8000'

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

On 2 Jul 2012, at 17:52, Nico Williams wrote: > So an IPv4 CIDR block like 10.2.93.128/25 would encode as x'0A025D81' > and 10.2.93.128/26 as x'0A025D82', and so on, with 10.2.93.128/32 > encoded as x'0A025D8000' (that's 5 bytes). That is, IPv4 addresses > would require one more byte than

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

So an IPv4 CIDR block like 10.2.93.128/25 would encode as x'0A025D81' and 10.2.93.128/26 as x'0A025D82', and so on, with 10.2.93.128/32 encoded as x'0A025D8000' (that's 5 bytes). That is, IPv4 addresses would require one more byte than usual. I'm not sure that we can justify the extra complexity

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

Ah, if you encode any bit string as a BLOB such that it ends in 3 bits that encode the length of the string mod 8, and with 7 - length of string mod 8 preceding zero-valued bits then you get a result that should sort [lexicographically] correctly, no? So bit string would be a trivial extension of

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

The key is to come up with a bit string encoding in bytes that is suitable for use in table keys -- they have to sort correctly when sorted lexicographically. The encoding should be reasonably efficient; one byte per-bit, for example, would be too inefficient (though in a pinch much better than