--MediaShuffle.sql
/*
2016-02-07 Tom Holden ve3meo
Problem posed by delichef at 
http://forums.rootsmagic.com/index.php?/topic/16304-bulk-file-name-change-for-hundreds-of-family-letters/
*/

DROP VIEW IF EXISTS vOLD
;
CREATE TEMP VIEW vOld AS
SELECT MediaID, MediaPath, MediaFile, SUBSTR(MediaFile,7,4) AS YYYY, SUBSTR(MediaFile,1,2) AS MM, SUBSTR(MediaFile,4,2) AS DD 
FROM MultiMediaTable 
WHERE MediaFile REGEXP '^[0-1]{1}[\d]{1}-[0-3]{1}[\d]{1}-[\d]{4}.+'  --matching pattern MM-DD-YYYY....
;

DROP VIEW IF EXISTS vNew
;
CREATE TEMP VIEW vNew AS
SELECT MediaID, MediaPath || 'NewFolder\' AS NewPath, YYYY || '-' || MM || '-' || DD || SUBSTR(MediaFile,11) AS NewFile
FROM vOld
;

DROP VIEW IF EXISTS vCmd
;
CREATE TEMP VIEW vCmd AS
SELECT 'COPY "' || MediaPath || MediaFile || '" "' || NewPath || NewFile || '"'AS Cmd
FROM vOld JOIN vNew USING(MediaID)
;

DROP VIEW IF EXISTS vSQL
;
CREATE TEMP VIEW vSQL AS
SELECT 'BEGIN;' AS SQL
UNION ALL
SELECT 'UPDATE MultiMediaTable SET MediaPath = ''' || NewPath || ''', MediaFile = ''' || NewFile || ''' WHERE MediaID = ' || MediaID ||';'
AS SQL FROM vNew
UNION ALL
SELECT 'COMMIT;'
;