1BEGIN; 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 110COMMIT; 111