Ignore:
Timestamp:
09/13/18 13:18:45 (14 months ago)
Author:
ddorfmei
Message:

#2931: Merged [16046-16138/trunk] into branch

Location:
branches/2931_OR-Tools_LP_MIP
Files:
3 edited

Legend:

Unmodified
Added
Removed
  • branches/2931_OR-Tools_LP_MIP

  • branches/2931_OR-Tools_LP_MIP/HeuristicLab.Services.Hive.DataAccess

  • branches/2931_OR-Tools_LP_MIP/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

    r14185 r16139  
    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] (
Note: See TracChangeset for help on using the changeset viewer.