SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Database System Catalog #datadefinitions

Posted on by chief superintendent

RootsMagic 4 Database System Catalog

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

type name tbl_name rootpage sql
table AddressLinkTable AddressLinkTable 52 CREATE TABLE AddressLinkTable (LinkID INTEGER
PRIMARY KEY, OwnerType INTEGER, AddressID INTEGER, OwnerID INTEGER, AddressNum
INTEGER, Details TEXT )
index idxAddressName AddressTable 19 CREATE INDEX idxAddressName ON AddressTable (Name)
table AddressTable AddressTable 18 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
)
index idxChildID ChildTable 11 CREATE INDEX idxChildID ON ChildTable (ChildID)
index idxChildFamilyID ChildTable 12 CREATE INDEX idxChildFamilyID ON ChildTable (FamilyID)
index idxChildOrder ChildTable 13 CREATE INDEX idxChildOrder ON ChildTable (ChildOrder)
table ChildTable ChildTable 10 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 )
index idxCitationSourceID CitationTable 50 CREATE INDEX idxCitationSourceID ON CitationTable
(SourceID)
table CitationTable CitationTable 49 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 )
index idxCitationOwnerID CitationTable 51 CREATE INDEX idxCitationOwnerID ON CitationTable
(OwnerID)
index idxRecType ConfigTable 3 CREATE INDEX idxRecType ON ConfigTable (RecType)
table ConfigTable ConfigTable 2 CREATE TABLE ConfigTable (RecID INTEGER PRIMARY
KEY, RecType INTEGER, Title TEXT, DataRec BLOB )
index idxOwnerDate EventTable 17 CREATE INDEX idxOwnerDate ON EventTable (OwnerID,SortDate)
index idxOwnerEvent EventTable 15 CREATE INDEX idxOwnerEvent ON EventTable (OwnerID,EventType)
table EventTable EventTable 14 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 )
index idxExclusionIndex ExclusionTable 65 CREATE UNIQUE INDEX idxExclusionIndex ON ExclusionTable
(ExclusionType, ID1, ID2)
table ExclusionTable ExclusionTable 64 CREATE TABLE ExclusionTable (RecID INTEGER PRIMARY
KEY, ExclusionType INTEGER, ID1 INTEGER, ID2 INTEGER )
table FactTypeTable FactTypeTable 20 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 )
index idxFactTypeAbbrev FactTypeTable 22 CREATE INDEX idxFactTypeAbbrev ON FactTypeTable
(Abbrev)
index idxFactTypeName FactTypeTable 21 CREATE INDEX idxFactTypeName ON FactTypeTable
(Name)
index idxFactTypeGedcomTag FactTypeTable 24 CREATE INDEX idxFactTypeGedcomTag ON FactTypeTable
(GedcomTag)
table FamilyTable FamilyTable 5 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 )
index idxFamilyMotherID FamilyTable 8 CREATE INDEX idxFamilyMotherID ON FamilyTable
(MotherID)
index idxFamilyFatherID FamilyTable 6 CREATE INDEX idxFamilyFatherID ON FamilyTable
(FatherID)
table GroupTable GroupTable 63 CREATE TABLE GroupTable (RecID INTEGER PRIMARY
KEY, GroupID INTEGER, StartID INTEGER, EndID INTEGER )
table LabelTable LabelTable 69 CREATE TABLE LabelTable (LabelID INTEGER PRIMARY
KEY, LabelType INTEGER, LabelValue INTEGER, LabelName TEXT COLLATE RMNOCASE,
Description TEXT )
index idxLabelType LabelTable 70 CREATE INDEX idxLabelType ON LabelTable (LabelType)
index idxLinkRmId LinkTable 58 CREATE INDEX idxLinkRmId ON LinkTable (rmID)
table LinkTable LinkTable 57 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 )
index idxLinkExtId LinkTable 59 CREATE INDEX idxLinkExtId ON LinkTable (extID)
index idxMediaOwnerID MediaLinkTable 30 CREATE INDEX idxMediaOwnerID ON MediaLinkTable
(OwnerID)
table MediaLinkTable MediaLinkTable 28 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 )
index idxMediaCaption MediaLinkTable 31 CREATE INDEX idxMediaCaption ON MediaLinkTable
(Caption)
index idxMediaURL MultimediaTable 27 CREATE INDEX idxMediaURL ON MultimediaTable (URL)
table MultimediaTable MultimediaTable 25 CREATE TABLE MultimediaTable (MediaID INTEGER
PRIMARY KEY, MediaType INTEGER, MediaPath TEXT, MediaFile TEXT COLLATE RMNOCASE,
URL TEXT, Thumbnail BLOB )
index idxMediaFile MultimediaTable 26 CREATE INDEX idxMediaFile ON MultimediaTable
(MediaFile)
index idxSurname NameTable 35 CREATE INDEX idxSurname ON NameTable (Surname)
index idxGiven NameTable 36 CREATE INDEX idxGiven ON NameTable (Given)
table NameTable NameTable 32 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 )
index idxNameOwnerID NameTable 34 CREATE INDEX idxNameOwnerID ON NameTable (OwnerID)
index idxNamePrimary NameTable 38 CREATE INDEX idxNamePrimary ON NameTable (IsPrimary)
index idxSurnameGiven NameTable 37 CREATE INDEX idxSurnameGiven ON NameTable (Surname,
Given, BirthYear, DeathYear)
table PersonTable PersonTable 4 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 )
index idxPlaceName PlaceTable 41 CREATE INDEX idxPlaceName ON PlaceTable (Name)
index idxPlaceAbbrev PlaceTable 42 CREATE INDEX idxPlaceAbbrev ON PlaceTable (Abbrev)
table PlaceTable PlaceTable 39 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 )
index idxResearchOwnerID ResearchTable 44 CREATE INDEX idxResearchOwnerID ON ResearchTable
(OwnerID)
table ResearchTable ResearchTable 43 CREATE 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 )
index idxResearchName ResearchTable 45 CREATE INDEX idxResearchName ON ResearchTable
(Name)
table RoleTable RoleTable 60 CREATE TABLE RoleTable (RoleID INTEGER PRIMARY
KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence
TEXT )
index idxRoleEventType RoleTable 62 CREATE INDEX idxRoleEventType ON RoleTable (EventType)
table SourceTable SourceTable 46 CREATE TABLE SourceTable (SourceID INTEGER PRIMARY
KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT,
IsPrivate INTEGER, TemplateID INTEGER, Fields BLOB )
index idxSourceName SourceTable 48 CREATE INDEX idxSourceName ON SourceTable (Name)
table SourceTemplateTable SourceTemplateTable 66 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 )
index idxSourceTemplateName SourceTemplateTable 67 CREATE INDEX idxSourceTemplateName ON SourceTemplateTable
(Name)
table WitnessTable WitnessTable 53 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 )
index idxWitnessEventID WitnessTable 55 CREATE INDEX idxWitnessEventID ON WitnessTable
(EventID)
index idxWitnessPersonID WitnessTable 56 CREATE INDEX idxWitnessPersonID ON WitnessTable
(PersonID)

Discussions & comments from Wikispaces site


MarkVS

Pulling Together All the Events for An Individual

MarkVS
06 January 2010 16:12:56

One thing that I wanted to do, and I expect might be of interest to others, is to be able to stream all the events for an individual and then, depending on what reporting I wanted to do, sort and/or group and/or filter the records accordingly. I will document elsewhere how to get into the database via Microsoft Access but, whatever tool you are using, getting hold of all the Events for a person is not as straightforward as it might (should?) be.

From what I have learned to date it appears to be neceesary to run three separate queries and combine the results to get the “complete” datastream. These are the queries I think you need:

1) Firstly the straightforward one. The Events held on the Event Table for individuals need to be pulled off. It is important to select only those with an OWNERTYPE of 0. You can link to the various other tables from this data to pick up the FactType description, name of the person etc etc, as required.

2) Secondly, the family events. These are also held in the Event Table but with an OWNERTYPE of 1. This means that the event being recorded acutally applies to 2 people. So to get this data into a stream of events you need to take the value of the OWNERID field and use this to look up the record in the FAMILY table (ie not the PERSON table as you would if the OWNERTYPE was 0). In the Family table you will see the Husband and Wife ID and these can be used to pick up the names of the two people to whom the event applies. Then in theory generate an Event into the data stream for both of them.

3) Thirdly we need to pick up Shared events. These are recorded in the Witness table. This holds the Person ID to link back to the Name of the person and the Event ID to link back to the Event record (and from there to the fact description etc etc).

It seems to me the Database design could have been a wee bit slicker – but this is the way it appears to me at this time.

I am now intendeing to work on a combined set of queries that will pull all this data together into a single Access query – from which, hopefully, it will be easy to pull off regular reports without having to cope with a lot of multi-table SQL statements.

Regards, MVS


romermb

romermb
06 January 2010 23:08:00

For readability, I made some changes to the headers:

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 ‘Spouse RIN’, NameTable2.Surname COLLATE NOCASE AS ‘Spouse Surname’, NameTable2.Suffix COLLATE NOCASE AS ‘Spouse Suffix’, NameTable2.Prefix COLLATE NOCASE AS ‘Spouse Prefix’, NameTable2.Given COLLATE NOCASE AS ‘Spouse Given Name’
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

UNION

SELECT FactTypeTable.Name COLLATE NOCASE, ‘Principal’, NameTable2.OwnerID, NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE, NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE, NameTable1.OwnerID, NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE, NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE
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

UNION

SELECT FactTypeTable.Name COLLATE NOCASE, ‘Principal’, NameTable.OwnerID, NameTable.Surname COLLATE NOCASE, NameTable.Suffix COLLATE NOCASE, NameTable.Prefix COLLATE NOCASE, NameTable.Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL
FROM EventTable
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
INNER JOIN NameTable ON EventTable.OwnerID = NameTable.OwnerID
WHERE EventTable.OwnerType = 0 AND NameTable.IsPrimary = 1

UNION

SELECT ‘Alternate name’, ‘Principal’, OwnerID, Surname COLLATE NOCASE, Suffix COLLATE NOCASE, Prefix COLLATE NOCASE, Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL
FROM NameTable
WHERE IsPrimary = 0

UNION

SELECT FactTypeTable.Name COLLATE NOCASE, ‘Shared’, NameTable.OwnerID, NameTable.Surname COLLATE NOCASE, NameTable.Suffix COLLATE NOCASE, NameTable.Prefix COLLATE NOCASE, NameTable.Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL
FROM WitnessTable
INNER JOIN EventTable ON WitnessTable.EventID = EventTable.EventID
INNER JOIN NameTable ON WitnessTable.PersonID = NameTable.OwnerID
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
WHERE NameTable.IsPrimary = 1


ve3meo

Use of page-related Discussion

ve3meo
07 January 2010 14:52:53

Let’s try to start discussion topics around the most relevant page. For example, the first discussion topic on this ‘Database System Catalogue’ page is about the problem of designing a query that will pull together all the facts for each individual. I think that should have been started with a new page linked from the page ‘Problem Queries’. That would make it more probable for new members to find by being consistent with the wiki navigational structure (immature as it may be). I’ll try to move the essentials of that discussion over to a new ‘Problem Query’ page for continuation there.

Tom

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.