SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Genealogy Software Comparison #database

Posted on by Tom Holden

The following exchanges between Lee Irons and Tom Holden shed some light on the differences between RootsMagic and two other leading genealogy software programs (Legacy Family Tree 7.5 and Family Tree Maker 2012) and what can give rise to incompatabilities when data is transferred between them via GEDCOM.

 
Hi Tom,
There are software comparison reviews out there, but they are pretty much at the
thousand-foot altitude, basically useless to advanced users. I've been considering
putting together a comparison that gets down into the weeds on these three programs.

My email below just scratches the surface, as you know. The industry definitely
needs daylight shed on it so that people can see what they are really getting when
they purchase one of these programs. It might help provide some motivation to the
developers to get moving on cutting-edge enhancements and stop nibbling at the crumbs.
If you would like to post my email below as a precursor to a greater effort, then
please feel free. ;-)
Lee

-- Tom Holden wrote:
 
> Hi Lee,
>
> Great explanation of the differences among the three programs. I'm not aware
> of anybody having done this publicly. Would you be willing to contribute
> something like this to the SQLite Tools For RootsMagic wiki, or let me quote
> this on it? All in the interest of advancing our understanding of the
> characteristics of data imported into RM from other programs ;-)
>
> Tom
>
> ---Original Message---
> From: Lee Irons
> Sent: Sunday, December 18, 2011 11:53 PM
> To: 'Tom Holden'
> Subject: RE: Completed conversion of shared family facts
> ...
> Yes, you are correct regarding the differences in approach between the three
> programs. LFT 7.5 does 1-or-more-citations : 1-event/fact, but has a
> quasi-1-citation : many-events/facts functionality by allowing a source
> citation to be copied and pasted to other events and then appear to be one
> citation for the many events in reports and publications. LFT does not have
> "shared facts," but when a fact type is applied to a marriage, it becomes a
> marriage fact. FTM 2012 has a 1-or-more citations : 1-or-more-events/facts.
> In practice, the user can create 1 citation and link it to many events/facts
> with ease. It differentiates between individual facts, which can be shared
> by anyone, and marriage facts, which can only be applied to married couples.
> RM5, of course, takes the approach of having shared events. I'm sure the
> database structure is quite different for all of these.
>
> I would say that, of the three, LFT 7.5 has advanced merge, find,
> search-and-replace, and report/publication functionality. FTM 2012 has
> advanced sourcing, mapping, and heads-up-display functionality. RM 5 has
> advanced place management functionality (with county checker and place
> details), and has the only roles-for-shared-facts functionality of the
> three. All of these other functionality in all three has strengths and
> weaknesses.
>
> Regarding the way I cite sources and build evidence, my migration to RM 5
> taught me a lot of lessons and showed me some weaknesses in what I was
> doing. I have spent the last couple of weeks improving my methodology and
> now have something that would work with LFT or FTM without me needing to use
> the shared facts capability of RM 5. The Roots Magic developers seem to
> have their hands full with playing catch-up to the advanced capabilities of
> FTM and LFT, so I am going to keep my eye on RM for a while. Family Tree
> Maker 2012 is not perfect. It only recognizes current-day places (does not
> validate the existence of counties based upon date). Its fact sentence
> functionality is weak. It also does not chronologically order the facts on
> the person screen, nor does it allow the user to manually reorder them, so
> it looks messy. Also, its report and publications capability needs some
> work. However, LFT 7.5 has weak advanced sourcing , which makes me have to
> do a lot of clicking around, and copying and pasting to accomplish what I
> want. So I am focused on trying to make FTM 2012 work for me right now,
> because its source citation capability is that good. I have sent some
> enhancement requests off to Family Tree Maker developers for advanced fact
> management (needs better fact sentence creation capability and the ability
> to reorder facts), advanced place management (needs an Original Place Name
> field as opposed to the current Place field which only recognizes
> current-day place names for valid name checking and automatic mapping and a
> Date-Line Checker, similar to RM's County Checker), and advanced report
> customization. Legacy Family Tree would have to build in some Advanced
> Sourcing capability to bring me back. I've put in the enhancement requests
> to the LFT developers, but it looks unlikely that they are going to happen.
>
> Cheer!
>
> Lee
>
> ---Original Message---
> From: Tom Holden
> Sent: Friday, December 16, 2011 8:26 AM
> To: Lee Irons
> Subject: Re: Completed conversion of shared family facts
>
...
> I'm interested in your comment on FTM2012 as I was seriously thinking of
> giving it a try just about when RM5 came out and distracted me. Also, your
> description that it "merges the duplicate source citations into single
> source citations with multiple facts of multiple individuals linked to them"
>
> sounds different from what you were trying to do in RM5 which was to merge
> matching independent facts into shares of a single fact. Maybe the goal is
> the same (single citation for all) but the tools and solutions are
> necessarily different. In RM, every citation is uniquely linked to one
> person, or one family or one event - a 1:1 relationship. I wonder if, in FTM
> and LFT, multiple persons|families|events can link to one citation (many:1).
>
> Two very different database designs which could account for the difficulties
> in migrating between LFT and RM whereas it might be naturally easier between
> FTM and LFT. I did not even look at the LFT GEDCOM to see how it might
> differ.
>
> Tom
>
>
>
> ---Original Message---
> From: Lee Irons
> Sent: Friday, December 16, 2011 1:48 AM
> To: 'Tom Holden'
> Subject: RE: Completed conversion of shared family facts
>
> Tom,
>
...
> On the other hand, I have found that Family Tree Maker 2012 imports my
> Legacy file and merges the duplicate source citations into single source
> citations with multiple facts of multiple individuals linked to them. It
> does exactly what I want. It also has the ability to place a pin anywhere
> on a Bing map location to record the geocode for a place that it can't
> automatically find. The reporting capability isn't all that great, but I
> can move the data back and forth through a GEDCOM 5.5 file between Family
> Tree Maker and Legacy and use Legacy's reporting capability, which is top of
> the line.
>
> Long story short, I think I have found my solution using other software.
> Roots Magic had some nice things (like Place Details), but it appears that
> it would be too difficult to convert my data.
>
> Feel free to keep playing around with my data file as you like. I'll still
> keep an eye on the forum and get updates and upgrades to Roots Magic. Kind
> of a hobby.
>
> Cheers,
>
> Lee

MS Access, SQLite, and Don Quixote #msaccess #subqueries #rmnocase

Posted on by thejerrybryan

I wanted to report some further investigations into my quest to be able to write the same query only once for use both directly in SQLite and again for MS Access, with hopefully not a whole lot editing required to make the same basic query work in either environment.

On the issue of what may be called the “nested JOIN” problem where Access wants to base queries on other queries, further testing and some sage advice from Tom suggests that this problem may be solved with nested SELECT’s rather than with nested JOIN’s, and the resultant SQL will work pretty much equally well in either environment. To wit, I am finding that I’m wanting to create queries that can be structured roughly as follows:

SELECT
        some_right_and_left_stuff
FROM
        (SELECT some_left_stuff FROM some_tables_on_the_left) AS L
LEFT JOIN
        (SELECT some_right_stuff FROM some_tables_on_the_right) AS R ON some_right_and_left_stuff;

Of course, both some_tables_on_the_left and some_tables_on_the_right may involve some additional JOINS, but the required parentheses and nesting seem so far to be totally compatible between Access and SQLite. But then, the RMNOCASE issue problem and the BLOB problem must be dealt with differently between Access and SQLite.

Here’s an example of a compatibility problem I didn’t expect. Suppose we replace “SELECT some_left_stuff FROM some_tables_on_the_left” from the third line of the pseudo-code with the following. It’s a real query that will run successfully on both environments. The query returns a list RM Record Numbers associated with a Named Group. In this example, it’s group #6, and I used the group number rather than the group name to avoid having to deal with text strings. Indeed, that’s why this query is so compatible – it references no text strings. I totally stole this query from Tom, by the way. I doubt I could have figured out how to do it on my own.

SELECT
        N.OwnerID AS Recno
 
FROM GroupTable AS G,
     PersonTable AS P INNER JOIN
     NameTable AS N ON P.PersonID=N.OwnerID
 
WHERE
       N.OwnerID >= G.StartID
            AND
       N.OwnerID <= G.EndID
            AND
       G.GroupID = 6

Next, we make the exact same query into one using nested SELECT’s. In this case, it’s totally silly to introduce an extra level of nesting just for this one query. But remember it’s ultimately our purpose to LEFT JOIN this query with another, and the best way to do the LEFT JOIN will surely be with nested SELECT’s.

SELECT
        L.RecNo AS RecNo
 
FROM
       (
         SELECT
                N.OwnerID AS Recno
 
         FROM GroupTable AS G,
              PersonTable AS P INNER JOIN
              NameTable AS N ON P.PersonID=N.OwnerID
 
        WHERE
 
          N.OwnerID >= G.StartID
               AND
          N.OwnerID <= G.EndID
               AND
          G.GroupID = 6
 
       ) AS L

Again, this query “just works” in both environments and it still produces a list of the all the RM Record Numbers associated with one particular Named Group. And again the reason the query works in both environments is that it is only dealing with numeric data. So let’s confuse things by adding one text field. We will start with the original query that was not nested.

SELECT
        N.OwnerID AS Recno,
        N.Surname AS Surname
 
FROM
      GroupTable AS G,
      PersonTable AS P INNER JOIN
      NameTable AS N ON P.PersonID=N.OwnerID
 
WHERE
 
      N.OwnerID >= G.StartID
           AND
      N.OwnerID <= G.EndID
           AND
      G.GroupID = 6

Perhaps a little surprisingly, this query still works in both environments even though I have added a text string to it, namely, NameTable.Surname. But I think it’s more or less by accident that the query works in both environments without any use of COLLATE NOCASE or CAST or StrConv. Which is to say, I’m not doing any manipulation or comparison of NameTable.Surname whatsoever.

Finally, we add a text string to the nested query.

SELECT
        L.RecNo AS RecNo,
        L.Surname AS Surname
 
FROM
      (
       SELECT
               N.OwnerID AS Recno,
               N.Surname AS Surname
 
       FROM
            GroupTable AS G,
            PersonTable AS P INNER JOIN
            NameTable AS N ON P.PersonID=N.OwnerID
 
       WHERE
              N.OwnerID >= G.StartID
                   AND
              N.OwnerID <= G.EndID
                   AND
              G.GroupID = 6
 
      ) AS L

This query still works fine in ACCESS, and indeed it’s more user friendly than before because it’s listing the person’s surname in addition to the person’s Record Number. But in SQLite the query gives one of those obnoxious “no such collation sequence: RMNOCASE” errors. I’m not doing any processing or manipulation or comparison of a text string, but there’s the error, anyway. I’m guessing that SQLite doing something that causes the collation sequence error because it’s converting the data from N.Surname in the innermost SELECT to L.Surname in the outermost SELECT. I can fix the query in SQLite by adding COLLATE NOCASE to Surname, but then the query will no longer work in Access. Apparently there is always going to be a lot of editing required to be able to have both an Access version and an SQLite version of the same query. And the query above is really simple.

Jerry

Discussions & comments from Wikispaces site


thejerrybryan

UPDATE Syntax, multiple tables

thejerrybryan
02 July 2011 15:12:34

I’m ready to play with an UPDATE transaction (in a test database, of course), but I need to update one table based on the contents of another table. The examples that I can find do not seem to work in SQLite.

The following works:

UPDATE SomeTable
SET SomeColumn=whatever
WHERE SomeOtherColumn=somethingelse

I need the WHERE clause to reference another table, and I can’t figure out how to do it. Conceptually, I need the following.

UPDATE SomeTable INNER JOIN SomeOtherTable ON some_condition
SET SomeTable.SomeColumn=whatever
WHERE SomeOtherTable.SomeOtherColumn=somethingelse

It certainly seems like this sort of thing ought to be very easy to do, but I can’t figure out the syntax.

Thanks,
Jerry


ve3meo

ve3meo
02 July 2011 20:03:16

Jerry, I wonder if Media Repair Queries might provide a solution. I banged my head against the same question and came up with that technique in the absence of anything better.

Sounds like you’re almost ready to roll out some neat stuff!

Tom

Inline comments


ve3meo

Comment: SQLiteSpy with a new extension provid…

ve3meo
16 December 2011 02:27:19

SQLiteSpy with a new extension providing a fake RMNOCASE collation should work without having to change the query to include COLLATE NOCASE.

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

Posted on by Tom Holden

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

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

MediaRepair.sql

Query

1. Lists duplicate media file names

MediaIDMediaFileMediaPath
86_East Dyberry Cemetery 1.JPGd:My DocumentsGenealogyGravestonesUSA – East Dyberry (Wayne Co)
93_East Dyberry Cemetery 1.JPGE:My DocumentsGenealogyGravestonesUSA – East Dyberry (Wayne Co)
48bailie-agnes,1885-jan-26-(b).jpge:My DocumentsGenealogySurgeonerBirths
251bailie-agnes,1885-jan-26-(b).jpgd:My DocumentsGenealogySurgeonerBirths

2. Creates a list of UPDATE commands to replace the MediaID in MediaLinkTable that points to the MultimediaTable record having the path to E drive with the MediaID of the same file on the D drive. Copy this list to another SQL edit page and run it. NB – this query was for the specific problem of this database: the same files were on two different drives having the same paths.

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

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

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

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

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

LinkIDDUPESLinkedTo
1381_East Dyberry Cemetery 1.JPG
1375_East Dyberry Cemetery 7.JPG
761bailie-agnes,1885-jan-26-(b).jpg
72341Ballyclare Town Hall.pcx

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

County Check #county

Posted on by Tom Holden

RootsMagic 5 introduced a new feature that checks what you have entered in the Place of a fact/event against a database of counties stored in the file CountyCheckDB.dat. This file is partially human readable when opened in a text editor. What it reveals is that there are four Internet sites that it is set to call for maps and other information:

TagURLDescription
FSWIKIhttps://www.familysearch.org/learn/wiki/en/Family Search Wiki
NAHCBhttps://publications.newberry.org/ahcbp/Newberry Atlas of Historical County Boundaries (US)
WIKIhttp://en.wikipedia.org/wikiWikipedia
http://maps.familysearch.orgEngland Jurisdictions 1851

The first three appear to get parameters for a specific place that are appended to the URL and passed to the CountyCheck controls “Online Info” and “Online Map” to open these sites on a specific page. The last one is called without parameters for places in England.

RM5 Version Monitoring #database

Posted on by Tom Holden

The database documentation was done largely on RootsMagic version 5.0.0.3 and those released in the several days following. It’s unlikely that the database schema has changed as of the most-recent version. However, any changes may be detected by opening a database created by each version with a SQLite manager, then exporting either a query of the full sqlite_master table or the database schema (just the SQL field of that table wrapped in a transaction) and comparing the exports from each version using a text editor that has a compare function (e.g. Notepad++).

The page RM5 Database System Catalog displays the full sqlite_master table from 5.0.0.6.

For future comparisons, below is the schema from 5.0.0.6:

BEGIN TRANSACTION;
CREATE TABLE AddressLinkTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, AddressID INTEGER, OwnerID INTEGER, AddressNum INTEGER, Details TEXT );
CREATE TABLE AddressTable (AddressID INTEGER PRIMARY KEY, AddressType INTEGER, Name TEXT COLLATE RMNOCASE, Street1 TEXT, Street2 TEXT, City TEXT, State TEXT, Zip TEXT, Country TEXT, Phone1 TEXT, Phone2 TEXT, Fax TEXT, Email TEXT, URL TEXT, Latitude INTEGER, Longitude INTEGER, Note BLOB );
CREATE TABLE ChildTable (RecID INTEGER PRIMARY KEY, ChildID INTEGER, FamilyID INTEGER, RelFather INTEGER, RelMother INTEGER, ChildOrder INTEGER, IsPrivate INTEGER, ProofFather INTEGER, ProofMother INTEGER, Note BLOB );
CREATE TABLE CitationTable (CitationID INTEGER PRIMARY KEY, OwnerType INTEGER, SourceID INTEGER, OwnerID INTEGER, Quality TEXT, IsPrivate INTEGER, Comments BLOB, ActualText BLOB, RefNumber TEXT, Flags INTEGER, FIELDS BLOB );
CREATE TABLE ConfigTable (RecID INTEGER PRIMARY KEY, RecType INTEGER, Title TEXT, DataRec BLOB );
CREATE TABLE EventTable (EventID INTEGER PRIMARY KEY, EventType INTEGER, OwnerType INTEGER, OwnerID INTEGER, FamilyID INTEGER, PlaceID INTEGER, SiteID INTEGER, DATE TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, STATUS INTEGER, EditDate FLOAT, Sentence BLOB, Details BLOB, Note BLOB );
CREATE TABLE ExclusionTable (RecID INTEGER PRIMARY KEY, ExclusionType INTEGER, ID1 INTEGER, ID2 INTEGER );
CREATE TABLE FactTypeTable (FactTypeID INTEGER PRIMARY KEY, OwnerType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, GedcomTag TEXT, UseValue INTEGER, UseDate INTEGER, UsePlace INTEGER, Sentence BLOB, Flags INTEGER );
CREATE TABLE FamilyTable (FamilyID INTEGER PRIMARY KEY, FatherID INTEGER, MotherID INTEGER, ChildID INTEGER, HusbOrder INTEGER, WifeOrder INTEGER, IsPrivate INTEGER, Proof INTEGER, SpouseLabel INTEGER, FatherLabel INTEGER, MotherLabel INTEGER, Note BLOB );
CREATE TABLE GroupTable (RecID INTEGER PRIMARY KEY, GroupID INTEGER, StartID INTEGER, EndID INTEGER );
CREATE TABLE LabelTable (LabelID INTEGER PRIMARY KEY, LabelType INTEGER, LabelValue INTEGER, LabelName TEXT COLLATE RMNOCASE, Description TEXT );
CREATE TABLE LinkTable (LinkID INTEGER PRIMARY KEY, extSystem INTEGER, LinkType INTEGER, rmID INTEGER, extID TEXT, Modified INTEGER, extVersion TEXT, extDate FLOAT, STATUS INTEGER, Note BLOB );
CREATE TABLE MediaLinkTable (LinkID INTEGER PRIMARY KEY, MediaID INTEGER, OwnerType INTEGER, OwnerID INTEGER, IsPrimary INTEGER, Include1 INTEGER, Include2 INTEGER, Include3 INTEGER, Include4 INTEGER, SortOrder INTEGER, RectLeft INTEGER, RectTop INTEGER, RectRight INTEGER, RectBottom INTEGER, Note TEXT, Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, DATE TEXT, SortDate INTEGER, Description BLOB );
CREATE TABLE MultimediaTable (MediaID INTEGER PRIMARY KEY, MediaType INTEGER, MediaPath TEXT, MediaFile TEXT COLLATE RMNOCASE, URL TEXT, Thumbnail BLOB , Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, DATE TEXT, SortDate INTEGER, Description BLOB);
CREATE TABLE NameTable (NameID INTEGER PRIMARY KEY, OwnerID INTEGER, Surname TEXT COLLATE RMNOCASE, Given TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE, Nickname TEXT COLLATE RMNOCASE, NameType INTEGER, DATE TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, EditDate FLOAT, Sentence BLOB, Note BLOB, BirthYear INTEGER, DeathYear INTEGER );
CREATE TABLE PersonTable (PersonID INTEGER PRIMARY KEY, UniqueID TEXT, Sex INTEGER, EditDate FLOAT, ParentID INTEGER, SpouseID INTEGER, Color INTEGER, Relate1 INTEGER, Relate2 INTEGER, Flags INTEGER, Living INTEGER, IsPrivate INTEGER, Proof INTEGER, Bookmark INTEGER, Note BLOB );
CREATE TABLE PlaceTable (PlaceID INTEGER PRIMARY KEY, PlaceType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, Normalized TEXT, Latitude INTEGER, Longitude INTEGER, LatLongExact INTEGER, MasterID INTEGER, Note BLOB );
CREATE TABLE ResearchItemTable (ItemID INTEGER PRIMARY KEY, LogID INTEGER, DATE TEXT, SortDate INTEGER, RefNumber TEXT, Repository TEXT, Goal TEXT, SOURCE TEXT, RESULT TEXT );
CREATE TABLE RoleTable (RoleID INTEGER PRIMARY KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence TEXT );
CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, FIELDS BLOB );
CREATE TABLE SourceTemplateTable (TemplateID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, Description TEXT, Favorite INTEGER, Category TEXT, Footnote TEXT, ShortFootnote TEXT, Bibliography TEXT, FieldDefs BLOB );
CREATE TABLE WitnessTable (WitnessID INTEGER PRIMARY KEY, EventID INTEGER, PersonID INTEGER, WitnessOrder INTEGER, ROLE INTEGER, Sentence TEXT, Note BLOB, Given TEXT COLLATE RMNOCASE, Surname TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE );
CREATE INDEX idxAddressName ON AddressTable (Name);
CREATE INDEX idxChildFamilyID ON ChildTable (FamilyID);
CREATE INDEX idxChildID ON ChildTable (ChildID);
CREATE INDEX idxChildOrder ON ChildTable (ChildOrder);
CREATE INDEX idxCitationOwnerID ON CitationTable (OwnerID);
CREATE INDEX idxCitationSourceID ON CitationTable (SourceID);
CREATE UNIQUE INDEX idxExclusionIndex ON ExclusionTable (ExclusionType, ID1, ID2);
CREATE INDEX idxFactTypeAbbrev ON FactTypeTable (Abbrev);
CREATE INDEX idxFactTypeGedcomTag ON FactTypeTable (GedcomTag);
CREATE INDEX idxFactTypeName ON FactTypeTable (Name);
CREATE INDEX idxFamilyFatherID ON FamilyTable (FatherID);
CREATE INDEX idxFamilyMotherID ON FamilyTable (MotherID);
CREATE INDEX idxGiven ON NameTable (Given);
CREATE INDEX idxLabelType ON LabelTable (LabelType);
CREATE INDEX idxLinkExtId ON LinkTable (extID);
CREATE INDEX idxLinkRmId ON LinkTable (rmID);
CREATE INDEX idxMediaCaption ON MediaLinkTable (Caption);
CREATE INDEX idxMediaFile ON MultimediaTable (MediaFile);
CREATE INDEX idxMediaOwnerID ON MediaLinkTable (OwnerID);
CREATE INDEX idxMediaURL ON MultimediaTable (URL);
CREATE INDEX idxNameOwnerID ON NameTable (OwnerID);
CREATE INDEX idxNamePrimary ON NameTable (IsPrimary);
CREATE INDEX idxOwnerDate ON EventTable (OwnerID,SortDate);
CREATE INDEX idxOwnerEvent ON EventTable (OwnerID,EventType);
CREATE INDEX idxPlaceAbbrev ON PlaceTable (Abbrev);
CREATE INDEX idxPlaceName ON PlaceTable (Name);
CREATE INDEX idxRecType ON ConfigTable (RecType);
CREATE INDEX idxResearchItemLogID ON ResearchItemTable (LogID);
CREATE INDEX idxResearchName ON ResearchTable (Name);
CREATE INDEX idxResearchOwnerID ON ResearchTable (OwnerID);
CREATE INDEX idxRoleEventType ON RoleTable (EventType);
CREATE INDEX idxSourceName ON SourceTable (Name);
CREATE INDEX idxSourceTemplateName ON SourceTemplateTable (Name);
CREATE INDEX idxSurname ON NameTable (Surname);
CREATE INDEX idxSurnameGiven ON NameTable (Surname, Given, BirthYear, DeathYear);
CREATE INDEX idxWitnessEventID ON WitnessTable (EventID);
CREATE INDEX idxWitnessPersonID ON WitnessTable (PersonID);
COMMIT TRANSACTION;

RM5 Table Summaries #datadefinitions

Posted on by Tom Holden

The following non-system tables currently reside in the RM5 database:

NameDescription
AddressLinkTableTranslates OwnerID from various tables to the key AddressID in AddressTable.
AddressTableStores Addresses for Persons in the tree, Repositories, and other entities that need not be in the tree. Note that the Name in this table is not the same as the names in the NameTable.
ChildTableStores relationship to family(ies) for each child. A child may have two or more families – e.g., birth and adoptive. Links to PersonTable for child and to FamilyTable for parents.
CitationTableStores Citation details with links to PersonTable, FamilyTable, and EventTable and to the SourceTable for the sources cited. One record per citation.
ConfigTableStores File settings, Report settings, Fonts, etc to preserve between sessions.
EventTableStores details for each Fact/Event with links to Persons, Families having the event and to the FactTypeTable for the Fact properties.
ExclusionTableStores Problem List items indicated by user as Not a problem.
FactTypeTableDefinition of the standard and user-defined Facts related to by the EventTable.
FamilyTableStores Family pairings, spousal order, and notes.
GroupTableStores data for Named Groups: ranges of PersonID’s from PersonTable that were marked for the group.
LabelTableStores labels for Named Groups and probably other labels, too.
LinkTableProbably has to do with New Family Search support and future support for remote Media.
MediaLinkTableStores relationship between records in MultiMediaTable and Person, Event, Place Tables along with settings for use in reports or scrapbooks, captions, notes (unused?), descriptions.
MultimediaTableStores path and name to Media files, a type of media code, URL (unused as of 5.0.0.6), and binary thumbnail for image files.
NameTableStores name parts for persons in tree, including Alternate Names as separate records (unlimited), primary and privacy settings, birth year and death year.
PersonTableStores basic data for persons in tree: globally Unique ID as well as internal PersonID, FamilyID of parent family and PersonID of spouse, calculated relationship to other person in tree, privacy and living flags, and general note.
PlaceTableStores system pre-defined and user-defined Places and Place Details (or sites) with Name, Abbreviation, Normalized Name, geographical coordinates, and notes. Linked from eventtable.
ResearchItemTableStores
ResearchTableStores contents of To-Do list with link to PersonTable or FamilyTable, depending on where task was created.
RoleTableStores system pre-defined and user-defined Roles with sentence templates for most FactTypes, linked from WitnessTable.
SourceTableStores Citation Source details for each unique source, linked from CitationTable (many to 1 allowed); linked to AddressTable for Source Repository and to SourceTemplateTable for support of Citation data entry and reports.
SourceTemplateTableStores system pre-defined and user-defined sentence and field templates for different kinds of citation sources, following published guidelines. Linked from SourceTable for support of Citation data entry and reports.
WitnessTableStores data for individual and family Event sharing among persons in tree and outside the tree, with links to RoleTable (Witness, Doctor, user-defined), EventTable, and to PersonTable (if in tree). Stores note for shared event plus name of person if not in tree. If person is not in tree, nothing of this record appears in reports.

RM5 Database System Catalog #datadefinitions

Posted on by Tom Holden

A singular system table, sqlite_master, resides in a SQLite database. The table in the RootsMagic 5 database essentially defines the RootsMagic 5 database tables, indices, and fields, as below.

Differences in structure with RootsMagic 4 include the addition of a new table, ResearchItemTable (and its index), to incorporate the newly-incorporated Research Log feature. In addition, MultimediaTable now includes five additional fields to account for the revised treatment of media throughout the program.

typenametbl_namerootpagesql
tableConfigTableConfigTable2CREATE TABLE ConfigTable (RecID INTEGER PRIMARY KEY, RecType INTEGER, Title TEXT, DataRec BLOB )
indexidxRecTypeConfigTable3CREATE INDEX idxRecType ON ConfigTable (RecType)
tablePersonTablePersonTable4CREATE TABLE PersonTable (PersonID INTEGER PRIMARY KEY, UniqueID TEXT, Sex INTEGER, EditDate FLOAT, ParentID INTEGER, SpouseID INTEGER, Color INTEGER, Relate1 INTEGER, Relate2 INTEGER, Flags INTEGER, Living INTEGER, IsPrivate INTEGER, Proof INTEGER, Bookmark INTEGER, Note BLOB )
tableFamilyTableFamilyTable5CREATE TABLE FamilyTable (FamilyID INTEGER PRIMARY KEY, FatherID INTEGER, MotherID INTEGER, ChildID INTEGER, HusbOrder INTEGER, WifeOrder INTEGER, IsPrivate INTEGER, Proof INTEGER, SpouseLabel INTEGER, FatherLabel INTEGER, MotherLabel INTEGER, Note BLOB )
indexidxFamilyFatherIDFamilyTable6CREATE INDEX idxFamilyFatherID ON FamilyTable (FatherID)
indexidxFamilyMotherIDFamilyTable8CREATE INDEX idxFamilyMotherID ON FamilyTable (MotherID)
tableChildTableChildTable10CREATE TABLE ChildTable (RecID INTEGER PRIMARY KEY, ChildID INTEGER, FamilyID INTEGER, RelFather INTEGER, RelMother INTEGER, ChildOrder INTEGER, IsPrivate INTEGER, ProofFather INTEGER, ProofMother INTEGER, Note BLOB )
indexidxChildIDChildTable11CREATE INDEX idxChildID ON ChildTable (ChildID)
indexidxChildFamilyIDChildTable12CREATE INDEX idxChildFamilyID ON ChildTable (FamilyID)
indexidxChildOrderChildTable13CREATE INDEX idxChildOrder ON ChildTable (ChildOrder)
tableEventTableEventTable14CREATE TABLE EventTable (EventID INTEGER PRIMARY KEY, EventType INTEGER, OwnerType INTEGER, OwnerID INTEGER, FamilyID INTEGER, PlaceID INTEGER, SiteID INTEGER, Date TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, Status INTEGER, EditDate FLOAT, Sentence BLOB, Details BLOB, Note BLOB )
indexidxOwnerEventEventTable15CREATE INDEX idxOwnerEvent ON EventTable (OwnerID,EventType)
indexidxOwnerDateEventTable17CREATE INDEX idxOwnerDate ON EventTable (OwnerID,SortDate)
tableAddressTableAddressTable18CREATE TABLE AddressTable (AddressID INTEGER PRIMARY KEY, AddressType INTEGER, Name TEXT COLLATE RMNOCASE, Street1 TEXT, Street2 TEXT, City TEXT, State TEXT, Zip TEXT, Country TEXT, Phone1 TEXT, Phone2 TEXT, Fax TEXT, Email TEXT, URL TEXT, Latitude INTEGER, Longitude INTEGER, Note BLOB )
indexidxAddressNameAddressTable19CREATE INDEX idxAddressName ON AddressTable (Name)
tableFactTypeTableFactTypeTable20CREATE TABLE FactTypeTable (FactTypeID INTEGER PRIMARY KEY, OwnerType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, GedcomTag TEXT, UseValue INTEGER, UseDate INTEGER, UsePlace INTEGER, Sentence BLOB, Flags INTEGER )
indexidxFactTypeNameFactTypeTable21CREATE INDEX idxFactTypeName ON FactTypeTable (Name)
indexidxFactTypeAbbrevFactTypeTable22CREATE INDEX idxFactTypeAbbrev ON FactTypeTable (Abbrev)
indexidxFactTypeGedcomTagFactTypeTable24CREATE INDEX idxFactTypeGedcomTag ON FactTypeTable (GedcomTag)
tableMultimediaTableMultimediaTable25CREATE TABLE MultimediaTable (MediaID INTEGER PRIMARY KEY, MediaType INTEGER, MediaPath TEXT, MediaFile TEXT COLLATE RMNOCASE, URL TEXT, Thumbnail BLOB , Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, Date TEXT, SortDate INTEGER, Description BLOB)
indexidxMediaFileMultimediaTable26CREATE INDEX idxMediaFile ON MultimediaTable (MediaFile)
indexidxMediaURLMultimediaTable27CREATE INDEX idxMediaURL ON MultimediaTable (URL)
tableMediaLinkTableMediaLinkTable28CREATE TABLE MediaLinkTable (LinkID INTEGER PRIMARY KEY, MediaID INTEGER, OwnerType INTEGER, OwnerID INTEGER, IsPrimary INTEGER, Include1 INTEGER, Include2 INTEGER, Include3 INTEGER, Include4 INTEGER, SortOrder INTEGER, RectLeft INTEGER, RectTop INTEGER, RectRight INTEGER, RectBottom INTEGER, Note TEXT, Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, Date TEXT, SortDate INTEGER, Description BLOB )
indexidxMediaOwnerIDMediaLinkTable30CREATE INDEX idxMediaOwnerID ON MediaLinkTable (OwnerID)
indexidxMediaCaptionMediaLinkTable31CREATE INDEX idxMediaCaption ON MediaLinkTable (Caption)
tableNameTableNameTable32CREATE TABLE NameTable (NameID INTEGER PRIMARY KEY, OwnerID INTEGER, Surname TEXT COLLATE RMNOCASE, Given TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE, Nickname TEXT COLLATE RMNOCASE, NameType INTEGER, Date TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, EditDate FLOAT, Sentence BLOB, Note BLOB, BirthYear INTEGER, DeathYear INTEGER )
indexidxNameOwnerIDNameTable34CREATE INDEX idxNameOwnerID ON NameTable (OwnerID)
indexidxSurnameNameTable35CREATE INDEX idxSurname ON NameTable (Surname)
indexidxGivenNameTable36CREATE INDEX idxGiven ON NameTable (Given)
indexidxSurnameGivenNameTable37CREATE INDEX idxSurnameGiven ON NameTable (Surname, Given, BirthYear, DeathYear)
indexidxNamePrimaryNameTable38CREATE INDEX idxNamePrimary ON NameTable (IsPrimary)
tablePlaceTablePlaceTable39CREATE TABLE PlaceTable (PlaceID INTEGER PRIMARY KEY, PlaceType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, Normalized TEXT, Latitude INTEGER, Longitude INTEGER, LatLongExact INTEGER, MasterID INTEGER, Note BLOB )
indexidxPlaceNamePlaceTable41CREATE INDEX idxPlaceName ON PlaceTable (Name)
indexidxPlaceAbbrevPlaceTable42CREATE INDEX idxPlaceAbbrev ON PlaceTable (Abbrev)
tableResearchTableResearchTable43CREATE TABLE ResearchTable (TaskID INTEGER PRIMARY KEY, TaskType INTEGER, OwnerID INTEGER, OwnerType INTEGER, RefNumber TEXT, Name TEXT COLLATE RMNOCASE, Status INTEGER, Priority INTEGER, Date1 TEXT, Date2 TEXT, Date3 TEXT, SortDate1 INTEGER, SortDate2 INTEGER, SortDate3 INTEGER, Filename TEXT, Details BLOB )
indexidxResearchOwnerIDResearchTable44CREATE INDEX idxResearchOwnerID ON ResearchTable (OwnerID)
indexidxResearchNameResearchTable45CREATE INDEX idxResearchName ON ResearchTable (Name)
tableSourceTableSourceTable46CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, Fields BLOB )
indexidxSourceNameSourceTable48CREATE INDEX idxSourceName ON SourceTable (Name)
tableCitationTableCitationTable49CREATE TABLE CitationTable (CitationID INTEGER PRIMARY KEY, OwnerType INTEGER, SourceID INTEGER, OwnerID INTEGER, Quality TEXT, IsPrivate INTEGER, Comments BLOB, ActualText BLOB, RefNumber TEXT, Flags INTEGER, Fields BLOB )
indexidxCitationSourceIDCitationTable50CREATE INDEX idxCitationSourceID ON CitationTable (SourceID)
indexidxCitationOwnerIDCitationTable51CREATE INDEX idxCitationOwnerID ON CitationTable (OwnerID)
tableAddressLinkTableAddressLinkTable52CREATE TABLE AddressLinkTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, AddressID INTEGER, OwnerID INTEGER, AddressNum INTEGER, Details TEXT )
tableWitnessTableWitnessTable53CREATE TABLE WitnessTable (WitnessID INTEGER PRIMARY KEY, EventID INTEGER, PersonID INTEGER, WitnessOrder INTEGER, Role INTEGER, Sentence TEXT, Note BLOB, Given TEXT COLLATE RMNOCASE, Surname TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE )
indexidxWitnessEventIDWitnessTable55CREATE INDEX idxWitnessEventID ON WitnessTable (EventID)
indexidxWitnessPersonIDWitnessTable56CREATE INDEX idxWitnessPersonID ON WitnessTable (PersonID)
tableLinkTableLinkTable57CREATE TABLE LinkTable (LinkID INTEGER PRIMARY KEY, extSystem INTEGER, LinkType INTEGER, rmID INTEGER, extID TEXT, Modified INTEGER, extVersion TEXT, extDate FLOAT, Status INTEGER, Note BLOB )
indexidxLinkRmIdLinkTable58CREATE INDEX idxLinkRmId ON LinkTable (rmID)
indexidxLinkExtIdLinkTable59CREATE INDEX idxLinkExtId ON LinkTable (extID)
tableRoleTableRoleTable60CREATE TABLE RoleTable (RoleID INTEGER PRIMARY KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence TEXT )
indexidxRoleEventTypeRoleTable62CREATE INDEX idxRoleEventType ON RoleTable (EventType)
tableGroupTableGroupTable63CREATE TABLE GroupTable (RecID INTEGER PRIMARY KEY, GroupID INTEGER, StartID INTEGER, EndID INTEGER )
tableExclusionTableExclusionTable64CREATE TABLE ExclusionTable (RecID INTEGER PRIMARY KEY, ExclusionType INTEGER, ID1 INTEGER, ID2 INTEGER )
indexidxExclusionIndexExclusionTable65CREATE UNIQUE INDEX idxExclusionIndex ON ExclusionTable (ExclusionType, ID1, ID2)
tableSourceTemplateTableSourceTemplateTable66CREATE TABLE SourceTemplateTable (TemplateID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, Description TEXT, Favorite INTEGER, Category TEXT, Footnote TEXT, ShortFootnote TEXT, Bibliography TEXT, FieldDefs BLOB )
indexidxSourceTemplateNameSourceTemplateTable67CREATE INDEX idxSourceTemplateName ON SourceTemplateTable (Name)
tableLabelTableLabelTable69CREATE TABLE LabelTable (LabelID INTEGER PRIMARY KEY, LabelType INTEGER, LabelValue INTEGER, LabelName TEXT COLLATE RMNOCASE, Description TEXT )
indexidxLabelTypeLabelTable70CREATE INDEX idxLabelType ON LabelTable (LabelType)
tableResearchItemTableResearchItemTable288CREATE TABLE ResearchItemTable (ItemID INTEGER PRIMARY KEY, LogID INTEGER, Date TEXT, SortDate INTEGER, RefNumber TEXT, Repository TEXT, Goal TEXT, Source TEXT, Result TEXT )
indexidxResearchItemLogIDResearchItemTable304CREATE INDEX idxResearchItemLogID ON ResearchItemTable (LogID)

Comparing Two RM Databases #compare #database #rmnocase

Posted on by thejerrybryan

I wasn’t sure whether to post this page on the RootsMagic 4 wiki or the RootsMagic 5 wiki, but it really applies to both.

I’ve been considering the problem of data loss when using the RM Drag and Drop facility. Behind the scenes, Drag and Drop consists of a GEDCOM export from one RM database followed immediately by a GEDCOM import into another RM database. So the problem of the data loss really reduces down to identifying and fixing the causes of data loss in GEDCOM export and identifying and fixing the causes of data loss in GEDCOM import. I’m not sure that users such as us can do very much about fixing the causes, but I think we might be able to help with identifying the causes. And a constant question that’s never been answered to my satisfaction is, just what data might be lost in a Drag and Drop operation? What are all the actual possible causes, not just what are some of the rumored possible causes?

My idea is as follows. What if I could Drag and Drop my entire database into a new, empty database, and then run some kind of comparison utility that would compare my original database to the copy, and to do so at the SQL table and row level? Even if the Drag and Drop were perfect, I could imagine some trivial differences. But since Drag and Drop is not perfect, I would hope that those trivial differences that couldn’t be prevented wouldn’t obscure the real differences that would represent defects in the Drag and Drop process.

My initial concept was to try to write SQL that would accomplish the required comparison, but a little searching of the Internet revealed that there are already utilities available for download that seem to be more than capable of doing the required download. The utility that I settled on for initial testing may be found at
http://www.codeproject.com/KB/database/SQLiteCompareUtility.aspx and is called simply SQLite Compare. It seems pretty slick. It will run comparisons, identify which tables are different between the original and the copy, and then will allow you to drill down to see the differences. It allows you to include or not include BLOB fields in the comparison.

To that end and as a preliminary test, I made a copy of my database with the RM Copy function and ran SQLite Compare. Because this was a database level copy rather than a Drag and Drop, I was expecting no differences. But there were a few tables with differences, namely the Address Table, the Media Link Table, the Research Table, the Source Table, and the Source Template Table. All the rest of the tables compared just fine. Being a bit puzzled as to why any of the tables should have had any differences, I tried using the SQLite Compare capability to drill down to see the differences. I shouldn’t have been surprised by the results, but I was just a little bit surprised (shame on me). My attempt to drill down failed with the following dreaded error message.

ERROR: data comparison failed (SQLite error no such collation sequence: RMNOCASE)

So I’m wondering if anybody can think of a way to get around this problem? For the purposes of what I’m trying to accomplish, I really don’t need RMNOCASE nor just plain NOCASE nor any other kind of case processing. Is there anyway to turn off RMNOCASE for a whole database (a copy of a copy sort of thing, of course – not my real database) in order to be able to run SQLite Compare. The truth is that even if I tried to write my own compare utility, I would have to fight with the RMNOCASE problem all the way through.

Jerry

Added later in the day on 12/6/2011: I’m totally new to the SQLite Compare utility. Upon further review and upon playing with it further, I think I slightly misinterpreted the initial results. It was not saying that there were differences in the Address Table, the Media Link Table, etc. after the copy. Rather, it was saying that there were what it calls “comparison errors” after the copy. And when I drilled down to see what was going on, the RMNOCASE error messages provided the details of the “comparison errors”. So I’m going to have to think about this a little more and experiment a little more. Given that the tables listed above had RMNOCASE error messages, why didn’t other tables that use RMNOCASE collation also have RMNOCASE error messages rather than comparing as equal?

Discussions & comments from Wikispaces site


ve3meo

Faux RMNOCASE

ve3meo
07 December 2011 01:08:51

Jerry, what I have done successfully in the past without obvious damage was to rename a UTF8 collation in the paid license version of SQLite Developer to RMNOCASE. The good news with RM5 is the REINDEX utility so that even if the faux collation results in something unwanted, the indexes can be rebuilt with the right collation.

Tom


ve3meo

Try a NOCASE Copy of the Database

ve3meo
07 December 2011 03:26:29

This idea works in principle.

1. Get the full DDL of your RM4 (sub RM5 if that’s what you are working on) into a text editor.
2. Global replace RMNOCASE with NOCASE and save as a SQL file named, say, RM4_Create_as_DB3_NOCASE.sql
3. Create new empty DB3 database; load and run the modified DDL to create a NOCASE mirror of the RM4 structure
4. With your DB3 open, execute this:
ATTACH DATABASE ‘full URI to the RM4 file’ AS RM4;
5. Then run the following statement for each table (NameTable is the example):
INSERT INTO NameTable SELECT * FROM [RM4].NameTable;
6. Now you have all the data from your RM4 database in a DB3 with standard collations.
7. Do the same thing for each control and test database
8. Now run SQLite_Compare against the control DB3 and test DB3. It should work without tripping over the RMNOCASE collation.

Tom


ve3meo

ve3meo
07 December 2011 04:07:41

 

File Not Found

has step 5 expanded to all RM4 tables. According to romer, there is one additional table to be covered in RM5.


thejerrybryan

Thanks for the RMNOCASE Info

thejerrybryan
07 December 2011 04:22:19

I think I’ll try Tom’s free solution, but it may be several days before I have time to try it.

I’m actually on 5 now, and I’m aware of the extra table. I agree that moving forward most posts on the Wiki should be posted to 5 rather than 4 unless there is an issue specific to 4.

Jerry


mfseeker

RM Drag and Drop

mfseeker
25 January 2012 15:03:28

Jerry,

Have you made any further progress with your original project of comparing RM databases to evaluate the accuracy of RM’s GEDCOM out to GEDCOM in cycles?


thejerrybryan

thejerrybryan
26 January 2012 04:21:54

I put the project on hold when I realized that the SQLite Compare Utility wouldn’t be able to perform meaningful compares after a drag and drop or export/import. The problem is that most table rows will be “renumbered” after a drag and drop or export/import.

Convert Database to NOCASE

I do have another idea how to approach the project, but I haven’t had time to try it out.

Jerry


ve3meo

Comment: “…a way to get around this problem?” (no such collation sequence: RMNOCASE)

ve3meo
03 September 2018 20:10:25

ve3meo Dec 6, 2011

See my discussion notes:
a) $ for a SQLite manager that can fake a RMNOCASE
b) free solution copying the data for both the control and the test RM databases to DB3 files with same structure but NOCASE instead of RMNOCASE and comparing the DB3’s.


ve3meo

Comment: “SQLite Compare”

ve3meo
06 September 2018 18:26:45

ve3meo Dec 6, 2011

Very interesting find!

All Citations – Query #citations

Posted on by Tom Holden

CTE Versions for RM7 and RM8

Revised code by Pat Jones using common table expressions:
Version for RM7 and below  AllCitations-RM7.sql  tested 2021-01-20

Version for #RM8 and above  AllCitations-RM8.sql revised 2022-04-27 to include duplicate uses

Unlike the old version, this script does not appear to be able to display “headless citations” or “phantom sources or citations” as Null fields as in the screenshot below from the original script from 2010.

Old version for RM7 and below

AllCitations.sql revised 2022-04-26 to correct a SQLite error detected by later versions of SQLite 

Lists all citations in the database from which citations of non-existent sources (‘phantoms’) and citations for non-existent events or persons (‘headless’) can be found, along with other useful information such as all citations per source.

The query builds a temporary table with index which it then queries with a filter to suppress duplicate reports of citations against Alternate Names. While the query could be made without a temporary table, it would run exponentially more slowly with larger databases without invoking measures that are deprecated by SQLite documentation and incompatible with SQLite versions < 3.6.3. The intermediate table overcomes this problem and may be used for additional queries that will execute very quickly.

SQLiteSpy-AllCitations.png
Screenshot from SQLiteSpy

Discussions & comments from Wikispaces site


ve3meo

 

Slow query – possible improvement w/o temp table

 

ve3meo
05 February 2010 15:26:27

Parking this idea here for reference.

Problem: I’ve encountered really slow query speed on larger databases, usually when both NameTable.IsPrimary and OwnerID are constraints. When just OwnerID is the constraint, the sqlite query optimizer correctly chooses the idxNameTableOwnerID index and it runs fast. Add the IsPrimary constraint and it chooses the idxNameTableIsPrimary index and it runs very slow.

Possible Solutions:
1. Use the INDEXED BY clause to override the optimisier. However, use of this clause to tune performance is a “No-No”, according to the docs and not supported by sqlite < 3.6.3, e.g., by SQLiteman and DBTools DBManager.

2. Temporary table built w/o the IsPrimary constraint but including IsPrimary as a field. Query the temp table with the IsPrimary constraint. Works well.

3. Newest idea from the sqlite newsgroup: when querying with both constraints, make the IsPrimary a formula or expression to trick the query optimiser to ignore it.
SELECT * FROM NameTable … WHERE OwnerID=’x’ AND +IsPrimary=1;

Ref:”Igor Tandetnik” <itandetnik@mvps.org> wrote in message news:hkh4a0$mk8$1@ger.gmane.org…


ve3meo

 

ve3meo
05 February 2010 16:17:49

I meant to post this against the AllFacts4Persons query which is where I first ran into the speed problem. Having just tested the slight modification of the query below by adding the ‘+’ operator to the IsPrimary constraints improved the execution time using sqlite 3.6.22 by a factor of 3883, from 1130s to 0.291s!!!

[code]SELECT FactTypeTable.Name COLLATE NOCASE AS Fact, ‘Principal’ AS ‘Role
Type’, NameTable1.OwnerID AS RIN, NameTable1.Surname COLLATE NOCASE AS
Surname, NameTable1.Suffix COLLATE NOCASE AS Suffix, NameTable1.Prefix
COLLATE NOCASE AS Prefix, NameTable1.Given COLLATE NOCASE AS ‘Given Name’,
NameTable2.OwnerID AS ‘Sharer RIN’, NameTable2.Surname COLLATE NOCASE AS
‘Sharer Surname’, NameTable2.Suffix COLLATE NOCASE AS ‘Sharer Suffix’,
NameTable2.Prefix COLLATE NOCASE AS ‘Sharer Prefix’, NameTable2.Given
COLLATE NOCASE AS ‘Sharer Given Name’, COUNT(1) AS Count
FROM EventTable
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
INNER JOIN NameTable AS NameTable1 ON FamilyTable.FatherID =
NameTable1.OwnerID
INNER JOIN NameTable AS NameTable2 ON FamilyTable.MotherID =
NameTable2.OwnerID
WHERE EventTable.OwnerType = 1 AND +NameTable1.IsPrimary = 1 AND
+NameTable2.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12[/code]


Geraniums

 

Citations for non-existent events or persons (‘headless’)

Geraniums
05 July 2010 09:36:07

I have a large area of pink in the report.

Do the “CitID” and “CrcID” numbers mean anything? There are no surnames or given names – I assume that’s what’s meant by “headless”.

How can I find out where in the RootsMagic database that these can be fixed.

Thanks,
Debbie


ve3meo

 

ve3meo
05 July 2010 12:17:15

CitID is the internal reference number for a citation and SrcID is the same for a Master Source, both numbers hidden from the RootsMagic user.

If you are seeing pink, I think you may be using SQLiteSpy -that’s how it indicates a Null value. A Null value for both Surname and Given likely indicates a “headless” citation, unless a person was entered in the database without being assigned any names – I think you may have a few examples of that.

Headless citations cannot be fixed in the RootsMagic app except through a GEDCOM export/import.

Tom

Scope of Names in Queries and Sub-queries #msaccess #subqueries

Posted on by thejerrybryan

I have been running into some subtle little problems with queries and sub-queries that suggest to me that I don’t have an adequate understanding of the scope of names within SQL. To that end, I created the following very trivial queries to test against both SQLite and MS Access.

--  Test query #1
SELECT Z.PersonID FROM
  (
   SELECT P.PersonID
     FROM PersonTable AS P
     WHERE P.PersonID <= 5
  ) AS Z
 
--  Test query #2
SELECT P.PersonID FROM
  (
   SELECT P.PersonID
     FROM PersonTable AS P
     WHERE P.PersonID <= 5
  ) AS Z
 
  --  Test query #3
SELECT PersonID FROM
  (
   SELECT P.PersonID
     FROM PersonTable AS P
     WHERE P.PersonID <= 5
  ) AS Z

All three queries use the identical sub-query. The only thing the sub-query does is to create a virtual table containing one column and up to five rows, where the column is the PersonID from the PersonTable and the rows are the PersonID values from 1 to 5. In my database, all five rows exist. The five rows may or may not all exist in any other database depending on whether or not the first five people that were entered into the database have been deleted or merged since they were first entered, or indeed whether as many as five people were ever entered into the database.

Query #1 works in both SQLite and MS Access (except that you have to delete the comment for MS Access). This is the expected behavior. It seems to be the case that SQL is always happy with one alias for a table in the sub-query (the alias is P in this case) and another alias for the same table outside the sub-query (the alias is Z in this case).

Query #2 fails in SQLite and works in MS Access (except that you have to delete the comment for MS Access). I think the query should fail in both cases, and I find the fact that it works in MS Access to be extremely disappointing. It appears that SQLite is treating the alias P as local to the sub-query but that MS Access is treating the alias P as global to the entire query, both inside and outside the sub-query. That seems to explain some rather strange looking errors I have seen from MS Access when I used the same alias both inside and outside a sub-query or when I used the same alias in two different and (I thought) independent sub-queries. It’s beginning to seem to me that to use MS Access safely, any table that is aliased multiple times in the same query will have to have a distinct alias each time, even though would seem that sensible scope rules would not really make distinct aliases necessary. Which is to say, it sounds like you can’t say that aliases in MS Access have to be “unique within the same scope but don’t have to be unique when they appear in different scopes” because MS Access seems to have only one scope – namely the whole query. I may not be completely correct about MS Access’s scope rules, but treating all scopes as global and programming accordingly seems to be the only thing that always works safely for me in the MS Access environment.

Query #3 works works in both SQLite and MS Access (except that you have to delete the comment for MS Access). I suppose that’s ok, but it makes me uncomfortable to write my code that way where the table that goes with a column is implicit, even though the table in question is a virtual table created with a sub-query. So I will write my query/sub-query combinations in the style of Query #1 rather than in the style of Query #3.

Jerry