na potrzeby jednego z projektów miałem zaprojektować bazę. jednym z elementów bazy była tabelka z danymi użytkowników. ponieważ zbiór danych był spory i do tego zmienny, tabelka z użytkownikami została mocno uproszczona, ale za to zostały dodane tabelki na dane dodatkowe.

finalnie – tabelka z użytkownikami wyglądała mniej więcej tak:

CREATE TABLE users (
id         BIGSERIAL  ,
username   TEXT        NOT NULL DEFAULT '',
password   TEXT       ,
registered TIMESTAMPTZ NOT NULL DEFAULT now(),
active     BOOL        NOT NULL DEFAULT 'true',
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_users_username ON users (username);

małe, proste i łatwe. było. ale potem pojawiły się zmiany.

zmiana numer 1: klient którego konto zostanie wyłączone może założyć nowe konto o identycznej nazwie.

oops. username przestanie być unikalny? a może robić tabelę na dane archiwalne/usunięte? niestety – danych jako takich nie mogę kasować – muszą zostać w bazie.

krótki research pokazał, że przenoszenie danych do innych tabel (archiwum) nie wchodzi w grę – musiałbym zrobić kopię całej bazy jako archiwum. za dużo roboty. może coś prostszego?

pokombinowałem, przypomniałem sobie rozmaite rzeczy i zrobiłem:

DROP INDEX ui_users_username;
CREATE UNIQUE INDEX ui_users_username ON users (username) WHERE active = TRUE;

co to robi? to proste – nadal mam indeks unikalny, ale tylko aktywnych kont. konta nieaktywne nie są indeksowane w ogóle, więc także nie są objęte limitem unikalności. całość działa ładne. do czasu.

zmiana numer 2: nazwy kont (ich unikalność) powinny nie pozwalać na dwa konta – typu “depesz" i “Depesz".

no, to to trywiał – mały trigger “BEFORE INSERT OR UPDATE" który mi username lowercase'uje i po sprawie. a jednak nie. nazwa użytkownika ma się wyświetlać tak jak on sobie zażyczył. jak sobie przy rejestracji wpisał “Depesz" to ma mu się tak wyświetlać. ale nie powinnismy dopuścić do rejestracji “depesz"‘a. oraz powinniśmy umożliwić mu zalogowanie sie zarówno jako “Depesz" jak i “depesz".

oops część 2. kombinuję. krok numer 1 – dodatkowe pole które trzyma nazwę konta w postaci “tak jak user podał", a username będę triggerował do lowercase'a. ale to jest brzydkie. i duplikuje mi dane. myślałem nad tym jakiś czas gdy nagle mnie olśniło: indeksy funkcyjne. wystarczy:

DROP INDEX ui_users_username;
CREATE UNIQUE INDEX ui_users_username ON users ( LOWER(username) ) WHERE active = TRUE;

i po sprawie:

# INSERT INTO users (username) VALUES ('depesz');
INSERT 0 1
 
# INSERT INTO users (username) VALUES ('Hubert Lubaczewski');
INSERT 0 1
 
# INSERT INTO users (username) VALUES ('hubert lubaczewski');
ERROR:  duplicate KEY violates UNIQUE CONSTRAINT "ui_users_username"
 
# SELECT * FROM users;
id |      username      | password |          registered           | active
----+--------------------+----------+-------------------------------+--------
1 | depesz             | [NULL]   | 2006-12-05 23:16:52.52406+01  | t
2 | Hubert Lubaczewski | [NULL]   | 2006-12-05 23:16:58.370124+01 | t
(2 ROWS)

podsumowując – mam tabelkę która trzyma bazowe informacje o użytkownikach, pilnując tego by mógł być tylko 1 aktywny o tej samej nazwie – gdzie “ta sama" jest sprawdzanie niezależnie od wielkości liter. i do tego nie zmieniamy danych wpisanych przez usera – i jeśli zażyczy sobie (rejestrując się) jakichś różnych wielkości liter – tak też to zrobimy i tak mu wyświetlimy. życie jest piękne.