TeamTrack database schema extensions for integration with Perforce

1. Introduction

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

The purpose of this document is to make sure that TeamTrack and the P4DTI replicator are consistent in their treatment of these relations.

This document will be modified as the product is developed.

The intended readership is 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.

The TeamTrack schema which this document extends is documented in [TeamShare 2000-01-20].

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:

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:

• How these fields get their initial values.

• How to tell if the integration is running.

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

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.

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.

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:

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

   • Delete a fix record (for example, the fix record was added by mistake).

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

   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.

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.

Defined configuration parameters are as follows:

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

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.