[sqlite] Searching a table of patterns

[David Rayna]

One thing I have occasionally desired is be able to search a table of 
patterns given a string that might match some rows.
This is the reverse of searching a table of strings to find ones that 
match a pattern.
I was wondering if anyone else has come across this and if a small 
change to sqlite might make it possible.
In a few cases both at the same time would have been useful (find rows 
where pattern in column A matches value X and value in column B matches 
pattern Y).

[sqlite] Using a "personal" partial index

[David Rayna]

I got excited when I saw the "indexed by" clause.
A technique I used years ago with FoxPro & etc was to allow each user to 
create his own personal index of the same data.
FoxPro allowed storing index files separately and on each user's own 
local C: drive.
The index was on a complex computed "quality" value based on multiple 
table fields allowing each user to create his own ranking of the data 
based on his own input terms.

In sqlite, would I give each user their own name for the index which he 
could create, drop and redefine as desired?
How complex can the index value computation be? Would I need to 
implement it in C and access via sqlite source code?
Can select ... indexed by  work as a pre-filtering and sorting of the 
data for each user's own view which could be filtered more if needed?