-- CitationSort.sql

/*
2016-01-08 Tom Holden ve3meo

Sorts CitationTable rows alphabetically by Source Name to force 
lists in CitationManager
to be sorted likewise. Because this changes the CitationIDs of many
or even all citations, tables that point to this table are updated
accordingly.
*/

/*
Create a temp table to store all columns from CitationTable
plus an additional column for the new CitationID
*/

BEGIN
;
DROP TABLE IF EXISTS xCitationSortTable
;
CREATE TEMP TABLE xCitationSortTable AS
SELECT 0 AS NewCitationID, CitationTable.* 
FROM CitationTable
LEFT JOIN SourceTable
USING(SourceID)
ORDER BY Name
; 

-- Assign the new CitationId in the temp table
UPDATE OR ROLLBACK xCitationSortTable SET NewCitationID = RowID
;

-- Replace OwnerID in MediaLinkTable with NewCitationID FROM xCitationSortTable
UPDATE OR ROLLBACK MediaLinkTable
SET OwnerID = (SELECT NewCitationID FROM xCitationSortTable X WHERE MediaLinkTable.OwnerID = X.CitationID)
WHERE OwnerType = 4  -- Citation
;

-- Replace OwnerID in URLTable with NewCitationID FROM xCitationSortTable
UPDATE OR ROLLBACK URLTable
SET OwnerID = (SELECT NewCitationID FROM xCitationSortTable X WHERE URLTable.OwnerID = X.CitationID)
WHERE OwnerType = 4  -- Citation
;

-- Replace rows of CitationTable with sorted rows
DELETE FROM CitationTable
;

INSERT OR ROLLBACK INTO CitationTable
SELECT NewCitationID, OwnerType, SourceID, OwnerID, Quality, IsPrivate, Comments, ActualText, RefNumber, Flags, Fields
FROM xCitationSortTable
;

COMMIT
;

-- End of script