Thanks Dan, I think I get your point. COLLATE not only provides a new way to compare values (what is what I need), but also gives new appearances to existing values by which they are sorted or grouped. So 158;42;76 can not appear as 158, 42, and 76 at the same time (158;42;76 == 42, 158;42;76 == 76, does not imply 42 == 76).
I guess I will have to take the longer route, namely define a customized comparison function and translate user input internally. Thanks again for all the help, I am glad that I asked before I write any code. Bo On Fri, Mar 21, 2014 at 10:44 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
