Home Page list
Query for Sources Without Media #sources #media
I haven't posted here in rather a long time. Here is a little query I wrote to identify Sources without media. By "Sources", I mean the same thing that RM calls Master Sources, but I think that they should just be called Sources along with their associated Source Details in common with having Places and Place Details. We do not need to speak of Master Places.
This query is a part of my project to move all sourcing information into the Source area of RM rather than storing some of the sourcing information in the Source Details area of RM. As described on the RM Forums in the thread "Adventures in Extreme Splitting", I think the tools for managing Source Details are completely inadequate, so I just decided to quit using them. The only way to quit using them is to move all sourcing information into the Source area of RM. And as a part of this project, I'm trying to have at least one media file associated with every Source. This query therefore identifies Sources without media.
I doubt that this query will be of much value to anybody but me, but I think it would be pretty easy to adapt it into being a "Source Details without media" query. Also, it suggests some ways to display information about media files that are totally unavailable within RM itself.
While I was at it, I decided to do a query to display the Master Source name along with the associated footnote sentence. I couldn't figure out how to do it with SQLiteSpy so I did it with Access. I need to study further one of Tom's pages on parsing XML. It might be much easier with his technique than with what I was trying to do. Also, it only worked for me because I was using the built-in Free Form source template, so all I had to do was to look for the <Footnote> tag in the XML.
The reason I had to use Access is that the INSTR function is not yet supported in SQLiteSpy, which is my SQLite manager of choice. Tom documented some alternative mangers that do support INSTR. But when I tried to download them, I got into a messy situation where the download sites were trying to download all manner of "annoy-ware" along with the SQLite manager. I was very uncomfortable with what was going on, so I abandoned the downloads and switched to Access instead for this particular query. In case this might help anybody despite that fact that it's Access, here it is.
SELECT Left(S.Footnote,S.Footnote_Len) AS Footnote, S.Name
FROM (SELECT SS.Name, SS.Footnote, InStr(1,SS.Footnote,"</Value></Field><Field>")-1 AS Footnote_Len
FROM (SELECT SSS.Name, Mid(SSS.Fields,SSS.Footnote_Begin_Less22+22) AS Footnote
FROM (SELECT SSSS.Name, SSSS.Fields, InStr(1,SSSS.Fields,"Footnote") AS Footnote_Begin_Less22
FROM (SELECT Left(SSSSS.Name,Len(SSSSS.Name)) AS Name, StrConv(SSSSS.Fields,64) AS Fields
FROM SourceTable AS SSSSS) AS SSSS) AS SSS) AS SS) AS S
ORDER BY footnote
The StrConv function is sort of the Access version of CAST. The argument 64 is not a length. Rather, it's a flag of what type of conversion is required. InStr is a string searching function which I believe is identical to INSTR in SQLite. The fact that InStr would work in Access is why I used Access.
Here is a bit of what the report looks like. I like the report because it makes it very easy to eyeball various footnote sentences for consistency. This is a genealogical comment rather than an SQLite one, but I'm not very happy with some aspects of the footnote sentences. For example, I often don't have the marriage book number or page number - only the license number. But that's because Knox County does not give public access to the marriage books and the information I get from them usually only has the license number. But the license number plus the year actually is sufficient to uniquely identify the record.
Just as a reminder: because of my Extreme Source Splitting strategy, 100% of the footnote sentence may be found in the Master Source area of RM, which is the whole point of this exercise. No matter how many times the Master Source is cited, I can make any needed edits or corrections to the citation just one time and the change takes place for all instances of the citation.
And as a further reminder (and as documented in the sources_without_media.sql query above), every one of these Master Sources and footnote sentences will be supported by an appropriate collection of media files - and I only have to process each media file one time and it immediately is linked to every citation where the respective Master Source is used, again which is the whole point of this exercise.
Master Source Name Footnote Sentence
Armed with the new version of SQLiteSpy, I rewrote the footnote query directly in SQLite rather than it having to be in Access. The query is still dependent upon the footnote having been generated by the built-in FreeForm template. Moving forward, I would like to be able to switch over to real Source Templates that place all the data into the Master Source. But if I do, I would still very much like to be able to run something equivalent to this query to display my footnote
sentences in the context of other footnote sentences.
I'm cognizant of the fact this query could have been written without as many levels of nested subqueries, but the subqueries made the query extremely easy to write. Several manipulations have to be performed on the XML to coerce it into being the footnote sentence, and each level of subquery performs one of the needed manipulations.
SELECT S.Name AS Name, SUBSTR(S.Footnote,1,S.Footnote_Len) AS Footnote
FROM (SELECT SS.Name, SS.Footnote, INSTR(SS.Footnote,'</Value></Field><Field>')-1 AS Footnote_Len
FROM (SELECT SSS.Name, SUBSTR(SSS.Fields,SSS.Footnote_Begin) AS Footnote
FROM (SELECT SSSS.Name, SSSS.Fields, INSTR(SSSS.Fields,'Footnote')+22 AS Footnote_Begin
FROM (SELECT SSSSS.Name AS NAME, CAST(SSSSS.Fields AS TEXT) AS Fields
FROM SourceTable AS SSSSS) AS SSSS) AS SSS) AS SS) AS S
ORDER BY Footnote