I want to have a single query which is extracting the complete structure of the database into a simple XML.
For example having:
create table my_test
(
f11 number,
f12 number,
f13 varchar2(300),
CONSTRAINT my_test_pk PRIMARY KEY (f11)
);
create table my_test_2
(
f21 number,
f22 number,
f23 varchar2(300),
CONSTRAINT my_test_pk_2 PRIMARY KEY (f21, f22),
CONSTRAINT fk_column_2
FOREIGN KEY (f22)
REFERENCES my_test (f11)
);
create table my_test_3
(
f31 number,
f32 number,
f33 varchar2(300),
CONSTRAINT my_test_pk_3 PRIMARY KEY (f31),
CONSTRAINT fk_column_3
FOREIGN KEY (f32)
REFERENCES my_test (f11)
);
comment on table my_test_2 is 'this is my test_2';
comment on table my_test is 'this is my test';
comment on column my_test.f11 is 'my_test.f11';
comment on column my_test.f12 is 'my_test.f12';
comment on column my_test.f13 is 'my_test.f13';
comment on column my_test_2.f21 is 'my_test.f21';
comment on column my_test_2.f22 is 'my_test.f22';
comment on column my_test_2.f23 is 'my_test.f23';
I want to have as output:
<?xml version="1.0" encoding="UTF-8"?>
<schema name="MARCOA">
<table name="MY_TEST" pk_column_name="F11" pk_name="MY_TEST_PK" pk_owner="MARCOA">
<comment>this is my test</comment>
<column name="F11" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">
<comment>my_test.f11</comment>
<referenced_by rk_ref_column_name="F22" rk_ref_table_name="MY_TEST_2" />
<referenced_by rk_ref_column_name="F32" rk_ref_table_name="MY_TEST_3" />
</column>
<column name="F12" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="Y">
<comment>my_test.f12</comment>
</column>
<column name="F13" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">
<comment>my_test.f13</comment>
</column>
</table>
<table name="MY_TEST_2">
<comment>this is my test_2</comment>
<column name="F21" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">
<comment>my_test.f21</comment>
</column>
<column name="F22" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">
<comment>my_test.f22</comment>
<referencing fk_ref_column_name="F11" fk_ref_table_name="MY_TEST" />
</column>
<column name="F23" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">
<comment>my_test.f23</comment>
</column>
</table>
<table name="MY_TEST_3">
<comment />
<column name="F31" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">
<comment />
</column>
<column name="F32" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="Y">
<comment />
<referencing fk_ref_column_name="F11" fk_ref_table_name="MY_TEST" />
</column>
<column name="F33" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">
<comment />
</column>
</table>
</schema>
And here is the query to do it:
with csv_constraint as (
SELECT distinct owner, constraint_name, table_name, SUBSTR (SYS_CONNECT_BY_PATH (column_name , ','), 2) csv
FROM (SELECT
acc.owner, acc.constraint_name, acc.table_name, acc.column_name,
acc.position rn,
COUNT (*) OVER (partition by cc.constraint_name) cnt
FROM
all_cons_columns acc left outer join all_constraints cc
on (acc.owner = cc.owner and acc.table_name = cc.table_name and cc.constraint_type = 'P')
where (cc.owner, cc.table_name) in (select t.owner, t.table_name from all_tables t)
/* to be changed */ and cc.owner = 'MARCOA'
)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY constraint_name = prior constraint_name and rn = PRIOR rn + 1
),
xml_builder as(
select 'STARTTAG' as tagtype, '_0000' as postfix, '<?>' as xml_tag from dual union all
select 'CLOSETAG' as tagtype, '_9999' as postfix, '</?>' as xml_tag from dual
),
schemas as (
select
lpad(user_id, 4, '0') as hh_s_id,
username
from all_users
where username = user
),
tab_cols_cons as (
select
tt.owner,
tt.table_name,
cl.owner pk_owner,
cl.constraint_name pk_name,
cl.csv pk_column_name,
lpad(ss.user_id, 4, '0') as hh_s_id,
lpad(t_id, 4, '0') as hh_t_id
from (
select ROW_NUMBER( ) OVER (PARTITION BY owner ORDER BY table_name NULLS LAST) t_id, t.*
from all_tables t
)tt join all_users ss
on (tt.owner = ss.username)
left outer join csv_constraint cl
on (cl.owner = tt.owner and tt.table_name = cl.table_name)
/* to be changed */ where tt.owner = 'MARCOA' and tt.table_name like 'MY_TEST%'
),
tab_cols as (
select
atc.owner,
atc.table_name,
atc.column_name,
atc.nullable,
case
when atc.data_type = 'NUMBER' then atc.data_type || '(' || atc.data_length || ', ' || nvl(atc.data_scale, 0) || ')'
when atc.data_type = 'VARCHAR2' then atc.data_type || '(' || atc.data_length || ')'
else atc.data_type
end as col_type,
atc.num_distinct,
atc.num_nulls,
tcc.hh_s_id,
tcc.hh_t_id,
lpad(atc.column_id + 1, 4, '0') as hh_c_id
from tab_cols_cons tcc join all_tab_columns atc on (tcc.owner = atc.owner and atc.table_name = tcc.table_name)
),
refs as (
select distinct all_constraints.owner fk_owner, -- all_constraints.constraint_name, all_constraints.constraint_type,
all_constraints.constraint_name fk_name,
all_cons_columns.table_name fk_table_name,
all_cons_columns.column_name fk_column_name,
all_constraints.r_owner fk_ref_owner,
-- all_constraints.r_constraint_name fk_ref_pk_name,
fk_ref_table_name,
fk_ref_column_name
from ALL_CONSTRAINTS
join ALL_CONS_COLUMNS on ALL_CONSTRAINTS.constraint_name = ALL_CONS_COLUMNS.constraint_name
join (
select all_constraints.owner fk_owner,
all_constraints.constraint_name fk_name,
-- all_constraints.constraint_type,
all_cons_columns.table_name fk_ref_table_name,
all_cons_columns.column_name fk_ref_column_name
from ALL_CONSTRAINTS
join ALL_CONS_COLUMNS on ALL_CONSTRAINTS.constraint_name = ALL_CONS_COLUMNS.constraint_name
where constraint_type in ('P')
) on all_constraints.r_owner = fk_owner and all_constraints.r_constraint_name = fk_name
where ALL_CONSTRAINTS.owner = user
and constraint_type in ('R')
),
referenced_list as (
select fk_owner, fk_table_name rk_ref_table_name, fk_column_name rk_ref_column_name,
hh_s_id,
hh_t_id,
hh_c_id,
ROW_NUMBER( ) OVER (PARTITION BY fk_owner, fk_table_name , fk_column_name ORDER BY fk_ref_owner, fk_ref_table_name, fk_ref_column_name NULLS LAST) rd_id
from tab_cols tc join refs r on (tc.owner = r.fk_ref_owner and tc.table_name = r.fk_ref_table_name and tc.column_name = r.fk_ref_column_name )
),
referencing_list as (
select fk_ref_owner, fk_ref_table_name, fk_ref_column_name,
hh_s_id,
hh_t_id,
hh_c_id
from tab_cols tc join refs r on (tc.owner = r.fk_owner and tc.table_name = r.fk_table_name and tc.column_name = r.fk_column_name )
)
select xml
from (
select res_query, final_id, xml
from (
select
'q1' as res_query,
case
when tagtype = 'STARTTAG'
then replace(xml_builder.xml_tag, '?', 'schema name="' || username || '"')
else replace(xml_builder.xml_tag, '?', 'schema')
end as xml,
hh_s_id || xml_builder.postfix as final_id
from schemas join xml_builder on (1=1)
union all
select
'q2' as res_query,
case
when tagtype = 'STARTTAG'
then
case when pk_column_name is null
then replace(xml_builder.xml_tag, '?', 'table name="' || table_name || '"')
else replace(xml_builder.xml_tag, '?', 'table name="' || table_name || '" pk_owner = "' || pk_owner || '" pk_name = "' || pk_name || '" pk_column_name = "' || pk_column_name || '"')
end
else replace(xml_builder.xml_tag, '?', 'table')
end as xml,
hh_s_id || '_' || hh_t_id || xml_builder.postfix as final_id
from tab_cols_cons join xml_builder on (1=1)
union all
select
'q3' as res_query,
'<comment>' || comments || '</comment>' as xml,
hh_s_id || '_' || hh_t_id || '_0001' as final_id
from tab_cols_cons t join all_tab_comments atc on (t.owner = atc.owner and t.table_name = atc.table_name)
union all
select
'q4' as res_query,
case
when tagtype = 'STARTTAG'
then replace(xml_builder.xml_tag, '?', 'column name="' || column_name || '" nullable="' || nullable || '" type="' || col_type || '" num_distinct = "' || num_distinct || '" num_nulls ="' || num_nulls || '"' )
else replace(xml_builder.xml_tag, '?', 'column')
end as xml,
hh_s_id || '_' || hh_t_id || '_' || hh_c_id || xml_builder.postfix as final_id
from tab_cols join xml_builder on (1=1)
union all
select
'q5' as res_query,
'<comment>' || comments || '</comment>' as xml,
hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_0001' as final_id
from tab_cols tc join all_col_comments acc on (tc.owner = acc.owner and tc.table_name = acc.table_name and tc.column_name = acc.column_name)
union all
select
'q6' as res_query,
'<referencing fk_ref_table_name="' || fk_ref_table_name || '" fk_ref_column_name="' || fk_ref_column_name || '"/>' as xml,
hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_0002' as final_id
from referencing_list
union all
select
'q7' as res_query,
'<referenced_by rk_ref_table_name="' || rk_ref_table_name || '" rk_ref_column_name="' || rk_ref_column_name || '"/>' as xml,
hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_' || lpad(rd_id+2, 4, '0') as final_id
from referenced_list
)
order by 2
);
Nessun commento:
Posta un commento