In implementing this Ive discovered it would be far easier with a data model change.
If you look at the current dotlrn_communities it is a view where archive_p = 'f'.
I would like to change that to its own view dotlrn_communities_not_arch and make dotlrn_communities equivalent to dotlrn_communities_all.
When dotLRN was originally written there was not a clear understanding of what archiving and deleteing and not active should mean for a class. Someone made the decision that archived communities should not generally be shown. Now that we have fleshed out our requirements for archiving it turns out that it should not be so strict.
Looking at the code, it looks to me like dotLRN_communities is the generally used table, and that in most cases when it was used a conscious decision to exclude archived communities was not made.
I think it would be better in the long run for me to go through and determine where archived communities should not be shown and explicitly change those queries to use dotLRN_communities_not_arch.
TAB, what do you think? What do other people think?
Here are the new views from communities-create.sql
create or replace view dotlrn_communities
as
select dotlrn_communities_all.*
from dotlrn_communities_all;
create or replace view dotlrn_communities_not_arch
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.*,
groups.join_policy
from dotlrn_communities_not_arch dotlrn_communities,
groups
where dotlrn_communities.community_id = groups.group_id
and groups.join_policy <> 'closed';
create or replace view dotlrn_active_communities
as
select dotlrn_communities.*
from dotlrn_communities_not_arch dotlrn_communities
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
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';
