Discussion Forum for StarTeam Users


Re: StarTeam Data Dictionary


[ Follow Ups ] [ Post Followup ] [ Discussion Forum for StarTeam Users]

Posted by Spoon on April 13, 2001 at 12:09:23:

In Reply to: StarTeam Data Dictionary posted by Spoon on April 13, 2001 at 12:06:19:

Ok I've been messing with this for a while and have part of what I needed. One thing we've wanted is to be able to pull all comments between 2 revisions. This was possible in VSS with the recursive history then show the report but there is no easy way in StarTeam (at their own admission) to do this. So I've been deciphering the underlying database. This is what I've come up with so far. If anyone else has come up with anything please add. Also I figured out the look up tables. That portion was actually rather easy though. Also I should say that I did these on MS SQL.
/* Pull all comments between 2 labels */
DECLARE @ProjectName Varchar(254)
DECLARE @ViewName VarChar(254)
DECLARE @LabelName1 VarChar(254)
DECLARE @LabelName2 VarChar(254)

SELECT @ProjectName = 'Project name here'
SELECT @ViewName = 'View name here (same as project is base view)'
SELECT @LabelName1 = 'label1'
SELECT @LabelName2 = 'label2'

DECLARE @ProjectID int
DECLARE @ViewID int
DECLARE @LabelID1 int
DECLARE @LabelT1 Decimal(16,10)
DECLARE @LabelID2 int
DECLARE @LabelT2 Decimal(16,10)

SELECT @ProjectID = ID FROM Project WHERE name = @ProjectName
SELECT @ViewID = ID FROM StView WHERE ProjectID = @ProjectID AND name = @ViewName
SELECT @LabelID1 = ID, @LabelT1 = LabelTime FROM Config_Label WHERE ProjectID = @ProjectID AND ViewID = @ViewID AND name = @LabelName1 AND DeletedTime = 0
SELECT @LabelID2 = ID, @LabelT2 = LabelTime FROM Config_Label WHERE ProjectID = @ProjectID AND ViewID = @ViewID AND name = @LabelName2 AND DeletedTime = 0
SELECT DISTINCT F.Name, F.ContentVersion, CC.PartText
FROM Catalog_Comments CC RIGHT JOIN (FilesHst_Info FI INNER JOIN Files F ON FI.ParentID = F.ID) ON F.CommentID = CC.CommentID
WHERE FI.ModifiedTime <= @LabelT2 AND FI.ModifiedTime !< @LabelT1 AND F.ViewID = @ViewID AND F.ProjectID = @ProjectID
UNION
SELECT DISTINCT F.Name, F.ContentVersion, CC.PartText
FROM Catalog_Comments CC RIGHT JOIN Files F ON F.CommentID = CC.CommentID
WHERE F.ModifiedTime <= @LabelT2 AND F.ModifiedTime !< @LabelT1 AND F.ViewID = @ViewID AND F.ProjectID = @ProjectID


/* look up fields */
SELECT CT.SQLName + '.' + CF.SQLName + ' = ' + CS.StringText, CE.Code, CE.Text_ID
FROM Catalog_Strings CS RIGHT JOIN
(Catalog_Enums CE RIGHT JOIN
(Catalog_Tables CT RIGHT JOIN
Catalog_Fields CF
ON CT.ID = CF.Table_ID)
ON CE.Field_ID = CF.Field_ID AND
CE.Table_ID = CT.ID)
ON CS.StringID = CE.Text_ID
WHERE Not CE.Code Is NULL




Follow Ups:



Post a Followup

Name:
E-Mail:

Subject:

Comments:


[ Follow Ups ] [ Post Followup ] [ Discussion Forum for StarTeam Users]