[12878] | 1 | /* HeuristicLab
|
---|
[14185] | 2 | * Copyright (C) 2002-2016 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
|
---|
[12878] | 3 | *
|
---|
| 4 | * This file is part of HeuristicLab.
|
---|
| 5 | *
|
---|
| 6 | * HeuristicLab is free software: you can redistribute it and/or modify
|
---|
| 7 | * it under the terms of the GNU General Public License as published by
|
---|
| 8 | * the Free Software Foundation, either version 3 of the License, or
|
---|
| 9 | * (at your option) any later version.
|
---|
| 10 | *
|
---|
| 11 | * HeuristicLab is distributed in the hope that it will be useful,
|
---|
| 12 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
---|
| 13 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
---|
| 14 | * GNU General Public License for more details.
|
---|
| 15 | *
|
---|
| 16 | * You should have received a copy of the GNU General Public License
|
---|
| 17 | * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
|
---|
| 18 | */
|
---|
| 19 |
|
---|
| 20 | /* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
|
---|
[6983] | 21 | USE [HeuristicLab.Hive-3.3]
|
---|
| 22 |
|
---|
[15411] | 23 | ALTER TABLE [dbo].[AssignedProjectResource] DROP CONSTRAINT [Project_AssignedProjectResource]
|
---|
| 24 | ALTER TABLE [dbo].[AssignedProjectResource] WITH CHECK ADD CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY([ProjectId])
|
---|
[15378] | 25 | REFERENCES [dbo].[Project] ([ProjectId])
|
---|
[6983] | 26 | ON UPDATE CASCADE
|
---|
| 27 | ON DELETE CASCADE
|
---|
| 28 | GO
|
---|
[15411] | 29 | ALTER TABLE [dbo].[AssignedProjectResource] DROP CONSTRAINT [Resource_AssignedProjectResource]
|
---|
| 30 | ALTER TABLE [dbo].[AssignedProjectResource] WITH CHECK ADD CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY([ResourceId])
|
---|
[6983] | 31 | REFERENCES [dbo].[Resource] ([ResourceId])
|
---|
| 32 | ON UPDATE CASCADE
|
---|
| 33 | ON DELETE CASCADE
|
---|
| 34 | GO
|
---|
| 35 |
|
---|
[15411] | 36 | ALTER TABLE [dbo].[AssignedTaskResource] DROP CONSTRAINT [Task_AssignedTaskResource]
|
---|
| 37 | ALTER TABLE [dbo].[AssignedTaskResource] WITH CHECK ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY([TaskId])
|
---|
| 38 | REFERENCES [dbo].[Task] ([TaskId])
|
---|
| 39 | ON UPDATE CASCADE
|
---|
| 40 | ON DELETE CASCADE
|
---|
| 41 | GO
|
---|
| 42 | ALTER TABLE [dbo].[AssignedTaskResource] DROP CONSTRAINT [Resource_AssignedTaskResource]
|
---|
| 43 | ALTER TABLE [dbo].[AssignedTaskResource] WITH CHECK ADD CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY([ResourceId])
|
---|
| 44 | REFERENCES [dbo].[Resource] ([ResourceId])
|
---|
| 45 | ON UPDATE CASCADE
|
---|
| 46 | ON DELETE CASCADE
|
---|
| 47 | GO
|
---|
| 48 |
|
---|
[6983] | 49 | ALTER TABLE dbo.Task ALTER COLUMN TaskId ADD ROWGUIDCOL;
|
---|
| 50 | ALTER TABLE dbo.Task WITH NOCHECK ADD CONSTRAINT [DF_Task_TaskId] DEFAULT (NEWSEQUENTIALID()) FOR TaskId;
|
---|
| 51 | GO
|
---|
| 52 |
|
---|
| 53 | ALTER TABLE [dbo].[StateLog] DROP CONSTRAINT [Task_StateLog]
|
---|
| 54 | ALTER TABLE [dbo].[StateLog] WITH CHECK ADD CONSTRAINT [Task_StateLog] FOREIGN KEY([TaskId])
|
---|
| 55 | REFERENCES [dbo].[Task] ([TaskId])
|
---|
| 56 | ON UPDATE CASCADE
|
---|
| 57 | ON DELETE CASCADE
|
---|
| 58 | GO
|
---|
| 59 | ALTER TABLE [dbo].[StateLog] DROP CONSTRAINT [Resource_StateLog]
|
---|
| 60 | ALTER TABLE [dbo].[StateLog] WITH CHECK ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY([SlaveId])
|
---|
| 61 | REFERENCES [dbo].[Resource] ([ResourceId])
|
---|
| 62 | ON UPDATE CASCADE
|
---|
| 63 | ON DELETE SET NULL
|
---|
| 64 | GO
|
---|
| 65 |
|
---|
| 66 | ALTER TABLE dbo.Plugin ALTER COLUMN PluginId ADD ROWGUIDCOL;
|
---|
| 67 | ALTER TABLE dbo.Plugin WITH NOCHECK ADD CONSTRAINT [DF_Plugin_PluginId] DEFAULT (NEWSEQUENTIALID()) FOR PluginId;
|
---|
| 68 |
|
---|
| 69 | ALTER TABLE dbo.PluginData WITH NOCHECK ADD CONSTRAINT [DF_PluginData_PluginDataId] DEFAULT (NEWSEQUENTIALID()) FOR PluginDataId;
|
---|
| 70 |
|
---|
| 71 | ALTER TABLE [dbo].[PluginData] DROP CONSTRAINT [Plugin_PluginData]
|
---|
| 72 | ALTER TABLE [dbo].[PluginData] WITH CHECK ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY([PluginId])
|
---|
| 73 | REFERENCES [dbo].[Plugin] ([PluginId])
|
---|
| 74 | ON UPDATE CASCADE
|
---|
| 75 | ON DELETE CASCADE
|
---|
| 76 | GO
|
---|
| 77 |
|
---|
| 78 | ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId ADD ROWGUIDCOL;
|
---|
| 79 | ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (NEWSEQUENTIALID()) FOR RequiredPluginId;
|
---|
| 80 |
|
---|
| 81 | ALTER TABLE [dbo].[RequiredPlugins] DROP CONSTRAINT [Task_RequiredPlugin]
|
---|
| 82 | ALTER TABLE [dbo].[RequiredPlugins] WITH CHECK ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY([TaskId])
|
---|
| 83 | REFERENCES [dbo].[Task] ([TaskId])
|
---|
| 84 | ON UPDATE CASCADE
|
---|
| 85 | ON DELETE CASCADE
|
---|
| 86 | GO
|
---|
| 87 |
|
---|
| 88 | ALTER TABLE [dbo].[RequiredPlugins] DROP CONSTRAINT [Plugin_RequiredPlugin]
|
---|
| 89 | ALTER TABLE [dbo].[RequiredPlugins] WITH CHECK ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY([PluginId])
|
---|
| 90 | REFERENCES [dbo].[Plugin] ([PluginId])
|
---|
| 91 | ON UPDATE CASCADE
|
---|
| 92 | ON DELETE CASCADE
|
---|
| 93 | GO
|
---|
| 94 |
|
---|
| 95 | ALTER TABLE dbo.Resource ALTER COLUMN ResourceId ADD ROWGUIDCOL;
|
---|
| 96 | ALTER TABLE dbo.Resource WITH NOCHECK ADD CONSTRAINT [DF_Resource_ResourceId] DEFAULT (NEWSEQUENTIALID()) FOR ResourceId;
|
---|
| 97 |
|
---|
| 98 | ALTER TABLE dbo.Downtime ALTER COLUMN DowntimeId ADD ROWGUIDCOL;
|
---|
| 99 | ALTER TABLE dbo.Downtime WITH NOCHECK ADD CONSTRAINT [DF_Downtime_DowntimeId] DEFAULT (NEWSEQUENTIALID()) FOR DowntimeId;
|
---|
| 100 |
|
---|
| 101 | ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
|
---|
| 102 | ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId;
|
---|
[15378] | 103 | ALTER TABLE [dbo].[Job] DROP CONSTRAINT [Project_Job]
|
---|
| 104 | ALTER TABLE [dbo].[Job] WITH CHECK ADD CONSTRAINT [Project_Job] FOREIGN KEY([ProjectId])
|
---|
| 105 | REFERENCES [dbo].[Project] ([ProjectId])
|
---|
| 106 | ON UPDATE CASCADE
|
---|
| 107 | ON DELETE CASCADE
|
---|
| 108 | GO
|
---|
[6983] | 109 |
|
---|
| 110 | ALTER TABLE dbo.StateLog ALTER COLUMN StateLogId ADD ROWGUIDCOL;
|
---|
| 111 | ALTER TABLE dbo.StateLog WITH NOCHECK ADD CONSTRAINT [DF_StateLog_StateLogId] DEFAULT (NEWSEQUENTIALID()) FOR StateLogId;
|
---|
| 112 |
|
---|
| 113 | ALTER TABLE [dbo].[JobPermission] DROP CONSTRAINT [Job_JobPermission]
|
---|
| 114 | ALTER TABLE [dbo].[JobPermission] WITH CHECK ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY([JobId])
|
---|
| 115 | REFERENCES [dbo].[Job] ([JobId])
|
---|
| 116 | ON UPDATE CASCADE
|
---|
| 117 | ON DELETE CASCADE
|
---|
| 118 | GO
|
---|
| 119 |
|
---|
[7916] | 120 | ALTER TABLE [dbo].[ResourcePermission] DROP CONSTRAINT [Resource_ResourcePermission]
|
---|
| 121 | ALTER TABLE [dbo].[ResourcePermission] WITH CHECK ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY([ResourceId])
|
---|
| 122 | REFERENCES [dbo].[Resource] ([ResourceId])
|
---|
| 123 | ON UPDATE CASCADE
|
---|
| 124 | ON DELETE CASCADE
|
---|
| 125 | GO
|
---|
| 126 |
|
---|
[15378] | 127 | ALTER TABLE dbo.Project ALTER COLUMN ProjectId ADD ROWGUIDCOL;
|
---|
| 128 | ALTER TABLE dbo.Project WITH NOCHECK ADD CONSTRAINT [DF_Project_ProjectId] DEFAULT (NEWSEQUENTIALID()) FOR ProjectId;
|
---|
| 129 |
|
---|
| 130 | ALTER TABLE [dbo].[ProjectPermission] DROP CONSTRAINT [Project_ProjectPermission]
|
---|
| 131 | ALTER TABLE [dbo].[ProjectPermission] WITH CHECK ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY([ProjectId])
|
---|
| 132 | REFERENCES [dbo].[Project] ([ProjectId])
|
---|
| 133 | ON UPDATE CASCADE
|
---|
| 134 | ON DELETE CASCADE
|
---|
| 135 | GO
|
---|
| 136 |
|
---|
[6983] | 137 | /* create indices */
|
---|
| 138 | CREATE INDEX Index_RequiredPlugins_TaskId ON RequiredPlugins(TaskId);
|
---|
| 139 | GO
|
---|
| 140 |
|
---|
[12878] | 141 | -- speed up joins between Job and Task
|
---|
| 142 | CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
|
---|
| 143 | ON [dbo].[Task] ([JobId])
|
---|
[12926] | 144 | INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command])
|
---|
[6983] | 145 | GO
|
---|
| 146 |
|
---|
[12878] | 147 | -- this is an index to speed up the GetWaitingTasks() method
|
---|
| 148 | CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
|
---|
| 149 | ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
|
---|
[12926] | 150 | INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId])
|
---|
[6983] | 151 | GO
|
---|
| 152 |
|
---|
| 153 |
|
---|
| 154 | /****** Object: Trigger [dbo].[tr_JobDeleteCascade] Script Date: 04/19/2011 16:31:53 ******/
|
---|
| 155 | SET ANSI_NULLS ON
|
---|
| 156 | GO
|
---|
| 157 | SET QUOTED_IDENTIFIER ON
|
---|
| 158 | GO
|
---|
| 159 | -- =============================================
|
---|
| 160 | -- Author: cneumuel
|
---|
| 161 | -- Create date: 19.04.2011
|
---|
| 162 | -- Description: (1) Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
|
---|
| 163 | -- (2) Deletes all associated jobs. This cannot be done with cascading delete,
|
---|
| 164 | -- because the job table defines a INSTEAD OF DELETE trigger itself, which
|
---|
| 165 | -- is not compatible with cascading deletes.
|
---|
| 166 | -- =============================================
|
---|
| 167 | CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS
|
---|
| 168 | BEGIN
|
---|
[12878] | 169 | DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
|
---|
| 170 | DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
|
---|
[6983] | 171 | END
|
---|
| 172 | GO
|
---|
| 173 |
|
---|
| 174 | -- =============================================
|
---|
| 175 | -- Author: cneumuel
|
---|
| 176 | -- Create date: 11.11.2010
|
---|
| 177 | -- 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/)
|
---|
[12878] | 178 | -- =============================================DeletedJobStatistics
|
---|
[6983] | 179 | CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS
|
---|
| 180 | BEGIN
|
---|
[12878] | 181 | -- recursively delete jobs
|
---|
| 182 | CREATE TABLE #Table(
|
---|
| 183 | TaskId uniqueidentifier
|
---|
| 184 | )
|
---|
| 185 | INSERT INTO #Table (TaskId)
|
---|
| 186 | SELECT TaskId FROM deleted
|
---|
| 187 |
|
---|
| 188 | DECLARE @c INT
|
---|
| 189 | SET @c = 0
|
---|
| 190 |
|
---|
| 191 | WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
|
---|
| 192 | SELECT @c = COUNT(TaskId) FROM #Table
|
---|
| 193 |
|
---|
| 194 | INSERT INTO #Table (TaskId)
|
---|
| 195 | SELECT Task.TaskId
|
---|
| 196 | FROM Task
|
---|
| 197 | LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
|
---|
| 198 | WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
|
---|
| 199 | AND #Table.TaskId IS NULL
|
---|
| 200 | END
|
---|
| 201 |
|
---|
| 202 | DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
|
---|
| 203 | DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
|
---|
[6983] | 204 | END
|
---|
| 205 | GO
|
---|
| 206 |
|
---|
[7185] | 207 |
|
---|