Сравнение кириллицы в SQLite без учета регистра

У SQLite есть одна особенность. Сравнение строк без учета регистра, функции изменения регистра работают только для ASCII-символов. Это особенность может принести немало хлопот, если нужно строить запросы с использованием, например, кириллицы.

Например, если в колонке str сохранены значения “VaLuE”, “value”, то запрос SELECT * FROM tab WHERE str LIKE “value” вернет две строки. LIKE по-умолчанию не чувствителен к регистру. Если в базе данных сохранены значения “ЗнАчЕнИе” и “значение”, то такой же запрос вернет только одну строку.

Для решения подобных задач SQLite предоставляет механизм переопределения встроенных функций и правил сравнения в пользовательском коде. Ниже пример кода на Python, который выполняет переопределение:

 1 # coding = utf-8
 2 
 3 import re, sqlite3
 4 
 5 # Переопределение оператора сравнения с двумя и тремя параметрами
 6 def sqlite_like(template_, value_):
 7     return return sqlite_like_escape(template_, value_, None)
 8 
 9 def sqlite_like_escape(template_, value_, escape_):
10     re_ = re.compile(template_.lower().
11                         replace(".", "\\.").replace("^", "\\^").replace("$", "\\$").
12                         replace("*", "\\*").replace("+", "\\+").replace("?", "\\?").
13                         replace("{", "\\{").replace("}", "\\}").replace("(", "\\(").
14                         replace(")", "\\)").replace("[", "\\[").replace("]", "\\]").
15                         replace("_", ".").replace("%", ".*?"))
16     return re_.match(value_.lower()) != None    
17     
18 # Переопределение функции преобразования к нижнему регистру
19 def sqlite_lower(value_):
20     return value_.lower()
21       
22 # Переопределение правила сравнения строк
23 def sqlite_nocase_collation(value1_, value2_):
24     return cmp(value1_.decode('utf-8').lower(), value2_.decode('utf-8').lower())
25   
26 # Переопределение функции преобразования к верхнему геристру
27 def sqlite_upper(value_):
28     return value_.upper()
29       
30 def main():
31     # Создание подключения к базе данных
32     conn_ = sqlite3.connect("./db.sqlite")
33   
34     # Регистрация переопределения в подключении к базе данных
35     cursor_ = conn_.cursor()
36 
37     conn_.create_collation("BINARY", sqlite_nocase_collation)
38     conn_.create_collation("NOCASE", sqlite_nocase_collation)
39 
40     conn_.create_function("LIKE", 2, sqlite_like)
41     conn_.create_function("LOWER", 1, sqlite_lower)
42     conn_.create_function("UPPER", 1, sqlite_upper)
43       
44     # ...

С этим вопросом я столкнулся, когда начал применять Trac, в качестве системы управления проектами.