-- packages/acs-events/sql/postgresql/activity-create.sql -- -- @author W. Scott Meeks -- @author Gary Jin (gjin@arsdigita.com) -- -- @ported 2001-06-26 -- -- $Id: activity-create.sql,v 1.11.2.1 2019/04/09 07:05:07 michaela Exp $ -- -- procedure inline_0/0 -- CREATE OR REPLACE FUNCTION inline_0( ) RETURNS integer AS $$ DECLARE attr_id acs_attributes.attribute_id%TYPE; BEGIN -- Event object PERFORM acs_object_type__create_type ( 'acs_activity', -- object_type 'Activity', -- pretty_name 'Activities', -- pretty_plural 'acs_object', -- supertype 'acs_activities', -- table_name 'activity_id', -- id_column 'null', -- package_name (default) 'f', -- abstract_p (default) null, -- type_extension_table (default) null -- name_method (default) ); -- Event attributes attr_id := acs_attribute__create_attribute ( 'acs_activity', -- object_type 'name', -- attribute_name 'string', -- data_type 'Name', -- pretty_name 'Names', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_activity', -- object_type 'description', -- attribute_name 'string', -- data_type 'Description', -- pretty_name 'Descriptions', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_activity', -- object_type 'html_p', -- attribute_name 'boolean', -- data_type 'HTML?', -- pretty_name 'HTML?', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_activity', -- object_type 'status_summary', -- attribute_name 'string', -- data_type 'Status Summary', -- pretty_name 'Status Summaries', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); return 0; END; $$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); -- The activities table create table acs_activities ( activity_id integer constraint acs_activities_activity_id_fk references acs_objects(object_id) on delete cascade constraint acs_activities_activity_id_pk primary key, name varchar(255) not null, description text, -- is the activity description written in html? html_p boolean default 'f', status_summary varchar(255) ); comment on table acs_activities is ' Represents what happens during an event '; create table acs_activity_object_map ( activity_id integer constraint acs_act_obj_mp_activity_id_fk references acs_activities on delete cascade, object_id integer constraint acs_act_obj_mp_object_id_fk references acs_objects(object_id) on delete cascade, constraint acs_act_obj_mp_pk primary key(activity_id, object_id) ); comment on table acs_activity_object_map is ' Maps between an activity and multiple ACS objects. '; -- Activity API (all have activity_id as parameter)) -- -- new() -- delete() -- -- name() -- edit (name,description,html_p,status_summary) -- -- object_map (object_id) -- object_unmap (object_id) -- added select define_function_args('acs_activity__new','activity_id;null,name,description;null,html_p;f,status_summary;null,object_type;acs_activity,creation_date;now(),creation_user;null,creation_ip;null,context_id;null'); -- -- procedure acs_activity__new/10 -- -- -- Create a new activity -- -- @author W. Scott Meeks -- -- @param activity_id Id to use for new activity -- @param name Name of the activity -- @param description Description of the activity -- @param html_p Is the description HTML? -- @param status_summary Additional status note (optional) -- @param object_type 'acs_activity' -- @param creation_date default now() -- @param creation_user acs_object param -- @param creation_ip acs_object param -- @param context_id acs_object param -- -- @return The id of the new activity. CREATE OR REPLACE FUNCTION acs_activity__new( new__activity_id integer, -- default null, new__name varchar, new__description text, -- default null, new__html_p boolean, -- default 'f', new__status_summary text, -- default null, new__object_type varchar, -- default 'acs_activity' new__creation_date timestamptz, -- default now(), new__creation_user integer, -- default null, new__creation_ip varchar, -- default null, new__context_id integer -- default null ) RETURNS integer AS $$ DECLARE v_activity_id acs_activities.activity_id%TYPE; BEGIN v_activity_id := acs_object__new( new__activity_id, -- object_id new__object_type, -- object_type new__creation_date, -- creation_date new__creation_user, -- creation_user new__creation_ip, -- creation_ip new__context_id, -- context_id 't', -- security_inherit_p new__name, -- title null -- package_id ); insert into acs_activities (activity_id, name, description, html_p, status_summary) values (v_activity_id, new__name, new__description, new__html_p, new__status_summary); return v_activity_id; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_activity__delete','activity_id'); -- -- procedure acs_activity__delete/1 -- -- Deletes an activity -- -- @author W. Scott Meeks -- -- @param activity_id Id of activity to delete -- -- @return 0 (procedure dummy) -- CREATE OR REPLACE FUNCTION acs_activity__delete( delete__activity_id integer ) RETURNS integer AS $$ DECLARE BEGIN -- Cascade will cause delete from acs_activities -- and acs_activity_object_map PERFORM acs_object__delete(delete__activity_id); return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_activity__name','activity_id'); -- -- procedure acs_activity__name/1 -- -- -- Get name of this activity -- -- @author gjin@arsdigita.com -- -- @param activity_id -- -- @return Name of activity -- CREATE OR REPLACE FUNCTION acs_activity__name( name__activity_id integer ) RETURNS varchar AS $$ DECLARE v_activity_name acs_activities.name%TYPE; BEGIN select name into v_activity_name from acs_activities where activity_id = name__activity_id; return v_activity_name; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_activity__edit','activity_id,name;null,description;null,html_p;null,status_summary;null'); -- -- procedure acs_activity__edit/5 -- -- Update the name or description of an activity -- -- @author W. Scott Meeks -- -- @param activity_id activity to update -- @param name optional New name for this activity -- @param description optional New description for this activity -- @param html_p optional New value of html_p for this activity -- @param status_summary optional New value of status_summary for this activity -- -- @return 0 (procedure dummy) -- CREATE OR REPLACE FUNCTION acs_activity__edit( edit__activity_id integer, edit__name varchar, -- default null, edit__description text, -- default null, edit__html_p boolean, -- default null edit__status_summary text -- default null ) RETURNS integer AS $$ DECLARE BEGIN update acs_activities set name = coalesce(edit__name, name), description = coalesce(edit__description, description), html_p = coalesce(edit__html_p, html_p), status_summary = coalesce(edit__status_summary, status_summary) where activity_id = edit__activity_id; update acs_objects set title = coalesce(edit__name, title) where object_id = edit__activity_id; return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_activity__object_map','activity_id,object_id'); -- -- procedure acs_activity__object_map/2 -- -- Adds an object mapping to an activity -- -- @author W. Scott Meeks -- -- @param activity_id id of activity to add mapping to -- @param object_id id of object to add mapping for -- -- @return 0 (procedure dummy) -- CREATE OR REPLACE FUNCTION acs_activity__object_map( object_map__activity_id integer, object_map__object_id integer ) RETURNS integer AS $$ DECLARE BEGIN insert into acs_activity_object_map (activity_id, object_id) values (object_map__activity_id, object_map__object_id); return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_activity__object_unmap','activity_id,object_id'); -- -- procedure acs_activity__object_unmap/2 -- -- -- Removes an object mapping to an activity -- -- @author W. Scott Meeks -- -- @param activity_id id of activity to add mapping to -- @param object_id id of object to add mapping for -- -- @return 0 (procedure dummy) -- CREATE OR REPLACE FUNCTION acs_activity__object_unmap( object_unmap__activity_id integer, object_unmap__object_id integer ) RETURNS integer AS $$ DECLARE BEGIN delete from acs_activity_object_map where activity_id = object_unmap__activity_id and object_id = object_unmap__object_id; return 0; END; $$ LANGUAGE plpgsql;