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

     1  BEGIN;
     2  
     3    ALTER TABLE worker_task_caches
     4      ADD job_id integer,
     5      ADD step_name text,
     6      ADD path text;
     7  
     8    UPDATE worker_task_caches
     9    SET job_id=tc.job_id,
    10        step_name=tc.step_name,
    11        path=tc.path
    12    FROM task_caches tc
    13    WHERE worker_task_caches.task_cache_id=tc.id;
    14  
    15    ALTER TABLE worker_task_caches
    16      ALTER COLUMN step_name SET NOT NULL,
    17      ALTER COLUMN path SET NOT NULL;
    18  
    19    DROP INDEX task_caches_job_id_step_name_path_uniq;
    20  
    21    DROP INDEX worker_task_caches_worker_name_task_cache_id_uniq;
    22    CREATE UNIQUE INDEX worker_task_caches_uniq
    23      ON worker_task_caches (job_id, step_name, worker_name, path);
    24  
    25    CREATE INDEX worker_task_caches_job_id ON worker_task_caches USING btree (job_id);
    26  
    27    ALTER TABLE ONLY worker_task_caches
    28      ADD CONSTRAINT worker_task_caches_job_id_fkey FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE;
    29  
    30    ALTER TABLE worker_task_caches DROP CONSTRAINT worker_task_caches_task_cache_fkey;
    31    ALTER TABLE ONLY worker_task_caches
    32      DROP COLUMN task_cache_id;
    33  
    34    DROP TABLE task_caches;
    35  
    36  COMMIT;