Free cookie consent management tool by TermsFeed Policy Generator

Ignore:
Timestamp:
08/30/18 11:32:56 (6 years ago)
Author:
jkarder
Message:

#2839: merged [15377-16116/branches/2839_HiveProjectManagement] into trunk

Location:
trunk
Files:
4 edited

Legend:

Unmodified
Added
Removed

  • trunk

  • trunk/HeuristicLab.Services.Hive.DataAccess

  • trunk/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

    r14185 r16117  
    1717 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
    1818 */
    19 USE [HeuristicLab.Hive-3.3]
     19USE [HeuristicLab.Hive-3.4]
    2020
    2121EXEC sp_configure filestream_access_level, 2
    … …  
    2525
    2626SET ARITHABORT ON
    27 CREATE TABLE [dbo].[AssignedResources](
     27
     28CREATE TABLE [dbo].[AssignedProjectResource](
    2829  [ResourceId] UniqueIdentifier NOT NULL,
    29   [TaskId] UniqueIdentifier NOT NULL,
    30   CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
     30  [ProjectId] UniqueIdentifier NOT NULL,
     31  CONSTRAINT [PK_dbo.ResourceIdProjectId] PRIMARY KEY ([ResourceId], [ProjectId])
     32  )
     33CREATE TABLE [dbo].[AssignedJobResource](
     34  [ResourceId] UniqueIdentifier NOT NULL,
     35  [JobId] UniqueIdentifier NOT NULL,
     36  CONSTRAINT [PK_dbo.ResourceIdJobId] PRIMARY KEY ([ResourceId], [JobId])
    3137  )
    3238CREATE TABLE [dbo].[Plugin](
    … …  
    6773  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
    6874  )
    69 CREATE TABLE [dbo].[ResourcePermission](
    70   [ResourceId] UniqueIdentifier NOT NULL,
    71   [GrantedUserId] UniqueIdentifier NOT NULL,
    72   [GrantedByUserId] UniqueIdentifier NOT NULL,
    73   CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
    74   )
    7575CREATE TABLE [dbo].[Task](
    7676  [TaskId] UniqueIdentifier NOT NULL,
    … …  
    103103  [Name] VarChar(MAX) NOT NULL,
    104104  [Description] VarChar(MAX),
    105   [ResourceIds] VarChar(MAX),
    106105  [OwnerUserId] UniqueIdentifier NOT NULL,
    107106  [DateCreated] DateTime NOT NULL,
     107  [ProjectId] UniqueIdentifier NOT NULL,
     108  [JobState] VarChar(30) NOT NULL,
    108109  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
    109110  )
    … …  
    148149  CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
    149150  )
    150 ALTER TABLE [dbo].[AssignedResources]
    151   ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
    152 ALTER TABLE [dbo].[AssignedResources]
    153   ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
     151CREATE TABLE [dbo].[Project](
     152  [ProjectId] UniqueIdentifier NOT NULL,
     153  [ParentProjectId] UniqueIdentifier,
     154  [DateCreated] DateTime NOT NULL,
     155  [Name] VarChar(MAX) NOT NULL,
     156  [Description] VarChar(MAX),
     157  [OwnerUserId] UniqueIdentifier NOT NULL,
     158  [StartDate] DateTime NOT NULL,
     159  [EndDate] DateTime,
     160  CONSTRAINT [PK_dbo.Project] PRIMARY KEY ([ProjectId])
     161  )
     162CREATE TABLE [dbo].[ProjectPermission](
     163  [ProjectId] UniqueIdentifier NOT NULL,
     164  [GrantedUserId] UniqueIdentifier NOT NULL,
     165  [GrantedByUserId] UniqueIdentifier NOT NULL,
     166  CONSTRAINT [PK_dbo.ProjectPermission] PRIMARY KEY ([ProjectId], [GrantedUserId])
     167  )
     168
     169ALTER TABLE [dbo].[AssignedProjectResource]
     170  ADD CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
     171ALTER TABLE [dbo].[AssignedProjectResource]
     172  ADD CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
     173ALTER TABLE [dbo].[AssignedJobResource]
     174  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
     175ALTER TABLE [dbo].[AssignedJobResource]
     176  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
    154177ALTER TABLE [dbo].[RequiredPlugins]
    155   ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
     178  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId]) ON UPDATE CASCADE ON DELETE CASCADE;
    156179ALTER TABLE [dbo].[RequiredPlugins]
    157   ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
     180  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
    158181ALTER TABLE [dbo].[Resource]
    159   ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
    160 ALTER TABLE [dbo].[ResourcePermission]
    161   ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
     182  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId]);
    162183ALTER TABLE [dbo].[Task]
    163   ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
     184  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId]);
    164185ALTER TABLE [dbo].[Task]
    165   ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
     186  ADD CONSTRAINT [Job_Task] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
    166187ALTER TABLE [dbo].[Downtime]
    167   ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
     188  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
     189ALTER TABLE [dbo].[Job]
     190  ADD CONSTRAINT [Project_Job] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]);
    168191ALTER TABLE [dbo].[TaskData]
    169   ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
     192  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
    170193ALTER TABLE [dbo].[PluginData]
    171   ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
     194  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId]) ON UPDATE CASCADE ON DELETE CASCADE;
    172195ALTER TABLE [dbo].[StateLog]
    173   ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
     196  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
    174197ALTER TABLE [dbo].[StateLog]
    175   ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
     198  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
    176199ALTER TABLE [dbo].[JobPermission]
    177   ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
     200  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
     201ALTER TABLE [dbo].[Project]
     202  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId]);
     203ALTER TABLE [dbo].[ProjectPermission]
     204  ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
    178205
    179206GO
    … …  
    192219CREATE TABLE [statistics].[DimClient] (
    193220    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
    194     [Name]             VARCHAR (MAX)    NOT NULL,
    195221    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
    196     [ExpirationTime]   DATETIME        NULL,
    197     [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
    198     [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
    199     [GroupName]        VARCHAR (MAX)    NULL,
    200     [GroupName2]       VARCHAR (MAX)    NULL,
     222  [ParentResourceId] UNIQUEIDENTIFIER NULL,
     223  [Name]             VARCHAR (MAX)    NOT NULL,
     224  [ResourceType]     VARCHAR (MAX)    NULL,
     225  [DateCreated]      DATETIME         NOT NULL,
     226    [DateExpired]      DATETIME         NULL
    201227    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
    202228);
    … …  
    204230    [JobId]          UNIQUEIDENTIFIER NOT NULL,
    205231    [UserId]         UNIQUEIDENTIFIER NOT NULL,
     232  [ProjectId]      UNIQUEIDENTIFIER NOT NULL,
    206233    [JobName]        VARCHAR (MAX)    NOT NULL,
    207234    [UserName]       VARCHAR (MAX)    NOT NULL,
    … …  
    212239    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
    213240);
     241CREATE TABLE [statistics].[DimProject] (
     242    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimProject_Id] DEFAULT (newsequentialid()) NOT NULL,
     243    [ProjectId]        UNIQUEIDENTIFIER NOT NULL,
     244  [ParentProjectId]  UNIQUEIDENTIFIER NULL,
     245  [Name]             VARCHAR (MAX)    NOT NULL,
     246  [Description]      VARCHAR (MAX)    NULL,
     247  [OwnerUserId]      UNIQUEIDENTIFIER NOT NULL,
     248  [StartDate]        DATETIME         NOT NULL,
     249    [EndDate]          DATETIME         NULL,
     250  [DateCreated]      DATETIME         NOT NULL,
     251    [DateExpired]      DATETIME         NULL
     252  CONSTRAINT [PK_DimProject] PRIMARY KEY CLUSTERED ([Id] ASC)
     253);
    214254CREATE TABLE [statistics].[DimUser] (
    215255    [UserId] UNIQUEIDENTIFIER NOT NULL,
    216256    [Name]   VARCHAR (MAX)    NOT NULL,
    217257    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
     258);
     259
     260
     261
     262CREATE TABLE [statistics].[FactProjectInfo] (
     263  [ProjectId]   UNIQUEIDENTIFIER NOT NULL,
     264  [Time]      DATETIME     NOT NULL,
     265  [NumTotalCores] INT        NOT NULL,
     266  [NumUsedCores]  INT        NOT NULL,
     267  [TotalMemory] INT        NOT NULL,
     268  [UsedMemory]  INT        NOT NULL
     269  CONSTRAINT [PK_FactProjectInfo] PRIMARY KEY CLUSTERED ([ProjectId] ASC, [Time] ASC),
     270  CONSTRAINT [FK_FactProjectInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
     271  CONSTRAINT [FK_FactProjectInfo_DimProject] FOREIGN KEY ([ProjectId]) REFERENCES [statistics].[DimProject] ([Id])
    218272);
    219273CREATE TABLE [statistics].[FactClientInfo] (

  • trunk/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

    r14185 r16117  
    1919
    2020/* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
    21 USE [HeuristicLab.Hive-3.3]
    22 
    23 ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Task_AssignedResource]
    24 ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Task_AssignedResource] FOREIGN KEY([TaskId])
    25 REFERENCES [dbo].[Task] ([TaskId])
    26 ON UPDATE CASCADE
    27 ON DELETE CASCADE
    28 GO
    29 ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Resource_AssignedResource]
    30 ALTER TABLE [dbo].[AssignedResources]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedResource] FOREIGN KEY([ResourceId])
     21USE [HeuristicLab.Hive-3.4]
     22
     23ALTER TABLE [dbo].[AssignedProjectResource]  DROP  CONSTRAINT [Project_AssignedProjectResource]
     24ALTER TABLE [dbo].[AssignedProjectResource]  WITH CHECK ADD  CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY([ProjectId])
     25REFERENCES [dbo].[Project] ([ProjectId])
     26ON UPDATE CASCADE
     27ON DELETE CASCADE
     28GO
     29ALTER TABLE [dbo].[AssignedProjectResource]  DROP  CONSTRAINT [Resource_AssignedProjectResource]
     30ALTER TABLE [dbo].[AssignedProjectResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY([ResourceId])
     31REFERENCES [dbo].[Resource] ([ResourceId])
     32ON UPDATE CASCADE
     33ON DELETE CASCADE
     34GO
     35ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Job_AssignedJobResource]
     36ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY([JobId])
     37REFERENCES [dbo].[Job] ([JobId])
     38ON UPDATE CASCADE
     39ON DELETE CASCADE
     40GO
     41ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Resource_AssignedJobResource]
     42ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY([ResourceId])
    3143REFERENCES [dbo].[Resource] ([ResourceId])
    3244ON UPDATE CASCADE
    … …  
    88100ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
    89101ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId;
     102ALTER TABLE [dbo].[Job]  DROP  CONSTRAINT [Project_Job]
     103ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [Project_Job] FOREIGN KEY([ProjectId])
     104REFERENCES [dbo].[Project] ([ProjectId])
     105ON UPDATE CASCADE
     106GO
    90107
    91108ALTER TABLE dbo.StateLog ALTER COLUMN StateLogId ADD ROWGUIDCOL;
    … …  
    99116GO
    100117
    101 ALTER TABLE [dbo].[ResourcePermission]  DROP  CONSTRAINT [Resource_ResourcePermission]
    102 ALTER TABLE [dbo].[ResourcePermission]  WITH CHECK ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY([ResourceId])
    103 REFERENCES [dbo].[Resource] ([ResourceId])
     118ALTER TABLE dbo.Project ALTER COLUMN ProjectId ADD ROWGUIDCOL;
     119ALTER TABLE dbo.Project WITH NOCHECK ADD CONSTRAINT [DF_Project_ProjectId] DEFAULT (NEWSEQUENTIALID()) FOR ProjectId;
     120
     121ALTER TABLE [dbo].[ProjectPermission]  DROP  CONSTRAINT [Project_ProjectPermission]
     122ALTER TABLE [dbo].[ProjectPermission]  WITH CHECK ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY([ProjectId])
     123REFERENCES [dbo].[Project] ([ProjectId])
    104124ON UPDATE CASCADE
    105125ON DELETE CASCADE
    … …  
    123143
    124144
     145
     146-- OBSOLETE - DO NOT PERFORM (start)
    125147/****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
    126148SET ANSI_NULLS ON
    … …  
    175197END
    176198GO
    177 
    178 
     199-- OBSOLETE (end)
     200
Note: See TracChangeset for help on using the changeset viewer.