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 )
+
+
+
+