Contents
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.
Jerry
Added 06/18/2013
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 footnoteThe 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.
Jerry
Master Source Name Footnote Sentence
Added 6/19/2013
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.
Jerry
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
thejerrybryan
19 June 2013 01:22:18
Thanks for the update. The newest version appears to be 1.9.3. However, I seem to have created a problem for myself. Namely, there is something wrong with RMNOCASE and SourceTable. Something so simple as “SELECT SourceID from SourceTable” throws the RMNOCASE error, and SourceID is a numeric field. I can’t even double click on the SourceTable just to view the whole thing. It appears to be the case that SourceTable is the only table with this problem.
Jerry
thejerrybryan
19 June 2013 01:25:24
SELECT * FROM SourceTable does work, however.
ve3meo
19 June 2013 02:27:25
This does not make sense – something is royally screwed up. Even a SELECT of a field defined with the RMNOCASE collation should not throw the error unless it is sorted. And under Options > Options > Extensions, do you have RMNOCASE_fake-SQLiteSpy.dll? If so, then there should be no such errors on any RMNOCASE defined field. In SQLiteSpy, run REINDEX and see if that clears the problem. When you return to RootsMagic, use its Rebuild Indexes tool before resuming normal operations.
Tom
thejerrybryan
19 June 2013 03:17:48
I hadn’t applied the fake RMNOCASE since I upgraded to 1.9.3. I have now done so, and the problem is solved. But as you say, there should be no error unless something is sorted. It’s almost like 1.9.3 is sorting something behind the scenes that we don’t know about. And the error is thrown even if I explicitly override the collation with NOCASE.
Also, I was wrong about it being just SourceTable. The error also occurs in AddressTable, MediaLinkTable, ResearchTable, and SourceTemplateTable. These are tables I never run queries against, so the problem could have been there forever. And I didn’t even try to run against SourceTable after I realized that INSTR wouldn’t work with my current version of SQLiteSpy.
If you have 1.9.3, you might try temporarily taking away the fake RMNOCASE collation to see if you also have the problem. Indeed, if you widen the window pane that contains the list of tables on the left side of the screen, the tables in question all have an error message beside them. You don’t even have to run a query or double click one of the tables to see the problem.
After removing the fake RMNOCASE collation, you will have to shut down SQLiteSpy and restart it to see the effect, and vice versa when you put it back.
Jerry
thejerrybryan
19 June 2013 03:43:58
On a lark, I restored 1.9.0. It does not have the RMNOCASE problem when the fake RMNOCASE collation is not installed. Which is to say you can access all the tables if you don’t sort. Without the fake RMNOCASE collation, you obviously have to do something to deal with RMNOCASE if you do sort. But the problem I’m describing was definitely introduced sometime between 1.9.0 and 1.9.3.
Jerry
ve3meo
19 June 2013 03:56:06
Too late for me now to do the testing you suggest. Between SQLiteSpy 1.9.0 and 1.9.3, SQLite itself has changed and I would suspect the problem or effect of a missing collation lies there. That should also manifest itself in the DOS executable of SQLite.
ve3meo
19 June 2013 14:13:26
The behaviour you describe is now inherent in SQLite itself (I see it in the Windows command line shell) and is not the fault of SQLiteSpy. ‘Why’ is a mystery but it may have something to do with query optimisation or auto-indexing. Or maybe it is an unwarranted bug.
thejerrybryan
19 June 2013 18:27:12
So I guess the fake RMNOCASE collation is now the only RMNOCASE solution for those tables that have the problem I’m describing. Even explicitly overriding the collation in a query does not take care of the problem. So I’ll probably just go with the fake RMNOCASE from now on and not even try to influence collation explicitly in my queries.
Jerry
ve3meo
19 June 2013 18:42:24
And don’t update your odbc connection between Access and SQLite databases. Even though a later version might resolve some issues, maybe it will be unusable. I’m now pretty sure this problem has to do with SQLite’s query optimiser trying to use an Index that is on a field having the RMNOCASE collation. I don’t see any way of turning off the optimiser. Dropping the index might clear that problem but introduce others such as loss of speed and the need to regenerate the index for ongoing use by RootsMagic.
Tom
thejerrybryan
19 June 2013 19:58:07
I had wondered what might happen with ODBC and Access. So far, nothing bad has happened to me.
It is the case that Access suffers from the RMNOCASE problem just like SQLiteSpy does. There not being a fake RMNOCASE solution available to Access, the trick that has worked for me 100% of the time is to substring out any RMNOCASE field that I need to sort on, and do the “substring out” thing in a subquery that passes the substring up to the next higher level of subquery. The substring operation I perform makes a “substring” of the whole field without dropping any characters.
There is more information about this issue with RMNOCASE and Access elsewhere in the Wiki. Because of the nature of Access, it’s often the case that your query does not actually need to sort directly on one of the RMNOCASE fields. But you will still want to use a trick to make the field sortable anyway. That’s because of the visual nature of Access where you can click on the column headers of the result of a query and sort by that column even though the column wasn’t sorted in the original query. The ability to sort in this fashion is lost unless you suppress the RMNOCASE problem.
Jerry
ve3meo
21 June 2013 03:28:46
I rejoined the sqlite-users list and posted the problem. I have had replies from both D. Richard Hipp, lead developer of SQLite, and Ralf Junker, developer of SQLiteSpy. I have a better understanding of what’s going on, a partial work-around but concluding that any recent SQLite manager must have at least the fake RMNOCASE collation for it to be able to use any of the queries and scripts developed for the RootsMagic4+ databases. Now if only Bruce would reveal the real RMNOCASE (are you reading this, Bruce?). The discussion begins at [sqlite] False Error ‘no such collation sequence’ http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2013-June/046965.html
Tom
ve3meo
21 June 2013 03:36:20
The current version of the ODBC driver from http://www.ch-werner.de/sqliteodbc/ wraps the (current) SQLIte 3.7.17 library. Since the command line version sqlite3.exe is built from 3.7.17 and exhibits the same problems as SQLiteSpy 1.9.3, built from 3.7.16.1, I would not be surprised if the current ODBC introduced some sort of problem in your MS Access interface.
Tom