Nordic (8)


This series is a commentary of sorts on a PowerPoint™ presentation called Nordic – Object Relational Database Design. The previous posts are at

We have completed a physical design that includes the association class, COURTSHIPS. Now we move onto slide #26 of the presentation.

Navigate Class Hierarchy

To replicate the query in the slide, we will use the USER_TYPES data dictionary view in conjunction with the CONNECT BY condition. For further discussion of these hierarchical queries, please see Nested Sets in Oracle (1) and the subsequent articles.

SELECT
    type_name,
    LEVEL
  FROM
    user_types
  START WITH
    type_name = 'HUMAN'
  CONNECT BY
    type_name = PRIOR supertype_name
/

The result is:

TYPE_NAME                           LEVEL
------------------------------ ----------
HUMAN                                   1
ANIMAL                                  2

As for slide #27, we modify our query slightly to become:

SELECT
    type_name,
    LEVEL
  FROM
    user_types
  START WITH
    type_name = 'HUMAN'
  CONNECT BY
    supertype_name = PRIOR type_name
/

Which produces:

TYPE_NAME                           LEVEL
------------------------------ ----------
HUMAN                                   1
GIRL                                    2
GUY                                     2

This is the correct answer as we have included the GIRL type in our type hierarchy.

Class State Inheritance

Slide #29 presents a challenge. Object state appears to be object itself with inheritance and polymorphism enabled.

Slide #30 confirms this supposition because the Nordic model implements classes as objects in its own space.

We are going to implement this as a type hierarchy itself with constructor functions enforcing valid values at the time of creation.

Definition of ANIMAL_STATE type

The CREATE TYPE and CREATE TYPE BODY commands are used to create the definition for the type and its implementation. The RAISE_APPLICATION_ERROR procedure is used to send a meaningful message back to the user. The SQL*Plus command SHOW ERRORS displays any errors in the previous command.

-- Type definition for ANIMAL_STATE
CREATE OR REPLACE TYPE animal_state AS OBJECT (
  current_state VARCHAR2(30),
  CONSTRUCTOR FUNCTION animal_state(
    current_state VARCHAR2
  ) RETURN SELF AS RESULT
)
NOT FINAL;
/
SHOW ERRORS
-- Type implementation for ANIMAL_STATE
CREATE OR REPLACE TYPE BODY animal_state IS
  CONSTRUCTOR FUNCTION animal_state(
    current_state VARCHAR2
  ) RETURN SELF AS RESULT AS
  BEGIN
    IF current_state IN ( 'Preborn', 'Healthy', 'Ill', 'Deceased' ) THEN
      self.current_state := current_state;
    ELSE
      RAISE_APPLICATION_ERROR( -20001, current_state || ' is not a valid animal state', FALSE );
    END IF;
    RETURN;
  END animal_state;
END;
/
SHOW ERRORS

Testing the ANIMAL_STATE type

These complete without errors. Let’s test them.

DECLARE
  l_state animal_state := NULL;
BEGIN
  l_state := NEW animal_state( 'Fred' );
END;
/

This fails with:

DECLARE
*
ERROR at line 1:
ORA-20001: Fred is not a valid animal state
ORA-06512: at "ORDBMS.ANIMAL_STATE", line 15
ORA-06512: at line 4

This is good because bad data cannot be created. Can we bypass this checking?

DECLARE
  s animal_state := NULL;
BEGIN
  s := NEW animal_state( 'Ill' );
  s.current_state := 'Fred';
END;
/

This returns:

anonymous block completed

Bad news! The problem is that all attributes in Oracle types are public. As this example shows, we cannot implement a state machine enforcing the object state transitions to conform to some model. This is a similar issue to that of the COURTSHIP type discussed in earlier articles.

Class State Inheritance

Let’s create a subtype of ANIMAL_STATE for humans (by including two (2) extra states):

-- Type definition for HUMAN_STATE
CREATE OR REPLACE TYPE human_state UNDER animal_state (
  FINAL CONSTRUCTOR FUNCTION human_state(
    current_state VARCHAR2
  ) RETURN SELF AS RESULT
)
FINAL;
/
SHOW ERRORS
-- Type implementation for ANIMAL_STATE
CREATE OR REPLACE TYPE BODY human_state IS
  CONSTRUCTOR FUNCTION human_state(
    current_state VARCHAR2
  ) RETURN SELF AS RESULT AS
  BEGIN
    IF current_state IN ( 'Preborn', 'Healthy', 'Ill', 'Deceased', 'In Love', 'Married' ) THEN
      self.current_state := current_state;
    ELSE
      RAISE_APPLICATION_ERROR( -20002, current_state || ' is not a valid human state', FALSE );
    END IF;
    RETURN;
  END human_state;
END;
/
SHOW ERRORS

Updating the ANIMAL Type

Because we have already released a version of the type into the wild and there are dependent objects, we need to use the ALTER TYPE command with the CASCADE option:

ALTER TYPE animal
  ADD ATTRIBUTE state animal_state
  CASCADE
;

Updating the ANIMALS Table

After this command, there is a new column called STATE that is set to NULL. To give realistic values, we need to use the UPDATE command. The IS OF TYPE condition was examined in more detail in Nordic (3).

UPDATE animals ptr
  SET state = NEW animal_state( 'Healthy' )
  WHERE VALUE(ptr) IS OF TYPE( horse )
/
UPDATE animals ptr
  SET state = NEW human_state( 'Healthy' )
  WHERE VALUE(ptr) IS OF TYPE( human )
/
COMMIT;

Note that the subtype, HUMAN_STATE, was used instead of the base type for all humans. At this stage of the physical design, there is nothing to stop us from using that subtype for all animals.

Let’s check the data:

SQL> select value(p) from animals p;

VALUE(P)(NAME, STATE(CURRENT_STATE))
----------------------------------------
HORSE('Mabel', ANIMAL_STATE('Healthy'))
GIRL('Mary Lou', HUMAN_STATE('Healthy'))
GUY('Jim Bob', HUMAN_STATE('Healthy'))
GUY('Billy Joe', HUMAN_STATE('Healthy'))

Problems with ANIMALS Table

The known problems are:

  1. The STATE column should be NOT NULL
  2. The STATE column should be of type, HUMAN_TYPE, for humans only.
  3. The state transitions are not enforced for the STATE column as per slide #31.

The problem with invalid states seems to be fixed by the requirement that only objects can be inserted or updated for the STATE column. The type constructor enforces the valid values.

NOT NULL Constraint

This is easily done through the ALTER TABLE command:

ALTER TABLE animals
  ADD CONSTRAINT animals_state_nn
    CHECK( state IS NOT NULL )
/

This is verified via:

SQL> INSERT INTO animals (name) VALUES( 'Fred' );
INSERT INTO animals (name) VALUES( 'Fred' )
*
ERROR at line 1:
ORA-02290: check constraint (ORDBMS.ANIMALS_STATE_NN) violated

Type Consistency and State Transitons

The next step in the physical design is to try triggers to maintain consistency between the subtypes, as well as state transistion. We will use the CREATE TRIGGER command to accomplish this.

BEFORE INSERT OR UPDATE Trigger on Animals

We will use the OBJECT VALUE pseudo-column in the type tesing. Otherwise, the PLS-00049: bad bind variable 'NEW' error will appear when the :NEW variable is used in the conditional expression.

In PL/SQL, the TYPE keyword is dropped from the IS OF TYPE condition.

We are using the searched CASE expression to test conditions in sequence.

CREATE OR REPLACE TRIGGER animals_pre
  BEFORE INSERT OR UPDATE
  ON animals
  FOR EACH ROW
  BEGIN
    IF :new.object_value IS OF ( human ) THEN
      IF :new.state IS OF ( human_state ) THEN
        NULL;
      ELSE
        RAISE_APPLICATION_ERROR( -20003, 'Humans must have a human_state', FALSE );
      END IF;
    ELSE
      IF :new.state IS OF ( human_state ) THEN
        RAISE_APPLICATION_ERROR( -20004, 'Non-humans must have an animal_state', FALSE );
      ELSE
        NULL;
      END IF;
    END IF;
    CASE
      WHEN inserting
        THEN NULL;
      WHEN :new.state.current_state = :old.state.current_state
        THEN NULL;
      WHEN :new.state.current_state = 'Preborn'
        THEN RAISE_APPLICATION_ERROR( -20005, 'Cannot change state to Preborn', FALSE );
      WHEN :old.state.current_state = 'Deceased'
        THEN RAISE_APPLICATION_ERROR( -20006, 'Cannot change state from Deceased', FALSE );
      WHEN :new.state.current_state = 'Married' AND
           :old.state.current_state  'In Love'
        THEN RAISE_APPLICATION_ERROR( -20007, 'Cannot get married without being in-love', FALSE );
      ELSE NULL;
    END CASE;
  END animals_pre;
/
SHOW ERRORS

Test the Trigger

-- This should work
INSERT INTO animals VALUES( 'Shrek', NEW animal_state( 'Healthy' ) );
-- This should fail with ORA-20004
INSERT INTO animals VALUES( 'Fred', NEW human_state( 'In Love' ) );
-- This should fail with ORA-20003
INSERT INTO animals(OBJECT_VALUE) VALUES( NEW guy( 'Fred', NEW animal_state( 'Preborn' ) ) );
-- This should work
INSERT INTO animals(OBJECT_VALUE) VALUES( NEW guy( 'Fred', NEW human_state( 'Preborn' ) ) );
-- This should work
INSERT INTO animals(OBJECT_VALUE) VALUES( NEW girl( 'Fiona', NEW human_state( 'In Love' ) ) );
-- This should fail with ORA-20003
UPDATE animals SET OBJECT_VALUE = NEW guy( name, state ) WHERE name = 'Shrek';
-- This should fail with ORA-20004
UPDATE animals SET OBJECT_VALUE = NEW horse( name, state ) WHERE name = 'Billy Joe';
-- This should work
UPDATE animals ptr
  SET OBJECT_VALUE = NEW guy( ptr.name, NEW human_state( ptr.state.current_state ) )
  WHERE name = 'Shrek';
-- This should work
UPDATE animals SET state = NEW human_state( 'Ill' ) WHERE name = 'Fred';
-- This should fail with ORA-20005
UPDATE animals SET state = NEW human_state( 'Preborn' ) WHERE name = 'Fred';
-- This should work
UPDATE animals SET state = NEW human_state( 'Deceased' ) WHERE name = 'Fred';
-- This should fail with ORA-20006
UPDATE animals SET state = NEW human_state( 'Healthy' ) WHERE name = 'Fred';
-- This should work
UPDATE animals SET state = NEW human_state( 'Married' ) WHERE name = 'Fiona';
-- This should fail with ORA-20007
UPDATE animals SET state = NEW human_state( 'Married' ) WHERE name = 'Mary Lou';
-- Check the data
SELECT object_value FROM animals;
-- Undo all of these changes
ROLLBACK;

Sample Test Run

SQL> -- This should work
SQL> INSERT INTO animals VALUES( 'Shrek', NEW animal_state( 'Healthy' ) );

1 row created.

SQL> -- This should fail with ORA-20004
SQL> INSERT INTO animals VALUES( 'Fred', NEW human_state( 'In Love' ) );
INSERT INTO animals VALUES( 'Fred', NEW human_state( 'In Love' ) )
                                        *
ERROR at line 1:
ORA-20004: Non-humans must have an animal_state
ORA-06512: at "ORDBMS.ANIMALS_PRE", line 10
ORA-04088: error during execution of trigger 'ORDBMS.ANIMALS_PRE'


SQL> -- This should fail with ORA-20003
SQL> INSERT INTO animals(OBJECT_VALUE) VALUES( NEW guy( 'Fred', NEW animal_state( 'Preborn' ) ) );
INSERT INTO animals(OBJECT_VALUE) VALUES( NEW guy( 'Fred', NEW animal_state( 'Preborn' ) ) )
                                                               *
ERROR at line 1:
ORA-20003: Humans must have a human_state
ORA-06512: at "ORDBMS.ANIMALS_PRE", line 6
ORA-04088: error during execution of trigger 'ORDBMS.ANIMALS_PRE'


SQL> -- This should work
SQL> INSERT INTO animals(OBJECT_VALUE) VALUES( NEW guy( 'Fred', NEW human_state( 'Preborn' ) ) );

1 row created.

SQL> -- This should work
SQL> INSERT INTO animals(OBJECT_VALUE) VALUES( NEW girl( 'Fiona', NEW human_state( 'In Love' ) ) );

1 row created.

SQL> -- This should fail with ORA-20003
SQL> UPDATE animals SET OBJECT_VALUE = NEW guy( name, state ) WHERE name = 'Shrek';
UPDATE animals SET OBJECT_VALUE = NEW guy( name, state ) WHERE name = 'Shrek'
 *
ERROR at line 1:
ORA-20003: Humans must have a human_state
ORA-06512: at "ORDBMS.ANIMALS_PRE", line 6
ORA-04088: error during execution of trigger 'ORDBMS.ANIMALS_PRE'


SQL> -- This should fail with ORA-20004
SQL> UPDATE animals SET OBJECT_VALUE = NEW horse( name, state ) WHERE name = 'Billy Joe';
UPDATE animals SET OBJECT_VALUE = NEW horse( name, state ) WHERE name = 'Billy Joe'
 *
ERROR at line 1:
ORA-20004: Non-humans must have an animal_state
ORA-06512: at "ORDBMS.ANIMALS_PRE", line 10
ORA-04088: error during execution of trigger 'ORDBMS.ANIMALS_PRE'


SQL> -- This should work
SQL> UPDATE animals ptr
  2    SET OBJECT_VALUE = NEW guy( ptr.name, NEW human_state( ptr.state.current_state ) )
  3    WHERE name = 'Shrek';

1 row updated.

SQL> -- This should work
SQL> UPDATE animals SET state = NEW human_state( 'Ill' ) WHERE name = 'Fred';

1 row updated.

SQL> -- This should fail with ORA-20005
SQL> UPDATE animals SET state = NEW human_state( 'Preborn' ) WHERE name = 'Fred';
UPDATE animals SET state = NEW human_state( 'Preborn' ) WHERE name = 'Fred'
 *
ERROR at line 1:
ORA-20005: Cannot change state to Preborn
ORA-06512: at "ORDBMS.ANIMALS_PRE", line 21
ORA-04088: error during execution of trigger 'ORDBMS.ANIMALS_PRE'


SQL> -- This should work
SQL> UPDATE animals SET state = NEW human_state( 'Deceased' ) WHERE name = 'Fred';

1 row updated.

SQL> -- This should fail with ORA-20006
SQL> UPDATE animals SET state = NEW human_state( 'Healthy' ) WHERE name = 'Fred';
UPDATE animals SET state = NEW human_state( 'Healthy' ) WHERE name = 'Fred'
 *
ERROR at line 1:
ORA-20006: Cannot change state from Deceased
ORA-06512: at "ORDBMS.ANIMALS_PRE", line 23
ORA-04088: error during execution of trigger 'ORDBMS.ANIMALS_PRE'


SQL> -- This should work
SQL> UPDATE animals SET state = NEW human_state( 'Married' ) WHERE name = 'Fiona';

1 row updated.

SQL> -- This should fail with ORA-20007
SQL> UPDATE animals SET state = NEW human_state( 'Married' ) WHERE name = 'Mary Lou';
UPDATE animals SET state = NEW human_state( 'Married' ) WHERE name = 'Mary Lou'
 *
ERROR at line 1:
ORA-20007: Cannot get married without being in-love
ORA-06512: at "ORDBMS.ANIMALS_PRE", line 26
ORA-04088: error during execution of trigger 'ORDBMS.ANIMALS_PRE'


SQL> -- Check the data
SQL> SELECT object_value FROM animals;

OBJECT_VALUE(NAME, STATE(CURRENT_STATE))
--------------------------------------------------------------------------------
HORSE('Mabel', ANIMAL_STATE('Healthy'))
GIRL('Mary Lou', HUMAN_STATE('Healthy'))
GUY('Jim Bob', HUMAN_STATE('Healthy'))
GUY('Billy Joe', HUMAN_STATE('Healthy'))
GUY('Shrek', HUMAN_STATE('Healthy'))
GUY('Fred', HUMAN_STATE('Deceased'))
GIRL('Fiona', HUMAN_STATE('Married'))

7 rows selected.

SQL> -- Undo all of these changes
SQL> ROLLBACK;

Rollback complete.

Conclusion

The state transition turned out easier than I expected. The trigger is fairly straight-forward. There is none of the complexity that was involved in the association class.

Advertisements

One thought on “Nordic (8)

  1. Nordic (10) « Yet Another OCM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s