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

     1  BEGIN;
     2  
     3    CREATE TABLE resource_config_versions (
     4        "id" serial NOT NULL PRIMARY KEY,
     5        "resource_config_id" integer NOT NULL REFERENCES resource_configs (id) ON DELETE CASCADE,
     6        "version" jsonb NOT NULL,
     7        "version_md5" text NOT NULL,
     8        "metadata" jsonb NOT NULL DEFAULT 'null',
     9        "check_order" integer NOT NULL DEFAULT 0
    10    );
    11  
    12    ALTER TABLE resource_config_versions
    13      ADD CONSTRAINT "resource_config_id_and_version_md5_unique" UNIQUE ("resource_config_id", "version_md5");
    14  
    15    CREATE TABLE resource_disabled_versions (
    16      "resource_id" integer NOT NULL REFERENCES resources (id) ON DELETE CASCADE,
    17      "version_md5" text NOT NULL
    18    );
    19  
    20    CREATE UNIQUE INDEX resource_disabled_versions_resource_id_version_md5_uniq
    21    ON resource_disabled_versions (resource_id, version_md5);
    22  
    23    INSERT INTO resource_disabled_versions (resource_id, version_md5)
    24    SELECT vr.resource_id, md5(vr.version)
    25    FROM versioned_resources vr
    26    WHERE NOT enabled
    27    ON CONFLICT DO NOTHING;
    28  
    29    ALTER TABLE resource_configs
    30      ADD COLUMN last_checked timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00',
    31      ADD COLUMN check_error text;
    32  
    33    ALTER TABLE resource_types
    34      ADD COLUMN check_error text;
    35  
    36    CREATE TABLE build_resource_config_version_inputs (
    37        "build_id" integer NOT NULL REFERENCES builds (id) ON DELETE CASCADE,
    38        "resource_id" integer NOT NULL REFERENCES resources (id) ON DELETE CASCADE,
    39        "version_md5" text NOT NULL,
    40        "name" text NOT NULL
    41    );
    42  
    43    CREATE UNIQUE INDEX build_resource_config_version_inputs_uniq
    44    ON build_resource_config_version_inputs (build_id, resource_id, version_md5, name);
    45  
    46    INSERT INTO build_resource_config_version_inputs (build_id, resource_id, version_md5, name)
    47    SELECT bi.build_id, vr.resource_id, md5(vr.version), bi.name
    48    FROM build_inputs bi, versioned_resources vr
    49    WHERE bi.versioned_resource_id = vr.id
    50    ON CONFLICT DO NOTHING;
    51  
    52    CREATE TABLE build_resource_config_version_outputs (
    53        "build_id" integer NOT NULL REFERENCES builds (id) ON DELETE CASCADE,
    54        "resource_id" integer NOT NULL REFERENCES resources (id) ON DELETE CASCADE,
    55        "version_md5" text NOT NULL,
    56        "name" text NOT NULL
    57    );
    58  
    59    CREATE UNIQUE INDEX build_resource_config_version_outputs_uniq
    60    ON build_resource_config_version_outputs (build_id, resource_id, version_md5, name);
    61  
    62    INSERT INTO build_resource_config_version_outputs (build_id, resource_id, version_md5, name)
    63    SELECT bo.build_id, vr.resource_id, md5(vr.version), r.name
    64    FROM build_outputs bo, versioned_resources vr, resources r
    65    WHERE bo.versioned_resource_id = vr.id AND vr.resource_id = r.id
    66    ON CONFLICT DO NOTHING;
    67  
    68    TRUNCATE TABLE next_build_inputs;
    69  
    70    ALTER TABLE next_build_inputs
    71      ADD COLUMN resource_config_version_id integer NOT NULL REFERENCES resource_config_versions (id) ON DELETE CASCADE,
    72      ADD COLUMN resource_id integer NOT NULL REFERENCES resources (id) ON DELETE CASCADE,
    73      DROP COLUMN version_id;
    74  
    75    CREATE INDEX next_build_inputs_resource_config_version_id ON next_build_inputs (resource_config_version_id);
    76  
    77    TRUNCATE TABLE independent_build_inputs;
    78  
    79    ALTER TABLE independent_build_inputs
    80      ADD COLUMN resource_config_version_id integer NOT NULL REFERENCES resource_config_versions (id) ON DELETE CASCADE,
    81      ADD COLUMN resource_id integer NOT NULL REFERENCES resources (id) ON DELETE CASCADE,
    82      DROP COLUMN version_id;
    83  
    84    CREATE INDEX independent_build_inputs_resource_config_version_id ON independent_build_inputs (resource_config_version_id);
    85  
    86    DROP INDEX resource_caches_resource_config_id_version_params_hash_key;
    87  
    88    ALTER TABLE resource_caches ALTER COLUMN version TYPE jsonb USING version::jsonb;
    89  
    90    CREATE UNIQUE INDEX resource_caches_resource_config_id_version_params_hash_uniq
    91    ON resource_caches (resource_config_id, md5(version::text), params_hash);
    92  
    93    ALTER TABLE worker_resource_config_check_sessions
    94      DROP COLUMN team_id;
    95  
    96    DELETE FROM worker_resource_config_check_sessions;
    97  
    98    CREATE UNIQUE INDEX worker_resource_config_check_sessions_uniq
    99    ON worker_resource_config_check_sessions (resource_config_check_session_id, worker_base_resource_type_id);
   100  
   101    ALTER TABLE resources
   102      DROP COLUMN last_checked,
   103      DROP CONSTRAINT resources_resource_config_id_fkey,
   104      ADD CONSTRAINT resources_resource_config_id_fkey FOREIGN KEY (resource_config_id) REFERENCES resource_configs(id) ON DELETE RESTRICT;
   105  
   106    ALTER TABLE resource_types
   107      DROP COLUMN last_checked,
   108      DROP COLUMN version;
   109  
   110  COMMIT;