Index: openacs-4/packages/address-book/sql/postgresql/address-book-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/address-book/sql/postgresql/address-book-create.sql,v
diff -u -N -r1.1 -r1.2
--- openacs-4/packages/address-book/sql/postgresql/address-book-create.sql	22 Aug 2001 22:55:22 -0000	1.1
+++ openacs-4/packages/address-book/sql/postgresql/address-book-create.sql	28 Aug 2001 23:51:53 -0000	1.2
@@ -1,6 +1,7 @@
 --
 -- packages/address-book/sql/address-book-create.sql
--- @author ported to pg by Rafael Calvo (rafa@sedal.usyd.edu.au)
+-- @author ported to pg by Rafael Calvo (rafa@sedal.usyd.edu.au) and 
+--         Don Baccus (dhogaza@pacifier.com)
 -- @author jmileham@arsdigita.com
 -- @creation_date 2000-12-04
 -- @cvs-id $Id$
@@ -435,8 +436,8 @@
        integer      -- ab_contact_attrs.attr_id%TYPE
 ) returns integer as '
 declare
-	attr_id_one    alias for $1;         
-	attr_id_two    alias for $2;
+	p_attr_id_one    alias for $1;         
+	p_attr_id_two    alias for $2;
 begin
   -- The exists clause verifies that the application is trying
   -- to perform a legal swap (one between two attributes of
@@ -445,13 +446,13 @@
   update ab_contact_attrs aca1
      set sort_key = (select sort_key
                        from ab_contact_attrs aca2
-                      where aca1.attr_id = decode(aca2.attr_id,
-                                           swap_sort.attr_id_one, swap_sort.attr_id_two,
-                                           swap_sort.attr_id_two, swap_sort.attr_id_one))
-   where attr_id in (swap_sort.attr_id_one, swap_sort.attr_id_two)
+                      where aca1.attr_id = case aca2.attr_id
+                                           when p_attr_id_one then p_attr_id_two
+                                           when p_attr_id_two then p_attr_id_one)
+   where attr_id in (p_attr_id_one, p_attr_id_two)
      and exists (select 1 from ab_contact_attrs aca3, ab_contact_attrs aca4
-                  where aca3.attr_id = swap_sort.attr_id_one
-                    and aca4.attr_id = swap_sort.attr_id_two
+                  where aca3.attr_id = p_attr_id_one
+                    and aca4.attr_id = p_attr_id_two
                     and aca3.contact_id = aca4.contact_id);
 
 end;' language 'plpgsql';
@@ -471,16 +472,11 @@
 -- --------------------
 -- -- AB_CONTACT_REL --
 -- --------------------
---  RC: acs_rel_type is defined in packages/acs-kernel/sql/postgresql/acs-relationships-create.sql
 
--- PERFORM  acs_rel_type__create_role(''contact''); 
---  -- Dont know what to call the object associated with the oontact,
---  -- so I wont define a role for it.  Should be ok, seeing as the
---  -- groups system does this too.
-
 create function inline_0 ()
 returns integer as '
 begin
+PERFORM  acs_rel_type__create_role(''contact''); 
 PERFORM  acs_rel_type__create_type (
    ''ab_contact_rel'',			--   rel_type
    ''Address Book Contact Relation'',	--   pretty_name
@@ -681,23 +677,32 @@
 
 end;' language 'plpgsql';
 
+create function pl_address__orphan_address(integer) returns integer as '
 
+declare
+  v_rel_id subplace_rels.rel_id%TYPE;
+begin
+  select sr.rel_id
+  from subplace_rels sr, acs_rels ar
+  where ar.rel_id = sr.rel_id and ar.object_id_two = :address_id;
+  if found then
+    subplace_rel__delete(v_rel_id);
+  pl_address__delete(:address_id);
+end;' language 'plpgsql';
+	
+
 -- function delete
 create function ab_contact__delete (
-	-- contact_id	
-	-- delete_orphan_addresses_p	in char default t
        integer,         -- ab_contacts.contact_id%TYPE
        boolean		-- char default t
 ) returns integer as '
 declare
 	contact_id			alias for $1;		
 	delete_orphan_addresses_p	alias for $2;
-	v_ab_contact_attrs_row ab_contact_attrs%ROWTYPE;
-	v_addresses_located_row pl_addresses_located%ROWTYPE;
+	v_ab_contact_attrs_row		ab_contact_attrs%ROWTYPE;
+	v_addresses_located_row		pl_addresses_located%ROWTYPE;
+        v_rel_id			ab_contact_rels.rel_id%TYPE;
 begin
-	-- RC: Code to find orphans temporarily removed: FIX
-	-- RC: set delete_orphan_address to false
-	delete_orphan_addresses_p = ''f'';
 
 -- First blow away attributes
    for v_ab_contact_attrs_row in (
@@ -708,114 +713,41 @@
 	ab_contact_attr__delete(v_ab_contact_attrs_row.attr_id);
    end loop;
 
-  -- Then iterate through address location relations
-  -- RC: Missing code that delets address relations-- FIX
-
-    -- If we are deleting orphans then
-    -- RC: Missing code here. This is buggy and should be completed.
-
-
+   for v_addresses_located_row in (select * from pl_addresses_located where locatee_id = ab_contact.delete.contact_id) loop
+     subplace_rel__delete(v_addresses_located_row.rel_id);
+     if delete_orphan_addresses_p and
+        not exists(select 1
+                   from place_element_map pem, pl_address pl
+                   where pem.place_id = pl.address_id and
+                   v_addresses_located_row.address_id = pl.address_id) 
+     then
+       select sr.rel_id into v_rel_id
+       from subplace_rels sr, acs_rels ar
+       where ar.rel_id = sr.rel_id and ar.object_id_two = v_addresses_located_row.address_id;
+       if found then
+         subplace_rel__delete(v_rel_id);
+       end if;
+     end if;
 return 0;
 end;' language 'plpgsql';
 
 
---create function ab_contact__delete (
---	-- contact_id	
---	-- delete_orphan_addresses_p	in char default
---       integer,         -- ab_contacts.contact_id%TYPE
---       boolean		-- char default 
---) returns integer as '
---declare
---	contact_id			alias for $1;		
---	delete_orphan_addresses_p	alias for $2;
---	v_ab_contact_attrs_row ab_contact_attrs%ROWTYPE;
---	v_addresses_located_row pl_addresses_located%ROWTYPE;
---begin
---	select count(*) = 0 
---             into v_address_is_orphan_p
---             from place_element_map
---            where place_id = v_addresses_located_row.address_id;
---
---	v_address_is_orphan_p boolean;
---
---        if v_address_is_orphan_p then
---	   select sr.rel_id
---           into   v_rel_id
---           from   subplace_rels sr, acs_rels ar
---           where  ar.rel_id = sr.rel_id
---           and    ar.object_id_two = v_addresses_located_row.address_id;
---          
---	  v_rel_id subplace_rels.rel_id%TYPE;
---
---
---  -- First blow away attributes
---   for v_ab_contact_attrs_row in (
---       select attr_id 
---       from   ab_contact_attrs 
---       where  contact_id = ab_contact.delete.contact_id) 
---   loop
---	ab_contact_attr__delete(v_ab_contact_attrs_row.attr_id);
---   end loop;
---
---  -- Then iterate through address location relations
---
---   for v_addresses_located_row in (
---       select * 
---       from pl_addresses_located 
---       where locatee_id = ab_contact.delete.contact_id) 
---   loop
---
---    -- Delete the rel
---     subplace_rel__delete(v_addresses_located_row.rel_id);
---
---    -- If we're deleting orphans then
---     if delete_orphan_addresses_p = 't' then
---
---    -- Check if the address is an orphan
---       open address_is_orphan_p_cursor(address_id => v_addresses_located_row.address_id);
---       fetch address_is_orphan_p_cursor into v_address_is_orphan_p;
---       close address_is_orphan_p_cursor;
---       if v_address_is_orphan_p = 't' then
---
---        -- Delete the address's subplace_relation
---         open subplace_rel_cursor(address_id => v_addresses_located_row.address_id);
---         fetch subplace_rel_cursor into v_rel_id;
---         if not subplace_rel_cursor%NOTFOUND then
---           PERFORM subplace_rel.delete(v_rel_id);
---         end if;
---         close subplace_rel_cursor;
---
---        -- Delete the address itself
---         PERFORM pl_address__delete(v_addresses_located_row.address_id);
---
---       end if;
---
---     end if;
---
---
---
---return 0;
---end;' language 'plpgsql';
-
-
-select * from users;
-
 -- function work_phone
 create function ab_contact__work_phone (
        --  contact_id
        integer  -- ab_contacts.contact_id%TYPE
-) return varchar as '
+) returns varchar as '
 declare
-        contact_id_p alias for $1;
+        p_contact_id alias for $1;
         v_value ab_contact_attrs.value%TYPE;  -- varchar(200)
 begin
    select value
      into v_value
      from ab_contact_attrs
-     where contact_id = contact_id_p
+     where contact_id = p_contact_id
      and sort_key = (select min(sort_key)
                        from ab_contact_attrs
-                      where contact_id = contact_id_p
+                      where contact_id = p_contact_id
                         and type_key = ''work_phone'');
   return v_value;
 end;' language 'plpgsql';
@@ -824,18 +756,18 @@
 create function ab_contact__home_phone (
        -- contact_id	
        integer		-- ab_contacts.contact_id%TYPE
-) return varchar as ' 
+) returns varchar as ' 
 declare
-	contact_id_p	alias for $1;
+	p_contact_id	alias for $1;
 	v_value ab_contact_attrs.value%TYPE;
 begin
 	select value
 	into v_value
 	from ab_contact_attrs
-	where contact_id = contact_id_p
+	where contact_id = p_contact_id
 	and sort_key = (select min(sort_key)
 	       from ab_contact_attrs
-	      where contact_id = contact_id_p
+	      where contact_id = p_contact_id
 		and type_key = ''home_phone'');
    return v_value;
 
@@ -845,18 +777,18 @@
 create function ab_contact__fax (
        -- contact_id	
        integer		-- ab_contacts.contact_id%TYPE
-) return varchar as '
+) returns varchar as '
 declare
-	contact_id_p alias for $1;
+	p_contact_id alias for $1;
 	v_value ab_contact_attrs.value%TYPE;
 begin
 	select value
 	    into v_value
 	    from ab_contact_attrs
-	   where contact_id = contact_id_p
+	   where contact_id = p_contact_id
 	     and sort_key = (select min(sort_key)
  		       from ab_contact_attrs
- 		      where contact_id = contact_id_p
+ 		      where contact_id = p_contact_id
  			and type_key = ''fax'');
 return v_value;
 
@@ -866,38 +798,38 @@
 create function ab_contact__other (
        -- contact_id	
        integer		-- ab_contacts.contact_id%TYPE
-) return varchar as '
+) returns varchar as '
 declare
-	contact_id_p alias for $1;
+	p_contact_id alias for $1;
 	v_value ab_contact_attrs.value%TYPE;
 begin
    select value
      into v_value
      from ab_contact_attrs
-    where contact_id = contact_id_p
+    where contact_id = p_contact_id
       and sort_key = (select min(sort_key)
  		       from ab_contact_attrs
- 		      where contact_id = contact_id_p
+ 		      where contact_id = p_contact_id
  			and type_key = ''other'');
    return v_value;
 end;' language 'plpgsql';
 
 -- function email
-create function ab_contact__fax (
+create function ab_contact__email (
        -- contact_id	
        integer		-- ab_contacts.contact_id%TYPE
-) return varchar as '
+) returns varchar as '
 declare
-	contact_id_p alias for $1;
+	p_contact_id alias for $1;
 	v_value ab_contact_attrs.value%TYPE;
 begin
    select value
      into v_value
      from ab_contact_attrs
-    where contact_id = contact_id_p
+    where contact_id = p_contact_id
       and sort_key = (select min(sort_key)
  		       from ab_contact_attrs
- 		      where contact_id = contact_id_p
+ 		      where contact_id = p_contact_id
  			and type_key = ''email'');
    return v_value;
 
@@ -909,11 +841,11 @@
 
 create view ab_contacts_complete as
    select ac.*,
-          ab_contact__work_phone(contact_id) work_phone,
-          ab_contact__home_phone(contact_id) home_phone,
-          ab_contact__fax(contact_id) fax,
-          ab_contact__other(contact_id) other,
- 	  ab_contact__email(contact_id) email
+          ab_contact__work_phone(contact_id) as work_phone,
+          ab_contact__home_phone(contact_id) as home_phone,
+          ab_contact__fax(contact_id) as fax,
+          ab_contact__other(contact_id) as other,
+ 	  ab_contact__email(contact_id) as email
      from ab_contacts ac;
 
 -- 
