Index: openacs-4/packages/dotlrn/sql/oracle/dotlrn-users-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/Attic/dotlrn-users-create.sql,v diff -u -r1.8 -r1.9 --- openacs-4/packages/dotlrn/sql/oracle/dotlrn-users-create.sql 10 Jan 2002 19:30:17 -0000 1.8 +++ openacs-4/packages/dotlrn/sql/oracle/dotlrn-users-create.sql 8 Feb 2002 20:50:56 -0000 1.9 @@ -1,157 +1,77 @@ - -- --- The DotLRN basic system --- copyright 2001, OpenForce, Inc. --- distributed under the GNU GPL v2 +-- Create the dotLRN Users package -- --- for Oracle 8/8i. (We're guessing 9i works, too). +-- @author yon@openforce.net +-- @version $Id$ -- --- ben@openforce.net --- October 30th, 2001 --- we remember September 11th, 2001 --- --- --- Users of the dotLRN system --- - --- not an object, there's really no use for it --- this is just so other, non-university systems can set up their own user types -create table dotlrn_user_types ( - type_id integer not null - constraint dlrn_user_type_pk primary key, - type varchar2(200) not null - constraint dlrn_user_type_un unique +create table dotlrn_user_profile_rels ( + rel_id constraint dotlrn_usr_prfl_rels_rel_id_fk + references user_profile_rels (rel_id) + constraint dotlrn_user_profile_rels_pk + primary key, + id varchar2(100) ); -create table dotlrn_user_rels ( - rel_id integer not null - constraint dlrn_user_rel_fk references membership_rels(rel_id) - constraint dlrn_user_rel_pk primary key, - type_id integer not null - constraint dlrn_user_rel_type_fk references dotlrn_user_types(type_id) +create table dotlrn_full_user_profile_rels ( + rel_id constraint dotlrn_fup_rels_rel_fk + references dotlrn_user_profile_rels (rel_id) + constraint dotlrn_full_user_prfl_rels_pk + primary key, + portal_id constraint dotlrn_fup_rels_portal_fk + references portals (portal_id) + constraint dotlrn_fup_rels_portal_nn + not null, + theme_id constraint dotlrn_fup_rels_theme_fk + references portal_element_themes (theme_id) ); -create table dotlrn_full_user_rels ( - rel_id integer not null - constraint dlrn_full_user_rel_fk references dotlrn_user_rels(rel_id) - constraint dlrn_full_user_rel_pk primary key, - theme_id integer - constraint dlrn_full_user_theme_fk references portal_element_themes(theme_id), - portal_id integer not null - constraint dlrn_full_user_portal_fk references portals(portal_id) +create table dotlrn_user_types ( + type varchar2(100) + constraint dotlrn_user_types_pk + primary key, + pretty_name varchar2(200), + group_id constraint dotlrn_user_types_group_id_fk + references profiled_groups (group_id) + constraint dotlrn_user_types_group_id_nn + not null ); --- the user group -declare - foo integer; -begin - foo:= acs_group.new ( - group_id => null, - object_type => 'group', - creation_date => sysdate, - group_name => 'dotLRN Users' - ); -end; -/ -show errors - -create or replace function dotlrn_get_group_id return integer is - p_group_id integer; -begin - select max(group_id) into p_group_id - from groups - where group_name = 'dotLRN Users'; - - return p_group_id; -end; -/ -show errors - -create view dotlrn_users +create or replace view dotlrn_users as select acs_rels.rel_id, + dotlrn_user_profile_rels.id, registered_users.user_id, - first_names, - last_name, - email, - dotlrn_user_types.type_id, - type - from dotlrn_user_rels, + 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_get_group_id() - and acs_rels.rel_id = dotlrn_user_rels.rel_id - and dotlrn_user_rels.type_id = dotlrn_user_types.type_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 +create or replace view dotlrn_full_users as - select acs_rels.rel_id, - registered_users.user_id, - first_names, - last_name, - email, - dotlrn_user_types.type_id, - type, - portal_id, - theme_id - from dotlrn_user_rels, - dotlrn_full_user_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_get_group_id() - and acs_rels.rel_id = dotlrn_user_rels.rel_id - and dotlrn_user_rels.rel_id = dotlrn_full_user_rels.rel_id - and dotlrn_user_types.type_id = dotlrn_user_rels.type_id; + 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; --- The packages +@@ dotlrn-user-profile-provider-create.sql +@@ dotlrn-users-init.sql +@@ dotlrn-users-package-create.sql -declare - v_group_id integer; - foo integer; -begin - acs_rel_type.create_type ( - rel_type => 'dotlrn_user_rel', - supertype => 'membership_rel', - pretty_name => 'dotLRN User Membership', - pretty_plural => 'dotLRN User Memberships', - package_name => 'dotlrn_user_rel', - table_name => 'dotlrn_user_rels', - id_column => 'rel_id', - object_type_one => 'group', role_one => null, min_n_rels_one => 0, max_n_rels_one => null, - object_type_two => 'user', role_two => null, min_n_rels_two => 0, max_n_rels_two => 1 - ); +-- create administrators +@@ dotlrn-admins-create.sql - acs_rel_type.create_type ( - rel_type => 'dotlrn_full_user_rel', - supertype => 'dotlrn_user_rel', - pretty_name => 'dotLRN Full User Membership', - pretty_plural => 'dotLRN Full User Memberships', - package_name => 'dotlrn_full_user_rel', - table_name => 'dotlrn_full_user_rels', - id_column => 'rel_id', - object_type_one => 'group', role_one => null, min_n_rels_one => 0, max_n_rels_one => null, - object_type_two => 'user', role_two => null, min_n_rels_two => 0, max_n_rels_two => 1 - ); +-- create professors +@@ dotlrn-professors-create.sql - v_group_id:= dotlrn_get_group_id(); - - -- Now we create the rel segments! - foo:= rel_segment.new ( - segment_name => 'dotLRN Users', - group_id => v_group_id, - rel_type => 'dotlrn_user_rel' - ); - - foo:= rel_segment.new ( - segment_name => 'dotLRN Full Access Users', - group_id => v_group_id, - rel_type => 'dotlrn_full_user_rel' - ); -end; -/ -show errors +-- create students +@@ dotlrn-students-create.sql