Remember our friendly Human Resources (HR) schema? And in that schema, remember our EMPLOYEES table? And it has that awesome self-referential key constraint, tying the manager_ID back to an existing employee_ID?
No?
It looks like this –
What if I wanted to work with this ’employee’ as an object or entity in say, my Java or Typescript code? And maybe I’d like to interact with this via an API of the REST persuasion?
That’s pretty easy with our new 23ai Database feature, and I’ve talked about that before once or twice.
Let’s create a DV for just the EMPLOYEE
And the tricksy part, we’re going to have an attribute or object in there, that represents the manager. I want more than their employee_ID information, I WANT THEIR NAME!
Like…this:
So if I were get employee 101, via the REST API, that would look like this –
So not only do I know that Neena reports to employee 100, I know that employee 100 is ‘Steven King.’
Cool.
The Duality View SQL/DDL code
CREATE OR REPLACE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW peeps_with_boss AS
employees @nocheck {
_id : employee_id,
firstName : first_name,
lastName : last_name,
salary,
boss : employees @nocheck @link (from : ["MANAGER_ID"]) {
boss_id : employee_id,
boss_name : employees @nest {
firstName : first_name,
lastName : last_name
}
}
};For the moment we need to specify the ’employees’ source on the boss_name object, but this will get simpler in an upcoming 23AI database release.
You can find more examples in Developer’s Guide in the Oracle Docs.
A sneak peek –
You can write this code, or you can use our upcoming visual builder in VS Code to construct or edit these Duality Views, that will looks like this –
General idea, add your base table or view, add additional views, set the attributes you want, and see the resulting SQL or GraphQL syntax to create those. You can even ‘test’ the DV before you create it!
The OpenAPI spec for our REST APIs
This can be found via your ORDS server at an address that looks like this –
https://yourserver.com/ords/schema/open-adpi-catalog/duality_view_name/
You could sling the output to your Swagger tools, or you can get to this client directly from your REST API editor in SQL Developer Web.
The GUI:
The code:
{
"openapi":"3.0.0",
"info":{
"title":"ORDS generated API for PEEPS_WITH_BOSS",
"version":"1.0.0"
},
"servers":[
{
"url":"http://localhost:8080/ords/hr/peeps_with_boss/"
}
],
"paths":{
"/":{
"get":{
"description":"Retrieve records from PEEPS_WITH_BOSS",
"responses":{
"200":{
"description":"The queried record.",
"content":{
"application/json":{
"schema":{
"type":"object",
"properties":{
"items":{
"type":"array",
"items":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
}
}
}
}
}
},
"parameters":[
{
"name":"q",
"in":"query",
"schema":{
"type":"string"
},
"description":"JSON QBE parameter."
}
]
},
"patch":{
"description":"PEEPS_WITH_BOSS",
"responses":{
"200":{
"description":"Patched successfully.",
"content":{
"application/json":{
"schema":{
"type":"object",
"properties":{
"rowsUpdated":{
"type":"number"
}
}
}
}
}
}
},
"parameters":[
{
"name":"q",
"in":"query",
"schema":{
"type":"string"
},
"description":"JSON QBE parameter."
}
],
"requestBody":{
"content":{
"application/merge-patch+json; charset=utf-8":{
"schema":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
}
}
},
"delete":{
"description":"Remove a record from PEEPS_WITH_BOSS",
"responses":{
"204":{
"description":"Deleted result."
}
},
"parameters":[
{
"name":"q",
"in":"query",
"schema":{
"type":"string"
},
"description":"JSON QBE parameter."
}
]
},
"post":{
"description":"Create a new record on PEEPS_WITH_BOSS",
"responses":{
"201":{
"description":"The successfully created record.",
"content":{
"application/json":{
"schema":{
"type":"object",
"properties":{
}
}
}
}
}
},
"parameters":[
],
"requestBody":{
"content":{
"application/json":{
"schema":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
}
}
}
},
"/batchload":{
"post":{
"description":"Create new records on PEEPS_WITH_BOSS",
"responses":{
"200":{
"description":"The status of the processed records.",
"content":{
"application/json":{
"schema":{
"type":"object",
"properties":{
}
}
}
}
}
},
"parameters":[
],
"requestBody":{
"content":{
"application/json":{
"schema":{
"type":"array",
"items":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
},
"application/json; boundary=NL":{
"schema":{
"description":"JSON items separated by new lines."
}
}
}
}
}
},
"/{id}":{
"get":{
"description":"Retrieve a record from PEEPS_WITH_BOSS",
"responses":{
"200":{
"description":"The queried record.",
"content":{
"application/json":{
"schema":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
}
}
},
"parameters":[
{
"name":"id",
"in":"path",
"required":true,
"schema":{
"type":"string",
"pattern":"^[^/]+$"
},
"description":"Primary key(s): boss_id,_id"
}
]
},
"put":{
"description":"Create or update a record on PEEPS_WITH_BOSS",
"responses":{
"200":{
"description":"The successfully updated record.",
"content":{
"application/json":{
"schema":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
}
},
"201":{
"description":"The successfully created record.",
"content":{
"application/json":{
"schema":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
}
}
},
"parameters":[
{
"name":"id",
"in":"path",
"required":true,
"schema":{
"type":"string",
"pattern":"^[^/]+$"
},
"description":"Primary key(s): boss_id,_id"
}
],
"requestBody":{
"content":{
"application/json":{
"schema":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
}
}
},
"delete":{
"description":"Remove a record from PEEPS_WITH_BOSS",
"responses":{
"204":{
"description":"Deleted result."
}
},
"parameters":[
{
"name":"id",
"in":"path",
"required":true,
"schema":{
"type":"string",
"pattern":"^[^/]+$"
},
"description":"Primary key(s): boss_id,_id"
}
]
},
"patch":{
"description":"PEEPS_WITH_BOSS",
"responses":{
"200":{
"description":"Patched successfully.",
"content":{
"application/json":{
"schema":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
}
}
},
"parameters":[
{
"name":"id",
"in":"path",
"required":true,
"schema":{
"type":"string",
"pattern":"^[^/]+$"
},
"description":"Primary key(s): boss_id,_id"
}
],
"requestBody":{
"content":{
"application/merge-patch+json; charset=utf-8":{
"schema":{
"$ref":"#/components/schemas/peeps_with_boss_ITEM"
}
}
}
}
}
}
},
"components":{
"schemas":{
"peeps_with_boss_ITEM":{
"properties":{
"_metadata":{
"properties":{
"etag":{
"type":"string",
"maxLength":200
},
"asof":{
"type":"string",
"maxLength":20
}
}
},
"lastName":{
"type":"string",
"maxLength":25
},
"firstName":{
"type":"string",
"maxLength":20,
"nullable":true
},
"_id":{
"type":"number",
"x-ords-primary-key":"true"
},
"salary":{
"type":"number",
"nullable":true
},
"boss":{
"type":"object",
"properties":{
"boss_id":{
"type":"number",
"x-ords-primary-key":"true"
},
"boss_name":{
"type":"object",
"properties":{
"firstName":{
"type":"string",
"maxLength":20,
"nullable":true
},
"lastName":{
"type":"string",
"maxLength":25
}
}
}
}
}
}
}
}
}
}
