--
-- contact-manager.sql
--
-- defined by philg@mit.edu on March 31, 1999
--
-- a generic, albeit somewhat wimpy, contact manager 
--

-- we use "contact_" as a table prefix so as not to get confused
-- with any content management systems whose tables would be prefixed
-- "cm_"

-- create the user_group for the contact manager 

create sequence contact_event_id_sequence;

-- this table points into some other table in the system but we 
-- can't say which one in advance

create table contact_events (
	contact_event_id	integer primary key,
	other_table_key		varchar(700) not null,
	event_date		datetime not null,
	user_id			integer not null references users,
	contactee_name		varchar(200),
	contactee_email		varchar(100),
	note			varchar(4000)
);

-- we need a table for more structured info (acquired if the user 
-- presses particular buttons in the /contact-manager/ directory)

-- event_type could be 'not_worth_contacting' or 'success'

create table contact_structured_events (
	contact_event_id	integer primary key,
	other_table_key		varchar(700) not null,
	user_id			integer not null references users,
	event_date		datetime not null,
	event_type		varchar(100) not null
);

-- build an Intermedia index on this table using a USER_DATASTORE (PL/SQL proc
-- that will combine the contactee_name and _email with the note); this works
-- in Oracle 8.1.5 or newer -- **** it does not work in regular Oracle 8.0 ****

-- note that we don't put this into the site-wide index because it is so separate; 
-- if you were a sales person searching for a contact name, you wouldn't want to
-- wade through public content.  Nor would people searching public content ever
-- be sent here; this contact information simply isn't part of the site content!

-- **** this procedure must be owned by CTXSYS! *****

-- conn ctxsys/ctxsyspassword
-- create or replace procedure contact_events_index_proc 
-- ( nextrow IN ROWID, nextclob IN OUT CLOB )
-- IS
--  event_record    contact_events%ROWTYPE;
-- BEGIN
--  select * into event_record from contact_events where rowid = nextrow;
--  dbms_lob.writeappend(v_nextclob, length(event_record.contactee_name), event_record.contactee_name);
-- END contact_events_index_proc;
-- /
-- show errors 

-- **** then you have to make it excecutable by the regular Web server user! *****

-- conn realuser/realuserpassword
-- grant execute on contact_events_index_proc to realuser;