/*
o	Transaction name
o	Technical name of the transaction, full name
o	Database name, if one
o	Schema name
o	Table name
o	Column name
o	Kind of the access : READ / WRITE

*/

/* 
 Replace <LOCAL_SCHEMA> with your AIP local schema
 E.g. : 
	set search_path to <LOCAL_SCHEMA>;
		will be replaced with
	set search_path to AIP836_LOCAL;
	
 Replace <CENTRAL_SCHEMA> with your AIP central schema
 E.g. :
	<CENTRAL_SCHEMA>.dss_objects t, 
		will be replaces with 
	AIP836_CENTAL.dss_objects t, 
	
 Replace <SNAPSHOT_ID> your snapshot name
 E.g. : 
	snap.snapshot_name = <SNAPSHOT_NAME>
		will be replaced with 
	snap.snapshot_name = 'full stack python 300 a2 01'
*/
set search_path to <LOCAL_SCHEMA>;

/*
Build Temporary data
*/


create temporary table tmp_object_columns AS 
SELECT tab.idkey AS parent_id, col.keynam AS object_name, col.idkey AS object_id, 
            proptyp.infval AS object_type_str, 
                CASE
                    WHEN proplen.idobj IS NOT NULL THEN proplen.infval
                    ELSE 0
                END AS object_length, 
                CASE
                    WHEN proppre.idobj IS NOT NULL THEN proppre.infval
                    ELSE 0
                END AS object_precision, 
            colpos.infval AS object_position, 
                CASE
                    WHEN propnull.idobj IS NOT NULL THEN propnull.infval
                    ELSE 0
                END AS object_nullisable, 
            NULL::integer AS object_pk, NULL::integer AS object_fk
           FROM keys tab, keypar kp, 
    keys col
      JOIN ( SELECT objdsc.idobj, objdsc.infval
                   FROM objdsc
                  WHERE objdsc.inftyp = 45008 AND objdsc.infsubtyp = 3) proptyp ON col.idkey = proptyp.idobj
   LEFT JOIN ( SELECT objinf.idobj, objinf.infval
              FROM objinf
             WHERE objinf.inftyp = 45001 AND objinf.infsubtyp = 2) propnull ON col.idkey = propnull.idobj
   LEFT JOIN ( SELECT objinf.idobj, objinf.infval
         FROM objinf
        WHERE objinf.inftyp = 45008 AND objinf.infsubtyp = 2) proppre ON col.idkey = proppre.idobj
   LEFT JOIN ( SELECT objinf.idobj, objinf.infval
    FROM objinf
   WHERE objinf.inftyp = 45008 AND objinf.infsubtyp = 1) proplen ON col.idkey = proplen.idobj
   JOIN ( SELECT objinf.idobj, objinf.infval
   FROM objinf
  WHERE objinf.inftyp = 45001 AND objinf.infsubtyp = 1) colpos ON col.idkey = colpos.idobj
  WHERE tab.idkey = kp.idparent AND (tab.objtyp IN ( SELECT tc.idtyp
   FROM typcat tc
  WHERE tc.idtyp = tab.objtyp AND (tc.idcatparent = ANY (ARRAY[6100, 6101])))) AND kp.idkey = col.idkey AND (col.objtyp IN ( SELECT tc.idtyp
   FROM typcat tc
  WHERE tc.idtyp = col.objtyp AND tc.idcatparent = 138019));
  
create temporary table txtemp (transaction_id integer, transaction_name varchar(255), object_id integer, object_name varchar(255), object_full_name varchar(255), object_type_str varchar(255));

insert into txtemp
select t.object_id transaction_id, t.object_name transaction_name, o.object_id , o.object_name, o.object_full_name, ot.object_type_description
from 
<CENTRAL_SCHEMA>.dss_objects t, 
<CENTRAL_SCHEMA>.dss_link_info li, 
<CENTRAL_SCHEMA>.dss_snapshots snap,
<CENTRAL_SCHEMA>.dss_objects o, 
<CENTRAL_SCHEMA>.dss_object_types ot
where t.object_type_id = 30002
and t.object_id = li.previous_object_id
and li.next_object_id = o.object_id
and o.object_type_id = ot.object_type_id
and li.snapshot_id = snap.snapshot_id
and li.link_type_id in(11002, 11003, 11004, 11005)
and snap.snapshot_name = <SNAPSHOT_NAME>
order by 1;

update txtemp set object_id=(
select distinct site_object_id from <CENTRAL_SCHEMA>.dss_translation_table where object_id=txtemp.object_id);

update txtemp set transaction_id=(
select distinct site_object_id from <CENTRAL_SCHEMA>.dss_translation_table where object_id=txtemp.transaction_id);

update txtemp set transaction_id=(
select distinct form_id from dss_transaction where object_id=txtemp.transaction_id);

alter table txtemp add transaction_fullname varchar(255) null;

update txtemp set transaction_fullname=(
select distinct object_fullname from cdt_objects where object_id=txtemp.transaction_id);

create index object_id_idx on txtemp(object_id);
create index object_type_str_idx on txtemp(object_type_str);

/*
Build Report 
Choose the option "Execute to file" from "Query" menu
*/
select distinct 
tx.transaction_name as "Transaction Name", 
tx.transaction_fullname as "Transaction Full Name", 
coalesce(db.object_name, '-') as "Database Name", 
own.object_name as "Schema Name",
tbl.object_name as "Table Name", 
col.object_name as "Column Name",
case 	when tx.object_name like 'set%' then 'WRITE'
	when tx.object_name like 'get%' then 'READ'
end as "Access Mode"
from 
	txtemp tx join acc m2e on m2e.idclr=tx.object_id and m2e.acctyplo=16777216 and m2e.acctyphi in (512,1024) -- AccessRead / AccessWrite 
			  join cdt_objects ep on m2e.idcle=ep.object_id 
			  join acc j2t on j2t.idclr=ep.object_id
			  join tmp_object_columns col on j2t.idcle=col.object_id
			  join cdt_objects tbl on col.parent_id=tbl.object_id and lower(tbl.object_type_str) like '%table'
			  join ctt_object_parents tblp on tblp.object_id=col.parent_id
			  join cdt_objects own on tblp.parent_id=own.object_id
			  left join ctt_object_parents ownp on ownp.object_id=own.object_id
			  left join cdt_objects db on ownp.parent_id=db.object_id
										and lower(db.object_type_str) like '%database'	
UNION

select distinct 
tx.transaction_name as "Transaction Name", 
tx.transaction_fullname as "Transaction Full Name", 
coalesce(db.object_name, '-') as "Database Name", 
own.object_name as "Schema Name",
tbl.object_name as "Table Name", 
col.object_name as "Column Name",
case 	when m2c.acctyphi = 512  then 'READ'
	when m2c.acctyphi = 1024 then 'WRITE'
end as "Access Mode"
from 
	txtemp tx join acc m2c on m2c.idclr=tx.object_id and m2c.acctyplo=16777216 and m2c.acctyphi in (512,1024) -- AccessRead / AccessWrite 
			  join tmp_object_columns col on m2c.idcle=col.object_id 
			  join cdt_objects tbl on col.parent_id=tbl.object_id and lower(tbl.object_type_str) like '%table'
			  join ctt_object_parents tblp on tblp.object_id=col.parent_id
			  join cdt_objects own on tblp.parent_id=own.object_id
			  left join ctt_object_parents ownp on ownp.object_id=own.object_id
			  left join cdt_objects db on ownp.parent_id=db.object_id
										and lower(db.object_type_str) like '%database'	
union 
select distinct 
tx.transaction_name as "Transaction Name", 
tx.transaction_fullname as "Transaction Full Name", 
coalesce(db.object_name, '-') as "Database Name", 
own.object_name as "Schema Name",
tbl.object_name as "Table Name", 
col.object_name as "Column Name",
case 	when acctyphi=1024 then 'WRITE'
	when acctyphi=512 then 'READ'
end as "Access Mode"
from 
	txtemp tx join acc p2c on p2c.idclr=tx.object_id and (lower(tx.object_type_str) like  '%procedure' or lower(tx.object_type_str) like  '%function')
							and p2c.acctyplo=16777216 and p2c.acctyphi in (512,1024)
			  join tmp_object_columns col on p2c.idcle=col.object_id
			  join cdt_objects tbl on col.parent_id=tbl.object_id and lower(tbl.object_type_str) like '%table'
			  join ctt_object_parents tblp on tblp.object_id=col.parent_id
			  join cdt_objects own on tblp.parent_id=own.object_id
			  left join ctt_object_parents ownp on ownp.object_id=own.object_id
			  left join cdt_objects db on ownp.parent_id=db.object_id
										and lower(db.object_type_str) like '%database'	
UNION

select distinct 
tx.transaction_name as "Transaction Name", 
tx.transaction_fullname as "Transaction Full Name", 
coalesce(db.object_name, '-') as "Database Name", 
own.object_name as "Schema Name",
tbl.object_name as "Table Name", 
col.object_name as "Column Name",
case 	when acctyphi=1024 then 'WRITE'
	when acctyphi=512 then 'READ'
end as "Access Mode"
from 
	txtemp tx join acc p2c on p2c.idclr=tx.object_id and (lower(tx.object_type_str) like '%procedure' or lower(tx.object_type_str) like '%function' )
							and p2c.acctyplo=16777216 and p2c.acctyphi in (512,1024)
			  join tmp_object_columns col on p2c.idcle=col.object_id
			  join cdt_objects tbl on col.parent_id=tbl.object_id and lower(tbl.object_type_str) like '%table'
			  join ctt_object_parents tblp on tblp.object_id=col.parent_id
			  join cdt_objects own on tblp.parent_id=own.object_id
			  left join ctt_object_parents ownp on ownp.object_id=own.object_id
			  left join cdt_objects db on ownp.parent_id=db.object_id
										and lower(db.object_type_str) like '%database'	
order by 2,3,4;

/*
Cleanup
*/
