SQLite Tools for RootsMagic – Page 18 – Exploit your RootsMagic family tree database with SQLite Tools

Multiple Spouses query #spouse #multiples

Posted on 2013-04-262022-04-28 by Tom Holden

Update 2021-03-11: compatible with RM4 to #RM8

When I ran the RootsMagic 4 Statistics List report on my database, I was startled by the stat for Maximum marriages per person – 7 for one or more males! That did not seem right for my genealogy – maybe it was a merging artifact. What if there were others with 7, or 6 or 5 – all of which would be suspect. I could not find which person(s) had so many spouses using RM4, short of running the Marriage List report and inspecting it. For a very large database, this would be hit or miss and slow. So I wrote this simple query to list the persons with multiple spouses. You can use the RIN of persons that bear further investigation to find them in RootsMagic and resolve any problems. In my case, the person with 7 spouses was *Unconnected Research Trees, a method that Laura described to collect loose persons in the database for further investigation.

Multi-spouses.sql

Screenshot from SQLiteSpy showing the query and some of the results. Surnames blurred for privacy.

People Who Share A Fact with a Principal, But Who Are Not in a Tree in The File List – Query #sharedevent #person

Posted on 2013-04-262022-02-21 by romer

Description

Sharing of facts was a new feature added with RootsMagic 4. A Person or Family in the database (a Principal) is now able to share a fact with others either in a tree in the database file by linking to them or with those not in a tree in the file by indicating name only. Those people sharing the event with the Principal are each assigned a role in that fact/event.

Reporting and other capabilities within the program relative to shared facts haven’t yet been fully developed, and there’s currently no way to produce information relating to those not in a tree in the file (as of current version RM 4.0.7.1). However, the following SQL code can be run against the database in order to obtain that information, as well as relevant fact and Principal data:
FactsSharedWithTreelessPersons-OO.sql Download this variant compatible with OpenOffice Base when used in Run SQL Direct mode.
– includes these people in Report > Lifelines of a principal.
Script below is compatible with RM4- #RM7 and #RM8

-- People Who Share A Fact with a Principal, But Who Are Not in a Tree in The File List
-- created by romermb on 26 Feb 2010
 
-- Individual Facts
SELECT   WitnessTable.Surname COLLATE NOCASE AS Surname,
         WitnessTable.Suffix COLLATE NOCASE AS Suffix,
         WitnessTable.Prefix COLLATE NOCASE AS Prefix,
         WitnessTable.Given COLLATE NOCASE AS Given,
         RoleTable.RoleName COLLATE NOCASE AS ROLE,
         FactTypeTable.Name COLLATE NOCASE AS Fact,
         NULL AS MRIN,
         NameTable.OwnerID AS RIN1,
         NameTable.Surname COLLATE NOCASE AS Surname1,
         NameTable.Suffix COLLATE NOCASE AS Suffix1,
         NameTable.Prefix COLLATE NOCASE AS Prefix1,
         NameTable.Given COLLATE NOCASE AS Given1,
         NULL AS RIN2,
         NULL AS Surname2,
         NULL AS Suffix2,
         NULL AS Prefix2,
         NULL AS Given2
FROM     WitnessTable
         LEFT OUTER JOIN RoleTable ON
         WitnessTable.ROLE = RoleTable.RoleID
         LEFT OUTER JOIN EventTable ON
         WitnessTable.EventID = EventTable.EventID
         LEFT OUTER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         LEFT OUTER JOIN NameTable ON
         EventTable.OwnerID = NameTable.OwnerID
WHERE    WitnessTable.PersonID = 0 AND EventTable.OwnerType = 0 AND
         NameTable.IsPrimary = 1
 
UNION ALL
 
-- Family Facts
SELECT   WitnessTable.Surname COLLATE NOCASE AS Surname,
         WitnessTable.Suffix COLLATE NOCASE AS Suffix,
         WitnessTable.Prefix COLLATE NOCASE AS Prefix,
         WitnessTable.Given COLLATE NOCASE AS Given,
         RoleTable.RoleName COLLATE NOCASE AS ROLE,
         FactTypeTable.Name COLLATE NOCASE AS Fact,
         FamilyTable.FamilyID AS MRIN,
         NameTable1.OwnerID AS RIN1,
         NameTable1.Surname COLLATE NOCASE AS Surname1,
         NameTable1.Suffix COLLATE NOCASE AS Suffix1,
         NameTable1.Prefix COLLATE NOCASE AS Prefix1,
         NameTable1.Given COLLATE NOCASE AS Given1,
         NameTable2.OwnerID AS RIN2,
         NameTable2.Surname COLLATE NOCASE AS Surname2,
         NameTable2.Suffix COLLATE NOCASE AS Suffix2,
         NameTable2.Prefix COLLATE NOCASE AS Prefix2,
         NameTable2.Given COLLATE NOCASE AS Given2
FROM     WitnessTable
         LEFT OUTER JOIN RoleTable ON
         WitnessTable.ROLE = RoleTable.RoleID
         LEFT OUTER JOIN EventTable ON
         WitnessTable.EventID = EventTable.EventID
         LEFT OUTER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         LEFT OUTER JOIN FamilyTable ON
         EventTable.OwnerID = FamilyTable.FamilyID
         LEFT OUTER JOIN NameTable AS NameTable1 ON
         FamilyTable.FatherID = NameTable1.OwnerID
         LEFT OUTER JOIN NameTable AS NameTable2 ON
         FamilyTable.MotherID = NameTable2.OwnerID
WHERE    WitnessTable.PersonID = 0 AND EventTable.OwnerType = 1 AND
         NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1
 
ORDER BY 1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 14, 15, 16, 17

Discussions & comments from Wikispaces site

People Who Share a Fact with a Principal List – Query #sharedevent #person

Posted on 2013-04-262022-02-21 by romer

Description

Reporting and other capabilities within the program relative to shared facts haven’t yet been fully developed (as of current version RM 4.0.7.1). However, the following SQL code can be run against the database in order to obtain that information, as well as relevant fact and Principal data:

and compatible with RM4- #RM7 and #RM8

-- People Who Share A Fact with a Principal List
-- created by romermb on 10 Mar 2010
 
-- Individual Facts, Person Sharing Fact in Tree in File
SELECT   WitnessTable.PersonID AS RIN,
         NameTable.Surname COLLATE NOCASE AS Surname,
         NameTable.Suffix COLLATE NOCASE AS Suffix,
         NameTable.Prefix COLLATE NOCASE AS Prefix,
         NameTable.Given COLLATE NOCASE AS Given,
         RoleTable.RoleName COLLATE NOCASE AS ROLE,
         FactTypeTable.Name COLLATE NOCASE AS Fact,
         NULL AS MRIN,
         NameTable1.OwnerID AS RIN1,
         NameTable1.Surname COLLATE NOCASE AS Surname1,
         NameTable1.Suffix COLLATE NOCASE AS Suffix1,
         NameTable1.Prefix COLLATE NOCASE AS Prefix1,
         NameTable1.Given COLLATE NOCASE AS Given1,
         NULL AS RIN2,
         NULL AS Surname2,
         NULL AS Suffix2,
         NULL AS Prefix2,
         NULL AS Given2
FROM     WitnessTable
         LEFT OUTER JOIN NameTable ON
         WitnessTable.PersonID = NameTable.OwnerID
         LEFT OUTER JOIN RoleTable ON
         WitnessTable.ROLE = RoleTable.RoleID
         LEFT OUTER JOIN EventTable ON
         WitnessTable.EventID = EventTable.EventID
         LEFT OUTER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         LEFT OUTER JOIN NameTable AS NameTable1 ON
         EventTable.OwnerID = NameTable1.OwnerID
WHERE    WitnessTable.PersonID <> 0 AND EventTable.OwnerType = 0 AND
         NameTable.IsPrimary = 1 AND NameTable1.IsPrimary = 1
 
UNION ALL
 
-- Family Facts, Person Sharing Fact in Tree in File
SELECT   WitnessTable.PersonID AS RIN,
         NameTable.Surname COLLATE NOCASE AS Surname,
         NameTable.Suffix COLLATE NOCASE AS Suffix,
         NameTable.Prefix COLLATE NOCASE AS Prefix,
         NameTable.Given COLLATE NOCASE AS Given,
         RoleTable.RoleName COLLATE NOCASE AS ROLE,
         FactTypeTable.Name COLLATE NOCASE AS Fact,
         FamilyTable.FamilyID AS MRIN,
         NameTable1.OwnerID AS RIN1,
         NameTable1.Surname COLLATE NOCASE AS Surname1,
         NameTable1.Suffix COLLATE NOCASE AS Suffix1,
         NameTable1.Prefix COLLATE NOCASE AS Prefix1,
         NameTable1.Given COLLATE NOCASE AS Given1,
         NameTable2.OwnerID AS RIN2,
         NameTable2.Surname COLLATE NOCASE AS Surname2,
         NameTable2.Suffix COLLATE NOCASE AS Suffix2,
         NameTable2.Prefix COLLATE NOCASE AS Prefix2,
         NameTable2.Given COLLATE NOCASE AS Given2
FROM     WitnessTable
         LEFT OUTER JOIN NameTable ON
         WitnessTable.PersonID = NameTable.OwnerID
         LEFT OUTER JOIN RoleTable ON
         WitnessTable.ROLE = RoleTable.RoleID
         LEFT OUTER JOIN EventTable ON
         WitnessTable.EventID = EventTable.EventID
         LEFT OUTER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         LEFT OUTER JOIN FamilyTable ON
         EventTable.OwnerID = FamilyTable.FamilyID
         LEFT OUTER JOIN NameTable AS NameTable1 ON
         FamilyTable.FatherID = NameTable1.OwnerID
         LEFT OUTER JOIN NameTable AS NameTable2 ON
         FamilyTable.MotherID = NameTable2.OwnerID
WHERE    WitnessTable.PersonID <> 0 AND EventTable.OwnerType = 1 AND
         NameTable.IsPrimary = 1 AND NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1
 
UNION ALL
 
-- Individual Facts, Person Sharing Fact Not in Tree in File
SELECT   NULL AS RIN,
         WitnessTable.Surname COLLATE NOCASE AS Surname,
         WitnessTable.Suffix COLLATE NOCASE AS Suffix,
         WitnessTable.Prefix COLLATE NOCASE AS Prefix,
         WitnessTable.Given COLLATE NOCASE AS Given,
         RoleTable.RoleName COLLATE NOCASE AS ROLE,
         FactTypeTable.Name COLLATE NOCASE AS Fact,
         NULL AS MRIN,
         NameTable.OwnerID AS RIN1,
         NameTable.Surname COLLATE NOCASE AS Surname1,
         NameTable.Suffix COLLATE NOCASE AS Suffix1,
         NameTable.Prefix COLLATE NOCASE AS Prefix1,
         NameTable.Given COLLATE NOCASE AS Given1,
         NULL AS RIN2,
         NULL AS Surname2,
         NULL AS Suffix2,
         NULL AS Prefix2,
         NULL AS Given2
FROM     WitnessTable
         LEFT OUTER JOIN RoleTable ON
         WitnessTable.ROLE = RoleTable.RoleID
         LEFT OUTER JOIN EventTable ON
         WitnessTable.EventID = EventTable.EventID
         LEFT OUTER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         LEFT OUTER JOIN NameTable ON
         EventTable.OwnerID = NameTable.OwnerID
WHERE    WitnessTable.PersonID = 0 AND EventTable.OwnerType = 0 AND
         NameTable.IsPrimary = 1
 
UNION ALL
 
-- Family Facts, Person Sharing Fact Not in Tree in File
SELECT   NULL AS RIN,
         WitnessTable.Surname COLLATE NOCASE AS Surname,
         WitnessTable.Suffix COLLATE NOCASE AS Suffix,
         WitnessTable.Prefix COLLATE NOCASE AS Prefix,
         WitnessTable.Given COLLATE NOCASE AS Given,
         RoleTable.RoleName COLLATE NOCASE AS ROLE,
         FactTypeTable.Name COLLATE NOCASE AS Fact,
         FamilyTable.FamilyID AS MRIN,
         NameTable1.OwnerID AS RIN1,
         NameTable1.Surname COLLATE NOCASE AS Surname1,
         NameTable1.Suffix COLLATE NOCASE AS Suffix1,
         NameTable1.Prefix COLLATE NOCASE AS Prefix1,
         NameTable1.Given COLLATE NOCASE AS Given1,
         NameTable2.OwnerID AS RIN2,
         NameTable2.Surname COLLATE NOCASE AS Surname2,
         NameTable2.Suffix COLLATE NOCASE AS Suffix2,
         NameTable2.Prefix COLLATE NOCASE AS Prefix2,
         NameTable2.Given COLLATE NOCASE AS Given2
FROM     WitnessTable
         LEFT OUTER JOIN RoleTable ON
         WitnessTable.ROLE = RoleTable.RoleID
         LEFT OUTER JOIN EventTable ON
         WitnessTable.EventID = EventTable.EventID
         LEFT OUTER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         LEFT OUTER JOIN FamilyTable ON
         EventTable.OwnerID = FamilyTable.FamilyID
         LEFT OUTER JOIN NameTable AS NameTable1 ON
         FamilyTable.FatherID = NameTable1.OwnerID
         LEFT OUTER JOIN NameTable AS NameTable2 ON
         FamilyTable.MotherID = NameTable2.OwnerID
WHERE    WitnessTable.PersonID = 0 AND EventTable.OwnerType = 1 AND
         NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1
 
ORDER BY 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 15, 16, 17, 18

Discussions & comments from Wikispaces site

Paragraphing #update #paragraphing #reports

Posted on 2013-04-262019-01-26 by Tom Holden

Paragraph control in the narrative reports in RootsMagic 4 and 5 leaves much to be desired. A richly facted person with many notes will be described in one long paragraph, apart from any paragraphing within the body of a fact note, unless special non-intuitive measures are taken. Likewise, another long paragraph of family facts and notes follows.

Different folks have tried different strategies with varying success. Basically, there are two:

Use the Customize Sentence feature for Facts in the Edit Person screen and enter double Carriage Return/Line Feeds (the Enter key or Ctrl-M in the editor) at the beginning of the sentence of the fact for which you want to start a new paragraph (often after a long note from the preceding fact).
Add double CR/LFs at the end of notes of facts following which you want the next fact sentence to be in a new paragraph.

In both cases, one is likely going to want to do further paragraphing touch-ups in Microsoft Word on the RTF file saved from the RM Report Viewer. Also, the first creates new paragraphs even if notes are excluded from a report; that may not be wanted. The second does not survive a transfer; trailing white-space in notes is truncated on a GEDCOM export or drag’n’drop transfer between RM databases.

Until RootsMagic provides better control of paragraphing and persistence through export and transfer, it is desirable to have some batch process that can quickly provide a first cut at paragraphing reasonably. This page and its queries attempt to provide such tools.

Paragraph-strip.sql This query strips out leading and trailing CR/LF and blank spaces from the custom fact sentences and fact notes for persons and families. Execute it repeatedly until you think you have stripped out prior paragraphing. Twice should be enough if there was never more than two pairs of CR/LF entered at the beginning of a custom sentence or at either the beginning or end of a fact note. Paragraphing within the body of a note is unaffected.

Paragraph-add.sql This query so far addresses only paragraphing of person facts. It adds double CR/LFs at the end of each non-empty fact note and then attempts to strip them from the last note before the beginning of the family notes or children.It’s largely untested – feedback invited. If the Person facts are paragraphing as intended, then the same strategy may work with family notes leading to the spouse and child list.

(
Added by Jerry, 12/23/2011). I have done some testing of some of the items in Paragraph-strip.sql. For now, I’m focusing on the General (Individual) Note only. The SQL that I tested ran just fine. I have now run it in a small test database, in a copy of my production database, and in my production database. Using the SQL scripts saved me many, many hours of very tedious and error prone work.

The only little white space glitch I found was that in addition to blanks and CR/LF sequences, I found some leading TAB characters at the front of a few notes (CTL-I or X”09″). These were introduced into my database via GEDCOM import over a decade ago, before I really knew what I was doing in managing my database. I would have eventually found and cleaned them up anyway, but Paragraph-strip.sql greatly facilitated the process. In the meantime, here’s a very simple little query that I ran to monitor the progress of the changes I was making with SQL from Paragraph-strip.SQL. That’s how I found the TAB characters. I used an equivalent query to monitor changes on the right end of the notes.)

Jerry
)

SELECT P.PersonID,
       HEX( SUBSTR(P.Note,1,1) ) AS LeftEndH1,
       P.Note
FROM
       PersonTable AS P
       WHERE LENGTH(P.Note) > 0
 
ORDER BY LeftEndH1, P.Note

Discussions & comments from Wikispaces site

Place Details without a Place #placedetails #phantom

Posted on 2013-04-262022-04-28 by Tom Holden

Orphaned Place Details

On integrating these queries into the RMtrix bundled utilities, I found it necessary to revise them extensively, partly because they do not safely or efficiently address those orphaned Place Details that are actually used by a fact/event in the database. Moreover, without the foreknowledge of the names of the Place Details, it is impossible to find them using RootsMagic tools. My revised queries provide both an efficient way of finding where the orphaned sites are used, so that we can get at them with the Edit Person screen, and a speedy deletion of only those that are unused.

Screenshot from RMtrix of results from the OrphanedPlaceDetails query. The unused ones have blanks for Person and Fact; the used ones can be readily fixed by using RM5 to edit the fact for the person shown.

FAQ

What is an Orphaned Place Detail? It’s a Place Detail for which there is no parent Place in the PlaceTable.
What’s the problem with them? Unused ones merely clutter up one table. While ones that are used do appear to come out alright in narrative reports, they do not show in the Place List, they cannot be: edited, have images tagged to them, geotagged nor commented.
How do they arise in the first place? One way for certain is that a Place Detail can be added through the Edit Person screen with the Place field left empty (as of RM5021). Other possibilities are from past deletion or merging of the parent.
How do I fix the ones I want to keep? Use the OrphanedPlaceDetails query in RMtrix or download and run it in SQLiteSpy to get the report. Using RootsMagic, select the persons listed and find the fact in their Edit Person screen; assign the Place and then add the Place Detail.
How do I get rid of the unused ones? Orphaned Place Details – Delete Unused in RMtrix or download and run it in SQliteSpy. WARNING – there is risk of database corruption of the sort that RM5 can repair but RM4 cannot. After the deletion, use RM5’s Database Tools > Rebuild Indexes. Best to leave the unused Place Details alone in RM4.

Downloads

OrphanedPlaceDetails.sql

OrphanedPlaceDetails_DeleteUnused.sql

Original Post from Jerry Bryan

I’ve discovered that I have four Place Details in my database without a Place.RMGC_Properties – Query The situation can be identified with the following extremely trivial query.

SELECT *
    FROM PlaceTable
    WHERE PlaceType = 2
                 AND
          MasterID = 0

PlaceType = 2 identifies PlaceTable entries that are for Place Details, and if PlaceType = 2 then MasterID identifies the associated Place with which the Place Details are associated. MasterID=0 indicates that there is no Place associated with the Place Details.
[Inline comment:

external image user_none_lg.jpg ve3meo Jan 30, 2012

RMGC_Properties – Query flags all unused Place Details, including those with no master Place, in the row “-Unused Place Details”. However, it does not report a Place Detail used in a fact but lacking a master Place as that may be a legitimate use. For example, the Occupation fact – we might know that he worked at General Motors but not in which municipality. I suggest you run this query as you might discover some other surprises. It needs work to update the Media reporting to RM5.
]

For each of the four Place Details in question, I did a search within RM5 itself for “Any Fact Place Details contains (the Place Details in question)”. In three cases, nothing was found. In the fourth case, RM5 itself found the Place Details in question associated with a blank Place (basically, an impossible situation).

I have not run any of Tom’s Place/Place Details queries against my database. Whatever strange thing happened to my database just happened between me doing data entry with the keyboard and mouse and then with whatever processing RM4/RM5 did behind the scenes. I include both RM4 and RM5 in this scenario because I don’t know when this little glitch in my database happened – before RM5 or after RM5.

I decided to fix my database with the following and equally trivial query.

DELETE
    FROM PlaceTable
    WHERE PlaceType = 2
                 AND
          MasterID = 0

But the DELETE query will not run. It gets the infamous error message: SQLite Error 1 – no such collation sequence: RMNOCASE. I don’t understand why there is an RMNOCASE error when both of the data elements I’m testing are numeric.
[inline comment: “why there is an RMNOCASE error when both of the data elements I’m testing are numeric”

external image user_none_lg.jpg ve3meo Jan 30, 2012

Because the query tries to delete a record with a field (PlaceTable.Name) that is so collated and that field is used in an index (idxPlaceName) for that table. Hence the index must be updated and cannot be. If you use SQLiteSpy with the fake RMNOCASE extension, you will succeed in deleting these records. However, you should follow up in RM5 with the Database Integrity Check and, if errors reported in an index, then the Reindex tool.

Tom

]
Jerry

Added by Jerry Bryan 3/6/2012

I have concluded that my original description of orphaned Place Details was just a symptom of a larger problem. Namely, somewhere in the process of using the RM 5.0.2.1 feature to split a Place into Place + Place Details and merging any resultant duplicate places, there appears to be a bug whereby one of the duplicate places being merged is deleted without the all the pointers in the EventsTable for that place being adjusted to point to the entry in the PlaceTable that is being kept. The following query will identify all such orphaned place pointers in the Events Table.

/*
     This query identifies all events in the EventTable which contain a PlaceID value that does
     not appear as a PlaceID value in the PlaceTable.  This is an "impossible" situation which
     should never occur.  However, due to a possible bug in RM 5.0.2.1 or other unknown cause,
     this "impossible" situation has occurred one time in my database.
 
     This "impossible" situation occurred after numerous repetitions of splitting a Place
     into Place + Place Details and subseqently merging the resultant duplicate Place values.
 
     If the database against which this query is run does not have the problem, then the query
     will return no results.  So "no results" is the sign of success.
*/
 
 
 
SELECT L.EventID, L.EventType, L.OwnerType, L.OwnerID, L.FamilyID, L.PlaceID, P.PlaceID
   FROM
       (
        SELECT EventID, EventType, OwnerType, OwnerID, FamilyID, PlaceID
        FROM EventTable
        WHERE PlaceID != 0          /* Eliminate events without places in the sub-query  */
        ORDER BY PlaceID, EventID
       ) AS L
 
             LEFT JOIN
 
        PlaceTable AS P ON L.PlaceID = P.PlaceID
 
    WHERE P.PlaceID IS NULL;     /* Events with a place, but the place is not in the PlaceTable  */

Discussions & comments from Wikispaces site

Inline comments

Four Little Queries #names #blanks #placedetails

Posted on 2013-04-262019-01-26 by Tom Holden

Query Name	Description	File
blankname_in_addresslist	List Persons with Blank Names in the Address List — (a fault that may occur in a GEDCOM import).	RM4_Queries.sql
selected_surnames	List Persons with specified Surnames. — Example of creating a SQL View or Virtual Table and the explicit use of COLLATE NOCASE to override the RMNOCASE collation defined for certain fields and embedded in the RootsMagic application.
PlacesDetails	Lists Places having Place Details
UnusedPlaces	List of unused Places

The queries above are included in the one file. Some SQLite managers can import a query file and some can also export a query file (e.g. SQLiteman does both). Others may require you to open the file with a text editor and copy/paste the query into the SQLite manager’s query editor. With SQLiteman, you place the cursor anywhere in a query line and that is the one executed; SQLiteSpy requires you to highlight the selected command and execute using Ctrl+F9 rather than F9 which would run the whole listing. Each query must end with a semi-colon to demark it from the others.

Optionally, the queries can be expanded to include the CREATE VIEW command by deleting the semicolon at the end of its line. When successfully executed, the query is embedded in the database as a virtual table which remains with the database file until it is dropped (deleted). Some SQLite managers allow you to save a query as a view from the user interface, putting a GUI in front an internal CREATE VIEW.

Duplicate Name Search – query #duplicates #names

Posted on 2013-04-262022-04-27 by Tom Holden

This query lists duplicate name pairs with a weighted score indicating the degree of match similar to RootsMagic 4’s Duplicate Search Merge tool. On a large database, it produced useful results in 4.5 minutes compared to 45 minutes for RM4. It operates with fixed settings similar to DSM’s default settings, e.g. Birth Years within 2 years of each other. In use, one would run the query to produce a report and from its list carry out Manual Merge in RootsMagic.

Download: DuplicateNameSearch.sql

Screenshot of results of DuplicateNameSearch query

Screenshot of RootsMagic Duplicate Search Merge

Media Type Reset #media #update

Posted on 2013-04-262023-02-19 by Tom Holden

Users can mistakenly add image files to the Media Gallery with the wrong MediaType which prevents the images from being used in reports. MediaType is normally set by user selection in the Add Media Item dialog and cannot be changed from within RootsMagic 6.0.0.2 and earlier. Within RM the only option is to add the media item again selecting Image as the Media type in the Add Media Item dialog. Of course, the item must be tagged to all the same things as the mis-typed item and the latter must be removed from the Gallery. This might be manageable for a few such mistakes but not if one had consistently erred on a large number of additions. Media Type Reset can correct all mis-typed errors in seconds.

The MediaTypeReset script sets the MultimediaTable.MediaType according to the file extension of the media file. It first sets all MediaType values to 0 (untyped), then sets Image (1), Sound (3) and Video (4) types according to the file extensions. All remaining entries are set to File (2).

It is unclear that RootsMagic makes any distinction among the File, Sound and Video types other than the filters applied when browsing for the file to be added and to display a symbolic thumbnail. Regardless, double-clicking on such items in the Media Gallery opens the file with its associated external application. None are included in RM reports.

When Image type is selected, the Scanner button is enabled and the disk browsing filter is set to an undeclared list of image file extensions. By testing with many file type extensions, only those types that pass the browser filter have been included in the script as only those that the Image Viewer/Editor can render should be allowed. More significantly, the Image explorer mimics but seems different from the Windows Explorer opened for the other file types and may ‘remember’ a different path. Some users may prefer to use a consistent browser. Using the File type along with MediaTypeReset allows one to do so.

On adding an item selected through the Image type, RootsMagic generates a thumbnail image. For images added via the other types, no thumbnail is created nor are they used in any reports. After running MediaTypeReset, the thumbnail is generated on opening an Album to which the item is tagged, or on opening the Media Gallery, subject to Program Options.

MediaTypeReset.sql Download

rev 2023-02-19 added the .mp4 extension as a “Video” type. Tested without error on #RM8.

contains the 2012-12-09 version which omitted the .mp4 extension

-- MediaTypeReset.sql
/*
2012-12-04 Tom Holden ve3meo
2012-12-09 restricted Image file types to those rendered by Viewer/Reporter
2023-02-19 MP4 added

Users can mistakenly add image files to the Media Gallery with the 
wrong MediaType which prevents the images from being used in reports.
MediaType is normally set by the user selection in the Add Media Item dialog
and cannot be changed from within RootsMagic 6.0.0.2 and earlier.
This script sets the MediaType according to the file extension of the media file.
*/

-- Clear all previous settings
UPDATE MultimediaTable
 SET MediaType=0
;
-- Set Image files (only RM Add Media filters + .targ, .tiff because of viewer/reporter limitations)
UPDATE MultimediaTable
 SET MediaType=1
  WHERE LOWER(SUBSTR(MediaFile,-4)) 
  IN ('.bmp','.gif','.jpg','jpeg','.png','.tga','targ','.tif','tiff')
; 
-- Set Sound files (RM Add Media filters on just .wav, .mid, .mp3 but no player to set constraints)
UPDATE MultimediaTable
 SET MediaType=3
  WHERE LOWER(SUBSTR(MediaFile,-4)) 
  IN ('.wav','.mid','.mp3','.ogg','.gsm','.dct','flac','aiff','.vox','.raw','.wma','.aac')
  OR LOWER(SUBSTR(MediaFile,-3)) 
  IN ('.au','.ra','ram','dss','msv','dvf','ape')
;
-- Set Video files (RM filters on Add Media only for .avi, .mov, .mpg, .mpeg, .wmv but no player to set constraints)
UPDATE MultimediaTable
 SET MediaType=4
  WHERE LOWER(SUBSTR(MediaFile,-4)) 
  IN ('.3gp','.asf','.avi','.dat','.flv','.m4v','.mkv','.mov','mp4','mpeg','.mpg','.mpe','.swf','.wmv')
;
-- Set files, other than previously set Image, Sound, Video types, to File type
UPDATE MultimediaTable
 SET MediaType=2
  WHERE MediaType=0
;

Media – Delete Unused #media #delete

Posted on 2013-04-262019-01-26 by Tom Holden

Deletes records from MultiMediaTable untagged by any Person, Family, Fact, Place, Source or Citation. Was formerly included in Delete Phantoms.

MediaDeleteUnused.sql

-- MediaDeleteUnused.sql
/*
2013-01-21 Tom Holden ve3meo
 
Deletes records from MultiMediaTable untagged by
any Person, Family, Fact, Place, Source or Citation.
 
Extracted from DeletePhantoms.sql
*/
DELETE
FROM MultimediaTable
WHERE MediaID NOT IN (
        -- Person media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 0
            AND OwnerID IN (
                SELECT PersonID
                FROM PersonTable
                )
 
        UNION
 
        -- Family media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 1
            AND OwnerID IN (
                SELECT FamilyID
                FROM FamilyTable
                )
 
        UNION
 
        -- Event Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 2
            AND OwnerID IN (
                SELECT EventID
                FROM EventTable
                )
 
        UNION
 
        -- Master Source Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 3
            AND OwnerID IN (
                SELECT SourceID
                FROM SourceTable
                )
 
        UNION
 
        -- Citation Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 4
            AND OwnerID IN (
                SELECT CitationID
                FROM CitationTable
                )
 
        UNION
 
        -- Place Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 5
            AND OwnerID IN (
                SELECT PlaceID
                FROM PlaceTable
                )
        );

MediaTags – Delete Personal Having Fact Duplicates #media

Posted on 2013-04-262019-01-26 by Tom Holden

It is possible to excessively tag a media item to a Person and his/her Facts/Events. If someone uses Facts – Split Shared to Individual AND MediaTags – Copy Shared Facts Media To Sharee Personal, there certainly will be real duplicate mediatags for each person, one is the tag for the newly created Individual event from the first script, the other
is the workaround Personal tag generated by the second. A procedure is needed to delete the duplicate Personal ones. This script endeavours to do that, based on the unique combinations of MediaID and OwnerID of events having media tags; Personal media tags having the same combination are deleted.

MediaTags-DeletePersonalHavingFactDupes.sql

-- MediaTags-DeletePersonalHavingFactDupes.sql
/*
2012-12-28 Tom Holden ve3meo
 
Deletes Personal MediaTags (Person-General) for media
having duplicate tags to an event for the same person.
*/
 
-- SELECT LinkID, MediaID, OwnerID -- testing
DELETE
FROM MediaLinkTable
WHERE OwnerType=0
AND MediaID || '.' || OwnerID
IN
(
-- build ordered list of event mediatags in form MediaID.PersonID
SELECT DISTINCT MediaID || '.' || PersonID AS MediaPerson
FROM
(
-- LinkIDs for PersonID's having Indiv Event MediaTags
SELECT DISTINCT LinkID, MediaID, E.OwnerID AS PersonID FROM MediaLinkTable ML
INNER JOIN EventTable E
ON ML.OwnerID = E.EventID
 AND ML.OwnerType=2
 AND E.OwnerType = 0
 
UNION
 
-- LinkIDs for Husband PersonID's having Family Event MediaTags
SELECT DISTINCT LinkID, MediaID, F.FatherID AS HusbandID FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType=2
 AND E.OwnerType = 1
INNER JOIN FamilyTable F
 ON E.OwnerID = F.FamilyID
 
UNION
 
-- LinkIDs for Wife PersonID's having Family Event MediaTags
SELECT DISTINCT LinkID, MediaID, F.MotherID AS WifeID
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType=2
 AND E.OwnerType = 1
INNER JOIN FamilyTable F
 ON E.OwnerID = F.FamilyID
)
ORDER BY MediaPerson
);

Description

Discussions & comments from Wikispaces site

Nomenclature

Description

Discussions & comments from Wikispaces site

Add date field to query

Thanks

Discussions & comments from Wikispaces site

Italics in the fact notes

Orphaned Place Details

FAQ

Downloads

Original Post from Jerry Bryan

Discussions & comments from Wikispaces site

No names in “Person” column

Inline comments

Comment: RMGC_Properties – Query flags all unu…

Comment: Because the query tries to delete a r…