[Nottingham] Silly big attachments in ownCloud *Calendars*

[Martin]

On 26/11/14 16:27, Martin wrote:

> A quick fix was to remove any/all big offending attachments with a
> sneaky bit of SQL:
> 
> update oc_clndr_objects set (calendardata, lastmodified) =
> (regexp_replace(calendardata,'ATTACH;X-ORACLE-FILENAME=[^[:cntrl:]]+[[:cntrl:]]+(
> [^[:cntrl:]]+[[:cntrl:]]+)+[[:cntrl:]]*','','g' ), lastmodified+60)
> where char_length( calendardata ) > 8192 ;
> 
> 
> Rather than blindly updating with that, a good check for what would be
> zapped is to first run:
> 
> select char_length( calendardata ) as cdsize, calendarid, startdate,
> substring(
> regexp_replace(calendardata,'ATTACH;X-ORACLE-FILENAME=[^[:cntrl:]]+[[:cntrl:]]+(
> [^[:cntrl:]]+[[:cntrl:]]+)+[[:cntrl:]]*','toberemoved','g' ) from
> 'toberemoved[^[:cntrl:]]+[[:cntrl:]]+' ) from oc_clndr_objects where
> char_length( calendardata ) > 8192 order by cdsize;

Those both select/update ALL "calandardata" entries that are greater
than 8k chars, regardless of whether there are any attachments.

That is interesting in itself to see the list of /all/ big entries from
the select query. However, the _update_ will update the "lastmodified"
time for all big entries regardless. That is not wanted if the update is
to be repeatedly run...


Hence "v2" for allowing repeat runs of the update:

# update oc_clndr_objects set (calendardata, lastmodified) =
(regexp_replace(calendardata,'ATTACH;X-ORACLE-FILENAME=[^[:cntrl:]]+[[:cntrl:]]+(
[^[:cntrl:]]+[[:cntrl:]]+)+[[:cntrl:]]*','','g' ), lastmodified+60)
where char_length( calendardata ) > 8192 and calendardata ~
'ATTACH;X-ORACLE-FILENAME=[^[:cntrl:]]+' ;

Note the extra "and" clause.


Similarly so for the look-see select if you only want to see the
attachments:

# select char_length( calendardata ) as cdsize, calendarid, startdate,
substring( calendardata from 'ATTACH;X-ORACLE-FILENAME=[^[:cntrl:]]+' )
from oc_clndr_objects where char_length( calendardata ) > 8192 and
calendardata ~ 'ATTACH;X-ORACLE-FILENAME=[^[:cntrl:]]+' order by cdsize;


To my eye, there is an ugly repeat of the string search for the select
example...

No worry for performance for my case, but could that be optimised out
for any 'big' searches?...



Cheers,
Martin


-- 
- ------------------ - ----------------------------------------
-    Martin Lomas    - OpenPGP (GPG/PGP) Public Key: 0xCEE1D3B7
- martin @ ml1 co uk - Import from   hkp://subkeys.pgp.net   or
- ------------------ - http:// ml1 .co .uk/martin_ml1_co_uk.gpg