MySQL i automatyczne tworzenie historii rekordu w bazie [cz. 2/2]

Zgod­nie z obiet­nicą dzisiaj druga część. Zwięk­szamy poziom trud­no­ści o rela­cję wiele-do-wielu.

Na począ­tek dorzu­camy tabelę Ficzer zawie­ra­jącą dodat­kowe bajery, o które ma być wzbo­ga­cony artykuł:

CREATE TABLE IF NOT EXISTS `Ficzer` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`nazwa` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `Ficzer` (`id`, `nazwa`) VALUES
(1, 'Podświetlenie'),
(3, 'Pogrubienie'),
(5, 'Pochylenie');

Zaraz za nią tworzymy tabelę pośred­ni­czącą Ogloszenie_Ficzer:

CREATE TABLE IF NOT EXISTS `Ogloszenie_Ficzer` (
  `ogloszenie_id` INT(11) NOT NULL,
  `ficzer_id` INT(11) NOT NULL,
  PRIMARY KEY (`ogloszenie_id`,`ficzer_id`),
  KEY `ficzer_id` (`ficzer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE `Ogloszenie_Ficzer`
  ADD CONSTRAINT `Ogloszenie_Ficzer_ibfk_4` FOREIGN KEY (`ficzer_id`) REFERENCES `Ficzer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `Ogloszenie_Ficzer_ibfk_3` FOREIGN KEY (`ogloszenie_id`) REFERENCES `Ogloszenie` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Proce­dura skła­do­wana ogloszenie_history musi zostać rozbu­do­wana o obsługę doda­nych tabel (a raczej tabeli, bo wystar­czy nam tabela pośred­ni­cząca). Doko­namy „spłasz­cze­nia” struk­tury bazy w wier­szu histo­rii wymie­nia­jąc wszyst­kie ficzery rozdzie­lone śred­ni­kami. Dorzu­camy pole ficzers do OgloszenieHistory:

ALTER TABLE `OgloszenieHistory` ADD COLUMN `ficzers` text DEFAULT NULL;

Poni­żej uaktu­al­niony kod proce­dury składowanej:

DELIMITER $$
CREATE PROCEDURE `ogloszenie_history`(IN `id` INT, IN `change_type` ENUM('created','modified','deleted')) 
MODIFIES SQL DATA 
BEGIN 
  DECLARE p_user_id INT; 
  DECLARE p_data TEXT;
 
  # Ciag tekstowy zawierajacy ficzer_id;ficzer_id;...
  DECLARE p_ficzers TEXT DEFAULT NULL;
 
  # Pojedynczy wiersz kursora.
  DECLARE p_ficzer INT;
 
  # Blokada kursora gdy braknie wynikow.
  DECLARE p_last_ficzer INT DEFAULT FALSE;
 
  # Deklaracja kursora przechodzacego po wszystkich ficzerach ogloszenia.
  DECLARE cur_ficzer CURSOR FOR SELECT ficzer_id FROM Ogloszenie_Ficzer WHERE ogloszenie_id=id;
 
  # Ustawienie blokady kursora.
  DECLARE continue handler FOR NOT found SET p_last_ficzer = TRUE;
 
  # Wyciagam dane ze zmienianego wiersza i wrzucam do zadeklarowanych wczesniej zmiennych.
  SELECT modified_by, DATA INTO p_user_id, p_data FROM Ogloszenie WHERE id=id LIMIT 1;
 
  OPEN cur_ficzer;
  ficzer_loop: LOOP
    FETCH cur_ficzer INTO p_ficzer;
    IF p_last_ficzer THEN
      LEAVE ficzer_loop;
    END IF;
 
    # Sklejam kolejne wartosci.
    SET p_ficzers = CONCAT_WS(';', p_ficzers, p_ficzer);
  END LOOP;
  CLOSE cur_ficzer;
 
  # Wrzucam wiersz do historii.
  INSERT INTO OgloszenieHistory (ogloszenie_id, change_type, user_id, DATA, ficzers) 
    VALUES (id, change_type, p_user_id, p_data, p_ficzers);
 
END$$
DELIMITER ;

Dzięki zasto­so­wa­niu kursora zbie­ramy sobie każdo­ra­zowo bieżące warto­ści tabeli pośred­ni­czą­cej. Ostat­nią rzeczą jest doda­nie wyzwalaczy:

DROP TRIGGER IF EXISTS `new_ficzer`;
DELIMITER //
CREATE TRIGGER `new_ficzer` AFTER INSERT ON `Ogloszenie_Ficzer`
  FOR EACH ROW BEGIN
    CALL ogloszenie_history (NEW.ogloszenie_id, 'modified');
END//
DELIMITER ;
DROP TRIGGER IF EXISTS `delete_ficzer`;
DELIMITER //
CREATE TRIGGER `delete_ficzer` BEFORE DELETE ON `Ogloszenie_Ficzer`
  FOR EACH ROW BEGIN
    CALL ogloszenie_history (OLD.ogloszenie_id, 'modified');
END//
DELIMITER ;

Stosu­jąc takie rozwią­za­nie każda zmiana jest reje­stro­wana w dzien­niku zmian. Minu­sem jest to, że nastę­puje gwał­towny przy­rost wier­szy (doda­nie 10 ficze­rów do ogło­sze­nia powo­duje doda­nie 10 wier­szy histo­rii, skaso­wa­nie to samo). Jeśli bardzo zależy nam na ogra­ni­cze­niu liczby wier­szy trzeba zasta­no­wić się nad jakimś auto­ma­tem odpa­la­nym cyklicz­nie z CRONa kasu­ją­cym starą histo­rię lub bardziej wyra­fi­no­wa­nym — wyła­pu­ją­cym „stany pośred­nie” (co zresztą też jest niezłym pretek­stem do napi­sa­nia na blogu ;-) ).

PS.: Inte­re­su­je­cie się DARTem? Jesz­cze trochę ponad 100 defek­tów i będzie mile­stone 1.

Podobne wpisy:

  1. MySQL i auto­ma­tyczne tworze­nie histo­rii rekordu w bazie [cz. 1/2]
  2. MySQL, PDO i proce­dury składowane
  3. Łącze­nie zapy­tań Zend_Db_Select w Zend Frame­work [cz. 1/2]
  4. Tworze­nie pakie­tów ICMPPHP

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.

Możesz użyć następujących tagów oraz atrybutów HTML-a: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>