Index: branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/CreateHiveDatabaseApplication.cs
===================================================================
--- branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/CreateHiveDatabaseApplication.cs	(revision 6419)
+++ branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/CreateHiveDatabaseApplication.cs	(revision 6431)
@@ -1,5 +1,5 @@
 ﻿#region License Information
 /* HeuristicLab
- * Copyright (C) 2002-2010 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
+ * Copyright (C) 2002-2011 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
  *
  * This file is part of HeuristicLab.
@@ -20,7 +20,7 @@
 #endregion
 
-using HeuristicLab.PluginInfrastructure;
 using System.IO;
 using System.Windows.Forms;
+using HeuristicLab.PluginInfrastructure;
 
 namespace HeuristicLab.Services.Hive.DataAccess {
Index: branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/Initialize Hive Database.sql
===================================================================
--- branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/Initialize Hive Database.sql	(revision 6431)
+++ branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/Initialize Hive Database.sql	(revision 6431)
@@ -0,0 +1,178 @@
+﻿USE [HeuristicLab.Hive-3.4]
+/* create and initialize hive database tables */
+
+SET ARITHABORT ON
+CREATE TABLE [dbo].[AssignedResources](
+  [ResourceId] UniqueIdentifier NOT NULL, 
+  [JobId] UniqueIdentifier NOT NULL, 
+  [AssignedRessourcesId] UniqueIdentifier NOT NULL, 
+  CONSTRAINT [PK_dbo.AssignedResources] PRIMARY KEY ([AssignedRessourcesId])
+  )
+CREATE TABLE [dbo].[Plugin](
+  [PluginId] UniqueIdentifier NOT NULL, 
+  [Name] VarChar(MAX), 
+  [Version] VarChar(MAX), 
+  [UserId] UniqueIdentifier, 
+  [DateCreated] DateTime, 
+  [Hash] VarBinary(20), 
+  CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId])
+  )
+CREATE TABLE [dbo].[RequiredPlugins](
+  [RequiredPluginId] UniqueIdentifier NOT NULL, 
+  [JobId] UniqueIdentifier NOT NULL, 
+  [PluginId] UniqueIdentifier NOT NULL, 
+  CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
+  )
+CREATE TABLE [dbo].[Resource](
+  [ResourceId] UniqueIdentifier NOT NULL, 
+  [Name] VarChar(MAX), 
+  [ResourceType] NVarChar(4000) NOT NULL, 
+  [ParentResourceId] UniqueIdentifier, 
+  [CpuSpeed] Int, 
+  [Memory] Int, 
+  [Login] DateTime, 
+  [SlaveState] VarChar(15), 
+  [Cores] Int, 
+  [FreeCores] Int, 
+  [FreeMemory] Int, 
+  [IsAllowedToCalculate] Bit, 
+  [CpuArchitecture] VarChar(3), 
+  [OperatingSystem] VarChar(MAX), 
+  [LastHeartbeat] DateTime, 
+  [CpuUtilization] float, 
+  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
+  )
+CREATE TABLE [dbo].[Job](
+  [JobId] UniqueIdentifier NOT NULL, 
+  [JobState] VarChar(30), 
+  [ExecutionTimeMs] float, 
+  [LastHeartbeat] DateTime, 
+  [ParentJobId] UniqueIdentifier, 
+  [Priority] Int NOT NULL, 
+  [CoresNeeded] Int NOT NULL, 
+  [MemoryNeeded] Int NOT NULL, 
+  [IsParentJob] Bit, 
+  [FinishWhenChildJobsFinished] Bit, 
+  [Command] VarChar(30), 
+  [HiveExperimentId] UniqueIdentifier NOT NULL, 
+  [IsPrivileged] Bit, 
+  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
+  )
+CREATE TABLE [dbo].[UptimeCalendar](
+  [UptimeCalendarId] UniqueIdentifier, 
+  [ResourceId] UniqueIdentifier, 
+  [StartDate] DateTime, 
+  [EndDate] DateTime, 
+  [AllDayEvent] Bit, 
+  [Recurring] Bit, 
+  [RecurringId] UniqueIdentifier, 
+  CONSTRAINT [PK_dbo.UptimeCalendar] PRIMARY KEY ([UptimeCalendarId])
+  )
+CREATE TABLE [dbo].[HiveExperiment](
+  [HiveExperimentId] UniqueIdentifier NOT NULL, 
+  [Name] VarChar(MAX), 
+  [Description] VarChar(MAX), 
+  [ResourceIds] VarChar(MAX), 
+  [OwnerUserId] UniqueIdentifier, 
+  [DateCreated] DateTime, 
+  [LastAccessed] DateTime, 
+  [IsHiveEngine] Bit, 
+  CONSTRAINT [PK_dbo.HiveExperiment] PRIMARY KEY ([HiveExperimentId])
+  )
+CREATE TABLE [dbo].[JobData](
+  [JobId] UniqueIdentifier NOT NULL, 
+  [Data] VarBinary(MAX), 
+  [LastUpdate] DateTime, 
+  CONSTRAINT [PK_dbo.JobData] PRIMARY KEY ([JobId])
+  )
+CREATE TABLE [dbo].[PluginData](
+  [PluginDataId] UniqueIdentifier NOT NULL, 
+  [PluginId] UniqueIdentifier NOT NULL, 
+  [Data] VarBinary(MAX) NOT NULL, 
+  [FileName] VarChar(MAX), 
+  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
+  )
+CREATE TABLE [dbo].[StateLog](
+  [StateLogId] UniqueIdentifier NOT NULL, 
+  [State] VarChar(30) NOT NULL, 
+  [DateTime] DateTime NOT NULL, 
+  [JobId] UniqueIdentifier NOT NULL, 
+  [UserId] UniqueIdentifier, 
+  [SlaveId] UniqueIdentifier, 
+  [Exception] VarChar(MAX), 
+  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
+  )
+CREATE TABLE [dbo].[HiveExperimentPermission](
+  [HiveExperimentId] UniqueIdentifier NOT NULL, 
+  [GrantedUserId] UniqueIdentifier NOT NULL, 
+  [GrantedByUserId] UniqueIdentifier NOT NULL, 
+  [Permission] VarChar(15) NOT NULL, 
+  CONSTRAINT [PK_dbo.HiveExperimentPermission] PRIMARY KEY ([HiveExperimentId], [GrantedUserId])
+  )
+CREATE TABLE [Lifecycle](
+  [LifecycleId] Int, 
+  [LastCleanup] DateTime, 
+  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
+  )
+CREATE TABLE [DeletedJobStatistics](
+  [UserId] UniqueIdentifier NOT NULL, 
+  [ExecutionTimeMs] float NOT NULL, 
+  [ExecutionTimeMsFinishedJobs] float NOT NULL, 
+  [StartToEndTimeMs] float NOT NULL, 
+  [DeletedJobStatisticsId] UniqueIdentifier NOT NULL, 
+  CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId])
+  )
+CREATE TABLE [UserStatistics](
+  [StatisticsId] UniqueIdentifier NOT NULL, 
+  [UserId] UniqueIdentifier NOT NULL, 
+  [ExecutionTimeMs] float NOT NULL, 
+  [UsedCores] Int NOT NULL, 
+  [ExecutionTimeMsFinishedJobs] float NOT NULL, 
+  [StartToEndTimeMs] float NOT NULL, 
+  CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId])
+  )
+CREATE TABLE [SlaveStatistics](
+  [StatisticsId] UniqueIdentifier NOT NULL, 
+  [SlaveId] UniqueIdentifier NOT NULL, 
+  [Cores] Int NOT NULL, 
+  [FreeCores] Int NOT NULL, 
+  [CpuUtilization] float NOT NULL, 
+  [Memory] Int NOT NULL, 
+  [FreeMemory] Int NOT NULL, 
+  CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId])
+  )
+CREATE TABLE [Statistics](
+  [StatisticsId] UniqueIdentifier NOT NULL, 
+  [Timestamp] DateTime NOT NULL, 
+  CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])
+  )
+ALTER TABLE [dbo].[AssignedResources]
+  ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+ALTER TABLE [dbo].[AssignedResources]
+  ADD CONSTRAINT [Job_AssignedResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+ALTER TABLE [dbo].[RequiredPlugins]
+  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
+ALTER TABLE [dbo].[RequiredPlugins]
+  ADD CONSTRAINT [Job_RequiredPlugin] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+ALTER TABLE [dbo].[Resource]
+  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+ALTER TABLE [dbo].[Job]
+  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([ParentJobId]) REFERENCES [dbo].[Job]([JobId])
+ALTER TABLE [dbo].[Job]
+  ADD CONSTRAINT [HiveExperiment_Job] FOREIGN KEY ([HiveExperimentId]) REFERENCES [dbo].[HiveExperiment]([HiveExperimentId])
+ALTER TABLE [dbo].[UptimeCalendar]
+  ADD CONSTRAINT [Resource_UptimeCalendar] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+ALTER TABLE [dbo].[JobData]
+  ADD CONSTRAINT [Job_JobData] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+ALTER TABLE [dbo].[PluginData]
+  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
+ALTER TABLE [dbo].[StateLog]
+  ADD CONSTRAINT [Job_StateLog] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+ALTER TABLE [dbo].[StateLog]
+  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
+ALTER TABLE [dbo].[HiveExperimentPermission]
+  ADD CONSTRAINT [HiveExperiment_HiveExperimentPermission] FOREIGN KEY ([HiveExperimentId]) REFERENCES [dbo].[HiveExperiment]([HiveExperimentId])
+ALTER TABLE [UserStatistics]
+  ADD CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
+ALTER TABLE [SlaveStatistics]
+  ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
Index: branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/Prepare Hive Database.sql
===================================================================
--- branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/Prepare Hive Database.sql	(revision 6431)
+++ branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/Prepare Hive Database.sql	(revision 6431)
@@ -0,0 +1,246 @@
+USE [HeuristicLab.Hive-3.4]
+/* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
+/* adds default values */
+/* creates delete and update cascades */
+/* creates indices */
+/* creates views */
+/* creates triggers */
+
+
+ALTER TABLE dbo.AssignedResources ALTER COLUMN AssignedRessourcesId ADD ROWGUIDCOL;
+ALTER TABLE dbo.AssignedResources WITH NOCHECK ADD CONSTRAINT [DF_AssignedResources_AssignedRessourcesId] DEFAULT (NEWSEQUENTIALID()) FOR AssignedRessourcesId;
+ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Job_AssignedResource]
+ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Job_AssignedResource] FOREIGN KEY([JobId])
+REFERENCES [dbo].[Job] ([JobId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Resource_AssignedResource]
+ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedResource] FOREIGN KEY([ResourceId])
+REFERENCES [dbo].[Resource] ([ResourceId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
+ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId;
+GO
+
+ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Job_StateLog]
+ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Job_StateLog] FOREIGN KEY([JobId])
+REFERENCES [dbo].[Job] ([JobId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Resource_StateLog]
+ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY([SlaveId])
+REFERENCES [dbo].[Resource] ([ResourceId])
+ON UPDATE CASCADE
+ON DELETE SET NULL
+GO
+
+ALTER TABLE dbo.Plugin ALTER COLUMN PluginId ADD ROWGUIDCOL;
+ALTER TABLE dbo.Plugin WITH NOCHECK ADD CONSTRAINT [DF_Plugin_PluginId] DEFAULT (NEWSEQUENTIALID()) FOR PluginId;
+
+ALTER TABLE dbo.PluginData ALTER COLUMN PluginDataId ADD ROWGUIDCOL;
+ALTER TABLE dbo.PluginData WITH NOCHECK ADD CONSTRAINT [DF_PluginData_PluginDataId] DEFAULT (NEWSEQUENTIALID()) FOR PluginDataId;
+
+ALTER TABLE [dbo].[PluginData]  DROP  CONSTRAINT [Plugin_PluginData]
+ALTER TABLE [dbo].[PluginData]  WITH CHECK ADD  CONSTRAINT [Plugin_PluginData] FOREIGN KEY([PluginId])
+REFERENCES [dbo].[Plugin] ([PluginId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId ADD ROWGUIDCOL;
+ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (NEWSEQUENTIALID()) FOR RequiredPluginId;
+
+ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Job_RequiredPlugin]
+ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Job_RequiredPlugin] FOREIGN KEY([JobId])
+REFERENCES [dbo].[Job] ([JobId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Plugin_RequiredPlugin]
+ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY([PluginId])
+REFERENCES [dbo].[Plugin] ([PluginId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE dbo.Resource ALTER COLUMN ResourceId ADD ROWGUIDCOL;
+ALTER TABLE dbo.Resource WITH NOCHECK ADD CONSTRAINT [DF_Resource_ResourceId] DEFAULT (NEWSEQUENTIALID()) FOR ResourceId;
+
+ALTER TABLE dbo.UptimeCalendar ALTER COLUMN UptimeCalendarId ADD ROWGUIDCOL;
+ALTER TABLE dbo.UptimeCalendar WITH NOCHECK ADD CONSTRAINT [DF_UptimeCalendar_UptimeCalendarId] DEFAULT (NEWSEQUENTIALID()) FOR UptimeCalendarId;
+
+ALTER TABLE dbo.HiveExperiment ALTER COLUMN HiveExperimentId ADD ROWGUIDCOL;
+ALTER TABLE dbo.HiveExperiment WITH NOCHECK ADD CONSTRAINT [DF_HiveExperiment_HiveExperimentId] DEFAULT (NEWSEQUENTIALID()) FOR HiveExperimentId;
+
+ALTER TABLE dbo.StateLog ALTER COLUMN StateLogId ADD ROWGUIDCOL;
+ALTER TABLE dbo.StateLog WITH NOCHECK ADD CONSTRAINT [DF_StateLog_StateLogId] DEFAULT (NEWSEQUENTIALID()) FOR StateLogId;
+
+ALTER TABLE [dbo].[HiveExperimentPermission]  DROP  CONSTRAINT [HiveExperiment_HiveExperimentPermission]
+ALTER TABLE [dbo].[HiveExperimentPermission]  WITH CHECK ADD CONSTRAINT [HiveExperiment_HiveExperimentPermission] FOREIGN KEY([HiveExperimentId])
+REFERENCES [dbo].[HiveExperiment] ([HiveExperimentId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[Statistics] ALTER COLUMN StatisticsId ADD ROWGUIDCOL;
+ALTER TABLE [dbo].[Statistics] WITH NOCHECK ADD CONSTRAINT [DF_Statistics_StatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR StatisticsId;
+GO
+
+ALTER TABLE [dbo].[DeletedJobStatistics] ALTER COLUMN DeletedJobStatisticsId ADD ROWGUIDCOL;
+ALTER TABLE [dbo].[DeletedJobStatistics] WITH NOCHECK ADD CONSTRAINT [DF_DeletedJobStatistics_DeletedJobStatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR DeletedJobStatisticsId;
+GO
+
+ALTER TABLE [dbo].[SlaveStatistics]  DROP  CONSTRAINT [Statistics_SlaveStatistics]
+ALTER TABLE [dbo].[SlaveStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY([StatisticsId])
+REFERENCES [dbo].[Statistics] ([StatisticsId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[UserStatistics]  DROP  CONSTRAINT [Statistics_UserStatistics]
+ALTER TABLE [dbo].[UserStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY([StatisticsId])
+REFERENCES [dbo].[Statistics] ([StatisticsId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+/* create indices */
+CREATE INDEX Index_RequiredPlugins_JobId ON RequiredPlugins(JobId);
+GO
+
+/* views */
+-- =============================================
+-- Author:		cneumuel
+-- Description:	Returns the first StateLog entry for each job
+-- =============================================
+CREATE VIEW [dbo].[view_FirstState]
+AS
+SELECT     sl.JobId, sl.DateTime, sl.State
+FROM         dbo.StateLog AS sl INNER JOIN
+                          (SELECT     JobId, MIN(DateTime) AS DateTime
+                            FROM          dbo.StateLog
+                            GROUP BY JobId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.JobId = minDate.JobId
+
+GO
+
+-- =============================================
+-- Author:		cneumuel
+-- Description:	Returns the last StateLog entry for each job
+-- =============================================
+CREATE VIEW [dbo].[view_LastState]
+AS
+SELECT     sl.JobId, sl.DateTime, sl.State
+FROM         dbo.StateLog AS sl INNER JOIN
+                          (SELECT     JobId, MAX(DateTime) AS DateTime
+                            FROM          dbo.StateLog
+                            GROUP BY JobId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.JobId = minDate.JobId
+GO
+
+-- =============================================
+-- Author:		cneumuel
+-- Description:	returns aggregates statistic information for every minute
+-- =============================================
+CREATE VIEW [dbo].[view_Statistics]
+AS
+SELECT  CONVERT(VARCHAR(19), MIN(s.Timestamp), 120) AS DateTime, SUM(ss.Cores) AS Cores, SUM(ss.FreeCores) AS FreeCores, 
+        AVG(ss.CpuUtilization) AS CpuUtilization, SUM(ss.Memory) AS Memory, SUM(ss.FreeMemory) AS FreeMemory, x.exSum AS ExecutionTimeHours, 
+        x.exFinishedSum AS ExecutionTimeFinished, x.exStartToEndSum AS StartToEndTimeFinished
+FROM         dbo.SlaveStatistics AS ss INNER JOIN
+                      dbo.[Statistics] AS s ON ss.StatisticsId = s.StatisticsId INNER JOIN
+                      dbo.Resource AS r ON ss.SlaveId = r.ResourceId INNER JOIN
+                          (SELECT     StatisticsId, SUM(ExecutionTimeMs) / 1000 / 60 / 60 AS exSum, SUM(ExecutionTimeMsFinishedJobs) / 1000 / 60 / 60 AS exFinishedSum, 
+                                                   SUM(StartToEndTimeMs) / 1000 / 60 / 60 AS exStartToEndSum
+                            FROM          dbo.UserStatistics AS us
+                            GROUP BY StatisticsId) AS x ON s.StatisticsId = x.StatisticsId
+GROUP BY s.StatisticsId, x.exSum, x.exFinishedSum, x.exStartToEndSum
+
+/* triggers */
+GO
+/****** Object:  Trigger [dbo].[tr_HiveExperimentDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
+SET ANSI_NULLS ON
+GO
+SET QUOTED_IDENTIFIER ON
+GO
+-- =============================================
+-- Author:		cneumuel
+-- Create date: 19.04.2011
+-- Description:	(1) Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
+--				(2) Deletes all associated jobs. This cannot be done with cascading delete, 
+--              because the job table defines a INSTEAD OF DELETE trigger itself, which
+--              is not compatible with cascading deletes.
+-- =============================================
+CREATE TRIGGER [dbo].[tr_HiveExperimentDeleteCascade] ON [dbo].[HiveExperiment] INSTEAD OF DELETE AS 
+BEGIN
+	DELETE Job FROM deleted, Job WHERE deleted.HiveExperimentId = Job.HiveExperimentId
+	DELETE HiveExperiment FROM deleted, HiveExperiment WHERE deleted.HiveExperimentId = HiveExperiment.HiveExperimentId
+END
+GO
+
+-- =============================================
+-- Author:		cneumuel
+-- Create date: 11.11.2010
+-- 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/)
+-- =============================================
+CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS 
+BEGIN
+	-- add statistics
+	INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeMs, ExecutionTimeMsFinishedJobs, StartToEndTimeMs)
+	SELECT 
+		he.OwnerUserId AS UserId, 
+		SUM(j.ExecutionTimeMs) AS ExecutionTimeMs, 
+		ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) AS ExecutionTimeMsFinishedJobs,
+		ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(MS,fs.DateTime,ls.DateTime) ELSE 0 END), 0) AS StartToEndMs
+	FROM 
+		deleted j, 
+		HiveExperiment he, 
+		view_FirstState fs, 
+		view_LastState ls
+	WHERE 
+		he.HiveExperimentId = j.HiveExperimentId AND 
+		fs.JobId = j.JobId AND 
+		ls.JobId = j.JobId
+	GROUP BY he.OwnerUserId
+
+	-- recursively delete jobs
+	CREATE TABLE #Table( 
+		JobId uniqueidentifier 
+	)
+	INSERT INTO #Table (JobId)
+	SELECT JobId FROM deleted
+	
+	DECLARE @c INT
+	SET @c = 0
+	
+	WHILE @c <> (SELECT COUNT(JobId) FROM #Table) BEGIN
+		SELECT @c = COUNT(JobId) FROM #Table
+		
+		INSERT INTO #Table (JobId)
+			SELECT Job.JobId
+			FROM Job
+			LEFT OUTER JOIN #Table ON Job.JobId = #Table.JobId
+			WHERE Job.ParentJobId IN (SELECT JobId FROM #Table)
+				AND #Table.JobId IS NULL
+	END
+	
+	DELETE JobData FROM JobData INNER JOIN #Table ON JobData.JobId = #Table.JobId
+	DELETE Job FROM Job INNER JOIN #Table ON Job.JobId = #Table.JobId
+END
+GO
+
+-- =============================================
+-- Author:		cneumuel
+-- Create date: 17.05.2011
+-- Description:	Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
+-- =============================================
+--CREATE TRIGGER [dbo].[tr_DeletedJobStats] ON [dbo].[Job] AFTER DELETE AS
+--BEGIN
+
+
+--END
+--GO
