SQLite Forum

can i custom index blob data?

can i custom index blob data?

(1) By davecotter on 2021-07-14 15:08:30 [link] [source]

i can display blob data in a custom way, easily enough, but i want said data to be indexed / searchable / sortable according to how i would display it.

eg: if a binary blob has "file alias data" (which is basically a file path but with extra info such that the file can be found if moved), then in the display i want to show the "resolved" path, i also want to be able to sort by that and search by it.

how can i accomplish this? deterministic user-function? virtual table? relational link?

any ideas? i'm open to suggestions!


(2) By Donald Griggs (dfgriggs) on 2021-07-14 19:08:43 in reply to 1 [source]

Hi Dave,

I'm not confident I understand what you're asking.

If you want to index and sort on data derived from your blobs, then unless space is in very short supply, could you not extract and manipulate this data, then store it in additional columns?

A generated column can even be virtual: https://www.sqlite.org/draft/gencol.html

Alternately, triggers can be used to keep the derivative data in sync with its mother blob.

(3) By David Raymond (dvdraymond) on 2021-07-14 19:19:15 in reply to 1 [link] [source]

Remember that you can make Indexes on expressions, so if the way you're pulling out stuff from the blob is something that can be expressed in SQL with deterministic functions, then you can index on it.