Changeset 4769 for branches/HeuristicLab.Hive/sources/HeuristicLab.Hive/HeuristicLab.Hive.Server.LINQDataAccess/3.3/Scripts/prepareHiveDatabase.sql

Timestamp:

11/11/10 10:46:12 (13 years ago)

Author:

cneumuel

Message:

#1260

• delete cascade for HiveExperiments and Jobs database
• automatic downloading of HiveExperiments-List in HiveExperimentManager

File:

• branches/HeuristicLab.Hive/sources/HeuristicLab.Hive/HeuristicLab.Hive.Server.LINQDataAccess/3.3/Scripts/prepareHiveDatabase.sql (modified) (4 diffs)

branches/HeuristicLab.Hive/sources/HeuristicLab.Hive/HeuristicLab.Hive.Server.LINQDataAccess/3.3/Scripts/prepareHiveDatabase.sql

@@ -1 +1 @@
 /* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
+USE [HeuristicLab.Hive]
+GO
 
 /*
@@ -32 +34 @@
 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;
@@ -51 +65 @@
 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 [PluginInfo_RequiredPlugin]
+ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [PluginInfo_RequiredPlugin] FOREIGN KEY([PluginId])
+REFERENCES [dbo].[PluginInfo] ([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;
@@ -65 +93 @@
 /* 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 Job FROM Job INNER JOIN #Table ON Job.JobId = #Table.JobId
+END