2 -- Stats structure and process changes
5 drop table stats_project_build_tmp;
6 drop table tmp_projs_releases_tmp;
7 begin; delete from stats_project where day is null or week is null; commit;
8 drop table stats_project_tmp;
9 drop table topproj_admins;
10 DROP TABLE frs_dlstats_agg;
11 DROP TABLE frs_dlstats_filetotal_agg_old;
12 DROP TABLE stats_agg_pages_by_browser;
13 DROP TABLE stats_agg_pages_by_day_old;
14 DROP TABLE stats_agr_filerelease;
15 DROP TABLE stats_agr_project;
16 drop table group_cvs_history;
17 drop table project_counts_tmp;
21 -- Change the date format of stats_agg_site_by_group
23 -- Populated daily by site_stats.php
25 CREATE TABLE frs_dlstats_file_agg_tmp AS
27 substring(day::text from 1 for 6)::int AS month,
28 substring(day::text from 7 for 2)::int AS day,
31 from frs_dlstats_file_agg;
33 DROP TABLE frs_dlstats_file_agg;
34 ALTER TABLE frs_dlstats_file_agg_tmp RENAME TO frs_dlstats_file_agg;
36 CREATE UNIQUE INDEX frsdlfileagg_month_day_file ON frs_dlstats_file_agg(month,day,file_id);
39 drop index httpdl_fid;
40 drop index httpdl_group_id;
41 create index statshttpdl_day_fileid ON stats_http_downloads(day,filerelease_id);
43 drop index ftpdl_group_id;
44 create index statsftpdl_day_fileid ON stats_ftp_downloads(day,filerelease_id);
47 -- Create an archive table of project_weekly_metric
49 -- Populated by project_weekly_metric.php
51 CREATE TABLE stats_project_metric (
52 month int not null default 0,
53 day int not null default 0,
54 ranking int not null default 0,
55 percentile float not null default 0,
56 group_id int not null default 0
59 CREATE UNIQUE INDEX statsprojectmetric_month_day_group ON stats_project_metric(month,day,group_id);
63 -- Change the date format of stats_agg_site_by_group
65 -- Populated daily by site_stats.php
67 CREATE TABLE stats_agg_site_by_group_tmp AS
69 substring(day::text from 1 for 6)::int AS month,
70 substring(day::text from 7 for 2)::int AS day,
73 from stats_agg_site_by_group ;
75 DROP TABLE stats_agg_site_by_group;
76 ALTER TABLE stats_agg_site_by_group_tmp RENAME TO stats_agg_site_by_group;
78 DROP TABLE stats_agg_site_by_day;
80 CREATE UNIQUE INDEX statssitebygroup_month_day_group ON stats_agg_site_by_group(month,day,group_id);
84 -- Change the date format of stats_agg_logo_by_group
86 -- Populated daily by site_stats.php
88 CREATE TABLE stats_agg_logo_by_group_tmp AS
90 substring(day::text from 1 for 6)::int AS month,
91 substring(day::text from 7 for 2)::int AS day,
94 from stats_agg_logo_by_group ;
96 DROP TABLE stats_agg_logo_by_group;
97 ALTER TABLE stats_agg_logo_by_group_tmp RENAME TO stats_agg_logo_by_group;
99 CREATE UNIQUE INDEX statslogobygroup_month_day_group ON stats_agg_logo_by_group(month,day,group_id);
105 create table stats_subd_pages (
106 month INT NOT NULL DEFAULT 0,
107 day INT NOT NULL DEFAULT 0,
108 group_id INT NOT NULL DEFAULT 0,
109 pages INT NOT NULL DEFAULT 0
113 -- Migrate data from old stats_project table
115 INSERT INTO stats_subd_pages
116 SELECT month,day,group_id,subdomain_views
117 FROM stats_project WHERE subdomain_views > 0;
119 CREATE UNIQUE INDEX statssubdpages_month_day_group ON stats_subd_pages(month,day,group_id);
122 create table stats_cvs_user (
123 month INT NOT NULL DEFAULT 0,
124 day INT NOT NULL DEFAULT 0,
125 group_id INT NOT NULL DEFAULT 0,
126 user_id INT NOT NULL DEFAULT 0,
127 checkouts INT NOT NULL DEFAULT 0,
128 commits INT NOT NULL DEFAULT 0,
129 adds INT NOT NULL DEFAULT 0
132 create table stats_cvs_group (
133 month INT NOT NULL DEFAULT 0,
134 day INT NOT NULL DEFAULT 0,
135 group_id INT NOT NULL DEFAULT 0,
136 checkouts INT NOT NULL DEFAULT 0,
137 commits INT NOT NULL DEFAULT 0,
138 adds INT NOT NULL DEFAULT 0
142 -- Migrate data from old stats_project table
144 INSERT INTO stats_cvs_group
145 SELECT month,day,group_id,cvs_checkouts,cvs_commits,cvs_adds
147 WHERE cvs_checkouts > 0
151 CREATE UNIQUE INDEX statscvsgroup_month_day_group ON stats_cvs_group(month,day,group_id);
154 DROP INDEX archive_project_day;
155 DROP INDEX archive_project_month;
156 DROP INDEX archive_project_monthday;
157 DROP INDEX archive_project_week;
158 DROP INDEX project_log_group;
161 -- Populated daily by site_stats.php
163 create table stats_project_developers (
164 month INT NOT NULL DEFAULT 0,
165 day INT NOT NULL DEFAULT 0,
166 group_id INT NOT NULL DEFAULT 0,
167 developers INT NOT NULL DEFAULT 0
171 -- Migrate data from old stats_project table
174 CREATE UNIQUE INDEX statsprojectdev_month_day_group ON stats_project_developers(month,day,group_id);
178 -- Reorg and normalize stats_project as much as feasible
180 -- Populated daily by site_stats.php
182 DROP TABLE stats_project;
184 create table stats_project (
185 month INT NOT NULL DEFAULT 0,
186 day INT NOT NULL DEFAULT 0,
187 group_id INT NOT NULL DEFAULT 0,
188 file_releases INT DEFAULT 0,
189 msg_posted INT DEFAULT 0,
190 msg_uniq_auth INT DEFAULT 0,
191 bugs_opened INT DEFAULT 0,
192 bugs_closed INT DEFAULT 0,
193 support_opened INT DEFAULT 0,
194 support_closed INT DEFAULT 0,
195 patches_opened INT DEFAULT 0,
196 patches_closed INT DEFAULT 0,
197 artifacts_opened INT DEFAULT 0,
198 artifacts_closed INT DEFAULT 0,
199 tasks_opened INT DEFAULT 0,
200 tasks_closed INT DEFAULT 0,
201 help_requests INT DEFAULT 0
204 CREATE UNIQUE INDEX statsproject_month_day_group ON stats_project(month,day,group_id);
207 CREATE TABLE "stats_project_months" (
210 "developers" integer,
211 "group_ranking" integer,
212 "group_metric" double precision,
213 "logo_showings" integer,
215 "site_views" integer,
216 "subdomain_views" integer,
217 "page_views" integer,
218 "file_releases" integer,
219 "msg_posted" integer,
220 "msg_uniq_auth" integer,
221 "bugs_opened" integer,
222 "bugs_closed" integer,
223 "support_opened" integer,
224 "support_closed" integer,
225 "patches_opened" integer,
226 "patches_closed" integer,
227 "artifacts_opened" integer,
228 "artifacts_closed" integer,
229 "tasks_opened" integer,
230 "tasks_closed" integer,
231 "help_requests" integer,
232 "cvs_checkouts" integer,
233 "cvs_commits" integer,
237 CREATE INDEX "statsprojectmonths_groupid" on "stats_project_months" using btree ( "group_id" "int4_ops" );
238 CREATE INDEX "statsprojectmonths_groupid_mont" on "stats_project_months" using btree ( "group_id" "int4_ops", "month" "int4_ops" );
241 CREATE TABLE "stats_site_pages_by_day" (
244 "site_page_views" integer
247 CREATE UNIQUE INDEX "statssitepgsbyday_oid" on "stats_site_pages_by_day" using btree ( "oid" "oid_ops" );
248 CREATE INDEX "statssitepagesbyday_month_day" on "stats_site_pages_by_day" using btree ( "month" "int4_ops", "day" "int4_ops" );
251 CREATE TABLE "stats_site_pages_by_month" (
253 "site_page_views" integer
257 CREATE TABLE "stats_site_months" (
259 "site_page_views" integer,
261 "subdomain_views" integer,
262 "msg_posted" integer,
263 "bugs_opened" integer,
264 "bugs_closed" integer,
265 "support_opened" integer,
266 "support_closed" integer,
267 "patches_opened" integer,
268 "patches_closed" integer,
269 "artifacts_opened" integer,
270 "artifacts_closed" integer,
271 "tasks_opened" integer,
272 "tasks_closed" integer,
273 "help_requests" integer,
274 "cvs_checkouts" integer,
275 "cvs_commits" integer,
279 CREATE INDEX "statssitemonths_month" on "stats_site_months" using btree ( "month" "int4_ops" );
283 -- Reorg and normalize the stats_site table
285 -- Populated daily by site_stats.php
287 create table stats_site_tmp AS
288 select month,day,uniq_users,sessions,total_users,new_users,new_projects
291 DROP TABLE stats_site;
292 ALTER TABLE stats_site_tmp RENAME TO stats_site;
294 CREATE UNIQUE INDEX statssite_month_day on stats_site(month,day);