/* RM4-MultiLinkedMediaDuplicationPreRM5.sql
2012-01-31 ve3meo

THIS SCRIPT REQUIRES YOU TO KEEP THE RMGC DATABASE OPEN IN YOUR SQLITE MANAGER WHILE YOU:
1. EXECUTE it, then
2. COPY the first set of results as SQL commands to another window of your currently
running SQL editor and execute. Then,
3. COPY a short SQL statement from the bottom comment section to your SQL editor and
execute it; then,
4. COPY the results of that query to a Windows batch or command file; now you may close 
your SQLite manager (but you don't have to).
5. Run the batch/command file to make copies of the multiply-linked files.
At this point, you can open the database file with RM4 to check that nothing untoward 
has become of it. It should be ready for conversion by RM5.

RM4 supported a unique set of metadata (Caption, Description, Date, SortDate, RefNumber)
for each use of a media file, i.e., if the same image file was used for a citation, a fact
and a person, there were three sets of metadata. RM5 does not permit that, allowing only 
one set of metadata per file. When a database is updated to RM5, only one of those three sets
of metadata survive.

This series of queries and related Windows commands revises a RM4 database and makes 
copies of the multiply-linked files so that the update to RM5 will preserve all
the metadata.
*/
DROP TABLE IF EXISTS MultiLinkMediaDupe
;

CREATE TEMP TABLE IF NOT EXISTS MultiLinkMediaDupe
AS
  
SELECT LinkID, MediaID, MediaType, MediaPath, MediaFile, URL, Thumbnail
FROM MultiMediaTable NATURAL JOIN MediaLinkTable
WHERE MediaID
IN
 (
  SELECT MediaID 
  FROM
  (
   SELECT MediaID, COUNT()-1 AS Dupes FROM MultiMediaTable NATURAL JOIN MediaLinkTable 
   GROUP BY MediaID
   )
  WHERE Dupes > 0
  )
--GROUP BY MediaID
;

/*
The next query adds new entries to the Media Gallery for unique copies of the common media files.
To make their filenames unique, the original name is prefaced by the unique LinkID of each
link which pointed to the common gallery item (common MediaID). Each new entry gets a new, unique MediaID.
*/

INSERT INTO MultimediaTable 
SELECT NULL AS MediaID, MediaType, MediaPath, LinkID || '-' || MediaFile AS MediaFile, URL, Thumbnail
FROM MultiLinkMediaDupe
WHERE LinkID 
NOT IN
(
SELECT LinkID FROM MultiLinkMediaDupe GROUP BY MediaID
)
;

/*
Copy the results of the next query to another instance of your SQL editor, while keeping the 
database open in your SQLite manager, and execute it. It replaces the links to common files
with links to the unique copies of each file.
*/

SELECT 'UPDATE MediaLinkTable SET MediaID=' || MediaID || ' WHERE LinkID=' || LinkID || ' ;' AS "--SQL"
FROM
(
 SELECT MMT.MediaID, LinkID 
 FROM MultiMediaTable AS MMT 
 INNER JOIN MultiLinkMediaDupe AS MLMD 
 ON MMT.MediaFile LIKE LinkID || '-' || MLMD.MediaFile
 )
;


/* 
Select (highlight) the SQL statement from between the dashed lines and 
execute it alone in place (Ctrl-F9 in SQLiteSpy) or copy to another window 
of your SQL editor, while keeping the RM4 database open in its SQlite manager, 
and execute it there.
Copy the results of this query to Notepad or another plain text editor and save it
as a Windows batch (.bat) or command (.cmd) file to your Desktop or any convenient folder.
Then run it by double-clicking on it or right-click > Open. If there are lots of multi-linked
mediafiles, it could take some time to make all the copies.

--------------
SELECT 'COPY "' || MLMD.MediaPath || MLMD.MediaFile || '" "' || MMT.MediaPath || MMT.MediaFile || '"' 
AS "REM Make unique copies of multi-linked RM4 media files" 
FROM MultiLinkMediaDupe AS MLMD 
INNER JOIN MultiMediaTable AS MMT 
ON MMT.MediaFile LIKE LinkID || '-' || MLMD.MediaFile
;
--------------

That completes the preparation of the RM4 database and mediafiles for conversion to RM5, 
preserving all possible unique metadata. You can open it in RM4 and check the Media Gallery
to confirm that the copied files have been added and are used as expected.
*/