Changeset 15630 for branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

Timestamp:

01/18/18 15:08:25 (7 years ago)

Author:

jzenisek

Message:

#2839

• updated Heartbeat processing (regarding: checking against AssignedJobResources and handling of the updated Job deletion routine)
• updated Job deletion routine(still in progress at GenerateStatistics)

File:

• branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql (modified) (2 diffs)

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

@@ -111 +111 @@
   [DateCreated] DateTime NOT NULL,
   [ProjectId] UniqueIdentifier NOT NULL,
+  [JobState] VarChar(30) NOT NULL,
   CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
   )
@@ -172 +173 @@
 
 ALTER TABLE [dbo].[AssignedProjectResource]
-  ADD CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+  ADD CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[AssignedProjectResource]
-  ADD CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
+  ADD CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[AssignedTaskResource]
-  ADD CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+  ADD CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[AssignedTaskResource]
-  ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
+  ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[AssignedJobResource]
-  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[AssignedJobResource]
-  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[RequiredPlugins]
-  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
+  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[RequiredPlugins]
-  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
+  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[Resource]
-  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId]);
 ALTER TABLE [dbo].[Task]
-  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
+  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[Task]
-  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[Downtime]
-  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[Job]
-  ADD CONSTRAINT [Project_Job] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
+  ADD CONSTRAINT [Project_Job] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]);
 ALTER TABLE [dbo].[TaskData]
-  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
+  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[PluginData]
-  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
+  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[StateLog]
-  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
+  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[StateLog]
-  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
+  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[JobPermission]
-  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[Project]
-  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId])
+  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[ProjectPermission]
-  ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
+  ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
 
 GO