-- Sources-CreateExtremelySplitFromCitations.sql
/*
A work in progress, exploring the batch creation of Extremely Split sources
and citations thereof from existing sources and citations
2013-06-18 Tom Holden ve3meo
2013-06-27 rev now converts Free Form lumped sources.
2013-06-28 rev now maps Detail Text webtags from lumped sources to Master Text webtags for newly split sources
2013-06-29 rev now maps images from lumped Master and Detail to newly split Master; repositories copied;
exception process to prevent reconversion of already split sources and templates.
2013-07-27 rev adds person name and fact type to Master Source Name for improved granularity.
2013-09-03 rev accommodates a null spouse that would previously null the new source name;
revised test for Family vs Indiv, perhaps needlessly but further testing warranted to cover all combinations
2014-10-07 rev to handle a Source having no fields defined in the Source Template for the Master Source
A heavy duty SQLite procedure that takes considerable time to complete. Optimisation
for speed yet to be done.
Deletion of lumped sources and their citations yet to be done; also for
unused custom source templates. May need to clean out garbage before running
the main script as well as after - Delete Phantoms query?
*/
-- First make a list of already ultimately split source templates
DROP TABLE IF EXISTS xSplitSourceTemplateExceptTable;
CREATE TABLE IF NOT EXISTS xSplitSourceTemplateExceptTable
AS
SELECT TemplateID FROM SourceTemplateTable
WHERE CAST(FieldDefs AS TEXT) NOT LIKE '%True%'
;
-- Make a list of already ultimately split sources based on no values in CitationTable.Fields
DROP TABLE IF EXISTS xSplitSourceExceptTable;
CREATE TABLE IF NOT EXISTS xSplitSourceExceptTable
AS
SELECT SourceID FROM CitationTable C
EXCEPT
SELECT SourceID FROM CitationTable C WHERE CAST(C.Fields AS TEXT) LIKE '%%'
;
-- 2014-10-07
DROP VIEW IF EXISTS SourceFieldsView
;
-- cast SourceTable.Fields to Text and replace the empty tag with the normally non-empty
-- pair into a view for the subsequent building of xSplitSourceTable 2014-10-07
CREATE TEMP VIEW SourceFieldsView
AS SELECT SourceID, REPLACE(CAST(Fields AS TEXT), '', '') AS FieldsTxt
FROM SourceTable
;
-- Create table of new Extreme Split Master Sources from Citations of existing Sources
DROP TABLE
IF EXISTS xSplitSourceTable;
CREATE TABLE
IF NOT EXISTS xSplitSourceTable AS
SELECT C.CitationID AS OldCitationID
,NULL AS NewSourceID
,S.SourceID AS SourceID
,'^' || S.NAME AS NAME -- needs more granular identifier
,CASE
WHEN S.RefNumber NOT LIKE '' AND C.RefNumber NOT LIKE ''
THEN S.RefNumber || ', ' || C.RefNumber
ELSE S.RefNumber || C.RefNumber
END
AS RefNumber --citation ref number concatenated to source refnumber
,CASE
WHEN S.ActualText NOT LIKE '' AND C.ActualText NOT LIKE ''
THEN S.ActualText || ' || ' || C.ActualText
ELSE S.ActualText || C.ActualText
END
AS ActualText -- citation text concatenated to source text
,CASE
WHEN S.Comments NOT LIKE '' AND C.Comments NOT LIKE ''
THEN S.Comments || ' || ' || C.Comments
ELSE S.Comments || C.Comments
END
AS Comments -- citation comments concatenated to source comments
,S.IsPrivate OR C.IsPrivate AS IsPrivate -- if either is private then the new Source is too
,S.TemplateID AS TemplateID -- points to existing TemplateID for now
,SUBSTR(SFV.FieldsTxt, 1, INSTR(SFV.FieldsTxt, '') - 1) || SUBSTR(CAST(C.Fields AS TEXT), INSTR(CAST(C.Fields AS TEXT), '') + 8)
AS FieldsTxt -- citation fields concatenated to source fields (CAST as BLOB on INSERT into SourceTable)
FROM CitationTable C
INNER JOIN SourceTable S USING (SourceID)
NATURAL JOIN SourceFieldsView SFV --2014-10-07
WHERE S.TemplateID NOT IN
(SELECT TemplateID FROM xSplitSourceTemplateExceptTable)
OR C.SourceID NOT IN
(SELECT SourceID FROM xSplitSourceExceptTable);
-- Assign new SourceIDs to the new sources
UPDATE xSplitSourceTable
SET NewSourceID = (
SELECT MAX(RowID)
FROM SourceTable
) + RowID;
-- Add Person name and fact type to Master Source Name
--- Personal citations
UPDATE xSplitSourceTable
SET Name =
(
SELECT S.Name || ' - ' || N.Surname || ', ' || N.Given || ' (Person)' FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN NameTable N ON C.OwnerID = N.OwnerID
WHERE +N.IsPrimary AND C.OwnerType = 0
AND xSplitSourceTable.OldCitationID = C.CitationID
)
WHERE OldCitationID IN (SELECT CitationID FROM CitationTable WHERE OwnerType = 0)
;
--- Family citations
---- Spouse
UPDATE xSplitSourceTable
SET Name =
ifnull(
(
SELECT S.Name || ' - ' || ifnull(N1.Surname,'') || ', ' || ifnull(N1.Given,'') || ' (Spouse) ' || ifnull(N2.Surname,'') || ', ' || ifnull(N2.Given,'') FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN FamilyTable F ON C.OwnerID = F.FamilyID
INNER JOIN NameTable N1 ON F.FatherID = N1.OwnerID
INNER JOIN NameTable N2 ON F.MotherID = N2.OwnerID
WHERE +N1.IsPrimary AND +N2.IsPrimary AND C.OwnerType = 1
AND xSplitSourceTable.OldCitationID = C.CitationID
)
,Name) -- endof ifnull()
WHERE OldCitationID IN (SELECT CitationID FROM CitationTable WHERE OwnerType = 1)
;
---- Parents (does not exist?)
--- Event citations
----Indiv Event
UPDATE xSplitSourceTable
SET Name =
(
SELECT S.Name || ' - ' || N.Surname || ', ' || N.Given || ' (' || FT.Name || ')' FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN EventTable E ON C.OwnerID = E.EventID
INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID
INNER JOIN NameTable N ON E.OwnerID = N.OwnerID
WHERE +N.IsPrimary AND C.OwnerType = 2
AND E.OwnerType = 0 AND FT.OwnerType = 0 -- 2013-09-03 corrected error failed to test FactTypeTable OwnerType
AND xSplitSourceTable.OldCitationID = C.CitationID
)
WHERE OldCitationID
IN (SELECT CitationID
FROM CitationTable C
INNER JOIN EventTable E ON C.OwnerID = E.EventID
WHERE C.OwnerType = 2 AND E.OwnerType = 0
)
;
----Family Event
-- allow for null spouse
UPDATE xSplitSourceTable
SET Name =
ifnull(
(
SELECT S.Name || ' - ' || ifnull(N1.Surname,'') || ', ' || ifnull(N1.Given,'') || ' (' || FT.Name || ') ' || ifnull(N2.Surname,'') || ', ' || ifnull(N2.Given,'') FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN EventTable E ON C.OwnerID = E.EventID
INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID
INNER JOIN FamilyTable F ON E.OwnerID = F.FamilyID
INNER JOIN NameTable N1 ON F.FatherID = N1.OwnerID
INNER JOIN NameTable N2 ON F.MotherID = N2.OwnerID
WHERE +N1.IsPrimary AND +N2.IsPrimary AND C.OwnerType = 2
AND E.OwnerType = 0 AND FT.OwnerType = 1 -- 2013-09-03 corrected error E.OwnerType = 1 which is for child-parent event not spousal
AND xSplitSourceTable.OldCitationID = C.CitationID
)
,Name) -- end of ifnull()
WHERE OldCitationID
IN (SELECT CitationID
FROM CitationTable C
INNER JOIN EventTable E ON C.OwnerID = E.EventID
INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID -- 2013-09-03 corrected for test of spousal events
WHERE C.OwnerType = 2 AND FT.OwnerType = 1
)
;
--- Alternate Name
UPDATE xSplitSourceTable
SET Name =
(
SELECT S.Name || ' - ' || N1.Surname || ', ' || N1.Given || ' (Alt Name) ' || N2.Surname || ', ' || N2.Given FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN NameTable N2 ON C.OwnerID = N2.NameID
INNER JOIN NameTable N1 ON N2.OwnerID = N1.OwnerID
WHERE +N1.IsPrimary AND NOT +N2.IsPrimary AND C.OwnerType = 7
AND xSplitSourceTable.OldCitationID = C.CitationID
)
WHERE OldCitationID IN (SELECT CitationID FROM CitationTable WHERE OwnerType = 7)
;
------- Source names extended
/* Create new Extreme Split Source Templates in a table
started from those templates used by cited sources
*/
DROP TABLE
IF EXISTS xSplitSourceTemplateTable;
CREATE TABLE
IF NOT EXISTS xSplitSourceTemplateTable AS
SELECT NULL AS NewTemplateID
,*
FROM SourceTemplateTable
WHERE TemplateID IN (
SELECT DISTINCT TemplateID
FROM xSplitSourceTable
)
-- AND NAME NOT LIKE '^%' --don't convert already converted ones
;
/*Convert all fields in the Source Template defined as Source Details or Citation Details
to Master Source fields, thus enabling the template to be used for Extreme Splitting.*/
UPDATE xSplitSourceTemplateTable
SET FieldDefs = CAST(REPLACE(CAST(FieldDefs AS TEXT), 'True', 'False') AS BLOB)
--WHERE NAME NOT LIKE '^%' --don't convert already converted ones
;
/* Create a template for extremely split Free Form sources
*/
INSERT INTO xSplitSourceTemplateTable
(NewTemplateID, TemplateID, Name, Description, Favorite, Category, Footnote, ShortFootnote, Bibliography, FieldDefs)
VALUES
(
Null
,0
,'Free Form -xSplit'
,'Free Form modified for extreme splitting'
,0
,'All'
,'[Footnote][Page]|, [Page]>.'
,'[ShortFootnote][Page]|, [Page]>.'
,'[Bibliography].'
,CAST('?
FootnoteFootnoteTextAuthor, title, publisher (full description of source)Used for first footnote citing this source in a report.
Exports to GEDCOM TITL tagFalse
ShortFootnoteShort FootnoteTextAuthor's surname or short title or other key for re-citingUsed for subsequent footnotes citing a source previously footnoted in a reportFalse
BibliographyBibliographyTextDescribe source sufficiently for others to identifyFalse
PageDetailsTextDetails to help find the citation within the sourceUsed in the full and short footnotes.
False'
AS BLOB)
)
;
-- Assign new TemplateIDs to the new templates
UPDATE xSplitSourceTemplateTable
SET NewTemplateID = (
SELECT MAX(TemplateID)
FROM SourceTemplateTable
) + RowID;
/*
-- IF the Free Form -xSplit template already exists in the SourceTemplateTable
-- merge multiple instances to one and put its TemplateID into NewTemplateID
-- then delete it from SourceTemplateTable
*/
UPDATE SourceTable
SET TemplateID =
(
SELECT TemplateID FROM SourceTemplateTable
WHERE Name LIKE '^Free Form -xSplit'
)
WHERE TemplateID IN
(
SELECT TemplateID FROM SourceTemplateTable
WHERE Name LIKE '^Free Form -xSplit'
)
;
UPDATE xSplitSourceTemplateTable
SET NewTemplateID =
ifnull(
(SELECT TemplateID FROM SourceTemplateTable
WHERE Name LIKE '^Free Form -xSplit')
,NewTemplateID)
WHERE TemplateID = 0
;
DELETE FROM SourceTemplateTable
WHERE Name LIKE '^Free Form -xSplit'
;
-- Point new xSources to the new xTemplates
UPDATE xSplitSourceTable
SET TemplateID = (
SELECT NewTemplateID
FROM xSplitSourceTemplateTable ST
WHERE xSplitSourceTable.TemplateID = ST.TemplateID
)
WHERE TemplateID IN (
SELECT DISTINCT TemplateID
FROM xSplitSourceTemplateTable
);
-- Create a table of new split citations from the existing ones
--SELECT CitationID, CAST(Fields AS TEXT) FROM CitationTable
DROP TABLE
IF EXISTS xSplitCitationTable;
CREATE TABLE
IF NOT EXISTS xSplitCitationTable AS
SELECT NULL AS NewCitationID
,NULL AS NewSourceID
,*
FROM CitationTable;
--
UPDATE xSplitCitationTable
SET NewCitationID = (
SELECT MAX(CitationID)
FROM CitationTable
) + RowID
,NewSourceID = (
SELECT NewSourceID
FROM xSplitSourceTable S
WHERE xSplitCitationTable.CitationID = S.OldCitationID
)
,Fields = CAST('
' AS BLOB);
--SELECT CitationID, CAST(Fields AS TEXT) FROM xSplitCitationTable
;
-- Insert the new split templates, sources and citations into the RM database
INSERT OR IGNORE INTO SourceTemplateTable
SELECT NewTemplateID
,'^' || NAME
,Description
,Favorite
,Category
,Footnote
,ShortFootnote
,Bibliography
,FieldDefs
FROM xSplitSourceTemplateTable;
INSERT INTO SourceTable
SELECT NewSourceID
,NAME
,RefNumber
,ActualText
,Comments
,IsPrivate
,TemplateID
,CAST(FieldsTxt AS BLOB)
FROM xSplitSourceTable;
INSERT INTO CitationTable
SELECT NewCitationID
,OwnerType
,NewSourceID
,OwnerID
,Quality
,IsPrivate
,Comments
,ActualText
,RefNumber
,Flags
,Fields
FROM xSplitCitationTable;
-- Copy Detail Text WebTag from old lumped citation to new split source Master Text WebTag
INSERT INTO URLTable
SELECT NULL AS LinkID
,3 AS OwnerType
,C.NewSourceID AS OwnerID
,U.LinkType
,U.Name
,U.URL
,U.Note
FROM URLTable U
INNER JOIN xSplitCitationTable C
ON U.OwnerID=C.CitationID
WHERE U.OwnerType =4
;
-- Copy Master Text WebTag from old lumped source to new split source Master Text WebTag
INSERT INTO URLTable
SELECT NULL AS LinkID
,3 AS OwnerType
,S.NewSourceID AS OwnerID
,U.LinkType
,U.Name
,U.URL
,U.Note
FROM URLTable U
INNER JOIN xSplitSourceTable S
ON U.OwnerID=S.SourceID
WHERE U.OwnerType =3
;
-- Copy Repositories from original lumped source to split sources
INSERT INTO AddressLinkTable
SELECT
NULL AS LinkID
,A.OwnerType
,A.AddressID
,S.NewSourceID
,A.AddressNum
,A.Details
FROM AddressLinkTable A
INNER JOIN xSplitSourceTable S
ON A.OwnerID = S.SourceID
WHERE A.OwnerType=3
;
-- Copy MediaLinks from citation or Master of original lumped source to Master split source
DROP TABLE IF EXISTS xSplitMediaLinkTable;
CREATE TABLE IF NOT EXISTS xSplitMediaLinkTable
AS
SELECT * FROM MediaLinkTable WHERE OwnerType = 4 -- copy citation links first
AND OwnerID IN (SELECT CitationID FROM xSplitCitationTable)
; --
INSERT INTO xSplitMediaLinkTable
SELECT * FROM MediaLinkTable
WHERE OwnerType = 3 -- copy master source links second
AND OwnerID IN (SELECT DISTINCT SourceID FROM xSplitSourceTable)
;
---- Point links for lumped Master source to split Master Source
UPDATE xSplitMediaLinkTable
SET
LinkID = NULL,
OwnerID = (SELECT NewSourceID FROM xSplitSourceTable S WHERE OwnerID=S.SourceID)
WHERE OwnerType = 3
--AND OwnerID IN (SELECT DISTINCT SourceID FROM xSplitSourceTable)
;
---- Point links for lumped citations to split Master Source
UPDATE OR REPLACE xSplitMediaLinkTable
SET
LinkID = NULL,
OwnerType = 3,
OwnerID = (SELECT NewSourceID FROM xSplitCitationTable C WHERE xSplitMediaLinkTable.OwnerID=C.CitationID)
WHERE OwnerType = 4
--AND OwnerID IN (SELECT DISTINCT CitationID FROM xSplitCitationTable)
;
INSERT INTO MediaLinkTable
SELECT * FROM xSplitMediaLinkTable
;
/* All lumped sources now duplicated by split sources for review.
Deletion of lumped sources and their citations yet to be done; also for
unused custom source templates.
*/