4 april 2024

Relational
Let’s start by creating a couple of relational tables. Again, we create the constructors, drivers and constructordrivers tables, just like we did in the Table Values Constructor blog.

create table if not exists constructors
(
 constructorid number         generated by default on null as identity
, name          varchar2( 16 )
, nationality   varchar2( 16 )
)
annotations ( purpose 'hold the constructors' )
/
create table if not exists drivers
(
 driverid      number         generated by default on null as identity
, driver_number number  (  2 )
, code          varchar2(  3 )
, driver_name   varchar2( 32 )
, dob           date
, nationality   varchar2( 16 )
)
annotations ( purpose 'hold the drivers' )
/
create table if not exists constructordrivers
(
 constructordriverid number         generated by default on null as identity
, constructorid       number
, driverid            number
)
annotations ( purpose 'register which driver is connected to which constructor' )
/

Now we can add the rows to the tables. For this example, we only add two constructors, Red Bull and AlphaTauri, to the table and the drivers at the start of the 2023 season. For Red Bull the drivers were Max Verstappen and Sergio Perez. For AlphaTauri, the drivers are Yuki Tsunoda and Nyck de Vries.

-- insert the constructors, using a table values constructor
insert into constructors
 ( name, nationality )
values
 ( 'AlphaTauri'    , 'Italian'  )
, ( 'Red Bull'      , 'Austrian' )
/
-- insert the drivers, using a table values constructor
insert into drivers
 ( driver_number, code, driver_name, dob, nationality )
values
 ( 11, 'PER', 'Sergio Pérez'    , to_date( '19900126', 'YYYYMMDD' ), 'Mexican'    )
, ( 33, 'VER', 'Max Verstappen'  , to_date( '19970930', 'YYYYMMDD' ), 'Dutch'      )
, ( 22, 'TSU', 'Yuki Tsunoda'    , to_date( '20000511', 'YYYYMMDD' ), 'Japanese'   )
, ( 21, 'DEV', 'Nyck de Vries'   , to_date( '19950206', 'YYYYMMDD' ), 'Dutch'      )
/
-- connect the drivers to their constructors, using a table values constructor
insert into constructordrivers
 ( constructorid, driverid )
values
 ( ( select con.constructorid from constructors con where con.name = 'Red Bull' )
 , ( select drv.driverid from drivers drv where drv.code = 'VER' )
 )
, ( ( select con.constructorid from constructors con where con.name = 'Red Bull' )
 , ( select drv.driverid from drivers drv where drv.code = 'PER' )
 )
, ( ( select con.constructorid from constructors con where con.name = 'AlphaTauri' )
 , ( select drv.driverid from drivers drv where drv.code = 'DEV' )
 )
, ( ( select con.constructorid from constructors con where con.name = 'AlphaTauri' )
 , ( select drv.driverid from drivers drv where drv.code = 'TSU' )
 )
/
commit
/

We now have the relational representation of these two teams at the beginning of the 2023 season.

JSON
If we want to show this relational data in a JSON document, we can do this by creating a view like this:

create or replace view constructor_v as
select json_object
      ( key 'constructorid' value con.constructorid
      , key 'name'          value con.name
      , key 'nationality'   value con.nationality
      , key 'drivers'       value
        ( select json_arrayagg( drivers returning clob )
          from   ( select json_object
                          ( key 'condrvid' value cds.constructordriverid
                          , key 'driver'   value json_object
                                                 ( key 'driverid'      value drv.driverid
                                                 , key 'driver_number' value drv.driver_number
                                                 , key 'code'          value drv.code
                                                 , key 'driver_name'   value drv.driver_name
                                                 , key 'dob'           value drv.dob
                                                 , key 'nationality'   value drv.nationality
                                                 )
                          ) drivers
                   from   constructordrivers cds
                   join   drivers            drv
                     on   ( cds.driverid = drv.driverid )
                   where  cds.constructorid = con.constructorid
                 )
        )
      ) data
from   constructors con
/

When selecting from this view, I use the json_serialize( document pretty ) construct to return the result in a more human readable form.

-- select Red Bull from this view
select json_serialize( cv.data pretty ) document
from   constructor_v cv
where  json_exists( cv.data
                 ,'$?(@.name like $name)'
                  passing '%Bull%' as "name"
                 )
/
DOCUMENT
------------------------------------------------------------
{
 "constructorid" : 2,
 "name" : "Red Bull",
 "nationality" : "Austrian",
 "drivers" :
 [
   {
     "condrvid" : 2,
     "driver" :
     {
       "driverid" : 1,
       "driver_number" : 11,
       "code" : "PER",
       "driver_name" : "Sergio Perez",
       "dob" : "1990-01-26T00:00:00",
       "nationality" : "Mexican"
     }
   },
   {
     "condrvid" : 1,
     "driver" :
     {
       "driverid" : 2,
       "driver_number" : 33,
       "code" : "VER",
       "driver_name" : "Max Verstappen",
       "dob" : "1997-09-30T00:00:00",
       "nationality" : "Dutch"
     }
   }
 ]
}

A view can be created like this in Oracle Database 19c. Especially the modern development environments expect JSON documents, and have no notion about the relational format. This way you can provide the relational data in a JSON format to the clients. 

Retrieving data is one thing, but you probably also need to perform other operations on the data, like INSERT, UPDATE, or DELETE, or when you operating in the RESTful world, POST, PUT, or DELETE. This is where it gets tricky, as we will have to create custom code to perform these actions.

This is where Oracle Database 23c comes to the rescue with JSON Relational Duality Views. To be able to create JSON Relational Duality views we must define Primary Keys and Foreign Keys on the tables (Of course, we should always create keys to preserve referential integrity).

alter table if exists constructors
add constraint pk_constructor 
   primary key ( constructorid )
/
alter table if exists drivers
add constraint pk_driver
   primary key ( driverid )
/
alter table if exists constructordrivers
add constraint pk_constructordriver
   primary key ( constructordriverid )
/
alter table if exists constructordrivers
add constraint fk_constructordrivers_constructors
   foreign key ( constructorid ) references constructors( constructorid )
/
alter table if exists constructordrivers
add constraint fk_constructordrivers_drivers
   foreign key ( driverid ) references drivers( driverid )
/

With the referential integrity in place you can create the JSON Relational Duality View on the tables. The way we create a view is a bit different from the previous one. To create the exact same layout as before we create a Duality View like this:

create or replace json relational duality view constructor_dv as
select json { 'constructorid' : con.constructorid
           , 'name'          : con.name
           , 'nationality'   : con.nationality
           , 'drivers'       :
             [ select json { 'condrvid' : constructordriverid
                           , 'driver'   :
                             ( select json { 'driverid'      : drv.driverid
                                           , 'driver_number' : drv.driver_number
                                           , 'code'          : drv.code
                                           , 'driver_name'   : drv.driver_name
                                           , 'dob'           : drv.dob
                                           , 'nationality'   : drv.nationality
                                           }
                               from   drivers drv with update
                               where  cds.driverid = drv.driverid
                             )
                           }
               from   constructordrivers cds with insert update delete
               where  con.constructorid = cds.constructorid
             ]
           }
from   constructors con with insert update delete
/

As you can see, we cannot create joins in the view, but we can select data from a connected table in a different level of the document. You join the child level to the parent level in the where clause. The tables and fields used in the view can be annotated with keywords to define if they can (or cannot) be [no]inserted, [no]updated, or [no]deleted. We can also define which fields in the underlying tables should be used in the calculation of the _etag value to prevent overwriting other sessions changes with the [no]check annotation. The _metadata object is generated automagically; I’ll explain what it’s used for in just a bit.

-- select Red Bull from this duality view
select json_serialize( cdv.data pretty ) document
from   constructor_dv cdv
where  json_exists( cdv.data
                 ,'$?(@.name like $name)'
                  passing '%Bull%' as "name"
                 )
/
DOCUMENT
------------------------------------------------------------
{
 "_metadata" :
 {
   "etag" : "1DE2ECCA18CBB8614F4F302537FF50A8",
   "asof" : "0000000001480FCC"
 },
 "constructorid" : 2,
 "name" : "Red Bull",
 "nationality" : "Austrian",
 "drivers" :
 [
   {
     "condrvid" : 1,
     "driver" :
     {
       "driverid" : 2,
       "driver_number" : 33,
       "code" : "VER",
       "driver_name" : "Max Verstappen",
       "dob" : "1997-09-30T00:00:00",
       "nationality" : "Dutch"
     }
   },
   {
     "condrvid" : 2,
     "driver" :
     {
       "driverid" : 1,
       "driver_number" : 11,
       "code" : "PER",
       "driver_name" : "Sergio Perez",
       "dob" : "1990-01-26T00:00:00",
       "nationality" : "Mexican"
     }
   }
 ]
}

This document looks almost exactly the same as the one that is created by the normal view, except for the "_metadata" tag. The data in this tag is used by Oracle Database to perform its optimistic locking and to make sure other sessions changes aren’t overwritten.

If we perform a standard update to the data in the table for instance, change the driver number for Max Verstappen from 33 (his normal number) to 1 (for the World Champion) like this:

update drivers
set    driver_number = 1
where  driver_number = 33
/
And then query the view again, we see the value has changed there as well:

-- select Red Bull from this duality view
select json_serialize( cdv.data pretty ) document
from   constructor_dv cdv
where  json_exists( cdv.data
                 ,'$?(@.name like $name)'
                  passing '%Bull%' as "name"
                 )
/
<<<removed for brevity>>>
     {
       "driverid" : 2,
       "driver_number" : 1,
       "code" : "VER",
       "driver_name" : "Max Verstappen",
       "dob" : "1997-09-30T00:00:00",
       "nationality" : "Dutch"
     }
<<<removed for brevity>>>

This was also possible with the ‘normal’ view. But to demonstrate how we can leverage the power of JSON Relational Duality Views, we create another duality view, just on the drivers table, this time using GraphQL format.

-- create a simple driver view, using GraphQL
create or replace json relational duality view simpledriver_dv as
drivers @insert @update @delete
{ driverid      : driverid
, driver_number : driver_number
, code          : code
, driver_name   : driver_name
, dob           : dob
, nationality   : nationality
}
/
-- select Max Verstappen from this duality view
select json_serialize( sdv.data pretty ) document
from   simpledriver_dv sdv
where  json_exists( sdv.data
                 ,'$?(@.driver_name like $name)'
                  passing '%Verstappen%' as "name"
                 )
/
DOCUMENT
------------------------------------------------------------
{
 "_metadata" :
 {
   "etag" : "F1D73823C6B33E3FB8FFD7C5AAC52C1E",
   "asof" : "0000000001480FDC"
 },
 "driverid" : 2,
 "driver_number" : 1,
 "code" : "VER",
 "driver_name" : "Max Verstappen",
 "dob" : "1997-09-30T00:00:00",
 "nationality" : "Dutch"
}

If we issue an update against this view, providing a JSON document as input, we can change the driver_number back to 33. Note that we must supply the value of the _metadata.etag to make sure the document hasn’t been updated since you last queried the document.

update simpledriver_dv sdv
set    data = '{ "driverid" : 2
              , "driver_number" : 33
              , "code" : "VER"
              , "driver_name" : "Max Verstappen"
              , "dob" : "1997-09-30T00:00:00"
              , "nationality" : "Dutch"
              }'
where  json_exists( sdv.data
                 ,'$?(@._metadata.etag like $etag)'
                  passing 'F1D73823C6B33E3FB8FFD7C5AAC52C1E' as "etag"
                 )
/
-- select Max Verstappen from this duality view
select json_serialize( sdv.data pretty ) document
from   simpledriver_dv sdv
where  json_exists( sdv.data
                 ,'$?(@.driver_name like $name)'
                  passing '%Verstappen%' as "name"
                 )
/
DOCUMENT
------------------------------------------------------------
{
 "_metadata" :
 {
   "etag" : "45F4F82C9F8FACB5051543C0901D7F85",
   "asof" : "0000000001480FE3"
 },
 "driverid" : 2,
 "driver_number" : 33,
 "code" : "VER",
 "driver_name" : "Max Verstappen",
 "dob" : "1997-09-30T00:00:00",
 "nationality" : "Dutch"
}

We create another driver duality view, this time with the connection to the constructor in place:

create or replace json relational duality view driver_dv as
drivers @insert @update @delete
{ driverid      : driverid
, driver_number : driver_number
, code          : code
, driver_name   : driver_name
, dob           : dob
, nationality   : nationality
, constructor   : constructordriver @insert @update @delete
 { condrvid : condrvid
   constructors @noinsert @noupdate @nodelete @unnest
   { constructorid : constructorid
   , name          : name
   , nationality   : nationality
   }
 }
}
/

And now we can add a driver and connect him to the correct constructor with a single JSON document. Let’s add Daniel Ricciardo to the AlphaTauri team:

insert into driver_dv values
 ( '{ "driver_number" : 3
   , "code" : "RIC"
   , "driver_name" : "Daniel Ricciardo"
   , "dob" : "1989-07-01T00:00:00"
   , "nationality" : "Australian"
   , "constructor" : [
                       { "constructorid" : 1
                       , "name" : "AlphaTauri"
                       , "nationality" : "Italian"
                       }
                     ]
   }'
 )
/

If we now select from our simple driver duality view we see that Daniel has been added:

-- select Daniel Ricciardo from this duality view
select json_serialize( sdv.data pretty ) document
from   simpledriver_dv sdv
where  json_exists( sdv.data
                 ,'$?(@.driver_name like $name)'
                  passing '%Ricciardo%' as "name"
                 )
/
DOCUMENT
------------------------------------------------------------
{
 "_metadata" :
 {
   "etag" : "3B94DB50B09A8EE0379E6DDE255CA6EE",
   "asof" : "0000000001480FF0"
 },
 "driverid" : 5,
 "driver_number" : 3,
 "code" : "RIC",
 "driver_name" : "Daniel Ricciardo",
 "dob" : "1989-07-01T00:00:00",
 "nationality" : "Australian"
}

As we can see, the driverid has been automagically added to the record. If we query the relational table, we can see that Daniel has been added there as well.

select *
from   drivers
where  driver_name like '%Ricciardo%'
/
DRIVERID DRIVER_NUMBER COD DRIVER_NAME                      DOB       NATIONALITY
-------- ------------- --- -------------------------------- --------- ----------------
      5             3 RIC Daniel Ricciardo                 01-JUL-89 Australian

Now select AlphaTauri from the first duality view we created:

select json_serialize( cdv.data pretty ) document
from   constructor_dv cdv
where  json_exists( cdv.data
                 ,'$?(@.name like $name)'
                  passing '%Tauri%' as "name"
                 )
/
DOCUMENT
------------------------------------------------------------
{
 "_metadata" :
 {
   "etag" : "793AE5493F8DEF20E0F502E616C8DC72",
   "asof" : "0000000001480FF0"
 },
 "constructorid" : 1,
 "name" : "AlphaTauri",
 "nationality" : "Italian",
 "drivers" :
 [
   {
     "condrvid" : 3,
     "driver" :
     {
       "driverid" : 4,
       "driver_number" : 21,
       "code" : "DEV",
       "driver_name" : "Nyck de Vries",
       "dob" : "1995-02-06T00:00:00",
       "nationality" : "Dutch"
     }
   },
   {
     "condrvid" : 4,
     "driver" :
     {
       "driverid" : 3,
       "driver_number" : 22,
       "code" : "TSU",
       "driver_name" : "Yuki Tsunoda",
       "dob" : "2000-05-11T00:00:00",
       "nationality" : "Japanese"
     }
   },
   {
     "condrvid" : 5,
     "driver" :
     {
       "driverid" : 5,
       "driver_number" : 3,
       "code" : "RIC",
       "driver_name" : "Daniel Ricciardo",
       "dob" : "1989-07-01T00:00:00",
       "nationality" : "Australian"
     }
   }
 ]
}

As we can see, Nyck de Vries, the driver that was replaced by Daniel Ricciardo, is still present as a driver for the AlphaTauri team, this is not correct. We can update the document in the constructor_dv view, thereby removing Nyck from the team:

update constructor_dv cdv
set    data = '{ "constructorid" : 1
              , "name" : "AlphaTauri"
              , "nationality" : "Italian"
              , "drivers" :
                [ { "condrvid" : 4
                  , "driver" :
                    { "driverid" : 3
                    , "driver_number" : 22
                    , "code" : "TSU"
                    , "driver_name" : "Yuki Tsunoda"
                    , "dob" : "2000-05-11T00:00:00"
                    , "nationality" : "Japanese"
                    }
                  },
                  { "condrvid" : 5
                  , "driver" :
                    { "driverid" : 5
                    , "driver_number" : 3
                    , "code" : "RIC"
                    , "driver_name" : "Daniel Ricciardo"
                    , "dob" : "1989-07-01T00:00:00"
                    , "nationality" : "Australian"
                    }
                  }
                ]
              }'
where  json_exists( cdv.data
                 ,'$?(@._metadata.etag like $etag)'
                  passing '793AE5493F8DEF20E0F502E616C8DC72' as "etag"
                 )
/

Be aware that if we omit the condrvid tag from the document you pass in, the old record will be deleted and a new record will be created in the constructordrivers table. Depending on what our application intends, this may not be the desired behaviour.

RESTful services
What’s the use of supplying JSON documents when we are issuing SQL statements anyway? Would it not make more sense to just update the relational tables and have the client retrieve the updated JSON documents? Well, if we REST enable our database, we can just issue PUT and POST commands directly against the database with a JSON document as the payload and have Oracle Database do all the heavy lifting of putting all the data in the correct tables.

I am not an expert in ORDS or Restful services, so I used Martin Bach’s blog to build an environment where I could play with this.
To REST enable the view, we first have to REST enable our schema:

declare
 pragma autonomous_transaction;
begin
 ords.enable_schema
   ( p_enabled             => true
   , p_schema              => 'DEMO'
   , p_url_mapping_type    => 'BASE_PATH'
   , p_url_mapping_pattern => 'demo'
   , p_auto_rest_auth      => false
   );
 commit;
end;
/

Then we can REST enable the Duality View:

declare
 pragma autonomous_transaction;
begin
 ords.enable_object
   ( p_enabled        => true
   , p_schema         => 'DEMO'
   , p_object         => 'SIMPLEDRIVER_DV'
   , p_object_type    => 'VIEW'
   , p_object_alias   => 'simpledriver_dv'
   , p_auto_rest_auth => false
   );
 commit;
end;
/

Now when we request data from the service, for instance, via a browser using this url http://localhost:8181/ords/demo/simpledriver_dv/2, we will receive a document similar to this one:

{"driverid":2,"driver_number":33,"code":"VER","driver_name":"Max Verstappen","dob":"1997-09-30T00:00:00","nationality":"Dutch","_metadata":{"etag":"45F4F82C9F8FACB5051543C0901D7F85","asof":"00000000004234A8"},"links":[{"rel":"self","href":"http://localhost:8181/ords/demo/simpledriver_dv/2"},{"rel":"describedby","href":"http://localhost:8181/ords/demo/metadata-catalog/simpledriver_dv/item"},{"rel":"collection","href":"http://localhost:8181/ords/demo/simpledriver_dv/"}]}

It is not formatted of course. We get all the information from the view, including the _metadata, and a couple of links, for instance to the entire collection.

To update the driver number, using a REST service, I used the Thunder Client extension for VSCode.
When we Send a request like this:

We will get a similar response.

The proof is of course checking the data in the database:

select *
from   drivers drv
where  drv.driverid = 2
/

DRIVERID DRIVER_NUMBER COD DRIVER_NAME      DOB        NATIONALITY
-------- ------------- --- ---------------- ---------- ----------------
      2             1 VER Max Verstappen   30-SEP-97  Dutch

JSON Relational Duality gives you the best of both worlds. Relational experts can work on the same data as Document database experts without learning a new trade. App developers will love Duality views as they can request any JSON document layout they want. In addition, database development becomes simpler as the database developers do not have to deal with time-consuming ORM mapping. Oracle made a massive leap over other Databases with this new feature.

The source code used in this article can be found at:
https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/dualityviews.sql

Also read my previous blogs about Oracle Database 23:

• Introduction blog series Oracle Database 23
• Developer Role
• Group by Alias
• If [Not] Exists 
• Table values constructor
• The Boolean data type
• New PL/SQL iterator constructs
• Annotations
• SQL Macros 
• Immutable tables
• SQL Domains
• Lock Free Reservation
   

Auteur: Patrick Barel

Terug naar overzicht

Deel

Facebook Twitter LinkedIn E-mail Whatsapp