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

     1  BEGIN;
     2    ALTER TABLE builds RENAME COLUMN schema TO engine;
     3    ALTER TABLE builds RENAME COLUMN private_plan TO engine_metadata;
     4  
     5    CREATE MATERIALIZED VIEW latest_completed_builds_per_job AS
     6     WITH latest_build_ids_per_job AS (
     7             SELECT max(b_1.id) AS build_id
     8               FROM (builds b_1
     9                 JOIN jobs j ON ((j.id = b_1.job_id)))
    10              WHERE (b_1.status <> ALL (ARRAY['pending'::build_status, 'started'::build_status]))
    11              GROUP BY b_1.job_id
    12            )
    13     SELECT b.id,
    14        b.name,
    15        b.status,
    16        b.scheduled,
    17        b.start_time,
    18        b.end_time,
    19        b.engine,
    20        b.engine_metadata,
    21        b.completed,
    22        b.job_id,
    23        b.reap_time,
    24        b.team_id,
    25        b.manually_triggered,
    26        b.interceptible,
    27        b.nonce,
    28        b.public_plan,
    29        b.pipeline_id,
    30        b.tracked_by
    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        b.tracked_by
    63       FROM (builds b
    64         JOIN latest_build_ids_per_job l ON ((l.build_id = b.id)))
    65      WITH NO DATA;
    66    CREATE UNIQUE INDEX next_builds_per_job_id ON next_builds_per_job USING btree (id);
    67    REFRESH MATERIALIZED VIEW next_builds_per_job;
    68  
    69    CREATE MATERIALIZED VIEW transition_builds_per_job AS
    70     WITH builds_before_transition AS (
    71             SELECT b_1.job_id,
    72                max(b_1.id) AS max
    73               FROM ((builds b_1
    74                 LEFT JOIN jobs j ON ((b_1.job_id = j.id)))
    75                 LEFT JOIN latest_completed_builds_per_job s ON ((b_1.job_id = s.job_id)))
    76              WHERE ((b_1.status <> s.status) AND (b_1.status <> ALL (ARRAY['pending'::build_status, 'started'::build_status])))
    77              GROUP BY b_1.job_id
    78            )
    79     SELECT DISTINCT ON (b.job_id) b.id,
    80        b.name,
    81        b.status,
    82        b.scheduled,
    83        b.start_time,
    84        b.end_time,
    85        b.engine,
    86        b.engine_metadata,
    87        b.completed,
    88        b.job_id,
    89        b.reap_time,
    90        b.team_id,
    91        b.manually_triggered,
    92        b.interceptible,
    93        b.nonce,
    94        b.public_plan,
    95        b.pipeline_id,
    96        b.tracked_by
    97       FROM (builds b
    98         LEFT JOIN builds_before_transition ON ((b.job_id = builds_before_transition.job_id)))
    99      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))
   100      ORDER BY b.job_id, b.id
   101      WITH NO DATA;
   102    CREATE UNIQUE INDEX transition_builds_per_job_id ON transition_builds_per_job USING btree (id);
   103    REFRESH MATERIALIZED VIEW transition_builds_per_job;
   104  COMMIT;