Scripts for Comparing RM7 and RM9 Databases

#1 · 2023-05-29, 9:59 am

Quote from thejerrybryan on 2023-05-29, 9:59 am
Here is version 1.0 of my new scripts to compare RM7 and RM9 databases, table by table and row by row. The scripts assume you have attached an RM7 database "AS RM7" and an RM9 database "AS RM9". There is a sample script called attach_rm7_rm9.sql that can be customized to your particular file names for this purpose. There is no File=>Open involved in running these scripts, just the ATTACH's. The ATTACH's are not embedded in the scripts which perform the comparisons.
There is a separate script for each table of interest. I tried to think of a way to make a single script for all the tables, but I was not able to think of a way. One reason is that in the event of a mismatch, I wanted to display the differences between RM7 and RM9, and each table has different columns.
The scripts ignore RM9 columns that don't exist in RM7, and they do things like convert columns with the same function and different names to using the same names.
The script for each table follows the same model. There is a query for the RM7 version of a table UNION'd with a query for the RM9 version of the same table. Each query adds a column called Version which has the value 7 for RM7 and the value 9 for RM9. In the case of a perfect match for the tables, each RM7 row will have a perfect match with an RM9 row except for the Version column. The two queries which are UNION'd together then become a subquery for a larger query. In the larger query, a GROUP BY is performed on all the columns except for the Version column with a COUNT function performed on the GROUP BY. A COUNT of 2 indicates a perfect match and a COUNT other than 2 indicates a mismatch of some sort.
The project practically begs for some C++ code or some Python code or some such to create a user interface, but I have always found it extremely difficult to create a Windows user interface. So for now, the script for each table just has to be run one table at a time. I don't have a script for every single table, just for the tables of most interest. The script for MultiMedia files will require manual tinkering to include your media folder locations to map RM9's relative folders in MultiMediaTable.MediaPath to the absolute folder locations where your media files are really located.
A perfect match for every row in a table results in No Results. For any mismatch, the mismatched RM7 row and RM9 row are displayed. You have to determine visually where the mismatch has occurred. Again, this begs for a good user interface.
I'm quite confident that Version 1.0 of these scripts is not the last word on these scripts and that additional tweaking will be required moving forward.
I have zipped the files so that there will be only one file to download.

Here is version 1.0 of my new scripts to compare RM7 and RM9 databases, table by table and row by row. The scripts assume you have attached an RM7 database "AS RM7" and an RM9 database "AS RM9". There is a sample script called attach_rm7_rm9.sql that can be customized to your particular file names for this purpose. There is no File=>Open involved in running these scripts, just the ATTACH's. The ATTACH's are not embedded in the scripts which perform the comparisons.

There is a separate script for each table of interest. I tried to think of a way to make a single script for all the tables, but I was not able to think of a way. One reason is that in the event of a mismatch, I wanted to display the differences between RM7 and RM9, and each table has different columns.

The scripts ignore RM9 columns that don't exist in RM7, and they do things like convert columns with the same function and different names to using the same names.

The script for each table follows the same model. There is a query for the RM7 version of a table UNION'd with a query for the RM9 version of the same table. Each query adds a column called Version which has the value 7 for RM7 and the value 9 for RM9. In the case of a perfect match for the tables, each RM7 row will have a perfect match with an RM9 row except for the Version column. The two queries which are UNION'd together then become a subquery for a larger query. In the larger query, a GROUP BY is performed on all the columns except for the Version column with a COUNT function performed on the GROUP BY. A COUNT of 2 indicates a perfect match and a COUNT other than 2 indicates a mismatch of some sort.

The project practically begs for some C++ code or some Python code or some such to create a user interface, but I have always found it extremely difficult to create a Windows user interface. So for now, the script for each table just has to be run one table at a time. I don't have a script for every single table, just for the tables of most interest. The script for MultiMedia files will require manual tinkering to include your media folder locations to map RM9's relative folders in MultiMediaTable.MediaPath to the absolute folder locations where your media files are really located.

A perfect match for every row in a table results in No Results. For any mismatch, the mismatched RM7 row and RM9 row are displayed. You have to determine visually where the mismatch has occurred. Again, this begs for a good user interface.

I'm quite confident that Version 1.0 of these scripts is not the last word on these scripts and that additional tweaking will be required moving forward.

I have zipped the files so that there will be only one file to download.

Uploaded files:

comp_rm7_rm9_folder.zip