--
-- create personal communities
--
-- @author yon (yon@openforce.net)
-- @creation-date August 18th, 2002
-- @version $Id: personal-community-create.sql,v 1.1 2003/11/14 20:57:06 janines Exp $
--

\i personal-community-init.sql

create table dotlrn_pers_communities (
    community_id                integer
                                constraint dpc_community_id_fk
                                references dotlrn_communities_all (community_id),
    user_id                     integer
                                constraint dpc_user_id_fk
                                references users (user_id),
    constraint dotlrn_pers_communities_pk
    primary key (community_id, user_id)
);

create view dotlrn_pers_communities_full
as
    select dotlrn_communities.*,
           dotlrn_pers_communities.user_id,
           dotlrn_community__url(dotlrn_communities.community_id::integer) as url,
           groups.join_policy
    from dotlrn_communities,
         dotlrn_pers_communities,
         groups
    where dotlrn_communities.community_id = dotlrn_pers_communities.community_id
    and dotlrn_communities.community_id = groups.group_id;

select define_function_args('dotlrn_pers_community__new','community_id,user_id,community_key,pretty_name,description,package_id,portal_id,non_member_portal_id,join_policy,creation_date,creation_user,creation_ip,context_id');

create function dotlrn_pers_community__new (integer,integer,varchar,varchar,varchar,integer,integer,integer,varchar,timestamp,integer,varchar,integer)
returns integer as '
declare
    p_community_id                  alias for $1;
    p_user_id                       alias for $2;
    p_community_key                 alias for $3;
    p_pretty_name                   alias for $4;
    p_description                   alias for $5;
    p_package_id                    alias for $6;
    p_portal_id                     alias for $7;
    p_non_member_portal_id          alias for $8;
    p_join_policy                   alias for $9;
    p_creation_date                 alias for $10;
    p_creation_user                 alias for $11;
    p_creation_ip                   alias for $12;
    p_context_id                    alias for $13;
    v_community_id                  integer;
begin
    v_community_id := dotlrn_community__new(
        p_community_id,
        null,
        ''dotlrn_pers_community'',
        p_community_key,
        p_pretty_name,
        p_description,
        ''f'',
        p_portal_id,
        p_non_member_portal_id,
        p_package_id,
        p_join_policy,
        p_creation_date,
        p_creation_user,
        p_creation_ip,
        p_context_id
    );

    insert
    into dotlrn_pers_communities
    (community_id, user_id)
    values
    (v_community_id, p_user_id);

    return v_community_id;
end;
' language 'plpgsql';

select define_function_args('dotlrn_pers_community__delete','community_id');

create function dotlrn_pers_community__delete (integer)
returns integer as '
declare
    p_community_id                  alias for $1;
begin
    delete
    from dotlrn_pers_communities
    where community_id = p_community_id;

    perform dotlrn_community__delete(p_community_id);
    return(0);
end;
' language 'plpgsql';