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

Ponie­waż dawno nie pisa­łem o bazach danych, dzisiaj coś dla miło­śni­ków wyzwa­la­czy i proce­dur skła­do­wa­nych w MySQL. Ile razy myśle­li­ście, że fajnie by było żeby coś „samo się robiło”? Taką samo­się można napi­sać dosyć łatwo jeśli chcemy stwo­rzyć mecha­nizm śledze­nia histo­rii rekordu. Wpis rozbiję na dwie części z uwagi na to, że w następ­nej skom­pli­kuję całość o tworze­nie histo­rii rekordu z wielu tabel.

Załóżmy, że mamy tabelę Ogloszenie, w której trzy­mamy dane, które chcemy śledzić. Ma nastę­pu­jącą strukturę:

CREATE TABLE IF NOT EXISTS `Ogloszenie` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `modified_by` INT(11) NOT NULL,
  `data` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Tabela śledząca ogło­sze­nie będzie wyglą­dała mniej więcej tak (nie śmiać mi się z polsko-angielskich hybryd nazewniczych):

CREATE TABLE IF NOT EXISTS `OgloszenieHistory` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ogloszenie_id` INT(11) NOT NULL,
  `change_type` enum('created','modified','deleted') NOT NULL,
  `user_id` INT(11) NOT NULL,
  `event_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `data` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Wiemy, że możemy podcze­piać się pod zmiany w bazie danych za pomocą wyzwa­la­czy (trig­ge­rów). Tworząc histo­rię wystar­czy śledzić zmiany w postaci zapy­tań typu insert, update i delete. Wyzwa­la­cze będą różnić się właści­wie tylko typem wyko­na­nego zapy­ta­nia. Nie wiem jak wam, ale mnie na kilo­metr „zaje­chało” redun­dan­cją kodu wyzwa­la­czy, więc napi­szemy sobie proce­durę skła­do­waną, którą każdy z wyzwa­la­czy będzie odpa­lał. Jest to najtrud­niej­sze zada­nie (kod proce­dur wykrza­cza się jak szalony).

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; 
 
  # 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;
 
  # Wrzucam wiersz do historii.
  INSERT INTO OgloszenieHistory (ogloszenie_id, change_type, user_id, DATA) 
    VALUES (id, change_type, p_user_id, p_data);
 
END$$
DELIMITER ;

Zwróć­cie uwagę na pole change_type, którego dopusz­czalne warto­ści to created, modi­fieddele­ted, czyli dokład­nie takie jak w tabeli OgloszenieHistory. Skutecz­nie zawęzi nam to możli­wość „zatru­cia” histo­rii niezro­zu­mia­łymi typami zmian.
Pozo­stało zrobie­nie wyzwalaczy:

DROP TRIGGER IF EXISTS `inserted_ogloszenie`;
DELIMITER //
CREATE TRIGGER `inserted_ogloszenie` AFTER INSERT ON `Ogloszenie`
 FOR EACH ROW BEGIN
  # Podpinamy sie po insercie dysponujac swiezo auto inkrementowanym ID wiersza.
  CALL ogloszenie_history(NEW.id, 'created');
END//
DELIMITER ;
DROP TRIGGER IF EXISTS `updated_ogloszenie`;
DELIMITER //
CREATE TRIGGER `updated_ogloszenie` AFTER UPDATE ON `Ogloszenie`
 FOR EACH ROW BEGIN
  # Podajemy id zmienionego rekordu.
  CALL ogloszenie_history(NEW.id, 'modified');
END//
DELIMITER ;
DROP TRIGGER IF EXISTS `deleted_ogloszenie`;
DELIMITER //
CREATE TRIGGER `deleted_ogloszenie` BEFORE DELETE ON `Ogloszenie`
 FOR EACH ROW BEGIN
  # Zanim baza skasuje wiersz zapiszemy sobie TO wydarzenie.
  CALL ogloszenie_history(OLD.id, 'deleted');
END//
DELIMITER ;

Śledze­nie histo­rii przed­sta­wione powy­żej jest o tyle dobre, że zwal­nia nas z pisa­nia jakie­go­kol­wiek kodu po stro­nie apli­ka­cji. Wszystko robi za nas baza. Dodat­kowo mamy pełny wgląd kto i kiedy zmie­niał dane. Przy­kła­dowe wpisy w tabeli OgloszenieHistory:

id, ogloszenie_id, change_type, user_id, event_date,           data
1   1              'created'    1        '2012-09-05 18:09:03' 'First data'
2   1              'modified'   2        '2012-09-05 18:09:16' 'Modified data'
3   1              'modified'   1        '2012-09-05 18:09:29' 'Modified data drugi raz'
4   2              'created'    1        '2012-09-05 18:09:37' 'Second data'
5   1              'deleted'    2        '2012-09-05 18:42:42' 'Modified data drugi raz'

W zasto­so­wa­niach prak­tycz­nych nie mamy właści­wie do czynie­nia z bazami bez rela­cji, więc taka prosta histo­ria na niewiele się zda. W następ­nej części wpro­wa­dzę śledze­nie rela­cji typu wiele-do-wielu. Kiedy będzie następna część? Wtedy kiedy będzie mi się chciało pisać ;-)

Podobne wpisy:

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

One comment

  • gawar
    15 maja 2014 - 19:32 | Permalink

    Hej mam pyta­nie czy podany w tym wpisie kod działa prawi­dłowo? Kiedyś natkną­łem się na ten wpis i chcia­łem to wypró­bo­wać. Zapy­ta­nia SQL wyko­nują się bez błędu nato­miast już podczas doda­wa­nia nowych wpisów czy edycji ogło­szeń nie zapi­sują się popraw­nie warto­ści w user_id i data. Nie znam się na proce­du­rach i trig­ge­rach dlatego może ktoś podpo­wie z czym może być problem:)

  • 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>