Index: openacs-4/packages/ams/tcl/ams-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ams/tcl/ams-procs-postgresql.xql,v diff -u -r1.7 -r1.8 --- openacs-4/packages/ams/tcl/ams-procs-postgresql.xql 27 Feb 2005 17:07:17 -0000 1.7 +++ openacs-4/packages/ams/tcl/ams-procs-postgresql.xql 18 May 2005 17:11:48 -0000 1.8 @@ -1,748 +1,87 @@ - - -postgresql7.2 - - - - select ams_attribute_id, required_p - from ams_list_attribute_map - where list_id = :list_id - order by sort_order - - - - - - select ams_object_id(:object_id) - - - - - - select ams_object__new( - :object_id, - :package_id, - now(), - :creation_user, - :creation_ip - ); - - - - - - select ams_option__new (:ams_attribute_id,:option,:sort_order) - - - - - - select ams_option__delete (:option_id) - - - - - - - select ams_option__map (:option_map_id,:option_id) - - - - - - select ams.*, - acs.attribute_name, - acs.pretty_name, - acs.pretty_plural, - acs.object_type, - aw.storage_type - from ams_attributes ams, - acs_attributes acs, - ams_widgets aw - where ams.ams_attribute_id = :ams_attribute_id - and ams.attribute_id = acs.attribute_id - and ams.widget_name = aw.widget_name - - - - - - select ac.attribute_name, - ac.pretty_name, - ac.object_type, - aw.widget, - aw.datatype, - aw.parameters, - aw.storage_type - from ams_attributes aa, - acs_attributes ac, - ams_widgets aw - where aa.ams_attribute_id = :ams_attribute_id - and aa.attribute_id = ac.attribute_id - and aa.widget_name = aw.widget_name - - - - - - select option, option_id - from ams_options - where ams_attribute_id = :ams_attribute_id - order by sort_order - - - - - - select '1' from acs_attributes where object_type = :object_type and attribute_name = :attribute_name - - - - - - select ams.ams_attribute_id - from ams_attributes ams, acs_attributes acs - where acs.object_type = :object_type - and acs.attribute_name = :attribute_name - and acs.attribute_id = ams.attribute_id - - - - - - select ams_attribute__name (:ams_attribute_id) - - - - - - select aw.storage_type - from ams_widgets aw, ams_attributes aa - where aa.ams_attribute_id = :ams_attribute_id - and aw.widget_name = aa.widget_name - - - - - - select ams_attribute__delete (:ams_attribute_id) - - - - - - select aav.*, - ao.object_id, - ams_attribute__options_string(option_map_id) as options_string, - ams_attribute__postal_address_string(address_id) as address_string, - ams_attribute__telecom_number_string(number_id) as telecom_number_string - from ams_attribute_values aav, cr_revisions cr, ams_objects ao - where ao.object_id in ($sql_object_id_list) - and ao.ams_object_id = cr.item_id - and cr.revision_id = aav.revision_id - and aav.superseed_revision_id is null - order by ao.object_id, aav.ams_attribute_id - - - - - - select ams_attribute_value__new ( - :revision_id, - :ams_attribute_id, - :option_map_id, - :address_id, - :number_id, - :time, - :value, - :value_mime_type - ) - - - - - - - select ams_attribute_value__save ( - :revision_id, - :ams_attribute_id, - :option_map_id, - :address_id, - :number_id, - :time, - :value, - :value_mime_type - ) - - - - - - select acs_object__new ( - null, - 'telecom_number', - ( select creation_date from acs_objects where object_id = :revision_id ), - ( select creation_user from acs_objects where object_id = :revision_id ), - ( select creation_ip from acs_objects where object_id = :revision_id ), - :revision_id - ) - - - - - - insert into telecom_numbers ( - number_id, - itu_id, - national_number, - area_city_code, - subscriber_number, - extension, - sms_enabled_p, - best_contact_time, - location, - phone_type_id - ) values ( - :number_id, - :itu_id, - :national_number, - :area_city_code, - :subscriber_number, - :extension, - :sms_enabled_p, - :best_contact_time, - :location, - :phone_type_id - ) - - - - - - select acs_object__new ( - null, - 'postal_address', - ( select creation_date from acs_objects where object_id = :revision_id ), - ( select creation_user from acs_objects where object_id = :revision_id ), - ( select creation_ip from acs_objects where object_id = :revision_id ), - :revision_id - ) - - - - - - insert into postal_addresses ( - address_id, - delivery_address, - municipality, - region, - postal_code, - country_code, - additional_text, - postal_type - ) values ( - :address_id, - :delivery_address, - :municipality, - :region, - :postal_code, - :country_code, - :additional_text, - :postal_type - ) - - - - - - insert into ams_attribute_values - (revision_id,ams_attribute_id,option_map_id,address_id,number_id,time,value,value_mime_type) - values - (:revision_id,:ams_attribute_id,:option_map_id,:address_id,:number_id,:time,:value,:value_mime_type) - - - - - - update ams_attribute_values - set superseed_revision_id = :revision_id - where ams_attribute_id = :ams_attribute_id - and superseed_revision_id is null - and revision_id in ( select revision_id - from cr_revisions - where item_id = :ams_object_id - and revision_id <> :revision_id ) - - - - - - select * - from ams_lists - where list_id = :list_id - - - - - - - select ams_attribute_id - from ams_list_attribute_map - where list_id = :list_id - - - - - - - select '1' - from ams_lists - where package_key = :package_key - and object_type = :object_type - and list_name = :list_name - - - - - - select list_id - from ams_lists - where package_key = :package_key - and object_type = :object_type - and list_name = :list_name - - - - - - - select ams_list__attribute_map ( - :list_id, - :ams_attribute_id, - :sort_order, - :required_p, - :section_heading - ) - - - - - - select sort_order - from ams_list_attribute_map - where list_id = :list_id - order by sort_order desc - limit 1 - - - - - - delete from ams_list_attribute_map - where list_id = :list_id - and ams_attribute_id = :ams_attribute_id - - - - - - update ams_list_attribute_map - set required_p = 't' - where list_id = :list_id - and ams_attribute_id = :ams_attribute_id - - - - - - update ams_list_attribute_map - set required_p = 'f' - where list_id = :list_id - and ams_attribute_id = :ams_attribute_id - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - select * from postal_addresses where address_id = :address_id - - - - - - - select telecom_number__new ( - :area_city_code, - :best_contact_time, - :extension, - :itu_id, - :location, - :national_number, - null, - null, - null, - :sms_enabled_p, - :subscriber_number, - :creation_user, - :creation_ip, - null - ) - - - - - - - select * from telecom_numbers where number_id = :number_id - - - - - - - select * - from contact_attributes ca, - contact_widgets cw, - contact_attribute_object_map caom, - contact_attribute_names can - where caom.object_id = :object_id - and ca.ams_attribute_id = can.ams_attribute_id - and can.locale = :locale - and caom.ams_attribute_id = ca.ams_attribute_id - and ca.widget_id = cw.widget_id - and not ca.depreciated_p - and acs_permission__permission_p(ca.ams_attribute_id,:user_id,'write') - order by caom.sort_order - - - - - - - - select ca.ams_attribute_id, - ca.attribute, - cav.option_map_id, - cav.address_id, - cav.number_id, - to_char(cav.time,'YYYY MM DD') as time, - cav.value, - cav.value_format, - cw.storage_column - from contact_attributes ca, - contact_widgets cw, - contact_attribute_object_map caom left join - ( select * - from contact_attribute_values - where party_id = :party_id - and not deleted_p ) cav - on (caom.ams_attribute_id = cav.ams_attribute_id) - where caom.object_id = '$object_id' - and caom.ams_attribute_id = ca.ams_attribute_id - and ca.widget_id = cw.widget_id - and not ca.depreciated_p - and ( - cav.option_map_id is not null - or cav.address_id is not null - or cav.number_id is not null - or cav.value is not null - or cav.time is not null - or ca.attribute in ($custom_field_sql_list) - ) - and acs_permission__permission_p(ca.ams_attribute_id,'$user_id','$permission') - order by caom.sort_order - - - - - - - select name - from organizations - where organization_id = :party_id - - - - - - - select legal_name - from organizations - where organization_id = :party_id - - - - - - - select reg_number - from organizations - where organization_id = :party_id - - - - - - - select first_names - from persons - where person_id = :party_id - - - - - - - select cao.option_id, cao.option - from contact_attribute_options cao, - organization_types ot, - organization_type_map otm - where cao.option = ot.type - and cao.ams_attribute_id = :ams_attribute_id - and otm.organization_type_id = ot.organization_type_id - and otm.organization_id = :party_id - - - - - - - select first_names - from persons - where person_id = :party_id - - - - - - - select last_name - from persons - where person_id = :party_id - - - - - - - select email - from parties - where party_id = :party_id - - - - - - - select url - from parties - where party_id = :party_id - - - - - - - select cao.option, cao.option_id - from contact_attribute_options cao, - contact_attribute_option_map caom - where caom.option_id = cao.option_id - and caom.option_map_id = :option_map_id - - - - - - select * - from contact_attributes ca, - contact_widgets cw, - contact_attribute_object_map caom, - contact_attribute_names can - where caom.object_id = :object_id - and ca.ams_attribute_id = can.ams_attribute_id - and can.locale = :locale - and caom.ams_attribute_id = ca.ams_attribute_id - and ca.widget_id = cw.widget_id - and not ca.depreciated_p - and acs_permission__permission_p(ca.ams_attribute_id,:user_id,'write') - order by caom.sort_order - - - - - - - select cav.address_id as old_address_id - from contact_attribute_values cav, - postal_addresses pa - where cav.party_id = :party_id - and cav.ams_attribute_id = :ams_attribute_id - and not cav.deleted_p - and cav.address_id = pa.address_id - and pa.delivery_address = :delivery_address - and pa.municipality = :municipality - and pa.region = :region - and pa.postal_code = :postal_code - and pa.country_code = :country_code - - - - - - - select cav.number_id as old_number_id - from contact_attribute_values cav, - telecom_numbers tn - where cav.party_id = :party_id - and cav.ams_attribute_id = :ams_attribute_id - and not cav.deleted_p - and cav.number_id = tn.number_id - and tn.subscriber_number = :attribute_value_temp - - - - - - - select option_map_id - from contact_attribute_values - where party_id = :party_id - and ams_attribute_id = :ams_attribute_id and not deleted_p - - - - - - - select option_id - from contact_attribute_option_map - where option_map_id = :option_map_id - - - - - - - select nextval('contact_attribute_option_map_id_seq') as option_map_id - - - - - - - insert into contact_attribute_option_map - (option_map_id,party_id,option_id) - values - (:option_map_id,:party_id,:option_id) - - - - - - - update parties set email = :attribute_value_temp where party_id = :party_id - - - - - - - update parties set url = :attribute_value_temp where party_id = :party_id - - - - - - - update organizations set name = :attribute_value_temp where organization_id = :party_id - - - - - - - update organizations set legal_name = :attribute_value_temp where organization_id = :party_id - - - - - - - update organizations set reg_number = :attribute_value_temp where organization_id = :party_id - - - - - - - delete from organization_type_map where organization_id = :party_id - - - - - - - select organization_type_id - from contact_attribute_options cao, - organization_types ot - where cao.option = ot.type - and cao.option_id = :option_id - - - - - - - insert into organization_type_map - (organization_id, organization_type_id) - values - (:party_id, :organization_type_id) - - - - - - - update persons set first_names = :attribute_value_temp where person_id = :party_id - - - - - - - update persons set last_name = :attribute_value_temp where person_id = :party_id - - - - - + + + + + + select acs_attribute__create_attribute ( + :object_type, + :attribute_name, + :datatype, + :pretty_name, + :pretty_plural, + :table_name, + :column_name, + :default_value, + :min_n_values, + :max_n_values, + :sort_order, + :storage, + :static_p + ) + + + + + + select attribute_id + from acs_attributes + where object_type = :object_type + and attribute_name = :attribute_name + + + + + + select ams_attribute_value__save ( + :object_id, + :attribute_id, + :value_id + ) + + + + + + select alam.attribute_id, + alam.required_p, + alam.section_heading, + aa.attribute_name, + aa.pretty_name, + aa.widget + from ams_list_attribute_map alam, + ams_attributes aa + where alam.attribute_id = aa.attribute_id + and alam.list_id = :list_id + order by alam.sort_order + + + + + + select alam.attribute_id, + alam.required_p, + alam.section_heading, + aa.attribute_name, + aa.pretty_name, + aa.widget + from ams_list_attribute_map alam, + ams_attributes aa + where alam.attribute_id = aa.attribute_id + and alam.list_id = :list_id + order by alam.sort_order + + + + + + select aav.*, aa.attribute_name, aa.widget, aa.pretty_name, + ams_attribute_value__value(aav.attribute_id,aav.value_id) as value + from ams_attribute_values aav, + ams_attributes aa + where aav.object_id = :object_id + and aav.attribute_id = aa.attribute_id + and aa.attribute_id in ( select attribute_id from ams_list_attribute_map where list_id = :list_id ) + + + +