Ravenbrook / Projects / Perforce Defect Tracking Integration / Version 1.0 Product Sources / Design
Perforce Defect Tracking Integration Project
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].
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.)
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.
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++.
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.
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.
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.
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.6).
It would be nice to have a method in the TeamShare API for creating an index.
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.) |
TS_P4DTI_ACTION |
char(32) |
N | R | The action that the replicator should take with this record, one of "keep", "discard", "replicate" or "wait". See [GDR 2000-09-13, 2.10]. |
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 " |
Notes:
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.
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.
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.
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 " The value for the " |
Notes:
The fixes relation is many-to-many. A case may be associated with many changes, and a change may be associated with many cases.
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.
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).
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.)
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 " The value for the " |
Notes:
In a configuration with multiple Perforce servers, there may be multiple changes with the same number. These are distinguished by the Perforce server id.
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 The value for the 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 The value for the |
CHANGELIST_URL |
TS_FILENAME |
A structure (see section 2.2). The value for the The value for the If there is no |
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.
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.)
[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-10-05] | "P4DTI Project Design Document Procedure"; Richard Brooksby; Ravenbrook Limited; 2000-10-05. |
[RB 2000-08-30] | "Design document structure" (e-mail message); Richard Brooksby; Ravenbrook Limited; 2000-08-30. |
[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 (version 1.0)"; 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. |
[van Rossum 2000-03-22] | "Python Reference Manual (Release 1.5.2)"; Guido van Rossum; Corporation for National Research Initiatives; 2000-03-22. |
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
(section 2.6). Listed the fields which it
would be sensible to index (section
2.7). |
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. |
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/version/1.0/design/teamtrack-p4dti-schema/index.html#3 $
Ravenbrook / Projects / Perforce Defect Tracking Integration / Version 1.0 Product Sources / Design