Usuwając coś permanentnie z bazy danych musimy być bardzo ostrożni, bowiem przywrócenie danych jest bardzo trudne, czasem niemożliwe. Podstawową strukturę bazy danych powinno się budować na samym początku tworzenia aplikacji, z biegiem czasu rozbudowywać ją, ale unikać przebudowywania. Niestety są przypadki, gdzie trzeba przebudować jedną rzecz, co powoduje zmianę w wielu warstwach nie tyle aplikacji, co strukturze bazodanowej.
Dziś postaram się opisać, jakie kroki trzeba wykonać, aby bezpiecznie usunąć zdublowane rekordy z bazy danych nie tracąc żadnych danych:
Po wykonaniu kroku pierwszego zabieramy się za kolejny. Jest to najważniejszy moment naszych operacji. Aby ułatwić zrozumienie problemu, podam przykład z życia. Aplikacja posiadała poważny błąd, który umożliwiał zdublowanie użytkowników, ściślej: można było zdublować username. Za każdym razem, gdy użytkownik się logował i pisał komentarze, był ich właścicielem, ale comment_author posiadały różne ID tego samego użytkownika. Zaraz po skopiowaniu bazy danych spróbowałem przepisać ID autorów komentarzy na pierwszy rekord identyfikujący użytkownika, jaki istnieje w tabeli użytkowników. Skonstruowałem zapytanie:
UPDATE cms_comments
JOIN cms_members AS user_original ON(user_original.user_id = comment_author)
SET comment_author = (SELECT user_first.user_id FROM cms_members AS user_first WHERE user_first.user_name = user_original.user_name ORDER BY user_first.user_id ASC LIMIT 0, 1)
Usunięcie zdublowanych użytkowników było już tylko formalnością. Teraz się okaże, dlaczego zależało mi na wyciągnięciu dokładnie pierwszego rekordu reprezentującego “unikalnego” użytkownika: poniższe zapytanie (ALTER IGNORE TABLE ADD UNIQUE) usunie wszystkie kolejne rekordy oznaczone jako duplicated:
ALTER IGNORE TABLE cms_members ADD UNIQUE INDEX(user_name);
Krótki komentarz z manuala do ALTER TABLE:
IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
Pisząc ostatnie posty związane z bazami danych, mam nadzieję, że komuś się przydadzą.
Im więcej nietypowych rzeczy programuję, tym więcej nietypowych problemów musze pokonać. Co powiecie na sumę 2 liczb, z których jedna jest wartością NULL powstałą w wyniku działania SUM() lub pochodnych, gdzie nie odnaleziono żadnego rekordu.
Badamy:
SELECT 1+2+3
>> 6
SELECT 1+2+NULL
>> NULL
SELECT COALESCE( NULL, 0 )
>> 0
Zatem analogicznie do powyższego przykładu:
UPDATE users SET user_points = user_points + COALESCE((SELECT SUM( ... ) WHERE ...), 0)
Punkty użytkownika już zawsze będą się sumowały poprawnie :-)
Wczorajszy dzień był “wysypem nowego oprogramowania” - nowego w sensie nowych wersji, np takich jak Firefox 3.5, PHP 5.3. Pisałem wczoraj o pgAdminie3 o wersji 1.10.0 a dzisiaj będzie o samej bazie danych PostgreSQL w wersji oznaczonej numerem 8.4.
W wersji PostgreSQL 8.4 zostały wprowadzone:
Pełna lista zmian: http://www.postgresql.org/about/press/features84.html
Kanał ATOM
