-- SURNAME-TitleCase.sql
/* 2022-10-29 Tom Holden ve3meo
Converts unique ALL-CAPS Surnames from NameTable 
to having Titlecase (AKA Propercase), i.e.,
only the initial letter of each word is capitalised.

REQUIRES SQLite Expert Personal with the unifuzz.dll fake RMNOCASE collation
because of its support of Unicode in text functions and and support of 
run-time variables 

*/


/* Backup Surnames from NameTable

-----------STEP ONE-------------
DROP TABLE IF EXISTS SurnamesOLD
;
CREATE TABLE SurnamesOLD
 (NameID INTEGER PRIMARY KEY, Surname TEXT COLLATE RMNOCASE)
;
INSERT INTO SurnamesOLD
 SELECT NameID, Surname FROM NameTable
;
-----------STEP ONE-------------
*/


/* Set Surnames in NameTable to UPPER case

-----OPTIONAL FOR TESTING-------
UPDATE NameTable SET Surname = UPPER(Surname)
;

-- OR Set Surnames in NameTable to LOWER case
UPDATE NameTable SET Surname = LOWER(Surname)
;
-----OPTIONAL FOR TESTING-------
*/

/* Make a table of UPPER CASE Surnames to be converted
to Proper Case. Prompts for first letter on which to filter.
Leave blank to get all.

-----------STEP TWO-------------
REINDEX;

DROP TABLE IF EXISTS SurnamesWORK
;

CREATE TEMP TABLE SurnamesWORK
AS
 SELECT DISTINCT Surname AS OldName, TRIM(Surname) AS Name FROM NameTable  --TRIMmed to remove adjacent blanks
 WHERE Surname = UPPER(Surname)
  AND (Surname LIKE $FirstLetter ||'%'           -- e.g., "MARTIN"
      OR Surname REGEXP '^[^A-Z]'||$FirstLetter) -- e.g., "(MARTIN)"
;
SELECT * FROM SurnamesWORK --INSPECT
;
-----------STEP TWO-------------
*/

/* Convert Surnames from SurnamesWORK to Proper Names in the 
ProperName table

----------STEP THREE------------
DROP TABLE IF EXISTS ProperName
;
CREATE TEMP TABLE ProperName AS
-- following adapted from an answer in StackOverflow 2017-11-23
-- https://stackoverflow.com/a/47450800
WITH RECURSIVE pcase(OldName, rest, result) AS (
  SELECT --ID,
         OldName,
         Name,
         ''
  FROM SurnamesWORK

  UNION ALL

  SELECT --id,
         OldName,
         substr(rest, 2),
         result || CASE WHEN substr(result, -1) GLOB '[A-Za-z]'
                        THEN lower(substr(rest, 1, 1))
                        ELSE upper(substr(rest, 1, 1))
                   END
  FROM pcase
  WHERE rest <> ''
)
SELECT --id,
       OldName AS Surname,
       result AS NewSurname
FROM pcase
WHERE rest = ''
ORDER BY OldName COLLATE RMNOCASE
;

SELECT * FROM ProperName -- INSPECT AND EDIT IF NEEDED
;
----------STEP THREE------------
*/

/* ADJUST SPECIAL CASES NAMES BEGINNING Mac, Mc

-----STEP THREE ADDENDUM 1--------
-- should find in compound names, too, but only first instance 
-- so have to run twice for compound names like McGill-MacKay
DROP TABLE IF EXISTS SpecialName
;
CREATE TEMP TABLE SpecialName AS
SELECT *, '' AS NewSurname2 FROM ProperName WHERE 0
UNION ALL
SELECT *, SUBSTR(NewSurname,1,INSTR(NewSurname,'Mac')+2)
          ||UPPER(substr(NewSurname,INSTR(NewSurname,'Mac')+3,1))
          ||substr(NewSurname,INSTR(NewSurname,'Mac')+4)
 FROM ProperName 
 WHERE INSTR(NewSurname,'Mac') 
UNION ALL
SELECT *, SUBSTR(NewSurname,1,INSTR(NewSurname,'Mc')+1)
          ||UPPER(substr(NewSurname,INSTR(NewSurname,'Mc')+2,1))
          ||substr(NewSurname,INSTR(NewSurname,'Mc')+3) 
 FROM ProperName 
 WHERE INSTR(NewSurname,'Mc')
-- UNION ALL -- Add other special casess here, such as Van
;

SELECT * FROM SpecialName -- INSPECT AND EDIT NewSurname2 IF NEEDED
;
-----STEP THREE ADDENDUM 1-------- 
*/

/* Apply special names to ProperName table if ADDENDUM 1 has been done

-----STEP THREE ADDENDUM 2--------
UPDATE ProperName
SET NewSurname =
 (SELECT NewSurname2 FROM SpecialName WHERE ProperName.Surname=SpecialName.Surname)
WHERE Surname IN (SELECT Surname FROM SpecialName)
;

SELECT * FROM ProperName WHERE Surname LIKE 'MAC%' OR Surname LIKE 'MC%'
; --INSPECT AND EDIT NewSurname in the ProperName table IF NEEDED

-----STEP THREE ADDENDUM 2--------
*/

/* Apply NewSurnames to NameTable

----------STEP FOUR------------
UPDATE NameTable
SET Surname =
 (Select NewSurname FROM ProperName
  WHERE NameTable.Surname=ProperName.Surname)
WHERE NameTable.Surname IN (SELECT Surname FROM ProperName)
;  -- UPDATE of an entire NameTable can take tens of seconds for 
   -- tens of thousands of Names depending on the speed of the computer  
----------STEP FOUR------------
*/

/* INSPECT USING VARIOUS TESTS - SELECT ONE STATEMENT AT A TIME

----------STEP FIVE----------

-- Changed Surnames (Not behaving as I would expect)
SELECT DISTINCT SurnamesOLD.Surname AS OldSurname, NameTable.Surname 
FROM SurnamesOLD LEFT JOIN NameTable USING(NameID)
WHERE OldSurname LIKE $FirstLetter||'%'
AND NOT NameTable.Surname GLOB OldSurname
ORDER BY OldSurname
; 

-- All Surnames 
SELECT DISTINCT Surname FROM NameTable
WHERE Surname LIKE $FirstLetter||'%'
;

-- Remaining UPPERCASE SURNAMES
SELECT DISTINCT Surname FROM NameTable
WHERE Surname LIKE $FirstLetter||'%'
AND Surname = UPPER(Surname)
;

----------STEP FIVE----------
*/

/* IF   happy with results AND you are finished converting, 
   THEN rid the Database of the SurnamesOLD table
        ----------STEP SIX------------ 
        DROP TABLE IF EXISTS SurnamesOLD
        ;
        ----------STEP SIX------------
   ELSE GOTO STEP TWO
*/

--Be sure to run File>Database Tools in RM when you return
----------THE END----------- 

SELECT 'NOTHING EXECUTED!
Select the code within a ---STEP--- and execute it.'
AS WARNING
;