source: branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql @ 15630

Last change on this file since 15630 was 15630, checked in by jzenisek, 3 years ago

#2839

  • updated Heartbeat processing (regarding: checking against AssignedJobResources and handling of the updated Job deletion routine)
  • updated Job deletion routine(still in progress at GenerateStatistics)
File size: 9.2 KB
Line 
1/* HeuristicLab
2 * Copyright (C) 2002-2016 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
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 */
21USE [HeuristicLab.Hive-3.3]
22
23ALTER TABLE [dbo].[AssignedProjectResource]  DROP  CONSTRAINT [Project_AssignedProjectResource]
24ALTER TABLE [dbo].[AssignedProjectResource]  WITH CHECK ADD  CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY([ProjectId])
25REFERENCES [dbo].[Project] ([ProjectId])
26ON UPDATE CASCADE
27ON DELETE CASCADE
28GO
29ALTER TABLE [dbo].[AssignedProjectResource]  DROP  CONSTRAINT [Resource_AssignedProjectResource]
30ALTER TABLE [dbo].[AssignedProjectResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY([ResourceId])
31REFERENCES [dbo].[Resource] ([ResourceId])
32ON UPDATE CASCADE
33ON DELETE CASCADE
34GO
35-- OBSOLETE (start)
36ALTER TABLE [dbo].[AssignedTaskResource]  DROP  CONSTRAINT [Task_AssignedTaskResource]
37ALTER TABLE [dbo].[AssignedTaskResource]  WITH CHECK ADD  CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY([TaskId])
38REFERENCES [dbo].[Task] ([TaskId])
39ON UPDATE CASCADE
40ON DELETE CASCADE
41GO
42ALTER TABLE [dbo].[AssignedTaskResource]  DROP  CONSTRAINT [Resource_AssignedTaskResource]
43ALTER TABLE [dbo].[AssignedTaskResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY([ResourceId])
44REFERENCES [dbo].[Resource] ([ResourceId])
45ON UPDATE CASCADE
46ON DELETE CASCADE
47GO
48-- OBSOLETE (end)
49ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Job_AssignedJobResource]
50ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY([JobId])
51REFERENCES [dbo].[Job] ([JobId])
52ON UPDATE CASCADE
53ON DELETE CASCADE
54GO
55ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Resource_AssignedJobResource]
56ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY([ResourceId])
57REFERENCES [dbo].[Resource] ([ResourceId])
58ON UPDATE CASCADE
59ON DELETE CASCADE
60GO
61
62ALTER TABLE dbo.Task ALTER COLUMN TaskId ADD ROWGUIDCOL;
63ALTER TABLE dbo.Task WITH NOCHECK ADD CONSTRAINT [DF_Task_TaskId] DEFAULT (NEWSEQUENTIALID()) FOR TaskId;
64GO
65
66ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Task_StateLog]
67ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Task_StateLog] FOREIGN KEY([TaskId])
68REFERENCES [dbo].[Task] ([TaskId])
69ON UPDATE CASCADE
70ON DELETE CASCADE
71GO
72ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Resource_StateLog]
73ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY([SlaveId])
74REFERENCES [dbo].[Resource] ([ResourceId])
75ON UPDATE CASCADE
76ON DELETE SET NULL
77GO
78
79ALTER TABLE dbo.Plugin ALTER COLUMN PluginId ADD ROWGUIDCOL;
80ALTER TABLE dbo.Plugin WITH NOCHECK ADD CONSTRAINT [DF_Plugin_PluginId] DEFAULT (NEWSEQUENTIALID()) FOR PluginId;
81
82ALTER TABLE dbo.PluginData WITH NOCHECK ADD CONSTRAINT [DF_PluginData_PluginDataId] DEFAULT (NEWSEQUENTIALID()) FOR PluginDataId;
83
84ALTER TABLE [dbo].[PluginData]  DROP  CONSTRAINT [Plugin_PluginData]
85ALTER TABLE [dbo].[PluginData]  WITH CHECK ADD  CONSTRAINT [Plugin_PluginData] FOREIGN KEY([PluginId])
86REFERENCES [dbo].[Plugin] ([PluginId])
87ON UPDATE CASCADE
88ON DELETE CASCADE
89GO
90
91ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId ADD ROWGUIDCOL;
92ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (NEWSEQUENTIALID()) FOR RequiredPluginId;
93
94ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Task_RequiredPlugin]
95ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY([TaskId])
96REFERENCES [dbo].[Task] ([TaskId])
97ON UPDATE CASCADE
98ON DELETE CASCADE
99GO
100
101ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Plugin_RequiredPlugin]
102ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY([PluginId])
103REFERENCES [dbo].[Plugin] ([PluginId])
104ON UPDATE CASCADE
105ON DELETE CASCADE
106GO
107
108ALTER TABLE dbo.Resource ALTER COLUMN ResourceId ADD ROWGUIDCOL;
109ALTER TABLE dbo.Resource WITH NOCHECK ADD CONSTRAINT [DF_Resource_ResourceId] DEFAULT (NEWSEQUENTIALID()) FOR ResourceId;
110
111ALTER TABLE dbo.Downtime ALTER COLUMN DowntimeId ADD ROWGUIDCOL;
112ALTER TABLE dbo.Downtime WITH NOCHECK ADD CONSTRAINT [DF_Downtime_DowntimeId] DEFAULT (NEWSEQUENTIALID()) FOR DowntimeId;
113
114ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
115ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId;
116ALTER TABLE [dbo].[Job]  DROP  CONSTRAINT [Project_Job]
117ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [Project_Job] FOREIGN KEY([ProjectId])
118REFERENCES [dbo].[Project] ([ProjectId])
119ON UPDATE CASCADE
120-- ON DELETE CASCADE (no "ON DELETE CASCADE" >>> if project is deleted, jobs (actually job-belonging tasks) should be aborted, but not deleted
121GO
122
123ALTER TABLE dbo.StateLog ALTER COLUMN StateLogId ADD ROWGUIDCOL;
124ALTER TABLE dbo.StateLog WITH NOCHECK ADD CONSTRAINT [DF_StateLog_StateLogId] DEFAULT (NEWSEQUENTIALID()) FOR StateLogId;
125
126ALTER TABLE [dbo].[JobPermission]  DROP  CONSTRAINT [Job_JobPermission]
127ALTER TABLE [dbo].[JobPermission]  WITH CHECK ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY([JobId])
128REFERENCES [dbo].[Job] ([JobId])
129ON UPDATE CASCADE
130ON DELETE CASCADE
131GO
132
133ALTER TABLE dbo.Project ALTER COLUMN ProjectId ADD ROWGUIDCOL;
134ALTER TABLE dbo.Project WITH NOCHECK ADD CONSTRAINT [DF_Project_ProjectId] DEFAULT (NEWSEQUENTIALID()) FOR ProjectId;
135
136ALTER TABLE [dbo].[ProjectPermission]  DROP  CONSTRAINT [Project_ProjectPermission]
137ALTER TABLE [dbo].[ProjectPermission]  WITH CHECK ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY([ProjectId])
138REFERENCES [dbo].[Project] ([ProjectId])
139ON UPDATE CASCADE
140ON DELETE CASCADE
141GO
142
143/* create indices */
144CREATE INDEX Index_RequiredPlugins_TaskId ON RequiredPlugins(TaskId);
145GO
146
147-- speed up joins between Job and Task
148CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
149ON [dbo].[Task] ([JobId])
150INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command])
151GO
152
153-- this is an index to speed up the GetWaitingTasks() method
154CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
155ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
156INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId])
157GO
158
159
160
161-- OBSOLETE (start)
162/****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
163SET ANSI_NULLS ON
164GO
165SET QUOTED_IDENTIFIER ON
166GO
167-- =============================================
168-- Author:    cneumuel
169-- Create date: 19.04.2011
170-- Description: (1) Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
171--        (2) Deletes all associated jobs. This cannot be done with cascading delete,
172--              because the job table defines a INSTEAD OF DELETE trigger itself, which
173--              is not compatible with cascading deletes.
174-- =============================================
175CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS
176BEGIN
177    DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
178    DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
179END
180GO
181
182-- =============================================
183-- Author:    cneumuel
184-- Create date: 11.11.2010
185-- 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/)
186-- =============================================DeletedJobStatistics
187CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS
188BEGIN
189    -- recursively delete jobs
190    CREATE TABLE #Table(
191        TaskId uniqueidentifier
192    )
193    INSERT INTO #Table (TaskId)
194    SELECT TaskId FROM deleted
195   
196    DECLARE @c INT
197    SET @c = 0
198   
199    WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
200        SELECT @c = COUNT(TaskId) FROM #Table
201       
202        INSERT INTO #Table (TaskId)
203            SELECT Task.TaskId
204            FROM Task
205            LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
206            WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
207                AND #Table.TaskId IS NULL
208    END
209   
210    DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
211    DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
212END
213GO
214-- OBSOLETE (end)
215
Note: See TracBrowser for help on using the repository browser.