This post is relating to repeating and single attributes in underlying database. Documentum objects support 2 types of attributes:
- Single Value Attributes
Single Value properties are properties that can hold a single value at a time like the attributes r_object_id, object_name,…etc.
- Repeating Attributes
Repeating Attributes are properties that can hold multiple values like the attributes Keywords, i_folder_id, r_aspect_name…etc.
All objects/properties/attributes are saved in underlying database. Depending of object in which the attribute is defined and its type (single/repeating), the attribute is stored either in table “object_type_s” (or single) or “object_type_r” (for repeating). For example, the attributes defined in the dm_sysobject type are stored in the tables dm_sysobject_s and dm_sysobject_r.
If my_huo_object has two custom single single_attr_1 and single_attr_2 and 2 repeating value attributes repeat_attr_1 and repeat_attr_2, then:
- the my_huo_object_s table will be the columns single_attr_1 and single_attr_2
- the my_huo_object_r table will be the columns repeat_attr_1 and repeat_attr_2
So, each single value attribute has single column in the “_s” table. However, the repeating value attribute with others important colmuns as i_position which determines the position of the value in repeating attribute to which it belongs.
For our previous example, the my_huo_object_r table will be the columns repeat_attr_1, repeat_attr_2 and i_postion.
In this example, the custom accelera_doc object type has the supertype, dm_document. Therefore, the following total set of database tables are used to store the properties of accelera_doc objects:
Concerning the views, each object type has 4 views named:
- _sv and _sp for all Single value attributes in conjunction with all super types
- _rv and _rp for all repeating value attributes in conjunction with all super types
The two _sp and _rp views are used by DQL and the two _sv and _rv views are used by the Object Manager. So, in case of requiring to query the table rather than the object use the _sp, _rp views rather than making your own joins between _s or _r tables up the inheritance hierarchy.
For example, the type dm_document has four views: dm_document_sp, dm_document_sv, dm_document_rp and dm_document_rv. If my_huo_object extends dm_document, its views (my_huo_object_sp, my_huo_object_sv, my_huo_object_rp and my_huo_object_rv) will have all the single/repeating attributes of dm_sysobject, dm_document and my_huo_object. Note : dm_document doesn’t have any single attribute or repeating attribute.
CALCULATE REPEATING VALUE POSITION
Some notes concerning th calculate of repeating’s index:
- i_position uses negative numbers for indexing. so, -1 is the first element, -2 is the second, and so on.
The expression “(i_position*-1)-1 as index” and “-(i_position+1) as sameindex” are equivalent.
- Example 1: for standard object
// Retrieve document id and index position where repeating attribute 'r_aspect_name' contains 'aspect_java_lu' # For repeating attribute describe dm_sysobject_r; r_object_id Char(16) i_position Integer i_partition Integer r_aspect_name Char(64) i_folder_id Char(16) authors Char(48) a_effective_date Time/Date a_effective_flag Char(8) a_effective_label Char(32) a_expiration_date Time/Date a_extended_properties Char(32) a_publish_formats Char(32) keywords Char(48) r_component_label Char(32) r_composite_id Char(16) r_composite_label Char(32) r_order_no Integer r_property_bag Char(2048) r_version_label Char(32) # For single attribute describe dm_sysobject_s; r_object_id Char(16) i_position Integer i_partition Integer acl_domain Char(255) acl_name Char(32) a_application_type Char(32) ... select r_object_id, r_aspect_name as my_aspect, (i_position*-1)-1 as index, -(i_position+1) as sameindex from dm_document where r_aspect_name = 'aspect_java_lu' enable (row_based); OR select r_object_id, r_aspect_name as my_aspect, (i_position*-1)-1 as index, -(i_position+1) as sameindex from dm_sysobject where r_aspect_name = 'aspect_java_lu' enable (row_based); OR select r_object_id, r_aspect_name as my_aspect, (i_position*-1)-1 as index, -(i_position+1) as sameindex from dm_sysobject_r where r_aspect_name = 'aspect_java_lu' enable (row_based); r_object_id my_aspect index sameindex 09xxxxxxxxxxxxxa aspect_java_lu 0 0 09xxxxxxxxxxxxxb aspect_java_lu 1 1 09xxxxxxxxxxxxxc aspect_java_lu 1 1 09xxxxxxxxxxxxxd aspect_java_lu 0 0
- Example 2: for the ASPECT
Where do Aspects Store Attribute Values? The dmc_aspect_type_s and dmc_aspect_type_r tables contains the definitions of the aspect. The i_attr_def column contains the name of the table that holds the attributes and values for the Aspect. select i_attr_def from dmc_aspect_type where object_name = 'my_aspect'; i_attr_def = dmi_03xxxxxxxxxxxc # For repeating attribute describe dm_dbo.dmi_03xxxxxxxxxxxc_r; r_object_id Char(16) i_position Integer i_partition Integer additionnal_approvers Char(50) contacts Char(50) ... # For single attribute describe dm_dbo.dmi_03xxxxxxxxxxxc_s; r_object_id Char(16) i_position Integer i_partition Integer afs_request_num Char(12) request_status Char(25) approved_rejected_date Time/Date ... # select r_object_id, contacts as my_contact, (i_position*-1)-1 as index, -(i_position+1) as sameindex from dm_dbo.dmi_03xxxxxxxxxxxc_r where contacts like 'Huseyin%' order by r_object_id, i_position desc; r_object_id my_contact index sameindex 09xxxxxxxxxxxxx9 Huseyin OZVEREN 1 1 09xxxxxxxxxxxxxa Huseyin Ozveren 3 3 09xxxxxxxxxxxxxe Huseyin Me 0 0
Below, the useful link to the Documentum 6 Object-Relational Model :