<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Replicator interface to TeamTrack</title>
</head>
<body bgcolor="#FFFFFF" text="#000000" link="#000099" vlink="#660066" alink="#FF0000">
<div align="center">
<p>
<a href="http://www.ravenbrook.com/">Ravenbrook</a> /
<a href="http://www.ravenbrook.com/project/">Projects</a> /
<a href="http://www.ravenbrook.com/project/p4dti/">Perforce Defect Tracking Integration</a> /
<a href="../../index.html">Version 2.0 Product Sources</a> /
<a href="../index.html">Design</a>
</p>
<p><i><a href="http://www.ravenbrook.com/project/p4dti/">Perforce Defect Tracking Integration Project</a></i></p>
<hr />
<h1>Replicator interface to TeamTrack</h1>
<address>
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>,
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>,
2000-09-13
</address>
</div>
<h2><a id="section-1" name="section-1">1. Introduction</a></h2>
<p>This document describes the implementation and configuration of the
replicator interface to TeamTrack.</p>
<p><b>This document is obsolete.</b> The P4DTI no longer supports
integration with TeamTrack. This document is retained for reference
purposes, but will not be maintained.</p>
<p>The purpose of this document was to make it possible for people to
maintain the interface to TeamTrack.</p>
<p>This document will <b>not</b> be modified as the product is
developed.</p>
<p>The intended readership of this document is the product
developers. The intended readership of section 2 also includes experts
on the TeamTrack database schema.</p>
<p>This document is not confidential.</p>
<h2><a id="section-2" name="section-2">2. TeamTrack database queries</a></h2>
<p> This section lists the queries that the TeamTrack interface makes
into the TeamTrack database. All these queries are executed using the
<code>TSServer::ReadRecordListWithWhere()</code> method in the TeamShare
API, via the <a
href="../python-teamtrack-interface/index.html#server.query"><code>query()</code>
method</a> in the Python interface to TeamTrack [<a title="Python
interface to TeamTrack: design"
href="../python-teamtrack-interface/index.html">GDR
2000-08-08</a>]. </p>
<p> 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. </p>
<h3><a id="section-2.1" name="section-2.1">2.1. Straightforward queries</a></h3>
<ol>
<li><p> 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. </p>
<blockquote><code>SELECT * FROM TS_STATES WHERE TS_PROJECTID IN (SELECT
TS_ID FROM TS_WORKFLOWS WHERE TS_TABLEID =
<i>cases-table-id</i>)</code></blockquote></li>
<li><p> 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. </p>
<blockquote><code>SELECT * FROM TS_FIELDS WHERE TS_TABLEID =
<i>case-table-id</i> AND TS_STATUS = 0</code></blockquote></li>
<li><p> Get the selections for a field. </p>
<blockquote><code>SELECT * FROM TS_SELECTIONS WHERE TS_FLDID =
<i>n</i></code></blockquote></li>
<li><p> Get the user id corresponding to a login name. </p>
<blockquote><code>SELECT * FROM TS_USERS WHERE TS_LOGINID =
'<i>name</i>'</code></blockquote></li>
<li><p> Find the first change (if any) made on or after a given
date. </p>
<blockquote><code>SELECT * FROM TS_CHANGES WHERE TS_ID IN (SELECT
MIN(TS_ID) FROM TS_CHANGES WHERE TS_TIME >= <i>date</i>
</code></blockquote></li>
</ol>
<h3><a id="section-2.2" name="section-2.2">2.2. Queries using cursor emulation (1)</a></h3>
<p> This section lists queries that are implemented using cursor
emulation [<a title="Performance analysis of TeamShare API workarounds"
href="http://www.ravenbrook.com/project/p4dti/doc/2001-05-16/teamtrack-performance/#solution-4">GDR
2001-05-16, 3.4</a>] to work around capacity problems with the TeamShare
API (<a href="http://www.ravenbrook.com/project/p4dti/issue/job000277/">job000277</a>). </p>
<p> The cursor emulation divides a single query into a sequence of
queries with <code>WHERE</code> clauses as shown below. Each query gets
a chunk of records, from one to <i>n</i> records (here <i>n</i> = 20 for
illustration). See [<a title="Performance analysis of TeamShare API
workarounds"
href="http://www.ravenbrook.com/project/p4dti/doc/2001-05-16/teamtrack-performance/">GDR
2001-05-16</a>] for details. </p>
<blockquote><pre>
(<i>query</i>) AND TS_ID BETWEEN (SELECT MIN(TS_ID) FROM TS_CASES WHERE TS_ID > -1 AND (<i>query</i>))
AND (SELECT MIN(TS_ID) + 19 FROM TS_CASES WHERE TS_ID > -1 AND (<i>query</i>))
(<i>query</i>) AND TS_ID BETWEEN (SELECT MIN(TS_ID) FROM TS_CASES WHERE TS_ID > 36 AND (<i>query</i>))
AND (SELECT MIN(TS_ID) + 19 FROM TS_CASES WHERE TS_ID > 36 AND (<i>query</i>))
...</pre></blockquote>
<ol>
<li><p> 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. </p>
<blockquote><code>SELECT * FROM <i>case-table</i> WHERE TS_P4DTI_RID =
'<i>replicator-id</i>' OR ((TS_P4DTI_RID = '' OR TS_P4DTI_RID IS NULL)
AND TS_LASTMODIFIEDDATE >= <i>date</i>) </code></blockquote></li>
<li><p> Get all the changes to cases since change <i>n</i>, excepting
changes made by the replicator. </p>
<blockquote><code>SELECT * FROM TS_CHANGES WHERE TS_TABLEID =
<i>case-table-id</i> AND TS_ID > <i>n</i> AND TS_REALUSERID <>
<i>replicator-userid</i> AND TS_USERID <>
<i>replicator-userid</i></code></blockquote></li>
</ol>
<h3><a id="section-2.3" name="section-2.3">2.3. Query using cursor emulation (2)</a></h3>
<p> To get cases numbered <i>n1</i>, <i>n2</i>, ..., 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. </p>
<blockquote><code>SELECT * FROM <i>case-table</i> WHERE (TS_P4DTI_RID =
'<i>replicator-id</i>' OR TS_P4DTI_RID = '' OR TS_P4DTI_RID IS NULL) AND
TS_ID IN (<i>n1</i>, <i>n2</i>, ...)</code></blockquote>
<h3><a id="section-2.4" name="section-2.4">2.4. Queries on the P4DTI schema extensions</a></h3>
<p> This section lists queries on the P4DTI schema extensions in the
<code>TS_VCACTIONS</code> table; see [<a title="TeamTrack database
schema extensions for integration with Perforce"
href="../teamtrack-p4dti-schema/index.html">GDR 2000-09-04</a>]. </p>
<ol>
<li><p> Get the filespecs associated with case <i>n</i>. </p>
<blockquote><code>SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 1 AND
TS_RECID = <i>n</i> </code></blockquote></li>
<li><p> Get the fixes associated with case <i>n</i>. </p>
<blockquote><code>SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 2 AND
TS_RECID = <i>n</i> </code></blockquote></li>
<li><p> Get the last change record that was replicated by a given
replicator. </p>
<blockquote><code>SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 4 AND
TS_CHAR1 = '<i>replicator-id</i>' AND TS_CHAR2 =
'LAST_CHANGE'</code></blockquote></li>
<li><p> Get all the configuration parameters for a given
replicator. </p>
<blockquote><code>SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 4 AND
TS_CHAR1 = '<i>replicator-id</i>' </code></blockquote></li>
<li><p> Get a changelist description. </p>
<blockquote><code>SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 3 AND
TS_CHAR1 = '<i>replicator-id</i>' AND TS_INFO1 = <i>n</i>
</code></blockquote></li>
</ol>
<h2><a id="section-A" name="section-A">A. References</a></h2>
<table>
<tr valign="top">
<td>[<a id="ref-GDR-2000-08-08" name="ref-GDR-2000-08-08" href="../python-teamtrack-interface/index.html">GDR 2000-08-08</a>]</td>
<td>
"Python interface to TeamTrack: design";
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-08-08.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-GDR-2000-09-04" name="ref-GDR-2000-09-04" href="../teamtrack-p4dti-schema/index.html">GDR 2000-09-04</a>]</td>
<td>
"TeamTrack database schema extensions for integration with Perforce";
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-09-04.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-GDR-2001-05-16" name="ref-GDR-2001-05-16" href="http://www.ravenbrook.com/project/p4dti/doc/2001-05-16/teamtrack-performance/">GDR 2001-05-16</a>]</td>
<td>
"Performance analysis of TeamShare API workarounds";
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2001-05-16.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-RB-2000-08-30" name="ref-RB-2000-08-30" href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-30/design-document-structure/">RB 2000-08-30</a>]</td>
<td>
"Design document structure" (e-mail message);
<a href="mailto:rb@ravenbrook.com">Richard Brooksby</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-08-30.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-RB-2000-10-05" name="ref-RB-2000-10-05" href="http://www.ravenbrook.com/project/p4dti/procedure/design-document/">RB 2000-10-05</a>]</td>
<td>
"P4DTI Project Design Document Procedure";
<a href="mailto:rb@ravenbrook.com">Richard Brooksby</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-10-05.
</td>
</tr>
</table>
<h2><a id="section-B" name="section-B">B. Document History</a></h2>
<table>
<tr valign="top">
<td>2000-09-13</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Created based on [<a href="http://info.ravenbrook.com/mail/2000/08/18/18-58-50/0.txt">RB 2000-08-18</a>] and [<a href="http:/project/p4dti/doc/2000-08-30/design-document-structure/">RB 2000-08-30</a>].</td>
</tr>
<tr valign="top">
<td>2000-10-05</td>
<td><a href="mailto:rb@ravenbrook.com">RB</a></td>
<td>Updated reference to design document procedure [<a href="http://www.ravenbrook.com/project/p4dti/procedure/design-document/">RB 2000-10-05</a>] to point to on-line document. Added placeholder comment.</td>
</tr>
<tr valign="top">
<td> 2001-03-02 </td>
<td> <a href="mailto:rb@ravenbrook.com">RB</a> </td>
<td> Transferred copyright to Perforce under their license. </td>
</tr>
<tr valign="top">
<td>2001-06-28</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Added TeamTrack database queries.</td>
</tr>
<tr valign="top">
<td>2001-07-02</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Changed cursor queries to reflect bug fix.</td>
</tr>
<tr valign="top">
<td>2003-06-02</td>
<td><a href="mailto:nb@ravenbrook.com">NB</a></td>
<td>Marked as obsolete.</td>
</tr>
</table>
<hr />
<p> <small>This document is copyright © 2001 Perforce Software, Inc. All rights reserved.</small> </p>
<p> <small>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.</small> </p>
<p> <small> <strong> 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. </strong> </small> </p>
<div align="center">
<p><code>$Id: //info.ravenbrook.com/project/p4dti/version/2.0/design/replicator-teamtrack-interface/index.html#3 $</code></p>
<p>
<a href="http://www.ravenbrook.com/">Ravenbrook</a> /
<a href="http://www.ravenbrook.com/project/">Projects</a> /
<a href="http://www.ravenbrook.com/project/p4dti/">Perforce Defect Tracking Integration</a> /
<a href="../../index.html">Version 2.0 Product Sources</a> /
<a href="../index.html">Design</a>
</p>
</div>
</body>
</html>