Changeset 4905 for branches/HeuristicLab.Hive/sources/HeuristicLab.Hive.New/HeuristicLab.Services.Hive.DataAccess/3.3/Tools/prepareHiveDatabase.sql
- Timestamp:
- 11/22/10 17:13:27 (14 years ago)
- Location:
- branches/HeuristicLab.Hive/sources/HeuristicLab.Hive.New
- Files:
-
- 2 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/HeuristicLab.Hive/sources/HeuristicLab.Hive.New
- Property svn:ignore
-
old new 1 1 *.suo 2 Tests
-
- Property svn:ignore
-
branches/HeuristicLab.Hive/sources/HeuristicLab.Hive.New/HeuristicLab.Services.Hive.DataAccess/3.3/Tools/prepareHiveDatabase.sql
r4629 r4905 31 31 ALTER TABLE dbo.AssignedResources ALTER COLUMN AssignedRessourcesId ADD ROWGUIDCOL; 32 32 ALTER TABLE dbo.AssignedResources WITH NOCHECK ADD CONSTRAINT [DF_AssignedResources_AssignedRessourcesId] DEFAULT (newid()) FOR AssignedRessourcesId; 33 ALTER TABLE [dbo].[AssignedResources] DROP CONSTRAINT [Job_AssignedResource] 34 ALTER TABLE [dbo].[AssignedResources] WITH CHECK ADD CONSTRAINT [Job_AssignedResource] FOREIGN KEY([JobId]) 35 REFERENCES [dbo].[Job] ([JobId]) 36 ON UPDATE CASCADE 37 ON DELETE CASCADE 38 GO 39 ALTER TABLE [dbo].[AssignedResources] DROP CONSTRAINT [Resource_AssignedResource] 40 ALTER TABLE [dbo].[AssignedResources] WITH CHECK ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY([ResourceId]) 41 REFERENCES [dbo].[Resource] ([ResourceId]) 42 ON UPDATE CASCADE 43 ON DELETE CASCADE 44 GO 33 45 34 46 ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL; … … 50 62 ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (newid()) FOR RequiredPluginId; 51 63 64 ALTER TABLE [dbo].[RequiredPlugins] DROP CONSTRAINT [Job_RequiredPlugin] 65 ALTER TABLE [dbo].[RequiredPlugins] WITH CHECK ADD CONSTRAINT [Job_RequiredPlugin] FOREIGN KEY([JobId]) 66 REFERENCES [dbo].[Job] ([JobId]) 67 ON UPDATE CASCADE 68 ON DELETE CASCADE 69 GO 70 71 ALTER TABLE [dbo].[RequiredPlugins] DROP CONSTRAINT [Plugin_RequiredPlugin] 72 ALTER TABLE [dbo].[RequiredPlugins] WITH CHECK ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY([PluginId]) 73 REFERENCES [dbo].[Plugin] ([PluginId]) 74 ON UPDATE CASCADE 75 ON DELETE CASCADE 76 GO 77 52 78 ALTER TABLE dbo.Resource ALTER COLUMN ResourceId ADD ROWGUIDCOL; 53 79 ALTER TABLE dbo.Resource WITH NOCHECK ADD CONSTRAINT [DF_Resource_ResourceId] DEFAULT (newid()) FOR ResourceId; … … 64 90 /* create indices */ 65 91 CREATE INDEX Index_RequiredPlugins_JobId ON RequiredPlugins(JobId); 92 93 /* triggers */ 94 SET ANSI_NULLS ON 95 GO 96 SET QUOTED_IDENTIFIER ON 97 GO 98 -- ============================================= 99 -- Author: cneumuel 100 -- Create date: 11.11.2010 101 -- Description: Deletes the root-job of the experiment when experiment is deleted 102 -- ============================================= 103 CREATE TRIGGER [dbo].[tr_HiveExperimentDeleteCascade] ON [dbo].[HiveExperiment] FOR DELETE AS 104 SET NOCOUNT ON 105 DELETE Job FROM deleted, Job WHERE Job.JobId = deleted.RootJobId 106 GO 107 108 SET ANSI_NULLS ON 109 GO 110 SET QUOTED_IDENTIFIER ON 111 GO 112 -- ============================================= 113 -- Author: cneumuel 114 -- Create date: 11.11.2010 115 -- 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/) 116 -- ============================================= 117 CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS 118 BEGIN 119 CREATE TABLE #Table( 120 JobId uniqueidentifier 121 ) 122 INSERT INTO #Table (JobId) 123 SELECT JobId FROM deleted 124 125 DECLARE @c INT 126 SET @c = 0 127 128 WHILE @c <> (SELECT COUNT(JobId) FROM #Table) BEGIN 129 SELECT @c = COUNT(JobId) FROM #Table 130 131 INSERT INTO #Table (JobId) 132 SELECT Job.JobId 133 FROM Job 134 LEFT OUTER JOIN #Table ON Job.JobId = #Table.JobId 135 WHERE Job.ParentJobId IN (SELECT JobId FROM #Table) 136 AND #Table.JobId IS NULL 137 END 138 139 DELETE Job FROM Job INNER JOIN #Table ON Job.JobId = #Table.JobId 140 END
Note: See TracChangeset
for help on using the changeset viewer.