SQL Queries - CAST Central Base - Queries on modules - How to compare the lines of code of all modules of an application for two snapshots
Purpose of Query
This page provides a query which will compare the lines of code for all modules of an application for two snapshots of the application (snapshot_id required). It assumes that two snapshots of the application have been successfully performed. A second query is provided which also does this comparison for the last two snapshots (no snapshot_id required, though application id required).
Applicable CAST Version
| Release | Yes/No |
|---|---|
| 8.2.x | ✅ |
| 8.1.x | ✅ |
| 8.0.x | ✅ |
Applicable RDBMS
| RDBMS | Yes/No |
|---|---|
| Oracle Server | ❓ |
| Microsoft SQL Server | ❓ |
| CSS2 | ✅ |
Query for CSS
Run the query below on the central base to get the results. The query requires two snapshots for the same application to be modified in the query below - a newer one and older one (snapshot_id_new and snapshot_id_old below). Snapshot_id can be found by running the query on this page: SQL Queries - CAST Central Base - Queries on snapshots - How to check the status of a snapshot
SELECT t1."Module_Name" AS "Module Name" ,
t1."NLOC" AS "Current Version" ,
t2."NLOC" AS "Previous Version" ,
t1."NLOC" - t2."NLOC" AS "Difference",
ROUND ( (t1."NLOC"- t2."NLOC") *100/ CAST(t2."NLOC" AS FLOAT),2) AS "Variance (in %)"
FROM (SELECT DISTINCT PT.MODULE_NAME "Module_Name" ,
PT.SNAPSHOT_NAME "Snapshot_Name",
QV01.MEAS_VALUE "NLOC" ,
PT.SNAPSHOT_ID "Snapshot_id"
FROM csv_portf_tree PT ,
dss_snapshot_info I,
dss_objects DSS ,
csv_quantity_val QV01
WHERE PT.SNAPSHOT_ID = <snapshot_id_new>
AND I.snapshot_ID = PT.SNAPSHOT_ID
AND I.SNAPSHOT_ID <> -1
AND I.object_id = DSS.object_id
AND DSS.object_type_id = -102
AND QV01.CONTEXT_ID = PT.MODULE_ID
AND QV01.SNAPSHOT_ID = PT.SNAPSHOT_ID
AND QV01.MEASURE_ID = 10151
ORDER BY 1,
2,
3,
4
)
t1
INNER JOIN
(SELECT DISTINCT PT.MODULE_NAME "Module_Name" ,
PT.SNAPSHOT_NAME "Snapshot_Name",
QV01.MEAS_VALUE "NLOC" ,
PT.SNAPSHOT_ID
FROM csv_portf_tree PT ,
dss_snapshot_info I,
dss_objects DSS ,
csv_quantity_val QV01
WHERE PT.SNAPSHOT_ID = <snapshot_id_old>
AND I.snapshot_ID = PT.SNAPSHOT_ID
AND I.SNAPSHOT_ID <> -1
AND I.object_id = DSS.object_id
AND DSS.object_type_id = -102
AND QV01.CONTEXT_ID = PT.MODULE_ID
AND QV01.SNAPSHOT_ID = PT.SNAPSHOT_ID
AND QV01.MEASURE_ID = 10151
ORDER BY 1,
2,
3,
4
)
t2
ON t1."Module_Name" = t2."Module_Name";
Query result example
“mod1”;79234;79217;17;0.02
Query result interpretation
Output provides the module name, the lines of code for the “new” snapshot for this module, the lines of code for the “old” snapshot for this module, the difference between the snapshots, and the percentage variation of the difference
Run the modified query below on the central base to get the results for the last two snapshots of an application’ no snapshot_id required, but the application id needs to be substituted in 3 spots in the query below and the application id can be obtained by following this page: SQL Queries - CAST Central Base - Queries on applications - How to get the ID of the application
Run the query below on the central base to get the results. The application id needs to be substituted in 3 spots in the query below and the application id can be obtained by following this page: SQL Queries - CAST Central Base - Queries on applications - How to get the ID of the applicationSELECT t1."Module_Name" AS "Module Name" ,
t1."NLOC" AS "Current Version" ,
t2."NLOC" AS "Previous Version" ,
t1."NLOC" - t2."NLOC" AS "Difference",
ROUND ( (t1."NLOC"- t2."NLOC") *100/ CAST(t2."NLOC" AS FLOAT),2) AS "Variance (in %)"
FROM (SELECT DISTINCT PT.MODULE_NAME "Module_Name" ,
PT.SNAPSHOT_NAME "Snapshot_Name",
QV01.MEAS_VALUE "NLOC" ,
PT.SNAPSHOT_ID "Snapshot_id"
FROM csv_portf_tree PT ,
dss_snapshot_info I,
dss_objects DSS ,
csv_quantity_val QV01
WHERE PT.SNAPSHOT_ID =
(SELECT MAX(SNAPSHOT_ID)
FROM DSS_SNAPSHOTS
WHERE application_id = <Application ID>
)
AND I.snapshot_ID = PT.SNAPSHOT_ID
AND I.SNAPSHOT_ID <> -1
AND I.object_id = DSS.object_id
AND DSS.object_type_id = -102
AND QV01.CONTEXT_ID = PT.MODULE_ID
AND QV01.SNAPSHOT_ID = PT.SNAPSHOT_ID
AND QV01.MEASURE_ID = 10151
ORDER BY 1,
2,
3,
4
)
t1
INNER JOIN
(SELECT DISTINCT PT.MODULE_NAME "Module_Name" ,
PT.SNAPSHOT_NAME "Snapshot_Name",
QV01.MEAS_VALUE "NLOC" ,
PT.SNAPSHOT_ID
FROM csv_portf_tree PT ,
dss_snapshot_info I,
dss_objects DSS ,
csv_quantity_val QV01
WHERE PT.SNAPSHOT_ID =
(SELECT MAX(snapshot_id)
FROM dss_snapshots
WHERE application_id = <Application ID>
AND snapshot_id NOT IN
(SELECT MAX(snapshot_id)
FROM dss_snapshots
WHERE application_id = <Application ID>
)
)
AND I.snapshot_ID = PT.SNAPSHOT_ID
AND I.SNAPSHOT_ID <> -1
AND I.object_id = DSS.object_id
AND DSS.object_type_id = -102
AND QV01.CONTEXT_ID = PT.MODULE_ID
AND QV01.SNAPSHOT_ID = PT.SNAPSHOT_ID
AND QV01.MEASURE_ID = 10151
ORDER BY 1,
2,
3,
4
)
t2
ON t1."Module_Name" = t2."Module_Name";
Query for Oracle
Enter the SQL query
Query result example
Query result interpretation
Query for SQL server
Enter the SQL query
Query result example
Query result interpretation
Notes/comments
Related Pages