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.
Procedura kasująca obrazki również jest raczej prosta. Rzućcie okiem:
DELIMITER $$
CREATE PROCEDURE `delete_image`(IN image_id INT)
MODIFIES SQL DATA
COMMENT 'Deletes and reorders if there is a gap.'
BEGIN
DECLARE image_gallery_id, image_order, max_order INT;
SELECT gallery_id, ordr INTO image_gallery_id, image_order FROM images WHERE id=image_id LIMIT 1;
DELETE FROM images WHERE id=image_id LIMIT 1;
SELECT MAX(ordr) INTO max_order FROM images WHERE gallery_id=image_gallery_id LIMIT 1;
# Sprawdzamy czy istnieja jakies obrazki za skasowanym
IF max_order IS NOT NULL AND max_order > image_order THEN
WHILE image_order < max_order DO
# Cofamy w petli wypelniajac luke po skasowanym obrazku
UPDATE images SET ordr=image_order WHERE ordr=image_order+1 AND gallery_id=image_gallery_id;
SET image_order = image_order + 1;
END WHILE;
END IF;
END$$Wywołanie jest super proste. Wpisujemy i zapominamy:
$q = $pdo->prepare('CALL delete_image(?)');
$q->bindParam(1, $imageId, PDO::PARAM_INT);
$q->execute();Najlepsze zostawiłem na koniec. Procedura przestawiająca pozycję obrazka. Spotkałem się w necie z rozwiązaniem typu „bierzesz sobie wszystkie id obrazków w kolejności, przejeżdżasz foreachem, który nadaje kolejność i na każdym obrazku wykonujesz update”. No dobra, ale co jeżeli przestawiam kolejność tylko ostatniego i przedostatniego, a w galerii mam 1000 zdjęć? Powyższe rozwiązanie orze całą galerię, a userzy czekają 5 sek. na załadowanie się strony. A co jeżeli kilka osób na raz coś przestawia w swoich galeriach? Wtedy na serwerze włącza się na kilka minut turbo hardcore i pojawia się „Pan Gąbka” 
Moje rozwiązanie polega na wyłapaniu wyłącznie tego co wymaga zmian. Dałem kilka komentarzy dla jasności.
DELIMITER $$
CREATE PROCEDURE `reorder_image`(IN image_id INT, IN new_image_order INT)
MODIFIES SQL DATA
COMMENT 'Reorders images. Does nothing if given order is out of scope.'
BEGIN
DECLARE current_image_order, image_gallery_id, is_destination_order_exists INT;
# Lapie obecne polozenie obrazka i przy okazji id galerii
SELECT ordr, gallery_id INTO current_image_order, image_gallery_id FROM images WHERE id=image_id LIMIT 1;
# Sprawdzam czy punkt docelowy w ogole istnieje
SELECT ordr INTO is_destination_order_exists FROM images WHERE gallery_id=image_gallery_id AND ordr=new_image_order LIMIT 1;
# Jezeli punkt docelowy istnieje i jest inny od obecnego TO rozpoczynam dzialanie
IF is_destination_order_exists IS NOT NULL AND current_image_order <> new_image_order THEN
# Jezeli przestawiam obrazek do gory
IF current_image_order > new_image_order THEN
WHILE current_image_order >= new_image_order DO
UPDATE images SET ordr=current_image_order+1 WHERE gallery_id=image_gallery_id AND ordr= current_image_order LIMIT 1;
SET current_image_order = current_image_order - 1;
END WHILE;
# Jezeli przestawiam obrazek w dol
ELSEIF current_image_order < new_image_order THEN
WHILE current_image_order <= new_image_order DO
UPDATE images SET ordr=current_image_order-1 WHERE gallery_id=image_gallery_id AND ordr= current_image_order LIMIT 1;
SET current_image_order = current_image_order + 1;
END WHILE;
END IF;
# Mam juz miejsce, wrzucam obrazek tam gdzie ma byc
UPDATE images SET ordr=new_image_order WHERE id=image_id LIMIT 1;
END IF;
END$$Użycie przestawiania kolejności jest również proste:
$q = $pdo->prepare('CALL reorder_image(?,?)');
$q->bindParam(1, $imageId, PDO::PARAM_INT);
$q->bindParam(2, $wantedImageOrder, PDO::PARAM_INT);
$q->execute();Jeśli ktoś dobrnął aż tutaj to proszę „lajknąć” i „plusnąć”. Niedzielne pozdro4all.
)