Index: branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql
===================================================================
--- branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql	(revision 6452)
+++ 	(revision )
@@ -1,245 +1,0 @@
-USE [HeuristicLab.Hive-3.4]
-/* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
-
-/* use these DROP commands if you want to run this query a second time on the same db */
-
-/*
-ALTER TABLE dbo.AssignedResources ALTER COLUMN AssignedRessourcesId DROP ROWGUIDCOL;
-ALTER TABLE dbo.Job ALTER COLUMN JobId DROP ROWGUIDCOL;
-ALTER TABLE dbo.SlaveGroup_Resource ALTER COLUMN SlaveGroup_RessourceId DROP ROWGUIDCOL;
-ALTER TABLE dbo.Plugin ALTER COLUMN PluginId DROP ROWGUIDCOL;
-ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId DROP ROWGUIDCOL;
-ALTER TABLE dbo.Resource ALTER COLUMN ResourceId DROP ROWGUIDCOL;
-ALTER TABLE dbo.UptimeCalendar ALTER COLUMN UptimeCalendarId DROP ROWGUIDCOL;
-ALTER TABLE dbo.HiveExperiment ALTER COLUMN HiveExperimentId DROP ROWGUIDCOL;
-
-ALTER TABLE dbo.AssignedResources DROP CONSTRAINT [DF_AssignedResources_AssignedRessourcesId];
-ALTER TABLE dbo.Job DROP CONSTRAINT [DF_Job_JobId];
-ALTER TABLE dbo.SlaveGroup_Resource DROP CONSTRAINT [DF_SlaveGroup_ResourceSlaveGroup_RessourceId];
-ALTER TABLE dbo.Plugin DROP CONSTRAINT [DF_Plugin_PluginId];
-ALTER TABLE dbo.RequiredPlugins DROP CONSTRAINT [DF_RequiredPlugins_RequiredPluginId];
-ALTER TABLE dbo.Resource DROP CONSTRAINT [DF_Resource_ResourceId];
-ALTER TABLE dbo.UptimeCalendar DROP CONSTRAINT [DF_UptimeCalendar_UptimeCalendarId];
-ALTER TABLE dbo.HiveExperiment DROP CONSTRAINT [DF_HiveExperiment_HiveExperimentId];
-*/
-
-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
-
-/* 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, 
-		SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END) AS ExecutionTimeMsFinishedJobs,
-		SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(MS,fs.DateTime,ls.DateTime) ELSE 0 END) 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
