August 30, 2009
SQL Is Still Your Friend
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:Email sign up
- 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.
- 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.
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