Index: openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/postgresql/Attic/project-manager-table-create.sql,v
diff -u -r1.5 -r1.6
--- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql	7 Aug 2003 23:08:45 -0000	1.5
+++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql	13 Aug 2003 18:56:29 -0000	1.6
@@ -19,9 +19,6 @@
 				references cr_revisions on delete cascade
 				constraint pm_projects_id_pk
 				primary key,
-	project_name   		varchar(255) 
-				constraint pm_projects_name_nn
-				not null,
 	-- a user-specified
 	project_code		varchar(255),
 	goal        		varchar(4000),
@@ -53,10 +50,71 @@
  );
 
 
+
+create table pm_project_roles (
+        role_id                 integer
+                                constraint pm_project_role_id_pk
+                                primary key,
+        one_line                varchar(100)
+                                constraint pm_project_role_one_line_uq
+                                unique,
+        description             varchar(2000),
+        sort_order              integer
+);
+
+
+comment on table pm_project_roles is '
+  Roles represent the way in which a party participates in a project. For example,
+  they could be a manager, or client, or participant.. The sort order determines 
+  what order it is displayed in.
+';
+
+insert into pm_project_roles (role_id, one_line, description, sort_order) values ('1','Participant','Team members who are responsible for the completion of the project','10');
+insert into pm_project_roles (role_id, one_line, description, sort_order) values ('2','Manager','Manages the team to complete the project on time and on budget.','20');
+
+
+create sequence pm_project_role_seq start 3;
+
+
+create table pm_project_role_map (
+        project_id              integer
+                                constraint pm_project_role_map_project_fk
+                                references pm_projects
+                                on delete cascade,
+        role_id                 integer
+                                constraint pm_project_role_map_role_fk
+                                references pm_project_roles,
+        party_id                integer
+                                constraint pm_project_role_map_user_id_fk 
+                                references parties(party_id)
+                                on delete cascade
+);
+
+comment on table pm_project_role_map is '
+  Maps who is a part of what project, and in what capacity
+';
+
+
+create table pm_project_default_roles (
+        role_id                 integer
+                                constraint pm_proj_default_role_fk
+                                references pm_project_roles
+                                on delete cascade,
+        party_id                 integer
+                                constraint pm_proj_default_role_party_fk 
+                                references parties(party_id)
+                                on delete cascade
+);
+
+comment on table pm_project_role_map is '
+  Specifies what role a person is a part of by default, for projects
+';
+
 -- we create two tables to store task information
 -- the information that we keep revisions on is in the 
 -- pm_task_revisions table, the rest is in pm_task
 
+create sequence pm_tasks_number_seq;
 
 create table pm_tasks (
         task_id                         integer
@@ -170,3 +228,74 @@
                                         constraint pm_tasks_const_type
                                         references pm_task_constraint_types
 );
+
+
+-- assignments
+
+
+create table pm_task_roles (
+        role_id                 integer
+                                constraint pm_task_role_id_pk
+                                primary key,
+        one_line                varchar(100)
+                                constraint pm_task_role_one_line_uq
+                                unique,
+        description             varchar(2000),
+        is_observer_p           char(1) default 'f'
+                                constraint pm_task_role_ck
+                                check (is_observer_p in ('t','f')),
+        sort_order              integer
+);
+
+comment on table pm_task_roles is '
+  Roles represent the way in which a role participates in a task. For example,
+  they could be an assignee, or a watcher. They can be divided up the same way 
+  the organization is broken up, by sales and R and D for example. These terms
+  can be made up to fit the conditions of the local company. Defaults are set
+  up by another table, pm_task_default_role 
+  The is_observer_p specifies
+  whether they are directly responsible for the task. People not directly
+  responsible will not get email notifications reminding them that tasks are
+  overdue, for example.
+';
+
+insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('1','Manager','This person oversees someone responsible for completion of the task','t','10');
+insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('2','Assignee','This person is directly responsible for completion of the task','f','20');
+insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('3','Watcher','This person is interested in developments of a ticket, but not directly responsible for it.','t','30');
+
+
+create sequence pm_task_role_seq start 4;
+
+
+create table pm_task_role_map (
+        task_id                 integer
+                                constraint pm_task_role_map_task_fk
+                                references pm_tasks
+                                on delete cascade,
+        role_id                 integer
+                                constraint pm_task_roles_fk
+                                references pm_task_roles,
+        party_id                integer
+                                constraint pm_task_role_map_user_id_fk
+                                references parties(party_id)
+                                on delete cascade
+);
+
+comment on table pm_task_role_map is '
+  Maps who is a part of what task, and in what capacity
+';
+
+create table pm_task_default_roles (
+        role_id                 integer
+                                constraint pm_task_default_role_fk
+                                references pm_task_roles
+                                on delete cascade,
+        party_id                integer
+                                constraint pm_task_default_role_party_fk 
+                                references parties(party_id)
+                                on delete cascade
+);
+
+comment on table pm_task_role_map is '
+  Specifies what role a person is a part of by default, for tasks
+';
