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

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