[query] Support SQLite's LIKE for non-fulltext text searching #267

New issue

Open

opened 2020-08-06 16:57:37 +00:00 by gburd · 1 comment

gburd commented

2020-08-06 16:57:37 +00:00

(Migrated from github.com)

While implementing a rough clone of Firefox for iOS's logins handling, I noticed that we don't support SQLite's LIKE operator for non-fulltext text searching. That's what we use in Firefox for iOS, and I don't want to make some of these fields :db/fulltext true, so we should grow support for it. I'm thinking that it'll be a special filtering function, like:

[:find ?e :where
 [?e :credential/name ?t]
 [(string-contains ?t "pattern")]]

although there are a few subtleties. First, "pattern" can be a binding (which is well supported by SQLite). Second, the pattern can contain _ and %, which have special meaning to SQLite. It's easy to escape a constant pattern, but not so easy to escape a binding (coming from elsewhere in the query engine, i.e., another column). We could make escaping the responsibility of the consumer, but that's likely to lead to surprises.

As a first cut, we could only accept literal patterns, which we can escape (or not) and transform concretely.

While implementing a rough clone of Firefox for iOS's logins handling, I noticed that we don't support SQLite's `LIKE` operator for non-fulltext text searching. That's what we use in Firefox for iOS, and I don't want to make some of these fields `:db/fulltext true`, so we should grow support for it. I'm thinking that it'll be a special filtering function, like: ``` [:find ?e :where [?e :credential/name ?t] [(string-contains ?t "pattern")]] ``` although there are a few subtleties. First, `"pattern"` can be a binding (which is well supported by SQLite). Second, the pattern can contain `_` and `%`, which have special meaning to SQLite. It's easy to escape a constant pattern, but not so easy to escape a binding (coming from elsewhere in the query engine, i.e., another column). We could make escaping the responsibility of the consumer, but that's likely to lead to surprises. As a first cut, we could only accept literal patterns, which we can escape (or not) and transform concretely.

thomcc commented

2020-08-06 18:45:59 +00:00

(Migrated from github.com)

Assuming you mean fe32a99a10/components/logins/src/db.rs (L403) (or the equivalent line from swift logins that this replaced)... It's worth noting that the this code is not correct (tricky to change for a bunch of backwards compat reasons, though).

IIRC the actual logic it should have is an eTLD+1 lookup, which is a lot more complex (... actually, this might be the case where it should be just an origin comparison... would need to double-check).

Assuming it is eTLD+1, I think ultimately the idea was eventually mentat would have native URL support (somewhere there's an issue for this) and this operation would either be supported natively on that type, or could be added easily.

(Although supporting calling rust functions bound at runtime (a la sqlite3_create_function and friends) might be a better approach there...)

Assuming you mean https://github.com/mozilla/application-services/blob/fe32a99a108fa771c2155bae13288e922690f096/components/logins/src/db.rs#L403 (or the equivalent line from swift logins that this replaced)... It's worth noting that the this code is not correct (tricky to change for a bunch of backwards compat reasons, though). IIRC the actual logic it should have is an eTLD+1 lookup, which is a lot more complex (... actually, this might be the case where it should be just an origin comparison... would need to double-check). Assuming it is eTLD+1, I think ultimately the idea was eventually mentat would have native URL support (somewhere there's an issue for this) and this operation would either be supported natively on that type, or could be added easily. (Although supporting calling rust functions bound at runtime (a la sqlite3_create_function and friends) might be a better approach there...)