My co-workers and I have documented several ways of getting information out of TestTrack including using the TestTrack Software Developer Kit (SDK).  For this entry, I’d like to focus on a tried and true friend: SQL. The Structured Query Language has been used for over 25 years and it still is a good tool to have.  SQL Server, Oracle, mySQL, and PostgreSQL all have a query tool to easily retrieve data.  The TestTrack Native database has a, ODBC driver so you can use products like Microsoft Access to query.

Before I go any further, I have to give this warning: Only use SQL to query data; never update or insert new records into a TestTrack database using SQL.  There is a long and complicated reason for this but trust me, updating data via SQL is bad.

Using a good querying tool, you can connect to your TestTrack database and see all the tables and indexes we use. With some tables, like DEFECTS, TESTCASE, and TESTRUN, it is pretty obvious what data is stored within. Others, like CSTTREVTVAL, take a while to understand. To help define how these tables interact, Seapine has created the TestTrack Database Layout reference document that includes an Entity Relation Diagram (ERD) for each table group and a description of each table and field.  This document is part of the TestTrack ODBC Guide which you can download here. Make sure that the document matches your database version.

TestTrack has a very flexible architecture and you pay for that flexibility when you write SQL queries. Data you think might be stored on one or two tables might really be stored on five or six tables. Here are a few tips I can offer:

1. When joining data from two tables always include a condition where ProjectID are the same. All of your TestTrack project data is stored in the same set of tables so the ProjectID is the field that separates the data you want from other projects.
2. Most of the relationships between tables may not have data so be prepared for a lot of LEFT JOINS in your queries. It complicates your queries but it is a fact of life in the TestTrack database.

Below is an example of a query of basic defect information using T-SQL (Microsoft’s SQL Server query language). You can find other queries in the Seapine Labs Wiki here.

SELECT ISNULL(d.Summary, '') AS 'Summary', ISNULL(disp.Descriptor, '') AS 'Disposition',
       ISNULL(type.Descriptor, '') AS 'Type', ISNULL(prior.Descriptor, '') AS 'Priority',
       ISNULL(prod.Descriptor, '') AS 'Product', ISNULL(comp.Descriptor, '') AS 'Component',
       ISNULL(d.Reference, '') AS 'Reference', ISNULL(sevr.Descriptor, '') AS 'Severity',
       ISNULL(CONVERT(varchar(12), d.dateEnter, 101), '') AS 'Date Entered',
       ISNULL(enteredby.FirstName + ' ' + enteredby.LastName, '') AS 'Entered By'
  FROM DEFECTS AS d
       LEFT OUTER JOIN (SELECT ProjectID, idRecord, Descriptor
                          FROM FLDDISPO) AS disp
               ON d.ProjectID = disp.ProjectID AND d.idDisposit = disp.idRecord
       LEFT OUTER JOIN (SELECT ProjectID, idRecord, Descriptor
                          FROM FLDTYPE) AS type
               ON d.ProjectID = type.ProjectID AND d.idType = type.idRecord
       LEFT OUTER JOIN (SELECT ProjectID, idRecord, Descriptor
                          FROM FLDPRIOR) AS prior
               ON d.ProjectID = prior.ProjectID AND d.idPriority = prior.idRecord
       LEFT OUTER JOIN (SELECT ProjectID, idRecord, Descriptor
                          FROM FLDPROD) AS prod
               ON d.ProjectID = prod.ProjectID AND d.idProduct = prod.idRecord
       LEFT OUTER JOIN (SELECT ProjectID, idRecord, Descriptor
                          FROM FLDCOMP) AS comp
               ON d.ProjectID = comp.ProjectID AND d.idCompon = comp.idRecord
       LEFT OUTER JOIN (SELECT ProjectID, idRecord, Descriptor
                          FROM FLDSEVER) AS sevr
               ON d.ProjectID = sevr.ProjectID AND d.idSeverity = sevr.idRecord
       LEFT OUTER JOIN (SELECT ProjectID, idRecord, FirstName, LastName
                          FROM USERS) AS enteredby
               ON d.ProjectID = enteredby.ProjectID AND d.idEnterBy = enteredby.idRecord

Related posts:

1. Common and Complex T-SQL Statements for Custom Reporting
2. TestTrack Native Database Dashboard
3. Automation Rules Report
4. TestTrack MySQL Dashboard
5. Dynamic SQL Queries