21.10 Using the Converter, Default Behavior
Use of the JSON-to-duality converter with its default configuration-field
values (except for minFieldFrequency and minTypeFrequency)
is illustrated. In particular, configuration field useFlexFields is
true. The database objects needed to support the document sets are
inferred, and the SQL DDL code to construct them is generated.
Unlike the case in Migrating To Duality, Simplified Recipe, here we look at the
effect of nonzero (and non-default) values of minFieldFrequency and
minTypeFrequency, 25 and 15, respectively. The input document sets
are the same (student_tab, teacher_tab, and
course_tab), as are the names of the duality views generated
(student, teacher, and
course).
Here we again use the value of configuration field
useFlexFields, true, which means the tables underlying
duality views have flex columns. This allows the views to support some scalar fields whose
values don't consistently correspond to single SQL scalar data types.
Note:
For more information about flex columns, see:
Flex Columns, Beyond the Basics in JSON-Relational Duality Developer's Guide
The document sets in the examples here are very small. In order to
demonstrate the handling of outlier (high-entropy) fields, in examples here we use large
values for migrator configuration fields minFieldFrequency (value
25) and minTypeFrequency (value
15), instead of the default value of 5.
A field is an occurrence outlier for a given
document set if it occurs in less than minFieldFrequency percent of the
documents.
A field is a type outlier for a given document set
if any of its values occurs with a given type in less than
minTypeFrequency percent of the documents.
-
An occurrence-outlier field (a field that occurs rarely) is not mapped by the converter to any underlying column. If the converter produces flex columns (configuration field
useFlexFields=true, the default value), then the importer places an unmapped field in a flex column of a table underlying the duality view. If there are no flex columns then the importer reports an unmapped field in an import error log, and the field is not supported in the duality view. -
A type-outlier field (a field whose value is rarely of a different type than usual) is handled differently. Import tries to convert any values of a rare type to the expected type for the field. Unsuccessful conversion is reported in an import error log, and the field is not used in the duality view.
See JSON Configuration Fields Specifying
Migrator Parameters in JSON-Relational Duality
Developer's Guide for information about configuration fields
minFieldFrequency, minTypeFrequency, and
useFlexFields.
Example 21-18 INFER_SCHEMA and GENERATE_SCHEMA with useFlexFields = true
The code here to infer and generate the schema is the same as that in
the example "INFER_SCHEMA and GENERATE_SCHEMA with Zero Frequency Thresholds: No
Outliers" in Migrating To Duality, Simplified Recipe, except that (1)
the configuration-fields input argument to infer_schema includes
values for minFieldFrequency (25) and
minTypeFrequency (15) that suit our small
document sets. The value of useFlexFields is true,
the default value, so this gives us a good idea of the default converter
behavior.
See JSON Configuration Fields Specifying Migrator Parameters in JSON-Relational Duality Developer's Guide for the default behavior of other configuration fields.
DECLARE
er_schema JSON;
schema_sql CLOB;
BEGIN
er_schema :=
DBMS_JSON_DUALITY.infer_schema(
JSON('{"tableNames" : [ "STUDENT_TAB",
"TEACHER_TAB",
"COURSE_TAB"],
"viewNames" : [ "STUDENT",
"TEACHER",
"COURSE" ],
"minFieldFrequency" : 25,
"minTypeFrequency" : 15}'));
schema_sql := DBMS_JSON_DUALITY.generate_schema(er_schema);
EXECUTE IMMEDIATE schema_sql;
END;
/
The following example shows the JSON schema returned by function
DBMS_JSON_DUALITY.infer_schema:
Example 21-19 JSON Schema from INFER_SCHEMA for Duality Views: Default Behavior
{"tables" :
[ {"title" : "map_course_root_to_student_root",
"dbObject" : "map_course_root_to_student_root",
"type" : "object",
"dbObjectType" : "table",
"dbMapTable" : true,
"properties" : {"map_course_id" : {"sqlType" : "varchar2",
"maxLength" : 64,
"nullable" : false},
"map_student_id" : {"sqlType" : "number",
"nullable" : false}},
"required" : [ "map_course_id", "map_student_id" ],
"dbPrimaryKey" : [ "map_course_id",
"map_student_id"],
"dbForeignKey" : [ {"map_course_id" : {"dbObject" : "course_root",
"dbColumn" : "course_id"}},
{"map_student_id" : {"dbObject" : "student_root",
"dbColumn" : "student_id"}} ]},
{"title" : "teacher_root",
"dbObject" : "teacher_root",
"type" : "object",
"dbObjectType" : "table",
"properties" : {"_id" : {"sqlType" : "number", "nullable" : false},
"name" : {"sqlType" : "varchar2",
"maxLength" : 64,
"nullable" : true,
"unique" : false},
"salary" : {"sqlType" : "number",
"nullable" : true,
"unique" : false},
"department" : {"sqlType" : "varchar2",
"maxLength" : 64,
"nullable" : true,
"unique" : false},
"phone_number" : {"sqlType" : "json",
"nullable" : true,
"unique" : false}},
"required" : [ "_id" ],
"dbPrimaryKey" : [ "_id" ]},
{"title" : "course_root",
"dbObject" : "course_root",
"type" : "object",
"dbObjectType" : "table",
"properties" : {"name" : {"sqlType" : "varchar2",
"maxLength" : 64,
"nullable" : true,
"unique" : false},
"course_id" : {"sqlType" : "varchar2",
"maxLength" : 64,
"nullable" : false},
"credit_hours" : {"sqlType" : "json",
"nullable" : true,
"unique" : false},
"class_type" : {"sqlType" : "varchar2",
"maxLength" : 64,
"nullable" : true,
"unique" : false},
"avg_grade" : {"sqlType" : "json",
"nullable" : true,
"unique" : false},
"_id_teacher_root" : {"sqlType" : "number",
"nullable" : true,
"unique" : false}},
"required" : [ "course_id" ],
"dbPrimaryKey" : [ "course_id" ],
"dbForeignKey" : [ {"_id_teacher_root" : {"dbObject" : "teacher_root",
"dbColumn" : "_id"}} ]},
{"title" : "student_root",
"dbObject" : "student_root",
"type" : "object",
"dbObjectType" : "table",
"properties" : {"age" : {"sqlType" : "number",
"nullable" : true,
"unique" : false},
"name" : {"sqlType" : "varchar2",
"maxLength" : 64,
"nullable" : true,
"unique" : false},
"advisor_id" : {"sqlType" : "number",
"nullable" : true,
"unique" : false},
"student_id" : {"sqlType" : "number",
"nullable" : false},
"dorm_id" : {"sqlType" : "number",
"nullable" : true,
"unique" : false}},
"required" : [ "student_id" ],
"dbPrimaryKey" : [ "student_id" ],
"dbForeignKey" : [ {"advisor_id" : {"dbObject" : "teacher_root",
"dbColumn" : "_id"}},
{"dorm_id" : {"dbObject" : "student_dormitory",
"dbColumn" : "dorm_id"}} ]},
{"title" : "student_dormitory",
"dbObject" : "student_dormitory",
"type" : "object",
"dbObjectType" : "table",
"properties" : { "dorm_id" : {"sqlType" : "number",
"nullable" : false},
"dorm_name" : {"sqlType" : "varchar2",
"maxLength" : 64,
"nullable" : true,
"unique" : false}},
"required" : [ "dorm_id" ],
"dbPrimaryKey" : [ "dorm_id" ]} ],
"views" : [ {"title" : "STUDENT",
"dbObject" : "STUDENT",
"dbObjectType" : "dualityView",
"dbObjectProperties" : [ "insert", "update", "delete", "check" ],
"dbMappedTableObject" : "student_root",
"type" : "object",
"properties" :
{"_id" : {"type" : "number",
"dbAssigned" : true,
"dbFieldProperties" : [ "check" ],
"dbObject" : "student_root",
"dbColumn" : "student_id"},
"dbPrimaryKey" : [ "_id" ],
"age" : {"type" : [ "number",
"null" ],
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "student_root",
"dbColumn" : "age"},
"name" : {"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "student_root",
"dbColumn" : "name"},
"courses" :
{"type" : "array",
"items" : {"type" : "object",
"dbMappedTableObject" : "course_root",
"properties" :
{"dbPrimaryKey" : [ "ora$mapCourseId",
"ora$mapStudentId" ],
"ora$mapCourseId" :
{"type" : "string",
"maxLength" : 64,
"dbAssigned" : true,
"dbFieldProperties" : [ "check" ]},
"ora$mapStudentId" :
{"type" : "number",
"dbAssigned" : true,
"dbFieldProperties" : [ "check" ] },
"name" :
{"type" : [ "string",
"null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "course_root",
"dbColumn" : "name"},
"avgGrade" :
{"type" : [ "number",
"string",
"null" ],
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "course_root",
"dbColumn" : "avg_grade"},
"courseNumber" :
{"type" : "string",
"maxLength" : 64,
"dbFieldProperties" : [ "check" ],
"dbObject" : "course_root",
"dbColumn" : "course_id"}},
"required" : [ "ora$mapCourseId",
"ora$mapStudentId",
"courseNumber" ]}},
"advisorId" : {"type" : [ "number", "null" ],
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "student_root",
"dbColumn" : "advisor_id"},
"dormitory" : {"type" : "object",
"dbMappedTableObject" : "student_dormitory",
"properties" :
{"dormId" :
{"type" : "number",
"dbFieldProperties" : [ "check" ],
"dbObject" : "student_dormitory",
"dbColumn" : "dorm_id"},
"dormName" :
{"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "student_dormitory",
"dbColumn" : "dorm_name"}},
"required" : [ "dormId" ]},
"studentId" : {"dbFieldProperties" : [ "computed" ]}}},
{"title" : "COURSE",
"dbObject" : "COURSE",
"dbObjectType" : "dualityView",
"dbObjectProperties" : [ "insert", "update", "delete", "check" ],
"dbMappedTableObject" : "course_root",
"type" : "object",
"properties" :
{"_id" : { "type" : "string",
"maxLength" : 64,
"dbAssigned" : true,
"dbFieldProperties" : [ "check" ],
"dbObject" : "course_root",
"dbColumn" : "course_id"},
"dbPrimaryKey" : [ "_id" ],
"name" : {"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "course_root",
"dbColumn" : "name"},
"teacher" :
{"type" : "object",
"dbMappedTableObject" : "teacher_root",
"properties" :
{"name" : {"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "teacher_root",
"dbColumn" : "name"},
"teacherId" : {"type" : "number",
"dbFieldProperties" : [ "check" ],
"dbObject" : "teacher_root",
"dbColumn" : "_id"}},
"required" : [ "teacherId" ]},
"courseId" : {"dbFieldProperties" : [ "computed" ]},
"students" :
{"type" : "array",
"items" :
{"type" : "object",
"dbMappedTableObject" : "student_root",
"properties" :
{"dbPrimaryKey" : [ "ora$mapCourseId",
"ora$mapStudentId" ],
"ora$mapCourseId" : {"type" : "string",
"maxLength" : 64,
"dbAssigned" : true,
"dbFieldProperties" : [ "check" ]},
"ora$mapStudentId" : {"type" : "number",
"dbAssigned" : true,
"dbFieldProperties" : [ "check" ]},
"name" :
{"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "student_root",
"dbColumn" : "name"},
"studentId" : {"type" : "number",
"dbFieldProperties" : [ "check" ],
"dbObject" : "student_root",
"dbColumn" : "student_id"}},
"required" : [ "ora$mapCourseId",
"ora$mapStudentId",
"studentId" ]}},
"creditHours" :
{"type" : [ "number", "string", "null" ],
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "course_root",
"dbColumn" : "credit_hours"}}},
{"title" : "TEACHER",
"dbObject" : "TEACHER",
"dbObjectType" : "dualityView",
"dbObjectProperties" : [ "insert", "update", "delete", "check" ],
"dbMappedTableObject" : "teacher_root",
"type" : "object",
"properties" :
{"_id" : {"type" : "number",
"dbFieldProperties" : [ "check" ],
"dbObject" : "teacher_root",
"dbColumn" : "_id"},
"name" : {"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "teacher_root",
"dbColumn" : "name"},
"salary" : {"type" : [ "number", "null" ],
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "teacher_root",
"dbColumn" : "salary"},
"department" : {"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "teacher_root",
"dbColumn" : "department"},
"phoneNumber" :
{"type" : [ "string", "array", "null" ],
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "teacher_root",
"dbColumn" : "phone_number"},
"coursesTaught" :
{"type" : "array",
"items" :
{"type" : "object",
"dbMappedTableObject" : "course_root",
"properties" :
{"name" : {"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "course_root",
"dbColumn" : "name"},
"courseId" : {"type" : "string",
"maxLength" : 64,
"dbFieldProperties" : [ "check" ],
"dbObject" : "course_root",
"dbColumn" : "course_id"},
"classType" : {"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "course_root",
"dbColumn" : "class_type"}},
"required" : [ "courseId" ]}},
"studentsAdvised" :
{"type" : "array",
"items" :
{"type" : "object",
"dbMappedTableObject" : "student_root",
"properties" :
{"name" : {"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "student_root",
"dbColumn" : "name"},
"dormId" : {"type" : [ "number", "null" ],
"dbFieldProperties" : [ "update", "check" ],
"dbObject" : "student_root",
"dbColumn" : "dorm_id"},
"studentId" : {"type" : "number",
"dbFieldProperties" : [ "check" ],
"dbObject" : "student_root",
"dbColumn" : "student_id"}},
"required" : [ "studentId" ]}}}} ],
"configOptions" : {"outputFormat" : "executable",
"useFlexFields" : true}}
The differences here from the schema inferred when
minFieldFrequency and minTypeFrequency are zero
(see the example "JSON Schema from INFER_SCHEMA for Duality Views with No Outliers"
in Migrating To Duality, Simplified Recipe) are these:Foot 1
-
For the student table and view, column and field
agehave type number. -
For the course table and view, column
notesand fieldNotesare absent.
In the schema inferred when minFieldFrequency and
minTypeFrequency are zero, the notes column and field are
present, the age column has type json, and the age
field has type number-or-string.
So even before generating DDL code to create the duality views and their
tables, you can see from the output of infer_schema some of what to
expect for those two outlier fields. If you recall that there is a student document with
age = "Nineteen" then you already know that, on
import, that document won't have an age field.
The following example shows the DDL code produced by
generate_schema.
Example 21-20 DDL Code from GENERATE_SCHEMA with useFlexFields = true
Function DBMS_JSON_DUALITY.generate_schema, produces
the generated DDL code shown here if passed the JSON schema in the example "JSON
Schema from INFER_SCHEMA for Duality Views: Default Behavior" in Using the Converter, Default Behavior, which is returned
by function infer_schema (example "INFER_SCHEMA and
GENERATE_SCHEMA with useFlexFields = true" in Using the Converter, Default Behavior) as input.
Differences from the example "DDL Code from GENERATE_SCHEMA for No-Outlier Use Case" in Migrating To Duality, Simplified Recipe are as follows:
-
Column
student_root.agehas typenumberhere, not number-or-string. -
There is no column
course_root.notesto support fieldNotes. (Instead, the importer will place fieldNotesin flex columncourse_root.ora$course_flex.)
The duality-view definitions here use GraphQL syntax. Equivalent SQL duality-view definitions are shown in the example "SQL DDL Code for Duality-View Creations with useFlexFields = true" in Using the Converter, Default Behavior.
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE student_dormitory(
dorm_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
dorm_name varchar2(64),
ora$student_flex JSON(Object),
PRIMARY KEY(dorm_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE map_course_root_to_student_root(
map_course_id varchar2(64) DEFAULT ON NULL SYS_GUID(),
map_student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$student_flex JSON(Object),
ora$course_flex JSON(Object),
PRIMARY KEY(map_course_id,map_student_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_root(
age number,
name varchar2(64),
dorm_id number,
advisor_id number,
student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$student_flex JSON(Object),
ora$teacher_flex JSON(Object),
PRIMARY KEY(student_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_root(
"_id" number GENERATED BY DEFAULT ON NULL AS IDENTITY,
name varchar2(64),
salary number,
department varchar2(64),
phone_number json VALIDATE ''{"oneOf" : [{ "type" :"string"}, { "type" :"array"}]}'',
ora$teacher_flex JSON(Object),
ora$course_flex JSON(Object),
PRIMARY KEY("_id")
)';
EXECUTE IMMEDIATE 'CREATE TABLE course_root(
name varchar2(64),
avg_grade json VALIDATE ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
course_id varchar2(64) DEFAULT ON NULL SYS_GUID(),
class_type varchar2(64),
credit_hours json VALIDATE ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
"_id_teacher_root" number,
ora$teacher_flex JSON(Object),
ora$course_flex JSON(Object),
PRIMARY KEY(course_id)
)';
EXECUTE IMMEDIATE 'ALTER TABLE map_course_root_to_student_root
ADD CONSTRAINT fk_map_course_root_to_student_root_to_course_root
FOREIGN KEY (map_course_id) REFERENCES course_root(course_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE map_course_root_to_student_root
ADD CONSTRAINT fk_map_course_root_to_student_root_to_student_root
FOREIGN KEY (map_student_id) REFERENCES student_root(student_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_teacher_root
FOREIGN KEY (advisor_id) REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_student_dormitory
FOREIGN KEY (dorm_id) REFERENCES student_dormitory(dorm_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_teacher_root
FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS
fk_map_course_root_to_student_root_to_course_root_index
ON map_course_root_to_student_root(map_course_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS
fk_map_course_root_to_student_root_to_student_root_index
ON map_course_root_to_student_root(map_student_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS
fk_student_root_to_teacher_root_index
ON student_root(advisor_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS
fk_student_root_to_student_dormitory_index
ON student_root(dorm_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS
fk_course_root_to_teacher_root_index
ON course_root("_id_teacher_root")';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW STUDENT AS
student_root @insert @update @delete
{
_id : student_id
age
name
courses: map_course_root_to_student_root @insert @update @delete @array
{
ora$mapCourseId: map_course_id
ora$mapStudentId: map_student_id
ora$student_flex @flex
course_root @unnest @insert @update @object
{
name
avgGrade: avg_grade
courseNumber: course_id
}
}
advisorId:advisor_id
dormitory: student_dormitory @insert @update @object
{
dormId: dorm_id
dormName: dorm_name
ora$student_flex @flex
}
studentId @generated (path: "$._id")
ora$student_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
teacher_root @insert @update @delete
{
"_id"
name
salary
department
phoneNumber: phone_number
coursesTaught: course_root @insert @update @delete @array
{
name
courseId: course_id
classType: class_type
ora$teacher_flex @flex
}
studentsAdvised: student_root @insert @update @delete @array
{
name
dormId:dorm_id
studentId: student_id
ora$teacher_flex @flex
}
ora$teacher_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
course_root @insert @update @delete
{
_id : course_id
name
teacher: teacher_root @insert @update @object
{
name
teacherId: "_id"
ora$course_flex @flex
}
courseId @generated (path: "$._id")
students: map_course_root_to_student_root @insert @update @delete @array
{
ora$mapCourseId: map_course_id
ora$mapStudentId: map_student_id
ora$course_flex @flex
student_root @unnest @insert @update @object
{
name
studentId: student_id
}
}
creditHours: credit_hours
ora$course_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER INSERT_TRIGGER_STUDENT
BEFORE INSERT
ON STUDENT
FOR EACH ROW
DECLARE
inp_jsonobj json_object_t;
BEGIN
inp_jsonobj := json_object_t(:new.data);
IF NOT inp_jsonobj.has(''_id'')
THEN
inp_jsonobj.put(''_id'', inp_jsonobj.get(''studentId''));
:new.data := inp_jsonobj.to_json;
END IF;
END;';
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER INSERT_TRIGGER_COURSE
BEFORE INSERT
ON COURSE
FOR EACH ROW
DECLARE
inp_jsonobj json_object_t;
BEGIN
inp_jsonobj := json_object_t(:new.data);
IF NOT inp_jsonobj.has(''_id'')
THEN
inp_jsonobj.put(''_id'', inp_jsonobj.get(''courseId''));
:new.data := inp_jsonobj.to_json;
END IF;
END;';
END;
Besides creating the duality views and their underlying tables, the DDL code does the following as part of the default behavior:
-
For each duality view
<view-name>, each table that directly underlies the top-level fields of an object in the supported documents has a flex column namedora$<view-name>_flex(becauseuseFlexFieldswas implicitlytruefor the DDL generation). -
Tables
student_rootandteacher_roothave primary-key columnsstudent_idand_id,respectively. -
Table
course_roothas primary-key columncourse_id. Its column_id_teacher_rootis a foreign key to column_idof tableteacher_root, which is the primary key of that table. Tablecourse_roothas an index on its foreign-key column,_id_teacher_root. -
Table
map_course_root_to_student_rootis a mapping table between tablescourse_rootandstudent_root.-
Its primary key is a composite of its columns
map_course_idandmap_student_id. -
Its columns
map_course_idandmap_student_idare foreign keys to columnscourse_idandstudent_idin tablescourse_rootandstudent_root, respectively, which are the primary-key columns of those tables. -
It has indexes on its two foreign-key columns.
-
-
Views
courseandstudenteach have a field (courseIdandstudentId, respectively) whose value is not stored but is generated from the value of the view's field_id.This is because a duality view must have an
_idfield, which corresponds to the identifying columns of the root table that underlies it, but documents from the existing app instead have acourseIdorstudentIdfield. In viewscourseandstudentthose fields are always generated from field_id, so inserting a document stores their values in field_idinstead. (See Document-Identifier Field for Duality Views in JSON-Relational Duality Developer's Guide.) -
Views
courseandstudenteach have a before-insert trigger (insert_trigger_courseandinsert_trigger_student, respectively) that stores the value of an incomingcourseIdorstudentIdfield, respectively, in field_id. If the incoming document has no field_idat its top level yet, then the trigger (1) adds it and (2) gives it the value of fieldcourseIdorstudentId. (Importing usesINSERToperations, and these triggers fire just before such operations.)
Example 21-21 SQL DDL Code For Duality-View Creations with useFlexFields = true
For information, in case SQL is more familiar to you than GraphQL, this SQL DDL code is equivalent to the GraphQL duality-view creation code shown in the preceding example "DDL Code from GENERATE_SCHEMA with useFlexFields = true" .
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW STUDENT AS
SELECT JSON {'_id' : s.student_id,
'age' : s.age,
'name' : s.name,
'courses' :
[SELECT JSON {'ora$mapCourseId' : m.map_course_id,
'ora$mapStudentId' : m.map_student_id,
m.ora$student_flex AS FLEX,
UNNEST
(SELECT JSON {'name' : c.name,
'avgGrade' : c.avg_grade,
'courseNumber' : c.course_id}
FROM course_root c WITH INSERT UPDATE
WHERE c.course_id = m.map_course_id)}
FROM map_course_root_to_student_root m WITH INSERT UPDATE DELETE
WHERE s.student_id = m.map_student_id],
'advisorId' : s.advisor_id,
'dormitory' :
(SELECT JSON {'dormId' : sd.dorm_id,
'dormName' : sd.dorm_name,
sd.ora$student_flex AS FLEX}
FROM student_dormitory sd WITH INSERT UPDATE
WHERE s.dorm_id = sd.dorm_id),
'studentId' IS GENERATED USING PATH '$._id',
s.ora$student_flex AS FLEX
RETURNING JSON}
FROM student_root s WITH INSERT UPDATE DELETE;
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
SELECT JSON {'_id' : t."_id",
'name' : t.name,
'salary' : t.salary,
'department' : t.department,
'coursesTaught' :
[SELECT JSON {'name' : c.name,
'courseId' : c.course_id,
'classType' : c.class_type,
c.ora$teacher_flex AS FLEX}
FROM course_root c WITH INSERT UPDATE DELETE
WHERE c."_id_teacher_root" = t."_id"],
'studentsAdvised' :
[SELECT JSON {'name' : s.name,
'dormId' : s.dorm_id,
'studentId' : s.student_id,
s.ora$teacher_flex AS FLEX}
FROM student_root s WITH INSERT UPDATE DELETE
WHERE s.advisor_id = t."_id"],
t.ora$teacher_flex AS FLEX
RETURNING JSON}
FROM teacher_root t WITH INSERT UPDATE DELETE;
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
SELECT JSON {'_id' : c.course_id,
'name' : c.name,
'teacher' :
(SELECT JSON {'name' : t.name,
'teacherId' : t."_id",
t.ora$course_flex AS FLEX}
FROM teacher_root t WITH INSERT UPDATE
WHERE t."_id" = c."_id_teacher_root"),
'courseId' IS GENERATED USING PATH '$._id',
'students' :
[SELECT JSON {'ora$mapCourseId' : m.map_course_id,
'ora$mapStudentId' : m.map_student_id,
m.ora$course_flex AS FLEX,
UNNEST
(SELECT JSON {'name' : s.name,
'studentId' : s.student_id}
FROM student_root s WITH INSERT UPDATE
WHERE s.student_id = m.map_student_id)}
FROM map_course_root_to_student_root m WITH INSERT UPDATE DELETE
WHERE c.course_id = m.map_course_id],
'creditHours' : c.credit_hours,
c.ora$course_flex AS FLEX
RETURNING JSON}
FROM course_root c WITH INSERT UPDATE DELETE;When you generate the DDL code and then execute it, the duality views and their underlying tables are created.
After executing the DDL code, you run converter function
DBMS_JSON_DUALITY.validate_schema_report for each kind (student,
teacher, course) of input table and duality view, to validate the conversion.
Example 21-22 VALIDATE_SCHEMA_REPORT for Default Case (useFlexFields = true)
SELECT * FROM DBMS_JSON_DUALITY.validate_schema_report(
table_name => 'STUDENT_TAB',
view_name => 'STUDENT');
SELECT * FROM DBMS_JSON_DUALITY.validate_schema_report(
table_name => 'TEACHER_TAB',
view_name => 'TEACHER');
SELECT * FROM DBMS_JSON_DUALITY.validate_schema_report(
table_name => 'COURSE_TAB',
view_name => 'COURSE');
For the student data, an error is reported for the string value
("Nineteen") of field age in the document for
student Luis F. (studentId = 9) —
only a numeric or null value is allowed.
Function validate_schema_report places the anomalous
input document in column DATA of its output, and it places an entry
in column ERRORS of the same report row:
DATA
--------
{"studentId":9,"name":"Luis F.","age":"Nineteen","advisorId":101,"courses":[{"co
urseNumber":"CS101","name":"Algorithms","avgGrade":75},{"courseNumber":"MATH102"
,"name":"Calculus","avgGrade":95},{"courseNumber":"MATH103","name":"Advanced Alg
ebra","avgGrade":82}],"dormitory":{"dormId":201,"dormName":"ABC"}}ERRORS
----------
[{"schemaPath":"$","instancePath":"$","code":"JZN-00501","error":"JSON schema va
lidation failed"},{"schemaPath":"$.properties","instancePath":"$","code":"JZN-00
514","error":"invalid properties: 'age'"},{"schemaPath":"$.properties.age.type",
"instancePath":"$.age","code":"JZN-00504","error":"invalid type found, actual: s
tring, expected one of: number, null"},{"schemaPath":"$.properties.age.extendedT
ype","instancePath":"$.age","code":"JZN-00504","error":"invalid type found, actu
al: string, expected one of: number, null"}]For that document, the importer will try, and fail, to convert
the string value of "Nineteen" in the input data to a number. It
will log that type failure as an error. If the input string value were instead
"19" then the importer would be able to convert the value to
the number 19 and store it as such.
There are no errors reported for the teacher data.
No errors are reported for the course data either. In particular, there
is no error for occurrence-outlier field Notes of the
Algebra course (MATH101). This is because
useFlexFields is true creates flex columns to
the underlying tables. As it does with all input fields that aren't mapped to
columns, the importer will place field Notes in a flex
column, so it will be supported by the course duality view.
At this point, before importing you could choose to change the
age field in the student input document for Luis J., to give a
number value of 19 instead of the string value
"Nineteen". Besides fixing problematic data, before importing you
might sometimes want to modify/edit the DDL scripts, to change the conversion behavior
or the names of the views, tables, or indexes to be created.
-
DBMS_JSON_DUALITY in Oracle Database PL/SQL Packages and Types Reference for information about subprograms
generate_schema, infer_schema, andvalidate_import_report -
VALIDATE_REPORT Function in Oracle Database PL/SQL Packages and Types Reference for information about function
DBMS_JSON_SCHEMA.validate_report
Parent topic: Migrating From JSON To Duality
Footnote Legend
Footnote 1: All of these fields also have type null, which is generally the case for fields that don't correspond to identifying columns.