14.9.2.2 Exercising Duality View AutoREST Endpoint

Test an AutoREST duality view endpoint to see the database defaults and checks it enforces.

AutoREST is the simplest way to enable a REST API when the built-in defaulting and checks the database performs meet your needs. By default, a duality view's inherits its underlying tables':

• defaulted column values
• referential integrity checks
• check constraint enforcement, and
• table trigger logic.

It also only allows the DML operations you enabled via @insert, @update, or @delete annotations. These native database features extend to your AutoREST-enabled API for the duality view.

Applying these rules to action items, consider the following example. Say you define table-level checks:

• On the ACTION_ITEMS table:
  • check constraint STATUS IN ('OPEN','COMPLETE')
  • default value on STATUS of 'OPEN'
• On the ACTION_ITEM_TEAM_MEMBERS table:
  • check constraint ROLE IN ('LEAD','MEMBER')
  • default value on ROLE of 'MEMBER'

These defaults and checks are enforced.

Consider another example where you include a non-existent staff member name Leopoldo when POST-ing a new action item to the AutoREST-enabled duality view endpoint:

{
    "name": "New Product Promotion Deck",
    "team": [
        {
            "role": "LEAD",
            "name": "Usha"
        },
        {"name": "Leopoldo"}
    ]
}

You get the error:

ORA-40937: Cannot insert into table 'STAFF' in JSON Relational Duality View
           'ACTION_ITEMS_DV': Missing INSERT annotation

The duality view's natural behavior while processing action team members explains this result:

• If action team staff member id is supplied, use it,
• Otherwise, lookup staff member ID in STAFF using unique key NAME if it exists
• Otherwise, insert new staff member to get new ID to reference.

However, the ACTION_ITEMS_DV duality view definition does not allow inserts into STAFF.