On 9/8/15, Eduardo Morras <emorrasg at yahoo.es> wrote:
>
>
> Hello,
>
> I have a virtual table that implements query perceptual hashing data[1]. Now
> I'm thinking about converting the virtual table implementation in a
> collation on a normal sqlite3 table, but collation requieres that '=','<'
> and '>' be well defined by obeying the rules cited on create_collation()
> page[2]. Sometimes, rule 2 may not be true, but I always query for '=',
Yes, but under the hood, SQLlite never does an == query on the b-trees
even if you ask for a == query in the SQL. Instead, the b-trees are
queried using one of >, >=, <, or <=. A query of the form:
x=$value
Gets translated (at the b-tree layer) into
x>=$value AND x<=$value
So it is *very* important that the comparison operators all work
correctly on your collating sequence function. If they don't, then
SQLite will give incorrect answers.
> never need sort query output and result is unique.
> If I calculate the perceptual hash of an input, I want to get the closer
> (minimal distance) hash in the table calculating equal . Can I use a
> collation in this case?
>
> [1] Perceptual Hashing: Hash function that similar input data has equal or
> similar hash.
> [2] http://www.sqlite.org/c3ref/create_collation.html
> 1.If A==B then B==A.
> 2.If A==B and B==C then A==C.
> 3.If A<B THEN B>A.
> 4.If A<B and B<C then A<C.
>
> --- ---
> Eduardo Morras <emorrasg at yahoo.es>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
drh at sqlite.org
