Skip to content

Feature/1082 failure when comparing nested objects #1179

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
lwasylow merged 9 commits into from
Jan 29, 2022
Merged

Feature/1082 failure when comparing nested objects #1179

Show file tree
Hide file tree
Changes from 6 commits
Commits
Show all changes
9 commits
Select commit Hold shift + click to select a range
c11aa24
Initial check in.
lwasylow Jan 26, 2022
e0ac97c
Adding test for double nested objects
lwasylow Jan 26, 2022
5e983a8
Fixing test object name as its too large for 11gR2 (31 char)
lwasylow Jan 26, 2022
3c3d320
Fixing issue when a hash was tried to be generated on the null value …
lwasylow Jan 26, 2022
14b0b8d
Fixing issue when a collection nested inside object was failing due t…
lwasylow Jan 26, 2022
85b2349
Removing empty line from code.
lwasylow Jan 26, 2022
c14a3ad
Address PR comments
lwasylow Jan 28, 2022
a2cf02e
Creating regression test scenario for fixed issue 1098 and supporting…
lwasylow Jan 29, 2022
b11e799
Fixing an 11g limit on size of the procedure of 30.
lwasylow Jan 29, 2022
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
6 changes: 4 additions & 2 deletions source/core/ut_utils.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -882,12 +882,14 @@ create or replace package body ut_utils is

function get_hash(a_data raw, a_hash_type binary_integer := dbms_crypto.hash_sh1) return t_hash is
begin
return dbms_crypto.hash(a_data, a_hash_type);
--We cannot run hash on null
return case when a_data is null then null else dbms_crypto.hash(a_data, a_hash_type) end;
end;

function get_hash(a_data clob, a_hash_type binary_integer := dbms_crypto.hash_sh1) return t_hash is
begin
return dbms_crypto.hash(a_data, a_hash_type);
--We cannot run hash on null
return case when a_data is null then null else dbms_crypto.hash(a_data, a_hash_type) end;
end;

function qualified_sql_name(a_name varchar2) return varchar2 is
Expand Down
10 changes: 7 additions & 3 deletions source/expectations/data_values/ut_compound_data_helper.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -278,7 +278,7 @@ create or replace package body ut_compound_data_helper is
begin
if a_data_info is not empty then
for i in 1..a_data_info.count loop
if a_data_info(i).has_nested_col = 0 then
if a_data_info(i).has_nested_col = 0 and a_data_info(i).column_type <> 'OBJECT' then
--Get XMLTABLE column list
add_element_to_list(l_xmltab_list,generate_xmltab_stmt(a_data_info(i)));
--Get Select statment list of columns
Expand Down Expand Up @@ -398,8 +398,12 @@ create or replace package body ut_compound_data_helper is
l_column_list ut_varchar2_list := ut_varchar2_list();
begin
for i in 1..a_cursor_info.count loop
l_column_list.extend;
l_column_list(l_column_list.last) := a_cursor_info(i).access_path;
--This avoids extracting single columns from nested objects.
--as we can go down to any level but we will lose visibility of parent.
if a_cursor_info(i).hierarchy_level = 1 then
l_column_list.extend;
l_column_list(l_column_list.last) := a_cursor_info(i).access_path;
end if;
end loop;
return l_column_list;
end;
Expand Down
12 changes: 8 additions & 4 deletions source/expectations/data_values/ut_cursor_column.tpb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -14,8 +14,11 @@ create or replace type body ut_cursor_column as
self.column_len := a_col_max_len; --length of column
self.column_precision := a_col_precision;
self.column_scale := a_col_scale;
self.column_name := TRIM( BOTH '''' FROM a_col_name); --name of the column
self.column_type_name := coalesce(a_col_type_name,a_col_type); --type name e.g. test_dummy_object or varchar2
self.column_name := case when a_col_name is null and a_collection = 1 then
self.column_type_name
else TRIM( BOTH '''' FROM a_col_name)
end; --name of the column, however in nested object for collection name is not defined in cursor.
self.xml_valid_name := ut_utils.get_valid_xml_name(self.column_name);
self.display_path := case when a_access_path is null then
self.column_name
Expand All @@ -25,15 +28,16 @@ create or replace type body ut_cursor_column as
self.access_path := case when a_access_path is null then
self.xml_valid_name
else
a_access_path||'/'||self.xml_valid_name
a_access_path||'/'||self.xml_valid_name
end; --Access path used for XMLTABLE query
self.filter_path := '/'||self.access_path; --Filter path will differ from access path in anydata type
--Transformed name needs to be build on full access path to avoid ambiguity when there is 3 or more levels of nesting.
self.transformed_name := case when length(self.xml_valid_name) > 30 then
'"'||ut_compound_data_helper.get_fixed_size_hash(self.parent_name||self.xml_valid_name)||'"'
'"'||ut_compound_data_helper.get_fixed_size_hash(self.access_path)||'"'
when self.parent_name is null then
'"'||self.xml_valid_name||'"'
else
'"'||ut_compound_data_helper.get_fixed_size_hash(self.parent_name||self.xml_valid_name)||'"'
'"'||ut_compound_data_helper.get_fixed_size_hash(self.access_path)||'"'
end; --when is nestd we need to hash name to make sure we dont exceed 30 char
self.column_type := a_col_type; --column type e.g. user_defined , varchar2
self.column_schema := a_col_schema_name; -- schema name
Expand Down
4 changes: 4 additions & 0 deletions test/install_ut3_tester_helper.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -6,9 +6,13 @@ alter session set plsql_optimize_level=0;
--Install ut3_tester_helper
@@ut3_tester_helper/test_dummy_object.tps
@@ut3_tester_helper/other_dummy_object.tps
@@ut3_tester_helper/test_dummy_nested_object.tps
@@ut3_tester_helper/test_dummy_double_nested_object.tps
@@ut3_tester_helper/test_dummy_object_list.tps
@@ut3_tester_helper/test_dummy_nested_object_list.tps
@@ut3_tester_helper/test_tab_varchar2.tps
@@ut3_tester_helper/test_tab_varray.tps
@@ut3_tester_helper/test_nested_tab_varray.tps
@@ut3_tester_helper/test_dummy_number.tps
@@ut3_tester_helper/ut_test_table.sql
@@ut3_tester_helper/test_event_object.tps
Expand Down
15 changes: 15 additions & 0 deletions test/ut3_tester_helper/test_dummy_double_nested_object.tps
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,15 @@
declare
l_exists integer;
begin
select count(1) into l_exists from user_types where type_name = 'TEST_DUMMY_DOUBLE_NESTED_OBJ';
if l_exists > 0 then
execute immediate 'drop type test_dummy_double_nested_obj force';
end if;
end;
/

create or replace type test_dummy_double_nested_obj as object (
first_double_nested_obj test_dummy_nested_object,
"Value" varchar2(30)
)
/
15 changes: 15 additions & 0 deletions test/ut3_tester_helper/test_dummy_nested_object.tps
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,15 @@
declare
l_exists integer;
begin
select count(1) into l_exists from user_types where type_name = 'TEST_DUMMY_NESTED_OBJECT';
if l_exists > 0 then
execute immediate 'drop type test_dummy_nested_object force';
end if;
end;
/

create or replace type test_dummy_nested_object as object (
first_nested_obj test_dummy_object,
sec_nested_obj test_dummy_object
)
/
14 changes: 14 additions & 0 deletions test/ut3_tester_helper/test_dummy_nested_object_list.tps
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,14 @@
declare
l_exists integer;
begin
select count(1) into l_exists from user_types where type_name = 'TEST_DUMMY_NESTED_OBJECT_LIST';
if l_exists > 0 then
execute immediate 'drop type test_dummy_nested_object_list force';
end if;
end;
/

create or replace type test_dummy_nested_object_list as object (
first_nested_obj test_dummy_object_list
)
/
10 changes: 10 additions & 0 deletions test/ut3_tester_helper/test_dummy_object_list.tps
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,2 +1,12 @@
declare
l_exists integer;
begin
select count(1) into l_exists from user_types where type_name = 'TEST_DUMMY_OBJECT_LIST';
if l_exists > 0 then
execute immediate 'drop type test_dummy_object_list force';
end if;
end;
/

create or replace type test_dummy_object_list as table of test_dummy_object
/
14 changes: 14 additions & 0 deletions test/ut3_tester_helper/test_nested_tab_varray.tps
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,14 @@
declare
l_exists integer;
begin
select count(1) into l_exists from user_types where type_name = 'TEST_NESTED_TAB_VARRAY';
if l_exists > 0 then
execute immediate 'drop type test_nested_tab_varray force';
end if;
end;
/

create or replace type test_nested_tab_varray as object (
n_varray t_varray
)
/
137 changes: 137 additions & 0 deletions test/ut3_user/expectations/test_expectation_anydata.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -991,6 +991,143 @@ Rows: [ 60 differences, showing first 20 ]
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);

end;

procedure failure_nesting_objects is
l_actual_message varchar2(32767);
l_expected_message varchar2(32767);
begin
--Arrange
g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ));
g_test_actual := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'C', '0') ));
--Act
l_expected_message := q'[%Actual: ut3_tester_helper.test_dummy_nested_object was expected to equal: ut3_tester_helper.test_dummy_nested_object
%Diff:
%Rows: [ 1 differences ]
%Row No. 1 - Actual: <SEC_NESTED_OBJ><ID>1</ID><name>C</name><Value>0</Value></SEC_NESTED_OBJ>
%Row No. 1 - Expected: <SEC_NESTED_OBJ><ID>1</ID><name>B</name><Value>0</Value></SEC_NESTED_OBJ>]';
ut3_develop.ut.expect(g_test_actual).to_equal(g_test_expected);
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 failure_double_nested_objects is
l_actual_message varchar2(32767);
l_expected_message varchar2(32767);
begin
--Arrange
g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_double_nested_obj(ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ),'Test'));
g_test_actual := anydata.convertObject( ut3_tester_helper.test_dummy_double_nested_obj(ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'C', '0') ),'Test'));
--Act
l_expected_message := q'[%Actual: ut3_tester_helper.test_dummy_double_nested_obj was expected to equal: ut3_tester_helper.test_dummy_double_nested_obj
%Diff:
%Rows: [ 1 differences ]
%Row No. 1 - Actual: <FIRST_DOUBLE_NESTED_OBJ><FIRST_NESTED_OBJ><ID>1</ID><name>A</name><Value>0</Value></FIRST_NESTED_OBJ><SEC_NESTED_OBJ><ID>1</ID><name>C</name><Value>0</Value></SEC_NESTED_OBJ></FIRST_DOUBLE_NESTED_OBJ>
%Row No. 1 - Expected: <FIRST_DOUBLE_NESTED_OBJ><FIRST_NESTED_OBJ><ID>1</ID><name>A</name><Value>0</Value></FIRST_NESTED_OBJ><SEC_NESTED_OBJ><ID>1</ID><name>B</name><Value>0</Value></SEC_NESTED_OBJ></FIRST_DOUBLE_NESTED_OBJ>]';
ut3_develop.ut.expect(g_test_actual).to_equal(g_test_expected);
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 success_nesting_objects is
begin
--Arrange
g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ));
g_test_actual := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ));
--Act
ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
end;

procedure success_double_nested_objects is
begin
--Arrange
g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_double_nested_obj(ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ),'Test'));
g_test_actual := anydata.convertObject( ut3_tester_helper.test_dummy_double_nested_obj(ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ),'Test'));
--Act
ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
end;

procedure failure_nested_object_list is
l_actual_message varchar2(32767);
l_expected_message varchar2(32767);
l_actual ut3_tester_helper.test_dummy_object_list;
l_expected ut3_tester_helper.test_dummy_object_list;
begin
--Arrange
select ut3_tester_helper.test_dummy_object( rownum + 1, 'Something '||rownum, rownum)
bulk collect into l_actual
from dual connect by level <=2
order by rownum desc;
select ut3_tester_helper.test_dummy_object( rownum, 'Something '||rownum, rownum)
bulk collect into l_expected
from dual connect by level <=2
order by rownum desc;
--Arrange
g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object_list(l_actual));
g_test_actual := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object_list(l_expected));
--Act
l_expected_message := q'[%Actual: ut3_tester_helper.test_dummy_nested_object_list was expected to equal: ut3_tester_helper.test_dummy_nested_object_list
%Diff:
%Rows: [ 1 differences ]
%Row No. 1 - Actual: <FIRST_NESTED_OBJ><TEST_DUMMY_OBJECT><ID>2</ID><name>Something 2</name><Value>2</Value></TEST_DUMMY_OBJECT><TEST_DUMMY_OBJECT><ID>1</ID><name>Something 1</name><Value>1</Value></TEST_DUMMY_OBJECT></FIRST_NESTED_OBJ>
%Row No. 1 - Expected: <FIRST_NESTED_OBJ><TEST_DUMMY_OBJECT><ID>3</ID><name>Something 2</name><Value>2</Value></TEST_DUMMY_OBJECT><TEST_DUMMY_OBJECT><ID>2</ID><name>Something 1</name><Value>1</Value></TEST_DUMMY_OBJECT></FIRST_NESTED_OBJ>]';
ut3_develop.ut.expect(g_test_actual).to_equal(g_test_expected);
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 success_nested_object_list is
l_actual ut3_tester_helper.test_dummy_object_list;
l_expected ut3_tester_helper.test_dummy_object_list;
begin
--Arrange
select ut3_tester_helper.test_dummy_object( rownum , 'Something '||rownum, rownum)
bulk collect into l_actual
from dual connect by level <=2
order by rownum desc;
select ut3_tester_helper.test_dummy_object( rownum, 'Something '||rownum, rownum)
bulk collect into l_expected
from dual connect by level <=2
order by rownum desc;
--Arrange
g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object_list(l_actual));
g_test_actual := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object_list(l_expected));
--Act
ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
end;

procedure nested_varray_same_data is
begin
--Arrange
g_test_expected := anydata.convertObject( ut3_tester_helper.test_nested_tab_varray(ut3_tester_helper.t_varray(1)) );
g_test_actual := anydata.convertObject( ut3_tester_helper.test_nested_tab_varray(ut3_tester_helper.t_varray(1)) );
--Act
ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
end;

procedure nested_varray_diff_data is
l_actual_message varchar2(32767);
l_expected_message varchar2(32767);
begin
--Arrange
g_test_expected := anydata.convertObject( ut3_tester_helper.test_nested_tab_varray(ut3_tester_helper.t_varray(1)) );
g_test_actual := anydata.convertObject( ut3_tester_helper.test_nested_tab_varray(ut3_tester_helper.t_varray(2)) );
--Act
ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
l_expected_message := q'[%Actual: ut3_tester_helper.test_nested_tab_varray was expected to equal: ut3_tester_helper.test_nested_tab_varray
%Diff:
%Rows: [ 1 differences ]
%Row No. 1 - Actual: <N_VARRAY><NUMBER>2</NUMBER></N_VARRAY>
%Row No. 1 - Expected: <N_VARRAY><NUMBER>1</NUMBER></N_VARRAY>]';
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;
/
24 changes: 24 additions & 0 deletions test/ut3_user/expectations/test_expectation_anydata.pks
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -203,5 +203,29 @@ create or replace package test_expectation_anydata is

--%test ( Empty Array not equal array with space )
procedure arr_empty_nqua_arr_e_unord;

--%test ( Failure of comparing nested objects )
Comment thread
lwasylow marked this conversation as resolved.
Outdated
procedure failure_nesting_objects;

--%test ( Failure of comparing double nested objects )
procedure failure_double_nested_objects;

--%test ( Success of comparing nested objects )
procedure success_nesting_objects;

--%test ( Success of comparing double nested objects )
procedure success_double_nested_objects;

--%test ( Failure of comparing nested object list )
procedure failure_nested_object_list;

--%test ( Success of comparing nested object list )
procedure success_nested_object_list;

--%test(Nested VARRAYS with same data)
procedure nested_varray_same_data;

--%test(Nested VARRAYS with different data)
procedure nested_varray_diff_data;
Comment thread
lwasylow marked this conversation as resolved.
Outdated
end;
/