Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-04 Thread Steven E. Harris
Igor Tandetnik writes: > It doesn't matter. The whole point is that the key is constructed in > such a way that both procedures would arrive at the same result. That's really what was at the heart of my question: If separate value-by-value was necessary, then I would conclude that the concatenat

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Richard Hipp
On Tue, Jul 3, 2012 at 3:59 PM, Igor Tandetnik wrote: > On 7/3/2012 3:50 PM, Steven E. Harris wrote: > >> The first paragraph mentions that the encoding allows comparison of keys >> with memcmp(), which makes it sound like an entire key -- meaning the >> concatenation of several values -- can be

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Igor Tandetnik
On 7/3/2012 3:50 PM, Steven E. Harris wrote: The first paragraph mentions that the encoding allows comparison of keys with memcmp(), which makes it sound like an entire key -- meaning the concatenation of several values -- can be be compared in one operation against another key. The second parag

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Steven E. Harris
Richard Hipp writes: > The keys are encoded (see > http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way > that causes a lexicographical ordering of the keys to correspond to > what the user wants out of ORDER BY. The first paragraph mentions that the encoding allows comparison of

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Steven E. Harris
Richard Hipp writes: > The keys are encoded (see > http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way > that causes a lexicographical ordering of the keys to correspond to > what the user wants out of ORDER BY. I don't understand why the example entries for values 99.0, 99.01,

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Steven E. Harris
Richard Hipp writes: > The keys are encoded (see > http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way > that causes a lexicographical ordering of the keys to correspond to > what the user wants out of ORDER BY. I don't understand why the example entries for values 99.0, 99.01,

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-02 Thread Steven E. Harris
Richard Hipp writes: > The keys are encoded (see > http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way > that causes a lexicographical ordering of the keys to correspond to > what the user wants out of ORDER BY. I don't understand why the example entries for values 99.0, 99.01,

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-02 Thread Cory Nelson
On Fri, Jun 29, 2012 at 5:58 PM, Richard Hipp wrote: > > On Fri, Jun 29, 2012 at 6:40 PM, Nico Williams wrote: > > > On Fri, Jun 29, 2012 at 5:24 PM, Richard Hipp wrote: > > > varint+value does not sort BLOBs in lexicographical order. > > > > > > Not having a distinct terminator for the BLOB mean

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-02 Thread Niall O'Reilly
On 2 Jul 2012, at 18:20, Jay A. Kreibich wrote: > The idea of using a plugin system to expand database functionality > seems to fit well with the SQLite way of getting things done. > Functions, collations, and virtual tables are already done in a > similar way. Extending that to types seems

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-02 Thread Jay A. Kreibich
On Mon, Jul 02, 2012 at 10:13:13AM -0500, Nico Williams scratched on the wall: > 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. This is also > a proper way to sort IPv6 blocks. Alternatively, it'd be nice to have > nat

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-02 Thread Nico Williams
On Mon, Jul 2, 2012 at 4:29 AM, Niall O'Reilly wrote: > > On 29 Jun 2012, at 23:58, Richard Hipp wrote: > >> But you know: How often do people use BLOBs as keys? What other SQL >> engines other than SQLite even allow BLOBs as keys? Are we trying to >> optimize something that is never actually u

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-02 Thread Simon Slavin
On 2 Jul 2012, at 10:29am, Niall O'Reilly wrote: > On 29 Jun 2012, at 23:58, Richard Hipp wrote: > >> But you know: How often do people use BLOBs as keys? What other SQL >> engines other than SQLite even allow BLOBs as keys? Are we trying to >> optimize something that is never actually used?

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-02 Thread Niall O'Reilly
On 2 Jul 2012, at 10:51, Dan Kennedy wrote: > That would be a reasonable use. But the blob in this case will be what, > eight bytes (or 10 in its encoded form)? 10, 18, 34, or 66, depending on which of six classes [*] of object is involved, using the encoding I have in mind at th

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-02 Thread Dan Kennedy
On 07/02/2012 04:29 PM, Niall O'Reilly wrote: On 29 Jun 2012, at 23:58, Richard Hipp wrote: But you know: How often do people use BLOBs as keys? What other SQL engines other than SQLite even allow BLOBs as keys? Are we trying to optimize something that is never actually used? For

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-02 Thread Niall O'Reilly
On 29 Jun 2012, at 23:58, Richard Hipp wrote: > But you know: How often do people use BLOBs as keys? What other SQL > engines other than SQLite even allow BLOBs as keys? Are we trying to > optimize something that is never actually used? For an IPAM application I have on my back burner

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-06-29 Thread Nico Williams
OK, I give :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-06-29 Thread Richard Hipp
On Fri, Jun 29, 2012 at 6:40 PM, Nico Williams wrote: > On Fri, Jun 29, 2012 at 5:24 PM, Richard Hipp wrote: > > varint+value does not sort BLOBs in lexicographical order. > > > > Not having a distinct terminator for the BLOB means that two BLOBs where > > one is a prefix of the other might not c

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-06-29 Thread Nico Williams
On Fri, Jun 29, 2012 at 5:24 PM, Richard Hipp wrote: > varint+value does not sort BLOBs in lexicographical order. > > Not having a distinct terminator for the BLOB means that two BLOBs where > one is a prefix of the other might not compare correctly. Would 31-bit encoding help? __

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-06-29 Thread Richard Hipp
On Fri, Jun 29, 2012 at 6:09 PM, Nico Williams wrote: > On Fri, Jun 29, 2012 at 4:39 PM, Cory Nelson wrote: > > On Fri, Jun 29, 2012 at 2:48 PM, Richard Hipp wrote: > > What is the rationale for the 7-bit BINARY encoding? The performance > impact > > will surely outweigh any convenience of being

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-06-29 Thread Nico Williams
On Fri, Jun 29, 2012 at 4:39 PM, Cory Nelson wrote: > On Fri, Jun 29, 2012 at 2:48 PM, Richard Hipp wrote: > What is the rationale for the 7-bit BINARY encoding? The performance impact > will surely outweigh any convenience of being able to treat blobs as > 0-terminated strings. I tend to agree.

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-06-29 Thread Cory Nelson
On Fri, Jun 29, 2012 at 2:48 PM, Richard Hipp wrote: > On Fri, Jun 29, 2012 at 3:40 PM, Nico Williams >wrote: > > > So, if I understand section 3.2 of the SQLite4 design page then it > > will often be the case that lookup keys will not be stored in an order > > that will be useful for optimizing

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-06-29 Thread Nico Williams
On Fri, Jun 29, 2012 at 2:48 PM, Richard Hipp wrote: > On Fri, Jun 29, 2012 at 3:40 PM, Nico Williams wrote: >> So, if I understand section 3.2 of the SQLite4 design page then it >> will often be the case that lookup keys will not be stored in an order >> that will be useful for optimizing common

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-06-29 Thread Richard Hipp
On Fri, Jun 29, 2012 at 3:40 PM, Nico Williams wrote: > So, if I understand section 3.2 of the SQLite4 design page then it > will often be the case that lookup keys will not be stored in an order > that will be useful for optimizing common ORDER BY expressions. Is > this correct? Not correct.

[sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-06-29 Thread Nico Williams
So, if I understand section 3.2 of the SQLite4 design page then it will often be the case that lookup keys will not be stored in an order that will be useful for optimizing common ORDER BY expressions. Is this correct? If so, is this worth the trade-off for the single key/value storage complexity