test2bdbb

--------------------------------------------------------
--  File created - Monday-December-19-2016
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Sequence S_ANNOTATION
--------------------------------------------------------

   CREATE SEQUENCE  "S_ANNOTATION"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 56 CACHE 10 NOORDER  NOCYCLE ;
/
--------------------------------------------------------
--  DDL for Table ANNOTATION
--------------------------------------------------------

  CREATE TABLE "ANNOTATION"
   (	"ID" NUMBER,
	"B_ID" NUMBER,
	"U_ID" NUMBER,
	"INFO" VARCHAR2(20 BYTE)
   ) ;
/
--------------------------------------------------------
--  DDL for Table BUG
--------------------------------------------------------

  CREATE TABLE "BUG"
   (	"ID" NUMBER,
	"NAME" VARCHAR2(20 BYTE),
	"STATUS" VARCHAR2(20 BYTE) DEFAULT 'ACTIVE',
	"U_ID" NUMBER
   ) ;
/
--------------------------------------------------------
--  DDL for Table USERS
--------------------------------------------------------

  CREATE TABLE "USERS"
   (	"ID" NUMBER,
	"NAME" VARCHAR2(20 BYTE),
	"TYPE" NUMBER
   ) ;
/
REM INSERTING into ANNOTATION
SET DEFINE OFF;
Insert into ANNOTATION (ID,B_ID,U_ID,INFO) values (49,1,3,'ANNOTATIONTEXT2');
Insert into ANNOTATION (ID,B_ID,U_ID,INFO) values (50,1,2,'ADMIN_WAS_HERE');
Insert into ANNOTATION (ID,B_ID,U_ID,INFO) values (46,1,3,'Created issue.');
Insert into ANNOTATION (ID,B_ID,U_ID,INFO) values (47,1,3,'Nobody heped.');
Insert into ANNOTATION (ID,B_ID,U_ID,INFO) values (48,1,3,'ANNOTATIONTEXT');
Insert into ANNOTATION (ID,B_ID,U_ID,INFO) values (1,2,3,'hello');
Insert into ANNOTATION (ID,B_ID,U_ID,INFO) values (2,2,2,'admin1');
Insert into ANNOTATION (ID,B_ID,U_ID,INFO) values (3,3,3,'another one');
Insert into ANNOTATION (ID,B_ID,U_ID,INFO) values (4,3,1,'admin other');
REM INSERTING into BUG
SET DEFINE OFF;
Insert into BUG (ID,NAME,STATUS,U_ID) values (3,'OTHER_CLOSED','CLOSED',3);
Insert into BUG (ID,NAME,STATUS,U_ID) values (1,'OPEN_BUG','ACTIVE',3);
Insert into BUG (ID,NAME,STATUS,U_ID) values (2,'CLOSED_B','CLOSED',3);
REM INSERTING into USERS
SET DEFINE OFF;
Insert into USERS (ID,NAME,TYPE) values (1,'GOD',0);
Insert into USERS (ID,NAME,TYPE) values (2,'admin',0);
Insert into USERS (ID,NAME,TYPE) values (3,'user',1);
Insert into USERS (ID,NAME,TYPE) values (4,'normal',1);
--------------------------------------------------------
--  DDL for Index USERS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "USERS_PK" ON "USERS" ("ID")
  ;
/
--------------------------------------------------------
--  DDL for Index ANNOTATION_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "ANNOTATION_PK" ON "ANNOTATION" ("ID")
  ;
/
--------------------------------------------------------
--  DDL for Index BUG_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "BUG_PK" ON "BUG" ("ID")
  ;
/
--------------------------------------------------------
--  Constraints for Table USERS
--------------------------------------------------------

  ALTER TABLE "USERS" MODIFY ("ID" NOT NULL ENABLE);

  ALTER TABLE "USERS" MODIFY ("NAME" NOT NULL ENABLE);

  ALTER TABLE "USERS" MODIFY ("TYPE" NOT NULL ENABLE);

  ALTER TABLE "USERS" ADD CONSTRAINT "USERS_PK" PRIMARY KEY ("ID") ENABLE;
/
--------------------------------------------------------
--  Constraints for Table BUG
--------------------------------------------------------

  ALTER TABLE "BUG" ADD CONSTRAINT "BUG_PK" PRIMARY KEY ("ID") ENABLE;

  ALTER TABLE "BUG" MODIFY ("NAME" NOT NULL ENABLE);

  ALTER TABLE "BUG" MODIFY ("ID" NOT NULL ENABLE);

  ALTER TABLE "BUG" MODIFY ("STATUS" NOT NULL ENABLE);

  ALTER TABLE "BUG" MODIFY ("U_ID" NOT NULL ENABLE);
/
--------------------------------------------------------
--  Constraints for Table ANNOTATION
--------------------------------------------------------

  ALTER TABLE "ANNOTATION" ADD CONSTRAINT "ANNOTATION_PK" PRIMARY KEY ("ID") ENABLE;

  ALTER TABLE "ANNOTATION" MODIFY ("ID" NOT NULL ENABLE);

  ALTER TABLE "ANNOTATION" MODIFY ("INFO" NOT NULL ENABLE);

  ALTER TABLE "ANNOTATION" MODIFY ("B_ID" NOT NULL ENABLE);

  ALTER TABLE "ANNOTATION" MODIFY ("U_ID" NOT NULL ENABLE);
/
--------------------------------------------------------
--  Ref Constraints for Table ANNOTATION
--------------------------------------------------------

  ALTER TABLE "ANNOTATION" ADD CONSTRAINT "ANNOTATION_BUG_FK1" FOREIGN KEY ("B_ID")
	  REFERENCES "BUG" ("ID") ON DELETE CASCADE ENABLE;

  ALTER TABLE "ANNOTATION" ADD CONSTRAINT "ANNOTATION_USERS_FK1" FOREIGN KEY ("U_ID")
	  REFERENCES "USERS" ("ID") ON DELETE CASCADE ENABLE;
/
--------------------------------------------------------
--  Ref Constraints for Table BUG
--------------------------------------------------------

  ALTER TABLE "BUG" ADD CONSTRAINT "BUG_USERS_FK1" FOREIGN KEY ("U_ID")
	  REFERENCES "USERS" ("ID") ON DELETE CASCADE ENABLE;
/
--------------------------------------------------------
--  DDL for Trigger T_ANNOTATION_ID
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "T_ANNOTATION_ID"
BEFORE INSERT
ON ANNOTATION
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  IF(:NEW.ID IS NULL) THEN
  SELECT S_ANNOTATION.nextval
  INTO :new.ID
  FROM dual;
  end if;
END;
/
ALTER TRIGGER "T_ANNOTATION_ID" ENABLE;
/
--------------------------------------------------------
--  DDL for Function CREATE_ANNOTATION
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "CREATE_ANNOTATION" (userId IN ANNOTATION.U_ID%TYPE, bugId IN ANNOTATION.B_ID%TYPE, info IN ANNOTATION.INFO%TYPE)
RETURN ANNOTATION.ID%TYPE IS
    created_id ANNOTATION.ID%TYPE := -1;
  CURSOR CREATED (userId IN ANNOTATION.U_ID%TYPE, bugId IN ANNOTATION.B_ID%TYPE, info IN ANNOTATION.INFO%TYPE) IS
    SELECT ID
    FROM ANNOTATION
    WHERE B_ID=bugId AND U_ID=userId AND INFO=info;

  CURSOR VALID_CHECK_SUPERUSER (userId IN ANNOTATION.U_ID%TYPE) IS
    SELECT TYPE
    FROM USERS
    WHERE ID=userId;

  userRole USERS.ID%TYPE := -1;

  CURSOR BUG_OWNER (bugId IN ANNOTATION.B_ID%TYPE) IS
    SELECT U_ID
    FROM bug
    WHERE ID=bugId;

  bugOwner BUG.U_ID%TYPE := -1;

  CURSOR ACTIVE_COUNT (userId IN ANNOTATION.U_ID%TYPE) IS
    SELECT ID
    FROM BUG
    WHERE STATUS='ACTIVE' AND U_ID=userId;

  activeCount BUG.ID%TYPE := 0;

  CURSOR BUG_STATUS (bugId IN ANNOTATION.B_ID%TYPE) IS
    SELECT STATUS
    FROM BUG
    WHERE ID=bugId;

  bugStatus BUG.STATUS%TYPE := '';

  fk_violation_exception EXCEPTION;
  PRAGMA exception_init (fk_violation_exception, -2291);
BEGIN
  OPEN VALID_CHECK_SUPERUSER(userId);
  FETCH VALID_CHECK_SUPERUSER INTO userRole;
  CLOSE VALID_CHECK_SUPERUSER;

  IF (userRole != 0) THEN
    OPEN BUG_OWNER(bugId);
    FETCH BUG_OWNER INTO bugOwner;
    CLOSE BUG_OWNER;
    IF (bugOwner != userId) THEN
      RAISE_APPLICATION_ERROR(-20001, 'USER ' || userId || ' is not authorized to modify this issue!');
    END IF;
  END IF;

  OPEN ACTIVE_COUNT(userId);
  FETCH ACTIVE_COUNT INTO activeCount;
  CLOSE ACTIVE_COUNT;

  IF (activeCount != 1 AND userRole != 0)
    THEN RAISE_APPLICATION_ERROR(-20001, 'USER ' || userId || ' has no/more than one active issue(s)!');
  END IF;

  OPEN BUG_STATUS(bugId);
  FETCH BUG_STATUS INTO bugStatus;
  CLOSE BUG_STATUS;

  IF (bugStatus != 'ACTIVE')
    THEN RAISE_APPLICATION_ERROR(-20001, 'ISSUE ' || bugId || ' is not ACTIVE!');
  END IF;

  INSERT INTO ANNOTATION (B_ID, U_ID, INFO)
  VALUES (bugId, userId, info);

  OPEN CREATED(userId, bugId, info);
  FETCH CREATED INTO created_id;
  CLOSE CREATED;
  RETURN created_id;
EXCEPTION
  WHEN fk_violation_exception THEN
    IF SQLERRM LIKE '%(STATIA01.ANNOTATION_BUG_FK1)%'
    THEN RAISE_APPLICATION_ERROR(-20001, 'ISSUE with id ' || bugId || ' does not exist!');
    ELSIF SQLERRM LIKE '%(STATIA01.ANNOTATION_USERS_FK1)%'
    THEN RAISE_APPLICATION_ERROR(-20001, 'USER with id ' || userId || ' does not exist!');
    ELSE RAISE;
    END IF;
END;

/

/
--------------------------------------------------------
--  DDL for Function MKDIR
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "MKDIR" (userId IN ANNOTATION.U_ID%TYPE, bugId IN ANNOTATION.B_ID%TYPE, info IN ANNOTATION.INFO%TYPE)
            --dirId IN DIRS.ID%TYPE, dirName IN DIRS.NAME%TYPE)
RETURN ANNOTATION.ID%TYPE IS
    created_id ANNOTATION.ID%TYPE := -1;
  CURSOR CREATED (userId IN ANNOTATION.U_ID%TYPE, bugId IN ANNOTATION.B_ID%TYPE, info IN ANNOTATION.INFO%TYPE) IS
    SELECT ID
    FROM ANNOTATION
    WHERE B_ID=bugId AND U_ID=userId AND INFO=info;

  CURSOR ACTIVE_COUNT (userId IN ANNOTATION.U_ID%TYPE) IS
    SELECT ID
    FROM BUG
    WHERE STATUS='ACTIVE' AND U_ID=userId;

  activeCount BUG.ID%TYPE := 0;

  CURSOR BUG_STATUS (bugId IN ANNOTATION.B_ID%TYPE) IS
    SELECT STATUS
    FROM BUG
    WHERE ID=bugId;

  bugStatus BUG.STATUS%TYPE := '';

  fk_violation_exception EXCEPTION;
  PRAGMA exception_init (fk_violation_exception, -2291);
BEGIN
  OPEN ACTIVE_COUNT(userId);
  FETCH ACTIVE_COUNT INTO activeCount;
  CLOSE ACTIVE_COUNT;

  IF (activeCount != 1)
    THEN RAISE_APPLICATION_ERROR(-20001, 'USER ' || userId || ' has no/more than one active issue(s)!');
  END IF;

  OPEN BUG_STATUS(userId);
  FETCH BUG_STATUS INTO bugStatus;
  CLOSE BUG_STATUS;

  IF (bugStatus != 'ACTIVE')
    THEN RAISE_APPLICATION_ERROR(-20001, 'ISSUE ' || bugId || ' is not ACTIVE!');
  END IF;

  INSERT INTO ANNOTATION (B_ID, U_ID, INFO)
  VALUES (bugId, userId, info);

  OPEN CREATED(userId, bugId, info);
  FETCH CREATED INTO created_id;
  CLOSE CREATED;
  RETURN created_id;
EXCEPTION
  WHEN fk_violation_exception THEN
    IF SQLERRM LIKE '%(STATIA01.ANNOTATION_BUG_FK1)%'
    THEN RAISE_APPLICATION_ERROR(-20001, 'ISSUE with id ' || bugId || ' does not exist!');
    ELSE IF SQLERRM LIKE '%(STATIA01.ANNOTATION_USERS_FK1)%'
    THEN RAISE_APPLICATION_ERROR(-20001, 'USER with id ' || userId || ' does not exist!');
    ELSE RAISE;
    END IF;
END;

/

/
--------------------------------------------------------
--  DDL for Procedure FIND_CLOSED_ISSUES
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "FIND_CLOSED_ISSUES" (userId IN BUG.U_ID%TYPE)
IS
  CURSOR CLOSED (userId IN BUG.U_ID%TYPE) IS
    SELECT B.ID, B.NAME, B.U_ID
    FROM BUG B
    WHERE B.U_ID=userId AND B.STATUS='CLOSED';

  CURSOR ANNOTATIONS_BY_BUG (bugId IN BUG.ID%TYPE) IS
    SELECT A.ID, A.U_ID, A.INFO
    FROM ANNOTATION A
    WHERE A.B_ID=bugId;

  bugId BUG.ID%TYPE;
  bugName BUG.NAME%TYPE;
  bugOwner BUG.U_ID%TYPE;
  annotationId ANNOTATION.ID%TYPE;
  annotationUser ANNOTATION.U_ID%TYPE;
  annotationInfo ANNOTATION.INFO%TYPE;
BEGIN
  OPEN CLOSED(userId);
  LOOP
      FETCH CLOSED INTO bugId, bugName, bugOwner;
      EXIT WHEN CLOSED%NOTFOUND;
      dbms_output.put_line('BUG: ' || bugId || ', called: ' || bugName || ' was created by user: ' || bugOwner);
      OPEN ANNOTATIONS_BY_BUG(bugId);
      LOOP
          FETCH ANNOTATIONS_BY_BUG INTO annotationId, annotationUser, annotationInfo;
          EXIT WHEN ANNOTATIONS_BY_BUG%NOTFOUND;
          dbms_output.put_line('    ANNOTATION: ' || annotationId || ', with info: ' || annotationInfo || ' was created by user: ' || annotationUser);
      END LOOP;
    CLOSE CLOSED;
  END LOOP;
  CLOSE CLOSED;
END;

/

/

-- SOLUTIONS

SET SERVEROUTPUT ON SIZE 20000 FORMAT WORD_WRAPPED;

SELECT COUNT(*)
FROM BUG
WHERE STATUS='ACTIVE' AND U_ID=3;

CREATE OR REPLACE
FUNCTION CREATE_ANNOTATION (userId IN ANNOTATION.U_ID%TYPE, bugId IN ANNOTATION.B_ID%TYPE, info IN ANNOTATION.INFO%TYPE)
RETURN ANNOTATION.ID%TYPE IS
    created_id ANNOTATION.ID%TYPE := -1;
  CURSOR CREATED (userId IN ANNOTATION.U_ID%TYPE, bugId IN ANNOTATION.B_ID%TYPE, info IN ANNOTATION.INFO%TYPE) IS
    SELECT ID
    FROM ANNOTATION
    WHERE B_ID=bugId AND U_ID=userId AND INFO=info;

  CURSOR VALID_CHECK_SUPERUSER (userId IN ANNOTATION.U_ID%TYPE) IS
    SELECT TYPE
    FROM USERS
    WHERE ID=userId;

  userRole USERS.ID%TYPE := -1;

  CURSOR BUG_OWNER (bugId IN ANNOTATION.B_ID%TYPE) IS
    SELECT U_ID
    FROM bug
    WHERE ID=bugId;

  bugOwner BUG.U_ID%TYPE := -1;

  CURSOR ACTIVE_COUNT (userId IN ANNOTATION.U_ID%TYPE) IS
    SELECT ID
    FROM BUG
    WHERE STATUS='ACTIVE' AND U_ID=userId;

  activeCount BUG.ID%TYPE := 0;

  CURSOR BUG_STATUS (bugId IN ANNOTATION.B_ID%TYPE) IS
    SELECT STATUS
    FROM BUG
    WHERE ID=bugId;

  bugStatus BUG.STATUS%TYPE := '';

  fk_violation_exception EXCEPTION;
  PRAGMA exception_init (fk_violation_exception, -2291);
BEGIN
  OPEN VALID_CHECK_SUPERUSER(userId);
  FETCH VALID_CHECK_SUPERUSER INTO userRole;
  CLOSE VALID_CHECK_SUPERUSER;

  IF (userRole != 0) THEN
    OPEN BUG_OWNER(bugId);
    FETCH BUG_OWNER INTO bugOwner;
    CLOSE BUG_OWNER;
    IF (bugOwner != userId) THEN
      RAISE_APPLICATION_ERROR(-20001, 'USER ' || userId || ' is not authorized to modify this issue!');
    END IF;
  END IF;

  OPEN ACTIVE_COUNT(userId);
  FETCH ACTIVE_COUNT INTO activeCount;
  CLOSE ACTIVE_COUNT;

  IF (activeCount != 1 AND userRole != 0)
    THEN RAISE_APPLICATION_ERROR(-20001, 'USER ' || userId || ' has no/more than one active issue(s)!');
  END IF;

  OPEN BUG_STATUS(bugId);
  FETCH BUG_STATUS INTO bugStatus;
  CLOSE BUG_STATUS;

  IF (bugStatus != 'ACTIVE')
    THEN RAISE_APPLICATION_ERROR(-20001, 'ISSUE ' || bugId || ' is not ACTIVE!');
  END IF;

  INSERT INTO ANNOTATION (B_ID, U_ID, INFO)
  VALUES (bugId, userId, info);

  OPEN CREATED(userId, bugId, info);
  FETCH CREATED INTO created_id;
  CLOSE CREATED;
  RETURN created_id;
EXCEPTION
  WHEN fk_violation_exception THEN
    IF SQLERRM LIKE '%(STATIA01.ANNOTATION_BUG_FK1)%'
    THEN RAISE_APPLICATION_ERROR(-20001, 'ISSUE with id ' || bugId || ' does not exist!');
    ELSIF SQLERRM LIKE '%(STATIA01.ANNOTATION_USERS_FK1)%'
    THEN RAISE_APPLICATION_ERROR(-20001, 'USER with id ' || userId || ' does not exist!');
    ELSE RAISE;
    END IF;
END;

DECLARE
  newAnnotationId ANNOTATION.ID%TYPE;
BEGIN
  --newAnnotationId := CREATE_ANNOTATION (-21, 1, 'ANNOTATIONTEXT'); -- MISSING USERID
  --newAnnotationId := CREATE_ANNOTATION (3, -2, 'ANNOTATIONTEXT'); -- MISSING BUG
  --newAnnotationId := CREATE_ANNOTATION (3, 2, 'ANNOTATIONTEXT'); -- CLOSED BUG
  --newAnnotationId := CREATE_ANNOTATION (3, 1, 'ANNOTATIONTEXT2'); -- VALID
  --newAnnotationId := CREATE_ANNOTATION (2, 1, 'ADMIN_WAS_HERE'); -- VALID, AS 2 IS ADMIN
  newAnnotationId := CREATE_ANNOTATION (4, 1, 'NON_ADMIN_WAS_HERE'); -- SHOULD FAIL, AS 4 IS NOT ADMIN
  dbms_output.put_line(newAnnotationId);
END;

SELECT B.ID, B.U_ID, B.INFO
FROM ANNOTATION B
WHERE B.B_ID=1;

CREATE OR REPLACE
PROCEDURE FIND_CLOSED_ISSUES (userId IN BUG.U_ID%TYPE)
IS
  CURSOR CLOSED (userId IN BUG.U_ID%TYPE) IS
    SELECT B.ID, B.NAME, B.U_ID
    FROM BUG B
    WHERE B.U_ID=userId AND B.STATUS='CLOSED';

  CURSOR ANNOTATIONS_BY_BUG (bugId IN BUG.ID%TYPE) IS
    SELECT A.ID, A.U_ID, A.INFO
    FROM ANNOTATION A
    WHERE A.B_ID=bugId;

  bugId BUG.ID%TYPE;
  bugName BUG.NAME%TYPE;
  bugOwner BUG.U_ID%TYPE;
  annotationId ANNOTATION.ID%TYPE;
  annotationUser ANNOTATION.U_ID%TYPE;
  annotationInfo ANNOTATION.INFO%TYPE;
BEGIN
  OPEN CLOSED(userId);
  LOOP
      FETCH CLOSED INTO bugId, bugName, bugOwner;
      EXIT WHEN CLOSED%NOTFOUND;
      dbms_output.put_line('BUG: ' || bugId || ', called: ' || bugName || ' was created by user: ' || bugOwner);
      OPEN ANNOTATIONS_BY_BUG(bugId);
      LOOP
          FETCH ANNOTATIONS_BY_BUG INTO annotationId, annotationUser, annotationInfo;
          EXIT WHEN ANNOTATIONS_BY_BUG%NOTFOUND;
          dbms_output.put_line('    ANNOTATION: ' || annotationId || ', with info: ' || annotationInfo || ' was created by user: ' || annotationUser);
      END LOOP;
    CLOSE CLOSED;
  END LOOP;
  CLOSE CLOSED;
END;

BEGIN
  FIND_CLOSED_ISSUES(3);
END;