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

People Who Share a Fact with a Principal 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 (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
);

MediaTags – Copy Shared Facts Media To Sharee Personal #media #sharedevent

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

This script responds as a workaround to a 13 Dec 2011 wish expressed by MarkVS, that RootsMagic’s Media Album/Gallery and other Media dialog windows that show MediaTags should also show the indirect tags to shared events for the Persons having non-Principal roles, sometimes called “sharees” and the shared event in their context a “sharee event”. To do so can only be done by programming changes; one year later, nothing has changed. A workaround is to generate Personal mediatags from the shared event’s media for these non-Principal sharees. That’s what this script does.

Considerations:

Assuming the shared event’s mediatag is Primary for that event and Included in Scrapbook, so, too is each inherited mediatag for each sharee event. Therefore, the script generates a Primary-No, Scrapbook-No mediatag for the Person to avoid conflict with the truly Personal and other script-generated Personal mediatags and so that the Person Scrapbook does not receive duplicates.
These workaround mediatags probably should have some unique property that would distinguish them from regular mediatags and the indirect ones that may appear if and when RootsMagic is enhanced to display them. The current script does so, perhaps too subtly, by adding a sentence to the Comment field in the Media Tag dialog window (Description field in MediaLinkTable). There are other techniques that might be better suited for future script operations – e.g., an apparently unused Note field in MediaLinkTable or wrapping the sentence in some distinct string sequence such as the “{ … }” pairing used for distinctive privacy flags in Facts – Split Shared to Individual (note that they do not function as privacy flags in the MediaTag Comments/Description field because it is not outputted in any report anyway).
If someone uses Facts – Split Shared to Individual in combination with this script, there certainly will be real duplicate mediatags for each person, one is this workaround Personal tag, the other is the tag for the newly created Individual event. A procedure is needed to delete the duplicate Personal ones. It could rely on a more distinctive branding as discussed in 2) or simply look for basic matches with mediatags for events as in MediaTags – Delete Personal Having Fact Duplicates.

2013-01-05 1.1 outputs media tag for sharees having no defined role in the event.
Sentence in tag description reads “Shared in the …”.
2012-12-28 V1 complete – tags for both family and non-family shared events
2012-12-27 V0 creates mediatags for shared individual events, not for shared family events

MediaTags-CopySharedFactsMediaToShareePersonal.sql

-- MediaTags-CopySharedFactsMediaToShareePersonal.sql
/*
2012-12-27 Tom Holden ve3meo
2012-12-28 V1 complete
2013-01-05 1.1 outputs media tag for sharees having no defined role in the event.
           Sentence in tag description reads "Shared in the ...".
 
Creates a media tag, for media already tagged to a shared event,
 to the Persons sharing the event. The Description contains
 the original Description appended with the Person's role name,
 the event the Person shared in and the name(s) of the Principal(s)
 in the event.
 
Version 1 deals with tags for shared non-family and family events.
 
Use MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by this script.
*/
 
 
INSERT OR REPLACE INTO MediaLinkTable
 
-- tags for shared individual (non-family) events
SELECT
 NULL AS LinkID,
 MediaID,
 0 AS OwnerType,
 OwnerID,
 0 AS IsPrimary,
 0 AS Include1,
 0 AS Include2,
 0 AS Include3,
 0 AS Include4,
 0 AS SortOrder,
 0 AS RectLeft,
 0 AS RectTop,
 0 AS RectRight,
 0 AS RectBottom,
 '' AS Note,
 Caption,
 RefNumber,
 DATE,
 SortDate,
 Description
FROM
(
SELECT
 ML.MediaID AS MediaID,
 W.PersonID AS OwnerID,
 ML.Caption AS Caption,
 ML.RefNumber AS RefNumber,
 ML.DATE AS DATE,
 ML.SortDate AS SortDate,
 ML.Description
  || ' '  || ifnull(R.RoleName, 'Shared')  || ' in the '  || LOWER(F.Name)  ||  ' of '  || N.Given  || ' '  || N.Surname  || '-'  || N.OwnerID
  AS Description
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType = 2 -- Event
 AND E.OwnerType = 0 -- Person
INNER JOIN WitnessTable W
 USING(EventID)
INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
LEFT JOIN RoleTable R ON W.ROLE = R.RoleID
INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE W.PersonID > 0
AND ML.MediaID || '.' || W.PersonID
NOT IN
(
 SELECT DISTINCT MediaID || '.' || OwnerID
 FROM MediaLinkTable
 WHERE OwnerType = 0
 ORDER BY MediaID, OwnerID
 )
 
UNION
-- tags for shared family events
SELECT
 ML.MediaID AS MediaID,
 W.PersonID AS OwnerID,
 ML.Caption AS Caption,
 ML.RefNumber AS RefNumber,
 ML.DATE AS DATE,
 ML.SortDate AS SortDate,
 ML.Description  || ' '  || ifnull(R.RoleName, 'Shared')  || ' in the '  || LOWER(F.Name)  ||  ' of '  || Husb.Given  || ' '  || Husb.Surname  || '-'  || Husb.OwnerID  || ' & '  || Wife.Given  || ' '  || Wife.Surname  || '-'  || Wife.OwnerID
 
  AS Description
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType = 2 -- Event
 AND E.OwnerType = 1 -- FamilyPerson
INNER JOIN FamilyTable Fam
 ON E.OwnerID = Fam.FamilyID
LEFT JOIN NameTable Husb
 ON Fam.FatherID = Husb.OwnerID AND +Husb.IsPrimary
LEFT JOIN NameTable Wife
 ON Fam.MotherID = Wife.OwnerID AND +Wife.IsPrimary
INNER JOIN WitnessTable W
 USING(EventID)
INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
LEFT JOIN RoleTable R ON W.ROLE = R.RoleID
--INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE W.PersonID > 0
AND W.PersonID || '.' || ML.MediaID
NOT IN
(
 SELECT DISTINCT MediaID || '.' || OwnerID
 FROM MediaLinkTable
 WHERE OwnerType = 0
 ORDER BY MediaID, OwnerID
 )
)
 
;
 
-- USE MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by above
;

Fact Inclusion Controls #facttypes #events #gedcom #reports

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

2021-10-28: The following queries are compatible with #RM8 but, while included in , the app itself is not.

If a user has set certain Fact Types to be excluded from GEDCOM export, those fact types are also not transferred to another database via Drag and Drop (DnD). This setting is controlled in the Edit Fact Type window, accessible through the menu path Lists > Fact Type List which opens the Fact Types window through which one can inspect the settings one fact at a time. The Edit button opens the Edit Fact Type window on the selected fact. To ensure that all facts or events for the selected persons are transferred via DnD, one needs to remember to inspect and change the ‘excluded’ facts to ‘included’; following the DnD, the settings wanted for GEDCOM, if different from those for DnD, must be restored. Given the one-at-a-time procedure, it is desirable to have more expeditious ways to change settings. This page offers some short queries that will satisfy some needs until such time as RootsMagic gains more efficient controls. These queries are included in RMtrix.

List Include Settings

This query puts the Include settings for all fact types in table form on one screen for easy inspection. Sorting the list on any column rapidly shows which fact types have been included or excluded from RootsMagic outputs.

Screenshot from SQLiteSpy. 1 = included, 0 = excluded.

-- List FactType Include Settings
SELECT
 Name,
 Flags & 1 AS GEDCOM,
 Flags & 2 <> 0 AS WEB,
 Flags & 4 <> 0 AS FGS,
 Flags & 8 <> 0 AS NARR,
 Flags & 16 <> 0 AS INDIV,
 Flags & 32 <> 0 AS LISTS
FROM FactTypeTable
ORDER BY Name
;

N.B.: As written, the above query (and some of the others) requires SQLiteSpy to have the fake RMNOCASE collation as described at RMNOCASE – faking it in SQLiteSpy.

Save Current Fact Type Settings for Later Restoration

It would be nice to be able to save the current settings, make changes that are temporary, and restore them later. This query simply makes a copy of the FactTypeTable and saves it in the database file. Dead simple and does not affect RootsMagic operations.

-- Save current FactType settings for subsequent restoration
DROP TABLE IF EXISTS zFactTypeTable;
CREATE TABLE IF NOT EXISTS zFactTypeTable
AS SELECT * FROM FactTypeTable;

Set All Fact Types to be Included in GEDCOM and Drag and Drop

This query changes only the GEDCOM include setting so that all fact types are included in both Export and DnD. Similar UPDATE queries could be done for other include settings.

-- Set all FactTypes to be included in GEDCOM and Drag and Drop
UPDATE FactTypeTable
 SET Flags = Flags +1
WHERE NOT Flags & 1
;

Restore Saved Fact Type Settings

Regardless of what changes have been made or how they were made, this query returns the FactTypeTable to the way it was when its then current settings were saved to a backup table.

-- Restore saved FactTypeTable settings
INSERT OR REPLACE INTO FactTypeTable
SELECT * FROM zFactTypeTable;

Other Ideas

Some may want to have more than one set of saved settings and be able to name or describe each set. That is possible; it would require another custom table and the addition of a SettingID column to the table that stores the multiple sets, or a different table for each set.
Direct interaction with the settings from a tabular screen – possible using a high level programming language

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…

List Include Settings

Save Current Fact Type Settings for Later Restoration

Set All Fact Types to be Included in GEDCOM and Drag and Drop

Restore Saved Fact Type Settings

Other Ideas

Discussions & comments from Wikispaces site

Having problems