-- SourcesSort.sql

/*
2016-01-08 Tom Holden ve3meo

Sorts SourceTable rows alphabetically to facilitate inspection using SQLite.
Because this changes the SourceIDs of many
or even all sources, tables that point to this table are updated
accordingly.
*/

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

BEGIN
;
DROP TABLE IF EXISTS xSourceSortTable
;
CREATE TEMP TABLE xSourceSortTable AS
SELECT 0 AS NewSourceID, * 
FROM SourceTable
ORDER BY Name
; 

-- Assign the new SourceId in the temp table
UPDATE OR ROLLBACK xSourceSortTable SET NewSourceID = RowID
;

-- Replace SourceID in CitationTable with NewSourceID FROM xSourceSortTable
UPDATE OR ROLLBACK CitationTable 
SET SourceID = (SELECT NewSourceID FROM xSourceSortTable X WHERE CitationTable.SourceID = X.SourceID)
;

-- Replace OwnerID in MediaLinkTable with NewSourceID FROM xSourceSortTable
UPDATE OR ROLLBACK MediaLinkTable
SET OwnerID = (SELECT NewSourceID FROM xSourceSortTable X WHERE MediaLinkTable.OwnerID = X.SourceID)
WHERE OwnerType = 3  -- Master Source
;

-- Replace OwnerID in AddressLinkTable with NewSourceID FROM xSourceSortTable
UPDATE OR ROLLBACK AddressLinkTable
SET OwnerID = (SELECT NewSourceID FROM xSourceSortTable X WHERE AddressLinkTable.OwnerID = X.SourceID)
WHERE OwnerType = 3  -- Master Source
;

-- Replace OwnerID in URLTable with NewSourceID FROM xSourceSortTable
UPDATE OR ROLLBACK URLTable
SET OwnerID = (SELECT NewSourceID FROM xSourceSortTable X WHERE URLTable.OwnerID = X.SourceID)
WHERE OwnerType = 3  -- Master Source
;

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

INSERT OR ROLLBACK INTO SourceTable
SELECT NewSourceID, Name, RefNumber, ActualText, Comments, IsPrivate, TemplateID, Fields
FROM xSourceSortTable
;

COMMIT
;

-- End of script