|
/*
|
|
OP_Code для PH_HISTORY
|
|
0 - ph updated
|
|
1 - ph deleted
|
|
2 - ph inserted
|
|
3 - attrval deleted
|
|
4 - attrval inserted
|
|
*/
|
|
|
|
CREATE OR REPLACE FUNCTION AddUpdatedPh_to_PhHistory() RETURNS TRIGGER AS
|
|
$$
|
|
BEGIN
|
|
-- триггер фильтруется и смотрит только на эти поля, если добавляем тут, то и в вызывающую функцию тоже:
|
|
IF (OLD.name IS DISTINCT FROM NEW.name) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Name', new.name);
|
|
END IF;
|
|
IF (OLD.filename IS DISTINCT FROM NEW.filename) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'FileName', new.filename);
|
|
END IF;
|
|
IF (OLD.duration IS DISTINCT FROM NEW.duration) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Duration', new.duration);
|
|
END IF;
|
|
IF (OLD.type IS DISTINCT FROM NEW.type) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Type', new.type);
|
|
END IF;
|
|
IF (OLD.intro IS DISTINCT FROM NEW.intro) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Intro', new.intro);
|
|
END IF;
|
|
IF (OLD.outro IS DISTINCT FROM NEW.outro) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Outro', new.outro);
|
|
END IF;
|
|
IF (OLD.fadein IS DISTINCT FROM NEW.fadein) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'FadeIn', new.fadein);
|
|
END IF;
|
|
IF (OLD.fadeout IS DISTINCT FROM NEW.fadeout) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'FadeOut', new.fadeout);
|
|
END IF;
|
|
IF (OLD.startnext IS DISTINCT FROM NEW.startnext) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'StartNext', new.startnext);
|
|
END IF;
|
|
IF (OLD.ph_start IS DISTINCT FROM NEW.ph_start) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Ph_Start', new.ph_start);
|
|
END IF;
|
|
IF (OLD.ph_stop IS DISTINCT FROM NEW.ph_stop) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Ph_Stop', new.ph_stop);
|
|
END IF;
|
|
IF (OLD.cmd IS DISTINCT FROM NEW.cmd) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Cmd', new.cmd);
|
|
END IF;
|
|
IF (OLD.freq IS DISTINCT FROM NEW.freq) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Freq', new.freq);
|
|
END IF;
|
|
IF (OLD.smplsize IS DISTINCT FROM NEW.smplsize) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'SmplSize', new.smplsize);
|
|
END IF;
|
|
IF (OLD.radioid IS DISTINCT FROM NEW.radioid) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'RadioID', new.radioid);
|
|
END IF;
|
|
IF (OLD.deleted IS DISTINCT FROM NEW.deleted) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Deleted', new.deleted);
|
|
END IF;
|
|
IF (OLD.id_number IS DISTINCT FROM NEW.id_number) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'ID_Number', new.id_number);
|
|
END IF;
|
|
IF (OLD.modifyft IS DISTINCT FROM NEW.modifyft) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'ModifyFT', new.modifyft);
|
|
END IF;
|
|
IF (OLD.ddb_owner IS DISTINCT FROM NEW.ddb_owner) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'DDB_Owner', new.ddb_owner);
|
|
END IF;
|
|
IF (OLD.version IS DISTINCT FROM NEW.version) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Version', new.version);
|
|
END IF;
|
|
IF (OLD.ddb_id IS DISTINCT FROM NEW.ddb_id) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'DDB_ID', new.ddb_id);
|
|
END IF;
|
|
IF (OLD.intro2 IS DISTINCT FROM NEW.intro2) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Intro2', new.intro2);
|
|
END IF;
|
|
IF (OLD.intro3 IS DISTINCT FROM NEW.intro3) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Intro3', new.intro3);
|
|
END IF;
|
|
IF (OLD.startthis IS DISTINCT FROM NEW.startthis) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'StartThis', new.startthis);
|
|
END IF;
|
|
IF (OLD.archive IS DISTINCT FROM NEW.archive) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Archive', new.archive);
|
|
END IF;
|
|
IF (OLD.readyonair IS DISTINCT FROM NEW.readyonair) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'ReadyOnAir', new.readyonair);
|
|
END IF;
|
|
IF (OLD.lccode IS DISTINCT FROM NEW.lccode) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'LcCode', new.lccode);
|
|
END IF;
|
|
IF (OLD.publisher IS DISTINCT FROM NEW.publisher) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Publisher', new.publisher);
|
|
END IF;
|
|
IF (OLD.cmd2 IS DISTINCT FROM NEW.cmd2) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Cmd2', new.cmd2);
|
|
END IF;
|
|
IF (OLD.textsizems IS DISTINCT FROM NEW.textsizems) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'TextSizeMs', new.textsizems);
|
|
END IF;
|
|
IF (OLD.phonotype IS DISTINCT FROM NEW.phonotype) THEN
|
|
INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'PhonoType', new.phonotype);
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION AddDeletedPh_to_PhHistory() RETURNS TRIGGER AS
|
|
$$
|
|
BEGIN
|
|
INSERT INTO PH_HISTORY (PH_ID, OP_Code) VALUES (old.id, 1);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION AddInsertedPh_to_PhHistory() RETURNS TRIGGER AS
|
|
$$
|
|
BEGIN
|
|
INSERT INTO PH_HISTORY (PH_ID, OP_Code, Name, Value) VALUES (new.id, 2, 'Type', new.Type);
|
|
INSERT INTO PH_HISTORY (PH_ID, OP_Code, Name, Value) VALUES (new.id, 2, 'Name', new.Name);
|
|
INSERT INTO PH_HISTORY (PH_ID, OP_Code, Name, Value) VALUES (new.id, 2, 'FileName', new.FileName);
|
|
INSERT INTO PH_HISTORY (PH_ID, OP_Code, Name, Value) VALUES (new.id, 2, 'ID_Number', new.ID_Number);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION AddDeletedAttribute_to_PhHistory() RETURNS TRIGGER AS
|
|
$$
|
|
BEGIN
|
|
INSERT INTO PH_HISTORY (PH_ID, OP_Code, AttrVal_ID) VALUES (old.ph_id, 3, old.attrval_id);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION AddInsertedAttribute_to_PhHistory() RETURNS TRIGGER AS
|
|
$$
|
|
BEGIN
|
|
INSERT INTO PH_HISTORY (PH_ID, OP_Code, AttrVal_ID) VALUES (new.ph_id, 4, new.attrval_id);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DO
|
|
$$
|
|
DECLARE
|
|
ver int := 3;
|
|
|
|
BEGIN
|
|
|
|
IF EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = 'components')
|
|
THEN
|
|
IF EXISTS (SELECT veri FROM public.components WHERE name = 'mdb.ph_history' AND veri >= ver)
|
|
THEN
|
|
RAISE NOTICE 'Current PH_HISTORY version is equal or higher than %', ver;
|
|
RETURN;
|
|
END IF;
|
|
ELSE
|
|
RAISE NOTICE 'Components table not found. Script exit';
|
|
RETURN;
|
|
END IF;
|
|
|
|
RAISE NOTICE 'Script begin';
|
|
|
|
DROP TABLE IF EXISTS PH_HISTORY CASCADE;
|
|
|
|
CREATE TABLE PH_HISTORY
|
|
(
|
|
"id" SERIAL NOT NULL
|
|
CONSTRAINT PK_hystory PRIMARY KEY,
|
|
"username" varchar(50) NULL
|
|
CONSTRAINT DF_hystory_UserName DEFAULT (current_user),
|
|
"hostname" varchar(50) NULL
|
|
CONSTRAINT DF_hystory_HostName DEFAULT (inet_server_addr()),
|
|
"modifydate" timestamptz NOT NULL
|
|
CONSTRAINT DF_hystory_ModifyDate DEFAULT (now()),
|
|
"op_code" smallint NOT NULL
|
|
CONSTRAINT DF_hystory_OP_Code DEFAULT ((0)),
|
|
"ph_id" int NOT NULL,
|
|
"name" varchar(50) NULL,
|
|
"value" text NULL,
|
|
"attrval_id" int NULL
|
|
);
|
|
|
|
DROP TRIGGER IF EXISTS History_Updated ON PH;
|
|
-- Тут в MS портянка с курсором ! (не совсем понятно зачем)
|
|
CREATE TRIGGER History_Updated
|
|
AFTER UPDATE OF
|
|
-- триггер будет работать только для указанных в запросе полей, если добавляем тут, то и в триггер тоже:
|
|
name, filename, duration, type, intro, outro, fadein, fadeout, startnext, ph_start, ph_stop, cmd, freq,
|
|
smplsize, radioid, deleted, id_number, modifyft, ddb_owner, version, ddb_id, intro2, intro3, startthis,
|
|
archive, readyonair, lccode, publisher, cmd2, textsizems, phonotype
|
|
ON PH
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE AddUpdatedPh_to_PhHistory();
|
|
RAISE NOTICE 'Created: TRIGGER History_Updated';
|
|
|
|
DROP TRIGGER IF EXISTS History_Deleted ON PH;
|
|
CREATE TRIGGER History_Deleted
|
|
AFTER DELETE
|
|
ON PH
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE AddDeletedPh_to_PhHistory();
|
|
RAISE NOTICE 'Created: TRIGGER History_Deleted';
|
|
|
|
DROP TRIGGER IF EXISTS History_Inserted ON PH;
|
|
CREATE TRIGGER History_Inserted
|
|
AFTER INSERT
|
|
ON PH
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE AddInsertedPh_to_PhHistory();
|
|
RAISE NOTICE 'Created: TRIGGER History_Inserted';
|
|
|
|
DROP TRIGGER IF EXISTS History_Attr_Deleted ON PH_TO_ATTRVALS;
|
|
CREATE TRIGGER History_Attr_Deleted
|
|
AFTER DELETE
|
|
ON PH_TO_ATTRVALS
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE AddDeletedAttribute_to_PhHistory();
|
|
RAISE NOTICE 'Created: TRIGGER History_Attr_Deleted';
|
|
|
|
DROP TRIGGER IF EXISTS History_Attr_Inserted ON PH_TO_ATTRVALS;
|
|
CREATE TRIGGER History_Attr_Inserted
|
|
AFTER INSERT
|
|
ON PH_TO_ATTRVALS
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE AddInsertedAttribute_to_PhHistory();
|
|
RAISE NOTICE 'Created: TRIGGER History_Attr_Inserted';
|
|
|
|
IF EXISTS (SELECT veri FROM public.components WHERE name = 'mdb.ph_history')
|
|
THEN
|
|
UPDATE public.components set veri = ver WHERE name = 'mdb.ph_history';
|
|
ELSE
|
|
INSERT INTO public.components (name, vers, veri) VALUES ('mdb.ph_history', '', ver);
|
|
END IF;
|
|
|
|
RAISE NOTICE 'Script end!';
|
|
END;
|
|
$$;
|
