Forum OpenACS Development: AMS storing attributes in tables

Collapse
Posted by Malte Sussdorff on
One of our requirements for contacts (and therefore AMS) is the ability to access and store information in seperate tables that are already in existence and used by other packages to store and retrieve data. In other words, there exists a table called im_companies that stores company information in the P/O system. As we are using parts of the system, we would like to store the entries made in Contacts (about the company) within this table, at least for the attributes that are contained there.

Matthew's first idea was:

> I will assume you are tring to make the following part of AMS:
>
> # select * from pos_table;
> po_id | amount | transaction_type
> -------+--------+------------------
> 123 | $145 | PURCHASING
>
> You would create a proc ams::widget::po that when called on via a
> request for ad_form_widget returns two elements a curreny widget and a
> dropdown with your transaction types (or a custom created
> template::element::widget - much like I created for postal_addresses).
> Then when AMS saves the data it would request the ams::widget::po proc
> to "form_save_value" where your ams::widget::po proc would insert the
> data into your pos_table and return the po_id, which AMS would insert
> into the ams_attribute_values table. Then when requesting the
> ams_attribute_values it would reference the ams_value__method specified
> by the ams::widget::po proc.
>
> I hope this explains how to extend AMS to deal with other data types and
> have it use generic storage for certain widget types.

Though this would work if we create a im_customer widget, it does not provide enough flexibility as all fields within the widget would be fixed.

After some discussion the second idea from Matthew is as follows:

++++++++++++

For text, numeric and date attributes (i.e. attributes that are one
column in a table - richtext, postal_address, telecom_number, etc would
not work sicne they are multiple columns so thats WAY trickier), I would
do the following. At information value saving time ams::wiget::${type}
-request "form_value_save", i would still store the info generically
(i.e. leave it as it is for faster information retrieval), BUT I would
also do the following:

1 - check if the attribute is type specific (generic ones get stored
only in AMS tables)
2 - if the attribute is type specific see if you can figure out the
table and column that that attribute represents from info in the
acs_attributes and acs_object_types tables.
3 - if that column actually exists (we need a way of checking for it and
not throwing an error if it doesn't since lots of attributes aren't set
up to do this exactly) we updated that table where the object_id
supplied to ams is the id_column of that object type in teh
acs_object_types table. Again, if there is an error we can't barf.

Now, this would work for "normal" objects, such as normal person objects
without contacts, but because contacts makes person objects into
cr_items. So without a cr_itemized person it would look at the
acs_objects table and see that the table is "persons" and the id_column
is "person_id" and then it would check for the first_names column in the
persons table (since the column is supposed to be the same as the
attribute name) and if that column exists update that table for the
object_id given. BUT with cr_itemzed person objects we don't pass the
person_id to ams, we pass the party_revision_id, so I don't know how we
would do that. We could check if the object_id passed to ams has a value
in the object_type specific table, and if not check object_id is in fact
a revision_id and get the item_id form that revision and recheck that
new item_id on the object_type specific table - but this might be too
slow, and i'm not sure if its making too many assumptions about how AMS
is to deal with type specific storage.

+++++++++++

Sadly this still does not help with the fact that we need to see changes made by P/O to the im_companies table, so we definitely need a retrieval query to get the elements from this table. Furthermore, AMS currently only works with the attribute name, but if I want to query acs-attributes I need the object type as well where the object type is different from the "person" or "organization" object type in use by contacts.

Any ideas how to best proceed from here ?