Media Repair Queries #filenames #duplicates #media #links #rmnocase #delete #update

Importing and merging duplicates of persons, families, places and sources already in a database can give rise to unwanted proliferation of duplicate items in the Media Gallery and multiple links to the same media item from a person, family, event, place or source. RMGC_Properties – Query now reports the quantities of such duplicates. A problem database that I worked on had 80 duplicate file names and 160 duplicate links from persons, families, events, places or sources. One image had 42 links from the same Place. As the Media Gallery grows, it becomes increasingly difficult to notice duplicate image file names and much harder to identify and remove duplicate links.

The series of queries in this SQL file help to identify duplicate file names and duplicate links and demonstrates a repair for one case of how duplicate file names can arise and a repair for duplicate links, regardless of cause. The repair queries delete records and, unfortunately, require the use of a SQLite manager capable of faking a RMNOCASE collation sequence to update the associated indexes. The investigative queries can be run on any SQLite manager. CAUTION: untested with RM5 and may give unwanted results due to changes in the media tables.

MediaRepair.sql

Query

1. Lists duplicate media file names

MediaID MediaFile MediaPath
86 _East Dyberry Cemetery 1.JPG d:My DocumentsGenealogyGravestonesUSA – East Dyberry (Wayne Co)
93 _East Dyberry Cemetery 1.JPG E:My DocumentsGenealogyGravestonesUSA – East Dyberry (Wayne Co)
48 bailie-agnes,1885-jan-26-(b).jpg e:My DocumentsGenealogySurgeonerBirths
251 bailie-agnes,1885-jan-26-(b).jpg d:My DocumentsGenealogySurgeonerBirths

2. Creates a 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. NB – this query was for the specific problem of this database: the same files were on two different drives having the same paths.

3. Query #2 produces a list of queries such as this:

command
UPDATE medialinktable SET MediaID=106 WHERE MediaID=107;
UPDATE medialinktable SET MediaID=108 WHERE MediaID=109;
UPDATE medialinktable SET MediaID=110 WHERE MediaID=111;

This list is copied into a SQL edit window of the SQLite manager and executed, sans the header “command”.

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

5. Lists duplicate links in MediaLinkTable to media files in MultimediaTable

LinkID DUPES LinkedTo
138 1 _East Dyberry Cemetery 1.JPG
137 5 _East Dyberry Cemetery 7.JPG
76 1 bailie-agnes,1885-jan-26-(b).jpg
723 41 Ballyclare Town Hall.pcx

6. DELETEs duplicate links from MediaLinkTable. REQUIRES SharpPlus SQLite Developer, SQLiteSpy with extension, or other SQLite manager supporting a fake RMNOCASE collation.

Leave a Reply

Your email address will not be published. Required fields are marked *