Taoffi's blog

prisonniers du temps

doc5ync–Trie database integration process

I continue here the excursion around using the Trie pattern and structures to index e-book words for the doc5ync project.

If you missed the beginning of the story, you can find it Here, Here and Here

The role of the client integration tool (a WPF app) is to pull e-books information to be indexed from the database, proceed to indexing the words and creating the links between each word and its related e-book. This is done using some settings: the language to index, the minimum number of chars to consider a sequence as a ‘word’… etc.

trie-with-data-db-integration-process

The integration process flow is quite simple:

  • Once we are happy with the obtained results, we use the tool to push the trie to the database in a staging table.
  • A database stored procedure can then extract the staging data into the tables used for presenting the index on the project web page.

trie-web-page

The staging table has a few fields:

  • The word string
  • The related e-book ID (relationship => docs table (e-books))
  • The number of occurrences of the word
  • The timestamp of the last insertion

The only difficulty encountered was the number of records (often tens of thousands) to push to the staging table. The (artisanal!) solution was to concatenated values of  blocks of records to be inserted (I.e.:  ‘insert into table(field1, field2, …) values ( v1, v2, …), (v3, v4, …), …’ etc.). Sending 150 records per command seemed to be a sustainable choice.

The staging table data is to be dispatched into two production tables:

  • doc5_trie_words:
    • word ID
    • language ID
    • word string
    • word’s number of occurrences
    • comments

 

  • doc5_trie_word_docs:
    • word ID (relationship => the above table)
    • e-book ID (relationship => docs (e-books) table)

 

Once the data is in the staging table, the work of the stored procedure is quite straightforward:

  • Delete the current words table (which cascade deletes the words / docs reference records)
  • Import the staging word (strings and occurrences) records into doc5_trie_words
  • Import the related word / doc IDs into doc5_trie_word_docs.

Many words are common between languages and e-books. Therefore assigning a language to a word has no sense unless all its related documents are from one specific language. That is the additional and final task of the stored proc.

Next step: the index web page presentation!

That will be the subject of the next post!

Back end – front end: Services loose coupling

Most mobile solutions are built and operate in a distributed architecture where services play a prominent role for providing data and applying business logic.

Let us take an example of a hotel reservation mobile app:

  • When the user specifies the desired target city, a back end service would provide the list of available managed/operated hotels at this place.
  • Once he or she specifies the desired stay period, the service would filter the above list to provide those having free rooms for that period.
  • When the user selects a room and applies for the reservation, the service would then register that reservation, proceeds to payment… etc.

It is hardly imaginable for such an app to operate independently from a back end service.

 

The price of distributed apps

In the previous example, the mobile app presents and manipulates several objects: the place (city/location…), the hotel, the room… etc.

Information about each of these objects is obtained from the service. The structures (metadata) of objects transmitted by the service should obviously match those handled by the mobile app.

In a typical (SOAP) situation, using Visual Studio for instance, your mobile app project would reference the service. Through the service's WSDL file, Visual Studio would generate the code for objects (classes) and operations defined by that referenced service.

If one of service's objects structure changes, you have to relaunch the process to update your app's service reference so that your objects' metadata be in sync with those defined by the service. If you don't do this, your app will simply crash at the first service call involving any unmatched object!

After each single change, deploying the solution's components (service / client app) become also a big hurdle.

In a few solutions, that may be desirable (or required). In most, the hurdle surpasses the benefits. It is hardly sustainable in a large solution. Actually it is not even sustainable when you are the sole developer of both components!

Resorting to use REST instead of SOAP (you may combine both) does not solve the difficulty, as that only changes the serialization format.

 

Using one single object

Let us imagine a service whose operations return always the same object. That would be quite handy independently of that object's serialization format.

In several large solutions I came to explore during last years, aggregating services responses into an abstract object, say, ServiceResponse was a common design practice. A given service operation response may look like (wsdl):

<element name="Operation1Response">
    <complexType>
        <sequence>
            <element name="Operation1Output"    type="Operation1OutputObject"/>
            <element name="ServiceStatus"     type="ServiceStatus"/>
        </sequence>
    </complexType>
</element>

 

With this, you know that any service operation you may call will always return a structure containing your expected object along with a service status object.

That approach seems good. Still it is going half way in abstraction, because you still should know the exact structure (metadata) of the expected object. And hence doesn't solve the evolution and deployment hassle: if, for some reason, you change the service's object structure, all your consumers should sync this change before deployment. The deployment of a new service version requires the deployment of a new consumers' versions… big hassle = increased risks.

 

Back to property bags!

I previously talked about property bags (here and here…).

A short reminder:

  • Objects (as we know till nowJ) are structures composed of: properties, methods and events.
  • An object can be considered as a set of properties (a property bag)
  • An object property can be defined as:
    • A name
    • A data type
    • A Value
  • Property value can be either:
    • A primitive type (value type or whatever we may consider as 'primitive'… string for instance)
    • A composite type: an object itself containing a set of properties (=property bag)

 

 

With that simple structure we are able to represent virtually any object in a strongly-typed manner.

 

Using property bags, we can imagine a unique structure for our service response. Which may look like this (wsdl):

<element name="ServiceResponse">
    <complexType>
        <sequence>
            <element name="OperationOutput"    type="PropertyBag"/>
            <element name="ServiceStatus"     type="PropertyBag"/>
        </sequence>
    </complexType>
</element>

 

Now, when we call any service operation, we know in advance that it will return an output property bag, accompanied with a status property bag.

Each element of our property bag being strongly typed (specifies its data type), we can easily convert the received bag to the expected object.

That conversion itself can be subject to a set of business rules. But that would be implemented 'once' independently of service or consumer versions and deployment constraints.

 

This approach doesn't eliminate the need for a unified business model. It enforces its usage.

By creating a loose coupling mechanism between services and consumers it allows more separation of concerns and minimizes evolution and deployment risks.

Exercise: service side code sample

Let us define service response as:

[DataContract(Namespace = "")]
public partial class ServicePropertyBagStatus : ServiceMessage

{
    PropertyBagContainer    _responseOutpput;

    [DataMember]

    public PropertyBagContainer ResponseOutput

    {

        get { return _responseOutpput; }

        set { _responseOutpput = value; }

    }
    …
    …

Our response structure would then look like (wsdl):

<complexType name="ServicePropertyBagStatus">
    <complexContent mixed="false">
        <extension base="ServiceMessage">
            <sequence>
                <element name="ResponseOutput" type="PropertyBagContainer"/>
            </sequence>
        </extension>
    </complexContent>
</complexType>

 

Sample code of a service operation (for reading a customer data) may look like this:

[OperationContract]
public ServicePropertyBagStatus GetCustomer(int customerId)

{
    return SvcUtilities.LoadCustomer(customerId);

}

Our service utilities module method LoadCustomer:

public static ServicePropertyBagStatus LoadCustomer(int customeId)
{
    ServicePropertyBagStatus    status    = new ServicePropertyBagStatus();

    if(customerId == 0)
    {
        status.SetErrorMessage("No value provided customerId");
        return status;
    }

    // load the customer's info from database server

    Customer        customer    = Customer.LoadDbUser(customerId);

    // extract this object's data contract into a property bag container

    status.ResponseOutput = ExtractPropertyBagContainer(customer.GetType(),customer);

    // return the service response status

    return status;
}

Extract the object's property bag:

internal static PropertyBagContainer ExtractPropertyBagContainer(Type type, object obj)
{
    // get soap xml of the object's data contract

    DataContractSerializer serializer = new DataContractSerializer(type);
    MemoryStream stream = new MemoryStream();

    serializer.WriteObject(stream, obj);

    // parse the soap xml into a property bag container
    PropertyBagContainer container = PropertyBagContainer.ParseXml(stream, true, null);

    stream.Close();
    return container;

}

Some Property bag container helper methods:

public class PropertyBagContainer
{
    protected PropertyBag _bag;


    // parse an object into a property bag container
    public static PropertyBagContainer ParseObject(object obj, ObjProperty parent);

    // parse xml stream into a property bag container
    public static PropertyBagContainer ParseXml(Stream stream, bool parseSiblings, ObjProperty parent);

    // parse xml node tree into a property bag container
    public static PropertyBagContainer ParseXml(XElement xnode, bool parseSiblings, ObjProperty parent);
}

Client side code sample

Our service operations will now return a Property Bag Container containing the expected object's property bag. A helper static method of PropertyBag (AssignObjectProperties) allows us to assign its content to a specific object:

public static bool AssignObjectProperties(object targetObject, PropertyBag bag)

 

We can thus write:

public bool ParsePropertyBag(PropertyBag bag)
{

return PropertyBag.AssignObjectProperties(this, bag);

}

 

Assigning property bag contents to an object is done by assigning values of the bag items to object's properties having the same name and data type. Extra logic can be introduced here according to your business requirements (for instance: checking object integrity through specific attributes).

 

Now, on the service consumer application, assume we have a service that is referenced as CustomerService. We may call the service's LoadCustomer operation like in the following code:

public bool LoadServiceCustomer(int customerId)
{
    CustomerService proxy = new CustomerService();
    var status = proxy.
LoadCustomer(customerId);


    // we may check the service status for errors here 
     ….

    // assign received properties to this object
    return this.ParsePropertyBag(status.ResponseOutput.PropertyBag);
}

Client side with Json

The process is the same in a REST configuration (You may see some details about WCF REST services here) as you actually will always receive a ServicePropertyBagStatus object independently of the transmission format (xml / json… etc.). Parsing the received response into a property bag container can be done using components like the .Net NewtonSoft.Json:

HttpWebRequest request = (HttpWebRequest) WebRequest.Create(url);
string str = GetResponseString(request);
ServicePropertyBagStatus  response = JsonConvert.DeserializeObject<ServicePropertyBagStatus>(str);
PropertyBagContainer container    = response.ResponseOutput;

You can download the property bag binaries (std + portable) here.

Meta-models: towards a universal dependency injection framework

Intro

I joined an interesting presentation today about unit tests. Part of the presentation was related to dependency injection usage for unit testing. That brought to my mind again the benefits of meta-models.

The problem

Unit testing often faces the problem of having to instantiate objects that may impact the test itself or simply make the test impossible.

A sound example of this is when the method to be tested requires the instantiation of an object requiring, for instance, a database connection.

In this schema, we have 3 actors

  • The function
  • The caller
  • The object (SomeObject) which cannot be instantiated.

 

Traditional recipe

One, now traditional, recipe is to use an Interface (instead of the specific object) as a function parameter. And, according to the context, instantiate and use an object that implements the defined interface. The figure below illustrates this.

 

Good solution… a little acrobatic, but OK… good.

 

A problem, though. We now have 6 actors (to code and maintain):

  • The Function
  • The caller
  • The original 'true' object (SomeObject)
  • The Interface
  • The interface implementations (=2)!

 

On the other hand, testing a function may also need to instantiate a 'true' object context. In which case, we would have to rewrite (and recompileJ) our test code to instantiate a 'true' implementation when required.

A third point: in this solution, we defined the Interface to represent an abstraction of the original object. What if the original object itself was, for instance, a database record? This would then introduce a new actor in our playgroundJ

 

As Bjarne Stroustrup puts it:

"Any verbose and tedious solution is error-prone because programmers get bored" J

 

Meta-models may be a better way

In the current case, meta-models (see my previous posts) can be used to more simply describe all actors (object structures / methods / properties…).

An illustration:

 

 

Reminder: meta-models are closely related and enforced by Reflection (assemblies' meta-data). Through meta-models AND Reflection (namespace System.Reflection) developers can gain more flexibility to create versatile and scalable software.

 

In our present case, meta-models not only expose less actors to code and maintain, but also lower the complexity level of involved actors (for instance: maintaining database records instead of hard coding implementation).

 

The main goal for this approach is to ultimately write fewer universal methods to invoke and instantiate any object!

Will try to deliver a code sample in a future post. In the meantime, you may download meta-model sample code here!

A dive into the undocumented TFS meta-model – Part II

 

In a previous post, I started exploring the TFS configuration database. Let's continue here by having a look at some more configuration objects and relationships.

TFS Config security objects

 

Referenced table

Primary column

Table

Foreign column

tbl_security_identity_cache

tf_id

tbl_gss_group_membership

member_id

tbl_security_identity_cache

tf_id

tbl_security_domain_groups

group_id

tbl_security_identity_cache

tf_id

tbl_security_membership_cache

container_id

tbl_security_identity_cache

tf_id

tbl_security_membership_cache

member_id

tbl_gss_groups

tf_id

tbl_gss_group_membership

parent_group_id

tbl_security_domain

domain_number

tbl_security_domain_groups

domain_number

tbl_security_domain

domain_number

tbl_security_projects

domain_number

tbl_security_identity_type

type_id (int)

tbl_security_identity_cache

type (tinyint)

The relationship diagram above suggests that we can query user logins, group-membership for specific projects by a query like the following:


SELECT TOP (100) PERCENT
    login.display_name  AS user_name,
    proj.scope_name     AS project,
    login_grp.display_name AS user_group

FROM dbo.tbl_security_membership_cache AS membership INNER JOIN
  dbo.tbl_security_identity_cache AS
login ON membership.member_id = login.tf_id INNER JOIN
  dbo.tbl_security_projects AS proj INNER JOIN
     dbo.tbl_security_domain AS domain INNER JOIN
       dbo.tbl_security_domain_groups AS grp ON domain.domain_number = grp.domain_number
       ON proj.domain_number = domain.domain_number
     ON
membership.container_id = grp.group_id INNER JOIN
  dbo.tbl_security_identity_cache AS
login_grp ON grp.group_id = login_grp.tf_id

ORDER
BY user_name, project

 

The above query may give us results similar to the following

user_name

project

user_group

Administrator

Research education "agile

Administrators

Administrator

Research education "agile

[TEAM FOUNDATION]\Team Foundation Service Accounts

Administrator

SourceSafeProjects

[TEAM FOUNDATION]\Team Foundation Service Accounts

Administrator

SourceSafeProjects

Administrators

Administrator

DefaultCollection

[TEAM FOUNDATION]\Team Foundation Service Accounts

Administrator

DefaultCollection

Administrators

 

TFS database pause: Change set quantitative statistics sample

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

 

A dive into the undocumented TFS meta-model – Part I

TFS… introduction to this series


Microsoft TFS (Team Foundation Server… recently renamed to ALM: Application Lifecycle Management) is a great project management platform.

TFS is built on meta-model abstractions (that were first seen, in Microsoft's products, in SharePoint).

TFS, like SharePoint, is composed of several software components which operate around several databases: the configuration database and one or more content databases.

Configuration and content databases' elements (tables, views, functions, stored procedures… etc.) represent the meta-models (schemas and business logic for each related feature) which govern and control much of the software components' behaviors.

TFS is often presented as a software project management solution. In my view, this seems a little reductive of its value. Because TFS features truly span a much larger project management scope.

Many useful literatures have been written about TFS: its features, usage, guidance, troubleshooting… etc. Few have enlightened its meta-model structures and, as of my knowledge, none has put some clear light about its (undocumented) databases' structures. That is what I will try to do in this series, more to value TFS abstractions than to nude its internal mechanics. And also to enforce, again, the meta-model approach as a rational basis for software solutions.

 

TFS logical architecture

 

For its contents, TFS maintains a database per project-collection.

Each collection may contain one or more projects. And each project can be managed according to a selected methodology (Agile / Scrum / CMMI… etc.). In fact, TFS applies and operates the selected methodology through the provided methodology project management template. Ultimately, this means you can build your own methodology and use it to manage your project with TFS. All what you need is to provide the desired methodology template.

Managing a project with TFS consists of recording and following-up specific Work items (defined by the methodology template)… those can be items like: Tasks, Bugs, Change requests, User story… etc.

As TFS was initially brewed to manage software projects, a common item in each project is the Source control repository which helps developers to maintain their source code versions and branches in a very efficient architecture composed of 'change sets'.

 

 

To discover and figure out its components' data and their required behaviors, TFS uses a configuration database. A first look at the configuration database will be the subject of this first article in the series.

 

The configuration database


Like SharePoint, TFS databases are delivered without documentation or relational diagrams. They simply don't contain any explicit Foreign-keys or relationships. Whatever the reason behind this choice, that seems a little surprising of a product whose main aim is projects' transparency and documentationJ.

That said, to discover, understand and correctly interpret the role of each TFS database item and their inter-relationships you should do a deep dive and be armed with some patience and empiric methods!

 

Hosts and processes


Let's have a look at three important tables:

  • tbl_ServiceHost contains the list of services' host information. This includes the item's name, the database connection string (when relevant), the Virtual directory and Resource directory…
  • tbl_ServiceHostProcess contains information about current processes. Machine name, Process name, Identity under which the process is running, start time…
  • tbl_ServiceHostInstance contains the list of processes' instances per host (Process Id / Host Id / Start time).

 

In my case, the table tbl_ServiceHost contained 3 entries for:

  • Team Foundation: root item (parentless… see diagram ParentHostId column). Connected to the configuration database.
  • Two child entries each for one of my TFS Project collections:
    • Default collection.
    • Training collection.

 

The above relational schema allows us to query the tables and obtain results like the following about current running services:


SELECT TOP (100) PERCENT
svcHost.Name         AS host,
svcHost.VirtualDirectory,
svcHost.ConnectionString,
process.MachineName    AS machine,
process.ProcessName,
process.StartTime


FROM dbo.tbl_ServiceHostInstance AS instance INNER JOIN
dbo.tbl_ServiceHost AS svcHost ON instance.HostId = svcHost.HostId LEFT OUTER JOIN
dbo.tbl_ServiceHostProcess AS process ON instance.ProcessId = process.ProcessId LEFT OUTER JOIN
dbo.tbl_ServiceHost AS hostParent ON svcHost.ParentHostId = hostParent.HostId

ORDER BY process.StartTime DESC, hostParent.Name, host

 

Sample results

host 

VirtualDirectory 

ConnectionString 

machine

ProcessName 

StartTime 

TEAM FOUNDATION 

NULL 

Data Source= hp10\SqlTfs;Initial Catalog=Tfs_Configuration;Integrated Security=True

HP10 

w3wp.exe 

2012-05-21 20:18:09.997 

DefaultCollection 

~/DefaultCollection/ 

Data Source=hp10\sqltfs;Initial Catalog=tfs_defaultcollection;Integrated Security=True 

HP10 

w3wp.exe 

2012-05-21 20:18:09.997 

Training collection 

~/Training collection/ 

Data Source=hp10\SqlTfs;Initial Catalog="Tfs_Training collection";Integrated Security=True

HP10 

w3wp.exe 

2012-05-21 20:18:09.997 

TEAM FOUNDATION 

NULL 

Data Source=hp10\SqlTfs;Initial Catalog=Tfs_Configuration;Integrated Security=True 

HP10 

TfsJobAgent.exe 

2012-05-21 06:51:37.870 

DefaultCollection 

~/DefaultCollection/ 

Data Source=hp10\sqltfs;Initial Catalog=tfs_defaultcollection;Integrated Security=True 

HP10 

TfsJobAgent.exe 

2012-05-21 06:51:37.870 

Training collection 

~/Training collection/ 

Data Source=hp10\SqlTfs;Initial Catalog="Tfs_Training collection";Integrated Security=True

HP10 

TfsJobAgent.exe 

2012-05-21 06:51:37.870 

 

Ressources and services

Again, let's follow up the following tables:

  • tbl_CatalogResourceType stores a list of resource types. Like: 'Machine', 'Infrastructure Root', 'Project Server', 'Team Foundation Project Collection Database'… etc. Here is some sample entries:

Identifier 

DisplayName 

Description 

9FB288AE-9D94-40CB-B5E7-0EFC3FE3599F 

Default location for team project portals 

By default, each team project Web site will be a sub-site of this location.

0584A4A2-475B-460E-A7AC-10C28951518F 

Machine 

A machine that exists as part of the TFS deployment. 

14F04669-6779-42D5-8975-184B93650C83 

Infrastructure Root 

The root of the catalog tree that describes the physical makeup of the TFS deployment.

1B6B5931-69F6-4C53-90A0-220B177353B7 

Team Foundation Project Collection Database 

The database that houses the information for a Team Foundation Project Collection. 

526301DE-F821-48C8-ABBD-3430DC7946D3 

Team Foundation Configuration Database

The database that houses the information for a Team Foundation Application Instance. 

289DD275-CECA-4698-8042-38D2E86FC682 

Project Server 

Project Server Information 

3DADD190-40E6-4FC1-A306-4906713C87CE 

SharePoint Web Application 

A SharePoint Web application that has been configured to support team project portals.

3C856555-8737-48B6-8B61-4B24DB7FEB15 

Test Controller 

This is a test controller type in Catalog Service. 

26338D9E-D437-44AA-91F2-55880A328B54 

Team Project Collection 

A Team Project Collection that exists within the TFS deployment.

450901B6-B528-4863-9876-5BD3927DF467 

Project Portal 

A Web site or SharePoint site that provides a portal for a team project. 

15DA1594-45F5-47D4-AE52-78F16E67EB1E 

Process Guidance 

A Web site or SharePoint library that provides process guidance for a team project.

47FA57A4-8157-4FB5-9A64-A7A4954BD284 

Team Web Access 

Team Web Access Location 

48577A4A-801E-412C-B8AE-CF7EF3529616 

Team Project 

A Team Project that exists within the TFS deployment. 

 

  • tbl_CatalogResource stores resources (each referring to one of the types in the above tbl_CatalogResourceType table. Sample entries:

 

Identifier 

ResourceType 

DisplayName 

Description 

7462A882-741B-4F91-885F-1405A22256DF 

1B6B5931-69F6-4C53-90A0-220B177353B7 

Tfs_Training collection

 

C5CD36C5-8373-4BF7-B3FA-16199DAFF106 

48577A4A-801E-412C-B8AE-CF7EF3529616 

cmmi project 

cmmi training project 

16480162-C502-4197-872A-4DDDFE49BA90 

1B6B5931-69F6-4C53-90A0-220B177353B7 

tfs_defaultcollection 

 

DCC270E7-6EC7-45BF-A827-53BBB5386606

14F04669-6779-42D5-8975-184B93650C83 

Infrastructure Root 

The root of the catalog tree that describes the physical makeup of the TFS deployment. 

2F8C7D1C-EB1F-4058-B34D-60FA2F6CA7DC 

48577A4A-801E-412C-B8AE-CF7EF3529616 

Agile project 

 

D7121DA7-3E32-49C3-BCB1-68C83C4383B7

47FA57A4-8157-4FB5-9A64-A7A4954BD284 

Team Web Access 

 

4B812279-A7A6-4A8A-92A4-7AA208589FEB 

EB1E0B3B-FAA1-49D2-931A-FDC373682BA5 

SQLTFS 

 

F69EFD2B-51C6-461B-9E49-8089DF86C9A3 

0584A4A2-475B-460E-A7AC-10C28951518F 

hp10 

 

5242DBFC-06C8-4CAD-A27B-99212BEEE999

526301DE-F821-48C8-ABBD-3430DC7946D3 

Tfs_Configuration 

 

713FF18A-E0E8-4192-A7B9-A0900F50A5BC 

26338D9E-D437-44AA-91F2-55880A328B54 

DefaultCollection 

 

 

  • tbl_ServiceDefinition stores a list of predefined services (classes) and the web access page for each. Sample entries (among other things, you may note curly braces which are place holders for some url parameters in the RelativePath column):

 

ServiceType 

Identifier 

DisplayName 

RelativePath 

AdministrationService 

C18D6E34-68E8-40D2-A619-E7477558976E

Administration Service 

/TeamFoundation/Administration/v3.0/AdministrationService.asmx 

Annotate 

74B15E02-0AC2-414F-A9B9-30268659D3B5 

Team Web Access (Annotate) 

/web/ann.aspx 

AnnotateSourceControlItem

D271E722-C261-4BC2-B0F7-1C8A9E13F907 

Team Web Access (AnnotateSourceControlItem) 

/web/ann.aspx?pcguid={projectCollectionGuid}&path={itemPath}&cs={itemChangeset} 

CatalogService 

C2F9106F-127A-45B7-B0A3-E0AD8239A2A7 

Catalog Service 

/TeamFoundation/Administration/v3.0/CatalogService.asmx

ChangesetDetail 

D40EF625-CCA7-4E73-B9EC-86CBE1534CE0 

Team Web Access (ChangesetDetail) 

/web/cs.aspx 

CreateWorkItem 

14CD69C6-88F9-4C8C-A259-D2441D77D1AF

Team Web Access (CreateWorkItem) 

/web/wi.aspx?puri={projectUri}&wit={workItemType} 

Difference 

2B84D900-1F08-486C-9C47-0E6AF371D03C 

Team Web Access (Difference) 

/web/diff.aspx 

Eventing 

C424AE04-8C6F-4516-8B2D-238FFFCA3081 

Event Service 

/TeamFoundation/Administration/v3.0/EventService.asmx

IdentityManagementService 

3DE26348-00BE-4B82-8E4A-E5AD004CFECD 

Identity Management Service 

/TeamFoundation/Administration/v3.0/IdentityManagementService.asmx 

JobService 

DA1C0184-14FE-4E13-B7FC-6EAA07D84BE8 

TFS Background Job Service 

/TeamFoundation/Administration/v3.0/JobService.asmx 

 

  • tbl_CatalogServiceReference stores per-resource services' entries. (Resource Identifier / Service Identifier…).

 

ResourceIdentifier 

AssociationKey 

ServiceIdentifier 

ServiceType 

D7121DA7-3E32-49C3-BCB1-68C83C4383B7 

ChangesetDetail 

D40EF625-CCA7-4E73-B9EC-86CBE1534CE0

ChangesetDetail 

D7121DA7-3E32-49C3-BCB1-68C83C4383B7 

CreateWorkItem 

14CD69C6-88F9-4C8C-A259-D2441D77D1AF 

CreateWorkItem 

D7121DA7-3E32-49C3-BCB1-68C83C4383B7 

Difference 

2B84D900-1F08-486C-9C47-0E6AF371D03C 

Difference 

D7121DA7-3E32-49C3-BCB1-68C83C4383B7

DiffSourceControlItems 

5E91C4DA-0013-4EBB-943D-CC77F5ADB82D 

DiffSourceControlItems 

D7121DA7-3E32-49C3-BCB1-68C83C4383B7 

DiffSourceControlShelvedItem 

4C81A44D-67AB-4D23-9CBE-339C9102993B 

DiffSourceControlShelvedItem 

D7121DA7-3E32-49C3-BCB1-68C83C4383B7 

ExploreSourceControlPath

AC0770BC-1DD6-4B8E-A811-5A03690DF44F 

ExploreSourceControlPath 

D7121DA7-3E32-49C3-BCB1-68C83C4383B7 

Home 

0F9CED5D-89F9-4743-BAB8-FA511FF09A8C 

TSWAHome 

 

This allows us to query the services in a way similar to the following:

SELECT resType.DisplayName AS ResourceType,
svc.DisplayName AS Service,
svc.Description AS serviceDescription
FROM dbo.tbl_ServiceDefinition AS svc INNER JOIN
dbo.tbl_CatalogServiceReference AS svcRef ON svc.Identifier = svcRef.ServiceIdentifier
INNER JOIN
dbo.tbl_CatalogResource AS Res ON svcRef.ResourceIdentifier = Res.Identifier
INNER JOIN
dbo.tbl_CatalogResourceType AS resType ON Res.ResourceType = resType.Identifier

 

 

Sample results:

ResourceType

Service

serviceDescription

Team Web Access

Team Web Access (Annotate)

 

Team Web Access

Team Web Access (AnnotateSourceControlItem)

 

Team Web Access

Team Web Access (ChangesetDetail)

 

Team Web Access

Team Web Access (CreateWorkItem)

 

Team Web Access

Team Web Access (Difference)

 

Team Web Access

Team Web Access (DiffSourceControlItems)

 

Team Web Access

Team Web Access (DiffSourceControlShelvedItem)

 

Team Web Access

Team Web Access (DiffSourceControlShelvedItem)

 

Team Web Access

Team Web Access (ExploreSourceControlPath)

 

Team Project Collection

Location Service

Location Service for Visual Studio Team Foundation Server.

Team Project Collection

Location Service

Location Service for Visual Studio Team Foundation Server.

Team Foundation Server Instance

Location Service

Location Service for Visual Studio Team Foundation Server.

Team Web Access

Team Web Access (OpenWorkItem)

 

 

More about TFS meta-models in following posts!