/* 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 (newid()) 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 (newid()) FOR JobId;

--ALTER TABLE [dbo].[Job]  DROP  CONSTRAINT [Slave_Job]
--ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [Slave_Job] FOREIGN KEY([ResourceId])
--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 (newid()) FOR PluginId;

ALTER TABLE dbo.PluginData ALTER COLUMN PluginDataId ADD ROWGUIDCOL;
ALTER TABLE dbo.PluginData WITH NOCHECK ADD CONSTRAINT [DF_PluginData_PluginDataId] DEFAULT (newid()) FOR PluginDataId;

ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId ADD ROWGUIDCOL;
ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (newid()) 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 (newid()) FOR ResourceId;

ALTER TABLE dbo.UptimeCalendar ALTER COLUMN UptimeCalendarId ADD ROWGUIDCOL;
ALTER TABLE dbo.UptimeCalendar WITH NOCHECK ADD CONSTRAINT [DF_UptimeCalendar_UptimeCalendarId] DEFAULT (newid()) FOR UptimeCalendarId;

ALTER TABLE dbo.HiveExperiment ALTER COLUMN HiveExperimentId ADD ROWGUIDCOL;
ALTER TABLE dbo.HiveExperiment WITH NOCHECK ADD CONSTRAINT [DF_HiveExperiment_HiveExperimentId] DEFAULT (newid()) FOR HiveExperimentId;

/* create indices */
CREATE INDEX Index_RequiredPlugins_JobId ON RequiredPlugins(JobId);

/* triggers */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		cneumuel
-- Create date: 11.11.2010
-- Description:	Deletes the root-job of the experiment when experiment is deleted
-- =============================================
CREATE TRIGGER [dbo].[tr_HiveExperimentDeleteCascade] ON [dbo].[HiveExperiment] FOR DELETE AS 
SET NOCOUNT ON
DELETE Job FROM deleted, Job WHERE Job.JobId = deleted.RootJobId
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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
	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