artifact-man.sql

GForge 3.0 协作开发平台 支持CVS, mailing lists, bug tracking, message boards/forums, task management, perman

--
--	need to widen the preference column in user_preferences
--	postgres isn't as slick as MySQL in this respect
--
BEGIN;
ALTER TABLE user_preferences RENAME COLUMN preference_value TO dead1;
ALTER TABLE user_preferences ADD COLUMN preference_value TEXT;
UPDATE user_preferences SET preference_value=dead1;
UPDATE user_preferences SET dead1='';
COMMIT;
--
--	user_group permissions flag
--
ALTER TABLE user_group ADD COLUMN artifact_flags INT NOT NULL DEFAULT 0;
UPDATE user_group SET artifact_flags=0;

--
--	each group can have multiple artifact types
--
create table artifact_group_list (
group_artifact_id serial primary key,
group_id int not null,
name text,
description text,
is_public int not null default 0,
allow_anon int not null default 0,
email_all_updates int not null default 0,
email_address text not null,
due_period int not null default 2592000,
use_resolution int not null default 0,
submit_instructions text,
browse_instructions text,
datatype int not null default 0
);

CREATE INDEX artgrouplist_groupid on artifact_group_list (group_id);
CREATE INDEX artgrouplist_groupid_public on artifact_group_list (group_id,is_public);

create table artifact_resolution (
id serial primary key,
resolution_name text
);

INSERT INTO artifact_resolution SELECT * FROM bug_resolution;

--
--	new permissions model required
--
create table artifact_perm (
id serial primary key,
group_artifact_id int not null,
user_id int not null,
perm_level int not null DEFAULT 0
);

CREATE INDEX artperm_groupartifactid on artifact_perm (group_artifact_id);
CREATE UNIQUE INDEX artperm_groupartifactid_userid on artifact_perm (group_artifact_id,user_id);

--
--	create a view to make selecting all perms for a user_id/group_id easier
--

CREATE VIEW artifactperm_user_vw AS 
SELECT ap.id, ap.group_artifact_id, ap.user_id, ap.perm_level, users.user_name, users.realname
	FROM artifact_perm ap, users 
	WHERE users.user_id=ap.user_id;

CREATE VIEW artifactperm_artgrouplist_vw AS
SELECT agl.group_artifact_id,agl.name,agl.description,agl.group_id,ap.user_id, ap.perm_level
FROM artifact_perm ap, artifact_group_list agl
WHERE ap.group_artifact_id=agl.group_artifact_id;

--
--	similar to bug_category
--
CREATE TABLE artifact_category (
  id serial primary key,
  group_artifact_id int NOT NULL,
  category_name text NOT NULL,
  auto_assign_to int not null DEFAULT 100
);

CREATE INDEX artcategory_groupartifactid on artifact_category (group_artifact_id);

--
--	similar to bug_group
--
CREATE TABLE artifact_group (
  id serial primary key,
  group_artifact_id int NOT NULL,
  group_name text NOT NULL
);

CREATE INDEX artgroup_groupartifactid on artifact_group (group_artifact_id);

--
--      similar to bug_status
--
CREATE TABLE artifact_status (
  id serial primary key,
  status_name text NOT NULL
);

--
--	similar to bug table
--
CREATE TABLE artifact (
  artifact_id serial primary key,
  group_artifact_id int NOT NULL,
  status_id int DEFAULT '1' NOT NULL,
  category_id int DEFAULT '100' NOT NULL,
  artifact_group_id int DEFAULT '0' NOT NULL,
  resolution_id int not null default '100',
  priority int DEFAULT '5' NOT NULL,
  submitted_by int DEFAULT '100' NOT NULL,
  assigned_to int DEFAULT '100' NOT NULL,
  open_date int DEFAULT '0' NOT NULL,
  close_date int DEFAULT '0' NOT NULL,
  summary text NOT NULL,
  details text NOT NULL
);

CREATE INDEX art_groupartid ON artifact (group_artifact_id);
CREATE INDEX art_groupartid_statusid ON artifact (group_artifact_id,status_id);
CREATE INDEX art_groupartid_assign ON artifact (group_artifact_id,assigned_to);
CREATE INDEX art_groupartid_submit ON artifact (group_artifact_id,submitted_by);
create index art_submit_status ON artifact(submitted_by,status_id);
create index art_assign_status ON artifact(assigned_to,status_id);
create index art_groupartid_artifactid on artifact (group_artifact_id,artifact_id);



CREATE VIEW artifact_vw AS
SELECT 
artifact.*,
u.user_name AS assigned_unixname,
u.realname AS assigned_realname,
u.email AS assigned_email,
u2.user_name AS submitted_unixname,
u2.realname AS submitted_realname,
u2.email AS submitted_email,
artifact_status.status_name, 
artifact_category.category_name, 
artifact_group.group_name, 
artifact_resolution.resolution_name 
FROM 
users u, users u2, artifact, artifact_status, artifact_category, artifact_group, artifact_resolution
WHERE 
artifact.assigned_to=u.user_id
AND artifact.submitted_by=u2.user_id
AND artifact.status_id=artifact_status.id 
AND artifact.category_id=artifact_category.id 
AND artifact.artifact_group_id=artifact_group.id
AND artifact.resolution_id=artifact_resolution.id;


--
--	audit trail table
--
CREATE TABLE artifact_history (
  id serial primary key,
  artifact_id int DEFAULT '0' NOT NULL,
  field_name text DEFAULT '' NOT NULL,
  old_value text DEFAULT '' NOT NULL,
  mod_by int DEFAULT '0' NOT NULL,
  entrydate int DEFAULT '0' NOT NULL
);

CREATE INDEX arthistory_artid on artifact_history(artifact_id);
CREATE INDEX arthistory_artid_entrydate on artifact_history(artifact_id,entrydate);

--
--	create a view from the audit trail which joins the user table and history table
--
CREATE VIEW artifact_history_user_vw AS
SELECT ah.id, ah.artifact_id, ah.field_name, ah.old_value, ah.entrydate, users.user_name 
FROM artifact_history ah, users 
WHERE ah.mod_by=users.user_id; 

--
--	files attached to a given artifact
--
CREATE TABLE artifact_file (
  id serial primary key,
  artifact_id int NOT NULL,
  description text NOT NULL,
  bin_data text NOT NULL,
  filename text NOT NULL,
  filesize int NOT NULL,
  filetype text NOT NULL,
  adddate int not null DEFAULT '0',
  submitted_by int not null
);

CREATE INDEX artfile_artid on artifact_file(artifact_id);
CREATE INDEX artfile_artid_adddate on artifact_file(artifact_id,adddate);

--
--      create a view from the files which joins the user table and files table
--
CREATE VIEW artifact_file_user_vw AS
SELECT af.id, af.artifact_id, af.description, af.bin_data, af.filename, af.filesize, af.filetype, 
	af.adddate, af.submitted_by, users.user_name, users.realname
FROM artifact_file af,users 
WHERE af.submitted_by=users.user_id;

--
--	messages and comments attached to an artifact
--
CREATE TABLE artifact_message (
  id serial primary key,
  artifact_id int NOT NULL,
  submitted_by int not null,
  from_email text NOT NULL,
  adddate int DEFAULT '0' NOT NULL,
  body text NOT NULL
);

CREATE INDEX artmessage_artid on artifact_message(artifact_id);
CREATE INDEX artmessage_artid_adddate on artifact_message(artifact_id,adddate);

--
--      create a view from the messages which joins the user table and messages table
--
CREATE VIEW artifact_message_user_vw AS
SELECT am.id, am.artifact_id, am.from_email, am.body, am.adddate, 
users.user_id, users.email, users.user_name, users.realname
FROM artifact_message am,users 
WHERE am.submitted_by=users.user_id;

--
--	table containing list of people monitoring each artifact
--
CREATE TABLE artifact_monitor (
id serial primary key,
artifact_id int NOT NULL,
user_id int not null,
email text
);

CREATE INDEX artmonitor_artifactid on artifact_monitor(artifact_id);

ALTER TABLE artifact_monitor ADD CONSTRAINT artifactmonitor_artifactid_fk
        FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id) MATCH FULL;

INSERT INTO artifact_group_list VALUES (100,1,'Default','Default Data - Dont Edit',3,0,0,'0',0);
INSERT INTO artifact_category VALUES (100,100,'None',100);
INSERT INTO artifact_group VALUES (100,100,'None');
INSERT INTO artifact_status VALUES (1,'Open');
INSERT INTO artifact_status VALUES (2,'Closed');
INSERT INTO artifact_status VALUES (3,'Deleted');

CREATE TABLE artifact_canned_responses (
  id serial primary key,
  group_artifact_id int NOT NULL,
  title text NOT NULL,
  body text NOT NULL
);

CREATE INDEX artifactcannedresponses_groupid ON artifact_canned_responses (group_artifact_id);

CREATE TABLE artifact_counts_agg (
group_artifact_id int not null,
count int not null
);
CREATE INDEX artifactcountsagg_groupartid ON artifact_counts_agg(group_artifact_id);

GRANT SELECT ON 
artifact,
artifact_group_list
TO stats;