Skip to content

Instantly share code, notes, and snippets.

@gggeek
Created April 24, 2020 22:50
Show Gist options
  • Save gggeek/e677856a5fdefb1b1d22309286bf5246 to your computer and use it in GitHub Desktop.
Save gggeek/e677856a5fdefb1b1d22309286bf5246 to your computer and use it in GitHub Desktop.
ezplatform data integrity: check any file in the ezimagefile table which is not listed in the xml text of ezcontentobject_attribute of type ezimage
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
@gggeek
Copy link
Author

gggeek commented Apr 24, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment