-- -- packages/address-book/sql/address-book-create.sql -- @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: address-book-create.sql,v 1.1 2002/07/09 17:35:01 rmello 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_types'', -- 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 ); -- DRB: where's the friggin' index on this table? Why don't we -- use type_id rather than type_key or get rid of type_id? -- 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 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 -- the same contact_id). update ab_contact_attrs aca1 set sort_key = (select sort_key from ab_contact_attrs aca2 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 = p_attr_id_one and aca4.attr_id = p_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 -- -- -------------------- 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 ''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'; create function ab_contact__delete_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 ( 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_rel_id ab_contact_rels.rel_id%TYPE; begin -- 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; 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'; -- function work_phone create function ab_contact__work_phone ( -- contact_id integer -- ab_contacts.contact_id%TYPE ) returns varchar as ' declare 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 = p_contact_id and sort_key = (select min(sort_key) from ab_contact_attrs where contact_id = p_contact_id 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 ) returns varchar as ' declare 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 = p_contact_id and sort_key = (select min(sort_key) from ab_contact_attrs where contact_id = p_contact_id 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 ) returns varchar as ' declare 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 = p_contact_id and sort_key = (select min(sort_key) from ab_contact_attrs where contact_id = p_contact_id 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 ) returns varchar as ' declare 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 = p_contact_id and sort_key = (select min(sort_key) from ab_contact_attrs where contact_id = p_contact_id and type_key = ''other''); return v_value; end;' language 'plpgsql'; -- function email create function ab_contact__email ( -- contact_id integer -- ab_contacts.contact_id%TYPE ) returns varchar as ' declare 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 = p_contact_id and sort_key = (select min(sort_key) from ab_contact_attrs where contact_id = p_contact_id 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) 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; -- 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;