-- MediaRepair.sql
-- by ve3meo 11 Dec 2010
-- Developed specifically to correct problems in a database that had:
--  -duplicate image files in the same path on two different drives
--  -multiple duplicate links to the same files
-- This is a series of SQL queries to be run in sequence, one at a time.
-- Some require SharpPlus SQLIte Developer or other that can support a fake RMNOCASE collation.
--
-- Can be used as an example for other repairs
-- RMGC_Properties.sql now reports on duplicate media filenames and duplicate media links

--------------------------------------------------
-- 1. List of duplicate MediaFiles

SELECT MMT.MEDIAID COLLATE NOCASE, MMT.MediaFile COLLATE NOCASE, MMT.MediaPath COLLATE NOCASE 
 FROM MultimediaTable AS MMT,
  (
   SELECT MediaFile COLLATE NOCASE, COUNT(*) AS CTR FROM MultimediaTable 
    GROUP BY MediaFile COLLATE NOCASE
   ) AS DUPES
 WHERE MMT.MediaFile COLLATE NOCASE LIKE DUPES.MediaFile COLLATE NOCASE
  AND CTR > 1
;
--------------------------------------------------

--------------------------------------------------
-- 2. Create list of UPDATE commands to replace the MediaID in MediaLinkTable that points to the MultimediaTable record having 
--  the path to E drive with the MediaID of the same file on the D drive. Copy this list to another SQL edit page and run it.

SELECT DISTINCT 'UPDATE medialinktable SET MediaID='||newmediaid||' WHERE MediaID='||oldmediaid||';' AS command FROM medialinktable AS ML,

-- duplicate files MediaID's
 (SELECT D.MEDIAID AS newmediaid, E.MEDIAID as oldmediaid FROM
  ( 
    SELECT MEDIAID, mediafile COLLATE NOCASE, mediapath FROM multimediatable WHERE mediapath LIKE 'e:%'    
   ) AS E   
 JOIN  
  (
    SELECT MEDIAID, mediafile COLLATE NOCASE, mediapath FROM multimediatable WHERE mediapath LIKE 'd:%'
   ) AS D   
 WHERE E.mediafile = D.mediafile 
 ) 
 WHERE ML.MEDIAID = oldMEDIAID
;   
--------------------------------------------------

--------------------------------------------------
-- 3. RUN the commands copied from the above query
--------------------------------------------------

--------------------------------------------------
-- 4. After UPDATing the MediaIDs in MediaLinkTable to the new MediaIDs, then run this query 
--  to delete the records with oldMediaIDs from MultimediatTable.
--  REQUIRES SharpPlus SQLite Developer or other SQLite manager supporting a fake RMNOCASE collation.

DELETE FROM MultimediaTable WHERE MediaID IN
( 
SELECT E.MEDIAID as oldmediaid FROM
  ( 
    SELECT MEDIAID, mediafile COLLATE NOCASE, mediapath FROM multimediatable WHERE mediapath COLLATE NOCASE LIKE 'e:%'    
   ) AS E   
 JOIN  
  (
    SELECT MEDIAID, mediafile COLLATE NOCASE, mediapath FROM multimediatable WHERE mediapath  COLLATE NOCASE LIKE 'd:%'
   ) AS D   
 WHERE E.mediafile COLLATE NOCASE = D.mediafile COLLATE NOCASE 
 )   
;
--------------------------------------------------

--------------------------------------------------
-- 5. List of duplicate links in MediaLinkTable to media file in MultimediaTable
--
SELECT * FROM
 (
  SELECT LinkID, COUNT(*)-1 AS DUPES, MediaFile COLLATE NOCASE AS LinkedTo FROM MediaLinkTable NATURAL JOIN MultimediaTable
  GROUP BY OwnerType, OwnerID, IsPrimary, Include1, Include2, Include3, Include4, SortOrder, RectLeft, RectTop, RectRight, RectBottom, Note, Caption COLLATE NOCASE, RefNumber COLLATE NOCASE, Date, SortDate, Description
 )
WHERE DUPES > 0
ORDER BY LinkedTo
;
--------------------------------------------------


--------------------------------------------------
-- 6. DELETE duplicate links from MediaLinkTable
--  REQUIRES SharpPlus SQLite Developer or other SQLite manager supporting a fake RMNOCASE collation.

DELETE FROM medialinktable WHERE LINKID NOT IN
(SELECT LinkID FROM
 (
-- Count duplicates in MediaLinkTable
SELECT COUNT(*),* FROM MediaLinkTable
GROUP BY 3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
 ) 
)
;
--------------------------------------------------