--
-- File Name: obsexample.sql
--
-- Object-Relational Example
--
-- This scripts create all necessary tables, 
-- constraints and sequences for the Scientific 
-- Observation example.



DROP TYPE PRODUCT_TYPE;
DROP TABLE PASSED_OBSERVATION_LIST;
DROP TYPE PASSEDOBSARRAY;
DROP TYPE PASSEDOBS;
DROP TABLE OCEANIC_OBSERVATION_LIST;
DROP TYPE OCEANIC_OBSERVATION_TYPE;
DROP TYPE OCEANIC_OBSERVATION;
DROP TABLE QC_EVENT_LIST;
DROP TYPE QUALITY_CONTROL_EVENT;
DROP TABLE ATMOSEVENT_LIST;
DROP TYPE ATMOSEVENT;
DROP TABLE SCIENTIST_LIST
DROP TYPE SCIENTIST;
DROP TABLE PLATFORM_TYPE_LIST;
DROP TYPE PLATFORM_TYPE;


CREATE TYPE PLATFORM_TYPE AS OBJECT(
key_id	NUMBER(8),
type		VARCHAR2(50),
description  VARCHAR2(50));



CREATE TABLE PLATFORM_TYPE_LIST OF PLATFORM_TYPE;


CREATE TYPE SCIENTIST AS OBJECT(
usr_id	NUMBER(6),
lastname	VARCHAR2(20),
firstname	VARCHAR2(20),
platform_id  NUMBER,
for_platform REF PLATFORM_TYPE);


CREATE TABLE SCIENTIST_LIST OF SCIENTIST;


CREATE TYPE ATMOSEVENT AS OBJECT(
key_id		NUMBER(8),
when_t		DATE,
name       	VARCHAR2(30),
type		VARCHAR2(20),
refkey		NUMBER(8),
transformed_to	REF atmosevent);


CREATE TABLE ATMOSEVENT_LIST OF ATMOSEVENT;


CREATE TYPE OCEANIC_OBSERVATION AS OBJECT(
latitude_deg	NUMBER(10,4),
longitude_deg	NUMBER(10,4),
windspeed_mps	NUMBER(10,4),
adj_windspeed_mps NUMBER(10,4),
wind_direction_deg NUMBER(6),
pressure_mb  NUMBER(6));


CREATE TYPE OCEANIC_OBSERVATION_TYPE AS OBJECT(
obs_id       NUMBER(8),
when_t       DATE,
at_time      CHAR(8),
station_id   NUMBER(6),
produced_id  NUMBER(8),
produced_by  REF PLATFORM_TYPE,
obsobj       OCEANIC_OBSERVATION);


-- List of all oceanic observations by date, 
-- time, and platform type


CREATE TABLE OCEANIC_OBSERVATION_LIST 
   OF OCEANIC_OBSERVATION_TYPE;


-- use qc_id_seq to update QUALITY_CONTROL_EVENT qc_id


CREATE TYPE QUALITY_CONTROL_EVENT AS OBJECT(
qc_id     NUMBER(8),
when_t    DATE,
at_time   CHAR(8),
event_id	NUMBER(8),
for_event	REF atmosevent,
whom_id  NUMBER(6),
by_whom  REF scientist);


CREATE TABLE QC_EVENT_LIST OF QUALITY_CONTROL_EVENT;


CREATE OR REPLACE TYPE PASSEDOBS AS OBJECT(
obsid    NUMBER(8),
when_t   DATE);


CREATE TYPE PASSEDOBSARRAY AS TABLE OF PASSEDOBS;


CREATE TABLE PASSED_OBSERVATION_LIST(
qcid     NUMBER(8),
when_t   DATE,
at_time  CHAR(8),
idobj    passedObsArray)
NESTED TABLE idobj STORE AS pobsid_list;


ALTER TABLE POBSID_LIST
  STORAGE (MINEXTENTS 1 MAXEXTENTS 20);


-- Now create the constraints



ALTER TABLE PLATFORM_TYPE_LIST
  ADD CONSTRAINT PT_KEY_ID_PK PRIMARY KEY(KEY_ID);
 

ALTER TABLE SCIENTIST_LIST 
  ADD CONSTRAINT SL_USR_ID_PK PRIMARY KEY(USR_ID);
  

ALTER TABLE ATMOSEVENT_LIST
  ADD CONSTRAINT AL_KEY_ID_PK PRIMARY KEY(KEY_ID);
  

ALTER TABLE OCEANIC_OBSERVATION_LIST
  ADD CONSTRAINT O_OBS_ID_PK PRIMARY KEY(OBS_ID);
  

ALTER TABLE QC_EVENT_LIST
  ADD CONSTRAINT QC_ID_PK PRIMARY KEY(QC_ID);
  

ALTER TABLE QC_EVENT_LIST
  ADD CONSTRAINT qc_whom_id_fk
  FOREIGN KEY(whom_id)
  REFERENCES SCIENTIST_LIST(usr_id)
  ON DELETE CASCADE;


ALTER TABLE PASSED_OBSERVATION_LIST
  ADD Constraint po_qc_id_fk
  FOREIGN KEY(qcid)
  REFERENCES QC_EVENT_LIST(qc_id)
  ON DELETE CASCADE;


ALTER TABLE PASSED_OBSERVATION_LIST
  ADD CONSTRAINT passed_qcid_ukey UNIQUE(qcid);

 
ALTER TABLE PASSED_OBSERVATION_LIST
  MODIFY (qcid NOT NULL);

 
-- Now create the sequences

-- key_id sequence for PLATFORM_TYPE
DROP SEQUENCE PT_key_SEQ;
CREATE SEQUENCE PT_key_SEQ;
  


-- usr_id sequence for SCIENTIST
DROP SEQUENCE USERSEQ;
CREATE SEQUENCE USERSEQ; 
  



-- key_id sequence for ATMOSEVENT
DROP SEQUENCE atm_key_seq;
CREATE SEQUENCE atm_key_seq;

  

DROP SEQUENCE OBSID_SEQ;
CREATE SEQUENCE OBSID_SEQ;
  


-- qc_id sequence for QUALITY_CONTROL_EVENT
DROP SEQUENCE qc_id_seq;
CREATE SEQUENCE qc_id_seq;

 
-- passed_id sequence for PASSED_OBSERVATION
DROP SEQUENCE passed_id_seq;
CREATE SEQUENCE passed_id_seq;
  


-- Now create the PL/SQL package


DROP PACKAGE OBSACTIONS;
CREATE OR REPLACE PACKAGE OBSACTIONS AS
--  Get a new Obs_id
    FUNCTION getObsId RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES(getObsId,WNDS,WNPS,RNPS);

--  Add a new Observation
    PROCEDURE insertObs(p_newobsid	IN NUMBER,
                        p_whent		IN VARCHAR2,
                        p_attime		IN CHAR,
                        p_ptlid		IN NUMBER,
                        p_latdeg		IN NUMBER,
                        p_londeg		IN NUMBER,
                        p_wsmps		IN NUMBER,
                        p_adjwsmps	IN NUMBER,
                        p_wddeg		IN NUMBER,
                        p_pmb		IN NUMBER,
                        p_stlid		IN NUMBER);

END OBSACTIONS;
/

CREATE OR REPLACE PACKAGE BODY OBSACTIONS AS
--  Use the private FindCurrTime function to
--  find current time. This method is accessible
--  only to the functions and procedures specified
--  in the body section of the OBSACTIONS package.
--  It's analogous to a Java private method.

    FUNCTION FindCurrTime RETURN CHAR IS
    v_time  CHAR(8);
    BEGIN
         SELECT TO_CHAR(SYSDATE,'HH24MISS') INTO v_time FROM DUAL;
         RETURN v_time;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
            RETURN v_time;
    END FindCurrTime;

--  Get a new Obs_id

    FUNCTION getObsId RETURN NUMBER IS
      p_obsid NUMBER;
    BEGIN
         SELECT OBSID_SEQ.NEXTVAL INTO p_obsid FROM DUAL;
         RETURN p_obsid;
    EXCEPTION
         WHEN OTHERS THEN
              RETURN p_obsid;
    End getObsId;

--  Create an object of TYPE Oceanic_observation
--  This is a private method.

    FUNCTION OceanicObservation (p_latdeg	IN NUMBER,
                             p_londeg	IN NUMBER,
                             p_wsmps	IN NUMBER,
                             p_adjwsmps IN NUMBER,
                             p_wddeg	IN NUMBER,
                             p_pmb	IN NUMBER) 
                                  RETURN oceanic_observation IS
--     Create an oceanic_observation type.
       v_obs  oceanic_observation := 
           oceanic_observation(p_latdeg,p_londeg,
               p_wsmps,p_adjwsmps,p_wddeg,p_pmb);
    BEGIN
         RETURN v_obs;
    END OceanicObservation;

--  Add a new Observation
    PROCEDURE insertObs(p_newobsid	IN NUMBER,
                        p_whent		IN VARCHAR2,
                        p_attime		IN CHAR,
                        p_ptlid		IN NUMBER,
                        p_latdeg		IN NUMBER,
                        p_londeg		IN NUMBER,
                        p_wsmps		IN NUMBER,
                        p_adjwsmps	IN NUMBER,
                        p_wddeg		IN NUMBER,
                        p_pmb		IN NUMBER,
                        p_stlid		IN NUMBER) IS

      v_date DATE := TO_DATE(p_whent,'DD-MON-YYYY');
      v_time  CHAR(8) := p_attime;
      v_newobsid  NUMBER := TO_NUMBER(p_newobsid);
      v_platformref  REF PLATFORM_TYPE;

      v_obs Oceanic_Observation := 
        Oceanic_Observation(p_latdeg,p_londeg,
          p_wsmps,p_adjwsmps,p_wddeg,p_pmb);
    BEGIN
--	Check input date p_whent
       IF  v_date IS NULL THEN
           SELECT SYSDATE INTO v_date FROM DUAL;
       END IF;

--	Check input time
      IF  v_time IS NULL THEN
          v_time := FindCurrTime;
      END IF;

-- 	Get platform_type REF
      SELECT REF(P) INTO v_platformref
          FROM platform_type_list P
          WHERE P.key_id = p_ptlid;

--	Insert new object
      INSERT INTO OCEANIC_OBSERVATION_LIST
        VALUES(
               v_newobsid, v_date, v_time, p_stlid, p_ptlid,
               v_platformref, v_obs
              );
     -- commit;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
          NULL;
     WHEN OTHERS THEN
          NULL;
   END insertObs;

END OBSACTIONS;
/

DROP PACKAGE QCACTIONS;
CREATE OR REPLACE PACKAGE QCACTIONS AS
--  Get a new qc_id
    FUNCTION GETNEWQCID RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES(GETNEWQCID,WNDS,WNPS,RNPS);
--  Add a new QcSet
    PROCEDURE insertQcSet(p_newqcid	IN NUMBER,
                          p_whent		IN VARCHAR2,
                          p_attime	IN CHAR,
                          p_evtid		IN NUMBER,
                          p_whom_id	IN NUMBER);
--  Add a new row in the PASSED_OBSERVATION_LIST table
    PROCEDURE insertQcSetObs(p_qcid	 IN NUMBER,
                             p_obsid	 IN NUMBER);
END QCACTIONS;
/

CREATE OR REPLACE PACKAGE BODY QCACTIONS AS
--  Find current time
    FUNCTION FindCurrTime RETURN CHAR IS
      v_time  CHAR(8);
    BEGIN
         SELECT TO_CHAR(SYSDATE,'HH24MISS') 
                  INTO v_time FROM DUAL;
         RETURN v_time;
    EXCEPTION
         WHEN NO_DATA_FOUND THEN
            RETURN v_time;
    END FindCurrTime;

FUNCTION GETNEWQCID RETURN NUMBER IS
      p_qcid NUMBER;
   BEGIN
        SELECT qc_id_seq.NEXTVAL INTO p_qcid FROM DUAL;
        RETURN p_qcid;
   EXCEPTION
     WHEN OTHERS THEN
        RETURN p_qcid;
   End GETNEWQCID;

PROCEDURE insertQcSet(p_newqcid	IN NUMBER,
        p_whent	 IN VARCHAR2, p_attime IN CHAR,
        p_evtid	 IN NUMBER, p_whom_id IN NUMBER) IS

    v_date DATE := TO_DATE(p_whent,'DD-MON-YYYY');
    v_time  CHAR(8) := p_attime;
    v_newqcid  NUMBER := TO_NUMBER(p_newqcid);
    v_eventref  REF atmosevent;
    v_scientistref  REF scientist;

  BEGIN
--    Check input date p_whent
      IF  v_date IS NULL THEN
          SELECT SYSDATE INTO v_date FROM DUAL;
      END IF;
--    Check input time
      IF  v_time IS NULL THEN
          v_time := FindCurrTime;
      END IF;
--    Get atmosevent REF
      SELECT REF(P) INTO v_eventref
          FROM ATMOSEVENT_LIST P
          WHERE P.key_id = p_evtid;
--    Get scientist REF
      SELECT REF(P) INTO v_scientistref
         FROM SCIENTIST_LIST P
         WHERE P.usr_id = p_whom_id;
--    Insert new row
      INSERT INTO QC_EVENT_LIST
        VALUES(p_newqcid,v_date,v_time,p_evtid,v_eventref,
               p_whom_id,v_scientistref);
   EXCEPTION
      WHEN OTHERS THEN
         RAISE NO_DATA_FOUND;
   END insertQcSet;
PROCEDURE insertQcSetObs(p_qcid IN NUMBER,p_obsid IN NUMBER) IS
    v_qcdate DATE;
    v_time  CHAR(8);
    v_qcid  NUMBER := TO_NUMBER(p_qcid);
    v_obsdate DATE;
    v_obsid  NUMBER := TO_NUMBER(p_obsid);
    v_cnt   NUMBER;
    BEGIN
--       Check p_qcid
         IF  v_qcid IS NULL THEN
             RAISE NO_DATA_FOUND;
         END IF;
--       Check v_obsid
         IF  v_obsid IS NULL THEN
             RAISE NO_DATA_FOUND;
         END IF;
--       Check QcSet exists
         SELECT Q.when_t, q.at_time INTO v_qcdate, v_time
            FROM QC_EVENT_LIST Q
            WHERE Q.qc_id = v_qcid;
--       Check Obsid exists
         SELECT O.when_t INTO v_obsdate
           FROM OCEANIC_OBSERVATION_LIST O
           WHERE O.obs_id = v_obsid;
         v_cnt := 0;
--       Check PASSED_OBSERVATION_LIST exists
         SELECT COUNT(1) INTO v_cnt
            FROM PASSED_OBSERVATION_LIST P
            WHERE P.qcid = v_qcid;
         IF  ( v_cnt = 0 ) THEN
              INSERT INTO PASSED_OBSERVATION_LIST
                VALUES(v_qcid,v_qcdate,v_time,PASSEDOBSARRAY());
         END IF;
         INSERT INTO TABLE
             (SELECT P.idobj FROM passed_observation_list P
                WHERE P.qcid = v_qcid)
            VALUES(PASSEDOBS(v_obsid,v_obsdate));
   EXCEPTION
      WHEN OTHERS THEN
         RAISE NO_DATA_FOUND;
   END insertQcSetObs;

END QCACTIONS;
/