JSON To Duality Migrator Components: Converter and Importer

21.3 JSON To Duality Migrator Components: Converter and Importer

The JSON To Duality Migrator has two components: the converter and the importer. Their PL/SQL subprograms are described.

Converter: Create the database objects needed to support the original JSON documents: duality views and their underlying tables and indexes.
Importer: Import Oracle Database JSON-type document sets that correspond to the original external documents into the duality views created by the converter.

The converter is composed of these PL/SQL functions in package DBMS_JSON_DUALITY:

infer_schema infers a JSON schema that represents all of the input document sets.
- Input: A JSON object whose members specify configuration parameters for the inference operation — see JSON Configuration Fields Specifying Migrator Parameters.
- Output: a JSON Schema document that specifies the inferred relational schema. If no such schema can be found then an error is raised saying that the converter can't create duality views that correspond to the input document sets.
generate_schema produces the DDL code to create the required database objects for each duality view.
- Input: the JSON schema returned by function infer_schema.
- Output: DDL scripts to create the needed database objects.
infer_and_generate_schema performs both operations.
- Input: same as infer_schema.
- Output: same as generate_schema.
validate_schema_report checks the adequacy of the database objects to be created by the DDL code generated by function generate_schema. It reports on the validity of the input JSON documents according to the duality views to be created, identifying documents that can't be supported, with reasons why not. These are the documents that fail validation against a JSON schema for the duality views.
- Input:
  - table_owner_name: The name of the database schema (user) that owns table table_name.
  - table_name: The name of an input table of JSON documents.
  - view_owner_name: The name of the database schema (user) that owns view view_name.
  - view_name: The name of the corresponding duality view to be created by the DDL code generated by generate_schema.
- Output: A table of validation failures for input JSON documents, one row per failed document (no rows means that all documents are valid). A row has CLOB columns DATA (the invalid document) and ERRORS (a JSON array of errors, with the same format as field errors of function DBMS_JSON_SCHEMA.validate_report).

The importer is composed of these PL/SQL subprograms in package DBMS_JSON_DUALITY:

Procedure import_all populates all duality views created by the converter with the documents from the corresponding input document sets (more precisely, with the relational data needed to support such documents). In an error-log table it reports an error for each document that couldn't be imported. (Only the first such error encountered per document is reported.)
- Input: A JSON object whose members specify configuration parameters for the import operation — see JSON Configuration Fields Specifying Migrator Parameters .
- Output: (1) Duality views with their underlying tables filled with the relational data that supports the same documents. (2) Error-log tables that report any documents that could not be imported.
Procedure import populates a single duality view with the documents from the corresponding input document set. Its error logging is the same as that of procedure import_all.
- Input: (1) An Oracle Database JSON document set, that is, a table with a single JSON-type column containing documents of a given kind. (2) The name of a duality view to populate. (3, optional) Name of the table owner. (4, optional) Name of the view owner. (5, optional) Name of the owner of the error log table. (6, optional) Name of the error log table. (7, optional) Reject limit value, whose meaning is the same as configuration field rejectLimit: the maximum number of errors that can be logged (importing is ended when this limit is exceeded).
- Output: (1) A duality view with its underlying tables filled with the relational data that supports the same documents. (2) An error-log table that reports any documents that could not be imported.
Tip:

In general, use procedure import_all, not procedure import. Perform a single-view import only when it's unlikely to interfere with the data in other duality views.

Using import to import multiple single views separately can be problematic because of view interdependencies. For example:
- You can't use import to populate view student before view teacher, because root table student_root has foreign key column advisor_id, which requires the corresponding teacher data to already exist.
- On the other hand, you can't use import to populate view teacher before view student, because teacher documents have a dormId field in their embedded student objects, and the corresponding column, dorm_id, is a foreign key from table student_root to table student_dormitory. This requires that table student_dormitory be populated before view teacher. And that table can only be populated by importing existing student documents.
Function validate_import_report reports successfully imported JSON documents that are invalid.

Each row in the output table corresponds to a validation failure for a JSON document that was imported (no rows means all documents are valid). A row has CLOB columns DATA (the invalid document) and ERRORS (a JSON array of errors, each having the format of a JSON Patch document that compares an input document and the corresponding imported document in the duality view). See JavaScript Object Notation (JSON) Patch, IETF RFC6902 for the error format.
- table_owner_name: The name of the database schema (user) that owns table table_name.
- table_name: The name of an input table of JSON documents.
- view_owner_name: The name of the database schema (user) that owns view view_name.
- view_name: The name of the corresponding duality view to be populated with the documents in table table_name.

Note that import error logging reports only on a document that couldn't be imported, and import validation reports only on documents that were successfully imported (but that are problematic in some way).

Related Topics

Flex Columns, Beyond the Basics