JavaBlog.fr / Java.lu Database,Documentum,DQL DCTM Documentum : Repeating and Single Value Attributes in Database, Tables, Views, Aspects, i_position

Documentum : Repeating and Single Value Attributes in Database, Tables, Views, Aspects, i_position

Hello,

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.

 

TABLES
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:

 


 
VIEWS
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 :
https://community.emc.com/servlet/JiveServlet/previewBody/1269-102-1-1396/DocumentumContentServer6ORDiagram.pdf

That’s all!!!

Huseyin OZVEREN

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.

Related Post