Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admin-profile-provider-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-admin-profile-provider-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admin-profile-provider-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,52 @@ +-- +-- Implementation of the profile provider interface for dotLRN Admins. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-admin-profile-provider-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +begin + + -- create the implementation + select acs_sc_impl__new( + 'profile_provider', + 'dotlrn_admin_profile_provider', + 'dotlrn_admin_profile_provider' + ); + + -- add the bindings to the method implementations + + -- name method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_admin_profile_provider', + 'name', + 'dotlrn_admin_profile_provider::name', + 'TCL' + ); + + -- prettyName method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_admin_profile_provider', + 'prettyName', + 'dotlrn_admin_profile_provider::prettyName', + 'TCL' + ); + + -- render method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_admin_profile_provider', + 'render', + 'dotlrn_admin_profile_provider::render', + 'TCL' + ); + + -- bind this implementation to the interface it implements + select acs_sc_binding__new( + 'profile_provider', + 'dotlrn_admin_profile_provider' + ); + +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admin-profile-provider-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-admin-profile-provider-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admin-profile-provider-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,50 @@ +-- +-- Implementation of the profile provider interface for dotLRN Admins. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-admin-profile-provider-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + -- drop the binding between this implementation and the interface it + -- implements. + acs_sc_binding.delete( + contract_name => 'profile_provider', + impl_name => 'dotlrn_admin_profile_provider' + ); + + -- drop the bindings to the method implementations + + -- name method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_admin_profile_provider', + impl_operation_name => 'name' + ); + + -- prettyName method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_admin_profile_provider', + impl_operation_name => 'prettyName' + ); + + -- render method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_admin_profile_provider', + impl_operation_name => 'render' + ); + + -- drop the implementation + acs_sc_impl.delete( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_admin_profile_provider' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-admins-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,26 @@ +-- +-- Create the dotLRN Admins package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-admins-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create table dotlrn_admin_profile_rels ( + rel_id integer + constraint dotlrn_adm_prfl_rels_rel_id_fk + references dotlrn_user_profile_rels (rel_id) + constraint dotlrn_admin_prfl_rels_pk + primary key +); + +create table dotlrn_full_admin_profile_rels ( + rel_id integer + constraint dotlrn_fl_adm_prfl_rels_rel_fk + references dotlrn_full_user_profile_rels (rel_id) + constraint dotlrn_full_admin_prfl_rels_pk + primary key +); + +@@ dotlrn-admin-profile-provider-create.sql +@@ dotlrn-admins-init.sql +@@ dotlrn-admins-package-create.sql Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-admins-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,13 @@ +-- +-- Drop the dotLRN Admins package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-admins-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +@@ dotlrn-admins-package-drop.sql +@@ dotlrn-admins-sanitize.sql +@@ dotlrn-admin-profile-provider-drop.sql + +drop table dotlrn_full_admin_profile_rels; +drop table dotlrn_admin_profile_rels; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-admins-init.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-init.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,114 @@ +-- +-- Initialize the dotLRN Admins package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-admins-init.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + + +create function inline_1() +returns integer as ' +declare + foo integer; + group_id integer; + dotlrn_users_group_id integer; +begin + + PERFORM acs_rel_type__create_type( + ''dotlrn_admin_profile_rel'', + ''dotLRN Admin Profile Membership'', + ''dotLRN Admin Profile Memberships'', + ''dotlrn_user_profile_rel'', + ''dotlrn_admin_profile_rels'', + ''rel_id'', + ''dotlrn_admin_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(impl_id) + into foo + from acs_sc_impls + where impl_name = ''dotlrn_admin_profile_provider''; + + group_id := profiled_group__new( + foo, + ''dotLRN Admins'' + ); + + insert + into dotlrn_user_types + (type, pretty_name, group_id) + values + (''admin'', ''Staff'', group_id); + + foo := rel_segment__new( + ''dotLRN Profiled Admins'', + group_id, + ''dotlrn_admin_profile_rel'' + ); + + select group_id + into dotlrn_users_group_id + from groups + where group_name = ''dotLRN Users''; + + foo := composition_rel__new( + dotlrn_users_group_id, + group_id + ); + +end; +' language 'plpgsql'; + +select inline_1(); +drop function inline_1(); + + +create function inline_2() +returns integer as ' +declare + foo integer; +begin + PERFORM acs_rel_type--create_type( + ''dotlrn_full_admin_profile_rel'', + ''dotLRN Full Admin Profile Membership'', + ''dotLRN Full Admin Profile Memberships'', + ''dotlrn_full_user_profile_rel'', + ''dotlrn_full_admin_profile_rels'', + ''rel_id'', + ''dotlrn_full_admin_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = ''dotlrn_admin_profile_provider''); + + foo := rel_segment__new( + ''dotLRN Full Profiled Admins'', + foo, + ''dotlrn_full_admin_profile_rel'' + ); + +end; +' language 'plpgsql'; + +select inline_2(); +drop function inline_2(); \ No newline at end of file Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-admins-package-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-package-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,141 @@ +-- +-- Create the Admin package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-admins-package-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +select define_function_args ('dotlrn_admin_profile_rel__new','rel_id,id,rel_type;dotlrn_admin_profile_rel,group_id,user_id,creation_user,creation_ip'); + +select define_function_args ('dotlrn_admin_profile_rel__delete','rel_id'); + + +create function dotlrn_admin_profile_rel__new(integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_id alias for $2; + p_rel_type alias for $3; + p_group_id alias for $4; + p_user_id alias for $5; + p_creation_user alias for $6; + p_creation_ip alias for $7; + v_rel_id dotlrn_user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := dotlrn_user_profile_rel__new( + p_rel_id, + p_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_admin_profile_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_admin_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_admin_profile_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_user_profile_rel__delete(p_rel_id); + return (0); +END; +' language 'plpgsql'; + + +-- full admins + +select define_function_args('dotlrn_full_admin_profile_rel__new','rel_id,portal_id,theme_id,id,rel_type,group_id,user_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_full_admin_profile_rel__delete','rel_id'); + + +create function dotlrn_full_admin_profile_rel__new(integer,integer,integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_portal_id alias for $2; + p_theme_id alias for $3; + p_id alias for $4; + p_rel_type alias for $5; + p_group_id alias for $6; + p_user_id alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + v_rel_id dotlrn_full_user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := dotlrn_full_user_profile_rel__new( + p_rel_id, + p_portal_id, + p_theme_id, + p_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_full_admin_profile_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_full_admin_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_full_admin_profile_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_full_user_profile_rel__delete(p_rel_id); + return(0); +END; +' language 'plpgsql'; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-admins-package-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-package-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,9 @@ +-- +-- Create the dotLRN Admins package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-admins-package-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +drop package dotlrn_full_admin_profile_rel; +drop package dotlrn_admin_profile_rel; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-sanitize.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-admins-sanitize.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-admins-sanitize.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,65 @@ +-- +-- Sanitize the dotLRN Admin package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-admins-sanitize.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Full Profiled Admins'; + + rel_segment.delete( + segment_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_full_admin_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Profiled Admins'; + + rel_segment.delete( + segment_id => foo + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = 'dotlrn_admin_profile_provider'); + + delete + from dotlrn_user_types + where group_id = foo; + + profiled_group.delete( + group_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_admin_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-applet-sc-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-applet-sc-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-applet-sc-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,216 @@ +-- +-- The DotLRN applet service contract +-- +-- copyright 2001, OpenForce, Inc. +-- distributed under the GNU GPL v2 +-- +-- for Oracle 8/8i. (We're guessing 9i works, too). +-- +-- ben@openforce.net, arjun@openforce.net +-- ported to PG by Yon and Ben +-- +-- started October 1st, 2001 +-- we remember September 11th +-- + +-- This is the service contract for dotLRN applets. A dotlrn applet MUST +-- have AT LEAST the procs (with the proper arguments) defined below to work +-- as a dotlrn applet. + +begin + select acs_sc_contract__new ( + 'dotlrn_applet', + 'dotLRN Applet contract' + ); + + -- Get a pretty name + select acs_sc_msg_type__new ( + 'dotlrn_applet.GetPrettyName.InputType', + '' + ); + + select acs_sc_msg_type__new ( + 'dotlrn_applet.GetPrettyName.OutputType', + 'pretty_name:string' + ); + + select acs_sc_operation__new ( + 'dotlrn_applet', + 'GetPrettyName', + 'Get the pretty name of the applet', + 't', -- not cacheable + 0, -- n_args + 'dotlrn_applet.GetPrettyName.InputType', + 'dotlrn_applet.GetPrettyName.OutputType' + ); + + -- Add the applet to dotlrn (used for one-time initialization) + select acs_sc_msg_type__new( + 'dotlrn_applet.AddApplet.InputType', + '' + ); + + select acs_sc_msg_type__new( + 'dotlrn_applet.AddApplet.OutputType', + 'success_p:boolean,error_message:string' + ); + + select acs_sc_operation__new ( + 'dotlrn_applet', + 'AddApplet', + 'Add the Applet to dotlrn - used for one-time initialization', + 'f', -- not cacheable + 0, -- n_args + 'dotlrn_applet.AddApplet.InputType', + 'dotlrn_applet.AddApplet.OutputType' + ); + + -- Add the applet to a community + select acs_sc_msg_type__new( + 'dotlrn_applet.AddAppletToCommunity.InputType', + 'community_id:integer' + ); + + select acs_sc_msg_type__new( + 'dotlrn_applet.AddAppletToCommunity.OutputType', + 'success_p:boolean,error_message:string' + ); + + select acs_sc_operation__new ( + 'dotlrn_applet', + 'AddAppletToCommunity', + 'Add the Applet to a specific dotlrn community', + 'f', -- not cacheable + 1, -- n_args + 'dotlrn_applet.AddAppletToCommunity.InputType', + 'dotlrn_applet.AddAppletToCommunity.OutputType' + ); + + -- add a user to dotlrn (used for user-specific one time stuff) + select acs_sc_msg_type__new( + 'dotlrn_applet.AddUser.InputType', + 'user_id:integer' + ); + + select acs_sc_msg_type.new( + 'dotlrn_applet.AddUser.OutputType', + 'success_p:boolean,error_message:string' + ); + + select acs_sc_operation__new ( + 'dotlrn_applet', + 'AddUser', + 'Add a user to dotlrn, used for user-specific one-time init', + 'f', -- not cacheable + 1, -- n_args + 'dotlrn_applet.AddUser.InputType', + 'dotlrn_applet.AddUser.OutputType' + ); + + + -- add a user to the a specfic dotlrn community + select acs_sc_msg_type__new( + 'dotlrn_applet.AddUserToCommunity.InputType', + 'community_id:integer,user_id:integer' + ); + + select acs_sc_msg_type__new( + 'dotlrn_applet.AddUserToCommunity.OutputType', + 'success_p:boolean,error_message:string' + ); + + select acs_sc_operation__new ( + 'dotlrn_applet', + 'AddUserToCommunity', + 'Add a user to a community', + 'f', -- not cacheable + 2, -- n_args + 'dotlrn_applet.AddUserToCommunity.InputType', + 'dotlrn_applet.AddUserToCommunity.OutputType' + ); + + -- remove a user from dotlrn + select acs_sc_msg_type__new( + 'dotlrn_applet.RemoveUser.InputType', + 'user_id:integer' + ); + + select acs_sc_msg_type__new( + 'dotlrn_applet.RemoveUser.OutputType', + 'success_p:boolean,error_message:string' + ); + + select acs_sc_operation__new ( + 'dotlrn_applet', + 'RemoveUser', + 'Remove a user from dotlrn', + 'f', -- not cacheable + 1, -- n_args + 'dotlrn_applet.RemoveUser.InputType', + 'dotlrn_applet.RemoveUser.OutputType' + ); + + -- remove a user from the community + select acs_sc_msg_type__new( + 'dotlrn_applet.RemoveUserFromCommunity.InputType', + 'community_id:integer,user_id:integer' + ); + + select acs_sc_msg_type__new( + 'dotlrn_applet.RemoveUserFromCommunity.OutputType', + 'success_p:boolean,error_message:string' + ); + + select acs_sc_operation__new ( + 'dotlrn_applet', + 'RemoveUserFromCommunity', + 'Remove a user from a community, applet does appropriate cleanup', + 'f', -- not cacheable + 2, -- n_args + 'dotlrn_applet.RemoveUserFromCommunity.InputType', + 'dotlrn_applet.RemoveUserFromCommunity.OutputType' + ); + + -- remove the applet from dotlrn + select acs_sc_msg_type__new( + 'dotlrn_applet.RemoveApplet.InputType', + '' + ); + + select acs_sc_msg_type__new( + 'dotlrn_applet.RemoveApplet.OutputType', + 'success_p:boolean,error_message:string' + ); + + select acs_sc_operation__new ( + 'dotlrn_applet', + 'RemoveApplet', + 'Remove the applet', + 'f', -- not cacheable + 0, -- n_args + 'dotlrn_applet.RemoveApplet.InputType', + 'dotlrn_applet.RemoveApplet.OutputType' + ); + + -- remove the applet from a community + select acs_sc_msg_type__new( + 'dotlrn_applet.RemoveAppletFromCommunity.InputType', + 'community_id:integer' + ); + + select acs_sc_msg_type__new( + 'dotlrn_applet.RemoveAppletFromCommunity.OutputType', + 'success_p:boolean,error_message:string' + ); + + select acs_sc_operation__new ( + 'dotlrn_applet', + 'RemoveAppletFromCommunity', + 'Remove the applet from a given community', + 'f', -- not cacheable + 2, -- n_args + 'dotlrn_applet.RemoveAppletFromCommunity.InputType', + 'dotlrn_applet.RemoveAppletFromCommunity.OutputType' + ); + +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-classes-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-classes-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-classes-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,327 @@ +-- +-- The DotLRN basic system +-- copyright 2001, OpenForce, inc. +-- distributed under the GNU GPL v2 +-- +-- for PG 7.1.3 +-- +-- @author Ben Adida (ben@openforce.net) +-- @author yon (yon@openforce.net) +-- @creation-date 2001-08-18 +-- @version $Id: dotlrn-classes-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create table dotlrn_departments ( + department_key varchar(100) + constraint dotlrn_departments_dept_key_fk + references dotlrn_community_types (community_type) + constraint dotlrn_departments_pk + primary key, + external_url varchar(250) +); + +create view dotlrn_departments_full +as + select dotlrn_departments.department_key, + dotlrn_community_types.pretty_name, + dotlrn_community_types.description, + dotlrn_community_types.package_id, + dotlrn_community_types.supertype, + (select site_node__url(site_nodes.node_id) + from site_nodes + where site_nodes.object_id = dotlrn_community_types.package_id) as url, + dotlrn_departments.external_url + from dotlrn_departments, + dotlrn_community_types + where dotlrn_departments.department_key = dotlrn_community_types.community_type; + +create table dotlrn_terms ( + term_id integer + constraint dotlrn_terms_pk + primary key, + term_name varchar(20) + constraint dotlrn_t_term_name_nn + not null, + term_year varchar(4) + constraint dotlrn_t_term_year_nn + not null, + start_date date + default now() + constraint dotlrn_t_start_date_nn + not null, + end_date date + default (now() + '180 days'::timespan) + constraint dotlrn_t_end_date_nn + not null +); + +create table dotlrn_classes ( + class_key varchar(100) constraint dotlrn_classes_class_key_fk + references dotlrn_community_types (community_type) + constraint dotlrn_classes_pk + primary key, + department_key varchar(100) constraint dotlrn_classes_dept_key_fk + references dotlrn_departments (department_key) + constraint dotlrn_classes_dept_key_nn + not null +); + +create view dotlrn_classes_full +as + select dotlrn_classes.class_key, + dotlrn_community_types.pretty_name, + dotlrn_community_types.description, + dotlrn_community_types.package_id, + dotlrn_community_types.supertype, + (select site_node__url(site_nodes.node_id) + from site_nodes + where site_nodes.object_id = dotlrn_community_types.package_id) as url, + dotlrn_classes.department_key + from dotlrn_classes, + dotlrn_community_types + where dotlrn_classes.class_key = dotlrn_community_types.community_type; + +create table dotlrn_class_instances ( + class_instance_id integer constraint dotlrn_ci_class_instance_id_fk + references dotlrn_communities (community_id) + constraint dotlrn_class_instances_pk + primary key, + class_key varchar(100) constraint dotlrn_ci_class_key_fk + references dotlrn_classes (class_key) + constraint dotlrn_ci_class_key_nn + not null, + term_id integer constraint dotlrn_ci_term_id_fk + references dotlrn_terms (term_id) + constraint dotlrn_ci_term_id_nn + not null +); + +create view dotlrn_class_instances_full +as + select dotlrn_class_instances.class_instance_id, + dotlrn_class_instances.class_key, + dotlrn_class_instances.term_id, + dotlrn_terms.term_name, + dotlrn_terms.term_year, + dotlrn_terms.start_date, + dotlrn_terms.end_date, + dotlrn_communities.community_type, + dotlrn_communities.community_key, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.active_start_date, + dotlrn_communities.active_end_date, + dotlrn_communities.portal_id, + dotlrn_communities.portal_template_id, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + dotlrn_classes_full.pretty_name as class_name, + dotlrn_classes_full.url as class_url, + dotlrn_classes_full.department_key, + dotlrn_departments_full.pretty_name as department_name, + dotlrn_departments_full.url as department_url, + groups.join_policy + from dotlrn_communities, + dotlrn_class_instances, + dotlrn_terms, + dotlrn_classes_full, + dotlrn_departments_full, + groups + where dotlrn_communities.community_id = dotlrn_class_instances.class_instance_id + and dotlrn_class_instances.term_id = dotlrn_terms.term_id + and dotlrn_communities.community_type = dotlrn_classes_full.class_key + and dotlrn_classes_full.department_key = dotlrn_departments_full.department_key + and dotlrn_communities.community_id = groups.group_id; + +create view dotlrn_class_instances_current +as + select * + from dotlrn_class_instances_full + where now() between active_start_date and active_end_date; + +create view dotlrn_class_instances_not_old +as + select * + from dotlrn_class_instances_full + where active_end_date >= now(); + + +select define_function_args ('dotlrn_department__new','department_key,pretty_name,pretty_plural,description,package_id,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args ('dotlrn_department__delete', 'department_key'); + + +create function dotlrn_department__new(varchar,varchar,varchar,varchar,integer,timestamp,integer,varchar,integer) +returns varchar as ' +DECLARE + p_department_key alias for $1; + p_pretty_name alias for $2; + p_pretty_plural alias for $3; + p_description alias for $4; + p_package_id alias for $5; + p_creation_date alias for $6; + p_creation_user alias for $7; + p_creation_ip alias for $8; + p_context_id alias for $9; + v_department_key dotlrn_departments.department_key%TYPE; +BEGIN + v_department_key := dotlrn_community_type__new ( + p_department_key, + ''dotlrn_class_instance'', + p_pretty_name, + p_pretty_plural, + p_description, + p_package_id, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id + ); + + insert + into dotlrn_departments + (department_key) values (v_department_key); + + return v_department_key; +END; +' language 'plpgsql'; + + +create function dotlrn_department__delete(varchar) +returns integer as ' +DECLARE + p_department_key alias for $1; +BEGIN + delete + from dotlrn_departments + where department_key = p_department_key; + + PERFORM dotlrn_community_type__delete(p_department_key); + return(0); +END; +' language 'plpgsql'; + + +select define_function_args('dotlrn_class__new','class_key,department_key,pretty_name,pretty_plural,description,package_id,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args('dotlrn_class__delete','class_key'); + + +create function dotlrn_class__new(varchar,varchar,varchar,varchar,varchar,integer,timestamp,integer,varchar,integer) +returns varchar as ' +DECLARE + p_class_key alias for $1; + p_department_key alias for $2; + p_pretty_name alias for $3; + p_pretty_plural alias for $4; + p_description alias for $5; + p_package_id alias for $6; + p_creation_date alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + p_context_id alias for $10; + v_class_key dotlrn_classes.class_key%TYPE; +BEGIN + v_class_key := dotlrn_community_type__new ( + p_class_key, + p_department_key, + p_pretty_name, + p_pretty_plural, + p_description, + p_package_id, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id + ); + + insert + into dotlrn_classes + (class_key, department_key) values (v_class_key, p_department_key); + + return v_class_key; +END; +' language 'plpgsql'; + + +create function dotlrn_class__delete(varchar) +returns integer as ' +DECLARE + p_class_key alias for $1; +BEGIN + delete + from dotlrn_classes + where class_key = p_class_key; + + PERFORM dotlrn_community_type__delete(p_class_key); + return(0); +END; +' language 'plpgsql'; + + + +select define_function_args('dotlrn_class_instance__new','class_instance_id,class_key,term_id,community_key,pretty_name,description,package_id,portal_id,portal_template_id,join_policy,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args('dotlrn_class_instance__delete','class_instance_id'); + + +create function dotlrn_class_instance__new(integer,varchar,integer,varchar,varchar,varchar,integer,integer,integer,varchar,timestamp,integer,varchar,integer) +returns integer as ' +DECLARE + p_class_instance_id alias for $1; + p_class_key alias for $2; + p_term_id alias for $3; + p_community_key alias for $4; + p_pretty_name alias for $5; + p_description alias for $6; + p_package_id alias for $7; + p_portal_id alias for $8; + p_portal_template_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; + v_class_instance_id dotlrn_class_instances.class_instance_id%TYPE; +BEGIN + v_class_instance_id := dotlrn_community__new ( + p_class_instance_id, + p_class_key, + p_community_key, + p_pretty_name, + p_description, + p_package_id, + p_portal_id, + p_portal_template_id, + p_join_policy, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id + ); + + insert + into dotlrn_class_instances + (class_instance_id, class_key, term_id) + values + (v_class_instance_id, p_class_key, p_term_id); + + return v_class_instance_id; +END; +' language 'plpgsql'; + + +create function dotlrn_class_instance__delete(integer) +returns integer as ' +DECLARE + p_class_instance_id alias for $1; +BEGIN + delete + from dotlrn_class_instances + where class_instance_id= p_class_instance_id; + + PERFORM dotlrn_community__delete(p_class_instance_id); + return(0); +END; +' language 'plpgsql'; + Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-clubs-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-clubs-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-clubs-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,105 @@ + +-- +-- The DotLRN basic system +-- copyright 2001, OpenForce, Inc. +-- distributed under the GNU GPL v2 +-- +-- for PG 7.1.3 +-- +-- @author Ben Adida (ben@openforce.net) +-- @author yon (yon@openforce.net) +-- @creation-date August 18th, 2001 +-- @version $Id: dotlrn-clubs-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create table dotlrn_clubs ( + club_id integer + constraint dotlrn_clubs_club_id_fk + references dotlrn_communities (community_id) + constraint dotlrn_clubs_pk + primary key +); + +create view dotlrn_clubs_full +as + select dotlrn_clubs.club_id, + dotlrn_communities.community_type, + dotlrn_communities.community_key, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.active_start_date, + dotlrn_communities.active_end_date, + dotlrn_communities.portal_id, + dotlrn_communities.portal_template_id, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + groups.join_policy + from dotlrn_communities, + dotlrn_clubs, + groups + where dotlrn_communities.community_id = dotlrn_clubs.club_id + and dotlrn_communities.community_id = groups.group_id; + + +select define_function_args ('dotlrn_club__new','club_id,community_key,pretty_name,description,package_id,portal_id,portal_template_id,join_policy,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args ('dotlrn_club__delete','club_id'); + + +create function dotlrn_club__new(integer,varchar,varchar,varchar,integer,integer,integer,varchar,timestamp,integer,varchar,integer) +returns integer as ' +DECLARE + p_club_id alias for $1; + p_community_key alias for $2; + p_pretty_name alias for $3; + p_description alias for $4; + p_package_id alias for $5; + p_portal_id alias for $6; + p_portal_template_id alias for $7; + p_join_policy alias for $8; + p_creation_date alias for $9; + p_creation_user alias for $10; + p_creation_ip alias for $11; + p_context_id alias for $12; + v_club_id integer; +BEGIN + v_club_id := dotlrn_community__new( + p_club_id, + NULL, + ''dotlrn_club'', + p_community_key, + p_pretty_name, + p_description, + p_package_id, + p_portal_id, + p_portal_template_id, + p_join_policy, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id + ); + + insert + into dotlrn_clubs (club_id) + values (v_club_id); + + return v_club_id; +END; +' language 'plpgsql'; + + +create function dotlrn_club__delete(integer) +returns integer as ' +DECLARE + p_club_id alias for $1; +BEGIN + delete + from dotlrn_clubs + where club_id = p_club_id; + + PERFORM dotlrn_community__delete(p_club_id); + return(0); +END; +' language 'plpgsql'; + Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-communities-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-communities-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-communities-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,408 @@ +-- +-- The DotLRN communities construct +-- copyright 2001, OpenForce, Inc. +-- distributed under the GNU GPL v2 +-- +-- for PG +-- +-- @author Ben Adida (ben@openforce.net) +-- @author yon (yon@openforce.net +-- @author arjun (arjun@openforce.net) +-- @creation-date September 20th, 2001 (redone) +-- @version $Id: dotlrn-communities-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create table dotlrn_community_types ( + community_type varchar(100) constraint dotlrn_ct_community_type_fk + references group_types (group_type) + constraint dotlrn_community_types_pk + primary key, + pretty_name varchar(100) + constraint dotlrn_ct_pretty_name_nn + not null, + description varchar(4000), + package_id integer + constraint dotlrn_ct_package_id_fk + references apm_packages (package_id), + supertype varchar(100) constraint dotlrn_ct_supertype_fk + references dotlrn_community_types (community_type) +); + +create table dotlrn_communities ( + community_id integer constraint dotlrn_c_community_id_fk + references groups (group_id) + constraint dotlrn_communities_pk + primary key, + parent_community_id integer constraint dotlrn_c_parent_comm_id_fk + references dotlrn_communities (community_id), + community_type varchar(100) not null + constraint dotlrn_c_community_type_fk + references dotlrn_community_types (community_type), + community_key varchar(100) + constraint dotlrn_c_community_key_nn + not null, + pretty_name varchar(100) + constraint dotlrn_c_pretty_name_nn + not null, + description varchar(4000), + active_start_date date, + active_end_date date, + portal_id integer constraint dotlrn_c_portal_id_fk + references portals (portal_id), + admin_portal_id integer constraint dotlrn_c_admin_portal_id_fk + references portals (portal_id), + portal_template_id integer constraint dotlrn_c_portal_template_id_fk + references portals (portal_id), + package_id integer constraint dotlrn_c_package_id_fk + references apm_packages (package_id), + -- We can't have two communities with the same parent with the same key (url) + -- even if the parent_community_id is null, which it will be for non-subcommunities + constraint dotlrn_c_community_key_un + unique (community_key, parent_community_id) +); + +create view dotlrn_communities_not_closed +as + select dotlrn_communities.*, + groups.join_policy + from dotlrn_communities, + groups + where dotlrn_communities.community_id = groups.group_id + and groups.join_policy <> 'closed'; + +create view dotlrn_active_communities +as + select * + from dotlrn_communities + where (active_start_date is null or active_start_date < now()) + and (active_end_date is null or active_end_date > now()); + +create view dotlrn_active_comms_not_closed +as + select dotlrn_communities.*, + groups.join_policy + from dotlrn_active_communities dotlrn_communities, + groups + where dotlrn_communities.community_id = groups.group_id + and groups.join_policy <> 'closed'; + +create table dotlrn_applets ( + applet_id integer + constraint dotlrn_applets_applet_id_nn + not null + constraint dotlrn_applets_applet_pk + primary key, + applet_key varchar(100) + constraint dotlrn_applets_applet_key_nn + not null + constraint dotlrn_applets_applet_key_uk + unique, + status char(10) + default 'active' + constraint dotlrn_applets_status_nn + not null + constraint dotlrn_applets_status_ck + check (status in ('active','inactive')) +); + +create table dotlrn_community_applets ( + community_id integer + constraint dotlrn_ca_community_id_nn + not null + constraint dotlrn_ca_community_id_fk + references dotlrn_communities (community_id), + applet_id integer + constraint dotlrn_ca_applet_key_nn + not null + references dotlrn_applets (applet_id), + constraint dotlrn_community_applets_pk primary key (community_id, applet_id), + -- this is the package_id of the package this applet represents + package_id integer, + active_p char(1) + default 't' + constraint dotlrn_ca_active_p_nn + not null + constraint dotlrn_ca_active_p_ck + check (active_p in ('t','f')) +); + + +select define_function_args ('dotlrn_community_type__new','community_type,parent_type;dotlrn_community,pretty_name,pretty_plural,description,package_id,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args ('dotlrn_community_type__delete','community_type'); + +select define_function_args ('dotlrn_community_type__name','community_type'); + +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; +BEGIN + return dotlrn_community_type__new( + p_community_type, + p_parent_type, + p_pretty_name, + p_pretty_plural, + p_description, + null, + null, + null, + null, + null, + ); +END; +' language 'plpgsql'; + +create function dotlrn_community_type__new (varchar,varchar,varchar,varchar,varchar,integer,timestamp,integer,varchar,integer) +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_package_id alias for $6; + p_creation_date alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + p_context_id alias for $10; + 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; + + PERFORM acs_object_type__create_type ( + p_community_type, + p_community_type, + p_community_type, + v_parent_object_type, + ''dotlrn_communities'', + ''community_id'', + v_unique_name, + ''acs_group.name'' + ); + + insert + into group_types + (group_type, default_join_policy) + values + (p_community_type, ''closed''); + + insert + into dotlrn_community_types + (community_type, pretty_name, description, package_id, supertype) + values + (p_community_type, p_pretty_name, p_description, p_package_id, p_parent_type); + + return p_community_type; +END; +' language 'plpgsql'; + + +create function dotlrn_community_type__delete(varchar) +returns integer as ' +DECLARE + p_community_type alias for $1; +BEGIN + delete + from dotlrn_community_types + where community_type = p_community_type; + + PERFORM acs_object_type__drop_type(p_community_type); + return(0); +END; +' language 'plpgsql'; + + +create function dotlrn_community_type__name(varchar) +returns varchar as ' +DECLARE + p_community_type alias for $1; +BEGIN + return name from dotlrn_community_types where community_type= p_community_type; +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__set_active_dates','community_id,start_date,end_date'); + +select define_function_args('dotlrn_community__delete','community_id'); + +select define_function_args('dotlrn_community__name','community_id'); + +select define_function_args('dotlrn_community__member_p','community_id,party_id'); + +select define_function_args('dotlrn_community__admin_p','community_id,party_id'); + +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) +returns integer as ' +DECLARE + 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; +BEGIN + c_id := acs_group__new ( + 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 + (community_id, + parent_community_id, + community_type, + community_key, + pretty_name, + description, + package_id, + portal_id, + portal_template_id) + values + (c_id, + p_parent_community_id, + p_community_type, + p_community_key, + p_pretty_name, + p_description, + p_package_id, + p_portal_id, + p_portal_template_id); + + return c_id; +END; +' language 'plpgsql'; + + +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_end_date alias for $3; +BEGIN + update dotlrn_communities + set active_start_date = p_start_date, + active_end_date = p_end_date + where community_id = p_community_id; + + return p_community_id; +END; +' language 'plpgsql'; + +create function dotlrn_community__delete(integer) +returns integer as ' +DECLARE + p_community_id alias for $1; +BEGIN + delete + from dotlrn_communities + where community_id = p_community_id; + + PERFORM acs_group__delete(p_community_id); + return(0); +END; +' language 'plpgsql'; + + +create function dotlrn_community__name(integer) +returns varchar as ' +DECLARE + p_community_id alias for $1; +BEGIN + return acs_group__name(p_community_id); +END; +' language 'plpgsql'; + + +create function dotlrn_community__member_p(integer,integer) +returns boolean as ' +DECLARE + p_community_id alias for $1; + p_party_id alias for $2; +BEGIN + -- to do (ben) + return ''t''; +END; +' language 'plpgsql'; + + +create function dotlrn_community__admin_p(integer,integer) +returns boolean as ' +DECLARE + p_community_id alias for $1; + p_party_id alias for $2; +BEGIN + IF acs_permission__permission_p(p_community_id, p_party_id, ''dotlrn_admin_community'') = ''t'' + then return ''t''; + end if; + + IF acs_permission__permission_p(p_community_id, p_party_id, ''admin'') = ''t'' + then return ''t''; + end if; + + return ''f''; +END; +' language 'plpgsql'; + + +create function dotlrn_community__url(integer) +returns varchar as ' +DECLARE + p_community_id alias for $1; +BEGIN + return site_node__url(site_nodes.node_id) + from dotlrn_communities, + site_nodes + where dotlrn_communities.community_id = p_community_id + and site_nodes.object_id = dotlrn_communities.package_id; +END; +' language 'plpgsql'; + + +create view dotlrn_communities_full +as + select dotlrn_communities.*, + dotlrn_community__url(dotlrn_communities.community_id) as url, + groups.group_name, + groups.join_policy + from dotlrn_communities, + groups + where dotlrn_communities.community_id = groups.group_id; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-community-memberships-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-community-memberships-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-community-memberships-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,302 @@ + +-- +-- The DotLRN communities membership constructs +-- copyright 2001, OpenForce, Inc. +-- distributed under the GNU GPL v2 +-- +-- for Oracle 8/8i. (We're guessing 9i works, too). +-- ported to PG by Yon and Ben +-- +-- @author Ben Adida (ben@openforce.net) +-- @author yon (yon@openforce.net) +-- @creation-date November 6th, 2001 +-- @version $Id: dotlrn-community-memberships-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create table dotlrn_member_rels ( + rel_id integer + constraint dotlrn_member_rels_rel_id_fk + references membership_rels (rel_id) + constraint dotlrn_member_rels_rel_id_pk + primary key, + portal_id integer + constraint dotlrn_member_rels_portal_fk + references portals (portal_id) +); + +create view dotlrn_member_rels_full +as + select acs_rels.rel_id as rel_id, + acs_rels.object_id_one as community_id, + acs_rels.object_id_two as user_id, + acs_rels.rel_type, + (select acs_rel_roles.pretty_name + from acs_rel_roles + where acs_rel_roles.role = (select acs_rel_types.role_two + from acs_rel_types + where acs_rel_types.rel_type = acs_rels.rel_type)) as role, + dotlrn_member_rels.portal_id, + membership_rels.member_state + from dotlrn_member_rels, + acs_rels, + membership_rels + where dotlrn_member_rels.rel_id = acs_rels.rel_id + and acs_rels.rel_id = membership_rels.rel_id; + +create view dotlrn_member_rels_approved +as + select * + from dotlrn_member_rels_full + where member_state = 'approved'; + +create table dotlrn_admin_rels ( + rel_id integer + constraint dotlrn_admin_rels_rel_id_fk + references dotlrn_member_rels (rel_id) + constraint dotlrn_admin_rels_rel_id_pk + primary key +); + +create view dotlrn_admin_rels_full +as + select dotlrn_member_rels_full.rel_id, + dotlrn_member_rels_full.community_id, + dotlrn_member_rels_full.user_id, + dotlrn_member_rels_full.rel_type, + dotlrn_member_rels_full.role, + dotlrn_member_rels_full.portal_id, + dotlrn_member_rels_full.member_state + from dotlrn_member_rels_full, + dotlrn_admin_rels + where dotlrn_member_rels_full.rel_id = dotlrn_admin_rels.rel_id; + +-- +-- For Classes +-- + +create table dotlrn_student_rels ( + rel_id integer + constraint dotlrn_student_rels_rel_id_fk + references dotlrn_member_rels (rel_id) + constraint dotlrn_student_rels_rel_id_pk + primary key +); + +create view dotlrn_student_rels_full +as + select dotlrn_member_rels_full.rel_id, + dotlrn_member_rels_full.community_id, + dotlrn_member_rels_full.user_id, + dotlrn_member_rels_full.rel_type, + dotlrn_member_rels_full.role, + dotlrn_member_rels_full.portal_id, + dotlrn_member_rels_full.member_state + from dotlrn_member_rels_full, + dotlrn_student_rels + where dotlrn_member_rels_full.rel_id = dotlrn_student_rels.rel_id; + +create table dotlrn_ta_rels ( + rel_id integer + constraint dotlrn_ta_rels_rel_id_fk + references dotlrn_admin_rels (rel_id) + constraint dotlrn_ta_rels_rel_id_pk + primary key +); + +create view dotlrn_ta_rels_full +as + select dotlrn_admin_rels_full.rel_id, + dotlrn_admin_rels_full.community_id, + dotlrn_admin_rels_full.user_id, + dotlrn_admin_rels_full.rel_type, + dotlrn_admin_rels_full.role, + dotlrn_admin_rels_full.portal_id, + dotlrn_admin_rels_full.member_state + from dotlrn_admin_rels_full, + dotlrn_ta_rels + where dotlrn_admin_rels_full.rel_id = dotlrn_ta_rels.rel_id; + +create table dotlrn_ca_rels ( + rel_id integer + constraint dotlrn_ca_rels_rel_id_fk + references dotlrn_admin_rels (rel_id) + constraint dotlrn_ca_rels_rel_id_pk + primary key +); + +create view dotlrn_ca_rels_full +as + select dotlrn_admin_rels_full.rel_id, + dotlrn_admin_rels_full.community_id, + dotlrn_admin_rels_full.user_id, + dotlrn_admin_rels_full.rel_type, + dotlrn_admin_rels_full.role, + dotlrn_admin_rels_full.portal_id, + dotlrn_admin_rels_full.member_state + from dotlrn_admin_rels_full, + dotlrn_ca_rels + where dotlrn_admin_rels_full.rel_id = dotlrn_ca_rels.rel_id; + +create table dotlrn_cadmin_rels ( + rel_id integer + constraint dotlrn_cadmin_rels_rel_id_fk + references dotlrn_admin_rels (rel_id) + constraint dotlrn_cadmin_rels_rel_id_pk + primary key +); + +create view dotlrn_cadmin_rels_full +as + select dotlrn_admin_rels_full.rel_id, + dotlrn_admin_rels_full.community_id, + dotlrn_admin_rels_full.user_id, + dotlrn_admin_rels_full.rel_type, + dotlrn_admin_rels_full.role, + dotlrn_admin_rels_full.portal_id, + dotlrn_admin_rels_full.member_state + from dotlrn_admin_rels_full, + dotlrn_cadmin_rels + where dotlrn_admin_rels_full.rel_id = dotlrn_cadmin_rels.rel_id; + +create table dotlrn_instructor_rels ( + rel_id integer + constraint dotlrn_instructor_rels_rel_fk + references dotlrn_admin_rels(rel_id) + constraint dotlrn_instructor_rels_rel_pk + primary key +); + +create view dotlrn_instructor_rels_full +as + select dotlrn_admin_rels_full.rel_id, + dotlrn_admin_rels_full.community_id, + dotlrn_admin_rels_full.user_id, + dotlrn_admin_rels_full.rel_type, + dotlrn_admin_rels_full.role, + dotlrn_admin_rels_full.portal_id, + dotlrn_admin_rels_full.member_state + from dotlrn_admin_rels_full, + dotlrn_instructor_rels + where dotlrn_admin_rels_full.rel_id = dotlrn_instructor_rels.rel_id; + +-- +-- Object Types and Attributes +-- + +select acs_rel_type__create_type ( + 'dotlrn_member_rel', + 'dotLRN Community Membership', + 'dotLRN Community Memberships', + 'membership_rel', + 'dotlrn_member_rels', + 'rel_id', + 'dotlrn_member_rel', + 'dotlrn_community', null, + 0, null, + 'user', 'member', + 0, null + ); + + select acs_rel_type__create_type ( + 'dotlrn_admin_rel', + 'dotLRN Admin Community Membership', + 'dotLRN Admin Community Memberships', + 'dotlrn_member_rel', + 'dotlrn_admin_rels', + 'rel_id', + 'dotlrn_admin_rel', + 'dotlrn_community', null, + 0, null, + 'user', 'admin', + 0, null + ); + + select acs_rel_type__create_type ( + 'dotlrn_student_rel', + 'dotLRN Student Community Membership', + 'dotLRN Student Community Memberships', + 'dotlrn_member_rel', + 'dotlrn_student_rels', + 'rel_id', + 'dotlrn_student_rel', + 'dotlrn_class_instance', null, + 0, null, + 'user', 'student', + 0, null + ); + + select acs_rel_type__create_type ( + 'dotlrn_ta_rel', + 'dotLRN Teaching Assistant Community Membership', + 'dotLRN Teaching Assistant Community Memberships', + 'dotlrn_admin_rel', + 'dotlrn_ta_rels', + 'rel_id', + 'dotlrn_ta_rel', + 'dotlrn_class_instance', null, + 0, null, + 'user', 'teaching_assistant', + 0, null + ); + + select acs_rel_type__create_type ( + 'dotlrn_ca_rel', + 'dotLRN Course Assitant Community Membership', + 'dotLRN Course Assitant Community Memberships', + 'dotlrn_admin_rel', + 'dotlrn_ca_rels', + 'rel_id', + 'dotlrn_ca_rel', + 'dotlrn_class_instance', null, + 0, null, + 'user', 'course_assistant', + 0, null + ); + + select acs_rel_type__create_type ( + 'dotlrn_cadmin_rel', + 'dotlrn_admin_rel', + 'dotLRN Course Administrator Community Membership', + 'dotLRN Course Administrator Community Memberships', + 'dotlrn_cadmin_rel', + 'dotlrn_cadmin_rels', + 'rel_id', + 'dotlrn_class_instance', null, + 0, null, + 'user', 'course_admin', + 0, null + ); + + select acs_rel_type__create_type ( + 'dotlrn_instructor_rel', + 'dotLRN Instructor Community Membership', + 'dotLRN Instructor Community Memberships', + 'dotlrn_admin_rel', + 'dotlrn_instructor_rels', + 'rel_id', + 'dotlrn_instructor_rel', + 'dotlrn_class_instance', null, + 0, null, + 'user', 'instructor', + 0, null + ); + + -- + -- and now for the attributes + -- + select acs_attribute__create_attribute ( + 'dotlrn_member_rel', + 'portal_id', + 'integer', + 'Page ID', + 'Page IDs', + NULL, + NULL, + NULL, + 1, + 1, + NULL, + 'type_specific', + 'f' + ); +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-community-memberships-packages-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-community-memberships-packages-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-community-memberships-packages-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,407 @@ + +-- +-- The DotLRN memberships packages +-- copyright 2001, OpenForce, Inc. +-- distributed under the GNU GPL v2 +-- +-- for Oracle 8/8i. (We're guessing 9i works, too). +-- ported to PG by Yon and Ben +-- +-- ben@openforce.net +-- started November 6th, 2001 +-- + +-- +-- Basic dotLRN membership rel +-- + +select define_function_args('dotlrn_member_rel__new','rel_id,rel_type;dotlrn_member_rel,portal_id,community_id,user_id,member_state;approved,creation_user,creation_ip'); + +select define_function_args('dotlrn_member_rel__delete','rel_id'); + + +create function dotlrn_member_rel__new(integer,varchar,integer,integer,integer,varchar,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_rel_type alias for $2; + p_portal_id alias for $3; + p_community_id alias for $4; + p_user_id alias for $5; + p_member_state alias for $6; + p_creation_user alias for $7; + p_creation_ip alias for $8; + v_rel_id integer; +BEGIN + v_rel_id:= membership_rel__new( + p_rel_id, + p_rel_type, + p_community_id, + p_user_id, + p_member_state, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_member_rels + (rel_id, portal_id) + values + (v_rel_id, p_portal_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_member_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete from dotlrn_member_rels where rel_id= p_rel_id; + + PERFORM membership_rel__delete(p_rel_id); + + return 0; +END; +' language 'plpgsql'; + + + +select define_function_args('dotlrn_admin_rel__new','rel_id,rel_type;dotlrn_admin_rel,community_id,user_id,member_state,portal_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_admin_rel__delete','rel_id'); + +create function dotlrn_admin_rel__new(integer,varchar,integer,integer,varchar,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_rel_type alias for $2; + p_community_id alias for $3; + p_user_id alias for $4; + p_member_state alias for $5; + p_portal_id alias for $6; + p_creation_user alias for $7; + p_creation_ip alias for $8; + v_rel_id integer; +BEGIN + v_rel_id:= dotlrn_member_rel__new( + p_rel_id, + p_rel_type, + p_portal_id, + p_community_id, + p_user_id, + p_member_state, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_admin_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_admin_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete from dotlrn_admin_rels where rel_id= p_rel_id; + + PERFORM dotlrn_admin_rel__delete(p_rel_id); + + return 0; +END; +' language 'plpgsql'; + + +select define_function_args('dotlrn_student_rel__new','rel_id,rel_type;dotlrn_student_rel,portal_id,class_instance_id,user_id,member_state,creation_user,creation_ip'); + +select define_function_args('dotlrn_student_rel__delete','rel_id'); + +create function dotlrn_student_rel__new(integer,varchar,integer,integer,integer,varchar,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_rel_type alias for $2; + p_portal_id alias for $3; + p_class_instance_id alias for $4; + p_user_id alias for $5; + p_member_state alias for $6; + p_creation_user alias for $7; + p_creation_ip alias for $8; + v_rel_id integer; +BEGIN + v_rel_id:= dotlrn_member_rel__new( + p_rel_id, + p_rel_type, + p_portal_id, + p_class_instance_id, + p_user_id, + p_member_state, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_student_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_student_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete from dotlrn_student_rels where rel_id= p_rel_id; + + PERFORM dotlrn_member_rel__delete(p_rel_id); + + return 0; +END; +' language 'plpgsql'; + + + +select define_function_args('dotlrn_ta_rel__new','rel_id,rel_type;dotlrn_ta_rel,class_instance_id,user_id,member_state,portal_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_ta_rel__delete','rel_id'); + + +create function dotlrn_ta_rel__new(integer,varchar,integer,integer,varchar,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_rel_type alias for $2; + p_class_instance_id alias for $3; + p_user_id alias for $4; + p_member_state alias for $5; + p_portal_id alias for $6; + p_creation_user alias for $7; + p_creation_ip alias for $8; + v_rel_id integer; +BEGIN + v_rel_id:= dotlrn_admin_rel__new( + p_rel_id, + p_rel_type, + p_class_instance_id, + p_user_id, + p_member_state, + p_portal_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_ta_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_ta_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_ta_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_admin_rel__delete(rel_id); + + return 0; +END; +' language 'plpgsql'; + + +-- ca rel + +select define_function_args('dotlrn_ca_rel__new','rel_id,rel_type;dotlrn_ca_rel,class_instance_id,user_id,member_state,portal_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_ca_rel__delete','rel_id'); + + +create function dotlrn_ca_rel__new(integer,varchar,integer,integer,varchar,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_rel_type alias for $2; + p_class_instance_id alias for $3; + p_user_id alias for $4; + p_member_state alias for $5; + p_portal_id alias for $6; + p_creation_user alias for $7; + p_creation_ip alias for $8; + v_rel_id integer; +BEGIN + v_rel_id:= dotlrn_admin_rel__new( + p_rel_id, + p_rel_type, + p_class_instance_id, + p_user_id, + p_member_state, + p_portal_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_ca_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_ca_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_ca_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_admin_rel__delete(rel_id); + + return 0; +END; +' language 'plpgsql'; + + +-- course admin + +select define_function_args('dotlrn_cadmin_rel__new','rel_id,rel_type;dotlrn_cadmin_rel,class_instance_id,user_id,member_state,portal_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_cadmin_rel__delete','rel_id'); + + +create function dotlrn_cadmin_rel__new(integer,varchar,integer,integer,varchar,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_rel_type alias for $2; + p_class_instance_id alias for $3; + p_user_id alias for $4; + p_member_state alias for $5; + p_portal_id alias for $6; + p_creation_user alias for $7; + p_creation_ip alias for $8; + v_rel_id integer; +BEGIN + v_rel_id:= dotlrn_admin_rel__new( + p_rel_id, + p_rel_type, + p_class_instance_id, + p_user_id, + p_member_state, + p_portal_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_cadmin_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_cadmin_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_cadmin_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_admin_rel__delete(rel_id); + + return 0; +END; +' language 'plpgsql'; + + +-- instructor rel + +select define_function_args('dotlrn_instructor_rel__new','rel_id,rel_type;dotlrn_instructor_rel,class_instance_id,user_id,member_state,portal_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_instructor_rel__delete','rel_id'); + + +create function dotlrn_instructor_rel__new(integer,varchar,integer,integer,varchar,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_rel_type alias for $2; + p_class_instance_id alias for $3; + p_user_id alias for $4; + p_member_state alias for $5; + p_portal_id alias for $6; + p_creation_user alias for $7; + p_creation_ip alias for $8; + v_rel_id integer; +BEGIN + v_rel_id:= dotlrn_admin_rel__new( + p_rel_id, + p_rel_type, + p_class_instance_id, + p_user_id, + p_member_state, + p_portal_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_instructor_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_instructor_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_instructor_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_admin_rel__delete(rel_id); + + return 0; +END; +' language 'plpgsql'; + Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/dotlrn-create.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/dotlrn/sql/postgresql/dotlrn-create.sql 20 Sep 2001 18:29:35 -0000 1.1 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-create.sql 29 Mar 2002 16:58:17 -0000 1.2 @@ -1,59 +1,22 @@ - -- -- The DotLRN basic system -- copyright 2001, OpenForce, Inc. -- distributed under the GNU GPL v2 -- --- ben@openforce.net --- for PostgreSQL 7.1.2 and above +-- for PG 7.1.3 and above -- +-- ben@openforce.net -- started August 18th, 2001 -- - - --- Since a lot of stuff needs to happen to set up a group type, --- this isn't going to be done at the data model level, it's going to --- be done in Tcl. Woohoo. - - --- We do need some basic data model to represent classes, class instances --- and the associated information there - -create table dotlrn_classes ( - class_key varchar(100) not null - constraint dotlrn_class_class_key_fk - references group_types(group_type) - constraint dotlrn_class_class_key_pk - primary key, - node_id integer not null - constraint dotlrn_class_node_id_fk - references site_nodes (node_id) -); - -create table dotlrn_class_instances ( - class_instance_id integer not null - constraint dotlrn_class_inst_id_fk - references groups(group_id) - constraint dotlrn_class_inst_id_pk - primary key, - class_instance_key varchar(100) not null - constraint dotlrn_class_inst_key_un unique, - class_key varchar(100) not null - constraint dotlrn_class_inst_class_key_fk - references dotlrn_classes(class_key), - node_id integer not null - constraint dotlrn_class_inst_node_id_fk - references site_nodes(node_id) -); - -create table dotlrn_class_inst_applets ( - class_instance_id integer not null - constraint dotlrn_class_inst_app_inst_fk - references dotlrn_class_instances(class_instance_id), - applet varchar(200) not null, - constraint dotlrn_class_inst_app_pk primary key (class_instance_id, applet), - node_id integer not null - constraint dotlrn_class_inst_app_node_id_fk - references site_nodes(node_id) -); +\i dotlrn-users-create.sql +\i dotlrn-communities-create.sql +\i dotlrn-clubs-create.sql +\i dotlrn-classes-create.sql +\i dotlrn-applet-sc-create.sql +\i dotlrn-init.sql +\i dotlrn-community-memberships-create.sql +\i dotlrn-community-memberships-packages-create.sql +\i dotlrn-main-portlet-create.sql +\i dotlrn-security-create.sql +\i dotlrn-user-extension-create.sql Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-external-profile-provider-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-external-profile-provider-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-external-profile-provider-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,52 @@ +-- +-- Implementation of the profile provider interface for dotLRN Externals. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-external-profile-provider-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +begin + + -- create the implementation + select acs_sc_impl__new( + 'profile_provider', + 'dotlrn_external_profile_provider', + 'dotlrn_external_profile_provider' + ); + + -- add the bindings to the method implementations + + -- name method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_external_profile_provider', + 'name', + 'dotlrn_external_profile_provider::name', + 'TCL' + ); + + -- prettyName method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_external_profile_provider', + 'prettyName', + 'dotlrn_external_profile_provider::prettyName', + 'TCL' + ); + + -- render method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_external_profile_provider', + 'render', + 'dotlrn_external_profile_provider::render', + 'TCL' + ); + + -- bind this implementation to the interface it implements + select acs_sc_binding__new( + 'profile_provider', + 'dotlrn_external_profile_provider' + ); + +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-external-profile-provider-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-external-profile-provider-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-external-profile-provider-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,50 @@ +-- +-- Implementation of the profile provider interface for dotLRN Externals. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-external-profile-provider-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + -- drop the binding between this implementation and the interface it + -- implements. + acs_sc_binding.delete( + contract_name => 'profile_provider', + impl_name => 'dotlrn_external_profile_provider' + ); + + -- drop the bindings to the method implementations + + -- name method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_external_profile_provider', + impl_operation_name => 'name' + ); + + -- prettyName method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_external_profile_provider', + impl_operation_name => 'prettyName' + ); + + -- render method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_external_profile_provider', + impl_operation_name => 'render' + ); + + -- drop the implementation + acs_sc_impl.delete( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_external_profile_provider' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-externals-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,26 @@ +-- +-- Create the dotLRN Externals package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-externals-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create table dotlrn_external_profile_rels ( + rel_id integer + constraint dotlrn_ext_rels_rel_id_fk + references dotlrn_user_profile_rels (rel_id) + constraint dotlrn_ext_profile_rels_pk + primary key +); + +create table dotlrn_full_ext_profile_rels ( + rel_id integer + constraint dotlrn_fe_prfl_rels_rel_fk + references dotlrn_full_user_profile_rels (rel_id) + constraint dotlrn_fe_prfl_rels_pk + primary key +); + +\i dotlrn-external-profile-provider-create.sql +\i dotlrn-externals-init.sql +\i dotlrn-externals-package-create.sql Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-externals-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,13 @@ +-- +-- Drop the dotLRN Externals package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-externals-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +@@ dotlrn-externals-package-drop.sql +@@ dotlrn-externals-sanitize.sql +@@ dotlrn-external-profile-provider-drop.sql + +drop table dotlrn_full_ext_profile_rels; +drop table dotlrn_external_profile_rels; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-externals-init.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-init.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,114 @@ +-- +-- Initialize the dotLRN Externals package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-externals-init.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + + +create function inline_1() +returns integer as ' +declare + foo integer; + group_id integer; + dotlrn_users_group_id integer; +begin + + PERFORM acs_rel_type__create_type( + ''dotlrn_external_profile_rel'', + ''dotLRN External Profile Membership'', + ''dotLRN External Profile Memberships'', + ''dotlrn_user_profile_rel'', + ''dotlrn_external_profile_rels'', + ''rel_id'', + ''dotlrn_external_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(impl_id) + into foo + from acs_sc_impls + where impl_name = ''dotlrn_external_profile_provider''; + + group_id := profiled_group__new( + foo, + ''dotLRN Externals'' + ); + + insert + into dotlrn_user_types + (type, pretty_name, group_id) + values + (''external'', ''External'', group_id); + + foo := rel_segment__new( + ''dotLRN Profiled Externals'', + group_id, + ''dotlrn_external_profile_rel'' + ); + + select group_id + into dotlrn_users_group_id + from groups + where group_name = ''dotLRN Users''; + + foo := composition_rel__new( + dotlrn_users_group_id, + group_id + ); + +end; +' language 'plpgsql'; + +select inline_1(); +drop function inline_1(); + + +create function inline_2() +returns integer as ' +declare + foo integer; +begin + PERFORM acs_rel_type--create_type( + ''dotlrn_full_external_profile_rel'', + ''dotLRN Full External Profile Membership'', + ''dotLRN Full External Profile Memberships'', + ''dotlrn_full_user_profile_rel'', + ''dotlrn_full_external_profile_rels'', + ''rel_id'', + ''dotlrn_full_external_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = ''dotlrn_external_profile_provider''); + + foo := rel_segment__new( + ''dotLRN Full Profiled Externals'', + foo, + ''dotlrn_full_external_profile_rel'' + ); + +end; +' language 'plpgsql'; + +select inline_2(); +drop function inline_2(); \ No newline at end of file Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-externals-package-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-package-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,141 @@ +-- +-- Create the External package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-externals-package-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +select define_function_args ('dotlrn_external_profile_rel__new','rel_id,id,rel_type;dotlrn_external_profile_rel,group_id,user_id,creation_user,creation_ip'); + +select define_function_args ('dotlrn_external_profile_rel__delete','rel_id'); + + +create function dotlrn_external_profile_rel__new(integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_id alias for $2; + p_rel_type alias for $3; + p_group_id alias for $4; + p_user_id alias for $5; + p_creation_user alias for $6; + p_creation_ip alias for $7; + v_rel_id dotlrn_user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := dotlrn_user_profile_rel__new( + p_rel_id, + p_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_external_profile_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_external_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_external_profile_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_user_profile_rel__delete(p_rel_id); + return (0); +END; +' language 'plpgsql'; + + +-- full externals + +select define_function_args('dotlrn_full_external_profile_rel__new','rel_id,portal_id,theme_id,id,rel_type,group_id,user_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_full_external_profile_rel__delete','rel_id'); + + +create function dotlrn_full_external_profile_rel__new(integer,integer,integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_portal_id alias for $2; + p_theme_id alias for $3; + p_id alias for $4; + p_rel_type alias for $5; + p_group_id alias for $6; + p_user_id alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + v_rel_id dotlrn_full_user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := dotlrn_full_user_profile_rel__new( + p_rel_id, + p_portal_id, + p_theme_id, + p_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_full_external_profile_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_full_external_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_full_external_profile_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_full_user_profile_rel__delete(p_rel_id); + return(0); +END; +' language 'plpgsql'; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-externals-package-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-package-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,9 @@ +-- +-- Create the dotLRN Externals package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-externals-package-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +drop package dotlrn_full_ext_profile_rel; +drop package dotlrn_external_profile_rel; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-sanitize.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-externals-sanitize.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-externals-sanitize.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,65 @@ +-- +-- Sanitize the dotLRN External package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-externals-sanitize.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Full Profiled Externals'; + + rel_segment.delete( + segment_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_full_external_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Profiled Externals'; + + rel_segment.delete( + segment_id => foo + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = 'dotlrn_external_profile_provider'); + + delete + from dotlrn_user_types + where group_id = foo; + + profiled_group.delete( + group_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_external_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/dotlrn-init.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-init.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,49 @@ + +-- +-- The DotLRN basic system +-- copyright 2001, OpenForce, Inc. +-- distributed under the GNU GPL v2 +-- +-- for PostgreSQL v7.1 and above +-- +-- ben@openforce.net +-- September 25th, 2001 +-- we remember September 11th, 2001 +-- + +begin + -- Create the base community type + select dotlrn_community_type__new( + 'dotlrn_community', + null, + 'Community', + 'Communities', + 'Communities - the base community type' + ); + + -- create the dotlrn_class community type + select dotlrn_community_type__new( + 'dotlrn_class_instance', + 'dotlrn_community', + 'Class', + 'Classes', + 'e.g. 6.001' + ); + + -- create the dotlrn_club community type + dotlrn_community_type__new( + 'dotlrn_club', + 'dotlrn_community', + 'Club', + 'Clubs', + 'e.g. Alumni' + ); + + -- create roles + acs_rel_type__create_role('student', 'Student', 'Students'); + acs_rel_type__create_role('course_assistant', 'Course Assistant', 'Course Assistants'); + acs_rel_type__create_role('teaching_assistant', 'Teaching Assistant', 'Teaching Assistants'); + acs_rel_type__create_role('instructor', 'Professor', 'Professors'); + acs_rel_type__create_role('course_admin', 'Course Administrator', 'Course Administrators'); + acs_rel_type__create_role('admin', 'Administrator', 'Administrators'); +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-main-portlet-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-main-portlet-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-main-portlet-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,128 @@ +-- +-- packages/dotlrn/sql/oracle/dotlrn-main-portlet-create.sql +-- + +-- Creates a dotLRN datasource for including on a user's main portal page. + +-- Copyright (C) 2001 OpenForce, Inc. +-- @author Ben Adida (ben@openforce.net) +-- @creation-date 2001-11-05 + +-- $Id: dotlrn-main-portlet-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ + +-- This is free software distributed under the terms of the GNU Public +-- License version 2 or higher. Full text of the license is available +-- from the GNU Project: http://www.fsf.org/copyleft/gpl.html + +create function inline_0 () +return integer as ' +declare +begin + ds_id portal_datasources.datasource_id%TYPE; +begin + ds_id := portal_datasource__new( + ''dotlrn_main_portlet'', + ''Displays the list of communities a user belongs to'' + ); + + -- the standard 4 params + + -- shadeable_p + select portal_datasource.set_def_param( + ds_id, ''t'', ''t'', ''shadeable_p'', ''f'' + ); + + -- hideable_p + select portal_datasource__set_def_param( + ds_id, ''t'', ''t'', ''hideable_p'', ''f'' + ); + + -- user_editable_p + select portal_datasource__set_def_param( + ds_id, ''t'', ''t'', ''user_editable_p'', ''f'' + ); + + -- shaded_p + select portal_datasource__set_def_param( + ds_id, ''t'', ''t'', ''shaded_p'', ''f'' + ); + + -- link_hideable_p + select portal_datasource__set_def_param( + ds_id, ''t'', ''t'', ''link_hideable_p'', ''t'' + ); + + -- create the implementation + select acs_sc_impl__new( + ''portal_datasource'', + ''dotlrn_main_portlet'', + ''dotlrn_main_portlet'' + ); + + -- add all the hooks + select acs_sc_impl_alias__new ( + ''portal_datasource'', + ''dotlrn_main_portlet'', + ''MyName'', + ''dotlrn_main_portlet::my_name'', + ''TCL'' + ); + + select acs_sc_impl_alias__new ( + ''portal_datasource'', + ''dotlrn_main_portlet'', + ''GetPrettyName'', + ''dotlrn_main_portlet::get_pretty_name'', + ''TCL'' + ); + + select acs_sc_impl_alias__new ( + ''portal_datasource'', + ''dotlrn_main_portlet'', + ''Link'', + ''dotlrn_main_portlet::link'', + ''TCL'' + ); + + select acs_sc_impl_alias__new ( + ''portal_datasource'', + ''dotlrn_main_portlet'', + ''AddSelfToPage'', + ''dotlrn_main_portlet::add_self_to_page'', + ''TCL'' + ); + + select acs_sc_impl_alias__new ( + ''portal_datasource'', + ''dotlrn_main_portlet'', + ''Show'', + ''dotlrn_main_portlet::show'', + ''TCL'' + ); + + select acs_sc_impl_alias__new ( + ''portal_datasource'', + ''dotlrn_main_portlet'', + ''Edit'', + ''dotlrn_main_portlet::edit'', + ''TCL'' + ); + + select acs_sc_impl_alias__new ( + ''portal_datasource'', + ''dotlrn_main_portlet'', + ''RemoveSelfFromPage'', + ''dotlrn_main_portlet::remove_self_from_page'', + ''TCL'' + ); + + select acs_sc_binding__new ( + ''portal_datasource'', + ''dotlrn_main_portlet'' + ); + +end; +' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-main-portlet-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-main-portlet-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-main-portlet-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,33 @@ + +-- Drops dotLRN main portlet datasources for portal portlets + +-- Copyright (C) 2001 Openforce, Inc. +-- @author Ben Adida (ben@openforce.net) +-- @creation-date 2001-11-04 + +-- $Id: dotlrn-main-portlet-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ + +-- This is free software distributed under the terms of the GNU Public +-- License version 2 or higher. Full text of the license is available +-- from the GNU Project: http://www.fsf.org/copyleft/gpl.html + +declare + ds_id portal_datasources.datasource_id%TYPE; +begin + + begin + select datasource_id into ds_id + from portal_datasources + where name = 'dotlrn-main-portlet'; + exception when no_data_found then + ds_id := null; + end; + + if ds_id is not null then + portal_datasource.delete(ds_id); + end if; + +end; +/ +show errors; + Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professor-profile-provider-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-professor-profile-provider-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professor-profile-provider-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,52 @@ +-- +-- Implementation of the profile provider interface for dotLRN Professors. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-professor-profile-provider-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +begin + + -- create the implementation + select acs_sc_impl__new( + 'profile_provider', + 'dotlrn_professor_profile_provider', + 'dotlrn_professor_profile_provider' + ); + + -- add the bindings to the method implementations + + -- name method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_professor_profile_provider', + 'name', + 'dotlrn_professor_profile_provider::name', + 'TCL' + ); + + -- prettyName method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_professor_profile_provider', + 'prettyName', + 'dotlrn_professor_profile_provider::prettyName', + 'TCL' + ); + + -- render method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_professor_profile_provider', + 'render', + 'dotlrn_professor_profile_provider::render', + 'TCL' + ); + + -- bind this implementation to the interface it implements + select acs_sc_binding__new( + 'profile_provider', + 'dotlrn_professor_profile_provider' + ); + +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professor-profile-provider-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-professor-profile-provider-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professor-profile-provider-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,50 @@ +-- +-- Implementation of the profile provider interface for dotLRN Professors. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-professor-profile-provider-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + -- drop the binding between this implementation and the interface it + -- implements. + acs_sc_binding.delete( + contract_name => 'profile_provider', + impl_name => 'dotlrn_professor_profile_provider' + ); + + -- drop the bindings to the method implementations + + -- name method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_professor_profile_provider', + impl_operation_name => 'name' + ); + + -- prettyName method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_professor_profile_provider', + impl_operation_name => 'prettyName' + ); + + -- render method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_professor_profile_provider', + impl_operation_name => 'render' + ); + + -- drop the implementation + acs_sc_impl.delete( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_professor_profile_provider' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-professors-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,26 @@ +-- +-- Create the dotLRN Professors package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-professors-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create table dotlrn_professor_profile_rels ( + rel_id integer + constraint dotlrn_prof_rels_rel_id_fk + references dotlrn_user_profile_rels (rel_id) + constraint dotlrn_prof_profile_rels_pk + primary key +); + +create table dotlrn_full_prof_profile_rels ( + rel_id integer + constraint dotlrn_fp_prfl_rels_rel_fk + references dotlrn_full_user_profile_rels (rel_id) + constraint dotlrn_full_prof_prfl_rels_pk + primary key +); + +\i dotlrn-professor-profile-provider-create.sql +\i dotlrn-professors-init.sql +\i dotlrn-professors-package-create.sql Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-professors-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,13 @@ +-- +-- Drop the dotLRN Professors package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-professors-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +@@ dotlrn-professors-package-drop.sql +@@ dotlrn-professors-sanitize.sql +@@ dotlrn-professor-profile-provider-drop.sql + +drop table dotlrn_full_prof_profile_rels; +drop table dotlrn_professor_profile_rels; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-professors-init.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-init.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,114 @@ +-- +-- Initialize the dotLRN Professors package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-professors-init.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + + +create function inline_1() +returns integer as ' +declare + foo integer; + group_id integer; + dotlrn_users_group_id integer; +begin + + PERFORM acs_rel_type__create_type( + ''dotlrn_professor_profile_rel'', + ''dotLRN Professor Profile Membership'', + ''dotLRN Professor Profile Memberships'', + ''dotlrn_user_profile_rel'', + ''dotlrn_professor_profile_rels'', + ''rel_id'', + ''dotlrn_professor_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(impl_id) + into foo + from acs_sc_impls + where impl_name = ''dotlrn_professor_profile_provider''; + + group_id := profiled_group__new( + foo, + ''dotLRN Professors'' + ); + + insert + into dotlrn_user_types + (type, pretty_name, group_id) + values + (''professor'', ''Professor'', group_id); + + foo := rel_segment__new( + ''dotLRN Profiled Professors'', + group_id, + ''dotlrn_professor_profile_rel'' + ); + + select group_id + into dotlrn_users_group_id + from groups + where group_name = ''dotLRN Users''; + + foo := composition_rel__new( + dotlrn_users_group_id, + group_id + ); + +end; +' language 'plpgsql'; + +select inline_1(); +drop function inline_1(); + + +create function inline_2() +returns integer as ' +declare + foo integer; +begin + PERFORM acs_rel_type--create_type( + ''dotlrn_full_professor_profile_rel'', + ''dotLRN Full Professor Profile Membership'', + ''dotLRN Full Professor Profile Memberships'', + ''dotlrn_full_user_profile_rel'', + ''dotlrn_full_professor_profile_rels'', + ''rel_id'', + ''dotlrn_full_professor_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = ''dotlrn_professor_profile_provider''); + + foo := rel_segment__new( + ''dotLRN Full Profiled Professors'', + foo, + ''dotlrn_full_professor_profile_rel'' + ); + +end; +' language 'plpgsql'; + +select inline_2(); +drop function inline_2(); \ No newline at end of file Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-professors-package-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-package-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,141 @@ +-- +-- Create the Professor package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-professors-package-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +select define_function_args ('dotlrn_professor_profile_rel__new','rel_id,id,rel_type;dotlrn_professor_profile_rel,group_id,user_id,creation_user,creation_ip'); + +select define_function_args ('dotlrn_professor_profile_rel__delete','rel_id'); + + +create function dotlrn_professor_profile_rel__new(integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_id alias for $2; + p_rel_type alias for $3; + p_group_id alias for $4; + p_user_id alias for $5; + p_creation_user alias for $6; + p_creation_ip alias for $7; + v_rel_id dotlrn_user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := dotlrn_user_profile_rel__new( + p_rel_id, + p_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_professor_profile_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_professor_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_professor_profile_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_user_profile_rel__delete(p_rel_id); + return (0); +END; +' language 'plpgsql'; + + +-- full professors + +select define_function_args('dotlrn_full_professor_profile_rel__new','rel_id,portal_id,theme_id,id,rel_type,group_id,user_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_full_professor_profile_rel__delete','rel_id'); + + +create function dotlrn_full_professor_profile_rel__new(integer,integer,integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_portal_id alias for $2; + p_theme_id alias for $3; + p_id alias for $4; + p_rel_type alias for $5; + p_group_id alias for $6; + p_user_id alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + v_rel_id dotlrn_full_user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := dotlrn_full_user_profile_rel__new( + p_rel_id, + p_portal_id, + p_theme_id, + p_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_full_professor_profile_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_full_professor_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_full_professor_profile_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_full_user_profile_rel__delete(p_rel_id); + return(0); +END; +' language 'plpgsql'; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-professors-package-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-package-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,9 @@ +-- +-- Create the dotLRN Professors package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-professors-package-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +drop package dotlrn_full_prof_profile_rel; +drop package dotlrn_professor_profile_rel; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-sanitize.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-professors-sanitize.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-professors-sanitize.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,65 @@ +-- +-- Sanitize the dotLRN Professor package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-professors-sanitize.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Full Profiled Professors'; + + rel_segment.delete( + segment_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_full_professor_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Profiled Professors'; + + rel_segment.delete( + segment_id => foo + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = 'dotlrn_professor_profile_provider'); + + delete + from dotlrn_user_types + where group_id = foo; + + profiled_group.delete( + group_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_professor_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-security-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-security-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-security-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,60 @@ + +-- +-- dotLRN Project +-- +-- copyright 2001, OpenForce +-- released under the GPL, v2.0 +-- +-- ben@openforce +-- ported to PG by Yon and Ben +-- +-- 11/28/2001 +-- +-- Security Setup for dotLRN +-- privileges, privilege inheritance +-- + +BEGIN + -- the ability to browse dotLRN in general + PERFORM acs_privilege__create_privilege('dotlrn_browse'); + + -- the ability to even view that a community exists + PERFORM acs_privilege__create_privilege('dotlrn_view_community'); + + -- the ability to participate in a community + PERFORM acs_privilege__create_privilege('dotlrn_edit_community'); + + -- the ability to admin a community + PERFORM acs_privilege__create_privilege('dotlrn_admin_community'); + + -- the ability to create a community + PERFORM acs_privilege__create_privilege('dotlrn_create_community'); + + -- the ability to even view a community type + PERFORM acs_privilege__create_privilege('dotlrn_view_community_type'); + + -- the ability to admin a community type + PERFORM acs_privilege__create_privilege('dotlrn_admin_community_type'); + + -- the ability to create a community type + PERFORM acs_privilege__create_privilege('dotlrn_create_community_type'); + + -- Consistent permissions + PERFORM acs_privilege__add_child('dotlrn_edit_community', 'dotlrn_view_community'); + PERFORM acs_privilege__add_child('dotlrn_admin_community', 'dotlrn_edit_community'); + + -- inheritance + PERFORM acs_privilege__add_child('create', 'dotlrn_create_community_type'); + PERFORM acs_privilege__add_child('create', 'dotlrn_create_community'); + PERFORM acs_privilege__add_child('write', 'dotlrn_edit_community'); + PERFORM acs_privilege__add_child('read', 'dotlrn_view_community'); + PERFORM acs_privilege__add_child('read', 'dotlrn_view_community_type'); + PERFORM acs_privilege__add_child('admin', 'dotlrn_admin_community'); + PERFORM acs_privilege__add_child('admin', 'dotlrn_admin_community_type'); + + -- for now, we only want admins to be able to browse by default + PERFORM acs_privilege__add_child('admin', 'dotlrn_browse'); + + -- no default permissions + +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-student-profile-provider-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-student-profile-provider-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-student-profile-provider-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,52 @@ +-- +-- Implementation of the profile provider interface for dotLRN Students. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-student-profile-provider-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +begin + + -- create the implementation + select acs_sc_impl__new( + 'profile_provider', + 'dotlrn_student_profile_provider', + 'dotlrn_student_profile_provider' + ); + + -- add the bindings to the method implementations + + -- name method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_student_profile_provider', + 'name', + 'dotlrn_student_profile_provider::name', + 'TCL' + ); + + -- prettyName method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_student_profile_provider', + 'prettyName', + 'dotlrn_student_profile_provider::prettyName', + 'TCL' + ); + + -- render method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_student_profile_provider', + 'render', + 'dotlrn_student_profile_provider::render', + 'TCL' + ); + + -- bind this implementation to the interface it implements + select acs_sc_binding__new( + 'profile_provider', + 'dotlrn_student_profile_provider' + ); + +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-student-profile-provider-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-student-profile-provider-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-student-profile-provider-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,50 @@ +-- +-- Implementation of the profile provider interface for dotLRN Students. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-student-profile-provider-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + -- drop the binding between this implementation and the interface it + -- implements. + acs_sc_binding.delete( + contract_name => 'profile_provider', + impl_name => 'dotlrn_student_profile_provider' + ); + + -- drop the bindings to the method implementations + + -- name method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_student_profile_provider', + impl_operation_name => 'name' + ); + + -- prettyName method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_student_profile_provider', + impl_operation_name => 'prettyName' + ); + + -- render method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_student_profile_provider', + impl_operation_name => 'render' + ); + + -- drop the implementation + acs_sc_impl.delete( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_student_profile_provider' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-students-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,26 @@ +-- +-- Create the dotLRN Students package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-students-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create table dotlrn_student_profile_rels ( + rel_id integer + constraint dotlrn_std_rels_rel_id_fk + references dotlrn_user_profile_rels (rel_id) + constraint dotlrn_student_profile_rels_pk + primary key +); + +create table dotlrn_full_stud_profile_rels ( + rel_id integer + constraint dotlrn_fs_prfl_rels_rel_fk + references dotlrn_full_user_profile_rels (rel_id) + constraint dotlrn_fs_prfl_rels_pk + primary key +); + +\i dotlrn-student-profile-provider-create.sql +\i dotlrn-students-init.sql +\i dotlrn-students-package-create.sql Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-students-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,13 @@ +-- +-- Drop the dotLRN Students package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-students-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +@@ dotlrn-students-package-drop.sql +@@ dotlrn-students-sanitize.sql +@@ dotlrn-student-profile-provider-drop.sql + +drop table dotlrn_full_stud_profile_rels; +drop table dotlrn_student_profile_rels; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-students-init.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-init.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,114 @@ +-- +-- Initialize the dotLRN Students package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-students-init.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + + +create function inline_1() +returns integer as ' +declare + foo integer; + group_id integer; + dotlrn_users_group_id integer; +begin + + PERFORM acs_rel_type__create_type( + ''dotlrn_student_profile_rel'', + ''dotLRN Student Profile Membership'', + ''dotLRN Student Profile Memberships'', + ''dotlrn_user_profile_rel'', + ''dotlrn_student_profile_rels'', + ''rel_id'', + ''dotlrn_student_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(impl_id) + into foo + from acs_sc_impls + where impl_name = ''dotlrn_student_profile_provider''; + + group_id := profiled_group__new( + foo, + ''dotLRN Students'' + ); + + insert + into dotlrn_user_types + (type, pretty_name, group_id) + values + (''student'', ''Student'', group_id); + + foo := rel_segment__new( + ''dotLRN Profiled Students'', + group_id, + ''dotlrn_student_profile_rel'' + ); + + select group_id + into dotlrn_users_group_id + from groups + where group_name = ''dotLRN Users''; + + foo := composition_rel__new( + dotlrn_users_group_id, + group_id + ); + +end; +' language 'plpgsql'; + +select inline_1(); +drop function inline_1(); + + +create function inline_2() +returns integer as ' +declare + foo integer; +begin + PERFORM acs_rel_type--create_type( + ''dotlrn_full_student_profile_rel'', + ''dotLRN Full Student Profile Membership'', + ''dotLRN Full Student Profile Memberships'', + ''dotlrn_full_user_profile_rel'', + ''dotlrn_full_student_profile_rels'', + ''rel_id'', + ''dotlrn_full_student_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = ''dotlrn_student_profile_provider''); + + foo := rel_segment__new( + ''dotLRN Full Profiled Students'', + foo, + ''dotlrn_full_student_profile_rel'' + ); + +end; +' language 'plpgsql'; + +select inline_2(); +drop function inline_2(); \ No newline at end of file Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-students-package-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-package-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,141 @@ +-- +-- Create the Student package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-students-package-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +select define_function_args ('dotlrn_student_profile_rel__new','rel_id,id,rel_type;dotlrn_student_profile_rel,group_id,user_id,creation_user,creation_ip'); + +select define_function_args ('dotlrn_student_profile_rel__delete','rel_id'); + + +create function dotlrn_student_profile_rel__new(integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_id alias for $2; + p_rel_type alias for $3; + p_group_id alias for $4; + p_user_id alias for $5; + p_creation_user alias for $6; + p_creation_ip alias for $7; + v_rel_id dotlrn_user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := dotlrn_user_profile_rel__new( + p_rel_id, + p_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_student_profile_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_student_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_student_profile_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_user_profile_rel__delete(p_rel_id); + return (0); +END; +' language 'plpgsql'; + + +-- full students + +select define_function_args('dotlrn_full_student_profile_rel__new','rel_id,portal_id,theme_id,id,rel_type,group_id,user_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_full_student_profile_rel__delete','rel_id'); + + +create function dotlrn_full_student_profile_rel__new(integer,integer,integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_portal_id alias for $2; + p_theme_id alias for $3; + p_id alias for $4; + p_rel_type alias for $5; + p_group_id alias for $6; + p_user_id alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + v_rel_id dotlrn_full_user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := dotlrn_full_user_profile_rel__new( + p_rel_id, + p_portal_id, + p_theme_id, + p_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_full_student_profile_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_full_student_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_full_student_profile_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_full_user_profile_rel__delete(p_rel_id); + return(0); +END; +' language 'plpgsql'; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-students-package-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-package-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,9 @@ +-- +-- Create the dotLRN Students package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-students-package-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +drop package dotlrn_full_stud_profile_rel; +drop package dotlrn_student_profile_rel; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-sanitize.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-students-sanitize.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-students-sanitize.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,65 @@ +-- +-- Sanitize the dotLRN Student package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-students-sanitize.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Full Profiled Students'; + + rel_segment.delete( + segment_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_full_student_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Profiled Students'; + + rel_segment.delete( + segment_id => foo + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = 'dotlrn_student_profile_provider'); + + delete + from dotlrn_user_types + where group_id = foo; + + profiled_group.delete( + group_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_student_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-user-extension-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-user-extension-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-user-extension-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,74 @@ + +-- +-- The dotLRN extension to user data notifications +-- copyright 2002, OpenForce +-- distributed under GPL v2.0 +-- +-- ported to PG by Yon and Ben +-- ben@openforce.net +-- +-- 01/22/2002 +-- + + +begin + -- create the implementation + select acs_sc_impl__new ( + 'UserData', + 'dotlrn_user_extension', + 'dotlrn_user_extension' + ); + + -- add all the hooks + + -- UserNew + select acs_sc_impl_alias__new ( + 'UserData', + 'dotlrn_user_extension', + 'UserNew', + 'dotlrn_user_extension::user_new', + 'TCL' + ); + + -- UserNew + select acs_sc_impl_alias__new ( + 'UserData', + 'dotlrn_user_extension', + 'UserApprove', + 'dotlrn_user_extension::user_approve', + 'TCL' + ); + + -- UserNew + select acs_sc_impl_alias__new ( + 'UserData', + 'dotlrn_user_extension', + 'UserDeapprove', + 'dotlrn_user_extension::user_deapprove', + 'TCL' + ); + + -- UserNew + select acs_sc_impl_alias__new ( + 'UserData', + 'dotlrn_user_extension', + 'UserModify', + 'dotlrn_user_extension::user_modify', + 'TCL' + ); + + -- UserNew + select acs_sc_impl_alias__new ( + 'UserData', + 'dotlrn_user_extension', + 'UserDelete', + 'dotlrn_user_extension::user_delete', + 'TCL' + ); + + -- Add the binding + select acs_sc_binding__new ( + 'UserData', + 'dotlrn_user_extension' + ); +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-user-profile-provider-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-user-profile-provider-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-user-profile-provider-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,52 @@ +-- +-- Implementation of the profile provider interface for dotlrn users. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-user-profile-provider-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +begin + + -- create the implementation + select acs_sc_impl__new( + 'profile_provider', + 'dotlrn_user_profile_provider', + 'dotlrn_user_profile_provider' + ); + + -- add the bindings to the method implementations + + -- name method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_user_profile_provider', + 'name', + 'dotlrn_user_profile_provider::name', + 'TCL' + ); + + -- prettyName method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_user_profile_provider', + 'prettyName', + 'dotlrn_user_profile_provider::prettyName', + 'TCL' + ); + + -- render method + select acs_sc_impl_alias__new( + 'profile_provider', + 'dotlrn_user_profile_provider', + 'render', + 'dotlrn_user_profile_provider::render', + 'TCL' + ); + + -- bind this implementation to the interface it implements + select acs_sc_binding__new( + 'profile_provider', + 'dotlrn_user_profile_provider' + ); + +end; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-user-profile-provider-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-user-profile-provider-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-user-profile-provider-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,50 @@ +-- +-- Implementation of the profile provider interface for dotlrn users. +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-user-profile-provider-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + -- drop the binding between this implementation and the interface it + -- implements. + acs_sc_binding.delete( + contract_name => 'profile_provider', + impl_name => 'dotlrn_user_profile_provider' + ); + + -- drop the bindings to the method implementations + + -- name method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_user_profile_provider', + impl_operation_name => 'name' + ); + + -- prettyName method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_user_profile_provider', + impl_operation_name => 'prettyName' + ); + + -- render method + foo := acs_sc_impl.delete_alias( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_user_profile_provider', + impl_operation_name => 'render' + ); + + -- drop the implementation + acs_sc_impl.delete( + impl_contract_name => 'profile_provider', + impl_name => 'dotlrn_user_profile_provider' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-users-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,85 @@ +-- +-- Create the dotLRN Users package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-users-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create table dotlrn_user_profile_rels ( + rel_id integer + constraint dotlrn_usr_prfl_rels_rel_id_fk + references user_profile_rels (rel_id) + constraint dotlrn_user_profile_rels_pk + primary key, + id varchar(100) +); + +create table dotlrn_full_user_profile_rels ( + rel_id integer + constraint dotlrn_fup_rels_rel_fk + references dotlrn_user_profile_rels (rel_id) + constraint dotlrn_full_user_prfl_rels_pk + primary key, + portal_id integer + constraint dotlrn_fup_rels_portal_fk + references portals (portal_id) + constraint dotlrn_fup_rels_portal_nn + not null, + theme_id integer + constraint dotlrn_fup_rels_theme_fk + references portal_element_themes (theme_id) +); + +create table dotlrn_user_types ( + type varchar(100) + constraint dotlrn_user_types_pk + primary key, + pretty_name varchar(200), + group_id integer + constraint dotlrn_user_types_group_id_fk + references profiled_groups (group_id) + constraint dotlrn_user_types_group_id_nn + not null +); + +create view dotlrn_users +as + select acs_rels.rel_id, + dotlrn_user_profile_rels.id, + registered_users.user_id, + registered_users.first_names, + registered_users.last_name, + registered_users.email, + dotlrn_user_types.type + from dotlrn_user_profile_rels, + acs_rels, + registered_users, + dotlrn_user_types + where acs_rels.object_id_two = registered_users.user_id + and acs_rels.object_id_one = dotlrn_user_types.group_id + and acs_rels.rel_id = dotlrn_user_profile_rels.rel_id; + +create view dotlrn_full_users +as + select dotlrn_users.*, + dotlrn_full_user_profile_rels.portal_id, + dotlrn_full_user_profile_rels.theme_id + from dotlrn_users, + dotlrn_full_user_profile_rels + where dotlrn_users.rel_id = dotlrn_full_user_profile_rels.rel_id; + +\i dotlrn-user-profile-provider-create.sql +\i dotlrn-users-init.sql +\i dotlrn-users-package-create.sql + +-- create administrators +\i dotlrn-admins-create.sql + +-- create professors +\i dotlrn-professors-create.sql + +-- create students +\i dotlrn-students-create.sql + +-- create external users +\i dotlrn-externals-create.sql Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-users-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,29 @@ +-- +-- Drop the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-users-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +-- drop external users +@@ dotlrn-externals-drop.sql + +-- drop students +@@ dotlrn-students-drop.sql + +-- drop professors +@@ dotlrn-professors-drop.sql + +-- drop admins +@@ dotlrn-admins-drop.sql + +@@ dotlrn-users-package-drop.sql +@@ dotlrn-users-sanitize.sql +@@ dotlrn-user-profile-provider-drop.sql + +drop view dotlrn_full_users; +drop view dotlrn_users; + +drop table dotlrn_user_types; +drop table dotlrn_full_user_profile_rels; +drop table dotlrn_user_profile_rels; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-users-init.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-init.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,98 @@ +-- +-- Initialize the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-users-init.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +create function inline_1() +returns integer as ' +declare + foo integer; +begin + + acs_rel_type.create_type( + ''dotlrn_user_profile_rel'', + ''dotLRN User Profile Membership'', + ''dotLRN User Profile Memberships'', + ''user_profile_rel'', + ''dotlrn_user_profile_rels'', + ''rel_id'', + ''dotlrn_user_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(impl_id) + into foo + from acs_sc_impls + where impl_name = ''dotlrn_user_profile_provider''; + + foo := profiled_group__new( + foo, + ''dotLRN Users'' + ); + + foo := rel_segment__new( + segment_name => ''dotLRN Profiled Users'', + group_id => foo, + rel_type => ''dotlrn_user_profile_rel'' + ); + + return(0); +end; +' language 'plpgsql'; + +select inline_1(); +drop function inline_1(); + + +create function inline_2() +returns integer as ' +declare + foo integer; +begin + PERFORM acs_rel_type__create_type( + ''dotlrn_full_user_profile_rel'', + ''dotLRN Full User Profile Membership'', + ''dotLRN Full User Profile Memberships'', + ''dotlrn_user_profile_rel'', + ''dotlrn_full_user_profile_rels'', + ''rel_id'', + ''dotlrn_full_user_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = ''dotlrn_user_profile_provider''); + + foo := rel_segment__new( + ''dotLRN Full Profiled Users'', + foo, + ''dotlrn_full_user_profile_rel'' + ); + + return (0); +end; +' language 'plpgsql'; + +select inline_2(); +drop function inline_2(); + Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-users-package-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-package-create.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,138 @@ +-- +-- Create the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-users-package-create.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +select define_function_args ('dotlrn_user_profile_rel__new','rel_id,id,rel_type;dotlrn_user_profile_rel,group_id,user_id,creation_user,creation_ip'); + +select define_function_args ('dotlrn_user_profile_rel__delete','rel_id'); + + +create function dotlrn_user_profile_rel__new(integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_id alias for $2; + p_rel_type alias for $3; + p_group_id alias for $4; + p_user_id alias for $5; + p_creation_user alias for $6; + p_creation_ip alias for $7; + v_rel_id user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := user_profile_rel__new( + p_rel_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_user_profile_rels + (rel_id, id) + values + (v_rel_id, p_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_user_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_user_profile_rels + where rel_id = p_rel_id; + + PERFORM user_profile_rel__delete(p_rel_id); + return (0); +END; +' language 'plpgsql'; + + +-- full users + +select define_function_args('dotlrn_full_user_profile_rel__new','rel_id,portal_id,theme_id,id,rel_type;dotlrn_full_user_profile_rel,group_id,user_id,creation_user,creation_ip'); + +select define_function_args('dotlrn_full_user_profile_rel__delete','rel_id'); + + +create function dotlrn_full_user_profile_rel__new(integer,integer,integer,varchar,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_portal_id alias for $2; + p_theme_id alias for $3; + p_id alias for $4; + p_rel_type alias for $5; + p_group_id alias for $6; + p_user_id alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + v_rel_id dotlrn_user_profile_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 := p_group_id; + end if; + + v_rel_id := dotlrn_user_profile_rel__new( + p_rel_id, + p_id, + p_rel_type, + v_group_id, + p_user_id, + p_creation_user, + p_creation_ip + ); + + insert + into dotlrn_full_user_profile_rels + (rel_id, portal_id, theme_id) + values + (v_rel_id, p_portal_id, p_theme_id); + + return v_rel_id; +END; +' language 'plpgsql'; + + +create function dotlrn_full_user_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from dotlrn_full_user_profile_rels + where rel_id = p_rel_id; + + PERFORM dotlrn_user_profile_rel__delete(p_rel_id); + return (0); +END; +' language 'plpgsql'; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-users-package-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-package-drop.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,9 @@ +-- +-- Create the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-users-package-drop.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +drop package dotlrn_full_user_profile_rel; +drop package dotlrn_user_profile_rel; Index: openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-sanitize.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/Attic/dotlrn-users-sanitize.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/sql/postgresql/dotlrn-users-sanitize.sql 29 Mar 2002 16:58:17 -0000 1.1 @@ -0,0 +1,61 @@ +-- +-- Sanitize the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: dotlrn-users-sanitize.sql,v 1.1 2002/03/29 16:58:17 ben Exp $ +-- + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Full Profiled Users'; + + rel_segment.delete( + segment_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_full_user_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors + +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = 'dotLRN Profiled Users'; + + rel_segment.delete( + segment_id => foo + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = 'dotlrn_user_profile_provider'); + + profiled_group.delete( + group_id => foo + ); + + acs_rel_type.drop_type( + rel_type => 'dotlrn_user_profile_rel', + cascade_p => 't' + ); + +end; +/ +show errors Index: openacs-4/packages/dotlrn-dotlrn/sql/postgresql/dotlrn-dotlrn-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-dotlrn/sql/postgresql/dotlrn-dotlrn-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-dotlrn/sql/postgresql/dotlrn-dotlrn-create.sql 29 Mar 2002 16:59:13 -0000 1.1 @@ -0,0 +1,107 @@ + +-- +-- The "dotLRN" applet for dotLRN +-- copyright 2001, OpenForce +-- distributed under GPL v2.0 +-- +-- ported to PG by Ben +-- ben,arjun@openforce.net +-- +-- 10/05/2001 +-- + + +begin + -- create the implementation + select acs_sc_impl__new ( + 'dotlrn_applet', + 'dotlrn_dotlrn', + 'dotlrn_dotlrn' + ); + + -- add all the hooks + + -- GetPrettyName + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_dotlrn', + 'GetPrettyName', + 'dotlrn_dotlrn::get_pretty_name', + 'TCL' + ); + + -- AddApplet + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_dotlrn', + 'AddApplet', + 'dotlrn_dotlrn::add_applet', + 'TCL' + ); + + + -- AddAppletToCommunity + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_dotlrn', + 'AddAppletToCommunity', + 'dotlrn_dotlrn::add_applet_to_community', + 'TCL' + ); + + + -- RemoveApplet + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_dotlrn', + 'RemoveApplet', + 'dotlrn_dotlrn::remove_applet', + 'TCL' + ); + + -- AddUser + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_dotlrn', + 'AddUser', + 'dotlrn_dotlrn::add_user', + 'TCL' + ); + + + -- AddUserToCommunity + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_dotlrn', + 'AddUserToCommunity', + 'dotlrn_dotlrn::add_user_to_community', + 'TCL' + ); + + -- RemoveUser + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_dotlrn', + 'RemoveUser', + 'dotlrn_dotlrn::remove_user', + 'TCL' + ); + + -- RemoveUserFromCommunity + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_dotlrn', + 'RemoveUserFromCommunity', + 'dotlrn_dotlrn::remove_user_from_community', + 'TCL' + ); + + -- Add the binding + select acs_sc_binding__new ( + contract_name => 'dotlrn_applet', + impl_name => 'dotlrn_dotlrn' + ); +end; + +\i dotlrn-members-create.sql +\i dotlrn-members-staff-create.sql Index: openacs-4/packages/dotlrn-dotlrn/sql/postgresql/dotlrn-members-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-dotlrn/sql/postgresql/dotlrn-members-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-dotlrn/sql/postgresql/dotlrn-members-create.sql 29 Mar 2002 16:59:13 -0000 1.1 @@ -0,0 +1,104 @@ + +-- +-- The "dotLRN members" applet for dotLRN +-- copyright 2001, OpenForce +-- distributed under GPL v2.0 +-- +-- ported to PG by Ben +-- ben,arjun@openforce.net +-- +-- 10/05/2001 +-- + + +begin + -- create the implementation + select acs_sc_impl__new ( + 'dotlrn_applet', + 'dotlrn_members', + 'dotlrn_members' + ); + + -- add all the hooks + + -- GetPrettyName + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members', + 'GetPrettyName', + 'dotlrn_members::get_pretty_name', + 'TCL' + ); + + -- AddApplet + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members', + 'AddApplet', + 'dotlrn_members::add_applet', + 'TCL' + ); + + + -- AddAppletToCommunity + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members', + 'AddAppletToCommunity', + 'dotlrn_members::add_applet_to_community', + 'TCL' + ); + + + -- RemoveApplet + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members', + 'RemoveApplet', + 'dotlrn_members::remove_applet', + 'TCL' + ); + + -- AddUser + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members', + 'AddUser', + 'dotlrn_members::add_user', + 'TCL' + ); + + + -- AddUserToCommunity + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members', + 'AddUserToCommunity', + 'dotlrn_members::add_user_to_community', + 'TCL' + ); + + -- RemoveUser + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members', + 'RemoveUser', + 'dotlrn_members::remove_user', + 'TCL' + ); + + -- RemoveUserFromCommunity + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members', + 'RemoveUserFromCommunity', + 'dotlrn_members::remove_user_from_community', + 'TCL' + ); + + -- Add the binding + select acs_sc_binding__new ( + contract_name => 'dotlrn_applet', + impl_name => 'dotlrn_members' + ); +end; Index: openacs-4/packages/dotlrn-dotlrn/sql/postgresql/dotlrn-members-staff-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-dotlrn/sql/postgresql/dotlrn-members-staff-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn-dotlrn/sql/postgresql/dotlrn-members-staff-create.sql 29 Mar 2002 16:59:13 -0000 1.1 @@ -0,0 +1,104 @@ + +-- +-- The "dotLRN members" applet for dotLRN +-- copyright 2001, OpenForce +-- distributed under GPL v2.0 +-- +-- +-- ben,arjun@openforce.net +-- +-- 10/05/2001 +-- + + +begin + -- create the implementation + select acs_sc_impl__new ( + 'dotlrn_applet', + 'dotlrn_members_staff', + 'dotlrn_members_staff' + ); + + -- add all the hooks + + -- GetPrettyName + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members_staff', + 'GetPrettyName', + 'dotlrn_members_staff::get_pretty_name', + 'TCL' + ); + + -- AddApplet + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members_staff', + 'AddApplet', + 'dotlrn_members_staff::add_applet', + 'TCL' + ); + + + -- AddAppletToCommunity + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members_staff', + 'AddAppletToCommunity', + 'dotlrn_members_staff::add_applet_to_community', + 'TCL' + ); + + + -- RemoveApplet + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members_staff', + 'RemoveApplet', + 'dotlrn_members_staff::remove_applet', + 'TCL' + ); + + -- AddUser + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members_staff', + 'AddUser', + 'dotlrn_members_staff::add_user', + 'TCL' + ); + + + -- AddUserToCommunity + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members_staff', + 'AddUserToCommunity', + 'dotlrn_members_staff::add_user_to_community', + 'TCL' + ); + + -- RemoveUser + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members_staff', + 'RemoveUser', + 'dotlrn_members_staff::remove_user', + 'TCL' + ); + + -- RemoveUserFromCommunity + select acs_sc_impl_alias__new ( + 'dotlrn_applet', + 'dotlrn_members_staff', + 'RemoveUserFromCommunity', + 'dotlrn_members_staff::remove_user_from_community', + 'TCL' + ); + + -- Add the binding + select acs_sc_binding__new ( + contract_name => 'dotlrn_applet', + impl_name => 'dotlrn_members_staff' + ); +end; Index: openacs-4/packages/profile-provider/sql/postgresql/profile-provider-contract-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/profile-provider/sql/postgresql/profile-provider-contract-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/profile-provider/sql/postgresql/profile-provider-contract-create.sql 29 Mar 2002 16:53:02 -0000 1.1 @@ -0,0 +1,78 @@ +-- +-- Profile Provider interface definition +-- +-- ported to PG by Ben. +-- @author yon@openforce.net +-- @version $Id: profile-provider-contract-create.sql,v 1.1 2002/03/29 16:53:02 ben Exp $ +-- + + -- create the interface + select acs_sc_contract__new( + 'profile_provider', + 'profile provider interface definition' + ); + + -- create the methods of this interface + + -- name method + select acs_sc_msg_type__new( + 'profile_provider.name.InputType', + '' + ); + + select acs_sc_msg_type__new( + 'profile_provider.name.OutputType', + 'name:string' + ); + + select acs_sc_operation__new( + 'profile_provider', + 'name', + 'return the name of the profile provider', + 't', + 0, + 'profile_provider.name.InputType', + 'profile_provider.name.OutputType' + ); + + -- prettyName method + select acs_sc_msg_type__new( + 'profile_provider.prettyName.InputType', + '' + ); + + select acs_sc_msg_type__new( + 'profile_provider.prettyName.OutputType', + 'prettyName:string' + ); + + select acs_sc_operation__new( + 'profile_provider', + 'prettyName', + 'return the pretty name of the profile provider', + 't', + 0, + 'profile_provider.prettyName.InputType', + 'profile_provider.prettyName.OutputType' + ); + + -- render method + select acs_sc_msg_type__new( + 'profile_provider.render.InputType', + '' + ); + + select acs_sc_msg_type__new( + 'profile_provider.render.OutputType', + 'output:string' + ); + + select acs_sc_operation__new( + 'profile_provider', + 'render', + 'output the HTML from this profile provider', + 't', + 0, + 'profile_provider.render.InputType', + 'profile_provider.render.OutputType' + ); Index: openacs-4/packages/profile-provider/sql/postgresql/profile-provider-contract-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/profile-provider/sql/postgresql/profile-provider-contract-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/profile-provider/sql/postgresql/profile-provider-contract-drop.sql 29 Mar 2002 16:53:02 -0000 1.1 @@ -0,0 +1,59 @@ +-- +-- Profile Provider interface definition +-- +-- @author yon@openforce.net +-- @version $Id: profile-provider-contract-drop.sql,v 1.1 2002/03/29 16:53:02 ben Exp $ +-- + +begin + + -- drop the methods of this interface + + -- name method + perform acs_sc_operation__delete( + 'profile_provider', + 'name' + ); + + perform acs_sc_msg_type__delete( + 'profile_provider.name.InputType' + ); + + perform acs_sc_msg_type__delete( + 'profile_provider.name.OutputType' + ); + + -- prettyName method + perform acs_sc_operation__delete( + 'profile_provider', + 'prettyName' + ); + + perform acs_sc_msg_type__delete( + 'profile_provider.prettyName.InputType' + ); + + perform acs_sc_msg_type__delete( + 'profile_provider.prettyName.OutputType' + ); + + -- render method + perform acs_sc_operation__delete( + 'profile_provider', + 'render' + ); + + perform acs_sc_msg_type__delete( + 'profile_provider.render.InputType' + ); + + perform acs_sc_msg_type__delete( + 'profile_provider.render.OutputType' + ); + + -- create the interface + perform acs_sc_contract__delete( + 'profile_provider' + ); + +end; Index: openacs-4/packages/profile-provider/sql/postgresql/profile-provider-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/profile-provider/sql/postgresql/profile-provider-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/profile-provider/sql/postgresql/profile-provider-create.sql 29 Mar 2002 16:53:02 -0000 1.1 @@ -0,0 +1,9 @@ +-- +-- Create the Profile Provider packages +-- +-- @author yon@openforce.net +-- @version $Id: profile-provider-create.sql,v 1.1 2002/03/29 16:53:02 ben Exp $ +-- + +\i profile-provider-contract-create.sql +\i profiled-group-create.sql Index: openacs-4/packages/profile-provider/sql/postgresql/profile-provider-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/profile-provider/sql/postgresql/profile-provider-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/profile-provider/sql/postgresql/profile-provider-drop.sql 29 Mar 2002 16:53:02 -0000 1.1 @@ -0,0 +1,9 @@ +-- +-- Drop the Profile Provider packages +-- +-- @author yon@openforce.net +-- @version $Id: profile-provider-drop.sql,v 1.1 2002/03/29 16:53:02 ben Exp $ +-- + +\i profiled-group-drop.sql +\i profile-provider-contract-drop.sql Index: openacs-4/packages/profile-provider/sql/postgresql/profiled-group-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/profile-provider/sql/postgresql/profiled-group-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/profile-provider/sql/postgresql/profiled-group-create.sql 29 Mar 2002 16:53:02 -0000 1.1 @@ -0,0 +1,114 @@ +-- +-- Groups that implement the profile provider interface +-- +-- @author yon@milliped.com +-- @version $Id: profiled-group-create.sql,v 1.1 2002/03/29 16:53:02 ben Exp $ +-- + +create table profiled_groups ( + group_id integer + constraint pg_group_id_fk + references groups (group_id) + constraint profiled_groups_pk + primary key, + profile_provider integer + constraint pg_profile_provider_fk + references acs_sc_impls (impl_id) + constraint pg_profile_provider_nn + not null +); + +select acs_object_type__create_type( + 'profiled_group', + 'Profiled Group', + 'Profiled Groups', + 'group', + 'profiled_groups', + 'group_id', + 'profiled_group', + 'f', + NULL, + 'acs_group.name' +); + + +select define_function_args ('profiled_group__new','group_id,profile_provider,group_name,join_policy,email,url,object_type;profiled_group,creation_date,creation_user,creation_ip'); + +create function profiled_group__new(integer,integer,varchar,varchar,varchar,varchar,varchar,timestamp,integer,varchar) +returns integer as ' +DECLARE + p_group_id alias for $1; + p_profile_provider alias for $2; + p_group_name alias for $3; + p_join_policy alias for $4; + p_email alias for $5; + p_url alias for $6; + p_object_type alias for $7; + p_creation_date alias for $8; + p_creation_user alias for $9; + p_creation_ip alias for $10; + v_group_id integer; +BEGIN + v_group_id := acs_group__new( + p_group_id, + p_object_type, + p_creation_date, + p_creation_user, + p_creation_ip, + p_email, + p_url, + p_group_name, + p_join_policy, + NULL + ); + + insert + into profiled_groups + (group_id, profile_provider) + values + (v_group_id, p_profile_provider); + + return v_group_id; +END; +' language 'plpgsql'; + + +create function profiled_group__new(integer, varchar) +returns integer as ' +DECLARE + p_profile_provider alias for $1; + p_group_name alias for $2; +BEGIN + return profiled_group__new( + NULL, + p_profile_provider, + p_group_name, + NULL, + NULL, + NULL, + ''profiled_group'', + now(), + NULL, + NULL + ); +END; +' language 'plpgsql'; + + +select define_function_args('profiled_group__delete','group_id'); + +create function profiled_group__delete(integer) +returns integer as ' +DECLARE + p_group_id alias for $1; +BEGIN + delete + from profiled_groups + where profiled_groups.group_id = p_group_id; + + perform acs_group__delete(p_group_id); + + return 0; +END; +' language 'plpgsql'; + Index: openacs-4/packages/profile-provider/sql/postgresql/profiled-group-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/profile-provider/sql/postgresql/profiled-group-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/profile-provider/sql/postgresql/profiled-group-drop.sql 29 Mar 2002 16:53:02 -0000 1.1 @@ -0,0 +1,14 @@ +-- +-- Groups that implement the profile provider interface +-- +-- @author yon@milliped.com +-- @version $Id: profiled-group-drop.sql,v 1.1 2002/03/29 16:53:02 ben Exp $ +-- + +drop function profiled_group__new(integer,integer,varchar,varchar,varchar,varchar,varchar,timestamp,integer,varchar); + +drop function profiled_group__delete(integer); + +select acs_object_type__drop_type('profiled_group'); + +drop table profiled_groups; Index: openacs-4/packages/user-profile/sql/postgresql/user-profile-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-profile/sql/postgresql/user-profile-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-profile/sql/postgresql/user-profile-create.sql 29 Mar 2002 16:56:10 -0000 1.1 @@ -0,0 +1,18 @@ +-- +-- Create the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: user-profile-create.sql,v 1.1 2002/03/29 16:56:10 ben Exp $ +-- + +create table user_profile_rels ( + rel_id integer + constraint up_rels_rel_id_fk + references membership_rels (rel_id) + constraint user_profile_rels_pk + primary key +); + +\i user-profile-provider-create.sql +\i user-profile-init.sql +\i user-profile-package-create.sql Index: openacs-4/packages/user-profile/sql/postgresql/user-profile-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-profile/sql/postgresql/user-profile-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-profile/sql/postgresql/user-profile-drop.sql 29 Mar 2002 16:56:10 -0000 1.1 @@ -0,0 +1,12 @@ +-- +-- Drop the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: user-profile-drop.sql,v 1.1 2002/03/29 16:56:10 ben Exp $ +-- + +\i user-profile-package-drop.sql +\i user-profile-sanitize.sql +\i user-profile-provider-drop.sql + +drop table user_profile_rels; Index: openacs-4/packages/user-profile/sql/postgresql/user-profile-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-profile/sql/postgresql/user-profile-init.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-profile/sql/postgresql/user-profile-init.sql 29 Mar 2002 16:56:10 -0000 1.1 @@ -0,0 +1,60 @@ +-- +-- Initialize the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: user-profile-init.sql,v 1.1 2002/03/29 16:56:10 ben Exp $ +-- + +create function inline_1() +returns integer as ' +DECLARE + foo integer; +BEGIN + PERFORM acs_rel_type__create_type( + ''user_profile_rel'', + ''Profiled User Membership'', + ''Profiled User Memberships'', + ''membership_rel'', + ''user_profile_rels'', + ''rel_id'', + ''user_profile_rel'', + ''profiled_group'', + null, + 0, + null, + ''user'', + null, + 0, + 1 + ); + + select min(impl_id) + into foo + from acs_sc_impls + where impl_name = ''user_profile_provider''; + + foo:= profiled_group__new( + foo, + ''Profiled Users'' + ); + + PERFORM rel_segment__new( + NULL, + ''rel_segment'', + NULL, + NULL, + NULL, + NULL, + NULL, + ''Profiled Users'', + foo, + ''user_profile_rel'', + NULL + ); + + return 0; +end; +' language 'plpgsql'; + +select inline_1(); +drop function inline_1(); Index: openacs-4/packages/user-profile/sql/postgresql/user-profile-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-profile/sql/postgresql/user-profile-package-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-profile/sql/postgresql/user-profile-package-create.sql 29 Mar 2002 16:56:10 -0000 1.1 @@ -0,0 +1,72 @@ +-- +-- Create the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: user-profile-package-create.sql,v 1.1 2002/03/29 16:56:10 ben Exp $ +-- + + +select define_function_args ('user_profile_rel__new','rel_id,rel_type;user_profile_rel,group_id,user_id,creation_user,creation_ip'); + +select define_function_args ('user_profile_rel__delete','rel_id'); + + +create function user_profile_rel__new(integer,varchar,integer,integer,integer,varchar) +returns integer as ' +DECLARE + p_rel_id alias for $1; + p_rel_type alias for $2; + p_group_id alias for $3; + p_user_id alias for $4; + p_creation_user alias for $5; + p_creation_ip alias for $6; + v_rel_id membership_rels.rel_id%TYPE; + v_group_id groups.group_id%TYPE; +BEGIN + if p_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 = ''user_profile_provider''); + else + v_group_id := p_group_id; + end if; + + v_rel_id := membership_rel__new( + p_rel_id, + p_rel_type, + v_group_id, + p_user_id, + ''approved'', + p_creation_user, + p_creation_ip + ); + + insert + into user_profile_rels + (rel_id) + values + (v_rel_id); + + return v_rel_id; + end; +END; +' language 'plpgsql'; + + +create function user_profile_rel__delete(integer) +returns integer as ' +DECLARE + p_rel_id alias for $1; +BEGIN + delete + from user_profile_rels + where rel_id = p_rel_id; + + PERFORM membership_rel__delete(p_rel_id); + + return 0; +END; +' language 'plpgsql'; Index: openacs-4/packages/user-profile/sql/postgresql/user-profile-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-profile/sql/postgresql/user-profile-package-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-profile/sql/postgresql/user-profile-package-drop.sql 29 Mar 2002 16:56:10 -0000 1.1 @@ -0,0 +1,11 @@ +-- +-- Create the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: user-profile-package-drop.sql,v 1.1 2002/03/29 16:56:10 ben Exp $ +-- + +drop function user_profile_rel__new(integer,varchar,integer,integer,integer,varchar); + +drop function user_profile_rel__delete(integer); + Index: openacs-4/packages/user-profile/sql/postgresql/user-profile-provider-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-profile/sql/postgresql/user-profile-provider-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-profile/sql/postgresql/user-profile-provider-create.sql 29 Mar 2002 16:56:10 -0000 1.1 @@ -0,0 +1,48 @@ +-- +-- Implementation of the profile provider interface for users. +-- +-- @author yon@openforce.net +-- @version $Id: user-profile-provider-create.sql,v 1.1 2002/03/29 16:56:10 ben Exp $ +-- + + -- create the implementation + select acs_sc_impl__new( + 'profile_provider', + 'user_profile_provider', + 'user_profile_provider' + ); + + -- add the bindings to the method implementations + + -- name method + select acs_sc_impl_alias__new ( + 'profile_provider', + 'user_profile_provider', + 'name', + 'user_profile_provider::name', + 'TCL' + ); + + -- prettyName method + select acs_sc_impl_alias__new ( + 'profile_provider', + 'user_profile_provider', + 'prettyName', + 'user_profile_provider::prettyName', + 'TCL' + ); + + -- render method + select acs_sc_impl_alias__new ( + 'profile_provider', + 'user_profile_provider', + 'render', + 'user_profile_provider::render', + 'TCL' + ); + + -- bind this implementation to the interface it implements + select acs_sc_binding__new( + 'profile_provider', + 'user_profile_provider' + ); Index: openacs-4/packages/user-profile/sql/postgresql/user-profile-provider-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-profile/sql/postgresql/user-profile-provider-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-profile/sql/postgresql/user-profile-provider-drop.sql 29 Mar 2002 16:56:10 -0000 1.1 @@ -0,0 +1,46 @@ +-- +-- Implementation of the profile provider interface for users. +-- +-- @author yon@openforce.net +-- @version $Id: user-profile-provider-drop.sql,v 1.1 2002/03/29 16:56:10 ben Exp $ +-- + +begin + + -- drop the binding between this implementation and the interface it + -- implements. + select acs_sc_binding__delete ( + 'profile_provider', + 'user_profile_provider' + ); + + -- drop the bindings to the method implementations + + -- name method + select acs_sc_impl_alias__delete ( + 'profile_provider', + 'user_profile_provider', + 'name' + ); + + -- prettyName method + select acs_sc_impl_alias__delete ( + 'profile_provider', + 'user_profile_provider', + 'prettyName' + ); + + -- render method + select acs_sc_impl_alias__delete ( + 'profile_provider', + 'user_profile_provider', + 'render' + ); + + -- drop the implementation + select acs_sc_impl__delete( + 'profile_provider', + 'user_profile_provider' + ); + +end; Index: openacs-4/packages/user-profile/sql/postgresql/user-profile-sanitize.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/user-profile/sql/postgresql/user-profile-sanitize.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/user-profile/sql/postgresql/user-profile-sanitize.sql 29 Mar 2002 16:56:10 -0000 1.1 @@ -0,0 +1,43 @@ +-- +-- Sanitize the User Profile package +-- +-- @author yon@openforce.net +-- @version $Id: user-profile-sanitize.sql,v 1.1 2002/03/29 16:56:10 ben Exp $ +-- + +create function inline_1() +returns integer as ' +declare + foo integer; +begin + + select min(segment_id) + into foo + from rel_segments + where segment_name = ''Profiled Users''; + + perform rel_segment__delete( + foo + ); + + select min(group_id) + into foo + from profiled_groups + where profile_provider = (select min(impl_id) + from acs_sc_impls + where impl_name = ''user_profile_provider''); + + perform profiled_group__delete( + foo + ); + + select acs_rel_type__drop_type( + ''user_profile_rel'', + ''t'' + ); + +end; +' language 'plpgsql'; + +select inline_1(); +drop function inline_1();