Example 7-1 Fields Generated Using a SQL Query and a SQL Expression

This example defines duality view race_dv_sql_gen. The definition is the same as that for view race_dv in Example 3-5, but with two additional, generated fields:

• fastestTime — Fastest time for the race. Uses SQL-query field generation.

• onPodium — Whether the race result for a given driver places the driver on the podium. Uses SQL-expression field generation.

The fastestTime value is computed by applying SQL aggregate function min to the race times of the drivers on the podium. These are obtained from field time of object field winner of JSON-type column podium of the race table: podium.winner.time.

The onPodium value is computed from the value of column position of table driver_race_map. If that column value is 1, 2, or 3 then the value of field onPodium is "YES"; otherwise it is "NO". This logic is realized by evaluating a SQL CASE expression.

GraphQL:

CREATE JSON RELATIONAL DUALITY VIEW race_dv_sql_gen AS
  race
    {_id         : race_id
     name        : name
     laps        : laps @NOUPDATE
     podium      : podium @NOCHECK
     fastestTime @generated (sql : "SELECT min(rt.podium.winner.time) FROM race rt")
     result      : driver_race_map @insert @update @delete @link (to : ["RACE_ID"])
       {driverRaceMapId : driver_race_map_id
        onPodium        @generated (sql : "(CASE WHEN position BETWEEN 1 AND 3
                                                   THEN 'YES'
                                                   ELSE 'NO'
                                            END)")
        driver @unnest @update @noinsert @nodelete
          {driverId : driver_id
           name     : name}}};

(This definition uses GraphQL directive @link with argument to, to specify, for the nested object that's the value of field result, to use foreign-key column race_id of table driver_race_map, which links to primary-key column race_id of table race. See Oracle GraphQL Directive @link.)

SQL:

CREATE JSON RELATIONAL DUALITY VIEW race_dv_sql_gen AS
  SELECT JSON {'_id'         : r.race_id,
               'name'        : r.name,
               'laps'        : r.laps WITH NOUPDATE,
               'date'        : r.race_date,
               'podium'      : r.podium WITH NOCHECK,
               'fastestTime' : GENERATED USING
                                (SELECT min(rt.podium.winner.time) FROM race rt),
               'result'      :
                 [ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
                                'position'        : drm.position,
                                'onPodium'        : GENERATED USING
                                                      (CASE WHEN position BETWEEN 1 AND 3
                                                              THEN 'YES'
                                                              ELSE 'NO'
                                                       END),
                                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;

Example 7-2 Field Generated Using a SQL/JSON Path Expression

This example defines duality view team_dv_path_gen. The definition is the same as that for view team_dv in Example 3-1, except that the points for the team are not stored in the team table. They are calculated by summing the points for the drivers on the team.

SQL/JSON path expression $.driver.points.sum() realizes this. It applies aggregate item method sum() to the values in column points of table driver.

GraphQL:

CREATE JSON RELATIONAL DUALITY VIEW team_dv_path_gen AS
  team @insert @update @delete
    {_id    : team_id
     name   : name
     points @generated (path : "$.driver.points.sum()")
     driver @insert @update @link (to : ["TEAM_ID"])
       {driverId : driver_id
        name     : name
        points   : points @nocheck}};

SQL:

CREATE JSON RELATIONAL DUALITY VIEW team_dv_path_gen AS
  SELECT JSON {'_id'    : t.team_id,
               'name'   : t.name,
               'points' : GENERATED USING PATH '$.driver.points.sum()',
               '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;

Previously in this documentation we've assumed that the points field for a driver and the points field for a team were both updated by application code. But the team points are entirely defined by the driver points values. It makes sense to consolidate this logic (functional dependence) in the team duality view itself, expressing it declaratively (team's points = sum of its drivers' points).

Example 7-3 Fields Generated Using Hidden Fields

This example defines duality view emp_dv_gen using employees table emp.

• It defines hidden fields wage and tips using columns emp.wage and emp.tips, respectively.

• It generates field totalComp using a SQL expression that sums the values of columns emp.wage and emp.tips.

• It generates Boolean field highTips using a SQL/JSON path expression that compares the values of fields tips and wage.

CREATE TABLE emp(empno NUMBER PRIMARY KEY,
                 first VARCHAR2(100),
                 last  VARCHAR2(100),
                 wage  NUMBER,
                 tips  NUMBER);

INSERT INTO emp VALUES (1, 'Jane', 'Doe', 1000, 2000);

GraphQL:

CREATE JSON RELATIONAL DUALITY VIEW emp_dv_gen AS
  emp
    {_id       : empno
     wage      : wage @hidden
     tips      : tips @hidden
     totalComp @generated (sql  : "wage + tips")
     highTips  @generated (path : "$.tips > $.wage")};

SQL:

CREATE JSON RELATIONAL DUALITY VIEW emp_dv_gen AS
  SELECT JSON {'_id'       : EMPNO,
               'wage'      : e.wage HIDDEN,
               'tips'      : e.tips HIDDEN,
               'totalComp' : GENERATED USING (e.wage + e.tips),
               'highTips'  : GENERATED USING PATH '$.tips > $.wage'}
    FROM emp e;

SELECT data FROM emp_dv_gen;

Query result (pretty-printed here for clarity):

{"_id"       : 1,
 "totalComp" : 3000, 
 "highTips"  : true, 
 "_metadata" : {"etag" : "B8CA77231CA578A6137788C83BC0F410",
                "asof" : "000025B864BC59AB"}}