Index: openacs-4/packages/acs-person/sql/oracle/acs-person-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-person/sql/oracle/Attic/acs-person-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-person/sql/oracle/acs-person-create.sql 26 Sep 2002 03:32:07 -0000 1.1 @@ -0,0 +1,201 @@ +-- packages/acs-person/sql/oracle/acs-person-create.sql +-- +-- @author jon@jongriffin.com +-- @creation-date 2002-09-25 +-- @cvs-id $Id: acs-person-create.sql,v 1.1 2002/09/26 03:32:07 jong Exp $ + +-- This is a fairly direct mapping of the HR-XML PersonName v1.0 schema +-- see the docs for explanations + +-- create a package sequence +create sequence acs_persons_seq; + +-- create some lookup tables + +-- affix_type +create table affix_type ( + affix_type_id integer + constraint affix_type_id_pk + primary key, + xml_name varchar(20) + constraint affix_type_xml_name_nn + not null, + help_text varchar(400) + constraint affix_type_pretty_name_nn + not null +); + +-- add some initial data + +insert into affix_type + (affix_type_id, xml_name,help_text) +values + (nextval('acs_persons_seq'),'aristocraticTitle','i.e. Baron, Graf, Earl, etc.'); + +insert into affix_type + (affix_type_id, xml_name,help_text) +values + (nextval('acs_persons_seq'),'aristocraticPrefix','i.e. Von, etc.'); + +insert into affix_type + (affix_type_id, xml_name,help_text) +values + (nextval('acs_persons_seq'),'formOfAddress','Contains the salutation, +i.e. Mr., Mrs., Hon., Dr., etc.'); + +insert into affix_type + (affix_type_id,xml_name,help_text) +values + (nextval('acs_persons_seq'),'FamilyNamePrefix','Contains the part of the person''s +name that precedes the family name. i.e. Van den, Von, etc.'); + +insert into affix_type + (affix_type_id,xml_name,help_text) +values + (nextval('acs_persons_seq'),'generation','i.e. Sr. Jr., III'); + +insert into affix_type + (affix_type_id,xml_name,help_text) +values + (nextval('acs_persons_seq'),'qualifications','Contains the letters used to describe the academic qualifications held by a person and/or the distinctions conferred upon them. +i.e. PhD, MD, CPA, MCSD, etc.'); + + + + +-- Main table +create table acs_persons ( + acs_person_id integer + constraint acs_person_id_pk + primary key + constraint acs_person_id_fk + references acs_objects(object_id), + formatted_name varchar (200), + given_name varchar (100), + preferred_given_name varchar (100), + middle_name varchar (100), + -- The spec says that all fields should be optional + -- This data model requires at least the family_name + -- element. Madonna and Prince go here whether they + -- consider that a family name or not. + family_name varchar (100) + constraint acs_prsn_fmly_nme_nn + not null, + -- link into users + user_id integer + constraint acs_user_id_fk + references users(user_id) +); + +create index acs_persons_full_name_ix on acs_persons (given_name,family_name); +create index acs_persons_family_name_ix on acs_persons (family_name); + +comment on table acs_persons is ' +This is the main table for acs_persons. It is a direct mapping of the HR-XML +PersonName v1.0 schema +'; + +comment on column acs_persons.acs_person_id is ' +Primary key. +'; + +comment on column acs_persons.family_name is ' +The last name. +'; + +comment on column acs_persons.given_name is ' +Given or first name. +'; + +comment on column acs_persons.middle_name is ' +This could also be more than one name. +'; + +comment on column acs_persons.formatted_name is ' +This is the name as it might appear in a letter. +'; + +comment on column acs_persons.preferred_given_name is ' +'; + + +comment on column acs_persons.acs_person_id is ' +Foriegn key. This can be null as we also want to use this for non-users progams +i.e. contact manager etc. +'; + + +-- Now the map tables + +-- affix_acs_persons_map +-- +-- This is necessary due to the way HR-XML implemented the affix system. +-- Since it is many to many this is the only way. + +create table affix_acs_persons_map ( + acs_person_id integer + constraint affix_person_id_fk + references persons(person_id), + affix_type_id integer + constraint affix_type_id_fk + references affix_type(affix_type_id), + ---- add the primary key to ensure uniqueness + constraint affix_acs_prsn_map_pk + primary key (acs_person_id,affix_type_id) +); + +-- acs_persons_given_names +create table acs_persons_given_names ( + given_name_id integer + constraint acs_prsn_gvn_nme_pk + primary key, + acs_person_id integer + constraint acs_prsn_gvn_nme_fk + references acs_persons(acs_person_id), + extra_given_name varchar(100) + constraint acs_prsn_gvn_nme_name_nn + not null, + sort_order integer, + ---- add some constraints to keep data sanity + constraint acs_prsn_gvn_nme_uq + unique (acs_person_id,extra_given_name,sort_order) +); + +create table acs_persons_middle_names ( + middle_name_id integer + constraint acs_prsn_mdl_nme_pk + primary key, + acs_person_id integer + constraint acs_prsn_mdl_nme_fk + references acs_persons(acs_person_id), + extra_middle_name varchar(100) + constraint acs_prsn_mdl_nme_name_nn + not null, + sort_order integer, + ---- add some constraints to keep data sanity + constraint acs_prsn_mdl_nme_uq + unique (acs_person_id,extra_middle_name,sort_order) +); + +create table acs_persons_family_names ( + family_name_id integer + constraint acs_prsn_fmly_nme_pk + primary key, + acs_person_id integer + constraint acs_prsn_fmly_nme_fk + references acs_persons(acs_person_id), + extra_family_name varchar(100) + constraint acs_prsn_fmly_nme_name_nn + not null, + sort_order integer, + ---- add some constraints to keep data sanity + constraint acs_prsn_fmly_nme_uq + unique (acs_person_id,extra_family_name,sort_order) +); + +-- plsql procs +@@ acs-person-plsql.sql + +-- Service contract +-- Broken drop script +-- @@ acs-person-sc-create.sql Index: openacs-4/packages/acs-person/sql/oracle/acs-person-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-person/sql/oracle/Attic/acs-person-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-person/sql/oracle/acs-person-drop.sql 26 Sep 2002 03:32:07 -0000 1.1 @@ -0,0 +1,34 @@ +-- packages/acs-person/sql/oracle/acs-person-drop.sql +-- +-- @author jon@jongriffin.com +-- @creation-date 2002-09-25 +-- @cvs-id $Id: acs-person-drop.sql,v 1.1 2002/09/26 03:32:07 jong Exp $ + +set server output on + +--@@ acs-person-sc-drop.sql + +-- drop sequence +drop sequence acs_persons_seq; + +-- drop functions + +drop package acs_person; + +--drop permissions +delete from acs_permissions where object_id in (select acs_person_id from acs_persons); + +-- drop the tables +drop table affix_type; +drop table affix_acs_persons_map; +drop table acs_persons_given_names; +drop table acs_persons_middle_names; +drop table acs_persons_family_names; +drop table acs_persons; + + +-- drop attributes and object types + +execute acs_object_type.drop_type('acs_person','t'); + +show errors; \ No newline at end of file Index: openacs-4/packages/acs-person/sql/oracle/acs-person-plsql.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-person/sql/oracle/Attic/acs-person-plsql.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-person/sql/oracle/acs-person-plsql.sql 26 Sep 2002 03:32:07 -0000 1.1 @@ -0,0 +1,195 @@ +-- packages/acs-person/sql/oracle/acs-person-plsql.sql +-- +-- @author jon@jongriffin.com +-- @creation-date 2002-09-25 +-- @cvs-id $Id: acs-person-plsql.sql,v 1.1 2002/09/26 03:32:07 jong Exp $ + +begin + acs_object_type.create_type ( + object_type => 'acs_person', + pretty_name => 'ACS Person', + pretty_plural => 'ACS Persons', + supertype => 'acs_object', + table_name => 'acs_persons', + id_column => 'acs_person_id' + ); +end; +/ +show errors + +create or replace package acs_person +as + -- + -- + function new ( + acs_person_id in acs_persons.acs_person_id%TYPE default null, + family_name in acs_persons.family_name%TYPE, + given_name in acs_persons.given_name%TYPE, + middle_name in acs_persons.middle_name%TYPE default null, + formatted_name in acs_persons.formatted_name%TYPE default null, + preferred_given_name in acs_persons.preferred_given_name%TYPE default null, + user_id in acs_persons.user_id%TYPE default null + object_type in acs_objects.object_type%TYPE default 'acs_object', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_use in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + context_id in acs_objects.context_id%TYPE default null + ) return acs_persons.acs_person_id%TYPE; + -- + -- + procedure delete ( + acs_person_id in acs_persons.acs_person_id%TYPE + ); + procedure set ( + acs_person_id in acs_persons.acs_person_id%TYPE default null, + family_name in acs_persons.family_name%TYPE, + given_name in acs_persons.given_name%TYPE, + middle_name in acs_persons.middle_name%TYPE default null, + formatted_name in acs_persons.formatted_name%TYPE default null, + preferred_given_name in acs_persons.preferred_given_name%TYPE default null, + user_id in acs_persons.user_id%TYPE default null + ); + -- + -- +end acs_person_id; +/ +show errors + + +create or replace package body contact +as + function new ( + acs_person_id in acs_persons.acs_person_id%TYPE default null, + family_name in acs_persons.family_name%TYPE, + given_name in acs_persons.given_name%TYPE, + middle_name in acs_persons.middle_name%TYPE default null, + formatted_name in acs_persons.formatted_name%TYPE default null, + preferred_given_name in acs_persons.preferred_given_name%TYPE default null, + user_id in acs_persons.user_id%TYPE default null + object_type in acs_objects.object_type%TYPE default 'acs_object', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_use in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + context_id in acs_objects.context_id%TYPE default null + ) return acs_persons.acs_person_id%TYPE + is + v_acs_person_id acs_persons.acs_person_id%TYPE; + begin + v_acs_person_id := acs_object.new ( + object_id => acs_person_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + -- + insert into acs_persons + ( acs_person_id, + family_name, + given_name, + middle_name, + formatted_name, + preferred_given_name + ) + values + ( v_acs_person_id, + family_name, + given_name, + middle_name, + formatted_name, + preferred_given_name); + return v_acs_person_id; + end new; + -- + -- + procedure delete ( + acs_person_id in acs_persons.acs_person_id%TYPE + ) + is + begin + delete from acs_persons + where acs_person_id = acs_person.delete.acs_person_id; + -- + acs_object.delete(acs_person_id); + end delete; + -- + -- + procedure set ( + acs_person_id in acs_persons.acs_person_id%TYPE default null, + family_name in acs_persons.family_name%TYPE, + given_name in acs_persons.given_name%TYPE, + middle_name in acs_persons.middle_name%TYPE default null, + formatted_name in acs_persons.formatted_name%TYPE default null, + preferred_given_name in acs_persons.preferred_given_name%TYPE default null, + user_id in acs_persons.user_id%TYPE default null + ) + is + begin + update acs_persons + set + given_name = p_given_name, + middle_name = p_middle_name, + family_name = p_family_name, + formatted_name = p_formatted_name, + preferred_given_name = p_preferred_given_name, + user_id = p_user_id + where acs_person_id = p_acs_person_id; + + end set; + -- + -- + +end acs_person; +/ +show errors + +-- ancillary tables +begin + acs_object_type.create_type ( + object_type => 'acs_person_given_name', + pretty_name => 'ACS Person Given Name', + pretty_plural => 'ACS Person Given Names', + supertype => 'acs_object', + table_name => 'acs_person_given_name', + id_column => 'given_name_id' + ); +end; +/ +show errors + +create or replace package acs_person_given_name +as + procedure set ( + p_acs_person_id in acs_persons_given_name.acs_person_id%TYPE, + p_extra_given_name in acs_persons_given_name.extra_given_name%TYPE, + p_sort_order in acs_persons_given_name.sort_order%TYPE, + p_given_name_id in acs_persons_given_name.given_name_id%TYPE + ); + +end acs_person_given_name; +/ +show errors + + +create or replace package body acs_person_given_name +as + procedure set ( + p_acs_person_id in acs_persons_given_name.acs_person_id%TYPE, + p_extra_given_name in acs_persons_given_name.extra_given_name%TYPE, + p_sort_order in acs_persons_given_name.sort_order%TYPE, + p_given_name_id in acs_persons_given_name.given_name_id%TYPE + ) + is + begin + update acs_persons_given_name + set + acs_person_id = p_acs_person_id, + given_name = p_given_name, + sort_order = p_sort_order + where given_name_id = p_given_name_id; + +end acs_person_given_name; +/ +show errors + Index: openacs-4/packages/acs-person/sql/oracle/acs-person-sc-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-person/sql/oracle/Attic/acs-person-sc-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-person/sql/oracle/acs-person-sc-create.sql 26 Sep 2002 03:32:07 -0000 1.1 @@ -0,0 +1,58 @@ +select acs_sc_impl__new( + 'FtsContentProvider', -- impl_contract_name + 'acs_person', -- impl_name + 'acs_persons' -- impl_owner_name +); + +select acs_sc_impl_alias__new( + 'FtsContentProvider', -- impl_contract_name + 'contact', -- impl_name + 'datasource', -- impl_operation_name + 'acs_persons__datasource', -- impl_alias + 'TCL' -- impl_pl +); + +select acs_sc_impl_alias__new( + 'FtsContentProvider', -- impl_contract_name + 'contact', -- impl_name + 'url', -- impl_operation_name + 'acs_persons__url', -- impl_alias + 'TCL' -- impl_pl +); + + +create function acs_persons__itrg () +returns opaque as ' +begin + perform search_observer__enqueue(new.contact_id,''INSERT''); + return new; +end;' language 'plpgsql'; + +create function acs_persons__dtrg () +returns opaque as ' +begin + perform search_observer__enqueue(old.contact_id,''DELETE''); + return old; +end;' language 'plpgsql'; + +create function acs_persons__utrg () +returns opaque as ' +begin + perform search_observer__enqueue(old.contact_id,''UPDATE''); + return old; +end;' language 'plpgsql'; + + +create trigger acs_persons__itrg after insert on acs_persons +for each row execute procedure acs_persons__itrg (); + +create trigger acs_persons__dtrg after delete on acs_persons +for each row execute procedure acs_persons__dtrg (); + +create trigger acs_persons__utrg after update on acs_persons +for each row execute procedure acs_persons__utrg (); + + + + + Index: openacs-4/packages/acs-person/sql/oracle/acs-person-sc-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-person/sql/oracle/Attic/acs-person-sc-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-person/sql/oracle/acs-person-sc-drop.sql 26 Sep 2002 03:32:07 -0000 1.1 @@ -0,0 +1,23 @@ +select acs_sc_binding__delete( + 'FtsContentProvider', -- contract_name + 'acs_person' -- impl_name +); + +select acs_sc_impl__delete( + 'FtsContentProvider', -- impl_contract_name + 'acs_person' -- impl_name +); + + + + +drop trigger acs_persons__utrg on acs_persons; +drop trigger acs_persons__dtrg on acs_persons; +drop trigger acs_persons__itrg on acs_persons; + + + +drop function acs_persons__utrg (); +drop function acs_persons__dtrg (); +drop function acs_persons__itrg (); +