--
-- packages/ticket-tracker/sql/options-create.sql
--
-- 
-- @author Phong Nguyen (phong@arsdigita.com)
-- @author Tony Tseng (tony@arsidigta.com)
-- 
-- @creation-date 2000-12-15
--
-- @cvs-id $Id: options-create.sql,v 1.2 2001/07/09 16:18:59 vinodk Exp $
--
-- Ported to OpenACS - Vinod Kurup (vkurup@massmed.org)
--

create table ttracker_options (
    option_id        integer
                     constraint ttracker_options_pk
                     primary key,
    name             varchar(100)
                     constraint ttkr_options_name_nn
                     not null,
    value            integer
                     constraint ttkr_options_value_nn
                     not null,
    package_id       integer
                     constraint ttkr_options_package_id_fk
                     references apm_packages(package_id) on delete cascade,
    function         varchar(10)
                     constraint ttkr_options_function_nn
                     not null
                     constraint ttkr_options_function_ck
                     check (function in ('severity', 'priority')),
    constraint       ttkr_opt_pkg_fun_val_un unique(package_id, function, value),
    constraint	     ttkr_opt_pkg_fun_nam_un unique(package_id, function, name)
);

-- generating a sequence for the primary key
-- since we're not using acs_object
create sequence ttracker_option_id_seq;
create view ttracker_option_id_sequence as 
	   select nextval('ttracker_option_id_seq') as nextval;

comment on table ttracker_options is '
    A table to store priority/severity options.
    Basically just pairs of name/value.
';

comment on column ttracker_options.option_id is '
    The integer primary key.
';

comment on column ttracker_options.package_id is '
    The package instance that uses this option.
';

comment on column ttracker_options.name is '
    The user readable name of the priority/severity option.
';

comment on column ttracker_options.value is '
    The actual integer value of the priority/severity
';

comment on column ttracker_options.function is '
    The function of this entry. Either priority or severity
';