Feeds:
Posts
Comments

Posts Tagged ‘repeating attributes’

Few days back I had read the chapter on Aliases. Since then I was eager to play with them. Ultimately I got my game plan ready. It was not a full-fledged plan which could give me a result but it was good enough for an initiative. I like flexible plans so that I can make changes as and when I feel, according to my comfort. Okay, lets stop the gossip and let the game start.

Setting up the field:

I will be using ACL Template to play with Aliases. As I am using ACL Template, I will also be requiring a set of users and objects.

As a first step I use the following DQL query to create a user.

create dm_user object
set home_docbase = 'ASSAPArchive',
set user_os_domain = 'infch02088',
set user_name = 'TestUser1',
set user_os_nam e= 'TestUser1',
set user_source = 'inline password',
set user_password = 'TestUser1'

Similar DQL queries were used to create four more users TestUser2, TestUser3, TestUser4 and TestUser5.

I like users to be part of a group.

CREATE GROUP TestUserGroup
WITH MEMBERS
(SELECT user_name
FROM dm_user
WHERE user_name like 'TestUser%')

No need to worry; there are no additional users with similar name in my repository. Now I have a group named TestUserGroup and it has five members named TestUser1,…..TestUser5.

Till here it was pretty easy. Isn’t it? But the game has not started yet. I am just arranging the players at their respective positions.

Before I create the ACL Template, I will need the Alias Set with the Alias that has to be used in the ACL Template.

CREATE dm_alias_set OBJECT
SET object_name = 'TestUserAlias';
UPDATE dm_alias_set OBJECTS
APPEND alias_name = 'TestUser',
APPEND alias_value = 'TestUser1',
APPEND alias_category = 1,
APPEND alias_usr_category = 1,
APPEND alias_description = 'Testing alias in ACL Template'
WHERE object_name = 'TestUserAlias';

Now I can go ahead with the creation of ACL Template. ACL Template is not same as ACL. It is actually a kind of ACL where aliases are used in place of users/groups. When such an ACL is applied to an object at runtime, the content server resolves the alias and creates a custom ACL with the resolved users/groups and the permissions in the ACL Template. ACL Template can be recognized as objects of dm_acl with acl_class = 1.

Below is the screen shot for the ACL Template created through Application Builder.newacltemplate_1

The field is set now.

Users : TestUser1, TestUser2, TestUser3, TestUser4, TestUser5.

Group : TestUserGroup

Alias Set Name : TestUserAlias

Alias Name : TestUser

ACL Template : TestACLTemplate

The Game Begins:

Let me go ahead with objects (of type dm_document) creation and apply the Template ACL to them.

CREATE dm_document object
SET object_name = 'TestObject1',
SET acl_name = 'TestACLTemplate'
SET acl_domain = 'ASSAPArchive'
LINK '/dmadmin'

FOUL!!!!!!!!!!!

[DM_QUERY_F_UP_SAVE]fatal:  “UPDATE:  An error has occurred during a save operation.”

[DM_POLICY_E_AS_NO_ALIAS_SET_USER]error:  “No default alias set found for user dmadmin. The following alias sets were searched: sessionconfig, user, user’s default group, server config, and policy (if applicable)”

Let me associate the Alias Set with the user, else there is no way the Alias can be resolved.

UPDATE dm_user OBJECT
SET alias_set_id =
(SELECT r_object_id
FROM dm_alias_set
WHERE object_name = 'TestUserAlias')
WHERE user_name = 'dmadmin'

The Alias Set is associated with the user. Once again I use the previously used DQL query for creating a dm_document object and applying the ACL Template to it. This time I was successful.

Lets check the permissions in the property page of the newly created object. A new custom ACL named dm_450003f080001517_80001100 is created and assigned to the object. It should be noted that the r_object_id of the ACL Template is 450003f080001517. Below is the screen shot of the property page. The new ACL has the resolved user name from the Alias Set.

testuser1permission_11

Second half:

The first part of the game is over. Now I need to change the user in the Alias set and check whether the change is reflected in the permissions of the new object.

UPDATE dm_alias_set OBJECT
SET alias_value[0] = 'TestUser2'
WHERE object_name = 'TestUserAlias'

The change in Alias Set is reflected in the object’s permission. So, a successful implementation of Alias Set is achieved. But a single Alias Set is not of much help in a practical scenario. Let me create one more Alias Set.

CREATE dm_alias_set OBJECT
SET object_name = 'TestUserAlias1'
APPEND alias_name = 'TestUser',
APPEND alias_value = 'TestUser1',
APPEND alias_category = 1,
APPEND alias_usr_category = 1,
APPEND alias_description = 'Testing alias in ACL Template'

The newly created Alias Set is now applied to the user dmadmin. Next time I created an object named TestObject2 and applied the ACL Template to it; the resolved user was as per the new Alias Set. Now I have two objects on which I had applied the same ACL Template and both have different users in their custom permission sets.

Analysis:

This situation is achieved because the Alias Set associated with dmadmin was changed before applying the ACL Template to the new object. Am I going to change the Alias set of dmadmin every time I apply the ACL Template to an object? That doesn’t appear to be a good idea. Further If due to some reason I need to reassign the ACL Template to the older object I may be into serious trouble.

Result:

The Alias Set was created and implemented along with the ACL Template. But the approach was not satisfactory for the practical purposes. A new plan has to be devised. Let me take a break now. I will be back with the new plan soon.

Related Articles:

Read Full Post »

Few days ago my friend asked me a dql query. He wanted to have a list of all the documents and their folder paths in the repository.

It didn’t appear to be a tough one. I knew that the i_folder_id of dm_document is mapped with the r_object_id of the dm_folder to which the document is linked to. A document can be linked to multiple folders and that explains why this i_folder_id is a repeating attribute. The dm_folder has another repeating attribute r_folder_path which stores the folder path of that particular folder. The reason for r_folder_path being repeating is same as that for i_folder_id in case of dm_document.

I told him this dql to get the folder path of all the documents.

SELECT r_folder_path
FROM dm_folder
WHERE r_object_id in
(SELECT distinct i_folder_id
FROM dm_document)

He didn’t look happy enough. What he actually wanted was a list of documents with the folder path. I told him that it is not a problem; we can put a join and get the object_name from dm_document.

SELECT doc.object_name,fol.r_folder_path
FROM dm_document doc, dm_folder fol
WHERE any doc.i_folder_id = fol.r_object_id

The query looked very simple to me. He seemed to be happy and he told he will try it out. I was happy that I have given him a solution. I was a bit surprised when he told me that he was getting an error while executing this query. I didn’t expect this. The error was:

    :[DM_QUERY2_E_REPEAT_TYPE_JOIN]error: “Your query is selecting repeating attributes and joining types.”

The above query works fine if I select only doc.object_name; but it was not allowing me to select fol.r_folder_path which is a repeating attribute. The conclusion was that a repeating valued attribute cannot be selected in a normal query which uses repeating valued attribute in a join. It can be achieved by using DQL Hints mentioned later. But this friend of mine needs both Document Name and Folder Path together. I asked him to write a dfc code which gets all the documents, finds their folder path and writes this information to a file. But my friend is a real lazy guy. The bad part being that I am also no different.

I thought a lot and in the end I got an idea. I asked him to fire a query on the RDBMS tables. The object types are represented as _s and _r tables in RDBMS for their single and repeating valued attribute respectively. The query has to be fired on dm_document_s, dm_document_r, dm_folder_s and dm_folder_r tables.

So, the new query which I formed was:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_document_s doc_s, dm_document_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

But when I fire this query on my docbase I again got an error.

    :[DM_QUERY2_E_TABLE_NOT_FOUND]error: “The database table or view was not found in the database. Error from the database was: ‘ — The database object is invalid — STATE=S0002, CODE=208, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘dbo.dm_document_r’.'”

I realized that there is no such table called dm_document_r as dm_document doesn’t have any repeating attribute of its own. But in that case from where should I get the value for i_folder_id? If i_folder_id is not an attribute of dm_document then it should be inherited from dm_sysobject. Isn’t so?

So I had my new dql:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_document_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

I was so confident. This is the ultimate query which will get me the result. When I fired this query I got the following result.

    :[DM_QUERY_E_CURSOR_ERROR]error: “A database error has occurred during the creation of a cursor (‘ STATE=S0022, CODE=207, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ‘object_name’.’).”

How could I forget that object_name is also an attribute of dm_sysobject. Actually the dm_document_s has just one attribute and that is the r_object_id. As a matter of fact dm_document doesn’t have any attribute of its own. Then what is the use of having the object_type dm_document? I leave this question to be answered by you. This has to be my final query.

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

Ultimately I got the result without any errors. But I wasn’t too happy. Many records in object_name as well as r_folder_path were showing as empty.
I did a quick fix:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
AND fol_r.r_folder_path is not nullstring

The r_folder_path was not showing any empty records but the case was not so with object_name. Something clicked in my mind. My mind had found a flaw in the query which was looking fine to me till now. My friend was interested in documents; not the sysobjects. I was actually giving him a lot of garbage.

And thus I got my next query:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
AND fol_r.r_folder_path is not nullstring
AND doc_s.r_object_type = 'dm_document'

Cool….. My friend looks to be the happiest person on earth. He got the result he was looking for. But I still want to do one more small change.

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_type = 'dm_document'
AND fol_r.r_folder_path is not nullstring
AND doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

The result is same as the earlier query but somehow I look more satisfied with this query. I am not sure how good or how efficient this query is but my friend got what he wanted and I was satisfied that I was able to help him out.

The story is not over yet. I just checked the sql equivalent of this query in the dql component of webtop.

Here is the result:

SELECT all doc_s.object_name, fol_r.r_folder_path
FROM dbo.dm_sysobject_s doc_s, dbo.dm_sysobject_r doc_r, dbo.dm_folder_r fol_r
WHERE ((doc_s.r_object_type=N'dm_document')
AND fol_r.r_folder_path != ' '
AND (doc_s.r_object_id=doc_r.r_object_id)
AND (doc_r.i_folder_id=fol_r.r_object_id))

Do you know what the most interesting part is?

After doing all this crap, I posted it on my blog. I had added tags like DQL, Documentum, etc. When I clicked on the DQL link (The tag which I had added) I got Rajendra’s blog in the result. He has posted a query there.

SELECT A.r_object_id, A.object_name, B.r_folder_path
FROM dm_document A, dm_folder_r B
WHERE any A.i_folder_id = B.r_object_id

Doesn’t that look much simple? :D:D:D

Here is another query from inthewoods. It uses DQL Hint and appears to be a better option than the earlier two.

SELECT doc.r_object_id, doc.object_name, fld.r_folder_path
FROM dm_document doc, dm_folder fld
WHERE doc.i_folder_id = fld.r_object_id ENABLE(ROW_BASED)

So.. Thats the whole story. Hope you enjoyed it.

*Guess it would be a nice idea to add ‘fld.r_folder_path is not nullstring’ and ‘order by r_object_id’ to the final query.

Read Full Post »