Discussion Forum for StarTeam Users


Re: StarTeam Data Dictionary


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

Posted by mjuras on May 08, 2001 at 07:26:40:

In Reply to: Re: StarTeam Data Dictionary posted by Spoon on April 13, 2001 at 12:09:23:

IMHO, it is easier to report the contents of the reporistory using the SDK than SQL.

What are you trying to report?


: 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]