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';
