A Sample Query Created with Views #events #colorcoding #sql #views #citations #sources

Posted on 2018-03-172019-01-25 by thejerrybryan

I don’t post here as often as I probably should. I find that most of my SQL queries are quite specific to my own personal research and are of little general interest. On the other hand, Tom has a genius for coming up with queries that are of very general utility. The queries I’m going to post in this particular message are probably very much in the category of being quite specific to my own personal research, but perhaps some of the techniques I’m using might be of interest to others. In particular, for complex queries I now use SQL VIEW statements to create a number of views, and I then construct my queries from these views. This breaks the query into smaller pieces, which I find simpler. I’m just a simple country boy, so I like simple.

Having said that, breaking complex tasks into many smaller pieces can be taken too far, to the point that managing the pieces can become more complex than the original problem was. The reader will have to decide if I have gone too far in that direction. Also, using VIEW statements can make SQL queries run a bit slower than they would
otherwise. Again, the reader will have to decide if the technique of using views is advantageous or not. It is advantageous for me, but it might not be advantageous for everybody.

The overall query we are going to be working on is to identify facts without sources for a particular sub-population of individuals in my database. So we have to go through a process identifying the individuals in the sub-population, identifying their facts (both individual and family), and identifying which of those facts do not have sources. We start by considering a very simple query to list the values in RM’s PersonTable for everyone who is color coded as red. Events-CitationsMissing-UsingViews.sql 2017-04-17 This compilation of many of the following scripts can be downloaded and executed to list events with no citations.

SELECT P.*
FROM PersonTable AS P
WHERE P.Color = 1;

I’m going to present a number of similar examples in the same general vein, but in the meantime I would point out the following. There is no particular magic about the color red. I just needed an example. I could have chosen a different color or combination of colors for the example. I could could have chosen an RM Named Group for the example, but the example would have been slightly more complicated. Etc. But the point is that it’s very easy in SQL to test for color coding. So I have moved some selection logic that might possibly be very complicated to do in SQL such as “all the descendants of John Doe” out of SQL and into RM. Which is to say, it’s very easy in RM to color code all descendants of John Doe as red but it’s not so easy in SQL to select all the descendants of John Doe.

Having created this query, we can run it to test it. We can then convert it into a VIEW as follows. We DROP the VIEW before doing a CREATE so that the query can be run multiple times. We include IF EXISTS so that the DROP will run correctly the first time before the VIEW has ever been created. And we make a TEMP VIEW so that the VIEW is kept in memory and doesn’t clutter up the actual database.

DROP VIEW IF EXISTS PersonTableView;
CREATE TEMP VIEW PersonTableView AS
SELECT P.*
FROM PersonTable AS P
WHERE P.Color = 1;

Having created a view in this manner, we can then invoke it to actually use it or just to test it again as follows.

SELECT P.*
FROM PersonTableView AS P;

Obviously, if this is all we have done, we have introduced extra work and complexity into an SQL query process for no particular advantage. The advantage comes only when we construct more complicated queries. In particular, we can replace every reference to PersonTable in a complex query with a reference to PersonTableView. Having done so, the query is automatically filtered to include only those individuals who are color coded as red (or whatever filtering we have specified in the definition of PersonTableView).

Here are some other examples of other possible definitions of PersonTableView.

-- PersonTableView Example #1
--
-- This one filters on the color maroon and it omits any individuals whose
-- given name contains the string "dummy". I have a number of dummy people
-- in my database,  denoted by the string "dummy" in the given name field.
-- For many queries, I wish to exclude these dummy people. This query does
-- a JOIN with the NameTable to gain access to the given name, but it does
-- not select any of the columns from the NameTable to be included in the view.
-- A similar idea could be used to filter on a particular surname, e.g.
--
--             AND N.Surname LIKE('Smith%');
--
DROP VIEW IF EXISTS PersonTableView;
CREATE TEMP VIEW PersonTableView AS
SELECT P.*
FROM PersonTable AS P
       INNER JOIN
     NameTable AS N ON N.OwnerID = P.PersonID
WHERE P.Color = 8 AND N.Given NOT LIKE('%dummy%'); -- color 8 is maroon
 
-- PersonTableView Example #2
--
-- This example filters on a Named Group which is defined within
-- RM. I have hard coded the group number as group 37 for simplicity,
-- but it would also be easy to include a JOIN between the GroupTable
-- and the LabelTable to specify the name of the group by its name.
-- In order to hard code the group number, you have to look up the
-- group number in the LabelTable yourself which is completely trivial.
-- For the purposes of this example, I have assumed that we have done
-- so and that the group of interest is group 37. The query
-- does a cross JOIN between the GroupTable and the PersonTable for the
-- purpose of making the selection with a WHERE statement, but no columns
-- from the GroupTable are selected in the query. (A cross JOIN is when you
-- list multiple table names and you are separate them with a comma rather
-- than with a JOIN statement.)
--
DROP VIEW IF EXISTS PersonTableView;
CREATE TEMP VIEW PersonTableView AS
SELECT P.*
FROM PersonTable AS P,
     GroupTable AS G
WHERE G.GroupID=37
         AND
      G.StartID <= P.PersonID AND P.PersonID <= G.EndID;
 
-- PersonTableView Example #3
--
-- This example filters on a small list of people based on their
-- PersonID. Such a VIEW is usually most useful during the
-- development and debugging of a query to target the query
-- towards a very small population of individuals who have
-- the properties of interest to the query.
--
DROP VIEW IF EXISTS PersonTableView;
CREATE TEMP VIEW PersonTableView AS
SELECT P.*
FROM PersonTable AS P
WHERE P.PersonID IN (3,17,33);

Having created a filtered PersonTable called PersonTableView, you might like to have a filtered NameTable called NameTableView to go along with it. Such a view might be created by SQL such as the following.

-- NameTableView example.-- Create a view of the FactTypeTable filtered to exclude dummy facts.
DROP VIEW IF EXISTS NameTableView;
CREATE TEMP VIEW NameTableView AS
SELECT N.*
FROM NameTable AS N
        INNER JOIN
     PersonTableView AS P ON N.OwnerID = P.PersonID;

You could then use NameTableView instead of NameTable in queries, or you could just list your filtered NameTable as follows.

SELECT N.*
FROM NameTableView AS N;

Having created a filtered PersonTable called PersonTableView, you might like to have a filtered FamilyTable called FamilyTableView to go along with it. Such a view might be created by SQL such as the following.

-- FamilyTableView example.
--
-- The FamilyTable is filtered to include only individuals who are included in the filtered PersonTable.
-- The DISTINCT parameter is needed to avoid selecting the same row of the FamilyTable twice if
-- both the Father and the Mother are in PersonTableView. The VIEW is set up to select the family
-- if either or both spouses are in the PersonTableView. If you want the VIEW to select the family
-- only if both spouses are in the PersonTableView, change the OR to AND.
DROP VIEW IF EXISTS FamilyTableView;
CREATE TEMP VIEW FamilyTableView AS
SELECT DISTINCT F.*
FROM FamilyTable AS F
        INNER JOIN
     PersonTableView AS P ON (F.FatherID = P.PersonID OR F.MotherID = P.PersonID);

We could go on in this same vein for a while, just giving examples of how to make queries without actually making a query. Instead, let us now try to make an example that might actually be useful. In particular, let us make a query that lists facts without a source. We start by making a View of the FactTypeTable. As will continue to be the case, much of this view is specific to my own research, but I include it to present ideas.

-- FactTypeTableView example.
--
-- I use a lot of dummy facts - facts that will never appear in any reports but
-- which record "to do" type of information, research log type of information, etc.
-- Probably nobody else than me does this, but it still provides a good example.
-- The VIEW omits these dummy facts. I use the standard Reference Number fact
-- as a dummy fact (I'm phasing out this usage), and facts whose name incudes an
-- asterisk as dummy facts. This query omits such fact types from the VIEW.
-- If this type of filtering is not needed, you could just reference the FactTypeTable
-- directly without creating a VIEW or else have a FactTypeView that includes
-- the entire FactTypeTable.
--
DROP VIEW IF EXISTS FactTypeTableView;
CREATE TEMP VIEW FactTypeTableView AS
SELECT F.*
FROM FactTypeTable AS F
WHERE (F.Abbrev NOT LIKE '%*%' AND F.Abbrev NOT LIKE 'Ref%');

We now use the filtered VIEW of the FactTypeTable to create a filtered VIEW of the EventTable. Obviously, the filtering of the FactTypeTable and the filtering of the EventTable could have been done in a single VIEW. But I find it simpler to split the filtering into separate pieces.

-- EventTableView example.
--
-- This VIEW simply applies the concept of dummy facts from the FactTypeTable to the EventTable.
-- It also adds one new variable so that that the EventTableView has the name of fact type available
-- to display for each event, a piece of data that is not available in the EventTable itself.
-- I wonder about the wisdom if this approach. We could instead leave out the name of the fact type
-- for now and bring it in later in the query. That way, the EventTableView would be a pure
-- representation of what is in the actual EventTable.
--
DROP VIEW IF EXISTS EventTableView;
CREATE TEMP VIEW EventTableView AS
SELECT E.*,
       F.Abbrev
FROM EventTable AS E
       INNER JOIN
     FactTypeTableView AS F ON E.EventType = F.FactTypeID;

We now use the filtered VIEW of the EventTable to create two new filtered VIEW’s of the EventTable – one filtered VIEW for individual facts and one filtered VIEW for family facts. The two filtered views can be combined together with a UNION as required, but care is required because individual facts and family facts work somewhat differently.

-- Two EventTableView's - example to deal with individual facts vs. family facts.
DROP VIEW IF EXISTS IndividualEventView;
CREATE TEMP VIEW IndividualEventView AS
SELECT E.*
FROM EventTableView AS E
        INNER JOIN
     PersonTableView AS P ON E.OwnerID = P.PersonID
WHERE E.OwnerType = 0;
--
DROP VIEW IF EXISTS FamilyEventView;
CREATE TEMP VIEW FamilyEventView AS
SELECT E.*
FROM EventTableView AS E
        INNER JOIN
     FamilyTableView AS F ON E.OwnerID = F.FamilyID
WHERE E.OwnerType = 1;

We are now getting close to our goal of making a report of facts without sources. We require three queries for facts without sources, each of which we make into a VIEW. One query is for individual facts. One query is for family facts where the person is a Father. And one query is for family facts where the person is Mother. The latter two could surely be combined, but I have chosen not to do so. Note that the queries need to test for NULL and testing for NULL with SQL can be tricky and error prone if you are not extremely careful and mindful of how it works. I believe and hope that I have gotten it right. At least my code seems to produce correct results.

-- CitationViewIndividualEvents example.
--
-- We use a LEFT JOIN from the individuals to the citations to identify events
-- without citations, and the test for NULL effectively takes place after
-- the JOIN is complete.
--
-- First, we create a VIEW of the CitationTable filtered on OwnerType = 2 (citations for events)
-- The VIEW will be used several times and has the effect of isolating the test for citations
-- for events to being made at only a single place in the overall query.
--
DROP VIEW IF EXISTS CitationViewEvent;
CREATE TEMP VIEW CitationViewEvent AS
SELECT C.*
FROM CitationTable AS C
WHERE C.OwnerType = 2;
--
-- We now create the three queries for individual events, family events for the father, and family events for the mother.
-- The JOIN structure for the family event queries is slightly more complicated than is the JOIN structure for the
-- individual event query. The extra complication is because the RIN number comes from a different table than the
-- name of the fact when a family fact is involved.
--
DROP VIEW IF EXISTS CitationViewIndividualEvents;
CREATE TEMP VIEW CitationViewIndividualEvents AS   -- query for individual events
SELECT E.Ownerid AS RIN,
       E.Abbrev
FROM IndividualEventView AS E
       LEFT JOIN
     CitationViewEvent AS C ON C.OwnerID = E.EventID
WHERE C.OwnerID IS NULL;
 
DROP VIEW IF EXISTS FatherFactCitations;
CREATE TEMP VIEW FatherFactCitations AS
SELECT F.FatherID AS RIN,
       E.Abbrev
FROM FamilyEventView AS E
       INNER JOIN
     FamilyTableView AS F ON E.OwnerID = F.FamilyID
       LEFT JOIN
     CitationViewEvent AS C ON C.OwnerID = E.EventID
WHERE C.OwnerID IS NULL;
 
DROP VIEW IF EXISTS MotherFactCitations;
CREATE TEMP VIEW MotherFactCitations AS
SELECT F.MotherID AS RIN,
       E.Abbrev
FROM FamilyEventView AS E
       INNER JOIN
     FamilyTableView AS F ON E.OwnerID = F.FamilyID
       LEFT JOIN
     CitationViewEvent AS C ON C.OwnerID = E.EventID
WHERE C.OwnerID IS NULL;

Finally, each VIEW we need has been created. We can list the results with a query like the following. The query lists only the RIN number and the name of each fact for that person with no citation. We could easily list additional information, but I find that the RIN number is all I need. I use it in RM with Magnifying Glass -> Record to navigate to the correct person. The query has a minor quirk that is not worth fixing. Namely, a family fact without a citation will be listed for both spouses even if only one of the spouses is included in PersonTableView. But I regard a missing source as a problem to be fixed, and for a family fact the missing source can be fixed just as well from either spouse. Whenever I fix such a problem, I immediately rerun the query to get a reduced list of problems to be fixed.

SELECT * FROM CitationViewIndividualEvents
   UNION
SELECT * FROM FatherFactCitations
   UNION
SELECT * FROM MotherFactCitations;

I will end my narrative by reiterating what I said at the beginning: many (maybe most) users may find this way of developing queries not to be any simpler than a more normal way. But I really like developing such a script just a piece at a time. And by developing it in the order described, I can easily test and validate each piece of the script as I go. So by the end, it should just work.

Jerry

Discussions & comments from Wikispaces site

Kudos, Jerry

ve3meo
19 August 2016 00:05:58

Great development and exposition! A valuable learning page.

Tom

TreeShare – Link Pasted Ancestry Sources #ancestrycom #TreeShare #citations #sources

Posted on 2018-01-282019-01-25 by Tom Holden

Facebook Discussion Forums Discussion
EXPERIMENTAL

When a TreeShare linked Ancestry Source is Memorised and Pasted through RootsMagic Citation Manager, the pasted source loses its TreeShare link. The TreeShare update to the Ancestry Tree creates a new Other Source rather than linking to the Ancestry Source. This script creates a TreeShare link for such pasted Ancestry Sources from that of the original linked Ancestry Source.

Caveat:
The script has had little testing. Possibly there are types of Ancestry Sources whose extID in RM is NOT independent of the person or event, the principal on which the script was designed. Undesired outcomes might result.

Usage

Safest approach is to close out of TreeShare, run the script and reopen TreeShare to work through the updates to the Ancestry Tree.
For a newly downloaded Tree in which editing is just beginning, it is possible to run the script while in the Edit Person screen launched from TreeShare or even in the TreeShare Compare screen. I cannot say yet whether either is any less safe than closing and reopening TreeShare to carry out the updates. It is certainly more convenient and faster.

Download

TreeShare-LinkPastedAncestrySources Rev 2018-01-27 excludes OtherSources

Note to RootsMagic Developers

The Memorise-Paste function should be enhanced to memorise the extID of the linked citation along with its CitationID and to paste both the new Citation record and the new LinkAncestryTable record with the original extID. That procedure would not suffer the ambiguity that the above script has to deal with for Other Sources because it cannot know which linked citation was the original where there are multiples.

Observations

RM created sources are uploaded by TreeShare to become “Other Sources” on the Ancestry Tree. A link is (unreliably) established between the RM Citation and the Other Source as a record in the LinkAncestryTable. Its extID value appears to be person or even event-dependent. Thus the simple matching of an unlinked SourceID and Fields values from the CitationTable with citations that do have TreeShare links, as used for Ancestry Sources is insufficient for Other Sources.

Facts – Split Shared to Individual #facttypes #events #sharedevent #RM9

Posted on 2018-01-192023-05-18 by Tom Holden

Revised scripts for #RM8 and #RM9 added 2023-03-09. Lightly tested – feedback appreciated.

This page looks into the challenges involved in converting shared events to individual ones and tries to come up with a solution using SQLite queries (also integrated in the friendly app RMtrix for RM4-7).

The Quandary of Shared Events

We are caught in a quandary between using shared events in RootsMagic to advantage in its management and reporting versus their incompatibility with most external software, especially through GEDCOM. Support for shared events is even turned off in RootsMagic Essentials, which is what is included in the RM Shareable Drive feature! Until RootsMagic comes up with a satisfactory splitting option on export and TreeShare exchange, we seek other workarounds or avoidance:

Use shared events to the fullest extent but forego exporting to external software that may have advantages for certain kinds of analysis and reporting.
Forego using shared events so that you can more fully enjoy the benefits of exporting to external software.
Use shared events and after export apply MarkVS’ GEDCOM splitting function integrated in his GED Image Tracer. As of writing, it does a basic split but with flaws as a consequence of the inherent challenges that we will get into below.
Use shared events and after export import into David Knight’s iOS app GedView and save its GEDCOM for use with other software. As of ver 3.4.1: “Basic support added for shared events / facts as used by RootsMagic. GedView will load the event/fact and copy it to the individuals who share it.” I have yet to examine the results and it does require the iOS device.

None of the above may satisfy your requirements. This page is about a fifth approach – convert shared events within the database to individual ones. You choose whether to convert temporarily or permanently and whether to use both.

Screenshots

Here are some screenshots of the results using RM9.0.2.

Principal’s Edit Person screen showing Maggie Wood-1149 in the Daughter role of a shared Census event. Rose Ann Owen is a person not-in-database (no RIN) in the Boarder role.

Maggie Wood-1149 after splitting two shared events: Census and Immigration, showing both the shared events and the script created individual events. Note that Notes, Citations and Media carried over.

Principal’s Edit Person snippet showing a not-in-database sharer of the Census event in the role of Boarder. See the following shot showing the retention of the above information in the Principal’s Note.

Snippet from Find Everywhere for “{**” which begins the paragraph appended to the Principal’s Note for the original shared event to preserve information about sharers not-in-database. See WOOD, George-367 to find Rose Ann Owen and the other Boarders.

Applications that import RootsMagic Shared Events

While most other applications have no support for shared events, a small number do preserve the data when importing a GEDCOM from RootsMagic:

Application	Converts	Preserves	Description
Family Historian 7		X	Full family tree/genealogy software
GedSite	X		Generates website from GEDCOM
GedView	X		GEDCOM editor/viewer for iOS
Heredis 2021		X	Full family tree/genealogy software
Legacy Family Tree		X	Full family tree/genealogy software

Converts: converts shared events into individual ones
Preserves: converts RM shared events into application’s version of shared events.

The Challenges in Converting Shared to Individual Events

The unique data for the event itself for the Principal role is stored in EventTable and can remain unchanged.
Witnesses or sharees of the event are identified in the WitnessTable with their unique info: RoleID, custom sentence, Note.
Event data from the EventTable along with the sharee’s data from the WitnessTable and other supporting data from other tables need to be combined and entered as a new record in the EventTable for each sharee of an event.
Some Fact Types, such as Census, can be split into individual Census facts.
Most other Fact Types, such as Baptism, must not be split into individual Baptism facts; only the Principal was baptised. What to do with the sharees? Convert to the Miscellaneous fact type or some custom fact such as “Share”.
Do we need a sentence template for these Share facts, given that the objective is to export to third party software that does not use it? I hope nothing and maybe we have to suppress the sentence if we use a standard fact. No one sentence template will likely satisfy all fact types and roles. The default Census sentence template is decidedly unsuitable for the Census ‘share’ event created.
What do we put in the Description field of the Share fact? Probably the name(s) of the Principal(s), the name of his/her/their event type and the role of the sharee would be essential. Note the plural names if the shared event is a Family fact type. The default Census fact types do not use Description so will not show the householder’s(s’) name(s) nor the subject’s role.
We cannot invent new citations for the sharee event but can copy the citations for the Principal; they may need to be customised for the Share fact.
Some may want to operate on an intermediary database for export, eliminating all shared events with individual events.
Others may want to have both shared and individual events in their working database, using the indiv share events for export, the shared events for reports. That’s possible but keeping the Census and Fact types complicates things – their settings will have to be changed back and forth.
TreeShare complicated things when introduced in RM7.5: it does not support shared events for non-Principal persons, does not support the Census and Residence Family-type facts and transferring links with the Ancestry databases for Ancestry sources and media to the split events is uncertain. Splitting sharers into individual events helps with the first two and the normalising of Citations in RM8 facilitates the links. (added 2023-03-09)

Meeting the Challenge

An almost complete solution

ToDo:

Family events
special Fact Types, e.g. Census, Residence that should keep the same type
citation media
auto create ‘Share’ Fact Type, if not existing, complete with a default sentence template
webtags
1. fact don’t exist
2. citation
research log? for RM8+: Tasks – should anything be done? Complicated…
cleanup temp used IsPrimary and Flagsfields
do something with Share events generated for non-Person witness no longer generated. Now appending non-Person Name, Role Note to shared event Note in easily found privacy brackets.
temp use of EventTable.IsPrimary and CitationTable.Flags
1. don’t generate spurious events or citations
2. stop misusing these fields (CitationTable.Flags unused for RM8+)
Consider coexistence of Shared events and ‘Share’ events – former for RM internal use, latter for GEDCOM – could split script into two, second part deletes the Sharers so running the first part only leaves both in place. Done
1. Split: would leave Shared events as is and add Share events. User control of FactType output options and Private options might prove satisfactory.
2. Unshare: would remove the non-Principals from the shared events
3. Undo: would restore the non-Principals to shared events and delete the Share events and related citations, media tags and web tags
4. Hide tracks: would drop the query created tables from the database
Should Name, Role of Persons who shared an event be appended to Note of once-shared event? Done for persons not-in-database.
Describe the queries, how to use the~~m, illustrate results~~
Consider whether there is merit in converting sharers-not-in-database to Persons-in-database with an Association fact type to the Principal sharer.

Scripts

Four scripts are provided for user choice (all four are integrated in the friendly app RMtrix and are compatible with RM 6 and 7 and likely with 7.5):

Facts-SplitSharedToIndiv.sql or Facts-SplitSharedToIndiv-RM9.sql is to be run first and will create an Individual fact matching the shared fact for each non-Principal role. It should not be repeated.
Facts-Unshare.sql would be run next if it is desirable to eliminate shared facts from the database. The decision to do so is reversible, in the short term, at least. If the motivation for splitting was that 3rd party software did not recognise shared facts, it may not be necessary to unshare for the export.
Facts-Split-Undo.sql or Facts-Split-Undo-RM9.sql can be run to eliminate the events created by the first script and restore the shared facts deleted by the second, provided the following script has not been executed
Facts-Split-HideTracks.sql rids the database of the tables created by the first script;

Split Shared to Individual

Facts-SplitSharedToIndiv-RM9 Download (also for RM8)

Facts-SplitSharedToIndiv.sql for RM4-7
Facts-SplitSharedToIndiv-NoRIN.sql for RM4-7 2016-01-17 As above but does not include Principal’s RIN in the Description field in split events;
Kim Mills has done a very clear, well illustrated explanation of how to use these functions in RMtrix to split shared events prior to exporting and uploading to Rootsweb and Ancestry.com trees. Read it on her Footsteps of the Past blog.

Unshare

Facts-Unshare.sql for RM4-9

This simple script unshares all shared facts by deleting
all rows from the WitnessTable.

Undo Splits and Reshare

Facts-Split-Undo-RM9 Download (also for RM8)

Facts-Split-Undo.sql for RM4-7

Reverses the changes made by Facts-SplitSharedToIndiv-RM9.sql
Requires tables zTmpShareSplit and WitnessTableSafe from the Split query

Deletes the new Indiv Share, Census and Residence events and
related citations, media tags and webtags
and restores the Sharings. Cleans out the not-in-database sharers
appended to the original fact.

Requires sqlite manager with support for REGEXP_REPLACE() function.

Hide Tracks

Facts-Split-HideTracks.sql for RM4-9

Drops the tables created by Facts-SplitSharedToIndiv.sql It does not hide all traces as the Share events themselves attest and THERE IS NO GOING BACK: Facts-Split-Undo.sql will fail.

AllCitations.sql Error

Posted on 2017-12-272021-01-20 by chief superintendent

Update: 20 Jan 2021
New versions of sql code added to original post All Citations – Query #citations

I am running the following query in SQLite Expert Personal 5.2(x86):

-- AllCitations.sql
-- 2010-01-28 ve3meo
-- Lists citations for each person
-- 2010-01-29 rev by ve3meo to use LEFT OUTER JOINS to include the most orphaned citations
-- Citations for Alternate Names, added column for NameTable.IsPrimary AS Uniq to all queries
-- and negated it for Alt Name and Couple.Wife queries; filter on Uniq for principal name to
-- reduce multiple listing of same citation OR Uniq ISNULL for citations unlinked to persons.
-- Requires a temp table because of speed degradation when incorporated in main selects;
-- filtering can be done on screen in SQLiteDeveloper.
-- 2010-01-30 rev by ve3meo. Dropped UNIQUE from INDEX because other SQLite managers objected.
-- Put QUOTE() around BLOB type fields from CitationTable to display text where some SQLite
-- managers merely say BLOB.
-- To Do - maybe add eventtable.SortDate as a sorting criterion
-- 2011-11-04 ve3meo corrections for spouse, and family fact citations and multiples due Alt Name
-- 2020-12-30 ve3meo incorporated Pat Jones's correction for error reported in comments
-- BEGIN
--
-- all Personal citations for Individual
DROP TABLE IF EXISTS tmpCitations;
CREATE TEMP TABLE tmpCitations AS
SELECT c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname COLLATE NOCASE AS Surname, n.suffix COLLATE NOCASE AS Sfx, n.prefix COLLATE NOCASE AS Pfx, n.given COLLATE NOCASE AS Givens, n.birthyear AS Born,
n.deathyear AS Died, 'Personal' AS Citer, s.NAME COLLATE NOCASE AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, c.refnumber AS CitREFN,
QUOTE(c.actualtext) AS CitTxt, QUOTE(c.comments) AS CitComment
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN persontable p ON c.ownerid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
WHERE c.ownertype=0 AND +n.IsPrimary=1
;
INSERT INTO tmpCitations
-- all Fact citations for Individual
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname COLLATE NOCASE , n.suffix COLLATE NOCASE , n.prefix COLLATE NOCASE , n.given COLLATE NOCASE , n.birthyear,
n.deathyear, f.NAME AS Citer, s.NAME COLLATE NOCASE , s.refnumber, s.actualtext, s.comments, c.refnumber,
c.actualtext, c.comments
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
-- all Spouse citations for Father|Husband|Partner 1
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
n.deathyear, 'Spouse' as 'Citer', s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
c.actualtext, c.comments
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
-- LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
-- LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=1 -- AND e.ownertype=1 AND f.ownertype=1
AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
-- all Couple Event citations for Father|Husband|Partner 1
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
n.deathyear, f.NAME, s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
c.actualtext, c.comments
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN eventtable e ON e.eventid=c.ownerid
LEFT OUTER JOIN familytable fm ON e.ownerid=fm.familyid
LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=1 AND f.ownertype=1 AND n.IsPrimary=1
;

INSERT INTO tmpCitations
-- Citations for Alternate Names
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
n.deathyear, 'Alternate Name' AS Citer, s.NAME AS Source, s.refnumber, s.actualtext, s.comments, c.refnumber,
c.actualtext, c.comments
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN nametable n ON n.nameid=c.ownerid
WHERE c.ownertype=7 AND +n.IsPrimary=0
;
CREATE INDEX tmpCitations_idx ON tmpCitations(CitID);
-- Now filter the results to get rid of duplicate citation IDs due Alt Names
SELECT * FROM tmpcitations
WHERE uniq=1 OR uniq ISNULL
ORDER BY RIN, Citer , SOURCE
;
-- CitID
-- RIN, Citer;
-- END

Rev 2020-12-30 as stated in body plus reposted in block code format to overcome WordPress rendering it into something different

Discussions & comments from Wikispaces site

SQLite behaviour changed in Jan 2017

ve3meo
28 December 2017 21:20:11

The query still works in SQLiteSpy which was compiled with SQLite 3.13.0 but throws this error in my almost current version of SQLite Expert Personal compiled with SQLite 3.19.3. A Google search on the error message returns this ticket https://www.sqlite.org/src/info/25e335f802dd which reports the issue of SQLite3 behaving differently from PostGres which throws the error. It was pretty quickly changed. However, it may take some noodling to understand how to go about changing the above script so that it won’t throw the error in these later SQLite managers while supporting the intended inclusiveness by using the LEFT JOINs.

Possible Orphaned Records in the Event Table

Posted on 2017-12-172021-02-27 by Tom Holden

Problem

I was going through my database looking for United States of America and USA in the Place, Place Details and Description fields on the Edit Person screen. I am using the following query for the Place field:

-- Place with less than greater than.sql
SELECT e.eventid,
e.placeid, --e.siteid, e.familyid,
e.ownertype, -- 0 = person; 1 = family
e.ownerid,
e.ownerid,
n.surname,
n.given,
f.name,
p.name AS Place
 
FROM eventtable e
LEFT JOIN nametable n
ON e.ownerid = n.ownerid
 
LEFT JOIN facttypetable f
ON e.EventType = f.FactTypeID
 
LEFT JOIN placetable p
ON e.placeID = p.PlaceID
 
WHERE p.placetype = 0 -- 0 = Place, from Edit Person screen for a fact
--and e.eventtype = 29 -- Residence
--and E.ownerid = 1576
--and substr(p.name, 1,1) = "<"
--and (place like "%USA%"
--or place like "%of America%")
ORDER BY surname, given ;

These are some of the records that were in the results:
EventID PlaceID OwnerType OwnerID Surname Given Name Place
9505 1680 1 1290 Marriage Washington, Franklin, Missouri, USA
13606 3213 1 1756 Marriage Silver Lake, Kenosha, Wisconsin, USA
14302 1662 1 1802 Marriage Union, Franklin, Missouri, USA
18211 1680 1 2199 Marriage Washington, Franklin, Missouri, USA
20784 4416 1 2405 Marriage Rockingham, Virginia, USA
20976 4490 1 2428 Marriage , Rice, Minnesota, USA
23733 5314 1 2459 Marriage Luebbering, Franklin, Missouri, USA
27138 6062 1 2406 Marriage of Virginia, USA
27781 5055 1 2682 Marriage Madison, Kentucky, USA
28555 6351 1 2441 Marriage of Bound Brook, Somerset, NJ, USA
12116 2694 1 1576 Allen Linda Lee Marriage ,, Indiana, USA
16001 3593 0 6087 Briggs Augustus Horace Birth , Fountain, Indiana, USA
19479 4178 0 7055 Briggs Nellie May Burial Lonedell, Franklin, Missouri, United States of America
17510 3872 0 6529 Briggs Sally Wood Birth , Scioto, Ohio, USA

The records that are Owner Type 1 are no longer in the nametable. Which is why there is no surname or given name. Except for the Bold one. How did things get this way? I am not sure. The only thing I delete in the eventtable is the blank death dates using SQLite Expert Personal. Otherwise I use RM to delete stuff. Could this be happening when I have SQLite open and the database open and I get the database lock message when trying to delete an event or someone in RM?

I am not quite sure what VACUUM does. If it gets rid of orphaned records, could it include these kind of records? I know RM has an Clean Phantom Records button but it obviously is not removing these records. Should I report this to RM or can you do that since you work with them closer than I do? Or I think you do anyway.

I have done the reindex, clean phantom records and compact the database many times in RM. I even do the reindex and VACUUM in SQLite.

The Linda Lee Allen one above displays St. Mary’s Lutheran Church, Kenosha, Wisconsin, United States in the Place field on the Edit Person screen. When I go to the PlaceTable and find St. Mary’s Lutheran Church, Kenosha, Wisconsin, United States. It has a PlaceID of 7002. When I go to the EventTable and find PlaceID 7002, it has a OwnerID of 497 and EventType of 300. When I go to the NameTable and find OwnerID 497, it displays Marcotte Roberta. When I go to RM and find Marcotte Roberta, it displays Aurora, Adams, Colorado, United States in the Place field. I am soooo confused. I obviously missed a boat somewhere but where?

Answer

Yes, you missed the boat. In the EventTable, the OwnerID when the OwnerType=1 points to the FamilyID in FamilyTable. From there, FatherID is the PersonID of the Husband and MotherID is the PersonID of the Wife. It’s not that the records of OwnerType 1 are no longer in the NameTable; they are not intended to point directly to one person in the NameTable but indirectly to two people. The Marriage event is a couple or family event. That the bolded one in your example points to a person is merely coincidental – the OwnerID is a FamilyID that happens to correspond to an OwnerID in the NameTable but you should not be linking the two directly when it is EventTable.OwnerType 1, only when 0. There are 1 or 2 other OwnerType values for events in the EventTable. You will find them in the Database Design Spreadsheets. You don’t want to delete them.

SQLite VACUUM is renamed Compact in the RM UI. Records that have been deleted leave space in the database file that is unavailable to the Operating System for other files. VACCUM sucks it out, so to speak, and returns it as unused space to the OS.

The RM Clean Phantom Records is relatively comprehensive but not as aggressive as my Delete Phantoms.

And, no, I have no special connection to RM Inc.; just another lowly user.

Tom

RMNOCASE – faking it in SQLiteSpy #rmnocase #sqlitespy

Posted on 2017-12-132022-04-29 by Tom Holden

Background

RootsMagic 4+ has an internal, proprietary collation sequence called RMNOCASE that controls the order in which names of people, places, sources, etc. are sorted. Fields that are defined to use this collation cannot be sorted by an external SQLite tool without overriding it with another collation sequence, e.g.: NOCASE, nor can they be edited by the external tool without its own RMNOCASE collation. Despite its lack of the latter, SQLiteSpy was at the top of the list of SQLite managers for quite some time since the early days of this wiki. During that time, the only tool we knew of to get around the RMNOCASE obstruction was with the $30 SQLite Developer by renaming one of its UTF-8 collations. Then, with a custom extension described below, SQLiteSpy could do it, even more easily than SQLite Developer, for free. More on the SQLiteSpy story follows but, as of July, 2013, there is a new leader on the RMNOCASE front: see RMNOCASE – faking it in SQLite Expert, command-line shell et al.

Faking RMNOCASE in SQLiteSpy
Thanks to Ralf, the developer of the SQLite3 database engine for Delphi (DISQLite3) on which SQLiteSpy and RootsMagic 4 and up are based, I have compiled an extension for SQLiteSpy that adds a fake RMNOCASE collation sequence, thus enabling Spy to freely query and, indeed, edit the .rmgc databases. No more need to declare COLLATE NOCASE when comparing and ordering certain fields; now it’s possible to modify values in all fields and to insert or delete rows in all tables using the free SQLiteSpy.

But be warned, this collation is not identical to RMNOCASE and there may be some risks – probably none if the English alphabet is used exclusively. All it does is provide the NOCASE collation sequence to SQLiteSpy under another name, RMNOCASE. With other alphabets, anticipate that there will be differences in sorted lists and search results between RootsMagic and SQLiteSpy; editing, with non-English characters, a field that is collated and indexed with RMNOCASE will corrupt the index.

The good news is that RootsMagic 5 has a REINDEX tool that will rebuild the indexes using its internal RMNOCASE. For RootsMagic 4, the only way to rebuild the indexes is to transfer the data to a new database via drag’n’drop or GEDCOM. It is advisable to REINDEX in RootsMagic after adding or modifying any name-type data with an outboard SQLite manager.

Download & Installation

RMNOCASE_fake-SQLiteSpy64.dll.bak 19 Feb 2022 64-bit version of fake RMNOCASE collation for use with 64-bit SQLiteSpy.

RMNOCASE_fake-SQLiteSpy.dll.bak 27 June 2013 32-bit version for use with 32-bit SQLiteSpy.

Neither is identical to RootsMagic RMNOCASE so you must still use its Rebuild Indexes Database Tool after any SQLiteSpy queries that change data or REINDEX. (Remove .bak extension after download)

Download to the same folder where you have installed SQLiteSpy.exe. Open SQLiteSpy without a database and note the list of Collations numbers 7 and does not contain RMNOCASE. Register the extension via the menu path Options > Options > tab Extensions where you will enter the filename and OK. Create a new database (File > New) and note the list of Collations now includes RMNOCASE. It did before when a .rmgc file was open but SQLiteSpy had no access to the RM collation sequence. Now SQLiteSpy accesses the NOCASE collation whenever RMNOCASE is invoked. As long as SQLiteSpy stays open, the fake RMNOCASE collation remains accessible for every database opened during the session. The extension automatically reloads when SQLiteSpy is restarted, provided SQLiteSpy.exe is in a folder over which you have full privileges to allow Spy to write the file SQLiteSpy.db3 which stores the options (Spy does not report any problem if it cannot write the file). Alternatively, the extension can be loaded by command in the SQL editor and this command can be stored in a script. From the SQLiteSpy product description, “Extensions can be loaded for the current database via SELECT load_extension(‘FileName.dll’); or automatically for all databases by entering extension file names to the Options dialog”.

Discussions & comments from Wikispaces site

Unable to download fake RMNOCASE dll

thejerrybryan
18 December 2011 01:24:50

Well, I can download it, but within a second or two it has been deleted by my Norton anti-virus. I’ve been looking for a way to whitelist the fake RMNOCASE dll, but it looks like I will have to convince the Norton server to whitelist it for everybody rather than being able to whitelist it here on my local machine just for myself. Big brotherism run amok.

I’ll keep investigationg.

Jerry

thejerrybryan

thejerrybryan
18 December 2011 03:34:58

I got it to work. I had to ignore a pretty severe warning from Norton and move the file out of their Quarantine. Having done so, it all works.

In the beginning of researching the problem, it looked like I was going to have to submit the fake RMNOCASE dll to Norton for their engineers to analyze before they would whitelist it, but I found a way to force Norton to accept the dll without having to submit the dll to Norton for approval.

Jerry

Ksquared333

Rebuilding Indexes

Ksquared333
24 December 2015 23:47:18

So I ran RMNOCASE so I could use .rmgc files on my smallest RM data file (2912 KB). Then I ran an sql. Then I asked RM to Rebuild the Index because I do have some non-English characters in some of my names. That was over an hour ago and the index still isn’t rebuilt. I shudder to think what would happen if I rebuild an index for my biggest files of 72,000 KB or 80,000 KB. Am I doing something wrong? I hope this index gets rebuilt before I need to shut down the computer for the night.:-(
Kim D

ve3meo

ve3meo
25 December 2015 00:53:20

I don’t think you are doing anything wrong. The problem is that the RootsMagic Rebuild Indexes does more than the SQLite REINDEX function which it includes. It also updates the BirthYear and DeathYear columns in NameTable and that is what I suspect is slow. I was delighted that Bruce added the REINDEX function but dismayed that he combined it. Better they were kept separate but that might have caused more user confusion. Check your Windows Resource Monitor for cpu and memory utilisation and hard drive traffic. Perhaps there is some room for optimisation.

Database is locked

Posted on 2017-12-132019-01-25 by momakid

Is it possible to open the databases as read only? Obviously SQLite is opening the database for edit capability. I am getting the database is locked when I have the database open in sqlite and try to open RootsMagic. I am 95% of the time using sqlite for query only. I rarely do an update or delete.

Or is there a way to open the database without locking it?

Discussions & comments from Wikispaces site

Which SQLite Manager are you using?

thejerrybryan
14 December 2017 03:44:58

I have never seen this behavior with SQLiteSpy, even if I’m doing an update. I happily use SQLiteSpy and RM at the same time on the same database.

The problem I do encounter is that I can’t exit RM with a backup if SQLiteSpy has the database open. Something about RM’s backup procedure apparently tries to lock the database in a way that conflicts with SQLiteSpy. Exiting RM without a backup is always fine. It’s just the backup that’s the problem.

Finally, I try to avoid updates, but sometimes they do seem necessary. Usually, my updates “just work” and the newly updated data is immediately available to RM without incident. But in some situations, I have to run the Rebuild Index procedure inside of RM after one of my SQLiteSpy updates. Otherwise, there are indexing errors in the RM user interface. I think Tom understands the reason for this, but I confess I do not. My understanding of relational databases and indexes is that updates to data are also supposed to update the indexes automatically. I seldom encounter this problem, even when making updates, even when doing inserts. So I don’t understand why sometimes I do encounter this problem.

momakid

momakid
14 December 2017 05:29:03

I am using SQLite Expert Personal 5.2. It also happens with SQLite Expertt Personal 3.5.96.2516. I I have gotten the database lock message when SQLite is open and I try to open RM7.5 or update something on the edit screen like the residence date. But I get it when RM7.5 is open and I try to query the database also. I don’t do it everytime I do those things but it happens occasionally. I am trying to remember exactly what was going on in both programs. I think I may have updated a date in rootsmagic. I usually do not press save. I just press close. And the changes are saved. I do remember being on that screen and then running a query.
I sometimes have problems when I do a compact in RM. It never ends. So I do the 3 finger salute and end RM. I have the best luck re-indexing the file in SQLite. I am not sure what VACUUM does but I use it and then reindex it.
So far the only deletes I do is blank death dates.
I seldom do backups. I prefer making copies of the file before I do the deletes.

Exporting Data

Posted on 2017-12-122019-01-25 by momakid

My RootsMagic file has over 8,500 people in it, 23,000 events, 3,000 families, 3,000 citations, and 3,600 multimedia links I have a query that takes a bit to run. I am using sqlite expert Personal 5.2. I am trying to export the results of the query to a spreadsheet. So far I have not found the way to export it to a spreadsheet other than doing a Ctrl + A > Ctrl + C and Ctrl V in the spreadsheet.

Is there a way to do it? Or is there another program that can do it?

I believe SQLite Expert Pro supports a more powerful capability for export to spreadsheet. IIRC, Expert Personal supports only the copying of the results screen as you describe which is paged, 500 rows at a time is the default.

SQLiteSpy also supports copying its results screen which is not paged. That would be more convenient for results that are many more than 500 but not so large that it chokes the application.

SQLite Studio 3 can export results to a CSV file among other formats. I don’t have much experience with it; don’t know if it’s easy to add a fake RMNOCASE collation.

Excel itself can be connected via ODBC to a SQLite database and run queries; the RMNOCASE issue could be an impediment.

Tom

SQLite Expert Personal Edition Version 5.2.2.240 (x64) problem

Posted on 2017-11-122021-02-26 by momakid

I installed SQLite Expert Personal Edition Version 5.2.2.240 (x64).

ve3meo Nov 12, 2017

What is your OS? Is it 32 or 64 bit. You can install 32 or 64 bit programs on a 64 bit OS but you cannot install a 64 bit program on a 32 bit system. 32 bit programs on a 64 bit system must be installed in “Program Files (x86)”; 64 bit programs go into “Program Files”.

I started SQLite Expert Personal Edition
I opened my database
I got a popup saying SQLite Library not loaded.
I click OK
When I checked the SQLite Library in the options it contained sqlite3.dll 3.21.0 [FTS3 FTS4 FTS5 RTREE]

ve3meo Nov 12, 2017

That looks right. But maybe the 32b/64b issue caused this error.

I entered the following in the SQL window:

select surname
from nametable;

ve3meo Nov 12, 2017

Don’t select a field that requires RMNOCASE until you resolve the basic issues. This just complicates the matter.

I got Database not connected

ve3meo Nov 12, 2017

because the SQLite Library didn’t load. Has nothing to do with unifuzz.

I did a right click on my database
I selected Load Extension
I selected the the File Name (C:Program DataMicrosoftWindowsStart MenuProgramsSQLite ExpertPersonal 5 – 64bitunifuzz.dll

ve3meo Nov 12, 2017

This is a path to a hyperlink, not to the file unifuzz.dll unless that’s where you put it. I think the best place for extensions is with the program. On my 64b Win 10 system, I have the 32b version 4 program and unifuzz in “C:Program Files (x86)SQLite ExpertPersonal 4”

I entered sqlite_extension_init for the Entry Point
I checked Auto
I clicked OK
I got the following: Could not load library: C:Program DataMicrosoftWindowsStart MenuProgramsSQLite ExpertPersonal 5 – 64bitunifuzz.dll%1 is not a valid Win32 application.
I tried doing the above but left Auto unchecked
I got an Access violation error

I have put the unifuzz.dll file in C:program FileSQLite Expert Personal 5 folder and did the above steps and got the same results.

I put the unifuzz.dll file in the folder where the rmgc file is and still got the same thing.

What am I doing wrong?

Should the library be unifuzz.dll instead of sqlite.dll? If so how do I change it?

ve3meo Nov 12, 2017

No.

Discussions & comments from Wikispaces site

unifuzz.dll is compatible only with the 32 bit version

ve3meo
12 November 2017 16:19:01

Using unifuzz.dll with SQLite Expert

So uninstall the 64b SQLite Expert. Install the 32b version and place unifuzz.dll in the same folder as the program.

momakid

momakid
12 November 2017 18:17:11

Is it possible to use the 64b SQLite Expert version with Rootsmagic 7.5? If so what do I need to do to make it work? I have a 64b machine so I would like to use the 64b version. But if it is not possible, I have no problem using the 32b version. Thank you so very much. You are a miracle worker.

Inline comments

Where is this data?

Posted on 2017-10-252022-04-29 by momakid

I am trying to put query together that will get the information for the Date, Place, Place Details, Description, Citation, and media fields on the following screen:

Pic 1

The put the following query together to get the above information:
select et.ownerid,
nt.surname,
nt.given,
et.eventid,
et.eventtype,
nt.birthyear,
ft.Name,
et.placeid,
et.siteid,
substr(et.date,4,4) as Ev_Date,
–(select pt.name
— from placetable pt
— where et.siteid = pt.placeid) as Place,
pt.name as Place,
site.name as Place_Details,
trim(et.details) as Description,
ml.mediaid,
ml.linkid,
–mm.mediatype,
(select m1.mediapath
from multimediatable m1
where ml.MediaID = m1.mediaid) as Path,
(select m2.mediafile
from multimediatable m2
where ml.MediaID = m2.mediaid) as File,
(select m3.caption
from multimediatable m3
where ml.MediaID = m3.mediaid) as Caption,
(Select s1.name
from sourcetable s1
where ct.sourceid = s1.sourceid) as Source,
(Select s2.actualtext
from sourcetable s2
where ct.sourceid = s2.sourceid) as Actual_Text,
(Select s3.comments
from sourcetable s3
where ct.sourceid = s3.sourceid) as Comments

from eventtable et,
placetable pt,
facttypetable ft,
nametable nt,
sourcetable st

left join medialinktable as ml
on et.ownerid = ml.ownerid

left join citationtable as ct
on et.ownerid = ct.ownerid

left join placetable as site
on et.siteid = site.placeid

where et.ownerid = nt.ownerid
and et.eventtype = ft.facttypeid
and et.placeid = pt.placeid
and ct.sourceid = st.sourceid
and et.EventType = 29
and et.ownerid = 1490

order by nt.surname, nt.given, ev_date
;

I am getting the Event Date, Place, Place Details and Description.

I am not getting the following information:

Pic 2

Nor this information:

Pic 3

I am getting the source but it is not correct. I am not getting anything for Actual_Text or Comments.
I am getting the filepath which appears to be correct. I am getting a filename and caption but they are not correct.

I am getting too many rows

I want a row for every eventtype of 29 from the eventtable. If there is a place, place detail, description, mediapath, mediafile, caption, source, actual_text or comments, that it appear in the query results. If there isn’t data, the field be blank but the row to be displayed.

I want to be able to sort it my surname, given and eventdate.

I am missing something somewhere. Can you point me in the right direction or tell me what I am doing wrong?

Discussions & comments from Wikispaces site

Will produce big results set

ve3meo
25 October 2017 22:10:39

Because for each event for a person, there can be multiple citations AND multiple media. If an event has 2 of each, there will be 4 rows; 3 of each, 9 rows… And if you are also after the Master Source media and/or the Source Details media for which there can be more than one in each case, then it can exponentiate even faster. Are you sure that is what you really want? It’s one reason why RM has all these sub-windows.

momakid

momakid
26 October 2017 02:26:13

I have residence events for attaching the census image. They do have the month and year of the census. There should be only one citation and on a few occasions 2 images. If there is more than one citation, then I need research it and delete the extras. But on a whole there should be only one. There should be multiple years on a lot of them. I am trying to determine which records are missing census images, which ones are missing citations and sources. Which ones are missing a census year.
A family member had a shoe box full of funeral cards. I scanned them and started attaching them to the death event. But I soon discovered most of those women names were married names. I didn’t know their maiden names. So I created alternate names consisting of given name and maiden name for the given name of the alternate record. The surname was the married name and the suffix was the husbands given name. I was now able to find a big majority of the women.
BUT i think that could affect my results. I am not going to restrict it to the primary record first but I have a feeling I am going to have to. I did do a query to see how many residence records there were and there were 3,414. I am trying to think of why I should not get at least that many records in my results. I want to get every residence event. I may need to restrict it to A thru G, H thru K, etc. I will dump the results into an excel spreadsheet and work from it.
I did discover that I needed to look at the eventid and ownertype of 2 for the citation and medialinktable .I am getting the path, file, caption and source but not the source details.
This is what I have now:
select et.ownerid,
nt.IsPrimary,
nt.surname,
nt.given,
et.eventid,
et.eventtype,
nt.birthyear,
ft.Name,
et.placeid,
et.siteid,
substr(et.date,4,4) as Ev_Date,
pt.name as Place,
site.name as Place_Details,
trim(et.details) as Description,
ml.mediaid,
ml.linkid,
–mm.mediatype,
(select m1.mediapath
from multimediatable m1
where ml.MediaID = m1.mediaid) as Path,
(select m2.mediafile
from multimediatable m2
where ml.MediaID = m2.mediaid) as File,
(select m3.caption
from multimediatable m3
where ml.MediaID = m3.mediaid) as Caption,
(Select s1.name
from sourcetable s1
where ct.sourceid = s1.sourceid) as Source,
(Select s2.actualtext
from sourcetable s2
where ct.sourceid = s2.sourceid) as Actual_Text,
(Select s3.comments
from sourcetable s3
where ct.sourceid = s3.sourceid) as Comments

from eventtable et,
placetable pt,
facttypetable ft,
nametable nt,
sourcetable st

left join medialinktable as ml
on et.eventid = ml.ownerid and ml.ownertype = 2

left join citationtable as ct
on et.eventid = ct.ownerid and ct.ownertype = 2

left join placetable as site
on et.siteid = site.placeid

where et.ownerid = nt.ownerid
and et.eventtype = ft.facttypeid
and et.placeid = pt.placeid
and ct.sourceid = st.sourceid
–and nt.isprimary = 0
and et.EventType = 29
–and et.ownerid = 1490

order by nt.surname, nt.given, ev_date;

Discussions & comments from Wikispaces site

Kudos, Jerry

Usage

Download

Note to RootsMagic Developers

Observations

The Quandary of Shared Events

Screenshots

Applications that import RootsMagic Shared Events

The Challenges in Converting Shared to Individual Events

Meeting the Challenge

An almost complete solution

ToDo:

Scripts

Split Shared to Individual

Unshare

Undo Splits and Reshare

Hide Tracks

Discussions & comments from Wikispaces site

SQLite behaviour changed in Jan 2017

Problem

Answer

Background

Download & Installation

Discussions & comments from Wikispaces site

Unable to download fake RMNOCASE dll

Rebuilding Indexes

Discussions & comments from Wikispaces site

Which SQLite Manager are you using?

Discussions & comments from Wikispaces site

unifuzz.dll is compatible only with the 32 bit version

Inline comments

Comment: What is your OS? Is it 32 or 64 bit. …

Comment: That looks right. But maybe the 32b/6…

Comment: Don’t select a field that requires RM…

Comment: because the SQLite Library didn’t loa…

Comment: This is a path to a hyperlink, not to…

Comment: No.

Discussions & comments from Wikispaces site

Will produce big results set