/*

update_citation_order.sql   1/21/2024   Jerry Bryan

Update CitationLinkTable.SortOrder in RM9's database to establish
a sort order for citations in the Edit Person screen. This feature
in RM9 seems to have backend support in displaying the citations
in order but does not yet have frontend suppport in the UI to
establish that order.

This version of the script orders citations by SourceTable.Name where
the relevant SQL is ORDER BY S.Name. It would be easy to establish
other orderings such as ORDER BY CL.Quality, S.Name

This script orders citations by setting CitationLinkTable.SortOrder
to ascending integers by using the standard SQLite row_number()
feature and by using the standard SQLite OVER (PARTITION BY feature.

Interestingly enough, this script depends on the CitationLinkTable.OwnerType
column and the CitationLinkTable.OwnerID column but it does not depend
using a JOIN to the owning tables. It is sufficient to start with
CitationLinkTable and then to JOIN only with CitationTable and
SourceTable.

*/


UPDATE CitationLinkTable
SET SortOrder = 
(
SELECT A.NewSortOrder
FROM
   (
     SELECT CL.LinkID, CL.OwnerType, CL.OwnerID, S.Name, CL.SortOrder,
            row_number() OVER (PARTITION BY CL.OwnerType, CL.OwnerID ORDER BY S.Name) AS NewSortOrder
     FROM CitationLinkTable AS CL
     JOIN CitationTable AS C ON C.CitationID = CL.CitationID
     JOIN SourceTable AS S ON S.SourceID = C.SourceID
     ORDER BY CL.OwnerType, CL.OwnerID, S.Name
   ) AS A 
WHERE CitationLinkTable.LinkID = A.LinkID     
)