1 CREATE SEQUENCE rep_time_category_time_code_seq ;
2 CREATE TABLE rep_time_category (
3 time_code integer DEFAULT nextval('rep_time_category_time_code_seq'::text) UNIQUE,
6 CREATE TABLE rep_time_tracking (
8 report_date int not null,
10 project_task_id int not null,
11 time_code int not null CONSTRAINT reptimetrk_timecode REFERENCES rep_time_category(time_code),
14 -- CREATE UNIQUE INDEX reptimetrk_weekusrtskcde ON
15 -- rep_time_tracking (week,user_id,project_task_id,time_code);
16 CREATE INDEX reptimetracking_userdate ON
17 rep_time_tracking (user_id,week);
19 INSERT INTO rep_time_category VALUES ('1','Coding');
20 INSERT INTO rep_time_category VALUES ('2','Testing');
21 INSERT INTO rep_time_category VALUES ('3','Meeting');
22 SELECT setval('rep_time_category_time_code_seq',(SELECT max(time_code) FROM rep_time_category));
25 CREATE TABLE rep_users_added_daily (
26 day int not null primary key,
27 added int not null default 0
29 CREATE TABLE rep_users_added_weekly (
30 week int not null primary key,
31 added int not null default 0
33 CREATE TABLE rep_users_added_monthly (
34 month int not null primary key,
35 added int not null default 0
39 CREATE TABLE rep_users_cum_daily (
40 day int not null primary key,
41 total int not null default 0
43 CREATE TABLE rep_users_cum_weekly (
44 week int not null primary key,
45 total int not null default 0
47 CREATE TABLE rep_users_cum_monthly (
48 month int not null primary key,
49 total int not null default 0
53 CREATE TABLE rep_groups_added_daily (
54 day int not null primary key,
55 added int not null default 0
58 CREATE TABLE rep_groups_added_weekly (
59 week int not null primary key,
60 added int not null default 0
63 CREATE TABLE rep_groups_added_monthly (
64 month int not null primary key,
65 added int not null default 0
69 CREATE TABLE rep_groups_cum_daily (
70 day int not null primary key,
71 total int not null default 0
74 CREATE TABLE rep_groups_cum_weekly (
75 week int not null primary key,
76 total int not null default 0
79 CREATE TABLE rep_groups_cum_monthly (
80 month int not null primary key,
81 total int not null default 0
85 CREATE TABLE rep_user_act_daily (
88 tracker_opened int not null,
89 tracker_closed int not null,
92 cvs_commits int not null,
93 tasks_opened int not null,
94 tasks_closed int not null,
95 PRIMARY KEY (user_id,day)
98 CREATE TABLE rep_user_act_weekly (
101 tracker_opened int not null,
102 tracker_closed int not null,
105 cvs_commits int not null,
106 tasks_opened int not null,
107 tasks_closed int not null,
108 PRIMARY KEY (user_id,week)
111 CREATE TABLE rep_user_act_monthly (
112 user_id int not null,
114 tracker_opened int not null,
115 tracker_closed int not null,
118 cvs_commits int not null,
119 tasks_opened int not null,
120 tasks_closed int not null,
121 PRIMARY KEY (user_id,month)
124 CREATE VIEW rep_user_act_oa_vw AS
126 sum(tracker_opened) AS tracker_opened,
127 sum(tracker_closed) AS tracker_closed,
130 sum(cvs_commits) AS cvs_commits,
131 sum(tasks_opened) AS tasks_opened,
132 sum(tasks_closed) AS tasks_closed
133 FROM rep_user_act_monthly
136 -- per-project activity
137 CREATE TABLE rep_group_act_daily (
138 group_id int not null,
140 tracker_opened int not null,
141 tracker_closed int not null,
144 downloads int not null,
145 cvs_commits int not null,
146 tasks_opened int not null,
147 tasks_closed int not null,
148 PRIMARY KEY (group_id,day)
151 CREATE INDEX repgroupactdaily_day ON rep_group_act_daily(day);
153 CREATE TABLE rep_group_act_weekly (
154 group_id int not null,
156 tracker_opened int not null,
157 tracker_closed int not null,
160 downloads int not null,
161 cvs_commits int not null,
162 tasks_opened int not null,
163 tasks_closed int not null,
164 PRIMARY KEY (group_id,week)
167 CREATE INDEX repgroupactweekly_week ON rep_group_act_weekly(week);
169 CREATE TABLE rep_group_act_monthly (
170 group_id int not null,
172 tracker_opened int not null,
173 tracker_closed int not null,
176 downloads int not null,
177 cvs_commits int not null,
178 tasks_opened int not null,
179 tasks_closed int not null,
180 PRIMARY KEY (group_id,month)
183 CREATE INDEX repgroupactmonthly_month ON rep_group_act_monthly(month);
185 CREATE VIEW rep_group_act_oa_vw AS
187 sum(tracker_opened) AS tracker_opened,
188 sum(tracker_closed) AS tracker_closed,
191 sum(downloads) AS downloads,
192 sum(cvs_commits) AS cvs_commits,
193 sum(tasks_opened) AS tasks_opened,
194 sum(tasks_closed) AS tasks_closed
195 FROM rep_group_act_monthly
199 CREATE VIEW rep_site_act_daily_vw AS
201 sum(tracker_opened) AS tracker_opened,
202 sum(tracker_closed) AS tracker_closed,
205 sum(downloads) AS downloads,
206 sum(cvs_commits) AS cvs_commits,
207 sum(tasks_opened) AS tasks_opened,
208 sum(tasks_closed) AS tasks_closed
209 FROM rep_group_act_daily
212 CREATE VIEW rep_site_act_weekly_vw AS
214 sum(tracker_opened) AS tracker_opened,
215 sum(tracker_closed) AS tracker_closed,
218 sum(downloads) AS downloads,
219 sum(cvs_commits) AS cvs_commits,
220 sum(tasks_opened) AS tasks_opened,
221 sum(tasks_closed) AS tasks_closed
222 FROM rep_group_act_weekly
225 CREATE VIEW rep_site_act_monthly_vw AS
227 sum(tracker_opened) AS tracker_opened,
228 sum(tracker_closed) AS tracker_closed,
231 sum(downloads) AS downloads,
232 sum(cvs_commits) AS cvs_commits,
233 sum(tasks_opened) AS tasks_opened,
234 sum(tasks_closed) AS tasks_closed
235 FROM rep_group_act_monthly