source: branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql @ 12761

Last change on this file since 12761 was 12761, checked in by dglaser, 6 years ago

#2429: Worked on the maintenance WebApp plugin:

  • Space Usage Page: Displays the number of rows and allocated disk space for every database table
  • Plugin Page: Shows unused plugins and provides functionality to delete all and specific plugins
  • FactTask Page: Allows to aggregate all Job Tasks to a single task for a given job or jobs within an selected time period
  • FactClientInfo Page: Allows to aggregate consecutive FactClientInfo entries with the same state and isallowedtocalculate flag
File size: 11.6 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.