Using SQL Developer for VS Code to build Duality Views

I apologize in advance – this is a long post. But I promise it’ll be worth it, as it’s a fun topic!

I need to supply a JSON object to my developer friend, sourced from the database, that has our DEPARTMENTS, including useful information like…

department id
name of the dept
list of employees
with their names
and their compensation information

With 23ai, I could whip up this SQL or GraphQL…

💡 Tip: The GraphQL is easier to both type and read/understand!

Or I could just point-and-click my way thru it in VS Code, using SQL Dev extension v24.2

We updated our VS Code extension for SQL Developer yesterday, and the big new feature is a nice – build it, edit it, test it, document it – suite of tools for working with JSON Relational Duality Views in 23ai.

So instead of ‘hacking my way through the ‘new and exciting syntax,’ I can let the tools help me getting a running head start!

Step 1: Connect to a 23ai database

This is a new 23ai feature, so you’ll need a new 23ai database to see it. You have options:

Oracle Cloud (OCI Base & Autonomous), including Always Free Autonomous
Exadata or Oracle Database Appliance, check the regular places
23ai Free, Container, VirtualBox, & Linux or Windows Media

Once you’re connected, look for…

Step 2: Right click on the Duality Views tree node

We’re going to choose the ‘Duality Builder.’

Step 3: Find your ‘driving’ table, and add it

Click the ‘Add Root Table’ button, it’s the first one on the 2nd set of button. This will cause a slider to pop-out on the right hand side of the screen.

We’re going to select ‘DEPARTMENTS’

Step 4: Picks some columns, decide what will be ‘allowed’

I’m going to add ‘DEPARTMENT_NAME’, and you’ll notice that the DEPARTMENT_ID field is selected by default, as we need a PK value to supply the mandatory value for the _ID JSON attribute on the object.

In the upper left, we have a diagram panel. We can zoom in and out, we can drag objects around. We can select or unselect columns we want included in our DV.

Below the diagram we have features, nestled together.

Definition: We have by default the GraphQL syntax for creating our DV, but I I uncheck the ‘GraphQL Form’ box, we get the ‘regular DDL’, like so –

Test Query: This might be our MVP (most valuable part) !! We can try out the DV as we are pointing and clicking around. And by ‘try it out,’ I mean we can query what the DV would return if we had created it…without actually creating it.

Yeah, it’s a pretty boring object so far, but a few things of note. Let’s look at the ‘Test Query’ code that’s being executed.

SELECT JSON {
        '_id' : d.DEPARTMENT_ID,
        'departmentName' : d.DEPARTMENT_NAME,
        'employees' : 
            [ SELECT JSON {
                'employeeId' : e.EMPLOYEE_ID,
                'name' :  {
                    'first' : e.FIRST_NAME,
                    'last' : e.LAST_NAME
                },
                'startedOn' : e.HIRE_DATE,
                'compensation' :  {
                    'salary' : e.SALARY,
                    'comm' : e.COMMISSION_PCT
                }
            }
            FROM EMPLOYEES e
             WHERE  e.DEPARTMENT_ID = d.DEPARTMENT_ID 
         ]
    } AS dv_data 
    FROM DEPARTMENTS d
;

All Duality Views have a _id attribute that uniquely identify the object, hence the query on DEPARTMENTS.DEPARTMENT_ID and casting that to ‘_id’, and then we have a nested call to employees again casting as JSON…anyways, we can see what the Duality View will look like before actually creating it.

As the data comes back, I can right-click on a row, and ask for the Single Record View (SRV). The SRV will auto-format the JSON object so it’s human-readable.

As we make changes to our Duality View ‘design,’ we’ll be coming back to the ‘Test Query’ feature, regularly.

DV Properties (Upper Right): Here we can select the DV and set properties, or select an underlying attribute, and do the same. We’ll look at this in a moment.

Step 5: Adding Employees

We’re going to be able to do this, because we have a foreign key constraint on EMPLOYEES that follows DEPARTMENT_ID back to the DEPARTMENTS table.

So when we click the ‘Add Child Tables’ button…

Sometimes a GIF is worth…5 or 7 pictures!

Once our new table and columns have been added, we can do some fun things with the attributes up in the properties area.

Nested Key Names and Aliases

Let’s group the names together as a separate json object, and let’s also group both commission_pct and salary together as another.

And let’s also alias the attributes to something more sensible.

I select ‘SALARY’ or ‘COMMISSION_PCT’ on the right, and then on the ‘Nested Key Name,’ I input a value of ‘compensation.’ I can then see on the generated code below, that those two attributes now appear as part of the compensation object in the JSON definition.

Let’s do something similar for ‘FIRST_NAME’ and ‘LAST_NAME,’ and put them under a nested key name simply called ‘name,’ and then we can also alias those as ‘first’ and ‘last,’ respectively.

We’ve made a bunch of changes, so let’s test it, again.

In case that was hard to follow, here’s a snippet of the array of department objects…

{
    "_id": 80,
    "departmentName": "Sales",
    "employees": [
        {
            "employeeId": 145,
            "name": {
                "first": "John",
                "last": "Russell"
            },
            "compensation": {
                "salary": 14000,
                "comm": 0.4
            }
        },
        {
            "employeeId": 146,
            "name": {
                "first": "Karen",
                "last": "Partners"
            },
            "compensation": {
                "salary": 13500,
                "comm": 0.3
            }
        },...

Nothing has been created, until you hit the CREATE button!

Two important notes here, please read this so you don’t lose any work!

if you don’t mind others in the database ‘seeing your work,’ just hit the Create Duality View button
if you want more time to design and tinker, use the ‘Save to File’ button

Since this is a webview component in VS Code, you WILL lose your work if you close this dialog, as it’s not a ‘regular file,’ which comes with all the normal workspace protections, notably the file will be there when you re-open VS Code tomorrow.

The files are saved with a ‘dvb’ extension if you’re having trouble finding it…

What else can we do?

Well, a LOT.

Once the Duality View has been created, and we’re querying it … and stuff we can –

Create REST APIs

Our Oracle REST Data Services ‘AutoREST’ feature supports JSON Relational Duality Views! If the Views are ‘updatable,’ then you can PUT, POST, DELETE, & PATCH to your heart’s delight!