Please join me in welcoming Kevin McLarnon ( @kmclarnon ) in the role of Editor on this site and, especially, for volunteering to lead a renewed initiative to improve the documentation we have on the design of the RootsMagic v9 #database. He is counting on users better-versed than himself in sqlite3 and #RM9 (#RM10) to collaborate, contribute to and comment on the #datadefinitions but brings to the process experience with Google Workspace teams, websites and pedagogy.
The locus of activity is the page RM10 Data Dictionary which links to the collaborative spreadsheet on which all are invited to comment. Some of you will be approached for special contributions and you can also contact him directly. You will hear more from Kevin about the project in blog posts and/or Forum messages.
Thanks Tom. True confessions, I had to look up “pedagogy”.
I started a new forum thread – https://sqlitetoolsforrootsmagic.com/forum/topic/new-rm9-data-dictionary-project/#postid-1075 – and will be monitoring all of the communication channels on this site for project feedback.
Thanks,
Kevin
Upon opening a database in RootsMagic (10), the database itself (the .rmtree file) is modified. My guess is that something in the ConfigTable gets touched. Anyone know what?
I’ve not tracked down what gets changed on opening but identified the behaviour back with RM4. One that does as you navigate is the choice of parental line you follow – that’s in another table.
If you do dog in and find it, please share!
I have found that by just opening and then immediately closing a database in RootsMagic that at least the ConfigTable is modified. I suspect that this is the only change made.
I dumped the ConfigTable before and after with:
$ sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Enter “.help” for usage hints.
Connected to a transient in-memory database.
Use “.open FILENAME” to reopen on a persistent database.
sqlite> .open “../ZebMoore_Ancestry.rmtree”
sqlite> .load “../sqlite/unifuzz”
sqlite> .headers on
sqlite> .mode csv
sqlite> .once “ConfigTable.after.csv”
sqlite> SELECT * FROM ConfigTable;
sqlite> .quit
Here’s a CSV dump of the first 50 characters of the ConfigTable:
$ cut -c-50 ConfigTable.after.csv
RecID,RecType,Title,DataRec,UTCModDate
1,1,””,1000070
2,5,rptMultimediaList,”<header
3,4,1,"White<
Only RecID 1 (RecType = 1) (line 2 of this file) is modified — all other records are the same as before:
$ diff -y –suppress-common-lines ConfigTable.before.csv ConfigTable.after.csv
1,1,"",10000 | 1,1,””,10000
After replacing all of the comma separators with newlines, here is the result of a diff:
$ diff ConfigTable.before.csv.txt ConfigTable.after.csv.txt
10c10
45836.0737079514
Only the UTCModDate is changed.
If this is the only change, then this is a bit annoying since merely opening the file in RM causes the database to change. A revision control system, like git, will always report the database file as modified even though the pertinent data has not changed.
Hmmm, WordPress interpreted some of the angle bracket characters as formatting tags resulting in data loss. Let’s see if this works:
$ diff ConfigTable.before.csv.txt ConfigTable.after.csv.txt
10c10
45836.0737079514
On a simple test file, there was no change in the UTCModDate of the only record in ConfigTable after closing and reopening the database. This was on RM 10.0.7.0 32-bit Win 10, inspecting with SQLiteSpy. My settings may have a bearing: no auto reopen of the last closed database, Start person=Root person, FamilySearch support disabled, WebHints disabled. Once you start navigating a tree or changing the view, there will be updates of that record even though the tree data is unchanged. I don’t know git and whether it could ignore a table.
The values stored under ConfigTable.DataRec column are undeclared, unstyled XML, binary encoded. I cast them to text and copy the unparsed, continuous string, paste into XML Notepad to get a nicely structured view. The app is free from the Windows store.
Thanks Tom, By disabling all webhints and several other options, I found that by only opening and closing the database in RootsMagic the database is not modified — good!
git is a widely used revision control and collaboration system used by software developers. It’s able to detect changes to files and declare them as modified. Unless the file is text-based, it isn’t able to distinguish which part of the database (which table) changed. I’m using this as my backup solution for the database file.
If you want to detect where changes occurred in your database with git, given that it reads only text, you might use the .dump method either for the whole database or for selected tables.
https://www.prisma.io/dataguide/sqlite/importing-and-exporting-data-in-sqlite
I thought of that too, but not necessary for my use case. I was just opening up the database in RM to see if I hadn’t messed it up with my external sqlite queries. That is was changing from just opening it was annoying, and at the time, unexplicable. All is good now.
But you bring up a good idea. Two databases could be compared in a human readable fashion by dumping both and doing a diff. I looked at RM’s compare function and it seemed to be a bit cumbersome.