TreeShare downloads Ancestry media for citations with cryptic file names that mean nothing to humans. This set of scripts and batch or command-line operations prepends to the media filenames the name of one of the persons to whom the citation applies, their BirthYear-DeathYear and the name of the source cited, separated from the cryptic name by the @ symbol. Such information in the file names can help the user organise the downloaded media into folders by surname or even more finely divided. Renaming the files has no apparent effect on TreeShare operations.
A variant of the script could change the order in the new name to be source name first, which would aid in finding and reorganising by source, rather than by person.
Here is a screenshot showing results from a temporary table created by the second script which lists the Ancestry name (OldName) with its new, informative name.
Here is a screenshot of a Media Gallery after the files have been renamed.
Note that the source was cited to four facts or events about the person. and could be cited for multiple facts for multiple people, as, for example, a Census source and image could be tagged to Birth and Residence facts for all family members. However, there is only one image file for that source so the procedure takes the person and event last cited for use in the file name.
As always, use these tools at your own risk. I cannot predict all possible consequences. I saw no adverse effect in my one test case.
READ the comments at the beginning of each script and the instructions at the end, which will also be displayed on completion of the script execution. DO NOT CLOSE THE DATABASE IN THE SQLITE MANAGER UNTIL YOU NO LONGER WISH TO UNDO.
TreeShare-MediaRenameStep1.sql This script can do no harm. It is a variation of RM7_5-WaymarksViews.sql to revise the format and content of some of the Waymarks to suit this application. It generates a set of temporary SQLite Views with which one can inspect the contents of a table and from the Waymark info, navigate within RootsMagic to see where a record is used. For this application, it also generates a temporary table zMediaCitationTags needed for the following script.
TreeShare-MediaRenameStep2.sql Changes the media links so they will appear broken in the Media Gallery until you successfully rename the files with the batch commands it generates.
TreeShare-MediaRenameUNDO.sql Changes the media links back and generates batch commands for changing the media file names back to their Ancestry original.
Possible risks:
- A new filename might exceed the limit supported by the file system. Didn’t happen in my test case and would require very long Ancestry file name, source name, person name (couple events would be worst case).
- There is a character in one of the names that is invalid for a file name. Didn’t happen.
- Some RootsMagic operations may be locked out by having the database open in the SQLite manager and some may lock out the SQLite manager. Avoid doing a backup or using the RM Database Tools while in session on the SQLite manager. I’ve not had a problem while in TreeShare or main views and Edit Person. I’d be wary about going into FamilySearch Central and perhaps having WebHints on.
A 2020 blog post and videos by Margaret O’Brien, Data Mining DNA, adapts the above script to rename the Ancestry record image files but without changing the pre-RM8 database. I’m not sure why it is unnecessary to have the renamed files linked to the RM database but her use-case seems to be using RM Essentials solely for the purpose of retrieving those images for use with some other system.
She also has eliminated the need for RMNOCASE in her adaptation and makes some other tweaks to the image filename pattern.
More at https://www.dataminingdna.com/rename-rootsmagic-media-files-downloaded-from-your-ancestry-tree/
Does this process work in RM8? Regards Wayne b
I think it has to be revised to work with RM8.
Hi Tom!
How do I revise RM8 to work with your script?
I get SQLlite 3 error 1 – no such table: LinkAncestryTable.
The steps i took to that point was:
1. open RM database
2. highlight “main” in the left database column
3. open sql (TreeShare-MediaRenameStep1.sql)
4. F9 (excute sql)
Thanks for any help and God Bless!
Jeff 🙂
Off the top of my head, LinkAncestryTable in RM7 is AncestryTable in RM8. But there’s more to address: RM7 paths were all absolute, e.g., “D:\FamilyHistory\media\…”. RM8 introduced variables in the stored paths that have to be expanded for the batch file to work. There may be more due to other changes. I may have started on the update some months ago and forgot – I’ll have a look.
I don’t see any evidence that I did work on it but another complicating factor is the structural change around Citations in which RM7 CitationTable was split into two tables in RM8. That breaks many things.
Another is LinkTable has been replaced by FamilySearchTable; LabelTable by TagTable; ResearchTable and ResearchItemTable by TaskTable and TaskLinkTable. However, they are not necessarily simply the same table renamed; there are other more fundamental changes.
So it’s not a simple job to update it for RM8.
Tom,
Thank you so much for the explanation!
I have donrea little sql work in rootsmagic and TNG,
but sounds like way more that I can handle to produce something that will update the ancestry naming properly. Well appreciated your quick response and the work on the site!
Jeff 😀
And sorry is there a process to copy media files from one directory to another based on the multimedia list report in RM8 saved as an excel.
One could likely use the spreadsheet to generate Move commands for use in a batch or PowerShell script within limitations. Then run the Fix Broken Media Links tool to update the database on their locations. However, you might have to involve the database if the target paths are somehow dependent on data that is not available in the Multimedia list report.
Thanks Tom. I wait for the RM8 version of the renaming process and have started an excel spreadsheet with some VB to look at the multimedia file and hopefully move. Hit a snag with the files in OneDrive. There are always snags, that’s why we do this. Regards Wayne b
I’m trying to update the MediaFile name and am encountering this Error:
$ sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Enter “.help” for usage hints.
Connected to a transient in-memory database.
Use “.open FILENAME” to reopen on a persistent database.
sqlite> .open “../ZebMoore_Ancestry.rmtree”
sqlite> .load “./unifuzz.so”
sqlite> SELECT * FROM MultiMediaTable WHERE MediaID = 1955 ;
1955|1|*\ZebMoore_Ancestry_media|Marriage Cert (B8E331353CF94013B720E83E7C40E999).jpg|||Marriage Cert||D.+18730902..+00000000..|9223372036854775807|Oliver Oliver Ashenhurst & Eva Southern
Marriage Cert.
September 2, 1873
Mercer County, Illinois|45829.6222959259
sqlite> UPDATE MultiMediaTable SET MediaFile = ‘Marriage_Cert.jpg’ WHERE MediaID = 1955 ;
Error: stepping, database disk image is malformed (11)
sqlite>
A similar command has worked for other entries. Any ideas?
I’ve noticed that Margaret’s code uses a JOIN versus and UPDATE to make the change.
Error code 11 is the clue. Try REINDEX and then UPDATE. It’s probable that the mismatch between the RMNOCASE collation and the fake one of unifuzz has contributed to the error and REINDEXing in sqlite will resolve it. Of course, when you reopen the database in RM, the Database Tools> Test Integrity will fail and you will have to Rebuild Indexes in it. Worse case is that the disk image is more seriously malformed and fails on both sides.
Sorry that I didn’t see this reply until after I saw this from Richard Otter: https://github.com/RichardOtter/Genealogy-scripts/blob/main/RM/Run%20SQL/ReadMe.txt
Yes, doing a “REINDEX RMNOCASE;” before performing an UPDATE on any column containing the RMNOCASE collation solved this problem.
And, be sure to Rebuild Indexes in RootsMagic after doing such thing outside of RootsMagic.
BTW, I’m probably in a very small minority of RM users that is examining and/or manipulating the database outside of RM, and even more so, one that is doing so in a Linux environment. I had to update unifuzz for working in Linux to create a unifuzz.so file. …and rather than using Python scripts, I’m using Perl scripts.
Would you be willing to share your unifuzz.so file? I would post it here and credit you.
Do you know if sqlite3 on MacOS can use the same file?
It can be found here: https://github.com/mooredan/unifuzz/tree/master/release (unifuzz.so)
Once I get a Macbook running again, I’ll release the unifuzz.dylib after compiling it.
I’m pretty sure that the two files are not interchange-able. Confident enough that I wouldn’t both trying.