SQL Queries - CAST Central Base - Queries on Objects - How to check if objects are shared among modules

Purpose of Query

This SQL Query helps us to check if the objects are shared among modules. This query will be useful when you see a difference in values between application level and module level.

For instance - the number of violations at application level is not equal to sum of violations at module level

Applicable CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅
8.1.x✅
8.0.x✅
7.3.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server✅
Microsoft SQL Server✅
CSS2✅

Query for CSS

Execute the below query on the Central Base by replacing the <snaphshot_id> with your current snapshot_id.

select SNAPSHOT_ID,
       count(OBJECT_ID) as OBJECTS_NB,
       sum(SHARING_FACTOR) as SHARED_OBJECTS_NB,
       sum(NB) as VIOLATIONS,
       sum(NB * SHARING_FACTOR) as SHARED_VIOLATIONS
from (select R.SNAPSHOT_ID,
                     R.OBJECT_ID,
             count(distinct DLIM.NEXT_OBJECT_ID) as SHARING_FACTOR,
             count(distinct R.METRIC_ID) as NB
        from DSS_METRIC_TYPES T
        join DSS_METRIC_HISTO_TREE HT
          on HT.METRIC_ID = T.METRIC_ID
         and T.METRIC_GROUP = 1
       --  and HT.METRIC_CRITICAL = 1 --uncomment this line if you want to restrict the results to critical Quality rules
        join DSS_METRIC_RESULTS R
          on R.METRIC_ID = HT.METRIC_ID + 1
         and R.METRIC_VALUE_INDEX = 1
         and R.SNAPSHOT_ID = HT.SNAPSHOT_ID 
         join DSS_LINK_INFO DLI
           on DLI.SNAPSHOT_ID = HT.SNAPSHOT_ID 
          and DLI.LINK_TYPE_ID = 3
          and DLI.NEXT_OBJECT_ID = R.OBJECT_ID
         join DSS_LINK_INFO DLIM
           on DLIM.SNAPSHOT_ID = HT.SNAPSHOT_ID 
          and DLIM.LINK_TYPE_ID = 1
          and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
         join DSS_OBJECTS dsom
           on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
          and dsom.OBJECT_TYPE_ID = 20000
          and dsom.OBJECT_NAME not like '% union content%'                    
         join DSS_OBJECTS dsot
           on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
          and dsot.OBJECT_NAME not like '% union content%'
        where R.SNAPSHOT_ID = <snapshot_id>
        group by R.SNAPSHOT_ID, R.OBJECT_ID
      ) t
group by SNAPSHOT_ID;

Query result example

select SNAPSHOT_ID,
       count(OBJECT_ID) as OBJECTS_NB,
       sum(SHARING_FACTOR) as SHARED_OBJECTS_NB,
       sum(NB) as VIOLATIONS,
       sum(NB * SHARING_FACTOR) as SHARED_VIOLATIONS
from (select R.SNAPSHOT_ID,
                     R.OBJECT_ID,
             count(distinct DLIM.NEXT_OBJECT_ID) as SHARING_FACTOR,
             count(distinct R.METRIC_ID) as NB
        from DSS_METRIC_TYPES T
        join DSS_METRIC_HISTO_TREE HT
          on HT.METRIC_ID = T.METRIC_ID
         and T.METRIC_GROUP = 1
         --and HT.METRIC_CRITICAL = 1 --uncomment this line if you want to restrict the results to critical Quality rules
        join DSS_METRIC_RESULTS R
          on R.METRIC_ID = HT.METRIC_ID + 1
         and R.METRIC_VALUE_INDEX = 1
         and R.SNAPSHOT_ID = HT.SNAPSHOT_ID 
         join DSS_LINK_INFO DLI
           on DLI.SNAPSHOT_ID = HT.SNAPSHOT_ID 
          and DLI.LINK_TYPE_ID = 3
          and DLI.NEXT_OBJECT_ID = R.OBJECT_ID
         join DSS_LINK_INFO DLIM
           on DLIM.SNAPSHOT_ID = HT.SNAPSHOT_ID 
          and DLIM.LINK_TYPE_ID = 1
          and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
         join DSS_OBJECTS dsom
           on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
          and dsom.OBJECT_TYPE_ID = 20000
          and dsom.OBJECT_NAME not like '% union content%'                    
         join DSS_OBJECTS dsot
           on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
          and dsot.OBJECT_NAME not like '% union content%'
        where R.SNAPSHOT_ID = 8
        group by R.SNAPSHOT_ID, R.OBJECT_ID
      ) t
group by SNAPSHOT_ID;

Result -

“snapshot_id”;“objects_nb”;“shared_objects_nb”;“violations”;“shared_violations”
8;9232;9232;18525;18525

Query result interpretation

If the value of the objects_nb column is equal to shared_objects_nb then it means that there are no shared objects. If the value of the objects_nb column is different from shared_objects_nb then it means there are some shared objects.

If there are shared objects then you can get the details with the help of the below query -

select T.SNAPSHOT_ID, 
                dso.OBJECT_ID, 
                dso.OBJECT_NAME, 
                dso.OBJECT_FULL_NAME, 
                dsom.OBJECT_ID, 
                dsom.OBJECT_NAME, 
                dsot.OBJECT_ID, 
                dsot.OBJECT_NAME, 
                sum(NB) as violations,
                sum(NB*SHARING_FACTOR) as SHARED_VIOLATIONS
  from DSS_OBJECTS dso
  join ( select R.SNAPSHOT_ID,
                     R.OBJECT_ID,
                     count(distinct R.METRIC_ID) as NB,
                     count(distinct DLIM.PREVIOUS_OBJECT_ID) as SHARING_FACTOR
        from DSS_METRIC_TYPES T
        join DSS_METRIC_HISTO_TREE HT
          on HT.METRIC_ID = T.METRIC_ID
         and T.METRIC_GROUP = 1
        -- and HT.METRIC_CRITICAL = 1 --uncomment this line if you want to restrict the results to critical Quality rules
        join DSS_METRIC_RESULTS R
          on R.METRIC_ID = HT.METRIC_ID + 1
         and R.METRIC_VALUE_INDEX = 1
         and R.SNAPSHOT_ID = HT.SNAPSHOT_ID
         join DSS_LINK_INFO DLI
           on DLI.SNAPSHOT_ID = HT.SNAPSHOT_ID
          and DLI.LINK_TYPE_ID = 3
          and DLI.NEXT_OBJECT_ID = R.OBJECT_ID
         join DSS_LINK_INFO DLIM
           on DLIM.SNAPSHOT_ID = HT.SNAPSHOT_ID
          and DLIM.LINK_TYPE_ID = 1
          and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
         join (select * 
                               from DSS_OBJECTS dsom
                               where dsom.OBJECT_TYPE_ID = 20000
                               ) dsom 
           on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
         join DSS_OBJECTS dsot
           on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
          and dsot.OBJECT_NAME not like '% union content%'
        where R.SNAPSHOT_ID = 8
        group by R.SNAPSHOT_ID, R.OBJECT_ID,HT.metric_id
                ) T
    on T.OBJECT_ID = dso.OBJECT_ID
         join DSS_LINK_INFO DLI
           on DLI.SNAPSHOT_ID = T.SNAPSHOT_ID
          and DLI.LINK_TYPE_ID = 3
          and DLI.NEXT_OBJECT_ID = T.OBJECT_ID
         join DSS_LINK_INFO DLIM
           on DLIM.SNAPSHOT_ID = T.SNAPSHOT_ID
          and DLIM.LINK_TYPE_ID = 1
          and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
         join DSS_OBJECTS dsom
           on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
          and dsom.OBJECT_TYPE_ID = 20000
                  and dsom.OBJECT_NAME not like '% union content%'                    
         join DSS_OBJECTS dsot
           on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
          and dsot.OBJECT_NAME not like '% union content%'
where     T.SHARING_FACTOR > 1    
group by t.snapshot_id, dso.object_id,dsom.OBJECT_ID,dsot.object_id
order by T.SNAPSHOT_ID, dso.OBJECT_ID, dso.OBJECT_NAME, dso.OBJECT_FULL_NAME, dsom.OBJECT_ID, dsom.OBJECT_NAME, dsot.OBJECT_ID, dsot.OBJECT_NAME

Query result example

select T.SNAPSHOT_ID, 
                dso.OBJECT_ID, 
                dso.OBJECT_NAME, 
                dso.OBJECT_FULL_NAME, 
                dsom.OBJECT_ID, 
                dsom.OBJECT_NAME, 
                dsot.OBJECT_ID, 
                dsot.OBJECT_NAME, 
                sum(NB) as violations,
                sum(NB*SHARING_FACTOR) as SHARED_VIOLATIONS
  from DSS_OBJECTS dso
  join ( select R.SNAPSHOT_ID,
                     R.OBJECT_ID,
                     count(distinct R.METRIC_ID) as NB,
                     count(distinct DLIM.PREVIOUS_OBJECT_ID) as SHARING_FACTOR
        from DSS_METRIC_TYPES T
        join DSS_METRIC_HISTO_TREE HT
          on HT.METRIC_ID = T.METRIC_ID
         and T.METRIC_GROUP = 1
        -- and HT.METRIC_CRITICAL = 1 --uncomment this line if you want to restrict the results to critical Quality rules
        join DSS_METRIC_RESULTS R
          on R.METRIC_ID = HT.METRIC_ID + 1
         and R.METRIC_VALUE_INDEX = 1
         and R.SNAPSHOT_ID = HT.SNAPSHOT_ID
         join DSS_LINK_INFO DLI
           on DLI.SNAPSHOT_ID = HT.SNAPSHOT_ID
          and DLI.LINK_TYPE_ID = 3
          and DLI.NEXT_OBJECT_ID = R.OBJECT_ID
         join DSS_LINK_INFO DLIM
           on DLIM.SNAPSHOT_ID = HT.SNAPSHOT_ID
          and DLIM.LINK_TYPE_ID = 1
          and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
         join (select * 
                               from DSS_OBJECTS dsom
                               where dsom.OBJECT_TYPE_ID = 20000
                               ) dsom 
           on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
         join DSS_OBJECTS dsot
           on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
          and dsot.OBJECT_NAME not like '% union content%'
        where R.SNAPSHOT_ID = 8
        group by R.SNAPSHOT_ID, R.OBJECT_ID,HT.metric_id
                ) T
    on T.OBJECT_ID = dso.OBJECT_ID
         join DSS_LINK_INFO DLI
           on DLI.SNAPSHOT_ID = T.SNAPSHOT_ID
          and DLI.LINK_TYPE_ID = 3
          and DLI.NEXT_OBJECT_ID = T.OBJECT_ID
         join DSS_LINK_INFO DLIM
           on DLIM.SNAPSHOT_ID = T.SNAPSHOT_ID
          and DLIM.LINK_TYPE_ID = 1
          and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
         join DSS_OBJECTS dsom
           on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
          and dsom.OBJECT_TYPE_ID = 20000
                  and dsom.OBJECT_NAME not like '% union content%'                    
         join DSS_OBJECTS dsot
           on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
          and dsot.OBJECT_NAME not like '% union content%'
where     T.SHARING_FACTOR > 1    
group by t.snapshot_id, dso.object_id,dsom.OBJECT_ID,dsot.object_id
order by T.SNAPSHOT_ID, dso.OBJECT_ID, dso.OBJECT_NAME, dso.OBJECT_FULL_NAME, dsom.OBJECT_ID, dsom.OBJECT_NAME, dsot.OBJECT_ID, dsot.OBJECT_NAME

Result -

“snapshot_id”;“object_id”;“object_name”;“object_full_name”;“object_id”;“object_name”;“object_id”;“object_name”;“violations”;“shared_violations”
8;110115;“findHistoGradeActuelByAgent”;“RmoAfrHistoGradeDaoImpl.findHistoGradeActuelByAgent”;7;“metier”;163410;“metier-dao_7f76e979_";1;2
8;110159;“searchByCriteria”;“RmoOdmHistoStatutOmDaoImpl.searchByCriteria”;7;“metier”;163410;“metier-dao_7f76e979_";1;2

Query for Oracle

Same as Query for CSS

Query result example

Same as Query for CSS

Query result interpretation

Same as Query for CSS

Query for SQL Server

Same as Query for CSS

Query result example

Same as Query for CSS

Query result interpretation

Same as Query for CSS

Notes/comments

Ticket # 9052

Related Pages