Ticket #984: ckan_upgrade.diff

File ckan_upgrade.diff, 8.7 KB (added by kindly, 3 years ago)

This is the sql that needs to run on ckan.net *after* db upgrade has run on version 1.3.1

Line 
1
2ALTER TABLE group_extra_revision
3        DROP CONSTRAINT group_extra_revision_pkey;
4
5ALTER TABLE group_revision
6        DROP CONSTRAINT group_revision_pkey;
7
8ALTER TABLE package_group_revision
9        DROP CONSTRAINT package_group_revision_pkey;
10
11ALTER TABLE group_extra
12        DROP CONSTRAINT group_extra_revision_fkey;
13
14ALTER TABLE group_extra_revision
15        DROP CONSTRAINT group_extra_revision_continuity_id_fkey;
16
17ALTER TABLE group_revision
18        DROP CONSTRAINT group_revision_name_key;
19
20ALTER TABLE package
21        DROP CONSTRAINT package_revision_id_fkey;
22
23ALTER TABLE package_extra
24        DROP CONSTRAINT package_extra_package_id_fkey;
25
26ALTER TABLE package_extra
27        DROP CONSTRAINT package_extra_revision_id_fkey;
28
29ALTER TABLE package_extra_revision
30        DROP CONSTRAINT package_extra_revision_continuity_id_fkey;
31
32ALTER TABLE package_extra_revision
33        DROP CONSTRAINT package_extra_revision_package_id_fkey;
34
35ALTER TABLE package_extra_revision
36        DROP CONSTRAINT package_extra_revision_revision_id_fkey;
37
38ALTER TABLE package_group
39        DROP CONSTRAINT package_group_package_id_fkey;
40
41ALTER TABLE package_group
42        DROP CONSTRAINT package_group_revision_fkey;
43
44ALTER TABLE package_group_revision
45        DROP CONSTRAINT package_group_revision_continuity_id_fkey;
46
47ALTER TABLE package_resource
48        DROP CONSTRAINT package_resource_package_id_fkey;
49
50ALTER TABLE package_resource_revision
51        DROP CONSTRAINT package_resource_revision_continuity_id_fkey;
52
53ALTER TABLE package_resource_revision
54        DROP CONSTRAINT package_resource_revision_package_id_fkey;
55
56ALTER TABLE package_revision
57        DROP CONSTRAINT package_revision_continuity_id_fkey;
58
59ALTER TABLE package_revision
60        DROP CONSTRAINT package_revision_revision_id_fkey;
61
62ALTER TABLE package_role
63        DROP CONSTRAINT package_role_package_id_fkey;
64
65ALTER TABLE package_search
66        DROP CONSTRAINT package_search_package_id_fkey;
67
68ALTER TABLE package_tag
69        DROP CONSTRAINT package_tag_package_id_fkey;
70
71ALTER TABLE package_tag
72        DROP CONSTRAINT package_tag_revision_id_fkey;
73
74ALTER TABLE package_tag
75        DROP CONSTRAINT package_tag_tag_id_fkey;
76
77ALTER TABLE package_tag_revision
78        DROP CONSTRAINT package_tag_revision_continuity_id_fkey;
79
80ALTER TABLE package_tag_revision
81        DROP CONSTRAINT package_tag_revision_package_id_fkey;
82
83ALTER TABLE package_tag_revision
84        DROP CONSTRAINT package_tag_revision_revision_id_fkey;
85
86ALTER TABLE package_tag_revision
87        DROP CONSTRAINT package_tag_revision_tag_id_fkey;
88
89ALTER TABLE rating
90        DROP CONSTRAINT rating_package_id_fkey;
91
92
93ALTER TABLE authorization_group_role
94        DROP COLUMN id;
95
96ALTER TABLE changeset
97        DROP COLUMN status;
98
99ALTER TABLE group_extra_revision
100        ALTER COLUMN revision_id SET NOT NULL;
101
102ALTER TABLE group_revision
103        ALTER COLUMN revision_id SET NOT NULL;
104
105ALTER TABLE harvesting_job
106        ALTER COLUMN status SET NOT NULL;
107
108ALTER TABLE migrate_version
109        ALTER COLUMN repository_id TYPE character varying(250) /* TYPE change - table: migrate_version original: character varying(255) new: character varying(250) */;
110
111ALTER TABLE package
112        ALTER COLUMN id DROP DEFAULT,
113        ALTER COLUMN name SET NOT NULL,
114        ALTER COLUMN license_id TYPE text /* TYPE change - table: package original: character varying(100) new: text */;
115
116ALTER TABLE package_extra
117        ALTER COLUMN id DROP DEFAULT;
118
119ALTER TABLE package_extra_revision
120        ALTER COLUMN id DROP DEFAULT;
121
122ALTER TABLE package_group_revision
123        ALTER COLUMN revision_id SET NOT NULL;
124
125ALTER TABLE package_resource
126        ALTER COLUMN id DROP DEFAULT;
127
128ALTER TABLE package_resource_revision
129        ALTER COLUMN id DROP DEFAULT;
130
131ALTER TABLE package_revision
132        DROP COLUMN download_url,
133        ALTER COLUMN id DROP DEFAULT,
134        ALTER COLUMN name SET NOT NULL,
135        ALTER COLUMN license_id TYPE text /* TYPE change - table: package_revision original: character varying(100) new: text */;
136
137ALTER TABLE package_tag
138        ALTER COLUMN id DROP DEFAULT;
139
140ALTER TABLE package_tag_revision
141        ALTER COLUMN id DROP DEFAULT;
142
143ALTER TABLE rating
144        ALTER COLUMN rating TYPE double precision /* TYPE change - table: rating original: real new: double precision */;
145
146ALTER TABLE revision
147        ALTER COLUMN id DROP DEFAULT;
148
149ALTER TABLE tag
150        ALTER COLUMN id DROP DEFAULT,
151        ALTER COLUMN name SET NOT NULL;
152
153update package_group_revision set continuity_id = id;
154
155delete from package_group_revision where continuity_id = '36e4722b-1a72-4627-ba8d-1368cf9245cd';
156
157ALTER TABLE group_extra_revision
158        ADD CONSTRAINT group_extra_revision_pkey PRIMARY KEY (id, revision_id);
159
160ALTER TABLE group_revision
161        ADD CONSTRAINT group_revision_pkey PRIMARY KEY (id, revision_id);
162
163ALTER TABLE package_group_revision
164        ADD CONSTRAINT package_group_revision_pkey PRIMARY KEY (id, revision_id);
165
166ALTER TABLE group_extra
167        ADD CONSTRAINT group_extra_revision_id_fkey FOREIGN KEY (revision_id) REFERENCES revision(id);
168
169ALTER TABLE group_extra_revision
170        ADD CONSTRAINT group_extra_revision_continuity_id_fkey FOREIGN KEY (continuity_id) REFERENCES group_extra(id);
171
172ALTER TABLE group_revision
173        ADD CONSTRAINT group_revision_continuity_id_fkey FOREIGN KEY (continuity_id) REFERENCES "group"(id);
174
175ALTER TABLE harvested_document
176        ADD CONSTRAINT harvested_document_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
177
178ALTER TABLE harvested_document
179        ADD CONSTRAINT harvested_document_source_id_fkey FOREIGN KEY (source_id) REFERENCES harvest_source(id);
180
181ALTER TABLE package
182        ADD CONSTRAINT package_revision_id_fkey FOREIGN KEY (revision_id) REFERENCES revision(id);
183
184ALTER TABLE package_extra
185        ADD CONSTRAINT package_extra_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
186
187ALTER TABLE package_extra
188        ADD CONSTRAINT package_extra_revision_id_fkey FOREIGN KEY (revision_id) REFERENCES revision(id);
189
190ALTER TABLE package_extra_revision
191        ADD CONSTRAINT package_extra_revision_continuity_id_fkey FOREIGN KEY (continuity_id) REFERENCES package_extra(id);
192
193ALTER TABLE package_extra_revision
194        ADD CONSTRAINT package_extra_revision_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
195
196ALTER TABLE package_extra_revision
197        ADD CONSTRAINT package_extra_revision_revision_id_fkey FOREIGN KEY (revision_id) REFERENCES revision(id);
198
199ALTER TABLE package_group
200        ADD CONSTRAINT package_group_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
201
202ALTER TABLE package_group
203        ADD CONSTRAINT package_group_revision_id_fkey FOREIGN KEY (revision_id) REFERENCES revision(id);
204
205ALTER TABLE package_group_revision
206        ADD CONSTRAINT package_group_revision_continuity_id_fkey FOREIGN KEY (continuity_id) REFERENCES package_group(id);
207
208ALTER TABLE package_resource
209        ADD CONSTRAINT package_resource_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
210
211ALTER TABLE package_resource_revision
212        ADD CONSTRAINT package_resource_revision_continuity_id_fkey FOREIGN KEY (continuity_id) REFERENCES package_resource(id);
213
214ALTER TABLE package_resource_revision
215        ADD CONSTRAINT package_resource_revision_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
216
217ALTER TABLE package_revision
218        ADD CONSTRAINT package_revision_continuity_id_fkey FOREIGN KEY (continuity_id) REFERENCES package(id);
219
220ALTER TABLE package_revision
221        ADD CONSTRAINT package_revision_revision_id_fkey FOREIGN KEY (revision_id) REFERENCES revision(id);
222
223ALTER TABLE package_role
224        ADD CONSTRAINT package_role_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
225
226ALTER TABLE package_search
227        ADD CONSTRAINT package_search_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
228
229ALTER TABLE package_tag
230        ADD CONSTRAINT package_tag_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
231
232ALTER TABLE package_tag
233        ADD CONSTRAINT package_tag_revision_id_fkey FOREIGN KEY (revision_id) REFERENCES revision(id);
234
235ALTER TABLE package_tag
236        ADD CONSTRAINT package_tag_tag_id_fkey FOREIGN KEY (tag_id) REFERENCES tag(id);
237
238ALTER TABLE package_tag_revision
239        ADD CONSTRAINT package_tag_revision_continuity_id_fkey FOREIGN KEY (continuity_id) REFERENCES package_tag(id);
240
241ALTER TABLE package_tag_revision
242        ADD CONSTRAINT package_tag_revision_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
243
244ALTER TABLE package_tag_revision
245        ADD CONSTRAINT package_tag_revision_revision_id_fkey FOREIGN KEY (revision_id) REFERENCES revision(id);
246
247ALTER TABLE package_tag_revision
248        ADD CONSTRAINT package_tag_revision_tag_id_fkey FOREIGN KEY (tag_id) REFERENCES tag(id);
249
250ALTER TABLE rating
251        ADD CONSTRAINT rating_package_id_fkey FOREIGN KEY (package_id) REFERENCES package(id);
252
253ALTER TABLE user_object_role
254        ADD CONSTRAINT user_object_role_authorized_group_id_fkey FOREIGN KEY (authorized_group_id) REFERENCES authorization_group(id);
255
256DROP SEQUENCE package_extra_id_seq;
257
258DROP SEQUENCE package_extra_revision_id_seq;
259
260DROP SEQUENCE package_id_seq;
261
262DROP SEQUENCE package_resource_id_seq;
263
264DROP SEQUENCE package_resource_revision_id_seq;
265
266DROP SEQUENCE package_revision_id_seq;
267
268DROP SEQUENCE package_tag_id_seq;
269
270DROP SEQUENCE package_tag_revision_id_seq;
271
272DROP SEQUENCE revision_id_seq;
273
274DROP SEQUENCE tag_id_seq;