Forum OpenACS Q&A: trying to understand the acs_attributes table... what are sort_order, static_

Hello, I am trying to write a package that will use acs_attributes extensivelly, and was wondering a few things about the acs_attributes table:

• what is "sort_order" used for?
• I think i know why if they are "unique key" all the values in my table "1" - is it because sort order only needs to be unique in relation to attribute_id - is this correct? Once i know what sort_order is used for i will probably know why this constraint exists
• Also, what is the intended purpose of the static_p field?
• Am i correct in assuming that i can have a repeated pretty_name as long as it belongs to an object other than the one i am creating for my package?

so you don't have to go check the code, here is the data model in postgres, and under it is the code creating this data model:

openacs-dev=# d acs_attributes;
                      Table "acs_attributes"
     Column     |          Type          |        Modifiers        
----------------+------------------------+-------------------------
 attribute_id   | integer                | not null
 object_type    | character varying(100) | not null
 table_name     | character varying(30)  | 
 attribute_name | character varying(100) | not null
 pretty_name    | character varying(100) | not null
 pretty_plural  | character varying(100) | 
 sort_order     | integer                | not null
 datatype       | character varying(50)  | not null
 default_value  | text                   | 
 min_n_values   | integer                | not null default 1
 max_n_values   | integer                | not null default 1
 storage        | character varying(13)  | default 'type_specific'
 static_p       | boolean                | default 'f'
 column_name    | character varying(30)  | 
Indexes: acs_attrs_datatype_idx,
         acs_attrs_obj_type_idx,
         acs_attrs_tbl_name_idx
Primary key: acs_attributes_pk
Unique keys: acs_attributes_attr_name_un,
             acs_attributes_pretty_name_un,
             acs_attributes_sort_order_un
Check constraints: "acs_attributes_min_n_ck" (min_n_values >= 0)
                   "acs_attributes_max_n_ck" (max_n_values >= 0)
                   "acs_attributes_storage_ck" ((storage 
= 'type_specific'::"varchar") OR (storage = 'generic'::"varchar"))
                   "acs_attributes_n_values_ck" (min_n_values <= 
max_n_values)
Triggers: RI_ConstraintTrigger_137852,
          RI_ConstraintTrigger_137858,
          RI_ConstraintTrigger_137864,
          RI_ConstraintTrigger_137880,
          RI_ConstraintTrigger_137882,
          RI_ConstraintTrigger_137899,
          RI_ConstraintTrigger_137901,
          RI_ConstraintTrigger_138010,
          RI_ConstraintTrigger_138012,
          RI_ConstraintTrigger_138029,
          RI_ConstraintTrigger_138031,
          RI_ConstraintTrigger_143102,
          RI_ConstraintTrigger_143104,
          RI_ConstraintTrigger_143448,
          RI_ConstraintTrigger_143450

And here is the code to create the table

create table acs_attributes (
        attribute_id    integer not null
                        constraint acs_attributes_pk
                        primary key,
        object_type     varchar(100) not null
                        constraint acs_attributes_object_type_fk
                        references acs_object_types (object_type),
        table_name      varchar(30),
        constraint acs_attrs_obj_type_tbl_name_fk
        foreign key (object_type, table_name) 
        references acs_object_type_tables,
        attribute_name  varchar(100) not null,
        pretty_name     varchar(100) not null,
        pretty_plural   varchar(100),
        sort_order      integer not null,
        datatype        varchar(50) not null
                        constraint acs_attributes_datatype_fk
                        references acs_datatypes (datatype),
        default_value   text,
        min_n_values    integer default 1 not null
                        constraint acs_attributes_min_n_ck
                        check (min_n_values >= 0),
        max_n_values    integer default 1 not null
                        constraint acs_attributes_max_n_ck
                        check (max_n_values >= 0),
        storage         varchar(13) default 'type_specific'
                        constraint acs_attributes_storage_ck
                        check (storage in ('type_specific',
                                           'generic')),
        static_p        boolean default 'f',
        column_name     varchar(30),
        constraint acs_attributes_attr_name_un
        unique (attribute_name, object_type),
        constraint acs_attributes_pretty_name_un
        unique (pretty_name, object_type),
        constraint acs_attributes_sort_order_un
        unique (attribute_id, sort_order),
        constraint acs_attributes_n_values_ck
        check (min_n_values <= max_n_values)
);

Thanks for the help

Sort order is used for displaying the attributes of a particular object_type in a predetermined order.

static_p indicates whether the attribute is common to all instances of a particular object_type or whether it can be different for each instance.  If static_p is true, then all instances of the object_type will have the same value for the attribute.

The pretty_name only needs to be unique for a particular object_type.  For instance if you had object types foo and bar, they could both have an attribute with a pretty name of "Baz".

Thanks for the responses... i agree that it should be unique to attribute_id, not object_id... but the code listed above was pulled directly from the postgresql /packages/acs-kernel/sql/postgresql/acs-metadata-create.sql file - which came directly from the openacs4.5 tarball. To me it would have made more sense to have sort_order be a unique constraint in relation to the object_type - just as the two of you mentioned - hence the confusion. When pegged to attribute_id the constraint becomes irrelevent because you can't have two attributes of the same name for the same object.

You see this is what i don't understand, and this is more conceptual than anything else, it is why there isn't a generic way of storing data other than in text fields. I am trying to track user_profiles... and by this i mean home address, work address, phone numbers, birth date, number of children etc. you name it. This data is generic in nature (i.e. not dependent on a particular application), much like the biographies are part of generic acs_attributes and specific to a particular person, so it should be part of the object "person" (just as biographies are). So i was going to create a table like this...

create table userprofiles (
        object_id       integer not null
                        constraint acs_attr_values_obj_id_fk
                        references acs_objects (object_id) on delete cascade,
        attribute_id    integer not null
                        constraint acs_attr_values_attr_id_fk
                        references acs_attributes (attribute_id),
        attr_value_boolean      boolean,
        attr_value_varchar200   varchar(200),
        attr_value_varchar4000  varchar(4000),
        attr_value_text         text,
        attr_value_date         timestamp,
        attr_value_numeric      numeric,
        constraint userprofiles_boolean_pk primary key
        (object_id, attribute_id)
);

Which is essentially the same thing as the generic acs_attribute_values table... except that it allows me to choose the type of field i want to stuff the data in via the column_name field for that attribute, and create functions to enter it into the correct store.... what do you think? Would it make more sense to just base this information on the generic acs_attributes_values table and do an alter table to add the varchar, timestamp, integer and boolean columns?

I wouldn't recommend hacking up the core data-model, as it will just make upgrading a major hassle.

The acs_attributes mechanism already does what you want, so adding another table that duplicates acs_attribute_values serves no purpose.  The fact that the attribute values are stored as strings shouldn't present a problem, since the type information is stored in acs_attributes and 'text' types can be cast to any other type.

What kind of limitation are you seeing with the current setup?

I would like to follow this topic of acs_attributes and values kept in acs_attribute_values

How does one selects attr_values for an object_type without performing multiple SELECTs to get each Attribute_name => attr_value pair?