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

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

#2388:

HeuristicLab.Services.Hive.DataAccess-3.3:

  • Removed old statistics tables
  • Updated SQL Scripts

HeuristicLab.Services.WebApp-3.3:
HeuristicLab.Services.WebApp.Status-3.3:
HeuristicLab.Services.WebApp.Statistics-3.3:

  • Minor changes
File size: 8.7 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
95/* create indices */
96CREATE INDEX Index_RequiredPlugins_TaskId ON RequiredPlugins(TaskId);
97GO
98
99/* views */
100-- =============================================
101-- Author:    cneumuel
102-- Description: Returns the first StateLog entry for each job
103-- =============================================
104CREATE VIEW [dbo].[view_FirstState]
105AS
106SELECT     sl.TaskId, sl.DateTime, sl.State
107FROM         dbo.StateLog AS sl INNER JOIN
108                          (SELECT     TaskId, MIN(DateTime) AS DateTime
109                            FROM          dbo.StateLog
110                            GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
111
112GO
113
114-- =============================================
115-- Author:    cneumuel
116-- Description: Returns the last StateLog entry for each job
117-- =============================================
118CREATE VIEW [dbo].[view_LastState]
119AS
120SELECT     sl.TaskId, sl.DateTime, sl.State
121FROM         dbo.StateLog AS sl INNER JOIN
122                          (SELECT     TaskId, MAX(DateTime) AS DateTime
123                            FROM          dbo.StateLog
124                            GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
125GO
126
127/****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
128SET ANSI_NULLS ON
129GO
130SET QUOTED_IDENTIFIER ON
131GO
132-- =============================================
133-- Author:    cneumuel
134-- Create date: 19.04.2011
135-- Description: (1) Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
136--        (2) Deletes all associated jobs. This cannot be done with cascading delete,
137--              because the job table defines a INSTEAD OF DELETE trigger itself, which
138--              is not compatible with cascading deletes.
139-- =============================================
140CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS
141BEGIN
142    DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
143    DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
144END
145GO
146
147-- =============================================
148-- Author:    cneumuel
149-- Create date: 11.11.2010
150-- 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/)
151-- =============================================DeletedJobStatistics
152CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS
153BEGIN
154    SELECT
155        he.OwnerUserId AS UserId,
156        ROUND(SUM(j.ExecutionTimeMs) / 1000, 0) AS ExecutionTimeS, 
157        ROUND(ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) / 1000, 0) AS ExecutionTimeSFinishedJobs,
158        ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(s, fs.DateTime, ls.DateTime) ELSE 0 END), 0) AS StartToEndTimeS
159    FROM
160        deleted j,
161        Job he,
162        view_FirstState fs,
163        view_LastState ls
164    WHERE
165        he.JobId = j.JobId AND
166        fs.TaskId = j.TaskId AND
167        ls.TaskId = j.TaskId
168    GROUP BY he.OwnerUserId
169
170    -- recursively delete jobs
171    CREATE TABLE #Table(
172        TaskId uniqueidentifier
173    )
174    INSERT INTO #Table (TaskId)
175    SELECT TaskId FROM deleted
176   
177    DECLARE @c INT
178    SET @c = 0
179   
180    WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
181        SELECT @c = COUNT(TaskId) FROM #Table
182       
183        INSERT INTO #Table (TaskId)
184            SELECT Task.TaskId
185            FROM Task
186            LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
187            WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
188                AND #Table.TaskId IS NULL
189    END
190   
191    DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
192    DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
193END
194GO
195
196-- ============================================================
197-- Description: Create indices to speed up execution of queries
198-- ============================================================
199
200-- speed up joins between Job and Task
201CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
202ON [dbo].[Task] ([JobId])
203INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command],[IsPrivileged])
204GO
205
206-- this is an index to speed up the GetWaitingTasks() method
207CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
208ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
209INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId],[IsPrivileged])
210GO
Note: See TracBrowser for help on using the repository browser.