From Seapine Labs

Contents 1 Overview 2 Defect Queries 2.1 Select basic defect information 2.2 Select defect reported by information 2.3 Select defect custom field information 2.4 Select defect event history 2.5 Select defect Fix event custom field information 3 Test Case Queries 3.1 Select basic test case information 4 Test Run Queries 4.1 Select basic test run information 4.2 Select test run status for metrics 4.3 Select test run variant information 4.4 Select test run folder information

[edit] Overview

Here you'll find a number of T-SQL queries to fetch various information from the TestTrack database, along with explanations as necessary. The most common use of SQL access is dashboard reporting, but there are a number of reasons you might want to query the database directly (advanced statistical analysis for example).

[edit] Defect Queries

[edit] Select basic defect information

select
    isnull(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 d
    left join (
      select projectid, idrecord, descriptor from flddispo
      ) disp
      on d.projectid = disp.projectid and d.idDisposit = disp.idRecord
    left join (
      select projectid, idrecord, descriptor from fldtype
      ) [type]
      on d.projectid = [type].projectid and d.idType = [type].idRecord
    left join (
      select projectid, idrecord, descriptor from fldprior
      ) prior
      on d.projectid = prior.projectid and d.idPriority = prior.idRecord
    left join (
      select projectid, idrecord, descriptor from fldprod
      ) prod
      on d.projectid = prod.projectid and d.idProduct = prod.idRecord
    left join (
      select projectid, idrecord, descriptor from fldcomp
      ) comp
      on d.projectid = comp.projectid and d.idCompon = comp.idRecord
    left join (
      select projectid, idrecord, descriptor from fldsever
      ) sevr
      on d.projectid = sevr.projectid and d.idSeverity = sevr.idRecord
    left join (
      select projectid, idrecord, firstname, lastname from users
      ) enteredby
      on d.projectid = enteredby.projectid and d.idEnterBy = enteredby.idRecord

[edit] Select defect reported by information

select
    d.defectnum as 'Defect #',
    isnull(d.summary, '') as 'Summary',
    isnull(foundby.firstname + ' ' + foundby.lastname, '') as 'Found By',
    convert(varchar(12), rep.datefound, 101) as 'Date Found',
    isnull(verfnd.descriptor, '') as 'Found in Version',
    isnull(repro.descriptor, '') as 'Reproducable',
    isnull(rep.descrptn, '') as 'Desription',
    isnull(rep.reprosteps, '') as 'Steps To Reproduce',
    case rep.TstConType
      when 2 then isnull(testcnf.sysname, '')
      else 'User''s Test Config'
    end as 'Test Config',
    isnull(rep.otherhwsw, '') as 'Other Hw/Sw'
  from defects d, reportby rep
    left join (
      select projectid, idrecord, firstname, lastname from users
      ) foundby
      on rep.projectid = foundby.projectid and rep.idFoundBy = foundby.idRecord
    left join (
      select projectid, idrecord, descriptor from fldrepro
      ) repro
      on rep.projectid = repro.projectid and rep.idReprod = repro.idRecord
    left join (
      select projectid, idrecord, descriptor from fldversn
      ) verfnd
      on rep.projectid = verfnd.projectid and rep.versnfound = verfnd.idRecord
    left join (
      select projectid, idrecord, sysname from sysconf
      ) testcnf
      on rep.projectid = testcnf.projectid and rep.idConfig = testcnf.idRecord
  where
    rep.iddefrec = d.idRecord
    and rep.projectid = d.projectid

[edit] Select defect custom field information

4 custom fields, one for each supported type. You'll need to modify this query to match the fields you have. Additionally, you could compare by label or field code instead of id, but that information can be changed which would break your script.

select
    d.defectnum as 'Defect #',
    isnull(d.summary, '') as 'Summary',
    isnull(fld1.custvalue, '') as 'Text Field',
    isnull(fld2.custvalue, '') as 'Timestamp Field',
    isnull(fld3.custvalue, 'N') as 'Checkbox Field',
    isnull(fld4.descriptor, '') as 'Drop-down Field'
  from defects d
    left join (
      select val.projectid, val.parentid, val.custvalue from custmval val, flddfntn fld where fld.entitytype = 1684431732 and fld.projectid = val.projectid and fld.fieldid = 302 and fld.idrecord = val.idcustrec
      ) fld1
      on d.projectid = fld1.projectid and d.idrecord = fld1.parentid
    left join (
      select val.projectid, val.parentid, val.custvalue from custmval val, flddfntn fld where fld.entitytype = 1684431732 and fld.projectid = val.projectid and fld.fieldid = 303 and fld.idrecord = val.idcustrec
      ) fld2
      on d.projectid = fld2.projectid and d.idrecord = fld2.parentid
    left join (
      select val.projectid, val.parentid, val.custvalue from custmval val, flddfntn fld where fld.entitytype = 1684431732 and fld.projectid = val.projectid and fld.fieldid = 304 and fld.idrecord = val.idcustrec
      ) fld3
      on d.projectid = fld3.projectid and d.idrecord = fld3.parentid
    left join (
      select val.projectid, val.parentid, ddval.descriptor from fldcustm ddval, custmval val, flddfntn fld where fld.entitytype = 1684431732 and fld.projectid = val.projectid and fld.projectid = ddval.projectid and fld.fieldid = 305 and fld.idrecord = val.idcustrec and cast(val.custvalue as varchar) = ddval.idRecord
      ) fld4
      on d.projectid = fld4.projectid and d.idrecord = fld4.parentid

[edit] Select defect event history

select
    d.defectnum as 'Defect #',
    isnull(d.summary, '') as 'Summary',
    isnull(e.name, '') as 'Event',
    isnull(u.firstname + ' ' + u.lastname, '') as 'Applied By',
    convert(varchar, evt.dateevent, 100) as 'Date Applied',
    isnull(evt.notes, '') as 'Notes',
    isnull(rslt.name, '<no change>') as 'Resulting State'
  from users u, defects d, events e, defectevts evt
    left join (
      select projectid, idrecord, name from states
      ) rslt
      on evt.projectid = rslt.projectid and evt.rsltstate = rslt.idrecord
  where
    d.projectid = evt.projectid
    and d.projectid = e.projectid
    and d.projectid = u.projectid
    and d.idrecord = evt.parentid
    and evt.evtdefid = e.idrecord
    and evt.iduser = u.idrecord
order by
    d.idrecord, evt.dateevent, evt.ordernum

[edit] Select defect Fix event custom field information

Fetch the Fix Resolution information for any defect with an historic Fix event.

select
    d.defectnum as 'Defect #',
    isnull(d.summary, '') as 'Summary',
    isnull(e.name, '') as 'Event',
    isnull(u.firstname + ' ' + u.lastname, '') as 'Fixed By',
    convert(varchar, evt.dateevent, 100) as 'Date Fixed',
    isnull(evt.notes, '') as 'Fix Notes',
    isnull(fixres.descriptor, '<n/a>') as 'Resulting State'
  from users u, defects d, events e, defectevts evt
    left join (
      select val.projectid, val.iddefevt, ddval.descriptor from fldcustm ddval, cstdevtval val, flddfntn fld where fld.entitytype = 1684431732 and fld.projectid = val.projectid and fld.projectid = ddval.projectid and fld.fieldid = 303 and fld.idrecord = val.idcustrec and cast(val.custvalue as varchar) = ddval.idRecord
      ) fixres
      on evt.projectid = fixres.projectid and evt.idrecord = fixres.iddefevt
  where
    d.projectid = evt.projectid
    and d.projectid = e.projectid
    and d.projectid = u.projectid
    and d.idrecord = evt.parentid
    and evt.evtdefid = e.idrecord
    and evt.iduser = u.idrecord
    and e.name = 'Fix'
order by
    d.idrecord, evt.dateevent, evt.ordernum

[edit] Test Case Queries

[edit] Select basic test case information

select
    tc.tstcasenum as 'TC #',
    isnull(tc.summary, '') as 'Summary',
    (select s.name from states s, tcevts tce where tc.projectid = tce.projectid and tc.projectid = s.projectid and tc.idrecord = tce.parentid and tce.rsltstate = s.idrecord and tce.ordernum = (select max(ordernum) from tcevts where parentid = tc.idrecord and rsltstate != 0)) as 'Status',
    isnull(convert(varchar(12), tc.datecreate, 101), '') as 'Date Created',
    isnull(tctype.descriptor, '') as 'Test Type',
    case isautotest when 1 then 'Yes' else 'No' end as 'Automated?',
    cast(EstTime/60.00 as decimal(12,1)) as 'Est Testing Time (mins)',

    -- Default custom fields for test cases
    isnull(dsc.custvalue, '') as 'Description',
    isnull(scope.custvalue, '') as 'Scope',
    isnull(prec.custvalue, '') as 'Pre-Conditions',
    isnull(exprs.custvalue, '') as 'Expected Results',
    isnull(steps, '') as 'Steps'
  from testcase tc
    left join (
      select projectid, idrecord, descriptor from fldtotp
    ) tctype
    on tc.projectid = tctype.projectid and tc.idtype = tctype.idrecord
    left join (
      select val.projectid, val.parentid, val.custvalue from custtcval val, flddfntn fld where fld.entitytype = 1953723491 and fld.projectid = val.projectid and fld.fieldid = 301 and fld.idrecord = val.idcustrec
    ) dsc
    on tc.projectid = dsc.projectid and tc.idrecord = dsc.parentid
    left join (
      select val.projectid, val.parentid, val.custvalue from custtcval val, flddfntn fld where fld.entitytype = 1953723491 and fld.projectid = val.projectid and fld.fieldid = 302 and fld.idrecord = val.idcustrec
    ) scope
    on tc.projectid = scope.projectid and tc.idrecord = scope.parentid
    left join (
      select val.projectid, val.parentid, val.custvalue from custtcval val, flddfntn fld where fld.entitytype = 1953723491 and fld.projectid = val.projectid and fld.fieldid = 303 and fld.idrecord = val.idcustrec
    ) prec
    on tc.projectid = prec.projectid and tc.idrecord = prec.parentid
    left join (
      select val.projectid, val.parentid, val.custvalue from custtcval val, flddfntn fld where fld.entitytype = 1953723491 and fld.projectid = val.projectid and fld.fieldid = 304 and fld.idrecord = val.idcustrec
    ) exprs
    on tc.projectid = exprs.projectid and tc.idrecord = exprs.parentid

[edit] Test Run Queries

[edit] Select basic test run information

select
    tr.tstrunnum as 'TR #',
    tc.tstcasenum as 'TC #',
    isnull(tr.summary, '') as 'Summary',
    isnull(tr.prbstate, '') as 'Problem Statement',
    isnull(convert(varchar(12), tr.datecreate, 101), '') as 'Date Created',
    isnull(trset.descriptor, '') as 'TestRun Set',
    case tr.isautotest when 1 then 'Automated Test' else 'Non-automated Test' end as 'Test Type',
    cast(tr.EstTime/60.00 as decimal(12,1)) as 'Est Testing Time (mins)',

    -- Default custom fields for test runs
    isnull(dsc.custvalue, '') as 'Description',
    isnull(scope.custvalue, '') as 'Scope',
    isnull(prec.custvalue, '') as 'Pre-Conditions',
    isnull(exprs.custvalue, '') as 'Expected Results'
  from testcase tc, testrun tr
    left join (
      select projectid, idrecord, descriptor from fldtrset
    ) trset
    on tr.projectid = trset.projectid and tr.idtrset = trset.idrecord
    left join (
      select val.projectid, val.parentid, val.custvalue from custtrval val, flddfntn fld where fld.entitytype = 1953723506 and fld.projectid = val.projectid and fld.fieldid = 301 and fld.idrecord = val.idcustrec
    ) dsc
    on tr.projectid = dsc.projectid and tr.idrecord = dsc.parentid
    left join (
      select val.projectid, val.parentid, val.custvalue from custtrval val, flddfntn fld where fld.entitytype = 1953723506 and fld.projectid = val.projectid and fld.fieldid = 302 and fld.idrecord = val.idcustrec
    ) scope
    on tr.projectid = scope.projectid and tr.idrecord = scope.parentid
    left join (
      select val.projectid, val.parentid, val.custvalue from custtrval val, flddfntn fld where fld.entitytype = 1953723506 and fld.projectid = val.projectid and fld.fieldid = 303 and fld.idrecord = val.idcustrec
    ) prec
    on tr.projectid = prec.projectid and tr.idrecord = prec.parentid
    left join (
      select val.projectid, val.parentid, val.custvalue from custtrval val, flddfntn fld where fld.entitytype = 1953723506 and fld.projectid = val.projectid and fld.fieldid = 304 and fld.idrecord = val.idcustrec
    ) exprs
    on tr.projectid = exprs.projectid and tr.idrecord = exprs.parentid
  where
    tr.tstcaserid = tc.idrecord

[edit] Select test run status for metrics

Select information on the set, variants and current status of test runs. Assumes Waiting is the default state. Filters by Variant shortname, to make things easier; just change to whatever the field labels are in your client.

select
    tc.tstcasenum as 'TC #',
    tr.tstrunnum as 'TR #',
    tr.summary as 'TR Summary',
    isnull(trset.descriptor, '') as 'TestRun Set',
    isnull(variantsOS.Descriptor, '') as 'OS',
    isnull(variantsDB.Descriptor, '') as 'DB',
    isnull((select s.name from states s where s.projectid = tr.projectid and s.entitytype = 1953723506 and s.idrecord = (select rsltstate from trevts tre where tre.projectid = tr.projectid and tre.parentid = tr.idrecord and tre.ordernum = (select max(ordernum) from trevts tre1 where tre1.projectid = tr.projectid and tre1.parentid = tr.idrecord and tre1.iduser != 4294967294))), 'Waiting') as 'Status'
  from testcase tc, testrun tr
    left join (
      select projectid, idrecord, descriptor from fldtrset
    ) trset
    on tr.projectid = trset.projectid and tr.idtrset = trset.idrecord
    left join (
      select val.projectid, val.parentid, fld.shortname, ddval.descriptor from fldcustm ddval, custtrval val, flddfntn fld where fld.entitytype = 1668249206 and fld.projectid = val.projectid and fld.projectid = ddval.projectid and fld.idrecord = val.idcustrec and ddval.idRecord = cast(val.custvalue as varchar)
    ) variantsOS
    on tr.projectid = variantsOS.projectid and tr.idrecord = variantsOS.parentid and variantsOS.shortname = 'OS'
    left join (
      select val.projectid, val.parentid, fld.shortname, ddval.descriptor from fldcustm ddval, custtrval val, flddfntn fld where fld.entitytype = 1668249206 and fld.projectid = val.projectid and fld.projectid = ddval.projectid and fld.idrecord = val.idcustrec and ddval.idRecord = cast(val.custvalue as varchar)
    ) variantsDB
    on tr.projectid = variantsDB.projectid and tr.idrecord = variantsDB.parentid and variantsDB.shortname = 'DB'
  where
    tr.projectid = tc.projectid
    and tr.tstcaserid = tc.idrecord

[edit] Select test run variant information

Get a list of every test run and the Variants set for each.

select
    tr.tstrunnum as 'TR #',
    isnull(variants.shortname + ' - ' + variants.Descriptor, '') as 'Variant'
  from testrun tr
    left join (
      select val.projectid, val.parentid, fld.shortname, ddval.descriptor from fldcustm ddval, custtrval val, flddfntn fld where fld.entitytype = 1668249206 and fld.projectid = val.projectid and fld.projectid = ddval.projectid and fld.idrecord = val.idcustrec and ddval.idRecord = cast(val.custvalue as varchar)
    ) variants
    on tr.projectid = variants.projectid and tr.idrecord = variants.parentid
  order by tr.tstrunnum

[edit] Select test run folder information

Get a list of every test run and the associated folder(s).

select
    tr.tstrunnum as 'TR #',
    fldr.name as 'Folder'
  from testrun tr
    left join (
      select f.projectid, f.name, fi.entityid from fldritem fi, folder f where f.projectid = fi.projectid and f.idrecord = fi.folderid and fi.entitytype = 1953723506
    ) fldr
    on tr.projectid = fldr.projectid and tr.idrecord = fldr.entityid