--
-- packages/ticket-tracker/sql/category-create.sql
--
-- 
-- @author Tony Tseng (tony@arsidigta.com)
-- @author Phong Nguyen (phong@arsdigita.com)
-- 
-- @creation-date 2000-11-16
--
-- @cvs-id $Id: category-create.sql,v 1.1 2001/04/20 20:51:25 donb Exp $
--

----------------------------------
-- KNOWLEDGE LEVEL OBJECTS
----------------------------------
declare
    attr_id		acs_attributes.attribute_id%TYPE;
begin
    acs_object_type.create_type (
        object_type   => 'ttracker_category',
        pretty_name   => 'Ticket Tracker Category',
        pretty_plural => 'Ticket Tracker Categories',
        supertype     => 'acs_object',
        table_name    => 'ttracker_categories',
        id_column     => 'category_id',
        package_name  => 'ttracker_category',
        name_method   => 'ttracker_category.name'
    );  
  
    attr_id := acs_attribute.create_attribute (
        object_type    => 'ttracker_category',
        attribute_name => 'name',
        datatype       => 'string',
        pretty_name    => 'Name',
        pretty_plural  => 'Names'
    );

    attr_id := acs_attribute.create_attribute (
        object_type    => 'ttracker_category',
        attribute_name => 'description',
        datatype       => 'string',
        pretty_name    => 'Description',
        pretty_plural  => 'Descriptions'
    );
end;
/
show errors

----------------------------------
-- OPERATIONAL LEVEL
----------------------------------

create table ttracker_categories (
    category_id		integer
			constraint ttracker_categories_pk
			primary key
			constraint ttracker_categories_fk	
			references acs_objects(object_id) on delete cascade,
    package_id          constraint ttkr_cat_package_id_fk
                        references apm_packages (package_id) on delete cascade
                        constraint ttkr_cat_package_id_nn
                        not null,
    name                varchar2(100) 
			constraint ttracker_categories_name_nn
			not null,
    description		varchar2(4000),
    default_assignee    constraint ttracker_categories_assgn_fk
                        references parties(party_id)      
);

-- to prevent ttracker_categories from getting locked when persons table is being updated
create index ttkr_cat_assignee_idx on ttracker_categories (default_assignee);

-- to speed up duplication check
create index ttkr_cat_upper_name_idx on ttracker_categories upper(name);

-- people often query ttracker_categories to find out the categories within a package
create unique index ttkr_cat_pkg_name_cat_idx on ttracker_categories (package_id, name, category_id);

comment on table ttracker_categories is '
    This table holds information about ticket tracker categories
';
comment on column ttracker_categories.package_id is '
    The package instance that contains this category.
    Right now it is set as "on delete cascade"  
    so that we can delete the instance of the package right after we mount it 
';
comment on column ttracker_categories.name is '
    The name of the category
';
comment on column ttracker_categories.description is '
    The detailed description of the category
';
comment on column ttracker_categories.default_assignee is '
    The default assignee of this category. 
';