Skip to content

Commit 3759e90

Browse files
lwasylowlwasylow
committed
Phase 2. Removal of x path and clean-up deprecated code
1 parent 416df70 commit 3759e90

12 files changed

Lines changed: 140 additions & 496 deletions

‎source/expectations/data_values/ut_compound_data_helper.pkb‎

Lines changed: 107 additions & 106 deletions
Original file line numberDiff line numberDiff line change
@@ -20,23 +20,10 @@ create or replace package body ut_compound_data_helper is
2020
g_diff_count integer;
2121
g_filter_tab ut_varchar2_list;
2222

23-
function get_column_info_xml(a_column_details ut_key_anyval_pair) return xmltype is
24-
l_result varchar2(4000);
25-
l_res xmltype;
26-
l_data ut_data_value := a_column_details.value;
27-
l_key varchar2(4000) := ut_utils.xmlgen_escaped_string(a_column_details.KEY);
28-
l_is_diff number;
29-
begin
30-
l_result := '<'||l_key||' xml_valid_name="'||l_key;
31-
if l_data is of(ut_data_value_xmltype) then
32-
l_result := l_result||'" sql_diffable="0">' ||trim( both '''' from (treat(l_data as ut_data_value_xmltype).to_string));
33-
else
34-
l_is_diff := ut_curr_usr_compound_helper.is_sql_compare_int((treat(l_data as ut_data_value_varchar2).data_value));
35-
l_result := l_result||'" sql_diffable="'||l_is_diff||'">' || ut_utils.xmlgen_escaped_string((treat(l_data as ut_data_value_varchar2).data_value));
36-
end if;
37-
l_result := l_result ||'</'||l_key||'>';
38-
return xmltype(l_result);
39-
end;
23+
type t_type_name_map is table of varchar2(100) index by binary_integer;
24+
g_type_name_map t_type_name_map;
25+
g_anytype_name_map t_type_name_map;
26+
g_anytype_collection_name t_type_name_map;
4027

4128
function get_columns_filter(
4229
a_exclude_xpath varchar2, a_include_xpath varchar2,
@@ -60,32 +47,6 @@ create or replace package body ut_compound_data_helper is
6047
return l_filter;
6148
end;
6249

63-
/**
64-
* Current get column filter shaving off ROW tag during extract, this not working well with include and XMLTABLE option
65-
* so when there is extract we artificially inject removed tag
66-
**/
67-
function get_columns_row_filter(
68-
a_exclude_xpath varchar2, a_include_xpath varchar2,
69-
a_table_alias varchar2 := 'ucd', a_column_alias varchar2 := 'item_data'
70-
) return varchar2 is
71-
l_filter varchar2(32767);
72-
l_source_column varchar2(500) := a_table_alias||'.'||a_column_alias;
73-
begin
74-
-- this SQL statement is constructed in a way that we always get the same number and ordering of substitution variables
75-
-- That is, we always get: l_exclude_xpath, l_include_xpath
76-
-- regardless if the variables are NULL (not to be used) or NOT NULL and will be used for filtering
77-
if a_exclude_xpath is null and a_include_xpath is null then
78-
l_filter := ':l_exclude_xpath, :l_include_xpath, '||l_source_column||' as '||a_column_alias;
79-
elsif a_exclude_xpath is not null and a_include_xpath is null then
80-
l_filter := 'deletexml( '||l_source_column||', :l_exclude_xpath ) as '||a_column_alias||', :l_include_xpath';
81-
elsif a_exclude_xpath is null and a_include_xpath is not null then
82-
l_filter := ':l_exclude_xpath, xmlelement("ROW",extract( '||l_source_column||', :l_include_xpath )) as '||a_column_alias;
83-
elsif a_exclude_xpath is not null and a_include_xpath is not null then
84-
l_filter := 'xmlelement("ROW",extract( deletexml( '||l_source_column||', :l_exclude_xpath ), :l_include_xpath )) as '||a_column_alias;
85-
end if;
86-
return l_filter;
87-
end;
88-
8950
function get_columns_diff(a_expected ut_cursor_column_tab, a_actual ut_cursor_column_tab)
9051
return tt_column_diffs is
9152
l_column_filter varchar2(32767);
@@ -295,8 +256,8 @@ create or replace package body ut_compound_data_helper is
295256
end if;
296257
end;
297258

298-
function gen_compare_sql(a_column_info xmltype,a_inclusion_type boolean, a_is_negated boolean,a_unordered boolean,
299-
a_other ut_data_value_refcursor :=null, a_join_by_list ut_varchar2_list:=ut_varchar2_list() ) return clob is
259+
function gen_compare_sql(a_inclusion_type boolean, a_is_negated boolean,a_unordered boolean,
260+
a_other ut_data_value_refcursor :=null, a_join_by_list ut_varchar2_list:=ut_varchar2_list() ) return clob is
300261
l_compare_sql clob;
301262
l_temp_string varchar2(32767);
302263

@@ -428,7 +389,6 @@ create or replace package body ut_compound_data_helper is
428389
return l_column_list;
429390
end;
430391

431-
432392
function get_rows_diff_by_sql(a_act_cursor_info ut_cursor_column_tab,a_exp_cursor_info ut_cursor_column_tab,
433393
a_expected_dataset_guid raw, a_actual_dataset_guid raw, a_diff_id raw,
434394
a_join_by_list ut_varchar2_list, a_unordered boolean
@@ -608,65 +568,6 @@ create or replace package body ut_compound_data_helper is
608568
return dbms_utility.get_hash_value(a_string,a_base,a_size);
609569
end;
610570

611-
function columns_hash(
612-
a_data_value_cursor ut_data_value_refcursor, a_exclude_xpath varchar2, a_include_xpath varchar2,
613-
a_hash_type binary_integer := dbms_crypto.hash_sh1
614-
) return t_hash is
615-
l_cols_hash t_hash;
616-
begin
617-
if not a_data_value_cursor.is_null then
618-
execute immediate
619-
q'[select dbms_crypto.hash(replace(x.item_data.getclobval(),'>CHAR<','>VARCHAR2<'),]'||a_hash_type||') ' ||
620-
' from ( select '||get_columns_filter(a_exclude_xpath, a_include_xpath)||
621-
' from (select :columns_info as item_data from dual ) ucd' ||
622-
' ) x'
623-
into l_cols_hash using a_exclude_xpath,a_include_xpath, a_data_value_cursor.columns_info;
624-
end if;
625-
return l_cols_hash;
626-
end;
627-
628-
function is_pk_exists(a_expected_cursor xmltype,a_actual_cursor xmltype, a_exclude_xpath varchar2, a_include_xpath varchar2,a_join_by_xpath varchar2)
629-
return tt_missing_pk is
630-
l_pk_xpath_tabs ut_varchar2_list := ut_varchar2_list();
631-
l_column_filter varchar2(32767);
632-
l_no_missing_keys tt_missing_pk := tt_missing_pk();
633-
634-
begin
635-
if a_join_by_xpath is not null then
636-
l_pk_xpath_tabs := ut_utils.string_to_table(a_join_by_xpath,'|');
637-
l_column_filter := get_columns_row_filter(a_exclude_xpath, a_include_xpath);
638-
639-
execute immediate q'[
640-
with xpaths_tab as (select column_value xpath from table(:xpath_tabs)),
641-
expected_column_info as ( select :expected as item_data from dual ),
642-
actual_column_info as ( select :actual as item_data from dual )
643-
select REGEXP_SUBSTR (xpath,'[^(/\*/)](.+)$'),diif_type from
644-
(
645-
(select xpath,'e' diif_type from xpaths_tab
646-
minus
647-
select xpath,'e' diif_type
648-
from ( select ]'||l_column_filter||q'[ from expected_column_info ucd) x
649-
,xpaths_tab
650-
where xmlexists (xpaths_tab.xpath passing x.item_data)
651-
)
652-
union all
653-
(select xpath,'a' diif_type from xpaths_tab
654-
minus
655-
select xpath,'a' diif_type
656-
from ( select ]'||l_column_filter||q'[ from actual_column_info ucd) x
657-
,xpaths_tab
658-
where xmlexists (xpaths_tab.xpath passing x.item_data)
659-
)
660-
)]' bulk collect into l_no_missing_keys
661-
using l_pk_xpath_tabs,a_expected_cursor,a_actual_cursor,
662-
a_exclude_xpath, a_include_xpath,
663-
a_exclude_xpath, a_include_xpath;
664-
665-
end if;
666-
667-
return l_no_missing_keys;
668-
end;
669-
670571
procedure insert_diffs_result(a_diff_tab t_diff_tab, a_diff_id raw) is
671572
begin
672573
forall idx in 1..a_diff_tab.count
@@ -853,6 +754,106 @@ create or replace package body ut_compound_data_helper is
853754
end loop;
854755
return l_warn_msg;
855756
end;
757+
758+
function getxmlchildren(p_parent_name varchar2,a_cursor_table ut_cursor_column_tab)
759+
return xmltype is
760+
l_result xmltype;
761+
begin
762+
763+
select xmlagg(xmlelement(evalname t.column_name,t.column_type,
764+
getxmlchildren(t.column_name,a_cursor_table)))
765+
into l_result
766+
from table(a_cursor_table) t
767+
where (p_parent_name is not null and parent_name = p_parent_name)
768+
or (p_parent_name is null and parent_name is null)
769+
having count(*) > 0;
770+
771+
772+
return l_result;
773+
end;
774+
775+
function is_sql_compare_allowed(a_type_name varchar2) return boolean is
776+
begin
777+
--clob/blob/xmltype/object/nestedcursor/nestedtable
778+
if a_type_name IN (g_type_name_map(dbms_sql.blob_type),
779+
g_type_name_map(dbms_sql.clob_type),
780+
g_type_name_map(dbms_sql.bfile_type),
781+
g_anytype_name_map(dbms_types.typecode_namedcollection))
782+
then
783+
return false;
784+
else
785+
return true;
786+
end if;
787+
end;
788+
789+
function is_collection (a_owner varchar2,a_type_name varchar2, a_anytype_code in integer :=null) return boolean is
790+
l_type_view varchar2(200) := ut_metadata.get_dba_view('dba_types');
791+
l_typecode varchar2(100);
792+
begin
793+
if a_anytype_code is null then
794+
execute immediate 'select typecode from '||l_type_view ||'
795+
where owner = :owner and type_name = :typename'
796+
into l_typecode using a_owner,a_type_name;
797+
798+
return l_typecode = 'COLLECTION';
799+
else
800+
return a_anytype_code in (dbms_types.typecode_varray,dbms_types.typecode_table,dbms_types.typecode_namedcollection);
801+
end if;
802+
803+
exception
804+
when no_data_found then
805+
return false;
806+
end;
807+
808+
function get_column_type_desc(a_type_code in integer, a_dbms_sql_desc in boolean) return varchar2 is
809+
begin
810+
return case when a_dbms_sql_desc then g_type_name_map(a_type_code) else g_anytype_name_map(a_type_code) end;
811+
end;
812+
813+
814+
begin
815+
g_anytype_name_map(dbms_types.typecode_date) := 'DATE';
816+
g_anytype_name_map(dbms_types.typecode_number) := 'NUMBER';
817+
g_anytype_name_map(dbms_types.typecode_raw) := 'RAW';
818+
g_anytype_name_map(dbms_types.typecode_char) := 'CHAR';
819+
g_anytype_name_map(dbms_types.typecode_varchar2) := 'VARCHAR2';
820+
g_anytype_name_map(dbms_types.typecode_varchar) := 'VARCHAR';
821+
g_anytype_name_map(dbms_types.typecode_blob) := 'BLOB';
822+
g_anytype_name_map(dbms_types.typecode_bfile) := 'BFILE';
823+
g_anytype_name_map(dbms_types.typecode_clob) := 'CLOB';
824+
g_anytype_name_map(dbms_types.typecode_timestamp) := 'TIMESTAMP';
825+
g_anytype_name_map(dbms_types.typecode_timestamp_tz) := 'TIMESTAMP WITH TIME ZONE';
826+
g_anytype_name_map(dbms_types.typecode_timestamp_ltz) := 'TIMESTAMP WITH LOCAL TIME ZONE';
827+
g_anytype_name_map(dbms_types.typecode_interval_ym) := 'INTERVAL YEAR TO MONTH';
828+
g_anytype_name_map(dbms_types.typecode_interval_ds) := 'INTERVAL DAY TO SECOND';
829+
g_anytype_name_map(dbms_types.typecode_bfloat) := 'BINARY_FLOAT';
830+
g_anytype_name_map(dbms_types.typecode_bdouble) := 'BINARY_DOUBLE';
831+
g_anytype_name_map(dbms_types.typecode_urowid) := 'UROWID';
832+
g_anytype_name_map(dbms_types.typecode_varray) := 'VARRRAY';
833+
g_anytype_name_map(dbms_types.typecode_table) := 'TABLE';
834+
g_anytype_name_map(dbms_types.typecode_namedcollection) := 'NAMEDCOLLECTION';
835+
836+
g_type_name_map( dbms_sql.binary_bouble_type ) := 'BINARY_DOUBLE';
837+
g_type_name_map( dbms_sql.bfile_type ) := 'BFILE';
838+
g_type_name_map( dbms_sql.binary_float_type ) := 'BINARY_FLOAT';
839+
g_type_name_map( dbms_sql.blob_type ) := 'BLOB';
840+
g_type_name_map( dbms_sql.long_raw_type ) := 'LONG RAW';
841+
g_type_name_map( dbms_sql.char_type ) := 'CHAR';
842+
g_type_name_map( dbms_sql.clob_type ) := 'CLOB';
843+
g_type_name_map( dbms_sql.long_type ) := 'LONG';
844+
g_type_name_map( dbms_sql.date_type ) := 'DATE';
845+
g_type_name_map( dbms_sql.interval_day_to_second_type ) := 'INTERVAL DAY TO SECOND';
846+
g_type_name_map( dbms_sql.interval_year_to_month_type ) := 'INTERVAL YEAR TO MONTH';
847+
g_type_name_map( dbms_sql.raw_type ) := 'RAW';
848+
g_type_name_map( dbms_sql.timestamp_type ) := 'TIMESTAMP';
849+
g_type_name_map( dbms_sql.timestamp_with_tz_type ) := 'TIMESTAMP WITH TIME ZONE';
850+
g_type_name_map( dbms_sql.timestamp_with_local_tz_type ) := 'TIMESTAMP WITH LOCAL TIME ZONE';
851+
g_type_name_map( dbms_sql.varchar2_type ) := 'VARCHAR2';
852+
g_type_name_map( dbms_sql.number_type ) := 'NUMBER';
853+
g_type_name_map( dbms_sql.rowid_type ) := 'ROWID';
854+
g_type_name_map( dbms_sql.urowid_type ) := 'UROWID';
855+
g_type_name_map( dbms_sql.user_defined_type ) := 'USER_DEFINED_TYPE';
856+
g_type_name_map( dbms_sql.ref_type ) := 'REF_TYPE';
856857

857-
end;
858+
end;
858859
/

‎source/expectations/data_values/ut_compound_data_helper.pks‎

Lines changed: 10 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -58,9 +58,6 @@ create or replace package ut_compound_data_helper authid definer is
5858

5959
type t_diff_tab is table of t_diff_rec;
6060

61-
62-
function get_column_info_xml(a_column_details ut_key_anyval_pair) return xmltype;
63-
6461
function get_columns_filter(
6562
a_exclude_xpath varchar2, a_include_xpath varchar2,
6663
a_table_alias varchar2 := 'ucd', a_column_alias varchar2 := 'item_data'
@@ -87,17 +84,9 @@ create or replace package ut_compound_data_helper authid definer is
8784
function get_hash(a_data clob, a_hash_type binary_integer := dbms_crypto.hash_sh1) return t_hash;
8885

8986
function get_fixed_size_hash(a_string varchar2, a_base integer :=0,a_size integer :=30) return number;
90-
91-
function columns_hash(
92-
a_data_value_cursor ut_data_value_refcursor, a_exclude_xpath varchar2, a_include_xpath varchar2,
93-
a_hash_type binary_integer := dbms_crypto.hash_sh1
94-
) return t_hash;
95-
96-
function is_pk_exists(a_expected_cursor xmltype, a_actual_cursor xmltype, a_exclude_xpath varchar2, a_include_xpath varchar2,a_join_by_xpath varchar2)
97-
return tt_missing_pk;
9887

99-
function gen_compare_sql(a_column_info xmltype, a_inclusion_type boolean, a_is_negated boolean, a_unordered boolean,
100-
a_other ut_data_value_refcursor :=null, a_join_by_list ut_varchar2_list:=ut_varchar2_list() ) return clob;
88+
function gen_compare_sql(a_inclusion_type boolean, a_is_negated boolean, a_unordered boolean,
89+
a_other ut_data_value_refcursor :=null, a_join_by_list ut_varchar2_list:=ut_varchar2_list() ) return clob;
10190

10291
procedure insert_diffs_result(a_diff_tab t_diff_tab, a_diff_id raw);
10392

@@ -127,5 +116,13 @@ create or replace package ut_compound_data_helper authid definer is
127116

128117
function generate_missing_cols_warn_msg(a_missing_columns ut_varchar2_list,a_attribute in varchar2) return varchar2;
129118

119+
function getxmlchildren(p_parent_name varchar2,a_cursor_table ut_cursor_column_tab) return xmltype;
120+
121+
function is_sql_compare_allowed(a_type_name varchar2) return boolean;
122+
123+
function is_collection (a_owner varchar2,a_type_name varchar2, a_anytype_code in integer :=null) return boolean;
124+
125+
function get_column_type_desc(a_type_code in integer, a_dbms_sql_desc in boolean) return varchar2;
126+
130127
end;
131128
/

‎source/expectations/data_values/ut_compound_data_value.tpb‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -219,9 +219,8 @@ create or replace type body ut_compound_data_value as
219219
l_other := treat(a_other as ut_compound_data_value);
220220
l_diff_id := ut_compound_data_helper.get_hash(self.data_id||l_other.data_id);
221221

222-
open l_loop_curs for ut_compound_data_helper.gen_compare_sql(treat(a_other as ut_data_value_refcursor).col_info_desc,
223-
a_inclusion_compare, a_is_negated, a_unordered, treat(a_other as ut_data_value_refcursor),
224-
a_join_by_list ) using self.data_id,l_other.data_id;
222+
open l_loop_curs for ut_compound_data_helper.gen_compare_sql(a_inclusion_compare, a_is_negated, a_unordered,
223+
treat(a_other as ut_data_value_refcursor), a_join_by_list ) using self.data_id,l_other.data_id;
225224
loop
226225
fetch l_loop_curs bulk collect into l_diff_tab limit l_max_rows;
227226
exit when l_diff_tab.count = 0;

0 commit comments

Comments
 (0)