-- RoleSort.sql
/*
2016-01-07 Tom Holden ve3meo

Sorts RoleTable rows for user-added rolenames alphabetically and 
replaces WitnessTable.Role for these with new RoleTable.RoleID
The resulting list of roles in the RM Edit Fact Type dialog
is then presented in alphabetical sort order after the Principal
and any builtin roles.

Caveats:
1. If a future version of RM increases the number of builtin roles to more than 58,
   every instance of 58 in the script must be revised accordingly
*/

/*
Create a temp table to store all columns from RoleTable for user-added roles
plus an additional column for the new RoleID
*/
DROP TABLE IF EXISTS xRoleSortTable
;
CREATE TEMP TABLE xRoleSortTable AS
  SELECT 0 AS NewRoleID, * FROM RoleTable
  WHERE RoleID>58 -- Builtin roles end at 58 in RootsMagic 4-7; user-added roles are above 58
  ORDER BY EventType, RoleName  -- the table will be populated with the user-added roles sorted by fact type and name
;

-- Assign the new RoleId in the temp table
BEGIN
;
UPDATE xRoleSortTable SET NewRoleID = RowID + 58 -- 59 for row 1, 60 for row 2, ...
;
COMMIT
;

-- Change the old WitnessTable.Role for user-added roles to the NewRoleID found in the temp table
-- corresponding to the old RoleID
BEGIN
;
UPDATE WitnessTable
SET Role = (SELECT NewRoleID FROM xRoleSortTable X WHERE WitnessTable.Role = X.RoleID)
WHERE Role > 58  -- if a future version of RM increases the number of builtin roles, 58 must be revised accordingly
;
COMMIT
;

-- Delete all the user-added roles from the RoleTable
BEGIN
;
DELETE FROM RoleTable WHERE RoleID > 58
;
COMMIT
;

/*
 Replace the deleted user-added roles with the same but 
 with the new RoleID from the temp table to match up with 
 what was put into the WitnessTable
*/
BEGIN
;
INSERT INTO RoleTable
SELECT NewRoleID, RoleName, EventType, RoleType, Sentence
FROM xRoleSortTable
;
COMMIT
;
-- End of Script