source: trunk/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql @ 7916

Last change on this file since 7916 was 7916, checked in by jkarder, 6 years ago

#1860:

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