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

     1  BEGIN;
     2    CREATE TABLE versioned_resources (
     3        id integer NOT NULL,
     4        version text NOT NULL,
     5        metadata text NOT NULL,
     6        type text NOT NULL,
     7        enabled boolean DEFAULT true NOT NULL,
     8        resource_id integer,
     9        check_order integer DEFAULT 0 NOT NULL
    10    );
    11  
    12    CREATE SEQUENCE versioned_resources_id_seq
    13        START WITH 1
    14        INCREMENT BY 1
    15        NO MINVALUE
    16        NO MAXVALUE
    17        CACHE 1;
    18  
    19    ALTER SEQUENCE versioned_resources_id_seq OWNED BY versioned_resources.id;
    20  
    21    ALTER TABLE ONLY versioned_resources ALTER COLUMN id SET DEFAULT nextval('versioned_resources_id_seq'::regclass);
    22  
    23    ALTER TABLE ONLY versioned_resources
    24      ADD CONSTRAINT versioned_resources_pkey PRIMARY KEY (id);
    25  
    26    CREATE INDEX versioned_resources_resource_id_idx ON versioned_resources USING btree (resource_id);
    27  
    28    CREATE UNIQUE INDEX versioned_resources_resource_id_type_version ON versioned_resources USING btree (resource_id, type, md5(version));
    29  
    30    ALTER TABLE ONLY versioned_resources
    31      ADD CONSTRAINT fkey_resource_id FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE;
    32  
    33    CREATE TABLE build_inputs (
    34        build_id integer,
    35        versioned_resource_id integer,
    36        name text NOT NULL
    37    );
    38  
    39    CREATE INDEX build_inputs_build_id_idx ON build_inputs USING btree (build_id);
    40  
    41    CREATE INDEX build_inputs_build_id_versioned_resource_id ON build_inputs USING btree (build_id, versioned_resource_id);
    42  
    43    CREATE INDEX build_inputs_versioned_resource_id_idx ON build_inputs USING btree (versioned_resource_id);
    44  
    45    ALTER TABLE ONLY build_inputs
    46        ADD CONSTRAINT build_inputs_build_id_fkey FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE CASCADE;
    47  
    48    ALTER TABLE ONLY build_inputs
    49        ADD CONSTRAINT build_inputs_versioned_resource_id_fkey FOREIGN KEY (versioned_resource_id) REFERENCES versioned_resources(id) ON DELETE CASCADE;
    50  
    51    CREATE TABLE build_outputs (
    52        build_id integer,
    53        versioned_resource_id integer,
    54        explicit boolean DEFAULT false NOT NULL
    55    );
    56  
    57    CREATE INDEX build_outputs_build_id_idx ON build_outputs USING btree (build_id);
    58  
    59    CREATE INDEX build_outputs_build_id_versioned_resource_id ON build_outputs USING btree (build_id, versioned_resource_id);
    60  
    61    CREATE INDEX build_outputs_versioned_resource_id_idx ON build_outputs USING btree (versioned_resource_id);
    62  
    63    ALTER TABLE ONLY build_outputs
    64        ADD CONSTRAINT build_outputs_build_id_fkey FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE CASCADE;
    65  
    66    ALTER TABLE ONLY build_outputs
    67        ADD CONSTRAINT build_outputs_versioned_resource_id_fkey FOREIGN KEY (versioned_resource_id) REFERENCES versioned_resources(id) ON DELETE CASCADE;
    68  
    69    ALTER TABLE resources
    70      ADD COLUMN paused boolean DEFAULT false;
    71  COMMIT;