Check+RootsMagic+Database+Integrity


 * This page applies mainly to RootsMagic 4. RM5 incorporates the SQLite tools Integrity Check, Reindex, and Vacuum under the menu File > Database Tools > Test database integrity | Rebuild indexes | Compact database. However, it is not apparent that the Integrity Check is done automatically on start-up so users would be well advised to do it periodically. Now that there is an integral Integrity Check, we have learned that some modifications to the database using a SQLite manager having a substitute RMNOCASE collation results in index errors. Therefore, users of SQLite queries are warned: **
 * 1) ** If using RM4, avoid queries that modify fields collated with RMNOCASE. **
 * 2) ** If using RM5, reindex with the SQLIte manager before running the query; then reindex using RM5 before using it to make other changes to the database or major reports. **

Why?
As of RootsMagic 4.0.8.4, there is no built-in mechanism to check the integrity of a RootsMagic database. Some users have encountered SQLite error messages while using RootsMagic, the most terrifying of which is //SQLite Error 11 - Database disk image is malformed.// It can render the database unusable, or, at best, only parts of it usable. The database has been corrupted in some way and may be undetected for months until some procedure causes the SQLite database engine to attempt to access the corrupted area. Ever since the corruption enters, RootsMagic backups faithfully preserve it. For a real example, read the discussion on the RootsMagic forum in the topic [|Corrupt Database, Backups & Recovery Therefrom]. Had there been a convenient check of the integrity of the database used routinely, the corruption would have been caught much earlier. Corrective action could have been taken sooner and perhaps the cause identified, given a fresh memory of the events preceding. There is no reason why such an integrity check cannot be incorporated in the RootsMagic application on the opening of a database; that's where it should be done. Until the software is upgraded to do so, there are tools that can be used outside RootsMagic 4 to validate the integrity of its SQLite databases.

How?
Jump right in by reading How to query RM4 which just happens to use one of the following SQL commands to carry out an integrity check. Make an integrity check an integral part of opening your database by following the outline in Check RootsMagic Database Integrity on Opening.

The SQLite database engine incorporates two SQL commands that carry out a more or less comprehensive integrity check. From the on-line [|manual]: > **PRAGMA integrity_check(**//integer//**)** This pragma does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems. At most //integer// errors will be reported before the analysis quits. The default value for //integer// is 100. If no errors are found, a single row with the value "ok" is returned.
 * **PRAGMA integrity_check;**

> **PRAGMA quick_check(**//integer//**)** The pragma is like [|integrity_check] except that it does not verify that index content matches table content. By skipping the verification of index content, quick_check is able to run much faster than integrity_check. Otherwise the two pragmas are the same.
 * **PRAGMA quick_check;**

These SQL command lines can be run by any SQLite manager against a RootsMagic database opened by the manager with one big exception: indices can only be checked if SQLite can access a collation sequence named RMNOCASE, a name and collation unique to RootsMagic and embedded in the application. Thus, PRAGMA integrity_check will fail unless the SQLite manager has the means to add a collation sequence so named. SharpPlus SQLite Developer (not the free version) is one that can. Of course, RootsMagic should be capable of doing this full test of integrity as it has the RMNOCASE collation.

That leaves the partial test, PRAGMA quick-check, as the one that we can use on RootsMagic database files with any SQLite manager. And this may suffice, because even an 'empty' RootsMagic database is reported to have 13 missing rowid's from index idxSourceTemplateName - 'OK' would never be an output from integrity_check.

On tree page 4494 cell 112: 2nd reference to page 16050 On tree page 4494 cell 112: Child page depth differs On tree page 4494 cell 113: Child page depth differs On tree page 16048 cell 0: Rowid 2285 out of order (max larger than parent max of 800) On tree page 11057 cell 3: Rowid 801 out of order (min less than parent min of 2285) On tree page 10892 cell 0: 2nd reference to page 16050 On tree page 11617 cell 67: 2nd reference to page 16051 On tree page 11617 cell 67: Child page depth differs On tree page 11617 cell 68: Child page depth differs On tree page 11548 cell 0: 2nd reference to page 16045 On tree page 15783 cell 1: 2nd reference to page 16045 On tree page 12446 cell 53: 2nd reference to page 16048 On tree page 12446 cell 53: Child page depth differs On tree page 12446 cell 54: Child page depth differs Page 11154 is never used Page 11155 is never used Page 15429 is never used Page 15430 is never used Page 16054 is never used Page 16055 is never used Page 16056 is never used Page 16057 is never used rowid 55 missing from index idxSourceTemplateName rowid 172 missing from index idxSourceTemplateName rowid 190 missing from index idxSourceTemplateName rowid 241 missing from index idxSourceTemplateName rowid 296 missing from index idxSourceTemplateName rowid 297 missing from index idxSourceTemplateName rowid 326 missing from index idxSourceTemplateName rowid 333 missing from index idxSourceTemplateName rowid 345 missing from index idxSourceTemplateName rowid 401 missing from index idxSourceTemplateName rowid 21 missing from index idxSourceName rowid 61 missing from index idxSourceName rowid 63 missing from index idxSourceName ... cont'd for total of 1026 'rowid ## missing...' || On tree page 10429 cell 0: 2nd reference to page 16050 On tree page 4494 cell 112: 2nd reference to page 16050 On tree page 4494 cell 112: Child page depth differs On tree page 4494 cell 113: Child page depth differs On tree page 16048 cell 0: Rowid 2285 out of order (max larger than parent max of 800) On tree page 11057 cell 3: Rowid 801 out of order (min less than parent min of 2285) On tree page 10892 cell 0: 2nd reference to page 16050 On tree page 11617 cell 67: 2nd reference to page 16051 On tree page 11617 cell 67: Child page depth differs On tree page 11617 cell 68: Child page depth differs On tree page 11548 cell 0: 2nd reference to page 16045 On tree page 15783 cell 1: 2nd reference to page 16045 On tree page 12446 cell 53: 2nd reference to page 16048 On tree page 12446 cell 53: Child page depth differs On tree page 12446 cell 54: Child page depth differs Page 11154 is never used Page 11155 is never used Page 15429 is never used Page 15430 is never used Page 16054 is never used Page 16055 is never used Page 16056 is never used Page 16057 is never used ||
 * < ===Comparison of outputs for a corrupted RootsMagic database=== ||
 * **PRAGMA integrity_check;** || **PRAGMA quick_check;** ||
 * On tree page 10429 cell 0: 2nd reference to page 16050