source: stable/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql @ 13321

Last change on this file since 13321 was 12963, checked in by ascheibe, 7 years ago

#2355 merged r12926, r12927 and r12959 into stable

File size: 7.4 KB
Line 
1/* HeuristicLab
2 * Copyright (C) 2002-2015 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].[AssignedResources]  DROP  CONSTRAINT [Task_AssignedResource]
24ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Task_AssignedResource] FOREIGN KEY([TaskId])
25REFERENCES [dbo].[Task] ([TaskId])
26ON UPDATE CASCADE
27ON DELETE CASCADE
28GO
29ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Resource_AssignedResource]
30ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedResource] FOREIGN KEY([ResourceId])
31REFERENCES [dbo].[Resource] ([ResourceId])
32ON UPDATE CASCADE
33ON DELETE CASCADE
34GO
35
36ALTER TABLE dbo.Task ALTER COLUMN TaskId ADD ROWGUIDCOL;
37ALTER TABLE dbo.Task WITH NOCHECK ADD CONSTRAINT [DF_Task_TaskId] DEFAULT (NEWSEQUENTIALID()) FOR TaskId;
38GO
39
40ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Task_StateLog]
41ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Task_StateLog] FOREIGN KEY([TaskId])
42REFERENCES [dbo].[Task] ([TaskId])
43ON UPDATE CASCADE
44ON DELETE CASCADE
45GO
46ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Resource_StateLog]
47ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY([SlaveId])
48REFERENCES [dbo].[Resource] ([ResourceId])
49ON UPDATE CASCADE
50ON DELETE SET NULL
51GO
52
53ALTER TABLE dbo.Plugin ALTER COLUMN PluginId ADD ROWGUIDCOL;
54ALTER TABLE dbo.Plugin WITH NOCHECK ADD CONSTRAINT [DF_Plugin_PluginId] DEFAULT (NEWSEQUENTIALID()) FOR PluginId;
55
56ALTER TABLE dbo.PluginData WITH NOCHECK ADD CONSTRAINT [DF_PluginData_PluginDataId] DEFAULT (NEWSEQUENTIALID()) FOR PluginDataId;
57
58ALTER TABLE [dbo].[PluginData]  DROP  CONSTRAINT [Plugin_PluginData]
59ALTER TABLE [dbo].[PluginData]  WITH CHECK ADD  CONSTRAINT [Plugin_PluginData] FOREIGN KEY([PluginId])
60REFERENCES [dbo].[Plugin] ([PluginId])
61ON UPDATE CASCADE
62ON DELETE CASCADE
63GO
64
65ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId ADD ROWGUIDCOL;
66ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (NEWSEQUENTIALID()) FOR RequiredPluginId;
67
68ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Task_RequiredPlugin]
69ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY([TaskId])
70REFERENCES [dbo].[Task] ([TaskId])
71ON UPDATE CASCADE
72ON DELETE CASCADE
73GO
74
75ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Plugin_RequiredPlugin]
76ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY([PluginId])
77REFERENCES [dbo].[Plugin] ([PluginId])
78ON UPDATE CASCADE
79ON DELETE CASCADE
80GO
81
82ALTER TABLE dbo.Resource ALTER COLUMN ResourceId ADD ROWGUIDCOL;
83ALTER TABLE dbo.Resource WITH NOCHECK ADD CONSTRAINT [DF_Resource_ResourceId] DEFAULT (NEWSEQUENTIALID()) FOR ResourceId;
84
85ALTER TABLE dbo.Downtime ALTER COLUMN DowntimeId ADD ROWGUIDCOL;
86ALTER TABLE dbo.Downtime WITH NOCHECK ADD CONSTRAINT [DF_Downtime_DowntimeId] DEFAULT (NEWSEQUENTIALID()) FOR DowntimeId;
87
88ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
89ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId;
90
91ALTER TABLE dbo.StateLog ALTER COLUMN StateLogId ADD ROWGUIDCOL;
92ALTER TABLE dbo.StateLog WITH NOCHECK ADD CONSTRAINT [DF_StateLog_StateLogId] DEFAULT (NEWSEQUENTIALID()) FOR StateLogId;
93
94ALTER TABLE [dbo].[JobPermission]  DROP  CONSTRAINT [Job_JobPermission]
95ALTER TABLE [dbo].[JobPermission]  WITH CHECK ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY([JobId])
96REFERENCES [dbo].[Job] ([JobId])
97ON UPDATE CASCADE
98ON DELETE CASCADE
99GO
100
101ALTER TABLE [dbo].[ResourcePermission]  DROP  CONSTRAINT [Resource_ResourcePermission]
102ALTER TABLE [dbo].[ResourcePermission]  WITH CHECK ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY([ResourceId])
103REFERENCES [dbo].[Resource] ([ResourceId])
104ON UPDATE CASCADE
105ON DELETE CASCADE
106GO
107
108/* create indices */
109CREATE INDEX Index_RequiredPlugins_TaskId ON RequiredPlugins(TaskId);
110GO
111
112-- speed up joins between Job and Task
113CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
114ON [dbo].[Task] ([JobId])
115INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command])
116GO
117
118-- this is an index to speed up the GetWaitingTasks() method
119CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
120ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
121INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId])
122GO
123
124
125/****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
126SET ANSI_NULLS ON
127GO
128SET QUOTED_IDENTIFIER ON
129GO
130-- =============================================
131-- Author:    cneumuel
132-- Create date: 19.04.2011
133-- Description: (1) Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
134--        (2) Deletes all associated jobs. This cannot be done with cascading delete,
135--              because the job table defines a INSTEAD OF DELETE trigger itself, which
136--              is not compatible with cascading deletes.
137-- =============================================
138CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS
139BEGIN
140    DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
141    DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
142END
143GO
144
145-- =============================================
146-- Author:    cneumuel
147-- Create date: 11.11.2010
148-- 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/)
149-- =============================================DeletedJobStatistics
150CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS
151BEGIN
152    -- recursively delete jobs
153    CREATE TABLE #Table(
154        TaskId uniqueidentifier
155    )
156    INSERT INTO #Table (TaskId)
157    SELECT TaskId FROM deleted
158   
159    DECLARE @c INT
160    SET @c = 0
161   
162    WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
163        SELECT @c = COUNT(TaskId) FROM #Table
164       
165        INSERT INTO #Table (TaskId)
166            SELECT Task.TaskId
167            FROM Task
168            LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
169            WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
170                AND #Table.TaskId IS NULL
171    END
172   
173    DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
174    DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
175END
176GO
177
178
Note: See TracBrowser for help on using the repository browser.