Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql,v
diff -u -r1.1.2.1 -r1.1.2.2
--- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql	21 Apr 2003 01:02:34 -0000	1.1.2.1
+++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql	3 May 2003 23:52:33 -0000	1.1.2.2
@@ -1,5 +1,6 @@
-drop view user_tab_comments;
+-- PG 7.2 doesn't have create or replace view (7.3 does but we don't require it yet)
 
+drop view user_tab_comments;
 create view user_tab_comments as
   select upper(c.relname) as table_name,
     case
@@ -11,3 +12,50 @@
   from pg_class c left outer join pg_description d on (c.oid = d.objoid)
   where d.objsubid = 0;
 
+
+-- ******************************************************************
+-- * Community Core API
+-- ******************************************************************
+
+drop view registered_users;
+create view registered_users
+as
+  select p.email, p.url, pe.first_names, pe.last_name, u.*, mr.member_state
+  from parties p, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo
+  where party_id = person_id
+  and person_id = user_id
+  and u.user_id = m.member_id
+  and m.rel_id = mr.rel_id
+  and amo.name = 'registered_users'
+  and m.group_id = amo.object_id
+  and mr.member_state = 'approved'
+  and u.email_verified_p = 't';
+
+drop view cc_users;
+create view cc_users
+as
+select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id
+from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo
+where o.object_id = pa.party_id
+  and pa.party_id = pe.person_id
+  and pe.person_id = u.user_id
+  and u.user_id = m.member_id
+  and amo.name = 'registered_users'
+  and m.group_id = amo.object_id
+  and m.rel_id = mr.rel_id
+  and m.container_id = m.group_id;
+
+create function acs_priv_del_tr () returns opaque as '
+begin
+
+  delete from acs_privilege_descendant_map
+  where privilege = old.privilege;
+
+  return old;
+
+end;' language 'plpgsql';
+
+create trigger acs_priv_del_tr before delete
+on acs_privileges for each row
+execute procedure acs_priv_del_tr ();
+