Index: openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/postgresql/Attic/project-manager-drop.sql,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-drop.sql 21 May 2003 22:42:54 -0000 1.1 +++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-drop.sql 15 Jun 2003 13:01:52 -0000 1.2 @@ -1,39 +1,80 @@ --- packages/project-manager/sql/project-manager-drop.sql --- drop script --- --- @author jade@bread.com --- @creation-date 2003-05-15 --- @cvs-id $Id$ --- - ---drop package, which drops all functions created with define_function_args - -select drop_package('pm_project'); - ---drop permissions -delete from acs_permissions where object_id in (select project_id from pm_project); - ---drop objects -create function inline_0 () -returns integer as ' -declare - object_rec record; -begin - for object_rec in select object_id from acs_objects where object_type=''pm_project'' - loop - perform acs_object__delete( object_rec.object_id ); - end loop; - return 0; -end;' language 'plpgsql'; - -select inline_0(); -drop function inline_0(); - ---drop table -drop table pm_project; - ---drop type -select acs_object_type__drop_type( - 'pm_project', - 't' - ); \ No newline at end of file +-- packages/project-manager/sql/project-manager-drop.sql +-- drop script +-- +-- @author jade@bread.com +-- @creation-date 2003-05-15 +-- @cvs-id $Id$ +-- + +--drop permissions +delete from acs_permissions where object_id in (select project_id from pm_projects); + +-- unregister content_types from folder +create function inline_0 () +returns integer as ' +declare + v_folder_id cr_folders.folder_id%TYPE; + v_item_id cr_items.item_id%TYPE; + v_item_cursor RECORD; + +begin + select content_item__get_id(''projects'', null, ''f'') into v_folder_id from dual; + + -- delete all contents of projects folder + FOR v_item_cursor IN + select item_id + from cr_items + where parent_id = v_folder_id + LOOP + PERFORM pm_project__delete_project_item(v_item_cursor.item_id); + END LOOP; + + -- unregister_content_types + PERFORM content_folder__unregister_content_type ( + v_folder_id, -- folder_id + ''content_revision'', -- content_type + ''t'' -- include_subtypes + ); + PERFORM content_folder__unregister_content_type ( + v_folder_id, -- folder_id + ''pm_project'', -- content_type + ''t'' -- include_subtypes + ); + + -- this table must not hold reference to ''pm_project'' type + delete from cr_folder_type_map where content_type = ''pm_project''; + + -- delete projects folder + PERFORM content_folder__delete(v_folder_id); + + return 0; +end; +' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); + +-- drop package project-manager +--drop package, which drops all functions created with define_function_args +--select drop_package('pm_projects'); +drop function pm_project__name (integer); +drop function pm_project__new_root_folder (integer); +drop function pm_project__get_root_folder (integer); +drop function pm_project__new_project_folder (varchar, varchar, integer, + integer, varchar, integer); +drop function pm_project__new_project_item (integer, varchar, varchar, + integer, varchar, varchar, char(1), timestamptz, timestamptz, + timestamptz, timestamptz, char(1), timestamptz, integer, + varchar, integer); +drop function pm_project__delete_project_item (integer); +drop function pm_project__new_unique_name (integer); + +-- delete content_type 'pm_project' +select acs_object_type__drop_type( + 'pm_project', -- object_type + 't' -- cascade_p + ); + +--drop table +drop table pm_projects; +drop table pm_root_folders; \ No newline at end of file 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'; 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.1 -r1.2 --- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 21 May 2003 22:42:54 -0000 1.1 +++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 15 Jun 2003 13:01:52 -0000 1.2 @@ -8,59 +8,65 @@ -- -- packages/test-project/sql/postgresql/test-project-table-create.sql -- --- @author jader@bread.com and everyone else involved in this thread: http://openacs.org/forums/message-view?message_id=90742 +-- @author jader@bread.com, ncarroll@ee.usyd.edu.au and everyone else involved in this thread: http://openacs.org/forums/message-view?message_id=90742 -- @creation-date 2003-05-15 -- -create table pm_project ( +-- Project repository datamodel. +create table pm_projects ( project_id integer constraint project_manager_id_fk - references acs_objects(object_id) - constraint pm_project_id_pk + references cr_revisions on delete cascade + constraint pm_projects_id_pk primary key, + project_folder_id integer + constraint pm_project_folder_id_fk + references cr_folders, project_name varchar(255) - constraint pm_project_name_nn + constraint pm_projects_name_nn not null, -- a user-specified project_code varchar(255), - -- for subprojects - parent_project_id integer - constraint pm_project_parent_project_id_fk - references pm_project, goal varchar(4000), - description varchar(4000), -- is the deadline computed from the end date, or from -- today? -- e = end, t = today deadline_scheduling char(1) default 't' - constraint pm_project_dline_scheduling_ck + constraint pm_projects_dline_scheduling_ck check (deadline_scheduling in ('t','e')), planned_start_date timestamptz, planned_end_date timestamptz, actual_start_date timestamptz, actual_end_date timestamptz, ongoing_p char(1) default 'f' - constraint pm_project_ongoing_p_ck + constraint pm_projects_ongoing_p_ck check (ongoing_p in ('t','f')) ); -create function inline_0 () -returns integer as ' -begin - PERFORM acs_object_type__create_type ( - ''pm_project'', -- object_type - ''Project'', -- pretty_name - ''Projects'', -- pretty_plural - ''acs_object'', -- supertype - ''pm_project'', -- table_name - ''project_id'', -- id_column - null, -- package_name - ''f'', -- abstract_p - null, -- type_extension_table - ''pm_project__name'' -- name_method - ); - return 0; -end;' language 'plpgsql'; -select inline_0 (); -drop function inline_0 (); +-- 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 + ); + + +create table pm_root_folders ( + -- ID for this package instance + package_id integer + constraint pm_root_folder_package_id_fk + references apm_packages on delete cascade + constraint pm_root_folder_package_id_pk + primary key, + -- ID of the root folder + folder_id integer + constraint pm_root_folder_folder_id_fk + references cr_folders + constraint pm_root_folder_folder_id_un + unique +);