TeamShare API

Reference Guide

Last updated: 19 January 2000

Copyright © 1998, 1999, 2000 TeamShare, Inc.

All Rights Reserved

Dear Customer,

This document describes TeamShare’s database schema and Application Programming Interface (API) for TeamTrack.

With TeamTrack 4.0, we have opened our API to our customers to be used to safely modify TeamTrack databases. Through the database you can extend TeamTrack functionality to meet your company needs. Using the schema is a great means to read and report on information collected through TeamTrack that is not readily available through standard reporting. While this document is available upon customer request, we recommend using the schema only to report on data that is not easily accessible via TeamTrack. Modifications to the database should only be performed using the TeamTrack administrator or our Application Programming Interface (API).

Database

The TeamTrack database is stored in an underlying relational database. This document describes the tables, columns, and relationships of the database. This information should be used cautiously. Please note, TeamShare Customer Support does not support backend modifications. Additionally, changes, deletions and modifications performed directly to the database are not recommended as TeamShare reserves the right to modify the schema when necessary to implement new features. Hence changes made directly to the database may not be upward compatible. If a new table or column is added in a future version, any code written to a prior version will not have knowledge of the new tables or columns, and thus may not perform correctly.

Through TeamTrack, important information is captured in the database. Companies may have unique needs to gather, organize and analyze this data. While keeping in mind the schema may change with new releases of the product, it is safe to use this information to read and display data from the database. For example, you may wish to write a custom report that processes issues in a special way or you may want to capture time metrics (historical data) using the TS_CHANGES table. This document will aid you in creating reports of this nature and more.

Application Programming Interface (API)

TeamShare recommends performing database modifications through the Administrator or by creating an application which uses the API. Alterations that can not be done through the Administrator should be performed through the API. TeamShare makes best efforts to ensure upward compatibility should database changes be performed in this manner. The API is available to all TeamTrack customers.

The TeamShare API is written in C++. Using the API, modification to the database can now be done safely. Applications can be written to this API, which import and export data between TeamShare applications and other application programs. C++ programming experience is required to create an application using this class library. Please note, TeamShare does not support the API through its standard Customer Support contract. API support will be available in time-blocks through our Professional Services Group (PSG). PSG staffs expert C++ developers who are available to support and/or develop these applications to meet your needs. Should assistance be required in creating or understanding API classes, please contact your sales executive.

For more information about securing PSG services to support and/or develop API applications to meet your needs, contact your TeamShare account manager.

Thank you,

TeamShare, Inc.
TeamShare API

Overview

The TeamShare API is a collection of C++ source files that can be used to build a Windows DLL or a Linux library file. This library allows a client program to connect to the teamtrack server and query or update data in the database. The client program can run on the same computer as the teamtrack server or on any computer that has network access to the server.

Getting Started

Using the TeamShare API consists of the following steps.

1. With the API source code and a C++ compiler, build the library – TSApiWin32.dll, TSApi.dll or TSApi.lib.

2. Build the Sample API programs to test the API and verify its operation

3. Using the API’s C++ header files and the class descriptions in the remainder of this document, build a C++ program that connects to the teamtrack engine to query or update the database according to your needs.

The API and sample programs should compile on Windows 95, 98, NT, 2000 and Linux. For Windows platforms, we supply workspace and project files for MS Developers Studio. For Linux, we supply a basic makefile.

All the following directions are written for users of MSVC++ 6.0. If you are using some other development environment, you will need to use the menu commands appropriate for that tool.

Building the API and Sample Programs

The easiest way to do this is to load the workspace file samples.dsw and rebuild all. Use Build->Batch Build->Rebuild All from the menu. This will build the debug and release versions of the sample programs and the API DLLs. These binaries will all be put in the directories …\Build\Debug and …\Build\Release.

Now you can run each of the samples and get a feeling for how they operate. It may be a good idea to run against a sample database rather than your production system until you are accustomed to how the API operates. You can choose which server the sample programs connect to when you run them.

Note Concerning Developer Studio 97: In order to build the sample programs under Dev Studio 97, the preprocessor definition _DEV_STUDIO_5 will need to be added to each sample program's project settings as well. In addition, you should expect compiler warnings in Debug mode using Dev Studio 97.

Note Concerning User Privileges: The ability to connect to the server via the API is controlled on a per-user basis. By default, users do not have the privilege to connect using the API turned on. You will need to use ttAdmin to set this privilege for the appropriate users.

Building Your Own Custom Program Using the TeamShare API

This task requires significant skill in C++ programming and it is far beyond the scope of this document to provide detailed instructions. The following instructions will help get you started.

Preprocessor Symbols

· _DEV_STUDIO_5
If using Microsoft Dev Studio 97 (Visual C++ 5.0), you will need to add this preprocessor definition to your project.

· TS_USE_MFC
The API assumes no dependencies on the MFC CSocket class. In order to use MFC, define a preprocessor directive in the Project Settings called TS_USE_MFC. This definition directs the API to use the MFC CSocket class. If there is no definition, a generic CSocket class will be used. If using Linux or Unix, there is no definition necessary.

· If you are developing on Windows but not using MFC, it is not necessary to define any preprocessor directives. In this case, TS_USE_WINSOCK is defined and Windows Sockets is used. (Refer to TSSocket.h for the source code.)

Reference Guide

The TeamShare Application Programming Interface (hereafter API) can be used as an alternative means of manipulating data found in the TeamTrack database. It can be used to import or export data between TeamTrack and other application API programs, to add issues, incidents, users, groups, companies, contacts, projects, folders, and fields, or simply to move folders and projects around in the hierarchy. It can also be used to verify and establish privileges, mass transition issues or incidents, to verify data or develop custom reporting. In addition, the API provides a means to communicate or share data between applications. The API can be used to create an application that updates TeamTrack data with data from other applications.

The API is a C++ class library. C++ programming knowledge is required to create an application using this library. Note: TeamShare does not support the API through the standard Technical Support contract. Should assistance be required in creating or understanding API classes, please contact your sales representative.

Class synopsis

Classes the API Programmer will use on a regular basis:

TSServer	Representation of the TeamTrack server in the API program
TSRecord	A generic representation of a single record from any table in the TeamTrack database
TSRecordList	List of TSRecord objects.
TSTreeList	List of TSRecord objects in hierarchical order.
TSField	Represents a single field of a record. This generic field object will contain either a text string, an integer or a double depending on the datatype of the field.
TSFieldList	List of TSField objects.
TSPosition	Pointer to a position in a list. Used for iterating through a list of records or fields.
TSString	Class to manage a text string, perform comparisons, concatenations etc.
TSSchema	Schema for record.

Additional classes the typical API Programmer shouldn’t ever need to use:

TSObject	Abstract base class for any object to be put on a TSList
TSList	Base class for all list classes.
TSSocket	Object to send a receive data on a TCP/IP socket.
CSocket	Base class for the TSSocket class. Depending on whether you are writing your API program on UNIX or Windows this will either be the MFC CSocket class or TeamShare’s own CSocket class.

TSServer is the main interface used to communicate with a webserver hosting a TeamTrack database. It is the primary entry class to TeamTrack. Methods called in TSServer build a string with the name of the method and any parameters needed for that method. This string is then sent across the socket to the web server. The API application then issues a Receive and waits for data to come back. The web server parses the string and calls the appropriate method in TeamTrack’s source code. Once the request has been processed, data is sent back across the socket. The data is received and processed on the API side.

The other exposed classes are supporting classes. This document outlines all available classes, member functions and variables. Sample programs can be found in the TeamShare directory under API/samples. Individual or all the sample applications can be built using the Samples.dsw project workspace.

The sample programs are:

ConsolidateFields

Syntax: ConsolidateFields

This is a windows application which uses MFC classes. This program is used to combine two fields of the same type into one of the fields. For example, if there are two numeric fields, one that is used often and one that is rarely used, this program can be used to move the data from the field that is rarely used into the other field. The first field could then be used for something else. This program demonstrates the use of a dialog box for authenticating the user and obtaining input. The user will be prompted to select the appropriate table which has fields defined which the user wishes to consolidate. The user will also be prompted to select which field is the destination and which field is the source for the consolidation.

DeletedItemsReport

Syntax: DeletedItemsReport

Lists the issues or incidents that have been deleted, when, and by which user. Demonstrates the use of command prompts to obtain authentication for running the program.

ListGroupMembers

Syntax: ListGroupMembers

Lists the names of all the members of a specific group. Demonstrates use of command prompts to obtain the parameters for the program.

ListGroups

Syntax: ListGroups

Lists all group names defined in the database. Demonstrates using command prompts to obtain authentication for running the program.

ListPrivs

Syntax: ListPrivs

Lists the privileges for either a user or a group. Demonstrates using command prompts to obtain the parameters for the program. Privileges are not broken down by project.

ReadListWithWhere

Syntax: ReadListWithWhere <server_name><login_id> <password> <table_id> “where clause” (w/o the word “where”)

Reads all the records from a specified table based on the WHERE clause passed. The user does not need to type the word “WHERE”.

ReadRecord

Syntax: ReadRecord <server_name> <login_id> <password> <table_id> <record_id> <optional_field_type>

Reads a particular record from a table for a specified ID and displays the data on the screen.

ReadRecordMFC

Syntax: ReadRecordMFC /h | <server_name> <login_id> <password> <table_id> <record_id>

Reads a particular record from a table for a specified ID and displays the data on the screen. Demonstrates how to use the MFC CSocket class to connect to the webserver.

ReadUser

Syntax: ReadUser <server_name> <login_id> <password> <user_name>

Reads a record from the TS_USERS table and displays the data to the screen. This user is required to type all parameters after the program name.

ReadWithWhere

Syntax: ReadWithWhere

Reads the records from the specified table based on the WHERE clause passed. The user does not need to type the word “WHERE”. This sample demonstrates the use of command prompts to obtain the parameters for the program.

ShowAvailableTables

This is a windows program that lists the names of the tables defined in the database. All tableids are defined in TSDef.h using the naming convention: TS_TBLID_tablename. “Tablename” would be capitalized. For example, to reference the tableid for the TS_CONTACTS table, use TS_TBLID_CONTACTS. This sample demonstrates the use of dialog to obtain authentication for running the program and displaying information.

SubmitIssue

Syntax: SubmitIssue

Submits either an Issue or an Incident. This sample demonstrates the use of command prompts to obtain the parameters for the program. It is important to note that any fields can be skipped if the user does not wish to enter a value. If the field is required, the user will be re-prompted for the value. Multi-select fields are listed as data type “string” and need to be entered with the appropriate corresponding selection ids strung together. For example: 98, 99, 101.

TransitionIssue

Syntax: TransitionIssue

Transitions an Issue or an Incident record. This sample demonstrates the use of command prompts to obtain the parameters for the program. Note: The Transition Name parameter is case-sensitive.

Getting Started

A TeamTrack login id and password are required to establish a connection with the TeamTrack database across a socket. In order to run API programs on a machine other than the webserver, the login id and password must correspond to a user who has also been granted the privilege to “Connect using the API”. This is a new system privilege in version 4.0 of TeamTrack.

Steps to Building a Simple API Application Program

1. Create a connection to the TeamTrack web server using the TSServer class

TSServer teamtrack;

teamtrack.Connect(“admin”,”password”,”teamtrack_host”);

2. Create a record or record list that will be used to send or receive data from TeamTrack.

TSRecordList statesList;

3. If sending a record to TeamTrack using a function such as AddRecord, fill in the data values

· Use the SetString(), SetInt() and SetDouble() methods of the TSRecord class to set the values of the fields that aren’t going to be empty in the record.

· Refer to the TeamTrack database schema to determine whether a field is an integer or a string.

· The datatype of each field in the record is stored within the record so it is possible to retrieve it to verify the correct datatype before calling SetString(), SetInt() or SetDouble().

TSRecord user(TS_TBLID_USERS, &teamtrack);

Teamtrack.ReadRecord(&user, 5);

user.SetString(“email”,”chris_tester@teamshare.com”);

4. Call a method of the TSServer object to perform the desired action, such as ReadRecord() or AddRecord(). Check it’s return value to determine success or failure of the operation.

· All of the methods of the TSServer class return TS_OK when successful or, some error code such as TS_CONNECT_ERROR when the TSServer was unable to connect to the TeamTrack web server. (Refer to TSDef.h for error codes)

if(teamtrack.ReadAllRecords(&statesList, TS_TBLID_STATES) != TS_OK)

{

printf("Error reading from states table\n");

return 1;

}

Platform Dependencies

TS_USE_MFC

The API assumes no dependencies on the MFC CSocket class. In order to use MFC, define a preprocessor directive in the Project Settings called TS_USE_MFC. This definition directs the API to use the MFC CSocket class. If there is no definition, a generic CSocket class will be used. If using Linux or Unix, there is no definition necessary.

If the user is on Windows but not using MFC, it is not necessary to define any preprocessor directives. In this case, TS_USE_WINSOCK is defined and Windows Sockets is used. (Refer to TSSocket.h for the source code.)

Table Dependencies

To enforce the referential integrity between tables in the database, it is helpful to be knowledgeable about the relationships.

TS_TABLES – This table holds the last id value that was used for every table in the database. Any time a record is added to a table, the TS_LASTID column corresponding to that table needs to get incremented. The id values for every table are defined as constants in TSDef.h.

Reading data from tables

The majority of tables can be read using a generic read function, such as ReadAllRecords. This function has the added overhead of carrying any supporting lists of data that relate to the record being read. For example, if reading the list of Users, the supporting Membership and Privilege information is also read and added to the returning recordlist.

The Cases and Incidents tables are special.

Both TS_CASES and TS_INCIDENTS have only a few true fields (for example, id, parentid, tempid, etc.). Reading data from these tables requires thought. Fields are defined for a Workflow and the Workflow is associated with a Project. In addition, each project can override the field definition of the workflow. To read or write Issues or Incidents requires determining what project that issue belongs to, then which fields are valid for that project by which workflow is associated to it, then determining how fields have been affected by overrides.

The TSTreeList class is provided to facilitate reading data from tables that are recursive, for example, Projects, Workflows, Incidents, Folders, etc. It inherits from TSRecordList and should be used instead of TSRecordList when reading from these tables. To know which field is the sublist of a tree item, use the schema type called TS_SCHEMATYPE_TREELIST. It’s like a TS_SCHEMATYPE_LIST in every way except the field that has this schematype is the field that contains the sublist.

Table Schemas

There are certain tables that contain a few non-database columns in addition to their physical database columns. These non-database columns are built programmatically to prevent the user from have to issue a separate read for the information. They are helpful for coding purposed because they contain information related to the records in the table. For example, the schema for the Groups table contains database columns of ID, NAME, STATUS, ACCESSTYPE, and TYPE. It also contains non-database columns of MEMBERLIST and PRIVILEGES, which are lists of the members belonging to the group and the privileges attributed, respectively.

Helpful schema information (the fieldname is in bold):

Folders table

TREELIST(CFolder, 0, subfolders, DATATYPE_OBLISTEX)

Groups table

LIST(CGroup, 0, memberList, DATATYPE_OBLISTEX)

LIST(CGroup, 0, privileges, DATATYPE_OBLISTEX)

Notifications table

LIST(CNotification, 0, subscribers, DATATYPE_OBLISTEX)

Projects table

TREELIST(CProject, 0, subprojects, DATATYPE_OBLISTEX)

Selections table

LIST(CSelection, 0, projSelections, DATATYPE_OBLISTEX)

States table

LIST(CState, 0, transtriggerstates, DATATYPE_OBLISTEX)

Transitions table

LIST(CTransition, 0, properties, DATATYPE_OBLISTEX)

LIST(CTransition, 0, issueTypes, DATATYPE_OBLISTEX)

LIST(CTransition, 0, projectTransitions, DATATYPE_OBLISTEX)

LIST(CTransition, 0, transtriggers, DATATYPE_OBLISTEX)

Users table

LIST(CUser, 0, memberList, DATATYPE_OBLISTEX)

LIST(CUser, 0, privileges, DATATYPE_OBLISTEX)

Workflows table

TREELIST(CWorkflow, 0, subworkflows, DATATYPE_OBLISTEX)

How can the API be used?

The TeamShare API can be used for a variety of tasks. The following list of tasks corresponds directly to the individual methods defined in the TSServer class. Any of the methods can be used in combination to accomplish larger tasks.

Method	Description
AddField	Use to add a custom field to the following tables: Cases, Companies, Contacts, Incidents, Merchandise, Problems, Products, Resolutions, and Service Agreements
AddRecord	Add a new record to any table (i.e. new issues, incidents, users, groups, projects, folders, companies, contacts, establish privileges, create reports, etc.
BuildFieldList	Obtain a list of fields defined for any given table.
DeleteRecord	Delete any record from any table
GetConnectionInfo	Obtain information about the database connection
GetDbInfo	Obtain information about the database (i.e. version number, etc.)
GetInt	Obtain the numeric value of a column in any table
GetLastErrorMessage	Obtain the last error that occurred
GetSchema	Obtain the table schema for any table
GetString	Obtain the string value of any column in any table
GetSubmitTransition	Obtain the id value for the submit transition
HasPrivilege	Verify a user’s privileges
MoveFolder	Move a folder to a new place in the hierarchy
MoveProject	Move a project to a new place in the hierarchy
ReadAllRecords	Obtain all the records from any table
ReadAttachmentList	Obtain a list of the attachments to a given item
ReadChangeList	Obtain a list of changes made to any issue or incident. It’s possible to order the changes from newest to oldest. The default order is oldest to newest.
ReadFolderItems	Obtain a list of the items currently in a given folder
ReadFolderList	Obtain a list of folders defined for a given user
ReadProjectSelectionList	Obtain a list of selections for a given project
ReadProjectTransitionList	Obtain a list of transitions available for a given project
ReadPropertyList	Obtain a list of property records for a given transition
ReadRecord	Obtain one record from any table
ReadRecordForId	Obtain a record based on a given id value
ReadRecordListWithWhere	Obtain a list of records from a given table based on a set of conditions
ReadRecordWithWhere	Obtain one record from a given table based on a set of conditions
ReadReport	Obtain a report based on the name
ReadReportList	Obtain a list of reports available to a given user based on their privileges
ReadSelectionList	Obtain a list of selection values defined for a given field and that are available to a given project
ReadStateList	Obtain a list of states that are available to a given project
ReadTransitionList	Obtain a list of transitions and the fields they use / that are available
ReadUserDefinedFields	Obtain a list of the user-defined fields for a given table
ReadUserListForPrivilege	Obtain privilege information for a given user
ReadUserSelectionList	Obtain a list of selection field values available to a given user for a given project

Method	Description
ReadVCActions	Obtain a list of Version Control activity for a given issue
ReadVCActionsForModule	Obtain a list of Version Control activity for a given user
SetExitUrl	Sets the exit URL string stored in the SystemInfo table
Submit	Submit a new Issue or Incident to the database
Transition	Transition an existing Issue or Incident
UpdateList	Update a list of records for a given table; could be used to mass transition issues or incidents, or for importing a list of users instead of individually creating them
UpdateRecord	Update a single record in a table.
ValidateUser	Validate the login id for a given user, returns TRUE if the login id and password are valid
ValidateVersion	Validate the version of API against the version of TeamTrack installed on the webserver for compatibility

TSAPI objects / classes –

API Object Hierarchy ~

TSObject

|_____ TSField

|_____ TSRecord

|_____ TSSchema

|_____ TSString

TSList

|_____ TSFieldList

|_____ TSRecordList

|_____TSTreeList

TSPosition

TSServer

CSocket (MFC)

|_____ TSSocket

TSAPI Object Definition

TSField

This class represents a single field of a record. This generic field object will contain either a text string, an integer, or a double, depending on the datatype of the field.

Methods

void Copy (TSObject *newObj)

- Creates a copy of the TSField object

TSString DumpSchema (TSString indentation)

- Strings together the field name, datatype, schematype, and field type values. For debugging purposes.

TSObject * Duplicate()

- Creates a new instance of the TSField object and copies TSField (this) into the new instance

TSString FieldTypeToName (int fieldType)

- Obtains the string equivalent of the integer value for field type

TSObject * NewObject()

- Creates a new instance of TSField object and returns a pointer to the new instance

int Receive(TSSocket *socket, TSServer *server)

- Receives the incoming data off the socket; different handling based on datatype

int ReceiveSchema (TSSocket *socket)

- Gets the schematype, datatype, field type, and field name off the socket

int SocketString (TSString &str)

- Converts variables of different datatypes to string values.

TSString StringDump(int recursive, TSString indentation)

- Dump the values of the field into a string

TSField()

- Constructor method; initializes the variables of the TSField object.

TSField (const TSField &/*field*/)

- Overloaded constructor method to pass in a TSField object

~ TSField()

- Destructor method. Calls the appropriate methods to destroy the list objects that were created to support this class.

Member Variables

Int schemaType

Char fieldName[64]

Int dataType

Int fieldType (For use if this is a field of the Fields table)

Field data. One of these variables will contain data. The others will be 0 or NULL.

Char *charValue

Int charValueMax

Int intValue

Double doubleValue

TSRecordList *recordList

FieldType will be populated with the TS_FLDTYPE_X values defined in TSDef.h, if reading from the TS_FIELDS table. If reading from any other table, fieldType will be null and dataType will be populated with the TS_DATATYPE_X values defined in TSDef.h. The values indicate the type of data the field contains, for example, integer value, string value, boolean value, etc.
TSFieldList

This class is a descendant of the TSList class. It is used to contain a list of TSField objects.

Methods

TSString DumpSchema (TSString indentation)

- Dumps the contents of the field list object into a string; for debugging purposes

TSField * FindFieldByName(const char *fieldName, int fieldType)

- Obtains the field (TSField) from the list based on the name and field type that are passed.

TSField * GetAt (TSPosition *pos)

- Obtains the field from the list at the position passed.

Member Variables

None

TSList

This class is the base class from which all types of lists inherit.

Methods

int AddHead(TSObject *object)

- Adds an element to the beginning of the list

int AddTail(TSObject *object)

- Adds an element to the end of the list

int Copy(TSList *newList)

- Copies the contents this list into newList

int Duplicate(TSList *newList)

- Duplicate the contents of this list into the new list

void EmptyAndDestroyList()

- Delete the list object

void EmptyList()

- Iterates through the list (array) to remove each item

TSPosition * Get(int index)

- Moves to the next item in the list

TSObject * GetAt(TSPosition *pos)

- Obtains the item in the list at the index passed

TSPosition * GetFirst()

- Obtains the first item in the list

TSPosition * GetLast()

- Obtains the last item in the list

TSPosition * GetNext(TSPosition *pos)

- Obtains the next item in the list

TSPosition * GetPrev(TSPosition *pos)

- Obtains the previous item in the list

int InsertAfter(TSPosition *pos, TSObject *object)

- Adds an item to the list after the position passed

int InsertBefore(TSPosition *pos, TSObject *object)

- Adds an item to the list before the position passed

int Length()

- Obtains the length of the list

TSObject* RemoveObject(TSObject *pObject)

- Removes the actual object instance

TSObject * RemoveObject(TSPosition *pos)

-