lunedì 29 settembre 2014

Extract DB as XML



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