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.1 -r1.2 --- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-functions-create.sql 21 May 2003 22:42:54 -0000 1.1 +++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-functions-create.sql 15 Jun 2003 13:01:52 -0000 1.2 @@ -1,88 +1,317 @@ -- -- packages/project-manager/sql/postgresql/project-manager-functions-create.sql -- --- @author jade@bread.com +-- @author jade@bread.com, ncarroll@ee.usyd.edu.au -- @creation-date 2003-05-15 -- @cvs-id $Id$ -- -- -select define_function_args('pm_project__new','project_id,project_name,project_code,parent_project_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,context_id'); +-- 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_project__name', 'project_id'); +create or replace function pm_project__name (integer) +returns varchar as ' +declare + p_pm_project_id alias for $1; + v_pm_project_name pm_projects.project_name%TYPE; +begin + select project_name into v_pm_project_name + from pm_projects + where project_id = p_pm_project_id; + return v_pm_project_name; +end; +' language 'plpgsql'; -create or replace function pm_project__new (integer,varchar,varchar,integer,varchar,varchar,char(1),timestamptz,timestamptz,timestamptz,timestamptz,char(1),timestamptz,integer,varchar,integer) + +-- 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_project_id alias for $1; - p_project_name alias for $2; - p_project_code alias for $3; - p_parent_project_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_context_id alias for $16; - v_pm_project_id int; + p_package_id alias for $1; + + v_folder_id pm_root_folders.folder_id%TYPE; + v_folder_name cr_items.name%TYPE; begin - v_pm_project_id := acs_object__new ( - p_project_id, - ''pm_project'', - p_creation_date, - p_creation_user, - p_creation_ip, - p_context_id - ); - insert into pm_project - (project_id,project_name,project_code,parent_project_id,goal,description,deadline_scheduling,planned_start_date,planned_end_date,actual_start_date,actual_end_date,ongoing_p) - values - (v_pm_project_id, p_project_name, p_project_code, p_parent_project_id, p_goal, p_description, 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_pm_project_id, - p_creation_user, - ''admin'' - ); - return v_pm_project_id; + -- 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 + ); + + 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 + ''content_folder'', -- content_type + ''f'' -- include_subtypes (default) + ); + + -- TODO: Handle Permissions here for this folder. + + return v_folder_id; end;' language 'plpgsql'; -/* The __delete function deletes a record and all related overhead. */ - -select define_function_args('pm_project___delete','project_id'); -create or replace function pm_project__delete (integer) + +-- 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) returns integer as ' declare - p_pm_project_id alias for $1; + p_package_id alias for $1; + v_folder_id pm_root_folders.folder_id%TYPE; + v_count integer; begin - delete from acs_permissions - where object_id = p_pm_project_id; - delete from pm_project - where project_id = p_pm_project_id; + 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; + + return v_folder_id; + +end; ' language 'plpgsql'; + + +-- 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; + + 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); + else + v_parent_id := p_parent_id; + end if; + + -- Set the folder name + v_name := pm_project__new_unique_name (p_package_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'; + + +-- 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 ' +declare + p_project_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_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_id cr_items.item_id%TYPE; + v_parent_id cr_items.parent_id%TYPE; +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_item_id := content_item__new ( + p_project_name, -- name + v_parent_id, -- parent_id + v_id, -- item_id + null, -- locale + now(), -- creation_date + p_creation_user, -- creation_user + p_parent_id, -- context_id + p_creation_ip, -- creation_ip + ''content_item'', -- item_subtype + ''pm_project'', -- content_type + p_project_name, -- title + p_description, -- description + ''text/plain'', -- mime_type + null, -- nls_language + null -- data + ); + + 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 + 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_projects ( + project_id, project_folder_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, + 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'; + + +-- The delete function deletes a record and all related overhead. +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; +begin + delete from acs_permissions + where object_id = p_project_id; + + delete from pm_projects + where project_id = p_project_id; + raise NOTICE ''Deleting pm_project...''; - PERFORM acs_object__delete(p_pm_project_id); + + PERFORM content_item__delete(p_project_id); return 0; end;' language 'plpgsql'; -/* 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_project___name','project_id'); -create or replace function pm_project__name (integer) -returns varchar as ' +-- 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 - p_pm_project_id alias for $1; - v_pm_project_name pm_project.project_name%TYPE; + p_package_id alias for $1; + + v_name cr_items.name%TYPE; + v_package_key apm_packages.package_key%TYPE; + v_id integer; begin - select project_name into v_pm_project_name - from pm_project - where project_id = p_pm_project_id; - return v_pm_project_name; -end; -' language 'plpgsql'; + select package_key into v_package_key from apm_packages + where package_id = p_package_id; + + select acs_object_id_seq.nextval into v_id from dual; + + -- Set the name + select v_package_key || ''_'' || + to_char(current_timestamp, ''YYYYMMDD'') || ''_'' || + v_id into v_name; + + return v_name; +end;' language 'plpgsql';