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

     1  BEGIN;
     2    CREATE TABLE task_caches (
     3      id serial,
     4      worker_name text,
     5      job_id integer,
     6      step_name text NOT NULL,
     7      path text NOT NULL,
     8      PRIMARY KEY ("id")
     9    );
    10    CREATE UNIQUE INDEX task_caches_job_id_step_name_path_uniq
    11      ON task_caches (job_id, step_name, path);
    12  
    13    CREATE INDEX task_caches_job_id ON task_caches USING btree (job_id);
    14  
    15    ALTER TABLE ONLY task_caches
    16      ADD CONSTRAINT task_caches_job_id_fkey FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE;
    17  
    18    ALTER TABLE worker_task_caches
    19      ADD COLUMN task_cache_id INTEGER;
    20  
    21    ALTER TABLE worker_task_caches
    22      ADD CONSTRAINT worker_task_caches_task_cache_fkey FOREIGN KEY (task_cache_id) REFERENCES task_caches (id) ON DELETE CASCADE;
    23    CREATE INDEX worker_task_caches_task_cache_id ON worker_task_caches (task_cache_id);
    24  
    25    WITH ins AS (
    26      INSERT INTO task_caches (worker_name, job_id, step_name, path)
    27      SELECT DISTINCT wtc.worker_name, wtc.job_id, wtc.step_name, wtc.path
    28      FROM worker_task_caches wtc
    29      ON CONFLICT DO NOTHING
    30      RETURNING *
    31    )
    32    UPDATE worker_task_caches wtc
    33    SET task_cache_id = ins.id
    34    FROM ins
    35    WHERE ins.job_id = wtc.job_id
    36      AND ins.step_name = wtc.step_name
    37      AND ins.path = wtc.path;
    38  
    39    DROP INDEX worker_task_caches_uniq;
    40    CREATE UNIQUE INDEX worker_task_caches_worker_name_task_cache_id_uniq
    41      ON worker_task_caches (worker_name, task_cache_id);
    42  
    43  
    44    ALTER TABLE worker_task_caches
    45      DROP COLUMN job_id,
    46      DROP COLUMN step_name,
    47      DROP COLUMN path;
    48  
    49    ALTER TABLE task_caches
    50      DROP COLUMN worker_name;
    51  
    52  COMMIT;