On 03/21/2014 10:33 PM, Ben Peng wrote:
Hi, Tristan,
Your solution definitely works (we have defined a few custom functions) but
our application hides databases from users but allows users to use simple
conditions to retrieve results. To use this function, we would have to
1. teach users use this function, which is hard to do because it is
application specific and they need to know which fields need to use this
function.
2. translate user input to use this function internally. We will need to
use a separate table to record the fields that need translation, and change
user input accordingly.
A COLLATE function seems to mark the columns directly and save us from the
trouble of translating user input, so it might be a better solution in this
case.
SQLite assumes that collation sequences are internally consistent. From
the docs:
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.
But this would not be true of the proposed collation sequence. So
although some queries will appear to work, you'll be in trouble if
SQLite ever decides to create an automatic index based on the collation
sequence. Or if anybody ever uses an ORDER BY or GROUP BY clause that
uses it. Various other problems can likely arise if the optimizer starts
commuting expressions and so on, which it sometimes does.
Of course I don't know exactly what kinds of queries you are expecting,
but this seems like the kind of thing that will come back and bite you
to me.
Dan.
On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote:
Dear sqlite experts,
<disclaimer>I'm far from an "sqlite expert", others should be able to
provide a more authoritive answer</disclaimer>
I think what you want is rather to simply define your own custom
function to implement a custom match.
I think using COLLATE is wrong in this case, because the use case of
COLLATE is generally to implement ORDER BY, or perhaps to build a custom
index (to replace the regular strcmp() like behavior) but what you want
is a rather loose matching algorithm.
If you create a function using sqlite3_create_function(), similar to how
you might define a REGEX function, you might execute a query with:
SELECT * FROM table WHERE custom_match (table.column, 42) = 0;
And then, lets assume that "custom_match" uses sqlite3_result_int()
to set the result to 0 if there is a match, or -1 if there is no match,
then you should get a match for any row where table.column = 158;42;76
You can of course give more parameters to "custom_match", so that you
can use invocations like this:
custom_match (GREATER, table.column, 42)
custom_match (EQUALS, table.column, 42)
custom_match (LESSER, table.column, 42)
Make sense ?
Cheers,
-Tristan
I have an application where data stored in columns can be lists of
integers
(e.g. 158;42;76). I cannot really split such record into multiple records
(one for 158, one for 42 etc) and I am currently storing them as VARCHAR
because they represent a complete piece of information. However, when I
select records, I would like to compare or match any of the values. For
example, the record could be selected by either
C > 100 (match 158)
C < 100 (match 42 and 76)
C = 42 (match 42)
From what I have read so far, I could define a special collate function
for
these columns (sqlite3_create_collation etc), but before I jump into the
details, does anyone know
1. Is this the correct way to proceed?
2. Has anyone done anything similar so that I do not have to reinvent the
wheel?
Thank you very much,
Bo
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
