Changeset 6033 for branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql

Timestamp:

04/19/11 18:58:53 (13 years ago)

Author:

cneumuel

Message:

#1233

• created baseclass for jobs (ItemJob) which derives OperatorJobs and EngineJobs
• created special view for OptimizerJobs which derives from a more general view
• removed logic from domain class HiveExperiment and moved it into RefreshableHiveExperiment
• improved ItemTreeView
• corrected plugin dependencies
• fixed bug in database trigger when deleting HiveExperiments
• added delete cascade for Plugin and PluginData
• lots of fixes

File:

• branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql (modified) (4 diffs)

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql

@@ -55 +55 @@
 GO
 
---ALTER TABLE [dbo].[Job]  DROP  CONSTRAINT [HiveExperiment_Job]
---ALTER TABLE [dbo].[Job]  WITH CHECK ADD CONSTRAINT [HiveExperiment_Job] FOREIGN KEY([HiveExperimentId])
---REFERENCES [dbo].[HiveExperiment] ([HiveExperimentId])
---ON UPDATE CASCADE
---ON DELETE CASCADE
---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;
@@ -67 +60 @@
 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;
@@ -108 +108 @@
 
 /* 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
@@ -114 +117 @@
 -- =============================================
 -- Author:    cneumuel
--- Create date: 11.04.2011
+-- 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.
 -- =============================================
-ALTER TRIGGER [dbo].[tr_HiveExperimentDeleteCascade] ON [dbo].[HiveExperiment] INSTEAD OF DELETE AS 
+CREATE TRIGGER [dbo].[tr_HiveExperimentDeleteCascade] ON [dbo].[HiveExperiment] INSTEAD OF DELETE AS 
 BEGIN
-  DELETE Job FROM Job WHERE Job.HiveExperimentId = HiveExperimentId
+  DELETE Job FROM deleted, Job WHERE deleted.HiveExperimentId = Job.HiveExperimentId
+  DELETE HiveExperiment FROM deleted, HiveExperiment WHERE deleted.HiveExperimentId = HiveExperiment.HiveExperimentId
 END
+GO
 
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
 -- =============================================
 -- Author:    cneumuel