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.17 -r1.18
--- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql	26 Jan 2004 15:39:40 -0000	1.17
+++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql	26 Feb 2004 15:15:40 -0000	1.18
@@ -1,7 +1,6 @@
 -- TODO:
 -- 
 -- need to add in workflow (for status among other things)
--- add categories to projects
 
 --
 -- packages/project-manager/sql/postgresql/project-manager-table-create.sql
@@ -23,8 +22,8 @@
         description             varchar(100),
         -- closed or open
         status_type             char(1) default 'c'
-				constraint pm_projects_status_type_ck
-				check (status_type in ('c','o'))
+                                constraint pm_projects_status_type_ck
+                                check (status_type in ('c','o'))
 );
 
 insert into pm_project_status (status_id, description, status_type) values
@@ -33,27 +32,29 @@
 (2, 'Closed', 'c');
 
 
+-- project revisions, items are kept in cr_items
+
 create table pm_projects (
-	project_id		integer
-				constraint project_manager_id_fk
-				references cr_revisions on delete cascade
-				constraint pm_projects_id_pk
-				primary key,
-	-- a user-specified project code
-	project_code		varchar(255),
-	goal        		varchar(4000),
-	planned_start_date  	timestamptz,
-	planned_end_date	timestamptz,
-	actual_start_date  	timestamptz,
-	actual_end_date		timestamptz,
+        project_id     integer
+                                constraint pm_proj_rev_fk
+                                references cr_revisions on delete cascade
+                                constraint pm_proj_rev_pk
+                                primary key,
+        -- a user-specified project code
+        project_code            varchar(255),
+        goal                    varchar(4000),
+        planned_start_date      timestamptz,
+        planned_end_date        timestamptz,
+        actual_start_date       timestamptz,
+        actual_end_date         timestamptz,
         status_id               integer
                                 constraint pm_projects_status_id_nn
                                 not null
                                 constraint pm_projects_status_id_fk
                                 references pm_project_status,
-	ongoing_p		char(1) default 'f' 
-				constraint pm_projects_ongoing_p_ck
-				check (ongoing_p in ('t','f')),
+        ongoing_p               char(1) default 'f' 
+                                constraint pm_projects_ongoing_p_ck
+                                check (ongoing_p in ('t','f')),
         -- denormalized, computed values
         -- these are computed but stored in projects table for efficient
         -- access.
@@ -67,16 +68,19 @@
 
 -- create the content type
  select content_type__create_type (
-   'pm_project',		-- content_type
-   'content_revision',		-- supertype	
-   'Project',			-- pretty_name 
-   'Projects',			-- pretty_plural
-   'pm_projects',		-- table_name 
-   'project_id',		-- id_column 
-   'pm_project__name'		-- name_method
+   'pm_project',                -- content_type
+   'content_revision',          -- supertype    
+   'Project',                   -- pretty_name 
+   'Projects',                  -- pretty_plural
+   'pm_projects',     -- table_name 
+   'project_id',       -- id_column 
+   'pm_project__name'           -- name_method
  );
 
 
+-- other fields are added in too. See the -custom script.
+
+
 -- ROLES
 
 create sequence pm_role_seq start 4;
@@ -106,7 +110,6 @@
 
 insert into pm_roles (role_id, one_line, description, sort_order) values ('1','Lead','Team members who are responsible for the completion of the project','10');
 insert into pm_roles (role_id, one_line, description, sort_order) values ('2','Player','A person on the team responsible for completion of the project','20');
-
 insert into pm_roles (role_id, one_line, description, sort_order, is_observer_p) values ('3','Watcher','A person interested in developments, possibly helping out on it.','30','t');
 
 
@@ -131,7 +134,7 @@
 
 create table pm_project_assignment (
         project_id              integer
-                                constraint pm_project_role_map_project_fk
+                                constraint pm_proj_role_map_project_fk
                                 references pm_projects
                                 on delete cascade,
         role_id                 integer
@@ -157,6 +160,25 @@
 -- the information that we keep revisions on is in the 
 -- pm_task_revisions table, the rest is in pm_task
 
+create sequence pm_task_status_seq start 3;
+
+create table pm_task_status (
+        status_id               integer
+                                constraint pm_task_status_pk
+                                primary key,
+        description             varchar(100),
+        -- closed or open
+        status_type             char(1) default 'c'
+                                constraint pm_task_status_type_ck
+                                check (status_type in ('c','o'))
+);
+
+insert into pm_task_status (status_id, description, status_type) values
+(1, 'Open', 'o');
+insert into pm_task_status (status_id, description, status_type) values
+(2, 'Closed', 'c');
+
+
 create sequence pm_tasks_number_seq;
 
 create table pm_tasks (
@@ -166,7 +188,10 @@
                                         on delete cascade
                                         constraint pm_task_task_id_pk
                                         primary key,
-        task_number                     integer
+        task_number                     integer,
+        status                          integer
+                                        constraint pm_tasks_task_status_fk
+                                        references pm_task_status
 );
 
 
@@ -206,7 +231,7 @@
 -- create the content type
 select content_type__create_type (
    'pm_task',                   -- content_type
-   'content_revision',		-- supertype	
+   'content_revision',          -- supertype    
    'Task',                      -- pretty_name 
    'Tasks',                     -- pretty_plural
    'pm_tasks_revisions',        -- table_name (should this be pm_task?)
@@ -428,11 +453,31 @@
 );
 
 
-comment on table pm_project_assignment is '
-  Maps who is a part of what project, and in what capacity
+comment on table pm_task_assignment is '
+  Maps who is a part of what task, and in what capacity
 ';
 
+create table pm_task_xref (
+        task_id_1               integer
+                                constraint pm_task_xref_task1_nn
+                                not null
+                                constraint pm_task_xref_task1_fk
+                                references pm_tasks(task_id)
+                                on delete cascade,
+        task_id_2               integer
+                                constraint pm_task_xref_task2_nn
+                                not null
+                                constraint pm_task_xref_task2_fk
+                                references pm_tasks(task_id)
+                                on delete cascade,
+        constraint pm_task_xref_lt check (task_id_1 < task_id_2)
+);
 
+comment on table pm_task_xref is '
+  Maps related tasks.
+';
+
+
 -- PROCESSES
 
 create sequence pm_process_seq;
