Monthly Archives: November 2008

DQL: Oracle vs MSSQL

The following DQL returns no results when tested against an Oracle database. However, expected results were returned when testing against MSSQL.

select dos_extension, name from dm_format where dos_extension is not nullstring and dos_extension ”

The following DQL works on both databases.

select dos_extension, name from dm_format where dos_extension is not nullstring

Needless to say, DQL and SQL are similar in that they behave differently in different databases.

Advertisements

Useful Queries: Aliases

List all alias sets and their aliases:

select object_name, alias_name, alias_value, alias_category from dm_alias_set

List all permission set templates in the system:

select object_name from dm_acl where acl_class = 1


Useful Queries: Documents and Folders

List the names of all the cabinets in the repository:

select object_name from dm_cabinet

List all the users and their associated email addresses:

select user_name, user_address from dm_user

Count how many documents reside in the repository:

select count(*) as number_of_objects from dm_document


Useful Queries: Renditions

Display all objects that have renditions:

select r_object_id, object_name, a_content_type, r_object_type, r_lock_owner, r_link_cnt from dm_document where r_object_id in (select parent_id from dmr_content where rendition > 0)

Display all of my objects that have PDF renditions:

select r_object_id, object_name, a_content_type, r_object_type, r_lock_owner, r_link_cnt from dm_document where r_object_id in (select parent_id from dmr_content where full_format = ‘pdf’ and rendition > 0) and owner_name = user


Useful Queries: Multiple Content Files

How many content files are associate with a specific object:

select r_page_cnt from dm_document where r_object_id = ‘090000001800bcc8f’

What are the object IDs of the content objects associated with a specific object:

select r_object_id from dmr_content where any parent_id = ‘090000001800bcc8f’


Useful Queries: Objects and Content

Retrieve information about a specific content file:

select r_object_id, parent_id, page, rendition, content_size, set_time, set_client, set_file from dmr_content where any parent_id = ‘090009dd800039a4’

Which objects depend on a specific content file:

select r_object_type, r_lock_owner, r_link_cnt from dm_sysobject where i_contents_id = ‘0600030980000c78’


Useful Queries: Virtual Document

List all direct children of a specific virtual document (identified by object ID):

select r_object_yd from dm_sysobject in document id(‘09003214af93028c’)

List all children of a specific virtual document, including components of nested virtual documents:

select r_object_id from dm_sysobject in document id(‘09003214af93028c’) descend with any r_version_label = ‘CURRENT’

List all parent virtual documents to which a specific document belongs (identified by object ID):

select r_object_id from dmr_containment where child_id = (’09f5a5cb8000892b’)

List all documents that are virtual documents:

select object_name from dm_document where r_is_virtual_doc = 1