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.4 -r1.5 --- openacs-4/packages/dotlrn/sql/oracle/dotlrn-community-memberships-create.sql 1 Dec 2001 23:16:27 -0000 1.4 +++ openacs-4/packages/dotlrn/sql/oracle/dotlrn-community-memberships-create.sql 17 Dec 2001 23:02:55 -0000 1.5 @@ -12,50 +12,70 @@ create table dotlrn_member_rels ( - rel_id integer not null - constraint dlrn_mem_fk references membership_rels(rel_id) - constraint dlrn_mem_pk primary key, - portal_id integer - constraint dlrn_mem_portal_id_fk references portals(portal_id) -); + rel_id integer not null + constraint dlrn_mem_fk references membership_rels(rel_id) + constraint dlrn_mem_pk primary key, + portal_id integer + constraint dlrn_mem_portal_id_fk references portals(portal_id) +); -create 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 from dotlrn_member_rels, acs_rels where dotlrn_member_rels.rel_id = acs_rels.rel_id; +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 + from dotlrn_member_rels, + acs_rels + where dotlrn_member_rels.rel_id = acs_rels.rel_id; create table dotlrn_admin_rels ( - rel_id integer not null - constraint dlrn_adm_fk references dotlrn_member_rels(rel_id) - constraint dlrn_adm_pk primary key + rel_id integer not null + constraint dlrn_adm_fk references dotlrn_member_rels(rel_id) + constraint dlrn_adm_pk primary key ); -create view dotlrn_admin_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 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; +create or replace view dotlrn_admin_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 + 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; -- -- For Classes -- create table dotlrn_student_rels ( - rel_id integer not null - constraint dlrn_stud_fk references dotlrn_member_rels(rel_id) - constraint dlrn_stud_pk primary key + rel_id integer not null + constraint dlrn_stud_fk references dotlrn_member_rels(rel_id) + constraint dlrn_stud_pk primary key ); create view dotlrn_student_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 from dotlrn_student_rels, acs_rels where dotlrn_student_rels.rel_id = acs_rels.rel_id; create table dotlrn_ta_rels ( - rel_id integer not null - constraint dlrn_ta_fk references dotlrn_admin_rels(rel_id) - constraint dlrn_ta_pk primary key + rel_id integer not null + constraint dlrn_ta_fk references dotlrn_admin_rels(rel_id) + constraint dlrn_ta_pk primary key ); create view dotlrn_ta_rels_full as select acs_rels.rel_id as rel_id, object_id_two as community_id, object_id_two as user_id, rel_type from dotlrn_ta_rels, acs_rels where dotlrn_ta_rels.rel_id = acs_rels.rel_id; create table dotlrn_instructor_rels ( - rel_id integer not null - constraint dlrn_instruct_fk references dotlrn_admin_rels(rel_id) - constraint dlrn_instruct_pk primary key + rel_id integer not null + constraint dlrn_instruct_fk references dotlrn_admin_rels(rel_id) + constraint dlrn_instruct_pk primary key ); create view dotlrn_instructor_rels_full as select acs_rels.rel_id as rel_id, object_id_two as community_id, object_id_two as user_id, rel_type from dotlrn_instructor_rels, acs_rels where dotlrn_instructor_rels.rel_id = acs_rels.rel_id; @@ -66,88 +86,88 @@ -- declare - foo integer; + foo integer; begin - acs_rel_type.create_type ( - rel_type => 'dotlrn_member_rel', - supertype => 'membership_rel', - pretty_name => 'dotLRN Community Membership', - pretty_plural => 'dotLRN Community Memberships', - package_name => 'dotlrn_member_rel', - table_name => 'dotlrn_member_rels', - id_column => 'rel_id', - object_type_one => 'dotlrn_community', 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 => NULL - ); + acs_rel_type.create_type ( + rel_type => 'dotlrn_member_rel', + supertype => 'membership_rel', + pretty_name => 'dotLRN Community Membership', + pretty_plural => 'dotLRN Community Memberships', + package_name => 'dotlrn_member_rel', + table_name => 'dotlrn_member_rels', + id_column => 'rel_id', + object_type_one => 'dotlrn_community', 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 => NULL + ); - acs_rel_type.create_type ( - rel_type => 'dotlrn_admin_rel', - supertype => 'dotlrn_member_rel', - pretty_name => 'dotLRN Admin Community Membership', - pretty_plural => 'dotLRN Admin Community Memberships', - package_name => 'dotlrn_admin_rel', - table_name => 'dotlrn_admin_rels', - id_column => 'rel_id', - object_type_one => 'dotlrn_community', 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 => NULL - ); + acs_rel_type.create_type ( + rel_type => 'dotlrn_admin_rel', + supertype => 'dotlrn_member_rel', + pretty_name => 'dotLRN Admin Community Membership', + pretty_plural => 'dotLRN Admin Community Memberships', + package_name => 'dotlrn_admin_rel', + table_name => 'dotlrn_admin_rels', + id_column => 'rel_id', + object_type_one => 'dotlrn_community', 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 => NULL + ); - acs_rel_type.create_type ( - rel_type => 'dotlrn_student_rel', - supertype => 'dotlrn_member_rel', - pretty_name => 'dotLRN Student Community Membership', - pretty_plural => 'dotLRN Student Community Memberships', - package_name => 'dotlrn_student_rel', - table_name => 'dotlrn_student_rels', - id_column => 'rel_id', - object_type_one => 'dotlrn_class_instance', 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 => NULL - ); + acs_rel_type.create_type ( + rel_type => 'dotlrn_student_rel', + supertype => 'dotlrn_member_rel', + pretty_name => 'dotLRN Student Community Membership', + pretty_plural => 'dotLRN Student Community Memberships', + package_name => 'dotlrn_student_rel', + table_name => 'dotlrn_student_rels', + id_column => 'rel_id', + object_type_one => 'dotlrn_class_instance', 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 => NULL + ); - acs_rel_type.create_type ( - rel_type => 'dotlrn_ta_rel', - supertype => 'dotlrn_admin_rel', - pretty_name => 'dotLRN TA Community Membership', - pretty_plural => 'dotLRN TA Community Memberships', - package_name => 'dotlrn_ta_rel', - table_name => 'dotlrn_ta_rels', - id_column => 'rel_id', - object_type_one => 'dotlrn_class_instance', 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 => NULL - ); + acs_rel_type.create_type ( + rel_type => 'dotlrn_ta_rel', + supertype => 'dotlrn_admin_rel', + pretty_name => 'dotLRN TA Community Membership', + pretty_plural => 'dotLRN TA Community Memberships', + package_name => 'dotlrn_ta_rel', + table_name => 'dotlrn_ta_rels', + id_column => 'rel_id', + object_type_one => 'dotlrn_class_instance', 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 => NULL + ); - acs_rel_type.create_type ( - rel_type => 'dotlrn_instructor_rel', - supertype => 'dotlrn_admin_rel', - pretty_name => 'dotLRN Instructor Community Membership', - pretty_plural => 'dotLRN Instructor Community Memberships', - package_name => 'dotlrn_instructor_rel', - table_name => 'dotlrn_instructor_rels', - id_column => 'rel_id', - object_type_one => 'dotlrn_class_instance', 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 => NULL - ); + acs_rel_type.create_type ( + rel_type => 'dotlrn_instructor_rel', + supertype => 'dotlrn_admin_rel', + pretty_name => 'dotLRN Instructor Community Membership', + pretty_plural => 'dotLRN Instructor Community Memberships', + package_name => 'dotlrn_instructor_rel', + table_name => 'dotlrn_instructor_rels', + id_column => 'rel_id', + object_type_one => 'dotlrn_class_instance', 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 => NULL + ); - -- - -- and now for the attributes - -- - foo:= acs_attribute.create_attribute ( - object_type => 'dotlrn_member_rel', - attribute_name => 'portal_id', - datatype => 'integer', - pretty_name => 'Page ID', - pretty_plural => 'Page IDs' - ); + -- + -- and now for the attributes + -- + foo:= acs_attribute.create_attribute ( + object_type => 'dotlrn_member_rel', + attribute_name => 'portal_id', + datatype => 'integer', + pretty_name => 'Page ID', + pretty_plural => 'Page IDs' + ); end; /