ProGet 2024 Package Data-fix/De-Duplication Script (v1)

gistfile1.txt

---------------- SET THIS TO 'N' to run the data updates
-----------
DECLARE @DryMode YNINDICATOR = 'Y'
-------------------------------------------------

BEGIN TRY 

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC sp_executesql @SQL

BEGIN TRAN

PRINT 'Bad/Duplicate Cleanup Report; DryMode=' + @DryMode
PRINT GETUTCDATE()
PRINT '-----'

PRINT 'Finding improperly duplicated names (e.g. InedoLib, inedoLib), prefering the latest version of the name...'
;WITH DuplicatePackageNameIds AS
(
   SELECT [Canonical_PackageName_Id] = MAX([PackageName_Id]), [PackageType_Name], [PackageGroup_Name_Lower], [Package_Name_Lower]
     FROM [PackageNameIds]
    WHERE [PackageType_Name] <> 'rpm'
    GROUP BY [PackageType_Name] , [Package_Name_Lower], [PackageGroup_Name_Lower]
   HAVING COUNT(*) > 1
)
SELECT [PackageName_Id], [Canonical_PackageName_Id]
  INTO #DuplicatePackageNameIds
  FROM [PackageNameIds] PN, DuplicatePackageNameIds DPN
 WHERE PN.[PackageType_Name] = DPN.[PackageType_Name]
   AND ( (PN.[PackageGroup_Name_Lower] IS NULL AND PN.[PackageGroup_Name_Lower] IS NULL)
          OR PN.[PackageGroup_Name_Lower] = DPN.[PackageGroup_Name_Lower] )
   AND PN.[Package_Name_Lower] = DPN.[Package_Name_Lower]
   AND PN.[PackageName_Id] <> DPN.[Canonical_PackageName_Id]


PRINT 'Finding duplicated versions based on #DuplicatePackageVersionIds'
;WITH DuplicatePackageVersionIds AS
(
   SELECT [Canonical_PackageVersion_Id] = MAX([PackageVersion_Id]), [Canonical_PackageName_Id], PV.[Package_Version], PV.[Qualifier_Text]
     FROM [PackageVersionIds] PV, #DuplicatePackageNameIds DPN
    WHERE PV.[PackageName_Id] IN (DPN.[PackageName_Id], DPN.[Canonical_PackageName_Id])
    GROUP BY DPN.[Canonical_PackageName_Id], PV.[Package_Version], PV.[Qualifier_Text]
   HAVING COUNT(*) > 1
)
SELECT DPV.[Canonical_PackageVersion_Id], PV.[PackageVersion_Id]
  INTO #DuplicatePackageVersionIds
  FROM DuplicatePackageVersionIds DPV
       JOIN #DuplicatePackageNameIds DPN ON DPV.[Canonical_PackageName_Id] = DPN.[Canonical_PackageName_Id]
       JOIN [PackageVersionIds] PV
         ON PV.[PackageName_Id] = DPN.[PackageName_Id]
        AND PV.[Package_Version] = DPV.[Package_Version]
        AND ( (PV.[Qualifier_Text] IS NULL AND DPV.[Qualifier_Text] IS NULL)
             OR PV.[Qualifier_Text] = DPV.[Qualifier_Text])

PRINT 'Finding Bad NuGet Package versions...'
;WITH BadNuGetVersions AS
(
    SELECT PV.[PackageVersion_Id]
      FROM [PackageVersionIds] PV
      JOIN [PackageNameIds] PN ON PV.[PackageName_Id] = PN.[PackageName_Id]
     WHERE PN.[PackageType_Name] = 'nuget'
       AND LEN(PV.[Package_Version]) - LEN(REPLACE(PV.[Package_Version],'.','')) = 3
       AND RIGHT(PV.[Package_Version], 2) = '.0'
       AND CHARINDEX('-', PV.[Package_Version]) = 0
)
SELECT BPV.[PackageVersion_Id], [Canonical_PackageVersion_Id] = GPV.[PackageVersion_Id]
  INTO #BadNuGetVersions
  FROM BadNuGetVersions BPV
       LEFT JOIN [PackageVersionIds] PV ON BPV.[PackageVersion_Id] = PV.[PackageVersion_Id]
       LEFT JOIN [PackageVersionIds] GPV
              ON PV.[PackageName_Id]= GPV.[PackageName_Id]
             AND PV.[Package_Version] = GPV.[Package_Version]  + '.0'

IF EXISTS ( SELECT * FROM #DuplicatePackageVersionIds PVI,
              (SELECT [PackageVersion_Id] FROM #BadNuGetVersions UNION
               SELECT [Canonical_PackageVersion_Id] FROM #BadNuGetVersions) NPVI
            WHERE NPVI.[PackageVersion_Id] IN (PVI.[PackageVersion_Id], PVI.Canonical_PackageVersion_Id) )
BEGIN

  PRINT '*** Skipping NuGet Package Updates ***'
  PRINT 'The job must be run again to update NuGet packages'

END ELSE  BEGIN

   PRINT 'Updating PackageVersionIds (NuGet)...'
   UPDATE [PackageVersionIds]
      SET [Package_Version] = LEFT(PV.[Package_Version], LEN(PV.[Package_Version])-2) 
     FROM [PackageVersionIds] PV
      JOIN #BadNuGetVersions BPV ON PV.[PackageVersion_Id] = BPV.[PackageVersion_Id]
     WHERE BPV.[Canonical_PackageVersion_Id] IS NULL
   
   PRINT 'Considering Bad Versions Duplicates (NuGet)...'
   INSERT INTO #DuplicatePackageVersionIds ([PackageVersion_Id], [Canonical_PackageVersion_Id])
   SELECT [PackageVersion_Id], [Canonical_PackageVersion_Id]
    FROM  #BadNuGetVersions 
    WHERE [Canonical_PackageVersion_Id] IS NOT NULL

END

----------------------------------------------------------------
-- FIX #DuplicatePackageNameIds
-------------------------------------------------------

BEGIN PRINT 'Fixing PgvdPackageNames...'
   ;WITH DuplicatePgvdPackageNames AS
   (
       SELECT PN.[Pgvd_Id], DPN.[PackageName_Id]
         FROM [PgvdPackageNames] PN
              JOIN #DuplicatePackageNameIds DPN 
                ON PN.[PackageName_Id] IN (DPN.[PackageName_Id], DPN.[Canonical_PackageName_Id])
        GROUP BY PN.[Pgvd_Id], DPN.[PackageName_Id]
       HAVING COUNT(*) > 1
   )
   DELETE [PgvdPackageNames] 
     FROM [PgvdPackageNames] PN 
     JOIN DuplicatePgvdPackageNames DPN
       ON PN.[Pgvd_Id] = DPN.[Pgvd_Id]
      AND PN.[PackageName_Id] = DPN.[PackageName_Id]

   UPDATE [PgvdPackageNames]
      SET [PackageName_Id] = DPN.[Canonical_PackageName_Id]
     FROM [PgvdPackageNames] PN 
     JOIN #DuplicatePackageNameIds DPN ON PN.[PackageName_Id] = DPN.[PackageName_Id]
END

BEGIN PRINT 'Fixing LicensePackageNameIds...'
    ;WITH DuplicateLicensePackageNameIds AS
    (
        SELECT LPN.[License_Id], DPN.[PackageName_Id]
          FROM [LicensePackageNameIds] LPN
               JOIN #DuplicatePackageNameIds DPN 
                 ON LPN.[PackageName_Id] IN (DPN.[PackageName_Id], DPN.[Canonical_PackageName_Id])
         GROUP BY LPN.[License_Id], DPN.[PackageName_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [LicensePackageNameIds] 
      FROM [LicensePackageNameIds] LPN
      JOIN DuplicateLicensePackageNameIds DPN
        ON LPN.[License_Id] = DPN.[License_Id]
       AND LPN.[PackageName_Id] = DPN.[PackageName_Id]

    UPDATE [LicensePackageNameIds]
       SET [PackageName_Id] = DPN.[Canonical_PackageName_Id]
      FROM [LicensePackageNameIds] LPN
      JOIN #DuplicatePackageNameIds DPN ON LPN.[PackageName_Id] = DPN.[PackageName_Id]
END

BEGIN PRINT 'Disabling FKs to FeedPackages...'
   ALTER TABLE [FeedPackageStats] NOCHECK CONSTRAINT [FK__FeedPackageVersionStats__FeedPackages]
   ALTER TABLE [RubyGemFeedPackages] NOCHECK CONSTRAINT [FK__RubyGemFeedPackages__FeedPackages]
   ALTER TABLE [NpmFeedPackageTags] NOCHECK CONSTRAINT [FK__NpmFeedPackageTags__FeedPackages]
END

BEGIN PRINT 'Fixing FeedPackageStats...'
   ;WITH DuplicateFeedPackageStats AS
   (
       SELECT FPS.[Feed_Id], DPN.[PackageName_Id]
         FROM [FeedPackageStats] FPS
              JOIN #DuplicatePackageNameIds DPN 
                ON FPS.[PackageName_Id] IN (DPN.[PackageName_Id], DPN.[Canonical_PackageName_Id])
        GROUP BY FPS.[Feed_Id], DPN.[PackageName_Id]
       HAVING COUNT(*) > 1
   )
   DELETE [FeedPackageStats] 
    FROM [FeedPackageStats] FPS
    JOIN DuplicateFeedPackageStats DPN
      ON FPS.[Feed_Id] = DPN.[Feed_Id]
     AND FPS.[PackageName_Id] = DPN.[PackageName_Id]

   UPDATE [FeedPackageStats]
      SET [PackageName_Id] = DPN.[Canonical_PackageName_Id]
     FROM [FeedPackageStats] FPS
     JOIN #DuplicatePackageNameIds DPN ON FPS.[PackageName_Id] = DPN.[PackageName_Id]
END 

BEGIN PRINT 'Fixing NpmFeedPackageTags...'
    ;WITH DuplicateNpmFeedPackageTags AS
    (
        SELECT NFPT.[Feed_Id], DPN.[PackageName_Id]
          FROM [NpmFeedPackageTags] NFPT
               JOIN #DuplicatePackageNameIds DPN 
                 ON NFPT.[PackageName_Id] IN (DPN.[PackageName_Id], DPN.[Canonical_PackageName_Id])
         GROUP BY NFPT.[Feed_Id], DPN.[PackageName_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [NpmFeedPackageTags] 
      FROM [NpmFeedPackageTags] NFPT
      JOIN DuplicateNpmFeedPackageTags DPN
        ON NFPT.[Feed_Id] = DPN.[Feed_Id]
       AND NFPT.[PackageName_Id] = DPN.[PackageName_Id]

    UPDATE [NpmFeedPackageTags]
       SET [PackageName_Id] = DPN.[Canonical_PackageName_Id]
      FROM [NpmFeedPackageTags] NFPT
      JOIN #DuplicatePackageNameIds DPN ON NFPT.[PackageName_Id] = DPN.[PackageName_Id]
END

BEGIN PRINT 'Fixing RubyGemFeedPackages...'
    ;WITH DuplicateRubyGemFeedPackages AS
    (
        SELECT RGFPT.[Feed_Id], DPN.[PackageName_Id]
          FROM [RubyGemFeedPackages] RGFPT
               JOIN #DuplicatePackageNameIds DPN 
                 ON RGFPT.[PackageName_Id] IN (DPN.[PackageName_Id], DPN.[Canonical_PackageName_Id])
         GROUP BY RGFPT.[Feed_Id], DPN.[PackageName_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [RubyGemFeedPackages] 
      FROM [RubyGemFeedPackages] RGFPT
      JOIN DuplicateRubyGemFeedPackages DPN
        ON RGFPT.[Feed_Id] = DPN.[Feed_Id]
       AND RGFPT.[PackageName_Id] = DPN.[PackageName_Id]
    UPDATE [RubyGemFeedPackages]
       SET [PackageName_Id] = DPN.[Canonical_PackageName_Id]
      FROM [RubyGemFeedPackages] RGFPT
      JOIN #DuplicatePackageNameIds DPN ON RGFPT.[PackageName_Id] = DPN.[PackageName_Id]
END

BEGIN PRINT 'Fixing FeedPackages...'
    ;WITH DuplicateFeedPackages AS
    (
        SELECT FP.[Feed_Id], DPN.[PackageName_Id]
          FROM [FeedPackages] FP
               JOIN #DuplicatePackageNameIds DPN 
                 ON FP.[PackageName_Id] IN (DPN.[PackageName_Id], DPN.[Canonical_PackageName_Id])
         GROUP BY FP.[Feed_Id], DPN.[PackageName_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [FeedPackages] 
      FROM [FeedPackages] FP
      JOIN DuplicateFeedPackages DPN
        ON FP.[Feed_Id] = DPN.[Feed_Id]
       AND FP.[PackageName_Id] = DPN.[PackageName_Id]
    UPDATE [FeedPackages]
       SET [PackageName_Id] = DPN.[Canonical_PackageName_Id]
      FROM [FeedPackages] FP
      JOIN #DuplicatePackageNameIds DPN ON FP.[PackageName_Id] = DPN.[PackageName_Id]
END

BEGIN PRINT 'Enabling FKs to FeedPackages...'
   ALTER TABLE [FeedPackageStats] CHECK CONSTRAINT [FK__FeedPackageVersionStats__FeedPackages]
   ALTER TABLE [RubyGemFeedPackages] CHECK CONSTRAINT [FK__RubyGemFeedPackages__FeedPackages]
   ALTER TABLE [NpmFeedPackageTags] CHECK CONSTRAINT [FK__NpmFeedPackageTags__FeedPackages]
END

----------------------------------------------------------------
-- FIX #DuplicatePackageNameIds
-------------------------------------------------------

BEGIN PRINT 'Fixing EventOccurrences...'
   UPDATE [EventOccurrences] 
      SET [PackageVersion_Id] = DPV.[Canonical_PackageVersion_Id]
     FROM [EventOccurrences] EO
     JOIN #DuplicatePackageVersionIds DPV ON EO.[PackageVersion_Id] = DPV.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing PackageUsage23...'
    DELETE [PackageUsage23] WHERE [PackageVersion_Id] IN (SELECT [PackageVersion_Id] FROM [#DuplicatePackageVersionIds])
END

BEGIN PRINT 'Fixing DeletedPackages23...'
    ;WITH DuplicateDeletedPackages23 AS
    (
        SELECT DP23.[Feed_Id], DPV.[PackageVersion_Id]
          FROM [DeletedPackages23] DP23
               JOIN #DuplicatePackageVersionIds DPV
                 ON DP23.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY DP23.[Feed_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [DeletedPackages23] 
      FROM [DeletedPackages23] DP23
      JOIN DuplicateDeletedPackages23 DPN
        ON DP23.[Feed_Id] = DPN.[Feed_Id]
       AND DP23.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [DeletedPackages23]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [DeletedPackages23] DP23
      JOIN #DuplicatePackageVersionIds DPN ON DP23.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Disabling FKs to FeedPackageVersions...'
  ALTER TABLE [CondaFeedPackageVersions] NOCHECK CONSTRAINT [FK__CondaFeedPackageVersions__FeedPackageVersions]
  ALTER TABLE [NuGetFeedPackageVersions] NOCHECK CONSTRAINT [FK__NuGetFeedPackageVersions__FeedPackageVersions]
  ALTER TABLE [NuGetPackageSymbols23] NOCHECK CONSTRAINT [FK__NuGetPackageSymbols23__FeedPackageVersions]
  ALTER TABLE [NuGetSymbolPackageVersions23] NOCHECK CONSTRAINT [FK__NuGetSymbolPackageVersions23__FeedPackageVersions]
  ALTER TABLE [UniversalFeedPackageVersions] NOCHECK CONSTRAINT [FK__UniversalFeedPackageVersions__FeedPackageVersions]
  ALTER TABLE [FeedPackageVersionAnalyses] NOCHECK CONSTRAINT [FK__FeedPackageAnalyses__FeedPackageVersions]
  ALTER TABLE [FeedPackageVersionStats] NOCHECK CONSTRAINT [FK__FeedPackageVersionStats__FeedPackageVersions]
END

BEGIN PRINT 'Fixing CondaFeedPackageVersions...'
    ;WITH DuplicateCondaFeedPackageVersions AS
    (
        SELECT CFPV.[Feed_Id], DPV.[PackageVersion_Id]
          FROM [CondaFeedPackageVersions] CFPV
               JOIN #DuplicatePackageVersionIds DPV
                 ON CFPV.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY CFPV.[Feed_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [CondaFeedPackageVersions] 
      FROM [CondaFeedPackageVersions] CFPV
      JOIN DuplicateCondaFeedPackageVersions DPN
        ON CFPV.[Feed_Id] = DPN.[Feed_Id]
       AND CFPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [CondaFeedPackageVersions]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [CondaFeedPackageVersions] CFPV
      JOIN #DuplicatePackageVersionIds DPN ON CFPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing NuGetFeedPackageVersions...'
    ;WITH DuplicateNuGetFeedPackageVersions AS
    (
        SELECT NFPV.[Feed_Id], DPV.[PackageVersion_Id]
          FROM [NuGetFeedPackageVersions] NFPV
               JOIN #DuplicatePackageVersionIds DPV
                 ON NFPV.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY NFPV.[Feed_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [NuGetFeedPackageVersions] 
      FROM [NuGetFeedPackageVersions] NFPV
      JOIN DuplicateNuGetFeedPackageVersions DPN
        ON NFPV.[Feed_Id] = DPN.[Feed_Id]
       AND NFPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [NuGetFeedPackageVersions]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [NuGetFeedPackageVersions] NFPV
      JOIN #DuplicatePackageVersionIds DPN ON NFPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing NuGetPackageSymbols23...'
    ;WITH DuplicateNuGetPackageSymbols23 AS
    (
        SELECT NPS23.[Feed_Id], DPV.[PackageVersion_Id]
          FROM [NuGetPackageSymbols23] NPS23
               JOIN #DuplicatePackageVersionIds DPV
                 ON NPS23.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY NPS23.[Feed_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [NuGetPackageSymbols23] 
      FROM [NuGetPackageSymbols23] NPS23
      JOIN DuplicateNuGetPackageSymbols23 DPN
        ON NPS23.[Feed_Id] = DPN.[Feed_Id]
       AND NPS23.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [NuGetPackageSymbols23]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [NuGetPackageSymbols23] NPS23
      JOIN #DuplicatePackageVersionIds DPN ON NPS23.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing NuGetSymbolPackageVersions23...'
    ;WITH DuplicateNuGetSymbolPackageVersions23 AS
    (
        SELECT NSPV23.[Feed_Id], DPV.[PackageVersion_Id]
          FROM [NuGetSymbolPackageVersions23] NSPV23
               JOIN #DuplicatePackageVersionIds DPV
                 ON NSPV23.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY NSPV23.[Feed_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [NuGetSymbolPackageVersions23] 
      FROM [NuGetSymbolPackageVersions23] NSPV23
      JOIN DuplicateNuGetSymbolPackageVersions23 DPN
        ON NSPV23.[Feed_Id] = DPN.[Feed_Id]
       AND NSPV23.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [NuGetSymbolPackageVersions23]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [NuGetSymbolPackageVersions23] NSPV23
      JOIN #DuplicatePackageVersionIds DPN ON NSPV23.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing UniversalFeedPackageVersions...'
    ;WITH DuplicateUniversalFeedPackageVersions AS
    (
        SELECT UFPV.[Feed_Id], DPV.[PackageVersion_Id]
          FROM [UniversalFeedPackageVersions] UFPV
               JOIN #DuplicatePackageVersionIds DPV
                 ON UFPV.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY UFPV.[Feed_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [UniversalFeedPackageVersions] 
      FROM [UniversalFeedPackageVersions] UFPV
      JOIN DuplicateUniversalFeedPackageVersions DPN
        ON UFPV.[Feed_Id] = DPN.[Feed_Id]
       AND UFPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [UniversalFeedPackageVersions]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [UniversalFeedPackageVersions] UFPV
      JOIN #DuplicatePackageVersionIds DPN ON UFPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing FeedPackageVersionAnalyses...'
    ;WITH DuplicateFeedPackageVersionAnalyses AS
    (
        SELECT FPVA.[Feed_Id], DPV.[PackageVersion_Id]
          FROM [FeedPackageVersionAnalyses] FPVA
               JOIN #DuplicatePackageVersionIds DPV
                 ON FPVA.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY FPVA.[Feed_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [FeedPackageVersionAnalyses] 
      FROM [FeedPackageVersionAnalyses] FPVA
      JOIN DuplicateFeedPackageVersionAnalyses DPN
        ON FPVA.[Feed_Id] = DPN.[Feed_Id]
       AND FPVA.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [FeedPackageVersionAnalyses]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [FeedPackageVersionAnalyses] FPVA
      JOIN #DuplicatePackageVersionIds DPN ON FPVA.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing FeedPackageVersionStats...'
    ;WITH DuplicateFeedPackageVersionStats AS
    (
        SELECT FPVS.[Feed_Id], DPV.[PackageVersion_Id]
          FROM [FeedPackageVersionStats] FPVS
               JOIN #DuplicatePackageVersionIds DPV
                 ON FPVS.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY FPVS.[Feed_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [FeedPackageVersionStats] 
      FROM [FeedPackageVersionStats] FPVS
      JOIN DuplicateFeedPackageVersionStats DPN
        ON FPVS.[Feed_Id] = DPN.[Feed_Id]
       AND FPVS.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [FeedPackageVersionStats]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [FeedPackageVersionStats] FPVS
      JOIN #DuplicatePackageVersionIds DPN ON FPVS.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Enabling FKs to FeedPackageVersions...'
  ALTER TABLE [CondaFeedPackageVersions] CHECK CONSTRAINT [FK__CondaFeedPackageVersions__FeedPackageVersions]
  ALTER TABLE [NuGetFeedPackageVersions] CHECK CONSTRAINT [FK__NuGetFeedPackageVersions__FeedPackageVersions]
  ALTER TABLE [NuGetPackageSymbols23] CHECK CONSTRAINT [FK__NuGetPackageSymbols23__FeedPackageVersions]
  ALTER TABLE [NuGetSymbolPackageVersions23] CHECK CONSTRAINT [FK__NuGetSymbolPackageVersions23__FeedPackageVersions]
  ALTER TABLE [UniversalFeedPackageVersions] CHECK CONSTRAINT [FK__UniversalFeedPackageVersions__FeedPackageVersions]
  ALTER TABLE [FeedPackageVersionAnalyses] CHECK CONSTRAINT [FK__FeedPackageAnalyses__FeedPackageVersions]
  ALTER TABLE [FeedPackageVersionStats] CHECK CONSTRAINT [FK__FeedPackageVersionStats__FeedPackageVersions]

END

BEGIN PRINT 'Fixing FeedPackageVersions...'
    ;WITH DuplicateFeedPackageVersions AS
    (
        SELECT FPV.[Feed_Id], DPV.[PackageVersion_Id]
          FROM [FeedPackageVersions] FPV
               JOIN #DuplicatePackageVersionIds DPV
                 ON FPV.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY FPV.[Feed_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [FeedPackageVersions] 
      FROM [FeedPackageVersions] FPV
      JOIN DuplicateFeedPackageVersions DPN
        ON FPV.[Feed_Id] = DPN.[Feed_Id]
       AND FPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [FeedPackageVersions]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [FeedPackageVersions] FPV
      JOIN #DuplicatePackageVersionIds DPN ON FPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing DockerBlobPackages...'
    ;WITH DuplicateDockerBlobPackages AS
    (
        SELECT DBP.[DockerBlob_Id], DPV.[PackageVersion_Id]
          FROM [DockerBlobPackages] DBP
               JOIN #DuplicatePackageVersionIds DPV
                 ON DBP.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY DBP.[DockerBlob_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [DockerBlobPackages] 
      FROM [DockerBlobPackages] DBP
      JOIN DuplicateDockerBlobPackages DPN
        ON DBP.[DockerBlob_Id] = DPN.[DockerBlob_Id]
       AND DBP.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [DockerBlobPackages]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [DockerBlobPackages] DBP
      JOIN #DuplicatePackageVersionIds DPN ON DBP.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing EdgeNodePackageVersionStats...'
    ;WITH DuplicateEdgeNodePackageVersionStats AS
    (
        SELECT ENPVS.[ReplicationClient_Id], DPV.[PackageVersion_Id]
          FROM [EdgeNodePackageVersionStats] ENPVS
               JOIN #DuplicatePackageVersionIds DPV
                 ON ENPVS.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY ENPVS.[ReplicationClient_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [EdgeNodePackageVersionStats] 
      FROM [EdgeNodePackageVersionStats] ENPVS
      JOIN DuplicateEdgeNodePackageVersionStats DPN
        ON ENPVS.[ReplicationClient_Id] = DPN.[ReplicationClient_Id]
       AND ENPVS.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [EdgeNodePackageVersionStats]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [EdgeNodePackageVersionStats] ENPVS
      JOIN #DuplicatePackageVersionIds DPN ON ENPVS.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing LicensePackageVersionIds...'
    ;WITH DuplicateLicensePackageVersionIds AS
    (
        SELECT LPV.[License_Id], DPV.[PackageVersion_Id]
          FROM [LicensePackageVersionIds] LPV
               JOIN #DuplicatePackageVersionIds DPV
                 ON LPV.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY LPV.[License_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [LicensePackageVersionIds] 
      FROM [LicensePackageVersionIds] LPV
      JOIN DuplicateLicensePackageVersionIds DPN
        ON LPV.[License_Id] = DPN.[License_Id]
       AND LPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [LicensePackageVersionIds]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [LicensePackageVersionIds] LPV
      JOIN #DuplicatePackageVersionIds DPN ON LPV.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing PackageLicenses23...'
    ;WITH DuplicatePackageLicenses23 AS
    (
        SELECT PL23.[License_Id], DPV.[PackageVersion_Id]
          FROM [PackageLicenses23] PL23
               JOIN #DuplicatePackageVersionIds DPV
                 ON PL23.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY PL23.[License_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [PackageLicenses23] 
      FROM [PackageLicenses23] PL23
      JOIN DuplicatePackageLicenses23 DPN
        ON PL23.[License_Id] = DPN.[License_Id]
       AND PL23.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [PackageLicenses23]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [PackageLicenses23] PL23
      JOIN #DuplicatePackageVersionIds DPN ON PL23.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing ProjectBuildPackages...'
    ;WITH DuplicateProjectBuildPackages AS
    (
        SELECT PBP.[ProjectBuild_Id], DPV.[PackageVersion_Id]
          FROM [ProjectBuildPackages] PBP
               JOIN #DuplicatePackageVersionIds DPV
                 ON PBP.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY PBP.[ProjectBuild_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [ProjectBuildPackages] 
      FROM [ProjectBuildPackages] PBP
      JOIN DuplicateProjectBuildPackages DPN
        ON PBP.[ProjectBuild_Id] = DPN.[ProjectBuild_Id]
       AND PBP.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [ProjectBuildPackages]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [ProjectBuildPackages] PBP
      JOIN #DuplicatePackageVersionIds DPN ON PBP.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END

BEGIN PRINT 'Fixing ProjectIssues...'
    ;WITH DuplicateProjectIssues AS
    (
        SELECT PI.[Project_Id], DPV.[PackageVersion_Id]
          FROM [ProjectIssues] PI
               JOIN #DuplicatePackageVersionIds DPV
                 ON PI.[PackageVersion_Id] IN (DPV.[PackageVersion_Id], DPV.[Canonical_PackageVersion_Id])
         GROUP BY PI.[Project_Id], DPV.[PackageVersion_Id]
        HAVING COUNT(*) > 1
    )
    DELETE [ProjectIssues] 
      FROM [ProjectIssues] PI
      JOIN DuplicateProjectIssues DPN
        ON PI.[Project_Id] = DPN.[Project_Id]
       AND PI.[PackageVersion_Id] = DPN.[PackageVersion_Id]
    UPDATE [ProjectIssues]
       SET [PackageVersion_Id] = DPN.[Canonical_PackageVersion_Id]
      FROM [ProjectIssues] PI
      JOIN #DuplicatePackageVersionIds DPN ON PI.[PackageVersion_Id] = DPN.[PackageVersion_Id]
END


-- not sure why, but getting a FK conflict unless this is run first (and reenabled later)
ALTER TABLE [FeedPackageVersions] NOCHECK CONSTRAINT [FK__FeedPackageVersions__PackageVersionIds]

BEGIN PRINT 'Deleting Duplicate Ids...'
   DELETE [PackageVersionIds] WHERE [PackageVersion_Id] IN (SELECT [PackageVersion_Id] FROM #DuplicatePackageVersionIds)
   DELETE [PackageNameIds] WHERE [PackageName_Id] IN (SELECT [PackageName_Id] FROM #DuplicatePackageNameIds)
END

-- reenable
ALTER TABLE [FeedPackageVersions] CHECK CONSTRAINT [FK__FeedPackageVersions__PackageVersionIds]

DROP TABLE #DuplicatePackageNameIds
DROP TABLE #DuplicatePackageVersionIds
DROP TABLE #BadNuGetVersions

IF @DryMode = 'N' COMMIT ELSE ROLLBACK

END TRY BEGIN CATCH

    ROLLBACK

    DECLARE  @ErrorMessage VARCHAR(4000)
    SET @ErrorMessage = CAST(ERROR_MESSAGE() AS VARCHAR(4000));

    PRINT 'Error: ' + @ErrorMessage


    IF @DryMode = 'N' BEGIN

	INSERT INTO [LogMessages]
	(
		[Read_Indicator], [Logged_Date], [Message_Text],
		[Category_Name], [Message_Level], [Details_Bytes],
		[Feed_Id], [Connector_Id]
	)
	VALUES
	(
		'N', GETUTCDATE(), 'Error running package de-duplicator script',
		'Database', 40, CAST(@ErrorMessage AS VARBINARY(MAX)), 
        NULL,NULL
    )

     END

END CATCH

SET @SQL = 'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE'
EXEC sp_executesql @SQL