-- Classified Ads Package
--
-- @author Deds Castillo (deds@infiniteinfo.com)
-- @creation-date 2002-10-08
-- @cvs-id $Id: attributes-package-create.sql,v 1.3 2003/07/27 00:46:16 rmello Exp $
--

select define_function_args('ca_attributes__create_attribute','content_type,attribute_name,datatype,pretty_name,pretty_plural,sort_order,default_value,storage,table_name,column_spec,package_id');

create or replace function ca_attributes__create_attribute (varchar,varchar,varchar,varchar,varchar,integer,varchar,varchar,varchar,varchar,integer)
returns integer as '
declare
    p_content_type                  alias for $1;
    p_attribute_name                alias for $2;
    p_datatype                      alias for $3;
    p_pretty_name                   alias for $4;
    p_pretty_plural                 alias for $5;
    p_sort_order                    alias for $6;
    p_default_value                 alias for $7;
    p_storage                       alias for $8;
    p_table_name                    alias for $9;
    p_column_spec                   alias for $10;
    p_package_id                    alias for $11;
    v_attribute_id                  acs_attributes.attribute_id%TYPE;
    v_sort_order                    acs_attributes.sort_order%TYPE;
    v_sitewide_p                    ca_attributes.sitewide_p%TYPE;
    v_column_exists                 boolean;
begin

-- if storage is type_specific then we should always have a column_spec
-- if table_name is provided as well then we assume that the user
-- wants to use a combo of acs_attributes and table_name and not cr

    if p_storage = ''type_specific'' and
       p_column_spec is null then
     
        raise EXCEPTION ''-20000:  Type_specific attributes require a colum_spec parameter'';

    end if;

    if p_sort_order is null then
        select coalesce(max(sort_order), 0)+1 into v_sort_order
        from acs_attributes
        where object_type = p_content_type;
    else
        v_sort_order = p_sort_order;
    end if;

    if p_storage = ''generic'' then

        v_attribute_id := acs_attribute__create_attribute (
            p_content_type,             -- content_type
	        p_attribute_name,           -- attribute_name
	        p_datatype,                 -- datatype
	        p_pretty_name,              -- pretty_name
	        p_pretty_plural,            -- pretty_plural
            null,                       -- table_name
            null,                       -- column_name
	        p_default_value,            -- default_value
            1,                          -- min_n_values
            1,                          -- max_n_values
	        v_sort_order,               -- sort_order
            ''generic'',                -- storage
            ''f''                       -- static_p
    );

    else if p_storage = ''type_specific'' then
        if p_table_name is null then

-- table_name is null. so the user probably wants to use cr.
        
            v_attribute_id :=  content_type__create_attribute (
                p_content_type,             -- content_type
	            p_attribute_name,           -- attribute_name
	            p_datatype,                 -- datatype
	            p_pretty_name,              -- pretty_name
	            p_pretty_plural,            -- pretty_plural
	            v_sort_order,               -- sort_order
	            p_default_value,            -- default_value
	            p_column_spec               -- column_spec
            );

        else

-- user passed in a table_name. since acs_attributes do not
-- create columns automagically, we steal stuff from cr
-- to add the column before calling create_attribute

            select count(*) > 0 into v_column_exists 
            from pg_class c, pg_attribute a
            where c.relname::varchar = p_table_name
                  and c.oid = a.attrelid
                  and a.attname = lower(p_attribute_name);

            if NOT v_column_exists then
            execute ''alter table '' || p_table_name || '' add '' || 
                p_attribute_name || '' '' 
                || p_column_spec;
            end if;

            v_attribute_id := acs_attribute__create_attribute (
                p_content_type,             -- content_type
	            p_attribute_name,           -- attribute_name
	            p_datatype,                 -- datatype
	            p_pretty_name,              -- pretty_name
	            p_pretty_plural,            -- pretty_plural
                null,                       -- table_name
                null,                       -- column_name
	            p_default_value,            -- default_value
                1,                          -- min_n_values
                1,                          -- max_n_values
	            v_sort_order,               -- sort_order
                ''type_specific'',          -- storage
                ''f''                       -- static_p
            );

        end if;

    end if; end if;

-- insert the attribute_id into our own table for purposes
-- of tracking the package_id

    if p_package_id is null then
        v_sitewide_p := ''t'';
    else
        v_sitewide_p := ''f'';
    end if;

    insert into ca_attributes
        (attribute_id, package_id, sitewide_p)
    values
        (v_attribute_id, p_package_id, v_sitewide_p);

    return v_attribute_id;
end;
' language 'plpgsql';


select define_function_args('ca_attributes__drop_attribute','content_type,attribute_name');

create or replace function ca_attributes__drop_attribute (varchar,varchar)
returns integer as '
declare
    p_content_type                  alias for $1;
    p_attribute_name                alias for $2;
    v_attribute_id                  ca_attributes.attribute_id%TYPE;
begin
-- get the attribute_id
    select attribute_id into v_attribute_id
    from acs_attributes
    where object_type = p_content_type and
          attribute_name = p_attribute_name;

-- delete all attributes of this id    
    delete from ca_attribute_widget_values
    where attribute_id = v_attribute_id;

    delete from ca_attribute_values
    where attribute_id = v_attribute_id;

    delete from ca_attributes
    where attribute_id = v_attribute_id;


-- is this object a subtype of content_revision
    if content_item__is_subclass(p_content_type, ''content_revision'') = ''t'' then
        PERFORM content_type__drop_attribute (
            p_content_type,
            p_attribute_name,
            ''f''
        );

    else

        PERFORM acs_attribute__drop_attribute (
            p_content_type,
            p_attribute_name
        );
    end if;

    return null;
end;
' language 'plpgsql';


select define_function_args('ca_attributes__register_widget','attribute_id,widget,label,keyword_id,html_options,extra_options,select_options,validate,optional_p,enabled_p');

create or replace function ca_attributes__register_widget (integer,varchar,varchar,integer,varchar,text,varchar,varchar,char,char)
returns integer as '
declare
    p_attribute_id                  alias for $1;
    p_widget                        alias for $2;
    p_label                         alias for $3;
    p_keyword_id                    alias for $4;
    p_html_options                  alias for $5;
    p_extra_options                 alias for $6;
    p_select_options                alias for $7;
    p_validate                      alias for $8;
    p_optional_p                    alias for $9;
    p_enabled_p                     alias for $10;
begin

    -- DEDS: FIXME - check for options when inserting something that does not support it
    --  RBM: 07/2003 - What does the above mean? 
     
    insert into ca_attribute_widget_values
        ( attribute_id, widget, label, keyword_id, html_options, extra_options, 
	  select_options, validate, optional_p, enabled_p )
    values
        ( p_attribute_id, p_widget, p_label, p_keyword_id, p_html_options, p_extra_options,
	  p_select_options, p_validate, p_optional_p, p_enabled_p );

    return null;
end;
' language 'plpgsql';


select define_function_args('ca_attributes__unregister_widget','attribute_id');

create or replace function ca_attributes__unregister_widget (integer)
returns integer as '
declare
    p_attribute_id                  alias for $1;
begin
     
    delete from ca_attribute_widget_values
    where attribute_id = p_attribute_id;

    return null;
end;
' language 'plpgsql';


select define_function_args('ca_attributes__add_generic_value','object_id,attribute_id,attr_value');

create or replace function ca_attributes__add_generic_value (integer,integer,varchar)
returns integer as '
declare
    p_object_id                     alias for $1;
    p_attribute_id                  alias for $2;
    p_attr_value                    alias for $3;
begin
     
    insert into ca_attribute_values
        (object_id, attribute_id, attr_value)
    values
        (p_object_id, p_attribute_id, p_attr_value);

    return null;
end;
' language 'plpgsql';


select define_function_args('ca_attributes__edit_generic_value','object_id,attribute_id,attr_value');

create or replace function ca_attributes__edit_generic_value (integer,integer,varchar)
returns integer as '
declare
    p_object_id                     alias for $1;
    p_attribute_id                  alias for $2;
    p_attr_value                    alias for $3;
begin
     
    update ca_attribute_values
    set attr_value = p_attr_value
    where object_id = p_object_id and
          attribute_id = p_attribute_id;

    return null;
end;
' language 'plpgsql';




