Pracując na jednym z projektów, trafiliśmy na „ciekawe inaczej” wymaganie. Chodziło o to, by dodać wyszukiwanie po adresie email. Prosta sprawa, ale nie do końca.

Gmail a wielkość znaków

W adresie e-mail co do zasady ważna jest wielkość znaków. No, chyba że jesteś gmailem, to wtedy nie. Gmail jest usługą, która próbuje być cwana. Po pierwsze gmail nie rozróżnia wielkości znaków. Po drugie gmail nie uznaje kropek w adresie. Ma to pewne konsekwencje dla osób, które są przywiązane do kropek.
Po trzecie, większość naszych klientów wykorzystuje gmaila jako swój główny adres w naszym systemie lub ichni system korpo-poczty wykorzystuje gmaila.
Tu pojawił się problem.

Zrobisz selecta i będzie dobrze

Najprostszym rozwiązaniem naszego zadania byłoby stworzenie kodu, który uruchamia pod spodem zapytanie jak to tutaj:

Listing 1. Rozwiązanie naiwne

Select *
from accounts
where email_address = 'admin@example.com'

Przy czym problem z tym zapytaniem leży w sposobie porównywania wartości. Dobrze ilustruje to poniższy test:

Listing 2. Test rozwiązania naiwnego

@SpringBootTest
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
class AccountRepositoryTest {

	@Autowired
	AccountRepository repository;

	@Test
	@Order(1)
	void readAndWriteSameCase() {
		Account s = new Account("admin@example.com");
		repository.save(s);
		assertThat
				(repository.findById("admin@example.com"))
				.isNotEmpty();
	}

	@Test
	@Order(3)
	void readAndWriteNotSameCase() {
		assertThat(repository.findById("Admin@example.com")).isNotEmpty();
	}
}

Pierwszy test, readAndWriteSameCase, zakończy się sukcesem. Drugi będzie czerwony. Dlaczego?

Postgres jest OK

W przeciwieństwie do MySQLa Postgres prawidłowo rozróżnia wielkość liter. Tym samym proste porównanie dwóch wartości typu VARCHAR będzie zwracało false jeżeli wartości te różnią się wielkością liter właśnie.
Zidentyfikowaliśmy więc nasz „problem”, którego źródło leży, w niezrozumieniu tego jak działa baza danych, której używamy. Należy zatem pomyśleć o możliwych rozwiązaniach.

Rozwiązania i „rozwiazania”

Poniżej przedstawię kilka rozwiązań, które mają różny poziom dziwności, poręczności i słuszności.

Po stronie Javy

Pierwszym, bardzo naiwnym, ale też często spotykanym rozwiązaniem będzie umieszczenie „gdzieś w logice” wywołania toLowerCase. Nie jest to złe. Nie jest to też jakoś mądre. Jest za to bardzo naiwne i powinno być używane w ostateczności.

Czysty SQL – lower

Kolejnym rozwiązaniem jest takie przemodelowanie naszych zapytań, by używać funkcji lower z SQLa. Nasze przykładowe zapytanie mogłoby wtedy wyglądać tak:

Listing 3. Użycie lower

Select *
from accounts
where lower(email_address) = lower('admin@example.com')

Podobnie jak poprzednie, to rozwiązanie jest bardzo naiwne. Nie sprawdzi się w przypadku, gdy używamy JPA. Chyba że ręcznie ogarniemy każde zapytanie, gdzie potrzebujemy tego rodzaju porównań albo potrafimy posłużyć się hibernetową adnotacją @Formula. Co też nie do końca jest proste i ma swoje wady.

Czysty SQL – operator ~\*

Postgres posiada operator ~\*, który służy do porównania dwóch ciągów za pomocą wyrażeń regularnych POSIX. Po lewej stronie jest ciąg znaków, a po prawej wyrażenie:

Listing 4. Użycie ~

Select *
    from accounts
    where email_address ~* 'admin@example.com'

Operator ten ma kilka wersji:

  • ~ – Porównanie za pomocą wyrażenia regularnego, biorące pod uwagę wielkość znaków
  • ~\* – Porównanie za pomocą wyrażenia regularnego, niebiorące pod uwagę wielkości znaków
  • !~ – Porównanie za pomocą wyrażenia regularnego, biorące pod uwagę wielkość znaków, zwraca true jeżeli nie ma dopasowania.
  • !~\* – Porównanie za pomocą wyrażenia regularnego, niebiorące pod uwagę wielkości znaków, zwraca true jeżeli nie ma dopasowania.

I podobnie jak w poprzednim przypadku użycie tego operatora wymaga albo użycia SQL wszędzie gdzie to konieczne, albo kombinowania z adnotacjami.

Czysty SQL – rozszerzenie citext

Rozszerzenie to wprowadza do Postgresa typ citext, który zachowuje się jak text, ale ignoruje wielkość znaków. To rozwiązanie jest najmniej inwazyjne w kontekście kodu. Należy jedynie dołączyć do naszego projektu bibliotekę hibernate-types, w której znajduje się już gotowe wsparcie dla tego typu.

Podsumowanie

Opisany problem jest, paradoksalnie, nieoczywisty. We współczesnych systemach dokłada się dość szybko silniki wyszukiwania albo „hackuje” ten problem we wczesnym stadium życia projektu. W ten sposób powstają niezbyt zrozumiałe konstrukcje, których zadaniem jest zbędne weryfikowanie danych wejściowych. Co prawda nie trudno jest sobie wyobrazić tego typu problemy w systemach, gdzie ręcznie wprowadza się sygnatury dokumentów. Z drugiej strony jesteśmy przyzwyczajeni do pisania całej masy walidatorów, których jedynym zadaniem jest naprawianie błędów, wynikających z naszej niewiedzy. Pytanie czy to dobre podejście?