Ravenbrook / Projects / Perforce Defect Tracking Integration / Project Documents
Perforce Defect Tracking Integration Project
This document describes the application programmer interfaces (APIs) to three defect tracking systems being considered for integration with Perforce. It analyses the impact of these interfaces on the replication architecture [GDR 2000-05-08, 3.2].
The purpose of this document is to record requirements arising from the defect tracking APIs so that the integration can be designed to meet these requirements.
The intended readership is anyone working on the project.
This document is not confidential.
TeamTrack uses a relational database to store its data. TeamTrack supports Microsoft Access, Microsoft SQL Server (version 6.0 or higher), Oracle (versions 7, 8, and 8i). The schema is presented in [TeamShare 2000-01-20].
Issues are called "cases" and are stored as records in the Cases
table (database name TS_CASES). A case has a
title (80 characters), a description (a
text field -- can be arbitrarily long in some databases),
an owner (cross-reference to the Users table), a
projectid (cross-reference to the Projects table), a
state (cross-reference to the States table), and other
fields.
The states that a case can be in are user-defined, using the States table.
TeamTrack has a mechanism for adding fields to the Cases table (and a
number of other tables). The AddField method of the
TSServer class in the API adds a record to the Fields
table, which describes the fields in a number of tables. Adding records
to the Fields table causes appropriate fields to be added to the
relevent table.
The history of changes to a number of tables, including the Cases
table, is recorded in the Changes table. The Changes table has one
record for each field that has changed: this gives the user who made the
change, the time that the change was made, and affected table and field,
the old value for the field and the new value. (It looks as though the
Changes table can not record changes to the description
field of cases, since the description field is a
text field but the Changes table only has room to record
255 characters of changes to a string field.)
TeamTrack manages permissions using tables of users and groups. Group membership is represented by the many-to-many Members table. The Privileges table describes which users and groups have what kind of access to which projects (as stored in the Projects table). This presumably controls privileges for issues, since each issue belongs to one project.
The TeamShare API [TeamShare 2000-01-19] is written in C++. It is delivered in source form together with a DLL for Windows. Supported platforms are Windows 95, 98, NT, 2000 and Linux.
The API is quite generic: you build and examine TSRecord
objects representing database records, which contain
TSField objects representing database fields. The API
knows the database schema and can construct a TSRecord
object for each table in the database.
To get records from the database corresponding to the result of a
SELECT * FROM table WHERE condition you issue the call
ReadRecordListWithWhere(&results, table_id,
"condition").
The ReadChangeList function gets a list of changes for
an issue (newest first if you supply the appropriate flag).
When connecting to the TeamTrack server via the TeamShare API, a program must log in as a user. They then have privileges corresponding to that user.
Bugzilla uses MySQL for its database server. MySQL does not have transactions. Bugzilla attempts to get round this (sometimes) by locking the tables it is about to change.
The database schema is in the file checksetup.pl.
Issues are stored in the bugs table.
There is a bugs_activity table which looks as though it
describes changes to the bugs table. However, from an
examination of the source code it is not clear that the
bugs_activity table is always added to when the
bugs table is updated. So it may not be a reliable
indication of changes.
There appear to be no abstractions over the database. To get
records, Bugzilla issues the appropriate SQL SELECT query
and to change records, Bugzilla issues the appropriate
UPDATE query.
| Question | TeamTrack | Bugzilla |
|---|---|---|
| Can we get a list of issues? | Yes, by calling
TSServer::ReadAllRecords(&results,
TS_TBLID_CASES). |
Yes, by executing the appopriate SQL query of the
form SELECT * FROM bugs. |
| Can we add new issues? | Yes, by constructing a new record object for the
Cases table: TSRecord new_issue(TS_TBLID_CASES,
&server);, then instantiating its fields appropriately,
using a series of calls like new_issue.SetString("title",
title); and then calling
TSServer::Submit(&new_issue_id, login_name, &new_issue,
TS_TBLID_CASES, projectid);. (See the sample program
Samples/SubmitIssue/SubmitIssue.cpp for some an example
of submitting a new issue.) |
Yes, by executing the appopriate SQL query of the
form INSERT INTO bugs ( ... ) VALUES ( ... ). The code
in the post_bug.cgi file contains logic for deducing
default values for fields in the bugs table. It doesn't
look like there's any need to update the bugs_activity
table at this point, since post_bug.cgi doesn't. |
| Can we update existing issues? | Yes, by constructing a new record object for the
Cases table: TSRecord issue(TS_TBLID_CASES,
&server);, fetching the issue record using
server.ReadRecord(&issue, issue_id);, changing
appropriate properties with calls like issue.SetInt("projectid",
new_projectid); and then updating the issue with
server.UpdateRecord(&issue, 0);. (Is there a need to
update the Changes table by hand as part of the transaction? I would
expect this to be handled by the TeamTrack server, but the
documentation is not clear on this point.) |
Yes, by commiting a transaction that issues the
appropriate UPDATE bugs SET ... WHERE ... query, and
adding appropriate rows to the bugs_activity table. This
will work in MySQL 3.23.15 or later, which supports transactions [MySQL
2000-07-02, 8.4]. However, earlier versions do not, and it may
not be straightforward to switch the Bugzilla tables to being
transaction safe. If this proves tricky, it might be better to lock
the two affected tables instead of using a transaction. |
| Can we find out which issues have changed recently? | Yes, by querying the Changes table with code like
char query[100]; snprintf(query, 100, "TABLEID = %d AND TIME >=
%d ORDER BY TIME", TS_TBLID_CASES, time_of_last_query);
server.ReadRecordListWithWhere(&results, TS_TBLID_CHANGES,
query); |
The bugs_activity table has a
bug_when field that may indicate the date and time at
which the bug was changed. However, it is not clear from the code
that the bugs_activity table is kept up to date, so this
may not be reliable. |
| Can we add new fields to the issue relation? | Yes, constructing a new record for the Fields table:
TSRecord new_field(TS_TBLID_FIELDS, &server);,
instantiating its fields appropriately, using a series of calls like
new_field.SetString("name", "foo"); and finally creating
the field with a call like server.AddField(&new_field,
TS_TBLID_CASES, TS_FLDTYPE_NUMERIC);. |
Maybe. We must write our own software for adding
fields to the bugs table. The Bugzilla methodology for
extending the database seems to be to keep the schema up to date in
checksetup.pl and to have code in that file for checking
that the database is up-to-date and for fixing any incompatibilities.
(But what if a local site has modified their own
checksetup.pl as they probably have done? How could we
modify it too without stamping on their changes?) |
| Can we arrange to be alerted when issues change? | Maybe. TeamTrack's API provides no mechanism for this. However, the database may support triggers. | Maybe. MySQL does not support stored procedures or
triggers [MySQL
2000-07-02, 5.4.4]. So Bugzilla would have to be changed to do
the alerting. There is no abstraction for creating or updating bugs,
but the text "INSERT INTO bugs" appears in three places
(backdoor.cgi, bug_email.pl, and
post_bug.cgi) and the text "UPDATE bugs"
appears in ten places (CGI.pl,
doeditvotes.cgi, editcomponents.cgi,
editmilestones.cgi, editproducts.cgi,
editversions.cgi, globals.pl,
process_bug.cgi, processmail, and
santitycheck.cgi) so it may be possible to insert a call
to an alerting function after each of these updates. |
| Can we figure out if a given user has permission to work on an issue? | Yes, in theory. The HasPrivilege
function determines if a user has a particular set of privileges with
respect to a project. However, there are 115 kinds of privilege and
they are not clearly documented so it may not be easy to duplicate
TeamShare's privileges logic. |
Yes, in theory. The logic for checking whether a
user can submit a new bug is simply UserInGroup($product)
(see post_bug.cgi). The logic for checking if a user can
edit a bug appears to be in the subroutine
CheckCanChangeField in process_bug.cgi. |
| Other analysis | The replication daemon must be able to log into the TeamTrack server as a user with sufficient privileges. |
| [Bugzilla 2000-05-09] | "Bugzilla 2.10"; Mozilla; 2000-05-09. |
| [GDR 2000-05-08] | "Architecture Proposals for Defect Tracking Integration"; Gareth Rees; Ravenbrook Limited; 2000-05-08. |
| [MySQL 2000-07-02] | "MySQL Reference Manual for version 3.23.20-beta"; MySQL; 2000-07-02. |
| [TeamShare 2000-01-19] | "TeamShare API Reference Guide"; TeamShare; 2000-01-19. |
| [TeamShare 2000-01-20] | "TeamTrack Database Schema (Database Version: 21)"; TeamShare; 2000-01-20. |
| 2000-06-30 | GDR | Created. |
| 2000-07-03 | GDR | Described and analyzed TeamTrack and Bugzilla. |
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 verbatim copies of this document provided that you do not charge a fee for this document or for its distribution.
$Id: //info.ravenbrook.com/project/p4dti/doc/2000-06-30/dtapi-analysis/index.html#9 $
Ravenbrook / Projects / Perforce Defect Tracking Integration / Project Documents