[6983] | 1 | USE [HeuristicLab.Hive-3.3]
|
---|
| 2 | /* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
|
---|
| 3 | /* adds default values */
|
---|
| 4 | /* creates delete and update cascades */
|
---|
| 5 | /* creates indices */
|
---|
| 6 | /* creates views */
|
---|
| 7 | /* creates triggers */
|
---|
| 8 |
|
---|
| 9 |
|
---|
| 10 | ALTER TABLE [dbo].[AssignedResources] DROP CONSTRAINT [Task_AssignedResource]
|
---|
| 11 | ALTER TABLE [dbo].[AssignedResources] WITH CHECK ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY([TaskId])
|
---|
| 12 | REFERENCES [dbo].[Task] ([TaskId])
|
---|
| 13 | ON UPDATE CASCADE
|
---|
| 14 | ON DELETE CASCADE
|
---|
| 15 | GO
|
---|
| 16 | ALTER TABLE [dbo].[AssignedResources] DROP CONSTRAINT [Resource_AssignedResource]
|
---|
| 17 | ALTER TABLE [dbo].[AssignedResources] WITH CHECK ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY([ResourceId])
|
---|
| 18 | REFERENCES [dbo].[Resource] ([ResourceId])
|
---|
| 19 | ON UPDATE CASCADE
|
---|
| 20 | ON DELETE CASCADE
|
---|
| 21 | GO
|
---|
| 22 |
|
---|
| 23 | ALTER TABLE dbo.Task ALTER COLUMN TaskId ADD ROWGUIDCOL;
|
---|
| 24 | ALTER TABLE dbo.Task WITH NOCHECK ADD CONSTRAINT [DF_Task_TaskId] DEFAULT (NEWSEQUENTIALID()) FOR TaskId;
|
---|
| 25 | GO
|
---|
| 26 |
|
---|
| 27 | ALTER TABLE [dbo].[StateLog] DROP CONSTRAINT [Task_StateLog]
|
---|
| 28 | ALTER TABLE [dbo].[StateLog] WITH CHECK ADD CONSTRAINT [Task_StateLog] FOREIGN KEY([TaskId])
|
---|
| 29 | REFERENCES [dbo].[Task] ([TaskId])
|
---|
| 30 | ON UPDATE CASCADE
|
---|
| 31 | ON DELETE CASCADE
|
---|
| 32 | GO
|
---|
| 33 | ALTER TABLE [dbo].[StateLog] DROP CONSTRAINT [Resource_StateLog]
|
---|
| 34 | ALTER TABLE [dbo].[StateLog] WITH CHECK ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY([SlaveId])
|
---|
| 35 | REFERENCES [dbo].[Resource] ([ResourceId])
|
---|
| 36 | ON UPDATE CASCADE
|
---|
| 37 | ON DELETE SET NULL
|
---|
| 38 | GO
|
---|
| 39 |
|
---|
| 40 | ALTER TABLE dbo.Plugin ALTER COLUMN PluginId ADD ROWGUIDCOL;
|
---|
| 41 | ALTER TABLE dbo.Plugin WITH NOCHECK ADD CONSTRAINT [DF_Plugin_PluginId] DEFAULT (NEWSEQUENTIALID()) FOR PluginId;
|
---|
| 42 |
|
---|
| 43 | ALTER TABLE dbo.PluginData ALTER COLUMN PluginDataId ADD ROWGUIDCOL;
|
---|
| 44 | ALTER TABLE dbo.PluginData WITH NOCHECK ADD CONSTRAINT [DF_PluginData_PluginDataId] DEFAULT (NEWSEQUENTIALID()) FOR PluginDataId;
|
---|
| 45 |
|
---|
| 46 | ALTER TABLE [dbo].[PluginData] DROP CONSTRAINT [Plugin_PluginData]
|
---|
| 47 | ALTER TABLE [dbo].[PluginData] WITH CHECK ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY([PluginId])
|
---|
| 48 | REFERENCES [dbo].[Plugin] ([PluginId])
|
---|
| 49 | ON UPDATE CASCADE
|
---|
| 50 | ON DELETE CASCADE
|
---|
| 51 | GO
|
---|
| 52 |
|
---|
| 53 | ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId ADD ROWGUIDCOL;
|
---|
| 54 | ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (NEWSEQUENTIALID()) FOR RequiredPluginId;
|
---|
| 55 |
|
---|
| 56 | ALTER TABLE [dbo].[RequiredPlugins] DROP CONSTRAINT [Task_RequiredPlugin]
|
---|
| 57 | ALTER TABLE [dbo].[RequiredPlugins] WITH CHECK ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY([TaskId])
|
---|
| 58 | REFERENCES [dbo].[Task] ([TaskId])
|
---|
| 59 | ON UPDATE CASCADE
|
---|
| 60 | ON DELETE CASCADE
|
---|
| 61 | GO
|
---|
| 62 |
|
---|
| 63 | ALTER TABLE [dbo].[RequiredPlugins] DROP CONSTRAINT [Plugin_RequiredPlugin]
|
---|
| 64 | ALTER TABLE [dbo].[RequiredPlugins] WITH CHECK ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY([PluginId])
|
---|
| 65 | REFERENCES [dbo].[Plugin] ([PluginId])
|
---|
| 66 | ON UPDATE CASCADE
|
---|
| 67 | ON DELETE CASCADE
|
---|
| 68 | GO
|
---|
| 69 |
|
---|
| 70 | ALTER TABLE dbo.Resource ALTER COLUMN ResourceId ADD ROWGUIDCOL;
|
---|
| 71 | ALTER TABLE dbo.Resource WITH NOCHECK ADD CONSTRAINT [DF_Resource_ResourceId] DEFAULT (NEWSEQUENTIALID()) FOR ResourceId;
|
---|
| 72 |
|
---|
| 73 | ALTER TABLE dbo.Downtime ALTER COLUMN DowntimeId ADD ROWGUIDCOL;
|
---|
| 74 | ALTER TABLE dbo.Downtime WITH NOCHECK ADD CONSTRAINT [DF_Downtime_DowntimeId] DEFAULT (NEWSEQUENTIALID()) FOR DowntimeId;
|
---|
| 75 |
|
---|
| 76 | ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
|
---|
| 77 | ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId;
|
---|
| 78 |
|
---|
| 79 | ALTER TABLE dbo.StateLog ALTER COLUMN StateLogId ADD ROWGUIDCOL;
|
---|
| 80 | ALTER TABLE dbo.StateLog WITH NOCHECK ADD CONSTRAINT [DF_StateLog_StateLogId] DEFAULT (NEWSEQUENTIALID()) FOR StateLogId;
|
---|
| 81 |
|
---|
| 82 | ALTER TABLE [dbo].[JobPermission] DROP CONSTRAINT [Job_JobPermission]
|
---|
| 83 | ALTER TABLE [dbo].[JobPermission] WITH CHECK ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY([JobId])
|
---|
| 84 | REFERENCES [dbo].[Job] ([JobId])
|
---|
| 85 | ON UPDATE CASCADE
|
---|
| 86 | ON DELETE CASCADE
|
---|
| 87 | GO
|
---|
| 88 |
|
---|
| 89 | ALTER TABLE [dbo].[Statistics] ALTER COLUMN StatisticsId ADD ROWGUIDCOL;
|
---|
| 90 | ALTER TABLE [dbo].[Statistics] WITH NOCHECK ADD CONSTRAINT [DF_Statistics_StatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR StatisticsId;
|
---|
| 91 | GO
|
---|
| 92 |
|
---|
| 93 | ALTER TABLE [dbo].[DeletedJobStatistics] ALTER COLUMN DeletedJobStatisticsId ADD ROWGUIDCOL;
|
---|
| 94 | ALTER TABLE [dbo].[DeletedJobStatistics] WITH NOCHECK ADD CONSTRAINT [DF_DeletedJobStatistics_DeletedJobStatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR DeletedJobStatisticsId;
|
---|
| 95 | GO
|
---|
| 96 |
|
---|
| 97 | ALTER TABLE [dbo].[SlaveStatistics] DROP CONSTRAINT [Statistics_SlaveStatistics]
|
---|
| 98 | ALTER TABLE [dbo].[SlaveStatistics] WITH CHECK ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY([StatisticsId])
|
---|
| 99 | REFERENCES [dbo].[Statistics] ([StatisticsId])
|
---|
| 100 | ON UPDATE CASCADE
|
---|
| 101 | ON DELETE CASCADE
|
---|
| 102 | GO
|
---|
| 103 |
|
---|
| 104 | ALTER TABLE [dbo].[UserStatistics] DROP CONSTRAINT [Statistics_UserStatistics]
|
---|
| 105 | ALTER TABLE [dbo].[UserStatistics] WITH CHECK ADD CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY([StatisticsId])
|
---|
| 106 | REFERENCES [dbo].[Statistics] ([StatisticsId])
|
---|
| 107 | ON UPDATE CASCADE
|
---|
| 108 | ON DELETE CASCADE
|
---|
| 109 | GO
|
---|
| 110 |
|
---|
| 111 | /* create indices */
|
---|
| 112 | CREATE INDEX Index_RequiredPlugins_TaskId ON RequiredPlugins(TaskId);
|
---|
| 113 | GO
|
---|
| 114 |
|
---|
| 115 | /* views */
|
---|
| 116 | -- =============================================
|
---|
| 117 | -- Author: cneumuel
|
---|
| 118 | -- Description: Returns the first StateLog entry for each job
|
---|
| 119 | -- =============================================
|
---|
| 120 | CREATE VIEW [dbo].[view_FirstState]
|
---|
| 121 | AS
|
---|
| 122 | SELECT sl.TaskId, sl.DateTime, sl.State
|
---|
| 123 | FROM dbo.StateLog AS sl INNER JOIN
|
---|
| 124 | (SELECT TaskId, MIN(DateTime) AS DateTime
|
---|
| 125 | FROM dbo.StateLog
|
---|
| 126 | GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
|
---|
| 127 |
|
---|
| 128 | GO
|
---|
| 129 |
|
---|
| 130 | -- =============================================
|
---|
| 131 | -- Author: cneumuel
|
---|
| 132 | -- Description: Returns the last StateLog entry for each job
|
---|
| 133 | -- =============================================
|
---|
| 134 | CREATE VIEW [dbo].[view_LastState]
|
---|
| 135 | AS
|
---|
| 136 | SELECT sl.TaskId, sl.DateTime, sl.State
|
---|
| 137 | FROM dbo.StateLog AS sl INNER JOIN
|
---|
| 138 | (SELECT TaskId, MAX(DateTime) AS DateTime
|
---|
| 139 | FROM dbo.StateLog
|
---|
| 140 | GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
|
---|
| 141 | GO
|
---|
| 142 |
|
---|
| 143 | -- =============================================
|
---|
| 144 | -- Author: cneumuel
|
---|
| 145 | -- Description: returns aggregates statistic information for every minute
|
---|
| 146 | -- =============================================
|
---|
| 147 | CREATE VIEW [dbo].[view_Statistics]
|
---|
| 148 | AS
|
---|
| 149 | SELECT CONVERT(VARCHAR(19), MIN(s.Timestamp), 120) AS DateTime, SUM(ss.Cores) AS Cores, SUM(ss.FreeCores) AS FreeCores,
|
---|
| 150 | AVG(ss.CpuUtilization) AS CpuUtilization, SUM(ss.Memory) AS Memory, SUM(ss.FreeMemory) AS FreeMemory, x.exSum AS ExecutionTimeHours,
|
---|
| 151 | x.exFinishedSum AS ExecutionTimeFinished, x.exStartToEndSum AS StartToEndTimeFinished
|
---|
| 152 | FROM dbo.SlaveStatistics AS ss INNER JOIN
|
---|
| 153 | dbo.[Statistics] AS s ON ss.StatisticsId = s.StatisticsId INNER JOIN
|
---|
| 154 | dbo.Resource AS r ON ss.SlaveId = r.ResourceId INNER JOIN
|
---|
| 155 | (SELECT StatisticsId, SUM(ExecutionTimeMs) / 1000 / 60 / 60 AS exSum, SUM(ExecutionTimeMsFinishedJobs) / 1000 / 60 / 60 AS exFinishedSum,
|
---|
| 156 | SUM(StartToEndTimeMs) / 1000 / 60 / 60 AS exStartToEndSum
|
---|
| 157 | FROM dbo.UserStatistics AS us
|
---|
| 158 | GROUP BY StatisticsId) AS x ON s.StatisticsId = x.StatisticsId
|
---|
| 159 | GROUP BY s.StatisticsId, x.exSum, x.exFinishedSum, x.exStartToEndSum
|
---|
| 160 |
|
---|
| 161 | /* triggers */
|
---|
| 162 | GO
|
---|
| 163 | /****** Object: Trigger [dbo].[tr_JobDeleteCascade] Script Date: 04/19/2011 16:31:53 ******/
|
---|
| 164 | SET ANSI_NULLS ON
|
---|
| 165 | GO
|
---|
| 166 | SET QUOTED_IDENTIFIER ON
|
---|
| 167 | GO
|
---|
| 168 | -- =============================================
|
---|
| 169 | -- Author: cneumuel
|
---|
| 170 | -- Create date: 19.04.2011
|
---|
| 171 | -- Description: (1) Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
|
---|
| 172 | -- (2) Deletes all associated jobs. This cannot be done with cascading delete,
|
---|
| 173 | -- because the job table defines a INSTEAD OF DELETE trigger itself, which
|
---|
| 174 | -- is not compatible with cascading deletes.
|
---|
| 175 | -- =============================================
|
---|
| 176 | CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS
|
---|
| 177 | BEGIN
|
---|
| 178 | DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
|
---|
| 179 | DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
|
---|
| 180 | END
|
---|
| 181 | GO
|
---|
| 182 |
|
---|
| 183 | -- =============================================
|
---|
| 184 | -- Author: cneumuel
|
---|
| 185 | -- Create date: 11.11.2010
|
---|
| 186 | -- 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/)
|
---|
| 187 | -- =============================================
|
---|
| 188 | CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS
|
---|
| 189 | BEGIN
|
---|
| 190 | -- add statistics
|
---|
| 191 | INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeS, ExecutionTimeSFinishedJobs, StartToEndTimeS)
|
---|
| 192 | SELECT
|
---|
| 193 | he.OwnerUserId AS UserId,
|
---|
| 194 | ROUND(SUM(j.ExecutionTimeMs) / 1000, 0) AS ExecutionTimeS,
|
---|
| 195 | ROUND(ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) / 1000, 0) AS ExecutionTimeSFinishedJobs,
|
---|
| 196 | ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(s, fs.DateTime, ls.DateTime) ELSE 0 END), 0) AS StartToEndTimeS
|
---|
| 197 | FROM
|
---|
| 198 | deleted j,
|
---|
| 199 | Job he,
|
---|
| 200 | view_FirstState fs,
|
---|
| 201 | view_LastState ls
|
---|
| 202 | WHERE
|
---|
| 203 | he.JobId = j.JobId AND
|
---|
| 204 | fs.TaskId = j.TaskId AND
|
---|
| 205 | ls.TaskId = j.TaskId
|
---|
| 206 | GROUP BY he.OwnerUserId
|
---|
| 207 |
|
---|
| 208 | -- recursively delete jobs
|
---|
| 209 | CREATE TABLE #Table(
|
---|
| 210 | TaskId uniqueidentifier
|
---|
| 211 | )
|
---|
| 212 | INSERT INTO #Table (TaskId)
|
---|
| 213 | SELECT TaskId FROM deleted
|
---|
| 214 |
|
---|
| 215 | DECLARE @c INT
|
---|
| 216 | SET @c = 0
|
---|
| 217 |
|
---|
| 218 | WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
|
---|
| 219 | SELECT @c = COUNT(TaskId) FROM #Table
|
---|
| 220 |
|
---|
| 221 | INSERT INTO #Table (TaskId)
|
---|
| 222 | SELECT Task.TaskId
|
---|
| 223 | FROM Task
|
---|
| 224 | LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
|
---|
| 225 | WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
|
---|
| 226 | AND #Table.TaskId IS NULL
|
---|
| 227 | END
|
---|
| 228 |
|
---|
| 229 | DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
|
---|
| 230 | DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
|
---|
| 231 | END
|
---|
| 232 | GO
|
---|
| 233 |
|
---|
[7185] | 234 |
|
---|
| 235 | ALTER TRIGGER [dbo].[tr_StatisticsDeleteCascade] ON [dbo].[Statistics] INSTEAD OF DELETE AS
|
---|
| 236 | BEGIN
|
---|
| 237 | DELETE SlaveStatistics FROM deleted, SlaveStatistics WHERE deleted.StatisticsId = SlaveStatistics.StatisticsId
|
---|
| 238 | -- should also remove UserStatistics here
|
---|
| 239 | DELETE [Statistics] FROM deleted, [Statistics] WHERE deleted.StatisticsId = [Statistics].StatisticsId
|
---|
| 240 | END
|
---|
| 241 | GO
|
---|
| 242 |
|
---|
| 243 |
|
---|
[6983] | 244 | -- ============================================================
|
---|
| 245 | -- Description: Create indices to speed up execution of queries
|
---|
| 246 | -- ============================================================
|
---|
| 247 |
|
---|
| 248 | -- speed up joins between Job and Task
|
---|
| 249 | CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
|
---|
| 250 | ON [dbo].[Task] ([JobId])
|
---|
| 251 | INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command],[IsPrivileged])
|
---|
| 252 | GO
|
---|
| 253 |
|
---|
| 254 | -- this is an index to speed up the GetWaitingTasks() method
|
---|
| 255 | CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
|
---|
| 256 | ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
|
---|
| 257 | INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId],[IsPrivileged])
|
---|
| 258 | GO
|
---|