Ravenbrook / Projects / Perforce Defect Tracking Integration / Version 2.4 Product Sources / Design

Perforce Defect Tracking Integration Project

Python interface to TeamTrack: design

Gareth Rees, Ravenbrook Limited, 2000-08-08

1. Introduction

This document describes a Python extension that provides an interface to the TeamTrack API. It follows the design document procedure [RB 2000-10-05] and the design document structure [RB 2000-08-30].

This document is obsolete. The P4DTI no longer supports integration with TeamTrack. This document is retained for reference purposes, but will not be maintained.

The purpose of this document was to make it possible for people to maintain the extension, and to use the Python interface.

This document will not be modified as the product is developed.

The readership of this document is the product developers.

This document is not confidential.

2. TeamTrack configurations

2.1. Configurations

The Python interface to TeamTrack is designed to work in all the supported configurations.

There are four dimensions to consider in a TeamTrack configuration:

  1. The TeamTrack server version and build number. We support release 4.5 (various builds), 5.0 (build 5034), and 5.5 (build 55012). We also work with release 5.02 (build 50207), but do not support it.

  2. The TeamTrack API version. We support versions 2 and 3.

  3. The TeamTrack database schema. We support all TeamTrack database schemas from version 21.

  4. Whether the TeamTrack database was created in TeamTrack prior to 5.0, with a TS_CASES table, or in TeamTrack 5.0 or later, with a TTT_ISSUES table.

Table 1 sets out the compatibility between TeamTrack API versions and TeamTrack releases, as far as we know. "T" in the table means that we have tested that the combination works. "Y" means that we believe the combination works. "N" means that we know the combination does not work. "?" means we don't know.

Table 1. TeamTrack API compatibility

API revision TeamTrack version
4.5 5.0 5.01 5.02 5.5
2.1 T N N N N
3.1 N T Y1 Y1 Y1
3.3 N T Y T T
3.5 N N2 N2 N2 T


  1. Except when adding fields to the database; see [Shaw 2001-10-01].
  2. See [Shaw 2002-02-05].

TeamShare plan to move all customers to TeamTrack 5.5 by 2002-05-31. So after that point we may be able to drop support for older TeamTrack server versions and older TeamTrack API revisions. See [Shaw 2001-12-19].

2.2. API versions

To support both TeamTrack 4.5 and TeamTrack 5.*, we build two versions of the Python interface to TeamTrack:

  1. teamtrack45.pyd is the interface to TeamTrack 4.5 servers.
  2. teamtrack50.pyd is the interface to TeamTrack 5.* servers.

The module teamtrack.py calls either from teamtrack45 import * or from teamtrack50 import * according to the value of the teamtrack_version configuration parameter. That way, the P4DTI can just import teamtrack as before.

Note that there's no way to determine a TeamTrack server version through the API. If you have an incompatible API revision, then you may not be able to connect at all. See [Shaw 2001-07-02] and job000458.

2.3. Database schema

In TeamTrack 4.5 (and TeamTrack 5.* with a database upgraded from TeamTrack 4.5) the cases table is called TS_CASES and its table number is 1. In TeamTrack 5.0, the cases table is called TTT_ISSUES and its table number is 1000. This can't be determined by reference to the API version or by the database version (since these are the same in TeamTrack 5.0 whether the database was upgraded from TeamTrack 4.5 or not). Instead, one must check to see if the TS_TABLES table has a record with TS_ID = 1 (if so, that table is the cases table), or if there is no so record, run the query SELECT * FROM TS_TABLES WHERE TS_SNAME LIKE 'Issue' and take the TS_ID and TS_DBNAME fields of the record returned. This is the method suggested by TeamShare in [Shaw 2001-06-27] and [Shaw 2001-06-28].

This interface supports these configurations portably using server methods case_table_id and case_table_name.

3. Interface reference

3.1. Overview

The interface defines one module, teamtrack (use import teamtrack). There are two classes, representing TeamTrack servers and records from the TeamTrack database. These classes don't have names in Python: the only way to make a server object is to connect to a server using teamtrack.connect, and the only way to make record objects (at present) is to query the server.

Some features are not supported by every version of the TeamTrack API. The teamtrack module indicates which features it supports using the feature dictionary.

3.2. Error handling

Errors are always indicated by throwing a Python exception: teamtrack.error for errors in the interface; or teamtrack.tsapi_error for errors in the TeamTrack API. The interface never indicates errors by returning an exceptional value from a function. Exceptions of both types are associated with a message. For example:

  # do teamtrack stuff
except teamtrack.error, message:
  print 'teamtrack interface error: ', message
except teamtrack.tsapi_error, message:
  print 'TeamTrack API error: ', message

The teamtrack module can throw other exceptions than teamtrack.error and teamtrack.tsapi_error, notably KeyError (when a field name is not found in a record).

3.3. Identifiers in the teamtrack module

teamtrack.connect(user, password, hostname)

Connect to a TeamTrack server on hostname (use the format "host:8080" to specify a non-default port number) with the specified userid and password. If successful, return a server object representing the connection. For example:

import socket
server = teamtrack.connect('joe', '', socket.gethostname())


teamtrack.error is the Python error object for errors that occur in the teamtrack module.


A dictionary that maps the name of a feature to 1 if the feature is supported. (So you can test if the Python interface to TeamTrack supports feature foo by evaluating teamtrack.feature.get('foo').) The following feature names are defined:

When this feature is present, record objects (section 3.5) support the submit method.


A dictionary that maps the name of a table in the TeamTrack database (minus the initial TS_) to its table identifier (a small integer). For example


is the table identifier for the TS_CASES table.


A dictionary that maps the name of a TeamTrack field type to its identifier (a small integer). For example


is the field type for a text field.


teamtrack.tsapi_error is the Python error object for errors that occur in the TeamTrack API.

3.4. Server methods


Returns the table id of the table containing the cases (this is table 1 in databases created in TeamTrack 4.5 and 1000 in databases created in TeamTrack 5.0).


Returns the name of the table containing the cases (this is TS_CASES in databases created in TeamTrack 4.5 and TTT_ISSUES in databases created in TeamTrack 5.0).

delete_record(table_id, record_id)

Delete the record with the specified identifier from the specified table (which must be one of the table identifiers specified in teamtrack.table).


Returns a new record object. The record has the fields in the schema for the specified table (which must be one of the table identifiers specified in teamtrack.table), and is suitable for adding or submitting to that table.

query(table_id, where_clause)

Execute an SQL query on the specified table (which must be one of the table identifiers specified in teamtrack.table) of the form


(if where_clause is the empty string), or

SELECT * FROM table WHERE where_clause

(otherwise). Return the records matching the query as a list of record objects.

Remember to use the right field names in the where clause: the returned record may contain a field called foo, but the database field is probably TS_FOO. See [TeamShare 2000-01-20] for details of the TeamTrack database.

read_record(table_id, record_id)

Read the record from the specified table (which must be one of the table identifiers specified in teamtrack.table) with the specified record identifier. If successful, return a record object representing the record. For example, the call

record = server.read_record(teamtrack.table['CASES'], 17)

is roughly equivalent to the SQL query


read_state_list(workflow_id, include_parent=0)

Return a list of states which are available to cases in the given workflow. If the include_parent argument is 1, then the list includes states inherited from the parent workflow.

The returned list is a list of record objects from the TS_STATES table.


Return a list of states which are available to cases in the given project.

The returned list is a list of record objects from the TS_TRANSITIONS table.

3.5. Record methods

Records present (part of) the Python dictionary interface. To look up a field in a record object, index the record object with the field name. For example:

# Get the title of case 17
record = server.read_record(teamtrack.table['CASES'], 17)
title = record['TITLE']

To update a field in a record object, assign to the index expression. For example, record['TITLE'] = 'Foo'.

As for ordinary Python dictionaries, the has_key method determines if a field is present in the record, and the keys method returns a list of names of fields in the record.


Add the record to its table in the TeamTrack database. Update the record object so that it matches the new record in the table. Return the record object.


Add a field to the database schema, by adding a record to the TS_FIELDS table and using the information in that record to add the field to the appropriate table. Fields can only be added to the following tables: Cases, Incidents, Companies, Contacts, Merchandise, Products, Problems, Resolutions, and Service Agreements.

The record object must be in the right format for adding to the TS_FIELDS table. Its TABLEID field is used to determine which table the field should be added to, and its FLDTYPE field is used to determine the type of the added field (it should be one of the vaues in the teamtrack.field_type dictionary.

For example, to add a field to the TS_CASES table:

f = server.new_record(teamtrack.table['FIELDS']
f['TABLEID'] = teamtrack.table['CASES']
f['NAME'] = 'Cost to fix (in euros)'
f['DBNAME'] = 'COST'
f['FLDTYPE'] = teamtrack.field_type['NUMERIC']

See [TeamShare 2000-01-20] for details of the fields in the TS_FIELDS table and what they mean.

submit(login_id, project_id = None, folder_id = 0)

This method is supported if and only if the submit feature is supported.

Submit a new record into the workflow. The login_id argument must be a string containing the user name of the user on whose behalf the record is being submitted (this must correspond to the TS_LOGINID field for a record in the TS_USERS table).

The remaining arguments are optional. The project_id argument is the project the record should belong to; if it is not supplied then it is taken to be the TS_PROJECTID field in the submitted record. The folder_id argument corresponds to the nFolderId argument to the TSServer::Submit method. I don't know what it means. It defaults to zero if not supplied.

Returns the record identifier of the submitted record.

For example, this fragment of code takes a copy of case 1 and submits it as a new case on behalf of user Joe:

# Make a copy of case 1
old_id = 1
new = server.new_record(teamtrack.table['CASES'])
old = server.read_record(teamtrack.table['CASES'], old_id)
for f in fields_to_copy:
  new[f] = old[f]

# Submit the copy.
new_id = new.submit('Joe')


Return the table identifier of the table in the TeamTrack database to which this record corresponds. (For record retrieved from the TeamTrack database this is the table they came from; for records created using the server's new_record method, this is the table whose schema the record matches.)

transition(login_id, transition, project_id = None)

Transition a record in the workflow. In version 1.2 of the API, only records in the TS_CASES and TS_INCIDENTS tables can be transitioned. The login_id argument must be a string containing the user name of the user on whose behalf the transition is being made (this must correspond to the TS_LOGINID field for a record in the TS_USERS table). The transition argument is an integer identifying the transition to be carried out. It must be the TS_ID field of a record in the TS_TRANSITIONS table.

The project_id argument is the project the record should belong to after the transition; if it is not supplied then it is taken to be the TS_PROJECTID field in the record.

It is not straightforward to pick a transition that apply to a case if you don't know the transition's number. Transitions are local to workflows (and so there may be several transitions with a given name), but workflows form a hierarchy and inherit transitions from their parent.

The algorithm shown below constructs a map from workflow id and transition name to the transition record corresponding to that name in that workflow, and a map from project id to workflow id.

# Get all the transitions and workflows from the TeamTrack database.
transitions = server.query(teamtrack.table['TRANSITIONS'], '')
workflows = server.query(teamtrack.table['WORKFLOWS'], '1=1 ORDER BY TS_SEQUENCE')

# transition_map is a map from workflow id and transition name to
# the transition corresponding to that name in that workflow.
transition_map = {}
for t in transitions:
  # This is really a workflow id, not a project id (see the TeamTrack
  # schema documentation).
  w = t['PROJECTID']
  if not transition_map.has_key(w):
    transition_map[w] = {}
  if not transition_map[w].has_key(t['NAME']):
    transition_map[w][t['NAME']] = t

# Now go through all the workflows and add transitions they inherit from
# their parent workflow. This works because we've used the TS_SEQUENCE
# field to put the workflows in pre-order, so that all of a workflow's
# ancestors is considered before the workflow itself is considered.
for w in workflows:
  if not transition_map.has_key(w['ID']):
    transition_map[w['ID']] = {}
  if w['PARENTID']:
    for name, transition in transition_map[w['PARENTID']].items():
      if not transition_map[w['ID']].has_key(name):
        transition_map[w['ID']][name] = transition

# project_workflow is a map from project id to workflow id.
projects = server.query(teamtrack.table['PROJECTS'], '')
project_workflow = {}
for p in projects:
  project_workflow[p['ID']] = p['WORKFLOWID']

With these maps, we can apply the "Resolve" transition to case 12 on behalf of user Newton:

case = server.read_record(teamtrack.table['CASES'], 12)
transition = transition_map[project_workflow[case['PROJECTID']]]['Resolve']
case.transition('newton', transition['ID'])


Update the record in the TeamTrack database that corresponds to this record object. Update the record object so that it matches the updated record in the table. Return the record object. If unsuccessful, raise a teamtrack.error exception.

For example, to add some text to the description of case 2:

case = server.read_record(teamtrack.table['CASES'], 2)
case['DESCRIPTION'] = case['DESCRIPTION'] + '\nAdditional text.'

4. Notes on the extension

Python extension modules are described in [Lutz 1996, 14]. Additional details with respect to building Python extensions using Visual C++ on Windows are given in [Hammond 2000, 22].

The TeamTrack API is described in [TeamShare 2001-12-12].

4.1. Building the extension

I have only built the extension under Windows NT and Windows 2000 using Microsoft Visual C++. I believe it should build and run anywhere that Python and the TeamTrack API run.

TeamShare provide two versions of their library: tsapi.lib and TSApiWin32.dll. I can build extensions using the former but not using the latter. I guess that the former is suitable for console applications and that latter for MFC applications.

There are two places where I have used Windows-specific code (in both cases the code is protected by #if defined(WIN32) ... #endif):

  1. Sockets on Windows need to be initialized. The TeamTrack API provides the function TSInitializeWinsock to do this. I call this from initteamtrack in teamtrackmodule.cpp.

  2. Functions that are exported from a DLL need either to have the declarator __declspec(dllexport) or to be mentioned in a /DLLEXPORT:foo compiler option. We use the former method, defining the macro TEAMTRACK_EXPORTED for this purpose. The only exported function is initteamtrack in teamtrackmodule.cpp.

In the Developer Studio project for the Python interface to TeamTrack, there are three configurations. The "Release" configuration is normal. The "Debug" configuration builds a debugging interface but links with the non-debugging Python libraries. The "Python Debug" configuration builds a debugging interface and links with the debugging Python libraries. To use the third configuration you have to build a debugging version of Python (the binary distribution doesn't come with one).

Note that Python extensions have to be linked with the same Python library that the Python interpreter and all other extensions are linked with. So you can't build one extension with the debugging Python libraries and expect it to work with other extensions linked with the release Python libraries. This is explained in [Hammond 2000, 22].

4.2. Reference counting

Reference count management is briefly introduced in [Lutz 1996, page 585], but there's a much better account in [van Rossum 1999-04-13, 1.10].

I've commented each use of Py_DECREF with one of:

Where a Py_DECREF would be expected (because the object has been passed to a new owner) but is not needed because the new owner does not increment the reference count, I have added a note to say so. This applies to objects passed to PyList_SetItem and PyTuple_SetItem (I guess that these functions are optimized for the case where a newly-created object is added to the structure). See [van Rossum 1999-04-13, 1.10.2].

Python objects returned from functions need to have an extra reference count since they will be immediately put onto Python's stack without their reference count being incremented. Returning newly-created objects is safe, since they are always created with a reference count of 1. Other returned objects need to have their reference counts incremented.

4.3. Memory allocation errors

The TeamTrack API is very memory-hungry: a record from the TS_CASES table can take more than 600kb to represent in memory in the API client [Shaw 2001-04-16].

The TeamTrack API makes no attempt to check that memory allocation succeeds [GDR 2000-09-11, 2.2.2].

These two defects mean that running out of memory is commonplace and that this won't be detected, leading quickly to memory corruption and crashing.

TeamShare recommend two approaches to work around these defects:

  1. Don't select very many records at a time. This is proposed in [Shaw 2001-04-16] and analyzed in [GDR 2001-05-16]. This can be implemented in Python, so isn't considered any further here.

  2. To install a C++ memory exception handler and catch out of memory exceptions in the client code [Schreiber 2001-04-09] (this won't actually catch all memory allocation errors, since the TeamTrack API uses a mixture of C memory allocation (malloc and free) and C++ memory allocation (new and delete); the memory exception handler won't catch failed calls to malloc). An example of the second approach is given in [TeamShare 2001-04-09].

    I tried out this approach, and discovered the following:

    1. You can't add a memory exception handler when writing a Python extension library: Python installs its own memory exception handler and complains if you try to install your own.

    2. Python's memory exception handler catches memory allocation failures in the TeamTrack API and reports them as MemoryError exceptions.

    3. In our recommended configuration, where the P4DTI runs on the same machine as the TeamTrack server, it's the server that fails when memory is low, not the client (eventually a client call to recv on the socket blocks and eventually this times out). So maybe our advice is poor? See job000321.

A. References

[GDR 2000-09-11] "TeamTrack API comments"; Gareth Rees; Ravenbrook Limited; 2000-09-11.
[GDR 2001-05-16] "Performance analysis of TeamTrack API workarounds"; Gareth Rees; Ravenbrook Limited; 2001-05-16.
[Hammond 2000] "Python Programming on Win32"; Mark Hammond and Andy Robinson; OReilly; 2000-01; ISBN 1-56592-621-8.
[Lutz 1996] "Programming Python"; Mark Lutz; O'Reilly; 1996-10; ISBN 1-56592-197-6.
[RB 2000-08-30] "Design document structure" (e-mail message); Richard Brooksby; Ravenbrook Limited; 2000-08-30.
[RB 2000-10-05] "P4DTI Project Design Document Procedure"; Richard Brooksby; Ravenbrook Limited; 2000-10-05.
[Schreiber 2001-04-09] "TeamTrack API client update" (e-mail message); Royce Schreiber; TeamShare; 2001-04-09.
[Shaw 2001-04-16] "Notes on memory usage in the API" (e-mail message); Kelly Shaw; TeamShare; 2001-04-16.
[Shaw 2001-06-27] "Re: Advice needed: cases table id and name" (e-mail message); Kelly Shaw; TeamShare; 2001-06-27.
[Shaw 2001-06-28] "Re: Advice needed: cases table id and name" (e-mail message); Kelly Shaw; TeamShare; 2001-06-28.
[Shaw 2001-07-02] "Re: Nasty API bug" (e-mail message); Kelly Shaw; TeamShare; 2001-07-02.
[Shaw 2001-10-01] "Perforce Integration Issue with the Service Pack" (e-mail message); Kelly Shaw; TeamShare; 2001-10-01.
[Shaw 2001-12-19] "Migration plan for expiring licenses used to support P4DTI"; Kelly Shaw; TeamShare; 2001-12-19.
[Shaw 2002-02-05] "Re: TeamTrack API 3.5 compatibility" (e-mail message); Kelly Shaw; TeamShare; 2002-02-05.
[TeamShare 2000-01-20] "TeamTrack Database Schema (Database Version: 21)"; TeamShare; 2000-01-20.
[TeamShare 2001-04-09] "ReadRecordListWithWhere.C"; TeamShare; 2001-04-09.
[TeamShare 2001-05-02] "TeamTrack API Reference Guide"; TeamShare; 2001-05-02.
[TeamShare 2001-12-12] "TeamTrack API (for TeamTrack build 50207)"; TeamShare; 2000-12-12.
[van Rossum 1999-04-13] "Extending and Embedding the Python Interpreter (release 1.5.2)"; Guido van Rossum; 1999-04-13.

B. Document History

2000-08-08 GDR Created.
2000-08-29 GDR Moved to master/design/teamtrack/.
2000-08-30 GDR Renamed to master/design/python-teamtrack-interface/.
2000-09-07 GDR Added note about reference counts on values returned to Python. Changed XHTML identifiers for methods so that I don't have to renumber them. Split out error identifiers into their own sections. Added new cross-references. Sorted identifiers in the teamtrack module by name. Documented add_field method and field_type dictionary.
2000-09-15 GDR Documented transition and submit methods.
2000-10-05 RB Updated reference to design document procedure [RB 2000-10-05] to point to on-line document.
2001-03-02 RB Transferred copyright to Perforce under their license.
2001-06-26 GDR Added methods case_table_id and case_table_name.
2001-06-28 GDR Added reference to Kelly Shaw's advice for computing the case_table_id and case_table_name methods.
2001-07-03 GDR Renumbered sections. Added section on TeamTrack server versions. Explained how we support TeamTrack 4.5 and 5.0.
2002-01-10 GDR New dictionary feature indicates which features are supported. Method submit no longer takes a type argument and returns the record id, not the issue id. It defined iff the submit feature is supported.
2002-02-01 GDR Set out table of compatibility between TeamTrack API revisions and server version.
2002-03-14 NB Removed support for TeamTrack 5.02.
2003-06-02 NB Marked as obsolete.

This document is copyright © 2001 Perforce Software, Inc. All rights reserved.

Redistribution and use of this document in any form, with or without modification, is permitted provided that redistributions of this document retain the above copyright notice, this condition and the following disclaimer.

This document is provided by the copyright holders and contributors "as is" and any express or implied warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose are disclaimed. In no event shall the copyright holders and contributors be liable for any direct, indirect, incidental, special, exemplary, or consequential damages (including, but not limited to, procurement of substitute goods or services; loss of use, data, or profits; or business interruption) however caused and on any theory of liability, whether in contract, strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this document, even if advised of the possibility of such damage.

$Id: //info.ravenbrook.com/project/p4dti/version/2.4/design/python-teamtrack-interface/index.html#2 $

Ravenbrook / Projects / Perforce Defect Tracking Integration / Version 2.4 Product Sources / Design