Dql汇总

1.得到一个文件柜下的所有文档(包括版本)

select * from dm_document(all) where folder('/Temp',descend)

若获得文件柜下的当前版本文档：

select * from dm_document where folder('/Temp',descend)

2.通过i_chronicle_id字段可以得到该文档的根版本，即升级前的原始版本

select r_object_id,i_chronicle_id,object_name from dm_document(all) where folder('/ProjectFile',descend)

3.判断一个文档是否为虚拟文档的一部分(component_id为component的Chronicle ID)

 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'))

4.清除收件箱

 delete dmi_queue_item objects where delete_flag=0

5.获取某一类型的必备属性, r_component_id 即为组件对象r_object_id

 select attr_name from dmi_dd_attr_info where type_name='dm_folder' and is_required<>0

6.获取工作流attachements

 select r_component_id,r_component_name from dmi_wf_attachment where r_workflow_id='4d0003e880001229'

7.返回前10条数据

 select * from dm_document enable(return_top 10)

8.获取工作流信息

 select task_name,task_state,* from dmi_queue_item where router_id=’workflowId’

9.显示docbrokers

 Execute list_targets

10.查询active workflows

      select * from dm_document where r_object_id in(select r_component_id from dmi_package where r_workflow_id in(select r_object_id from dm_workflow where     r_runtime_state=1))

 

   11.查询一个文档的系统路径

      select doc.r_object_id,doc.object_name,mfile_url('',-1,'') as mypath,doc.i_folder_id from dm_document doc

12.查询文档的用户签名  

select audited_obj_id from dm_audittrail where event_name='dm_signoff' and user_name='tom'

13.测试NULL和默认值 NULLDATE、NULLSTRING、NULLINT、NULLID,注册表用NULL

  select * from dm_user where user_address is nullstring

14.返回指定组的用户成员

  select user_name from dm_user where user_group_name='admingroup'

15.查询属于一个虚拟文档的所有components

  Select * from dm_sysobject IN DOCUMENT ID(‘object_id’) DESCEND

16. 查询一个文件下面文件夹和文档的总数量

         select count(r_object_id) from dm_sysobject where (type(dm_document) and ...) or (type(dm_folder)) and folder(ID('...'), descend)

17.查询扩展名对应dctm的类型

 select name,dos_extension from dm_format

18.获取流程中的包或附件

 select r_component_name from dmi_package where r_workflow_id=’’

select * from dmi_wf_attachment where r_workflow_id=

 

19.判断时间字段是否为空

 select * from ps_pst_borrow where retime IS NULLDATE OR  retime IS NULL

20.dql分页或获取中间n条记录

 select r_object_id,object_name from dm_document ENABLE(RETURN_RANGE " + startRow + " " + endRow + " 'r_creation_date desc')

 21. ENABLE(ROW_BASED)用法

 

22.创建dm_filestore对象  必须先创建dm_location对象

 1. CREATE a dm_location object. i.e. AS follow:

 

CREATE dm_location OBJECT SET object_name = 'storage_02' SET path_type = 

'directory' SET file_system_path = 

'/newly/created/location/obsolute/path/to/data/content_storage_02'

 

2. CREATE a dm_filestore object. i.e. AS follow:

 

CREATE dm_filestore OBJECT SET name = 'filestore_02' SET is_public = F SET 

root = 'storage_02'

 

**root should have the value of newly created dm_location object, IN this 

example, storage_02.

 

3. CHANGE ALL dm_sysobject's, and custom types'--if applicable--default 

storage area TO the newly created filestore, USING DA OR DQL.

 

4. FLUSH cache, AND publish DATA dictionary. i.e.

CODE

API>flushcache,c

API>flush,persistentcache,c

API>flush,persistentobjcache,c

API>publish_dd,c

API>reinit,c

 

5. Clear dmcl cache ON the App server, AS well the content server AND 

re-start the two.