-- Sources-AuthorUnreverse.sql
/* 2017-03-30 Tom Holden ve3meo
Converts names for Author fields in Master Sources
from reverse order (surname first followed by comma)
to forward order without comma. This has been a problem
for some imports from TMG.

Uses a series of temporary SQLite Views to build up the 
final View before the actual UPDATE. 

Does not operate on 
any other name fields such as Editor; these require editing
of this script to become specific to each other name-type field. 
*/

-- find all master sources with a comma in the Author field
DROP VIEW IF EXISTS SourceFields
;
CREATE TEMP VIEW SourceFields AS
SELECT SourceID, CAST(Fields AS TEXT) AS Fields
FROM SourceTable
WHERE Fields REGEXP '.+<Name>Author</Name><Value>[^<]+,.+' 
;

-- Find the starting position of the Author name
DROP VIEW IF EXISTS AuthorStart
;
CREATE TEMP VIEW AuthorStart AS
SELECT SourceID
  , INSTR(Fields, '<Name>Author</Name>') + LENGTH('<Name>Author</Name><Value>')
    AS Start   
FROM SourceFields
;

-- extract evrything to the right of the starting position
DROP VIEW IF EXISTS AuthorFieldsRight
;
CREATE TEMP VIEW AuthorFieldsRight AS
SELECT SourceID
  , SUBSTR(Fields, Start) AS RightStr
FROM SourceFields
NATURAL JOIN AuthorStart
; 

-- extract the Author name in original reverse order
DROP VIEW IF EXISTS AuthorReverse
;
CREATE TEMP VIEW AuthorReverse AS
SELECT SourceID
  , SUBSTR(RightStr, INSTR(RightStr, '</Value>'),-256)
    AS AuthorRev
FROM AuthorFieldsRight
;

-- convert the name to forward order
DROP VIEW IF EXISTS AuthorForward
;
CREATE TEMP VIEW AuthorForward AS
SELECT SourceID
  , TRIM(SUBSTR(AuthorRev, INSTR(AuthorRev,',')+1))
  || ' ' || SUBSTR(AuthorRev, INSTR(AuthorRev,','), -256)
  AS AuthorFwd
FROM AuthorReverse
;

-- consolidate the original Fields, original Author and the converted Author
DROP VIEW IF EXISTS AuthorFwdRev
;
CREATE TEMP VIEW AuthorFwdRev AS
SELECT * 
FROM SourceFields
NATURAL JOIN AuthorReverse
NATURAL JOIN AuthorForward
; 

-- create for review and update the new Fields value with the forward name
DROP VIEW IF EXISTS SourceFieldsNew
;
CREATE TEMP VIEW SourceFieldsNew AS
SELECT SourceID
  , REPLACE(Fields, AuthorRev, AuthorFwd) AS Fields
FROM AuthorFwdRev
;

-- revise the Master Sources with the forward name
UPDATE SourceTable
SET Fields = 
 CAST(
      (SELECT Fields 
       FROM SourceFieldsNew SFN 
       WHERE SourceTable.SourceID = SFN.SourceID
       )
      AS BLOB
      )
WHERE SourceID IN (SELECT SourceID FROM SourceFieldsNew)
;

-- end of script