source: branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql @ 6033

/* 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;
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 (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].[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 (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;

ALTER TABLE dbo.StateLog ALTER COLUMN StateLogId ADD ROWGUIDCOL;
ALTER TABLE dbo.StateLog WITH NOCHECK ADD CONSTRAINT [DF_StateLog_StateLogId] DEFAULT (newid()) 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

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

/* triggers */
USE [HeuristicLab.Hive-3.4]
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: 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
  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