Changeset 15552 for branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts

Timestamp:

12/20/17 11:27:33 (7 years ago)

Author:

jzenisek

Message:

#2839 worked on permission checks in listing methods

Location:

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts

Files:

• Initialize Hive Database.sql (modified) (4 diffs)
• Prepare Hive Database.sql (modified) (2 diffs)

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

@@ -36 +36 @@
   CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
   )
+CREATE TABLE [dbo].[AssignedJobResource](
+  [ResourceId] UniqueIdentifier NOT NULL,
+  [JobId] UniqueIdentifier NOT NULL,
+  CONSTRAINT [PK_dbo.ResourceIdJobId] PRIMARY KEY ([ResourceId], [JobId])
+  )
 CREATE TABLE [dbo].[Plugin](
   [PluginId] UniqueIdentifier NOT NULL,
@@ -73 +78 @@
   CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
   )
-CREATE TABLE [dbo].[ResourcePermission](
-  [ResourceId] UniqueIdentifier NOT NULL,
-  [GrantedUserId] UniqueIdentifier NOT NULL,
-  [GrantedByUserId] UniqueIdentifier NOT NULL,
-  CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
-  )
 CREATE TABLE [dbo].[Task](
   [TaskId] UniqueIdentifier NOT NULL,
@@ -180 +179 @@
 ALTER TABLE [dbo].[AssignedTaskResource]
   ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
+ALTER TABLE [dbo].[AssignedJobResource]
+  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+ALTER TABLE [dbo].[AssignedJobResource]
+  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
 ALTER TABLE [dbo].[RequiredPlugins]
   ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
@@ -186 +189 @@
 ALTER TABLE [dbo].[Resource]
   ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
-ALTER TABLE [dbo].[ResourcePermission]
-  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
 ALTER TABLE [dbo].[Task]
   ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])

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

@@ -42 +42 @@
 ALTER TABLE [dbo].[AssignedTaskResource]  DROP  CONSTRAINT [Resource_AssignedTaskResource]
 ALTER TABLE [dbo].[AssignedTaskResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY([ResourceId])
+REFERENCES [dbo].[Resource] ([ResourceId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Job_AssignedJobResource]
+ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY([JobId])
+REFERENCES [dbo].[Job] ([JobId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Resource_AssignedJobResource]
+ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY([ResourceId])
 REFERENCES [dbo].[Resource] ([ResourceId])
 ON UPDATE CASCADE
@@ -105 +118 @@
 REFERENCES [dbo].[Project] ([ProjectId])
 ON UPDATE CASCADE
-ON DELETE CASCADE
+-- ON DELETE CASCADE
 GO