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

     1  BEGIN;
     2  
     3    CREATE TABLE resource_config_scopes (
     4      "id" serial NOT NULL PRIMARY KEY,
     5      "resource_config_id" integer NOT NULL REFERENCES resource_configs (id) ON DELETE CASCADE,
     6      "resource_id" integer REFERENCES resources (id) ON DELETE CASCADE,
     7      "last_checked" timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00',
     8      "check_error" text
     9    );
    10  
    11    CREATE UNIQUE INDEX resource_config_scopes_resource_id_resource_config_id_uniq
    12    ON resource_config_scopes (resource_id, resource_config_id)
    13    WHERE resource_id IS NOT NULL;
    14  
    15    CREATE UNIQUE INDEX resource_config_scopes_resource_config_id_uniq
    16    ON resource_config_scopes (resource_config_id)
    17    WHERE resource_id IS NULL;
    18  
    19    ALTER TABLE resource_configs
    20      DROP COLUMN unique_versions_resource_id,
    21      DROP COLUMN last_checked,
    22      DROP COLUMN check_error;
    23  
    24    CREATE UNIQUE INDEX resource_configs_resource_cache_id_so_key
    25    ON resource_configs (resource_cache_id, source_hash);
    26  
    27    CREATE UNIQUE INDEX resource_configs_base_resource_type_id_so_key
    28    ON resource_configs (base_resource_type_id, source_hash);
    29  
    30    TRUNCATE TABLE resource_config_versions CASCADE;
    31  
    32    ALTER TABLE resource_config_versions
    33      DROP COLUMN resource_config_id,
    34      ADD COLUMN resource_config_scope_id integer NOT NULL REFERENCES resource_config_scopes (id) ON DELETE CASCADE,
    35      ADD CONSTRAINT "resource_config_scope_id_and_version_md5_unique" UNIQUE ("resource_config_scope_id", "version_md5");
    36  
    37    ALTER TABLE resources
    38      ADD COLUMN resource_config_scope_id integer REFERENCES resource_config_scopes (id) ON DELETE SET NULL;
    39  
    40  COMMIT;