--
-- 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 $
--

@@ personal-community-init.sql

create table dotlrn_pers_communities (
    community_id                constraint dpc_community_id_fk
                                references dotlrn_communities_all (community_id),
    user_id                     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) 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;

create or replace package dotlrn_pers_community
is

    function new (
        community_id in dotlrn_pers_communities.community_id%TYPE default null,
        user_id in dotlrn_pers_communities.user_id%TYPE,
        community_key in dotlrn_communities_all.community_key%TYPE,
        pretty_name in dotlrn_communities_all.pretty_name%TYPE,
        description in dotlrn_communities_all.description%TYPE,
        package_id in dotlrn_communities_all.package_id%TYPE default null,
        portal_id in dotlrn_communities_all.portal_id%TYPE default null,
        non_member_portal_id in dotlrn_communities_all.non_member_portal_id%TYPE default null,
        join_policy in groups.join_policy%TYPE default null,
        creation_date in acs_objects.creation_date%TYPE default sysdate,
        creation_user in acs_objects.creation_user%TYPE default null,
        creation_ip in acs_objects.creation_ip%TYPE default null,
        context_id in acs_objects.context_id%TYPE default null
    ) return dotlrn_pers_communities.community_id%TYPE;

    procedure delete (
        community_id in dotlrn_pers_communities.community_id%TYPE
    );

end;
/
show errors

create or replace package body dotlrn_pers_community
is

    function new (
        community_id in dotlrn_pers_communities.community_id%TYPE default null,
        user_id in dotlrn_pers_communities.user_id%TYPE,
        community_key in dotlrn_communities_all.community_key%TYPE,
        pretty_name in dotlrn_communities_all.pretty_name%TYPE,
        description in dotlrn_communities_all.description%TYPE,
        package_id in dotlrn_communities_all.package_id%TYPE default null,
        portal_id in dotlrn_communities_all.portal_id%TYPE default null,
        non_member_portal_id in dotlrn_communities_all.non_member_portal_id%TYPE default null,
        join_policy in groups.join_policy%TYPE default null,
        creation_date in acs_objects.creation_date%TYPE default sysdate,
        creation_user in acs_objects.creation_user%TYPE default null,
        creation_ip in acs_objects.creation_ip%TYPE default null,
        context_id in acs_objects.context_id%TYPE default null
    ) return dotlrn_pers_communities.community_id%TYPE
    is
        v_community_id integer;
    begin

        v_community_id := dotlrn_community.new(
            community_id => community_id,
            community_type => 'dotlrn_pers_community',
            community_key => community_key,
            pretty_name => pretty_name,
            description => description,
            package_id => package_id,
            portal_id => portal_id,
            non_member_portal_id => non_member_portal_id,
            join_policy => join_policy,
            creation_date => creation_date,
            creation_user => creation_user,
            creation_ip => creation_ip,
            context_id => context_id
        );

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

        return v_community_id;

    end;

    procedure delete (
        community_id in dotlrn_pers_communities.community_id%TYPE
    )
    is
    begin
        delete
        from dotlrn_pers_communities
        where community_id = dotlrn_pers_community.delete.community_id;

        dotlrn_community.delete(community_id => community_id);
    end;

end;
/
show errors