-- Photobook datamodel 
--
-- @author davis@xarg.net
-- @creation-date 2002-02-09
-- @cvs-id $Id: photobook-create.sql,v 1.2 2004/03/18 22:45:42 janines Exp $


-- Backing data to drive various select widgets.
-- see phb::options and phb::options_grouped functions for 
-- examples of how to use the data.

create table phb_backing_data (
       -- A string identifier for the datatype.
       data_type               varchar(20),
       -- The short name is the actual text for the 
       short_name              varchar(200),
       -- Group is used for nested selects (for example when chosing a university)
       group_name              varchar(100), 
       -- Sort order.
       seq                     integer default 0,
       --
       constraint phb_backing_data_un
       unique (data_type,group_name, short_name)
);

create index phb_backing_data_shname_idx on phb_backing_data(short_name);

comment on table phb_backing_data is '
    Used to drive select lists and such.
';

create sequence phb_backing_order_seq;

create table phb_privacy ( 
       user_id                 integer not null
                               constraint phb_privacy_user_id_pk primary key
                               constraint phb_privacy_user_id_fk
                               references users(user_id)        
                               on delete cascade, 
       accepted_p              char(1) default 'f' not null,
                               constraint phb_privacy_accepted_p_ck
                               check (accepted_p in ('t', 'f')),
       creation_user           integer,
       creation_date           date default sysdate not null,
       creation_ip             varchar2(50)
);

comment on table phb_privacy is '
        store an audit of the privacy disclaimer data.
';
                                         

create table sloan_email (
       user_id           integer references users(user_id)
                         constraint sloan_email_user_id_pk 
                         primary key,
       efl               varchar2(200) 
                         constraint sloan_email_efl_un
                         unique,
       alias             varchar2(200) 
                         constraint sloan_email_alias_un
                         unique,
       outside           varchar2(200) 
                         constraint sloan_email_outside_un
                         unique,
       last_updated      date 
);

comment on table sloan_email is '
Stores user email mappings.';
comment on column sloan_email.efl is '
Email For Life. Sloan email address which will remain
active for the life of the user.';

-- Set up the create_efl procedure
begin
        acs_privilege.create_privilege('sloan_create_efl');
end; 
/
show errors


-- Load the data for the select widgets.
@@ photobook-data                                                           
@@ photobook-data-universities