Discussion Forum for StarTeam Users


Re: StarTeam Data Dictionary


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

Posted by Spoon on May 10, 2001 at 15:14:03:

In Reply to: Re: StarTeam Data Dictionary posted by mjuras on May 08, 2001 at 07:26:40:

Below is exactly what I am trying to find. I've modified since my last post.
I do have the SDK but have not had time to deal with it. Unfortunatly/fortuanatley my SQL skills are much better than my programming skills. So I went the SQL route first. I do want to do more with the sdk but have not had time to mess with it yet. The idea of the below is to pull all files that were checked in and their comments between 2 labels and or times.

If anyone has a good starter program for me that would make life easier.

Thanks


-- Optional Parameters
DECLARE @ProjectName Varchar(254) -- Base Project Name
DECLARE @ViewName VarChar(254) -- View name within Project (Optional)
DECLARE @LabelName1 VarChar(254) -- Start Label
DECLARE @Time1 datetime -- Start Time
DECLARE @LabelName2 VarChar(254) -- End Label (Optional)
Declare @Time2 datetime -- End Time (Optional)

-- Enter values for Parameters
SELECT @ProjectName = 'Infox-Policy'
SELECT @ViewName = 'Infox-Policy'
SELECT @LabelName1 = '1.6.35'
SELECT @LabelName2 = Null
SELECT @Time1 = Null
SELECT @Time2 = Null

IF @ProjectName Is Null -- Require a project name
RAISERROR ('You must enter a project name!', 16, -1)
ELSE IF (@LabelName1 Is Null AND @Time1 Is Null) -- Require a start label or datetime
RAISERROR ('You must enter a start label or time!', 16, -1)
ELSE -- Main Else Loop
BEGIN
-- Working Paremeters
DECLARE @TimeOffset Decimal(16,10) -- StarTeam Offset
SELECT @TimeOffset = 2.2083333333
DECLARE @ProjectID int -- Holds project name id
DECLARE @ViewID int -- Holds view name id
DECLARE @LabelID1 int -- Holds start label id
DECLARE @LabelID2 int -- Holds end label id
DECLARE @LabelT1 Decimal(16,10) -- Holds start label datetime converted to a decimal
DECLARE @LabelT2 Decimal(16,10) -- Holds end label datetime converted to a decimal

-- Find the ProjectID
SELECT @ProjectID = ID FROM Project WHERE name = @ProjectName

-- If the view name is null assume base view (view name same as project name)
IF @ViewName Is Null
BEGIN
SELECT @ViewName = @ProjectName
END
-- Find the ViewID
SELECT @ViewID = ID FROM StView WHERE ProjectID = @ProjectID AND name = @ViewName

-- Convert any dates entered to Decimal's for comparison
SELECT @LabelT1 = Convert(Decimal(16,10), @Time1)
SELECT @LabelT2 = Convert(Decimal(16,10), @Time2)


-- If the first label is null use the DateTime entered
IF Not @LabelName1 Is Null
BEGIN
SELECT @LabelID1 = ID,
@LabelT1 = LabelTime FROM Config_Label WHERE ProjectID = @ProjectID AND ViewID = @ViewID AND name = @LabelName1 AND DeletedTime = 0
END
ELSE -- If the label was null and no time entered raise an error
BEGIN
IF @LabelT1 Is Null
RAISERROR ('You must specify a Label or DateTime to search from', 16, -1)
ELSE
SELECT @LabelT1 = @LabelT1 + @TimeOffset --apply StarTeam Offset
END

-- If the Label is not null Find the time of the label
IF Not @LabelName2 Is Null
BEGIN
SELECT @LabelID2 = ID,
@LabelT2 = LabelTime FROM Config_Label WHERE ProjectID = @ProjectID AND ViewID = @ViewID AND name = @LabelName2 AND DeletedTime = 0
END
ELSE -- If no end label entered use the entered time
BEGIN
IF @LabelT2 Is Null -- If no end time entered use the current time
SELECT @LabelT2 = Convert(Decimal(16,10), GetDate()) + @TimeOffset
ELSE
SELECT @LabelT2 = @LabelT2 + @TimeOffset
END

-- Do the actual select of comments from StarTeam
SELECT DISTINCT
F.Name, F.ContentVersion, CC.PartText
FROM Catalog_Comments CC RIGHT JOIN
(FilesHst_Info FI WITH(NOLOCK) INNER JOIN
Files F WITH(NOLOCK)
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 WITH(NOLOCK) RIGHT JOIN
Files F WITH(NOLOCK)
ON F.CommentID = CC.CommentID
WHERE F.ModifiedTime <= @LabelT2
AND F.ModifiedTime !< @LabelT1
AND F.ViewID = @ViewID
AND F.ProjectID = @ProjectID

END -- End of Main Else Loop



Follow Ups:



Post a Followup

Name:
E-Mail:

Subject:

Comments:


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