Finding test-heavy projects and build configurations in TeamCity

teamcity-find-test-heavy-projects.md

Finding test-heavy build configurations and projects in TeamCity

Tip

These SQL queries should work for PostgreSQL and MySQL.

Top 10 projects by test count

WITH per_build AS (
    SELECT   build_id, COUNT(*) AS test_rows
    FROM     test_info
    GROUP BY build_id
),
     builds AS (
         SELECT build_id, build_type_id
         FROM   history
         UNION ALL
         SELECT r.build_id, r.build_type_id
         FROM   running r
         WHERE  NOT EXISTS (SELECT 1 FROM history h WHERE h.build_id = r.build_id)
         UNION ALL
         SELECT rbh.build_id, rbh.build_type_id
         FROM   removed_builds_history rbh
         WHERE  NOT EXISTS (SELECT 1 FROM history h WHERE h.build_id = rbh.build_id)
     )
SELECT     COALESCE(m.ext_id, b.build_type_id, '(orphaned: no build record)') AS build_configuration,
           COUNT(*)                                                          AS builds,
           SUM(pb.test_rows)                                                 AS test_rows,
           ROUND(AVG(pb.test_rows))                                          AS avg_rows_per_build
FROM       per_build pb
               LEFT JOIN  builds b            ON b.build_id = pb.build_id
               LEFT JOIN  build_type_mapping m ON m.int_id  = b.build_type_id AND m.main = 1
GROUP BY   1
ORDER BY   test_rows DESC
LIMIT      10;

Top 10 build configurations by test count

WITH per_build AS (
    SELECT   build_id, COUNT(*) AS test_rows
    FROM     test_info
    GROUP BY build_id
),
     builds AS (
         SELECT build_id, build_type_id
         FROM   history
         UNION ALL
         SELECT r.build_id, r.build_type_id
         FROM   running r
         WHERE  NOT EXISTS (SELECT 1 FROM history h WHERE h.build_id = r.build_id)
         UNION ALL
         SELECT rbh.build_id, rbh.build_type_id
         FROM   removed_builds_history rbh
         WHERE  NOT EXISTS (SELECT 1 FROM history h WHERE h.build_id = rbh.build_id)
     )
SELECT     COALESCE(m.ext_id, b.build_type_id, '(orphaned: no build record)') AS build_configuration,
           COUNT(*)                                                          AS builds,
           SUM(pb.test_rows)                                                 AS test_rows
FROM       per_build pb
               LEFT JOIN  builds b            ON b.build_id = pb.build_id
               LEFT JOIN  build_type_mapping m ON m.int_id  = b.build_type_id AND m.main = 1
GROUP BY   1
ORDER BY   test_rows DESC
LIMIT      10;