From c59f2ff771d656d6eccc1f5da68c674500c6951f Mon Sep 17 00:00:00 2001 From: elgekko Date: Fri, 10 Mar 2023 15:55:14 +0100 Subject: [PATCH] MEDIA filecount DB modositas --- .../scripts/043_add_filecount_to_media.sql | 8 ++- ...alter_mediafile_triggers_add_mediadata.sql | 59 +++++++++++++++++++ 2 files changed, 66 insertions(+), 1 deletion(-) create mode 100644 server/user.jobengine.osgi.db/migrations/scripts/044_alter_mediafile_triggers_add_mediadata.sql diff --git a/server/user.jobengine.osgi.db/migrations/scripts/043_add_filecount_to_media.sql b/server/user.jobengine.osgi.db/migrations/scripts/043_add_filecount_to_media.sql index a4e2ec63..4acd2fdc 100644 --- a/server/user.jobengine.osgi.db/migrations/scripts/043_add_filecount_to_media.sql +++ b/server/user.jobengine.osgi.db/migrations/scripts/043_add_filecount_to_media.sql @@ -5,6 +5,11 @@ DROP TRIGGER TRG_MEDIA_AFTER_UPDATE ALTER TABLE MEDIA ADD COLUMN FILECOUNT INT @ +CREATE TABLE FILECOUNTS AS +(SELECT MEDIAID, MEDIAFILECOUNT FROM VW_MEDIAFILES) +WITH DATA +@ + UPDATE MEDIA m SET m.FILECOUNT = ( SELECT f.MEDIAFILECOUNT FROM FILECOUNTS f WHERE f.MEDIAID = m.ID @@ -25,7 +30,8 @@ BEGIN ATOMIC END @ - +DROP TABLE FILECOUNTS +@ --CALL SYSPROC.ADMIN_CMD('REORG TABLE DB2ADMIN.MEDIA') --@ diff --git a/server/user.jobengine.osgi.db/migrations/scripts/044_alter_mediafile_triggers_add_mediadata.sql b/server/user.jobengine.osgi.db/migrations/scripts/044_alter_mediafile_triggers_add_mediadata.sql new file mode 100644 index 00000000..e1072d7f --- /dev/null +++ b/server/user.jobengine.osgi.db/migrations/scripts/044_alter_mediafile_triggers_add_mediadata.sql @@ -0,0 +1,59 @@ +-- // Modify MEDIAFILE trigger ad MEDIADATA changes +-- Migration SQL that makes the change goes here. + +CREATE OR REPLACE TRIGGER TRG_MEDIAFILE_AFTER_INSERT +AFTER INSERT ON MEDIAFILE +REFERENCING NEW AS new_data +FOR EACH ROW +BEGIN ATOMIC + IF new_data.storeid = TSMStoreId THEN + INSERT INTO MEDIADESCRIPTION (ITEMID, MEDIAID, MEDIAFILEID, DESCRIPTION) + SELECT ITEMID, MEDIAID, MEDIAFILEID, FULLDESC(ITEMID, MEDIAID, MEDIAFILEID) FROM VW_ITEMS + WHERE MEDIAFILEID = new_data.ID; + END IF; + UPDATE MEDIA m SET m.FILECOUNT = m.FILECOUNT + 1 WHERE m.ID = new_data.MEDIAID; +END +@ + +CREATE OR REPLACE TRIGGER TRG_MEDIAFILE_AFTER_DELETE +AFTER DELETE ON MEDIAFILE +REFERENCING OLD AS old_data +FOR EACH ROW +BEGIN ATOMIC + IF old_data.storeid = TSMStoreId THEN + DELETE FROM MEDIADESCRIPTION WHERE MEDIAFILEID = old_data.ID; + END IF; + UPDATE MEDIA m SET m.FILECOUNT = m.FILECOUNT - 1 WHERE m.ID = old_data.MEDIAID; +END +@ + +-- //@UNDO +-- SQL to undo the change goes here. +CREATE OR REPLACE TRIGGER TRG_MEDIAFILE_AFTER_INSERT +AFTER INSERT ON MEDIAFILE +REFERENCING NEW AS n +FOR EACH ROW +BEGIN ATOMIC + DECLARE storeid BIGINT; + SET storeid = (SELECT ID FROM STORE WHERE NAME='TSM'); + IF n.storeid = storeid THEN + INSERT INTO MEDIADESCRIPTION (ITEMID, MEDIAID, MEDIAFILEID, DESCRIPTION) + SELECT ITEMID, MEDIAID, MEDIAFILEID, FULLDESC(ITEMID, MEDIAID, MEDIAFILEID) FROM VW_ITEMS + WHERE MEDIAFILEID = N.ID; + END IF; +END +@ + +CREATE OR REPLACE TRIGGER TRG_MEDIAFILE_AFTER_DELETE +AFTER DELETE ON MEDIAFILE +REFERENCING OLD AS O +FOR EACH ROW +BEGIN ATOMIC + DECLARE storeid BIGINT; + SET storeid = (SELECT ID FROM STORE WHERE NAME='TSM'); + IF o.storeid = storeid THEN + DELETE FROM MEDIADESCRIPTION WHERE MEDIAFILEID = O.ID; + END IF; +END +@ + -- 2.54.0