Merge pull request #1181 from utPLSQL/feature/1114_support_native_json_21c

lwasylow · web-flow · commit ab1acfa6de10 · 2022-02-06T16:20:45.000Z
Support native JSON data type of Oracle Database 21c
diff --git a/.github/workflows/build.yml b/.github/workflows/build.yml
@@ -153,6 +153,7 @@ jobs:
 
       - name: SonarCloud Scan
         id: sonar
+        if: ${{ matrix.db_version_name == '21xe' }}
         uses: SonarSource/sonarcloud-github-action@master
         env:
           GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}  # Needed to get PR information, if any
diff --git a/docs/userguide/expectations.md b/docs/userguide/expectations.md
@@ -1749,18 +1749,23 @@ FAILURE
 
 # Comparing Json objects
 
-utPLSQL is capable of comparing json data-types **on Oracle 12.2 and above**.
+utPLSQL is capable of comparing json data-types of `json_element_t` **on Oracle 12.2 and above**, and also `json` **on Oracle 21 and above**
+
+**Note:**
+> Whenever a database is upgraded to compatible version the utPLSQL needs to be reinstalled to pick up json changes. E.g. upgrade from 18c to 21c to enable `json` type compare. 
+
 
 ### Notes on comparison of json data
 
 - Json data can contain objects, scalar or arrays.
 - During comparison of json objects the order doesn't matter.
 - During comparison of json arrays the index of element is taken into account
 - To compare json you have to make sure its type of  `json_element_t` or its subtypes
+- From version 21 and above a native `json` type is supported.
 
 
 
-Compare JSON example:
+Compare JSON example using `json_element_t`:
 ```sql
 declare
   l_expected json_element_t;
@@ -1842,7 +1847,7 @@ FAILURE
   at "anonymous block", line 59
 ```
 
-Comparing parts of JSON example:
+Comparing parts of JSON example using `json_element_t` subtypes:
 ```sql
 declare
   l_actual         json_object_t;
diff --git a/source/api/ut.pkb b/source/api/ut.pkb
@@ -98,6 +98,11 @@ create or replace package body ut is
     return ut_expectation_json(ut_data_value_json(a_actual), a_message);
   end;
   
+  function expect(a_actual in json , a_message varchar2 := null) return ut_expectation_json is
+  begin
+    return ut_expectation_json(ut_data_value_json(a_actual), a_message);
+  end;  
+  
   procedure fail(a_message in varchar2) is
   begin
     ut_expectation_processor.report_failure(a_message);
diff --git a/source/api/ut.pks b/source/api/ut.pks
@@ -47,6 +47,8 @@ create or replace package ut authid current_user as
 
   function expect(a_actual in json_element_t , a_message varchar2 := null) return ut_expectation_json;
 
+  function expect(a_actual in json , a_message varchar2 := null) return ut_expectation_json;
+
   procedure fail(a_message in varchar2);
 
   function run(
diff --git a/source/expectations/data_values/ut_compound_data_helper.pkb b/source/expectations/data_values/ut_compound_data_helper.pkb
@@ -785,6 +785,15 @@ create or replace package body ut_compound_data_helper is
     return l_diffs;
   end;
   
+  function get_json_object(a_json_t json) return json_element_t is
+    l_obj json_element_t;
+  begin
+     $if dbms_db_version.version >= 21 $then
+       l_obj := case when a_json_t is null then cast (null as json_element_t ) else json_element_t.parse(json_query(a_json_t, '$' returning clob)) end;
+     $end
+	 return l_obj;
+  end;
+  
 begin
   g_anytype_name_map(dbms_types.typecode_date)             := 'DATE';
   g_anytype_name_map(dbms_types.typecode_number)           := 'NUMBER';
diff --git a/source/expectations/data_values/ut_compound_data_helper.pks b/source/expectations/data_values/ut_compound_data_helper.pks
@@ -134,5 +134,7 @@ create or replace package ut_compound_data_helper authid definer is
   
   function get_json_diffs_type(a_diff_id raw) return tt_json_diff_type_tab;
   
+  function get_json_object(a_json_t json) return json_element_t;
+  
 end;
 /
diff --git a/source/expectations/data_values/ut_data_value_json.tpb b/source/expectations/data_values/ut_data_value_json.tpb
@@ -32,6 +32,13 @@ create or replace type body ut_data_value_json as
     return;
   end;
 
+  constructor function ut_data_value_json(self in out nocopy ut_data_value_json, a_value json) return self as result is
+    l_value json_element_t := ut_compound_data_helper.get_json_object(a_value);
+  begin 
+    init(l_value);
+    return;
+  end;
+
   overriding member function is_null return boolean is
   begin
     return (ut_utils.int_to_boolean(self.is_data_null));
@@ -140,7 +147,7 @@ create or replace type body ut_data_value_json as
       l_result :=
         case
           when ut_compound_data_helper.insert_json_diffs(
-            l_diff_id, self.json_tree.json_tree_info, l_other.json_tree.json_tree_info
+            l_diff_id, l_other.json_tree.json_tree_info, self.json_tree.json_tree_info
           ) > 0 then 1
           else 0
         end;
diff --git a/source/expectations/data_values/ut_data_value_json.tps b/source/expectations/data_values/ut_data_value_json.tps
@@ -19,6 +19,7 @@ create or replace type ut_data_value_json under ut_compound_data_value(
   json_tree      ut_json_tree_details,
   member procedure init (self in out nocopy ut_data_value_json, a_value json_element_t),
   constructor function ut_data_value_json(self in out nocopy ut_data_value_json, a_value json_element_t) return self as result,
+  constructor function ut_data_value_json(self in out nocopy ut_data_value_json, a_value json) return self as result,
   overriding member function is_null return boolean,
   overriding member function is_empty return boolean,
   overriding member function to_string return varchar2,
diff --git a/source/expectations/json_objects_specs.sql b/source/expectations/json_objects_specs.sql
@@ -1,8 +1,18 @@
 BEGIN
 
-  $if dbms_db_version.version = 12 and dbms_db_version.release >= 2 or dbms_db_version.version > 12 $then
+  $if dbms_db_version.version >= 21 $then
     dbms_output.put_line('Object exists , dont install');
-  $else
+  $elsif dbms_db_version.version = 12 and dbms_db_version.release >= 2 or ( dbms_db_version.version > 12 and dbms_db_version.version < 21 ) $then
+    dbms_output.put_line('Installing json structures specs for native json.');
+    execute immediate  q'[create or replace TYPE JSON FORCE AUTHID CURRENT_USER AS OBJECT(
+   dummyobjt NUMBER
+) NOT FINAL NOT INSTANTIABLE;]';   
+  $else  
+    dbms_output.put_line('Installing json structures specs for native json.');
+    execute immediate  q'[create or replace TYPE JSON FORCE AUTHID CURRENT_USER AS OBJECT(
+   dummyobjt NUMBER
+) NOT FINAL NOT INSTANTIABLE;]';   
+  
     dbms_output.put_line('Installing json structures specs.');
     execute immediate  q'[create or replace TYPE JSON_Element_T FORCE AUTHID CURRENT_USER AS OBJECT(
    dummyobjt NUMBER,
diff --git a/source/expectations/matchers/ut_equal.tpb b/source/expectations/matchers/ut_equal.tpb
@@ -158,6 +158,12 @@ create or replace type body ut_equal as
     return;
   end;
 
+  constructor function ut_equal(self in out nocopy ut_equal, a_expected json, a_nulls_are_equal boolean := null) return self as result is
+  begin
+    init(ut_data_value_json(a_expected), a_nulls_are_equal);
+    return;
+  end;
+
   member function include(a_items varchar2) return ut_equal is
     l_result ut_equal := self;
   begin
diff --git a/source/expectations/matchers/ut_equal.tps b/source/expectations/matchers/ut_equal.tps
@@ -42,6 +42,7 @@ create or replace type ut_equal force under ut_comparison_matcher(
   constructor function ut_equal(self in out nocopy ut_equal, a_expected yminterval_unconstrained, a_nulls_are_equal boolean := null) return self as result,
   constructor function ut_equal(self in out nocopy ut_equal, a_expected dsinterval_unconstrained, a_nulls_are_equal boolean := null) return self as result,
   constructor function ut_equal(self in out nocopy ut_equal, a_expected json_element_t, a_nulls_are_equal boolean := null) return self as result,
+  constructor function ut_equal(self in out nocopy ut_equal, a_expected json, a_nulls_are_equal boolean := null) return self as result,  
   member function include(a_items varchar2) return ut_equal,
   member function include(a_items ut_varchar2_list) return ut_equal,  
   member procedure include(self in ut_equal, a_items varchar2),
diff --git a/source/expectations/ut_expectation.tpb b/source/expectations/ut_expectation.tpb
@@ -176,6 +176,11 @@ create or replace type body ut_expectation as
     self.to_( ut_equal(a_expected, a_nulls_are_equal) );
   end;
 
+  member procedure to_equal(self in ut_expectation, a_expected json, a_nulls_are_equal boolean := null) is
+  begin
+    self.to_( ut_equal(a_expected, a_nulls_are_equal) );
+  end;
+
   member procedure not_to_equal(self in ut_expectation, a_expected anydata, a_nulls_are_equal boolean := null) is
   begin
     self.not_to( ut_equal(a_expected, a_nulls_are_equal) );
diff --git a/source/expectations/ut_expectation.tps b/source/expectations/ut_expectation.tps
@@ -49,6 +49,7 @@ create or replace type ut_expectation force under ut_expectation_base(
   member procedure to_equal(self in ut_expectation, a_expected yminterval_unconstrained, a_nulls_are_equal boolean := null),
   member procedure to_equal(self in ut_expectation, a_expected dsinterval_unconstrained, a_nulls_are_equal boolean := null),
   member procedure to_equal(self in ut_expectation, a_expected json_element_t, a_nulls_are_equal boolean := null),
+  member procedure to_equal(self in ut_expectation, a_expected json, a_nulls_are_equal boolean := null),
 
   member procedure not_to_equal(self in ut_expectation, a_expected anydata, a_nulls_are_equal boolean := null),
   member procedure not_to_equal(self in ut_expectation, a_expected anydata, a_exclude varchar2, a_nulls_are_equal boolean := null),
diff --git a/source/expectations/ut_expectation_json.tps b/source/expectations/ut_expectation_json.tps
@@ -24,4 +24,4 @@ create or replace type ut_expectation_json under ut_expectation(
   member procedure to_have_count(self in ut_expectation_json, a_expected integer),
   member procedure not_to_have_count(self in ut_expectation_json, a_expected integer)
 )
-/
+/
diff --git a/test/ut3_user/expectations/test_expectations_json.pkb b/test/ut3_user/expectations/test_expectations_json.pkb
diff --git a/test/ut3_user/expectations/test_expectations_json.pks b/test/ut3_user/expectations/test_expectations_json.pks

Original file line number	Diff line number	Diff line change
`@@ -134,5 +134,7 @@ create or replace package ut_compound_data_helper authid definer is`
`134`	`134`
`135`	`135`	`function get_json_diffs_type(a_diff_id raw) return tt_json_diff_type_tab;`
`136`	`136`
	`137`	`+ function get_json_object(a_json_t json) return json_element_t;`
	`138`	`+`
`137`	`139`	`end;`
`138`	`140`	`/`