Car-Racing Validation Directive Examples

These examples show how validation directives can enforce rules on JSON documents exposed by duality views. They cover checks on proposed documents, checks against related relational data, comparisons of old and new document values, read-time validation, and commit-time validation.

These examples use the team, driver, race, and driver_race_map tables. For descriptions of the tables and SQL definitions of the duality views, see Car-Racing Example, Tables and Creating Car-Racing Duality Views Using SQL. The examples that reference $.teamId use the driver_dv definition in which teamId is a top-level field.

Validation Examples

The following examples use a car-racing championship schema. A race has results, each result identifies a driver, and each driver belongs to a team. The examples assign points as follows: positions 1 through 10 earn 25, 18, 15, 12, 10, 8, 6, 4, 2, and 1 points, respectively. Other positions earn zero points.

A complete script at the end of this topic creates the tables, duality views, trigger, and validation directives, and loads the sample data.

Example 1: Validate a race before it is written

This directive requires each race to have between 1 and 100 laps. It checks the lap count in the proposed race document before the insert or update is applied.

CREATE OR REPLACE DIRECTIVE cr_race_laps_range
  FOR race_dv
  VALIDATE
  ON INSERT UPDATE
  BEFORE OBJECT
  NOVALIDATE ENABLE
  USING
    json_value(new.data, '$.laps' RETURNING NUMBER) BETWEEN 1 AND 100;

An insert or update with a lap count outside this range is rejected before the proposed race document is applied.

Example 2: Require each driver document to identify a team

This directive requires each driver document to contain a non-null team identifier.

CREATE OR REPLACE DIRECTIVE cr_driver_requires_team
  FOR driver_dv
  VALIDATE
  ON INSERT UPDATE
  BEFORE OBJECT
  NOVALIDATE ENABLE
  USING
    json_value(new.data, '$.teamId' RETURNING NUMBER) IS NOT NULL;

Example 3: Require unique classified positions in each race

No two classified drivers in the same race may have the same finishing position. A null position represents an unclassified driver in this example.

CREATE OR REPLACE DIRECTIVE cr_race_positions_unique
  FOR race_dv
  VALIDATE
  ON INSERT UPDATE SELECT
  AFTER OBJECT
  NOVALIDATE ENABLE
  USING
    (SELECT COUNT(*)
       FROM driver_race_map drm
      WHERE drm.race_id =
              json_value(new.data, '$._id' RETURNING NUMBER)
        AND drm.position IS NOT NULL)
    =
    (SELECT COUNT(DISTINCT drm.position)
       FROM driver_race_map drm
      WHERE drm.race_id =
              json_value(new.data, '$._id' RETURNING NUMBER)
        AND drm.position IS NOT NULL);

Example 4: Reconcile driver points with race results

A driver’s championship points must equal the points earned in the driver’s race results. This directive checks the total when a driver document is selected.

CREATE OR REPLACE DIRECTIVE cr_driver_points_sync
  FOR driver_dv
  VALIDATE
  ON SELECT
  AFTER OBJECT
  NOVALIDATE ENABLE
  USING
    NVL(json_value(new.data, '$.points' RETURNING NUMBER), 0)
    =
    (SELECT NVL(SUM(CASE drm.position
                      WHEN 1 THEN 25
                      WHEN 2 THEN 18
                      WHEN 3 THEN 15
                      WHEN 4 THEN 12
                      WHEN 5 THEN 10
                      WHEN 6 THEN 8
                      WHEN 7 THEN 6
                      WHEN 8 THEN 4
                      WHEN 9 THEN 2
                      WHEN 10 THEN 1
                      ELSE 0
                    END), 0)
       FROM driver_race_map drm
      WHERE drm.driver_id =
              json_value(new.data, '$._id' RETURNING NUMBER));

If the stored points do not match the points calculated from the race results, Oracle Database raises an error instead of returning the invalid driver document.

Example 5: Reconcile team points with race results

A team’s championship points must equal the total points earned by its drivers. This directive checks the total using data from the driver and race-result tables.

CREATE OR REPLACE DIRECTIVE cr_team_points_sync
  FOR team_dv
  VALIDATE
  ON SELECT
  AFTER OBJECT
  NOVALIDATE ENABLE
  USING
    NVL(json_value(new.data, '$.points' RETURNING NUMBER), 0)
    =
    (SELECT NVL(SUM(CASE drm.position
                      WHEN 1 THEN 25
                      WHEN 2 THEN 18
                      WHEN 3 THEN 15
                      WHEN 4 THEN 12
                      WHEN 5 THEN 10
                      WHEN 6 THEN 8
                      WHEN 7 THEN 6
                      WHEN 8 THEN 4
                      WHEN 9 THEN 2
                      WHEN 10 THEN 1
                      ELSE 0
                    END), 0)
       FROM driver d
       LEFT JOIN driver_race_map drm
         ON drm.driver_id = d.driver_id
      WHERE d.team_id =
              json_value(new.data, '$._id' RETURNING NUMBER));

Example 6: Prevent championship points from decreasing

Comparing old and new document values lets you enforce rules on changes to data. In this example, an update cannot reduce a driver’s championship points.

CREATE OR REPLACE DIRECTIVE cr_driver_points_non_decreasing
  FOR driver_dv
  VALIDATE
  ON UPDATE
  BEFORE OBJECT
  NOVALIDATE ENABLE
  USING
    json_value(old.data, '$.points' RETURNING NUMBER)
      <= json_value(new.data, '$.points' RETURNING NUMBER);

Example 7: Validate a driver’s old and new point totals against race results

A PL/SQL function is useful when validation requires multiple calculations. This function checks that the point total in each old and new driver document matches the points calculated from that document’s race-result array.

CREATE OR REPLACE FUNCTION cr_driver_points_delta_ok(
  old_data JSON,
  new_data JSON
) RETURN BOOLEAN
IS
  l_old_points  NUMBER;
  l_new_points  NUMBER;
  l_old_derived NUMBER;
  l_new_derived NUMBER;
BEGIN
  l_old_points := NVL(json_value(old_data, '$.points'
                                RETURNING NUMBER), 0);
  l_new_points := NVL(json_value(new_data, '$.points'
                                RETURNING NUMBER), 0);

  SELECT NVL(SUM(CASE final_position
                   WHEN 1 THEN 25
                   WHEN 2 THEN 18
                   WHEN 3 THEN 15
                   WHEN 4 THEN 12
                   WHEN 5 THEN 10
                   WHEN 6 THEN 8
                   WHEN 7 THEN 6
                   WHEN 8 THEN 4
                   WHEN 9 THEN 2
                   WHEN 10 THEN 1
                   ELSE 0
                 END), 0)
    INTO l_old_derived
    FROM JSON_TABLE(
           old_data,
           '$.race[*]'
           COLUMNS (final_position NUMBER PATH '$.finalPosition'));

  SELECT NVL(SUM(CASE final_position
                   WHEN 1 THEN 25
                   WHEN 2 THEN 18
                   WHEN 3 THEN 15
                   WHEN 4 THEN 12
                   WHEN 5 THEN 10
                   WHEN 6 THEN 8
                   WHEN 7 THEN 6
                   WHEN 8 THEN 4
                   WHEN 9 THEN 2
                   WHEN 10 THEN 1
                   ELSE 0
                 END), 0)
    INTO l_new_derived
    FROM JSON_TABLE(
           new_data,
           '$.race[*]'
           COLUMNS (final_position NUMBER PATH '$.finalPosition'));

  RETURN l_old_points = l_old_derived
     AND l_new_points = l_new_derived;
END;
/

Create a directive that calls the function:

CREATE OR REPLACE DIRECTIVE cr_driver_points_delta
  FOR driver_dv
  VALIDATE
  ON UPDATE
  AFTER OBJECT
  NOVALIDATE ENABLE
  USING cr_driver_points_delta_ok;

The directive uses this function to validate the old and new point totals.

Example 8: Keep a published podium consistent with race results

A race document contains podium information maintained by the application. The function treats the podium as unpublished when all three extracted driver identifiers are null. Otherwise, it checks that the driver identifiers, driver names, and team names for the top three positions match the corresponding relational data.

CREATE OR REPLACE FUNCTION cr_race_podium_ok(
  old_data JSON,
  new_data JSON
) RETURN BOOLEAN
IS
  l_race_id       NUMBER;
  l_winner_id     NUMBER;
  l_runner1_id    NUMBER;
  l_runner2_id    NUMBER;
  l_winner_name   VARCHAR2(255);
  l_runner1_name  VARCHAR2(255);
  l_runner2_name  VARCHAR2(255);
  l_winner_team   VARCHAR2(255);
  l_runner1_team  VARCHAR2(255);
  l_runner2_team  VARCHAR2(255);
  l_top_three     NUMBER;
  l_distinct_pos  NUMBER;
  l_matches       NUMBER;
BEGIN
  l_race_id := json_value(new_data, '$._id' RETURNING NUMBER);
  l_winner_id := json_value(new_data, '$.podium.winner.driverId'
                            RETURNING NUMBER);
  l_runner1_id := json_value(new_data,
                             '$.podium.firstRunnerUp.driverId'
                             RETURNING NUMBER);
  l_runner2_id := json_value(new_data,
                             '$.podium.secondRunnerUp.driverId'
                             RETURNING NUMBER);

  -- Treat the podium as unpublished when all three extracted driver identifiers
  -- are NULL.
  IF l_winner_id IS NULL
     AND l_runner1_id IS NULL
     AND l_runner2_id IS NULL THEN
    RETURN TRUE;
  END IF;

  l_winner_name := json_value(new_data, '$.podium.winner.name'
                              RETURNING VARCHAR2(255));
  l_runner1_name := json_value(new_data,
                               '$.podium.firstRunnerUp.name'
                               RETURNING VARCHAR2(255));
  l_runner2_name := json_value(new_data,
                               '$.podium.secondRunnerUp.name'
                               RETURNING VARCHAR2(255));
  l_winner_team := json_value(new_data, '$.podium.winner.team'
                              RETURNING VARCHAR2(255));
  l_runner1_team := json_value(new_data,
                               '$.podium.firstRunnerUp.team'
                               RETURNING VARCHAR2(255));
  l_runner2_team := json_value(new_data,
                               '$.podium.secondRunnerUp.team'
                               RETURNING VARCHAR2(255));

  SELECT COUNT(*), COUNT(DISTINCT position)
    INTO l_top_three, l_distinct_pos
    FROM driver_race_map
   WHERE race_id = l_race_id
     AND position BETWEEN 1 AND 3;

  IF l_top_three <> 3 OR l_distinct_pos <> 3 THEN
    RETURN FALSE;
  END IF;

  SELECT COUNT(*)
    INTO l_matches
    FROM driver_race_map drm
    JOIN driver d ON d.driver_id = drm.driver_id
    LEFT JOIN team t ON t.team_id = d.team_id
   WHERE drm.race_id = l_race_id
     AND ((drm.position = 1
           AND d.driver_id = l_winner_id
           AND d.name = l_winner_name
           AND t.name = l_winner_team)
       OR (drm.position = 2
           AND d.driver_id = l_runner1_id
           AND d.name = l_runner1_name
           AND t.name = l_runner1_team)
       OR (drm.position = 3
           AND d.driver_id = l_runner2_id
           AND d.name = l_runner2_name
           AND t.name = l_runner2_team));

  RETURN l_matches = 3;
END;
/

Create a directive that validates the race after an insert or update:

CREATE OR REPLACE DIRECTIVE cr_race_podium_consistency
  FOR race_dv
  VALIDATE
  ON INSERT UPDATE
  AFTER OBJECT
  NOVALIDATE ENABLE
  USING cr_race_podium_ok;

For example, changing the embedded winner’s name to Not Charles Leclerc causes the update to fail if the actual result identifies Charles Leclerc as the winner.

Example 9: Reconcile team points at commit time

A team’s points depend on the results of all its drivers. When a transaction changes several related rows or documents, this directive checks the final team total at commit.

CREATE OR REPLACE FUNCTION cr_team_points_ok(
  old_data JSON,
  new_data JSON
) RETURN BOOLEAN
IS
  l_team_id  NUMBER;
  l_actual   NUMBER;
  l_expected NUMBER;
BEGIN
  l_team_id := json_value(new_data, '$._id' RETURNING NUMBER);
  l_actual := NVL(json_value(new_data, '$.points'
                           RETURNING NUMBER), 0);

  SELECT NVL(SUM(CASE drm.position
                   WHEN 1 THEN 25
                   WHEN 2 THEN 18
                   WHEN 3 THEN 15
                   WHEN 4 THEN 12
                   WHEN 5 THEN 10
                   WHEN 6 THEN 8
                   WHEN 7 THEN 6
                   WHEN 8 THEN 4
                   WHEN 9 THEN 2
                   WHEN 10 THEN 1
                   ELSE 0
                 END), 0)
    INTO l_expected
    FROM driver d
    LEFT JOIN driver_race_map drm
      ON drm.driver_id = d.driver_id
   WHERE d.team_id = l_team_id;

  RETURN l_actual = l_expected;
END;
/

Create a directive that calls the function at commit:

CREATE OR REPLACE DIRECTIVE cr_team_points_consistency
  FOR team_dv
  VALIDATE
  ON INSERT UPDATE
  ON COMMIT
  NOVALIDATE ENABLE
  USING cr_team_points_ok;

If the team’s points do not match the points earned by its drivers, the commit fails and the transaction is rolled back.

Example 10: Raise a domain-specific error for an incomplete podium

An anonymous PL/SQL block can raise an application-specific error that describes the violated business rule. This example requires race results for positions 1, 2, and 3 whenever a podium driver identifier is specified.

CREATE OR REPLACE DIRECTIVE cr_published_podium_requires_top3
  FOR race_dv
  VALIDATE
  ON INSERT UPDATE
  AFTER OBJECT
  NOVALIDATE ENABLE
  USING
  DECLARE
    l_race_id       NUMBER;
    l_podium_exists BOOLEAN;
    l_top_three     NUMBER;
  BEGIN
    l_race_id := json_value(:new.data, '$._id' RETURNING NUMBER);

    l_podium_exists :=
      json_value(:new.data, '$.podium.winner.driverId' RETURNING NUMBER)
        IS NOT NULL
      OR json_value(:new.data,
                    '$.podium.firstRunnerUp.driverId' RETURNING NUMBER)
        IS NOT NULL
      OR json_value(:new.data,
                    '$.podium.secondRunnerUp.driverId' RETURNING NUMBER)
        IS NOT NULL;

    IF l_podium_exists THEN
      SELECT COUNT(DISTINCT position)
        INTO l_top_three
        FROM driver_race_map
       WHERE race_id = l_race_id
         AND position BETWEEN 1 AND 3;

      IF l_top_three <> 3 THEN
        raise_application_error(
          -20981,
          'A published podium requires classified positions 1, 2, and 3');
      END IF;
    END IF;
  END;
/

The block permits an unpublished podium. If any podium driver is specified, all three classified positions must exist.

Complete SQL Script

The following standalone script creates the tables, trigger, duality views, and validation directives, loads sample data, and includes optional validation tests.

/*
 * Validation directives for JSON-relational duality views
 *
 * These car-racing examples demonstrate single-document expressions,
 * scalar subqueries, old and new document comparisons, PL/SQL functions,
 * commit-time validation, and an anonymous block with a custom error message.
 */

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 120
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET LONG 20000
PROMPT
PROMPT === CAR-RACING SCHEMA AND DATA ===
PROMPT

-------------------------------------------------------------------------------
-- 1. Base schema
--
-- Create the car-racing tables. The insert trigger below adds championship
-- points to the driver and team totals when a race result is inserted.
-------------------------------------------------------------------------------

CREATE TABLE team (
    team_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    name    VARCHAR2(255) NOT NULL UNIQUE,
    points  INTEGER NOT NULL,
    CONSTRAINT team_pk PRIMARY KEY (team_id)
);

CREATE TABLE driver (
    driver_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    name      VARCHAR2(255) NOT NULL UNIQUE,
    points    INTEGER NOT NULL,
    team_id   INTEGER,
    CONSTRAINT driver_pk PRIMARY KEY (driver_id),
    CONSTRAINT driver_fk FOREIGN KEY (team_id) REFERENCES team(team_id)
);

CREATE TABLE race (
    race_id   INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    name      VARCHAR2(255) NOT NULL UNIQUE,
    laps      INTEGER NOT NULL,
    race_date DATE,
    podium    JSON,
    CONSTRAINT race_pk PRIMARY KEY (race_id)
);

CREATE TABLE driver_race_map (
    driver_race_map_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    race_id            INTEGER NOT NULL,
    driver_id          INTEGER NOT NULL,
    position           INTEGER,
    CONSTRAINT driver_race_map_uk UNIQUE (race_id, driver_id),
    CONSTRAINT driver_race_map_pk PRIMARY KEY (driver_race_map_id),
    CONSTRAINT driver_race_map_fk1 FOREIGN KEY (race_id)
        REFERENCES race(race_id),
    CONSTRAINT driver_race_map_fk2 FOREIGN KEY (driver_id)
        REFERENCES driver(driver_id)
);

-- Award points for finishing positions 1 through 10 when a result is inserted.
-- Other positions, including NULL, receive zero points.
CREATE OR REPLACE TRIGGER driver_race_map_trigger
BEFORE INSERT ON driver_race_map
FOR EACH ROW
DECLARE
    v_points  INTEGER;
    v_team_id INTEGER;
BEGIN
    SELECT team_id
    INTO v_team_id
    FROM driver
    WHERE driver_id = :NEW.driver_id;
    IF :NEW.position = 1 THEN
        v_points := 25;
    ELSIF :NEW.position = 2 THEN
        v_points := 18;
    ELSIF :NEW.position = 3 THEN
        v_points := 15;
    ELSIF :NEW.position = 4 THEN
        v_points := 12;
    ELSIF :NEW.position = 5 THEN
        v_points := 10;
    ELSIF :NEW.position = 6 THEN
        v_points := 8;
    ELSIF :NEW.position = 7 THEN
        v_points := 6;
    ELSIF :NEW.position = 8 THEN
        v_points := 4;
    ELSIF :NEW.position = 9 THEN
        v_points := 2;
    ELSIF :NEW.position = 10 THEN
        v_points := 1;
    ELSE
        v_points := 0;
    END IF;
    UPDATE driver
    SET points = points + v_points
    WHERE driver_id = :NEW.driver_id;
    UPDATE team
    SET points = points + v_points
    WHERE team_id = v_team_id;
END;
/

-------------------------------------------------------------------------------
-- 2. Duality views
-------------------------------------------------------------------------------

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_dv AS
SELECT JSON {
    '_id' : r.race_id,
    'name' : r.name,
    'laps' : r.laps WITH NOUPDATE,
    'date' : r.race_date,
    'podium' : r.podium WITH NOCHECK,
    'result' : [
        SELECT JSON {
            'driverRaceMapId' : drm.driver_race_map_id,
            'position' : drm.position,
            UNNEST (
                SELECT JSON {'driverId' : d.driver_id, 'name' : d.name}
                FROM driver d
                WITH NOINSERT UPDATE NODELETE
                WHERE d.driver_id = drm.driver_id
            )
        }
        FROM driver_race_map drm WITH INSERT UPDATE DELETE
        WHERE drm.race_id = r.race_id
    ]
}
FROM race r WITH INSERT UPDATE DELETE;

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv AS
SELECT JSON {
    '_id' : d.driver_id,
    'name' : d.name,
    'points' : d.points,
    UNNEST (
        SELECT JSON {'teamId' : t.team_id, 'team' : t.name WITH NOCHECK}
        FROM team t WITH NOINSERT NOUPDATE NODELETE
        WHERE t.team_id = d.team_id
    ),
    'race' : [
        SELECT JSON {
            'driverRaceMapId' : drm.driver_race_map_id,
            UNNEST (
                SELECT JSON {'raceId' : r.race_id, 'name' : r.name}
                FROM race r
                WITH NOINSERT NOUPDATE NODELETE
                WHERE r.race_id = drm.race_id
            ),
            'finalPosition' : drm.position
        }
        FROM driver_race_map drm WITH INSERT UPDATE NODELETE
        WHERE drm.driver_id = d.driver_id
    ]
}
FROM driver d WITH INSERT UPDATE DELETE;

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_dv AS
SELECT JSON {
    '_id' : t.team_id,
    'name' : t.name,
    'points' : t.points,
    'driver' : [
        SELECT JSON {
            'driverId' : d.driver_id,
            'name' : d.name,
            'points' : d.points WITH NOCHECK
        }
        FROM driver d WITH INSERT UPDATE
        WHERE d.team_id = t.team_id
    ]
}
FROM team t WITH INSERT UPDATE DELETE;

-------------------------------------------------------------------------------
-- 3. Sample data
--
-- Load teams, drivers, and two races. Inserting the race results invokes the
-- trigger, which updates the driver and team championship points.
-- The race results below are illustrative sample data.
-------------------------------------------------------------------------------

INSERT INTO team_dv VALUES
    ('{"_id" : 301,
     "name" : "Red Bull",
     "points" : 0,
     "driver" : [
       {"driverId" : 101, "name" : "Max Verstappen", "points" : 0},
       {"driverId" : 102, "name" : "Sergio Perez", "points" : 0}
     ]}');
INSERT INTO team_dv VALUES
    ('{"_id" : 302,
     "name" : "Ferrari",
     "points" : 0,
     "driver" : [
       {"driverId" : 103, "name" : "Charles Leclerc", "points" : 0},
       {"driverId" : 104, "name" : "Carlos Sainz Jr", "points" : 0}
     ]}');
INSERT INTO team_dv VALUES
    ('{"_id" : 2,
     "name" : "Mercedes",
     "points" : 0,
     "driver" : [
       {"driverId" : 105, "name" : "George Russell", "points" : 0},
       {"driverId" : 106, "name" : "Lewis Hamilton", "points" : 0}
     ]}');
INSERT INTO race_dv VALUES
    ('{"_id" : 201,
     "name" : "Bahrain Grand Prix",
     "laps" : 57,
     "date" : "2022-03-20T00:00:00",
     "podium" : {}}');
INSERT INTO race_dv VALUES
    ('{"_id" : 202,
     "name" : "Saudi Arabian Grand Prix",
     "laps" : 50,
     "date" : "2022-03-27T00:00:00",
     "podium" : {}}');
COMMIT;

-- Sample Bahrain Grand Prix finishing order: Leclerc, Sainz, Hamilton, Russell.
INSERT INTO driver_race_map(race_id, driver_id, position)
VALUES (201, 103, 1);

INSERT INTO driver_race_map(race_id, driver_id, position)
VALUES (201, 104, 2);

INSERT INTO driver_race_map(race_id, driver_id, position)
VALUES (201, 106, 3);

INSERT INTO driver_race_map(race_id, driver_id, position)
VALUES (201, 105, 4);

-- Sample Saudi Arabian Grand Prix finishing order: Verstappen, Hamilton,
-- Leclerc, Perez.
INSERT INTO driver_race_map(race_id, driver_id, position)
VALUES (202, 101, 1);

INSERT INTO driver_race_map(race_id, driver_id, position)
VALUES (202, 106, 2);

INSERT INTO driver_race_map(race_id, driver_id, position)
VALUES (202, 103, 3);

INSERT INTO driver_race_map(race_id, driver_id, position)
VALUES (202, 102, 4);

-- Populate the podium JSON objects. The podium validation function compares
-- their driver identifiers, driver names, and team names with relational data.
UPDATE race
SET podium = JSON('{"winner" :
                        {"driverId" : 103,
                         "name" : "Charles Leclerc",
                         "team" : "Ferrari",
                         "time" : "01:37:33.584"},
                       "firstRunnerUp" :
                        {"driverId" : 104,
                         "name" : "Carlos Sainz Jr",
                         "team" : "Ferrari",
                         "time" : "01:37:39.182"},
                       "secondRunnerUp" :
                        {"driverId" : 106,
                         "name" : "Lewis Hamilton",
                         "team" : "Mercedes",
                         "time" : "01:37:43.259"}}')
WHERE race_id = 201;

UPDATE race
SET podium = JSON('{"winner" :
                        {"driverId" : 101,
                         "name" : "Max Verstappen",
                         "team" : "Red Bull",
                         "time" : "01:24:19.293"},
                       "firstRunnerUp" :
                        {"driverId" : 106,
                         "name" : "Lewis Hamilton",
                         "team" : "Mercedes",
                         "time" : "01:24:25.112"},
                       "secondRunnerUp" :
                        {"driverId" : 103,
                         "name" : "Charles Leclerc",
                         "team" : "Ferrari",
                         "time" : "01:24:29.716"}}')
WHERE race_id = 202;

COMMIT;

-------------------------------------------------------------------------------
-- 4. Single-document validation expressions
-------------------------------------------------------------------------------

-- Require the lap count to be between 1 and 100.
CREATE OR REPLACE DIRECTIVE cr_race_laps_range
FOR race_dv
    VALIDATE
    ON INSERT UPDATE
    BEFORE OBJECT
    NOVALIDATE ENABLE
USING
        json_value(new.data, '$.laps' RETURNING NUMBER) BETWEEN 1 AND 100;

-- Require a non-null team identifier in each driver document.
CREATE OR REPLACE DIRECTIVE cr_driver_requires_team
FOR driver_dv
    VALIDATE
    ON INSERT UPDATE
    BEFORE OBJECT
    NOVALIDATE ENABLE
USING
        json_value(new.data, '$.teamId' RETURNING NUMBER) IS NOT NULL;

-------------------------------------------------------------------------------
-- 5. Cross-table scalar-subquery validation expressions
--
-- Compare document values with data in the underlying tables. Each aggregate
-- subquery returns one row, even when no matching rows exist. NVL converts a
-- NULL sum to zero.
-------------------------------------------------------------------------------

-- Require distinct non-null finishing positions within each race.
-- NULL positions are allowed for unclassified drivers.
CREATE OR REPLACE DIRECTIVE cr_race_positions_unique
FOR race_dv
    VALIDATE
    ON INSERT UPDATE SELECT
    AFTER OBJECT
    NOVALIDATE ENABLE
USING
    (SELECT COUNT(*)
    FROM driver_race_map drm
    WHERE drm.race_id =
        json_value(new.data, '$._id' RETURNING NUMBER)
        AND drm.position IS NOT NULL)
    =
    (SELECT COUNT(DISTINCT drm.position)
    FROM driver_race_map drm
    WHERE drm.race_id =
        json_value(new.data, '$._id' RETURNING NUMBER)
        AND drm.position IS NOT NULL);

-- When a driver document is selected, compare its championship points with
-- the points calculated from that driver's race results.
CREATE OR REPLACE DIRECTIVE cr_driver_points_sync
FOR driver_dv
    VALIDATE
    ON SELECT
    AFTER OBJECT
    NOVALIDATE ENABLE
USING
    NVL(json_value(new.data, '$.points' RETURNING NUMBER), 0)
    =
    (SELECT NVL(SUM(CASE drm.position
        WHEN 1 THEN 25
        WHEN 2 THEN 18
        WHEN 3 THEN 15
        WHEN 4 THEN 12
        WHEN 5 THEN 10
        WHEN 6 THEN 8
        WHEN 7 THEN 6
        WHEN 8 THEN 4
        WHEN 9 THEN 2
        WHEN 10 THEN 1
        ELSE 0
    END), 0)
    FROM driver_race_map drm
    WHERE drm.driver_id =
        json_value(new.data, '$._id' RETURNING NUMBER));

-- When a team document is selected, compare its championship points with
-- the points calculated from the race results of all drivers on that team.
CREATE OR REPLACE DIRECTIVE cr_team_points_sync
FOR team_dv
    VALIDATE
    ON SELECT
    AFTER OBJECT
    NOVALIDATE ENABLE
USING
    NVL(json_value(new.data, '$.points' RETURNING NUMBER), 0)
    =
    (SELECT NVL(SUM(CASE drm.position
        WHEN 1 THEN 25
        WHEN 2 THEN 18
        WHEN 3 THEN 15
        WHEN 4 THEN 12
        WHEN 5 THEN 10
        WHEN 6 THEN 8
        WHEN 7 THEN 6
        WHEN 8 THEN 4
        WHEN 9 THEN 2
        WHEN 10 THEN 1
        ELSE 0
    END), 0)
    FROM driver d
    LEFT JOIN driver_race_map drm
    ON drm.driver_id = d.driver_id
    WHERE d.team_id =
        json_value(new.data, '$._id' RETURNING NUMBER));

-------------------------------------------------------------------------------
-- 6. Compare old and new document values
-------------------------------------------------------------------------------

-- Prevent updates through driver_dv from reducing championship points.
-- This example assumes that downward corrections are not permitted.
CREATE OR REPLACE DIRECTIVE cr_driver_points_non_decreasing
FOR driver_dv
    VALIDATE
    ON UPDATE
    BEFORE OBJECT
    NOVALIDATE ENABLE
USING
    json_value(old.data, '$.points' RETURNING NUMBER)
    <= json_value(new.data, '$.points' RETURNING NUMBER);

-------------------------------------------------------------------------------
-- 7. PL/SQL function: validate points against old and new race results
--
-- Calculate points from the race arrays in the old and new driver documents.
-- Check that each document's point total matches its calculated total.
-- Positions outside the range 1 through 10, and NULL positions, contribute
-- zero points.
-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION cr_driver_points_delta_ok(
    old_data JSON,
    new_data JSON
) RETURN BOOLEAN
IS
    l_old_points  NUMBER;
    l_new_points  NUMBER;
    l_old_derived NUMBER;
    l_new_derived NUMBER;
BEGIN
    l_old_points := NVL(json_value(old_data, '$.points'
        RETURNING NUMBER), 0);
    l_new_points := NVL(json_value(new_data, '$.points'
        RETURNING NUMBER), 0);
    SELECT NVL(SUM(CASE final_position
        WHEN 1 THEN 25
        WHEN 2 THEN 18
        WHEN 3 THEN 15
        WHEN 4 THEN 12
        WHEN 5 THEN 10
        WHEN 6 THEN 8
        WHEN 7 THEN 6
        WHEN 8 THEN 4
        WHEN 9 THEN 2
        WHEN 10 THEN 1
        ELSE 0
    END), 0)
    INTO l_old_derived
    FROM JSON_TABLE(
        old_data,
        '$.race[*]'
        COLUMNS (final_position NUMBER PATH '$.finalPosition'));
    SELECT NVL(SUM(CASE final_position
        WHEN 1 THEN 25
        WHEN 2 THEN 18
        WHEN 3 THEN 15
        WHEN 4 THEN 12
        WHEN 5 THEN 10
        WHEN 6 THEN 8
        WHEN 7 THEN 6
        WHEN 8 THEN 4
        WHEN 9 THEN 2
        WHEN 10 THEN 1
        ELSE 0
    END), 0)
    INTO l_new_derived
    FROM JSON_TABLE(
        new_data,
        '$.race[*]'
        COLUMNS (final_position NUMBER PATH '$.finalPosition'));
    RETURN l_old_points = l_old_derived
        AND l_new_points = l_new_derived
        AND (l_new_points - l_old_points) =
            (l_new_derived - l_old_derived);
END;
/

CREATE OR REPLACE DIRECTIVE cr_driver_points_delta
FOR driver_dv
    VALIDATE
    ON UPDATE
    AFTER OBJECT
    NOVALIDATE ENABLE
USING cr_driver_points_delta_ok;

-------------------------------------------------------------------------------
-- 8. PL/SQL function: compare the podium with relational race results
--
-- If all three extracted podium driver identifiers are NULL, the function
-- treats the podium as unpublished. Otherwise, it checks the driver identifiers,
-- driver names, and team names against positions 1, 2, and 3 in the race results.
-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION cr_race_podium_ok(
    old_data JSON,
    new_data JSON
) RETURN BOOLEAN
IS
    l_race_id       NUMBER;
    l_winner_id     NUMBER;
    l_runner1_id    NUMBER;
    l_runner2_id    NUMBER;
    l_winner_name   VARCHAR2(255);
    l_runner1_name  VARCHAR2(255);
    l_runner2_name  VARCHAR2(255);
    l_winner_team   VARCHAR2(255);
    l_runner1_team  VARCHAR2(255);
    l_runner2_team  VARCHAR2(255);
    l_top_three     NUMBER;
    l_distinct_pos  NUMBER;
    l_matches       NUMBER;
BEGIN
    l_race_id := json_value(new_data, '$._id' RETURNING NUMBER);
    l_winner_id := json_value(new_data, '$.podium.winner.driverId'
        RETURNING NUMBER);
    l_runner1_id := json_value(new_data,
        '$.podium.firstRunnerUp.driverId'
        RETURNING NUMBER);
    l_runner2_id := json_value(new_data,
        '$.podium.secondRunnerUp.driverId'
        RETURNING NUMBER);
    -- Treat the podium as unpublished when all three extracted identifiers are NULL.
    IF l_winner_id IS NULL
        AND l_runner1_id IS NULL
        AND l_runner2_id IS NULL THEN
        RETURN TRUE;
    END IF;
    l_winner_name := json_value(new_data, '$.podium.winner.name'
        RETURNING VARCHAR2(255));
    l_runner1_name := json_value(new_data,
        '$.podium.firstRunnerUp.name'
        RETURNING VARCHAR2(255));
    l_runner2_name := json_value(new_data,
        '$.podium.secondRunnerUp.name'
        RETURNING VARCHAR2(255));
    l_winner_team := json_value(new_data, '$.podium.winner.team'
        RETURNING VARCHAR2(255));
    l_runner1_team := json_value(new_data,
        '$.podium.firstRunnerUp.team'
        RETURNING VARCHAR2(255));
    l_runner2_team := json_value(new_data,
        '$.podium.secondRunnerUp.team'
        RETURNING VARCHAR2(255));
    SELECT COUNT(*), COUNT(DISTINCT position)
    INTO l_top_three, l_distinct_pos
    FROM driver_race_map
    WHERE race_id = l_race_id
        AND position BETWEEN 1 AND 3;
    IF l_top_three <> 3 OR l_distinct_pos <> 3 THEN
        RETURN FALSE;
    END IF;
    SELECT COUNT(*)
    INTO l_matches
    FROM driver_race_map drm
    JOIN driver d ON d.driver_id = drm.driver_id
    LEFT JOIN team t ON t.team_id = d.team_id
    WHERE drm.race_id = l_race_id
        AND ((drm.position = 1
            AND d.driver_id = l_winner_id
            AND d.name = l_winner_name
            AND t.name = l_winner_team)
        OR (drm.position = 2
            AND d.driver_id = l_runner1_id
            AND d.name = l_runner1_name
            AND t.name = l_runner1_team)
        OR (drm.position = 3
            AND d.driver_id = l_runner2_id
            AND d.name = l_runner2_name
            AND t.name = l_runner2_team));
    RETURN l_matches = 3;
END;
/

CREATE OR REPLACE DIRECTIVE cr_race_podium_consistency
FOR race_dv
    VALIDATE
    ON INSERT UPDATE
    AFTER OBJECT
    NOVALIDATE ENABLE
USING cr_race_podium_ok;

-------------------------------------------------------------------------------
-- 9. PL/SQL function: validate team points at commit time
--
-- Defer validation of team points until commit, after the transaction has
-- completed its related changes.
-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION cr_team_points_ok(
    old_data JSON,
    new_data JSON
) RETURN BOOLEAN
IS
    l_team_id  NUMBER;
    l_actual   NUMBER;
    l_expected NUMBER;
BEGIN
    l_team_id := json_value(new_data, '$._id' RETURNING NUMBER);
    l_actual := NVL(json_value(new_data, '$.points'
        RETURNING NUMBER), 0);
    SELECT NVL(SUM(CASE drm.position
        WHEN 1 THEN 25
        WHEN 2 THEN 18
        WHEN 3 THEN 15
        WHEN 4 THEN 12
        WHEN 5 THEN 10
        WHEN 6 THEN 8
        WHEN 7 THEN 6
        WHEN 8 THEN 4
        WHEN 9 THEN 2
        WHEN 10 THEN 1
        ELSE 0
    END), 0)
    INTO l_expected
    FROM driver d
    LEFT JOIN driver_race_map drm
    ON drm.driver_id = d.driver_id
    WHERE d.team_id = l_team_id;
    RETURN l_actual = l_expected;
END;
/

CREATE OR REPLACE DIRECTIVE cr_team_points_consistency
FOR team_dv
    VALIDATE
    ON INSERT UPDATE
    ON COMMIT
    NOVALIDATE ENABLE
USING cr_team_points_ok;

-------------------------------------------------------------------------------
-- 10. PL/SQL anonymous block: require all three podium positions
--
-- Use an anonymous block to raise an application-specific error. If any podium
-- driver identifier is non-null, require race results for positions 1, 2, and 3.
-------------------------------------------------------------------------------

CREATE OR REPLACE DIRECTIVE cr_published_podium_requires_top3
FOR race_dv
    VALIDATE
    ON INSERT UPDATE
    AFTER OBJECT
    NOVALIDATE ENABLE
USING
DECLARE
    l_race_id       NUMBER;
    l_podium_exists BOOLEAN;
    l_top_three     NUMBER;
BEGIN
    l_race_id := json_value(:new.data, '$._id' RETURNING NUMBER);
    l_podium_exists :=
        json_value(:new.data, '$.podium.winner.driverId' RETURNING NUMBER)
            IS NOT NULL
        OR json_value(:new.data,
            '$.podium.firstRunnerUp.driverId' RETURNING NUMBER)
            IS NOT NULL
        OR json_value(:new.data,
            '$.podium.secondRunnerUp.driverId' RETURNING NUMBER)
            IS NOT NULL;
    IF l_podium_exists THEN
        SELECT COUNT(DISTINCT position)
        INTO l_top_three
        FROM driver_race_map
        WHERE race_id = l_race_id
            AND position BETWEEN 1 AND 3;
        IF l_top_three <> 3 THEN
            raise_application_error(
                -20981,
                'A published podium requires classified positions 1, 2, and 3');
        END IF;
    END IF;
END;
/

-------------------------------------------------------------------------------
-- 11. Display the documents and validation directives
-------------------------------------------------------------------------------

SELECT data AS driver_document
FROM driver_dv;

SELECT data AS race_document
FROM race_dv;

SELECT data AS team_document
FROM team_dv;

-- List validation directives with names matching the example naming pattern.
SELECT directive_name
FROM user_validation_directives
WHERE directive_name LIKE 'CR_%'
ORDER BY directive_name;

-------------------------------------------------------------------------------
-- 12. Optional validation examples
--
-- The SELECT statements below succeed with the sample data. Statements that
-- demonstrate validation failures are commented out. Uncomment and run each
-- failure example separately.
-------------------------------------------------------------------------------

-- Retrieve the documents to invoke the ON SELECT validation directives.
SELECT data FROM driver_dv;

SELECT data FROM race_dv;

SELECT data FROM team_dv;

-- Expected BEFORE OBJECT failure: laps must be between 1 and 100.
-- INSERT INTO race_dv VALUES
--   ('{"_id" : 901, "name" : "Invalid Lap Test", "laps" : 0,
--      "date" : "2022-04-10T00:00:00", "podium" : {}}');

-- Expected BEFORE OBJECT failure: championship points cannot decrease.
-- UPDATE driver_dv dv
--    SET data = json_transform(data, SET '$.points' = 0)
--  WHERE dv.data."_id" = 103;

-- Expected AFTER OBJECT validation failure: the podium winner's name does
-- not match the name of the driver in first place.
-- UPDATE race_dv dv
--    SET data = json_transform(
--                data,
--                SET '$.podium.winner.name' = 'Not Charles Leclerc')
--  WHERE dv.data."_id" = 201;

-- Expected ON COMMIT validation failure: team 302's points do not match
-- the total points earned by its drivers.
-- UPDATE team_dv dv
--    SET data = json_transform(data, SET '$.points' = 999)
--  WHERE dv.data."_id" = 302;
-- COMMIT;
-- ROLLBACK;

-- Expected SELECT failure after deliberately introducing duplicate positions.
-- Insert a race for this test, then roll back the changes after the query.
-- SAVEPOINT cr_duplicate_position_test;
-- INSERT INTO race(race_id, name, laps, race_date, podium)
--   VALUES (902, 'Duplicate Position Test', 60,
--           DATE '2022-04-10', JSON('{}'));
-- INSERT INTO driver_race_map(race_id, driver_id, position)
--   VALUES (902, 101, 1);
-- INSERT INTO driver_race_map(race_id, driver_id, position)
--   VALUES (902, 102, 1);
-- SELECT json_serialize(data PRETTY) FROM race_dv
--  WHERE json_value(data, '$._id' RETURNING NUMBER) = 902;
-- ROLLBACK TO cr_duplicate_position_test;
-- COMMIT;