SourceTemplate Query Request from RM's Online Community
There was a request from the online community for a query to determine where various source templates were used. I think the attached query will about do it. It was slightly trickier than it might otherwise have been because I also picked up the usage of the free form template and the free form template is not in SourceTemplateTable.
Uploaded files:There have been a number of prior scripts published here starting soon after the release of RM4 that provide information about the usage of source templates. Those on the page Source List Query all include the Template name, TemplateID number, whether in-built or custom and were updated in 2021 to RM8 and remain compatible with RM9. One reports at the Master Source Level while the other dives down to where each Citation is used which is what @thejerrybryan's new script does, more succinctly. It's useful to see a screenshot from each to visualise the breadth and depth of the information that each returns.
Each one was run on SqliteSpy on the same database. No attempt was made to reorder the results so that the same sources are displayed; rather a representative slice of the default order was selected to show a few different types.
- sources_by_template.sql-Spy.jpg @thejerrybryan 2024-03-15
- SourceList-RM8.sql-Spy.jpg @ve3meo 2021 update;
- MasterSources-RM8.sql-Spy.jpg @patjones 2021 update
An omission from #2 that I just noticed is the absence of the Citation Name column. That was new in RM8 and while the update dealt with the splitting of the CitationTable into two tables, I missed the inclusion of that column. Easily remedied but it widens an already very wide result set. Jerry's narrower result set does include it and that may be critical for certain tasks.
Uploaded files:
If I might offer a minor critique of my own query: it contains an unnecessary join to NameTable. There are two joins to NameTable. The first join is not necessary and the second one is.
The first join to NameTable has to do with citations to a person. The subquery presently reads as follows.
SELECT CL2.LinkID, N.OwnerID AS RIN1, NULL AS RIN2, NULL AS Fact
FROM CitationLinkTable AS CL2
JOIN NameTable AS N ON CL2.OwnerType = 0 AND N.OwnerID = CL2.OwnerID
But because N.OwnerID = CL2.OwnerID, it could just as well read this way without any join. And indeed if any join was needed at all, it more logically would be to PersonTable than to NameTable.
SELECT CL2.LinkID, CL2.OwnerID AS RIN1, NULL AS RIN2, NULL AS Fact
FROM CitationLinkTable AS CL2 WHERE CL2.OwnerType = 0
The second join to NameTable has to do with citations to names, so the JOIN to the NameTable is totally necessary.