First commit of adding a new 21c json type.

utPLSQL · lwasylow · Feb 6, 2022 · Jan 31, 2022 · Jan 31, 2022 · Feb 1, 2022
commit 9b8d7f7e889593777ae46c3485d36d7ae3ef4bf9
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_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 := case when a_value is null then cast (null as json_element_t ) else json_element_t.parse(json_query(a_value, '$' returning clob)) end;
+  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));

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,7 +1,12 @@
 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');
+  $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.');
+    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.');
     execute immediate  q'[create or replace TYPE JSON_Element_T FORCE AUTHID CURRENT_USER AS OBJECT(

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 function exclude(a_items varchar2) return ut_equal,

diff --git a/source/expectations/ut_expectation.tpb b/source/expectations/ut_expectation.tpb
@@ -201,6 +201,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
@@ -55,6 +55,7 @@ create or replace type ut_expectation authid current_user as object(
   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
@@ -1608,5 +1608,179 @@ create or replace package body test_expectations_json is
   end;
 
 
+  procedure success_on_same_data_njson
+  as
+    l_actual   json;
+  begin
+    -- Arrange
+    l_actual   := json('    {
+      "Actors": [
+        {
+          "name": "Tom Cruise",
+          "age": 56,
+          "Born At": "Syracuse, NY",
+          "Birthdate": "July 3, 1962",
+          "photo": "https://jsonformatter.org/img/tom-cruise.jpg",
+          "wife": null,
+          "weight": 67.5,
+          "hasChildren": true,
+          "hasGreyHair": false,
+          "children": [
+            "Suri",
+            "Isabella Jane",
+            "Connor"
+          ]
+        },
+        {
+          "name": "Robert Downey Jr.",
+          "age": 53,
+          "Born At": "New York City, NY",
+          "Birthdate": "April 4, 1965",
+          "photo": "https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
+          "wife": "Susan Downey",
+          "weight": 77.1,
+          "hasChildren": true,
+          "hasGreyHair": false,
+          "children": [
+            "Indio Falconer",
+            "Avri Roel",
+            "Exton Elias"
+          ]
+        }
+      ]
+    }');
+
+    --Act
+    ut3_develop.ut.expect( l_actual ).to_equal( l_actual );
+    --Assert
+    ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+  end;
+
+  procedure fail_on_diff_data_njson
+  as
+    l_expected json;
+    l_actual   json;
+    l_expected_message varchar2(32767);
+    l_actual_message   varchar2(32767);
+  begin
+    -- Arrange
+    l_expected   := json('{"Aidan Gillen": {"array": ["Game of Thrones","The Wire"],"string": "some string","int": "2","otherint": 4, "aboolean": "true", "boolean": false,"object": {"foo": "bar"}},"Amy Ryan": ["In Treatment","The Wire"],"Annie Fitzgerald": ["True Blood","Big Love","The Sopranos","Oz"],"Anwan Glover": ["Treme","The Wire"],"Alexander Skarsg?rd": ["Generation Kill","True Blood"],"Alice Farmer": ["The Corner","Oz","The Wire"]}');
+    l_actual := json('{"Aidan Gillen": {"array": ["Game of Thron\"es","The Wire"],"string": "some string","int": 2,"aboolean": true, "boolean": true,"object": {"foo": "bar","object1": {"new prop1": "new prop value"},"object2": {"new prop1": "new prop value"},"object3": {"new prop1": "new prop value"},"object4": {"new prop1": "new prop value"}}},"Amy Ryan": {"one": "In Treatment","two": "The Wire"},"Annie Fitzgerald": ["Big Love","True Blood"],"Anwan Glover": ["Treme","The Wire"],"Alexander Skarsgard": ["Generation Kill","True Blood"], "Clarke Peters": null}');
+
+    --Act
+    ut3_develop.ut.expect( l_actual ).to_equal( l_expected );
+    --Assert
+    l_expected_message := q'[%Missing property: "Alexander Skarsg?rd" on path: $
+%Extra   property: "Alexander Skarsgard" on path: $
+%Missing property: "Alice Farmer" on path: $
+%Extra   property: "Clarke Peters" on path: $
+%Extra   property: "one" on path: $."Amy Ryan"
+%Missing property: "The Sopranos" on path: $."Annie Fitzgerald"[2]
+%Extra   property: "two" on path: $."Amy Ryan"
+%Missing property: "Oz" on path: $."Annie Fitzgerald"[3]
+%Missing property: "otherint" on path: $."Aidan Gillen"
+%Extra   property: "object1" on path: $."Aidan Gillen"."object"
+%Extra   property: "object2" on path: $."Aidan Gillen"."object"
+%Extra   property: "object3" on path: $."Aidan Gillen"."object"
+%Extra   property: "object4" on path: $."Aidan Gillen"."object"
+%Actual  type: 'array' was expected to be: 'object' on path: $."Amy Ryan"
+%Actual  type: 'string' was expected to be: 'number' on path: $."Aidan Gillen"."int"
+%Actual  type: 'string' was expected to be: 'boolean' on path: $."Aidan Gillen"."aboolean"
+%Actual value: "True Blood" was expected to be: "Big Love" on path: $."Annie Fitzgerald"[0]
+%Actual value: "Big Love" was expected to be: "True Blood" on path: $."Annie Fitzgerald"[1]
+%Actual value: FALSE was expected to be: TRUE on path: $."Aidan Gillen"."boolean"
+%Actual value: "Game of Thrones" was expected to be: "Game of Thron\"es" on path: $."Aidan Gillen"."array"[0]%]';
+    l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
+    --Assert
+    ut.expect(l_actual_message).to_be_like(l_expected_message);
+    ut.expect(l_actual_message).to_be_like('%Diff: 20 differences found%');
+    ut.expect(l_actual_message).to_be_like('%13 missing properties%');
+    ut.expect(l_actual_message).to_be_like('%4 unequal values%');
+    ut.expect(l_actual_message).to_be_like('%3 incorrect types%');
+  end; 
+
+
+  --Please note that by the looks of things the call to json() results in null value being returned.
+  procedure null_json_variable_njson
+  as
+    l_expected json ;
+  begin
+    -- Arrange
+    l_expected := cast (null as json );
+
+    --Act
+    ut3_develop.ut.expect( l_expected ).to_be_null;
+    --Assert
+    ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+  end;
+
+  procedure to_have_count_njson as
+    l_actual   json;
+    l_expected_message varchar2(32767);
+    l_actual_message   varchar2(32767);
+  begin
+    -- Arrange
+    l_actual   := json('{"Aidan Gillen": {"array": ["Game of Thrones","The Wire"],"string": "some string","int": "2","otherint": 4, "aboolean": "true", "boolean": false,"object": {"foo": "bar"}},"Amy Ryan": ["In Treatment","The Wire"],"Annie Fitzgerald": ["True Blood","Big Love","The Sopranos","Oz"],"Anwan Glover": ["Treme","The Wire"],"Alexander Skarsg?rd": ["Generation Kill","True Blood"],"Alice Farmer": ["The Corner","Oz","The Wire"]}');
+
+    --Act
+    ut3_develop.ut.expect( l_actual ).to_have_count( 6 );
+
+    --Assert
+     ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+
+  end;
+
+  procedure fail_to_have_count_njson
+  as
+    l_actual   json;
+    l_expected_message varchar2(32767);
+    l_actual_message   varchar2(32767);
+  begin
+    -- Arrange
+    l_actual   := json('{"Aidan Gillen": {"array": ["Game of Thrones","The Wire"],"string": "some string","int": "2","otherint": 4, "aboolean": "true", "boolean": false,"object": {"foo": "bar"}},"Amy Ryan": ["In Treatment","The Wire"],"Annie Fitzgerald": ["True Blood","Big Love","The Sopranos","Oz"],"Anwan Glover": ["Treme","The Wire"],"Alexander Skarsg?rd": ["Generation Kill","True Blood"],"Alice Farmer": ["The Corner","Oz","The Wire"]}');
+
+    --Act
+    ut3_develop.ut.expect( l_actual ).to_have_count( 2 );
+    --Assert
+    l_expected_message := q'[%Actual: (json [ count = 6 ]) was expected to have [ count = 2 ]%]';
+    l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
+    --Assert
+    ut.expect(l_actual_message).to_be_like(l_expected_message);
+
+  end;
+
+  procedure not_to_have_count_njson
+  as
+    l_actual   json;
+    l_expected_message varchar2(32767);
+    l_actual_message   varchar2(32767);
+  begin
+    -- Arrange
+    l_actual   := json('{"Aidan Gillen": {"array": ["Game of Thrones","The Wire"],"string": "some string","int": "2","otherint": 4, "aboolean": "true", "boolean": false,"object": {"foo": "bar"}},"Amy Ryan": ["In Treatment","The Wire"],"Annie Fitzgerald": ["True Blood","Big Love","The Sopranos","Oz"],"Anwan Glover": ["Treme","The Wire"],"Alexander Skarsg?rd": ["Generation Kill","True Blood"],"Alice Farmer": ["The Corner","Oz","The Wire"]}');
+
+    --Act
+    ut3_develop.ut.expect( l_actual ).not_to_have_count( 7 );
+    --Assert
+    ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+  end;  
+
+  procedure fail_not_to_have_count_njson
+  as
+    l_actual   json;
+    l_expected_message varchar2(32767);
+    l_actual_message   varchar2(32767);
+  begin
+    -- Arrange
+    l_actual   := json('{"Aidan Gillen": {"array": ["Game of Thrones","The Wire"],"string": "some string","int": "2","otherint": 4, "aboolean": "true", "boolean": false,"object": {"foo": "bar"}},"Amy Ryan": ["In Treatment","The Wire"],"Annie Fitzgerald": ["True Blood","Big Love","The Sopranos","Oz"],"Anwan Glover": ["Treme","The Wire"],"Alexander Skarsg?rd": ["Generation Kill","True Blood"],"Alice Farmer": ["The Corner","Oz","The Wire"]}');
+
+    --Act
+    ut3_develop.ut.expect( l_actual ).not_to_have_count( 6 );
+    --Assert
+    l_expected_message := q'[%Actual: json [ count = 6 ] was expected not to have [ count = 6 ]%]';
+    l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
+    --Assert
+    ut.expect(l_actual_message).to_be_like(l_expected_message);
+  end; 
+
 end;
 /
diff --git a/test/ut3_user/expectations/test_expectations_json.pks b/test/ut3_user/expectations/test_expectations_json.pks
@@ -39,13 +39,13 @@ create or replace package test_expectations_json is
   --%test( Json object to have count )
   procedure to_have_count;
 
-    --%test( Fail Json object to have count)
+  --%test( Fail Json object to have count)
   procedure fail_to_have_count;
 
     --%test( Json object not to have count)
   procedure not_to_have_count;
 
-    --%test( Fail Json object not to have count)
+  --%test( Fail Json object not to have count)
   procedure fail_not_to_have_count;
 
   --%test( Json object to have count on array)
@@ -74,6 +74,27 @@ create or replace package test_expectations_json is
 
   --%test( Compare two json arrays )
   procedure check_json_arrays;
+
+  --%test(Gives success for identical data using native json for 21c and above)
+  procedure success_on_same_data_njson;
+
+  --%test(Gives failure for different data using native json for 21c and above)
+  procedure fail_on_diff_data_njson; 
+
+  --%test( Json variable is null using native json for 21c and above)
+  procedure null_json_variable_njson;
+
+  --%test( Json object to have count using native json for 21c and above)
+  procedure to_have_count_njson;
+
+  --%test( Fail Json object to have count using native json for 21c and above)
+  procedure fail_to_have_count_njson;
+
+  --%test( Json object not to have count using native json for 21c and above)
+  procedure not_to_have_count_njson;
+
+  --%test( Fail Json object not to have count using native json for 21c and above)
+  procedure fail_not_to_have_count_njson;
 
 end;
 /
-Original file line number
+Diff line change
@@ Expand Up @@
       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)
     )
-    /
+    /