FEATURE | Add support for LIKE operator in WHERE dicts

[raindrum]

✅ Describe your idea or issue

SQLite allows for a LIKE operator that performs wildcard searches, but as far as I can tell, it's not accessible in SQLLEX, at least not when criteria are given as a dict, like this:

jerrys = db['users'].select(ALL, WHERE={'name': ['LIKE', 'Jerry%']})

or this:

jerrys = db['users'].select(ALL, WHERE={'name': ['%', 'Jerry%']})

Would you be willing to add support for this?

✅ Additional context

It's possible to enable support, at least in this specific use-case, by adding "LIKE" to the recognized operators listed here, and patching it to add spaces when necessary, like so:

                    if len(values) > 1 and values[0] in [
                        "<", "<<", "<=",
                        ">=", ">>", ">",
                        "=", "==", "!=",
                        "<>", "LIKE",
                    ]:
                        operator = values.pop(0)
                        if operator == 'LIKE':
                           operator = ' LIKE '

But that's a pretty hacky solution, and I don't know how it interacts with the other ways to write WHERE statements, so I figured I would file an issue instead of a PR.

[v1a0]

Added to sqllex v0.2.0.0

LIKE support + some syntax sugar

Added LIKE support, and some syntax sugar I called "column-like-regex"

from sqllex import SQLite3x, AbstractTable, AbstractColumn, LIKE

db: SQLite3x = ...
cats: AbstractTable = db['cats']
cats_color: AbstractColumn = cats['color']

dark_cats = cats.select(
    SELECT=('id', 'color'),
    WHERE=(cats_color |LIKE| "%dark%")
)

print(dark_cats)  # [(14, "dark-grey"), (42, "dark-red")]


# another way (issue #39)
dark_cats = cats.select(
    SELECT=('id', 'color'),
    WHERE={
        'color': [LIKE, "%dark%"]
    }
)

print(dark_cats)  # [(14, "dark-grey"), (42, "dark-red")]