Sources+-+Adventures+in+Extreme+Splitting

toc This page brings to bear some SQLite tools in support of Jerry Bryan's discussion in the RootsMagic forums titled [|Adventures in Extreme Splitting]. In summary, the concept is that every unique citation is a Master Source, i.e., all data fields are to be found in the Master Source, none under Source Details. Jerry's observations are that RootsMagic behaves better as a result in these respects: A disadvantage is that the Bibliography may be verbose, becoming just an alphabetical sort of the complete endnotes, without duplicates and there is little opportunity to benefit from succinct Short Footnotes.
 * templated sources export to standard GEDCOM and thus to third-party software and websites with better integrity. See Ultimate Splitting of Sources for Better GEDCOM from RootsMagic
 * sources transfer more completely to FamilySearch Family Tree (RM does not transfer Research Notes and Detail WebTags, only Source Text and first Master WebTag so this procedure moves Research Notes and Detail Web Tags to the Master Source fields)
 * easy duplication
 * one page to edit all citations of a source instead of one page per citation, if the change would otherwise be at the conventional Source Details level.
 * one set of images per source which covers all citations of the source
 * a complete source can be created in the Source Manager

=Convert Existing Sources and Citations= This query converts all sources and citations and the source templates they use to extremely split sources. It's a work in progress - feedback invited. The current version converts lumped source templates, sources and citations to extremely split duplicates for review against the original "lumpy" sources. The new split source names are prepended with the carat character (^) to the original source name and appended to it are the name of the person to whom the citation belongs followed by the fact type in parentheses and, conditionally another name for family and alternate name facts. Images, WebTags, Repositories et al are now remapped. This is highly experimental so, of course, you will not want to run it against your working database without the appropriate precautions!!! Try it and see what you get.


 * 1) May be advisable to run Delete Phantoms and Source Templates - Merge Duplicates first.
 * 2) [[file:Sources-CreateExtremelySplitFromCitations.sql]] Stop here to review the new "ultimate split" sources and citations against the originals. rev 7 Oct 2014 now handles a Source having no Master Source fields defined in its Source Template (caused a RootsMagic error)
 * 3) [[file:Sources-DeleteAfterSplit.sql]] 30 Jun 2013. Delete the "lumpy" stuff.
 * 4) Deletion of unused custom source templates yet to be done; alternatively run the Conversion of Templates procedure below on custom templates so that they are ready to be used as Ultimately Split.
 * 5) Delete Phantoms: probably advisable to run this after the split.
 * 6) After the deletion, use RootsMagic Database Tools to test integrity, rebuild indexes if integrity not OK, and compact to recover space.

One problem I have seen with the results is that the differentiation of the Master Source Name due to the appending of the Person Name and Fact Type prevents the Source Manager's AutoMerge from merging otherwise identical Master Sources. Manual merging is way too laborious so until RootsMagic gives AutoMerge options, another outboard utility is needed. Stay tuned for Sources - Merge Duplicate Masters. =Conversion of Templates= A Free Form template is readily used for extreme splitting as there is but one Source Details field, [Page], which can be left empty and the Master Source fields may be filled arbitrarily with text. There are several built-in templates which are already setup for extreme splitting as they have no Source Details fields: Artifact, archived Artifact, Family, photographed (privately held) Artifact, privately held Bible Records Broadside, original Broadside, reprint Family Bible Record Genetic Test (DNA) report Legal Document, unrecorded (family copy) Letter, Historic, private Maps, Historic Maps, Topographic Military, Muster Rolls (manuscript) Photo, Portrait, Archived (Annotated) Photo, Portrait, Private (Annotated with Provenance) Photo, Portrait, Private, scanned School Records (student transcripts) Slip Laws, Federal Slip Laws, state The remaining ~400 templates have Source Details fields that preclude them from being used for extreme splitting unless modified.

The following query moves all Source Details fields into the Master Source for all sources, thus enabling them to be used for extreme splitting. code format="sql" -- SourceTemplateExtremeSplitConvert.sql /* 2013-06-18 Tom Holden ve3meo Converts all fields in the Source Template defined as Source Details or Citation Details to Master Source fields, thus enabling the template to be used for Extreme Spliting.

WARNING - use this on unused templates; it does not convert existing citations and master sources. UPDATE SourceTemplateTable SET FieldDefs = CAST(REPLACE(CAST(FieldDefs AS TEXT), 'True', 'False') AS BLOB) WHERE 1;-- Apply constraints here, otherwise all Source Templates are modified.

-- View FieldDefs SELECT CAST(FieldDefs AS TEXT) FROM SourceTemplateTable T;-- view results code As it stands, **this query modifies all the built-in templates**, which is fine for experimentation but to be avoided in practice. **Better to create editable templates first** and then run this query with WHERE 1 changed to WHERE TemplateID > 9999. Refer to Source Templates for ways to batch import or convert to editable source templates.

=Sources without Media= Needing to find Master Sources lacking media and to move media from Source Details to Master Source as part of his conversion to Extreme Splitting, Jerry posted Query for Sources Without Media.