1 CREATE INDEX artifactextrafldlmts_extrafieldid ON
2 artifact_extra_field_elements(extra_field_id);
4 CREATE INDEX artifactextrafielddata_artifactid ON
5 artifact_extra_field_data(artifact_id);
7 CREATE INDEX artifactextrafieldlist_groupartid ON
8 artifact_extra_field_list(group_artifact_id);
10 CREATE INDEX docdata_groupid ON doc_data (group_id,doc_group);
12 CREATE SEQUENCE artifact_extra_field_elements_element_id_seq;
13 ALTER TABLE artifact_extra_field_elements ALTER COLUMN
14 element_id SET DEFAULT nextval('artifact_extra_field_elements_element_id_seq');
15 DROP SEQUENCE artifact_group_selection_box_options_id_seq;
16 SELECT setval('artifact_extra_field_elements_element_id_seq',(SELECT
17 max(element_id) FROM artifact_extra_field_elements));
19 CREATE SEQUENCE artifact_extra_field_data_data_id_seq;
20 ALTER TABLE artifact_extra_field_data ALTER COLUMN
21 data_id SET DEFAULT nextval('artifact_extra_field_data_data_id_seq');
22 SELECT setval('artifact_extra_field_data_data_id_seq',(SELECT
23 max(data_id) FROM artifact_extra_field_data));
24 DROP SEQUENCE artifact_extra_field_data_id_seq;
26 CREATE SEQUENCE artifact_extra_field_list_extra_field_id_seq;
27 ALTER TABLE artifact_extra_field_list ALTER COLUMN
28 extra_field_id SET DEFAULT nextval('artifact_extra_field_list_extra_field_id_seq');
29 SELECT setval('artifact_extra_field_list_extra_field_id_seq',(SELECT
30 max(extra_field_id) FROM artifact_extra_field_list));
31 DROP SEQUENCE artifact_group_selection_box_list_id_seq;
34 ALTER TABLE artifact_counts_agg ADD CONSTRAINT
35 artifact_counts_agg_pkey primary key (group_artifact_id);
36 DROP INDEX artifactcountsagg_groupartid;
39 ALTER TABLE artifact_extra_field_elements DROP CONSTRAINT
40 artifact_group_selection_box_options_pkey;
41 ALTER TABLE artifact_extra_field_elements ADD CONSTRAINT
42 artifact_extra_field_elements_pkey primary key (element_id);
45 ALTER TABLE artifact_extra_field_list DROP CONSTRAINT
46 artifact_group_selection_box_list_pkey;
47 ALTER TABLE artifact_extra_field_list ADD CONSTRAINT
48 artifact_extra_field_list_pkey primary key (extra_field_id);
50 DROP INDEX artfile_artid;
52 DROP INDEX artgrouplist_groupid;
54 DROP INDEX arthistory_artid;
56 DROP INDEX artmessage_artid;
59 -- TODO DROP unnecessary sequence/id
61 ALTER TABLE artifact_monitor DROP CONSTRAINT artifact_monitor_pkey;
62 DROP INDEX artmonitor_artifactid;
63 ALTER TABLE artifact_monitor ADD CONSTRAINT artifact_monitor_pkey PRIMARY KEY (artifact_id,user_id);
64 CREATE INDEX artmonitor_useridartid ON artifact_monitor(user_id,artifact_id);
66 DROP INDEX artperm_groupartifactid;
68 CREATE INDEX cronhist_jobrundate ON cron_history(job,rundate);
70 DROP INDEX doc_group_doc_group;
73 -- TODO DROP unnecessary sequence/id
75 ALTER TABLE filemodule_monitor DROP CONSTRAINT filemodule_monitor_pkey;
76 DROP INDEX filemodule_monitor_id;
77 DROP INDEX filemodulemonitor_userid;
78 ALTER TABLE filemodule_monitor ADD CONSTRAINT filemodule_monitor_pkey PRIMARY KEY (filemodule_id,user_id);
79 CREATE INDEX filemodulemonitor_useridfilemoduleid ON filemodule_monitor (user_id,filemodule_id);
82 -- TODO DROP unnecessary sequence/id
84 ALTER TABLE forum_monitored_forums DROP CONSTRAINT forum_monitored_forums_pkey;
85 DROP INDEX forum_monitor_combo_id;
86 DROP INDEX forum_monitor_thread_id;
87 DROP INDEX forummonitoredforums_user;
88 ALTER TABLE forum_monitored_forums ADD CONSTRAINT forum_monitored_forums_pkey PRIMARY KEY (forum_id,user_id);
89 CREATE INDEX forummonitoredforums_useridforumid ON forum_monitored_forums(user_id,forum_id);
92 -- TODO DROP unnecessary sequence/id
94 ALTER TABLE forum_perm DROP CONSTRAINT forum_perm_id_key;
95 CREATE INDEX forumperm_useridgroupforumid ON forum_perm(user_id,group_forum_id);
96 ALTER TABLE forum_perm ADD CONSTRAINT forum_perm_pkey PRIMARY KEY (group_forum_id, user_id);
99 -- TODO DROP unnecessary sequence/id
101 ALTER TABLE forum_saved_place DROP CONSTRAINT forum_saved_place_pkey;
102 ALTER TABLE forum_saved_place ADD CONSTRAINT
103 forum_saved_place_pkey PRIMARY KEY (user_id,forum_id);
105 DROP INDEX frsdlfiletotal_fileid;
106 ALTER TABLE frs_dlstats_filetotal_agg DROP CONSTRAINT frs_dlstats_filetotal_agg_pkey;
107 ALTER TABLE frs_dlstats_filetotal_agg ADD CONSTRAINT
108 frs_dlstats_filetotal_agg_pkey PRIMARY KEY (file_id);
111 -- TODO investigate if group_plugin_id is needed at all
113 CREATE INDEX groupplugin_groupid ON group_plugin(group_id);
115 ALTER TABLE licenses DROP CONSTRAINT licenses_license_id_key CASCADE;
116 ALTER TABLE licenses ADD CONSTRAINT licenses_pkey PRIMARY KEY (license_id);
117 --groups fkey is dropped BY CASCADE
118 --"groups_license" FOREIGN KEY (license) REFERENCES licenses(license_id) MATCH FULL
119 ALTER TABLE groups ADD CONSTRAINT groups_license
120 FOREIGN KEY (license) REFERENCES licenses(license_id) MATCH FULL;
122 CREATE INDEX prdbdbs_groupid ON prdb_dbs(group_id);
123 CREATE INDEX prdbstates_stateid ON prdb_states(stateid);
126 -- TODO DROP unnecessary sequence/id
128 ALTER TABLE project_assigned_to DROP CONSTRAINT project_assigned_to_pkey;
129 DROP INDEX project_assigned_to_assigned_to;
130 DROP INDEX project_assigned_to_task_id;
131 --mop up duplicate ids
132 DELETE FROM project_assigned_to WHERE EXISTS (
133 SELECT * FROM (SELECT project_task_id,assigned_to_id,count(*) AS count FROM project_assigned_to
134 GROUP BY project_task_id,assigned_to_id ORDER BY count) ta WHERE ta.count > 1
135 AND ta.project_task_id=project_assigned_to.project_task_id);
136 ALTER TABLE project_assigned_to ADD CONSTRAINT
137 project_assigned_to_pkey PRIMARY KEY (project_task_id,assigned_to_id);
138 CREATE INDEX projectassigned_assignedtotaskid ON
139 project_assigned_to(assigned_to_id,project_task_id);
141 ALTER TABLE project_counts_agg ADD CONSTRAINT
142 project_counts_agg_pkey PRIMARY KEY (group_project_id);
145 -- TODO DROP unnecessary sequence/id
147 ALTER TABLE project_dependencies DROP CONSTRAINT project_dependencies_pkey;
148 DROP INDEX project_dependencies_task_id;
149 DROP INDEX project_is_dependent_on_task_id;
150 ALTER TABLE project_dependencies ALTER COLUMN link_type SET DEFAULT 'FS';
151 --mop up duplicate ids
152 DELETE FROM project_dependencies WHERE EXISTS (
153 SELECT * FROM (SELECT project_task_id,is_dependent_on_task_id,count(*) AS count
154 FROM project_dependencies
155 GROUP BY project_task_id,is_dependent_on_task_id ORDER BY count) ta WHERE ta.count > 1
156 AND ta.project_task_id=project_dependencies.project_task_id
157 AND ta.is_dependent_on_task_id=project_dependencies.is_dependent_on_task_id);
158 ALTER TABLE project_dependencies ADD CONSTRAINT project_dependencies_pkey
159 PRIMARY KEY(project_task_id,is_dependent_on_task_id);
160 CREATE INDEX projectdep_isdepon_projtaskid ON
161 project_dependencies(is_dependent_on_task_id,project_task_id);
163 DROP TABLE project_group_doccat;
164 DROP TABLE project_group_forum;
166 CREATE INDEX projectmsgs_projtaskidpostdate ON project_messages(project_task_id,postdate);
169 -- TODO DROP unnecessary sequence/id
171 ALTER TABLE project_perm DROP CONSTRAINT project_perm_id_key;
172 DROP INDEX projectperm_groupprojiduserid;
173 ALTER TABLE project_perm ADD CONSTRAINT project_perm_id_key PRIMARY KEY(group_project_id,user_id);
174 CREATE INDEX projectperm_useridgroupprojid ON project_perm(user_id,group_project_id);
177 DROP INDEX projectsumsagg_groupid;
178 --MAY HAVE TO RUN db_project_sums.php cronjob first
179 ALTER TABLE project_sums_agg ALTER type SET NOT NULL;
180 DELETE FROM project_sums_agg;
181 ALTER TABLE project_sums_agg ADD CONSTRAINT project_sums_agg_pkey PRIMARY KEY (group_id,type);
183 DROP INDEX project_task_group_project_id;
185 DROP INDEX projecttaskartifact_artifactid;
186 ALTER TABLE project_task_artifact ALTER project_task_id SET NOT NULL;
187 ALTER TABLE project_task_artifact ALTER artifact_id SET NOT NULL;
188 ALTER TABLE project_task_artifact ADD CONSTRAINT
189 project_task_artifact_pkey PRIMARY KEY (project_task_id,artifact_id);
190 CREATE INDEX projecttaskartifact_artidprojtaskid ON
191 project_task_artifact(artifact_id,project_task_id);
193 DROP INDEX projecttaskexternal_projtaskid;
194 ALTER TABLE project_task_external_order ADD CONSTRAINT
195 roject_task_external_order_pkey PRIMARY KEY (project_task_id);
197 --UNKNOWN IF CORRECT: project_weekly_metric
198 --UNKNOWN IF CORRECT: prweb_vhost
200 ALTER TABLE role DROP CONSTRAINT role_role_id_key CASCADE;
201 --NOTICE: drop cascades to constraint usergroup_roleid on table user_group
202 --NOTICE: drop cascades to constraint $1 on table role_setting
203 ALTER TABLE role ADD CONSTRAINT role_role_id_pkey PRIMARY KEY (role_id);
204 ALTER TABLE user_group ADD CONSTRAINT usergroup_roleid
205 FOREIGN KEY (role_id) REFERENCES role(role_id) MATCH FULL;
206 ALTER TABLE role_setting ADD CONSTRAINT rolesetting_roleroleid
207 FOREIGN KEY (role_id) REFERENCES role(role_id) ON DELETE CASCADE;
209 DROP INDEX rolesetting_roleidsectionid;
210 ALTER TABLE role_setting ADD CONSTRAINT role_setting_pkey
211 PRIMARY KEY (role_id,section_name,ref_id);
213 --skills_data ignored - to be dropped
214 --stats tables ignored - to be dropped
216 CREATE UNIQUE INDEX supportedlanguage_code ON supported_languages(language_code);
217 --TODO DROP supported_languages.filename
219 --NEED TO BE INVESTIGATED MORE THOROUGHLY
220 -- public | survey_questions | table | tperdue
221 -- public | survey_rating_aggregate | table | tperdue
222 -- public | survey_rating_response | table | tperdue
223 -- public | survey_responses | table | tperdue
224 DROP INDEX survey_responses_user_survey;
226 DROP INDEX troveagg_trovecatid;
227 --trove_agg - what is this?
228 DROP INDEX parent_idx;
229 CREATE INDEX trovecat_parentid ON trove_cat(parent);
230 DROP INDEX version_idx;
231 DROP INDEX root_parent_idx;
234 -- TODO DROP unnecessary sequence/id
236 ALTER TABLE trove_group_link DROP CONSTRAINT trove_group_link_pkey;
237 DROP INDEX trove_group_link_cat_id;
238 DROP INDEX trove_group_link_group_id;
239 CREATE INDEX trovegrouplink_groupidcatid ON trove_group_link(group_id,trove_cat_id);
240 ALTER TABLE trove_group_link ADD CONSTRAINT
241 trove_group_link_pkey PRIMARY KEY(trove_cat_id,group_id,trove_cat_version);
244 -- TODO DROP unnecessary sequence/id
246 ALTER TABLE trove_treesums DROP CONSTRAINT trove_treesums_pkey;
247 ALTER TABLE trove_treesums ADD CONSTRAINT trove_treesums_pkey PRIMARY KEY (trove_cat_id);
249 DROP INDEX user_diary_user;
250 DROP INDEX user_diary_date;
253 -- TODO DROP unnecessary sequence/id
255 ALTER TABLE user_diary_monitor DROP CONSTRAINT user_diary_monitor_pkey;
256 DROP INDEX user_diary_monitor_monitored_us;
257 DROP INDEX user_diary_monitor_user;
258 ALTER TABLE user_diary_monitor ADD CONSTRAINT
259 user_diary_monitor_pkey PRIMARY KEY (monitored_user,user_id);
260 CREATE INDEX userdiarymon_useridmonitoredid ON
261 user_diary_monitor(user_id,monitored_user);
264 -- TODO DROP unnecessary sequence/id
266 DROP INDEX admin_flags_idx;
267 DROP INDEX forum_flags_idx;
268 DROP INDEX project_flags_idx;
269 DROP INDEX user_group_group_id;
270 DROP INDEX user_group_user_id;
271 ALTER TABLE user_group DROP CONSTRAINT user_group_pkey;
272 ALTER TABLE user_group ADD CONSTRAINT user_group_pkey PRIMARY KEY (group_id,user_id);
273 CREATE INDEX usergroup_useridgroupid ON user_group(user_id,group_id);
274 DROP INDEX usergroup_uniq_groupid_userid;
276 CREATE UNIQUE INDEX usermetric_userid ON user_metric(user_id);
278 CREATE INDEX usermetrichistory_useridmonthday ON user_metric_history(user_id,month,day);
279 DROP INDEX user_metric_history_date_userid;
280 ALTER TABLE user_metric_history ADD CONSTRAINT
281 user_metric_history_pkey PRIMARY KEY (month,day,user_id);
284 -- TODO DROP unnecessary sequence/id
286 ALTER TABLE user_plugin DROP CONSTRAINT user_plugin_pkey;
287 ALTER TABLE user_plugin ALTER user_id SET NOT NULL;
288 ALTER TABLE user_plugin ALTER plugin_id SET NOT NULL;
289 ALTER TABLE user_plugin ADD CONSTRAINT user_plugin_pkey PRIMARY KEY (user_id,plugin_id);
292 DROP INDEX user_pref_user_id;
293 ALTER TABLE user_preferences ALTER user_id SET NOT NULL;
294 ALTER TABLE user_preferences ALTER preference_name SET NOT NULL;
295 ALTER TABLE user_preferences ADD CONSTRAINT
296 user_preferences_pkey PRIMARY KEY (user_id,preference_name);
298 DROP INDEX user_ratings_rated_by;
299 ALTER TABLE user_ratings ADD CONSTRAINT user_ratings_pkey PRIMARY KEY (rated_by,user_id,rate_field);
301 DROP INDEX users_user_pw;