February 7, 2009

Sample SQL Statements for Custom Reporting

Surround SCM

Works with Surround SCM 2009

Here you'll find a number of T-SQL queries to fetch various information from the Surround SCM database, along with explanations as necessary. The most common use of SQL access is dashboard reporting, but there are a number of reasons you might want to query the database directly (advanced statistical analysis for example).

[toc]

MS SQL Syntax Queries

File history by date

Query statement

SELECT convert(varchar(20),evt.EventDate,100)as Date, bf.[FileName] [File], b.BranchName Branch, u.UserName [User],
CASE evt.EventType
   WHEN '1' THEN 'Add'
   WHEN '2' THEN 'Add to Branch'
   WHEN '3' THEN 'Add From Branch'
   WHEN '4' THEN 'Check In'
END AS [User Event]
FROM BRANCHFILE bf, [EVENT] evt, BRANCH b, SCMUSERS u
WHERE evt.BranchFileID = bf.BranchFileID
and b.BranchID = evt.BranchID
and u.idUser = evt.UserID
ORDER BY EventDate DESC, [FILE]
The event table contains every historic event for each file. The EventType is the actual event. There isn't a table to cross-reference to get the event name at this time, which is why I used the case statement. The case statement is incomplete as I did not figure out all of the events at that time. I have posted a table with all of the events here.

Report Output

The Query above was used in a MS Reporting services project to create the report below:

Figure 1 - Sample report

Function to build repository path

The full repository path of a file is not stored anywhere. You can find out the repository that a file belongs to, and then find the parent of that repository, find the parent of that one, until you get to the root of the branch. Since this may be a repetitive function, I have created the function below:

Raw SQL Format

CREATE FUNCTION GetRepoPath(@BranchRepoID INT)
RETURNS VARCHAR(2000)

AS

BEGIN

   DECLARE @RepoPath varchar(2000)
   DECLARE @ParentRepo varchar(255)
   DECLARE @CurRepo varchar(255)
   DECLARE @FullRepoPath varchar(2000)

   DECLARE @RepoID INT
   DECLARE @ParentID INT

   SET @RepoPath = ''

   SELECT @RepoID = RepositoryID From BranchRepository br WHERE br.BranchRepoID = @BranchRepoID
   SELECT @CurRepo = RepositoryName From BRANCHREPOSITORY br WHERE br.RepositoryID = @RepoID
   SELECT @ParentID = ParentRepoID FROM REPOSITORY WHERE RepositoryID = @RepoID

   WHILE (@ParentID > 0)
      BEGIN
         SELECT @ParentRepo = RepositoryName From BRANCHREPOSITORY br, REPOSITORY r WHERE br.BranchRepoID = @ParentID

         SET @RepoPath =  @ParentRepo + '/' + @RepoPath

         SET @RepoID = @ParentID

         SELECT @ParentID = ParentRepoID FROM REPOSITORY WHERE RepositoryID = @RepoID

      END

   SET @FullRepoPath = @RepoPath+@CurRepo
   RETURN @FullRepoPath
END

CTE Version

Props to Jeff for sharing with me the concept of using CTE with the function. He took the function above and came up with this one, which is more efficient.
CREATE FUNCTION GetRepoPath(@BranchRepoID INT) RETURNS VARCHAR(2000) AS BEGIN
    DECLARE @RepoPath varchar(2000);

        WITH RepoPathCTE(BranchRepoID, ParentBranchRepoID, RepositoryName,LevelNumber)AS
        (
                SELECT r.BranchRepoID, r.ParentBranchRepoID, r.RepositoryName, 0
                        FROM BRANCHREPOSITORY r
                        where BranchRepoID=@BranchRepoID
                UNION ALL
                         SELECT r.BranchRepoID, r.ParentBranchRepoID, r.RepositoryName, rp.LevelNumber + 1
                         FROM BRANCHREPOSITORY r
                        INNER JOIN RepoPathCTE rp on rp.ParentBranchRepoID = r.BranchRepoID
        )

        SELECT @RepoPath =
        (
                SELECT RepositoryName + '/'  FROM RepoPathCTE rp ORDER BY LevelNumber DESC FOR XML PATH('')
        )

        RETURN(@RepoPath)
END

Example of using function

This is basically the same history by date report, but it also displays the full repository path as a separate column (calls the 'raw sql' function):
SELECT convert(varchar(20),evt.EventDate,100)as Date, bf.[FileName] [File], b.BranchName Branch, dbo.GetRepoPath(evt.BranchRepoID) Repository, u.UserName [User],
CASE evt.EventType
   WHEN '1' THEN 'Add'
   WHEN '2' THEN 'Add to Branch'
   WHEN '3' THEN 'Add From Branch'
   WHEN '4' THEN 'Check In'
END AS [User Event]
FROM BRANCHFILE bf, [EVENT] evt, BRANCH b, SCMUSERS u, BRANCHREPOSITORY r
WHERE evt.BranchFileID = bf.BranchFileID
and b.BranchID = evt.BranchID
and u.idUser = evt.UserID
and r.BranchRepoID = evt.BranchRepoID
ORDER BY EventDate DESC, [FILE]

Report Output

Below is a screenshot of a drilldown report created using MS Studio with the above query:

Figure 2 - Sample History Report

History Report by date with restrictions

The following reports gives check ins that occurred between Sep 3rd and Sep 5th on branch WysiChart 1.x:
SELECT convert(varchar(20),evt.EventDate,100)as Date, bf.[FileName] [File], b.BranchName Branch, dbo.GetRepoPath(evt.BranchRepoID) Repository, u.UserName [User],
-- Case statement to have name for events
CASE evt.EventType
   WHEN '1' THEN 'Add'
   WHEN '2' THEN 'Add to Branch'
   WHEN '3' THEN 'Add From Branch'
   WHEN '4' THEN 'Check In'
END AS [User Event]
--Tables where we get data from
FROM BRANCHFILE bf, [EVENT] evt, BRANCH b, SCMUSERS u, BRANCHREPOSITORY r
WHERE
--Restrictions for output data
evt.EventDate BETWEEN '09/03/2008 12:00:00 AM' and '09/05/2008 23:59:59 PM'
and evt.EventType = '4'
and b.BranchName = 'WysiChart 1.x'
--Relationships between the tables
and evt.BranchFileID = bf.BranchFileID
and b.BranchID = evt.BranchID
and u.idUser = evt.UserID
and r.BranchRepoID = evt.BranchRepoID
ORDER BY EventDate DESC, [FILE]

Function to get Defect Number

The following function parses the defect number out of the attach to defect event comments and converts it to an integer. Note: The function makes sure that it is only looking at comments that have 'Defect Number:' in the beginning.
CREATE FUNCTION GetDefectNum(@COMMENTS VARCHAR(1000))
RETURNS INT

AS

BEGIN
   DECLARE @EXPRESSION VARCHAR(2000)
   DECLARE @CMPCOMMENTS VARCHAR(2000)
   DECLARE @TmpString varchar(2000)

   DECLARE @STRLEN INT
   DECLARE @Index INT
   DECLARE @DNUM INT
   DECLARE @DIFF INT

   SET @EXPRESSION = 'Defect Number:'

   SET @STRLEN = LEN(@COMMENTS)
   SET @DIFF = 0
   IF @STRLEN > 12
   BEGIN
      SET @CMPCOMMENTS = SUBSTRING(@COMMENTS,0,14)
      SET @DIFF = DIFFERENCE(@CMPCOMMENTS, @EXPRESSION)
   END

   IF @DIFF > 3
   BEGIN
      SET @Index = CHARINDEX(';',@COMMENTS,0)
      SET @Index = @Index - 16
      SET @TmpString = SUBSTRING(@COMMENTS,16,@Index)
      SET @DNUM = CAST(@TmpString AS INT)
   END

   RETURN @DNUM

END

Example of function

The following statement returns a list of Defects that files where attached to on November 25th, 2008. This query could be handy when you do a build and want to know which defects where addressed in the latest build. In this query example, we do a daily build and want to know which defects we attached check ins for the build on the 25th of Novemeber.
SELECT DISTINCT dbo.GetDefectNum(evt.COMMENTS) DefectNumber
FROM BRANCHFILE bf, [EVENT] evt, BRANCH b, SCMUSERS u, BRANCHREPOSITORY r
WHERE evt.BranchFileID = bf.BranchFileID
and b.BranchID = evt.BranchID
and u.idUser = evt.UserID
and r.BranchRepoID = evt.BranchRepoID
and evt.EventType = 9
AND evt.EventDate BETWEEN '11/25/2008 12:00:00 AM' and '11/25/2008 23:59:59 PM'
ORDER BY DefectNumber

Sample output

The following is the output from a sample Surround SCM database:

Figure 3 - Sample output

PostgreSQL syntax queries

Get list of historical events that contain a string in the comments (case sensitive)

SELECT  COMMENTS, bf.FileName, evt.EventDate, RepositoryName, BranchName,
CASE evt.EventType
    WHEN '1' THEN 'Add'
    WHEN '2' THEN 'Add to Branch'
    WHEN '3' THEN 'Add From Branch'
    WHEN '4' THEN 'Check In'
    WHEN '20' THEN 'Rename' END AS Event, u.UserName

FROM COMMENTS c, EVENT evt, BRANCHFILE bf, BRANCH b, BRANCHREPOSITORY br, SCMUSERS u 

WHERE COMMENTS LIKE '%"+ Comment +"%' and
evt.CommentID = c.CommentID and evt.branchfileid = bf.branchfileid and
 evt.BranchID = b.BranchID and evt.BranchRepoID = br.BranchRepoID and evt.userid = u.iduser

Get history for file in specific branch and specific repository

PostgreSQL syntax
SELECT evt.EventDate, u.UserName,

CASE evt.EventType
     WHEN '1' THEN 'Add'
     WHEN '2' THEN 'Add to Branch'
     WHEN '3' THEN 'Add From Branch'
     WHEN '4' THEN 'Check In'
     WHEN '20' THEN 'Rename'
END AS Event

FROM BRANCHFILE bf, EVENT evt, SCMUSERS u
WHERE evt.BranchFileID = bf.BranchFileID and u.idUser = evt.UserID and
 evt.BranchFileID = (SELECT branchfileid FROM branchfile where filename = '<filename>' and branchrepoid = (SELECT branchrepoid from branchrepository
 where branchid = (SELECT branchid from branch where branchname = '<branchname>') and repositoryname = '<repositoryname>'))
 and evt.BranchRepoID = (SELECT branchrepoid from branchrepository where branchid = (SELECT branchid from branch where branchname = '<branchname>') and
 repositoryname = '<repositoryname>') and evt.BranchID = (SELECT branchid from branch where branchname = '<branchname>') ORDER BY evt.EventDate

Get history for a specified file in a specified branch and specified repository

PostgreSQL syntax
SELECT evt.EventDate, u.UserName,

CASE evt.EventType
  WHEN '1' THEN 'Add'
  WHEN '2' THEN 'Add to Branch'
  WHEN '3' THEN 'Add From Branch'
  WHEN '4' THEN 'Check In'
  WHEN '20' THEN 'Rename'
END AS Event 

FROM BRANCHFILE bf, EVENT evt, SCMUSERS u  

WHERE 

evt.BranchFileID = bf.BranchFileID and u.idUser = evt.UserID and
evt.BranchFileID = (SELECT branchfileid from branchfile where filename = 'filename'
                    and branchrepoid = (SELECT branchrepoid from branchrepository where branchid = (SELECT branchid from branch
                    where branchname = 'branchname') and repositoryname = 'reponame'))
and evt.BranchRepoID = (SELECT branchrepoid from branchrepository where branchid =
                         (SELECT branchid from branch where branchname = 'branchname') and repositoryname = 'reponame')
 and evt.BranchID = (SELECT branchid from branch where branchname = '<branchname>') ORDER BY evt.EventDate

Get List of Labels and file versions for a specific file

The following returns a list of labels and file versions for a given file. This will search all branches in the database. Note: This query will only return labels that are scoped to a single branch. This is because all global labels have a NULL value for the branch ID.
SELECT

 LabelName, FileVersion,BranchName, dbo.GetRepoPath(BRANCHFILE.BranchRepoID) Repository 

FROM

 LABEL, LABELFILE, BRANCHFILE, BRANCH

WHERE

 LABEL.LabelID = LABELFILE.LabelID AND
 LABELFILE.BranchFileID = BRANCHFILE.BranchFileID AND
 LABEL.BranchID = BRANCH.BranchID AND
 [FileName] = '<name of file>'