SQL Queries - CAST Management Base - How to check for overlapping analysis units by technology

Purpose of Query

This query when ran on the CAST management base will provide the list of source files based analysis units for a given technology, ordered by root path in alphabetical order

You just have to compare the root paths and see if there are some overlap on the others.

A pre-requisite is to identify the two tables you will use, depending on the technology :

In management base, for each technology you have one table containing the analysis units and one table containing the projects.

Their names are CMS__ANALYSIS and CMS__PROJECT.

For instance, for Java you have tables CMS_J2EE_ANALYSIS and CMS_J2EE_PROJECT.

For .Net you have CMS_NET_ANALYSIS and CMS_NET_PROJECT.

For C++ you have CMS_CPP_ANALYSIS and CMS_CPP_PROJECT

Applicable CAST Version

Release	Yes/No
8.3.x	✅

Applicable RDBMS

RDBMS	Yes/No
CSS	✅

Query for JAVA (CSS, Oracle, SQL Server)

SELECT cja.object_id  ,
       cja.object_name,
       cja.rootpath AS rootpath
FROM   cms_j2ee_analysis cja
WHERE  cja.rootpath IS NOT NULL

UNION ALL

SELECT   cja.object_id  ,
         cja.object_name,
         cjp.rootpath AS rootpath
FROM     cms_j2ee_analysis cja
         JOIN cms_j2ee_project cjp
         ON       cja.project_id=cjp.object_id
WHERE    cja.rootpath     IS NULL
ORDER BY rootpath

Query result example

14175;"mase-transverse-serveur";"S:\SOURCES\XXXX\YYYYY\Source\mase\mase-transverse\mase-transverse-serveur\pom.xml"  
15718;"mase_mase-transverse";"S:\SOURCES\XXXX\YYYYY\Source\mase\mase-transverse\mase-transverse-serveur\src\main"

Query result interpretation

Analysis units 14175 and 15718 are overlapping, since the root path of 15718 is included in the root path of 14175.

Query for CPP (CSS, Oracle, SQLServer)

SELECT   cca.object_id  ,
         cca.object_name,
         ccp.rootpath AS rootpath
FROM     cms_cpp_analysis cca
         JOIN cms_cpp_project ccp
         ON       cca.project_id=ccp.object_id
ORDER BY ccp.rootpath

Query result example

14122;"Analyzed-noSBMSDL";"S:\Sources\XXXX\Analyzed"  
14123;"Other";"S:\SOURCES\XXXX\Other"

Query result interpretation

Analysis units 14122 and 14123 are not overlapping, since the root paths are disjoined

Query for DotNet (CSS, Oracle, SQL Server)

SELECT   cna.object_id  ,
         cna.object_name,
         cnp.rootpath AS rootpath
FROM     cms_net_analysis cna
         JOIN cms_net_project cnp
         ON       cna.project_id = cnp.object_id
ORDER BY cnp.rootpath

Query result example

33031;"HelloWorld";"C:\CASTMS-833\Deploy\HelloWorldRazor\HelloWorld\HelloWorld.csproj"  
33032;"Other";"C:\CASTMS-833\Deploy\HelloWorldRazor\HelloWorld\SubFolder\Other.csproj"

Query result interpretation

Analysis units 33031 and 33032 are overlapping, since the root path of 33032 is included in the root path of 33031.

Notes/comments

Ticket # 7761