USE [HeuristicLab.Hive-3.4]
/* create and initialize hive database tables */

SET ARITHABORT ON
CREATE TABLE [dbo].[AssignedResources](
  [ResourceId] UniqueIdentifier NOT NULL, 
  [JobId] UniqueIdentifier NOT NULL, 
  [AssignedRessourcesId] UniqueIdentifier NOT NULL, 
  CONSTRAINT [PK_dbo.AssignedResources] PRIMARY KEY ([AssignedRessourcesId])
  )
CREATE TABLE [dbo].[Plugin](
  [PluginId] UniqueIdentifier NOT NULL, 
  [Name] VarChar(MAX), 
  [Version] VarChar(MAX), 
  [UserId] UniqueIdentifier, 
  [DateCreated] DateTime, 
  [Hash] VarBinary(20), 
  CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId])
  )
CREATE TABLE [dbo].[RequiredPlugins](
  [RequiredPluginId] UniqueIdentifier NOT NULL, 
  [JobId] UniqueIdentifier NOT NULL, 
  [PluginId] UniqueIdentifier NOT NULL, 
  CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
  )
CREATE TABLE [dbo].[Resource](
  [ResourceId] UniqueIdentifier NOT NULL, 
  [Name] VarChar(MAX), 
  [ResourceType] NVarChar(4000) NOT NULL, 
  [ParentResourceId] UniqueIdentifier, 
  [CpuSpeed] Int, 
  [Memory] Int, 
  [Login] DateTime, 
  [SlaveState] VarChar(15), 
  [Cores] Int, 
  [FreeCores] Int, 
  [FreeMemory] Int, 
  [IsAllowedToCalculate] Bit, 
  [CpuArchitecture] VarChar(3), 
  [OperatingSystem] VarChar(MAX), 
  [LastHeartbeat] DateTime, 
  [CpuUtilization] float, 
  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
  )
CREATE TABLE [dbo].[Job](
  [JobId] UniqueIdentifier NOT NULL, 
  [JobState] VarChar(30), 
  [ExecutionTimeMs] float, 
  [LastHeartbeat] DateTime, 
  [ParentJobId] UniqueIdentifier, 
  [Priority] Int NOT NULL, 
  [CoresNeeded] Int NOT NULL, 
  [MemoryNeeded] Int NOT NULL, 
  [IsParentJob] Bit, 
  [FinishWhenChildJobsFinished] Bit, 
  [Command] VarChar(30), 
  [HiveExperimentId] UniqueIdentifier NOT NULL, 
  [IsPrivileged] Bit, 
  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
  )
CREATE TABLE [dbo].[UptimeCalendar](
  [UptimeCalendarId] UniqueIdentifier, 
  [ResourceId] UniqueIdentifier, 
  [StartDate] DateTime, 
  [EndDate] DateTime, 
  [AllDayEvent] Bit, 
  [Recurring] Bit, 
  [RecurringId] UniqueIdentifier, 
  CONSTRAINT [PK_dbo.UptimeCalendar] PRIMARY KEY ([UptimeCalendarId])
  )
CREATE TABLE [dbo].[HiveExperiment](
  [HiveExperimentId] UniqueIdentifier NOT NULL, 
  [Name] VarChar(MAX), 
  [Description] VarChar(MAX), 
  [ResourceIds] VarChar(MAX), 
  [OwnerUserId] UniqueIdentifier, 
  [DateCreated] DateTime, 
  [LastAccessed] DateTime, 
  [IsHiveEngine] Bit, 
  CONSTRAINT [PK_dbo.HiveExperiment] PRIMARY KEY ([HiveExperimentId])
  )
CREATE TABLE [dbo].[JobData](
  [JobId] UniqueIdentifier NOT NULL, 
  [Data] VarBinary(MAX), 
  [LastUpdate] DateTime, 
  CONSTRAINT [PK_dbo.JobData] PRIMARY KEY ([JobId])
  )
CREATE TABLE [dbo].[PluginData](
  [PluginDataId] UniqueIdentifier NOT NULL, 
  [PluginId] UniqueIdentifier NOT NULL, 
  [Data] VarBinary(MAX) NOT NULL, 
  [FileName] VarChar(MAX), 
  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
  )
CREATE TABLE [dbo].[StateLog](
  [StateLogId] UniqueIdentifier NOT NULL, 
  [State] VarChar(30) NOT NULL, 
  [DateTime] DateTime NOT NULL, 
  [JobId] UniqueIdentifier NOT NULL, 
  [UserId] UniqueIdentifier, 
  [SlaveId] UniqueIdentifier, 
  [Exception] VarChar(MAX), 
  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
  )
CREATE TABLE [dbo].[HiveExperimentPermission](
  [HiveExperimentId] UniqueIdentifier NOT NULL, 
  [GrantedUserId] UniqueIdentifier NOT NULL, 
  [GrantedByUserId] UniqueIdentifier NOT NULL, 
  [Permission] VarChar(15) NOT NULL, 
  CONSTRAINT [PK_dbo.HiveExperimentPermission] PRIMARY KEY ([HiveExperimentId], [GrantedUserId])
  )
CREATE TABLE [Lifecycle](
  [LifecycleId] Int, 
  [LastCleanup] DateTime, 
  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
  )
CREATE TABLE [DeletedJobStatistics](
  [UserId] UniqueIdentifier NOT NULL, 
  [ExecutionTimeMs] float NOT NULL, 
  [ExecutionTimeMsFinishedJobs] float NOT NULL, 
  [StartToEndTimeMs] float NOT NULL, 
  [DeletedJobStatisticsId] UniqueIdentifier NOT NULL, 
  CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId])
  )
CREATE TABLE [UserStatistics](
  [StatisticsId] UniqueIdentifier NOT NULL, 
  [UserId] UniqueIdentifier NOT NULL, 
  [ExecutionTimeMs] float NOT NULL, 
  [UsedCores] Int NOT NULL, 
  [ExecutionTimeMsFinishedJobs] float NOT NULL, 
  [StartToEndTimeMs] float NOT NULL, 
  CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId])
  )
CREATE TABLE [SlaveStatistics](
  [StatisticsId] UniqueIdentifier NOT NULL, 
  [SlaveId] UniqueIdentifier NOT NULL, 
  [Cores] Int NOT NULL, 
  [FreeCores] Int NOT NULL, 
  [CpuUtilization] float NOT NULL, 
  [Memory] Int NOT NULL, 
  [FreeMemory] Int NOT NULL, 
  CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId])
  )
CREATE TABLE [Statistics](
  [StatisticsId] UniqueIdentifier NOT NULL, 
  [Timestamp] DateTime NOT NULL, 
  CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])
  )
ALTER TABLE [dbo].[AssignedResources]
  ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
ALTER TABLE [dbo].[AssignedResources]
  ADD CONSTRAINT [Job_AssignedResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
ALTER TABLE [dbo].[RequiredPlugins]
  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
ALTER TABLE [dbo].[RequiredPlugins]
  ADD CONSTRAINT [Job_RequiredPlugin] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
ALTER TABLE [dbo].[Resource]
  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
ALTER TABLE [dbo].[Job]
  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([ParentJobId]) REFERENCES [dbo].[Job]([JobId])
ALTER TABLE [dbo].[Job]
  ADD CONSTRAINT [HiveExperiment_Job] FOREIGN KEY ([HiveExperimentId]) REFERENCES [dbo].[HiveExperiment]([HiveExperimentId])
ALTER TABLE [dbo].[UptimeCalendar]
  ADD CONSTRAINT [Resource_UptimeCalendar] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
ALTER TABLE [dbo].[JobData]
  ADD CONSTRAINT [Job_JobData] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
ALTER TABLE [dbo].[PluginData]
  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
ALTER TABLE [dbo].[StateLog]
  ADD CONSTRAINT [Job_StateLog] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
ALTER TABLE [dbo].[StateLog]
  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
ALTER TABLE [dbo].[HiveExperimentPermission]
  ADD CONSTRAINT [HiveExperiment_HiveExperimentPermission] FOREIGN KEY ([HiveExperimentId]) REFERENCES [dbo].[HiveExperiment]([HiveExperimentId])
ALTER TABLE [UserStatistics]
  ADD CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
ALTER TABLE [SlaveStatistics]
  ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])