-- Classified Ads Package -- -- @author Deds Castillo (deds@infiniteinfo.com) -- @creation-date 2002-10-08 -- @cvs-id $Id: attributes-package-create.sql,v 1.5 2003/07/31 01:24:31 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,keyword_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_keyword_id alias for $11; v_attribute_id acs_attributes.attribute_id%TYPE; v_sort_order acs_attributes.sort_order%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 keyword_id insert into ca_attributes (attribute_id, keyword_id) values (v_attribute_id, p_keyword_id); 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,html_options,extra_options,select_options,validate,optional_p,enabled_p'); create or replace function ca_attributes__register_widget (integer,varchar,varchar,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_html_options alias for $4; p_extra_options alias for $5; p_select_options alias for $6; p_validate alias for $7; p_optional_p alias for $8; p_enabled_p alias for $9; 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, html_options, extra_options, select_options, validate, optional_p, enabled_p ) values ( p_attribute_id, p_widget, p_label, 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';