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

Last change on this file since 9665 was 9665, checked in by ascheibe, 9 years ago

#2030 merged hive performance branch back into trunk

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