Ticket #984: ckan_upgrade_v2.sql

File ckan_upgrade_v2.sql, 8.0 KB (added by kindly, 3 years ago)

This upgrade works from ckan 1.3.2.

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