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
|
--
-- Stats structure and process changes
--
drop table stats_project_build_tmp;
drop table tmp_projs_releases_tmp;
begin; delete from stats_project where day is null or week is null; commit;
drop table stats_project_tmp;
drop table topproj_admins;
DROP TABLE frs_dlstats_agg;
DROP TABLE frs_dlstats_filetotal_agg_old;
DROP TABLE stats_agg_pages_by_browser;
DROP TABLE stats_agg_pages_by_day_old;
DROP TABLE stats_agr_filerelease;
DROP TABLE stats_agr_project;
drop table group_cvs_history;
drop table project_counts_tmp;
--
-- Change the date format of stats_agg_site_by_group
--
-- Populated daily by site_stats.php
--
CREATE TABLE frs_dlstats_file_agg_tmp AS
SELECT
substring(day::text from 1 for 6)::int AS month,
substring(day::text from 7 for 2)::int AS day,
file_id,
downloads
from frs_dlstats_file_agg;
DROP TABLE frs_dlstats_file_agg;
ALTER TABLE frs_dlstats_file_agg_tmp RENAME TO frs_dlstats_file_agg;
CREATE UNIQUE INDEX frsdlfileagg_month_day_file ON frs_dlstats_file_agg(month,day,file_id);
drop index httpdl_fid;
drop index httpdl_group_id;
create index statshttpdl_day_fileid ON stats_http_downloads(day,filerelease_id);
drop index ftpdl_fid;
drop index ftpdl_group_id;
create index statsftpdl_day_fileid ON stats_ftp_downloads(day,filerelease_id);
--
-- Create an archive table of project_weekly_metric
--
-- Populated by project_weekly_metric.php
--
CREATE TABLE stats_project_metric (
month int not null default 0,
day int not null default 0,
ranking int not null default 0,
percentile float not null default 0,
group_id int not null default 0
);
CREATE UNIQUE INDEX statsprojectmetric_month_day_group ON stats_project_metric(month,day,group_id);
--
-- Change the date format of stats_agg_site_by_group
--
-- Populated daily by site_stats.php
--
CREATE TABLE stats_agg_site_by_group_tmp AS
SELECT
substring(day::text from 1 for 6)::int AS month,
substring(day::text from 7 for 2)::int AS day,
group_id,
count
from stats_agg_site_by_group ;
DROP TABLE stats_agg_site_by_group;
ALTER TABLE stats_agg_site_by_group_tmp RENAME TO stats_agg_site_by_group;
DROP TABLE stats_agg_site_by_day;
CREATE UNIQUE INDEX statssitebygroup_month_day_group ON stats_agg_site_by_group(month,day,group_id);
--
-- Change the date format of stats_agg_logo_by_group
--
-- Populated daily by site_stats.php
--
CREATE TABLE stats_agg_logo_by_group_tmp AS
SELECT
substring(day::text from 1 for 6)::int AS month,
substring(day::text from 7 for 2)::int AS day,
group_id,
count
from stats_agg_logo_by_group ;
DROP TABLE stats_agg_logo_by_group;
ALTER TABLE stats_agg_logo_by_group_tmp RENAME TO stats_agg_logo_by_group;
CREATE UNIQUE INDEX statslogobygroup_month_day_group ON stats_agg_logo_by_group(month,day,group_id);
--
-- Subdomain pages
--
create table stats_subd_pages (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
pages INT NOT NULL DEFAULT 0
);
--
-- Migrate data from old stats_project table
--
INSERT INTO stats_subd_pages
SELECT month,day,group_id,subdomain_views
FROM stats_project WHERE subdomain_views > 0;
CREATE UNIQUE INDEX statssubdpages_month_day_group ON stats_subd_pages(month,day,group_id);
create table stats_cvs_user (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
user_id INT NOT NULL DEFAULT 0,
checkouts INT NOT NULL DEFAULT 0,
commits INT NOT NULL DEFAULT 0,
adds INT NOT NULL DEFAULT 0
);
create table stats_cvs_group (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
checkouts INT NOT NULL DEFAULT 0,
commits INT NOT NULL DEFAULT 0,
adds INT NOT NULL DEFAULT 0
);
--
-- Migrate data from old stats_project table
--
INSERT INTO stats_cvs_group
SELECT month,day,group_id,cvs_checkouts,cvs_commits,cvs_adds
FROM stats_project
WHERE cvs_checkouts > 0
OR cvs_commits > 0
OR cvs_adds > 0;
CREATE UNIQUE INDEX statscvsgroup_month_day_group ON stats_cvs_group(month,day,group_id);
DROP INDEX archive_project_day;
DROP INDEX archive_project_month;
DROP INDEX archive_project_monthday;
DROP INDEX archive_project_week;
DROP INDEX project_log_group;
--
-- Populated daily by site_stats.php
--
create table stats_project_developers (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
developers INT NOT NULL DEFAULT 0
);
--
-- Migrate data from old stats_project table
--
CREATE UNIQUE INDEX statsprojectdev_month_day_group ON stats_project_developers(month,day,group_id);
--
-- Reorg and normalize stats_project as much as feasible
--
-- Populated daily by site_stats.php
--
DROP TABLE stats_project;
create table stats_project (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
file_releases INT DEFAULT 0,
msg_posted INT DEFAULT 0,
msg_uniq_auth INT DEFAULT 0,
bugs_opened INT DEFAULT 0,
bugs_closed INT DEFAULT 0,
support_opened INT DEFAULT 0,
support_closed INT DEFAULT 0,
patches_opened INT DEFAULT 0,
patches_closed INT DEFAULT 0,
artifacts_opened INT DEFAULT 0,
artifacts_closed INT DEFAULT 0,
tasks_opened INT DEFAULT 0,
tasks_closed INT DEFAULT 0,
help_requests INT DEFAULT 0
);
CREATE UNIQUE INDEX statsproject_month_day_group ON stats_project(month,day,group_id);
CREATE TABLE "stats_project_months" (
"month" integer,
"group_id" integer,
"developers" integer,
"group_ranking" integer,
"group_metric" double precision,
"logo_showings" integer,
"downloads" integer,
"site_views" integer,
"subdomain_views" integer,
"page_views" integer,
"file_releases" integer,
"msg_posted" integer,
"msg_uniq_auth" integer,
"bugs_opened" integer,
"bugs_closed" integer,
"support_opened" integer,
"support_closed" integer,
"patches_opened" integer,
"patches_closed" integer,
"artifacts_opened" integer,
"artifacts_closed" integer,
"tasks_opened" integer,
"tasks_closed" integer,
"help_requests" integer,
"cvs_checkouts" integer,
"cvs_commits" integer,
"cvs_adds" integer
);
CREATE INDEX "statsprojectmonths_groupid" on "stats_project_months" using btree ( "group_id" "int4_ops" );
CREATE INDEX "statsprojectmonths_groupid_mont" on "stats_project_months" using btree ( "group_id" "int4_ops", "month" "int4_ops" );
CREATE TABLE "stats_site_pages_by_day" (
"month" integer,
"day" integer,
"site_page_views" integer
);
CREATE UNIQUE INDEX "statssitepgsbyday_oid" on "stats_site_pages_by_day" using btree ( "oid" "oid_ops" );
CREATE INDEX "statssitepagesbyday_month_day" on "stats_site_pages_by_day" using btree ( "month" "int4_ops", "day" "int4_ops" );
CREATE TABLE "stats_site_pages_by_month" (
"month" integer,
"site_page_views" integer
);
CREATE TABLE "stats_site_months" (
"month" integer,
"site_page_views" integer,
"downloads" integer,
"subdomain_views" integer,
"msg_posted" integer,
"bugs_opened" integer,
"bugs_closed" integer,
"support_opened" integer,
"support_closed" integer,
"patches_opened" integer,
"patches_closed" integer,
"artifacts_opened" integer,
"artifacts_closed" integer,
"tasks_opened" integer,
"tasks_closed" integer,
"help_requests" integer,
"cvs_checkouts" integer,
"cvs_commits" integer,
"cvs_adds" integer
);
CREATE INDEX "statssitemonths_month" on "stats_site_months" using btree ( "month" "int4_ops" );
--
-- Reorg and normalize the stats_site table
--
-- Populated daily by site_stats.php
--
create table stats_site_tmp AS
select month,day,uniq_users,sessions,total_users,new_users,new_projects
from stats_site;
DROP TABLE stats_site;
ALTER TABLE stats_site_tmp RENAME TO stats_site;
CREATE UNIQUE INDEX statssite_month_day on stats_site(month,day);
|
