Monday, May 15, 2023

Precheck Constraints using JSON Schema in Oracle Database 23c

JSON Schema, Oracle Database 23c, Oracle Database Career, Oracle Database Skills, Oracle Database Jobs, Oracle Database Prep, Oracle Database Preparation, Oracle Database Tutorial and Materials, Oracle Database Guides, Oracle Database Certification

In Oracle database 23c we can use the PRECHECK keyword to mark check constraints as being validated externally by an application. The DBMS_JSON_SCHEMA package allows us to describe objects in the form of a JSON schema, which can be passed to an application to define how the data should be presented to the database so it is processed without errors.

◉ Basic Check Constraints


The PRECHECK keyword indicates a check constraint is prechecked by the application before the data it is sent to the database. In its default form, the check constraint is still validated in the database.

In this example we create a table with a check constraint set to PRECHECK. We see invalid JSON data still causes a constraint violation with the PRECHECK option.

drop table if exists t1 purge;

create table t1 (
  id         number,
  valid      varchar2(1),
  constraint t1_pk primary key (id),
  constraint valid_chk check (valid in ('Y','N')) precheck
);

insert into t1 (id, valid) values (1, 'B');
*
ERROR at line 1:
ORA-02290: check constraint (TESTUSER1.VALID_CHK) violated

SQL>

We use the ALTER TABLE command to set the constraint to DISABLE PRECHECK, which means we are totally reliant on the application to validate the data. This means we can insert invalid data if we fail to manually validate it.

alter table t1 modify constraint valid_chk disable precheck;

insert into t1 (id, valid) values (1, 'B');

1 row created.

SQL>

Remember, we can only enable the constraint if the underlying data doesn't violate it, unless we use the ENABLE NOVALIDATE option. In the following example we use the ALTER TABLE command to cycle through various constraint settings.

select status, validated, precheck
from   user_constraints
where  constraint_name = 'VALID_CHK';

STATUS   VALIDATED     PRECHECK
-------- ------------- --------
DISABLED NOT VALIDATED PRECHECK

SQL>

alter table t1 modify constraint valid_chk enable novalidate precheck;

select status, validated, precheck
from   user_constraints
where  constraint_name = 'VALID_CHK';

STATUS   VALIDATED     PRECHECK
-------- ------------- --------
ENABLED  NOT VALIDATED PRECHECK

SQL>

alter table t1 modify constraint valid_chk enable noprecheck;
                                 *
ERROR at line 1:
ORA-02293: cannot validate (TESTUSER1.VALID_CHK) - check constraint
violated

SQL>

truncate table t1;
alter table t1 modify constraint valid_chk enable noprecheck;

select status, validated, precheck
from   user_constraints
where  constraint_name = 'VALID_CHK';

STATUS   VALIDATED     PRECHECK
-------- ------------- --------
ENABLED  VALIDATED

SQL>

◉ JSON Schema Check Constraints


We can use the PRECHECK option for check constraints that validate JSON data against a JSON Schema.

In this example we create a table using the JSON data type, and use a check constraint to validate the JSON data conforms to a specific JSON schema. We are using the PRECHECK option, but even when we insert valid JSON data we get a JSON Schema violation if it doesn't conform to the JSON Schema defintion.

drop table if exists t2 purge;

create table t2 (
  id         number,
  json_data  json,
  constraint t2_pk primary key (id),
  constraint json_data_chk check (json_data is json validate '{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100}},
  "required"   : ["fruit", "quantity"]
}') precheck
);

insert into t2 (id, json_data) values (2, json('{"fruit":"apple"}'));
            *
ERROR at line 1:
ORA-40875: JSON schema validation error

SQL>

Here we use the ALTER TABLE command to set the constraint to DISABLE PRECHECK, which means we are totally reliant on the application to validate the data matches the JSON Schema. This means we can add valid JSON, which doesn't conform to the JSON Schema definition.

alter table t2 modify constraint json_data_chk disable precheck;

insert into t2 (id, json_data) values (3, json('{"fruit":"apple"}'));

1 row created.

SQL>

We cycle through the various constraint states as before.

select status, validated, precheck
from   user_constraints
where  constraint_name = 'JSON_DATA_CHK';

STATUS   VALIDATED     PRECHECK
-------- ------------- --------
DISABLED NOT VALIDATED PRECHECK

SQL>

alter table t2 modify constraint json_data_chk enable novalidate precheck;

select status, validated, precheck
from   user_constraints
where  constraint_name = 'JSON_DATA_CHK';

STATUS   VALIDATED     PRECHECK
-------- ------------- --------
ENABLED  NOT VALIDATED PRECHECK

SQL>

alter table t2 modify constraint json_data_chk enable noprecheck;
                                 *
ERROR at line 1:
ORA-02293: cannot validate (TESTUSER1.JSON_DATA_CHK) - check constraint violated

SQL>

truncate table t2;
alter table t2 modify constraint json_data_chk enable noprecheck;

select status, validated, precheck
from   user_constraints
where  constraint_name = 'JSON_DATA_CHK';

STATUS   VALIDATED     PRECHECK
-------- ------------- --------
ENABLED  VALIDATED

SQL>

◉ DBMS_JSON_SCHEMA.DESCRIBE


The DESCRIBE function in the DBMS_JSON_SCHEMA package generates a JSON schema describing the referenced object. It supports a variety of objects listed here. The description of the object can be used by an application to validate the data prior to sending it to the database.

We describe the T1 table created earlier. The output includes the table definition, and the check constraint for the VALID column. We've used the JSON_SERIALIZE function to pretty print the output, but this is not necessary.

set long 1000000 pagesize 100
column json_schema format a80

select json_serialize(
         dbms_json_schema.describe(
           object_name => 'T1',
           owner_name  => 'TESTUSER1')
         pretty) as json_schema;

JSON_SCHEMA
--------------------------------------------------------------------------------
{
  "title" : "T1",
  "dbObject" : "TESTUSER1.T1",
  "type" : "object",
  "dbObjectType" : "table",
  "properties" :
  {
    "ID" :
    {
      "extendedType" : "number"
    },
    "VALID" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 1,
      "allOf" :
      [
        {
          "enum" :
          [
            "Y",
            "N"
          ]
        }
      ]
    }
  },
  "required" :
  [
    "ID"
  ],
  "dbPrimaryKey" :
  [
    "ID"
  ]
}

SQL>

In this example we restrict the description to the VALID column.

set long 1000000 pagesize 100
column json_schema format a80

select json_serialize(
         dbms_json_schema.describe(
           object_name => 'T1',
           owner_name  => 'TESTUSER1',
           column_name => 'VALID')
         pretty) as json_schema;

JSON_SCHEMA
--------------------------------------------------------------------------------
{
  "dbColumn" : "VALID",
  "extendedType" :
  [
    "null",
    "string"
  ],
  "maxLength" : 1,
  "allOf" :
  [
    {
      "enum" :
      [
        "Y",
        "N"
      ]
    }
  ]
}

SQL>

We describe the T2 table created earlier. The output includes the table definition, and the check constraint for the JSON_DATA column, which itself includes the JSON schema defintion.

set long 1000000 pagesize 100
column json_schema format a80

select json_serialize(
         dbms_json_schema.describe(
           object_name => 'T2',
           owner_name  => 'TESTUSER1')
         pretty) as json_schema;

JSON_SCHEMA
--------------------------------------------------------------------------------
{
  "title" : "T2",
  "dbObject" : "TESTUSER1.T2",
  "type" : "object",
  "dbObjectType" : "table",
  "properties" :
  {
    "ID" :
    {
      "extendedType" : "number"
    },
    "JSON_DATA" :
    {
      "allOf" :
      [
        {
          "type" : "object",
          "properties" :
          {
            "fruit" :
            {
              "type" : "string",
              "minLength" : 1,
              "maxLength" : 10
            },
            "quantity" :
            {
              "type" : "number",
              "minimum" : 0,
              "maximum" : 100
            }
          },
          "required" :
          [
            "fruit",
            "quantity"
          ]
        }
      ]
    }
  },
  "required" :
  [
    "ID"
  ],
  "dbPrimaryKey" :
  [
    "ID"
  ]
}

SQL>

In this example we restrict the description to the JSON_DATA column.

set long 1000000 pagesize 100
column json_schema format a80

select json_serialize(
         dbms_json_schema.describe(
           object_name => 'T2',
           owner_name  => 'TESTUSER1',
           column_name => 'JSON_DATA')
         pretty) as json_schema;

JSON_SCHEMA
--------------------------------------------------------------------------------
{
  "dbColumn" : "JSON_DATA",
  "allOf" :
  [
    {
      "type" : "object",
      "properties" :
      {
        "fruit" :
        {
          "type" : "string",
          "minLength" : 1,
          "maxLength" : 10
        },
        "quantity" :
        {
          "type" : "number",
          "minimum" : 0,
          "maximum" : 100
        }
      },
      "required" :
      [
        "fruit",
        "quantity"
      ]
    }
  ]
}

SQL>

In this example we describe a JSON-relational duality view created in the article here.

set long 1000000 pagesize 200
column json_schema format a80

select json_serialize(
         dbms_json_schema.describe(
           object_name => 'DEPARTMENT_DV',
           owner_name  => 'TESTUSER1')
         pretty) as json_schema;

JSON_SCHEMA
--------------------------------------------------------------------------------
{
  "title" : "DEPARTMENT_DV",
  "dbObject" : "TESTUSER1.DEPARTMENT_DV",
  "dbObjectType" : "dualityView",
  "dbObjectProperties" :
  [
    "insertable",
    "updatable",
    "deletable",
    "check"
  ],
  "type" : "object",
  "properties" :
  {
    "_metadata" :
    {
      "etag" :
      {
        "extendedType" : "string",
        "maxLength" : 200
      },
      "asof" :
      {
        "extendedType" : "string",
        "maxLength" : 20
      }
    },
    "location" :
    {
      "extendedType" :
      [
        "string",
        "null"
      ],
      "maxLength" : 13,
      "dbAnnotations" :
      [
        "update",
        "check"
      ]
    },
    "departmentName" :
    {
      "extendedType" :
      [
        "string",
        "null"
      ],
      "maxLength" : 14,
      "dbAnnotations" :
      [
        "update",
        "check"
      ]
    },
    "departmentNumber" :
    {
      "extendedType" : "number",
      "sqlPrecision" : 2,
      "sqlScale" : 0,
      "dbAnnotations" :
      [
        "check"
      ]
    },
    "employees" :
    {
      "type" : "array",
      "items" :
      {
        "type" : "object",
        "properties" :
        {
          "job" :
          {
            "extendedType" :
            [
              "string",
              "null"
            ],
            "maxLength" : 9,
            "dbAnnotations" :
            [
              "update",
              "check"
            ]
          },
          "salary" :
          {
            "extendedType" :
            [
              "number",
              "null"
            ],
            "sqlPrecision" : 7,
            "sqlScale" : 2,
            "dbAnnotations" :
            [
              "update",
              "check"
            ]
          },
          "employeeNumber" :
          {
            "extendedType" : "number",
            "sqlPrecision" : 4,
            "sqlScale" : 0,
            "dbAnnotations" :
            [
              "check"
            ]
          },
          "employeeName" :
          {
            "extendedType" :
            [
              "string",
              "null"
            ],
            "maxLength" : 10,
            "dbAnnotations" :
            [
              "update",
              "check"
            ]
          }
        },
        "required" :
        [
          "employeeNumber"
        ]
      }
    }
  },
  "required" :
  [
    "departmentNumber"
  ]
}

SQL>

Source: oracle-base.com

Related Posts

0 comments:

Post a Comment