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