Collaborator Database Queries

Collaborator has a robust, built-in Report tool, offering many selections and filtering options to gather the information that is important to your review process. However, there are times when the one option you are looking for isn't one of the available selections or filters. Here's where Collaborator's associated SQL database comes in for generating custom reports. Specific information on the database schema and table relationships are found here in the online documentation.

 

Additionally, SmartBear's Support pages for Collaborator offers many useful links to videos and other artifacts to help with your Collaborator deployment. 

 

I recently had a customer request for some assistance on verifying if specific commits had been associated with reviews. Collaborator has a report generation option for this specific request, but the request was based on some scripting they were doing to enhance their CI/CD process. I put together a quick general query that they can use as a basis for refining exactly what they need for their script. The SQL query below is used to look at your review IDs, and output a table showing the title, phase, and associated commits with those reviews. To get this report, I used the schema diagram to find my associated SQL tables and how they linked together, then used a few nested JOIN statements for the formatting I needed. Collaborator also provides SQL output scripts used to generate many of the reports in the Reports tab. These can be used as great starting points to develop the queries you need for your reports and data parsing, and how I got started on the development of the script for this request.

 

In short, take advantage of the information in the database to enhance your reporting and automated workflows.

 

Sample SQL query:

SELECT
   review.review_id AS `Review ID`
  ,review.review_title AS `Review Title`
  ,phase.phase_title AS `Phase`
  ,version.version_scmversionname AS `Commit ID`

FROM
   review
      INNER JOIN joinreviewchangelist
      ON review.review_id = joinreviewchangelist.joinreviewchangelist_reviewid
         INNER JOIN changelist
        ON joinreviewchangelist.joinreviewchangelist_changelistid = changelist.changelist_id
           INNER JOIN version
           ON changelist.changelist_id = version.version_changelistid
   INNER JOIN phase
   ON review.review_phaseid = phase.phase_id
ORDER BY
   review.review_id DESC

 

Sample output in MySQL Workbench: