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.
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.