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.

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. Modification times

All the relations have a field containing the modification time for that relation. These times are always the time the relation was modified on the TeamTrack server, never the time the relations was modified in Perforce, even if the only reason for the modification in TeamTrack is that a modification has been replicated from Perforce.

The reason for this is that these times may be needed to be compared (in order to discover whether a record is up to date). Times on the TeamTrack and Perforce servers are not comparable, because their clocks may show different times.

2.6. 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.7. 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, TS_TIME2 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.6).

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:

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

To get the fixes which are to be replicated by the replicator with identifier case and which are not up-to-date with Perforce:

SELECT * FROM TS_VCACTIONS WHERE TS_TYPE = 2 AND TS_CHAR1 = 'case' AND TS_TIME1 > TS_TIME2

A. References

B. Document History

Copyright © 2000 Ravenbrook Limited. This document is provided "as is", without any express or implied warranty. In no event will the authors be held liable for any damages arising from the use of this document. You may make and distribute copies and derivative works of this document provided that (1) you do not charge a fee for this document or for its distribution, and (2) you retain as they appear all copyright and licence notices and document history entries, and (3) you append descriptions of your modifications to the document history.