cleanup.sql

-- This script takes the values for enclosure post metadata and adds the length
-- and type fields, if they don't exist.
--
-- The REGEXP /\n[0-9]+\n/ is meant to look for the length field. If it's
-- missing, then the value needs to be patched.

SET @type   := 'audio/mpeg';
SET @length := 0;

-- For a sanity check, here are the initial ones.
SELECT COUNT(*) AS `initial good count`
FROM slabwp_postmeta
WHERE meta_key='enclosure'
AND meta_value REGEXP '[\r\n][0-9]+[\r\n]';

-- This one handles items that only have URL in the enclosure.
UPDATE slabwp_postmeta
SET meta_value=CONCAT(meta_value, '\n', @length, '\n', @type)
WHERE meta_key='enclosure'
AND INSTR(meta_value, '\n')=0;

-- This one handles items that don't have these fields at all.
UPDATE slabwp_postmeta
SET meta_value=INSERT(meta_value,
                      INSTR(meta_value, '\n'),
                      0,
                      CONCAT('\n', @length, '\n', @type))
WHERE meta_key='enclosure'
AND meta_value NOT REGEXP '[\n\r][0-9]+[\n\r]'
AND INSTR(meta_value, '\n')<>0;

-- To complete the sanity check, here is the final count of good ones.
SELECT COUNT(*) AS `final good count`
FROM slabwp_postmeta
WHERE meta_key='enclosure'
AND meta_value REGEXP '[\r\n][0-9]+[\r\n]';