A different RMNOCASE in the Mac version?
Are there any of you exchanging databases between MacOS and Windows versions of RM9 noticing indexing errors in Database Tools => Test Integrity?
A RM9 user suffering from the rogue spouse name with a RIN of 0 (a problem that cropped up with much higher frequency in RM8 and continues with less frequency in RM9) sent me a backup to fix. What startled me was that it failed the RM9 Test Integrity tool with a list of indexing errors somewhat similar to what one sees after REINDEXing with SQLite using a fake RMNOCASE collation in SQLiteSpy. I got her to send me another backup of the database that I had fixed for the original problem and had sent to her with Test Integrity passed. She had done some work on it and ran the RM9 database tools before doing the backup. What I restored failed Test Integrity the same as the first!
- she uses an iMac running Ventura
- she regularly runs the database tools
- I'm using Windows 10
- we are both on RM 9.0.2
The only explanation that I can think of that would cause this discrepancy in the Test Integrity results is that the Mac version of RM9 does not use the same RMNOCASE collation sequence as the Windows version. Maybe the original RMNOCASE is built on Windows functions that cannot be matched in MacOS and necessitated a MacOS variant, just as we who use SQLite on our RM databases have to substitute a fake RMNOCASE.
I've swapped RM8 databases between mac and windows and haven't experienced this. If you email me the database I can test it on mac rm9. Perhaps there's a simpler explanation.
I would have to get her permission to share her file with you. Maybe the better approach would be to get her to create a new test file on her iMac/Ventura. Then I can verify that it fails my Windows RM test and propagate from that point.
Hi. I was able to reproduce the issue. It took a bit to update my windows system and add RM9 but a RM9.0.2 database file with no integrity errors on mac Ventura had lots of integrity errors when tested with 64-bit RM9.0.2 running on windows10. I had not moved a RM9 db file between OSes until just now.
The integrity test results looked very similar to what happens after using sqlitespy. I forgot to pull the file back to check on mac now that it has been indexed under windows; am assuming it will show a similar pattern. Will check that tomorrow.
That pretty well settles it, I should think. Is there anything other than the collation sequence that could cause that behaviour? I'm interested in what you see going from Windows to Mac.
Quote from Tom Holden on 2023-04-23, 11:08 pmIs there anything other than the collation sequence that could cause that behaviour?
If the sqlite database engine in the Mac version interpreted identical RMNOCASE collation sequences differently from the Windows version, that would be an equivalent to the collation sequences themselves being different. Is that even possible?
Quote from Tom Holden on 2023-04-23, 11:08 pmThat pretty well settles it, I should think. Is there anything other than the collation sequence that could cause that behaviour? I'm interested in what you see going from Windows to Mac.
Confirmed that a RM9.0.2 db file with no integrity issues on Win 64 fails integrity check on Mac Ventura. Re-indexing resolves the issue. The offending index rows were similar but not identical.
The only other difference that I can think of is that, currently, there are several issues with the dev tools that affect only Mac users. That's definitely in the swag category.
I had to look up "swag category"!
Sophisticated or Scientific Wild-Ass Guess(timate)
Very interesting thread. This custom collation sequence is very concerning.
Would you agree that in order to show a failed integrity check due to incompatible implementations of the collation function that the RMNOCASE collated columns would need to have some characters outside the ASCII range?
I presume all implementations of RMNOCASE: Win, Mac fake, would agree that 'C' > ' B' > 'A'
I would think that the problems would manifest with the collation function determining whether 'Ö' > 'ö' etc.
I do agree but I would have thought a brand new RM database would have no non-ASCII characters in it yet when integrity is tested in SQLite using either of the fake RMNOCASE collations (unifuzz.dll or aliased NOCASE) , there are missing rows in indexes reported. That I don't understand nor what the row number is referring to. Maybe those errors have no bearing on the sort order of characters within the ASCII range.