Media+Metadata,+Read,+Write,+Compare+with+Picasa

toc
 * Compare EXIF, IPTC, XMP metadata stored in image files to the Caption, Description and other metadata stored in a RootsMagic 5 database for image files linked to its Media Gallery and copy between the files and the database .**

//The principles and procedures outlined here, while based on the way Picasa uses metadata stored in image files, are applicable with appropriate modifications to relating other photo managers to RootsMagic 5.//

=Compare RM5 and Image File Metadata=

Introduction
I use Picasa to manage my collection of photographs and other media and often write captions in Picasa's picture editor for photos that I upload to Picasaweb online albums. Of course, working in RootsMagic, I get to write captions and descriptions for some of the same photos and for many others that I may not have captioned in Picasa. LessTX and others have asked that RM have some capability of using metadata fields stored in the image files as a way of loading values into RootsMagic or storing them in the image (e.g., [|Insert Image Metadata into Database] and some of the results in this [|search of the RootsMagic Forums]). This sounded attractive on the surface and so I have looked into it further.

Description
One result can be seen in this screenshot of a query in SQLite Expert Personal (which displays the thumbnail images, unlike most other SQLite managers): Under the left red box is the media caption and description metadata stored in the RootsMagic 5 database in the MultiMediaTable, along with the thumbnail image, image file name and path. Under the right hand box are the values extracted by ExifTool from the Caption-Abstract and Description metadata fields stored in the image file by Picasa. With this convenient comparison of the two sources of the image metadata, I can readily see discrepancies between the two and take any needed corrective action in the corresponding application.

Some Observations and Reservations re Automatic Synchronisation
I'll describe how I developed this query after discussing some of my observations and reservations about automatically synchronizing the captions and descriptions between the RootsMagic database and the images.


 * 1) Picasa ver 3.8.0 and RootsMagic 5.0.1.0
 * 2) Picasa stores the same value in both the Caption-Abstract and Description tags in the image (but I cannot account for one example above of several that have a Caption-Abstract value but not Description - perhaps it was written by an earlier version). Therefore, there cannot be a 1:1 relationship between the RootsMagic metadata fields and the image's metadata fields.
 * 3) Neither RootsMagic nor Picasa have a low limit on the length of the string that can be stored in their respective places. However, the RootsMagic caption should be constrained to be quite short because so little space is provided for printing it in the one place it is used, a Scrapbook report. If the Description is also included in the Scrapbook, then both Caption and Description values must be severely limited. Picasa displays captions most generously in its slideshows, less so in the Photo Editor and to 16-60 characters under its variably zoomed thumbnails.
 * 4) Given these significant differences, I cannot see a way of automatically copying from the metadata stored in the image file to the RM fields, without following up to edit them into appropriate sizes. The reverse may be more successful: concatenate the RootsMagic fields and copy the same value to both Caption-Abstract and Description tags in the image file.
 * 5) That said, the use of the image file in a Picasa Album might dictate a quite different caption from that used in a RootsMagic Scrapbook report (just as some users were dismayed to discover that in upgrading from RM4 to RM5 they had lost the ability to have a different caption for the same image when associated with different persons, events, etc. - the usage dictates the caption).
 * 6) Therefore, caption copying must be manually controlled, except for copying to blank fields which, nonetheless, is liable to require manual follow-up.
 * 7) Although these observations are specific to RootsMagic 5 and Picasa, the RM constraints also apply in the general case and the conclusions may not be significantly altered, except for the possible maintenance of the separation between Caption and Description.
 * 8) I have not explored whether there is any appropriate image metadata tags that may correspond with RM's media Date and Reference Number.

Installation
code format="winbatch" ECHO OFF REM RM5comparePicasa.bat REM REM Erase old sql file ERASE GetImageListFromRM.sql ERASE RMimagefiles.args ERASE ImageDescriptions.txt REM Build a SQL query and save 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 REM Open RM database, name passed as a parameter from the command line REM and execute commands saved in sql file written above. "C:\Program Files (x86)\SQLite\sqlite3.exe" %1 < GetImageListFromRM.sql REM list of JPG files now in RMimagefiles.args REM Now add additional ExifTool arguments for processing the list ECHO -execute >> RMimagefiles.args REM Call ExifTool to extract the Descriptions in table form exiftool.exe -T -@ RMimagefiles.args > ImageDescriptions.txt -common_args -directory -filename -Caption-Abstract -Description ECHO Note errors above, if any, before proceeding to import image metadata PAUSE "C:\Program Files (x86)\SQLite\sqlite3.exe" RM-Media.db3 < "ImportPicasaDescriptions.sql" ECHO Open RM-Media.db3 with your SQLite Manager, copy and run the following statements ECHO ATTACH DATABASE '%1' ; 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 ; PAUSE
 * Copy the windows command line shell sqlite3.exe from [|sqlite-shell-win32-x86-3071000.zip] to a folder of your choosing. The batch file below expects it to be in C:\Program Files (x86)\SQLite\ but that can be revised to suit.
 * Install exiftool.exe in a folder along with all of the related batch files and sql scripts. It could be located elsewhere if you revise the batch file accordingly.
 * Download [|RM5comparePicasa.bat] to the common folder

code code format="sql" DROP TABLE IF EXISTS PicasaTable ; CREATE TABLE IF NOT EXISTS PicasaTable ( [Copy], [MediaPath] CHAR COLLATE NOCASE, [MediaFile] CHAR COLLATE NOCASE, [Caption] CHAR COLLATE NOCASE, [Description] CHAR COLLATE NOCASE ) ; DELETE FROM PicasaTable ; .mode tabs .separator "   " .import "ImageDescriptions.txt" PicasaTable:
 * Create a shortcut to the batch file RM5comparePicasa.bat with the Target property ["C:\//yourpath//\RM5comparePicasa.bat" "C:\//yourpath//\//yourRM5database//.rmgc"] and name it something like "RM5comparePicasa //yourdatabase//". Make such a shortcut for each database you want to review.
 * Download [|ImportPicasaDescriptions.sql] to the common folder

code

Procedure
When you click on the shortcut, it launches the batch (or Windows command) file with the path to your database file passed to it in the %1 register. The batch file deletes the old files it created in a previous run and then proceeds to build a short sql script GetImageListFromRM.sql. Then it calls sqlite3 to open your database file and run the script which outputs the results of the query to the ExifTool arguments file RMimagefiles.args, to which it then appends the -execute argument. The args file contains the list of JPG files with full pathnames recorded in the RM5 database Media Gallery.

Next the batch file calls ExifTool to go through the list of image files, find them if it can, and extract the values from the Caption-Abstract and Description tags along with the paths and file names to a new file, ImageDescriptions.txt. There's a pause to review any possible error reports in the command window, e.g., file not found.

Now the batch file calls sqlite3 again to open or create the local database RM-Media.db3, creates PicasaTable with four columns matching those in the tab-delimited ImageDescription.txt and imports the latter into the table. That's it for the batch file, except for some advice as to what to do next: code format="sql" ATTACH DATABASE 'yourdatabase' ; code code format="sql" SELECT RM.MediaFile COLLATE NOCASE, Thumbnail, RM.Caption COLLATE NOCASE AS RMcaption, CAST(RM.Description COLLATE NOCASE AS TEXT) AS RMdescription, Pic.Caption AS MetaCaption, Pic.Description AS MetaDescription, Pic.MediaPath FROM MultiMediaTable AS RM INNER JOIN PicasaTable AS Pic ON RM.MediaFile COLLATE NOCASE LIKE Pic.MediaFile;
 * Open RM-Media.db3 with your SQLite manager (SQLite Expert Personal if you want to see the thumbnails) and run the following statements:

code If everything worked as intended, you should see results like the screenshot (adjust column widths to suit, of course).

=Copy RM Image Metadata to Image File (Picasa compatible)=

Description
This is basically a query in RM-Media.db3 (created and updated by the //Compare// utility with the RM5 database attached) to extract the list of files for which RM has a caption and the image file does not, along with the concatenation of caption and description values, and getting those into a file in the format required for ExifTool arguments with the appropriate arguments to tell ExifTool to write the value into the Caption-Abstract tag of the image files, and then run ExifTool against the args file. All this is managed by a batch file as was done for the Compare procedure. Here is an example showing how the RM5 Caption + Description end up being displayed as a Picasa caption.

This was a very tricky procedure to finally get going and resulted in considerable hair loss! The arguments for ExifTool must be one per line so carriage return/line feed controls (CR/LF) in the RM5 values must be replaced. Thus the white space above the URL in the RM Description disappears in the Picasa caption, the CR/LFs being replaced by periods.

This same difference applies in using ExifTool to copy the metadata from the image to RM: ExifTool itself substitutes a period for CR/LF embedded in the metadata values. Thus metadata values on either side containing CR/LF cannot be exactly replicated on the other, at least with ExifTool as the intermediary.

Installation
code format="sql" /* RM5copyMetadataToImageFileExifToolArgList.sql SELECT '-Caption-Abstract=' || REPLACE(CAST(RM.Caption AS TEXT),x'0D0A','.') || ' ' || REPLACE(CAST(RM.Description AS TEXT),x'0D0A','.') || CAST(X'0D0A' AS TEXT) || RM.MediaPath || RM.MediaFile || CAST(X'0D0A' AS TEXT) || '-execute' || Pic.ROWID || CAST(X'0D0A' AS TEXT) AS Args FROM MultiMediaTable AS RM INNER JOIN PicasaTable AS Pic ON RM.MediaFile COLLATE NOCASE = Pic.MediaFile WHERE (RM.Caption IS NOT NULL OR RM.Description IS NOT NULL) AND (Pic.Caption LIKE '-' AND Pic.Description LIKE '-')
 * sqlite3.exe and exiftool.exe are already installed as above
 * download [|RM5copyMetadataToImageFileExifToolArgList.sql] to the same folder as RM-Media.db3

code

code format="winbatch" ECHO OFF REM RM5copyMetadataToImageFile.bat REM REM Build sql file ECHO ATTACH DATABASE '%~1' AS RM ; > RM5copyMetaData.sql TYPE RM5copyMetadataToImageFileExifToolArgList.sql >> RM5copyMetaData.sql "C:\Program Files (x86)\SQLite\sqlite3.exe" RM-Media.db3 < RM5copyMetaData.sql > RM5copyMetaDataToImageFile.args ECHO. ECHO Check RM5copyMetaDataToImageFile.args before proceeding to write to the image files PAUSE exiftool -@ RM5copyMetaDataToImageFile.args > RM5copyMetaDataToImageFile.log ECHO. ECHO Finished copying captions to image files PAUSE END
 * download [|RM5copyMetadataToImageFileExifToolArgList.bat] to the same folder:

code

>
 * Create a shortcut to the batch file RM5copyMetadataToImageFile.bat with the Target property ["C:\//yourpath//\RM5copyMetadataToImageFile.bat" "C:\//yourpath//\//yourRM5database//.rmgc"] and name it something like "RM5copyMetadataToImageFile - //yourdatabase//". Make such a shortcut for each database you want to copy captions from.

Procedure
First run the //Compare// utility against your database to ensure that the RM-Media.db3 is up-to-date with the current metadata from all of the images used by the database. Then click on the shortcut "RM5copyMetadataToImageFile - //yourdatabase//". The Windows Command console will open and, almost immediately, you will asked to code format="winbatch" Check RM5copyMetaDataToImageFile.args before proceeding to write to the image files code Open the file (in the folder common to all of the .bat and .sql files you have installed as instructed above) with a non-wrapping text editor and note that each file is addressed by a triplet of three lines and an empty line after as: code -Caption-Abstract=Obituary - Colin xxx North Bay Nugget - Aug 13, 1956

C:\MyDocs\FamilyTree\Holxxx\Scrapbook\xxx\xxxn, Colin - obituary - 1956.jpg

-execute2

-Caption-Abstract=Registration of birth of Helen xxx 13

C:\MyDocs\FamilyTree\Holxxx\Scrapbook\xxx\xxxth, Helen Elizabeth - Birth Register xxx13.jpg

-execute3

... code There should be nothing else in this //args// file than the triplets of single lines as above. The number following -execute is the PicasaTable //rowid//, which may be useful in tracking down a problem if ExifTool encounters a bad image file (I think it will report that number in its error message).

Then, press any key to launch ExifTool to work on the list of files. ExifTool copies each file to a .jpg_original file and re-writes the .jpg file with the new Caption-Abstract value added.

After some time, proportional to the number of files being processed, you will see the message "Finished ... Press any key to continue." Because the batch file is really basic with no error-trapping, you will see this message regardless of errors. It's possible that no file may have been processed. Inspect the file RM5copyMetaDataToImageFile.log for any error message.

Assuming no errors, if Picasa is set to monitor the folders where these image files are located and was running at the time they were being revised by ExifTool, you will have seen the little pop-up message from Picasa as it updates its thumbnails and its internal storage of captions for searching. The ability to search through captions is one of Picasa's strong features, along with its facial recognition and multiple views: Albums, People, Folders. =Copy Image Metadata from JPG to RM= This procedure is trivial compared to the opposite direction. The heavy work has been done in the //Compare...// procedure, which should be carried out before attempting to copy metadata to the RootsMagic database as the //Copy...// relies on the RM-Media.db3 database created and updated by //Compare...// The only operation that makes sense to me that would benefit from such a procedure is to copy multiple image files' Caption-Abstract values to empty RootsMagic captions for those files. Copying onto existing captions is necessarily a manual process and could be done with copy/paste between the applications.

This is essentially a SQL query against both the RootsMagic database and the RM-Media.db3 database. However, it's complicated by the fact that I cannot figure out a way to make a single UPDATE statement work for multiple captions so a batch procedure is needed to generate a sql script file with multiple UPDATE statements, one per caption to be copied, and run that.

Installation
code format="sql" -- RM5copyMetadataToRM.sql -- generates an UPDATE statement for each blank caption in RM for which there is a caption extracted from the image file -- requires RM-Media.db3 with the RM5 database ATTACHed SELECT 'UPDATE MultiMediaTable SET Caption=(SELECT Caption FROM PicasaTable WHERE ROWID=' || PicID ||') WHERE MediaID=' || MediaID ||';' AS Statement FROM ( SELECT MediaID, Pic.ROWID AS PicID FROM MultiMediaTable AS RM INNER JOIN PicasaTable AS Pic ON REPLACE(RM.MediaPath || RM.MediaFile, '\', '/') LIKE Pic.MediaPath || '/' || Pic.MediaFile WHERE Pic.Caption NOT LIKE '-' AND RM.Caption IS NULL ) code code format="winbatch" ECHO OFF REM RM5copyMetadataToRM.bat REM Copies to blank RM5 captions the values from Caption-Abstract metadata stored REM in the corresponding image files REM Uses PicasaTable in RM-Media.db3 created by the related Compare routine REM and the MultiMediaTable of the target RootsMagic 5 database. REM Requires the sqlite3.exe command line shell REM REM Build and run sql script file that generates UPDATE statements for each caption to be copied ECHO ATTACH DATABASE '%~1' AS RM ; > RM5copyMetaData.sql TYPE RM5copyMetadataToRM.sql >> RM5copyMetaData.sql "C:\Program Files (x86)\SQLite\sqlite3.exe" RM-Media.db3 < RM5copyMetaData.sql > RM5UpdateCaptions.sql REM REM Build and run the sql script file that UPDATEs the target RootsMagic 5 database captions ECHO ATTACH DATABASE '%~1' AS RM ; > RM5copyMetaData.sql TYPE RM5UpdateCaptions.sql >> RM5copyMetaData.sql ECHO Check RM5copyMetaData.sql before running the caption UPDATEs PAUSE "C:\Program Files (x86)\SQLite\sqlite3.exe" RM-Media.db3 < RM5copyMetaData.sql ECHO Blank RM5 Captions Updated PAUSE END
 * requires sqlite3.exe command line shell as above
 * Download [|RM5copyMetadataToRM.sql] to the common folder:
 * Download [|RM5copyMetadataToRM.bat] to the common folder:

code
 * Create a shortcut to the batch file RM5copyMetadataToRM.bat with the Target property ["C:\//yourpath//\RM5copyMetadataToRM.bat" "C:\//yourpath//\//yourRM5database//.rmgc"] and name it something like "RM5copyMetadataToRM - //yourdatabase//". Make such a shortcut for each database to which you want to copy captions.

Procedure
First run the //Compare// utility against your database to ensure that the RM-Media.db3 is up-to-date with the current metadata from all of the images used by the database. Then click on the shortcut "RM5copyMetadataToRM - //yourdatabase//". Follow the on-screen instructions. Use RootsMagic to check the new captions. =Further Considerations=
 * 1) The two unidirectional copy caption to blank caption procedures could be combined into one batch and made to standalone without the need for the //Compare...// procedure to be run in advance.
 * 2) A comparison between RM's Caption + Description and the image file's Caption-Abstract by substituting the CR/LF's in the former with periods might be feasible.
 * 3) If MetaData comparisons and copying/editing were part of a future RM feature or outboard utility, it would be desirable to have a directional choice per image where there are non-blank values in both.
 * 4) What relationship should there be between RM's Date and Reference Number fields and any EXIF, IPTC, or XMP tag?
 * 5) What changes may be needed for compatibility with media managers other than Picasa?
 * 6) [|LessTX wish1] [|LessTX wish2] [|vyger wish] [|vyger wish2] [|Athena's 2008 wish]