DQL and Folder Paths

I came across this blog entry on dm_notes. While what Rajendra posts will work, it is not the best choice for production use.

The problem with his query is that the _r tables are not automatically accessible to non-superuser accounts unless they are explicitly registered. And the problem with registering the repeating tables is that then users can see things that the Documentum ACL security would not normally allow them to see. This is a Bad Thing in production.

In Documentum 5, EMC introduced a DQL hint called ROW_BASED that allows us to circumvent the DM_QUERY2_E_REPEAT_TYPE_JOIN error. Essentially the ROW_BASED hint disables Documentum's normal repeating property validation and post-processing and allows all the query results to be returned by the RDBMS through the Documentum server. The following DQL does the same thing as Rajendra's query, but is subject to normal Docbase security and doesn't require any special setup:

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 AND
fld.r_folder_path like '/System/%'
ENABLE(ROW_BASED, RETURN_TOP 10)

The RETURN_TOP hint is only there so the example query completes quickly. Note that both queries will return all locations where each document is linked. If you only want to know the first folder into which each document is linked, then add the following to the WHERE clause:

AND doc.i_position=-1 AND fld.i_position=-1

The i_position column can be used for other interesting queries, but that is a subject for a different post.

UPDATE: As long as I'm talking about "production ready" queries, I should point out that using r_folder_path LIKE '/System/%' is very slow since it forces a full-table-scan by the underlying database of the dm_folder_r table. It is better to use FOLDER('/System', descend) instead.