ezplatform data integrity: check any file in the ezimagefile table which is not listed in the xml text of ezcontentobject_attribute of type ezimage

check.sql

select i.contentobject_attribute_id, group_concat(i.filepath)
from ezimagefile i
left join (
  select
    id,
    concat(
      '|', ExtractValue(data_text, '/ezimage/@dirpath'), '/', replace(ExtractValue(data_text, '/ezimage/@filename'), '&', '&'),
      '|', ExtractValue(data_text, '/ezimage/@dirpath'), '/', replace(ExtractValue(data_text, '/ezimage/@basename'), '&', '&'), '_',
      replace(
        -- list of aliases, space separated
        replace(ExtractValue(data_text, '/ezimage/alias/@name'), '&', '&'),
        ' ',
        concat('.', ExtractValue(data_text, '/ezimage/@suffix'), '|', ExtractValue(data_text, '/ezimage/@dirpath'), '/', replace(ExtractValue(data_text, '/ezimage/@basename'), '&', '&') , '_')
      ),
      '.', ExtractValue(data_text, '/ezimage/@suffix'), '|'
    ) as all_filepaths
  from ezcontentobject_attribute
  where data_type_string = 'ezimage'
    and ExtractValue(data_text, '/ezimage/@filename') != '' and ExtractValue(data_text, '/ezimage/@filename') is not null
    -- and id = 27610921
) a on i.contentobject_attribute_id = a.id and a.all_filepaths like concat('%|',replace(i.filepath, '\\', '\\\\'),'|%')
where a.id is null
group by i.contentobject_attribute_id

Note: this is not perfect 100%
a) it misses replacing double quotes chars, in case someone used image files with double quotes in them
b) it will fail if anyone uploaded images with a pipe char in their filename
c) it misses a little optimization (escape _ and % chars in the 'like' expression)
d) it works only on mysql. postgresql version might be similar or completely different

As soon as points A and C are fixed, it will get added to the ezdbintegrity legacy extensions