1 ALTER FUNCTION frs_dlstats_filetotal_insert_ag() RENAME TO frs_dlstats_filetotal_insert_agg;
3 ALTER TABLE ONLY artifact_type_monitor DROP CONSTRAINT IF EXISTS artifact_type_monitor_group_artifact_id_fkey;
4 ALTER TABLE ONLY artifact_type_monitor ADD CONSTRAINT artifact_type_monitor_group_artifact_id_fkey FOREIGN KEY (group_artifact_id) REFERENCES artifact_group_list(group_artifact_id) ON DELETE CASCADE;
5 ALTER TABLE ONLY artifact_type_monitor DROP CONSTRAINT IF EXISTS artifact_type_monitor_user_id_fkey;
6 ALTER TABLE ONLY artifact_type_monitor ADD CONSTRAINT artifact_type_monitor_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);
8 ALTER TABLE artifact_type_monitor DROP CONSTRAINT "$1";
9 ALTER TABLE artifact_type_monitor DROP CONSTRAINT "$2";
11 ALTER TABLE ONLY forum_attachment DROP CONSTRAINT IF EXISTS forum_attachment_msg_id_fkey;
12 ALTER TABLE ONLY forum_attachment ADD CONSTRAINT forum_attachment_msg_id_fkey FOREIGN KEY (msg_id) REFERENCES forum(msg_id) ON DELETE CASCADE;
13 ALTER TABLE ONLY forum_attachment DROP CONSTRAINT IF EXISTS forum_attachment_userid_fkey;
14 ALTER TABLE ONLY forum_attachment ADD CONSTRAINT forum_attachment_userid_fkey FOREIGN KEY (userid) REFERENCES users(user_id) ON DELETE SET DEFAULT;
16 ALTER TABLE forum_attachment DROP CONSTRAINT "$1";
17 ALTER TABLE forum_attachment DROP CONSTRAINT "$2";
19 ALTER SEQUENCE supported_langu_language_id_seq RENAME TO supported_languages_pk_seq;
20 ALTER TABLE supported_languages ALTER COLUMN language_id SET DEFAULT nextval(('supported_languages_pk_seq'::text)::regclass);
21 ALTER SEQUENCE group_cvs_history_id_seq RENAME TO group_cvs_history_pk_seq;
22 ALTER TABLE group_cvs_history ALTER COLUMN id SET DEFAULT nextval(('group_cvs_history_pk_seq'::text)::regclass);
23 ALTER TABLE group_cvs_history DROP CONSTRAINT IF EXISTS group_cvs_history_pkey;
24 ALTER TABLE group_cvs_history ADD CONSTRAINT group_cvs_history_pkey PRIMARY KEY (id);
26 ALTER SEQUENCE project_messa_project_messa_seq RENAME TO project_messages_project_message_id_seq;
27 ALTER TABLE activity_log ALTER COLUMN ver SET DEFAULT 0::double precision;
28 ALTER TABLE artifact_extra_field_data ALTER COLUMN data_id SET DEFAULT nextval('artifact_extra_field_data_data_id_seq'::regclass);
29 ALTER TABLE artifact_extra_field_list ALTER COLUMN extra_field_id SET DEFAULT nextval('artifact_extra_field_list_extra_field_id_seq'::regclass);
30 ALTER TABLE artifact_extra_field_elements ALTER COLUMN element_id SET DEFAULT nextval('artifact_extra_field_elements_element_id_seq'::regclass);
32 ALTER TABLE db_images ALTER COLUMN upload_date SET DEFAULT 0;
33 ALTER TABLE db_images ALTER COLUMN version SET DEFAULT 0;
35 ALTER TABLE group_join_request DROP CONSTRAINT "$1";
36 ALTER TABLE group_join_request DROP CONSTRAINT "$2";
37 ALTER TABLE group_join_request DROP CONSTRAINT IF EXISTS group_join_request_group_id_fkey;
38 ALTER TABLE group_join_request ADD CONSTRAINT group_join_request_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(group_id) ON DELETE CASCADE;
39 ALTER TABLE group_join_request DROP CONSTRAINT IF EXISTS group_join_request_user_id_fkey;
40 ALTER TABLE group_join_request ADD CONSTRAINT group_join_request_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);
41 ALTER TABLE groups DROP CONSTRAINT IF EXISTS groups_license;
42 ALTER TABLE groups ADD CONSTRAINT groups_license FOREIGN KEY (license) REFERENCES licenses(license_id) MATCH FULL;
43 ALTER TABLE groups ALTER COLUMN unix_box SET DEFAULT 'shell'::character varying;
44 ALTER TABLE users ALTER COLUMN unix_box SET DEFAULT 'shell'::character varying;
46 DROP INDEX plugins_plugin_name_key;
47 ALTER TABLE plugins DROP CONSTRAINT IF EXISTS plugins_plugin_name_key;
48 ALTER TABLE plugins ADD CONSTRAINT plugins_plugin_name_key UNIQUE (plugin_name);
49 ALTER TABLE project_tags DROP CONSTRAINT IF EXISTS project_tags_group_id_fkey;
50 ALTER TABLE project_tags ADD CONSTRAINT project_tags_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL;
51 ALTER TABLE project_task DROP CONSTRAINT IF EXISTS project_task_group_project_id_f;
52 ALTER TABLE project_task ADD CONSTRAINT project_task_group_project_id_f FOREIGN KEY (group_project_id) REFERENCES project_group_list(group_project_id) MATCH FULL;
54 ALTER TABLE project_task_external_order DROP CONSTRAINT "$1";
55 ALTER TABLE project_task_external_order DROP CONSTRAINT IF EXISTS project_task_external_order_project_task_id_fkey;
56 ALTER TABLE project_task_external_order ADD CONSTRAINT project_task_external_order_project_task_id_fkey FOREIGN KEY (project_task_id) REFERENCES project_task(project_task_id) MATCH FULL ON DELETE CASCADE;
58 ALTER TABLE project_weekly_metric DROP CONSTRAINT IF EXISTS project_weekly_metric_pkey;
59 ALTER TABLE project_weekly_metric ADD CONSTRAINT project_weekly_metric_pkey PRIMARY KEY (ranking);
60 DROP SEQUENCE project_metric_wee_ranking1_seq;
62 ALTER TABLE role DROP CONSTRAINT "$1";
63 ALTER TABLE role DROP CONSTRAINT IF EXISTS role_group_id_fkey;
64 ALTER TABLE role ADD CONSTRAINT role_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(group_id) ON DELETE CASCADE;
66 ALTER TABLE project_messages ALTER COLUMN project_message_id SET DEFAULT nextval('project_messages_project_message_id_seq'::regclass);
67 ALTER SEQUENCE project_messages_project_message_id_seq OWNED BY project_messages.project_message_id;
69 DROP SEQUENCE themes_pk_seq;
70 ALTER SEQUENCE themes_theme_id_seq RENAME TO themes_pk_seq;
71 ALTER TABLE themes ALTER COLUMN theme_id SET DEFAULT nextval(('themes_pk_seq'::text)::regclass);
73 DROP SEQUENCE trove_treesums_pk_seq;
74 ALTER SEQUENCE trove_treesum_trove_treesum_seq RENAME TO trove_treesums_pk_seq;
75 ALTER TABLE trove_treesums ALTER COLUMN trove_treesums_id SET DEFAULT nextval(('trove_treesums_pk_seq'::text)::regclass);
77 ALTER SEQUENCE group_cvs_history_pk_seq MAXVALUE 2147483647;
78 ALTER SEQUENCE supported_languages_pk_seq MAXVALUE 2147483647;
79 ALTER SEQUENCE themes_pk_seq MAXVALUE 2147483647;
81 ALTER TABLE project_category ALTER COLUMN category_id SET DEFAULT nextval('project_categor_category_id_seq'::regclass);
83 CREATE VIEW mta_users AS SELECT users.user_name AS login, users.email FROM users WHERE (users.status = 'A'::bpchar);
85 CREATE VIEW nss_shadow AS SELECT users.user_name AS login, users.unix_pw AS passwd, 'n'::character(1) AS expired, 'n'::character(1) AS pwchange FROM users WHERE (users.unix_status = 'A'::bpchar);
87 INSERT INTO "project_status" (status_id, status_name) VALUES ('3', 'Deleted');
88 INSERT INTO "supported_languages" (language_id, name, filename, classname, language_code) VALUES ('13', 'Esperanto', 'Esperanto.class', 'Esperanto', 'eo ');
89 INSERT INTO "supported_languages" (language_id, name, filename, classname, language_code) VALUES ('15', 'Polish', 'Polish.class', 'Polish', 'pl ');
90 INSERT INTO "supported_languages" (language_id, name, filename, classname, language_code) VALUES ('18', 'Portuguese', 'Portuguese.class', 'Portuguese', 'pt ');
91 INSERT INTO "supported_languages" (language_id, name, filename, classname, language_code) VALUES ('19', 'Greek', 'Greek.class', 'Greek', 'el ');
92 INSERT INTO "supported_languages" (language_id, name, filename, classname, language_code) VALUES ('21', 'Indonesian', 'Indonesian.class', 'Indonesian', 'id ');
93 INSERT INTO "supported_languages" (language_id, name, filename, classname, language_code) VALUES ('25', 'Latin', 'Latin.class', 'Latin', 'la ');
94 INSERT INTO "supported_languages" (language_id, name, filename, classname, language_code) VALUES ('3', 'Hebrew', 'Hebrew.class', 'Hebrew', 'iw ');
95 INSERT INTO "supported_languages" (language_id, name, filename, classname, language_code) VALUES ('9', 'Norwegian', 'Norwegian.class', 'Norwegian', 'no ');
97 INSERT INTO "artifact_status" (id, status_name) VALUES ('3', 'Deleted');
99 INSERT INTO "people_skill" (skill_id, name) VALUES ('1', 'Ada');
100 INSERT INTO "people_skill" (skill_id, name) VALUES ('2', 'C');
101 INSERT INTO "people_skill" (skill_id, name) VALUES ('3', 'C++');
102 INSERT INTO "people_skill" (skill_id, name) VALUES ('4', 'HTML');
103 INSERT INTO "people_skill" (skill_id, name) VALUES ('5', 'LISP');
104 INSERT INTO "people_skill" (skill_id, name) VALUES ('6', 'Perl');
105 INSERT INTO "people_skill" (skill_id, name) VALUES ('7', 'PHP');
106 INSERT INTO "people_skill" (skill_id, name) VALUES ('8', 'Python');
107 INSERT INTO "people_skill" (skill_id, name) VALUES ('9', 'SQL');
109 ALTER TABLE frs_dlstats_filetotal_agg ALTER COLUMN file_id SET DEFAULT 0;
110 ALTER TABLE frs_dlstats_filetotal_agg ALTER COLUMN downloads SET DEFAULT 0;
111 ALTER TABLE frs_dlstats_filetotal_agg ALTER COLUMN downloads SET NOT NULL;
113 CREATE INDEX projecttaskartifact_projecttaskid ON project_task_artifact USING btree (project_task_id);
114 ALTER TABLE project_tags ALTER COLUMN name SET DEFAULT ''::text;
116 ALTER TABLE user_preferences DROP COLUMN dead1;
118 ALTER TABLE user_group ALTER COLUMN artifact_flags SET DEFAULT 0;
120 DROP TABLE IF EXISTS project_metric_tmp1;
121 DROP SEQUENCE IF EXISTS project_metric_tmp1_pk_seq;
123 ALTER TABLE user_preferences ADD COLUMN set_date_new integer DEFAULT 0 NOT NULL;
124 UPDATE user_preferences SET set_date_new = set_date;
125 ALTER TABLE user_preferences DROP COLUMN set_date;
126 ALTER TABLE user_preferences RENAME COLUMN set_date_new TO set_date;
128 ALTER TABLE project_task DROP CONSTRAINT project_task_category_id_fkey;
129 DROP INDEX project_categor_category_id_key;
130 ALTER TABLE project_category DROP CONSTRAINT IF EXISTS project_category_pkey;
131 ALTER TABLE project_category ADD CONSTRAINT project_category_pkey PRIMARY KEY (category_id);
132 ALTER TABLE project_task DROP CONSTRAINT IF EXISTS project_task_category_id_fkey;
133 ALTER TABLE project_task ADD CONSTRAINT project_task_category_id_fkey FOREIGN KEY (category_id) REFERENCES project_category(category_id);
135 ALTER TABLE users DROP CONSTRAINT users_themeid;
136 DROP INDEX themes_theme_id_key;
137 ALTER TABLE themes DROP CONSTRAINT IF EXISTS themes_pkey;
138 ALTER TABLE themes ADD CONSTRAINT themes_pkey PRIMARY KEY (theme_id);
139 ALTER TABLE users DROP CONSTRAINT IF EXISTS users_themeid;
140 ALTER TABLE users ADD CONSTRAINT users_themeid FOREIGN KEY (theme_id) REFERENCES themes(theme_id) MATCH FULL;
142 CREATE OR REPLACE VIEW stats_project_all_vw AS SELECT stats_project_months.group_id, (avg(stats_project_months.developers))::integer AS developers, (avg(stats_project_months.group_ranking))::integer AS group_ranking, avg(stats_project_months.group_metric) AS group_metric, sum(stats_project_months.logo_showings) AS logo_showings, sum(stats_project_months.downloads) AS downloads, sum(stats_project_months.site_views) AS site_views, sum(stats_project_months.subdomain_views) AS subdomain_views, sum(stats_project_months.page_views) AS page_views, sum(stats_project_months.file_releases) AS file_releases, sum(stats_project_months.msg_posted) AS msg_posted, (avg(stats_project_months.msg_uniq_auth))::integer AS msg_uniq_auth, sum(stats_project_months.bugs_opened) AS bugs_opened, sum(stats_project_months.bugs_closed) AS bugs_closed, sum(stats_project_months.support_opened) AS support_opened, sum(stats_project_months.support_closed) AS support_closed, sum(stats_project_months.patches_opened) AS patches_opened, sum(stats_project_months.patches_closed) AS patches_closed, sum(stats_project_months.artifacts_opened) AS artifacts_opened, sum(stats_project_months.artifacts_closed) AS artifacts_closed, sum(stats_project_months.tasks_opened) AS tasks_opened, sum(stats_project_months.tasks_closed) AS tasks_closed, sum(stats_project_months.help_requests) AS help_requests, sum(stats_project_months.cvs_checkouts) AS cvs_checkouts, sum(stats_project_months.cvs_commits) AS cvs_commits, sum(stats_project_months.cvs_adds) AS cvs_adds FROM stats_project_months GROUP BY stats_project_months.group_id;
143 CREATE OR REPLACE VIEW stats_project_vw AS SELECT spd.group_id, spd.month, spd.day, spd.developers, spm.ranking AS group_ranking, spm.percentile AS group_metric, salbg.count AS logo_showings, fdga.downloads, sasbg.count AS site_views, ssp.pages AS subdomain_views, (COALESCE(sasbg.count, 0) + COALESCE(ssp.pages, 0)) AS page_views, sp.file_releases, sp.msg_posted, sp.msg_uniq_auth, sp.bugs_opened, sp.bugs_closed, sp.support_opened, sp.support_closed, sp.patches_opened, sp.patches_closed, sp.artifacts_opened, sp.artifacts_closed, sp.tasks_opened, sp.tasks_closed, sp.help_requests, scg.checkouts AS cvs_checkouts, scg.commits AS cvs_commits, scg.adds AS cvs_adds FROM (((((((stats_project_developers spd LEFT JOIN stats_project sp USING (month, day, group_id)) LEFT JOIN stats_project_metric spm USING (month, day, group_id)) LEFT JOIN stats_cvs_group scg USING (month, day, group_id)) LEFT JOIN stats_agg_site_by_group sasbg USING (month, day, group_id)) LEFT JOIN stats_agg_logo_by_group salbg USING (month, day, group_id)) LEFT JOIN stats_subd_pages ssp USING (month, day, group_id)) LEFT JOIN frs_dlstats_group_vw fdga USING (month, day, group_id));
145 CREATE TABLE artifact_idx ( artifact_id integer, group_artifact_id integer, vectors tsvector );
146 CREATE TABLE artifact_message_idx ( id integer, artifact_id integer, vectors tsvector );
147 CREATE TABLE doc_data_idx ( docid integer, group_id integer, vectors tsvector );
148 CREATE TABLE forum_idx ( msg_id integer, group_id integer, vectors tsvector );
149 CREATE TABLE frs_file_idx ( file_id integer, release_id integer, vectors tsvector );
150 CREATE TABLE frs_release_idx ( release_id integer, vectors tsvector );
151 CREATE TABLE groups_idx ( group_id integer, vectors tsvector );
152 CREATE TABLE news_bytes_idx ( id integer, vectors tsvector );
153 CREATE TABLE project_task_idx ( project_task_id integer, vectors tsvector );
154 CREATE TABLE skills_data_idx ( skills_data_id integer, vectors tsvector );
155 CREATE TABLE users_idx ( user_id integer, vectors tsvector );
157 CREATE INDEX artifact_idxfti ON artifact_idx USING gist (vectors);
158 CREATE INDEX artifact_message_idxfti ON artifact_message_idx USING gist (vectors);
159 CREATE INDEX doc_data_idxfti ON doc_data_idx USING gist (vectors);
160 CREATE INDEX forum_idxfti ON forum_idx USING gist (vectors);
161 CREATE INDEX frs_file_idxfti ON frs_file_idx USING gist (vectors);
162 CREATE INDEX frs_release_idxfti ON frs_release_idx USING gist (vectors);
163 CREATE INDEX groups_idxfti ON groups_idx USING gist (vectors);
164 CREATE INDEX news_bytes_idxfti ON news_bytes_idx USING gist (vectors);
165 CREATE INDEX project_task_idxfti ON project_task_idx USING gist (vectors);
166 CREATE INDEX skills_data_idxfti ON skills_data_idx USING gist (vectors);
167 CREATE INDEX users_idxfti ON users_idx USING gist (vectors);
169 CREATE TRIGGER artifact_ts_update AFTER INSERT OR DELETE OR UPDATE ON artifact FOR EACH ROW EXECUTE PROCEDURE update_vectors('artifact');
170 CREATE TRIGGER artifactmessage_ts_update AFTER INSERT OR DELETE OR UPDATE ON artifact_message FOR EACH ROW EXECUTE PROCEDURE update_vectors('artifact_message');
171 CREATE TRIGGER doc_data_ts_update AFTER INSERT OR DELETE OR UPDATE ON doc_data FOR EACH ROW EXECUTE PROCEDURE update_vectors('doc_data');
172 CREATE TRIGGER forum_update AFTER INSERT OR DELETE OR UPDATE ON forum FOR EACH ROW EXECUTE PROCEDURE update_vectors('forum');
173 CREATE TRIGGER frs_file_ts_update AFTER INSERT OR DELETE OR UPDATE ON frs_file FOR EACH ROW EXECUTE PROCEDURE update_vectors('frs_file');
174 CREATE TRIGGER frs_release_ts_update AFTER INSERT OR DELETE OR UPDATE ON frs_release FOR EACH ROW EXECUTE PROCEDURE update_vectors('frs_release');
175 CREATE TRIGGER groups_ts_update AFTER INSERT OR DELETE OR UPDATE ON groups FOR EACH ROW EXECUTE PROCEDURE update_vectors('groups');
176 CREATE TRIGGER news_bytes_ts_update AFTER INSERT OR DELETE OR UPDATE ON news_bytes FOR EACH ROW EXECUTE PROCEDURE update_vectors('news_bytes');
177 CREATE TRIGGER project_task_ts_update AFTER INSERT OR DELETE OR UPDATE ON project_task FOR EACH ROW EXECUTE PROCEDURE update_vectors('project_task');
178 CREATE TRIGGER skills_data_ts_update AFTER INSERT OR DELETE OR UPDATE ON skills_data FOR EACH ROW EXECUTE PROCEDURE update_vectors('skills_data');
179 CREATE TRIGGER users_ts_update AFTER INSERT OR DELETE OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_vectors('users');
181 CREATE TYPE artifact_results AS ( group_artifact_id integer, artifact_id integer, summary text, open_date integer, realname character varying(32));
182 CREATE TYPE doc_data_results AS ( docid integer, title text, description text, groupname character varying(255));
183 CREATE TYPE export_groups_results AS ( group_name text, unix_group_name text, type_id integer, group_id integer, short_description text, license integer, register_time integer );
184 CREATE TYPE forum_results AS ( msg_id integer, subject text, post_date integer, realname character varying(32));
185 CREATE TYPE forums_results AS ( msg_id integer, subject text, post_date integer, realname character varying(32), forum_name text );
186 CREATE TYPE frs_results AS ( package_name text, release_name text, release_date integer, release_id integer, realname character varying(32));
187 CREATE TYPE groups_results AS ( group_name text, unix_group_name text, type_id integer, group_id integer, short_description text );
188 CREATE TYPE news_bytes_results AS ( summary text, post_date integer, forum_id integer, realname text );
189 CREATE TYPE project_task_results AS ( project_task_id integer, summary text, percent_complete integer, start_date integer, end_date integer, realname text, project_name text, group_project_id integer );
190 CREATE TYPE skills_data_results AS ( skills_data_id integer, type integer, title text, start integer, finish integer, keywords text );
191 CREATE TYPE trackers_results AS ( artifact_id integer, group_artifact_id integer, summary text, open_date integer, realname character varying(32), name text );
192 CREATE TYPE users_results AS ( user_name text, user_id integer, realname text );
194 DELETE FROM project_messages_idx;
196 INSERT INTO project_messages_idx (id, project_task_id, vectors)
197 SELECT project_message_id, project_task_id, to_tsvector(coalesce(body,'')) AS vectors
198 FROM project_messages ORDER BY project_message_id;