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