Attachment type attribute references at the DB side (Clarity PPM)

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

We have got attachment typ attributes on the system. Some of those fields do not have any attachment in the UI, but there is a value at the DB side.

We would expect a NULL value at the DB if the field is empty

 

Cause

The attachment type attribute references are only NULL at DB level when no file has ever been attached to the field.

Once a document is uploaded to the field the reference is created at DB level.

That reference is not nullified when the document is deleted. It gets reused when another document is uploaded to the same field. This is by design

Resolution

How do you identify at the DB side if the attachment type attributes have a document associated

Created two attachment type attributes at the project object: at1, at2
Populated them with a file 

select at1, at2 from odf_ca_project where id = 5007037 >>>> ids are (5020744, 5020748)

The following query will return data if attachments are still present:

select substr(af.assoc_obj_type, instr(af.assoc_obj_type, '::') + 2) internal_name,  files.name, files.description, ver.id version_id,
          files.id file_id, mime_type, language, version_number, is_latest,
          af.id folder_id, files.owner_id, af.folder_type, ver.byte_size file_size,
          af.assoc_obj_id, af.assoc_obj_type, ver.created_date, ver.last_updated_date,
          af.path_name || '/' || af.name fullPath,
          af.path_name || '/' || af.name || '/' || files.name fullPathName
          from  clb_dms_folders obf, clb_dms_files files, clb_dms_versions ver,
          clb_dms_folders af
          where af.assoc_obj_type like ( 'project' || '::%' )
          and   af.parent_folder_id = obf.id
          and   files.parent_folder_id = af.id
          and   ver.file_id = files.id
          and   ver.is_latest = 1   and obf.assoc_obj_id = 5007037     order by af.assoc_obj_type

If the attachments are deleted the record will drop from the clb tables, but the identifier will remain on the odf_ca_project table.