Quote from Tom Holden on 2023-04-27, 11:29 pm

However, if I do an Integrity check with SQLiteSpy using NOCASE as the fake RMNOCASE, I get these and only these errors on a new empty RM9 database:

integrity_check
row 154 missing from index idxSourceTemplateName
row 320 missing from index idxSourceTemplateName

Note there is no error reported for the idxPlaceName index for the PlaceTable. And if the rows missing from idxSourceTemplateName are the rownumbers in the SourceTemplateTable, the Name are plain:

TemplateID	Name
154	Personal Knowledge
320	NA (U.S.) Manuscripts

Quote from Kevin McLarnon on 2023-04-30, 12:13 pm

Quote from Kevin McLarnon on 2023-04-23, 4:49 pm

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 went back and rechecked RM8 files and I misspoke before... RM8 also produces integrity test indexing errors when swapping db files between mac and windows versions (at least as of rm8.2 which is the oldest rm8 version that I have).

Quote from Tom Holden on 2023-05-01, 11:36 pm

Tried and failed to find a way to inspect the data in the index of a SQLite table to see if that might help understand the "missing rowid..." from the Integrity_Check. So I tried an indirect method that yielded even more surprising results. Using SQLite Expert Personal 64, I created a database with two Tables of identical columns:

rowid ID Name
54 54 [x]
55 55 [y]
56 56 [z]
57 57 [À]
58 58 [Á]
59 59 [Â]

Both are populated with identical content, 208 characters (one per row) from multiple character sets and both are indexed on the Name field, one with the SQLite NOCASE collation, the other with the fake RMNOCASE collation based on unifuzz64.dll. So one table was named [NOCASE] and the other [RMNOCASE].

I then JOINed the two tables, one JOIN in each direction using the [Name] field. One would expect the result set would be about double (2*208=416) if there was a lower case character for every upper case character and the collation handled the two cases as equal. But I got 538 one way and 330 the other!

Here's the query yielding 538:

SELECT NOCASE.ID NOCASEid,RMNOCASE.ID RMNOCASEid,NOCASE.Name NOCASE,RMNOCASE.Name RMNOCASE
FROM RMNOCASE
JOIN NOCASE
USING(Name)

Explain Query Plan shows:

SCAN NOCASE

SEARCH RMNOCASE USING COVERING INDEX idxRMNOCASEname (Name=?)

Flip the query direction around yields 330:

SELECT NOCASE.ID NOCASEid,RMNOCASE.ID RMNOCASEid,NOCASE.Name NOCASE,RMNOCASE.Name RMNOCASE
FROM NOCASE
JOIN RMNOCASE
USING(Name)

SCAN RMNOCASE

SEARCH NOCASE USING COVERING INDEX idxNOCASEname (Name=?)

I'm not sure how to explain the different results - it is significant that they are so widely different. COUNT()  with GROUP BY table.Name gives puzzling totals that bear some thinking about. A contributing factor is that some characters are repeated in the included character sets, thus one character on one side may have multiple lower and upper-case matches on the other.

I've attached the database in a zip file if you want to explore.

Quote from Tom Holden on 2023-05-07, 9:47 pm

I've not had time to explore much further but Kevin Benson gave me a reference to using an "imposter table" to mimic what is in a table's index which I will look into. Meanwhile, it hit me that I had not done the obvious thing and that was to compare how the NOCASE and fake RMNOCASE collations sorted the identical sequence of characters described above. That was very revealing. Here's the first bunch leading to the letter B which demonstrates that NOCASE is very much ANSI biased (not compatible with UNICODE) while the fake RMNOCASE is UNICODE compatible:

NOCASE		RMNOCASE
ID	Name	ID	Name
28	[^]	128	[­]
29	[_]	28	[^]
30	[`]	29	[_]
2	[A]	30	[`]
31	[a]	130	[¯]
3	[B]	135	[´]
32	[b]	139	[¸]
4	[C]	138	[ˇ]
33	[c]	208	[˙]
5	[D]	133	[˛]
34	[d]	131	[°]
6	[E]	2	[A]
35	[e]	31	[a]
7	[F]	58	[Á]
36	[f]	89	[á]
8	[G]	148	[Á]
37	[g]	179	[á]
9	[H]	57	[À]
38	[h]	88	[à]
10	[I]	59	[Â]
39	[i]	90	[â]
11	[J]	149	[Â]
40	[j]	180	[â]
12	[K]	61	[Ä]
41	[k]	92	[ä]
13	[L]	151	[Ä]
42	[l]	182	[ä]
14	[M]	147	[Ā]
43	[m]	178	[ā]
15	[N]	60	[Ã]
44	[n]	91	[ã]
16	[O]	150	[Ã]
45	[o]	181	[ã]
17	[P]	62	[Å]
46	[p]	93	[å]
18	[Q]	152	[Å]
47	[q]	183	[å]
19	[R]	119	[Ą]
48	[r]	132	[ą]
20	[S]	63	[Æ]
49	[s]	94	[æ]
21	[T]	153	[Æ]
50	[t]	184	[æ]
22	[U]	3	[B]

As stated previously, some characters are duplicated in two or more character sets that were added to the table.

Quote from Marilyn Clark on 2023-11-20, 4:26 pm

Quote from Tom Holden on 2023-04-23, 1:26 pm

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.

 

UPDATE from the user in question (me): Same problem occurred again: the zero OwnerID. I know diddly about SQL but have been researching enough to:

Find that my Mac (now running Sonoma 14.1.1, Apple M1; RootsMagic 9.1.0) has SQlite 3.39.5 already built-in. So I downloaded/installed SQLite Flow in order to run the recommended query: DELETE FROM NameTable WHERE OwnerID = 0;

I tried to run this on a renamed copy of my current problematic RM db, on which I'd already run the 4 RM tools. The RM db was not open when I ran the query. You can see the results in one of my attached screenshots.

So I have this RMNOCASE problem and IDK how to proceed. I saw somewhere about maybe running REINDEX (in SQL?) before running the DELETE query, then using the RM tool to reindex within RM before using the cleaned file, but IDK the correct SQL syntax for the first Reindex. And the comments about Fuzzy things is way over my head. I don't want to be a chore, but this simple fix is becoming waaaaay too complicated. Thanks for listening.

Marilyn