Skip to content

Commit 77e4468

Browse files
lwasylowlwasylow
committed
Updated code with a new display
update tests to include table type Update key value pairs type
1 parent 61a334a commit 77e4468

14 files changed

Lines changed: 292 additions & 100 deletions

‎docs/userguide/advanced_data_comparison.md‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -163,10 +163,14 @@ This will show you difference in row 'TEST' regardless of order.
163163

164164
```sql
165165
Rows: [ 1 differences ]
166-
Expected: <USER_ID>-600</USER_ID> for key: TEST
167-
Actual: <USER_ID>-610</USER_ID> for key: TEST
166+
PK <USERNAME>TEST</USERNAME> - Expected: <USER_ID>-600</USER_ID>
167+
PK <USERNAME>TEST</USERNAME> - Actual: <USER_ID>-610</USER_ID>
168168
```
169169

170+
Assumption is that join by is made by column name so that what will be displayed as part of results.
171+
172+
173+
170174
**Please note that .join_by option will take longer to process due to need of parsing via primary keys.**
171175

172176
## Defining item as XPath

‎source/core/types/ut_key_xmlvalue_pair.tps‎

Lines changed: 0 additions & 20 deletions
This file was deleted.

‎source/expectations/data_values/ut_compound_data_helper.pkb‎

Lines changed: 17 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -21,15 +21,17 @@ create or replace package body ut_compound_data_helper is
2121
function get_column_info_xml(a_column_details ut_key_anyval_pair) return xmltype is
2222
l_result varchar2(4000);
2323
l_res xmltype;
24+
l_data ut_data_value := a_column_details.value;
25+
l_key varchar2(4000) := ut_utils.xmlgen_escaped_string(a_column_details.KEY);
2426
begin
25-
l_result := '<'||ut_utils.xmlgen_escaped_string(a_column_details.KEY)||' xml_valid_name="'||ut_utils.xmlgen_escaped_string(a_column_details.KEY)||'">';
26-
if a_column_details is of(ut_key_xmlvalue_pair) then
27-
l_result := l_result || (treat(a_column_details as ut_key_xmlvalue_pair).value.getstringval());
27+
l_result := '<'||l_key||' xml_valid_name="'||l_key||'">';
28+
if l_data is of(ut_data_value_xmltype) then
29+
l_result := l_result || (treat(l_data as ut_data_value_xmltype).to_string);
2830
else
29-
l_result := l_result || ut_utils.xmlgen_escaped_string((treat(a_column_details as ut_key_varcharvalue_pair).value));
31+
l_result := l_result || ut_utils.xmlgen_escaped_string((treat(l_data as ut_data_value_varchar2).data_value));
3032
end if;
31-
l_result := l_result ||'</'||ut_utils.xmlgen_escaped_string(a_column_details.KEY)||'>';
3233

34+
l_result := l_result ||'</'||l_key||'>';
3335
return xmltype(l_result);
3436
end;
3537

@@ -155,16 +157,13 @@ create or replace package body ut_compound_data_helper is
155157
return l_results;
156158
end;
157159

158-
function get_pk_value (a_join_by_xpath varchar2,a_item_data xmltype) return varchar2 is
159-
l_pk_value varchar2(4000);
160+
function get_pk_value (a_join_by_xpath varchar2,a_item_data xmltype) return clob is
161+
l_pk_value clob;
160162
begin
161-
select listagg(extractvalue(xmlelement("ROW",column_value),a_join_by_xpath),':') within group ( order by 1)
162-
into l_pk_value
163-
from table(xmlsequence(extract(a_item_data,'/*/*')));
164-
165-
return l_pk_value;
163+
select extract(a_item_data,a_join_by_xpath).getclobval() into l_pk_value from dual;
164+
return l_pk_value;
166165
exception when no_data_found then
167-
return 'null ';
166+
return 'null';
168167
end;
169168

170169
function get_rows_diff(
@@ -242,19 +241,19 @@ create or replace package body ut_compound_data_helper is
242241
unpivot ( data_item for diff_type in (exp_item as 'Expected:', act_item as 'Actual:') )
243242
)
244243
union all
245-
select case when exp.pk_hash is null then 'Extra:' else 'Missing:' end as diff_type,
244+
select case when exp.pk_hash is null then 'Extra' else 'Missing' end as diff_type,
246245
xmlserialize(content nvl(exp.item_data, act.item_data) no indent) diffed_row,
247246
coalesce(exp.pk_hash,act.pk_hash) pk_hash,
248247
coalesce(exp.pk_value,act.pk_value) pk_value
249-
from (select extract(ucd.item_data,'/*/*') item_data,i.pk_hash,
248+
from (select extract(deletexml(ucd.item_data, :join_by),'/*/*') item_data,i.pk_hash,
250249
ut_compound_data_helper.get_pk_value(:join_by,item_data) pk_value
251250
from ut_compound_data_tmp ucd,
252251
diff_info i
253252
where ucd.data_id = :self_guid
254253
and ucd.item_hash = i.item_hash
255254
) exp
256255
full outer join (
257-
select extract(ucd.item_data,'/*/*') item_data,i.pk_hash,
256+
select extract(deletexml(ucd.item_data, :join_by),'/*/*') item_data,i.pk_hash,
258257
ut_compound_data_helper.get_pk_value(:join_by,item_data) pk_value
259258
from ut_compound_data_tmp ucd,
260259
diff_info i
@@ -272,7 +271,7 @@ create or replace package body ut_compound_data_helper is
272271
a_exclude_xpath, a_include_xpath, a_expected_dataset_guid,
273272
a_join_by_xpath,
274273
a_exclude_xpath, a_include_xpath, a_actual_dataset_guid,
275-
a_join_by_xpath,a_expected_dataset_guid,a_join_by_xpath, a_actual_dataset_guid,
274+
a_join_by_xpath,a_join_by_xpath,a_expected_dataset_guid,a_join_by_xpath,a_join_by_xpath, a_actual_dataset_guid,
276275
a_max_rows;
277276

278277
return l_results;
@@ -502,7 +501,7 @@ create or replace package body ut_compound_data_helper is
502501
with xpaths_tab as (select column_value xpath from table(:xpath_tabs)),
503502
expected_column_info as ( select :expected as item_data from dual ),
504503
actual_column_info as ( select :actual as item_data from dual )
505-
select xpath,diif_type from
504+
select REGEXP_SUBSTR (xpath,'[^(/\*/)](.+)$'),diif_type from
506505
(
507506
(select xpath,'e' diif_type from xpaths_tab
508507
minus

‎source/expectations/data_values/ut_compound_data_helper.pks‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ create or replace package ut_compound_data_helper authid definer is
5959
a_expected xmltype, a_actual xmltype, a_exclude_xpath varchar2, a_include_xpath varchar2
6060
) return tt_column_diffs;
6161

62-
function get_pk_value (a_join_by_xpath varchar2,a_item_data xmltype) return varchar2;
62+
function get_pk_value (a_join_by_xpath varchar2,a_item_data xmltype) return clob;
6363

6464
function compare_type(a_join_by_xpath in varchar2,a_unordered boolean) return varchar2;
6565

‎source/expectations/data_values/ut_compound_data_value.tpb‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -97,7 +97,7 @@ create or replace type body ut_compound_data_value as
9797
function get_diff_message (a_row_diff ut_compound_data_helper.t_row_diffs,a_compare_type varchar2) return varchar2 is
9898
begin
9999
if a_compare_type = ut_compound_data_helper.gc_compare_join_by and a_row_diff.pk_value is not null then
100-
return ' '||rpad(a_row_diff.diff_type,10)||a_row_diff.diffed_row||' for key: '||a_row_diff.pk_value;
100+
return ' PK '||a_row_diff.pk_value||' - '||rpad(a_row_diff.diff_type,10)||a_row_diff.diffed_row;
101101
elsif a_compare_type = ut_compound_data_helper.gc_compare_join_by or a_compare_type = ut_compound_data_helper.gc_compare_normal then
102102
return ' Row No. '||a_row_diff.rn||' - '||rpad(a_row_diff.diff_type,10)||a_row_diff.diffed_row;
103103
elsif a_compare_type = ut_compound_data_helper.gc_compare_unordered then

‎source/expectations/data_values/ut_curr_usr_compound_helper.pkb‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -7,18 +7,18 @@ create or replace package body ut_curr_usr_compound_helper is
77

88

99
function get_column_type(a_desc_rec dbms_sql.desc_rec3,a_desc_user_types boolean := false) return ut_key_anyval_pair is
10+
l_data ut_data_value;
1011
l_result ut_key_anyval_pair;
1112
l_data_type varchar2(500) := 'unknown datatype';
12-
begin
13+
begin
1314
if g_type_name_map.exists(a_desc_rec.col_type) then
14-
l_result := ut_key_varcharvalue_pair(a_desc_rec.col_name,g_type_name_map(a_desc_rec.col_type));
15+
l_data := ut_data_value_varchar2(g_type_name_map(a_desc_rec.col_type));
1516
elsif a_desc_rec.col_type = g_user_defined_type and a_desc_user_types then
16-
l_result := ut_key_xmlvalue_pair(a_desc_rec.col_name,
17-
get_user_defined_type(a_desc_rec.col_schema_name,a_desc_rec.col_type_name));
17+
l_data :=ut_data_value_xmltype(get_user_defined_type(a_desc_rec.col_schema_name,a_desc_rec.col_type_name));
1818
elsif a_desc_rec.col_schema_name is not null and a_desc_rec.col_type_name is not null then
19-
l_result := ut_key_varcharvalue_pair(a_desc_rec.col_name,a_desc_rec.col_schema_name||'.'||a_desc_rec.col_type_name);
19+
l_data := ut_data_value_varchar2(a_desc_rec.col_schema_name||'.'||a_desc_rec.col_type_name);
2020
end if;
21-
return l_result;
21+
return ut_key_anyval_pair(a_desc_rec.col_name,l_data);
2222
end;
2323

2424
function get_columns_info(a_columns_tab dbms_sql.desc_tab3, a_columns_count integer,a_desc_user_types boolean := false) return ut_key_anyval_pairs is

‎source/expectations/data_values/ut_data_value_xmltype.tpb‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
create or replace type body ut_data_value_xmltype as
2+
/*
3+
utPLSQL - Version 3
4+
Copyright 2016 - 2017 utPLSQL Project
5+
6+
Licensed under the Apache License, Version 2.0 (the "License"):
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
10+
http://www.apache.org/licenses/LICENSE-2.0
11+
12+
Unless required by applicable law or agreed to in writing, software
13+
distributed under the License is distributed on an "AS IS" BASIS,
14+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15+
See the License for the specific language governing permissions and
16+
limitations under the License.
17+
*/
18+
19+
constructor function ut_data_value_xmltype(self in out nocopy ut_data_value_xmltype, a_value xmltype) return self as result is
20+
begin
21+
self.data_value := a_value;
22+
self.self_type := $$plsql_unit;
23+
self.data_type := 'xmltype';
24+
return;
25+
end;
26+
27+
overriding member function is_null return boolean is
28+
begin
29+
return (self.data_value is null);
30+
end;
31+
32+
overriding member function to_string return varchar2 is
33+
begin
34+
return ut_utils.to_string(self.data_value.getClobVal());
35+
end;
36+
37+
overriding member function compare_implementation(a_other ut_data_value) return integer is
38+
l_result integer;
39+
l_other ut_data_value_xmltype;
40+
begin
41+
if a_other is of (ut_data_value_xmltype) then
42+
l_other := treat(a_other as ut_data_value_xmltype);
43+
l_result := dbms_lob.compare(self.data_value.getClobVal(),l_other.data_value.getClobVal());
44+
end if;
45+
46+
return l_result;
47+
end;
48+
49+
end;
50+
/

source/core/types/ut_key_varcharvalue_pair.tps renamed to source/expectations/data_values/ut_data_value_xmltype.tps

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
create or replace type ut_key_varcharvalue_pair under ut_key_anyval_pair (
1+
create or replace type ut_data_value_xmltype under ut_data_value(
22
/*
33
utPLSQL - Version 3
44
Copyright 2016 - 2017 utPLSQL Project
@@ -15,6 +15,10 @@ create or replace type ut_key_varcharvalue_pair under ut_key_anyval_pair (
1515
See the License for the specific language governing permissions and
1616
limitations under the License.
1717
*/
18-
value varchar2(4000)
18+
data_value xmltype,
19+
constructor function ut_data_value_xmltype(self in out nocopy ut_data_value_xmltype, a_value xmltype) return self as result,
20+
overriding member function is_null return boolean,
21+
overriding member function to_string return varchar2,
22+
overriding member function compare_implementation(a_other ut_data_value) return integer
1923
)
2024
/

source/core/types/ut_key_anyval_pair.tps renamed to source/expectations/data_values/ut_key_anyval_pair.tps

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@ create or replace type ut_key_anyval_pair force as object(
1515
See the License for the specific language governing permissions and
1616
limitations under the License.
1717
*/
18-
key varchar2(4000)
18+
key varchar2(4000),
19+
value ut_data_value
1920
) not final
2021
/

source/core/types/ut_key_anyval_pairs.tps renamed to source/expectations/data_values/ut_key_anyval_pairs.tps

File renamed without changes.

0 commit comments

Comments
 (0)