On Fri, Apr 26, 2019 at 7:36 PM Jens Alfke <j...@mooseyard.com> wrote:
> We are using SQLite blobs to store some structured values, and need > control over how they are collated in queries, i.e. memcmp is not the > correct ordering. We’ve registered a custom collating function, but > unfortunately it doesn’t get called. According to the docs, collating > functions are only used to compare strings, not any other data type. Is > there any way around this limitation? > > The only workaround I can think of is to define a custom function that > converts a blob to a string and collate using those strings — e.g. `… ORDER > BY collatable_blob(b)`. But this requires expensive string conversions, and > it doesn’t work well with indexes. > > It would be much cleaner and more efficient if there were a type of > collating function that operated on all data types. > You are hitting what is IMHO one of the last big remaining "hole" in SQLite, which is its lack of User-Defined Types (UDTs) for persistent columns. (for transient values in function call chains, we have subtypes nowadays, which is as close as a UDT as SQLite as). Your blob obviously has internal structure, and while you're happy to store it as an opaque blob, you'd like to have it sort differently. You can of course work-around having a real UDT by assigning a custom collation to the column, but really it belongs on the "type", not the column. Coming back on your more specific question, aside from DRH adding support for User-Defined Collations to blob columns (a long shot I suspect), you could store your "structured values" as strings instead of blobs. AFAIK, SQLite does not check the string is valid UTF8, so can store arbitrary binary too. As long as you do not perform encoding conversions UTF8 <-> UTF16, and you do not call SQL functions assuming valid UTF8, you should be fine. And now you can have your custom collation. Note friendly to users of the DB that might see garbage when printing those "binary" strings though. But at this point, that's the only thing you can do that avoid the expensive conversions you pointed out. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
