Thursday, January 5, 2012

Detecting if a file is a merge in TFS VersionControl database

I was trying to run some metric calculations on files within a changeset, but I only wanted new files – i.e. I wanted to filter out merged, branched, or renamed files. For example, if someone created a branch, that shouldn’t count as adding 1000 new files.
One solution I found was to check the Command column of the TfsVersionControl.dbo.tbl_Version table. I realize the TfsVersionControl is a transactional database, and reports are encouraged to go off of TfsWareHouse, but that didn’t seem to contain this field.
Here’s the relevant SQL (NOTE: this is for VS2008, I haven’t tested it on VS2010).
select 
      CS.ChangeSetId, FullPath, Command, CreationDate,
      case
            when Command in (2,5,6,7,10,34) then cast(1 as bit)
        else cast(0 as bit)
      end as IsNew
from TfsVersionControl..tbl_Version V with (nolock)
      inner join TfsVersionControl..tbl_ChangeSet CS with (nolock)
      on V.VersionFrom = CS.ChangeSetId
where CS.ChangeSetId = 20123

The question becomes, what does the “tbl_Version .Command” column mean, and why those specific values? I couldn’t find official documentation (probably because it’s discouraged to run queries on it), so I did a distinct search on 50,000 different changesets to find all values being used, and I worked backwards comparing it against the Team Explorer UI to conclude it appears to be a bitflag for actions:

Command
Bit value
add
1
edit
2
branch
4
rename
8
delete
16
undelete
32
branch
64
merge
128


Recall there can be multiple actions (hence the bit field), such as merge and edit. So, if you want to find new code – i.e. adds or edits, then we’d take the following bit flags: 2, 5, 6, 7, 10, and 34.

Is New?
Bit value
Actions
Yes
2
edit
Yes
5
add (folder)
Yes
6
type/edit
Yes
7
add (add file)
No
8
rename
Yes
10
rename/edit
No
16
delete
No
24
delete,rename
No
32
undelete
Yes
34
undelete, edit
No
68
branch
No
70
branch, edit
No
84
branch,delete
No
128
merge
No
130
merge, edit
No
136
merge,rename
No
138
merge,rename,edit
No
144
merge,delete
No
152
merge, delete, rename
No
160
merge, undelete
No
162
merge, undelete, edit
No
196
merge, branch
No
198
merge, branch, edit
No
212
merge, branch, delete


Of course, this is induction, and it’s possible I may have missed something, but given a large sampling and lots of spot-checking, it appears to be reliable.

6 comments:

  1. Hi there,
    First thanks for your blog. Helped me a lot!
    Now i'am using the tbl_version for some reports as well and try to figure out the command column as i like to filter out all new files or files without real content modification.

    Question: In my TFS2010 i have following distinct values in the command column: 2,5,6,7,16,32,34,64,68,70,84,128,130,134,144,192,196,198,212,261,1024,1029,1031,1040,1168,2112,2114,2128,2240,2242,3136,3138,3152

    Many more value combinations compared to your table. Is there an easy / logical way to map these values to the corresponding TFS action or actions? Is there some conversion formula?

    Thanks in advance!

    ReplyDelete
  2. 2010 may have different mappings than 2008, so I'm not sure the exact 2010 mapping. But you could reverse engineer - i.e. find a file with 3152 and see what mapping shows up in the IDE?

    ReplyDelete
  3. Hey thanks for the feedback. I hoped someone would have done the research for TFS2010 before and i'm not sure if i have captured all possible actions already + hoping for some formula to find out.

    Now i did the reverse engineer and these are the results if someone would need it. For some reason there are some redundant actions in the higher ranges.

    Thanks & Cheers

    Bit
    Value Action
    2 Edit
    5 Add (folder)
    6 type/edit
    7 add (add file)
    16 delete
    32 undelete
    34 undelete,edit
    64 Branch
    68 Branch
    70 Branch, Edit
    84 Branch, delete
    128 Merge
    130 Merge, Edit
    134 Merge, Type, Edit
    144 Merge, Delete
    192 Merge,Branch,Edit
    196 Merge, Branch
    198 Merge,Branch,Edit
    212 Merge,Branch,Delete
    261 Add, Lock
    1024 Source Rename
    1029 Add (Folder), Source Rename
    1031 Add File, Source Rename
    1040 Delete, Source Rename
    1168 Merge, Delete, Source Rename
    2112 Rename
    2114 Rename, Edit
    2128 Delete, Rename
    2240 Merge, Rename
    2242 Merge, Rename, Edit
    3136 Rename, Source Rename
    3138 Rename, Edit, Source Rename
    3152 Delete, Rename, Source Rename

    ReplyDelete
    Replies
    1. in addition to the commands and actions you listed above, our TFS2010 tbl_Version has other command values:
      160
      162
      2144
      2146
      3264
      3266
      3280

      I have yet to figure out how to "reverse engineer" the commands. I'd really like to find out what action(s) these commands perform. How did you find this information?

      Delete
    2. 160 merge, undelete
      162 merge, undelete, edit

      listed in original post. I can't speak for the others since I don't know how they were defined. I don't have authorization to run the Team Explorer UI. Can someone define these?

      Delete
  4. The Command column in the tbl_Version table is a bit mask. You might try something like the following to help you out (sorry about the formatting):


    SELECT TOP 100 V.Command, STUFF(CASE WHEN (V.Command & 1) = 1 THEN ', add' ELSE '' END +
    CASE WHEN (V.Command & 2) = 2 AND (V.Command & 1) <> 1 THEN ', edit' ELSE '' END +
    CASE WHEN (V.Command & 4) = 4 AND (V.Command & 1) <> 1 THEN ', type'
    WHEN (V.Command & 4) = 4 AND (V.Command & 1) = 1 AND (V.Command & 2) = 2 THEN ' file'
    WHEN (V.Command & 4) = 4 AND (V.Command & 1) = 1 AND (V.Command & 2)<> 2 THEN ' folder'
    ELSE '' END +
    CASE WHEN (V.Command & 8) = 8 THEN ', rename' ELSE '' END +
    CASE WHEN (V.Command & 16) = 16 THEN ', delete' ELSE '' END +
    CASE WHEN (V.Command & 32) = 32 THEN ', undelete' ELSE '' END +
    CASE WHEN (V.Command & 64) = 64 THEN ', branch' ELSE '' END +
    CASE WHEN (V.Command & 128) = 128 THEN ', merge' ELSE '' END +
    CASE WHEN (V.Command & 256) = 256 THEN ', lock' ELSE '' END +
    CASE WHEN (V.Command & 512) = 512 THEN ', rollback' ELSE '' END +
    CASE WHEN (V.Command & 1024) = 1024 THEN ', source rename' ELSE '' END +
    CASE WHEN (V.Command & 2048) = 2048 THEN ', rename' ELSE '' END
    , 1, 2, '') AS CommandDescription
    FROM tbl_Version V

    ReplyDelete