How to determine when a certain ddmfieldattribute record was created?

Issue

• I am  writing a Groovy script to remove certain sensitive data created before a specific date, so that the Liferay database can be shared with support . One of the tables that contains sensitive data is DDMFieldAttribute. As this table has no createDate column, it's not clear how I can delete records created before a certain date. Please can you outline how we can derive the creation date of these records, potentially via a relationship with other entities?
• I guess my question could be formulated like this as well:
  • - you have a specific ddmfieldattribute record in your hands (e.g. you have its primary key, which is probably  fieldattributeid)
  • - what is the SQL SELECT statement that returns the creation date of this record? (or better said, the creation date of the entity/asset that is related to this record)

Environment

• 7.4

Resolution

• ddmfieldattribute records are tied to different assets. Unfortunately, you can't really figure out the asset type from the DDMFieldAttribute directly, but you can sort of guess at it, because each record has an associated classNameId, which is a way to identify what called the API.
   
  

  select fieldAttributeId, ClassName_.value as className 
  from DDMFieldAttribute 
    inner join DDMField using (fieldId) 
    inner join DDMStructureVersion using (structureVersionId) 
    inner join DDMStructure using (structureId) 
    inner join ClassName_ using (classNameId);

   
  Generally speaking, though, the two main users of DDMFieldAttribute in an out of the box installation are probably web content and documents and media's different metadata processors. 
   
  You can identify the DDMFieldAttribute modified dates for things related to web content by tying it to the JournalArticle, and the query looks a bit like this:
  

  select fieldAttributeId, modifiedDate
  from JournalArticle
    inner join DDMFieldAttribute on (JournalArticle.id_ = DDMFieldAttribute.storageId);

   
  You can identify the DDMFieldAttribute modified dates for things tied to documents and media metadata processors by tying it to the DLFileVersion, and the query looks a bit like this:
  

  select fieldAttributeId, modifiedDate
  from DLFileVersion
    inner join DLFileEntryMetadata on (DLFileVersion.fileVersionId = DLFileEntryMetadata.fileVersionId)
    inner join DDMFieldAttribute on (DDMFieldAttribute.storageId = DLFileEntryMetadata.ddmStorageId);

   
  You can identify what's not web content and not documents and media by looking at what's left over:
  

  select ClassName_.value as className, count(*)
  from DDMStructure
    inner join ClassName_ on (DDMStructure.classNameId = ClassName_.classNameId)
    inner join DDMStructureVersion on (DDMStructure.structureId = DDMStructureVersion.structureId)
    inner join DDMField on (DDMStructureVersion.structureVersionId = DDMField.structureVersionId)
    inner join DDMFieldAttribute on (DDMField.fieldId = DDMFieldAttribute.fieldId)
  where DDMFieldAttribute.storageId not in (
    select id_ from JournalArticle
    union
    select ddmStorageId from DLFileEntryMetadata
  )
  group by ClassName_.value;

   
  If you happen to see here additional class names and you can't figure out how to identify their modified dates, we might be able to help if you open a support ticket.