Ravenbrook / Projects / Perforce Defect Tracking Integration / Master Product Sources / Design

Perforce Defect Tracking Integration Project


TeamTrack database schema extensions for integration with Perforce

Gareth Rees, Ravenbrook Limited, 2000-09-04

1. Introduction

This document defines TeamTrack database schema extensions for integrating with Perforce.

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 sure that TeamTrack and the P4DTI replicator are consistent in their treatment of these relations.

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

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.

This document is not confidential.

This document extends the TeamTrack schemas for database version 21 [TeamShare 2000-01-20] and database version 514 [TeamShare 2001-06-19].

A sample TeamTrack database containing data conforming to this schema is available [GDR 2000-09-06b].

2. Notes on the schema extensions

2.1. Using the VCACTIONS table

Three replicated relations are stored in TeamTrack's TS_VCACTIONS table, since TeamTrack 4.0 does not have the ability to add new tables [GDR 2000-08-18, 3].

To store multiple relations in the TS_VCACTIONS table, I use the TS_TYPE field to indicate which relation each record belongs to. As of TeamTrack 4.0.3, the TS_TYPE field is not used, and is 0 for each record. I have added the following record types:

Record type Record contains
1 Case/filespec relation (see section 3.2).
2 Case/change relation (see section 3.3).
3 Change description (see section 3.4).
4 Replicator configuration (see section 3.5).

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.

TeamTrack must check the type field of each record when handling the TS_VCACTIONS 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.)

2.2. Storing arbitrary data

In some cases there are not enough string fields in the TS_VCACTIONS 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 TS_VCACTIONS has only a single variable-length text field.

To work around these limitations, and to allow for extensions to these relations in the future, I use the TS_FILENAME field to store (potentially) arbitrarily many items of data, by representing the data as a string of key-value pairs, like this:

{ 'key1' : 'value1', 'key2' : 'value2', ... }

This is the syntax that the Python programming language uses to parse and print its dictionary data structures.

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.

The details of Python's dictionary syntax are given in [van Rossum 2000-03-22, 5.2.5], and of Python's string syntax in [van Rossum 2000-03-22, 2.4.1]. 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 shortstring syntax.

For example, the following string encodes the field "name" with value "Joe" and the field "userid" with value 1123:

{ 'name': 'Joe', 'userid': 1123 }

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.

2.3. Dealing with multiple Perforce servers

In order to meet requirement 96 (the integration copes with multiple Perforce servers), the data format is designed to be extended to meet this requirement.

The schema extensions therefore specify for each record a replicator identifier which identifies the replicator which is handling replication for that record, and a Perforce server identifier, 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 TS_VCACTIONS table.

Since requirement 96 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.

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).

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++.

2.4. Presentation and modification

The tables in section 3 below have columns labelled "P?" and "M?".

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.

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.

2.5. Initialization

The extra fields in the TS_CASES table will be created by the replicator the first time it runs, using the TSServer::AddField 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.

This document will eventually explain:

2.6. Indexes

The TS_CASES table will be queried on the TS_P4DTI_RID and TS_P4DTI_REPLICATED fields.

The TS_VCACTIONS table will be queried on the TS_TYPE, TS_CHAR1, TS_RECID, TS_TIME1 and TS_INFO1 fields.

It would be sensible to build indexes on all these fields. TeamShare can do this for the TS_VCACTIONS table, but not for the TS_CASES table, since the extra fields won't be added until the replicator starts running (see section 2.5).

It would be nice to have a method in the TeamShare API for creating an index.

3. The schema extensions

3.1. Extensions to the cases relation

Column name Datatype P? M? Description
TS_P4DTI_RID char(32) N R Identifier of the replicator that handles replication for this case (see section 2.3); empty if the case is not replicated.
TS_P4DTI_SID char(32) Y R Identifier of the Perforce server that this case is replicated with (see section 2.3); empty if the case is not replicated.
TS_P4DTI_JOBNAME varchar(1024) Y R 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.)

3.2. Case/filespec association relation

The case/filespec relation is stored in the TS_VCACTIONS table as detailed in the table below. See [GDR 2000-08-18, 3.1.2] for design decisions relating to this relation.

Column name Datatype P? M? Description
TS_TYPE int N - Type 1 indicates a case/filespec association (see section 2.1).
TS_CHAR1 varchar(255) N - Identifier of the replicator which is handling replication of this case/filespec association (see section 2.3). This must be the same as the TS_P4DTI_RID field for the case given in the TS_RECID field of this record (see section 3.1).
TS_CHAR2 varchar(255) N - Identifier for the Perforce server from which this case/filespec association is replicated (see section 2.3). This must be the same as the TS_P4DTI_SID field for the case given in the TS_RECID field of this record (see section 3.1).
TS_TABLEID int N - The table id of the TS_CASES table.
TS_RECID int Y U The case (that is, the value of the TS_ID field of the record in the TS_CASES table that is associated with the filespec).
TS_TIME1 int N T 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.
TS_FILENAME text Y U

A structure (see section 2.2).

The value for the "filespec" key is the filespec that is associated with the case (a string).

Notes:

  1. 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.

  2. 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.

  3. 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 requirement 39. We decided to represent this relation in Perforce as a list of filespecs in a text field in the job specification [RB 2000-08-08, 4.5]. We can provide tools that operate on this field, for example to check out for edit all the files associated with a job.

    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 //depot/product/foo/release/3.7/... and //depot/product/foo/release/3.8/... 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.

3.3. Fixes relation

The Perforce fixes relation is stored in the TS_VCACTIONS table as detailed in the table below. See [GDR 2000-08-18, 3.1.3] for the design decisions relating to this relation.

Column name Datatype P? M? Description
TS_TYPE int N - Type 2 indicates a fix record (see section 2.1).
TS_CHAR1 varchar(255) N - Identifier of the replicator which is handling replication of this fix record (see section 2.3). This must be the same as the TS_P4DTI_RID field for the case given in the TS_RECID field of this record (see section 3.1).
TS_CHAR2 varchar(255) N - Identifier of the Perforce server from which this fix record is replicated (see section 2.3). This must be the same as the TS_P4DTI_SID field for the case given in the TS_RECID field of this record (see section 3.1).
TS_TABLEID int N - The table id of the TS_CASES table.
TS_RECID int Y U The case (that is, the value of the TS_ID field of the record in the TS_CASES table that corresponds to the job in this fix record).
TS_INFO1 int Y U The number of the change in this fix record.
TS_TIME1 int Y T The date and time that this fix record was last modified, or 0 if the record is new and was added by the replicator.
TS_AUTHOR1 int Y T The user who last modified this fix record (that is, the value of the TS_ID field of the record in the TS_USERS table of that user).
TS_FILENAME text Y U

A structure (see section 2.2).

The value for the "status" key is the status keyword in this fix record (a string).

The value for the "client" 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.

Notes:

  1. The fixes relation is many-to-many. A case may be associated with many changes, and a change may be associated with many cases.

  2. 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.

  3. 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).

  4. The TeamTrack user should be able to:

    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.)

3.4. Change descriptions

Perforce change descriptions are stored in the TS_VCACTIONS table as detailed in the table below. See [GDR 2000-08-18, 3.1.4] for the design decisions relating to this relation.

Note that change descriptions cannot be created or modified in TeamTrack. They are replicated from Perforce to TeamTrack only.

Column name Datatype P? M? Description
TS_TYPE int N - Type 3 indicates a change description (see section 2.1).
TS_CHAR1 varchar(255) N - Identifier of the replicator which is handling replication of this change (see section 2.3).
TS_CHAR2 varchar(255) N - Identifier of the Perforce server from which this change is replicated (see section 2.3).
TS_INFO1 int Y - The change number.
TS_AUTHOR1 int Y R The value of the TS_ID field of the record in the TS_USERS table of the TeamTrack user who corresponds to the Perforce user who created the change.
TS_INFO2 int Y R 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.
TS_FILENAME text Y R

A structure (see section 2.2).

The value for the "description" key is the change comment (a string).

The value for the "client" key is the name of the Perforce client on which the change was created.

Notes:

  1. In a configuration with multiple Perforce servers, there may be multiple changes with the same number. These are distinguished by the Perforce server id.

3.5. Replicator configuration

Replicator configuration parameters are stored in the TS_VCACTIONS table as detailed in the table below.

Column name Datatype P? M? Description
TS_TYPE int N - Type 4 indicates a replicator configuration parameter (see section 2.1).
TS_CHAR1 varchar(255) N - Identifier of the replicator to which this configuration paramater applies (see section 2.3).
TS_CHAR2 varchar(255) N - Name of the configuration parameter.

Defined configuration parameters are as follows:

Parameter name Field containing value Meaning of value
LAST_CHANGE TS_INFO1 The last record in the TS_CHANGES table that the replicator has dealt with.
SERVER TS_FILENAME

A structure (see section 2.2).

The value for the sid key is the server identifier of a Perforce server that the replicator replicates to.

The value for the description key is a short human-readable description of the Perforce server.

When a replicator replicates to several Perforce servers, there will be several entries with this key in the configuration for that replicator.

STATUS_VALUES TS_FILENAME

A structure (see section 2.2).

The value for the sid key is the server identifier of a Perforce server that the replicator replicates to.

The value for the description key is a list of Perforce job status keywords, replicated from the Perforce jobspec. The keywords are separated by slashes. For example, open/suspended/closed is the default list of job status keywords.

CHANGELIST_URL TS_FILENAME

A structure (see section 2.2).

The value for the sid key is the server identifier of a Perforce server that the replicator replicates to.

The value for the description key is a format string which can be used to build a URL that provides details of a Perforce changelist. The string has one %d 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.

If there is no CHANGELIST_URL parameter for a given replicator identifier and Perforce server identifier, then TeamTrack should not make the change number into a link.

JOB_URL TS_FILENAME

A structure (see section 2.2).

The value for the sid key is the server identifier of a Perforce server that the replicator replicates to.

The value for the description key is a format string which can be used to build a URL that provides details of a Perforce job. The string has one %s 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.

If there is no JOB_URL parameter for a given replicator identifier and Perforce server identifier, then TeamTrack should not make the jobname into a link.

Note that a single replicator may have several SERVER and several STATUS_VALUES 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 SERVER paramater whose TS_CHAR1 field matches the TS_P4DTI_RID field of the case, and whose value for the sid key in the TS_FILENAME field matches the RS_P4DTI_SID field of the case. A similar selection must be made among the possible STATUS_VALUES parameters when listing the possible statuses for a fix.

4. Example queries

To select all the cases which are to be replicated by the replicator with identifier case and which are not up-to-date with Perforce:

SELECT * FROM TS_CASES WHERE TS_P4DTI_RID = 'case' AND TS_LASTMODIFIEDDATE > TS_P4DTI_REPLICATED

To get all the change descriptions:

SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 3

To get the change numbers for the fixes for case 20:

SELECT TS_INFO1 FROM TS_VCACTIONS WHERE TS_TYPE = 2 AND TS_RECID = 20

To get all the filespecs associated case 20:

SELECT TS_FILENAME FROM TS_VCACTIONS WHERE TS_TYPE = 1 AND TS_RECiD = 20

(You then have to parse out the "filespec" key from the structure in the TS_FILENAME field.)

5. Determining what's changed

TeamTrack keeps a record of all changes to the TS_CASES table, in the TS_CHANGES table. This table has two fields containing userids: TS_USERID and TS_REALUSERID. The values in these fields depends on which API method was used to make the change (see [TeamShare 2001-03-19] for the API documentation).

When changes are made using the TSServer::UpdateRecord method, the TS_USERID field contains the user connected to the server via the API, and the TS_REALUSERID field contains 0.

But if you use the TSServer::Submit or TSServer::Transition 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 TS_USERID field in the TS_CHANGES table.

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 TS_CHANGES table with TS_ID greater than the LAST_CHANGE configuration parameter, and where neither the TS_USERID nor the TS_REALUSERID field contains the replicator's userid.

Supposing the replicator's userid is 17 and the LAST_CHANGE configuration parameter is 12345, this SQL query fetches the changed cases:

SELECT UNIQUE(TS_CASEID) FROM TS_CHANGES WHERE TS_TABLEID = 1 AND TS_ID > 12345 AND TS_REALUSERID <> 17 AND TS_USERID <> 17

A. References

[Fish 2000-09-06] "RE: TeamTrack database schema extensions for integration with Perforce" (e-mail message); Larry Fish; TeamShare Limited; 2000-09-06 00:30:28 GMT.
[GDR 2000-08-18] "TeamShare design meetings, 2000-08-14/2000-08-16"; Gareth Rees; Ravenbrook Limited; 2000-08-18.
[GDR 2000-09-06a] "RE: TeamTrack database schema extensions for integration with Perforce" (e-mail message); Gareth Rees; Ravenbrook Limited; 2000-09-06 10:11:04 GMT.
[GDR 2000-09-06b] "TeamTrack sample data"; Gareth Rees; Ravenbrook Limited; 2000-09-06.
[GDR 2000-09-13] "Replicator design"; Gareth Rees; Ravenbrook Limited; 2000-09-13.
[GDR 2000-10-10] "Defect in schema definition now fixed" (e-mail message); Gareth Rees; Ravenbrook Limited; 2000-10-10 16:41:25 GMT.
[GDR 2000-10-11] "Re: Design Discussion: modified time in the VCACTIONS table" (e-mail message); Gareth Rees; Ravenbrook Limited; 2000-10-11 09:29:41 GMT.
[RB 2000-08-08] "P4DTI Design Meetings with Perforce" Richard Brooksby; Ravenbrook Limited; 2000-08-08.
[RB 2000-08-10] "Replication mapping design notes" (e-mail message); Richard Brooksby; Ravenbrook Limited; 2000-08-10 11:27:03 GMT.
[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.
[TeamShare 2000-01-20] "TeamTrack Database Schema (Database Version: 21)"; TeamShare; 2000-01-20.
[TeamShare 2001-03-19] "TeamShare API (build 4509)"; TeamShare; 2001-03-19.
[TeamShare 2001-06-19] "TeamTrack Database Schema (Database Version: 514)"; ; ; 2001-06-19.
[van Rossum 2000-03-22] "Python Reference Manual (Release 1.5.2)"; Guido van Rossum; Corporation for National Research Initiatives; 2000-03-22.

B. Document History

2000-09-04 GDR Created based on [RB 2000-08-10], [RB 2000-08-18], [RB 2000-08-30] and [GDR 2000-08-18]. Fixed defects discovered in review with RB.
2000-09-06 GDR Added clarifications from my e-mail to Larry Fish [GDR 2000-09-06a] in response to his questions [Fish 2000-09-06]. Added reference to sample data [GDR 2000-09-06b]. Added note about modification times.
2000-09-10 GDR Added description of TS_P4DTI_REPLICATED field in the TS_CASES table.
2000-09-11 GDR Added example queries (section 4).
2000-09-13 GDR Explained how fields get added to the TS_CASES table. Listed the fields which it would be sensible to index.
2000-10-05 RB Updated reference to design document procedure [RB 2000-10-05] to point to on-line document.
2000-10-10 GDR Removed SID and SERVER_DESCRIPTION keys from the replicator configuration in the VCACTIONS table (because they don't support the possibility of a replicator replicating to more than one server). Added SERVER key which does. See [GDR 2000-10-10].
2000-10-11 GDR Made description of TS_TIME2 field in the VCACTIONS table clearer: it's now clear who requires it to be 0 and when. See [GDR 2000-10-11].
2000-11-30 GDR Removed all TS_TIME2 fields. These are not used, since (1) TeamTrack creates TS_CHANGES entries when these records changes, and (2) we don't support editing or adding associated stuff in TeamTrack anyway. Corrected description of STATUS_VALUES keyword. Removed P4DTI_REPLICATED field from TS_CASES table and added P4DTI_ACTION. Added CHANGELIST_URL configuration parameter.
2001-03-02 RB Transferred copyright to Perforce under their license.
2001-03-21 GDR Removed P4DTI_ACTION field (no longer required; conflict resolution is always immediate).
2001-03-22 GDR 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.
2001-07-09 NB Added JOB_URL by analogy with CHANGELIST_URL.
2001-11-05 GDR Added reference to TeamTrack database schema for database version 514.

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/branch/2005-03-02/agena-extension/design/teamtrack-p4dti-schema/index.html#1 $

Ravenbrook / Projects / Perforce Defect Tracking Integration / Master Product Sources / Design