+++ /dev/null
-CREATE SEQUENCE rep_time_category_time_code_seq ;
-CREATE TABLE rep_time_category (
- time_code integer DEFAULT nextval('rep_time_category_time_code_seq'::text) UNIQUE,
- category_name text
-);
-CREATE TABLE rep_time_tracking (
- week int not null,
- report_date int not null,
- user_id int not null,
- project_task_id int not null,
- time_code int not null CONSTRAINT reptimetrk_timecode REFERENCES rep_time_category(time_code),
- hours float not null
-);
--- CREATE UNIQUE INDEX reptimetrk_weekusrtskcde ON
--- rep_time_tracking (week,user_id,project_task_id,time_code);
-CREATE INDEX reptimetracking_userdate ON
- rep_time_tracking (user_id,week);
-
-INSERT INTO rep_time_category VALUES ('1','Coding');
-INSERT INTO rep_time_category VALUES ('2','Testing');
-INSERT INTO rep_time_category VALUES ('3','Meeting');
-SELECT setval('rep_time_category_time_code_seq',(SELECT max(time_code) FROM rep_time_category));
-
--- added users
-CREATE TABLE rep_users_added_daily (
- day int not null primary key,
- added int not null default 0
-);
-CREATE TABLE rep_users_added_weekly (
- week int not null primary key,
- added int not null default 0
-);
-CREATE TABLE rep_users_added_monthly (
- month int not null primary key,
- added int not null default 0
-);
-
--- cumulative users
-CREATE TABLE rep_users_cum_daily (
- day int not null primary key,
- total int not null default 0
-);
-CREATE TABLE rep_users_cum_weekly (
- week int not null primary key,
- total int not null default 0
-);
-CREATE TABLE rep_users_cum_monthly (
- month int not null primary key,
- total int not null default 0
-);
-
--- added groups
-CREATE TABLE rep_groups_added_daily (
- day int not null primary key,
- added int not null default 0
-);
-
-CREATE TABLE rep_groups_added_weekly (
- week int not null primary key,
- added int not null default 0
-);
-
-CREATE TABLE rep_groups_added_monthly (
- month int not null primary key,
- added int not null default 0
-);
-
--- cumulative groups
-CREATE TABLE rep_groups_cum_daily (
- day int not null primary key,
- total int not null default 0
-);
-
-CREATE TABLE rep_groups_cum_weekly (
- week int not null primary key,
- total int not null default 0
-);
-
-CREATE TABLE rep_groups_cum_monthly (
- month int not null primary key,
- total int not null default 0
-);
-
--- per-user activity
-CREATE TABLE rep_user_act_daily (
- user_id int not null,
- day int not null,
- tracker_opened int not null,
- tracker_closed int not null,
- forum int not null,
- docs int not null,
- cvs_commits int not null,
- tasks_opened int not null,
- tasks_closed int not null,
- PRIMARY KEY (user_id,day)
-);
-
-CREATE TABLE rep_user_act_weekly (
- user_id int not null,
- week int not null,
- tracker_opened int not null,
- tracker_closed int not null,
- forum int not null,
- docs int not null,
- cvs_commits int not null,
- tasks_opened int not null,
- tasks_closed int not null,
- PRIMARY KEY (user_id,week)
-);
-
-CREATE TABLE rep_user_act_monthly (
- user_id int not null,
- month int not null,
- tracker_opened int not null,
- tracker_closed int not null,
- forum int not null,
- docs int not null,
- cvs_commits int not null,
- tasks_opened int not null,
- tasks_closed int not null,
- PRIMARY KEY (user_id,month)
-);
-
-CREATE VIEW rep_user_act_oa_vw AS
- SELECT user_id,
- sum(tracker_opened) AS tracker_opened,
- sum(tracker_closed) AS tracker_closed,
- sum(forum) AS forum,
- sum(docs) AS docs,
- sum(cvs_commits) AS cvs_commits,
- sum(tasks_opened) AS tasks_opened,
- sum(tasks_closed) AS tasks_closed
- FROM rep_user_act_monthly
- GROUP BY user_id;
-
--- per-project activity
-CREATE TABLE rep_group_act_daily (
- group_id int not null,
- day int not null,
- tracker_opened int not null,
- tracker_closed int not null,
- forum int not null,
- docs int not null,
- downloads int not null,
- cvs_commits int not null,
- tasks_opened int not null,
- tasks_closed int not null,
- PRIMARY KEY (group_id,day)
-);
-
-CREATE INDEX repgroupactdaily_day ON rep_group_act_daily(day);
-
-CREATE TABLE rep_group_act_weekly (
- group_id int not null,
- week int not null,
- tracker_opened int not null,
- tracker_closed int not null,
- forum int not null,
- docs int not null,
- downloads int not null,
- cvs_commits int not null,
- tasks_opened int not null,
- tasks_closed int not null,
- PRIMARY KEY (group_id,week)
-);
-
-CREATE INDEX repgroupactweekly_week ON rep_group_act_weekly(week);
-
-CREATE TABLE rep_group_act_monthly (
- group_id int not null,
- month int not null,
- tracker_opened int not null,
- tracker_closed int not null,
- forum int not null,
- docs int not null,
- downloads int not null,
- cvs_commits int not null,
- tasks_opened int not null,
- tasks_closed int not null,
- PRIMARY KEY (group_id,month)
-);
-
-CREATE INDEX repgroupactmonthly_month ON rep_group_act_monthly(month);
-
-CREATE VIEW rep_group_act_oa_vw AS
- SELECT group_id,
- sum(tracker_opened) AS tracker_opened,
- sum(tracker_closed) AS tracker_closed,
- sum(forum) AS forum,
- sum(docs) AS docs,
- sum(downloads) AS downloads,
- sum(cvs_commits) AS cvs_commits,
- sum(tasks_opened) AS tasks_opened,
- sum(tasks_closed) AS tasks_closed
- FROM rep_group_act_monthly
- GROUP BY group_id;
-
--- overall activity
-CREATE VIEW rep_site_act_daily_vw AS
- SELECT day,
- sum(tracker_opened) AS tracker_opened,
- sum(tracker_closed) AS tracker_closed,
- sum(forum) AS forum,
- sum(docs) AS docs,
- sum(downloads) AS downloads,
- sum(cvs_commits) AS cvs_commits,
- sum(tasks_opened) AS tasks_opened,
- sum(tasks_closed) AS tasks_closed
- FROM rep_group_act_daily
- GROUP BY day;
-
-CREATE VIEW rep_site_act_weekly_vw AS
- SELECT week,
- sum(tracker_opened) AS tracker_opened,
- sum(tracker_closed) AS tracker_closed,
- sum(forum) AS forum,
- sum(docs) AS docs,
- sum(downloads) AS downloads,
- sum(cvs_commits) AS cvs_commits,
- sum(tasks_opened) AS tasks_opened,
- sum(tasks_closed) AS tasks_closed
- FROM rep_group_act_weekly
- GROUP BY week;
-
-CREATE VIEW rep_site_act_monthly_vw AS
- SELECT month,
- sum(tracker_opened) AS tracker_opened,
- sum(tracker_closed) AS tracker_closed,
- sum(forum) AS forum,
- sum(docs) AS docs,
- sum(downloads) AS downloads,
- sum(cvs_commits) AS cvs_commits,
- sum(tasks_opened) AS tasks_opened,
- sum(tasks_closed) AS tasks_closed
- FROM rep_group_act_monthly
- GROUP BY month;