Kanał ATOM
Autor wpisu: Śpiechu, dodany: 06.11.2011 14:10, tagi: mysql, php
Trochę nie pisałem. Mam nadzieję, że dzisiejszy wpis wszystkim wynagrodzi moją nieobecność. Ostatnio stanąłem przed wyzwaniem zrobienia galerii zdjęć, których kolejność dałoby się dowolnie modyfikować za pomocą przeciągania i upuszczania. Dzisiaj opiszę operacje bazodanowe, a na następny raz jQuery. Będę maksymalnie upraszczał aby nie zaciemniać meritum.
1. Przygotowania
Powiedzmy, że mamy 2 tabele relacyjne odpowiedzialne za przechowywanie galerii i obrazów. Np. takie:
CREATE TABLE `galleries` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `title` VARCHAR(250) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; CREATE TABLE IF NOT EXISTS `images` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `gallery_id` INT(10) UNSIGNED NOT NULL, `filename` VARCHAR(50) NOT NULL, `ordr` INT(10) UNSIGNED NOT NULL DEFAULT '1', `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `image_to_gallery` (`gallery_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Od razu widać dwa dziwactwa: dlaczego pole nazywa się ordr a nie order? Z czystego lenistwa. Order jest słowem zarezerwowanym w SQL (ORDER BY coś tam). Każdorazowo nazwa pola musiałaby być w nawiasach. Dlaczego pole updated ma domyślną wartość 0000–00-00 00:00:00? Ano dlatego, że CURRENT_TIMESTAMP można użyć tylko raz w tabeli. Wobec tego stworzymy od razu wyzwalacz (trigger), który przed każdym zapytaniem typu UPDATE poprawi wartość na taką jak trzeba.
DELIMITER $$ CREATE TRIGGER `updated_current_timestamp` BEFORE UPDATE ON `galleries` FOR EACH ROW BEGIN SET NEW.updated = NOW(); END$$
Na koniec trzeba stworzyć relację 1 galeria do wielu zdjęć.
ALTER TABLE `images` ADD CONSTRAINT `image_to_gallery` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Oznacza to, że kasując galerię od razu pozbędziemy się również wszystkich powiązanych z nią zdjęć. Pliki z dysku oczywiście nie znikną. Można napisać funkcję, która przed usunięciem galerii z bazy najpierw wyrzuca wszystkie powiązane z nią pliki, a dopiero potem wykonuje polecenie DELETE.
2. Decyzje
Teraz nadszedł czas na poważne decyzje. Chodzi o sposób manipulacji wierszami dotyczącymi zdjęć. Można to zrobić za pomocą PHP. Jest to rozwiązanie prostsze. Powoduje jednak spory narzut komunikacji PHP<—>SQL. W przypadku zwalenia wszystkiego na bazę danych, pchamy logikę wyżej i bliżej modyfikowanych danych. Minusem jest cholerna składnia SQLowa i późniejsze problematyczne utrzymanie kodu.
Problem oczywiście nie istnieje gdy robimy galeryjkę na 10 obrazków i przestawimy sobie kolejność ostatniego na przedostatni. Ja raczej podchodzę do rzeczy poważnie i wolę od początku zrobić to tak jak powinno być. Poza tym wyzwalacze i procedury składowane to jest to, co bazodanowe tygryski lubią najbardziej 
3. Wykonanie
Każdy nowy rekord tabeli images musi mieć nadany odpowiedni identyfikator pozycji ordr o 1 większy od ostatniego w danej galerii. Mamy trzy rozwiązania: czysty PHP, wyzwalacz wywoływany przed INSERTem lub procedura składowana. Zapytanie w PHP może wyglądać tak:
$q = $pdo->prepare('INSERT INTO images (filename,gallery_id,ordr) (SELECT ?,?,MAX(ordr)+1 FROM images WHERE gallery_id=? LIMIT 1)'); $q->bindValue(1, 'obrazek.jpg', PDO::PARAM_STR); $q->bindParam(2, $galleryId, PDO::PARAM_INT); $q->bindParam(3, $galleryId, PDO::PARAM_INT); $q->execute();
Wspominam o tym rozwiązaniu dlatego, że ma ciekawą konstrukcję INSERT SELECT. Zapewne większość z was po kilkunastokrotnej próbie wywołania polecenia INSERT INTO VALUES i gdzieś tam SELECT dostanie cholery i rozbije zapytanie na 2: pierwsze sprawdza ostatni ordr, a następne doda 1 i umieści INSERTem pozostałe dane.
Bazodanowe tygryski wybiorą jednak co innego. Procedury składowane!
DELIMITER $$ CREATE PROCEDURE `insert_image`(IN image_filename VARCHAR(50), IN image_gallery_id INT, OUT last_inserted_id INT) MODIFIES SQL DATA COMMENT 'Inserts new image at the end of given gallery.' BEGIN DECLARE max_order INT; # Zamiast SET zmienna= uzywam SELECT INTO just FOR fun SELECT MAX(ordr) INTO max_order FROM images WHERE gallery_id=image_gallery_id LIMIT 1; # Gdy obrazek jest pierwszy w galerii IF max_order IS NULL THEN SET max_order = 1; ELSE SET max_order = max_order + 1; END IF; INSERT INTO images (filename, gallery_id, ordr) VALUES (image_filename, image_gallery_id, max_order); SELECT LAST_INSERT_ID() INTO last_inserted_id; END$$
Próba wywołania $pdo->lastInsertId() zakończy się niepowodzeniem (a raczej zerem ). Dlatego potrzebujemy parametru wyjściowego. Poniżej pokazuję jak całość wywołać w PDO:
$q = $pdo->prepare('CALL insert_image(?,?,@lastInsertId)'); $q->bindValue(1, 'obrazek.jpg', PDO::PARAM_STR); $q->bindParam(2, $galleryId, PDO::PARAM_INT); $q->execute(); $outputArray = $pdo->query('select @lastInsertId')->fetch(PDO::FETCH_ASSOC); $lastInsertId = $outputArray['@lastInsertId'];
Ktoś może się zapytać po co te numery ze zmienną wyjściową. PDO i sterownik MySQL w PHP ma szpetny błąd dotyczący obsługi parametrów wyjściowych z procedur składowanych. Podobno w nowszych wersjach jest OK. Trik podany wyżej u mnie działa i oszczędza trochę nerwów.
