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
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/address-book/sql/postgresql/address-book-create.sql 22 Aug 2001 22:55:22 -0000 1.1
@@ -0,0 +1,932 @@
+--
+-- packages/address-book/sql/address-book-create.sql
+-- @author ported to pg by Rafael Calvo (rafa@sedal.usyd.edu.au)
+-- @author jmileham@arsdigita.com
+-- @creation_date 2000-12-04
+-- @cvs-id $Id: address-book-create.sql,v 1.1 2001/08/22 22:55:22 rafaelc Exp $
+--
+
+---------------------------------------------------------------
+-- Data Model for address-book package
+-- create ab_contact object *
+-- create ab_contacts table *
+-- create ab_contact_attr_type object *
+-- create ab_contact_attr_type table *
+-- create or replace ab_contact_attr_type package. functions: new,name,delete*
+-- create or replace package body ab_contact_attr_type. functions: new,name,de*
+-- Definition of Attribute types: work phone, home phone,etc. *
+-- create ab_contact_attr object *
+-- create ab_contact_attrs table *
+-- create or replace package ab_contact_attr. Functions:new,swap_sort,delete.
+-- create or replace package body ab_contact_attr
+-- create ab_contact_rel role
+-- create table ab_contact_rels
+-- create or replace package ab_contact_rel
+-- create or replace package body ab_contact_rel
+-- create or replace package ab_contact. Funcs: name,delete,work_phone,etc.
+-- create or replace package body ab_contact. Funcs
+-- create or replace view ab_contacts_complete
+-- create or replace view ab_contacts_related
+
+
+-----------------------
+-- OBJECTS --
+-----------------------
+
+
+create function inline_0 ()
+returns integer as '
+declare
+ attr_id acs_attributes.attribute_id%TYPE;
+begin
+-- ab_contact object
+ PERFORM acs_object_type__create_type (
+ ''ab_contact'', -- object_type
+ ''Address Book Contact'', -- pretty_name
+ ''Address Book Contact'', -- pretty_plural
+ ''acs_object'', -- supertype
+ ''ab_contact'', -- table_name
+ ''contact_id'', -- id_column
+ ''ab_contact'', -- package_name (default)
+ ''f'', -- abstract_p (default)
+ null, -- type_extension_table (default)
+ ''ab_contact.name'' -- name_method (default)
+ );
+
+
+-- ab_contact_attr_type OBJECT
+ PERFORM acs_object_type__create_type (
+ ''ab_contact_attr_type'', -- object_type
+ ''Address Book Contact Attribute Type'', -- pretty_name
+ ''Address Book Contact Attribute Types'', -- pretty_plural
+ ''acs_object'', -- supertype
+ ''ab_contact_attr_type'', -- table_name
+ ''type_id'', -- id_column
+ ''ab_contact_attr_type'', -- package_name (default)
+ ''f'', -- abstract_p (default)
+ null, -- type_extension_table (default)
+ ''ab_contact_attr_type.name'' -- name_method (default)
+ );
+
+-- ab_contact_attrs OBJECT
+ PERFORM acs_object_type__create_type (
+ ''ab_contact_attrs'', -- object type
+ ''Address Book Contact Attribute'', -- pretty_name
+ ''Address Book Contact Attributes'', -- pretty_plural
+ ''acs_object'', -- supertype
+ ''ab_contact_attrs'', -- table_name
+ ''attr_id'', -- id_column
+ ''ab_contact_attr'', -- package_name (default)
+ ''f'', -- abstract_p (default)
+ null, -- type_extension_table (default)
+ ''ab_contact_attr.name'' -- name_method (default)
+ );
+
+ return 0;
+end;' language 'plpgsql';
+
+select inline_0 ();
+drop function inline_0 ();
+
+-----------------------
+-- AB_CONTACTS TABLES --
+-----------------------
+-- ab_contacts table
+create table ab_contacts (
+ contact_id integer not null
+ constraint ab_contacts_contact_id_fk
+ references acs_objects (object_id)
+ constraint ab_contacts_pk primary key,
+ first_names varchar(200),
+ last_name varchar(200),
+ title varchar(200),
+ organization varchar(200)
+);
+
+
+-- ab_contact_attr_types TABLE --
+create table ab_contact_attr_types (
+ type_id integer not null
+ constraint ab_con_attr_tp_type_id_fk
+ references acs_objects (object_id)
+ constraint ab_contact_attr_types_pk
+ primary key,
+ type_name varchar(100) not null,
+ type_key varchar(20) not null
+ constraint ab_con_attr_tp_type_key_un
+ unique
+);
+
+-- ab_contact_attrs TABLE
+create table ab_contact_attrs (
+ attr_id integer not null
+ constraint ab_con_attrs_attr_id_fk
+ references acs_objects (object_id)
+ constraint ab_con_attrs_pk primary key,
+ contact_id integer not null
+ constraint ab_con_attrs_contact_id_fk
+ references ab_contacts (contact_id),
+ type_key varchar(20) not null
+ constraint ab_con_attrs_type_key_fk
+ references ab_contact_attr_types (type_key),
+ value varchar(200),
+ sort_key integer not null
+);
+
+-----------------------------------
+-- AB_CONTACT_ATTR_TYPES PACKAGE --
+-----------------------------------
+-------------------
+-- Function: new contact attribute type
+
+create function ab_contact_attr_type__new (
+ -- type_id
+ -- object_type
+ -- creation_date
+ -- creation_user
+ -- creation_ip
+ -- context_id
+ -- type_name
+ -- type_key
+ integer, -- ab_contact_attr_types.type_id%TYPE
+ varchar, -- acs_objects.object_type%TYPE
+ timestamp, -- acs_objects.creation_date%TYPE
+ integer, -- acs_objects.creation_user%TYPE
+ varchar, -- acs_objects.creation_ip%TYPE
+ integer, -- acs_objects.context_id%TYPE
+ varchar, -- ab_contact_attr_types.type_name%TYPE,
+ varchar -- ab_contact_attr_types.type_key%TYPE
+)
+returns integer as ' -- ab_contact_attr_types.type_id%TYPE
+declare
+ new__type_id alias for $1; -- default null
+ new__object_type alias for $2; -- default ab_contact_attr_type
+ new__creation_date alias for $3; -- default sysdate
+ new__creation_user alias for $4; -- default null
+ new__creation_ip alias for $5; -- default null
+ new__context_id alias for $6; -- default null
+ new__type_name alias for $7; -- default null
+ new__type_key alias for $8;
+ v_type_id ab_contact_attr_types.type_id%TYPE;
+begin
+ v_type_id := acs_object__new (
+ new__type_id,
+ new__object_type,
+ new__creation_date,
+ new__creation_user,
+ new__creation_ip,
+ new__context_id
+ );
+
+ insert into ab_contact_attr_types
+ (type_id, type_name, type_key)
+ values
+ (v_type_id, new__type_name, new__type_key);
+
+ return v_type_id;
+
+end;' language 'plpgsql';
+
+
+-- name contact attribute type
+create function ab_contact_attr_type__name (
+ integer -- ab_contact_attr_types.type_id%TYPE
+) returns integer as ' -- ab_contact_attr_types.type_name%TYPE
+declare
+ new__type_id alias for $1; -- default null
+begin
+ select type_name
+ into v_type_name
+ from ab_contact_attr_types
+ where type_id = name.type_id;
+ return v_type_name;
+
+end;' language 'plpgsql';
+
+
+-- delete contact attribute type
+----------------------
+create function ab_contact_attr_type__delete (
+ integer -- ab_contact_attr_types.type_id%TYPE
+) returns integer as '
+declare
+ delete__type_id alias for $1;
+begin
+ delete from acs_permissions
+ where object_id = delete__type_id;
+
+ delete from ab_contact_attr_types
+ where type_id = delete__type_id;
+
+ raise NOTICE ''Deleting contact attribute type...'';
+ PERFORM acs_object__delete(delete__type_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+
+
+
+-- ----------------------------
+-- -- DEFINE SOME ATTR TYPES --
+-- ----------------------------
+--
+-- -- This may look remarkably similar to the attribute types offered
+-- -- by the address book in PalmOS. Not a coincidence.
+--
+create function inline_0 () returns integer as '
+declare
+ tmpval ab_contact_attr_types.type_id%TYPE;
+ begin
+ tmpval :=
+ ab_contact_attr_type__new(
+ null, -- type_id default null
+ ''ab_contact_attr_type'', -- object_type
+ now(), -- creation_date
+ null, -- creation_user
+ null, -- creation_ip
+ null, -- context_id
+ ''Work Phone'', -- type_name
+ ''work_phone'' -- type_key
+ );
+
+-- raise notice "....here...";
+
+ tmpval :=
+ ab_contact_attr_type__new(
+ null, -- type_id default null
+ ''ab_contact_attr_type'', -- object_type
+ now(), -- creation_date
+ null, -- creation_user
+ null, -- creation_ip
+ null, -- context_id
+ ''Home Phone'', -- type_name
+ ''home_phone'' -- type_key
+ );
+
+ tmpval :=
+ ab_contact_attr_type__new(
+ null, -- type_id default null
+ ''ab_contact_attr_type'', -- object_type
+ now(), -- creation_date
+ null, -- creation_user
+ null, -- creation_ip
+ null, -- context_id
+ ''Fax Phone'', -- type_name
+ ''fax''); -- type_key
+
+ tmpval :=
+ ab_contact_attr_type__new(
+ null, -- type_id default null
+ ''ab_contact_attr_type'', -- object_type
+ now(), -- creation_date
+ null, -- creation_user
+ null, -- creation_ip
+ null, -- context_id
+ ''Other'', -- type_name
+ ''other''); -- type_key
+
+ tmpval :=
+ ab_contact_attr_type__new(
+ null, -- type_id default null
+ ''ab_contact_attr_type'', -- object_type
+ now(), -- creation_date
+ null, -- creation_user
+ null, -- creation_ip
+ null, -- context_id
+ ''E-mail Address'', -- type_name
+ ''email''); -- type_key
+
+ tmpval :=
+ ab_contact_attr_type__new(
+ null, -- type_id default null
+ ''ab_contact_attr_type'', -- object_type
+ now(), -- creation_date
+ null, -- creation_user
+ null, -- creation_ip
+ null, -- context_id
+ ''Main'', -- type_name
+ ''main''); -- type_key
+
+ tmpval :=
+ ab_contact_attr_type__new(
+ null, -- type_id default null
+ ''ab_contact_attr_type'', -- object_type
+ now(), -- creation_date
+ null, -- creation_user
+ null, -- creation_ip
+ null, -- context_id
+ ''Pager'', -- type_name
+ ''pager''); -- type_key
+
+ tmpval :=
+ ab_contact_attr_type__new(
+null, -- type_id default null
+ ''ab_contact_attr_type'', -- object_type
+ now(), -- creation_date
+ null, -- creation_user
+ null, -- creation_ip
+ null, -- context_id
+ ''Mobile Phone'', -- type_name
+ ''mobile''); -- type_key
+return 0;
+end;' language 'plpgsql';
+select inline_0 ();
+drop function inline_0 ();
+
+
+-- contact attributes package
+-------------------
+-- Function: new contact attribute
+create function ab_contact_attr__new (
+ -- attr_id in ab_contact_attrs.attr_id%TYPE
+ -- object_type in acs_objects.object_type%TYPE
+ -- creation_date in acs_objects.creation_date%TYPE
+ -- creation_user in acs_objects.creation_user%TYPE
+ -- creation_ip in acs_objects.creation_ip%TYPE
+ -- context_id in acs_objects.context_id%TYPE
+ -- contact_id in ab_contact_attrs.contact_id%TYPE,
+ -- type_key in ab_contact_attrs.type_key%TYPE,
+ -- value in ab_contact_attrs.value%TYPE,
+ -- before_attr_id in ab_contact_attrs.attr_id%TYPE
+ integer, -- ab_contact_attrs.attr_id%TYPE
+ varchar, -- acs_objects.object_type%TYPE
+ timestamp, -- acs_objects.creation_date%TYPE
+ integer, -- acs_objects.creation_user%TYPE
+ varchar, -- acs_objects.creation_ip%TYPE
+ integer, -- acs_objects.context_id%TYPE
+ integer, -- ab_contact_attrs.contact_id%TYPE,
+ integer, -- ab_contact_attrs.type_key%TYPE,
+ varchar, -- ab_contact_attrs.value%TYPE,
+ integer -- ab_contact_attrs.attr_id%TYPE
+
+) returns integer as ' -- ab_contact_attrs.attr_id%TYPE
+declare
+ new__type_id alias for $1; -- default null
+ new__object_type alias for $2; -- default ''ab_contact_attr''
+ new__creation_date alias for $3; -- default sysdate
+ new__creation_user alias for $4; -- default null
+ new__creation_ip alias for $5; -- default null
+ new__context_id alias for $6; -- default null
+ new__contact_id alias for $7; --
+ new__type_key alias for $8;
+ new__value alias for $9;
+ new__before_attr_id alias for $10; -- default null
+ v_attr_id ab_contact_attrs.attr_id%TYPE;
+begin
+ v_attr_id := acs_object__new (
+ new__attr_id,
+ new__object_type,
+ new__creation_date,
+ new__creation_user,
+ new__creation_ip,
+ new__context_id
+ );
+
+ if new.before_attr_id is not null then
+
+ -- Shift the attributes down to accomidate if they specified
+ -- before_attr_id
+
+update ab_contact_attrs aca
+ set sort_key = sort_key + 1
+ where sort_key >= (select sort_key
+ from ab_contact_attrs
+ where attr_id = new.before_attr_id)
+ and exists (select 1
+ from ab_contact_attrs
+ where attr_id = aca.attr_id
+ and contact_id = new.contact_id);
+
+ -- Insert into the newly create hole
+
+ insert into ab_contact_attrs
+ (attr_id, contact_id, type_key, value, sort_key)
+
+select new.v_attr_id, new.contact_id, new.type_key, new.value, (sort_key - 1)
+ from ab_contact_attrs
+ where attr_id = new.before_attr_id;
+
+ else
+
+ -- Otherwise, tack it on the end
+
+ insert into ab_contact_attrs
+ (attr_id, contact_id, type_key, value, sort_key)
+ select new.v_attr_id, new.contact_id, new.type_key, new.value, nvl(max(sort_key) + 1, 1)
+ from ab_contact_attrs
+ where contact_id = new.contact_id;
+
+ end if;
+
+ return v_attr_id;
+ end new;
+ );
+
+ return v_attr_id;
+
+end;' language 'plpgsql';
+
+
+-- swap_sort
+create function ab_contact_attr__swap_sort (
+ integer, -- ab_contact_attrs.attr_id%TYPE,
+ integer -- ab_contact_attrs.attr_id%TYPE
+) returns integer as '
+declare
+ attr_id_one alias for $1;
+ 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
+ -- the same contact_id).
+
+ 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)
+ 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
+ and aca3.contact_id = aca4.contact_id);
+
+end;' language 'plpgsql';
+
+-- delete
+create function ab_contact_attr__delete (
+ integer -- ab_contact_attrs.attr_id%TYPE
+) returns integer as '
+declare
+ attr_id alias for $1; -- default null
+begin
+ acs_object__delete(attr_id);
+ return 0;
+end;' language 'plpgsql';
+
+
+-- --------------------
+-- -- 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_type (
+ ''ab_contact_rel'', -- rel_type
+ ''Address Book Contact Relation'', -- pretty_name
+ ''Address Book Contact Relationships'',-- pretty_plural
+ ''relationship'', -- supertype,
+ ''ab_contact_rels'', -- table_name
+ ''rel_id'', -- id_column
+ ''ab_contact_rel'', -- package_name
+ ''acs_object'', -- object_type_one
+ null, -- role_one
+ 0, -- min_n_rels_one
+ null, -- max_n_rels_one
+ ''ab_contact'', -- object_type_two
+ ''contact'', -- role_two
+ 0, -- min_n_rels_two
+ null -- max_n_rels_two
+ );
+return 0;
+end;' language 'plpgsql';
+
+select inline_0();
+drop function inline_0();
+
+
+create table ab_contact_rels (
+ rel_id integer constraint ab_contact_rels_rel_id_fk
+ references acs_rels (rel_id)
+ constraint ab_contact_rels_rel_id_pk
+ primary key,
+ category varchar(30)
+);
+
+
+--------------------------------
+-- Package ab_contact_rel
+-- function new
+create function ab_contact_rel__new (
+ -- rel_id
+ -- rel_type
+ -- object_id_one
+ -- object_id_two
+ -- creation_user
+ -- creation_ip
+ -- category
+ integer, -- ab_contact_rels.rel_id%TYPE default null,
+ integer, -- acs_rels.rel_type%TYPE
+ integer, -- acs_rels.object_id_one%TYPE,
+ integer, -- acs_rels.object_id_two%TYPE,
+ varchar, -- acs_objects.creation_user%TYPE default null,
+ varchar, -- acs_objects.creation_ip%TYPE default null,
+ varchar -- ab_contact_rels.category%TYPE default null
+) returns integer as '
+declare
+ rel_id alias for $1;
+ rel_type alias for $2;
+ object_id_one alias for $3;
+ object_id_two alias for $4;
+ creation_user alias for $5;
+ creation_ip alias for $6;
+ category alias for $7;
+ v_rel_id ab_contact_rels.rel_id%TYPE;
+begin
+ v_rel_id :=
+ acs_rel__new (
+ rel_id,
+ rel_type,
+ object_id_one,
+ object_id_two,
+ object_id_one,
+ creation_user,
+ creation_ip
+ );
+
+ insert into ab_contact_rels
+ (rel_id, category)
+ values
+ (v_rel_id, new.category);
+ return v_rel_id;
+end;' language 'plpgsql';
+
+
+
+-- function delete
+
+create function ab_contact_rel__delete (
+ -- rel_id
+ integer -- ab_contact_rels.rel_id%TYPE
+) returns integer as '
+declare
+ rel_id alias for $1;
+begin
+ acs_object__delete(rel_id);
+ return 0;
+
+end;' language 'plpgsql';
+
+-- ------------------------
+-- -- AB_CONTACT PACKAGE --
+-- ------------------------
+-- function new
+
+create function ab_contact__new (
+ -- contact_id
+ -- object_type
+ -- creation_date
+ -- creation_user
+ -- creation_ip
+ -- context_id
+ -- first_names
+ -- last_name
+ -- title
+ -- organization
+ -- object_id
+ -- category
+ integer, -- ab_contacts.contact_id%TYPE default null,
+ varchar, -- acs_objects.object_type%TYPE
+ timestamp, -- acs_objects.creation_date%TYPE
+ integer, -- acs_objects.creation_user%TYPE
+ varchar, -- acs_objects.creation_ip%TYPE
+ integer, -- acs_objects.context_id%TYPE
+ varchar, -- ab_contacts.first_names%TYPE default null,
+ varchar, -- ab_contacts.last_name%TYPE default null,
+ varchar, -- ab_contacts.title%TYPE default null,
+ varchar, -- ab_contacts.organization%TYPE default null,
+ integer, -- acs_objects.object_id%TYPE default null,
+ varchar -- ab_contact_rels.category%TYPE default null
+
+) returns integer as ' -- ab_contacts.contact_id%TYPE;
+declare
+ new__contact_id alias for $1;
+ new__object_type alias for $2;
+ new__creation_date alias for $3;
+ new__creation_user alias for $4;
+ new__creation_ip alias for $5;
+ new__context_id alias for $6;
+ new__first_names alias for $7;
+ new__last_name alias for $8;
+ new__title alias for $9;
+ new__organization alias for $10;
+ new__object_id alias for $11;
+ new__category alias for $12;
+ v_contact_id ab_contacts.contact_id%TYPE;
+ v_rel_id ab_contact_rels.rel_id%TYPE;
+begin
+ v_contact_id :=
+ acs_object__new(
+ new__object_id, -- object_id
+ new__object_type, -- object_type
+ new__creation_date, -- creation_date
+ new__creation_user, -- creation_user
+ new__creation_ip, -- creation_ip
+ new__context_id -- context_id
+ );
+
+
+ -- If they supplied an object_id, relate it:
+ -- if object_id is not null then
+ v_rel_id :=
+ ab_contact_rel.new(
+ null, -- rel_id
+ null, -- rel_type
+ new__object_id, -- object_id_one
+ v_contact_id, -- object_id_two
+ new__creation_user,-- creation_user
+ new__creation_ip, -- creation_ip
+ new__category -- category
+);
+
+ end if;
+
+ insert into ab_contacts
+ (contact_id, first_names, last_name,
+ title, organization)
+ values
+ (v_contact_id, new.first_names, new.last_name,
+ new.title, new.organization);
+
+ return v_contact_id;
+
+end;' language 'plpgsql';
+
+
+-- function name
+create function ab_contact__name (
+ -- contact_id
+ integer -- ab_contacts.contact_id%TYPE
+) returns varchar as '
+declare
+ contact_id alias for $1;
+ v_name varchar(400);
+begin
+ select first_names||'' ''||last_name
+ into v_name
+ from ab_contacts
+ where contact_id = name.contact_id;
+
+ return v_name;
+
+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;
+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 (
+ 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
+ -- 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.
+
+
+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 '
+declare
+ contact_id_p 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
+ and sort_key = (select min(sort_key)
+ from ab_contact_attrs
+ where contact_id = contact_id_p
+ and type_key = ''work_phone'');
+ return v_value;
+end;' language 'plpgsql';
+
+-- function home_phone
+create function ab_contact__home_phone (
+ -- contact_id
+ integer -- ab_contacts.contact_id%TYPE
+) return varchar as '
+declare
+ contact_id_p 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
+ and sort_key = (select min(sort_key)
+ from ab_contact_attrs
+ where contact_id = contact_id_p
+ and type_key = ''home_phone'');
+ return v_value;
+
+end;' language 'plpgsql';
+
+-- function fax
+create function ab_contact__fax (
+ -- contact_id
+ integer -- ab_contacts.contact_id%TYPE
+) return varchar as '
+declare
+ contact_id_p 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
+ and sort_key = (select min(sort_key)
+ from ab_contact_attrs
+ where contact_id = contact_id_p
+ and type_key = ''fax'');
+return v_value;
+
+end;' language 'plpgsql';
+
+-- function other
+create function ab_contact__other (
+ -- contact_id
+ integer -- ab_contacts.contact_id%TYPE
+) return varchar as '
+declare
+ contact_id_p 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
+ and sort_key = (select min(sort_key)
+ from ab_contact_attrs
+ where contact_id = contact_id_p
+ and type_key = ''other'');
+ return v_value;
+end;' language 'plpgsql';
+
+-- function email
+create function ab_contact__fax (
+ -- contact_id
+ integer -- ab_contacts.contact_id%TYPE
+) return varchar as '
+declare
+ contact_id_p 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
+ and sort_key = (select min(sort_key)
+ from ab_contact_attrs
+ where contact_id = contact_id_p
+ and type_key = ''email'');
+ return v_value;
+
+end;' language 'plpgsql';
+
+-- --------------------------
+-- -- AB_CONTACTS_COMPLETE --
+-- --------------------------
+
+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
+ from ab_contacts ac;
+
+--
+create view ab_contacts_related as
+ select acc.*,
+ ar.rel_id,
+ ar.object_id_one as object_id,
+ acrel.category
+ from ab_contacts_complete acc,
+ acs_rels ar,
+ ab_contact_rels acrel
+ where acc.contact_id = ar.object_id_two
+ and ar.rel_id = acrel.rel_id;
+
+
+
Index: openacs-4/packages/address-book/sql/postgresql/address-book-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/address-book/sql/postgresql/address-book-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/address-book/sql/postgresql/address-book-drop.sql 22 Aug 2001 22:55:22 -0000 1.1
@@ -0,0 +1,141 @@
+-- packages/address-book/sql/address-book-drop.sql
+-- @author John Mileham (jmileham@arsdigita.com)
+-- @cvs-id $Id: address-book-drop.sql,v 1.1 2001/08/22 22:55:22 rafaelc Exp $
+
+drop view ab_contacts_related;
+
+drop view ab_contacts_complete;
+
+drop function ab_contact_attr_type__name(integer); -- RC
+drop function ab_contact_attr_type__delete(integer); -- RC
+
+
+drop function ab_contact_attr__swap_sort(integer,integer);--RC
+drop function ab_contact_attr__delete();
+
+drop function ab_contact_attr__delete();
+drop function ab_contact_attr_type__new(
+ integer,
+ varchar,
+ timestamp,
+ integer,
+ varchar,
+ integer,
+ varchar,
+ varchar );
+drop function ab_contact_attr__delete (integer);
+
+drop function ab_contact_attr__new (
+ integer,
+ varchar,
+ timestamp,
+ integer,
+ varchar,
+ integer,
+ integer,
+ integer,
+ varchar,
+ integer
+);
+
+drop function ab_contact__delete(integer, boolean);
+
+-- the original loops in Orcle look like:
+-- declare
+-- cursor object_id_c is
+-- select rel_id from ab_contact_rels;
+-- v_row ab_contact_rels%ROWTYPE;
+--begin
+-- for v_row in object_id_c loop
+-- ab_contact_rel.delete(v_row.rel_id);
+-- end loop;
+--end;
+--/
+--show errors
+
+
+create function inline_0 () returns integer as '
+begin
+ PERFORM
+ for attr_id
+ in select attr_id from ab_contact_attrs
+ loop
+ ab_contact_attr__delete(v_row.attr_id);
+ end loop;
+
+ for rel_id
+ in select rel_id from ab_contact_rels
+ loop
+ ab_contact_rel__delete(v_row.rel_id);
+ end loop;
+
+ for type_id
+ in select type_id from ab_contact_attr_types
+ loop
+ ab_contact_attr_types__delete(v_row.type_id);
+ end loop;
+
+ for contact_id
+ in select contact_id from ab_contacts
+ loop
+ ab_contacts__delete(v_row.contact_id);
+ end loop;
+return null;
+end;' language 'plpgsql';
+select inline_0 ();
+drop function inline_0 ();
+
+
+drop package ab_contact;
+
+drop package ab_contact_rel;
+
+drop table ab_contact_rels;
+
+begin
+ acs_rel_type__drop_type(''ab_contact_rel'');
+ acs_rel_type__drop_role(''contact'');
+end;
+less
+
+drop package ab_contact_attr;
+
+drop table ab_contact_attrs;
+
+create function inline_0 () returns integer as '
+begin
+ PERFORM acs_object_type__drop_type(''ab_contact_attr'');
+returns null;
+end;' language 'plpgsql';
+select inline_0 ();
+drop function inline_0 ();
+
+
+drop package ab_contact_attr_type;
+
+drop table ab_contact_attr_types;
+
+create function inline_0 () returns integer as '
+begin
+ PERFORM acs_object_type__drop_type(''ab_contact_attr_type'');
+returns null;
+end;' language 'plpgsql';
+select inline_0 ();
+drop function inline_0 ();
+
+drop table ab_contacts;
+
+
+create function inline_0 () returns integer as '
+begin
+ PERFORM acs_object_type__drop_type(''ab_contact'');
+returns null;
+end;' language 'plpgsql';
+select inline_0 ();
+drop function inline_0 ();
+
+drop function ab_contact_rel__delete(integer);
+drop function ab_contact_rel__new(integer,integer,integer,integer,varchar,varchar,varchar);
+
+drop function ab_contact__new( integer,varchar,timestamp,integer,varchar,integer,varchar,varchar,varchar,varchar,integer,varchar);
+drop function ab_contact__name( integer);