YAMS-CreateDatabase.sql

create database YAMS
go

use YAMS
go

-- stores versioned service packages
-- each version of a package can be used in multiple clusters and node types
create table dbo.ServicePackages
(
	ServiceId nvarchar(100) not null,
	ServiceVersion nvarchar(20) not null,
	ExpectedFileCount int not null,
	constraint PK_ServicePackage primary key (ServiceId, ServiceVersion)
)

-- track, upload, and download individual files to enable differential updates
create table dbo.ServicePackageFiles
(
	ServicePackageFileId uniqueidentifier primary key,
	ServiceId nvarchar(100) not null,
	ServiceVersion nvarchar(20) not null,
	FilePath nvarchar(512) not null, -- relative to root install path
	FileContent varbinary(max) null constraint DF_FileContent default (0x),
	FileCRC varbinary(20) null constraint DF_FileCRC default (0x),
	ExpectedFileSize int not null,
	constraint FK_ServicePackageFile_ServicePackage foreign key (ServiceId, ServiceVersion)
		references ServicePackages (ServiceId, ServiceVersion),
	constraint Unique_ServicePackageFile unique (ServiceId, ServiceVersion, FilePath)
)

-- Clusters are containers of NodeTypes, maps to ClusterConfig/ClusterManifest
create table dbo.Clusters
(
	ClusterId nvarchar(100) primary key,
	Properties varbinary(max) null constraint DF_Clusters_Properties default (0x)
)

-- maps to list of NodeTypes within the ClusterConfig/ClusterManifest
create table dbo.NodeTypes
(
	NodeTypeId nvarchar(100) not null, -- map to worker role name in Azure
	ClusterId nvarchar(100) not null, -- belongs to a single cluster
	Properties varbinary(max) null constraint DF_NodeTypes_Properties default (0x)
	constraint PK_NodeTypes primary key (ClusterId, NodeTypeId),
	constraint FK_NodeType_Cluster foreign key (ClusterId) references Clusters (ClusterId)
)

-- represents one version of a service installed on a specific node type on a certain cluster
-- maps to list of "Services" within NodeType
create table dbo.ServiceConfigs
(
	ServiceConfigId uniqueidentifier primary key,
	ServiceId nvarchar(100),
	ServiceVersion nvarchar(20),
	NodeTypeId nvarchar(100),
	ClusterId nvarchar(100),
	ExecutableName nvarchar(256) not null,
	ExecutableArgs nvarchar(256) not null default(''),
	Properties varbinary(max) null constraint DF_Properties default (0x),
	constraint Unique_ServiceConfig unique (ServiceId, ServiceVersion, ClusterId, NodeTypeId),
	constraint FK_ServiceConfig_NodeType foreign key (ClusterId, NodeTypeId)
		references NodeTypes (ClusterId, NodeTypeId),
	constraint FK_ServiceConfig_ServicePackage foreign key (ServiceId, ServiceVersion)
		references ServicePackages (ServiceId, ServiceVersion)
)

go

create view dbo.vServiceConfigs
as

select
	ServiceConfigId,
	ServiceId,
	ServiceVersion,
	NodeTypeId,
	ClusterId,
	ExecutableName,
	ExecutableArgs,
	convert(nvarchar(max), Properties) as Properties
from ServiceConfigs

go

create view dbo.vServicePackageFiles
as

select
	ServicePackageFileId,
	ServiceId,
	ServiceVersion,
	FilePath, -- relative to root install path
	len(FileContent) as FileContentSize,
	convert(nvarchar(max), FileCRC) as FileCRC,
	ExpectedFileSize
from ServicePackageFiles

go

create view dbo.vServicePackages
as

select
	sp.ServiceId,
	sp.ServiceVersion,
	sp.ExpectedFileCount,
	count(spf.ServicePackageFileId) as FileCount,
	sum(case when spf.FileContentSize = spf.ExpectedFileSize then 1 else 0 end) as CompletedFileCount,
	sum(spf.ExpectedFileSize) as ExpectedPackageSize,
	sum(spf.FileContentSize) as ActualPackageSize,
	convert(numeric(9,2), sum(spf.FileContentSize)) / convert(numeric(9,2), sum(spf.ExpectedFileSize)) as PercentComplete
from ServicePackages sp
join vServicePackageFiles spf
	on sp.ServiceId = spf.ServiceId
	and sp.ServiceVersion = spf.ServiceVersion
group by
	sp.ServiceId,
	sp.ServiceVersion,
	sp.ExpectedFileCount

go

create view dbo.vClusters
as

select
	c.ClusterId,
	convert(nvarchar(max), c.Properties) as Properties,
	count(nt.NodeTypeId) as NodeTypeCount,
	count(sc.ServiceConfigId) as ConfiguredServiceCount
from Clusters c
join NodeTypes nt on nt.ClusterId = c.ClusterId
join vServiceConfigs sc on sc.ClusterId = c.ClusterId
group by
	c.ClusterId,
	c.Properties

go

create view dbo.vNodeTypes
as

select
	nt.NodeTypeId,
	nt.ClusterId,
	convert(nvarchar(max), nt.Properties) as Properties,
	count(sc.ServiceConfigId) as ServiceCount,
	count(c.ClusterId) as ClusterCount
from NodeTypes nt
join vClusters c on c.ClusterId = nt.ClusterId
join vServiceConfigs sc on sc.NodeTypeId = nt.NodeTypeId
group by
	nt.NodeTypeId,
	nt.ClusterId,
	nt.Properties

go