1 Тема від fed_lviv

  • fed_lviv
  • fed_lviv
  • Replace Group
  • Поза форумом
  • Дата реєстрації: 11.09.2013
  • Повідомлень: 811
    • Репутація: 664

Тема: MySQL ведення історії даних

В БД є таблиця, де зберігаються дані по трансформаторах струму(ТС) на об'єктах:
ТАБЛИЦЯ:
idTCLTE   int   not null   primary key (id записа)
idObject   smallint   not null   (id об'єкта на якому всатновлено (встановлені) ТС) //використовується для з'єднання з таблицею об'єктів
numberTC   varchar   (серійний номер ТС)
idTC   smallint   not null   (id типу ТС) // використовується для з'єднання з таблицею типів ТС
testYear   smallint   (рік держповірки)
testQuarter   tinyint   (квартал держповірки)
mustTesting   tinyint   not null   (чи підлягає держповірці)

Тобто можна подивитися, на якому об'єкті встановлено ТС + скільки ТС встановлено на конкретному об'єкті.
Так, як ТС часто перестановлюють з одного об'єкта на інший, хотілося б бачити "історію мандрувань конкретного ТС".
І ось думаю, як правильно (найкраще) то зробити?
Думав про окрему таблицю, але на разі найкращий варіант це:
1.Додавання нової колонки в дану таблицю-щось типу останнє встановлення. Яка буде типу bool(tinyint). Тобто, якщо true - то це останнє встановлення даного ТС на об'єкті, всі інші записи таблиці в яких в колонці (останнє встановлення) false - це історія, де раніше були ТС.
2.Чіпляю тригер на таблицю при додаванні нового запису перевіряється чи є вже в таблиці інформація про даний ТС (numberTC та idTC) і якщо є, то у всіх "старих" записах про даний ТС змінити значення в полі останнє встановлення на false.

Хто, що може порекомендувати?

Подякували: 221VOLT1

2 Відповідь від koala

  • koala
  • koala
  • Лінива тваринка
  • Поза форумом
  • Дата реєстрації: 01.05.2013
  • Повідомлень: 15 601
    • Репутація: 13310

Re: MySQL ведення історії даних

Це зветься темпоральна таблиця. З таким підходом ви губите послідовність переносу ТС; якщо ж замість bool ставити datetime, то запити будуть обтяжені чимось на кшталт 

WHERE (умова пошуку) AND writeTime = MAX(SELECT writeTime FROM ... WHERE умова пошуку)

Для MySQL, гадаю, окрема таблиця "історія" буде кращим виходом.

Подякували: fed_lviv, 221VOLT, colin200x3

koala вебсайт

3 Відповідь від fed_lviv Востаннє редагувалося fed_lviv (21.09.2016 09:55:36)

  • fed_lviv
  • fed_lviv
  • Replace Group
  • Поза форумом
  • Дата реєстрації: 11.09.2013
  • Повідомлень: 811
    • Репутація: 664

Re: MySQL ведення історії даних

koala написав:

З таким підходом ви губите послідовність переносу ТС

Ну можна ж додати поле дати "знімання". Але погоджуюсь запити будуть не гарні.

koala написав:

Для MySQL, гадаю, окрема таблиця "історія" буде кращим виходом.

Зрозуміло, дякую, буду думати.
P.S. Таблиця "історія" теж тягне за собою додавання тригера до основної таблиці? На кшталт, додали новий запис, всі старі (які відповідають заданому критерію) видаляються з основної таблиці і додаються до таблиці "історії"?

Подякували: 221VOLT1

4 Відповідь від Torbins

  • Torbins
  • Torbins
  • Replace Team
  • Поза форумом
  • Дата реєстрації: 07.05.2012
  • Повідомлень: 3 080
    • Репутація: 2277

Re: MySQL ведення історії даних

fed_lviv
Це залежить від того, яка архітектура у вашої системи. Зазвичай усю подібну логіку виносять в середній слой (між БД і інтерфейсом). А в БД лишають лише перевірку цілісності даних.

Подякували: fed_lviv, 221VOLT2

5 Відповідь від fed_lviv

  • fed_lviv
  • fed_lviv
  • Replace Group
  • Поза форумом
  • Дата реєстрації: 11.09.2013
  • Повідомлень: 811
    • Репутація: 664

Re: MySQL ведення історії даних

Наразі, зупинився на "любимих" тригерах + таблиця історії:
Створив нову таблицю:

CREATE TABLE `tc_lte_history` (
  `idTCLTEH` smallint(3) unsigned NOT NULL AUTO_INCREMENT,
  `type` enum('ІТП','ЦТП','гуртожиток','майстерня','ТК','котельня') NOT NULL,
  `address` varchar(35) NOT NULL,
  `idTC` smallint(3) unsigned NOT NULL,
  `numberTC` varchar(10) DEFAULT NULL,
  `dateTrash` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTCLTEH`),
  KEY `FOREIGN_idTCH_idx` (`idTC`),
  CONSTRAINT `FOREIGN_idTCH` FOREIGN KEY (`idTC`) REFERENCES `tc` (`idTC`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Додав тригер до "основної" таблиці, ТРИГЕР ПРИ ВИДАЛЕННІ:

TRIGGER `tc_lte_BDEL` BEFORE DELETE ON `tc_lte` FOR EACH ROW BEGIN
BEGIN
    INSERT INTO tc_lte_history (type, address, idTC, numberTC) 
    SELECT objects.type, objects.address, tc_lte.idTC, tc_lte.numberTC
    FROM objects, tc_lte
    WHERE objects.idObject=tc_lte.idObject AND idTCLTE=OLD.idTCLTE;
END

А ось з тригером при додаванні, вийшла біда. Хотів спочатку всі "старі" дані в таблицю "історії". Після, йшло видалення "старих" даних з "основної" таблиці. І ось при видаленні з основної таблиці - трапилася біда. Тригер ПЕРЕД ВСТАВЛЯННЯМ і в тілі даного тригера не можна видаляти записи таблиці.
Тому, обійшовся додаванням УНІКАЛЬНОСТІ даних до "основної" таблиці, що в принципі і так мало бути, але чомусь не було.  :) 

UNIQUE KEY `UNIQ_TC_LTE` (`idTC`,`numberTC`)


Тобто, тепер, якщо той самий ТС намагаюсь додати в таблицю, то СУБД дану дію не пропускає. Треба спочатку видалити старі дані(а ось тут тригер при видаленні спрацьовує), а потім вставити нові.
Наразі думаю над тригером при оновленні даних.

6 Відповідь від fed_lviv Востаннє редагувалося fed_lviv (22.09.2016 13:39:42)

  • fed_lviv
  • fed_lviv
  • Replace Group
  • Поза форумом
  • Дата реєстрації: 11.09.2013
  • Повідомлень: 811
    • Репутація: 664

Re: MySQL ведення історії даних

Ось і тригер при оновленні даних, тобто йде запис в таблицю "історії" тільки коли:
-змінено адресу (ТС переїхав на новий об'єкт)
-змінено тип ТС (відповідно встановлений новий ТС)
-змінено зав. номер ТС (відповідно встановлений новий ТС)

DELIMITER ///
CREATE TRIGGER tc_lte_BUPD BEFORE UPDATE ON tc_lte FOR EACH ROW
BEGIN
    IF NEW.idObject <> OLD.idObject OR NEW.numberTC <> OLD.numberTC OR NEW.idTC <> OLD.idTC
    THEN
        INSERT INTO tc_lte_history (type, address, idTC, numberTC) 
        SELECT objects.type, objects.address, tc_lte.idTC, tc_lte.numberTC
        FROM objects, tc_lte
        WHERE objects.idObject=tc_lte.idObject AND idTCLTE=OLD.idTCLTE;
    END IF;
END;
///
DELIMITER ;