Index: openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-functions-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/postgresql/Attic/project-manager-functions-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-functions-create.sql 15 Jun 2003 13:01:52 -0000 1.2 +++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-functions-create.sql 30 Jul 2003 20:42:05 -0000 1.3 @@ -12,7 +12,9 @@ -- name of the object. This is a convention ensuring that all objects -- can be identified. Now we have to build that function. In this case, -- we'll return a field called title as the name. + select define_function_args('pm_project__name', 'project_id'); + create or replace function pm_project__name (integer) returns varchar as ' declare @@ -28,43 +30,44 @@ -- Create a new root folder + select define_function_args('pm_project__new_root_folder', 'package_id'); + create function pm_project__new_root_folder (integer) returns integer as ' declare p_package_id alias for $1; - v_folder_id pm_root_folders.folder_id%TYPE; + v_folder_id cr_folders.folder_id%TYPE; v_folder_name cr_items.name%TYPE; begin + + -- raise notice ''in new root folder''; + -- Set the folder name v_folder_name := pm_project__new_unique_name (p_package_id); v_folder_id := content_folder__new ( v_folder_name, -- name ''Projects'', -- label ''Project Repository'', -- description - null -- parent_id + null -- parent_id ); - insert into pm_root_folders - (package_id, folder_id) - values - (p_package_id, v_folder_id); - -- Register the standard content types - PERFORM content_folder__register_content_type ( - v_folder_id, -- folder_id - ''pm_project'', -- content_type - ''f'' -- include_subtypes + PERFORM content_folder__register_content_type ( + v_folder_id, -- folder_id + ''pm_project'', -- content_type + ''f'' -- include_subtypes ); - PERFORM content_folder__register_content_type( - v_folder_id, -- folder_id - ''content_folder'', -- content_type - ''f'' -- include_subtypes (default) - ); + -- there is no facility in the API for adding in the package_id, + -- so we have to do it ourselves + update cr_folders + set package_id = p_package_id + where folder_id = v_folder_id; + -- TODO: Handle Permissions here for this folder. return v_folder_id; @@ -74,111 +77,83 @@ -- Returns the root folder corresponding to a particular package instance. -- Creates a new root folder if one does not exist for the specified package -- instance. -select define_function_args('pm_project__get_root_folder', 'package_id'); -create function pm_project__get_root_folder (integer) + +select define_function_args('pm_project__get_root_folder', 'package_id,create_if_not_present_p'); + +create function pm_project__get_root_folder (integer, boolean) returns integer as ' declare p_package_id alias for $1; - v_folder_id pm_root_folders.folder_id%TYPE; + p_create_if_not_present_p alias for $2; + + v_folder_id cr_folders.folder_id%TYPE; v_count integer; begin - select count(*) into v_count - from pm_root_folders - where package_id = p_package_id; - if v_count > 0 then - select folder_id into v_folder_id - from pm_root_folders - where package_id = p_package_id; - else - -- Must be a new instance. Create a new root folder. - v_folder_id := pm_project__new_root_folder(p_package_id); - end if; + -- this function is not complete yet. It makes duplicates - return v_folder_id; -end; ' language 'plpgsql'; + -- raise notice ''in get root folder p_create_if_not_present_p = %'',p_create_if_not_present_p; + select count(*) into v_count + from cr_folders + where package_id = p_package_id; --- Create a new project folder. --- Top level project folders should be created under the PROJECTS --- container folder. --- Refer to file-storage-package-create.sql -select define_function_args('pm_project__new_project_folder', 'folder_name, description, parent_id, creation_user, creation_ip, package_id'); -create function pm_project__new_project_folder (varchar, varchar, integer, - integer, varchar, integer) -returns integer as ' -declare - p_folder_name alias for $1; - p_description alias for $2; - p_parent_id alias for $3; - p_creation_user alias for $4; - p_creation_ip alias for $5; - p_package_id alias for $6; + -- raise notice ''count is % for package_id %'', v_count, p_package_id; - v_folder_id cr_folders.folder_id%TYPE; - v_parent_id cr_items.parent_id%TYPE; - v_name cr_items.name%TYPE; -begin - -- If p_parent_id is null then treat the new folder as - -- a new root project folder. Therefore set parent_id to - -- the PROJECTS folder where all root project folders should - -- reside. - if p_parent_id is null - then - v_parent_id := pm_project__get_root_folder(p_package_id); + if v_count > 1 then + raise exception ''More than one project repository for this application instance''; + elsif v_count = 1 then + select folder_id into v_folder_id + from cr_folders + where package_id = p_package_id; else - v_parent_id := p_parent_id; - end if; + if p_create_if_not_present_p = true then + -- Must be a new instance. Create a new root folder. + raise notice ''creating a new root repository folder''; + v_folder_id := pm_project__new_root_folder(p_package_id); + else + -- raise notice ''setting to null''; + v_folder_id := null; + end if; + end if; - -- Set the folder name - v_name := pm_project__new_unique_name (p_package_id); + -- raise notice ''v_folder_id is %'', v_folder_id; - -- Create a new folder - v_folder_id := content_folder__new ( - v_name, -- name - p_folder_name, -- label - p_description, -- description - v_parent_id, -- parent_id - null, -- context_id - null, -- folder_id - now(), -- creation_date - p_creation_user, -- creation_user - p_creation_ip -- creation_ip - ); - - -- Register the standard content types - PERFORM content_folder__register_content_type ( - v_folder_id, -- folder_id - ''pm_project'', -- content_type - ''t'' -- include_subtypes - ); - - PERFORM content_folder__register_content_type( - v_folder_id, -- folder_id - ''content_folder'', -- content_type - ''t'' -- include_subtypes (default) - ); - - -- TODO: Handle Permissions here for this folder. - return v_folder_id; -end;' language 'plpgsql'; +end; ' language 'plpgsql'; -- Create a project item. + -- A project item should be placed within a folder. Therefore a new project -- item is associated with creating a new project folder that will contain -- the project item. A new root project folder will be created if parent_id -- is null. Otherwise a project folder will be created as a sub-folder -- of an existing project folder. + select define_function_args('pm_project__new_project_item', 'project_id, project_name, project_code, parent_id, goal, description, deadline_scheduling, planned_start_date, planned_end_date, actual_start_date, actual_end_date, ongoing_p, creation_date, creation_user, creation_ip, package_id'); -create function pm_project__new_project_item (integer, varchar, varchar, - integer, varchar, varchar, char(1), timestamptz, timestamptz, - timestamptz, timestamptz, char(1), timestamptz, integer, - varchar, integer) -returns integer as ' + +create function pm_project__new_project_item ( + integer, -- project_id + varchar, -- project_name + varchar, -- project_code + integer, -- parent_id + varchar, -- goal + varchar, -- description + char(1), -- deadline_scheduling + timestamptz, -- planned_start_date + timestamptz, -- planned_end_date + timestamptz, -- actual_start_date + timestamptz, -- actual_end_date + char(1), -- ongoing_p + timestamptz, -- creation_date + integer, -- creation_user + varchar, -- creation_ip + integer -- package_id +) returns integer +as ' declare p_project_id alias for $1; p_project_name alias for $2; @@ -204,19 +179,17 @@ begin select acs_object_id_seq.nextval into v_id from dual; - -- Need to create a new project folder for the project item. - -- If p_parent_id is null then a new root project folder will - -- be created. Otherwise a new folder will be created under - -- the folder with the folder_id equal to p_parent_id. - v_parent_id := pm_project__new_project_folder ( - p_project_name, -- folder_name - p_description, -- description - p_parent_id, -- parent_id - p_creation_user, -- creation_user - p_creation_ip, -- creation_ip - p_package_id -- package_id - ); + v_parent_id := pm_project__get_root_folder (p_package_id, ''t''); + -- raise notice ''v_parent_id (%) p_parent_id (%)'', v_parent_id, p_parent_id; + + if p_parent_id is not null + then + v_parent_id = p_parent_id; + end if; + + -- raise notice ''v_parent_id (%) p_parent_id (%)'', v_parent_id, p_parent_id; + v_item_id := content_item__new ( p_project_name, -- name v_parent_id, -- parent_id @@ -252,12 +225,12 @@ PERFORM content_item__set_live_revision (v_revision_id); insert into pm_projects ( - project_id, project_folder_id, project_name, project_code, + project_id, project_name, project_code, goal, deadline_scheduling, planned_start_date, planned_end_date, actual_start_date, actual_end_date, ongoing_p) values ( - v_revision_id, v_parent_id, p_project_name, p_project_code, + v_revision_id, p_project_name, p_project_code, p_goal, p_deadline_scheduling, p_planned_start_date, p_planned_end_date, p_actual_start_date, p_actual_end_date, p_ongoing_p); @@ -273,27 +246,125 @@ -- The delete function deletes a record and all related overhead. -select define_function_args('pm_project___delete_project_item', 'project_id'); + +select define_function_args('pm_project__delete_project_item', 'project_id'); + create or replace function pm_project__delete_project_item (integer) returns integer as ' declare p_project_id alias for $1; + v_child cr_items%ROWTYPE; begin + raise NOTICE ''Deleting pm_project...''; + delete from acs_permissions where object_id = p_project_id; + for v_child in select + item_id + from + cr_items + where + parent_id = p_project_id and + content_type = ''pm_project'' + LOOP + PERFORM pm_project__delete_project_item(v_child.item_id); + end loop; + delete from pm_projects where project_id = p_project_id; - raise NOTICE ''Deleting pm_project...''; - PERFORM content_item__delete(p_project_id); return 0; end;' language 'plpgsql'; +select define_function_args('pm_project__new_project_revision', 'item_id, project_name, project_code, parent_id, goal, description, deadline_scheduling, planned_start_date, planned_end_date, actual_start_date, actual_end_date, ongoing_p, creation_date, creation_user, creation_ip, package_id'); + +create function pm_project__new_project_revision ( + integer, -- item_id + varchar, -- project_name + varchar, -- project_code + integer, -- parent_id + varchar, -- goal + varchar, -- description + char(1), -- deadline_scheduling + timestamptz, -- planned_start_date + timestamptz, -- planned_end_date + timestamptz, -- actual_start_date + timestamptz, -- actual_end_date + char(1), -- ongoing_p + timestamptz, -- creation_date + integer, -- creation_user + varchar, -- creation_ip + integer -- package_id +) returns integer +as ' +declare + p_item_id alias for $1; + p_project_name alias for $2; + p_project_code alias for $3; + p_parent_id alias for $4; + p_goal alias for $5; + p_description alias for $6; + p_deadline_scheduling alias for $7; + p_planned_start_date alias for $8; + p_planned_end_date alias for $9; + p_actual_start_date alias for $10; + p_actual_end_date alias for $11; + p_ongoing_p alias for $12; + p_creation_date alias for $13; + p_creation_user alias for $14; + p_creation_ip alias for $15; + p_package_id alias for $16; + + v_revision_id cr_revisions.revision_id%TYPE; +begin + + -- the item_id is the project_id + + v_revision_id := content_revision__new ( + p_project_name, -- title + p_description, -- description + now(), -- publish_date + ''text/plain'', -- mime_type + NULL, -- nls_language + NULL, -- data + p_item_id, -- item_id + NULL, -- revision_id + now(), -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + + PERFORM content_item__set_live_revision (v_revision_id); + + insert into pm_projects ( + project_id, project_name, project_code, + goal, deadline_scheduling, planned_start_date, + planned_end_date, actual_start_date, actual_end_date, + ongoing_p) + values ( + v_revision_id, p_project_name, p_project_code, + p_goal, p_deadline_scheduling, p_planned_start_date, + p_planned_end_date, p_actual_start_date, + p_actual_end_date, p_ongoing_p); + + PERFORM acs_permission__grant_permission( + v_revision_id, + p_creation_user, + ''admin'' + ); + + return v_revision_id; +end;' language 'plpgsql'; + + + -- Creates and returns a unique name. + select define_function_args('pm_project__new_unique_name', 'package_id'); + create function pm_project__new_unique_name (integer) returns text as ' declare @@ -315,3 +386,225 @@ return v_name; end;' language 'plpgsql'; + +---------------------------------- +-- Tasks +---------------------------------- + +-- When we created the acs object type above, we specified a +-- 'name_method'. This is the name of a function that will return the +-- name of the object. This is a convention ensuring that all objects +-- can be identified. Now we have to build that function. In this case, +-- we'll return a field called title as the name. + +select define_function_args('pm_task__name', 'task_id'); + +create or replace function pm_task__name (integer) +returns varchar as ' +declare + p_pm_task_id alias for $1; + v_pm_task_name pm_task.title%TYPE; +begin + select title into v_pm_project_name + from pm_tasks_revisionsx + where item_id = p_pm_task_id limit 1; + return v_pm_task_name; +end; +' language 'plpgsql'; + + +-- Create a task item. + +-- A task should be placed within a project or another task. +-- If it is not associated with a project, then it is placed in the root +-- project repository folder. + +select define_function_args('pm_task__new_task_item', 'task_id, project_id, title, description, start_date, end_date, percent_complete, creation_date, creation_user, creation_ip, package_id'); + +create function pm_task__new_task_item ( + integer, -- task_id + integer, -- project_id + varchar, -- title + varchar, -- description + timestamptz, -- start_date + timestamptz, -- end_date + numeric, -- percent_complete + timestamptz, -- creation_date + integer, -- creation_user + varchar, -- creation_ip + integer -- package_id +) returns integer +as ' +declare + p_task_id alias for $1; + p_project_id alias for $2; + p_title alias for $3; + p_description alias for $4; + p_start_date alias for $5; + p_end_date alias for $6; + p_percent_complete alias for $7; + p_creation_date alias for $8; + p_creation_user alias for $9; + p_creation_ip alias for $10; + p_package_id alias for $11; + + v_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_id cr_items.item_id%TYPE; + v_task_number integer; +begin + select acs_object_id_seq.nextval into v_id from dual; + + -- We want to put the task under the project item + + -- create the task_number + + v_item_id := content_item__new ( + p_title, -- name + p_project_id, -- parent_id + v_id, -- item_id + null, -- locale + now(), -- creation_date + p_creation_user, -- creation_user + p_package_id, -- context_id + p_creation_ip, -- creation_ip + ''content_item'', -- item_subtype + ''pm_task'', -- content_type + p_title, -- title + p_description, -- description + ''text/plain'', -- mime_type + null, -- nls_language + null -- data + ); + + v_revision_id := content_revision__new ( + p_title, -- title + p_description, -- description + now(), -- publish_date + ''text/plain'', -- mime_type + NULL, -- nls_language + NULL, -- data + v_item_id, -- item_id + NULL, -- revision_id + now(), -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + + PERFORM content_item__set_live_revision (v_revision_id); + + insert into pm_tasks ( + task_id, task_number) + values ( + v_item_id, v_task_number); + + insert into pm_tasks_revisions ( + task_revision_id, start_date, end_date, percent_complete) + values ( + v_revision_id, p_start_date, p_end_date, p_percent_complete); + + PERFORM acs_permission__grant_permission( + v_revision_id, + p_creation_user, + ''admin'' + ); + + return v_revision_id; +end;' language 'plpgsql'; + + +select define_function_args('pm_task__new_task_revision', 'task_id, project_id, title, description, start_date, end_date, percent_complete, creation_date, creation_user, creation_ip, package_id'); + +create function pm_task__new_task_revision ( + integer, -- task_id + integer, -- project_id + varchar, -- title + varchar, -- description + timestamptz, -- start_date + timestamptz, -- end_date + numeric, -- percent_complete + timestamptz, -- creation_date + integer, -- creation_user + varchar, -- creation_ip + integer -- package_id +) returns integer +as ' +declare + p_task_id alias for $1; + p_project_id alias for $2; + p_title alias for $3; + p_description alias for $4; + p_start_date alias for $5; + p_end_date alias for $6; + p_percent_complete alias for $7; + p_creation_date alias for $8; + p_creation_user alias for $9; + p_creation_ip alias for $10; + p_package_id alias for $11; + + v_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_id cr_items.item_id%TYPE; +begin + select acs_object_id_seq.nextval into v_id from dual; + + -- We want to put the task under the project item + + v_revision_id := content_revision__new ( + p_title, -- title + p_description, -- description + now(), -- publish_date + ''text/plain'', -- mime_type + NULL, -- nls_language + NULL, -- data + v_item_id, -- item_id + NULL, -- revision_id + now(), -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + + PERFORM content_item__set_live_revision (v_revision_id); + + insert into pm_tasks_revisions ( + task_revision_id, start_date, end_date, percent_complete) + values ( + v_revision_id, p_start_date, p_end_date, p_percent_complete); + + PERFORM acs_permission__grant_permission( + v_revision_id, + p_creation_user, + ''admin'' + ); + + return v_revision_id; +end;' language 'plpgsql'; + + +-- The delete function deletes a record and all related overhead. + +select define_function_args('pm_task__delete_task_item', 'task_id'); + +create or replace function pm_task__delete_task_item (integer) +returns integer as ' +declare + p_task_id alias for $1; +begin + -- will not work because permissions are on revision_id not + -- item_id + -- delete from acs_permissions + -- where object_id = p_task_id; + + delete from pm_tasks_revisions + where task_revision_id in (select revision_id from pm_tasks_revisionsx where item_id = p_task_id); + + delete from pm_tasks + where task_id = p_task_id; + + raise NOTICE ''Deleting pm_task...''; + + PERFORM content_item__delete(p_task_id); + return 0; +end;' language 'plpgsql'; + +