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.

Jerry

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?

Discussions & comments from Wikispaces site