Contents
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:
- If using RM4, avoid queries that modify fields collated with RMNOCASE.
- 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 RootsMagic 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;
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 quick_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.
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.
ve3meo
20 June 2011 18:08:30
VACUUM could repair corruption of an index, as would REINDEX. Vacuum also would return file space to the OS for other uses if a database has undergone many changes and deletions.
Unfortunately, VACUUM and REINDEX cannot be done without SQLite having access to all collation sequences used by the db indexes. What’s missing from all SQLite managers is the RMNOCASE collation sequence, proprietary and secret to RM4. I have successfully done both using SQLite Developer and renaming one of the Unicode collations but I don’t know if that may result in some downstream risk.
We need to keep urging Bruce and RootsMagic to incorporate VACUUM and REINDEX as user options. If they would provide a SQLite user interface within the program, those PRAGMA commands should automatically become accessible.