21.9 二面性への移行、簡易レシピ
入力フィールドがまれに出現するのか、まれに使用される型があるのかを無視することで、JSONリレーショナル二面性への移行が容易になります。このような外れ値のケースに対処すると、移行プロセスが複雑になる可能性があります。
しかし、このようなケースに対処することで、より詳細な正規化が可能となり、バグを表す可能性のあるデータの異常を見つけるのに役立ちます。このトピックでは、より単純なアプローチについて説明します。後続の各トピックでは、マイグレータをより深く理解して構成するのに役立つ詳細について説明します。
デフォルトでは、コンバータは、ドキュメント・セットのドキュメントの5%未満で出現するフィールドを出現の外れ値として扱い、ドキュメントの5%未満で特定の型を持つフィールド出現を型出現の外れ値または型の外れ値として扱います。これらのしきい値の値はそれぞれ、構成フィールドminFieldFrequencyおよびminTypeFrequencyを使用して変更できます。
外れ値のレポートおよび処理を示すために、学生-教師-コースの例では、ドキュメント・セットが小規模であるので、通常、minFieldFrequencyには25%、minTypeFrequencyには15%を使用します。ただし、このトピックで使用される簡易レシピでは、両方ともゼロ・パーセントに設定するため、外れ値とみなされるフィールドはありません。
この2つのしきい値をゼロに設定する以外に、マイグレータのデフォルトの動作がここに示されています。この簡易レシピ(外れ値なし以外はデフォルトの動作)は、アプリケーションの移行のたびに始めるのに悪い方法ではありませんまた、多くの場合、最終的には必要なものになります。
最小頻度しきい値をゼロ・パーセントに設定することで、最大限の正規化を犠牲にして、移行する入力データを可能なかぎりそのまま受け入れるようにコンバータを構成します。学生-教師-コースの入力データには、最終的に外れ値として扱われるフィールドがいくつか含まれていますが、このトピックではそれらを特別に処理するようなことはありません。
通常、マイグレータのマニュアルの他の部分では外れ値として扱われるフィールドは、このトピックでは次のように処理されます:
-
MATH101(Algebra)のコースのドキュメントにおけるNotesの出現は、ドキュメントの1つのみ(20%)で出現していても、削除されません。 -
Luis F.の学生のドキュメントにおける文字列値"Nineteen"のageの出現は、その型が他の9つのドキュメント(90%)でのageの出現(数値)と一致するように、数値19に変換されません。また、この出現についてスキーマ推測検証エラーもレポートされません。
かわりに、このような(別の状況では問題があるとみなされる可能性がある)入力データは、単にそのまま保持されます。
重要なのは、移行によって明らかにされる可能性のある問題は外れ値フィールドのみではないということです。ここに示した簡易レシピを使用しても、インポータでエラーが発生する可能性はあります。そのよい例は、互いに矛盾しており、入力データを修正せずに調整することができなくなっている2つのドキュメント・セットです。たとえば、コースのドキュメントでは、Natalie C.がコースMATH101を教えていると記述され、教師のドキュメントではAbdul J.が教えていると記述されている場合です。「マイグレータ構成のみでは修正できないエラー」を参照してください。(マイグレータは、データを移行しない場合でも、このようなデータの一貫性の問題を検出するのに役立ちます。)
アプリケーションで特定のデータが異常であるかどうかは、その使用状況に応じて、ユーザーのみが知っています。たとえば、学生のageフィールド(通常、値は数値)に文字列"Nineteen"が1回出現するなど、フィールドのまれな型が正常か異常かを知っているのはユーザーのみです。入力データを最大限に尊重することが、ここで簡易レシピを使用して検討されるユースケースです。これは、重要なことの大部分をすぐに確認できるため、ドキュメント・セットの移行における最初のステップとして一般的に使用するアプローチでもあります。
移行の出発点は、Oracle Database転送表に格納されている3つの入力ドキュメント・セットです(「コンバータを使用する前に(1): データベース・ドキュメント・セットの作成」 を参照)。入力ドキュメントは、その項と(より簡潔に)「学校経営の例(マイグレータの入力ドキュメント)」の両方に示されています。
最初にPL/SQLファンクションDBMS_JSON_DUALITY.infer_schema、続いてファンクションDBMS_JSON_DUALITY.generate_schemaを使用して、(1)二面性ビュー、(2)その基礎となる表、(3)表に対する外部キー制約および索引、(4)ドキュメント・セットにまだ存在していない二面性ビュー用のドキュメント識別子フィールド_idを作成するトリガーを作成するSQLデータ定義(DDL)コードを生成します。DDLコードにより、入力データにまだ含まれていないため、最上位レベルのドキュメント識別子フィールド_idも追加されます。
次に、生成されたDDLコードを実行し、それらのデータベース・オブジェクトを作成します。
例21-9 頻度しきい値がゼロのINFER_SCHEMAおよびGENERATE_SCHEMA: 外れ値なし
この例では、PL/SQLファンクションDBMS_JSON_DUALITY.infer_schemaは、推測された二面性ビューとその基礎となる表および索引を表すJSONスキーマをJSON型変数er_schemaで返し、この変数がPL/SQLファンクションDBMS_JSON_DUALITY.generate_schemaに渡されます。generate_schemaからの出力(それらのデータベース・オブジェクトを作成するためのSQL DDLコード)は、EXECUTE IMMEDIATEを使用して起動されます。
構成フィールドminFieldFrequencyおよびminTypeFrequencyはいずれも、ファンクションinfer_schemaによるスキーマ推測のためにゼロに設定されます。つまり、入力JSONデータのフィールドは外れ値とみなされません。
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" : 0,
"minTypeFrequency" : 0}'));
schema_sql := DBMS_JSON_DUALITY.generate_schema(er_schema);
EXECUTE IMMEDIATE schema_sql;
END;
/
ファンクションinfer_schemaは、二面性ビューとその表を記述するJSONスキーマを生成します。この場合、スキーマは、すべての入力データ・フィールドが二面性ビューでサポートされていることを示しています。
例21-10 外れ値がない二面性ビューのINFER_SCHEMAからのJSONスキーマ
{"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},
"notes" : {"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" : "json",
"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",
"string",
"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"},
"Notes" : {"type" : [ "string", "null" ],
"maxLength" : 64,
"dbFieldProperties" : ["update", "check" ],
"dbObject" : "course_root",
"dbColumn" : "notes"},
"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}}
一般的な所見:
-
スキーマには2つの部分があります: (1)二面性ビューの基礎となる表の指定(フィールド
tables)と(2)二面性ビュー自体の指定(フィールドviews)。 -
表内の列のSQLデータ型は、フィールド
sqlTypeで指定されます。たとえば、列departmentのSQL型はVARCHAR2です。これは、ドキュメント・フィールドdepartmentの値が常にJSON文字列であるためです。 -
JSONデータ型は、入力ドキュメントの対応するフィールド(phoneNumber、creditHours、avgGradeおよびage)が混合型であるため、列phone_number、credit_hours、avg_gradeおよびageに使用されます。このようなフィールドに使用できる単一のSQLスカラー型はありません。 -
表
map_course_root_to_student_rootは、表course_rootとstudent_rootの間のマッピング表です。 -
識別列(サンプル・データの主キー列)を除く脚注1すべての列に
nullable=trueというフラグが付けられています。つまり、値が(SQL)NULLになる可能性があることを意味します。NULL値可能列に対応するフィールドは、(1)特定のドキュメントに存在する必要はなく、(2)存在する場合は、(JSON)null値を持つことができます。
このスキーマにはさらに多くの情報があります。ここでは、学生のビューとそのドキュメントのスキーマを例に、いくつかの点を説明します。
- ドキュメント識別子フィールド
_idの場合、スキーマ・フィールドdbAssignedは、各二面性ビューのドキュメントに_idが自動的に追加される(入力データには存在しない)ことを示しています。また、スキーマ・フィールドdbColumnは、_id値が列student_idに格納されることを示しています。 -
他にも、スキーマ・フィールド
dbAssignedは、フィールドora$mapCourseIdおよびora$mapStudentIdも学生のドキュメントに自動的に追加されることを示しています。 -
スキーマ・フィールド
dbPrimaryKeyの(シングルトン配列)値は、ドキュメント・フィールド_idが二面性ビューの唯一の識別(主キー)列に対応することを示しています。フィールド_idはドキュメント識別子です。 -
学生の入力ドキュメントでは、フィールド
studentIdがドキュメント識別子です。その最上位フィールドは学生の二面性ビューでサポートされていますが、その値はビューによって生成され、格納されません。スキーマ・フィールドdbFieldPropertiesの配列値の文字列"computed"が、これを示しています。(その値は、実際には列student_idの_id値から取得されます。) -
スキーマ・フィールド
dbMappedTableObjectは、(1)表student_rootが学生のビューの基礎となるルート表であること、(2)表course_rootがフィールドcoursesの配列値の基礎となること、表student_dormitoryがフィールドdormitoryのオブジェクト値のフィールドの基礎となることを示しています。
ファンクションgenerate_schemaは、JSONスキーマ(ファンクションinfer_schemaによって生成されるJSONスキーマなど)を入力として受け入れます。具体的には、infer_schemaが生成するJSONスキーマを編集して、generate_schemaの動作に影響を与えることができることを意味します。これを実行するには、手動で編集するか、SQL/JSONファンクションjson_transformを使用します。JSONスキーマの編集方法の例を次に示します:
例21-11 JSON_TRANSFORMを使用した推測されたJSONスキーマの編集
DBMS_JSON_DUALITY.infer_schemaによって返されるJSONスキーマを変更する1つの例として、列のmaxLength値を64から100に変更します。
ここでは、例「頻度しきい値がゼロのINFER_SCHEMAおよびGENERATE_SCHEMA: 外れ値なし」に示すように、PL/SQL変数er_schemaの値がDBMS_JSON_DUALITY.infer_schemaによって返されるスキーマであること前提としています。このjson_transformコードは、そのスキーマを変更し、学生のドキュメントのnameフィールドの最大長を100にして、変換された値を変数er_schemaに保存し直します。更新された変数は、DBMS_JSON_DUALITY.generate_schemaに渡すことができます。
SELECT json_transform(
er_schema,
SET '$.tables[3].properties.name.maxLength' = 100)
INTO er_schema FROM dual;このSET操作の左側は、SQL/JSONパス式です。表student_rootを指定するスキーマは、JSONスキーマ全体の配列tablesの4番目脚注2のエントリです。学生のドキュメントの最上位フィールドnameは、スキーマ・フィールドpropertiesの子として指定され、フィールドmaxLengthはフィールドnameの子です。(『Oracle Database JSON開発者ガイド』の「Oracle SQLファンクションJSON_TRANSFORM」を参照してください。)
「二面性への移行、簡易レシピ」の例「GENERATE_SCHEMAからのDDLコード(外れ値なしのユースケース)」は、例「頻度しきい値がゼロのINFER_SCHEMAおよびGENERATE_SCHEMA: 外れ値なし」でファンクションgenerate_schemaによって生成されたDDLコードを示しており、すべての入力データ・フィールドがサポートされることを示しています。
例21-12 GENERATE_SCHEMAからのDDLコード(外れ値なしのユースケース)
この例は、このトピックの他の例を基にしています。ファンクションDBMS_JSON_DUALITY.generate_schemaは、ファンクションinfer_schema (例「頻度しきい値がゼロのINFER_SCHEMAおよびGENERATE_SCHEMA: 外れ値なし」を参照)によって返される例「外れ値がない二面性ビューのINFER_SCHEMAからのJSONスキーマ」のJSONスキーマが渡され、これを入力として使用した場合、ここに示す生成されたDDLコードを生成します。
(かわりに、例「JSON_TRANSFORMを使用した推測されたJSONスキーマの編集」での変更の結果となるスキーマが渡された場合、ここでの唯一の変更点は、列student_root.nameのmaxLengthが64ではなく100であることです。)
構成フィールドoutputFormatの値は"executable" (デフォルト)であるため、生成されたDDLコードは文にEXECUTE IMMEDIATEを使用します。
DDLコードによって作成されるトリガーは、ビューの各ドキュメントに最上位フィールド_idを追加し、いずれの場合にも、対応する主キー・フィールドの値を指定します。たとえば、学生のドキュメントの場合、追加された_idフィールドに入力フィールドstudentIdの値を指定します。
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 json VALIDATE ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
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$course_flex JSON(Object),
ora$teacher_flex JSON(Object),
PRIMARY KEY("_id")
)';
EXECUTE IMMEDIATE 'CREATE TABLE course_root(
name varchar2(64),
notes 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$course_flex JSON(Object),
ora$teacher_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 COURSE AS
course_root @insert @update @delete
{
_id : course_id
name
Notes: notes
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 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 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;
DDLコードを実行すると変換は完了しますが、PL/SQLファンクションDBMS_JSON_DUALITY.validate_schema_reportを使用して検証する必要があります。これは、各二面性ビューにエラーがないこと(no rows selected)を示します。つまり、検証の失敗がないことを意味します。二面性ビューとそれらが表すリレーショナル・スキーマには問題ありません。
例21-13 VALIDATE_SCHEMA_REPORT (外れ値なしのユースケース)
SELECT * FROM DBMS_JSON_DUALITY.validate_schema_report(
table_name => 'TEACHER_TAB',
view_name => 'TEACHER');no rows selectedSELECT * FROM DBMS_JSON_DUALITY.validate_schema_report(
table_name => 'COURSE_TAB',
view_name => 'COURSE');no rows selectedSELECT * FROM DBMS_JSON_DUALITY.validate_schema_report(
table_name => 'STUDENT_TAB',
view_name => 'STUDENT');no rows selected二面性ビューは空のままで、入力データはまだ移入されていません。次に、(1)ビューのエラー・ログを作成し、(2)プロシージャimport_allを使用して、一時転送表*_TABからビューにデータをインポートします。
例21-14 エラー・ログの作成(外れ値なしのユースケース)
BEGIN
DBMS_ERRLOG.create_error_log(dml_table_name => 'COURSE',
err_log_table_name => 'COURSE_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'TEACHER',
err_log_table_name => 'TEACHER_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'STUDENT',
err_log_table_name => 'STUDENT_ERR_LOG',
skip_unsupported => TRUE);
END;
/エラー・ロギングでは、インポートできないドキュメントのみがレポートされます(また、特定のドキュメントで最初に発生したエラーのみがレポートされます)。
例21-15 ドキュメント・セットのインポート(外れ値なしのユースケース)
BEGIN
DBMS_JSON_DUALITY.import_all(
JSON('{"tableNames" : [ "STUDENT_TAB",
"TEACHER_TAB",
"COURSE_TAB" ],
"viewNames" : [ "STUDENT",
"TEACHER",
"COURSE" ],
"errorLog" : [ "STUDENT_ERR_LOG",
"TEACHER_ERR_LOG",
"COURSE_ERR_LOG" ]}'));
END;
/
インポートが完了し、二面性ビューが移入されます。
例21-16 インポートからのエラー・ログの確認(外れ値なしのユースケース)
エラー・ログが空で、インポート・エラーがないことを示しています。インポートされなかったドキュメントはありません。
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;no rows selectedSELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;no rows selectedSELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;no rows selected次に、DBMS_JSON_DUALITY.validate_import_reportを使用して、正常にインポートされたドキュメントの問題についてレポートします。この場合、何もレポートされません(no rows selected)。つまり、このような問題はありません。
例21-17 VALIDATE_IMPORT_REPORT (外れ値なしのユースケース)
SELECT * FROM DBMS_JSON_DUALITY.validate_import_report(
table_name => 'TEACHER_TAB',
view_name => 'TEACHER');no rows selectedSELECT * FROM DBMS_JSON_DUALITY.validate_import_report(
table_name => 'TEACHER_TAB',
view_name => 'TEACHER');no rows selectedSELECT * FROM DBMS_JSON_DUALITY.validate_import_report(
table_name => 'TEACHER_TAB',
view_name => 'TEACHER');no rows selectedノート:
validate_import_reportによってレポートされる可能性のある問題の例として、正常にインポートされたドキュメント間の矛盾があります。たとえば、コースのドキュメントでは、MATH101コース(Algebra)の教師がNatalie C.であると記述され、教師のドキュメントでは、そのコースの教師がAbdul J.であると記述されている場合、それらのドキュメントは矛盾します。インポート検証レポートは、Abdul J.の教師のドキュメントからMATH101を削除し、Natalie C.の教師のドキュメントに追加するなどして、ドキュメントを変更することで問題を解消するJSONパッチ・レシピを提示します。その特定の調整が望ましいものであるかどうかはわかりません。より適切なデータの修正として、かわりにMATH101のコースのドキュメントを変更してAbdul J.を教師として記述することが可能です。どの内容の修正が最適かを知っているのは、ユーザーのみです。
次のように、SQLファンクションjson_serializeを使用して、結果の二面性ビューでサポートされているドキュメント・コレクションを整形出力できます:
SELECT json_serialize(data PRETTY) FROM student;-
ドキュメント識別子フィールド
_id。この値は、ビューの基礎となるルート表の識別列に対応します。(値は通常、入力データ識別子フィールドと同じです。たとえば、学生のドキュメントの場合、フィールド_idはフィールドstudentIdと同じ値です。)『JSONリレーショナル二面性開発者ガイド』の「二面性ビューのドキュメント識別子フィールド」を参照してください。 -
ドキュメント処理フィールド
_metadata。『JSONリレーショナル二面性開発者ガイド』の「二面性ビューの作成」を参照してください。 -
マッピング表の列の識別用に指定されたフィールド(
ora$mapCourseIdなど)。「JSON-To-Dualityコンバータ: 機能」を参照してください。
これらの違いは想定されたものです。「JSON-To-Dualityコンバータ: 機能」を参照してください。
学生の二面性ビューでサポートされているドキュメントを次に示します:
{"_id" : 1,
"_metadata" : {"etag" : "39BA872C7E20186761BDD47B8AF40E3D",
"asof" : "000000000043EF3F"},
"age" : 20,
"name" : "Donald P.",
"courses" : [ {"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 1,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"},
{"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 1,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"},
{"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 1,
"name" : "Algebra",
"avgGrade" : 90,
"courseNumber" : "MATH101"} ],
"advisorId" : 102,
"dormitory" : {"dormId" : 201, "dormName" : "ABC"},
"studentId" : 1}SELECT json_serialize(data PRETTY) FROM student;JSON_SERIALIZE(DATAPRETTY)
--------------------------
{
"_id" : 1,
"_metadata" :
{
"etag" : "39BA872C7E20186761BDD47B8AF40E3D",
"asof" : "0000000000461E3D"
},
"age" : 20,
"name" : "Donald P.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 1,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 1,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 1,
"name" : "Algebra",
"avgGrade" : 90,
"courseNumber" : "MATH101"
}
],
"advisorId" : 102,
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC"
},
"studentId" : 1
}
{
"_id" : 2,
"_metadata" :
{
"etag" : "65B5DD1BE7B819306F2735F325E26400",
"asof" : "0000000000461E3D"
},
"age" : 21,
"name" : "Elena H.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 2,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 2,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 2,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
}
],
"advisorId" : 103,
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ"
},
"studentId" : 2
}
{
"_id" : 3,
"_metadata" :
{
"etag" : "E5AE58B21076D06FBA05010F0E1BEF21",
"asof" : "0000000000461E3D"
},
"age" : 20,
"name" : "Francis K.",
"courses" :
[
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 3,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 103,
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE"
},
"studentId" : 3
}
{
"_id" : 4,
"_metadata" :
{
"etag" : "D3B57FC478449FA24E123432C9D38673",
"asof" : "0000000000461E3D"
},
"age" : 19,
"name" : "Georgia D.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 4,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 4,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 4,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 203,
"dormName" : "LMN"
},
"studentId" : 4
}
{
"_id" : 5,
"_metadata" :
{
"etag" : "3FA71878EA5F02343CD62BC97F4C078E",
"asof" : "0000000000461E3D"
},
"age" : 21,
"name" : "Hye E.",
"courses" :
[
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 5,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 5,
"name" : "Algebra",
"avgGrade" : 90,
"courseNumber" : "MATH101"
}
],
"advisorId" : 103,
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC"
},
"studentId" : 5
}
{
"_id" : 6,
"_metadata" :
{
"etag" : "6F06B3DFCAEB4CF71669FDA9263B3236",
"asof" : "0000000000461E3D"
},
"age" : 21,
"name" : "Ileana D.",
"courses" :
[
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 6,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 102,
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI"
},
"studentId" : 6
}
{
"_id" : 7,
"_metadata" :
{
"etag" : "6A44A0B63DEC99978D98813B9D7C1D07",
"asof" : "0000000000461E3D"
},
"age" : 20,
"name" : "Jatin S.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 7,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 7,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE"
},
"studentId" : 7
}
{
"_id" : 8,
"_metadata" :
{
"etag" : "0B254C00DBCAA2E59DE30377138BD004",
"asof" : "0000000000461E3D"
},
"age" : 21,
"name" : "Katie H.",
"courses" :
[
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 8,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 8,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 102,
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI"
},
"studentId" : 8
}
{
"_id" : 9,
"_metadata" :
{
"etag" : "32D58F0278F226E26A5D4039A01D1288",
"asof" : "0000000000461E3D"
},
"age" : "Nineteen",
"name" : "Luis F.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 9,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 9,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 9,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC"
},
"studentId" : 9
}
{
"_id" : 10,
"_metadata" :
{
"etag" : "979816C4FD15DC805007B9FF7D822168",
"asof" : "0000000000461E3D"
},
"age" : 20,
"name" : "Ming L.",
"courses" :
[
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 10,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ"
},
"studentId" : 10
}
10 rows selected.入力ドキュメント・セットのドキュメント・セットと比較するために、二面性ビューでサポートされているドキュメント・セットを記述するデータ・ガイドJSONスキーマを作成することもできます。その作成方法は入力表のデータ・ガイドの作成方法(「コンバータを使用する前に(2): データ・ガイドJSONスキーマの作成(オプション)」の「入力ドキュメント・セット用のJSONデータ・ガイドの作成」を参照)と同じですが、入力データは、入力転送表student_tab、teacher_tabおよびcourse_tabではなく、二面性ビューstudent、teacherおよびcourseから選択されます。
ドキュメント(入力およびビューでサポートされている)の各種類のスキーマは同じです。ただし、次は除きます:
-
ドキュメント識別子フィールドとドキュメント処理フィールドの
_idおよび_metadataが二面性ビューのスキーマに追加されます。 -
ora$mapCourseIdやora$mapStudentIdなどのフィールドが二面性ビューのスキーマに追加されます。これらはマッピング表の列を識別します。 -
フィールド
o:last_analyzedの日付が異なります。これらは、データ・ガイドの作成日時を記録したにすぎません。
フィールドo:last_analyzedを除き、これらは、(1)元の入力ドキュメントおよび転送表ドキュメント(一方)と(2)二面性ビューでサポートされているドキュメント(他方)の間に存在する違いとして前述したのと同じ違いです。これは一般的なヒントを示しています:
ヒント:
ドキュメント・セットを比較するためのショートカットとしてドキュメント・セットをモデル化するJSONスキーマを比較できます。JSONスキーマには記述するドキュメント内の情報がすべて含まれているわけではありませんが、構造と型指定の違いを際立たせることができるため、優れた俯瞰的視点をもたらすプロキシとして機能します。
関連項目:
-
『Oracle Database JSON開発者ガイド』の「Oracle SQLファンクションJSON_TRANSFORM」
-
サブプログラム
generate_schema、infer_schema、import_all、validate_import_reportおよびvalidate_schema_reportの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』の「DBMS_JSON_DUALITY」を参照してください -
ファンクション
DBMS_JSON_SCHEMA.validate_reportの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』の「VALIDATE_REPORTファンクション」を参照してください。 -
プロシージャ
DBMS_ERRLOG.create_error_logの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』の「DBMS_ERRLOG」を参照してください
親トピック: JSONから二面性への移行
脚注一覧
脚注1: 主キー列の値をNULLにすることはできません。脚注2: JSON配列の索引付けはゼロベースです。