Schema.sql

CREATE TABLE [Account] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Account_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Account_Id] PRIMARY KEY NONCLUSTERED, 
  [AccountType] VARCHAR(50) NOT NULL, 
  [Name] NVARCHAR(100) NOT NULL, 
  [EnvironmentIds] NVARCHAR(MAX) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [Account] ADD CONSTRAINT [UQ_AccountUniqueName] UNIQUE([Name])

CREATE TABLE [ActionTemplate] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_ActionTemplate_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_ActionTemplate_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [Version] INT NOT NULL, 
  [ActionType] NVARCHAR(100) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [ActionTemplate] ADD CONSTRAINT [UQ_ActionTemplateUniqueName] UNIQUE([Name])

CREATE TABLE [ActivityLog] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_ActivityLog_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_ActivityLog_Id] PRIMARY KEY NONCLUSTERED, 
  [LogData] VARBINARY(MAX) NULL, 
  [Format] NVARCHAR(10) NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [ApiKey] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_ApiKey_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_ApiKey_Id] PRIMARY KEY NONCLUSTERED, 
  [UserId] VARCHAR(50) NOT NULL, 
  [ApiKeyHashed] NVARCHAR(100) NOT NULL, 
  [Created] DATETIMEOFFSET NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [ApiKey] ADD CONSTRAINT [UQ_ApiKeyUnique] UNIQUE([ApiKeyHashed])

CREATE TABLE [Artifact] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Artifact_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Artifact_Id] PRIMARY KEY NONCLUSTERED, 
  [Filename] NVARCHAR(100) NOT NULL, 
  [RelatedDocumentIds] NVARCHAR(MAX) NOT NULL, 
  [Created] DATETIMEOFFSET NOT NULL, 
  [ProjectId] VARCHAR(50) NULL, 
  [EnvironmentId] VARCHAR(50) NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Attachment] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Attachment_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Attachment_Id] PRIMARY KEY NONCLUSTERED, 
  [Data] VARBINARY(MAX) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Certificate] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Certificate_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Certificate_Id] PRIMARY KEY NONCLUSTERED, 
  [Thumbprint] NVARCHAR(100) NOT NULL, 
  [Name] NVARCHAR(100) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Configuration] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Configuration_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Configuration_Id] PRIMARY KEY NONCLUSTERED, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [DashboardConfiguration] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_DashboardConfiguration_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_DashboardConfiguration_Id] PRIMARY KEY NONCLUSTERED, 
  [IncludedEnvironmentIds] NVARCHAR(MAX) NOT NULL, 
  [IncludedProjectIds] NVARCHAR(MAX) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Deployment] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Deployment_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Deployment_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [Created] DATETIMEOFFSET NOT NULL, 
  [EnvironmentId] VARCHAR(50) NOT NULL, 
  [ProjectId] VARCHAR(50) NOT NULL, 
  [ReleaseId] VARCHAR(50) NOT NULL, 
  [ProjectGroupId] VARCHAR(50) NOT NULL, 
  [TaskId] VARCHAR(50) NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [DeploymentEnvironment] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_DeploymentEnvironment_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_DeploymentEnvironment_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [SortOrder] INT NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [DeploymentEnvironment] ADD CONSTRAINT [UQ_DeploymentEnvironmentNameUnique] UNIQUE([Name])

CREATE TABLE [DeploymentProcess] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_DeploymentProcess_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_DeploymentProcess_Id] PRIMARY KEY NONCLUSTERED, 
  [ProjectId] VARCHAR(50) NOT NULL, 
  [IsFrozen] BIT NOT NULL, 
  [Version] INT NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Event] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Event_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Event_Id] PRIMARY KEY NONCLUSTERED, 
  [RelatedDocumentIds] NVARCHAR(MAX) NOT NULL, 
  [ProjectId] VARCHAR(50) NULL, 
  [EnvironmentId] VARCHAR(50) NULL, 
  [Category] VARCHAR(50) NOT NULL, 
  [UserId] VARCHAR(50) NOT NULL, 
  [Username] NVARCHAR(100) NOT NULL, 
  [Occurred] DATETIMEOFFSET NOT NULL, 
  [Message] NVARCHAR(MAX) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Feed] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Feed_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Feed_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [FeedUri] NVARCHAR(100) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [Feed] ADD CONSTRAINT [UQ_FeedNameUnique] UNIQUE([Name])

CREATE TABLE [Interruption] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Interruption_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Interruption_Id] PRIMARY KEY NONCLUSTERED, 
  [Created] DATETIMEOFFSET NOT NULL, 
  [Title] NVARCHAR(100) NOT NULL, 
  [Status] VARCHAR(50) NOT NULL, 
  [RelatedDocumentIds] NVARCHAR(MAX) NOT NULL, 
  [ResponsibleTeamIds] NVARCHAR(MAX) NOT NULL, 
  [ProjectId] VARCHAR(50) NOT NULL, 
  [EnvironmentId] VARCHAR(50) NOT NULL, 
  [TaskId] VARCHAR(50) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Invitation] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Invitation_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Invitation_Id] PRIMARY KEY NONCLUSTERED, 
  [InvitationCode] NVARCHAR(100) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [LibraryVariableSet] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_LibraryVariableSet_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_LibraryVariableSet_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [VariableSetId] VARCHAR(50) NULL, 
  [ContentType] VARCHAR(50) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [LibraryVariableSet] ADD CONSTRAINT [UQ_LibraryVariableSetNameUnique] UNIQUE([Name])

CREATE TABLE [Lifecycle] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Lifecycle_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Lifecycle_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [Lifecycle] ADD CONSTRAINT [UQ_LifecycleNameUnique] UNIQUE([Name])

CREATE TABLE [Machine] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Machine_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Machine_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [IsDisabled] BIT NOT NULL, 
  [Roles] NVARCHAR(MAX) NOT NULL, 
  [EnvironmentIds] NVARCHAR(MAX) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [Machine] ADD CONSTRAINT [UQ_MachineNameUnique] UNIQUE([Name])

CREATE TABLE [NuGetPackage] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_NuGetPackage_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_NuGetPackage_Id] PRIMARY KEY NONCLUSTERED, 
  [PackageId] VARCHAR(50) NOT NULL, 
  [Version] NVARCHAR(250) NOT NULL, 
  [VersionMajor] INT NOT NULL, 
  [VersionMinor] INT NOT NULL, 
  [VersionBuild] INT NOT NULL, 
  [VersionRevision] INT NOT NULL, 
  [VersionSpecial] NVARCHAR(250) NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [OctopusServerInstance] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_OctopusServerInstance_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_OctopusServerInstance_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [LastSeen] DATETIMEOFFSET NOT NULL, 
  [Rank] VARCHAR(50) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Project] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Project_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Project_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [Slug] NVARCHAR(100) NOT NULL, 
  [IsDisabled] BIT NOT NULL, 
  [VariableSetId] VARCHAR(50) NULL, 
  [DeploymentProcessId] VARCHAR(50) NULL, 
  [ProjectGroupId] VARCHAR(50) NOT NULL, 
  [LifecycleId] VARCHAR(50) NOT NULL, 
  [AutoCreateRelease] BIT NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [Project] ADD CONSTRAINT [UQ_ProjectNameUnique] UNIQUE([Name])
ALTER TABLE [Project] ADD CONSTRAINT [UQ_ProjectSlugUnique] UNIQUE([Slug])

CREATE TABLE [ProjectGroup] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_ProjectGroup_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_ProjectGroup_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [ProjectGroup] ADD CONSTRAINT [UQ_ProjectGroupNameUnique] UNIQUE([Name])

CREATE TABLE [Release] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Release_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Release_Id] PRIMARY KEY NONCLUSTERED, 
  [Version] NVARCHAR(100) NOT NULL, 
  [Assembled] DATETIMEOFFSET NOT NULL, 
  [ProjectId] VARCHAR(50) NOT NULL, 
  [ProjectVariableSetSnapshotId] VARCHAR(50) NOT NULL, 
  [ProjectDeploymentProcessSnapshotId] VARCHAR(50) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [Release] ADD CONSTRAINT [UQ_ReleaseVersionUnique] UNIQUE([Version], [ProjectId])

CREATE TABLE [ServerTask] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_ServerTask_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_ServerTask_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(20) NOT NULL, 
  [Description] NVARCHAR(100) NOT NULL, 
  [QueueTime] DATETIMEOFFSET NOT NULL, 
  [StartTime] DATETIMEOFFSET NULL, 
  [CompletedTime] DATETIMEOFFSET NULL, 
  [ErrorMessage] NVARCHAR(MAX) NULL, 
  [ConcurrencyTag] NVARCHAR(100) NULL, 
  [State] VARCHAR(50) NOT NULL, 
  [HasPendingInterruptions] BIT NOT NULL, 
  [HasWarningsOrErrors] BIT NOT NULL, 
  [Controller] NVARCHAR(100) NULL, 
  [ProjectId] VARCHAR(50) NULL, 
  [EnvironmentId] VARCHAR(50) NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Team] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_Team_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_Team_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [MemberUserIds] NVARCHAR(MAX) NOT NULL, 
  [ProjectIds] NVARCHAR(MAX) NOT NULL, 
  [EnvironmentIds] NVARCHAR(MAX) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [Team] ADD CONSTRAINT [UQ_TeamNameUnique] UNIQUE([Name])

CREATE TABLE [User] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_User_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_User_Id] PRIMARY KEY NONCLUSTERED, 
  [Username] NVARCHAR(100) NOT NULL, 
  [IsActive] BIT NOT NULL, 
  [IsService] BIT NOT NULL, 
  [IdentificationToken] UNIQUEIDENTIFIER NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [User] ADD CONSTRAINT [UQ_UserUsernameUnique] UNIQUE([Username])

CREATE TABLE [UserRole] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_UserRole_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_UserRole_Id] PRIMARY KEY NONCLUSTERED, 
  [Name] NVARCHAR(100) NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)
ALTER TABLE [UserRole] ADD CONSTRAINT [UQ_UserRoleNameUnique] UNIQUE([Name])

CREATE TABLE [VariableSet] (
  [K] INTEGER IDENTITY(1,1) NOT NULL CONSTRAINT [IX_VariableSet_K] UNIQUE CLUSTERED, 
  [Id] VARCHAR(50) NOT NULL CONSTRAINT [PK_VariableSet_Id] PRIMARY KEY NONCLUSTERED, 
  [OwnerId] VARCHAR(50) NOT NULL, 
  [Version] INT NOT NULL, 
  [IsFrozen] BIT NOT NULL, 
  [JSON] NVARCHAR(MAX) NOT NULL
)

GO

CREATE VIEW IdsInUse AS 
  SELECT [Id], 'Account' AS [Type] FROM dbo.[Account]
  UNION ALL
  SELECT [Id], 'ActionTemplate' AS [Type] FROM dbo.[ActionTemplate]
  UNION ALL
  SELECT [Id], 'ActivityLog' AS [Type] FROM dbo.[ActivityLog]
  UNION ALL
  SELECT [Id], 'ApiKey' AS [Type] FROM dbo.[ApiKey]
  UNION ALL
  SELECT [Id], 'Artifact' AS [Type] FROM dbo.[Artifact]
  UNION ALL
  SELECT [Id], 'Attachment' AS [Type] FROM dbo.[Attachment]
  UNION ALL
  SELECT [Id], 'Certificate' AS [Type] FROM dbo.[Certificate]
  UNION ALL
  SELECT [Id], 'BackupConfiguration, BuiltInRepositoryConfiguration, License, MaintenanceConfiguration, SmtpConfiguration, UpgradeAvailability' AS [Type] FROM dbo.[Configuration]
  UNION ALL
  SELECT [Id], 'DashboardConfiguration' AS [Type] FROM dbo.[DashboardConfiguration]
  UNION ALL
  SELECT [Id], 'Deployment' AS [Type] FROM dbo.[Deployment]
  UNION ALL
  SELECT [Id], 'DeploymentEnvironment' AS [Type] FROM dbo.[DeploymentEnvironment]
  UNION ALL
  SELECT [Id], 'DeploymentProcess' AS [Type] FROM dbo.[DeploymentProcess]
  UNION ALL
  SELECT [Id], 'Event' AS [Type] FROM dbo.[Event]
  UNION ALL
  SELECT [Id], 'Feed' AS [Type] FROM dbo.[Feed]
  UNION ALL
  SELECT [Id], 'Interruption' AS [Type] FROM dbo.[Interruption]
  UNION ALL
  SELECT [Id], 'Invitation' AS [Type] FROM dbo.[Invitation]
  UNION ALL
  SELECT [Id], 'LibraryVariableSet' AS [Type] FROM dbo.[LibraryVariableSet]
  UNION ALL
  SELECT [Id], 'Lifecycle' AS [Type] FROM dbo.[Lifecycle]
  UNION ALL
  SELECT [Id], 'Machine' AS [Type] FROM dbo.[Machine]
  UNION ALL
  SELECT [Id], 'IndexedNuGetPackage' AS [Type] FROM dbo.[NuGetPackage]
  UNION ALL
  SELECT [Id], 'OctopusServerInstance' AS [Type] FROM dbo.[OctopusServerInstance]
  UNION ALL
  SELECT [Id], 'Project' AS [Type] FROM dbo.[Project]
  UNION ALL
  SELECT [Id], 'ProjectGroup' AS [Type] FROM dbo.[ProjectGroup]
  UNION ALL
  SELECT [Id], 'Release' AS [Type] FROM dbo.[Release]
  UNION ALL
  SELECT [Id], 'ServerTask' AS [Type] FROM dbo.[ServerTask]
  UNION ALL
  SELECT [Id], 'Team' AS [Type] FROM dbo.[Team]
  UNION ALL
  SELECT [Id], 'User' AS [Type] FROM dbo.[User]
  UNION ALL
  SELECT [Id], 'UserRole' AS [Type] FROM dbo.[UserRole]
  UNION ALL
  SELECT [Id], 'VariableSet' AS [Type] FROM dbo.[VariableSet]


GO

Views.sql

PRINT 'Creating views'
GO

CREATE VIEW Release_LatestByProject
AS
SELECT * FROM (
	SELECT *, ROW_NUMBER() OVER (PARTITION BY ProjectId ORDER BY Assembled desc) as RowNum from Release) rs
	WHERE RowNum = 1

GO

CREATE VIEW Release_WithDeploymentProcess
AS
SELECT [Release].[K] as Release_K
      ,[Release].[Id] as Release_Id
      ,[Release].[Version] as Release_Version
      ,[Release].[Assembled] as Release_Assembled
      ,[Release].[ProjectId] as Release_ProjectId
      ,[Release].[ProjectVariableSetSnapshotId] as Release_ProjectVariableSetSnapshotId
      ,[Release].[ProjectDeploymentProcessSnapshotId] as Release_ProjectDeploymentProcessSnapshotId
      ,[Release].[JSON] as Release_JSON
	  ,DP.[K] as DeploymentProcess_K
      ,DP.[Id] as DeploymentProcess_Id
      ,DP.[ProjectId] as DeploymentProcess_ProjectId
      ,DP.[Version] as DeploymentProcess_Version
      ,DP.[JSON] as DeploymentProcess_JSON
  FROM [dbo].[Release] as Release
  INNER JOIN [dbo].[DeploymentProcess] as DP on DP.[Id] = [Release].[ProjectDeploymentProcessSnapshotId]

GO

CREATE VIEW Dashboard
AS
SELECT
	db.Id as Id,
	db.Created as Created,
	db.ProjectId as ProjectId,
	db.EnvironmentId as EnvironmentId,
	db.ReleaseId as ReleaseId,
	db.TaskId as TaskId,
	db.[State] as [State],
	db.HasPendingInterruptions as HasPendingInterruptions,
	db.HasWarningsOrErrors as HasWarningsOrErrors,
	db.ErrorMessage as ErrorMessage,
	db.QueueTime as QueueTime,
	db.CompletedTime as CompletedTime,
	db.[Version] as [Version],
	(CASE WHEN [Rank] = 1 THEN 'C' ELSE 'P' END) as CurrentOrPrevious 
	FROM
	(
		SELECT d.Id as Id,
			d.Created as Created,
			d.ProjectId as ProjectId,
			d.EnvironmentId as EnvironmentId,
			d.ReleaseId as ReleaseId,
			d.TaskId as TaskId,
			t.[State] as [State],
			t.HasPendingInterruptions as HasPendingInterruptions,
			t.HasWarningsOrErrors as HasWarningsOrErrors,
			t.ErrorMessage as ErrorMessage,
			t.QueueTime as QueueTime,
			t.CompletedTime as CompletedTime,
			r.[Version] as [Version],
			ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId ORDER BY Created DESC) as [Rank]
		FROM [Deployment] d
			INNER JOIN [ServerTask] t on t.Id = d.TaskId
			INNER JOIN [Release] r on r.Id = d.ReleaseId
	) db
	WHERE db.Rank <= 2
GO

CREATE PROCEDURE GetNuGetPackages(
	@allowPreRelease bit = 0,
	@packageId nvarchar(250) = '',
	@latestOnly bit = 0,
	@minRow int = 0,
	@maxRow int = 30,
	@partialMatch bit = 0
) AS
BEGIN
	
	WITH PackagesCTE AS
	(
		SELECT * FROM 
		(
			SELECT *, 
				ROW_NUMBER() OVER (PARTITION BY PackageId ORDER BY VersionMajor DESC, VersionMinor DESC, VersionBuild DESC, VersionRevision DESC, CASE WHEN VersionSpecial = '' THEN 0 ELSE 1 END, VersionSpecial DESC) Recency,
		        ROW_NUMBER() OVER (ORDER BY PackageId, VersionMajor DESC, VersionMinor DESC, VersionBuild DESC, VersionRevision DESC, CASE WHEN VersionSpecial = '' THEN 0 ELSE 1 END, VersionSpecial DESC) AS RowNumber
			FROM [NuGetPackage]
			WHERE 
				((@allowPreRelease = 1) or (@allowPreRelease = 0 and VersionSpecial = '')) and
				((@packageId is null or @packageId = '') or (@partialMatch = 0 and PackageId = @packageId) or (@partialMatch = 1 and PackageId LIKE '%' + @packageId + '%'))
		) Packages
		WHERE (@latestOnly = 0 OR (@latestOnly = 1 and Recency = 1))
	)

	SELECT *, (SELECT TC=COUNT(*) FROM PackagesCTE) as TotalCount
		FROM PackagesCTE
		WHERE RowNumber >= @minRow AND RowNumber <= @maxRow ORDER BY RowNumber
END

GO