github.com/pf-qiu/concourse/v6@v6.7.3-0.20201207032516-1f455d73275f/atc/db/migration/migrations/1520950708_add_tracked_by_to_builds.up.sql (about)

     1  BEGIN;
     2    ALTER TABLE builds ADD COLUMN tracked_by text;
     3  
     4    DROP MATERIALIZED VIEW transition_builds_per_job;
     5    DROP MATERIALIZED VIEW next_builds_per_job;
     6    DROP MATERIALIZED VIEW latest_completed_builds_per_job;
     7  
     8    CREATE MATERIALIZED VIEW latest_completed_builds_per_job AS
     9     WITH latest_build_ids_per_job AS (
    10             SELECT max(b_1.id) AS build_id
    11               FROM (builds b_1
    12                 JOIN jobs j ON ((j.id = b_1.job_id)))
    13              WHERE (b_1.status <> ALL (ARRAY['pending'::build_status, 'started'::build_status]))
    14              GROUP BY b_1.job_id
    15            )
    16     SELECT b.id,
    17        b.name,
    18        b.status,
    19        b.scheduled,
    20        b.start_time,
    21        b.end_time,
    22        b.engine,
    23        b.engine_metadata,
    24        b.completed,
    25        b.job_id,
    26        b.reap_time,
    27        b.team_id,
    28        b.manually_triggered,
    29        b.interceptible,
    30        b.nonce,
    31        b.public_plan,
    32        b.pipeline_id,
    33        b.tracked_by
    34       FROM (builds b
    35         JOIN latest_build_ids_per_job l ON ((l.build_id = b.id)))
    36      WITH NO DATA;
    37    CREATE UNIQUE INDEX latest_completed_builds_per_job_id ON latest_completed_builds_per_job USING btree (id);
    38    REFRESH MATERIALIZED VIEW latest_completed_builds_per_job;
    39  
    40    CREATE MATERIALIZED VIEW next_builds_per_job AS
    41     WITH latest_build_ids_per_job AS (
    42             SELECT min(b_1.id) AS build_id
    43               FROM (builds b_1
    44                 JOIN jobs j ON ((j.id = b_1.job_id)))
    45              WHERE (b_1.status = ANY (ARRAY['pending'::build_status, 'started'::build_status]))
    46              GROUP BY b_1.job_id
    47            )
    48     SELECT b.id,
    49        b.name,
    50        b.status,
    51        b.scheduled,
    52        b.start_time,
    53        b.end_time,
    54        b.engine,
    55        b.engine_metadata,
    56        b.completed,
    57        b.job_id,
    58        b.reap_time,
    59        b.team_id,
    60        b.manually_triggered,
    61        b.interceptible,
    62        b.nonce,
    63        b.public_plan,
    64        b.pipeline_id,
    65        b.tracked_by
    66       FROM (builds b
    67         JOIN latest_build_ids_per_job l ON ((l.build_id = b.id)))
    68      WITH NO DATA;
    69    CREATE UNIQUE INDEX next_builds_per_job_id ON next_builds_per_job USING btree (id);
    70    REFRESH MATERIALIZED VIEW next_builds_per_job;
    71  
    72    CREATE MATERIALIZED VIEW transition_builds_per_job AS
    73     WITH builds_before_transition AS (
    74             SELECT b_1.job_id,
    75                max(b_1.id) AS max
    76               FROM ((builds b_1
    77                 LEFT JOIN jobs j ON ((b_1.job_id = j.id)))
    78                 LEFT JOIN latest_completed_builds_per_job s ON ((b_1.job_id = s.job_id)))
    79              WHERE ((b_1.status <> s.status) AND (b_1.status <> ALL (ARRAY['pending'::build_status, 'started'::build_status])))
    80              GROUP BY b_1.job_id
    81            )
    82     SELECT DISTINCT ON (b.job_id) b.id,
    83        b.name,
    84        b.status,
    85        b.scheduled,
    86        b.start_time,
    87        b.end_time,
    88        b.engine,
    89        b.engine_metadata,
    90        b.completed,
    91        b.job_id,
    92        b.reap_time,
    93        b.team_id,
    94        b.manually_triggered,
    95        b.interceptible,
    96        b.nonce,
    97        b.public_plan,
    98        b.pipeline_id,
    99        b.tracked_by
   100       FROM (builds b
   101         LEFT JOIN builds_before_transition ON ((b.job_id = builds_before_transition.job_id)))
   102      WHERE (((builds_before_transition.max IS NULL) AND (b.status <> ALL (ARRAY['pending'::build_status, 'started'::build_status]))) OR (b.id > builds_before_transition.max))
   103      ORDER BY b.job_id, b.id
   104      WITH NO DATA;
   105    CREATE UNIQUE INDEX transition_builds_per_job_id ON transition_builds_per_job USING btree (id);
   106    REFRESH MATERIALIZED VIEW transition_builds_per_job;
   107  COMMIT;