Page tree
Skip to end of metadata
Go to start of metadata
Purpose of Query

This page will explain how to customize the detail procedure of the Quality rule Persistent class methods equals and hashCode must access its fields through getter methods diag to execute it manually.

This is a particular J2EE diag. The DETAIL procedure uses work tables that are populated by other stored procedure and are truncated at the end of the Snapshot computation. If we run the detail query unchanged, no rows will be raised.

Applicable in CAST Version
Release
Yes/No
8.3.x(tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server (tick)
Microsoft SQL Server (question)
CSS (tick)

The stored procedure that calculates this rule is : <LOCAL_DATA_BASE>.DIAG_SCOPE_JEEUB001

The detail query is:

 INSERT
INTO   DSS_METRIC_SCOPES
       (
              OBJECT_ID        ,
              METRIC_PARENT_ID ,
              METRIC_ID        ,
              OBJECT_PARENT_ID ,
              SNAPSHOT_ID      ,
              METRIC_NUM_VALUE ,
              METRIC_CHAR_VALUE,
              METRIC_OBJECT_ID ,
              COMPUTE_VALUE
       )
SELECT DISTINCT P1.OBJECT_ID     ,
                I_METRIC_ID      ,
                I_METRIC_CHILD_ID,
                SC.MODULE_ID     ,
                I_SNAPSHOT_ID    ,
                0                ,
                NULL             ,
                0                ,
                0
FROM            WK_DIA_INHPRSTCLS T1
                JOIN DSSAPP_MODULES SC
                ON              T1.APPLICATION_ID = SC.MODULE_ID
                JOIN DIAG_OBJECT_PARENTS P1
                ON              T1.TARGET_OBJECT_ID = P1.PARENT_ID
                JOIN CDT_OBJECTS TN
                ON              P1.OBJECT_ID = TN.OBJECT_ID
                JOIN WK_DIA_INHPRSTCLS T2
                ON              T1.HIBERNATE_ID     = T2.HIBERNATE_ID --- T1 and T2 are in the same java class.
                AND             T1.SOURCE_OBJECT_ID = T2.SOURCE_OBJECT_ID
                AND             T1.TARGET_OBJECT_ID = T2.TARGET_OBJECT_ID
                AND             T1.APPLICATION_ID   = T2.APPLICATION_ID
                JOIN DIAG_OBJECT_PARENTS P2
                ON              T2.TARGET_OBJECT_ID = P2.PARENT_ID
                JOIN DIAG_CTV_LINKS_SIMPLE IL
                ON              IL.CALLER_ID = P1.OBJECT_ID
                AND             IL.CALLED_ID = P2.OBJECT_ID
WHERE           NOT EXISTS
                ( SELECT 1
                FROM    DSS_OBJECT_EXCEPTIONS E
                WHERE   E.METRIC_ID = I_METRIC_ID
                AND     E.OBJECT_ID = P1.OBJECT_ID
                )
AND             SC.TECHNO_TYPE = 140029
                /* JEE Module */
AND             P1.OBJECT_TYPE IN (102,988) -- method, gen method
AND             TN.OBJECT_NAME IN ('equals',
                                   'hashCode')
AND             P2.OBJECT_TYPE = 103 -- field


We cannot run this query because the WK_DIA_INHPRSTCLS table is a work table that is populated by the <LOCAL_DATA_BASE>.DIAG_HIBER_JAVA (When the snapshot ends, all the work table are truncated). An other Work table is needed to run the detail query. 

To be able to run the detail query to create this table -

Query for CSS
CREATE TEMPORARY TABLE <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP AS
SELECT *
FROM   <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT;
Query for Oracle
CREATE GLOBAL TEMPORARY TABLE <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP AS
SELECT *
FROM   <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT;


Query to populate the temporary WK_DIA_JAVAINHERIT_TMP

Query for CSS
For CAST 8.3.36 and lower:

INSERT
INTO   <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP
       (
              SOURCE_OBJECT_ID,
              TARGET_OBJECT_ID,
              LEV             ,
              APPLICATION_ID
       )
SELECT DISTINCT T1.OBJECT_ID,
                T1.OBJECT_ID,
                0           ,
                T1.APPLICATION_ID
FROM                              <LOCAL_DATA_BASE>.CTT_OBJECT_APPLICATIONS T1,
                                  <LOCAL_DATA_BASE>.DSSAPP_MODULES SC
WHERE           SC.TECHNO_TYPE    = 140029
AND             T1.APPLICATION_ID = SC.MODULE_ID
AND             T1.OBJECT_TYPE IN
                (SELECT IdTyp
                FROM    <LOCAL_DATA_BASE>.TypCat
                WHERE   IdCatParent IN ( 10024,
                                        1103,1099 )
                )
AND             T1.PROPERTIES = 0;

For CAST 8.3.37 and higher:

INSERT
INTO   <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP
       (
              SOURCE_OBJECT_ID,
              TARGET_OBJECT_ID,
              LEV             ,
              APPLICATION_ID
       )
SELECT DISTINCT T1.OBJECT_ID,
                T1.OBJECT_ID,
                0           ,
                T1.APPLICATION_ID
FROM                              <LOCAL_DATA_BASE>.CTT_OBJECT_APPLICATIONS T1,
                                  <LOCAL_DATA_BASE>.DSSAPP_MODULES SC
WHERE           SC.TECHNO_TYPE    = 140029
AND             T1.APPLICATION_ID = SC.MODULE_ID
AND             T1.OBJECT_TYPE IN
                (SELECT IdTyp
                FROM    <LOCAL_DATA_BASE>.TypCat
                WHERE   IdCatParent IN ( 10024,
                                        1103,1099 )
                )
AND             T1.PROPERTIES & 255 = 0;
Query for Oracle

Same as CSS


Create another temporary table -  WK_DIA_INHPRSTCLS_TMP

Query for CSS
CREATE TEMPORARY TABLE <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP AS
SELECT *
FROM   <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS;
Query for Oracle
CREATE GLOBAL TEMPORARY TABLE <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP AS
SELECT *
FROM   <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS;

Query to populate the temporary worktable WK_DIA_INHPRSTCLS_TMP

Query for CSS
For CAST 8.3.36 and below:

INSERT
INTO   <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP
       (
              HIBERNATE_ID    ,
              SOURCE_OBJECT_ID,
              TARGET_OBJECT_ID,
              LEV             ,
              APPLICATION_ID
       )
SELECT DISTINCT T2.OBJECT_ID        ,
                DIL.SOURCE_OBJECT_ID,
                DIL.TARGET_OBJECT_ID,
                DIL.LEV             ,
                DIL.APPLICATION_ID
FROM            <LOCAL_DATA_BASE>.CTV_OBJECTS T2            ,
                <LOCAL_DATA_BASE>.DIAG_CTV_LINKS IL         ,
                <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP DIL,
                <LOCAL_DATA_BASE>.CTT_OBJECT_APPLICATIONS T1
WHERE           T1.OBJECT_TYPE IN (100,989)
AND             T1.PROPERTIES = 0
AND             T2.OBJECT_TYPE IN
                ( SELECT IdTyp
                FROM                <LOCAL_DATA_BASE>.TypCat
                WHERE   IdCatParent = 136996
                )
AND             IL.CALLER_ID      = T1.OBJECT_ID
AND             IL.CALLED_ID      = T2.OBJECT_ID
AND             IL.LINK_TYPE_LO   = 32768
AND             IL.LINK_TYPE_HI   = 0
AND             T1.OBJECT_ID      = DIL.SOURCE_OBJECT_ID
AND             T1.APPLICATION_ID = DIL.APPLICATION_ID;

For CAST 8.3.37 and higher:

INSERT
INTO   <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP
       (
              HIBERNATE_ID    ,
              SOURCE_OBJECT_ID,
              TARGET_OBJECT_ID,
              LEV             ,
              APPLICATION_ID
       )
SELECT DISTINCT T2.OBJECT_ID        ,
                DIL.SOURCE_OBJECT_ID,
                DIL.TARGET_OBJECT_ID,
                DIL.LEV             ,
                DIL.APPLICATION_ID
FROM            <LOCAL_DATA_BASE>.CTV_OBJECTS T2            ,
                <LOCAL_DATA_BASE>.DIAG_CTV_LINKS IL         ,
                <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP DIL,
                <LOCAL_DATA_BASE>.CTT_OBJECT_APPLICATIONS T1
WHERE           T1.OBJECT_TYPE IN (100,989)
AND             T1.PROPERTIES & 255 = 0
AND             T2.OBJECT_TYPE IN
                ( SELECT IdTyp
                FROM                <LOCAL_DATA_BASE>.TypCat
                WHERE   IdCatParent = 136996
                )
AND             IL.CALLER_ID      = T1.OBJECT_ID
AND             IL.CALLED_ID      = T2.OBJECT_ID
AND             IL.LINK_TYPE_LO   = 32768
AND             IL.LINK_TYPE_HI   = 0
AND             T1.OBJECT_ID      = DIL.SOURCE_OBJECT_ID
AND             T1.APPLICATION_ID = DIL.APPLICATION_ID;
Query for Oracle

Same as CSS


Below is the detail query with the two new temporary work tables -

Query for CSS
SELECT DISTINCT P1.OBJECT_ID,
                k1.keynam   ,
                k2.keynam
FROM                                                <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP T1
                JOIN                                <LOCAL_DATA_BASE>.DSSAPP_MODULES SC
                ON              T1.APPLICATION_ID   = SC.MODULE_ID
                JOIN                                <LOCAL_DATA_BASE>.DIAG_OBJECT_PARENTS P1
                ON              T1.TARGET_OBJECT_ID = P1.PARENT_ID
                JOIN                                <LOCAL_DATA_BASE>.keys k1
                ON              P1.OBJECT_ID        = k1.idkey
                JOIN                                <LOCAL_DATA_BASE>.CDT_OBJECTS TN
                ON              k1.idkey            = TN.OBJECT_ID
                JOIN                                <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP T2
                ON              T1.HIBERNATE_ID     = T2.HIBERNATE_ID
                AND             T1.SOURCE_OBJECT_ID = T2.SOURCE_OBJECT_ID
                AND             T1.TARGET_OBJECT_ID = T2.TARGET_OBJECT_ID
                AND             T1.APPLICATION_ID   = T2.APPLICATION_ID
                JOIN                                <LOCAL_DATA_BASE>.DIAG_OBJECT_PARENTS P2
                ON              T2.TARGET_OBJECT_ID = P2.PARENT_ID
                JOIN                                <LOCAL_DATA_BASE>.keys k2
                ON              P2.PARENT_ID        = k2.idkey
                JOIN                                <LOCAL_DATA_BASE>.DIAG_CTV_LINKS_SIMPLE IL
                ON              IL.CALLER_ID        = P1.OBJECT_ID
                AND             IL.CALLED_ID        = P2.OBJECT_ID
WHERE           NOT EXISTS
                ( SELECT 1
                FROM                <LOCAL_DATA_BASE>.DSS_OBJECT_EXCEPTIONS E
                WHERE   E.METRIC_ID = 7494
                AND     E.OBJECT_ID = P1.OBJECT_ID
                )
AND             SC.TECHNO_TYPE = 140029
AND             P1.OBJECT_TYPE IN (102,988)
AND             TN.OBJECT_NAME IN ('equals',
                                   'hashCode')
AND             P2.OBJECT_TYPE = 103;
Query result example
object_id ;keynam ;keynam
1112 ; arbitratory_sign ; arbitratory_sign
Query result interpretation
 This query returns the object_id and object_name (of the objects violating this quality rule / execution of detail procedure )



Query for Oracle

Same as CSS

  • No labels