Creating Facts
Disclaimer: I have not explored the RM9 data dictionary yet....
Is it fairly straightforward to run a script to create Facts, including Roles and Template Sentences? I've got a bunch to do and the UI will take a long time.
It's hard to know without knowing more about your project. So the short answer for creating facts is yes and no.
The "yes" part is that simply adding facts can be fairly straight foward. It involves doing an INSERT into EventTable. That in turn means that your script needs to know the values that need to be inserted. These values will include EventType (like Birth or Death, except you need the ID number for the EventType, like Birth is 1 and Death is 2), OwnerID (the ID of the person for individual facts and the ID of the family for family facts), OwnerType (individual or family), etc. There are a lot of columns and I'm not going to go through all of them.
The "no" part is that it an be very difficult to calculate or create all the values that need to be inserted.
If you need any new fact types or new roles, then it's usually much easier to create those from the RM user interface than it is to create them from an SQLite script. That would include the template sentences for the facts and roles. The entries will be placed into FactTypeTable for facts and into RoleTable for roles.
For creating role records, the short answer for roles is again yes and no. This will involve INSERT's into WitnessTable. An INSERT into WitnessTable will require that the corresponding row in EventTable already be there. Again, the "yes" part is that it's pretty straight forward to do an INSERT into Witness Table. The "no" part again is that can be very difficult to calculate or create all the values that need to be inserted.
There are a number of scripts running around that involve facts and roles. I'm not sure I can find it on this site right now, but I created a script that involved adding a Parents fact to each person who has parents and in turn involved adding a Role to each newly added Parents fact to share the role with each of the Parents. It's surely different facts and roles than you need to add, but in general it has many of the same concepts. I suspect that one difference may be that all the data I needed was already in my RM database. I didn't need to pull in any external data.
(Added: I just found the script in my local files. I'll post it again right here to make it easier to find. It's quite a large script that creates temporary tables and temporary views as a part of prepping the data that needs to be inserted. It was written for RM6, but the tables involved are mostly the same in RM9. I think the big difference is that all RM9 tables have an UTCModDate column that wasn't there in RM6. A valid value would have to be loaded into that column. I'm pretty sure that there is an SQLite function to obtain the current date in UTC format that could be used to obtain the correct value for this column.)
Uploaded files:Quote from thejerrybryan on 2024-05-13, 8:59 pmI'm not sure I can find it on this site right now, but I created a script that involved adding a Parents fact to each person who has parents and in turn involved adding a Role to each newly added Parents fact to share the role with each of the Parents.
A search with just the term "parent" returned these two:
Thanks for the detailed response, and the scripts. However, I just realized I was not precise in my use case.
I want to programmatically create a number of Fact Templates, not Facts. I'm hoping this is a straightforward INSERT to a Fact Template table.
Just got a chance to peek at the schema, and it looks like I can do this with a simple INSERT into FactTypeTable.
Most of the fields are self-evident, but to double-check a few of them:
- FactTypeID. I assume I set this manually. Is there any numbering heuristic I should follow?
- OwnerType. Looks like 0 is appropriate, based on new Templates I created in the UI.
- GecomTag. "EVEN" looks appropriate.
- Flags. -1 looks appropriate.
- UTCModDate. I assume I set this manually.
Anything I've got wrong or am missing?
FactTypeID is an autoincrement field. You can't set it on the insert. Essentially you have to insert NULL as a place holder to make the columns line up properly on the insert. The NULL will turn into the next available ID. You can only find out after the fact what value was assigned.
OwnerType = 0 is for Individual facts. OwnerType = 1 is for family facts such as Marriage and Divorce.
GedcomTag = 'EVEN' essentially means this is a user defined fact.
Flags is a sequence of bit switches that control which Include options are turned on. It's a numeric fields so that -1 means all the bits are on (2's complement representation) which means that all the Include options are turned on.
- FactTypeID must be >999 for user-defined fact types; that's what sets it apart from the built-in fact types, not that the GEDCOM tag is EVEN.
- Flags: see Fact Inclusion Controls
- also see Fact Types
- UTCModDate: see Date Last Edited - UTCModDate
All above found using the site's search tool.
Thanks @thejerrybryan and @ve3meo, especially the note about site search. Should've tried that first.
But I'm hearing conflicting guidance on FactTypeID. Is it auto-increment, or do I manually set it to something > 999? I searched the site and couldn't find an answer to this question.
'"But I'm hearing conflicting guidance on FactTypeID. Is it auto-increment, or do I manually set it to something > 999? I searched the site and couldn't find an answer to this question."
That's a good question and I don't know and I will defer to Tom.
Most of RM's tables have autoincrement fields for the primary key. But for things like the FactTypeTable and the SourceTemplateTable, the built-in items have a key <= 999 and user defined items have a key > 999. Well, for SourceTemplateTable, the user defined items have a key > 9999. I don't know how this effect is achieved, except that after the first user defined item is given a key of 1000 or 10000, all subsequent user defined items would work just fine as autoincrement fields.
Because I don't know for sure how it works, I would probably work around the question by adding one dummy fact type from the RM user interface before I started doing inserts with SQLite. That would surely set the current autoincrement value to 1000 so that subsequent ones would be 1001, 1002, etc.
(And I also should have tried the search tool on Tom's site first.)
adding one dummy fact type from the RM user interface before I started doing inserts with SQLite. That would surely set the current autoincrement value to 1000
Since I've already created new Fact Templates in the UI, I've got entries in FactTypeTable with FactTypeID > 999, so autoincrement should perform correctly. Thx!