SQLite Tools for RootsMagic

I’ve discussed this query before, but this is my first attempt to post a real, live query in both MS Access and SQLite formats. At the present time, my To Do grid only includes 17 rows and 2 columns but I will be adding additional rows and additional columns. I should mention up front that this query does not use RM4’s To Do list feature in any way. It establishes To Do items in a different way.

The rows in the grid correspond to individuals in my RM4 database, and at the present time I have 17 individuals who are of active research interest. The list of individuals who are of active research interest will grow quickly. The list of individuals of active research interest is maintained as a Named Group in my RM4 database. If I add individuals to that named group, those individuals immediately show up in my query. I hardwired group #6 into my query rather than hardwiring the name of the group into my query because it’s easier to deal with numbers in a way that’s compatible between MS Access and SQLite than it is to deal with text strings. Indeed, my query started out by using a text string to reference the name of the group, but it was just too clumsy keeping the MS Access and the SQLite versions of the query in sync, so I switched to the group number rather than the group name.

The columns in the grid correspond to items from my own personal To Do list. These are items that are not very amenable to tools such as RM4’s own To Do list or to the automated tools provided by GenSmarts. Most of my personal To Do items have to do with getting my database in good enough shape that a narrative report produced by RM4 is as nearly camera ready as possible, and that such a report needs little or no additional editing with a word processor before publishing. The first column is labeled PrgraphOk, and if it is checked off that means that I have verified that all my notes end with the appropriate carriage returns (or not) to producing the paragraphing that I want to see in a narrative report. The second column is labeled CensusOk, and if it is checked off that means that I have verified that all my census facts print out as I wish them to do – sentence templates are what I want and the fact notes are what I want. I primarily use GenSmarts as my To Do list to be sure that I get the 1850 census for John Doe who was born in 1845 and died in 1905, for example. So my To Do item of CensusOk is related only to how the census facts print out, and to be sure that they are camera ready.

I started out with the idea of storing my PrgraphOk and my CensusOk items in an MS Access table that would be joined with my RM4 database. But I later decided that I wanted RM4 to be my one and only official record, so I’m storing the information directly in RM4. I chose to use the existing Reference Number fact. After verifying that my To Do item is done, I then add facts to RM4 such as Reference Number PrgraphOk or Reference Number CensusOk. My query is robust enough that the PrgraphOk and CensusOk items can be entered on the same Reference Number fact or on separate Reference Number facts.

The design of the query is basically (list of individuals in the group) LEFT JOIN (list of individuals with the required Reference Numbers). Essentially, group membership constitutes the To Do list and the Reference Numbers constitute the To Done list. I don’t print the Reference Numbers in any reports, nor do I export them in any GEDCOM. The Reference Number facts are just for my own internal use.

Here is the query for SQLite.

SELECT L.RecNo AS RecNo, L.Surname AS Surname, L.Given AS Given, R.PrgraphOK AS PrgraphOK, R.CensusOk AS CensusOk
 
FROM
 
         (SELECT
                  N.OwnerID AS RecNo,
                  N.Surname COLLATE NOCASE AS Surname,
                  N.Given COLLATE NOCASE AS Given
 
            FROM
                  GroupTable AS G,
                  NameTable AS N
                  WHERE N.NameType=0 AND N.OwnerID>=G.StartID AND N.OwnerID<=G.EndID AND G.GroupID=6
 
         )  AS L
 
 
           LEFT JOIN
 
 
         (SELECT
                  E.OwnerID AS RecNo,
                  MAX(CAST(E.Details AS TEXT) LIKE 'PrgraphOk') AS PrgraphOk,
                  MAX(CAST(E.Details AS TEXT) LIKE 'CensusOk') AS CensusOk
 
            FROM
                  EventTable AS E
                      INNER JOIN
                  FactTypeTable AS F ON E.EventType=F.FactTypeID
 
                  WHERE F.FactTypeID=35
                  GROUP BY E.OwnerID
 
         )  AS R ON R.RecNo = L.RecNo
 
ORDER BY Surname, Given

Here is the query for MS Access.

SELECT L.RecNo AS RecNo, LEFT(L.Surname,50) AS Surname, LEFT(L.Given,50) AS Given, R.PrgraphOK AS PrgraphOK, R.CensusOk AS CensusOk
 
FROM
 
         (SELECT
                  N.OwnerID AS RecNo,
                  N.Surname AS Surname,
                  N.Given AS Given
 
            FROM
                  GroupTable AS G,
                  NameTable AS N
                  WHERE N.NameType=0 AND N.OwnerID>=G.StartID AND N.OwnerID<=G.EndID AND G.GroupID=6
 
         )  AS L
 
 
           LEFT JOIN
 
 
         (SELECT
                  E.OwnerID AS RecNo,
                  MAX(Abs(StrConv(E.Details,64) LIKE "*PrgraphOk*")) AS PrgraphOk,
                  MAX(Abs(StrConv(E.Details,64) LIKE "*CensusOk*")) AS CensusOk
 
            FROM
                  EventTable AS E
                      INNER JOIN
                  FactTypeTable AS F ON E.EventType=F.FactTypeID
 
                  WHERE F.FactTypeID=35
                  GROUP BY E.OwnerID
 
         )  AS R ON R.RecNo = L.RecNo
 
ORDER BY Surname, Given

Here is some further discussion of the scripts.

1. MS Access does not support comments. SQLite does. At the present time, my comments therefore will be narrative such as this which is outside the SQL script. This is not good programming practice, but it feels enforced upon me by MS Access.
2. In the outer SELECT statement, the Left function is used in MS Access for the sole purpose of making the columns sortable as a part of the MS Access filtering mechanism. I believe that it is the RMNOCASE problem that makes this necessary. The results of the SQLite query are not filterable and sortable after the fact, anyway. With the SQLite approach, all filtering and sorting has to be included in the body of the SQL. But MS Access supports filtering and supporting of the results of the query.
3. SQLite requires COLLATE NOCASE for columns that are text strings in the first sub-query. This is a part of the RMNOCASE problem. If you run the inner SELECT as a free standing query rather than as sub-query, the COLLATE NOCASE is not required. I’m not sure of the proper language to describe the problem, but it’s like passing subroutine or function arguments work in some programming languages, and the RMNOCASE values from the inner SELECT cannot be passed out to the outer SELECT without first removing the RMNOCASE characteristic.
4. In the second sub-query, MS Access uses StrConv to gain access to the content of a BLOB and SQLite uses CAST to gain access to the contents of a BLOB. MS Access uses double quotes, and SQLite uses single quotes. In MS Access, the LIKE operator won’t find my character strings unless I precede and follow them with * as a wild card character, and SQLite won’t work with the * as a part of the string.
5. MS Access returns -1 for true and 0 for false on a compare. SQLite returns 1 for true and 0 for false. So with MS Access I used absolute value to convert -1 to 1.
6. The maximum function (the name of the function is MAX) is used in association with the GROUP BY operator to allow PrgraphOk and CensusOk to be specified on the same or different Reference Number facts. And in association with the fact that the two sub-queries are connected with a LEFT JOIN, the query works correctly, showing blanks for the PrgraphOk and CensusOk fields for everybody in the group even when the Reference Number fact is omitted completely.
7. I would much prefer to show blanks for “not completed” and an X for “completed”, rather than 0 and 1. But I couldn’t find any way to do so that would work for both MS Access and for SQLite. The basic problem is that MS Access doesn’t seem to support CASE or anything like CASE. There are numerous examples of CASE on the Internet for MS Access, but I couldn’t get any of them to work.
8. I hard coded the Reference Number fact as fact #35 for the same reason I hard coded my group as group #6.
9. This problem is pretty subtle, but it drove me crazy for a long time. I would have preferred not having text columns such as Surname and Given appearing in sub-queries at all, given the RMNOCASE and other problems with text columns. I would have preferred just having the text columns appearing as a part of the very original SELECT. And indeed, there is nothing in the sub-queries that needs any text columns. But keeping Surname and Given only in the main SELECT would have meant a three way JOIN (something JOIN something else JOIN still something else) rather than a two way JOIN because I would have had to JOIN the NameTable with the two sub-queries. But the MS Access way of doing JOINS of three items or more is very different than the SQLite way of doing JOINS of three items or more. Both MS Access and SQLite will only associate multiple JOINS from the left. So far, so good. But MS Access insists that the multiple JOINS include appropriate parentheses, even though there is only one way it will allow the parentheses to be placed – namely the placement of the parentheses must correspond to associating the multiple JOINS from the left. And SQLite insists that the parentheses not be there. (By associating from the left, in math the equivalent is (x+y)+z for the left and x+(y+z) for the right). If that’s all there was to it, I would probably just add and remove the parentheses as required in switching back and forth between MS Access and SQLite. But when you add and remove the parentheses to reflect the left-to-right association of the JOIN’s as being explicit with parentheses vs. implicit without the parentheses, the physical placement of the ON clauses also has to change. So I’m trying to formulate all my queries not to require more than one JOIN of two items within any particular query or sub-query. For this particular query, the only way I could think of to do so was to have text columns in one of the sub-queries that got passed back out to the main query. Oh, the joys of SQL standards (not!).

Here’s the way my report looks at the present time in MS Access. The report looks substantially the same with an SQLite manager such as SQLiteSpy.

Jerry

Added 7/29/2011

Here is a slightly modified version of the query for MS Access. It uses the IIF function to convert the 1’s and 0’s into X’s and blanks. IIF is another one of those non-standard MS Access features that make it difficult to create a query compatible between various versions of SQL. I’ve also added additional individuals to the named group for the list of individuals of active research interest, and I’ve added additional columns. I’ve been calling the columns a “to do list”, but I think that it would be more appropriate to call them a checklist.

SELECT
        L.RecNo AS RecNo,
        LEFT(L.Surname,50) AS Surname,
        LEFT(L.Given,50) AS Given,
        R.PrgraphOK AS PrgraphOK,
        R.CensusOk AS CensusOk,
        R.ObitOk AS ObitOk,
        R.SourceOk AS SourceOk,
        R.TombStoneOk AS TombStoneOk,
        R.SSDI_Ok AS SSDI_Ok
 
  FROM
        (
         SELECT
                 N.OwnerID AS RecNo,
                 N.Surname AS Surname,
                 N.Given AS Given
 
           FROM
                 GroupTable AS G,
                 NameTable AS N
                 WHERE N.NameType=0 AND N.OwnerID>=G.StartID AND N.OwnerID<=G.EndID AND G.GroupID=6
 
        )  AS L
 
                        LEFT JOIN
        (
         SELECT
                     E.OwnerID AS RecNo,
                     IIF(MAX(Abs(StrConv(E.Details,64) LIKE "*PrgraphOk*"))=1,"           X",NULL) AS PrgraphOk,
                     IIF(MAX(Abs(StrConv(E.Details,64) LIKE "*CensusOk*"))=1,"           X",NULL) AS CensusOk,
                     IIF(MAX(Abs(StrConv(E.Details,64) LIKE "*ObitOk*"))=1,"           X",NULL) AS ObitOk,
                     IIF(MAX(Abs(StrConv(E.Details,64) LIKE "*SourceOk*"))=1,"           X",NULL) AS SourceOk,
                     IIF(MAX(Abs(StrConv(E.Details,64) LIKE "*TombstoneOk*"))=1,"           X",NULL) AS TombstoneOk,
                     IIF(MAX(Abs(StrConv(E.Details,64) LIKE "*SSDI_Ok*"))=1,"           X",NULL) AS SSDI_Ok
 
           FROM
                     EventTable AS E
 
           WHERE
                     E.EventType=35
           GROUP BY
                     E.OwnerID
 
        )  AS R ON L.RecNo = R.RecNo
 
  ORDER BY L.Surname, L.Given

Here’s a screen shot of the query when it is run against my live RM4 database.