Pracuję ostatnio nad bardzo ciekawym serwisem internetowym, w którym istotną rolę odgrywa baza danych, a szczególnie jej wydajność. Ze względu na to, że zajmuje już ona solidne kilka gigabajtów, problemem staje się okresowe tworzenie backupu i przechowywanie go.     … #LINK#

Jedno z zapytań ofertowych zawierało w specyfikacji wymóg użycia bazy MySQL, niestety nasz CMS korzysta z PostgreSQL i przysłowiowy zonk. Z uwagi na to, że nie mamy bezpośredniego kontaktu z klientem i jesteśmy podwykonawcami przygotowaliśmy z początku wycenę zmiany CMS by korzystał z...

Klasa Zend_Db_Table jest zorientowanym obiektowo interfejsem pomiędzy naszym kodem, a tabelami w bazie danych. Widać więc, że ułatwia nam pracę tym bardziej, że dostarcza ona metody do wielu podstawowych operacji na danych przechowywanych w bazie. Dodatkową zaletą jest fakt, że użytkownik nie musi ingerować w poszczególne zapytania. Przygotowaniem odpowiednich zapytań dostosowanych do użytkowanej bazy danych [...]

W najnowszym projekcie spotkałem się z dość dziwnym problemem dotyczącym wykonywania zapytania, podczas którego należało zrobić join na dwóch tabelach. Tabela, dla której przygotowywane było zapytanie została zdefiniowana następująco: Natomiast kod, który odpowiedzialny był za wykonanie zapytania prezentuje się następująco: Niestety przy próbie wykonania tego zapytania otrzymałem wyjątek “Zend_Db_Table_Select_Exception” z komunikatem: “Select query cannot join [...]

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.

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Zwiń
Czytaj na blogu autora...

Na początku mojej nauki PHP kupiłem sobie książkę „PHP4 Aplikacje” (Tobiasa Ratschiller i Till Gerken – Wydawnictwo Robomatic). Zawarta w tej lekturze tematyka była wtedy dla mnie zbyt zaawansowana i potem wielokrotnie wracałem do tej pozycji stopniowo dojrzewając do poruszanych w niej tematów. Najdłużej wzbraniałem się przed zgłębieniem wiedzy dotyczącej operacji na bitach. Dzisiaj nie [...]

Język SQL to nie tylko genialne narzędzie pozwalające na manipulację danymi w bazie danych na stronie internetowej, ale także bardzo prosty sposób na wykonanie prostego sprawdzenia, czy pewne wartości zgadzają się z naszym stanem wiedzy. Oczywiście możliwe jest napisanie skryptu, który nam wszystko policzy, ale czy nie prościej jest stworzyć jedno zapytanie, które w bardzo [...]