Skip to content

Instantly share code, notes, and snippets.

@loic-sharma
Created November 7, 2017 17:02
Show Gist options
  • Save loic-sharma/39c65e3b4d02bac1f52242ef24cf3832 to your computer and use it in GitHub Desktop.
Save loic-sharma/39c65e3b4d02bac1f52242ef24cf3832 to your computer and use it in GitHub Desktop.
[NuGet] Powershell script to import Non-Community Packages to Statistics Database
# 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;"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment