--------------------------------------------------------
--  Datei erstellt -Mittwoch-Mai-16-2012   
--------------------------------------------------------

--------------------------------------------------------
--  Datenbank-Konfiguration
--
--  Der Benutzer der Maintain-Datenbank muss SELECT-Recht auf der SIG_KRANKHEIT-Tabelle der App-Datenbank besitzen!
--  Dies kann durch Ausfuehrung des folgenden Befehls innerhalb der App-Datenbank erreicht werden: 
--  GRANT SELECT ON SIG_KRANKHEIT TO $maintainUser; ($maintainUser ist dabei der Maintain-Benutzername)
--  Dieser Befehl muss nach jedem Neuanlegen der Tabelle SIG_KRANKHEIT erneut ausgefuehrt werden.
--
--  Achtung: Usernamen gross schreiben!
--------------------------------------------------------
define appDBUser = 'APPLICATION-DB-USER';
define maintainDBUser = 'MAINTAIN-DB-USER';

--------------------------------------------------------
--  DDL for Sequence SEQUENCE_FLEX_QUERY_ID
--------------------------------------------------------

   CREATE SEQUENCE  "SEQUENCE_FLEX_QUERY_ID"  MINVALUE 0 MAXVALUE 9999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Trigger SIGS_FLEX
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIGS_FLEX" BEFORE INSERT ON SIGS_FLEX_QUERY FOR EACH ROW

DECLARE
   -- Array für die Namen der Attribute
  TYPE array_of_attributes IS TABLE OF SIGS_FLEX_QUERY_AV.ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
  attribute_names array_of_attributes;
  -- Array für die Werte der Attribute von SIGS_FLEX_QUERY_AV
  TYPE array_of_values IS TABLE OF SIGS_FLEX_QUERY_AV.VALUE%TYPE INDEX BY BINARY_INTEGER;
  attribute_values array_of_values;

  -- Hilfsvariablen
  number_of_attributes NUMBER;
  anzahl NUMBER;
  queryStr VARCHAR2(4000);
  time_before BINARY_INTEGER;
  time_after BINARY_INTEGER;
BEGIN
  -- Starte Zeitmessung
  time_before := DBMS_UTILITY.GET_TIME;

  -- Anfrage einlesen
  number_of_attributes := 0;
  FOR var IN (SELECT ATTRIBUTE, VALUE FROM SIGS_FLEX_QUERY_AV WHERE QUERY_ID = :new.QUERY_ID) LOOP
    number_of_attributes := number_of_attributes + 1;
    attribute_names(number_of_attributes) := var.ATTRIBUTE;
    attribute_values(number_of_attributes) := var.VALUE;
  END LOOP;
 
  
  /* 1. Phase: */
  -- Prüfen ob das Geheimnis direkt impliziert wird, d.h. ob die Anfrage ein potentielles Geheimnis überdeckt
  
  -- ###########################################
  -- Ziel der dynamischen Anfrage
  -- ###########################################
  /* SELECT COUNT(*) FROM 
    (
      SELECT POT_SEC_ID FROM SIGS_FLEX_POT_SEC P WHERE 
        P.RELATIONNAME = 'SIG_KRANKHEIT' AND P.USER_ID = 23 AND 
        EXISTS
        (
          SELECT POT_SEC_ID FROM SIGS_FLEX_POT_SEC_AV AV WHERE AV.POT_SEC_ID = P.POT_SEC_ID AND AV.ATTRIBUTE = 'SYMPTOM' AND (AV.VALUE = '*' OR AV.VALUE = '*')
        ) AND
        EXISTS
        (
          SELECT POT_SEC_ID FROM SIGS_FLEX_POT_SEC_AV AV WHERE AV.POT_SEC_ID = P.POT_SEC_ID AND AV.ATTRIBUTE = 'DIAGNOSIS' AND (AV.VALUE = 'td4_Cancer' OR AV.VALUE = '*')
        ) AND
        EXISTS
        (
          SELECT POT_SEC_ID FROM SIGS_FLEX_POT_SEC_AV AV WHERE AV.POT_SEC_ID = P.POT_SEC_ID AND AV.ATTRIBUTE = 'PATIENT' AND (AV.VALUE = 'td4_Smith' OR AV.VALUE = '*')
        )
    );
  */
  
  -- Anfang
  queryStr := 'SELECT COUNT(*) FROM ( SELECT POT_SEC_ID FROM SIGS_FLEX_POT_SEC P WHERE P.RELATIONNAME = ''' || :new.RELATIONNAME || ''' AND P.USER_ID = ' || :new.USER_ID;
  
  -- EXISTS-Teile
  FOR i IN 1..number_of_attributes LOOP
    queryStr := queryStr || ' AND EXISTS ( SELECT POT_SEC_ID FROM SIGS_FLEX_POT_SEC_AV AV WHERE AV.POT_SEC_ID = P.POT_SEC_ID AND AV.ATTRIBUTE = ''' || attribute_names(i) || ''' AND (AV.VALUE = ''' || attribute_values(i) || ''' OR AV.VALUE = ''*'') )';
  END LOOP;
  
  queryStr := queryStr || ')';
  
  EXECUTE IMMEDIATE queryStr INTO anzahl;
  
  IF (anzahl = 1) THEN
    -- 1. Fall := Mind. ein Geheimnis wird direkt impliziert
    -- Anfrage muss verweigert werden, d.h. mum in SIGS_FLEX_QUERY_STAT.REACTION eintragen
    :new.REACTION := 'mum';
    
  /* 2. Phase: */
  ELSE
    -- 2. Fall := Keiner der Geheimnisse wird direkt impliziert
    -- Alle von der Anfrage überdeckten Prämissen vorläufig markieren
    
    -- ###########################################
    -- Ziel der dynamischen Anfrage
    -- ###########################################  
  /* 
    UPDATE SIGS_FLEX_SIGNATURES_P p SET FLAG_IMPL = 1 WHERE FLAG_IMPL = 0 AND 
      EXISTS
      (
        SELECT SIG_ID FROM SIGS_FLEX_SIGNATURES s WHERE s.SIG_ID = p.SIG_ID AND S.RELATIONNAME = 'SIG_KRANKHEIT' AND S.USER_ID = 23
      ) AND
      EXISTS 
      (
        SELECT SIG_ID FROM SIGS_FLEX_SIGNATURES_PAV pav WHERE pav.SIG_ID = p.SIG_ID AND pav.P_ID = p.P_ID AND PAV.ATTRIBUTE = 'SYMPTOM' AND (PAV.VALUE = '*' OR PAV.VALUE = '*')
      ) AND
      EXISTS 
      (
        SELECT SIG_ID FROM SIGS_FLEX_SIGNATURES_PAV pav WHERE pav.SIG_ID = p.SIG_ID AND pav.P_ID = p.P_ID AND PAV.ATTRIBUTE = 'DIAGNOSIS' AND (PAV.VALUE = 'td3_b4' OR PAV.VALUE = '*')
      ) AND 
      EXISTS 
      (
        SELECT SIG_ID FROM SIGS_FLEX_SIGNATURES_PAV pav WHERE pav.SIG_ID = p.SIG_ID AND pav.P_ID = p.P_ID AND PAV.ATTRIBUTE = 'PATIENT' AND (PAV.VALUE = 'td3_Smith' OR PAV.VALUE = '*')
      );
    */
    -- Anfang
    queryStr := 'UPDATE SIGS_FLEX_SIGNATURES_P p SET FLAG_IMPL = 1 WHERE FLAG_IMPL = 0 AND 
    EXISTS ( SELECT SIG_ID FROM SIGS_FLEX_SIGNATURES s WHERE s.SIG_ID = p.SIG_ID AND S.RELATIONNAME = ''' || :new.RELATIONNAME || ''' AND S.USER_ID = ' || :new.USER_ID || ' ) AND ';
    
    -- Attribute selektieren
    FOR i IN 1..number_of_attributes LOOP
      queryStr := queryStr || 'EXISTS 
      ( SELECT SIG_ID FROM SIGS_FLEX_SIGNATURES_PAV pav WHERE pav.SIG_ID = p.SIG_ID AND pav.P_ID = p.P_ID AND PAV.ATTRIBUTE = ''' ||  attribute_names(i) || ''' AND (PAV.VALUE = ''' ||  attribute_values(i) || ''' OR PAV.VALUE = ''*''))';
      
      IF (i != number_of_attributes) THEN
        queryStr := queryStr || ' AND ';
      END IF;
    END LOOP;
    
    EXECUTE IMMEDIATE queryStr;

    -- Prüfen ob alle Prämissen einer Signatur markiert sind
    SELECT COUNT(*) INTO anzahl FROM (SELECT 1 FROM DUAL WHERE EXISTS (
      SELECT SIG_ID FROM SIGS_FLEX_SIGNATURES_P s1 WHERE FLAG_IMPL=1 AND FLAG_OLD=0 AND NOT EXISTS (
        SELECT 1 FROM SIGS_FLEX_SIGNATURES_P s2 WHERE FLAG_IMPL=0 AND s1.SIG_ID=s2.SIG_ID)));
          
    IF (anzahl = 1 ) THEN
      -- 2.1 Fall := Mind. eine Signatur ist vollständig markiert
      -- Anfrage muss verweigert werden, d.h. mum in SIGS_FLEX_QUERY_STAT.REACTION eintragen
      :new.REACTION := 'mum';
      
      -- Alle vorläufig gesetzten Markierungen zurücksetzen
      UPDATE SIGS_FLEX_SIGNATURES_P SET FLAG_IMPL = FLAG_OLD WHERE FLAG_IMPL != FLAG_OLD;
    ELSE
      -- 2.2 Fall := Keiner der Signaturen ist vollständig markiert
      -- Anfrage darf Wahrheitsgemäß beantwortet werden, d.h. Anfrage auswerten [eval(Phi)] und anschließend das Ergebnis in CQE_QUERY_STAT.RESULT eintragen
     
      -- ###########################################
      -- Ziel der dynamischen Anfrage
      -- ###########################################     
      /*
      SELECT COUNT(*) INTO anzahl FROM (SELECT 1 FROM DUAL WHERE EXISTS (SELECT * FROM 
      cqedb1.SIG_KRANKHEIT WHERE 
      (ATTRIBUT_1 = WERT_VON_ATTRIBUT_1 OR WERT_VON_ATTRIBUT_1 = '*') AND 
      (ATTRIBUT_2 = WERT_VON_ATTRIBUT_2 OR WERT_VON_ATTRIBUT_2 = '*') AND 
      (ATTRIBUT_3 = WERT_VON_ATTRIBUT_3 OR WERT_VON_ATTRIBUT_3 = '*')));
      */
      
      -- Anfang
      queryStr := 'SELECT COUNT(*) FROM (SELECT 1 FROM DUAL WHERE EXISTS (SELECT * FROM &appDBUser .' || :new.RELATIONNAME || ' WHERE ';
      
      -- Attribute selektieren
      FOR i IN 1..number_of_attributes LOOP
        queryStr := queryStr || '(' || attribute_names(i) || ' = ''' || attribute_values(i) || ''' OR ''' || attribute_values(i) || ''' = ''*'')';
        IF (i != number_of_attributes) THEN
          queryStr := queryStr || ' AND ';
        END IF;
      END LOOP;
        
      -- Ende
      queryStr := queryStr || '))';
      
      EXECUTE IMMEDIATE queryStr INTO anzahl;
            
      IF (anzahl = 1) THEN
        -- 2.2.1 Fall := eval(Phi) = 1
        :new.REACTION := 1;
        -- Alle vorläufigen Markierungen übernehmen
        UPDATE SIGS_FLEX_SIGNATURES_P SET FLAG_OLD = FLAG_IMPL WHERE FLAG_IMPL!=FLAG_OLD;
      ELSE
        -- 2.2.1 Fall := eval(Phi) = 0
        :new.REACTION := 0;
        -- Alle vorläufig gesetzten Markierungen zurücksetzen
        UPDATE SIGS_FLEX_SIGNATURES_P SET FLAG_IMPL = FLAG_OLD WHERE FLAG_IMPL!=FLAG_OLD;
      END IF;
    END IF;
  END IF;
  
  -- Zeitmessung beenden
  time_after := DBMS_UTILITY.GET_TIME;
  :new.PROCESSING_TIME := (time_after - time_before) * 10;
  -- neue Tupel wieder entfernen
  DELETE FROM SIGS_FLEX_QUERY_AV WHERE QUERY_ID = :new.QUERY_ID;
  
END;
/
ALTER TRIGGER "SIGS_FLEX" ENABLE;
--------------------------------------------------------
--  DDL for Trigger SIGS_SIG_KRANKHEIT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIGS_SIG_KRANKHEIT" BEFORE INSERT ON SIGS_QUE_SIG_KRANKHEIT FOR EACH ROW

DECLARE
  -- Hilfsvariable
  anzahl NUMBER;
  time_before BINARY_INTEGER;
  time_after BINARY_INTEGER;
BEGIN
  -- Starte Zeitmessung
  time_before := DBMS_UTILITY.GET_TIME;
  
  -- Alle bis auf den aktuellen Tupel aus der Tabelle entfernen
  DELETE FROM SIGS_QUE_SIG_KRANKHEIT;
  
  /* 1. Phase: */
  -- Prüfen ob das Geheimnis direkt impliziert wird, d.h. ob die Anfrage ein potentielles Geheimnis überdeckt
  SELECT COUNT(*) INTO anzahl FROM (SELECT 1 FROM DUAL WHERE EXISTS (SELECT * FROM SIGS_PS_SIG_KRANKHEIT WHERE (USER_ID = :new.USER_ID) AND (SYMPTOM = :new.SYMPTOM OR SYMPTOM = '*') AND (DIAGNOSIS = :new.DIAGNOSIS OR DIAGNOSIS = '*') AND (PATIENT = :new.PATIENT OR PATIENT = '*')));
  
  IF (anzahl = 1) THEN
    -- 1. Fall := Mind. ein Geheimnis wird direkt impliziert
    -- Anfrage muss verweigert werden, d.h. mum in SIGS_QUE_SIG_KRANKHEIT.RESULT eintragen
    :new.REACTION := 'mum';
  
  /* 2. Phase: */
  ELSE
    -- 2. Fall := Keiner der Geheimnisse wird direkt impliziert
    -- Alle von der Anfrage überdeckten Prämissen vorläufig markieren
    UPDATE SIGS_SIG_SIG_KRANKHEIT SET FLAG_IMPL = 1 WHERE (SYMPTOM = :new.SYMPTOM OR SYMPTOM = '*') AND (DIAGNOSIS = :new.DIAGNOSIS OR DIAGNOSIS = '*') AND (PATIENT = :new.PATIENT OR PATIENT = '*');
    
    -- Prüfen ob alle Prämissen einer Signatur markiert sind
    SELECT COUNT(*) INTO anzahl FROM (SELECT 1 FROM DUAL WHERE EXISTS (SELECT SIG_ID FROM SIGS_SIG_SIG_KRANKHEIT s1 WHERE FLAG_IMPL=1 AND FLAG_OLD=0 AND NOT EXISTS (SELECT 1 FROM SIGS_SIG_SIG_KRANKHEIT s2 WHERE FLAG_IMPL=0 AND s1.SIG_ID=s2.SIG_ID)));

    IF (anzahl = 1 ) THEN
      -- 2.1 Fall := Mind. eine Signatur ist vollständig markiert
      -- Anfrage muss verweigert werden, d.h. mum in SIGS_QUE_SIG_KRANKHEIT.RESULT eintragen
      :new.REACTION := 'mum';
      
      -- Alle vorläufig gesetzten Markierungen zurücksetzen
      UPDATE SIGS_SIG_SIG_KRANKHEIT SET FLAG_IMPL = FLAG_OLD WHERE FLAG_IMPL!=FLAG_OLD;
    ELSE
      -- 2.2 Fall := Keiner der Signaturen ist vollständig markiert
      -- Anfrage darf Wahrheitsgemäß beantwortet werden, d.h. Anfrage auswerten [eval(Phi)] und anschließend das Ergebnis in SIGS_QUE_SIG_KRANKHEIT.RESULT eintragen
      SELECT COUNT(*) INTO anzahl FROM (SELECT 1 FROM DUAL WHERE EXISTS (SELECT * FROM &appDBUser . SIG_KRANKHEIT K WHERE (K.SYMPTOM = :new.SYMPTOM OR :new.SYMPTOM = '*') AND (K.DIAGNOSIS = :new.DIAGNOSIS OR :new.DIAGNOSIS = '*') AND (K.PATIENT = :new.PATIENT OR :new.PATIENT = '*')));
      
      IF (anzahl = 1) THEN
        -- 2.2.1 Fall := eval(Phi) = 1
        :new.REACTION := 1;
        
        -- Alle vorläufigen Markierungen übernehmen
        UPDATE SIGS_SIG_SIG_KRANKHEIT SET FLAG_OLD = FLAG_IMPL WHERE FLAG_IMPL!=FLAG_OLD;
      ELSE
        -- 2.2.1 Fall := eval(Phi) = 0
        :new.REACTION := 0;
        
        -- Alle vorläufig gesetzten Markierungen zurücksetzen
        UPDATE SIGS_SIG_SIG_KRANKHEIT SET FLAG_IMPL = FLAG_OLD WHERE FLAG_IMPL!=FLAG_OLD;
      END IF;
    END IF;
  END IF;
  
  -- Zeitmessung beenden
  time_after := DBMS_UTILITY.GET_TIME;
  :new.PROCESSING_TIME := (time_after - time_before) * 10;
END;
/
ALTER TRIGGER "SIGS_SIG_KRANKHEIT" ENABLE;
--------------------------------------------------------
--  DDL for Procedure SIGNATURE_TRANSFORMATION
--------------------------------------------------------
  CREATE OR REPLACE PROCEDURE "SIGNATURE_TRANSFORMATION" (relationname VARCHAR2) AS

-- Array für die Namen der Attribute
TYPE array_of_attributes IS TABLE OF ALL_TAB_COLUMNS.COLUMN_NAME%TYPE INDEX BY BINARY_INTEGER;
attribute_names array_of_attributes;

-- Laufvariable und Anzahl der Attribute der Relation
anzahlAttribute NUMBER;

-- Anfragestring 
queryStr VARCHAR2(4000);

BEGIN

/* Attribute der Relation in das Array einfügen */
anzahlAttribute := 0;
FOR var IN (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = '&appDBUser' AND TABLE_NAME = relationname) LOOP
  attribute_names(anzahlAttribute) := var.COLUMN_NAME;
  anzahlAttribute := anzahlAttribute + 1;
END LOOP;

/* SIGS_FLEX_SIGNATURES */
queryStr := 'INSERT INTO SIGS_FLEX_SIGNATURES (SIG_ID, RELATIONNAME, USER_ID) SELECT DISTINCT SIG_ID, ''' || relationname || ''', USER_ID FROM SIGS_SIG_' || relationname;
EXECUTE IMMEDIATE queryStr;

/* SIGS_FLEX_SIGNATURES_P */
queryStr := 'INSERT INTO SIGS_FLEX_SIGNATURES_P (SIG_ID, P_ID, FLAG_OLD, FLAG_IMPL) SELECT SIG_ID, ROWNUM , 0, 0 FROM SIGS_SIG_' || relationname || ' ORDER BY SIG_ID';
EXECUTE IMMEDIATE queryStr;

/* P_ID's richtig setzen */
 UPDATE SIGS_FLEX_SIGNATURES_P S1 SET P_ID = P_ID - (SELECT COUNT(SIG_ID) FROM SIGS_FLEX_SIGNATURES_P S2 WHERE S2.SIG_ID < S1.SIG_ID);

/* SIGS_FLEX_SIGNATURES_PAV */
-- ###########################################
-- ####   Ziel der dynamischen Anfrage   #####
-- ###########################################
/* INSERT INTO SIGS_FLEX_SIGNATURES_PAV (SIG_ID, P_ID, ATTRIBUTE, VALUE) SELECT SIG_ID, ROWNUM, 'SYMPTOM', SYMPTOM FROM SIGS_SIG_SIG_KRANKHEIT ORDER BY SIG_ID; */
FOR i IN 0..anzahlAttribute-1 LOOP
  queryStr := 'INSERT INTO SIGS_FLEX_SIGNATURES_PAV (SIG_ID, P_ID, ATTRIBUTE, VALUE) SELECT SIG_ID, ROWNUM, ''' || attribute_names(i) || ''', ' || attribute_names(i) || ' FROM SIGS_SIG_SIG_KRANKHEIT ORDER BY SIG_ID';
  EXECUTE IMMEDIATE queryStr;
END LOOP;

/* P_ID's richtig setzen */
queryStr := 'UPDATE SIGS_FLEX_SIGNATURES_PAV S1 SET P_ID = P_ID - (SELECT COUNT(SIG_ID) FROM SIGS_FLEX_SIGNATURES_PAV S2 WHERE S2.SIG_ID < S1.SIG_ID AND ATTRIBUTE = ''' || attribute_names(0) || ''')';
EXECUTE IMMEDIATE queryStr;

END;

/

--------------------------------------------------------
--  DDL for Procedure POT_SEC_TRANSFORMATION
--------------------------------------------------------
  CREATE OR REPLACE PROCEDURE "POT_SEC_TRANSFORMATION" (relationname VARCHAR2) AS

-- Array für die Namen der Attribute
TYPE array_of_attributes IS TABLE OF ALL_TAB_COLUMNS.COLUMN_NAME%TYPE INDEX BY BINARY_INTEGER;
attribute_names array_of_attributes;

-- Laufvariable und Anzahl der Attribute der Relation
anzahlAttribute NUMBER;

-- Anfragestring 
queryStr VARCHAR2(4000);

BEGIN

/* Attribute der Relation in das Array einfügen */
anzahlAttribute := 0;
FOR var IN (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = '&appDBUser' AND TABLE_NAME = relationname) LOOP
  attribute_names(anzahlAttribute) := var.COLUMN_NAME;
  anzahlAttribute := anzahlAttribute + 1;
END LOOP;

/* SIGS_FLEX_POT_SEC */
queryStr := 'INSERT INTO SIGS_FLEX_POT_SEC (POT_SEC_ID, RELATIONNAME, USER_ID) SELECT ID, ''' || relationname || ''', USER_ID FROM SIGS_PS_' || relationname;
EXECUTE IMMEDIATE queryStr;

/* SIGS_FLEX_POT_SEC_AV */
  -- ###########################################
  -- ####   Ziel der dynamischen Anfrage   #####
  -- ###########################################
  /* INSERT INTO SIGS_FLEX_POT_SEC_AV (POT_SEC_ID, ATTRIBUTE, VALUE) SELECT ID, 'ATTRIBUTENAME', ATTRIBUTENAME FROM SIGS_PS_SIG_KRANKHEIT; */
  FOR i IN 0..anzahlAttribute-1 LOOP
      queryStr := 'INSERT INTO SIGS_FLEX_POT_SEC_AV (POT_SEC_ID, ATTRIBUTE, VALUE) SELECT ID, ''' || attribute_names(i) || ''', ' || attribute_names(i) || ' FROM SIGS_PS_SIG_KRANKHEIT';
      EXECUTE IMMEDIATE queryStr;
  END LOOP;
  
END;

/

