Alexander Mylnikov

CSC Базы данных, 8 дз

У нас в багтрекере появилось новое требование: мы хотим хранить историю изменений статуса бага. Мы хотим иметь возможность видеть отчет типа

Баг №1: Improve overall performance дата | статус 01.09.2014 new 02.09.2014 assigned 25.09.2014 fixed 26.09.2014 assigned 06.10.2014 fixed 07.10.2014 verified

Мы не планируем выполнять такие отчеты часто, нам все же в основном будет интересен актуальный статус всех багов. Поэтому идея вносить дополнительный атрибут “дата” в таблицу Bug, или же наоборот, убирать атрибут status_id из таблицы Bug кажется не очень хорошей (но вы можете попробовать это сделать и оценить, насколько она в действительности плоха. Оценить можно написав, например, запрос, выдающий актуальные статусы всех багов)

с точки зрения клиента, в идеале, процедура изменения статуса не должна измениться вообще, и должно быть достаточно выполнить запрос вида

UPDATE Bug SET status_id = 2 WHERE num=1

Поиск актуальных статусов, в идеале, тоже должен остаться прежним:

SELECT * FROM Bug

А историю клиенту хочется получать запросом вида

SELECT * FROM BugHistory WHERE num=1

где BugHistory — это отдельная таблица, или представление.

Задание решается достаточно просто.
А имеено: с помощью триггреров. Наша задача при добавлении бага начинать его историю, и при каждом UPDATE если статус был изменен вносить строку в таблицу истории. Далее приведены функции.

CREATE OR REPLACE FUNCTION log_update()
  RETURNS trigger AS
$BODY$
BEGIN
    IF NEW.status_id <> OLD.status_id THEN
        INSERT INTO BugHistory(num, date, status_id)
        VALUES (OLD.num, now()::date, NEW.status_id)    
    END IF;    
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_log_status
BEFORE UPDATE
ON bug
FOR EACH ROW
EXECUTE PROCEDURE log_update();

CREATE OR REPLACE FUNCTION log_insert()
  RETURNS trigger AS
$BODY$
BEGIN
    INSERT INTO bughistory(num, date, status_id)
    VALUES (NEW.num, now()::date, NEW.status_id);
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER insert_log_status
AFTER INSERT
ON bug
FOR EACH ROW
EXECUTE PROCEDURE log_insert();

 

Структура созданной таблицы bughistory

CREATE TABLE bughistory
(
  num integer,
  date date,
  status_id integer,
  id serial NOT NULL,
  CONSTRAINT pk_bhis PRIMARY KEY (id ),
  CONSTRAINT status_fk FOREIGN KEY (status_id)
      REFERENCES bugstatus (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT to_bug_table FOREIGN KEY (num)
      REFERENCES bug (num) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE bughistory
  OWNER TO postgres;

-- Index: fki_status_fk

-- DROP INDEX fki_status_fk;

CREATE INDEX fki_status_fk
  ON bughistory
  USING btree
  (status_id );

-- Index: fki_to_bug_table

-- DROP INDEX fki_to_bug_table;

CREATE INDEX fki_to_bug_table
  ON bughistory
  USING btree
  (num );

Так же прилагаю дамп базы данных dbhw8