Corrupt Database Recovery #database

Posted on 2012-03-112019-01-26 by Tom Holden

Risk

Despite its reputation as a robust database system, SQLite is nevertheless vulnerable to disk degradation and disruptions to its disk write operations by power glitches, system crashes, etc. Thus there have been reports of the dreaded error message: SQLite Error 11 – database disk image is malformed. Other error messages may arise from corruption but this is the most common one. See the full list of SQLite result codes.

Backups may not help

RootsMagic does not exploit SQLite’s journal and rollback capabilities that could recover from some failures. Corruption may occur without an error message arising for some time because it occurred in an area of the database not currently in use. Moreover, the RootsMagic backup process won’t detect it and will faithfully preserve the corruption. Hence, when this error pops up, the question naturally follows “What do I do now?”. Winding through the backups may prove very frustrating.

Some recovery techniques

Here are some techniques I have used to recover all or some of a database, more or less in ascending order of the severity of the problem and the complexity of the recovery:

Drag and drop (selecting Everyone) to a new database. This can work if the corruption affects only a part of the database that is used for a certain kind of report or display, for example an index for a certain table.
Alternatively, export Everyone to GEDCOM and import that to a new database; that is equivalent to (1) in terms of the data transferred but we do not know if it is in any way a different SQLite call. If (1) fails and this export fails, then you can exclude many kinds of data for simpler export using fewer aspects of the database. Start with the most basic GEDCOM export; if it fails, skip to (3). Otherwise, progressively add elements to export until it fails – you may be lucky and have exported everything you want. Remember that there are GEDCOM settings in the FactType List that affect Drag’n’Drop the same as GEDCOM; you’ll need to check all FactTypes for inclusion in GEDCOM so you don’t lose data that is perfectly recoverable. Set the database you have created from DnD or from GEDCOM aside for later comparison with the results of the next steps.
Even if the DnD/Export succeeds, and certainly if it doesn’t, we have to resort to SQLite to be sure to recover the most complete set of data . Create a new, empty RootsMagic database file and open it with SQLiteSpy with the RMNOCASE extension, or equivalent. Then ATTACH the corrupted database. For example: “ATTACH ‘C:UserTomFamily TreeMyFamily.rmgc’ AS Old ;”. Open each table of the corrupted database one at a time and note which ones cause an error message.
Arguably, this step should follow the next – if it were to show that a critical table could not be sufficiently recovered, there is no point going further. For each table that does NOT cause an error message, copy its records into the corresponding table of the new, main database using the command (e.g. for the NameTable): “INSERT INTO NameTable SELECT * FROM Old.NameTable ;”. Revise and repeat for each table except for some which have built-in records such as the FactTypeTable, PlaceTable, RoleTable and SourceTemplateTable; the latter will require a command which includes a WHERE constraint or change to “INSERT OR IGNORE INTO …” to skip over the existing ones. Perhaps “INSERT OR REPLACE INTO…” is better as that will capture any customisations of FactTypes and Roles. The new database now has a perfect replica of the old, uncorrupted tables.
For each table that DOES cause an error message, only partial recovery is possible: from the first record to the last contiguously uncorrupted record, and, possibly, by further iterations, some records beyond the corruption. Open a corrupted table and ignore/close the error message. Scroll down the results to where corruption is encountered. Select and copy to clipboard all above that point. Paste the copied records into a text editor which supports regular expression search & replace, such as PSPad, or into a spreadsheet such as Excel. For very large results it may be necessary to do this in chunks to respect the limits of the text editing or spreadsheet program. Using the editing or spreadsheet tools, we need to transform this extract from the NameTable:

NameID OwnerID Surname Given Prefix Suffix Nickname NameType Date SortDate IsPrimary IsPrivate Proof EditDate Sentence Note BirthYear DeathYear
...
17 16 Hansen Frances Lorraine 0 . 9223372036854775807 1 0 0 0.0 1937 1939
18 17 Gibson Chester Herbert 0 . 9223372036854775807 1 0 0 0.0 1895 1949
19 18 Straub Florence Amelia 0 . 9223372036854775807 1 0 0 0.0 1896 1942
...

into this SQLite script:

BEGIN TRANSACTION;
...
INSERT INTO NameTable VALUES(17,16,'Hansen','Frances Lorraine','','','',0,'.',9223372036854770000,1,0,0,0,NULL,NULL,1937,1939);
INSERT INTO NameTable VALUES(18,17,'Gibson','Chester Herbert','','','',0,'.',9223372036854770000,1,0,0,0,NULL,NULL,1895,1949);
INSERT INTO NameTable VALUES(19,18,'Straub','Florence Amelia','','','',0,'.',9223372036854770000,1,0,0,0,NULL,NULL,1896,1942);
...
COMMIT TRANSACTION;

By examining the CREATE statement for the table, we can tell which fields are numeric (no quotes required), text (single quotes required) and binary or BLOB (requires CAST). In the above example, the Sentence and Note fields are binary text but there were no values so null could be used and was easier. Here’s an extract from another table which did have values in some BLOB fields:

PersonID    UniqueID    Sex    EditDate    ParentID    SpouseID    Color    Relate1    Relate2    Flags    Living    IsPrivate    Proof    Bookmark    Note
...
1165    979205DC09A94799B253CDBE51D90C137599    1    40853.0    0    0    0    0    0    0    0    0    0    0
1166    F79C9B38AFE44F619D4809724B3BAB134DFB    0    40853.0    0    0    0    0    0    0    0    0    0    0    Church records have Thomas living in 1850.
Could there have been 2 Thomas's in this family?
Bap. 26 feb 1971 SL. end. 1 apr 1971 Sl.Children's work done from 1980 census.
 
1167    E55E52812ECA44E8874FD0DB88750F4D14F3    0    40853.0    0    0    0    0    0    0    0    0    0    0
 
...

These need to be transformed into this SQLite script:

BEGIN TRANSACTION;
...
INSERT INTO PersonTable VALUES(1165,'979205DC09A94799B253CDBE51D90C137599',1,40853.0,0,0,0,0,0,0,0,0,0,0,CAST('' AS BLOB));
INSERT INTO PersonTable VALUES(1166,'F79C9B38AFE44F619D4809724B3BAB134DFB',0,40853.0,0,0,0,0,0,0,0,0,0,0,CAST('Church records have Thomas living in 1850.
Could there have been 2 Thomas''s in this family?
Bap. 26 feb 1971 SL. end. 1 apr 1971 Sl.Children''s work done from 1980 census.
 
' AS BLOB));
INSERT INTO PersonTable VALUES(1167,'E55E52812ECA44E8874FD0DB88750F4D14F3',0,40853.0,0,0,0,0,0,0,0,0,0,0,CAST('' AS BLOB));
...
COMMIT TRANSACTION;

Converting data to SQlite script

This is not a tutorial on regular expression search & replace. You can find those elsewhere. Whatever regular expression tool you use, there is a general order that needs to be followed:

Because the apostrophe demarks a string in SQLite syntax, possessive words such as “John’s” or other literal uses of the apostrophe requires it to be escaped (double apostrophes) before you wrap strings with single apostrophes. So the first step is a normal search for ‘ and replace with ” (that is two apostrophes, not a double quote).
The data values are separated by the Tab character. I tend to proceed then with replacements from left to right, using the Tab character to define the pattern and how it gets replaced at each position. You can find the type of each column by inspecting the SQLite command that creates the table; with SQLiteSpy, right-click on the table name in the left sidebar and select “Show CREATE statement”. It may take several iterations of search and replace for a table with many columns to arrive at a complete and syntactically correct INSERT statement from each row of the original table.
A multi-line field (with embedded new line codes as in the above example) is not manageable with PSPad’s search & replace so requires manual editing. There are other tools which can handle it – my favourite being Bill Klein’s ReplaceText. Regrettably, it is no longer supported and reportedly has some problems with certain Windows 7 installations, although I have not witnessed any.
Any remaining tab characters can be replaced globally with commas.

Finishing

Having used a fake RMNOCASE collation to build the data in the new database, the indexes may not jibe with RootsMagic’s inherent RMNOCASE. Open the new database with RootsMagic 5 (yes 5, not 4) and use its File > Database Tools > Rebuild Indexes tool. Then look at what you got from the DnD/Export versus the SQLite copying and choose to carry on with the one that seems most complete.

Discussions & comments from Wikispaces site

Drag ‘n’ Drop vs Export “everything”

ve3meo
10 March 2012 19:40:35

Added by Jerry Bryan 3/10/2012

(Tom, I’m sometimes still figuring out how to work the Wiki. I was just wanting in some sense to REPLY to this page and not really edit it, but I couldn’t figure out a way to do so. Please free to delete my comments from here and move them somewhere else.)

This page is a wonderful cautionary tale. It made me think that maybe once a week or so I should just drag and drop my entire database somewhere else, just to see what happens. I wouldn’t really use the copy, but maybe the drag and drop of the entire database might expose invisible database corruption that might otherwise remain invisible. Is that what you are saying? If so, then I would have one very minor quibble. Namely, in item (2) under recovery techniques, you say: “If you check off to include everything possible in the export, it is equivalent to (1).”

Item (2) is the export and item (1) is the drag and drop. The way I read the sentence I’m quoting, there is an expectation that drag and drop will include everything and that export will include everything only if you check to include everything possible. But my understanding is that drag and drop is fully equivalent to export, and to do a proper drag and drop of “everything” you would have to “check off to include everything possible” just like you would if you were doing an export of “everything”.

You can post messages “behind” the page. You can also Edit the page, select a key phrase and add a comment linked to the phrase. The phrase will be highlighted in yellow and the comments sidebar can be opened and closed to view the comment. The comment will also appear as a message “behind” the page.

I realise now after Geranium’s msg that Exporting Everyone with all the inclusions checked and exclusions unchecked is not as complete as Drag’n’Drop Everyone because some Fact settings exclude elements from export but not from DnD. To be truly equal, you would also have to go through the FactType List and include everything in it for export.

Tom

thejerrybryan

thejerrybryan
11 March 2012 01:14:29

I realise now after Geranium’s msg that
Exporting Everyone with all the inclusions
checked and exclusions unchecked is not
as complete as Drag’n’Drop Everyone
because some Fact settings exclude
elements from export but not from DnD.

My experience is that DnD honors Fact settings and therefore causes certain Facts to be omitted, just like Export. I don’t think there’s any way to make DnD get everything without messing around with lots of Fact settings. Am I missing something obvious?

Jerry

Place Errors #placedetails #places

Posted on 2012-03-072022-04-28 by Tom Holden

This page is an outgrowth from Place Details without a Place. There is the possibility to add Orphaned Place Details since at least RM4 because the program does not fully preclude it. A suspected bug or procedural cause in the Split Place function of RM 5.0.2.1 also may be contributing errors resulting in the loss of a Place. The queries developed on that page do not cover all the cases in one easy-to-use report. That is the goal of this page.

Given the structure of the RootsMagic database, one can identify three kinds of errors which result in the loss of a Place or Place Detail for an event:

Lost Places – the PlaceID pointer for the event cannot be matched in the PlaceTable.
Lost Place Detail – the SiteID pointer for the event cannot be matched in the PlaceTable
Orphaned Place Detail for which there are two sub-types:
1. Used by an event: the SiteID pointer is matched in the PlaceTable but that PlaceDetail’s MasterID (its parent’s PlaceID) is not (the parent Place is lost).
2. Unused: a Place Detail in the PlaceTable, whose MasterID (its parent’s PlaceID) is not, that is not used by an event.

The prior page and queries initially focussed on #3. This more comprehensive query reports all three types.

PlaceErrors3.sql This query requires a SQLite manager that supports a fake RMNOCASE collation else must be edited to override RMNOCASE with COLLATE NOCASE clauses in a few places. Alternatively, it will be available in RMtrix.

Screenshot of results from PlaceErrors3 query using SQLiteSpy with the fake RMNOCASE extension.

The Person, RIN, Year, Fact and Place Detail columns should prove helpful in finding the affected events using RootsMagic Explorer. In all cases, the Place will be blank in the Edit Source screen. The Place Detail, if not blank, may provide a clue to what Place it should be. RootsMagic’s Place List will not show any of these Place Details.

For those wishing to examine the database tables, the four numeric fields will help: the first two fields are the PlaceID and SiteID fields of the EventTable table; the last two are the PlaceID and MasterID fields of the PlaceTable table for the Place Detail corresponding to the SiteID of EventTable.

Use PlaceErrors3 to find these errors and take corrective action before entering into more place-splitting. The Unused Place Detail w/o Place error is easily removed using the OrphanedPlaceDetails_DeleteUnused.sql query described earlier on Place Details without a Place. Once having cleared all the problems, then run the query after each Place has been split into Place and Place Detail to confirm no new errors have been introduced.

SplitTree #splittreeancestorsleaves #application #visualc

Posted on 2012-03-042022-04-29 by Tom Holden

The RMSplit C# program below allows you to create a group in RootsMagic5 which contains a selected person, their ancestors, and a selected number of “leaves” (collateral lines us a given depth). If you choose one leaf, you will get children of everyone in the direct line (and their spouses if that is checked). If you choose two leaves, you will also get the children and parents of those in the first leaf, and so forth. Once the group is created in RootsMagic 5, you can then use the group to create reports for that group or export just the people in that group to a GEDCOM file. There are two files below. RMSplitBinary.zip has the files you need to install the program on your computer. Unzip the file to a directory on your computer and then run SETUP.EXE from that directory. There is internal documentation in the program. RMSplitSource.zip has the C# source code necessary to build your own version. I developed the code using Microsoft Visual C# 2010 Express and System.Data.SQLite from http://system.data.sqlite.org/.

The usual caveats apply to using this program:

I am the only one who has tested this so far. Please try it on a backup copy of your database to make sure it works for you.
You are welcome to modify and use the source code in your own projects, but please give me credit for the part I wrote.
This is not supported by RootsMagic and has only been tested on the current version of RootsMagic 5 as of 3 Jan 2012. (caveat emptor)

(The previous binary didn’t work for many users. Please leave a message and let me know whether this one works for you. There are two binary files to choose from. Use RmSplitbinary64.zip if you are running a ‍‍64-bit version of Windows‍‍ and RmSplitBinary32 if you are running a 32-bit version of Windows.)
RmSplitBinary32.zip
RmSplitBinary64.zip
RmSplitSource.zip

Author: R. Steven Turley

Won’t run?

Probably the same problem and solution as described for RMtrix.

Screenshots From a Modified Build

These are from ve3meo’s variant.

Select Person Form called from the Main screen Change Root Person button. The full name list includes alternate names and birth-death years. On opening it defaults to the first name.

Typing some letters filters the name list to only those containing the string. Click on any one to select it.

Main screen after selecting the database file, setting the root person, generations and leaves parameters, naming and creating the group. At the bottom is a status area showing the time it took (less than 1s) and the number of persons in the group. On the right is a log of actions,

Clip from RootsMagic 5 sidebar showing the members of the group created by RmSplit. To refresh the list after running RmSplit with different parameters, click on the Add, Edit, Delete Named Group icon next to the group name list box and cancel the resulting Named Groups dialog window.

Discussions & comments from Wikispaces site

Inline comments

WebTrees Website – Pre-Processing Tools using SQLite in Visual Basic et al #webtrees #visualbasic

Posted on 2012-03-042022-04-29 by Tom Holden

Source code

The following paragraphs are extracted from private messages from Tom, davistom1, who kindly agreed to their being brought onto a page for wider dissemination. He talks about the relationship between his RM4 database and media collection and two websites he operates, one a webtrees, the other GenWeb, both worth a visit. He developed some procedures using SQLite and Visual Basic that he runs against his RM database prior to exporting a GEDCOM for upload to the sites. Tom has made a deployable version of his RMSanity application along with its source code and it’s available for download on this page. (page created by another Tom, ve3meo).

…
I long ago developed a SQLITE based preprocessing program which reads the RM4 database (or RM5 for that matter) and does a whole bunch of sanity tests, including locating any duplicate or unused places, place details, media files, etc. which I can then resolve directly in RM4 (or RM5). I do the preprocessing/cleanup before export to GEDCOM in preparation for importing the latter into a webtrees database for a public website which I operate.
…

The computer based parts of my genealogy activity center around a desktop RM4 database (I haven’t actually switched to RM5 yet because of some of the headaches associated with the media handling re-design) and two websites which I operate. One website is my own webtrees based family history portal located at http://fotgp.com and the other is the Northampton County, North Carolina GenWeb site located at http://ncgenweb.us/northampton, for which I am site coordinator. All of these activities share parts of a large, currently ~11Gb, media library. The media library file tree is organized with top level directories representing some 75 repositories, mostly public record repositories, with sub tree branches mimicking the repository filing system but with some ad hoc repository branches also. My RM4 database currently links into about 7500 items in this media library. I have a whole bunch of homebrew code used to manage this mess, and a good bit of it directly queries the RM4 SQLite database for an array of sanity testing and data transformation between the various end uses.

My webtrees site is updated frequently from the RM4 database by first sanity testing it, manually tracking down and correcting discrepancies in it, backing it up, exporting it to GEDCOM, processing the GEDCOM through a bunch of transformation steps and finally importing the transformed GEDCOM into the site database. I do not routinely edit the webtrees database via its web interface, though that is what webtrees and its predecessor, phpGedView, were designed to do. My personal genealogy data baseline is my RM4 desktop database.

The connection with the GenWeb site is primarily the latter’s Cemeteries and Burials, Photo Albums and Places portals, the homebrew MySQL database which these portals employ, their links into the previously described media library and the homebrew code used to update the MySQL database. The only link between these and RM4 SQLite is that they share the same master media library, so no more about them here.

I get into RM SQLite databases two ways: (1) for some purposes I use the SQLite Developer app but mostly, (2) I use some homebrew code with the System.Data.SQLite ADO.NET provider library. I use SQLite Developer mainly in developing and debugging queries. Trying to distill the logic of my various SQLite queries to something which is both (1) compatible with the focus of your wiki and (2) presentable is a significant undertaking. Let me elaborate a bit and then we can explore whether it would be useful.

The RM4 database sanity test noted above is illustrative. I have written a desktop app which uses the aforementioned System.Data.SQLite ADO.NET provider to test for and report discrepancies in the following: (1) broken media links, (2) non-privatized SSNs, (3) orphaned place details, (4) un-used place details, (5) un-used places, (6) un-used media items and (7) duplicate media items. The app also requires two SQLite extensions, a function which tests for existence of a media library file denoted by its path and a NOCASE collation substitute for the infamous RMNOCASE. Incidentally, I don’t ever write to the RM4 database outside the RM4 app.

SQLite Queries in RMSanity

Following are examples of the hard coded SQLite queries used for some of these tests:

-- Broken media links
SELECT m, MediaFileExists(m) AS e
FROM (
      SELECT mediapath || mediafile AS m
      FROM MultimediaTable
      ) AS mm
WHERE e=0;
 
-- Non-Privatized SSNs
SELECT et.OwnerID
FROM EventTable AS et
JOIN FactTypeTable AS ft
ON et.EventType=ft.FactTypeID
WHERE ft.Name='Soc Sec No'
AND NOT et.IsPrivate=1;
 
-- Orphaned place details
SELECT pd.*, p.Name
FROM PlaceTable AS pd
LEFT OUTER JOIN PlaceTable AS p
ON pd.MasterID=p.PlaceID
WHERE pd.PlaceType=2
AND p.PlaceID ISNULL;
 
-- Un-Used media items
SELECT MediaPath || MediaFile AS media
FROM MultimediaTable AS mm
LEFT OUTER JOIN MediaLinkTable AS ml
ON mm.MediaID = ml.MediaID
WHERE ml.MediaID ISNULL;
 
-- Duplicate media items
SELECT *
FROM (
      SELECT COUNT(MediaID) AS c, MediaPath, MediaFile
      FROM MultimediaTable
      GROUP BY MediaPath, MediaFile
      )
WHERE c > 1;

SQLite Extensions in RMSanity

VB versions of the two extensions are:

<SQLiteFunction(Name:="RMNOCASE", FuncType:=FunctionType.Collation)> _
 
Friend Class NoCaseCollation
 
Inherits SQLiteFunction
 
Public Overrides Function Compare(ByVal param1 As String, ByVal param2 As String) As Integer
 
Return String.Compare(param1, param2, True)
 
End Function
 
End Class

and:

<SQLiteFunction(Name:="MediaFileExists", FuncType:=FunctionType.Scalar)> _
 
Friend Class MediaFileExists
 
Inherits SQLiteFunction
 
Public Overrides Function Invoke(ByVal args() As Object) As Object
 
Return My.Computer.FileSystem.FileExists(args(0))
 
End Function
 
End Class

Usage of RMSanity

When successfully installed, the RMSanity app will be found in the Start Menu. Starts quickly, with a blank screen Windows Console followed shortly by an Open File dialog filtered to show RootsMagic (*.rmgc) database files. Open any database and then an Open File dialog opens to name and locate the RMSanity log file. On completing that step, RMSanity proceeds to run its seven tests:

Broken media links
Non-privatized SSNs
Orphaned place details
Unused media items
Unused place details
Unused places
Duplicate media items

If all tests pass, the console window screen looks similar to this:

RMSanity: all tests passed.

As written, the application stops testing with the first test failed:

Each failed test then lists the information with which you can find and fix the problem using RootsMagic. In the above example, the individual with RIN 120 (I120) has a SSN fact that has not been privatized and so would be exported in the GEDCOM possibly to a website. Clearing all reported problems is Tom’s objective before exporting to his webtrees website. The log file contains exactly what you see on the screen so that you can refer to it as you work through the problems.

To close the application, DO NOT USE the windows X if you do not wish to be alarmed by an error message. Type ‘exit’ into the RMSanity screen and press Enter.

Download and Installation

Caveat Emptor

The first thing Tom says is that he developed RMSanity for his own use and purposes. If it is of use to anyone else, great, but he cannot support it. Use at your own risk! Risk is negligible because it is a read-only application that does not modify the database.

That said, this Tom thought it would be of interest to some and perhaps stimulate some others into further development of utilities in support of our use of RootsMagic. That RMSanity is in Visual Basic while the intent has been to develop the Bundled Utilities in Visual C# ought not be a deterrent – concepts are transferable if code is not. Those more comfortable with Visual Basic may find that the RMSanity source code is a great place to start.

Prerequisites

RMSanity uses the version of SQLite that requires Windows .NET 4 Framework (not just the Client). Install .NET 4 Framework.

Download

RMSanity.zip

Installation

Extract all the files under the Publish folder to a temporary folder. Find and click on setup.exe in the temporary folder. The installation will add a RMSanity folder to your start menu with a link to rmsanity.exe.

Won’t run?

Probably the same problem and solution as described for RMtrix.

Source code

Open Module1.vb to view the Visual Basic source code.

Discussions & comments from Wikispaces site

Simple Understanding

LessTX
13 February 2012 23:26:32

I’m going to try to translate that page into what a typical end user might understand, tell me where I’m wrong:

This program checks your database for inconsistencies and unused items, and then alerts you to things you want to fix.

So, to compare it to the TMG Utility (the only 3rd party genealogy utility with which I am familiar), it would have 7 items on the list
(Broken media links, Non-privatized SSNs, Orphaned place details, Unused media items, Unused place details, Unused places, Duplicate media items) and be the “log only” option. It doesn’t actually change the database in any way.

These 7 things seem like very good items to have on the wish list for the utility program, but there would need to be the step of “fixing” those things within the utility.

Excellent stuff!

ve3meo

ve3meo
14 February 2012 02:29:47

That’s it in a nutshell, Less.

Media List Query #media #filenames #msexcel #date #person #family #events #sources #citations #places #placedetails

Posted on 2012-03-022022-04-28 by Tom Holden

Description

One query lists all the media files in the RM4 Media Gallery and the ID of the Person, Family, Event, Source, Citation or Place to which each is linked. Used in conjunction with other queries, one can look up the media item or find the details about the item to which the media are attached. For example, the Source List query indicates if there are media items attached to a citation. Given the citation ID from the Source List query, one can readily find all the media items with that OwnerID in the Media List query results. Conversely, a media item may be attached to multiple items – this query helps to find each one by reporting the type and ID of the objects to which it is attached.

SQLite managers have differing capabilities in manipulating the results. SQLite Expert Personal can display thumbnail images but wraps the text contents of cells and prints “<null>” in empty cells; it has useful filtering and sorting so its display can be cluttered or effective, depending on selections. SQLiteSpy does not display thumbnails nor filter nor wrap but sorts quickly and produces a very clean display. Both allow you to select just the columns you want displayed for a less crowded screen. From either one, results can be copied and pasted into Excel, enabling a hyperlink to the media file produced by the query.

Download

MediaList.sql for RM4. MediaList-RM5.sql for RM5.

Screenshots

SQLiteExpert

Screenshot from SQLite Expert Personal. Requires horizontal scrolling to see rest of columns. Narrowing causes row height to expand. De-selecting columns can simplify the display.

SQLiteSpy

Screenshot from SQLiteSpy. No wrapping so narrowing columns does not affect row height but must select cell to view its full content. Deselecting columns can simplify the display.

Column Definitions

TBA

Source Template, A Better Free Form #sourcetemplates #sources #search #replace

Posted on 2012-02-242022-04-29 by Tom Holden

For a comprehensive comparison of the outputs from !MyFreeForm3, built-in and custom source templates, and Free Form see A Trio of Templates.

A Better Free Form Source Template

Give me a template that is easy to use, produces good full and short footnotes without extraneous punctuation and exports cleanly via GEDCOM to other software!
WARNING: This page has been extensively updated since 14 Oct 2011. !MyFreeForm3 supersedes !MyFreeForm and !MyFreeForm2 templates which are deprecated due to potential duplicating of the key term in the GEDCOM export along with an unwanted period.

RM4’s Source Templates are a boon and frustrating at the same time. If you can choose the right one for a given source from some 417 different templates, use it correctly, you do end up with a footnote and bibliography style that is pretty consistent with what the gurus advise it should be. That’s great for reports and websites generated from within RM4 but what gets exported to a standard GEDCOM for use by some other software is often pretty badly messed up.

The built-in Free Form template does a better job on exporting, you have total control over what gets into the TITL and PAGE tags, only limited by the order in which the tags are used to create footnotes. However, the Free Form template falls down in RM reports when a source is repeatedly cited, because there is only one Source Details field, Page, used by both the full, first footnote and by subsequent short footnotes. Thus, if lots of detail (e.g., think of a census citation) is entered in the Page field to ensure that the particular location within a source can readily be found, that same lengthy detail gets repeated in the subsequent short footnotes.

I have wrestled at length to come up with a Source Template that minimizes constraints (fewest fields=max flexibility), provides a short footnote if needed, exports nicely to standard GEDCOM, and has no extraneous punctuation. That has not been easy, given RM4’s idiosyncrasies. I believe !MyFreeForm3 is about the best that one can do. This custom template mimics the built-in Free Form template, adding one field in Source Details: Key: [ShortPage], for use in Short Footnotes instead of Details:[Page] (renamed from Page:[Page]). It also provides an additional field Punctuation:[Punc] in the Master Source with which you can override the default “, ” separator in the footnote between master source and source details.

Example of a source in !MyFreeForm3

Import this Source Template

Download the template file !MyFreeForm3.rmst to your computer. From RM4 main screen, select Lists > Source Templates to open the Source Templates window. Then select Import and browse to locate and select the .rmst file. If the new template was imported, at or near the top of the list of Source Types, you should see !MyFreeForm3. It’s now available for the creation of new sources.

Using !MyFreeForm3

Guidance on entering data

For Footnote, Short Footnote and Bibliography, follow the same practices as you would with the Free Form template, but do not end any with a period.
The Punctuation field only affects footnotes in RM4 generated reports, not exports. Plug in a character to see the effect in the right pane.
The two Source Details fields can be both filled, both null, or either one filled. Only when both are filled will the resulting footnote and short footnote differ in any way other than what you have defined in the corresponding Master Source fields. Filling neither or one is equivalent to the Free Form template.
Ending footnotes with a period is pretty standard. Due to an export issue, the template must not automatically supply it. You provide the ending period at the end of the bottom-most Source Details field you use. If both Source Details fields are empty, the footnote will not end with a period (sorry, there’s no way around it).
On export, RM4 is going to stuff “; ” between non-null Details:[Page] and Key:[ShortPage] which it puts into the PAGE tag. For consistency, that’s what the full Footnote sentence template does, too.

Compose draft source with other templates

To mimic in !MyFreeForm3 the EE et al footnotes as well as one can, I try using a two-stage procedure. First, I’ll use a Source Template suited to the type of source I am citing as a guide for how I may write a !MyFreeForm source. I fill in the fields of the selected template to generate the full and short footnotes and bibliography in the side panel, and copy these results to a word processor. Then I can parse the footnotes into their Master Source values (Footnote and ShortFootnote) and into their Source Details values (Details:[Page] and Key:[ShortPage]). Having done that, then I cancel the source creation and start over with the !MyFreeForm template, copying the values into the corresponding fields. That results in footnotes that resemble, to some degree, the gurus’ footnotes, at least to the same level of detail, if not order.

Copy draft citations from Ancestry.com et al

Lots of sites provide citations of their resources and there are online Citation Machines. In effect, they have used their own templates. Breaking them down into component parts is the same as for the draft sources created using RM4 source templates, described above. It’s a whole lot easier to parse them into !MyFreeForm3 than any other RM4 template, save the Free Form.

Examples

Have a look at this page for an example of a !MyFreeForm3 template used for the 1930 US Census: Source Template, A Better Free Form – Examples
and at the discussion below for an example of a directory as a source.

Merging

Merging of Free Form sources into !MyFreeForm3 sources is lossless, datawise. All the fields of Free Form are duplicated in !MyFreeForm3; only the Punctuation:[Punc] and Key:[ShortPage] fields are unique and will remain empty after the merge. However, to take advantage of the better Short Footnote, you will have to take out the key value from the Details:[Page] field and put it into the Key:[ShortPage]field of each citation. As long as Key:[ShortPage] remains empty, the Short Footnote will come out essentiallly the same as from Free Form.

Global Conversion to !MyFreeForm3

Global conversion of Free Form sources to !MyFreeForm3 sources is easily and quickly done using SQLite. Open the database with your SQLite manager and inspect SourceTemplateTable for the value !MyFreeForm3 in the Name column. It will be at or near the bottom of the table with a TemplateID of 10000 or greater. Copy the following queries, edit them to plug in the TemplateID of !MyFreeForm3 in your database in place of YourTemplateID and run the queries to map all your FreeForm sources to the !MyFreeForm template:

-- Map Free Form sources to !MyFreeForm3 source template
UPDATE SourceTable SET TemplateID=YourTemplateID WHERE TemplateID=0
;
-- Add a [ShortPage] definition to every citation of a !MyFreeForm3 source lacking one
--  else you will see the text "[ShortPage]" outputted in RM4 reports
UPDATE CitationTable
 SET FIELDS = CAST(REPLACE(CAST(FIELDS AS TEXT),'</Fields>', '<Field><Name>ShortPage</Name><Value/></Field></Fields>') AS BLOB)
  WHERE CitationID IN
   (SELECT CitationID FROM CitationTable C JOIN SourceTable S USING(SourceID)
     WHERE TemplateID=YourTemplateID AND CAST(C.FIELDS AS TEXT) NOT LIKE '%<Name>ShortPage%'
    )
;
-- A similar update of the SourceTable is required for the [Punc] field of !MyFreeForm3
--  else you will see the text "[Punc]" outputted in RM4 reports
UPDATE SourceTable
 SET FIELDS = CAST(REPLACE(CAST(FIELDS AS TEXT),'<Field><Name>ShortFootnote', '<Field><Name>Punc</Name><Value/></Field><Field><Name>ShortFootnote') AS BLOB)
  WHERE SourceID IN
   (SELECT SourceID FROM SourceTable S
     WHERE TemplateID=YourTemplateID AND CAST(S.FIELDS AS TEXT) NOT LIKE '%<Name>Punc%'
    )

Follow up to enter values in the Key:[ShortPage] field of each citation that needs an improved short footnote, and take out the duplicate values from the Details:[Page] field.

How to find citations needing an improved Short Footnote

This section focuses on sources that have been globally converted to the !MyFreeForm3 templates.

Inspecting the Source List Report

For all source types and sources, one could create a RM4 Report > List > Source List including the Short Footnote and Citation Details (but not Detail Text, Comment and Media as that may clutter the report). Unfortunately, this report cannot be filtered by type of Source Template so you would find sources using the !MyFreeForm templates by searching for the term “[ShortPage]” in the report. It occurs in both the Footnote and the Short Footnote line. Doubly unfortunately, this report makes it hard to tell if a Key:[ShortPage] value has been entered, but you can use it to look for lengthy citations used more than once and thus likely to incur the use of the Short Footnote. Since the person and fact are identified in the Citation list for a Source in this report, you can then edit each citation accordingly. Best to fix one, Memorize it, Paste it into the other facts that use it, compare the original for differences in Detail Text, Comment and Media and resolve them on the copy, then delete the old. That guarantees that the key citation properties are truly identical so that Short Footnote and Ibid will come into play.

“Example from Source List Report having a //ShortPage// field and therefore uses the !MyFreeForm3 template.

This screenshot from the Source List report shows that both Footnotes contain the [ShortPage] field so this is a !MyFreeForm3 source. It has two identical citations for facts about the same person so will surely use either the Short Footnote or Ibid in reports. It also has citation or source detail values for both the Details:[Page] and the Key:[ShortPage] but it is hard to tell because they are merely separated by a semi-colon. Lengthier citations with embedded semi-colons are even tougher to deconstruct into the two fields. If there is no obvious key value repeated, then either

[ShortPage] is null and the short footnote may be as verbose as the full one. The absence of any semi-colon “; ” in the citation is absolute confirmation but the opposite is not necessarily true.
This is a properly parsed !MyFreeForm3 citation. The absence of any semi-colon “; ” in the citation is absolute refutation but the opposite is not necessarily true.

Not overly helpful…

An SQLite query showing unparsed Source Details, longest first

Of course, SQLite could help in this process. I adapted an earlier query to produce the following report which shows the length (LenDet) of the field containing the [Page] value with a null value in [ShortPage].

Screenshot from SQLiteSpy of !MyFreeForm sources lacking a //ShortPage// value, in descending order of the length of the field containing the //Page// value.

Sorted in descending order, I work from the top down, re-running the query after each editing and replacement of duplicate citations (those with the same length most likely have identical values). Thus, the most verbose citations are tackled first. Some 156 characters out of the total shown under LenDet are used by XML tags, as can be seen at the bottom of the screen – that’s the value of the highlighted cell which contains the values of all the fields in Source Details. Download SourcesByDetailLength.sql

Discussions and comments from Wikispaces site

!MyFreeForm3 template

Geraniums
05 December 2011 18:01:37

If I share my database with others (some weeks another person enters data, other weeks a 2nd person enters data, etc), then we should all import your enhanced free form template (!MyFreeForm3), is that correct?

When RootsMagic has an update, does the template stay in the program? Would I ever need to re-import it?

How do I download your template? I clicked here, but then what?
!MyFreeForm3.rmst

Thanks!

ve3meo

ve3meo
05 December 2011 20:45:40

Once a custom template is imported into a database file, it stays there until deleted and cannot be deleted if it is in use by a source. If you share the database FILE, then everyone will see it. If a person having a source using a custom source template is transferred by drag and drop or by GEDCOM (including RM-specific features), the custom template spec is also transferred.

When an RM update or new release updates a database, it historically has preserved custom templates.

You should only need to re-import to a new database.

To download, right-click on the link and select ‘Save target as’, directing the save to a convenient place on your hard drive. Then follow the instructions in Help > custom source templates to import it into Lists > Source Templates.

Hope that’s clear and would appreciate your observations and experience in using the template.

Tom

Geraniums

How to use the new free form template

Geraniums
30 December 2011 19:00:05

Can I please have more examples of how to enter information in the different fields? And how the sentences read afterward.

I’m not sure what “key” means, or what type of punctuation gets entered. Where would the year of a publication be entered?

Thanks

ve3meo

ve3meo
30 December 2011 21:15:41

Using the labels beside each data entry field and enclosing them in square brackets to signify the variable data values one enters into the fields, here is what the resulting report sentences would look like:

Footnote: [footnote]<,|[punctuation]> [preamble]; [key]
Short Footnote: [shortfootnote]<,|[punctuation]> [key]
Bibliography: [bibliography].

This <,|[punctuation]> indicates that a comma will be used by default, else the character(s) you enter into the Punctuation field, such as a colon (:) or semi-colon (;); easiest to leave it empty as a comma is the most common punctuation.

I would urge you to actually try the template on a Play database so that you get the feel of it. The sentences will read the way you want them within the constraint that the Source Details always follow the Master Source data. And it’s not very different from Free Form, except for the resulting Short Footnote.

I used the term “Key” to indicate the most specific piece(s) of the Source Details that one would want in the Short Footnote and the term “Details” for the rest of the Source Details that show up with “Key” only in the long Footnote.

Year of publication would most commonly be entered in the Footnote field.

Here’s an example:
[footnote]: Lovell’s Canadian Dominion directory for 1871 (Montreal: J. Lovell, 1871); microform digital image, Canadiana.org, Internet Archive (http://www.archive.org/details/cihm_09143 : downloaded 7 April 2011)

[shortfootnote]: Lovell’s Canadian directory, 1871

[details]: entry for Sinclair Holden, Markham

[key]: page 518.

Resulting footnotes:
Footnote:
Lovell’s Canadian Dominion directory for 1871 (Montreal: J. Lovell, 1871); microform digital image, Canadiana.org, Internet Archive (http://www.archive.org/details/cihm_09143 : downloaded 7 April 2011), entry for Sinclair Holden, Markham; page 518.

Short Footnote:
Lovell’s Canadian directory, 1871, page 518.

I left [punctuation] empty so it defaulted to comma and I omitted Bibliography as it involves no other fields.

Hope that comes out on the Discussion page okay and is helpful. Glad you’re taking an interest.

Tom

RWells1938

Example

RWells1938
01 January 2012 12:47:07

Tom

Could you give a example on how you use your free from source using a 1930 census?

thanks
Roger

ve3meo

ve3meo
01 January 2012 20:56:33

Roger, I have done the example you requested as a PDF that is linked from the Page. If you look at its Table of Contents for “Examples”, you will readily find it.

Tom

All Citations & Dupes Count – Query #citations #duplicates

Posted on 2012-02-232022-04-27 by Tom Holden

Download: AllCitations+Dupes.sql 2010-07-07
Revised:
2012-02-23 to accelerate family fact citations by 200:1 (in one case)
2011-11-04 to correct spouse and family fact citations

Discussions & comments from Wikispaces site

Runs too long?

Geraniums
23 February 2012 12:03:32

I tried running this query, and it ran for nearly an hour and still no results were displayed. There doesn’t seem to be a Stop or Cancel button in SQLiteSpy (3.6.23)?

Running it on RM5 database using Win 7 32 bit.

File > Close Database – gave an error message that it’s still running.

File > Exit – didn’t close the program.

Had to cancel with task manager.

Geraniums

Geraniums
23 February 2012 12:04:42

What I was hoping to do was run a query for checking for duplicate citations.

You had helped me with that in 2010, and had given me this code, which works well:

— AllCitations+Dupes.sql
— 2010-07-07 by ve3meo
— based on AllCitations.sql, using a similar procedure to build a temporary
— table of all citations from which a subsequent query in the script
— can display specific results – in this case a summary report
— showing all sources cited against a person sorted by the numbers
— of duplicate citations.
— N.B. some SQLite managers may fail to display any results; it will be necessary to
— execute the last SELECT as a sub-query using whatever technique the manager
— provides, e.g., highlighting the command to be executed while the temporary
— table still exists.
— To Do – maybe use eventtable.SortDate as a sorting criterion instead of Date
— 2011-11-04 ve3meo corrections for spouse, and family fact citations and multiples due Alt Name
—
— BEGIN
— all Personal citations for Individual
DROP TABLE IF EXISTS tmpCitations;
CREATE TEMP TABLE tmpCitations AS
SELECT c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname COLLATE NOCASE AS Surname, n.suffix COLLATE NOCASE AS Sfx, n.prefix COLLATE NOCASE AS Pfx, n.given COLLATE NOCASE AS Givens, n.birthyear AS Born,
n.deathyear AS Died, Null AS EvtID, ‘Personal’ AS Citer, Null AS EvtDate,
s.NAME COLLATE NOCASE AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, s.fields AS SrcFlds,
c.refnumber AS CitREFN, QUOTE(c.actualtext) AS CitTxt, QUOTE(c.comments) AS CitComment, c.fields AS CitFlds
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN persontable p ON c.ownerid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
WHERE c.ownertype=0 AND +n.IsPrimary=1
;
INSERT INTO tmpCitations
— all Fact citations for Individual
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname COLLATE NOCASE , n.suffix COLLATE NOCASE , n.prefix COLLATE NOCASE , n.given COLLATE NOCASE , n.birthyear,
n.deathyear, e.EventID AS EvtID, f.NAME AS Citer, e.Date AS EvtDate,
s.NAME COLLATE NOCASE , s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1
;
INSERT INTO tmpCitations
— all Spouse citations for Father|Husband|Partner 1
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
n.deathyear, NULL AS EvtID, ‘(Spouse)’ AS ‘Name’, NULL AS EvtDate,
s.NAME, s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
— LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
— LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=1 — AND e.ownertype=1 AND f.ownertype=1
AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
— all Couple Event citations for Father|Husband|Partner 1
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
n.deathyear, e.EventID AS EvtID, f.NAME, e.Date AS EvtDate,
s.NAME, s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN familytable fm ON c.ownerid=e.EventID
LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=1 AND f.ownertype=1 AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
— Citations for Alternate Names
SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
n.deathyear, Null AS EvtID, ‘Alternate Name’ AS Citer, Null AS EvtDate,
s.NAME AS Source, s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds
FROM citationtable c
LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
LEFT OUTER JOIN nametable n ON n.nameid=c.ownerid
WHERE c.ownertype=7 AND +n.IsPrimary=0
;

— Display summary results, in descending order of duplications
SELECT RIN, Surname || ‘ ‘ || Sfx || ‘, ‘ || Pfx || ‘ ‘ || Givens || ‘ (b’ || Born || ‘-d’ || Died || ‘)’ AS Person, Citer, EvtDate, COUNT(1)-1 AS Dupes, Source FROM tmpcitations
WHERE uniq=1 OR uniq ISNULL
GROUP BY RIN, EvtID, Citer , SOURCE, SrcREFN, SrcTxt, SrcComment, SrcFlds, CitREFN, CitTxt, CitComment, CitFlds
ORDER BY Dupes DESC, RIN, EvtDate
;
— END

Bundled Utilities Project

Posted on 2012-02-202021-02-27 by Tom Holden

As of 2012 and following the release of RootsMagic 5, a small team of enthusiasts is embarking on a software project to consolidate the various SQLite queries reported on this wiki into a user-friendly application, accessible to more RootsMagic users. Additional utilities will, hopefully, be added once the basics are running. The project team is ad hoc, led by Steve Turley, and welcomes your input.

This page provides links to the various pages of discussion, examples, etc. return frequently to monitor progress.

RootsMagic Utilities at http://rmutils.wordpress.com/ the plain language blog and navigation hub for the project set up by LessTX. (Gone)

Related pages:
Bundled Utilities – RMtrix Tom Holden’s 1st gen RMtrix app in Visual C#.
WebTrees Website – Pre-Processing Tools using SQLite in Visual Basic et al Tom Davis’ RMSanity app in Visual Basic
SplitTree Steve’s first utility, developed in Visual C#, that got the ball rolling
Which Platform for RootsMagic Utilities? discussion that settled on continuing with Visual C# for prototyping; other matters.
Bundled Utilities Project Name suggestions and a poll. Get your 2 cents in!
Bundled Utilities – Groupings suggested groupings of utilities/functions under a menu structure; ongoing.
Bundled Utilities – Example content a look at TMG Utiltiy and Ltools for ideas of what to include and how to structure the program
A Proposal for a Named Group and Color Manager – a standalone utility offering major enhancements to these functions, being developed by Jerry Bryan, bundling later.
Wish List – post your wishes for utilities as a message in the Discussion area of this page

Discussions & comments from Wikispaces site

Check RootsMagic Database Integrity #pragma #database #integrity

Posted on 2012-02-042019-01-26 by Tom Holden

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.

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 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

Discussions & comments from Wikispaces site

JP-RM

Why checking RM database integrity proved very useful

JP-RM
02 June 2010 14:23:30

In early April 2010 I suffered an “SQLite error 11 – database disk image is malformed”. Although never encountered before, all of a sudden practically any addition to my RM data resulted in this error. In addition it then proved impossible to shutdown RM unless I reverted to using the Windows Task Manager.

Given that my data had been built up over several years, the implications of this error were most worrying. What to do?

I had only just upgraded from RM 4.0.7.1 to 4.0.8.0, so this was the most obvious aspect to investigate. So I raised a problem report with RM support, and attempted to describe a sequence of actions that they could use to attempt to recreate the problem.

I soon found that by creating a new empty database and making a few basic data additions, I seemed to be able to recreate the problem at will. However RM support were not able to cause the problem when they attempted recreation.

In parallel, as a result of my appends on the RM forums, I became aware of this website and received offers of use of some of the tools referenced here to investigate my data. At that stage, this just showed that my database was indeed corrupted.

Holidays and other activity then intervened, but I was hoping that RM support would come up with something useful. Unfortunately, nothing was forthcoming even though there were other uses who were starting to report the same (or a similar) problem.

Then RM forum activity started to point to this problem only occurring when RM was used by those running Windows on an Apple Mac. To be more precise, those using Parallels for Mac to enable Windows (XP SP3 in my case) to run at the same time as the native Mac OS X operating system. AND when the RM data was stored in a folder that was accessible from both Windows and Mac applications.

At this stage it was time to investigate more closely. Using the quick_check function of SQLiteSpy, I developed a simple and repeatable sequence of RM actions on a new empty RM database and ran quick_check every time the database changed (identified by the Date Modified in Windows Explorer). This showed exactly when a first database integrity error was caused. I assumed that this would lead in due course to an SQLite error 11 (but did not bother to go so far).

I then changed to using folders on the base XP virtual C-drive (not shared with Mac applications), ran the test sequence again, and the problem did not re-occur.

I also re-installed RM 4.0.7.1 and redid the tests, with exactly the same result. The problem again occurred when using shared folders. So the change to RM 4.0.8.0 – or by then 4.0.8.4 – was not the cause.

At this stage I then went back through my Mac data archive (Time Machine) to check exactly when RM maintenance and Parallels maintenance was applied. Then looking at when I had taken RM backups (I always take at least one per RM usage session on exiting RM) and looking at their file sizes and using the SQL in RMGC_TablesRowCouunt.sql to investigate their restores, I concluded that the error occurred the first time I added any additional data to my database after upgrading Parallels in mid-March (even though it was by then early April).

A quick investigation of the Parallels website then showed that there was indeed a problem with shared folder support in the level of Parallels I had upgraded to in mid-March (5.0.9344). Not only that, a fix was available (http://kb.parallels.com/en/8296) and applying it resulted in the problem disappearing.

To conclude, the problem was caused by using Parallels 5 at level 5.0.9344 and only when sharing the data folders used by RM between Windows and Mac applications.

One additional area that I found useful was the following:

RM backups have a date/time in Windows Explorer of when they were created. However, when a backup is restored, and BEFORE any change is made to the data, the date/time displayed in Windows Explorer will be the last time the data was modified, which MAY be earlier, both in time and date, than when the backup was taken. In my case this proved to be very useful as it showed that the data had not been modified between backups taken on different days. (Some days I just invoke RM to answer email queries, such that I won’t actually change the data on that day. RM (well Windows) does not update the last modified date/time in such cases.

RWells1938

Database Coruption

RWells1938
20 June 2011 17:40:56

Has anyone used the sqlite vaccume to clean up a RM4 database? Will this fix problems?

If so what do I need to have in order to do this?

Thanks
Roger

ve3meo

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.

RWells1938

re: Database Coruption

RWells1938
20 June 2011 18:58:44

If I use a gedcom export and then import the gedcom what should I look for as possible data loss?

Roger

ve3meo

ve3meo
20 June 2011 19:51:21

If the database is corrupted, the export may fail; if it does not fail, I cannot predict if there would be any data loss due to corruption. OTOH, a RM4 GEDCOM export/import or a drag’n’drop is not yet perfectly transparent – there remain some subtle and not-so-subtle losses even if the database file is uncorrupted. Cannot say completely, but, as of 4.1.1.4, white space at the end of Notes is lost and there may be some issues yet with custom sentences, roles, …

Tom

Media – Preserve Captions et al from RM4 to RM5 #media #metadata #batch

Posted on 2012-01-312019-01-25 by Tom Holden

RootsMagic 4 supported a unique set of metadata (Caption, Description, Date, SortDate, RefNumber) for each use of a media file, i.e., if the same image file was used for a citation, a fact and a person, there were three sets of metadata. RootsMagic 5 does not permit that, allowing only one set of metadata per file. When a RM4 database is updated to RM5, only one of those three sets of metadata survive.

This series of queries and related Windows commands revises a RM4 database and makes copies of the multiply-linked files so that the update to RM5 will preserve all the metadata.

It creates a temporary table in the RootsMagic 4 database named MultiLinkMediaDupe:

LinkID	MediaID	MediaType	MediaPath	MediaFile	URL	Thumbnail
103	96	1	C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHendry	Hendry, Alex – grave.jpg		Blob
143	96	1	C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHendry	Hendry, Alex – grave.jpg		Blob

Inserts new records into the MultiMediaTable to point to future unique copies of the multi-linked image file (the duplicate shown below is prefixed by the LinkID to make it unique):

MediaID	MediaType	MediaPath	MediaFile	URL	Thumbnail
134	1	C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHendry	103-Hendry, Alex – grave.jpg		Blob

Generates a bunch of SQL statements (one example shown) that you copy and execute to update the MediaLInkTable to point to the new unique copy of the media file from the link which holds the metadata:

–SQL
UPDATE MediaLinkTable SET MediaID=134 WHERE LinkID=103 ;

Generates a set of Windows commands (batch) that you copy to a .cmd or .bat file and run to make unique copies of the media files:

REM Make unique copies of multi-linked RM4 media files
COPY “C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHendryHendry, Alex – grave.jpg” “C:MyDocsFamilyTreeHolden-Cudworth_Wilson-WasonScrapbookHendry103-Hendry, Alex – grave.jpg”

When completed, the Media Gallery will have more items, all beginning with a number, followed by a hyphen, before the original file name. From there, you can check a few to confirm that they are used by the expected source, fact or person. If you’re happy, then you can safely upgrade to RM5 without fear of losing your custom captions.

RM4-MultiLinkedMediaDuplicationPreRM5.sql

Risk

Backups may not help

Some recovery techniques

Converting data to SQlite script

Finishing

Discussions & comments from Wikispaces site

Drag ‘n’ Drop vs Export “everything”

Won’t run?

Screenshots From a Modified Build

Discussions & comments from Wikispaces site

Congrats! but no process

Why Visual C#? Which wrapper?

New Wiki for Apps?

Speed

Compiled my own version

Bug with Leaves

Inline comment: “4-bit version of Windows‍”

Inline comments

Comment: 64 bit success for me! But do not use…

SQLite Queries in RMSanity

SQLite Extensions in RMSanity

Usage of RMSanity

Download and Installation

Caveat Emptor

Prerequisites

Download

Installation

Won’t run?

Source code

Discussions & comments from Wikispaces site

Simple Understanding

Description

Download

Screenshots

SQLiteExpert

SQLiteSpy

Column Definitions

A Better Free Form Source Template

Import this Source Template

Using !MyFreeForm3

Guidance on entering data

Compose draft source with other templates

Copy draft citations from Ancestry.com et al

Examples

Merging

Global Conversion to !MyFreeForm3

How to find citations needing an improved Short Footnote

Inspecting the Source List Report

An SQLite query showing unparsed Source Details, longest first

Discussions and comments from Wikispaces site

!MyFreeForm3 template

How to use the new free form template

Example

Discussions & comments from Wikispaces site

Runs too long?

Discussions & comments from Wikispaces site

The Public Face of the Project

Why?

How?

Comparison of outputs for a corrupted RootsMagic database

Discussions & comments from Wikispaces site

Why checking RM database integrity proved very useful

Database Coruption

re: Database Coruption