Replicator interface to TeamTrack

1. Introduction

This document describes the implementation and configuration of the replicator interface to TeamTrack.

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 interface to TeamTrack.

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

The intended readership of this document is the product developers. The intended readership of section 2 also includes experts on the TeamTrack database schema.

This document is not confidential.

2. TeamTrack database queries

This section lists the queries that the TeamTrack interface makes into the TeamTrack database. All these queries are executed using the TSServer::ReadRecordListWithWhere() method in the TeamShare API, via the query() method in the Python interface to TeamTrack [GDR 2000-08-08].

The purpose of this section is to make it possible for experts in the TeamTrack database schema at TeamShare to review this interface, discover defects, and provide early warning about changes in the TeamTrack schema that will affect this interface.

2.1. Straightforward queries

1. Select the states belong to cases workflows (but not states belonging to incidents workflows). The names of the states will be used to make the state field in Perforce.

   SELECT * FROM TS_STATES WHERE TS_PROJECTID IN (SELECT TS_ID FROM TS_WORKFLOWS WHERE TS_TABLEID = cases-table-id)

2. Get the fields in the cases table (but not deleted fields). The field names, types, attributes, and descriptions will be used to make corresponding fields in Perforce.

   SELECT * FROM TS_FIELDS WHERE TS_TABLEID = case-table-id AND TS_STATUS = 0

3. Get the selections for a field.

   SELECT * FROM TS_SELECTIONS WHERE TS_FLDID = n

4. Get the user id corresponding to a login name.

   SELECT * FROM TS_USERS WHERE TS_LOGINID = 'name'

5. Find the first change (if any) made on or after a given date.

   SELECT * FROM TS_CHANGES WHERE TS_ID IN (SELECT MIN(TS_ID) FROM TS_CHANGES WHERE TS_TIME >= date

2.2. Queries using cursor emulation (1)

This section lists queries that are implemented using cursor emulation [GDR 2001-05-16, 3.4] to work around capacity problems with the TeamShare API (job000277).

The cursor emulation divides a single query into a sequence of queries with WHERE clauses as shown below. Each query gets a chunk of records, from one to n records (here n = 20 for illustration). See [GDR 2001-05-16] for details.

(query) AND TS_ID BETWEEN (SELECT MIN(TS_ID)      FROM TS_CASES WHERE TS_ID > -1 AND (query))
                      AND (SELECT MIN(TS_ID) + 19 FROM TS_CASES WHERE TS_ID > -1 AND (query))
(query) AND TS_ID BETWEEN (SELECT MIN(TS_ID)      FROM TS_CASES WHERE TS_ID > 36 AND (query))
                      AND (SELECT MIN(TS_ID) + 19 FROM TS_CASES WHERE TS_ID > 36 AND (query))
...

1. Get all the cases which are either (a) replicated by a given replicator or (b) not replicated by any replicator and modified since a given date.

   SELECT * FROM case-table WHERE TS_P4DTI_RID = 'replicator-id' OR ((TS_P4DTI_RID = '' OR TS_P4DTI_RID IS NULL) AND TS_LASTMODIFIEDDATE >= date)

2. Get all the changes to cases since change n, excepting changes made by the replicator.

   SELECT * FROM TS_CHANGES WHERE TS_TABLEID = case-table-id AND TS_ID > n AND TS_REALUSERID <> replicator-userid AND TS_USERID <> replicator-userid

2.3. Query using cursor emulation (2)

To get cases numbered n1, n2, ..., but excluding cases replicated by some other replicator, we divide the list into groups of up to 20 case ids and issue queries of the following form.

SELECT * FROM case-table WHERE (TS_P4DTI_RID = 'replicator-id' OR TS_P4DTI_RID = '' OR TS_P4DTI_RID IS NULL) AND TS_ID IN (n1, n2, ...)

2.4. Queries on the P4DTI schema extensions

This section lists queries on the P4DTI schema extensions in the TS_VCACTIONS table; see [GDR 2000-09-04].

1. Get the filespecs associated with case n.

   SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 1 AND TS_RECID = n

2. Get the fixes associated with case n.

   SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 2 AND TS_RECID = n

3. Get the last change record that was replicated by a given replicator.

   SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 4 AND TS_CHAR1 = 'replicator-id' AND TS_CHAR2 = 'LAST_CHANGE'

4. Get all the configuration parameters for a given replicator.

   SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 4 AND TS_CHAR1 = 'replicator-id'

5. Get a changelist description.

   SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 3 AND TS_CHAR1 = 'replicator-id' AND TS_INFO1 = n

A. References

B. Document History

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.