Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-communities-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-communities-package-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/dotlrn/sql/postgresql/dotlrn-communities-package-create.sql 29 Mar 2002 20:21:49 -0000 1.1 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-communities-package-create.sql 2 Jul 2002 18:40:08 -0000 1.2 @@ -24,6 +24,7 @@ -- @author Ben Adida (ben@openforce.net) -- @author yon (yon@openforce.net -- @author arjun (arjun@openforce.net) +-- @author dan chak (chak@openforce.net) -- @creation-date September 20th, 2001 (redone) -- @version $Id$ -- @@ -37,11 +38,11 @@ create function dotlrn_community_type__new (varchar,varchar,varchar,varchar,varchar) returns varchar as ' DECLARE - p_community_type alias for $1; - p_parent_type alias for $2; - p_pretty_name alias for $3; - p_pretty_plural alias for $4; - p_description alias for $5; + p_community_type alias for $1; + p_parent_type alias for $2; + p_pretty_name alias for $3; + p_pretty_plural alias for $4; + p_description alias for $5; BEGIN return dotlrn_community_type__new( p_community_type, @@ -85,13 +86,13 @@ from dual; PERFORM acs_object_type__create_type ( + v_parent_object_type, p_community_type, p_community_type, p_community_type, - v_parent_object_type, - ''dotlrn_communities'', - ''community_id'', v_unique_name, + v_unique_name, + v_unique_name, ''acs_group.name'' ); @@ -103,9 +104,17 @@ insert into dotlrn_community_types - (community_type, pretty_name, description, package_id, supertype) + (community_type, + pretty_name, + description, + package_id, + supertype) values - (p_community_type, p_pretty_name, p_description, p_package_id, p_parent_type); + (p_community_type, + p_pretty_name, + p_description, + p_package_id, + p_parent_type); return p_community_type; END; @@ -131,15 +140,21 @@ returns varchar as ' DECLARE p_community_type alias for $1; + v_name dotlrn_community_types.pretty_name%TYPE; BEGIN - return name from dotlrn_community_types where community_type= p_community_type; + select dotlrn_community_types.pretty_name + into v_name + from dotlrn_community_types + where dotlrn_community_types.community_type = p_community_type; + + return v_name; END; ' language 'plpgsql'; -- dotlrn_community -select define_function_args('dotlrn_community__new','community_id,parent_community_id,community_id,community_key,pretty_name,description,portal_id,portal_template_id,package_id,join_policy,creation_date,creation_user,creation_ip,context_id'); +select define_function_args('dotlrn_community__new','community_id,parent_community_id,community_type,community_key,pretty_name,description,portal_id,non_member_portal_id,package_id,join_policy,creation_date,creation_user,creation_ip,context_id'); select define_function_args('dotlrn_community__set_active_dates','community_id,start_date,end_date'); @@ -154,36 +169,35 @@ select define_function_args('dotlrn_community__url','community_id'); -create function dotlrn_community__new(integer,integer,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,timestamp,integer,varchar,integer) +create function dotlrn_community__new(integer,integer,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,timestamp,integer,varchar,integer) returns integer as ' DECLARE - p_community_id alias for $1; - p_parent_community_id alias for $2; + p_community_id alias for $1; + p_parent_community_id alias for $2; p_community_type alias for $3; - p_community_key alias for $4; - p_pretty_name alias for $5; - p_description alias for $6; - p_portal_id alias for $7; - p_portal_template_id alias for $8; - p_package_id alias for $9; - p_join_policy alias for $10; - p_creation_date alias for $11; - p_creation_user alias for $12; - p_creation_ip alias for $13; - p_context_id alias for $14; - c_id integer; + p_community_key alias for $4; + p_pretty_name alias for $5; + p_description alias for $6; + archived_p alias for $7 + p_portal_id alias for $8; + p_non_member_portal_id alias for $9; + p_package_id alias for $10; + p_join_policy alias for $11; + p_creation_date alias for $12; + p_creation_user alias for $13; + p_creation_ip alias for $14; + p_context_id alias for $15; + c_id integer; BEGIN c_id := acs_group__new ( + p_context_id, p_community_id, p_community_type, p_creation_date, p_creation_user, p_creation_ip, - NULL, - NULL, p_community_key, p_join_policy - p_context_id, ); insert into dotlrn_communities @@ -204,8 +218,10 @@ p_pretty_name, p_description, p_package_id, - p_portal_id, - p_portal_template_id); + p_portal_id, + p_archived_p, + p_portal_id, + p_non_member_portal_id); return c_id; END; @@ -215,11 +231,11 @@ create function dotlrn_community__set_active_dates(integer,date,date) returns integer as ' DECLARE - p_community_id alias for $1; - p_start_date alias for $2; + p_community_id alias for $1; + p_start_date alias for $2; p_end_date alias for $3; BEGIN - update dotlrn_communities + update dotlrn_communities_all set active_start_date = p_start_date, active_end_date = p_end_date where community_id = p_community_id; @@ -256,30 +272,46 @@ create function dotlrn_community__member_p(integer,integer) returns boolean as ' DECLARE - p_community_id alias for $1; + p_community_id alias for $1; p_party_id alias for $2; + v_member_p char(1); BEGIN - -- to do (ben) - return ''t''; + select CASE + WHEN count(*) = 0 + THEN 'f' + ELSE 't' + END + into v_member_p + from dual + where exists (select 1 + from dotlrn_member_rels_approved + where dotlrn_member_rels_approved.user_id = dotlrn_community.member_p.party_id + and dotlrn_member_rels_approved.community_id = dotlrn_community.member_p.community_id); + + return v_member_p; + END; ' language 'plpgsql'; create function dotlrn_community__admin_p(integer,integer) returns boolean as ' DECLARE - p_community_id alias for $1; + p_community_id alias for $1; p_party_id alias for $2; + v_rv char(1); BEGIN - IF acs_permission__permission_p(p_community_id, p_party_id, ''dotlrn_admin_community'') = ''t'' - then return ''t''; - end if; + -- THIS NEEDS TO BE CHECKED! + -- chak, 2002-07-01 + select CASE + WHEN acs_permission__permission_p(p_community_id, p_party_id, ''dotlrn_admin_community'') = ''f'' + THEN acs_permission__permission_p(p_community_id,p_party_id,''admin'') + ELSE ''t'' + END + into r_rv + from dual; - IF acs_permission__permission_p(p_community_id, p_party_id, ''admin'') = ''t'' - then return ''t''; - end if; - - return ''f''; + return r_rv; END; ' language 'plpgsql'; @@ -288,16 +320,42 @@ returns varchar as ' DECLARE p_community_id alias for $1; + v_node_id site_nodes.node_id%TYPE; BEGIN - return site_node__url(site_nodes.node_id) - from dotlrn_communities, + select site_nodes.node_id into v_node_id + from dotlrn_communities_all, site_nodes - where dotlrn_communities.community_id = p_community_id - and site_nodes.object_id = dotlrn_communities.package_id; + where dotlrn_communities_all.community_id = p_community_id + and site_nodes.object_id = dotlrn_communities_all.package_id; + + return v_node_id; + + exception + when no_data_found then + return ''; END; ' language 'plpgsql'; +create dotlrn_community__function has_subcomm_p(integer) +returns varchar as ' +DECLARE + p_community_id alias for $1; + v_rv char(1); +BEGIN + select CASE + WHEN count(*) = 0 + THEN 'f' + ELSE 't' + END + into r_rv + from dual + where dotlrn_communities_all.community_id = p_community_id; + + return r_rv; +END; +' language 'plpgsql'; + create view dotlrn_communities_full as select dotlrn_communities.*,