Source List Query #sources #sourcetemplates #citations #xml #date

Description

Here are the most advanced and useful SQLite queries for reviewing citations in your RootsMagic 4-8 database. They provide some of the same info that you would get in the RootsMagic Source List report and then some you don’t… You have the advantage of sorting and filtering results in ways you cannot within RM and then finding the person and fact using that source in RM. And they provide the key ID’s with which you can find specific table records and possibly edit fields directly using SQLite (if you know what you are doing). What is yet missing is expansion of the data into sentences using the sentence templates; this requires a high level programming language and is unlikely to be added.

Two Queries

Two separate queries are needed because basic SQLite managers can only present results in tabular format and screen width is insufficient to show all the fields that make up a fully detailed RM4 Source List report. The two queries provide, respectively,:

  1. List of all the citations (relatively many) with details against the names of the Master Sources (RM4-8 as of 2022-02-21)
  2. List of all the Master Sources (relatively few) with their details but not the Source Details of each citation (RM4-8 as of 2021-03-03)

Using a SQLite manager that supports two or more queries in separate windows or tabs (e.g. SQLiteSpy), it is easy to have both queries’ results available by toggling between windows. To view the properties of media items, use the Media List Query in a third window.

1. Source List – Citations

Download SourceList.sql RMtrix_tiny_check.png for RM4 to #RM7
2011-11-06 now reports Free Form and orphaned citations; count of media items linked to citation; improved format of CitFields; compatability with managers other than SQLiteSpy.
2011-11-18 now outputs Source Fields as per MasterSources.sql plus Event Place and Event Site (Place Detail)

Download SourceList-RM8.sql #rm8 version of SourceList.sql 2022-02-21

This complex query was built on Romer’s much earlier Source Template List – Query. In so doing, I discovered errors and inconsistencies in and among it and my All Citations – Query and All Citations & Dupes Count – Query. Hopefully, these are now resolved with revisions to all of them. Major additions include date decoding from Date Decoder and the stripping of XML tags from the FIELDS field pioneered in Source Detail View (Parsing XML). The result is a very comprehensive and readable spreadsheet. Here are two examples of output displays from two different SQLite managers, a different set of columns selected for display:

SourceListQueryScreenShotExpert.png
From the latest version of the query, as displayed by SQLite Expert. Note the wrapping of text.

SQLite Expert requires that you comment out the ORDER BY clause in order to sort on a results column; it adds an ORDER BY clause and re-runs the query. It has excellent filtering tools but one can add one’s own WHERE clause to filter results.

SourceListQueryScreenShot.png
Screenshot of results of the query from SQLiteSpy

At the bottom of the screen, you see the content of the highlighted cell. Clicking on any column heading will sort on that one column. The order displayed in this screenshot is defined in the query which can be readily changed to suit your particular requirements. This one is sorted on Source Name, Cit Text, Cit Comment, Person 1, Cited by, EventDate, Person 2 and would be useful for reviewing citations of a source having identical texts.

Column Definitions

ColumnDescriptionEditable
Source Namename of the Master Source; primary sort field in this queryN
Source Fieldsthe field names and values entered in the Master SourceY
Cit Fieldsthe field names and values entered in the Source Details screen for a citationY
Qualcitation quality code (decoding would take up too much screen space but you’ll get the drift)Y
Cit TextSource Details textY
Cit CommentSource Details commentY
Cited byperson, family or fact type that cited the sourceN
EventDatefact/event date (helps to pick out in the Edit Person screen which of two or more facts of the same type cited the source)N
Event PlacePlace of the fact/eventN
Event SitePlace Detail of the fact/eventN
RIN 1record number of person whose fact cited the sourceN
Person 1name of person whose fact cited the sourceN
MRINFamilyID of FamilyTable, the Marriage Record Number that is invisible in RM4N
RIN 2record number of spouseN
Person 2name of spouseN
CitIDCitationID of CitationTableN
SrcIDSourceID of SourceTableN
Templatename of Source TemplateN
TpltIDTemplateID of SourceTemplateTableN
Tplt Type“OEM”=Built-in Source Template (TemplateID<10000); “USR”=user-defined Source Template (TemplateID>9999)N

2. Master Sources

Download MasterSources.sql RMtrix_tiny_check.png #rm7
2011-11-06 now reports Free Form and orphaned citations; count of media items linked to master source; improved format of SrcFields

2017-03-21 show unused Master Sources and correct citation count (was offset by 1)

2020-06-21 the offset ‘corrected’ above was for unused Master Sources and threw all others off by 1; this rev works for both used and unused Master Sources.

Download MasterSources-RM8.sql #rm8
2021-03-03 Converted to work with both RM7 and RM8 format – PJ Feb2021

MasterSourcesScreenShot.png

Column Definitions

ColumnDescriptionEditable
SrcIDSourceID of SourceTableN
Source Namename of the Master Source; primary sort field in this queryN
RefNumberMaster Source File #Y
SrcFieldsthe field names and values entered in the Master Source screenY
ActualTextMaster Source textY
CommentsMaster Source commentsY
IsPrivate1 if Master Source marked private, else 0 but currently unused (?)Y
Citationsnumber of times Master Source is citedN
Templatename of Source TemplateN
TpltIDTemplateID of SourceTemplateTableN

Discussions & comments from Wikispaces site


thejerrybryan

The Queries Don’t Run on My System

thejerrybryan
05 November 2011 23:09:17

Neither query will run. There are no error messages. It’s just that there are no results at all – almost as if my database were empty. But I can hand code very simple queries that demonstrate my chosen SQL manager SQLiteSpy is working ok and that my database is not empty.

Any ideas?

Thanks,
Jerry


thejerrybryan

thejerrybryan
06 November 2011 00:10:08

I haven’t looked at the first query yet, but the second query won’t run because all I have are Free Form templates. Hence, the following:

FROM SourceTemplateTable
INNER JOIN SourceTable ON
SourceTemplateTable.TemplateID = SourceTable.TemplateID

yields a NULL result because the Free Form template appears not to be in the SourceTemplateTable.

Jerry

DNA Test results list #dna #xml #replace

RootsMagic 4 allows one to add a DNA Test fact to a person and enter the results of two types of test. However, there is no report available for the results making it difficult to verify that all data have been accurately transcribed. The results are stored in the EventTable Details column in XML format, making a direct query of this field difficult to read. These two queries, one for each type of test, produce more easily read results by stripping out much of the XML tagging and formatting the results in list format, tab-delimited for the Y-STR query which has multiple columns.

DNA_mtDNA_locationslist.sql
DNA_Y-STR_markerslist.sql

Sample direct query of a Y-STR DNA Test fact:

PersonDNAtest
Surname, James Gordon<?xml version=”1.0″ encoding=”UTF-8″?>
<dnatest><type>Y-STR</type><laboratory code=”DNAConsultants”>DNA Consultants</laboratory><description>TestDesc</description><id>Test/Kit ID</id><haplogroup confirmed=”true”>Haplogroup</haplogroup><markers><marker code=”DYS385a”><name>DYS385a</name><value>5</value></marker><marker code=”DYS385b”><name>DYS385b</name><value>10.2</value></marker><marker code=”DYS388″><name>DYS388</name><value>6</value></marker><marker code=”DYS389i”><name>DYS389i</name><value>11</value></marker><marker code=”DYS389ii”><name>DYS389ii</name><value>21</value></marker><marker code=”DYS390″><name>DYS390</name><value>17</value></marker><marker code=”DYS391″><name>DYS391</name><value>4</value></marker><marker code=”DYS392″><name>DYS392</name><value>8</value></marker><marker code=”DYS393″><name>DYS393</name><value>6</value></marker><marker code=”DYS19″><name>DYS19</name><value>12-15</value></marker><marker code=”DYS426″><name>DYS426</name><value>6.2</value></marker><marker code=”DYS437″><name>DYS437</name><value>14</value></marker><marker code=”DYS438″><name>DYS438</name><value>6</value></marker><marker code=”DYS439″><name>DYS439</name><value>10</value></marker><marker code=”DYS441″><name>DYS441</name><value>8</value></marker><marker code=”DYS442″><name>DYS442</name><value>13</value></marker><marker code=”DYS444″><name>DYS444</name><value>8</value></marker><marker code=”DYS445″><name>DYS445</name><value>7</value></marker><marker code=”DYS446″><name>DYS446</name><value>8</value></marker><marker code=”DYS447″><name>DYS447</name><value>18</value></marker><marker code=”DYS448″><name>DYS448</name><value>11</value></marker><marker code=”DYS449″><name>DYS449</name><value>23</value></marker><marker code=”DYS452″><name>DYS452</name><value>7</value></marker><marker code=”DYS454″><name>DYS454</name><value>9</value></marker><marker code=”DYS455″><name>DYS455</name><value>4</value></marker><marker code=”DYS456″><name>DYS456</name><value>16</value></marker><marker code=”DYS458″><name>DYS458</name><value>10</value></marker><marker code=”DYS459a”><name>DYS459a</name><value>5</value></marker><marker code=”DYS459b”><name>DYS459b</name><value>2</value></marker><marker code=”DYS460″><name>DYS460</name><value>10.1</value></marker><marker code=”DYS461″><name>DYS461</name><value>11-12</value></marker><marker code=”DYS462″><name>DYS462</name><value>10</value></marker><marker code=”DYS463″><name>DYS463</name><value>5</value></marker><marker code=”DYS464a”><name>DYS464a</name><value>10.3</value></marker><marker code=”DYS464b”><name>DYS464b</name><value>10.1</value></marker><marker code=”DYS464c”><name>DYS464c</name><value>9.3</value></marker><marker code=”DYS464d”><name>DYS464d</name><value>10.3</value></marker><marker code=”GAAT1B07″><name>GGAAT1B07</name><value>14</value></marker><marker code=”YCAIIa”><name>YCAIIa</name><value>16.1</value></marker><marker code=”YCAIIb”><name>YCAIIb</name><value>16.1</value></marker><marker code=”GATAA10″><name>GATA-A10</name><value>14</value></marker><marker code=”DYS635″><name>DYS635</name><value>21-22</value></marker><marker code=”GATAH4″><name>GATA-H4</name><value>12</value></marker><marker code=”DYS465X”><name>DYS464X</name><value>4</value></marker><marker code=”DYS464f”><name>DYS464f</name><value>10.3</value></marker><marker code=”DYS481″><name>DYS481</name><value>22</value></marker><marker code=”DYS485″><name>DYS485</name><value>15</value></marker><marker code=”DYS487″><name>DYS487</name><value>12</value></marker></markers></dnatest>

Above processed by DNA_Y-STR_markerslist query:

PersonDNAtest
Surname, James Gordon“<?xml version=”1.0″ encoding=”UTF-8″?>
<dnatest><type>Y-STR</type><laboratory code=”DNAConsultants”>DNA Consultants</laboratory><description>TestDesc</description><id>Test/Kit ID</id><haplogroup confirmed=”true”>Haplogroup</haplogroup>
<marker code=”DYS385a”> DYS385a 5
<marker code=”DYS385b”> DYS385b 10.2
<marker code=”DYS388″> DYS388 6
<marker code=”DYS389i”> DYS389i 11
<marker code=”DYS389ii”> DYS389ii 21
<marker code=”DYS390″> DYS390 17
<marker code=”DYS391″> DYS391 4
<marker code=”DYS392″> DYS392 8
<marker code=”DYS393″> DYS393 6
<marker code=”DYS19″> DYS19 12-15
<marker code=”DYS426″> DYS426 6.2
<marker code=”DYS437″> DYS437 14
<marker code=”DYS438″> DYS438 6
<marker code=”DYS439″> DYS439 10
<marker code=”DYS441″> DYS441 8
<marker code=”DYS442″> DYS442 13
<marker code=”DYS444″> DYS444 8
<marker code=”DYS445″> DYS445 7
<marker code=”DYS446″> DYS446 8
<marker code=”DYS447″> DYS447 18
<marker code=”DYS448″> DYS448 11
<marker code=”DYS449″> DYS449 23
<marker code=”DYS452″> DYS452 7
<marker code=”DYS454″> DYS454 9
<marker code=”DYS455″> DYS455 4
<marker code=”DYS456″> DYS456 16
<marker code=”DYS458″> DYS458 10
<marker code=”DYS459a”> DYS459a 5
<marker code=”DYS459b”> DYS459b 2
<marker code=”DYS460″> DYS460 10.1
<marker code=”DYS461″> DYS461 11-12
<marker code=”DYS462″> DYS462 10
<marker code=”DYS463″> DYS463 5
<marker code=”DYS464a”> DYS464a 10.3
<marker code=”DYS464b”> DYS464b 10.1
<marker code=”DYS464c”> DYS464c 9.3
<marker code=”DYS464d”> DYS464d 10.3
<marker code=”GAAT1B07″> GGAAT1B07 14
<marker code=”YCAIIa”> YCAIIa 16.1
<marker code=”YCAIIb”> YCAIIb 16.1
<marker code=”GATAA10″> GATA-A10 14
<marker code=”DYS635″> DYS635 21-22
<marker code=”GATAH4″> GATA-H4 12
<marker code=”DYS465X”> DYS464X 4
<marker code=”DYS464f”> DYS464f 10.3
<marker code=”DYS481″> DYS481 22
<marker code=”DYS485″> DYS485 15
<marker code=”DYS487″> DYS487 12
</markers></dnatest>

Extract of above pasted into PSPAD and then into text-formatted cells in Excel 2010:
DNA_Y-STR_markerslist_in_Excel.png

Discussions & comments from Wikispaces site


ve3meo

Use a XML Viewer

ve3meo
03 September 2018 20:36:11

ve3meo Jun 18, 2011

Here’s another way to view the XML data. Copy the Details cell contents to a text file and save it with the extension .XML; then open it with Internet Explorer or some other XML viewer for a color-coded and formatted view that is a big readability improvement.

Inline comments


ve3meo

Comment: Here’s another way to view the XML da…

ve3meo
18 June 2011 19:22:00

Here’s another way to view the XML data. Copy the Details cell contents to a text file and save it with the extension .XML; then open it with Internet Explorer or some other XML viewer for a color-coded and formatted view that is a big readability improvement.

Source Detail View (Parsing XML) #citations #xml

Contents

    This page was stimulated by a question Jerry Bryan posed in the discussion on the Submit Your Problem page about finding and viewing the value of the Page field for a Free Form template. That led to a series of SQLite queries culminating in this one that I thought warranted a page for further discussion and development. It reports Master Source Names and their citations showing the field names and values entered via RootsMagic 4’s Edit Source window in the green area for Source Detail.

    The challenge to viewing these Source Details and other like fields is that they are stored in a single cell for each citation, in hexadecimally coded, XML-like text. In raw form, it looks like this, using SQLiteSpy:

    SourceNameFields
    Births: Scotland Birth & Baptisms; LDS index (ct NFS, o/l db)Blob

    A BLOB is the type name for a field containing hexadecimal vales. One has to convert the UTF-8 code to text using the CAST function to convert this BLOB field, strangely called FIELDS, to a text field we’ll call SourceDetail:

    SourceNameSourceDetail
    Births: Scotland Birth & Baptisms; LDS index (ct NFS, o/l db)<?xml version=”1.0″ encoding=”UTF-8″?>
    <Root><Fields><Field><Name>AccessType</Name><Value/></Field><Field><Name>AccessDate</Name><Value>4 March 2011</Value></Field><Field><Name>ItemOfInterest</Name><Value>Thomas Bowman entry, birth (25 Sep 1864), Dundee`|“|` Thomas Bowman bir. (1864) </Value></Field><Field><Name>SubmitData</Name><Value>C11282-1, Scotland-ODM, Source Film Number: 6035516</Value></Field></Fields></Root>

    That’s better but a forest of XML tags obscures the values of interest. The following example code uses the SUBSTR function to cut off the top and tail and nested REPLACE functions to strip out the XML tags and replace with punctuation. To keep the example simple, we won’t go into bringing out the persons, couples, and facts that cite these sources.

    -- Citations of all Sources with Source Names and Source Detail fieldnames and values.
    SELECT SourceName,
     REPLACE(
      REPLACE(
       REPLACE(
        REPLACE(
         REPLACE(
          REPLACE(PageValue, '</Name>', ': '),
         '<Field><Name>', '; '),
        '</Value>',''),
       '<Value>',''),
      '<Value/>',''),
     '</Field>','')
     AS SourceDetail
    FROM
     (
      SELECT S.Name COLLATE NOCASE AS SourceName, SUBSTR(CAST(C.FIELDS AS TEXT),68, LENGTH(CAST(C.FIELDS AS TEXT))-100) AS PageValue
      FROM CitationTable C LEFT JOIN SourceTable S USING (SourceID)
      )
    ORDER BY SourceName
    ;

    Here is some sample output:

    SourceNameSourceDetail
    Births: Scotland Birth & Baptisms; LDS index (ct NFS, o/l db)AccessType: ; AccessDate: 4 March 2011; ItemOfInterest: Thomas Bowman entry, birth (25 Sep 1864), Dundee`|“|` Thomas Bowman bir. (1864) ; SubmitData: C11282-1, Scotland-ODM, Source Film Number: 6035516

    Under SourceDetail, you can see the field name (always a one word concatenation) followed by a colon, space, field value and semi-colon, as substituted by the REPLACE() commands. (RM uses “||” to separate a long form from an abbreviation – these were manually quoted above because WikiEditor interprets it in a table as the start of a new cell.)

    The same principles could be applied to view the Master Source field names and values or anywhere else RM uses the XML-like format.

     


    Playing around with the Replace characters, I came up with a format that displays rather nicely in both a text editor and in Excel:

    Citations-SourceDetailExcel.png
    SQLiteSpy adds the quotes around the revised SourceDetail result, probably because of the embedded Carriage Returns. If you copy from SQLiteSpy and paste directly to Excel, you lose the control codes. Instead I paste to PSPad (Notepad should also work), copy from there to Excel, and then invoke its Text Import Wizard to delimit on Tabs with ” as text qualifier. The Text Import Wizard is not available for a direct paste to Excel from SQLiteSpy.

    Here’s the revised query:

    -- Source Names and Source Detail fieldnames and values for Citations of all Sources, in a columnar style
    SELECT SourceName||CAST (X'0D' AS TEXT) AS SourceName,
     REPLACE(
      REPLACE(
       REPLACE(
        REPLACE(
         REPLACE(
          REPLACE(PageValue, '</Name>', CAST (X'09' AS TEXT)),
         '<Field><Name>', CAST (X'09' AS TEXT)),
        '</Value>',''),
       '<Value>',''),
      '<Value/>',''),
     '</Field>',CAST (X'0D' AS TEXT))
     AS SourceDetail
    FROM
     (
      SELECT S.Name COLLATE NOCASE AS SourceName, SUBSTR(CAST(C.FIELDS AS TEXT),55, LENGTH(CAST(C.FIELDS AS TEXT))-87) AS PageValue
      FROM CitationTable C LEFT JOIN SourceTable S USING (SourceID)
      )
    ORDER BY SourceName
    ;
    

    I tried this query with three other current versions of software:

    1. SQLite Developer 3.8.3.496: results visible on screen, not as nice as SQLiteSpy; can directly copy/paste to Excel (no added quotes) and invoke Text Import Wizard, thus saving the intermediate paste/copy needed by Spy but first SourceDetail field is preceded with two tabs instead of one as programmed causing it to start in third column while all subsequent ones start in the intended second column. Simply changing the SUBSTR parameters from 55 to 68 and -87 to -100 solves that problem. So I prefer Spy for direct viewing of results – selecting a cell of results shows the full formatted result in the bottom-most window; no such window exists in Developer – the cell expands to the full width but the data is displayed as one row. I prefer Developer for the quality and efficiency of its export to Excel and a text editor (no added quotes and steps).
    2. SQLite2009Pro 3.7.6.3: results in SourceDetail column not visible on screen and improperly exported to Excel.
    3. SQLiteStudio 2.0.12: results in SourceDetail column not visible on screen and improperly exported to Excel.
    4. SQLite Expert Personal 3.3.28.2158: best on-screen display of results; copy/paste to Excel allows invoking Text Import Wizard, required to preserve tab and carriage returns but one tab seems to be lost. Perhaps query can be tuned.