Ticket #984: ckan_migration_fixes.sql

File ckan_migration_fixes.sql, 8.2 KB (added by kindly, 3 years ago)

Migration fixes.

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