8000 Refactored expectations on compound data by jgebal · Pull Request #1043 · utPLSQL/utPLSQL · GitHub
[go: up one dir, main page]
Skip to content
8000

Refactored expectations on compound data #1043

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
merged 3 commits into from
Feb 18, 2020
Merged

Refactored expectations on compound data #1043

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
1b0b8f8
Refactored expectations on compound data
jgebal Feb 15, 2020
94f9c43
Merge remote-tracking branch 'origin/develop' into feature/unify_curs…
jgebal Feb 15, 2020
054c39f
Fixed issues with new cursor-results compare query on 11g
jgebal Feb 16, 2020
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
123 changes: 73 additions & 50 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 @@ -119,8 +119,6 @@ create or replace package body ut_compound_data_helper is
|| case when a_order_enforced then q'[
--column position is not matching (both when excluded extra/missing columns as well as when they are included)
or (a_pos_nn != e_pos_nn and exp_col_pos != act_col_pos)]'
else
null
end ||q'[
order by exp_col_pos, act_col_pos]'
bulk collect into l_results using a_expected, a_actual;
Expand Down Expand Up @@ -417,73 +415,100 @@ create or replace package body ut_compound_data_helper is
l_join_xpath varchar2(32767) := ut_utils.to_xpath(a_join_by_list);
l_results tt_row_diffs;
l_sql varchar2(32767);
l_column_order varchar2(100);
begin
if a_enforce_column_order then
l_column_order := 'col_no';
else
l_column_order := 'col_name';
end if;
l_sql := q'[
with exp as (
with
exp_cols as (
select
exp_item_data, exp_data_id, item_no rn, rownum col_no, pk_value,
i.exp_item_data, 8000 i.exp_data_id, i.item_no rn, rownum col_no, i.diff_id,
s.column_value col, s.column_value.getRootElement() col_name,
nvl(s.column_value.getclobval(),empty_clob()) col_val
nvl( s.column_value.getclobval(), empty_clob() ) col_val
from (
select
exp_data_id, extract( ucd.exp_item_data, :column_path ) exp_item_data, item_no,
replace( extract( ucd.exp_item_data, :join_by ).getclobval(), chr(10) ) pk_value
from ut_compound_data_diff_tmp ucd
where diff_id = :diff_id
ucd.exp_data_id, extract( ucd.exp_item_data, :column_path ) exp_item_data,
ucd.item_no, ucd.diff_id
from ut_compound_data_diff_tmp ucd
where ucd.diff_id = :diff_id
and ucd.exp_data_id = :self_guid
) i,
table( xmlsequence( extract(i.exp_item_data,:extract_path) ) ) s
table( xmlsequence( extract( i.exp_item_data, :extract_path ) ) ) s
),
act as (
act_cols as (
select
act_item_data, act_data_id, item_no rn, rownum col_no, pk_value,
i.act_item_data, i.act_data_id, i.item_no rn, rownum col_no, i.diff_id,
s.column_value col, s.column_value.getRootElement() col_name,
nvl(s.column_value.getclobval(),empty_clob()) col_val
nvl( s.column_value.getclobval(), empty_clob() ) col_val
from (
select
act_data_id, extract( ucd.act_item_data, :column_path ) act_item_data, item_no,
replace( extract( ucd.act_item_data, :join_by ).getclobval(), chr(10) ) pk_value
from ut_compound_data_diff_tmp ucd
where diff_id = :diff_id
ucd.act_data_id, extract( ucd.act_item_data, :column_path ) act_item_data,
ucd.item_no, ucd.diff_id
from ut_compound_data_diff_tmp ucd
where ucd.diff_id = :diff_id
and ucd.act_data_id = :other_guid
) i,
table( xmlsequence( extract(i.act_item_data,:extract_path) ) ) s
table( xmlsequence( extract( i.act_item_data, :extract_path ) ) ) s
),
data_diff as (
select rn, diff_type, xmlserialize(content data_item no indent) diffed_row, null pk_value, col_name, diff_id
from (
select nvl(exp.rn, act.rn) rn,
exp.diff_id diff_id,
xmlagg(exp.col order by exp.col_no) exp_item,
xmlagg(act.col order by nvl(exp.col_no, act.col_no)) act_item,
max(nvl(exp.col_name,act.col_name)) col_name
from exp_cols exp
join act_cols act
on exp.rn = act.rn and exp.col_name = act.col_name
where dbms_lob.compare(exp.col_val, act.col_val) != 0
group by nvl(exp.rn, act.rn), exp.diff_id
)
unpivot ( data_item for diff_type in (exp_item as 'Expected:', act_item as 'Actual:') )
),
unordered_diff as (
select rn, diff_type, xmlserialize(content data_item no indent) diffed_row, null pk_value, col_name, diff_id
from (
select nvl(exp.rn, act.rn) rn,
exp.diff_id diff_id,
xmlagg(exp.col order by exp.col_name) exp_item,
xmlagg(act.col order by act.col_name) act_item,
max(nvl(exp.col_name,act.col_name)) col_name
from exp_cols exp
join act_cols act
on exp.rn = act.rn and exp.col_name = act.col_name
where dbms_lob.compare(exp.col_val, act.col_val) != 0
group by nvl(exp.rn, act.rn), exp.diff_id
)
unpivot ( data_item for diff_type in (exp_item as 'Expected:', act_item as 'Actual:') )
)
select rn, diff_type, diffed_row, pk_value pk_value
select rn, diff_type, diffed_row, pk_value
from (
select rn, diff_type, diffed_row, pk_value,
case when diff_type = 'Actual:' then 1 else 2 end rnk,
1 final_order,
col_name
from ( ]'
|| case when a_unordered then q'[
select rn, diff_type, xmlserialize(content data_item no indent) diffed_row, pk_value, col_name
from (
select nvl(exp.rn, act.rn) rn,
nvl(exp.pk_value, act.pk_value) pk_value,
exp.col exp_item,
act.col act_item,
nvl(exp.col_name,act.col_name) col_name
from exp
join act
on exp.rn = act.rn and exp.col_name = act.col_name
where dbms_lob.compare(exp.col_val, act.col_val) != 0
)
unpivot ( data_item for diff_type in (exp_item as 'Expected:', act_item as 'Actual:') ) ]'
select /*+ no_unnest */
u.rn, u.diff_type, u.diffed_row,
replace(
extract( case when i.exp_data_id is null then i.act_item_data else i.exp_item_data end, :join_by ).getclobval(),
chr(10)
) pk_value,
u.col_name
from data_diff u
join ut_compound_data_diff_tmp i
on i.diff_id = u.diff_id
and i.item_no = u.rn]'
else q'[
select rn, diff_type, xmlserialize(content data_item no indent) diffed_row, null pk_value, col_name
from (
select nvl(exp.rn, act.rn) rn,
xmlagg(exp.col order by exp.col_no) exp_item,
xmlagg(act.col order by act.col_no) act_item,
max(nvl(exp.col_name,act.col_name)) col_name
from exp exp
join act act
on exp.rn = act.rn and exp.col_name = act.col_name
where dbms_lob.compare(exp.col_val, act.col_val) != 0
group by (exp.rn, act.rn)
)
unpivot ( data_item for diff_type in (exp_item as 'Expected:', act_item as 'Actual:') ) ]'
select rn, diff_type, diffed_row, pk_value, col_name
from data_diff
where :join_by is null ]'
end ||q'[
)
union all
Expand Down Expand Up @@ -522,14 +547,12 @@ create or replace package body ut_compound_data_helper is
case when final_order = 1 then to_char(rn) else col_name end,
case when final_order = 1 then to_char(rnk) else col_name end
]'
else
null
end;
execute immediate l_sql
bulk collect into l_results
using l_exp_extract_xpath, l_join_xpath, a_diff_id, a_expected_dataset_guid,a_extract_path,
l_act_extract_xpath, l_join_xpath, a_diff_id, a_actual_dataset_guid,a_extract_path,
l_join_xpath, l_join_xpath, a_diff_id;
using l_exp_extract_xpath, a_diff_id, a_expected_dataset_guid, a_extract_path,
l_act_extract_xpath, a_diff_id, a_actual_dataset_guid, a_extract_path,
l_join_xpath, l_join_xpath, l_join_xpath, a_diff_id;
return l_results;
end;

Expand Down
6 changes: 2 additions & 4 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 @@ -864,10 +864,8 @@ Rows: [ 60 differences, showing first 20 ]
l_expected_message := q'[%Actual: ut3_tester_helper.test_dummy_object_list [ count = 2 ] was expected to equal: ut3_tester_helper.test_dummy_object_list [ count = 2 ]
%Diff:
%Rows: [ 3 differences ]
%PK <ID>2</ID> - Actual: <name>Something 2</name>
%PK <ID>2</ID> - Actual: <Value>2</Value>
%PK <ID>2</ID> - Expected: <name>Something 1</name>
%PK <ID>2</ID> - Expected: <Value>1</Value>
%PK <ID>2</ID> - Actual: <name>Something 2</name><Value>2</Value>
%PK <ID>2</ID> - Expected: <name>Something 1</name><Value>1</Value>
%PK <ID>1</ID> - Extra: <TEST_DUMMY_OBJECT><ID>1</ID><name>Something 1</name><Value>1</Value></TEST_DUMMY_OBJECT>
%PK <ID>4</ID> - Missing: <TEST_DUMMY_OBJECT><ID>4</ID><name>Something 2</name><Value>2</Value></TEST_DUMMY_OBJECT>]';
l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
Expand Down
22 changes: 10 additions & 12 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 @@ -371,7 +371,7 @@ create or replace package body test_expectations_cursor is
l_expected_message := q'[Actual: refcursor [ count = 1 ] was expected to equal: refcursor [ count = 1 ]
%Diff:
%Rows: [ 1 differences ]
%Row No. 1 - Actual: <COL_4>40</COL_4><COL_3>30</COL_3>
%Row No. 1 - Actual: <COL_3>30</COL_3><COL_4>40</COL_4>
%Row No. 1 - Expected: <COL_3>3</COL_3><COL_4>4</COL_4>]';
l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
--Assert
Expand All @@ -394,7 +394,7 @@ create or replace package body test_expectations_cursor is
l_expected_message := q'[Actual: refcursor [ count = 1 ] was expected to equal: refcursor [ count = 1 ]
%Diff:
%Rows: [ 1 differences ]
%Row No. 1 - Actual: <COL_4>40</COL_4><COL_3>30</COL_3>
%Row No. 1 - Actual: <COL_3>30</COL_3><COL_4>40</COL_4>
%Row No. 1 - Expected: <COL_3>3</COL_3><COL_4>4</COL_4>]';
l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
--Assert
Expand Down Expand Up @@ -860,9 +860,9 @@ Columns:
Rows: [ 4 differences ]
Row No. 1 - Actual: <SALARY>25000</SALARY>
Row No. 1 - Expected: <SALARY>10000</SALARY>
Row No. 2 - Actual: <FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><ID>3</ID><SALARY>100000</SALARY>
Row No. 2 - Actual: <ID>3</ID><FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><SALARY>100000</SALARY>
Row No. 2 - Expected: <ID>2</ID><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><SALARY>1000</SALARY>
Row No. 3 - Actual: <FIRST_NAME>JESSICA</FIRST_NAME><LAST_NAME>JONES</LAST_NAME><ID>4</ID><SALARY>2345</SALARY>
Row No. 3 - Actual: <ID>4</ID><FIRST_NAME>JESSICA</FIRST_NAME><LAST_NAME>JONES</LAST_NAME><SALARY>2345</SALARY>
Row No. 3 - Expected: <ID>3</ID><FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><SALARY>100000</SALARY>
Row No. 4 - Extra: <GENDER>M</GENDER><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><ID>2</ID><SALARY>1000</SALARY>]';
l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
Expand Down Expand Up @@ -896,9 +896,9 @@ Columns:
Rows: [ 4 differences ]
Row No. 1 - Actual: <SALARY>25000</SALARY>
Row No. 1 - Expected: <SALARY>10000</SALARY>
Row No. 2 - Actual: <FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><ID>3</ID><SALARY>100000</SALARY>
Row No. 2 - Actual: <ID>3</ID><FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><SALARY>100000</SALARY>
Row No. 2 - Expected: <ID>2</ID><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><SALARY>1000</SALARY>
Row No. 3 - Actual: <FIRST_NAME>JESSICA</FIRST_NAME><LAST_NAME>JONES</LAST_NAME><ID>4</ID><SALARY>2345</SALARY>
Row No. 3 - Actual: <ID>4</ID><FIRST_NAME>JESSICA</FIRST_NAME><LAST_NAME>JONES</LAST_NAME><SALARY>2345</SALARY>
Row No. 3 - Expected: <ID>3</ID><FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><SALARY>100000</SALARY>
Row No. 4 - Extra: <GENDER>M</GENDER><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><ID>2</ID><SALARY>1000</SALARY>]';
l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
Expand Down Expand Up @@ -1237,10 +1237,8 @@ Rows: [ 4 differences ]
l_expected_message := q'[Actual: refcursor [ count = 1 ] was expected to equal: refcursor [ count = 1 ]
%Diff:
%Rows: [ 1 differences ]
%PK <COL_1>1</COL_1> - Actual: <COL_3>30</COL_3>
%PK <COL_1>1</COL_1> - Expected: <COL_3>3</COL_3>
%PK <COL_1>1</COL_1> - Actual: <COL_4>40</COL_4>
%PK <COL_1>1</COL_1> - Expected: <COL_4>4</COL_4>]';
%PK <COL_1>1</COL_1> - Actual: <COL_3>30</COL_3><COL_4>40</COL_4>
%PK <COL_1>1</COL_1> - Expected: <COL_3>3</COL_3><COL_4>4</COL_4>]';
l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
--Assert
ut.expect(l_actual_message).to_be_like(l_expected_message);
Expand Down Expand Up @@ -2511,7 +2509,7 @@ Diff:%
begin
l_exp_message :='ORA-20218: SQL exception thrown when fetching data from cursor:
ORA-01476: divisor is equal to zero
at "UT3$USER#.TEST_EXPECTATIONS_CURSOR%", line 2522 ut3.ut.expect(l_actual).to_equal(l_expected);%
at "UT3$USER#.TEST_EXPECTATIONS_CURSOR%", line % ut3.ut.expect(l_actual).to_equal(l_expected);%
Check the query and data for errors.';

open l_actual for
Expand All @@ -2536,7 +2534,7 @@ Check the query and data for errors.';

l_exp_message :='ORA-20218: SQL exception thrown when fetching data from cursor:
ORA-01476: divisor is equal to zero
at "UT3$USER#.TEST_EXPECTATIONS_CURSOR%", line 2547 ut3.ut.expect(l_actual).to_equal(l_expected);%
at "UT3$USER#.TEST_EXPECTATIONS_CURSOR%", line % ut3.ut.expect(l_actual).to_equal(l_expected);%
Check the query and data for errors.';

open l_expected for
Expand Down
0