USE [HeuristicLab.Hive-3.3] /* 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] DROP CONSTRAINT [Task_AssignedResource] ALTER TABLE [dbo].[AssignedResources] WITH CHECK ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY([TaskId]) REFERENCES [dbo].[Task] ([TaskId]) 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.Task ALTER COLUMN TaskId ADD ROWGUIDCOL; ALTER TABLE dbo.Task WITH NOCHECK ADD CONSTRAINT [DF_Task_TaskId] DEFAULT (NEWSEQUENTIALID()) FOR TaskId; GO ALTER TABLE [dbo].[StateLog] DROP CONSTRAINT [Task_StateLog] ALTER TABLE [dbo].[StateLog] WITH CHECK ADD CONSTRAINT [Task_StateLog] FOREIGN KEY([TaskId]) REFERENCES [dbo].[Task] ([TaskId]) 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 [Task_RequiredPlugin] ALTER TABLE [dbo].[RequiredPlugins] WITH CHECK ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY([TaskId]) REFERENCES [dbo].[Task] ([TaskId]) 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.Job ALTER COLUMN JobId ADD ROWGUIDCOL; ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId; 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].[JobPermission] DROP CONSTRAINT [Job_JobPermission] ALTER TABLE [dbo].[JobPermission] WITH CHECK ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY([JobId]) REFERENCES [dbo].[Job] ([JobId]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[ResourcePermission] DROP CONSTRAINT [Resource_ResourcePermission] ALTER TABLE [dbo].[ResourcePermission] WITH CHECK ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY([ResourceId]) REFERENCES [dbo].[Resource] ([ResourceId]) 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_TaskId ON RequiredPlugins(TaskId); GO /* views */ -- ============================================= -- Author: cneumuel -- Description: Returns the first StateLog entry for each job -- ============================================= CREATE VIEW [dbo].[view_FirstState] AS SELECT sl.TaskId, sl.DateTime, sl.State FROM dbo.StateLog AS sl INNER JOIN (SELECT TaskId, MIN(DateTime) AS DateTime FROM dbo.StateLog GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId GO -- ============================================= -- Author: cneumuel -- Description: Returns the last StateLog entry for each job -- ============================================= CREATE VIEW [dbo].[view_LastState] AS SELECT sl.TaskId, sl.DateTime, sl.State FROM dbo.StateLog AS sl INNER JOIN (SELECT TaskId, MAX(DateTime) AS DateTime FROM dbo.StateLog GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId 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_JobDeleteCascade] 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_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS BEGIN DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId 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_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS BEGIN -- add statistics INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeS, ExecutionTimeSFinishedJobs, StartToEndTimeS) SELECT he.OwnerUserId AS UserId, ROUND(SUM(j.ExecutionTimeMs) / 1000, 0) AS ExecutionTimeS, ROUND(ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) / 1000, 0) AS ExecutionTimeSFinishedJobs, ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(s, fs.DateTime, ls.DateTime) ELSE 0 END), 0) AS StartToEndTimeS FROM deleted j, Job he, view_FirstState fs, view_LastState ls WHERE he.JobId = j.JobId AND fs.TaskId = j.TaskId AND ls.TaskId = j.TaskId GROUP BY he.OwnerUserId -- recursively delete jobs CREATE TABLE #Table( TaskId uniqueidentifier ) INSERT INTO #Table (TaskId) SELECT TaskId FROM deleted DECLARE @c INT SET @c = 0 WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN SELECT @c = COUNT(TaskId) FROM #Table INSERT INTO #Table (TaskId) SELECT Task.TaskId FROM Task LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table) AND #Table.TaskId IS NULL END DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId END GO CREATE TRIGGER [dbo].[tr_StatisticsDeleteCascade] ON [dbo].[Statistics] INSTEAD OF DELETE AS BEGIN DELETE SlaveStatistics FROM deleted, SlaveStatistics WHERE deleted.StatisticsId = SlaveStatistics.StatisticsId -- should also remove UserStatistics here DELETE [Statistics] FROM deleted, [Statistics] WHERE deleted.StatisticsId = [Statistics].StatisticsId END GO -- ============================================================ -- Description: Create indices to speed up execution of queries -- ============================================================ -- speed up joins between Job and Task CREATE NONCLUSTERED INDEX [TaskJobIdIndex] ON [dbo].[Task] ([JobId]) INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command],[IsPrivileged]) GO -- this is an index to speed up the GetWaitingTasks() method CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex] ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded]) INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId],[IsPrivileged]) GO