NORDIC Schema for TopLink Demonstration (4)

Having discovered the limitations of JDeveloper for support of object-oriented database objects in NORDIC Schema for TopLink Demonstration (3), we will clean up the NORDIC schema in both the offline and online databases, before exploring TopLink itself.

Clean Up Offline Database

Select the following objects (types and views) in the NORDIC schema of the offline database called Nordic_database (CTRL + left-click):

• ANIMAL
• ANIMAL_STATEFLOW
• BUILDING
• GIRL
• GUY
• HORSE
• HUMAN
• HUMAN_STATEFLOW
• SURREY
• SURREY_STATEFLOW
• BUILDINGS
• SURREYS

Right-click on the selection to bring up the context menu. Select Erase from disk.... Confirm and all is done.

Clean Up Online Database

Use the SQL icon in the JDeveloper toolbar to connect to XE_Nordic. Copy and paste the following commands into the SQL window:

DROP VIEW buildings;
DROP VIEW surreys;
DROP TYPE surrey;
DROP TYPE surrey_stateflow;
DROP TYPE building;
DROP TYPE girl;
DROP TYPE guy;
DROP TYPE human;
DROP TYPE horse;
DROP TYPE animal;
DROP TYPE human_stateflow;
DROP TYPE animal_stateflow;
PURGE RECYCLEBIN;

Everything will work, but we get the following error message in the Log window when we use the script option to execute the commands:

1/03/2009 19:23:53 oracle.dbtools.raptor.scriptrunner.ScriptRunner
WARNING: oracle.dbtools.raptor.scriptrunner.SQL.reportThreeWord(SQL.java:356)
java.lang.ArrayIndexOutOfBoundsException: 2
    at oracle.dbtools.raptor.scriptrunner.SQL.reportThreeWord(SQL.java:356)
    at oracle.dbtools.raptor.scriptrunner.SQL.run(SQL.java:59)
    at oracle.dbtools.raptor.scriptrunner.ScriptRunner.run(ScriptRunner.java:170)
    at oracle.dbtools.raptor.scriptrunner.ScriptRunner.run(ScriptRunner.java:292)

Change ANIMAL_SUBTYPE Table

Instead of recording the type hierarchy using Oracle Database Types, this information has to be recorded in the ANIMAL_SUBTYPE table. Run the following SQL to add an extra column:

ALTER TABLE animal_subtype
  ADD (
    supertype_id
    NUMBER
    DEFAULT 1
    CONSTRAINT animal_subtype_supertype_id_nn
        NOT NULL
    CONSTRAINT animal_subtype_supertype_id_fk
        REFERENCES animal_subtype( type_id )
  )
;
UPDATE animal_subtype SET supertype_id = 3 WHERE type_id IN ( 4, 5 );
COMMIT;

Validate the type hierarchy by using a modified version of the query from Nested Sets in Oracle (3) with the addition of the NOCYCLE keyword because the TYPE_ID and SUPERTYPE_ID are equal for the root node.:

SELECT
    LPAD( name, (LEVEL-1)*2 + LENGTH( name ), '. ' )
      AS name
  FROM
    animal_subtype
  START WITH
    type_id = supertype_id
  CONNECT BY NOCYCLE
    supertype_id = PRIOR type_id
;

The output is:

NAME
---------
ANIMAL
. HORSE
. HUMAN
. . GUY
. . GIRL

Trigger for ANIMAL_BASE

A preliminary version of the trigger on the ANIMAL_BASE is:

CREATE OR REPLACE TRIGGER animal_base_pre
  BEFORE INSERT OR UPDATE ON animal_base FOR EACH ROW
  DECLARE
    l_valid_type_id
    animal_base.type_id%TYPE;
  BEGIN
    /* Ensure that the subtype can be instantiated. */
    BEGIN
    SELECT type_id
        INTO l_valid_type_id
        FROM animal_subtype
        WHERE type_id=:new.type_id
          AND instantiable = 'Y';
    EXCEPTION
      WHEN NO_DATA_FOUND
        THEN RAISE_APPLICATION_ERROR(
          -20000,
          "Animal sub-type (" || :new.type_id ||
          ") is either invalid or not instantiable",
          FALSE
        );
    END;
    /* The human_state subtype (#2) is only valid for human subtypes (#4 and #5).
       All other subtypes are to use animal_state (#1) */
    IF ( :new.state_type_id = DECODE( :new.type_id, 4, 2, 5, 2, 1) ) THEN
    ELSE
      RAISE_APPLICATION_ERROR(
          -20001,
          "Animal sub-type (" || :new.type_id ||
          ") is not compatible with the animal_state sub-type (" ||
          :new.state_type_id || ")",
          FALSE
        );
    END IF;
END animal_base_pre;

Tags: JDeveloper, Nordic OODD, TopLink, XE Database