Issue: Regular Expressions for SQLite always case-insensitive
Author: phryk
State: closed
Created: 2017-03-14 03:49:43

https://github.com/coleifer/peewee/blob/master/peewee.py#L306

You're statically passing the `IGNORECASE` (`re.I`) flag for regexps in SQLite.
This makes it impossible to match only lower-case letters, which is kind of a
big deal for `CHECK` constraints using regular expressions.

I'd like to ask for this flag to simply be dropped from the `re.search` call.

I realize this might introduce an issue with backwards compatibility for some of
the people who currently depend on the case-insensitivity (if any). But nothing
those people are doing now will be impossible; Worst case they'll have to add
`A-Z` into some of their regexps…

This is a backwards-incompatible change, as you noted, but I think a sensible one. In the meantime, why not just register your own regexp function?

```python
db = SqliteExtDatabase(...)

@db.func('regex')
def regex(rgx, s):
    return re.search(rgx, s) is not None
```

9be3341940616b781f2b0948bbee6eb5872cba64 -- I added a 3rd optional parameter, `case_sensitive` which defaults to `False`. You can pass `True` to get the desired behavior.

Thanks for the help thus far. But I am not sure the committed fix is applicable for me.

If I understand it correctly, I can only pass a python `bool` to that function if I call it via
`peewee.fn.regexp`. However, I have a the following function to build a `CHECK` `Clause`:
```
def RegexpConstraint(field_name, regexp):

    return peewee.Clause(
            peewee.SQL('CHECK('),
            peewee.Expression(
                QuotedSQL(field_name),
                peewee.OP.REGEXP,
                QuotedSQL(regexp),
                flat=True
            ),
            peewee.SQL(')'),
    )
```

Usage: `name = CharField(index=True, unique=True, null=False, constraints=[RegexpConstraint('name', '^[a-z0-9_\-]+$')])`

Seems to me that even if I can figure out how to pass extra parameters via SQL within
the `CHECK` clause, I won't be able to pass in a `False`. Plus, even if that is possible,
it'll probably make that bit of code incompatible with anything but SQLite.

Can I adapt my `RegexpConstraint` to pass `False` to _sqlite_regexp or I should just opt
for the `@db.func` approach?

Another thing to consider is that PostgreSQL and MySQL both do regular expressions
case-sensitively by default. This might bite people in the arse, if they want to support multiple
DBMSes in a project that uses peewee.

Wandering the wide web and the IRCs of this world, I found that the best practice for making
a query using regular expressions case-insensitive is apparently doing it like:
`LOWER(col) REGEXP "^.*$"`

I was told actually using case-insensitive regular expressions (like with the `~*` operator for Postgres)
in `WHERE` clauses can lead to performance issues due to indexing problems, but I can't really claim to have understood that in its entirety.

You can pass in `0` of course, but why not just write your own user-defined function that behaves exactly as you want?

That's what I'll do then. Mostly wanted to make sure not to add any unneeded code that might make maintenance harder in the future. Thanks for the reply. :)