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

Timestamp:

09/25/17 14:45:14 (7 years ago)

Author:

jkarder

Message:

#2839: worked on database model

File:

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

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

@@ -25 +25 @@
 
 SET ARITHABORT ON
-CREATE TABLE [dbo].[AssignedResources](
+
+CREATE TABLE [dbo].[AssignedResource](
   [ResourceId] UniqueIdentifier NOT NULL,
-  [TaskId] UniqueIdentifier NOT NULL,
-  CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
+  [ProjectId] UniqueIdentifier NOT NULL,
+  CONSTRAINT [PK_dbo.ResourceIdProjectId] PRIMARY KEY ([ResourceId], [ProjectId])
   )
 CREATE TABLE [dbo].[Plugin](
@@ -106 +107 @@
   [OwnerUserId] UniqueIdentifier NOT NULL,
   [DateCreated] DateTime NOT NULL,
+  [ProjectId] UniqueIdentifier NOT NULL,
   CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
   )
@@ -148 +150 @@
   CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
   )
-ALTER TABLE [dbo].[AssignedResources]
+CREATE TABLE [dbo].[Project](
+  [ProjectId] UniqueIdentifier NOT NULL,
+  [ParentProjectId] UniqueIdentifier,
+  [DateCreated] DateTime NOT NULL,
+  [Name] VarChar(MAX) NOT NULL,
+  [Description] VarChar(MAX) NOT NULL,
+  [OwnerUserId] UniqueIdentifier NOT NULL,
+  [StartDate] DateTime NOT NULL,
+  [EndDate] DateTime,
+  CONSTRAINT [PK_dbo.Project] PRIMARY KEY ([ProjectId])
+  )
+CREATE TABLE [dbo].[ProjectPermission](
+  [ProjectId] UniqueIdentifier NOT NULL,
+  [GrantedUserId] UniqueIdentifier NOT NULL,
+  [GrantedByUserId] UniqueIdentifier NOT NULL,
+  CONSTRAINT [PK_dbo.ProjectPermission] PRIMARY KEY ([ProjectId], [GrantedUserId])
+  )
+
+ALTER TABLE [dbo].[AssignedResource]
   ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
-ALTER TABLE [dbo].[AssignedResources]
-  ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
+ALTER TABLE [dbo].[AssignedResource]
+  ADD CONSTRAINT [Project_AssignedResource] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
 ALTER TABLE [dbo].[RequiredPlugins]
   ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
@@ -166 +186 @@
 ALTER TABLE [dbo].[Downtime]
   ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+ALTER TABLE [dbo].[Job]
+  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])
@@ -176 +198 @@
 ALTER TABLE [dbo].[JobPermission]
   ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
+ALTER TABLE [dbo].[Project]
+  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId])
+ALTER TABLE [dbo].[ProjectPermission]
+  ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
 
 GO