source: branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/SQL Scripts/Prepare Hive Database.sql @ 6688

USE [HeuristicLab.Hive-3.4]
/* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
/* adds default values */
/* creates delete and update cascades */
/* creates indices */
/* creates views */
/* creates triggers */


ALTER TABLE dbo.AssignedResources ALTER COLUMN AssignedRessourcesId ADD ROWGUIDCOL;
ALTER TABLE dbo.AssignedResources WITH NOCHECK ADD CONSTRAINT [DF_AssignedResources_AssignedRessourcesId] DEFAULT (NEWSEQUENTIALID()) FOR AssignedRessourcesId;
ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Job_AssignedResource]
ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Job_AssignedResource] FOREIGN KEY([JobId])
REFERENCES [dbo].[Job] ([JobId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Resource_AssignedResource]
ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedResource] FOREIGN KEY([ResourceId])
REFERENCES [dbo].[Resource] ([ResourceId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId;
GO

ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Job_StateLog]
ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Job_StateLog] FOREIGN KEY([JobId])
REFERENCES [dbo].[Job] ([JobId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Resource_StateLog]
ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY([SlaveId])
REFERENCES [dbo].[Resource] ([ResourceId])
ON UPDATE CASCADE
ON DELETE SET NULL
GO

ALTER TABLE dbo.Plugin ALTER COLUMN PluginId ADD ROWGUIDCOL;
ALTER TABLE dbo.Plugin WITH NOCHECK ADD CONSTRAINT [DF_Plugin_PluginId] DEFAULT (NEWSEQUENTIALID()) FOR PluginId;

ALTER TABLE dbo.PluginData ALTER COLUMN PluginDataId ADD ROWGUIDCOL;
ALTER TABLE dbo.PluginData WITH NOCHECK ADD CONSTRAINT [DF_PluginData_PluginDataId] DEFAULT (NEWSEQUENTIALID()) FOR PluginDataId;

ALTER TABLE [dbo].[PluginData]  DROP  CONSTRAINT [Plugin_PluginData]
ALTER TABLE [dbo].[PluginData]  WITH CHECK ADD  CONSTRAINT [Plugin_PluginData] FOREIGN KEY([PluginId])
REFERENCES [dbo].[Plugin] ([PluginId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId ADD ROWGUIDCOL;
ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (NEWSEQUENTIALID()) FOR RequiredPluginId;

ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Job_RequiredPlugin]
ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Job_RequiredPlugin] FOREIGN KEY([JobId])
REFERENCES [dbo].[Job] ([JobId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Plugin_RequiredPlugin]
ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY([PluginId])
REFERENCES [dbo].[Plugin] ([PluginId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE dbo.Resource ALTER COLUMN ResourceId ADD ROWGUIDCOL;
ALTER TABLE dbo.Resource WITH NOCHECK ADD CONSTRAINT [DF_Resource_ResourceId] DEFAULT (NEWSEQUENTIALID()) FOR ResourceId;

ALTER TABLE dbo.Downtime ALTER COLUMN DowntimeId ADD ROWGUIDCOL;
ALTER TABLE dbo.Downtime WITH NOCHECK ADD CONSTRAINT [DF_Downtime_DowntimeId] DEFAULT (NEWSEQUENTIALID()) FOR DowntimeId;

ALTER TABLE dbo.HiveExperiment ALTER COLUMN HiveExperimentId ADD ROWGUIDCOL;
ALTER TABLE dbo.HiveExperiment WITH NOCHECK ADD CONSTRAINT [DF_HiveExperiment_HiveExperimentId] DEFAULT (NEWSEQUENTIALID()) FOR HiveExperimentId;

ALTER TABLE dbo.StateLog ALTER COLUMN StateLogId ADD ROWGUIDCOL;
ALTER TABLE dbo.StateLog WITH NOCHECK ADD CONSTRAINT [DF_StateLog_StateLogId] DEFAULT (NEWSEQUENTIALID()) FOR StateLogId;

ALTER TABLE [dbo].[HiveExperimentPermission]  DROP  CONSTRAINT [HiveExperiment_HiveExperimentPermission]
ALTER TABLE [dbo].[HiveExperimentPermission]  WITH CHECK ADD CONSTRAINT [HiveExperiment_HiveExperimentPermission] FOREIGN KEY([HiveExperimentId])
REFERENCES [dbo].[HiveExperiment] ([HiveExperimentId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Statistics] ALTER COLUMN StatisticsId ADD ROWGUIDCOL;
ALTER TABLE [dbo].[Statistics] WITH NOCHECK ADD CONSTRAINT [DF_Statistics_StatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR StatisticsId;
GO

ALTER TABLE [dbo].[DeletedJobStatistics] ALTER COLUMN DeletedJobStatisticsId ADD ROWGUIDCOL;
ALTER TABLE [dbo].[DeletedJobStatistics] WITH NOCHECK ADD CONSTRAINT [DF_DeletedJobStatistics_DeletedJobStatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR DeletedJobStatisticsId;
GO

ALTER TABLE [dbo].[SlaveStatistics]  DROP  CONSTRAINT [Statistics_SlaveStatistics]
ALTER TABLE [dbo].[SlaveStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY([StatisticsId])
REFERENCES [dbo].[Statistics] ([StatisticsId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UserStatistics]  DROP  CONSTRAINT [Statistics_UserStatistics]
ALTER TABLE [dbo].[UserStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY([StatisticsId])
REFERENCES [dbo].[Statistics] ([StatisticsId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

/* create indices */
CREATE INDEX Index_RequiredPlugins_JobId ON RequiredPlugins(JobId);
GO

/* views */
-- =============================================
-- Author:    cneumuel
-- Description: Returns the first StateLog entry for each job
-- =============================================
CREATE VIEW [dbo].[view_FirstState]
AS
SELECT     sl.JobId, sl.DateTime, sl.State
FROM         dbo.StateLog AS sl INNER JOIN
                          (SELECT     JobId, MIN(DateTime) AS DateTime
                            FROM          dbo.StateLog
                            GROUP BY JobId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.JobId = minDate.JobId

GO

-- =============================================
-- Author:    cneumuel
-- Description: Returns the last StateLog entry for each job
-- =============================================
CREATE VIEW [dbo].[view_LastState]
AS
SELECT     sl.JobId, sl.DateTime, sl.State
FROM         dbo.StateLog AS sl INNER JOIN
                          (SELECT     JobId, MAX(DateTime) AS DateTime
                            FROM          dbo.StateLog
                            GROUP BY JobId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.JobId = minDate.JobId
GO

-- =============================================
-- Author:    cneumuel
-- Description: returns aggregates statistic information for every minute
-- =============================================
CREATE VIEW [dbo].[view_Statistics]
AS
SELECT  CONVERT(VARCHAR(19), MIN(s.Timestamp), 120) AS DateTime, SUM(ss.Cores) AS Cores, SUM(ss.FreeCores) AS FreeCores, 
        AVG(ss.CpuUtilization) AS CpuUtilization, SUM(ss.Memory) AS Memory, SUM(ss.FreeMemory) AS FreeMemory, x.exSum AS ExecutionTimeHours, 
        x.exFinishedSum AS ExecutionTimeFinished, x.exStartToEndSum AS StartToEndTimeFinished
FROM         dbo.SlaveStatistics AS ss INNER JOIN
                      dbo.[Statistics] AS s ON ss.StatisticsId = s.StatisticsId INNER JOIN
                      dbo.Resource AS r ON ss.SlaveId = r.ResourceId INNER JOIN
                          (SELECT     StatisticsId, SUM(ExecutionTimeMs) / 1000 / 60 / 60 AS exSum, SUM(ExecutionTimeMsFinishedJobs) / 1000 / 60 / 60 AS exFinishedSum, 
                                                   SUM(StartToEndTimeMs) / 1000 / 60 / 60 AS exStartToEndSum
                            FROM          dbo.UserStatistics AS us
                            GROUP BY StatisticsId) AS x ON s.StatisticsId = x.StatisticsId
GROUP BY s.StatisticsId, x.exSum, x.exFinishedSum, x.exStartToEndSum

/* triggers */
GO
/****** Object:  Trigger [dbo].[tr_HiveExperimentDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    cneumuel
-- Create date: 19.04.2011
-- Description: (1) Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
--        (2) Deletes all associated jobs. This cannot be done with cascading delete, 
--              because the job table defines a INSTEAD OF DELETE trigger itself, which
--              is not compatible with cascading deletes.
-- =============================================
CREATE TRIGGER [dbo].[tr_HiveExperimentDeleteCascade] ON [dbo].[HiveExperiment] INSTEAD OF DELETE AS 
BEGIN
  DELETE Job FROM deleted, Job WHERE deleted.HiveExperimentId = Job.HiveExperimentId
  DELETE HiveExperiment FROM deleted, HiveExperiment WHERE deleted.HiveExperimentId = HiveExperiment.HiveExperimentId
END
GO

-- =============================================
-- Author:    cneumuel
-- Create date: 11.11.2010
-- Description: Recursively deletes all child-jobs of a job when it is deleted. (Source: http://devio.wordpress.com/2008/05/23/recursive-delete-in-sql-server/)
-- =============================================
CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS 
BEGIN
  -- add statistics
  INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeMs, ExecutionTimeMsFinishedJobs, StartToEndTimeMs)
  SELECT 
    he.OwnerUserId AS UserId, 
    SUM(j.ExecutionTimeMs) AS ExecutionTimeMs, 
    ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) AS ExecutionTimeMsFinishedJobs,
    ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(MS,fs.DateTime,ls.DateTime) ELSE 0 END), 0) AS StartToEndMs
  FROM 
    deleted j, 
    HiveExperiment he, 
    view_FirstState fs, 
    view_LastState ls
  WHERE 
    he.HiveExperimentId = j.HiveExperimentId AND 
    fs.JobId = j.JobId AND 
    ls.JobId = j.JobId
  GROUP BY he.OwnerUserId

  -- recursively delete jobs
  CREATE TABLE #Table( 
    JobId uniqueidentifier 
  )
  INSERT INTO #Table (JobId)
  SELECT JobId FROM deleted
  
  DECLARE @c INT
  SET @c = 0
  
  WHILE @c <> (SELECT COUNT(JobId) FROM #Table) BEGIN
    SELECT @c = COUNT(JobId) FROM #Table
    
    INSERT INTO #Table (JobId)
      SELECT Job.JobId
      FROM Job
      LEFT OUTER JOIN #Table ON Job.JobId = #Table.JobId
      WHERE Job.ParentJobId IN (SELECT JobId FROM #Table)
        AND #Table.JobId IS NULL
  END
  
  DELETE JobData FROM JobData INNER JOIN #Table ON JobData.JobId = #Table.JobId
  DELETE Job FROM Job INNER JOIN #Table ON Job.JobId = #Table.JobId
END
GO

-- =============================================
-- Author:    cneumuel
-- Create date: 17.05.2011
-- Description: Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
-- =============================================
--CREATE TRIGGER [dbo].[tr_DeletedJobStats] ON [dbo].[Job] AFTER DELETE AS
--BEGIN


--END
--GO