Index: openacs-4/packages/dotlrn/sql/oracle/classes-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/classes-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/dotlrn/sql/oracle/classes-create.sql 6 May 2002 23:55:01 -0000 1.1 +++ openacs-4/packages/dotlrn/sql/oracle/classes-create.sql 13 May 2002 19:46:18 -0000 1.2 @@ -98,7 +98,7 @@ create table dotlrn_class_instances ( class_instance_id constraint dotlrn_ci_class_instance_id_fk - references dotlrn_communities (community_id) + references dotlrn_communities_all (community_id) constraint dotlrn_class_instances_pk primary key, class_key constraint dotlrn_ci_class_key_fk @@ -313,12 +313,12 @@ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE default null, class_key in dotlrn_class_instances.class_key%TYPE, term_id in dotlrn_class_instances.term_id%TYPE, - community_key in dotlrn_communities.community_key%TYPE, - pretty_name in dotlrn_communities.pretty_name%TYPE, - description in dotlrn_communities.description%TYPE, - package_id in dotlrn_communities.package_id%TYPE default null, - portal_id in dotlrn_communities.portal_id%TYPE default null, - non_member_portal_id in dotlrn_communities.non_member_portal_id%TYPE default null, + community_key in dotlrn_communities_all.community_key%TYPE, + pretty_name in dotlrn_communities_all.pretty_name%TYPE, + description in dotlrn_communities_all.description%TYPE, + package_id in dotlrn_communities_all.package_id%TYPE default null, + portal_id in dotlrn_communities_all.portal_id%TYPE default null, + non_member_portal_id in dotlrn_communities_all.non_member_portal_id%TYPE default null, join_policy in groups.join_policy%TYPE default null, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, @@ -339,12 +339,12 @@ class_instance_id in dotlrn_class_instances.class_instance_id%TYPE default null, class_key in dotlrn_class_instances.class_key%TYPE, term_id in dotlrn_class_instances.term_id%TYPE, - community_key in dotlrn_communities.community_key%TYPE, - pretty_name in dotlrn_communities.pretty_name%TYPE, - description in dotlrn_communities.description%TYPE, - package_id in dotlrn_communities.package_id%TYPE default null, - portal_id in dotlrn_communities.portal_id%TYPE default null, - non_member_portal_id in dotlrn_communities.non_member_portal_id%TYPE default null, + community_key in dotlrn_communities_all.community_key%TYPE, + pretty_name in dotlrn_communities_all.pretty_name%TYPE, + description in dotlrn_communities_all.description%TYPE, + package_id in dotlrn_communities_all.package_id%TYPE default null, + portal_id in dotlrn_communities_all.portal_id%TYPE default null, + non_member_portal_id in dotlrn_communities_all.non_member_portal_id%TYPE default null, join_policy in groups.join_policy%TYPE default null, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, Index: openacs-4/packages/dotlrn/sql/oracle/clubs-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/clubs-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/dotlrn/sql/oracle/clubs-create.sql 6 May 2002 23:55:01 -0000 1.1 +++ openacs-4/packages/dotlrn/sql/oracle/clubs-create.sql 13 May 2002 19:46:18 -0000 1.2 @@ -30,7 +30,7 @@ create table dotlrn_clubs ( club_id constraint dotlrn_clubs_club_id_fk - references dotlrn_communities (community_id) + references dotlrn_communities_all (community_id) constraint dotlrn_clubs_pk primary key ); @@ -59,12 +59,12 @@ is function new ( club_id in dotlrn_clubs.club_id%TYPE default null, - community_key in dotlrn_communities.community_key%TYPE, - pretty_name in dotlrn_communities.pretty_name%TYPE, - description in dotlrn_communities.description%TYPE, - package_id in dotlrn_communities.package_id%TYPE default null, - portal_id in dotlrn_communities.portal_id%TYPE default null, - non_member_portal_id in dotlrn_communities.non_member_portal_id%TYPE default null, + community_key in dotlrn_communities_all.community_key%TYPE, + pretty_name in dotlrn_communities_all.pretty_name%TYPE, + description in dotlrn_communities_all.description%TYPE, + package_id in dotlrn_communities_all.package_id%TYPE default null, + portal_id in dotlrn_communities_all.portal_id%TYPE default null, + non_member_portal_id in dotlrn_communities_all.non_member_portal_id%TYPE default null, join_policy in groups.join_policy%TYPE default null, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, @@ -83,12 +83,12 @@ is function new ( club_id in dotlrn_clubs.club_id%TYPE default null, - community_key in dotlrn_communities.community_key%TYPE, - pretty_name in dotlrn_communities.pretty_name%TYPE, - description in dotlrn_communities.description%TYPE, - package_id in dotlrn_communities.package_id%TYPE default null, - portal_id in dotlrn_communities.portal_id%TYPE default null, - non_member_portal_id in dotlrn_communities.non_member_portal_id%TYPE default null, + community_key in dotlrn_communities_all.community_key%TYPE, + pretty_name in dotlrn_communities_all.pretty_name%TYPE, + description in dotlrn_communities_all.description%TYPE, + package_id in dotlrn_communities_all.package_id%TYPE default null, + portal_id in dotlrn_communities_all.portal_id%TYPE default null, + non_member_portal_id in dotlrn_communities_all.non_member_portal_id%TYPE default null, join_policy in groups.join_policy%TYPE default null, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, Index: openacs-4/packages/dotlrn/sql/oracle/communities-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/communities-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/dotlrn/sql/oracle/communities-create.sql 6 May 2002 23:55:01 -0000 1.1 +++ openacs-4/packages/dotlrn/sql/oracle/communities-create.sql 13 May 2002 19:46:18 -0000 1.2 @@ -43,14 +43,15 @@ references portals (portal_id) ); -create table dotlrn_communities ( +create table dotlrn_communities_all ( community_id constraint dotlrn_c_community_id_fk references groups (group_id) - constraint dotlrn_communities_pk + constraint dotlrn_communities_all_pk primary key, parent_community_id constraint dotlrn_c_parent_comm_id_fk - references dotlrn_communities (community_id), - community_type not null + references dotlrn_communities_all (community_id), + community_type constraint dotlrn_c_community_type_nn + not null constraint dotlrn_c_community_type_fk references dotlrn_community_types (community_type), community_key varchar2(100) @@ -62,6 +63,12 @@ description varchar2(4000), active_start_date date, active_end_date date, + archived_p char(1) + default 'f' + constraint dotlrn_c_archived_p_ck + check (archived_p in ('t', 'f')) + constraint dotlrn_c_archived_p_nn + not null, portal_id constraint dotlrn_c_portal_id_fk references portals (portal_id), non_member_portal_id constraint dotlrn_c_non_member_portal_fk @@ -70,12 +77,16 @@ references portals (portal_id), package_id 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 or replace view dotlrn_communities +as + select dotlrn_communities_all.* + from dotlrn_communities_all + where dotlrn_communities_all.archived_p = 'f'; + create or replace view dotlrn_communities_not_closed as select dotlrn_communities.*, @@ -87,10 +98,10 @@ create or replace view dotlrn_active_communities as - select * + select dotlrn_communities.* from dotlrn_communities - where (active_start_date is null or active_start_date < sysdate) - and (active_end_date is null or active_end_date > sysdate); + where (dotlrn_communities.active_start_date is null or dotlrn_communities.active_start_date < sysdate) + and (dotlrn_communities.active_end_date is null or dotlrn_communities.active_end_date > sysdate); create or replace view dotlrn_active_comms_not_closed as @@ -103,40 +114,39 @@ 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 + constraint dotlrn_applets_applet_key_un unique, status char(10) default 'active' + constraint dotlrn_applets_status_ck + check (status in ('active', 'inactive')) 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 + community_id constraint dotlrn_ca_community_id_fk + references dotlrn_communities_all (community_id) constraint dotlrn_ca_community_id_nn - not null - constraint dotlrn_ca_community_id_fk - references dotlrn_communities (community_id), - applet_id integer + not null, + applet_id constraint dotlrn_ca_applet_key_fk + references dotlrn_applets (applet_id) constraint dotlrn_ca_applet_key_nn - not null - references dotlrn_applets (applet_id), - constraint dotlrn_community_applets_pk primary key (community_id, applet_id), + not null, -- this is the package_id of the package this applet represents - package_id integer, + package_id constraint dotlrn_ca_package_id_fk + references apm_packages (package_id), 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')) + constraint dotlrn_ca_active_p_nn + not null, + constraint dotlrn_community_applets_pk + primary key (community_id, applet_id) ); Index: openacs-4/packages/dotlrn/sql/oracle/communities-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/communities-package-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/dotlrn/sql/oracle/communities-package-create.sql 6 May 2002 23:55:01 -0000 1.1 +++ openacs-4/packages/dotlrn/sql/oracle/communities-package-create.sql 13 May 2002 19:46:18 -0000 1.2 @@ -133,53 +133,55 @@ create or replace package dotlrn_community is + function new ( - community_id in dotlrn_communities.community_id%TYPE default null, - parent_community_id in dotlrn_communities.parent_community_id%TYPE default null, - community_type in dotlrn_communities.community_type%TYPE, - community_key in dotlrn_communities.community_key%TYPE, - pretty_name in dotlrn_communities.pretty_name%TYPE, - description in dotlrn_communities.description%TYPE, - portal_id in dotlrn_communities.portal_id%TYPE default null, - non_member_portal_id in dotlrn_communities.non_member_portal_id%TYPE default null, - package_id in dotlrn_communities.package_id%TYPE default null, + community_id in dotlrn_communities_all.community_id%TYPE default null, + parent_community_id in dotlrn_communities_all.parent_community_id%TYPE default null, + community_type in dotlrn_communities_all.community_type%TYPE, + community_key in dotlrn_communities_all.community_key%TYPE, + pretty_name in dotlrn_communities_all.pretty_name%TYPE, + description in dotlrn_communities_all.description%TYPE, + archived_p in dotlrn_communities_all.archived_p%TYPE default 'f', + portal_id in dotlrn_communities_all.portal_id%TYPE default null, + non_member_portal_id in dotlrn_communities_all.non_member_portal_id%TYPE default null, + package_id in dotlrn_communities_all.package_id%TYPE default null, join_policy in groups.join_policy%TYPE default null, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null - ) return dotlrn_communities.community_id%TYPE; + ) return dotlrn_communities_all.community_id%TYPE; procedure set_active_dates ( - community_id in dotlrn_communities.community_id%TYPE, - start_date in dotlrn_communities.active_start_date%TYPE, - end_date in dotlrn_communities.active_end_date%TYPE + community_id in dotlrn_communities_all.community_id%TYPE, + start_date in dotlrn_communities_all.active_start_date%TYPE, + end_date in dotlrn_communities_all.active_end_date%TYPE ); procedure delete ( - community_id in dotlrn_communities.community_id%TYPE + community_id in dotlrn_communities_all.community_id%TYPE ); function name ( - community_id in dotlrn_communities.community_id%TYPE + community_id in dotlrn_communities_all.community_id%TYPE ) return varchar; function member_p ( - community_id in dotlrn_communities.community_id%TYPE, + community_id in dotlrn_communities_all.community_id%TYPE, party_id in parties.party_id%TYPE ) return char; function admin_p ( - community_id in dotlrn_communities.community_id%TYPE, + community_id in dotlrn_communities_all.community_id%TYPE, party_id in parties.party_id%TYPE ) return char; function url ( - community_id in dotlrn_communities.community_id%TYPE + community_id in dotlrn_communities_all.community_id%TYPE ) return varchar2; function has_subcomm_p ( - community_id in dotlrn_communities.community_id%TYPE + community_id in dotlrn_communities_all.community_id%TYPE ) return char; end dotlrn_community; @@ -188,22 +190,24 @@ create or replace package body dotlrn_community as + function new ( - community_id in dotlrn_communities.community_id%TYPE default null, - parent_community_id in dotlrn_communities.parent_community_id%TYPE default null, - community_type in dotlrn_communities.community_type%TYPE, - community_key in dotlrn_communities.community_key%TYPE, - pretty_name in dotlrn_communities.pretty_name%TYPE, - description in dotlrn_communities.description%TYPE, - portal_id in dotlrn_communities.portal_id%TYPE default null, - non_member_portal_id in dotlrn_communities.non_member_portal_id%TYPE default null, - package_id in dotlrn_communities.package_id%TYPE default null, + community_id in dotlrn_communities_all.community_id%TYPE default null, + parent_community_id in dotlrn_communities_all.parent_community_id%TYPE default null, + community_type in dotlrn_communities_all.community_type%TYPE, + community_key in dotlrn_communities_all.community_key%TYPE, + pretty_name in dotlrn_communities_all.pretty_name%TYPE, + description in dotlrn_communities_all.description%TYPE, + archived_p in dotlrn_communities_all.archived_p%TYPE default 'f', + portal_id in dotlrn_communities_all.portal_id%TYPE default null, + non_member_portal_id in dotlrn_communities_all.non_member_portal_id%TYPE default null, + package_id in dotlrn_communities_all.package_id%TYPE default null, join_policy in groups.join_policy%TYPE default null, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null - ) return dotlrn_communities.community_id%TYPE + ) return dotlrn_communities_all.community_id%TYPE is c_id integer; begin @@ -218,14 +222,15 @@ join_policy => dotlrn_community.new.join_policy ); - insert into dotlrn_communities + insert into dotlrn_communities_all (community_id, parent_community_id, community_type, community_key, pretty_name, description, package_id, + archived_p, portal_id, non_member_portal_id) values @@ -236,47 +241,48 @@ dotlrn_community.new.pretty_name, dotlrn_community.new.description, dotlrn_community.new.package_id, + dotlrn_community.new.archived_p, dotlrn_community.new.portal_id, dotlrn_community.new.non_member_portal_id); return c_id; end; procedure set_active_dates ( - community_id in dotlrn_communities.community_id%TYPE, - start_date in dotlrn_communities.active_start_date%TYPE, - end_date in dotlrn_communities.active_end_date%TYPE + community_id in dotlrn_communities_all.community_id%TYPE, + start_date in dotlrn_communities_all.active_start_date%TYPE, + end_date in dotlrn_communities_all.active_end_date%TYPE ) is begin - update dotlrn_communities + update dotlrn_communities_all set active_start_date = dotlrn_community.set_active_dates.start_date, active_end_date = dotlrn_community.set_active_dates.end_date - where dotlrn_communities.community_id = dotlrn_community.set_active_dates.community_id; + where dotlrn_communities_all.community_id = dotlrn_community.set_active_dates.community_id; end; procedure delete ( - community_id in dotlrn_communities.community_id%TYPE + community_id in dotlrn_communities_all.community_id%TYPE ) is begin delete - from dotlrn_communities - where dotlrn_communities.community_id = dotlrn_community.delete.community_id; + from dotlrn_communities_all + where dotlrn_communities_all.community_id = dotlrn_community.delete.community_id; acs_group.delete(dotlrn_community.delete.community_id); end; function name ( - community_id in dotlrn_communities.community_id%TYPE + community_id in dotlrn_communities_all.community_id%TYPE ) return varchar is begin return acs_group.name(dotlrn_community.name.community_id); end; function member_p ( - community_id in dotlrn_communities.community_id%TYPE, + community_id in dotlrn_communities_all.community_id%TYPE, party_id in parties.party_id%TYPE ) return char is @@ -294,7 +300,7 @@ end; function admin_p ( - community_id in dotlrn_communities.community_id%TYPE, + community_id in dotlrn_communities_all.community_id%TYPE, party_id in parties.party_id%TYPE ) return char is @@ -312,16 +318,16 @@ end; function url ( - community_id in dotlrn_communities.community_id%TYPE + community_id in dotlrn_communities_all.community_id%TYPE ) return varchar2 is v_node_id site_nodes.node_id%TYPE; begin select site_nodes.node_id into v_node_id - from dotlrn_communities, + from dotlrn_communities_all, site_nodes - where dotlrn_communities.community_id = dotlrn_community.url.community_id - and site_nodes.object_id = dotlrn_communities.package_id; + where dotlrn_communities_all.community_id = dotlrn_community.url.community_id + and site_nodes.object_id = dotlrn_communities_all.package_id; return site_node.url(v_node_id); @@ -331,7 +337,7 @@ end; function has_subcomm_p ( - community_id in dotlrn_communities.community_id%TYPE + community_id in dotlrn_communities_all.community_id%TYPE ) return char is v_rv char(1); @@ -340,8 +346,8 @@ into v_rv from dual where exists (select 1 - from dotlrn_communities - where dotlrn_communities.parent_community_id = dotlrn_community.has_subcomm_p.community_id); + from dotlrn_communities_all + where dotlrn_communities_all.parent_community_id = dotlrn_community.has_subcomm_p.community_id); return v_rv; end; Index: openacs-4/packages/dotlrn/sql/oracle/community-memberships-packages-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/community-memberships-packages-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/dotlrn/sql/oracle/community-memberships-packages-create.sql 6 May 2002 23:55:01 -0000 1.1 +++ openacs-4/packages/dotlrn/sql/oracle/community-memberships-packages-create.sql 13 May 2002 19:46:18 -0000 1.2 @@ -34,7 +34,7 @@ function new ( rel_id in dotlrn_member_rels.rel_id%TYPE default null, rel_type in acs_rels.rel_type%TYPE default 'dotlrn_member_rel', - community_id in dotlrn_communities.community_id%TYPE, + community_id in dotlrn_communities_all.community_id%TYPE, user_id in users.user_id%TYPE, member_state in membership_rels.member_state%TYPE default 'approved', creation_user in acs_objects.creation_user%TYPE default null, @@ -55,7 +55,7 @@ function new ( rel_id in dotlrn_member_rels.rel_id%TYPE default null, rel_type in acs_rels.rel_type%TYPE default 'dotlrn_member_rel', - community_id in dotlrn_communities.community_id%TYPE, + community_id in dotlrn_communities_all.community_id%TYPE, user_id in users.user_id%TYPE, member_state in membership_rels.member_state%TYPE default 'approved', creation_user in acs_objects.creation_user%TYPE default null, @@ -105,7 +105,7 @@ function new ( rel_id in dotlrn_admin_rels.rel_id%TYPE default null, rel_type in acs_rels.rel_type%TYPE default 'dotlrn_admin_rel', - community_id in dotlrn_communities.community_id%TYPE, + community_id in dotlrn_communities_all.community_id%TYPE, user_id in users.user_id%TYPE, member_state in membership_rels.member_state%TYPE default 'approved', creation_user in acs_objects.creation_user%TYPE default null, @@ -126,7 +126,7 @@ function new ( rel_id in dotlrn_admin_rels.rel_id%TYPE default null, rel_type in acs_rels.rel_type%TYPE default 'dotlrn_admin_rel', - community_id in dotlrn_communities.community_id%TYPE, + community_id in dotlrn_communities_all.community_id%TYPE, user_id in users.user_id%TYPE, member_state in membership_rels.member_state%TYPE default 'approved', creation_user in acs_objects.creation_user%TYPE default null,