Index: openacs-4/packages/dotlrn/sql/oracle/dotlrn-community-memberships-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/Attic/dotlrn-community-memberships-create.sql,v diff -u -r1.9 -r1.10 --- openacs-4/packages/dotlrn/sql/oracle/dotlrn-community-memberships-create.sql 15 Mar 2002 02:06:12 -0000 1.9 +++ openacs-4/packages/dotlrn/sql/oracle/dotlrn-community-memberships-create.sql 16 Mar 2002 02:02:07 -0000 1.10 @@ -24,14 +24,28 @@ create or replace view dotlrn_member_rels_full as select acs_rels.rel_id as rel_id, - object_id_one as community_id, - object_id_two as user_id, - rel_type, - portal_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 - where dotlrn_member_rels.rel_id = acs_rels.rel_id; + acs_rels, + membership_rels + where dotlrn_member_rels.rel_id = acs_rels.rel_id + and acs_rels.rel_id = membership_rels.rel_id; +create or replace view dotlrn_member_rels_approved +as + select * + from dotlrn_member_rels_full + where member_state = 'approved'; + create table dotlrn_admin_rels ( rel_id constraint dotlrn_admin_rels_rel_id_fk references dotlrn_member_rels (rel_id) @@ -41,16 +55,16 @@ create or replace view dotlrn_admin_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, - dotlrn_member_rels.portal_id - from dotlrn_member_rels, - dotlrn_admin_rels, - acs_rels - where dotlrn_member_rels.rel_id = acs_rels.rel_id - and dotlrn_admin_rels.rel_id = acs_rels.rel_id; + 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 @@ -65,13 +79,16 @@ create or replace view dotlrn_student_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 - from dotlrn_student_rels, - acs_rels - where dotlrn_student_rels.rel_id = acs_rels.rel_id; + 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 constraint dotlrn_ta_rels_rel_id_fk @@ -82,13 +99,16 @@ create or replace view dotlrn_ta_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 - from dotlrn_ta_rels, - acs_rels - where dotlrn_ta_rels.rel_id = acs_rels.rel_id; + 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 constraint dotlrn_ca_rels_rel_id_fk @@ -99,13 +119,16 @@ create or replace view dotlrn_ca_rels_full as - select acs_rels.rel_id, - acs_rels.object_id_one as community_id, - acs_rels.object_id_two as user_id, - acs_rels.rel_type - from dotlrn_ca_rels, - acs_rels - where dotlrn_ca_rels.rel_id = acs_rels.rel_id; + 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 constraint dotlrn_cadmin_rels_rel_id_fk @@ -116,13 +139,16 @@ create or replace view dotlrn_cadmin_rels_full as - select acs_rels.rel_id, - acs_rels.object_id_one as community_id, - acs_rels.object_id_two as user_id, - acs_rels.rel_type - from dotlrn_cadmin_rels, - acs_rels - where dotlrn_cadmin_rels.rel_id = acs_rels.rel_id; + 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 constraint dotlrn_instructor_rels_rel_fk @@ -131,15 +157,18 @@ primary key ); -create view dotlrn_instructor_rels_full +create or replace view dotlrn_instructor_rels_full as - select acs_rels.rel_id as rel_id, - acs_rels.object_id_two as community_id, - acs_rels.object_id_two as user_id, - acs_rels.rel_type - from dotlrn_instructor_rels, - acs_rels - where dotlrn_instructor_rels.rel_id = acs_rels.rel_id; + 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