Basic DQL and API

Some basic DQL Queries

Folder:

To get all documents under a folder

select * from dm_document where FOLDER ('/Temp')

To get all documents under a folder and its sub folders

select * from dm_document where FOLDER ('/Temp',DESCEND)

To get folder path of a document

select r_folder_path from dm_folder where r_object_id in (select i_folder_id from dm_document where r_object_id='<r_object_id of document>')

     

Workflow:

To get the instance of a particular workflow:

select * from dm_workflow where process_id in (select r_object_id from dm_process where object_name='testworkflow')

To get the queue items for a workflow:

select * from dmi_queue_item where router_id='<r_object_id of workflow>'

To get the documents attached to a workflow

select distinct r_component_id from dmi_package where r_workflow_id='<r_object_id of workflow>'

Data Dictionary:

To get data dictionary information of attributes in an object type (for eg : dm_document)

select attr_name,label_text,type_name from dmi_dd_attr_info where type_name = 'dm_document'

To get data dictionary information of an object type:

select * from dmi_dd_type_info where type_name = 'dm_document'

Virtual Document:

To check whether a document is virtual document

select count(*) from dmr_containment where parent_id = <r_object_id>.

               If it returns a value > 0, it is a virtual document.

To select all direct and indirect components of virtual document

SELECT r_object_id, PARENT, DEPTH FROM dm_document IN DOCUMENT ID ('<r_object_id of virtual document>') DESCEND

To get the list of parent virtual documents to which our object is linked to:

select object_name,r_object_id from dm_sysobject

where r_object_id in (select parent_id from dmr_containment

where component_id = (select i_chronicle_id from dm_sysobject where r_object_id = '<child-object-id>')) 

Others:

To get current logged in user:

select user_name from dm_user where user_name = USER

Some basic API command

To Get All Attributes of an object

dump,c,<r_object_id>

To get all current logged in users

get,c,sessionconfig,r_user_name

To publish data dictionary

publish_dd,c

reinit,c