[NuGet] Powershell script to import Non-Community Packages to Statistics Database

CreateNonCommunityPackageSet.ps1

# The query that should be used to generate the CSV file from the Gallery DB:
#
# SELECT pr.[Id]
# FROM [dbo].[PackageRegistrations] pr
# INNER JOIN [dbo].[PackageRegistrationOwners] pro
#  ON pr.[Key] = pro.[PackageRegistrationKey]
# INNER JOIN [dbo].[Users] u
#  ON pro.[UserKey] = u.[Key]
# WHERE u.[Username] IN ('microsoft', 'dotnetframework')
# GROUP BY pr.[Id]

Param(
    [string] $Path,
    [string] $PackageSetName = "NonCommunityPackages",
    [int] $MaxValuesPerInsert = 1000
)

if ((Test-Path $Path) -eq $false) {
    Write-Output "Path '$($Path)' does not exist" -ForegroundColor red
    return
}

Write-Output "BEGIN TRANSACTION; "

# Get the ID for the NonCommunityPackages Package Set
Write-Output ""
Write-Output " IF NOT EXISTS (SELECT * FROM [dbo].[Dimension_PackageSet] WHERE [Name] = '$($packageSetName)')"
Write-Output " BEGIN"
Write-Output "  INSERT INTO [dbo].[Dimension_PackageSet] (Name) VALUES ('$($packageSetName)')"
Write-Output " END"

Write-Output ""
Write-Output " DECLARE @NonCommunityPackagesId int = (SELECT [Id] FROM [dbo].[Dimension_PackageSet] WHERE [Name] = '$($packageSetName)');"

# Remove all packages in the current package set.
Write-Output ""
Write-Output " DELETE FROM [dbo].[Fact_Package_PackageSet]"
Write-Output " WHERE [Dimension_PackageSet_Id] = @NonCommunityPackagesId;"

# Add all packages from the exported CSV.
$packageIds = Get-Content $Path

$inserts = $packageIds.Length / $MaxValuesPerInsert

for ($i = 0; $i -lt $inserts; $i++) {
    $skip = $i * $MaxValuesPerInsert
    $take = [Math]::Min($MaxValuesPerInsert, $packageIds.Length - $skip)
    $values = $packageIds | Select-Object -Skip $skip -First $take | % {
        $packageId = $_.ToLower()
        return "  (@NonCommunityPackagesId, '$($packageId)', 0)";
    }

    Write-Output ""
    Write-Output " INSERT INTO [dbo].[Fact_Package_PackageSet] (Dimension_PackageSet_id, LowercasedPackageId, IsPrimary) VALUES"

    Write-Output ($values -Join ",`n")
    Write-Output " ;"
}

# Finish the transaction.
Write-Output ""
Write-Output "COMMIT TRANSACTION;"