Creating Car-Racing Duality Views Using GraphQL

Team, driver, and race duality views for the car-racing application are created using GraphQL.

GraphQL is an open-source, general query and data-manipulation language that can be used with various databases. A subset of GraphQL syntax and operations are supported by Oracle Database for creating JSON-relational duality views. Oracle Database Support for GraphQL Developer's Guide describes the supported subset of GraphQL. It introduces syntax and features that are not covered here.

GraphQL queries and type definitions are expressed as a GraphQL document. The GraphQL examples shown here, for creating the car-racing duality views, are similar to the SQL examples. The most obvious difference is just syntactic.

The more important differences are that with a GraphQL definition of a duality view you don't need to explicitly specify these things:

Nested scalar subqueries.
Table links between foreign-key columns and identifying columns, as long as a child table has only one foreign key to its parent table.^Foot 1
The use of SQL/JSON generation functions (or their equivalent syntax abbreviations).

This information is instead all inferred from the graph/dependency relations that are inherent in the overall duality-view definitions. The tables underlying a duality view form a directed dependency graph by virtue of the relations among their identifying columns and foreign-key columns. A foreign key from one table, T-child, to another table, T-parent, results in a graph edge (an arrow) directed from node T-child to node T-parent.

You don't need to construct the dependency graph determined by a set of tables; that's done automatically (implicitly) when you define a duality view. But it can sometimes help to visualize it.

An edge (arrow) of the graph links a table with a foreign-key column to the table whose identifying column is the target of that foreign key. For example, an arrow from node (table) driver to node (table) team indicates that a foreign key of table driver is linked to a primary key of table team. In Figure 3-1, the arrows are labeled with the foreign and primary keys.

Figure 3-1 Car-Racing Example, Table-Dependency Graph

Description of "Figure 3-1 Car-Racing Example, Table-Dependency Graph"

The GraphQL code that defines a JSON-relational duality view takes the form of a GraphQL query (without the surrounding query { … } code), which specifies the graph structure, based on the dependency graph, which is used by the view. A GraphQL duality-view definition specifies, for each underlying table, the columns that are used to generate the JSON fields in the supported JSON documents.

In GraphQL, a view-defining query is represented by a GraphQL object schema, which, like the dependency graph on which it's based, is constructed automatically (implicitly). You never need to construct or see either the dependency graph or the GraphQL object schema that's used to create a duality view, but it can help to know something about each of them.

A GraphQL object schema is a set of GraphQL object types, which for a duality-view definition are based on the tables underlying the view.

The GraphQL query syntax for creating a duality view reflects the structure of the table-dependency graph, and it's based closely on the object-schema syntax. (One difference is that the names used are compatible with SQL.)

In an object schema, and thus in the query syntax, each GraphQL object type (mapped from a table) is named by a GraphQL field (not to be confused with a field in a JSON object). And each GraphQL field can optionally have an alias.

A GraphQL query describes a graph, where each node specifies a type. The syntax for a node in the graph is a (GraphQL) field name followed by its object type. If the field has an alias then that, followed by a colon (:), precedes the field name. An object type is represented by braces ({ ... }) enclosing a subgraph. A field need not be followed by an object type, in which case it is scalar.

The syntax of GraphQL is different from that of SQL. In particular, the syntax of names (identifiers) is different. In a GraphQL duality-view definition, any table and column names that are not allowed directly as GraphQL names are mapped to names that are. But simple, all-ASCII alphanumeric table and column names, such as those of the car-racing example, can be used directly in the GraphQL definition of a duality view.

For example:

```
driverId : driver_id
```
Field driver_id preceded by alias driverId .
```
driver : driver {driverId : driver_id,
                 name     : name,
                 points   : points}
```
Field driver preceded by alias driver and followed by an object type that has field driver_id, with alias driverId, and fields name and points, each with an alias named the same as the field.
```
driver {driverId : driver_id,
        name,
        points}
```
Equivalent to the previous example. Aliases that don't differ from their corresponding field names can be omitted.

In the object type that corresponds to a table, each column of the table is mapped to a scalar GraphQL field with the same name as the column.

Note:

In each of those examples, alias driverId would be replaced by alias _id, if used as a document-identifier field, that is, if driver is the root table and driver_id is its primary-key column.

Note:

In GraphQL commas (,) are not syntactically or semantically significant; they're optional, and are ignored. For readability, in this documentation we use commas within GraphQL {…}, to better suggest the corresponding JSON objects in the supported documents.

In a GraphQL definition of a duality view there's no real distinction between a node that contributes a single object to a generated JSON document and a node that contributes an array of such objects. You can use just { … } to specify that the node is a GraphQL object type, but that doesn't imply that only a single JSON object results from it in the supported JSON documents.

However, to have a GraphQL duality-view definition more closely reflect the JSON documents that the view is designed to support, you can optionally enclose a node that contributes an array of objects in brackets ([, ]).

For example, you can write [{…},…] instead of just {…},…, to show that this part of a definition produces an array of driver objects. This convention is followed in this documentation.

Keep in mind that this is only for the sake of human readers of the code; the brackets are optional, where they make sense. But if you happen to use them where they don't make sense then a syntax error is raised, to help you see your mistake.

You use the root table of a duality view as the GraphQL root field of the view definition. For example, for the duality view that defines team documents, you start with table team as the root: you write team {…}.

Within the { … } following a type name (such as team), which for a duality view definition is a table name, you specify the columns from that table that are used to create the generated JSON fields.

You thus use column names as GraphQL field names. By default, these also name the JSON fields you want generated.

If the name of the JSON field you want is the not same as that of the column (GraphQL field) that provides its value, you precede the column name with the name of the JSON field you want, separating the two by a colon (:). That is, you use a GraphQL alias to specify the desired JSON field name.

For example, driverId : driver_id means generate JSON field driverId from the data in column driver_id. In GraphQL terms, driverId is an alias for (GraphQL) field driver_id.

Using driver_id alone means generate JSON field driver_id from the column with that name.
Using driverId : driver_id means generate JSON field driverId from the data in column driver_id. In GraphQL terms, driverId is an alias for the GraphQL field driver_id.

When constructing a GraphQL query to create a duality view, you add a GraphQL field for each column in the table-dependency graph that you want to support a JSON field.

In addition, for each table T used in the duality view definition:

For each foreign-key link from T to a parent table T-parent, you add a field named T-parent to the query, to allow navigation from T to T-parent. This link implements a one-to-one relationship: there is a single parent T-parent.
For each foreign-key link from a table T-child to T, you add a field named T-child to the query, to allow navigation from T to T-child. This link implements a one-to-many relationship: there can be multiple children of type T-child.

Unnesting (flattening) of intermediate objects is the same as for a SQL definition of a duality view, but instead of SQL keyword UNNEST you use GraphQL directive @unnest. (All of the GraphQL duality-view definitions shown here use @unnest.)

In GraphQL you can introduce an end-of-line comment with the hash/number-sign character, #: it and the characters following it on the same line are commented out.

Example 3-6 Creating Duality View TEAM_DV Using GraphQL

This example creates a duality view supporting JSON documents where the team objects look like this — they contain a field driver whose value is an array of nested objects that specify the drivers on the team:

{"_id" : 301, "name" : "Red Bull", "points" : 0, "driver" : [...]}

(The view created is the same as that created using SQL in Example 3-1.)

CREATE JSON RELATIONAL DUALITY VIEW team_dv AS
  team @insert @update @delete
    {_id    : team_id,
     name   : name,
     points : points,
     driver : driver @insert @update
       [ {driverId : driver_id,
          name     : name,
          points   : points @nocheck} ]};

In the above example, square brackets are used to define an array structure by default. As an alternative to square brackets, you can use the @array directive to achieve the same array result.

Example 3-7 Duality-View Creation with @array Directive

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_dv AS
team @insert @update @delete {
    _id    : team_id,
    name   : name,
    points : points,
    driver : driver @insert @update @array {
        driverId : driver_id,
        name     : name,
        points   : points @nocheck
    } 
};
SELECT JSON_SERIALIZE(data PRETTY) FROM team_dv;

When there is no primary key–foreign key (PK-FK) relationship, using square brackets clearly specifies that the returned object should be an array. If neither square brackets nor the @array directive are specified, a singleton object will be generated instead of an array.

Example 3-8 Duality-View Creation without @array Directive

Recall from Car-Racing Example, Tables that the tables driver and team has a primary key - foreign key relationship. So, without the @array, the team field will be an singleton object.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv_team_obj AS 
driver {
    _id       : driver_id,
    name      : name,
    points    : points,
    team {
        teamId : team_id,
        team   : name
    }
};
SELECT JSON_SERIALIZE(data PRETTY) FROM driver_dv_team_obj;

The @array directive ensures that the sub-object is always returned as an array. Conversely, the @object directive forces the result to be a singleton object, even if multiple rows could otherwise be returned.

Example 3-9 Duality-View Creation with @object Directive

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_dv_driver_obj AS
team @insert @update @delete {
    _id    : team_id,
    name   : name,
    points : points,
    driver : driver @insert @update @object {
        driverId : driver_id,
        name     : name,
        points   : points @nocheck
    } 
};
SELECT JSON_SERIALIZE(data PRETTY) FROM driver_dv_team_obj;

Example 3-10 Creating Duality View DRIVER_DV Using GraphQL

This example creates a duality view supporting JSON documents where the driver objects look like this — they don't contain a field teamInfo whose value is a nested object with fields teamId and name. Instead, the data from table team is incorporated at the top level, with the team name as field team.


{"_id"      : 101,
 "name"     : "Max Verstappen",
 "points"   : 0,
 "teamId"   : 103,
 "team"     : "Red Bull",
 "race"     : [...]}

Two versions of the view creation are shown here. For simplicity, a first version has no annotations declaring updatability or ETAG-calculation exclusion.

CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
  driver
    {_id       : driver_id,
     name      : name,
     points    : points,
     team @unnest
       {teamId : team_id,
        team   : name},
     race      : driver_race_map
                   [ {driverRaceMapId : driver_race_map_id,
                      race @unnest
                        {raceId       : race_id,
                         name         : name},
                      finalPosition   : position} ]};

The second version of the view creation has updatability and ETAG @nocheck annotations. (It creates the same view as that created using SQL in Example 3-3.)

CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
  driver @insert @update @delete
    {_id       : driver_id,
     name      : name,
     points    : points,
     team @noinsert @noupdate @nodelete
       @unnest
       {teamId : team_id,
        team   : name @nocheck},
     race      : driver_race_map @insert @update @nodelete
                   [ {driverRaceMapId : driver_race_map_id,
                      race @noinsert @noupdate @nodelete
                        @unnest
                        {raceId : race_id,
                         name   : name},
                      finalPosition   : position} ]};

Example 3-11 Creating Duality View RACE_DV Using GraphQL

This example creates a duality view supporting JSON documents where the objects that are the elements of array result look like this — they don't contain a field driverInfo whose value is a nested object with fields driverId and name:

{"driverId" : 103, "name" : "Charles Leclerc", "position" : 1}

Two versions of the view creation are shown here. For simplicity, a first version has no annotations declaring updatability or ETAG-calculation exclusion.

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  race
    {_id    : race_id,
     name   : name,
     laps   : laps,
     date   : race_date,
     podium : podium,
     result : driver_race_map
       [ {driverRaceMapId : driver_race_map_id,
          position        : position,
          driver
            @unnest
            {driverId : driver_id,
             name     : name}} ]};

The second version of the view creation has updatability and ETAG @nocheck annotations. (It creates the same view as that created using SQL in Example 3-5.)

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  race @insert @update @delete
    {_id    : race_id,
     name   : name,
     laps   : laps @noupdate,
     date   : race_date,
     podium : podium @nocheck,
     result : driver_race_map @insert @update @delete
       [ {driverRaceMapId : driver_race_map_id,
          position        : position,
          driver @noinsert @update @nodelete
            @unnest
            {driverId : driver_id,
             name     : name}} ]};

Related Topics