vinijmoura

clear
$PAT=''
$Organization=''
$Project=""
$VariableGroup=""

$AzureDevOpsAuthenicationHeader = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$($PAT)")) }
$UriOrganization = "https://dev.azure.com/$($Organization)/"

$uriVariableGroup = "$($UriOrganization)$($Project)/_apis/distributedtask/variablegroups?groupName=$($VariableGroup)&queryOrder=IdDescending&api-version=7.1-preview.2"

vinijmoura

CREATE TABLE [dbo].[EnvironmentsApprovalsNames](
	[TeamProjectName] [varchar](100) NULL,
	[EnvironmentId] [int] NULL,
	[EnvironmentName] [varchar](150) NULL,
	[ApproverUniqueName] [varchar](100) NULL,
	[ApproverDisplayName] [varchar](100) NULL
) ON [PRIMARY]
GO

vinijmoura

Param
(
    [string]$PAT,
    [string]$Organization,
    [string]$Connstr
)

$SQLQuery = "TRUNCATE TABLE EnvironmentsApprovalsNames"
Invoke-Sqlcmd -query $SQLQuery -ConnectionString $Connstr

vinijmoura

CREATE TABLE [dbo].[Organization](
	[OrganizationId] [varchar](20) NOT NULL,
	[OrganizationName] [varchar](100) NOT NULL,
	CONSTRAINT [PK_Organization] PRIMARY KEY CLUSTERED
(
	[OrganizationId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
GO

vinijmoura

Param
(
    [string]$PAT,
    [string]$Organization,
    [string]$Connstr
)

$base64Token = [System.Convert]::ToBase64String([char[]]$PAT)
$headers = @{Authorization = 'Basic {0}' -f $base64Token};
$UriOrganization = "https://api.github.com/orgs/$($organization)"

vinijmoura

CREATE TABLE [dbo].[BuildApprovalsRequired](
	[TeamProjectName] [varchar](100) NULL,
	[BuildDefinitionId] [varchar](40) NULL,
	[BuildDefinitionName] [varchar](150) NULL,
	[BuildId] [varchar](20) NULL,
	[BuildNumber] [varchar](100) NULL,
	[BuildLink] [nvarchar](MAX) NULL,
	[BuildStageName] [varchar](100) NULL,
	[BuildEnvironmentName] [varchar](100) NULL
)

vinijmoura

Param
(
    [string]$PAT,
    [string]$Organization,
    [string]$Connstr
)

$SQLQuery = "TRUNCATE TABLE BuildApprovalsRequired"
Invoke-Sqlcmd -query $SQLQuery -ConnectionString $Connstr

vinijmoura

CREATE TABLE [dbo].[DeploymentGroupsMachinesCapabilities](
	[TeamProjectName] [varchar](100) NULL,
	[DeploymentGroupName] [varchar](100) NULL,
	[MachineName] [varchar](150) NULL,
	[CapabilityName] [varchar](150) NULL,
	[CapabilityValue] [nvarchar](MAX) NULL
	
) ON [PRIMARY]
GO

vinijmoura

Param
(
    [string]$PAT,
    [string]$Organization,
    [string]$Connstr
)

$SQLQuery = "DELETE FROM DeploymentGroupsMachinesCapabilities"
Invoke-Sqlcmd -query $SQLQuery -ConnectionString $Connstr

vinijmoura

CREATE TABLE [dbo].[RepositoriesStargazers](
	RepositoryName			    [varchar](100) NOT NULL,
	StargazerLogin			    [varchar](100) NOT NULL,
	StargazerAvatarUrl		  [nvarchar](MAX) NOT NULL,
	StargazerCreatedDate		[datetime] NOT NULL
)