Logo Home    Page list

Database System Catalog #datadefinitions

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

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

www.000webhost.com