RM6 Database System Catalog #database #datadefinitions

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

The difference in structure with RootsMagic 5 is the addition of one new table, URLTable (no index) for WebTags.

 

typenametbl_namerootpagesql
tableAddressLinkTableAddressLinkTable21CREATE TABLE AddressLinkTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, AddressID INTEGER, OwnerID INTEGER, AddressNum INTEGER, Details TEXT )
tableAddressTableAddressTable9CREATE 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 )
indexidxAddressNameAddressTable42CREATE INDEX idxAddressName ON AddressTable (Name)
tableChildTableChildTable5CREATE TABLE ChildTable (RecID INTEGER PRIMARY KEY, ChildID INTEGER, FamilyID INTEGER, RelFather INTEGER, RelMother INTEGER, ChildOrder INTEGER, IsPrivate INTEGER, ProofFather INTEGER, ProofMother INTEGER, Note BLOB )
indexidxChildIDChildTable37CREATE INDEX idxChildID ON ChildTable (ChildID)
indexidxChildFamilyIDChildTable38CREATE INDEX idxChildFamilyID ON ChildTable (FamilyID)
indexidxChildOrderChildTable39CREATE INDEX idxChildOrder ON ChildTable (ChildOrder)
tableCitationTableCitationTable20CREATE 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 )
indexidxCitationSourceIDCitationTable62CREATE INDEX idxCitationSourceID ON CitationTable (SourceID)
indexidxCitationOwnerIDCitationTable63CREATE INDEX idxCitationOwnerID ON CitationTable (OwnerID)
tableConfigTableConfigTable2CREATE TABLE ConfigTable (RecID INTEGER PRIMARY KEY, RecType INTEGER, Title TEXT, DataRec BLOB )
indexidxRecTypeConfigTable34CREATE INDEX idxRecType ON ConfigTable (RecType)
tableEventTableEventTable7CREATE 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 )
indexidxOwnerEventEventTable40CREATE INDEX idxOwnerEvent ON EventTable (OwnerID,EventType)
indexidxOwnerDateEventTable41CREATE INDEX idxOwnerDate ON EventTable (OwnerID,SortDate)
tableExclusionTableExclusionTable28CREATE TABLE ExclusionTable (RecID INTEGER PRIMARY KEY, ExclusionType INTEGER, ID1 INTEGER, ID2 INTEGER )
indexidxExclusionIndexExclusionTable73CREATE UNIQUE INDEX idxExclusionIndex ON ExclusionTable (ExclusionType, ID1, ID2)
tableFactTypeTableFactTypeTable10CREATE 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 )
indexidxFactTypeNameFactTypeTable43CREATE INDEX idxFactTypeName ON FactTypeTable (Name)
indexidxFactTypeAbbrevFactTypeTable45CREATE INDEX idxFactTypeAbbrev ON FactTypeTable (Abbrev)
indexidxFactTypeGedcomTagFactTypeTable46CREATE INDEX idxFactTypeGedcomTag ON FactTypeTable (GedcomTag)
tableFamilyTableFamilyTable4CREATE 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 )
indexidxFamilyFatherIDFamilyTable35CREATE INDEX idxFamilyFatherID ON FamilyTable (FatherID)
indexidxFamilyMotherIDFamilyTable36CREATE INDEX idxFamilyMotherID ON FamilyTable (MotherID)
tableGroupTableGroupTable26CREATE TABLE GroupTable (RecID INTEGER PRIMARY KEY, GroupID INTEGER, StartID INTEGER, EndID INTEGER )
tableLabelTableLabelTable30CREATE TABLE LabelTable (LabelID INTEGER PRIMARY KEY, LabelType INTEGER, LabelValue INTEGER, LabelName TEXT COLLATE RMNOCASE, Description TEXT )
indexidxLabelTypeLabelTable71CREATE INDEX idxLabelType ON LabelTable (LabelType)
tableLinkTableLinkTable24CREATE TABLE LinkTable (LinkID INTEGER PRIMARY KEY, extSystem INTEGER, LinkType INTEGER, rmID INTEGER, extID TEXT, Modified INTEGER, extVersion TEXT, extDate FLOAT, Status INTEGER, Note BLOB )
indexidxLinkRmIdLinkTable67CREATE INDEX idxLinkRmId ON LinkTable (rmID)
indexidxLinkExtIdLinkTable68CREATE INDEX idxLinkExtId ON LinkTable (extID)
tableMediaLinkTableMediaLinkTable13CREATE 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 )
indexidxMediaOwnerIDMediaLinkTable49CREATE INDEX idxMediaOwnerID ON MediaLinkTable (OwnerID)
indexidxMediaCaptionMediaLinkTable50CREATE INDEX idxMediaCaption ON MediaLinkTable (Caption)
tableMultimediaTableMultimediaTable11CREATE 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)
indexidxMediaFileMultimediaTable47CREATE INDEX idxMediaFile ON MultimediaTable (MediaFile)
indexidxMediaURLMultimediaTable48CREATE INDEX idxMediaURL ON MultimediaTable (URL)
tableNameTableNameTable14CREATE 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 )
indexidxNameOwnerIDNameTable51CREATE INDEX idxNameOwnerID ON NameTable (OwnerID)
indexidxSurnameNameTable52CREATE INDEX idxSurname ON NameTable (Surname)
indexidxGivenNameTable53CREATE INDEX idxGiven ON NameTable (Given)
indexidxSurnameGivenNameTable54CREATE INDEX idxSurnameGiven ON NameTable (Surname, Given, BirthYear, DeathYear)
indexidxNamePrimaryNameTable56CREATE INDEX idxNamePrimary ON NameTable (IsPrimary)
tablePersonTablePersonTable3CREATE 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 )
tablePlaceTablePlaceTable16CREATE 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 )
indexidxPlaceNamePlaceTable57CREATE INDEX idxPlaceName ON PlaceTable (Name)
indexidxPlaceAbbrevPlaceTable58CREATE INDEX idxPlaceAbbrev ON PlaceTable (Abbrev)
tableResearchItemTableResearchItemTable31CREATE TABLE ResearchItemTable (ItemID INTEGER PRIMARY KEY, LogID INTEGER, Date TEXT, SortDate INTEGER, RefNumber TEXT, Repository TEXT, Goal TEXT, Source TEXT, Result TEXT )
indexidxResearchItemLogIDResearchItemTable72CREATE INDEX idxResearchItemLogID ON ResearchItemTable (LogID)
tableResearchTableResearchTable17CREATE 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 )
indexidxResearchOwnerIDResearchTable59CREATE INDEX idxResearchOwnerID ON ResearchTable (OwnerID)
indexidxResearchNameResearchTable60CREATE INDEX idxResearchName ON ResearchTable (Name)
tableRoleTableRoleTable25CREATE TABLE RoleTable (RoleID INTEGER PRIMARY KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence TEXT )
indexidxRoleEventTypeRoleTable69CREATE INDEX idxRoleEventType ON RoleTable (EventType)
tableSourceTableSourceTable19CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, Fields BLOB )
indexidxSourceNameSourceTable61CREATE INDEX idxSourceName ON SourceTable (Name)
tableSourceTemplateTableSourceTemplateTable29CREATE TABLE SourceTemplateTable (TemplateID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, Description TEXT, Favorite INTEGER, Category TEXT, Footnote TEXT, ShortFootnote TEXT, Bibliography TEXT, FieldDefs BLOB )
indexidxSourceTemplateNameSourceTemplateTable70CREATE INDEX idxSourceTemplateName ON SourceTemplateTable (Name)
tableURLTableURLTable32CREATE TABLE URLTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, OwnerID INTEGER, LinkType INTEGER, Name TEXT, URL TEXT, Note BLOB )
tableWitnessTableWitnessTable23CREATE 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 )
indexidxWitnessEventIDWitnessTable64CREATE INDEX idxWitnessEventID ON WitnessTable (EventID)
indexidxWitnessPersonIDWitnessTable65CREATE INDEX idxWitnessPersonID ON WitnessTable (PersonID)

Leave a Reply

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