Contents

1RootsMagic 4 Database System Catalog
1.1Discussions & comments from Wikispaces site
1.1.1Pulling Together All the Events for An Individual
1.1.2Use of page-related Discussion

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

Database System Catalog #datadefinitions

RootsMagic 4 Database System Catalog

Discussions & comments from Wikispaces site

Leave a Reply Cancel reply