Skip to content

Changed names on internal columns in cursor & object compare. #999

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
jgebal merged 1 commit into from
Sep 23, 2019
Merged

Changed names on internal columns in cursor & object compare. #999

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
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
54 changes: 28 additions & 26 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 @@ -28,46 +28,46 @@ create or replace package body ut_compound_data_helper is
with exp as (
select
ucd.*,
{:duplicate_number:} dup_no
{:duplicate_number:} "UT3$_Dup#No"
from (
select
ucd.item_data
,x.data_id data_id
,position + x.item_no item_no
ucd."UT3$_Item#Data"
,x.data_id "UT3$_Data#Id"
,ucd."UT3$_Position#" + x.item_no "UT3$_Item#No"
{:columns:}
from ut_compound_data_tmp x,
xmltable('/ROWSET/ROW' passing x.item_data columns
item_data xmltype path '*'
,position for ordinality
"UT3$_Item#Data" xmltype path '*'
,"UT3$_Position#" for ordinality
{:xml_to_columns:} ) ucd
where data_id = :exp_guid
where x.data_id = :exp_guid
) ucd
)
, act as (
select
ucd.*,
{:duplicate_number:} dup_no
{:duplicate_number:} "UT3$_Dup#No"
from (
select
ucd.item_data
,x.data_id data_id
,position + x.item_no item_no
ucd."UT3$_Item#Data"
,x.data_id "UT3$_Data#Id"
,ucd."UT3$_Position#" + x.item_no "UT3$_Item#No"
{:columns:}
from ut_compound_data_tmp x,
xmltable('/ROWSET/ROW' passing x.item_data columns
item_data xmltype path '*'
,position for ordinality
"UT3$_Item#Data" xmltype path '*'
,"UT3$_Position#" for ordinality
{:xml_to_columns:} ) ucd
where data_id = :act_guid
where x.data_id = :act_guid
) ucd
)
select
a.item_data as act_item_data,
a.data_id act_data_id,
e.item_data as exp_item_data,
e.data_id exp_data_id,
a."UT3$_Item#Data" as act_item_data,
a."UT3$_Data#Id" act_data_id,
e."UT3$_Item#Data" as exp_item_data,
e."UT3$_Data#Id" exp_data_id,
{:item_no:} as item_no,
nvl(e.dup_no,a.dup_no) dup_no
nvl(e."UT3$_Dup#No",a."UT3$_Dup#No") dup_no
from act a {:join_type:} exp e on ( {:join_condition:} )
where {:where_condition:}]';

Expand Down Expand Up @@ -306,16 +306,16 @@ create or replace package body ut_compound_data_helper is
a_join_by_stmt := ut_utils.table_to_clob(l_join_by_list, ' and ');
elsif a_unordered then
-- If no key defined do the join on all columns
a_join_by_stmt := ' e.dup_no = a.dup_no and '||ut_utils.table_to_clob(l_equal_list, ' and ');
a_join_by_stmt := ' e."UT3$_Dup#No" = a."UT3$_Dup#No" and '||ut_utils.table_to_clob(l_equal_list, ' and ');
else
-- Else join on rownumber
a_join_by_stmt := 'a.item_no = e.item_no ';
a_join_by_stmt := 'a."UT3$_Item#No" = e."UT3$_Item#No" ';
end if;
a_not_equal_stmt := ut_utils.table_to_clob(l_not_equal_list, ' or ');
else
--Partition by piece when no data
ut_utils.append_to_clob(a_partition_stmt,' 1 ');
a_join_by_stmt := 'a.item_no = e.item_no ';
a_join_by_stmt := 'a."UT3$_Item#No" = e."UT3$_Item#No" ';
end if;
end;

Expand Down Expand Up @@ -349,8 +349,8 @@ create or replace package body ut_compound_data_helper is
begin
return
case
when a_unordered then 'row_number() over ( order by nvl(e.item_no,a.item_no))'
else 'nvl(e.item_no,a.item_no) '
when a_unordered then 'row_number() over ( order by nvl(e."UT3$_Item#No",a."UT3$_Item#No"))'
else 'nvl(e."UT3$_Item#No",a."UT3$_Item#No") '
end;
end;

Expand Down Expand Up @@ -387,9 +387,9 @@ create or replace package body ut_compound_data_helper is
end if;
--If its inclusion we expect a actual set to fully match and have no extra elements over expected
if a_inclusion_type then
ut_utils.append_to_clob(l_where_stmt,case when a_is_negated then ' 1 = 1 ' else ' ( a.data_id is null ) ' end);
ut_utils.append_to_clob(l_where_stmt,case when a_is_negated then ' 1 = 1 ' else ' ( a."UT3$_Data#Id" is null ) ' end);
else
ut_utils.append_to_clob(l_where_stmt,' (a.data_id is null or e.data_id is null) ');
ut_utils.append_to_clob(l_where_stmt,' (a."UT3$_Data#Id" is null or e."UT3$_Data#Id" is null) ');
end if;

l_compare_sql := replace(l_compare_sql,'{:where_condition:}',l_where_stmt);
Expand Down Expand Up @@ -561,6 +561,8 @@ create or replace package body ut_compound_data_helper is
procedure cleanup_diff is
begin
g_diff_count := 0;
delete from ut_compound_data_diff_tmp;
delete from ut_json_data_diff_tmp;
end;

function get_rows_diff_count return integer is
Expand Down
45 changes: 45 additions & 0 deletions test/ut3_user/expectations/test_expectations_cursor.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2825,5 +2825,50 @@ Check the query and data for errors.';
$end
end;

procedure compare_specific_column_names is
function get_cursor return sys_refcursor is
l_result sys_refcursor;
begin
open l_result for
select 'a' as item_data, rownum as data_id, rownum as item_no, rownum as dup_no, rownum as position from dual;
return l_result;
end;
begin
ut3.ut.expect(get_cursor()).to_equal(get_cursor());
ut3.ut.expect(get_cursor()).to_equal(get_cursor()).unordered();
ut3.ut.expect(get_cursor()).to_equal(get_cursor()).join_by('ITEM_DATA,DATA_ID,ITEM_NO,DUP_NO');
--Assert
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
end;

procedure multiple_cursor_expectations is
l_actual sys_refcursor;
l_expected sys_refcursor;
begin
open l_actual for select rownum rn from dual connect by level < 5;
open l_expected for select rownum rn from dual connect by level = 1;
ut3.ut.expect(l_actual).to_equal(l_expected);
open l_actual for select rownum rn from dual connect by level < 3;
open l_expected for select * from (select rownum rn from dual connect by level < 3) order by 1 desc;
ut3.ut.expect(l_actual).to_equal(l_expected);
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations(1)).to_equal(
'Actual: refcursor [ count = 4 ] was expected to equal: refcursor [ count = 1 ]
Diff:
Rows: [ 3 differences ]
Row No. 2 - Extra: <RN>2</RN>
Row No. 3 - Extra: <RN>3</RN>
Row No. 4 - Extra: <RN>4</RN>'
);
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations(2)).to_equal(
'Actual: refcursor [ count = 2 ] was expected to equal: refcursor [ count = 2 ]
Diff:
Rows: [ 2 differences ]
Row No. 1 - Actual: <RN>1</RN>
Row No. 1 - Expected: <RN>2</RN>
Row No. 2 - Actual: <RN>2</RN>
Row No. 2 - Expected: <RN>1</RN>'
);
end;

end;
/
8 changes: 7 additions & 1 deletion test/ut3_user/expectations/test_expectations_cursor.pks
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -462,8 +462,14 @@ create or replace package test_expectations_cursor is
--%test( Mixed column order exclusion )
procedure uc_columns_exclude;

--%test(Compares cursors with long column names - Issue #952 )
--%test( Compares cursors with long column names - Issue #952 )
procedure compare_long_column_names;

--%test( Compares cursors with specific column names - Issue #997 )
procedure compare_specific_column_names;

--%test( Multiple failures reported correctly - Issue #998 )
procedure multiple_cursor_expectations;

end;
/