# HG changeset patch
# User Andrew Shadura <andrew@shadura.me>
# Date 2016-01-31 16:51:32
# Node ID 69e7385231079197e28e197da589ea082b45a00d
# Parent  b3a51c3987be234ec43dab75fd54311c5745d595

db: match case-insensitively using func.lower, not ilike

ilike() uses SQL ILIKE operator internally, which means it interprets
'%' and '_' in the match pattern as wildcards. Instead of ilike(), it's
better to turn both operands to the lower case and compare them.

This also unbreaks the test case introduced in 13d0fe6f751a.

diff --git a/kallithea/model/db.py b/kallithea/model/db.py
--- a/kallithea/model/db.py
+++ b/kallithea/model/db.py
@@ -555,7 +555,7 @@ class User(Base, BaseModel):
     @classmethod
     def get_by_username(cls, username, case_insensitive=False, cache=False):
         if case_insensitive:
-            q = cls.query().filter(cls.username.ilike(username))
+            q = cls.query().filter(func.lower(cls.username) == func.lower(username))
         else:
             q = cls.query().filter(cls.username == username)
 
@@ -592,7 +592,7 @@ class User(Base, BaseModel):
 
     @classmethod
     def get_by_email(cls, email, cache=False):
-        q = cls.query().filter(cls.email.ilike(email))
+        q = cls.query().filter(func.lower(cls.email) == func.lower(email))
 
         if cache:
             q = q.options(FromCache("sql_cache_short",
@@ -602,7 +602,7 @@ class User(Base, BaseModel):
         if ret is None:
             q = UserEmailMap.query()
             # try fetching in alternate email map
-            q = q.filter(UserEmailMap.email.ilike(email))
+            q = q.filter(func.lower(UserEmailMap.email) == func.lower(email))
             q = q.options(joinedload(UserEmailMap.user))
             if cache:
                 q = q.options(FromCache("sql_cache_short",
@@ -851,7 +851,7 @@ class UserGroup(Base, BaseModel):
     def get_by_group_name(cls, group_name, cache=False,
                           case_insensitive=False):
         if case_insensitive:
-            q = cls.query().filter(cls.users_group_name.ilike(group_name))
+            q = cls.query().filter(func.lower(cls.users_group_name) == func.lower(group_name))
         else:
             q = cls.query().filter(cls.users_group_name == group_name)
         if cache:
@@ -1520,7 +1520,7 @@ class RepoGroup(Base, BaseModel):
     def get_by_group_name(cls, group_name, cache=False, case_insensitive=False):
         if case_insensitive:
             gr = cls.query() \
-                .filter(cls.group_name.ilike(group_name))
+                .filter(func.lower(cls.group_name) == func.lower(group_name))
         else:
             gr = cls.query() \
                 .filter(cls.group_name == group_name)