Where to find change-sets information?
TFS source control maintains change-sets for projects in the corresponding collection database. Each change-set is of course composed of the modified project files at the time of the Check-in.
Change-set information is stored in the tbl_ChangeSet table of the project collection database. That is: if you have a project collection 'Default Collection' stored in a database named tfs_default_collection, you will find change-sets information in the table tfs_default_collection.dbo.tbl_ChangeSet.
Files information is stored in tbl_files table, and file-versions information in tbl_Version table.
So, to obtain the list of files of a change-set number 861, for instance, you can execute a query similar to the following:
|
SELECT TOP (100) percent
chg_set.CreationDate,
chg_set.ChangeSetId,
v.FullPath
FROM dbo.tbl_ChangeSet (nolock)AS chg_set INNER JOIN
dbo.tbl_Version (nolock)AS v ON chg_set.ChangeSetId = v.VersionFrom LEFT OUTER JOIN
dbo.tbl_File (nolock) AS f ON v.FileId = f.FileId
WHERE (chg_set.ChangeSetId = 861)
ORDER BY chg_set.CreationDate, v.FullPath
|
Which may produce results like the following:
|
CreationDate
|
ChangeSetId
|
FullPath
|
|
2012-05-05 20:22:31.800
|
861
|
$\Research education "agile\SimpleUniConverter\commands\UniConvertCommandModel.cs\
|
|
2012-05-05 20:22:31.800
|
861
|
$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\
|
|
2012-05-05 20:22:31.800
|
861
|
$\Research education "agile\SimpleUniConverter\Convert.xaml\
|
|
2012-05-05 20:22:31.800
|
861
|
$\Research education "agile\SimpleUniConverter\MainWindow.xaml.cs\
|
|
2012-05-05 20:22:31.800
|
861
|
$\Research education "agile\SimpleUniConverter\MainWindow.xaml\
|
How many bytes changed?
As you may have noticed from the figure above, change-set file information contains a FileLength column. This suggests that we can obtain the number of changed bytes of a given file between two change-sets… through the following query results we can know that the file grew 696-551 = 145 bytes:
|
CreationDate
|
ChangeSetId
|
FullPath
|
FileLength
|
|
2012-04-30 22:33:10.040
|
843
|
$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\
|
551
|
|
2012-05-05 20:22:31.800
|
861
|
$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\
|
696
|
Who changed what?
The change-set table provides an CommitterId column through which we can know who made a given check-in. As this column contains an ID (integer refering tbl_identity.IdentityId), this is not straight forward information. We have to join other tables to obtain something useful.
In fact, the committer user name is stored in the Constants table. So, we have to join the Change-set's CommitterId column to tbl_identity.IdentityId, and then join tbl_identity.TeamFoundationId to Constants.TeamFoundationId… we can then get the user name in the Constants.DisplayPart column (or Constants.String column)
This can be presented by the following change in our initial view diagram:
|
CreationDate
|
ChangeSetId
|
FullPath
|
FileLength
|
DisplayPart
|
|
2012-04-30 22:33:10.040
|
843
|
$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\
|
551
|
Taoffi
|
|
2012-05-05 20:22:31.800
|
861
|
$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\
|
696
|
Taoffi
|
Changed bytes tracking generalization
It would, ideally, be beneficial to integrate such information into the TFS analytical database.
We still can do some turnaround by directly querying the database tables.
The idea is to list all change-set files in the order of their respective check-in date. To handle the probability of identical dates / files, we can use a row number to distinguish changes. This may be done like in the following view:
|
CREATE VIEW [dbo].[xtfs_changeset_files_qry]
AS
SELECT TOP (2147483647)
ROW_NUMBER() over( ORDER BY chg_set.CreationDate) as row_number,
chg_set.CreationDate,
const.DisplayPart as user_name,
chg_set.ChangeSetId,
v.FullPath,
f.FileLength
FROM dbo.tbl_ChangeSet (nolock) AS chg_set INNER JOIN
dbo.tbl_Identity (nolock) AS login ON chg_set.CommitterId = login.IdentityId INNER JOIN
dbo.tbl_Version (nolock) AS v ON chg_set.ChangeSetId = v.VersionFrom LEFT OUTER JOIN
dbo.tbl_File (nolock) AS f ON v.FileId = f.FileId inner join
Constants (nolock) AS const ON const.TeamFoundationId=login.TeamFoundationId
ORDER BY chg_set.CreationDate, v.FullPath
GO
|
We can then refer the view to itself (current version / previous version) obtain row-relative quantitative changes:
|
SELECT v1.row_number,
v1.CreationDate,
v1.user_name,
v1.FullPath AS v1FilePath,
v1.FileLength AS v1_length,
v_next.FileLength AS v2_length,
v_next.FullPath AS v2FilePath,
ISNULL(v_next.FileLength, 0)
- ISNULL(v1.FileLength, 0) AS changed_bytes
FROM dbo.xtfs_changeset_files_qry AS v_next RIGHT OUTER JOIN
dbo.xtfs_changeset_files_qry AS v1 ON v_next.FullPath = v1.FullPath
AND v_next.row_number = v1.row_number + 1
OR v_next.FullPath IS NULL
AND v_next.row_number IS NULL
|