Index: openacs-4/packages/postal-address/sql/oracle/postal-address-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/postal-address/sql/oracle/postal-address-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/postal-address/sql/oracle/postal-address-create.sql 21 Jan 2005 17:35:09 -0000 1.1 @@ -0,0 +1,82 @@ +-- @cvs-id: $Id: postal-address-create.sql,v 1.1 2005/01/21 17:35:09 jeffd Exp $ + +-- lookup table + +-- This is for hr-xml defined type +create table postal_types +( + type_id integer + constraint postal_types_id_pk + primary key, + description varchar (40) + constraint postal_types_desc_nn + not null +); + +insert into postal_types values (1,'Post Office Box'); +insert into postal_types values (2,'Street Address'); +insert into postal_types values (3,'Military Address'); +insert into postal_types values (4,'Undefined (default)'); + +-- main table +-- will get organization_name from other organizations table +-- I am not storing the parsed elements here. +-- That is better left to a validation proc + +create table postal_addresses +( + address_id integer + constraint postal__address_id_pk + primary key + constraint postal_address_id_fk + references acs_objects (object_id), + -- this could be a contact,person,organization etc. + party_id integer + constraint postal_owner_id_fk + references parties (party_id), + delivery_address varchar (1000) + constraint postal_address_delivery_nn + not null, + postal_code varchar (30), + municipality varchar (100), + region varchar (100), + country_code char (2) + constraint postal_addresses_country_fk + references countries (iso) + constraint postal_addresses_country_nn + not null, + additional_text varchar(100), + postal_type integer + constraint postal_addresses_type_fk + references postal_types (type_id) +); + +create index postal_addresses_country_ix on postal_addresses(country_code); +create index postal_addresses_party_ix on postal_addresses(party_id); + +comment on table postal_addresses is ' +This is the master address table. +'; + +comment on column postal_addresses.delivery_address is ' +This is the main delivery address. In the US of A it would be equivalent to line1, line2 and etc. +'; + +comment on column postal_addresses.postal_code is ' +This is equivalent to zip in the good ol'' US of A. +'; + +comment on column postal_addresses.municipality is ' +This is equivalent to City in the good ol'' US of A. +'; + +comment on column postal_addresses.region is ' +This is equivalent to state in the good ol'' US of A. +'; + +comment on column postal_addresses.country_code is ' +Required. This is the country of reference for validations and etc. +'; + +-- plsql +@ postal-address-plsql.sql Index: openacs-4/packages/postal-address/sql/oracle/postal-address-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/postal-address/sql/oracle/postal-address-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/postal-address/sql/oracle/postal-address-drop.sql 21 Jan 2005 17:35:09 -0000 1.1 @@ -0,0 +1,21 @@ +------------------------------------------------------------------------------ +-- Drop script for the postal_address type +-- +-- @author Tom Ayles (tom@beatniq.net) +-- @cvs-id $Id: postal-address-drop.sql,v 1.1 2005/01/21 17:35:09 jeffd Exp $ +------------------------------------------------------------------------------ + +BEGIN + FOR v_address IN (SELECT address_id FROM postal_addresses) LOOP + postal_address.del( v_address.address_id ); + END LOOP; + + acs_object_type.drop_type('postal_address','f'); +END; +/ + +DROP PACKAGE postal_address; + +DROP TABLE postal_addresses; + +DROP TABLE postal_types; Index: openacs-4/packages/postal-address/sql/oracle/postal-address-plsql.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/postal-address/sql/oracle/postal-address-plsql.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/postal-address/sql/oracle/postal-address-plsql.sql 21 Jan 2005 17:35:09 -0000 1.1 @@ -0,0 +1,195 @@ +-- packages/postal_address/sql/postgresql/postal_address-plsql.sql +-- +-- @author Jon Griffin +-- @creation-date 26 February 2003 +-- @cvs-id $Id: postal-address-plsql.sql,v 1.1 2005/01/21 17:35:09 jeffd Exp $ + +-- What no comments? + +declare +begin + acs_object_type.create_type ( + object_type => 'postal_address', + pretty_name => 'Postal Address', + pretty_plural => 'Postal Address', + supertype => 'acs_object', + table_name => 'postal_addresses', + id_column => 'address_id', + package_name => 'postal_address', + abstract_p => 'f', + type_extension_table => null, + name_method => null + ); +end; +/ + +CREATE OR REPLACE PACKAGE postal_address +AS + + FUNCTION new ( + additional_text in postal_addresses.additional_text%TYPE + default null, + address_id in postal_addresses.address_id%TYPE + default null, + country_code in postal_addresses.country_code%TYPE, + delivery_address in postal_addresses.delivery_address%TYPE, + municipality in postal_addresses.municipality%TYPE + default null, + party_id in parties.party_id%TYPE + default null, + postal_code in postal_addresses.postal_code%TYPE + default null, + postal_type in postal_addresses.postal_type%TYPE + default null, + region in postal_addresses.region%TYPE + default null, + creation_user 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, + security_inherit_p in acs_objects.security_inherit_p%TYPE + default 'f' + ) RETURN postal_addresses.address_id%TYPE; + + -- use del instead of delete to prevent 9i naming problems + PROCEDURE del ( + address_id in postal_addresses.address_id%TYPE + ); + + FUNCTION clone ( + address_id in postal_addresses.address_id%TYPE + ) RETURN postal_addresses.address_id%TYPE; + +END; +/ +show errors + +CREATE OR REPLACE PACKAGE BODY postal_address +AS + + FUNCTION new ( + additional_text in postal_addresses.additional_text%TYPE + default null, + address_id in postal_addresses.address_id%TYPE + default null, + country_code in postal_addresses.country_code%TYPE, + delivery_address in postal_addresses.delivery_address%TYPE, + municipality in postal_addresses.municipality%TYPE + default null, + party_id in parties.party_id%TYPE + default null, + postal_code in postal_addresses.postal_code%TYPE + default null, + postal_type in postal_addresses.postal_type%TYPE + default null, + region in postal_addresses.region%TYPE + default null, + creation_user 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, + security_inherit_p in acs_objects.security_inherit_p%TYPE + default 'f' + ) RETURN postal_addresses.address_id%TYPE IS + -- local vars + v_address_id postal_addresses.address_id%TYPE; + BEGIN + v_address_id := acs_object.new ( + null, + 'postal_address', + sysdate, + creation_user, + creation_ip, + context_id + ); + + update acs_objects set security_inherit_p = security_inherit_p + where object_id = v_address_id; + + insert into postal_addresses ( + additional_text, + address_id, + country_code, + delivery_address, + municipality, + party_id, + postal_code, + postal_type, + region + ) + values ( + additional_text, + v_address_id, + country_code, + delivery_address, + municipality, + party_id, + postal_code, + postal_type, + region + ); + + IF creation_user IS NOT NULL THEN + acs_permission.grant_permission ( + v_address_id, + creation_user, + 'admin' + ); + END IF; + + return v_address_id; + + END new; + + PROCEDURE del ( + address_id in postal_addresses.address_id%TYPE + ) IS + BEGIN + + delete from acs_permissions + where object_id = del.address_id; + + delete from postal_addresses + where address_id = del.address_id; + + acs_object.del(del.address_id); + + END del; + + FUNCTION clone ( + address_id in postal_addresses.address_id%TYPE + ) RETURN postal_addresses.address_id%TYPE IS + v_address_id postal_addresses.address_id%TYPE; + BEGIN + IF clone.address_id IS NULL THEN RETURN null; END IF; + + FOR x IN + (SELECT * FROM postal_addresses + INNER JOIN acs_objects ON object_id = address_id + WHERE address_id = clone.address_id) + LOOP + v_address_id := postal_address.new( + additional_text => x.additional_text, + country_code => x.country_code, + delivery_address => x.delivery_address, + municipality => x.municipality, + party_id => x.party_id, + postal_code => x.postal_code, + postal_type => x.postal_type, + region => x.region, + creation_user => x.creation_user, + creation_ip => x.creation_ip, + context_id => x.context_id + ); + END LOOP; + + RETURN v_address_id; + END clone; + +END postal_address; +/ +show errors