<?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>TeamTrack database schema extensions for integration with Perforce</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>TeamTrack database schema extensions for integration with Perforce</h1>
<address>
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>,
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>,
2000-09-04
</address>
</div>
<h2><a id="section-1" name="section-1">1. Introduction</a></h2>
<p>This document defines TeamTrack database schema extensions for
integrating with Perforce.</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 sure that TeamTrack and the
P4DTI replicator are consistent in their treatment of these
relations.</p>
<p>This document will <b>not</b> be modified as the product is developed.</p>
<p>The intended readership was developers at TeamShare who are working on
the TeamTrack interfaces to these relations, and developers at
Ravenbrook working on the replication of these relations.</p>
<p>This document is not confidential.</p>
<p>This document extends the TeamTrack schemas for database version 21
[<a
href="http://www.ravenbrook.com/project/p4dti/import/2000-01-20/teamtrack-schema/TTschema21.pdf">TeamShare
2000-01-20</a>] and database version 514 [<a title="TeamTrack Database
Schema (Database Version: 514)"
href="http://www.ravenbrook.com/project/p4dti/import/2001-06-19/teamtrack-5034/api/schema.htm">TeamShare
2001-06-19</a>].</p>
<p>A sample TeamTrack database containing data conforming to this schema
is available [<a
href="http://www.ravenbrook.com/project/p4dti/doc/2000-09-06/teamtrack-sample-data/">GDR
2000-09-06b</a>].</p>
<h2><a id="section-2" name="section-2">2. Notes on the schema extensions</a></h2>
<h3><a id="section-2.1" name="section-2.1">2.1. Using the VCACTIONS table</a></h3>
<p>Three replicated relations are stored in TeamTrack's
<code>TS_VCACTIONS</code> table, since TeamTrack 4.0 does not have the
ability to add new tables [<a href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-18/teamshare-meeting/#section-3">GDR
2000-08-18, 3</a>].</p>
<p>To store multiple relations in the <code>TS_VCACTIONS</code> table, I
use the <code>TS_TYPE</code> field to indicate which relation each
record belongs to. As of TeamTrack 4.0.3, the <code>TS_TYPE</code>
field is not used, and is 0 for each record. I have added the following
record types:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Record type</th>
<th>Record contains</th>
</tr>
<tr valign="top">
<td align="right">1</td>
<td>Case/filespec relation (see <a href="#section-3.2">section
3.2</a>).</td>
</tr>
<tr valign="top">
<td align="right">2</td>
<td>Case/change relation (see <a href="#section-3.3">section
3.3</a>).</td>
</tr>
<tr valign="top">
<td align="right">3</td>
<td>Change description (see <a href="#section-3.4">section
3.4</a>).</td>
</tr>
<tr valign="top">
<td align="right">4</td>
<td>Replicator configuration (see <a href="#section-3.5">section
3.5</a>).</td>
</tr>
</table>
<p>TeamShare must agree these meanings for the type field or propose
appropriate types for these relations. There may be a need to add new
types of relation in the future, so the set of types available to the
integration needs to be extensible.</p>
<p>TeamTrack must check the type field of each record when handling the
<code>TS_VCACTIONS</code> table. When presenting version control action
records, it should select only records with type 0. (It is probably
harmless for it to present all the records, but it may be confusing to
the user.)</p>
<h3><a id="section-2.2" name="section-2.2">2.2. Storing arbitrary data</a></h3>
<p>In some cases there are not enough string fields in the
<code>TS_VCACTIONS</code> to store the data that needs to be replicated.
In other cases the relation in Perforce has multiple items of data that
can be arbitrarily long, but the <code>TS_VCACTIONS</code> has only a
single variable-length text field.</p>
<p>To work around these limitations, and to allow for extensions to
these relations in the future, I use the <code>TS_FILENAME</code> field
to store (potentially) arbitrarily many items of data, by representing
the data as a string of key-value pairs, like this:</p>
<blockquote><code>
{ 'key1' : 'value1', 'key2' : 'value2', ... }
</code></blockquote>
<p>This is the syntax that the Python programming language uses to
parse and print its dictionary data structures.</p>
<p>The serialised dictionary consists of an open brace, a list of
key-value pairs separated by commas, and a close brace. Keys are
separated from values by colons. Each key is a string; each value is
either a string or a number. Strings may be in single or double quotes.
Strings may include escape sequences introduced by backslashes.
Whitespace may appear anywhere outside a string and is not significant.
Keys may appear in any order.</p>
<p>The details of Python's dictionary syntax are given in [<a
href="http://www.python.org/doc/current/ref/dict.html">van Rossum
2000-03-22, 5.2.5</a>], and of Python's string syntax in [<a
href="http://www.python.org/doc/current/ref/strings.html">van Rossum
2000-03-22, 2.4.1</a>]. I will ensure that dictionary keys that appear
in the TeamTrack database are always strings, that dictionary values are
either strings or numbers, and that strings always appear in
<code>shortstring</code> syntax.</p>
<p>For example, the following string encodes the field "name"
with value "Joe" and the field "userid" with value 1123:</p>
<blockquote><code>
{ 'name': 'Joe', 'userid': 1123 }
</code></blockquote>
<p>Because these encoded fields cannot be indexed by the database or
queried in SQL, I have tried to ensure that the fields encoded in this
format are the fields that are least likely to be queried.</p>
<h3><a id="section-2.3" name="section-2.3">2.3. Dealing with multiple Perforce servers</a></h3>
<p>In order to meet <a href="http://www.ravenbrook.com/project/p4dti/req/#req-96">requirement
96</a> (the integration copes with multiple Perforce servers), the data
format is designed to be extended to meet this requirement.</p>
<p>The schema extensions therefore specify for each record a
<i>replicator identifier</i> which identifies the replicator which is
handling replication for that record, and a <i>Perforce server
identifier</i>, which is a short identifier for the Perforce server that
the record is replicated with. Each of these identifiers must be 255
characters or less, to fit into the fixed-width text fields in the
<code>TS_VCACTIONS</code> table.</p>
<p>Since <a href="http://www.ravenbrook.com/project/p4dti/req/#req-96">requirement 96</a> is not
critical, it is likely that the first release of the integration will
only support a single Perforce server for each TeamTrack server.
However, it is important to develop the infrastructure to support this
requirement in future releases.</p>
<p>Replicator identifiers are not intended for presentation to the user.
They are restricted to 32 characters, and must conform to the syntax of
an identifier in C or C++ (letters, numbers or underscores only, must
start with a letter or an underscore).</p>
<p>Perforce server identifiers may be presented to the user (when that
happens it will be necessary for the replicator(s) to provide more
information about the Perforce servers so that appropriate information
can be provided to the user; this may be provided as a new relation).
They are restricted to 32 character, and must conform to the syntax of
an identifier in C or C++.</p>
<h3><a id="section-2.4" name="section-2.4">2.4. Presentation and modification</a></h3>
<p>The tables in <a href="#section-3">section 3</a> below have columns
labelled "P?" and "M?".</p>
<p>The "P?" column concerns whether it makes sense to present the
information in the field to the TeamTrack user: "Y" if it does, "N" if
the information should be hidden.</p>
<p>The "M?" column indicates who is allowed to modify the information in
the field. The column contains "-" if the field should never be
modified (after initialization); "R" if only the replicator is allowed
to modify it; "T" if both the replicator and TeamTrack are allowed to
modify it; and "U" if the replicator, TeamTrack, and the TeamTrack user
are allowed to modify it.</p>
<h3><a id="section-2.5" name="section-2.5">2.5. Initialization</a></h3>
<p>The extra fields in the <code>TS_CASES</code> table will be created
by the replicator the first time it runs, using the
<code>TSServer::AddField</code> method in the TeamShare API. When these
fields have been added, they will be filled in by the replicator. The
replicator will have a function that determines for each case whether it
should be replicated by that replicator; if so, then the fields for that
case will be set accordingly, otherwise they will be left blank.</p>
<p>This document will eventually explain:</p>
<ul>
<li><p>How these fields get their initial values.</p></li>
<li><p>How to tell if the integration is running.</p></li>
</ul>
<h3><a id="section-2.6" name="section-2.6">2.6. Indexes</a></h3>
<p>The <code>TS_CASES</code> table will be queried on the
<code>TS_P4DTI_RID</code> and <code>TS_P4DTI_REPLICATED</code>
fields.</p>
<p>The <code>TS_VCACTIONS</code> table will be queried on the
<code>TS_TYPE</code>, <code>TS_CHAR1</code>, <code>TS_RECID</code>,
<code>TS_TIME1</code> and <code>TS_INFO1</code> fields.</p>
<p>It would be sensible to build indexes on all these fields. TeamShare
can do this for the <code>TS_VCACTIONS</code> table, but not for the
<code>TS_CASES</code> table, since the extra fields won't be added until
the replicator starts running (see <a href="#section-2.5">section
2.5</a>).</p>
<p>It would be nice to have a method in the TeamShare API for creating
an index.</p>
<h2><a id="section-3" name="section-3">3. The schema extensions</a></h2>
<h3><a id="section-3.1" name="section-3.1">3.1. Extensions to the cases relation</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Column name</th>
<th>Datatype</th>
<th>P?</th>
<th>M?</th>
<th>Description</th>
</tr>
<tr valign="top">
<td><code>TS_P4DTI_RID</code></td>
<td><code><i>char(32)</i></code></td>
<td>N</td>
<td>R</td>
<td>Identifier of the replicator that handles replication for this
case (see <a href="#section-2.3">section 2.3</a>); empty if the case
is not replicated.</td>
</tr>
<tr valign="top">
<td><code>TS_P4DTI_SID</code></td>
<td><code><i>char(32)</i></code></td>
<td>Y</td>
<td>R</td>
<td>Identifier of the Perforce server that this case is replicated
with (see <a href="#section-2.3">section 2.3</a>); empty if the case
is not replicated.</td>
</tr>
<tr valign="top">
<td><code>TS_P4DTI_JOBNAME</code></td>
<td><code><i>varchar(1024)</i></code></td>
<td>Y</td>
<td>R</td>
<td>The name of the job in the Perforce server which this case
corresponds to; empty if the case is not replicated. (Perforce says,
"Identifiers can't be longer than 1024 characters" if you try to
create a job with a longer name.)</td>
</tr>
</table>
<h3><a id="section-3.2" name="section-3.2">3.2. Case/filespec association relation</a></h3>
<p>The case/filespec relation is stored in the <code>TS_VCACTIONS</code>
table as detailed in the table below. See [<a
href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-18/teamshare-meeting/#section-3.1.2">GDR
2000-08-18, 3.1.2</a>] for design decisions relating to this
relation.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Column name</th>
<th>Datatype</th>
<th>P?</th>
<th>M?</th>
<th>Description</th>
</tr>
<tr valign="top">
<td><code>TS_TYPE</code></td>
<td><code><i>int</i></code></td>
<td>N</td>
<td>-</td>
<td>Type 1 indicates a case/filespec association (see <a
href="#section-2.1">section 2.1</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_CHAR1</code></td>
<td><code><i>varchar(255)</i></code></td>
<td>N</td>
<td>-</td>
<td>Identifier of the replicator which is handling replication of this
case/filespec association (see <a href="#section-2.3">section
2.3</a>). This must be the same as the <code>TS_P4DTI_RID</code>
field for the case given in the <code>TS_RECID</code> field of this
record (see <a href="#section-3.1">section 3.1</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_CHAR2</code></td>
<td><code><i>varchar(255)</i></code></td>
<td>N</td>
<td>-</td>
<td>Identifier for the Perforce server from which this case/filespec
association is replicated (see <a href="#section-2.3">section
2.3</a>). This must be the same as the <code>TS_P4DTI_SID</code>
field for the case given in the <code>TS_RECID</code> field of this
record (see <a href="#section-3.1">section 3.1</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_TABLEID</code></td>
<td><code><i>int</i></code></td>
<td>N</td>
<td>-</td>
<td>The table id of the <code>TS_CASES</code> table.</td>
</tr>
<tr valign="top">
<td><code>TS_RECID</code></td>
<td><code><i>int</i></code></td>
<td>Y</td>
<td>U</td>
<td>The case (that is, the value of the <code>TS_ID</code> field of
the record in the <code>TS_CASES</code> table that is associated with
the filespec).</td>
</tr>
<tr valign="top">
<td><code>TS_TIME1</code></td>
<td><code><i>int</i></code></td>
<td>N</td>
<td>T</td>
<td>The date and time that this case/filespec association record was
last modified, or 0 if the record is new and was added by the
replicator.</td>
</tr>
<tr valign="top">
<td><code>TS_FILENAME</code></td>
<td><code><i>text</i></code></td>
<td>Y</td>
<td>U</td>
<td><p>A structure (see <a href="#section-2.2">section 2.2</a>).</p>
<p>The value for the "<code>filespec</code>" key is the filespec that
is associated with the case (a string).</p></td>
</tr>
</table>
<p>Notes:</p>
<ol>
<li><p>The case/filespec relation is many-to-many. A case may be
associated with many filespecs, and a filespec may be associated with
many cases.</p></li>
<li><p>In a configuration with multiple Perforce servers, there may be
multiple filespecs with the same name. These are distinguished by the
Perforce server id. A given case will be associated with filespecs from
only one Perforce server.</p></li>
<li><p>As of release 2000.1, Perforce does not support an arbitrary
relation between jobs and filespecs (though jobs and filespecs may be
related indirectly via the fix and change relations). We need to
provide basic support for this to meet <a
href="http://www.ravenbrook.com/project/p4dti/req/#req-39">requirement 39</a>. We decided to
represent this relation in Perforce as a list of filespecs in a text
field in the job specification [<a
href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-08/perforce-meeting/#section-4.5">RB
2000-08-08, 4.5</a>]. We can provide tools that operate on this field,
for example to check out for edit all the files associated with a
job.</p>
<p>One use case we have in mind is that a defect has been reported, and
passed to an analyst, who investigates and discovers that it is present
in releases 3.7 and 3.8. The analyst can record this formally in the
system by associating the defect with the filespecs
<code>//depot/product/foo/release/3.7/...</code> and
<code>//depot/product/foo/release/3.8/...</code> Later analysis can add
more specific filespecs as the defect is narrowed down to a particular
module or source file. Then when the defect is passed to a developer
they can easily identify (and access, using the tools) the files they
need to make changes to.</p></li>
</ol>
<h3><a id="section-3.3" name="section-3.3">3.3. Fixes relation</a></h3>
<p>The Perforce fixes relation is stored in the
<code>TS_VCACTIONS</code> table as detailed in the table below. See [<a
href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-18/teamshare-meeting/#section-3.1.3">GDR
2000-08-18, 3.1.3</a>] for the design decisions relating to this
relation.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Column name</th>
<th>Datatype</th>
<th>P?</th>
<th>M?</th>
<th>Description</th>
</tr>
<tr valign="top">
<td><code>TS_TYPE</code></td>
<td><code><i>int</i></code></td>
<td>N</td>
<td>-</td>
<td>Type 2 indicates a fix record (see <a href="#section-2.1">section
2.1</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_CHAR1</code></td>
<td><code><i>varchar(255)</i></code></td>
<td>N</td>
<td>-</td>
<td>Identifier of the replicator which is handling replication of this
fix record (see <a href="#section-2.3">section 2.3</a>). This must be
the same as the <code>TS_P4DTI_RID</code> field for the case given in
the <code>TS_RECID</code> field of this record (see <a
href="#section-3.1">section 3.1</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_CHAR2</code></td>
<td><code><i>varchar(255)</i></code></td>
<td>N</td>
<td>-</td>
<td>Identifier of the Perforce server from which this fix record is
replicated (see <a href="#section-2.3">section 2.3</a>). This must be
the same as the <code>TS_P4DTI_SID</code> field for the case given in
the <code>TS_RECID</code> field of this record (see <a
href="#section-3.1">section 3.1</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_TABLEID</code></td>
<td><code><i>int</i></code></td>
<td>N</td>
<td>-</td>
<td>The table id of the <code>TS_CASES</code> table.</td>
</tr>
<tr valign="top">
<td><code>TS_RECID</code></td>
<td><code><i>int</i></code></td>
<td>Y</td>
<td>U</td>
<td>The case (that is, the value of the <code>TS_ID</code> field of
the record in the <code>TS_CASES</code> table that corresponds to the
job in this fix record).</td>
</tr>
<tr valign="top">
<td><code>TS_INFO1</code></td>
<td><code><i>int</i></code></td>
<td>Y</td>
<td>U</td>
<td>The number of the change in this fix record.</td>
</tr>
<tr valign="top">
<td><code>TS_TIME1</code></td>
<td><code><i>int</i></code></td>
<td>Y</td>
<td>T</td>
<td>The date and time that this fix record was last modified, or 0 if
the record is new and was added by the replicator.</td>
</tr>
<tr valign="top">
<td><code>TS_AUTHOR1</code></td>
<td><code><i>int</i></code></td>
<td>Y</td>
<td>T</td>
<td>The user who last modified this fix record (that is, the value of
the <code>TS_ID</code> field of the record in the
<code>TS_USERS</code> table of that user).</td>
</tr>
<tr valign="top">
<td><code>TS_FILENAME</code></td>
<td><code><i>text</i></code></td>
<td>Y</td>
<td>U</td>
<td><p>A structure (see <a href="#section-2.2">section 2.2</a>).</p>
<p>The value for the "<code>status</code>" key is the status keyword
in this fix record (a string).</p>
<p>The value for the "<code>client</code>" key is the name of the
Perforce client on which this fix record was last modified (a string),
or the empty string if this fix record was modified in TeamTrack.</p></td>
</tr>
</table>
<p>Notes:</p>
<ol>
<li><p>The fixes relation is many-to-many. A case may be associated with
many changes, and a change may be associated with many cases.</p></li>
<li><p>In a configuration with multiple Perforce servers, there may be
multiple changes with the same number. These are distinguished by the
Perforce server id. A given case will be associated with changes from
only one Perforce server.</p></li>
<li><p>The fixes relation in Perforce is so-called for historical
reasons (there used to be only one meaning for a fix record: that the
change fixed the job).</p></li>
<li><p>The TeamTrack user should be able to:</p>
<ul>
<li><p>Change the status keyword (for example, it is discovered that
the change doesn't fix the bug after all, it is only a partial fix, or
a fix that works only in some releases).</p></li>
<li><p>Delete a fix record (for example, the fix record was added by
mistake).</p></li>
<li><p>Add a fix record (for example, it is discovered at the QA stage
that a change has fixed defect 196 as well as defect 145).</p></li>
</ul>
<p>I don't see a requirement to allow users to edit the change number in
a fix record. (Though they can achieve the same effect by deleting the
old fix record and adding a new one with a different change
number.)</p></li>
</ol>
<h3><a id="section-3.4" name="section-3.4">3.4. Change descriptions</a></h3>
<p>Perforce change descriptions are stored in the
<code>TS_VCACTIONS</code> table as detailed in the table below. See [<a
href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-18/teamshare-meeting/#section-3.1.4">GDR
2000-08-18, 3.1.4</a>] for the design decisions relating to this
relation.</p>
<p>Note that change descriptions cannot be created or modified in
TeamTrack. They are replicated from Perforce to TeamTrack only.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Column name</th>
<th>Datatype</th>
<th>P?</th>
<th>M?</th>
<th>Description</th>
</tr>
<tr valign="top">
<td><code>TS_TYPE</code></td>
<td><code><i>int</i></code></td>
<td>N</td>
<td>-</td>
<td>Type 3 indicates a change description (see <a
href="#section-2.1">section 2.1</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_CHAR1</code></td>
<td><code><i>varchar(255)</i></code></td>
<td>N</td>
<td>-</td>
<td>Identifier of the replicator which is handling replication of this
change (see <a href="#section-2.3">section 2.3</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_CHAR2</code></td>
<td><code><i>varchar(255)</i></code></td>
<td>N</td>
<td>-</td>
<td>Identifier of the Perforce server from which this change is
replicated (see <a href="#section-2.3">section 2.3</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_INFO1</code></td>
<td><code><i>int</i></code></td>
<td>Y</td>
<td>-</td>
<td>The change number.</td>
</tr>
<tr valign="top">
<td><code>TS_AUTHOR1</code></td>
<td><code><i>int</i></code></td>
<td>Y</td>
<td>R</td>
<td>The value of the <code>TS_ID</code> field of the record in the
<code>TS_USERS</code> table of the TeamTrack user who corresponds to
the Perforce user who created the change.</td>
</tr>
<tr valign="top">
<td><code>TS_INFO2</code></td>
<td><code><i>int</i></code></td>
<td>Y</td>
<td>R</td>
<td>A flags field. Bit 0 (the least significant bit) represents the
change status: 0 if the change is pending, or 1 if the change has been
submitted. The other bits in the field are reserved for future
expansions.</td>
</tr>
<tr valign="top">
<td><code>TS_FILENAME</code></td>
<td><code><i>text</i></code></td>
<td>Y</td>
<td>R</td>
<td><p>A structure (see <a href="#section-2.2">section 2.2</a>).</p>
<p>The value for the "<code>description</code>" key is the change
comment (a string).</p>
<p>The value for the "<code>client</code>" key is the name of the
Perforce client on which the change was created.</p></td>
</tr>
</table>
<p>Notes:</p>
<ol>
<li><p>In a configuration with multiple Perforce servers, there may be
multiple changes with the same number. These are distinguished by the
Perforce server id.</p></li>
</ol>
<h3><a id="section-3.5" name="section-3.5">3.5. Replicator configuration</a></h3>
<p>Replicator configuration parameters are stored in the
<code>TS_VCACTIONS</code> table as detailed in the table below.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Column name</th>
<th>Datatype</th>
<th>P?</th>
<th>M?</th>
<th>Description</th>
</tr>
<tr valign="top">
<td><code>TS_TYPE</code></td>
<td><code><i>int</i></code></td>
<td>N</td>
<td>-</td>
<td>Type 4 indicates a replicator configuration parameter (see <a
href="#section-2.1">section 2.1</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_CHAR1</code></td>
<td><code><i>varchar(255)</i></code></td>
<td>N</td>
<td>-</td>
<td>Identifier of the replicator to which this configuration paramater
applies (see <a href="#section-2.3">section 2.3</a>).</td>
</tr>
<tr valign="top">
<td><code>TS_CHAR2</code></td>
<td><code><i>varchar(255)</i></code></td>
<td>N</td>
<td>-</td>
<td>Name of the configuration parameter.</td>
</tr>
</table>
<p>Defined configuration parameters are as follows:</p>
<table>
<tr valign="top" align="left">
<th>Parameter name</th>
<th>Field containing value</th>
<th>Meaning of value</th>
</tr>
<tr valign="top">
<td><code>LAST_CHANGE</code></td>
<td><code>TS_INFO1</code></td>
<td>The last record in the <code>TS_CHANGES</code> table that the
replicator has dealt with.</td>
</tr>
<tr valign="top">
<td><code>SERVER</code></td>
<td><code>TS_FILENAME</code></td>
<td><p>A structure (see <a href="#section-2.2">section 2.2</a>).</p>
<p>The value for the <code>sid</code> key is the server identifier of
a Perforce server that the replicator replicates to.</p>
<p>The value for the <code>description</code> key is a short
human-readable description of the Perforce server.</p>
<p>When a replicator replicates to several Perforce servers, there
will be several entries with this key in the configuration for that
replicator.</p></td>
</tr>
<tr valign="top">
<td><code>STATUS_VALUES</code></td>
<td><code>TS_FILENAME</code></td>
<td><p>A structure (see <a href="#section-2.2">section 2.2</a>).</p>
<p>The value for the <code>sid</code> key is the server identifier of
a Perforce server that the replicator replicates to.</p>
<p>The value for the <code>description</code> key is a list of
Perforce job status keywords, replicated from the Perforce jobspec.
The keywords are separated by slashes. For example,
<code>open/suspended/closed</code> is the default list of job status
keywords.</p></td>
</tr>
<tr valign="top">
<td><code>CHANGELIST_URL</code></td>
<td><code>TS_FILENAME</code></td>
<td><p>A structure (see <a href="#section-2.2">section 2.2</a>).</p>
<p>The value for the <code>sid</code> key is the server identifier of
a Perforce server that the replicator replicates to.</p>
<p>The value for the <code>description</code> key is a format string
which can be used to build a URL that provides details of a Perforce
changelist. The string has one <code>%d</code> format specifier for
which the change number should be substituted. TeamTrack should make
the change number for an associated change to a case that is
replicated to the specified Perforce server into a link to this
URL.</p>
<p>If there is no <code>CHANGELIST_URL</code> parameter for a given
replicator identifier and Perforce server identifier, then TeamTrack
should not make the change number into a link.</p> </td>
</tr>
<tr valign="top">
<td><code>JOB_URL</code></td>
<td><code>TS_FILENAME</code></td>
<td><p>A structure (see <a href="#section-2.2">section 2.2</a>).</p>
<p>The value for the <code>sid</code> key is the server identifier of
a Perforce server that the replicator replicates to.</p>
<p>The value for the <code>description</code> key is a format string
which can be used to build a URL that provides details of a Perforce
job. The string has one <code>%s</code> format specifier for which
the jobname should be substituted. TeamTrack should make the
jobname for the job corresponding to a case that is replicated to
the specified Perforce server into a link to this URL.</p>
<p>If there is no <code>JOB_URL</code> parameter for a given
replicator identifier and Perforce server identifier, then TeamTrack
should not make the jobname into a link.</p> </td>
</tr>
</table>
<p>Note that a single replicator may have several <code>SERVER</code>
and several <code>STATUS_VALUES</code> configuration parameters, one for
each Perforce server it replicates to. TeamTrack must be sure to pick
the right one: for example, if TeamTrack need to describe the Perforce
server that a case is replicated to, it must look at the
<code>SERVER</code> paramater whose <code>TS_CHAR1</code> field matches
the <code>TS_P4DTI_RID</code> field of the case, and whose value for the
<code>sid</code> key in the <code>TS_FILENAME</code> field matches the
<code>RS_P4DTI_SID</code> field of the case. A similar selection must
be made among the possible <code>STATUS_VALUES</code> parameters when
listing the possible statuses for a fix.</p>
<h2><a id="section-4" name="section-4">4. Example queries</a></h2>
<p>To select all the cases which are to be replicated by the replicator
with identifier <code>case</code> and which are not up-to-date with
Perforce:</p>
<blockquote><code>
SELECT * FROM TS_CASES WHERE TS_P4DTI_RID = 'case' AND TS_LASTMODIFIEDDATE > TS_P4DTI_REPLICATED
</code></blockquote>
<p>To get all the change descriptions:</p>
<blockquote><code>
SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 3
</code></blockquote>
<p>To get the change numbers for the fixes for case 20:</p>
<blockquote><code>
SELECT TS_INFO1 FROM TS_VCACTIONS WHERE TS_TYPE = 2 AND TS_RECID = 20
</code></blockquote>
<p>To get all the filespecs associated case 20:</p>
<blockquote><code>
SELECT TS_FILENAME FROM TS_VCACTIONS WHERE TS_TYPE = 1 AND TS_RECiD = 20
</code></blockquote>
<p>(You then have to parse out the "<code>filespec</code>" key from the
structure in the <code>TS_FILENAME</code> field.)</p>
<h2><a id="section-5" name="section-5">5. Determining what's changed</a></h2>
<p> TeamTrack keeps a record of all changes to the <code>TS_CASES</code>
table, in the <code>TS_CHANGES</code> table. This table has two fields
containing userids: <code>TS_USERID</code> and
<code>TS_REALUSERID</code>. The values in these fields depends on which
API method was used to make the change (see [<a
href="http://www.ravenbrook.com/project/p4dti/import/2001-03-19/teamshare-api-4507/api/TeamShareAPI.htm">TeamShare
2001-03-19</a>] for the API documentation). </p>
<p> When changes are made using the <code>TSServer::UpdateRecord</code>
method, the <code>TS_USERID</code> field contains the user connected to
the server via the API, and the <code>TS_REALUSERID</code> field
contains 0. </p>
<p> But if you use the <code>TSServer::Submit</code> or
<code>TSServer::Transition</code> method, then you can specify a user on
whose behalf to submit or transition the case. This is the user whose
privileges are checked, and this user appears in the
<code>TS_USERID</code> field in the <code>TS_CHANGES</code> table. </p>
<p> So the replicator can identify cases that have changed since it last
polled the database (excepting changes that it made), by selecting
records from the <code>TS_CHANGES</code> table with <code>TS_ID</code>
greater than the <a href="#section-3.5"><code>LAST_CHANGE</code></a>
configuration parameter, and where neither the <code>TS_USERID</code>
nor the <code>TS_REALUSERID</code> field contains the replicator's
userid. </p>
<p> Supposing the replicator's userid is 17 and the
<code>LAST_CHANGE</code> configuration parameter is 12345, this SQL query
fetches the changed cases: </p>
<blockquote><code>
SELECT UNIQUE(TS_CASEID) FROM TS_CHANGES WHERE TS_TABLEID = 1 AND TS_ID
> 12345 AND TS_REALUSERID <> 17 AND TS_USERID <> 17
</code></blockquote>
<h2><a id="section-A" name="section-A">A. References</a></h2>
<table>
<tr valign="top">
<td>[<a id="ref-Fish-2000-09-06" name="ref-Fish-2000-09-06" href="http://info.ravenbrook.com/mail/2000/09/06/09-24-24/0.txt">Fish 2000-09-06</a>]</td>
<td>
"RE: TeamTrack database schema extensions for integration with Perforce" (e-mail message);
<a href="mailto:larry.fish@teamshare.com">Larry Fish</a>;
<a href="http://www.teamshare.com/">TeamShare Limited</a>;
2000-09-06 00:30:28 GMT.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-GDR-2000-08-18" name="ref-GDR-2000-08-18" href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-18/teamshare-meeting/">GDR 2000-08-18</a>]</td>
<td>
"TeamShare design meetings, 2000-08-14/2000-08-16";
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-08-18.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-GDR-2000-09-06a" name="ref-GDR-2000-09-06a" href="http://info.ravenbrook.com/mail/2000/09/06/10-11-04/0.txt">GDR 2000-09-06a</a>]</td>
<td>
"RE: TeamTrack database schema extensions for integration with Perforce" (e-mail message);
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-09-06 10:11:04 GMT.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-GDR-2000-09-06b" name="ref-GDR-2000-09-06b" href="http://www.ravenbrook.com/project/p4dti/doc/2000-09-06/teamtrack-sample-data/">GDR 2000-09-06b</a>]</td>
<td>
"TeamTrack sample data";
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-09-06.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-GDR-2000-09-13" name="ref-GDR-2000-09-13" href="../replicator/index.html">GDR 2000-09-13</a>]</td>
<td>
"Replicator design";
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-09-13.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-GDR-2000-10-10" name="ref-GDR-2000-10-10" href="http://info.ravenbrook.com/mail/2000/10/10/16-41-25/0.txt">GDR 2000-10-10</a>]</td>
<td>
"Defect in schema definition now fixed" (e-mail message);
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-10-10 16:41:25 GMT.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-GDR-2000-10-11" name="ref-GDR-2000-10-11" href="http://info.ravenbrook.com/mail/2000/10/11/09-29-41/0.txt">GDR 2000-10-11</a>]</td>
<td>
"Re: Design Discussion: modified time in the VCACTIONS table" (e-mail message);
<a href="mailto:gdr@ravenbrook.com">Gareth Rees</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-10-11 09:29:41 GMT.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-RB-2000-08-08" name="ref-RB-2000-08-08" href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-08/perforce-meeting/">RB 2000-08-08</a>]</td>
<td>
"P4DTI Design Meetings with Perforce"
<a href="mailto:rb@ravenbrook.com">Richard Brooksby</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-08-08.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-RB-2000-08-10" name="ref-RB-2000-08-10" href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-10/replication-mapping/">RB 2000-08-10</a>]</td>
<td>
"Replication mapping design notes" (e-mail message);
<a href="mailto:rb@ravenbrook.com">Richard Brooksby</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-08-10 11:27:03 GMT.
</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>
<tr valign="top">
<td>[<a id="ref-TeamShare-2000-01-20" name="ref-TeamShare-2000-01-20" href="http://www.ravenbrook.com/project/p4dti/import/2000-01-20/teamtrack-schema/TTschema21.pdf">TeamShare 2000-01-20</a>]</td>
<td>
"TeamTrack Database Schema (Database Version: 21)";
<a href="http://www.teamshare.com/">TeamShare</a>;
2000-01-20.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-TeamShare-2001-03-19" name="ref-TeamShare-2001-03-19" href="http://www.ravenbrook.com/project/p4dti/import/2001-03-19/teamshare-api-4507/api/TeamShareAPI.htm">TeamShare 2001-03-19</a>]</td>
<td>
"TeamShare API (build 4509)";
<a href="http://www.teamshare.com/">TeamShare</a>;
2001-03-19.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-TeamShare-2001-06-19" name="ref-TeamShare-2001-06-19" href="http://www.ravenbrook.com/project/p4dti/import/2001-06-19/teamtrack-5034/api/schema.htm">TeamShare 2001-06-19</a>]</td>
<td>
"TeamTrack Database Schema (Database Version: 514)";
<a href="mailto:"></a>;
<a href=""></a>;
2001-06-19.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-van-Rossum-2000-03-22" name="ref-van-Rossum-2000-03-22" href="http://www.python.org/doc/current/ref/ref.html">van Rossum 2000-03-22</a>]</td>
<td>
"Python Reference Manual (Release 1.5.2)";
<a href="mailto:guido@python.org">Guido van Rossum</a>;
Corporation for National Research Initiatives;
2000-03-22.
</td>
</tr>
</table>
<h2><a id="section-B" name="section-B">B. Document History</a></h2>
<table>
<tr valign="top">
<td>2000-09-04</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Created based on [<a href="http:/project/p4dti/doc/2000-08-10/replication-mapping/">RB 2000-08-10</a>], [<a href="http://info.ravenbrook.com/mail/2000/08/18/18-58-50/0.txt">RB 2000-08-18</a>], [<a href="http:/project/p4dti/doc/2000-08-30/design-document-structure/">RB 2000-08-30</a>] and [<a href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-18/teamshare-meeting/">GDR 2000-08-18</a>]. Fixed defects discovered in review with RB.</td>
</tr>
<tr valign="top">
<td>2000-09-06</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Added clarifications from my e-mail to Larry Fish [<a
href="http://info.ravenbrook.com/mail/2000/09/06/10-11-04/0.txt">GDR
2000-09-06a</a>] in response to his questions [<a
href="http://info.ravenbrook.com/mail/2000/09/06/09-24-24/0.txt">Fish 2000-09-06</a>]. Added reference to sample data [<a
href="http://www.ravenbrook.com/project/p4dti/doc/2000-09-06/teamtrack-sample-data/">GDR
2000-09-06b</a>]. Added note about modification times.</td>
</tr>
<tr valign="top">
<td>2000-09-10</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Added description of <code>TS_P4DTI_REPLICATED</code> field in the
<code>TS_CASES</code> table.</td>
</tr>
<tr valign="top">
<td>2000-09-11</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Added example queries (<a href="#section-4">section 4</a>).</td>
</tr>
<tr valign="top">
<td>2000-09-13</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Explained how fields get added to the <code>TS_CASES</code> table.
Listed the fields which it would be sensible to index.</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.</td>
</tr>
<tr valign="top">
<td>2000-10-10</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Removed <code>SID</code> and <code>SERVER_DESCRIPTION</code> keys
from the replicator configuration in the <code>VCACTIONS</code> table
(because they don't support the possibility of a replicator
replicating to more than one server). Added <code>SERVER</code> key
which does. See [<a href="http://info.ravenbrook.com/mail/2000/10/10/16-41-25/0.txt">GDR 2000-10-10</a>].</td>
</tr>
<tr valign="top">
<td>2000-10-11</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Made description of <code>TS_TIME2</code> field in the
<code>VCACTIONS</code> table clearer: it's now clear who requires it
to be 0 and when. See [<a
href="http://info.ravenbrook.com/mail/2000/10/11/09-29-41/0.txt">GDR
2000-10-11</a>].</td>
</tr>
<tr valign="top">
<td>2000-11-30</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Removed all <code>TS_TIME2</code> fields. These are not used,
since (1) TeamTrack creates <code>TS_CHANGES</code> entries when these
records changes, and (2) we don't support editing or adding associated
stuff in TeamTrack anyway. Corrected description of
<code>STATUS_VALUES</code> keyword. Removed
<code>P4DTI_REPLICATED</code> field from <code>TS_CASES</code> table
and added <code>P4DTI_ACTION</code>. Added
<code>CHANGELIST_URL</code> configuration parameter.</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-03-21</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Removed <code>P4DTI_ACTION</code> field (no longer required; conflict resolution is always immediate). </td>
</tr>
<tr valign="top">
<td>2001-03-22</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Removed section on modification times: the TeamTrack integration doesn't use them. Added section on determining what's changed. Added references to the TeamTrack database schema.</td>
</tr>
<tr valign="top">
<td>2001-07-09</td>
<td><a href="mailto:nb@ravenbrook.com">NB</a></td>
<td>Added JOB_URL by analogy with CHANGELIST_URL.</td>
</tr>
<tr valign="top">
<td>2001-11-05</td>
<td><a href="mailto:gdr@ravenbrook.com">GDR</a></td>
<td>Added reference to TeamTrack database schema for database version 514.</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/teamtrack-p4dti-schema/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>