Logo Home    Page list

Comparing Two RM Databases #compare #database #rmnocase

I wasn't sure whether to post this page on the RootsMagic 4 wiki or the RootsMagic 5 wiki, but it really applies to both.

I've been considering the problem of data loss when using the RM Drag and Drop facility. Behind the scenes, Drag and Drop consists of a GEDCOM export from one RM database followed immediately by a GEDCOM import into another RM database. So the problem of the data loss really reduces down to identifying and fixing the causes of data loss in GEDCOM export and identifying and fixing the causes of data loss in GEDCOM import. I'm not sure that users such as us can do very much about fixing the causes, but I think we might be able to help with identifying the causes. And a constant question that's never been answered to my satisfaction is, just what data might be lost in a Drag and Drop operation? What are all the actual possible causes, not just what are some of the rumored possible causes?

My idea is as follows. What if I could Drag and Drop my entire database into a new, empty database, and then run some kind of comparison utility that would compare my original database to the copy, and to do so at the SQL table and row level? Even if the Drag and Drop were perfect, I could imagine some trivial differences. But since Drag and Drop is not perfect, I would hope that those trivial differences that couldn't be prevented wouldn't obscure the real differences that would represent defects in the Drag and Drop process.

My initial concept was to try to write SQL that would accomplish the required comparison, but a little searching of the Internet revealed that there are already utilities available for download that seem to be more than capable of doing the required download. The utility that I settled on for initial testing may be found at
http://www.codeproject.com/KB/database/SQLiteCompareUtility.aspx and is called simply SQLite Compare. It seems pretty slick. It will run comparisons, identify which tables are different between the original and the copy, and then will allow you to drill down to see the differences. It allows you to include or not include BLOB fields in the comparison.

To that end and as a preliminary test, I made a copy of my database with the RM Copy function and ran SQLite Compare. Because this was a database level copy rather than a Drag and Drop, I was expecting no differences. But there were a few tables with differences, namely the Address Table, the Media Link Table, the Research Table, the Source Table, and the Source Template Table. All the rest of the tables compared just fine. Being a bit puzzled as to why any of the tables should have had any differences, I tried using the SQLite Compare capability to drill down to see the differences. I shouldn't have been surprised by the results, but I was just a little bit surprised (shame on me). My attempt to drill down failed with the following dreaded error message.

ERROR: data comparison failed (SQLite error no such collation sequence: RMNOCASE)

So I'm wondering if anybody can think of a way to get around this problem? For the purposes of what I'm trying to accomplish, I really don't need RMNOCASE nor just plain NOCASE nor any other kind of case processing. Is there anyway to turn off RMNOCASE for a whole database (a copy of a copy sort of thing, of course - not my real database) in order to be able to run SQLite Compare. The truth is that even if I tried to write my own compare utility, I would have to fight with the RMNOCASE problem all the way through.


Added later in the day on 12/6/2011: I'm totally new to the SQLite Compare utility. Upon further review and upon playing with it further, I think I slightly misinterpreted the initial results. It was not saying that there were differences in the Address Table, the Media Link Table, etc. after the copy. Rather, it was saying that there were what it calls "comparison errors" after the copy. And when I drilled down to see what was going on, the RMNOCASE error messages provided the details of the "comparison errors". So I'm going to have to think about this a little more and experiment a little more. Given that the tables listed above had RMNOCASE error messages, why didn't other tables that use RMNOCASE collation also have RMNOCASE error messages rather than comparing as equal?




ve3meo 07 December 2011 01:08:51

Jerry, what I have done successfully in the past without obvious damage was to rename a UTF8 collation in the paid license version of SQLite Developer to RMNOCASE. The good news with RM5 is the REINDEX utility so that even if the faux collation results in something unwanted, the indexes can be rebuilt with the right collation.



Try a NOCASE Copy of the Database

ve3meo 07 December 2011 03:26:29

This idea works in principle.

1. Get the full DDL of your RM4 (sub RM5 if that's what you are working on) into a text editor.
2. Global replace RMNOCASE with NOCASE and save as a SQL file named, say, RM4_Create_as_DB3_NOCASE.sql
3. Create new empty DB3 database; load and run the modified DDL to create a NOCASE mirror of the RM4 structure
4. With your DB3 open, execute this:
ATTACH DATABASE 'full URI to the RM4 file' AS RM4;
5. Then run the following statement for each table (NameTable is the example):
INSERT INTO NameTable SELECT * FROM [RM4].NameTable;
6. Now you have all the data from your RM4 database in a DB3 with standard collations.
7. Do the same thing for each control and test database
8. Now run SQLite_Compare against the control DB3 and test DB3. It should work without tripping over the RMNOCASE collation.


ve3meo 07 December 2011 04:07:41

File Not Found
File Not Found
has step 5 expanded to all RM4 tables. According to romer, there is one additional table to be covered in RM5.


Thanks for the RMNOCASE Info

thejerrybryan 07 December 2011 04:22:19

I think I'll try Tom's free solution, but it may be several days before I have time to try it.

I'm actually on 5 now, and I'm aware of the extra table. I agree that moving forward most posts on the Wiki should be posted to 5 rather than 4 unless there is an issue specific to 4.



RM Drag and Drop

mfseeker 25 January 2012 15:03:28


Have you made any further progress with your original project of comparing RM databases to evaluate the accuracy of RM's GEDCOM out to GEDCOM in cycles?

thejerrybryan 26 January 2012 04:21:54

I put the project on hold when I realized that the SQLite Compare Utility wouldn't be able to perform meaningful compares after a drag and drop or export/import. The problem is that most table rows will be "renumbered" after a drag and drop or export/import.


I do have another idea how to approach the project, but I haven't had time to try it out.



Comment: "...a way to get around this problem?" (no such collation sequence: RMNOCASE)

ve3meo 03 September 2018 20:10:25

ve3meo Dec 6, 2011

See my discussion notes:
a) $ for a SQLite manager that can fake a RMNOCASE
b) free solution copying the data for both the control and the test RM databases to DB3 files with same structure but NOCASE instead of RMNOCASE and comparing the DB3's.


Comment: "SQLite Compare"

ve3meo 06 September 2018 18:26:45

ve3meo Dec 6, 2011

Very interesting find!