Index: openacs-4/packages/dotlrn/sql/oracle/upgrade/upgrade-1.0-2.0d1.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/upgrade/upgrade-1.0-2.0d1.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn/sql/oracle/upgrade/upgrade-1.0-2.0d1.sql 9 Oct 2003 08:12:49 -0000 1.1
@@ -0,0 +1,1762 @@
+--
+-- Copyright (C) 2001, 2002 MIT
+--
+-- This file is part of dotLRN.
+--
+-- dotLRN is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 2 of the License, or (at your option) any later
+-- version.
+--
+-- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+-- details.
+--
+
+--
+-- Create the Admin package
+--
+-- @author yon@openforce.net
+-- @version $Id: upgrade-1.0-2.0d1.sql,v 1.1 2003/10/09 08:12:49 peterm Exp $
+--
+
+create or replace package dotlrn_admin_profile_rel
+as
+ function new (
+ rel_id in dotlrn_admin_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_admin_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_admin_profile_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors
+
+create or replace package body dotlrn_admin_profile_rel
+as
+ function new (
+ rel_id in dotlrn_admin_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_admin_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_user_profile_rels.rel_id%TYPE;
+ v_group_id groups.group_id%TYPE;
+ begin
+ if group_id is null then
+ select min(group_id)
+ into v_group_id
+ from profiled_groups
+ where profile_provider = (select min(impl_id)
+ from acs_sc_impls
+ where impl_name = 'dotlrn_admin_profile_provider');
+ else
+ v_group_id := group_id;
+ end if;
+
+ v_rel_id := dotlrn_user_profile_rel.new(
+ rel_id => rel_id,
+ user_id => user_id,
+ portal_id => portal_id,
+ theme_id => theme_id,
+ id => id,
+ rel_type => rel_type,
+ group_id => v_group_id,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_admin_profile_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_admin_profile_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_admin_profile_rels
+ where rel_id = dotlrn_admin_profile_rel.del.rel_id;
+
+ dotlrn_user_profile_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors
+--
+-- Copyright (C) 2001, 2002 MIT
+--
+-- This file is part of dotLRN.
+--
+-- dotLRN is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 2 of the License, or (at your option) any later
+-- version.
+--
+-- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+-- details.
+--
+
+--
+-- create the dotLRN classes model
+--
+-- @author Ben Adida (ben@openforce.net)
+-- @author yon (yon@openforce.net)
+-- @creation-date 2001-08-18
+-- @version $Id: upgrade-1.0-2.0d1.sql,v 1.1 2003/10/09 08:12:49 peterm Exp $
+--
+
+create or replace package dotlrn_department
+is
+ function new (
+ department_key in dotlrn_departments.department_key%TYPE,
+ pretty_name in dotlrn_community_types.pretty_name%TYPE,
+ pretty_plural in acs_object_types.pretty_plural%TYPE default null,
+ description in dotlrn_community_types.description%TYPE,
+ package_id in dotlrn_community_types.package_id%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_departments.department_key%TYPE;
+
+ procedure del (
+ department_key in dotlrn_departments.department_key%TYPE
+ );
+end;
+/
+show errors
+
+create or replace package body dotlrn_department
+is
+ function new (
+ department_key in dotlrn_departments.department_key%TYPE,
+ pretty_name in dotlrn_community_types.pretty_name%TYPE,
+ pretty_plural in acs_object_types.pretty_plural%TYPE default null,
+ description in dotlrn_community_types.description%TYPE,
+ package_id in dotlrn_community_types.package_id%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_departments.department_key%TYPE
+ is
+ v_department_key dotlrn_departments.department_key%TYPE;
+ begin
+ v_department_key := dotlrn_community_type.new (
+ community_type => department_key,
+ parent_type => 'dotlrn_class_instance',
+ pretty_name => pretty_name,
+ pretty_plural => pretty_plural,
+ description => description,
+ package_id => package_id,
+ creation_date => creation_date,
+ creation_user => creation_user,
+ creation_ip => creation_ip,
+ context_id => context_id
+ );
+
+ insert
+ into dotlrn_departments
+ (department_key) values (v_department_key);
+
+ return v_department_key;
+ end;
+
+ procedure del (
+ department_key in dotlrn_departments.department_key%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_departments
+ where department_key = department_key;
+
+ dotlrn_community_type.del(department_key);
+ end;
+end;
+/
+show errors
+
+create or replace package dotlrn_class
+is
+ function new (
+ class_key in dotlrn_classes.class_key%TYPE,
+ department_key in dotlrn_departments.department_key%TYPE,
+ pretty_name in dotlrn_community_types.pretty_name%TYPE,
+ pretty_plural in acs_object_types.pretty_plural%TYPE default null,
+ description in dotlrn_community_types.description%TYPE,
+ package_id in dotlrn_community_types.package_id%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_classes.class_key%TYPE;
+
+ procedure del (
+ class_key in dotlrn_classes.class_key%TYPE
+ );
+end;
+/
+show errors
+
+create or replace package body dotlrn_class
+is
+ function new (
+ class_key in dotlrn_classes.class_key%TYPE,
+ department_key in dotlrn_departments.department_key%TYPE,
+ pretty_name in dotlrn_community_types.pretty_name%TYPE,
+ pretty_plural in acs_object_types.pretty_plural%TYPE default null,
+ description in dotlrn_community_types.description%TYPE,
+ package_id in dotlrn_community_types.package_id%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_classes.class_key%TYPE
+ is
+ v_class_key dotlrn_classes.class_key%TYPE;
+ begin
+ v_class_key := dotlrn_community_type.new (
+ community_type => class_key,
+ parent_type => department_key,
+ pretty_name => pretty_name,
+ pretty_plural => pretty_plural,
+ description => description,
+ package_id => package_id,
+ creation_date => creation_date,
+ creation_user => creation_user,
+ creation_ip => creation_ip,
+ context_id => context_id
+ );
+
+ insert
+ into dotlrn_classes
+ (class_key, department_key) values (v_class_key, department_key);
+
+ return v_class_key;
+ end;
+
+ procedure del (
+ class_key in dotlrn_classes.class_key%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_classes
+ where class_key = class_key;
+
+ dotlrn_community_type.del(class_key);
+ end;
+end;
+/
+show errors
+
+create or replace package dotlrn_class_instance
+is
+ function new (
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE default null,
+ class_key in dotlrn_class_instances.class_key%TYPE,
+ term_id in dotlrn_class_instances.term_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_class_instances.class_instance_id%TYPE;
+
+ procedure del (
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE
+ );
+end;
+/
+show errors
+
+create or replace package body dotlrn_class_instance
+is
+ function new (
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE default null,
+ class_key in dotlrn_class_instances.class_key%TYPE,
+ term_id in dotlrn_class_instances.term_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_class_instances.class_instance_id%TYPE
+ is
+ v_class_instance_id dotlrn_class_instances.class_instance_id%TYPE;
+ begin
+ v_class_instance_id := dotlrn_community.new (
+ community_id => class_instance_id,
+ community_type => class_key,
+ 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_class_instances
+ (class_instance_id, class_key, term_id)
+ values
+ (v_class_instance_id, class_key, term_id);
+
+ return v_class_instance_id;
+ end;
+
+ procedure del (
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_class_instances
+ where class_instance_id= class_instance_id;
+
+ dotlrn_community.del(community_id => class_instance_id);
+ end;
+end;
+/
+show errors
+--
+-- Copyright (C) 2001, 2002 MIT
+--
+-- This file is part of dotLRN.
+--
+-- dotLRN is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 2 of the License, or (at your option) any later
+-- version.
+--
+-- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+-- details.
+--
+
+
+--
+-- create dotLRN clubs model
+--
+-- @author yon (yon@openforce.net)
+-- @creation-date August 18th, 2001
+-- @version $Id: upgrade-1.0-2.0d1.sql,v 1.1 2003/10/09 08:12:49 peterm Exp $
+--
+
+create or replace package dotlrn_club
+is
+ function new (
+ club_id in dotlrn_clubs.club_id%TYPE default null,
+ 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_clubs.club_id%TYPE;
+
+ procedure del (
+ club_id in dotlrn_clubs.club_id%TYPE
+ );
+end;
+/
+show errors
+
+create or replace package body dotlrn_club
+is
+ function new (
+ club_id in dotlrn_clubs.club_id%TYPE default null,
+ 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_clubs.club_id%TYPE
+ is
+ v_club_id integer;
+ begin
+ v_club_id := dotlrn_community.new(
+ community_id => club_id,
+ community_type => 'dotlrn_club',
+ 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_clubs (club_id)
+ values (v_club_id);
+
+ return v_club_id;
+ end;
+
+ procedure del (
+ club_id in dotlrn_clubs.club_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_clubs
+ where club_id = dotlrn_club.del.club_id;
+
+ dotlrn_community.del(community_id => club_id);
+ end;
+end;
+/
+show errors
+--
+-- Copyright (C) 2001, 2002 MIT
+--
+-- This file is part of dotLRN.
+--
+-- dotLRN is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 2 of the License, or (at your option) any later
+-- version.
+--
+-- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+-- details.
+--
+
+create or replace package dotlrn_community_type
+is
+ function new (
+ community_type in dotlrn_community_types.community_type%TYPE,
+ parent_type in dotlrn_community_types.supertype%TYPE default 'dotlrn_community',
+ pretty_name in dotlrn_community_types.pretty_name%TYPE,
+ pretty_plural in dotlrn_community_types.pretty_name%TYPE default null,
+ description in dotlrn_community_types.description%TYPE,
+ package_id in dotlrn_community_types.package_id%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_community_types.community_type%TYPE;
+
+ procedure del (
+ community_type in dotlrn_community_types.community_type%TYPE
+ );
+
+ function name (
+ community_type in dotlrn_community_types.community_type%TYPE
+ ) return varchar;
+end;
+/
+show errors
+
+create or replace package body dotlrn_community_type
+is
+ function new (
+ community_type in dotlrn_community_types.community_type%TYPE,
+ parent_type in dotlrn_community_types.supertype%TYPE default 'dotlrn_community',
+ pretty_name in dotlrn_community_types.pretty_name%TYPE,
+ pretty_plural in dotlrn_community_types.pretty_name%TYPE default null,
+ description in dotlrn_community_types.description%TYPE,
+ package_id in dotlrn_community_types.package_id%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_community_types.community_type%TYPE
+ is
+ v_parent_object_type acs_object_types.object_type%TYPE;
+ v_unique_name acs_objects.object_id%TYPE;
+ begin
+ if parent_type is null then
+ v_parent_object_type:= 'group';
+ else
+ v_parent_object_type:= parent_type;
+ end if;
+
+ select acs_object_id_seq.nextval
+ into v_unique_name
+ from dual;
+
+ acs_object_type.create_type (
+ supertype => v_parent_object_type,
+ object_type => dotlrn_community_type.new.community_type,
+ pretty_name => dotlrn_community_type.new.community_type,
+ pretty_plural => dotlrn_community_type.new.community_type,
+ table_name => v_unique_name,
+ id_column => v_unique_name,
+ package_name => v_unique_name,
+ name_method => 'acs_group.name'
+ );
+
+ insert
+ into group_types
+ (group_type, default_join_policy)
+ values
+ (dotlrn_community_type.new.community_type, 'closed');
+
+ insert
+ into dotlrn_community_types
+ (community_type,
+ pretty_name,
+ description,
+ package_id,
+ supertype)
+ values
+ (dotlrn_community_type.new.community_type,
+ dotlrn_community_type.new.pretty_name,
+ dotlrn_community_type.new.description,
+ dotlrn_community_type.new.package_id,
+ dotlrn_community_type.new.parent_type);
+
+ return community_type;
+ end;
+
+ procedure del (
+ community_type in dotlrn_community_types.community_type%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_community_types
+ where community_type = dotlrn_community_type.del.community_type;
+
+ delete
+ from group_types
+ where group_types.group_type = dotlrn_community_type.del.community_type;
+
+ acs_object_type.drop_type(dotlrn_community_type.del.community_type);
+ end;
+
+ function name (
+ community_type in dotlrn_community_types.community_type%TYPE
+ ) return varchar
+ is
+ v_name dotlrn_community_types.pretty_name%TYPE;
+ begin
+ select dotlrn_community_types.pretty_name
+ into v_name
+ from dotlrn_community_types
+ where dotlrn_community_types.community_type = dotlrn_community_type.name.community_type;
+
+ return v_name;
+ end;
+end;
+/
+show errors
+
+create or replace package dotlrn_community
+is
+
+ function new (
+ community_id in dotlrn_communities_all.community_id%TYPE default null,
+ parent_community_id in dotlrn_communities_all.parent_community_id%TYPE default null,
+ community_type in dotlrn_communities_all.community_type%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,
+ archived_p in dotlrn_communities_all.archived_p%TYPE default 'f',
+ 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,
+ package_id in dotlrn_communities_all.package_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_communities_all.community_id%TYPE;
+
+ procedure set_active_dates (
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ start_date in dotlrn_communities_all.active_start_date%TYPE,
+ end_date in dotlrn_communities_all.active_end_date%TYPE
+ );
+
+ procedure del (
+ community_id in dotlrn_communities_all.community_id%TYPE
+ );
+
+ function name (
+ community_id in dotlrn_communities_all.community_id%TYPE
+ ) return varchar;
+
+ function member_p (
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ party_id in parties.party_id%TYPE
+ ) return char;
+
+ function admin_p (
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ party_id in parties.party_id%TYPE
+ ) return char;
+
+ function url (
+ community_id in dotlrn_communities_all.community_id%TYPE
+ ) return varchar2;
+
+ function has_subcomm_p (
+ community_id in dotlrn_communities_all.community_id%TYPE
+ ) return char;
+
+end dotlrn_community;
+/
+show errors
+
+create or replace package body dotlrn_community
+as
+
+ function new (
+ community_id in dotlrn_communities_all.community_id%TYPE default null,
+ parent_community_id in dotlrn_communities_all.parent_community_id%TYPE default null,
+ community_type in dotlrn_communities_all.community_type%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,
+ archived_p in dotlrn_communities_all.archived_p%TYPE default 'f',
+ 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,
+ package_id in dotlrn_communities_all.package_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_communities_all.community_id%TYPE
+ is
+ c_id integer;
+ begin
+ c_id := acs_group.new (
+ context_id => dotlrn_community.new.context_id,
+ group_id => dotlrn_community.new.community_id,
+ object_type => dotlrn_community.new.community_type,
+ creation_date => dotlrn_community.new.creation_date,
+ creation_user => dotlrn_community.new.creation_user,
+ creation_ip => dotlrn_community.new.creation_ip,
+ group_name => dotlrn_community.new.community_key,
+ join_policy => dotlrn_community.new.join_policy
+ );
+
+ insert into dotlrn_communities_all
+ (community_id,
+ parent_community_id,
+ community_type,
+ community_key,
+ pretty_name,
+ description,
+ package_id,
+ archived_p,
+ portal_id,
+ non_member_portal_id)
+ values
+ (c_id,
+ dotlrn_community.new.parent_community_id,
+ dotlrn_community.new.community_type,
+ dotlrn_community.new.community_key,
+ dotlrn_community.new.pretty_name,
+ dotlrn_community.new.description,
+ dotlrn_community.new.package_id,
+ dotlrn_community.new.archived_p,
+ dotlrn_community.new.portal_id,
+ dotlrn_community.new.non_member_portal_id);
+
+ return c_id;
+ end;
+
+ procedure set_active_dates (
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ start_date in dotlrn_communities_all.active_start_date%TYPE,
+ end_date in dotlrn_communities_all.active_end_date%TYPE
+ )
+ is
+ begin
+ update dotlrn_communities_all
+ set active_start_date = dotlrn_community.set_active_dates.start_date,
+ active_end_date = dotlrn_community.set_active_dates.end_date
+ where dotlrn_communities_all.community_id = dotlrn_community.set_active_dates.community_id;
+ end;
+
+ procedure del (
+ community_id in dotlrn_communities_all.community_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_communities_all
+ where dotlrn_communities_all.community_id = dotlrn_community.del.community_id;
+
+ acs_group.del(dotlrn_community.del.community_id);
+ end;
+
+ function name (
+ community_id in dotlrn_communities_all.community_id%TYPE
+ ) return varchar
+ is
+ begin
+ return acs_group.name(dotlrn_community.name.community_id);
+ end;
+
+ function member_p (
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ party_id in parties.party_id%TYPE
+ ) return char
+ is
+ v_member_p char(1);
+ begin
+ select decode(count(*), 0, 'f', 't')
+ 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;
+
+ function admin_p (
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ party_id in parties.party_id%TYPE
+ ) return char
+ is
+ v_rv char(1);
+ begin
+ select decode(
+ acs_permission.permission_p(dotlrn_community.admin_p.community_id, dotlrn_community.admin_p.party_id, 'dotlrn_admin_community'),
+ 'f',
+ acs_permission.permission_p(dotlrn_community.admin_p.community_id, dotlrn_community.admin_p.party_id, 'admin'),
+ 't'
+ ) into v_rv
+ from dual;
+
+ return v_rv;
+ end;
+
+ function url (
+ community_id in dotlrn_communities_all.community_id%TYPE
+ ) return varchar2
+ is
+ v_node_id site_nodes.node_id%TYPE;
+ begin
+ select site_nodes.node_id into v_node_id
+ from dotlrn_communities_all,
+ site_nodes
+ where dotlrn_communities_all.community_id = dotlrn_community.url.community_id
+ and site_nodes.object_id = dotlrn_communities_all.package_id;
+
+ return site_node.url(v_node_id);
+
+ exception
+ when no_data_found then
+ return '';
+ end;
+
+ function has_subcomm_p (
+ community_id in dotlrn_communities_all.community_id%TYPE
+ ) return char
+ is
+ v_rv char(1);
+ begin
+ select decode(count(*), 0, 'f', 't')
+ into v_rv
+ from dual
+ where exists (select 1
+ from dotlrn_communities_all
+ where dotlrn_communities_all.parent_community_id = dotlrn_community.has_subcomm_p.community_id);
+ return v_rv;
+ end;
+
+end;
+/
+show errors
+
+
+create or replace package dotlrn_member_rel
+is
+
+ function new (
+ rel_id in dotlrn_member_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_member_rel',
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_member_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_member_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors;
+
+create or replace package body dotlrn_member_rel
+is
+
+ function new (
+ rel_id in dotlrn_member_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_member_rel',
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_member_rels.rel_id%TYPE
+ is
+ v_rel_id membership_rels.rel_id%TYPE;
+ begin
+ v_rel_id:= membership_rel.new(
+ rel_id => rel_id,
+ rel_type => rel_type,
+ object_id_one => community_id,
+ object_id_two => user_id,
+ member_state => dotlrn_member_rel.new.member_state,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_member_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_member_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_member_rels
+ where rel_id = dotlrn_member_rel.del.rel_id;
+
+ membership_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors;
+
+create or replace package dotlrn_admin_rel
+is
+
+ function new (
+ rel_id in dotlrn_admin_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_admin_rel',
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_admin_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_admin_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors;
+
+create or replace package body dotlrn_admin_rel
+is
+
+ function new (
+ rel_id in dotlrn_admin_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_admin_rel',
+ community_id in dotlrn_communities_all.community_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_admin_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_admin_rels.rel_id%TYPE;
+ begin
+ v_rel_id:= dotlrn_member_rel.new(
+ rel_id => rel_id,
+ rel_type => rel_type,
+ community_id => community_id,
+ user_id => user_id,
+ member_state => dotlrn_admin_rel.new.member_state,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_admin_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_admin_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_admin_rels
+ where rel_id = dotlrn_admin_rel.del.rel_id;
+
+ dotlrn_member_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors;
+
+create or replace package dotlrn_student_rel
+is
+
+ function new (
+ rel_id in dotlrn_student_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_student_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_student_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_student_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors;
+
+create or replace package body dotlrn_student_rel
+is
+
+ function new (
+ rel_id in dotlrn_student_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_student_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_student_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_student_rels.rel_id%TYPE;
+ begin
+ v_rel_id:= dotlrn_member_rel.new(
+ rel_id => rel_id,
+ rel_type => rel_type,
+ community_id => class_instance_id,
+ user_id => user_id,
+ member_state => dotlrn_student_rel.new.member_state,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_student_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_student_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_student_rels
+ where rel_id = dotlrn_student_rel.del.rel_id;
+
+ dotlrn_member_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors;
+
+create or replace package dotlrn_ta_rel
+is
+
+ function new (
+ rel_id in dotlrn_ta_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_ta_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_ta_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_ta_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors;
+
+create or replace package body dotlrn_ta_rel
+is
+
+ function new (
+ rel_id in dotlrn_ta_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_ta_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_ta_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_ta_rels.rel_id%TYPE;
+ begin
+ v_rel_id:= dotlrn_admin_rel.new(
+ rel_id => rel_id,
+ rel_type => rel_type,
+ community_id => class_instance_id,
+ user_id => user_id,
+ member_state => dotlrn_ta_rel.new.member_state,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_ta_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_ta_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_ta_rels
+ where rel_id = dotlrn_ta_rel.del.rel_id;
+
+ dotlrn_admin_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors;
+
+create or replace package dotlrn_ca_rel
+is
+
+ function new (
+ rel_id in dotlrn_ca_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_ca_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_ca_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_ca_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors;
+
+create or replace package body dotlrn_ca_rel
+is
+
+ function new (
+ rel_id in dotlrn_ca_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_ca_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_ca_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_ca_rels.rel_id%TYPE;
+ begin
+ v_rel_id:= dotlrn_admin_rel.new(
+ rel_id => rel_id,
+ rel_type => rel_type,
+ community_id => class_instance_id,
+ user_id => user_id,
+ member_state => dotlrn_ca_rel.new.member_state,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_ca_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_ca_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_ca_rels
+ where rel_id = dotlrn_ca_rel.del.rel_id;
+
+ dotlrn_admin_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors;
+
+create or replace package dotlrn_cadmin_rel
+is
+
+ function new (
+ rel_id in dotlrn_cadmin_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_cadmin_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_cadmin_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_cadmin_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors;
+
+create or replace package body dotlrn_cadmin_rel
+is
+
+ function new (
+ rel_id in dotlrn_cadmin_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_cadmin_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_cadmin_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_cadmin_rels.rel_id%TYPE;
+ begin
+ v_rel_id:= dotlrn_admin_rel.new(
+ rel_id => rel_id,
+ rel_type => rel_type,
+ community_id => class_instance_id,
+ user_id => user_id,
+ member_state => dotlrn_cadmin_rel.new.member_state,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_cadmin_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_cadmin_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_cadmin_rels
+ where rel_id = dotlrn_cadmin_rel.del.rel_id;
+
+ dotlrn_admin_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors;
+
+create or replace package dotlrn_instructor_rel
+is
+
+ function new (
+ rel_id in dotlrn_instructor_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_instructor_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_instructor_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_instructor_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors;
+
+create or replace package body dotlrn_instructor_rel
+is
+
+ function new (
+ rel_id in dotlrn_instructor_rels.rel_id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_instructor_rel',
+ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE,
+ user_id in users.user_id%TYPE,
+ member_state in membership_rels.member_state%TYPE default 'approved',
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_instructor_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_instructor_rels.rel_id%TYPE;
+ begin
+ v_rel_id:= dotlrn_admin_rel.new(
+ rel_id => rel_id,
+ rel_type => rel_type,
+ community_id => class_instance_id,
+ user_id => user_id,
+ member_state => dotlrn_instructor_rel.new.member_state,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_instructor_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_instructor_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_instructor_rels
+ where rel_id = dotlrn_instructor_rel.del.rel_id;
+
+ dotlrn_admin_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors;
+--
+-- Copyright (C) 2001, 2002 MIT
+--
+-- This file is part of dotLRN.
+--
+-- dotLRN is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 2 of the License, or (at your option) any later
+-- version.
+--
+-- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+-- details.
+--
+
+--
+-- Create the External package
+--
+-- @author yon@openforce.net
+-- @version $Id: upgrade-1.0-2.0d1.sql,v 1.1 2003/10/09 08:12:49 peterm Exp $
+--
+
+create or replace package dotlrn_external_profile_rel
+as
+ function new (
+ rel_id in dotlrn_external_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_external_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_external_profile_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors
+
+create or replace package body dotlrn_external_profile_rel
+as
+ function new (
+ rel_id in dotlrn_external_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_external_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_user_profile_rels.rel_id%TYPE;
+ v_group_id groups.group_id%TYPE;
+ begin
+ if group_id is null then
+ select min(group_id)
+ into v_group_id
+ from profiled_groups
+ where profile_provider = (select min(impl_id)
+ from acs_sc_impls
+ where impl_name = 'dotlrn_external_profile_provider');
+ else
+ v_group_id := group_id;
+ end if;
+
+ v_rel_id := dotlrn_user_profile_rel.new(
+ rel_id => rel_id,
+ user_id => user_id,
+ portal_id => portal_id,
+ theme_id => theme_id,
+ id => id,
+ rel_type => rel_type,
+ group_id => v_group_id,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_external_profile_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_external_profile_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_external_profile_rels
+ where rel_id = dotlrn_external_profile_rel.del.rel_id;
+
+ dotlrn_user_profile_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors
+--
+-- Copyright (C) 2001, 2002 MIT
+--
+-- This file is part of dotLRN.
+--
+-- dotLRN is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 2 of the License, or (at your option) any later
+-- version.
+--
+-- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+-- details.
+--
+
+--
+-- Create the Professor package
+--
+-- @author yon@openforce.net
+-- @version $Id: upgrade-1.0-2.0d1.sql,v 1.1 2003/10/09 08:12:49 peterm Exp $
+--
+
+create or replace package dotlrn_professor_profile_rel
+as
+ function new (
+ rel_id in dotlrn_professor_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_professor_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_professor_profile_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors
+
+create or replace package body dotlrn_professor_profile_rel
+as
+ function new (
+ rel_id in dotlrn_professor_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_professor_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_user_profile_rels.rel_id%TYPE;
+ v_group_id groups.group_id%TYPE;
+ begin
+ if group_id is null then
+ select min(group_id)
+ into v_group_id
+ from profiled_groups
+ where profile_provider = (select min(impl_id)
+ from acs_sc_impls
+ where impl_name = 'dotlrn_professor_profile_provider');
+ else
+ v_group_id := group_id;
+ end if;
+
+ v_rel_id := dotlrn_user_profile_rel.new(
+ rel_id => rel_id,
+ user_id => user_id,
+ portal_id => portal_id,
+ theme_id => theme_id,
+ id => id,
+ rel_type => rel_type,
+ group_id => v_group_id,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_professor_profile_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_professor_profile_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_professor_profile_rels
+ where rel_id = dotlrn_professor_profile_rel.del.rel_id;
+
+ dotlrn_user_profile_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors
+--
+-- Copyright (C) 2001, 2002 MIT
+--
+-- This file is part of dotLRN.
+--
+-- dotLRN is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 2 of the License, or (at your option) any later
+-- version.
+--
+-- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+-- details.
+--
+
+--
+-- Create the Student package
+--
+-- @author yon@openforce.net
+-- @version $Id: upgrade-1.0-2.0d1.sql,v 1.1 2003/10/09 08:12:49 peterm Exp $
+--
+
+create or replace package dotlrn_student_profile_rel
+as
+ function new (
+ rel_id in dotlrn_student_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_student_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_student_profile_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors
+
+create or replace package body dotlrn_student_profile_rel
+as
+ function new (
+ rel_id in dotlrn_student_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_student_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE
+ is
+ v_rel_id dotlrn_user_profile_rels.rel_id%TYPE;
+ v_group_id groups.group_id%TYPE;
+ begin
+ if group_id is null then
+ select min(group_id)
+ into v_group_id
+ from profiled_groups
+ where profile_provider = (select min(impl_id)
+ from acs_sc_impls
+ where impl_name = 'dotlrn_student_profile_provider');
+ else
+ v_group_id := group_id;
+ end if;
+
+ v_rel_id := dotlrn_user_profile_rel.new(
+ rel_id => rel_id,
+ user_id => user_id,
+ portal_id => portal_id,
+ theme_id => theme_id,
+ id => id,
+ rel_type => rel_type,
+ group_id => v_group_id,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_student_profile_rels
+ (rel_id)
+ values
+ (v_rel_id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_student_profile_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_student_profile_rels
+ where rel_id = dotlrn_student_profile_rel.del.rel_id;
+
+ dotlrn_user_profile_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors
+--
+-- Copyright (C) 2001, 2002 MIT
+--
+-- This file is part of dotLRN.
+--
+-- dotLRN is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 2 of the License, or (at your option) any later
+-- version.
+--
+-- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+-- details.
+--
+
+--
+-- Create the User Profile package
+--
+-- @author yon@openforce.net
+-- @version $Id: upgrade-1.0-2.0d1.sql,v 1.1 2003/10/09 08:12:49 peterm Exp $
+--
+
+create or replace package dotlrn_user_profile_rel
+as
+ function new (
+ rel_id in dotlrn_user_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_user_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE;
+
+ procedure del (
+ rel_id in dotlrn_user_profile_rels.rel_id%TYPE
+ );
+
+end;
+/
+show errors
+
+create or replace package body dotlrn_user_profile_rel
+as
+ function new (
+ rel_id in dotlrn_user_profile_rels.rel_id%TYPE default null,
+ user_id in users.user_id%TYPE,
+ portal_id in dotlrn_user_profile_rels.portal_id%TYPE,
+ theme_id in dotlrn_user_profile_rels.theme_id%TYPE default null,
+ id in dotlrn_user_profile_rels.id%TYPE default null,
+ rel_type in acs_rels.rel_type%TYPE default 'dotlrn_user_profile_rel',
+ group_id in groups.group_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return dotlrn_user_profile_rels.rel_id%TYPE
+ is
+ v_rel_id user_profile_rels.rel_id%TYPE;
+ v_group_id groups.group_id%TYPE;
+ begin
+ if group_id is null then
+ select min(group_id)
+ into v_group_id
+ from profiled_groups
+ where profile_provider = (select min(impl_id)
+ from acs_sc_impls
+ where impl_name = 'dotlrn_user_profile_provider');
+ else
+ v_group_id := group_id;
+ end if;
+
+ v_rel_id := user_profile_rel.new(
+ rel_id => rel_id,
+ rel_type => rel_type,
+ group_id => v_group_id,
+ user_id => user_id,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+
+ insert
+ into dotlrn_user_profile_rels
+ (rel_id, portal_id, theme_id, id)
+ values
+ (v_rel_id, portal_id, theme_id, id);
+
+ return v_rel_id;
+ end;
+
+ procedure del (
+ rel_id in dotlrn_user_profile_rels.rel_id%TYPE
+ )
+ is
+ begin
+ delete
+ from dotlrn_user_profile_rels
+ where rel_id = dotlrn_user_profile_rel.del.rel_id;
+
+ user_profile_rel.del(rel_id);
+ end;
+
+end;
+/
+show errors