@ECHO OFF
GOTO :start
RM5comparePicasa.bat
Tom Holden ve3meo 2012

Gets metadata from Caption-Abstract and Description fields from JPG files listed in the MultiMediaTable
of the RootsMagic 5 to 7.6 .rmgc database file specified in the call into a table in a temporary database and
generates a SQL statement for use in a SQLite manager to JOIN the two tables to review the RM Caption and 
Description with the metadata.

Context: https://sqlitetoolsforrootsmagic.com/media-metadata-read-write-compare-with-picasa/

Requires:
	sqlite3.exe command line shell from sqlite.org in C:\Program Files (x86)\SQLite
	ExifTool by Phil Harvey, in same folder as this batch file
	A SQLite manager to run the outputted SQL statement
	
Usage:
	From the command prompt or a shortcut call 
		RM5comparePicasa.bat "fullpathtoyourRMdatabasefileindoublequotes"
		
rev 2020-05-31 with RM7.6.3, ExifTool by Phil Harvey 11.99
	- confirmed works with RM 7.6
	- allowed UTF-8 characters in filenames (thanks, strathglass)
	- cleaner start on first time use in new directory
	- commented and prettied

:start
ECHO ****RM5comparePicasa.bat****
ECHO:
ECHO Deleting intermediate files from previous run, if any
IF EXIST GetImageListFromRM.sql ERASE GetImageListFromRM.sql
IF EXIST RMimagefiles.args ERASE RMimagefiles.args
IF EXIST ImageDescriptions.txt ERASE ImageDescriptions.txt

ECHO Building a SQL query to get the list of images from RM and saving to a file
ECHO .mode tabs > GetImageListFromRM.sql
ECHO .output RMimagefiles.args >> GetImageListFromRM.sql
ECHO SELECT MediaPath^|^|MediaFile AS MediaFile FROM MultiMediaTable WHERE MediaFile LIKE '%%.jpg' ORDER BY MediaFile ; >> GetImageListFromRM.sql
ECHO .exit >> GetImageListFromRM.sql

ECHO Opening RM database with SQLite, filename passed as a parameter from the command line; executing commands saved in sql file written above.
"C:\Program Files (x86)\SQLite\sqlite3.exe" %1 < GetImageListFromRM.sql
ECHO 	-list of JPG files now in RMimagefiles.args

ECHO Now adding additional ExifTool arguments for processing the list
ECHO -execute >> RMimagefiles.args 
ECHO:
ECHO Calling ExifTool to extract the Descriptions in table form
ECHO ----Disregard the error message: Invalid TAG name: "execute  "----
exiftool.exe -T -charset filename=utf8 -@ RMimagefiles.args > ImageDescriptions.txt -common_args  -directory -filename -Caption-Abstract -ImageDescription  
ECHO:
ECHO 	-metadata descriptions for found files are now in ImageDescriptions.txt
ECHO:
ECHO ----Note file errors above, if any, before proceeding to import image metadata text----
ECHO:
PAUSE 
"C:\Program Files (x86)\SQLite\sqlite3.exe" RM-Media.db3 < "ImportPicasaDescriptions.sql"
ECHO:
ECHO 	-metadata descriptions for found files have been imported into RM-Media.db3
ECHO:
REM On-screen instructions for user to follow
ECHO -----------------------------------------------------------------------------------------------
ECHO        Open RM-Media.db3 with your SQLite Manager, copy and run the following statements
ECHO -----------------------------------------------------------------------------------------------
ECHO:
ECHO ATTACH DATABASE %1 AS RM; --enclose unquoted filepath in either single or double quotes if none
ECHO SELECT RM.MediaFile COLLATE NOCASE, Thumbnail, RM.Caption COLLATE NOCASE AS RMcaption, 
ECHO  CAST(RM.Description COLLATE NOCASE AS TEXT) AS RMdescription, Pic.Caption AS MetaCaption, 
ECHO  Pic.Description AS MetaDescription, Pic.MediaPath FROM MultiMediaTable AS RM 
ECHO  INNER JOIN PicasaTable AS Pic ON RM.MediaFile COLLATE NOCASE LIKE Pic.MediaFile ;
ECHO:
ECHO ----Once you've copied the above, press any key to exit this batch file-------------------------
PAUSE >nul
EXIT