Inspections/Evaluations

TFS 2015 Database Structure Investigation

A TFS 2015 build agent maintains a list of directories where builds occur. Each of these directories is assigned a monotonically-incrementing number and has three children: “s” (“source” directory where your files are, usually, automatically checked-out for you), “b” (“binaries” directory where you can choose to stash files that aren’t automatically cleaned-up), and “a” (“artifact-staging” directory where you push files/directories that will be published). Each agent assigns its own IDs for each build definition.

I sought to figure out 1) how to lookup the mapping between the build-definitions and these agent-ID pairs, and 2) how to lookup the paths when you publish a build to be stored by the TFS server (rather than a fileshare). Every so often I wander into the TFS database to chip away at its elusive topology. I’ll briefly document my observations here for the benefit of myself and others. I’ll add on new information as I might encounter it in the future. I’m only currently concerned with build-definitions but any information that can be provided for release-definitions would also be welcomes.

There is a main database (e.g. “Tfs_Configuration”) and subordinate collection-specific database (e.e. “Tfs_DefaultCollection”).

[Build].[tbl_Build]

Information on individual builds including build numbers (e.g. 20160512.1) and “definition ID”.

SELECT looks like:

SELECT TOP 1000 [PartitionId]
,[DataspaceId]
,[BuildId]
,[DefinitionId]
,[DefinitionVersion]
,[BuildNumber]
,[BuildNumberRevision]
,[RepositoryId]
,[BranchId]
,[SourceVersion]
,[Parameters]
,[Status]
,[QueueId]
,[QueueTime]
,[Priority]
,[StartTime]
,[FinishTime]
,[Reason]
,[Result]
,[RequestedFor]
,[RequestedBy]
,[ChangedOn]
,[ChangedBy]
,[OrchestrationId]
,[Deleted]
,[ValidationIssues]
,[QueueOptions]
,[KeepForever]
,[ChangesCalculated]
,[DeletedOn]
FROM [Tfs_DefaultCollection].[Build].[tbl_Build]

Record looks like:

PartitionId DataspaceId BuildId DefinitionId    DefinitionVersion   BuildNumber BuildNumberRevision RepositoryId    BranchId    SourceVersion   Parameters  Status  QueueId QueueTime   Priority    StartTime   FinishTime  Reason  Result  RequestedFor    RequestedBy ChangedOn   ChangedBy   OrchestrationId Deleted ValidationIssues    QueueOptions    KeepForever ChangesCalculated   DeletedOn

1   22  168 1   58  20160701.1  1   1   2   C31 {"system.debug":"false","BuildConfiguration":"release","BuildPlatform":"any cpu"}   2   1   2016-07-01 16:11:01.2247845 3   2016-07-01 16:11:03.5025120 2016-07-01 16:11:23.2632483 1   2   FD456772-708D-496C-9259-32596770CD73    FD456772-708D-496C-9259-32596770CD73    2016-07-01 16:11:23.450 02442124-655A-49B2-A6FB-20269EDEBEF6    E6ABEC05-179C-4406-91CB-E96B9B6CFD7C    0   NULL    NULL    0   1   NULL

[Build].[tbl_Definition]

Describes individual build-definitions, presumably. Includes “definition ID”, “definition version” (probably a pointer to the head version), “definition name”.

SELECT looks like:

SELECT TOP 1000 [PartitionId]
      ,[DataspaceId]
      ,[DefinitionId]
      ,[DefinitionVersion]
      ,[DefinitionName]
      ,[Quality]
      ,[QueueId]
      ,[QueueStatus]
      ,[RepositoryId]
      ,[DefaultBranchId]
      ,[TriggerTypes]
      ,[Description]
      ,[BuildNumberFormat]
      ,[JobAuthorizationScope]
      ,[JobTimeout]
      ,[Comment]
      ,[Author]
      ,[CreatedOn]
      ,[ParentDefinitionId]
      ,[Options]
      ,[Repository]
      ,[Triggers]
      ,[Steps]
      ,[Variables]
      ,[Demands]
      ,[RetentionPolicy]
      ,[BadgeEnabled]
      ,[Deleted]
  FROM [Tfs_DefaultCollection].[Build].[tbl_Definition]

Record looks like:

PartitionId DataspaceId DefinitionId    DefinitionVersion   DefinitionName  Quality QueueId QueueStatus RepositoryId    DefaultBranchId TriggerTypes    Description BuildNumberFormat   JobAuthorizationScope   JobTimeout  Comment Author  CreatedOn   ParentDefinitionId  Options Repository  Triggers    Steps   Variables   Demands RetentionPolicy BadgeEnabled    Deleted

1   22  1   58  ConsoleProject.Dev  1   1   0   1   1   1   NULL    $(date:yyyyMMdd)$(rev:.r)   1   60  Added publish-to-server step.   FD456772-708D-496C-9259-32596770CD73    2016-07-01 16:10:57.070 NULL    [{"enabled":false,"definition":{"id":"7c555368-ca64-4199-add6-9ebaf0b0137d"},"inputs":{"multipliers":"[]","parallel":"false","continueOnError":"true","additionalFields":"{}"}},{"enabled":false,"definition":{"id":"a9db38f9-9fdc-478c-b0f9-464221e58316"},"inputs":{"workItemType":"1","assignToRequestor":"true","additionalFields":"{}"}},{"enabled":false,"definition":{"id":"57578776-4c22-4526-aeb0-86b6da17ee9c"},"inputs":{"additionalFields":"{}"}}]    {"properties":{"labelSources":"0","tfvcMapping":"{\"mappings\":[{\"serverPath\":\"$/d67f9d95-2f6c-43f0-aa2f-6f7804fde7db\",\"mappingType\":\"map\",\"localPath\":\"\\\\\"},{\"serverPath\":\"$/d67f9d95-2f6c-43f0-aa2f-6f7804fde7db/Drops\",\"mappingType\":\"cloak\",\"localPath\":\"\\\\\"}]}"},"id":"$/","type":"TfsVersionControl","name":"d67f9d95-2f6c-43f0-aa2f-6f7804fde7db","url":"http://dustin-pc:8181/tfs/DefaultCollection/","defaultBranch":"$/d67f9d95-2f6c-43f0-aa2f-6f7804fde7db","rootFolder":"$/d67f9d95-2f6c-43f0-aa2f-6f7804fde7db","clean":"false","checkoutSubmodules":false}  NULL    [{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"Build solution $/TestProject/TestConsoleApplication.sln","task":{"id":"71a9a2d3-a98a-4caa-96ab-affca411ecda","versionSpec":"*"},"inputs":{"solution":"$/d67f9d95-2f6c-43f0-aa2f-6f7804fde7db/TestConsoleApplication.sln","msbuildArgs":"/target:publish /p:ApplicationVersion=1.0.2.2 /p:InstallUrl=\\\\localhost\\clickonce\\consoletestfrombuild\\ /p:UpdateUrl=\\\\localhost\\clickonce\\consoletestfrombuild\\ /p:PublishUrl=\\\\localhost\\clickonce\\consoletestfrombuild\\ /p:UpdateEnabled=true /p:UpdateMode=Foreground /p:ProductName=TestConsoleApplication /p:IsWebBootstrapper=false","platform":"$(BuildPlatform)","configuration":"$(BuildConfiguration)","clean":"true","restoreNugetPackages":"true","vsVersion":"14.0","msbuildArchitecture":"x86","logProjectEvents":"true"}},{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"Build solution $/TestProject/TestDatabase.sln","task":{"id":"71a9a2d3-a98a-4caa-96ab-affca411ecda","versionSpec":"*"},"inputs":{"solution":"$/d67f9d95-2f6c-43f0-aa2f-6f7804fde7db/TestDatabase.sln","msbuildArgs":"","platform":"$(BuildPlatform)","configuration":"$(BuildConfiguration)","clean":"true","restoreNugetPackages":"true","vsVersion":"14.0","msbuildArchitecture":"x86","logProjectEvents":"true"}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"Run script $/TestProject/TestScripts/TestEcho.cmd","task":{"id":"bfc8bf76-e7ac-4a8c-9a55-a944a9f632fd","versionSpec":"*"},"inputs":{"filename":"$/d67f9d95-2f6c-43f0-aa2f-6f7804fde7db/TestScripts/TestEcho.cmd","arguments":"","modifyEnvironment":"false","workingFolder":"","failOnStandardError":"false"}},{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"Copy Files to: $(Build.ArtifactStagingDirectory)","task":{"id":"5bfb729a-a7c8-4a78-a7c3-8d717bb7c13c","versionSpec":"*"},"inputs":{"SourceFolder":"$(Build.SourcesDirectory)\\TestConsoleApplication\\bin\\Release","Contents":"**\\*","TargetFolder":"$(Build.ArtifactStagingDirectory)","CleanTargetFolder":"true","OverWrite":"false"}},{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"Copy Files to: $(Build.ArtifactStagingDirectory)\\deployment\\Release\\Utility","task":{"id":"5bfb729a-a7c8-4a78-a7c3-8d717bb7c13c","versionSpec":"*"},"inputs":{"SourceFolder":"$(Build.SourcesDirectory)\\Release\\Utility","Contents":"**\\*","TargetFolder":"$(Build.ArtifactStagingDirectory)\\deployment\\Release\\Utility","CleanTargetFolder":"false","OverWrite":"false"}},{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"Copy Files to: $(Build.ArtifactStagingDirectory)/TestDatabase1","task":{"id":"5bfb729a-a7c8-4a78-a7c3-8d717bb7c13c","versionSpec":"*"},"inputs":{"SourceFolder":"$(Build.SourcesDirectory)\\TestDatabase1\\bin\\Release","Contents":"TestDatabase1.dacpac","TargetFolder":"$(Build.ArtifactStagingDirectory)/TestDatabase1","CleanTargetFolder":"false","OverWrite":"false"}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"Publish Artifact: drop","task":{"id":"2ff763a7-ce83-4e1f-bc89-0ae63477cebe","versionSpec":"*"},"inputs":{"PathtoPublish":"$(build.artifactstagingdirectory)","ArtifactName":"drop","ArtifactType":"FilePath","TargetPath":"\\\\localhost\\build_publish\\$(Build.DefinitionName)\\$(Build.BuildNumber)"}},{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"Publish Artifact: Published_$(Build.BuildNumber)","task":{"id":"2ff763a7-ce83-4e1f-bc89-0ae63477cebe","versionSpec":"*"},"inputs":{"PathtoPublish":"$(build.artifactstagingdirectory)","ArtifactName":"Published_$(Build.BuildNumber)","ArtifactType":"Container","TargetPath":"\\\\my\\share\\$(Build.DefinitionName)\\$(Build.BuildNumber)"}}]  {"system.debug":{"value":"false","allowOverride":true},"BuildConfiguration":{"value":"release","allowOverride":true},"BuildPlatform":{"value":"any cpu","allowOverride":true},"DUSTINPASSWORD":{"value":null,"allowOverride":true,"isSecret":true}} NULL    [{"branches":["+refs/heads/*"],"artifacts":["build.SourceLabel"],"daysToKeep":10,"minimumToKeep":1,"deleteBuildRecord":true,"deleteTestResults":true}]  0   0

[Build].[tbl_DefinitionHistory]

Describes the history of changes to the definition. Includes “definition ID”, “definition version”, “definition name”, and several JSON blocks.

Note that the record I provided is the most recent available (in my local sandbox environment). Though I had just made a change to a definition, it was, curiously, not represented in this table (though many other, previous, changes ones were).

SELECT looks like:

SELECT TOP 1000 [PartitionId]
      ,[DataspaceId]
      ,[DefinitionId]
      ,[DefinitionVersion]
      ,[DefinitionName]
      ,[QueueId]
      ,[QueueStatus]
      ,[RepositoryId]
      ,[DefaultBranchId]
      ,[Description]
      ,[BuildNumberFormat]
      ,[JobAuthorizationScope]
      ,[JobTimeout]
      ,[Comment]
      ,[Author]
      ,[CreatedOn]
      ,[Options]
      ,[Repository]
      ,[Triggers]
      ,[Steps]
      ,[Variables]
      ,[Demands]
      ,[RetentionPolicy]
      ,[BadgeEnabled]
      ,[Deleted]
  FROM [Tfs_DefaultCollection].[Build].[tbl_DefinitionHistory]

Record looks like:

PartitionId DataspaceId DefinitionId    DefinitionVersion   DefinitionName  QueueId QueueStatus RepositoryId    DefaultBranchId Description BuildNumberFormat   JobAuthorizationScope   JobTimeout  Comment Author  CreatedOn   Options Repository  Triggers    Steps   Variables   Demands RetentionPolicy BadgeEnabled    Deleted

1   32  12  18  PWC Audit360 Simulation Build   1   0   2   9   NULL    $(date:yyyyMMdd)$(rev:.r)   1   60  NULL    FD456772-708D-496C-9259-32596770CD73    2016-05-27 18:45:03.117 [{"enabled":false,"definition":{"id":"7c555368-ca64-4199-add6-9ebaf0b0137d"},"inputs":{"multipliers":"[]","parallel":"false","continueOnError":"true","additionalFields":"{}"}},{"enabled":false,"definition":{"id":"a9db38f9-9fdc-478c-b0f9-464221e58316"},"inputs":{"workItemType":"16","assignToRequestor":"true","additionalFields":"{}"}},{"enabled":false,"definition":{"id":"57578776-4c22-4526-aeb0-86b6da17ee9c"},"inputs":{"additionalFields":"{}"}}]   {"properties":{"labelSources":"0","tfvcMapping":"{\"mappings\":[{\"serverPath\":\"$/d0942daa-ac39-462f-8115-fada54d8f780\",\"mappingType\":\"map\",\"localPath\":\"\\\\\"},{\"serverPath\":\"$/d0942daa-ac39-462f-8115-fada54d8f780/Drops\",\"mappingType\":\"cloak\",\"localPath\":\"\\\\\"}]}"},"id":"$/","type":"TfsVersionControl","name":"d0942daa-ac39-462f-8115-fada54d8f780","url":"http://dustin-pc:8181/tfs/DefaultCollection/","defaultBranch":"$/d0942daa-ac39-462f-8115-fada54d8f780","rootFolder":"$/d0942daa-ac39-462f-8115-fada54d8f780","clean":"false","checkoutSubmodules":false}  NULL    [{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"NuGet restore Audit360.Server.sln","task":{"id":"333b11bd-d341-40d9-afcf-b32d5ce6f23b","versionSpec":"*"},"inputs":{"solution":"Audit360.Server.sln","nugetConfigPath":"$/d0942daa-ac39-462f-8115-fada54d8f780/nuget.config","noCache":"false","nuGetRestoreArgs":"","nuGetPath":""}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"NuGet restore Audit360.SilverlightBuild.sln","task":{"id":"333b11bd-d341-40d9-afcf-b32d5ce6f23b","versionSpec":"*"},"inputs":{"solution":"Audit360.SilverlightBuild.sln","nugetConfigPath":"","noCache":"false","nuGetRestoreArgs":"","nuGetPath":""}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"NuGet restore Audit360.Database.sln","task":{"id":"333b11bd-d341-40d9-afcf-b32d5ce6f23b","versionSpec":"*"},"inputs":{"solution":"Audit360.Database.sln","nugetConfigPath":"","noCache":"false","nuGetRestoreArgs":"","nuGetPath":""}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"NuGet restore Audit360.Queue.sln","task":{"id":"333b11bd-d341-40d9-afcf-b32d5ce6f23b","versionSpec":"*"},"inputs":{"solution":"Audit360.Queue.sln","nugetConfigPath":"","noCache":"false","nuGetRestoreArgs":"","nuGetPath":""}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"NuGet restore MarketData.Database.sln","task":{"id":"333b11bd-d341-40d9-afcf-b32d5ce6f23b","versionSpec":"*"},"inputs":{"solution":"MarketData.Database.sln","nugetConfigPath":"","noCache":"false","nuGetRestoreArgs":"","nuGetPath":""}},{"enabled":true,"continueOnError":false,"alwaysRun":false,"displayName":"Build solution Audit360.Server.sln","task":{"id":"71a9a2d3-a98a-4caa-96ab-affca411ecda","versionSpec":"*"},"inputs":{"solution":"Audit360.Server.sln","msbuildArgs":"/t:build;publish /p:DeployOnBuild=True /m:1 /p:DeployPrefix=F1","platform":"$(BuildPlatform)","configuration":"$(BuildConfiguration)","clean":"true","restoreNugetPackages":"true","vsVersion":"14.0","msbuildArchitecture":"x86","logProjectEvents":"true"}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"Build solution Audit360.SilverlightBuild.sln","task":{"id":"71a9a2d3-a98a-4caa-96ab-affca411ecda","versionSpec":"*"},"inputs":{"solution":"Audit360.SilverlightBuild.sln","msbuildArgs":"","platform":"$(BuildPlatform)","configuration":"$(BuildConfiguration)","clean":"true","restoreNugetPackages":"true","vsVersion":"14.0","msbuildArchitecture":"x86","logProjectEvents":"true"}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"Build solution Audit360.Database.sln","task":{"id":"71a9a2d3-a98a-4caa-96ab-affca411ecda","versionSpec":"*"},"inputs":{"solution":"Audit360.Database.sln","msbuildArgs":"","platform":"$(BuildPlatform)","configuration":"$(BuildConfiguration)","clean":"true","restoreNugetPackages":"true","vsVersion":"14.0","msbuildArchitecture":"x86","logProjectEvents":"true"}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"Build solution Audit360.Queue.sln","task":{"id":"71a9a2d3-a98a-4caa-96ab-affca411ecda","versionSpec":"*"},"inputs":{"solution":"Audit360.Queue.sln","msbuildArgs":"","platform":"$(BuildPlatform)","configuration":"$(BuildConfiguration)","clean":"true","restoreNugetPackages":"true","vsVersion":"14.0","msbuildArchitecture":"x86","logProjectEvents":"true"}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"Build solution MarketData.Database.sln","task":{"id":"71a9a2d3-a98a-4caa-96ab-affca411ecda","versionSpec":"*"},"inputs":{"solution":"MarketData.Database.sln","msbuildArgs":"","platform":"$(BuildPlatform)","configuration":"$(BuildConfiguration)","clean":"true","restoreNugetPackages":"true","vsVersion":"14.0","msbuildArchitecture":"x86","logProjectEvents":"true"}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"Test Assemblies **\\$(BuildConfiguration)\\*test*.dll;-:**\\obj\\**","task":{"id":"ef087383-ee5e-42c7-9a53-ab56c98420f9","versionSpec":"*"},"inputs":{"testAssembly":"**\\$(BuildConfiguration)\\*test*.dll;-:**\\obj\\**","testFiltercriteria":"","runSettingsFile":"","overrideTestrunParameters":"","codeCoverageEnabled":"false","runInParallel":"false","vsTestVersion":"14.0","pathtoCustomTestAdapters":"","otherConsoleOptions":"","testRunTitle":"","platform":"$(BuildPlatform)","configuration":"$(BuildConfiguration)","publishRunAttachments":"true"}},{"enabled":false,"continueOnError":true,"alwaysRun":false,"displayName":"Publish symbols path: ","task":{"id":"0675668a-7bba-4ccb-901d-5ad6554ca653","versionSpec":"*"},"inputs":{"SymbolsPath":"","SearchPattern":"**\\bin\\**\\*.pdb","SymbolsFolder":"","SkipIndexing":"false","TreatNotIndexedAsWarning":"false","SymbolsMaximumWaitTime":"","SymbolsProduct":"","SymbolsVersion":"","SymbolsArtifactName":"Symbols_$(BuildConfiguration)"}},{"enabled":false,"continueOnError":false,"alwaysRun":true,"displayName":"Copy Files to: $(build.artifactstagingdirectory)","task":{"id":"5bfb729a-a7c8-4a78-a7c3-8d717bb7c13c","versionSpec":"*"},"inputs":{"SourceFolder":"$(build.sourcesdirectory)","Contents":"**\\bin\\$(BuildConfiguration)\\**","TargetFolder":"$(build.artifactstagingdirectory)","CleanTargetFolder":"false","OverWrite":"false"}},{"enabled":false,"continueOnError":false,"alwaysRun":true,"displayName":"Publish Artifact: drop","task":{"id":"2ff763a7-ce83-4e1f-bc89-0ae63477cebe","versionSpec":"*"},"inputs":{"PathtoPublish":"$(build.artifactstagingdirectory)","ArtifactName":"drop","ArtifactType":"Container","TargetPath":"\\\\my\\share\\$(Build.DefinitionName)\\$(Build.BuildNumber)"}},{"enabled":false,"continueOnError":false,"alwaysRun":false,"displayName":"Run script $/PwcSimulation/Build/RMPostBuild.cmd","task":{"id":"bfc8bf76-e7ac-4a8c-9a55-a944a9f632fd","versionSpec":"*"},"inputs":{"filename":"$/d0942daa-ac39-462f-8115-fada54d8f780/Build/RMPostBuild.cmd","arguments":"/C:FB /E:F1","modifyEnvironment":"false","workingFolder":"","failOnStandardError":"false"}}] {"system.debug":{"value":"false","allowOverride":true},"BuildConfiguration":{"value":"FB","allowOverride":true},"BuildPlatform":{"value":"any cpu","allowOverride":true}}   NULL    [{"branches":["+refs/heads/*"],"artifacts":["build.SourceLabel"],"daysToKeep":10,"minimumToKeep":1,"deleteBuildRecord":true,"deleteTestResults":true}]  0   0

[dbo].[tbl_Workspace]

Appears to describe the workspaces that are currently defined for users, builds, etc..

SELECT looks like:

SELECT TOP 1000 [PartitionId]
      ,[WorkspaceId]
      ,[OwnerId]
      ,[WorkspaceName]
      ,[WorkspaceVersion]
      ,[Type]
      ,[Comment]
      ,[CreationDate]
      ,[Computer]
      ,[PolicyOverrideComment]
      ,[LastAccessDate]
      ,[CheckInNoteId]
      ,[RefreshRecursive]
      ,[HasDeletedChanges]
      ,[SequentialId]
      ,[IsLocal]
      ,[PendingChangeSig]
      ,[FileTime]
      ,[LastMappingsUpdate]
      ,[ProjectNotificationId]
      ,[ItemIdCounter]
      ,[VersionStamp]
  FROM [Tfs_DefaultCollection].[dbo].[tbl_Workspace]

Record looks like :

PartitionId WorkspaceId OwnerId WorkspaceName   WorkspaceVersion    Type    Comment CreationDate    Computer    PolicyOverrideComment   LastAccessDate  CheckInNoteId   RefreshRecursive    HasDeletedChanges   SequentialId    IsLocal PendingChangeSig    FileTime    LastMappingsUpdate  ProjectNotificationId   ItemIdCounter   VersionStamp

1   420000001   65CED833-E406-4B14-BDB2-51590FE7569C    ws_5_1  1   0   Created by Distributed Task - getCode   2016-06-29 03:47:10.363 DUSTIN-PC   NULL    2016-06-29 03:47:10.363 NULL    0   0   22  1   B2140B25-F70A-4B4D-BFB1-184703037010    0   2016-06-29 03:47:10.370 37  -1025   2.00

[dbo].[tbl_WorkingFolder]

This use of this table is unclear. It describes the paths on the disk where the build-processes for each definition are hosted, but they also include a “workspace ID”. This would make sense for individual builds but not for latent build-definitions (workspaces for builds are ephemeral, unless the build fails, and are only created once the build starts). This seems promising for where the mappings for the build directories are stored, except that there appears to be potentially many records for the same build-definitions.

SELECT looks like:

SELECT TOP 1000 [PartitionId]
      ,[WorkspaceId]
      ,[ItemDataspaceId]
      ,[ProjectName]
      ,[ServerItem]
      ,[LocalItem]
      ,[MappingType]
      ,[CreationDate]
      ,[Depth]
  FROM [Tfs_DefaultCollection].[dbo].[tbl_WorkingFolder]

Record looks like:

PartitionId WorkspaceId ItemDataspaceId ProjectName ServerItem  LocalItem   MappingType CreationDate    Depth

1   320000001   21  TestProject $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\ C:\tfs>build>agent\>work\1\s\  1   2016-07-01 16:11:07.500 120
1   320000001   21  TestProject $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\Drops\   NULL    0   2016-07-01 16:11:07.500 120
1   360000001   21  TestProject $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\ C:\tfs>build>agent\>work\3\s\  1   2016-05-24 18:22:09.760 120
1   360000001   21  TestProject $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\Drops\   NULL    0   2016-05-24 18:22:09.760 120
1   420000001   21  TestProject $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\ C:\tfs>build>agent\>work\5\s\  1   2016-06-29 03:47:10.363 120
1   420000001   21  TestProject $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\Drops\   NULL    0   2016-06-29 03:47:10.363 120

[dbo].[tbl_WorkingFolderHistory]

The purpose of this table is completely unclear, though it includes build location paths (just like WorkingFolder).

SELECT looks like:

SELECT TOP 1000 [PartitionId]
      ,[WorkspaceId]
      ,[ItemDataspaceId]
      ,[ServerItem]
      ,[LocalItem]
      ,[Active]
  FROM [Tfs_DefaultCollection].[dbo].[tbl_WorkingFolderHistory]

Record looks like:

PartitionId WorkspaceId ItemDataspaceId ServerItem  LocalItem   Active

1   320000001   21  $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\ C:\tfs>build>agent\>work\1\s\  1
1   320000001   21  $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\Drops\   C:\tfs>build>agent\>work\1\s\Drops\    0
1   360000001   21  $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\ C:\tfs>build>agent\>work\3\s\  1
1   400000001   31  $\d0942daa"ac39"462f"8115"fada54d8f780\ C:\tfs>build>agent\>work\7\s\  0
1   400000001   31  $\d0942daa"ac39"462f"8115"fada54d8f780\Drops\   C:\tfs>build>agent\>work\7\s\Drops\    0
1   420000001   21  $\d67f9d95"2f6c"43f0"aa2f"6f7804fde7db\ C:\tfs>build>agent\>work\5\s\  1

AppEngine Development Environment Module Restrictions

AppEngine has some very tight but obvious restrictions on what types of Python modules can be invoked from application code. The general rule of thumb is that modules that need filesystem access or C code can’t be used. So, which modules are allowed or disallowed? Which modules are partially implemented, or defined and completely empty (yes, there are/were some)?

Unfortunately, the only official list of such modules is very dated.

There was a point, in the not-too-distant past, that the reigning perception of AppEngine’s module support was that the development environment does no such restriction, leaving a dangerous and scary gap between what will definitely run on your system and what you can be sure will run in production.

It turns out that there is some protection in the development environment.. Maybe even complete protection.

The google/appengine/tools/devappserver2/python/sandbox.py module appears to be wholly responsible for the loading of modules. At the top, there’s a sys.meta_path assignment. This is what appears as of version 1.8.4:

  sys.meta_path = [
      StubModuleImportHook(),
      ModuleOverrideImportHook(_MODULE_OVERRIDE_POLICIES),
      BuiltinImportHook(),
      CModuleImportHook(enabled_library_regexes),
      path_override_hook,
      PyCryptoRandomImportHook,
      PathRestrictingImportHook(enabled_library_regexes)
      ]

This defines a series of module “finders” responsible for resolving imported modules. This is where restrictions are imposed. The following are descriptions/insights about each one.

StubModuleImportHook: Replaces complete modules with different ones.
ModuleOverrideImportHook: Adjust partially white-listed modules (symbols may be added, removed, or updated).
BuiltinImportHook: Imposes a white-list on builtin modules. This raises an ImportError on everything else.
CModuleImportHook: Imposes a white-list on C modules.
path_override_hook: Has an instance of PathOverrideImportHook. It looks like this module looks for modules in special paths (the kind scattered in the.
PyCryptoRandomImportHook: Fixes the loading of Crypto.Random.OSRNG.new .
PathRestrictingImportHook: Makes sure any remaining imports come out of an accessible path.

If you have a question of what specific modules are involved, look in the sandbox.py module mentioned above. The first four finders are relatively concrete. Most of their modules are expressed in lists.