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
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
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.