Free cookie consent management tool by TermsFeed Policy Generator

Ignore:
Timestamp:
06/21/12 18:02:33 (12 years ago)
Author:
gkronber
Message:

#1847: merged trunk changes r7800:HEAD into gp move operators branch

Location:
branches/GP-MoveOperators
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • branches/GP-MoveOperators

  • branches/GP-MoveOperators/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

    r6983 r8085  
    55CREATE TABLE [dbo].[AssignedResources](
    66  [ResourceId] UniqueIdentifier NOT NULL,
    7   [TaskId] UniqueIdentifier NOT NULL,   
     7  [TaskId] UniqueIdentifier NOT NULL,
    88  CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
    99  )
    1010CREATE TABLE [dbo].[Plugin](
    11   [PluginId] UniqueIdentifier NOT NULL, 
    12   [Name] VarChar(MAX),
    13   [Version] VarChar(MAX),
    14   [UserId] UniqueIdentifier,
    15   [DateCreated] DateTime,
    16   [Hash] VarBinary(20),
     11  [PluginId] UniqueIdentifier NOT NULL,
     12  [Name] VarChar(MAX) NOT NULL,
     13  [Version] VarChar(MAX) NOT NULL,
     14  [UserId] UniqueIdentifier NOT NULL,
     15  [DateCreated] DateTime NOT NULL,
     16  [Hash] VarBinary(20) NOT NULL,
    1717  CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId])
    1818  )
    1919CREATE TABLE [dbo].[RequiredPlugins](
    20   [RequiredPluginId] UniqueIdentifier NOT NULL, 
    21   [TaskId] UniqueIdentifier NOT NULL, 
    22   [PluginId] UniqueIdentifier NOT NULL, 
     20  [RequiredPluginId] UniqueIdentifier NOT NULL,
     21  [TaskId] UniqueIdentifier NOT NULL,
     22  [PluginId] UniqueIdentifier NOT NULL,
    2323  CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
    2424  )
    2525CREATE TABLE [dbo].[Resource](
    26   [ResourceId] UniqueIdentifier NOT NULL,
    27   [Name] VarChar(MAX),
    28   [ResourceType] NVarChar(4000) NOT NULL,
    29   [ParentResourceId] UniqueIdentifier,
    30   [CpuSpeed] Int,
    31   [Memory] Int,
    32   [Login] DateTime,
    33   [SlaveState] VarChar(15),
    34   [Cores] Int,
    35   [FreeCores] Int,
    36   [FreeMemory] Int,
    37   [IsAllowedToCalculate] Bit,
    38   [CpuArchitecture] VarChar(3),
    39   [OperatingSystem] VarChar(MAX),
    40   [LastHeartbeat] DateTime,
    41   [CpuUtilization] float,
    42   [HbInterval] int,
     26  [ResourceId] UniqueIdentifier NOT NULL,
     27  [Name] VarChar(MAX) NOT NULL,
     28  [ResourceType] NVarChar(4000) NOT NULL,
     29  [ParentResourceId] UniqueIdentifier,
     30  [CpuSpeed] Int,
     31  [Memory] Int,
     32  [Login] DateTime,
     33  [SlaveState] VarChar(15),
     34  [Cores] Int,
     35  [FreeCores] Int,
     36  [FreeMemory] Int,
     37  [IsAllowedToCalculate] Bit,
     38  [CpuArchitecture] VarChar(3),
     39  [OperatingSystem] VarChar(MAX),
     40  [LastHeartbeat] DateTime,
     41  [CpuUtilization] float,
     42  [HbInterval] int NOT NULL,
     43  [IsDisposable] Bit,
     44  [OwnerUserId] UniqueIdentifier,
    4345  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
    4446  )
     47CREATE TABLE [dbo].[ResourcePermission](
     48  [ResourceId] UniqueIdentifier NOT NULL,
     49  [GrantedUserId] UniqueIdentifier NOT NULL,
     50  [GrantedByUserId] UniqueIdentifier NOT NULL,
     51  CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
     52  )
    4553CREATE TABLE [dbo].[Task](
    46   [TaskId] UniqueIdentifier NOT NULL, 
    47   [TaskState] VarChar(30),
    48   [ExecutionTimeMs] float,
    49   [LastHeartbeat] DateTime, 
    50   [ParentTaskId] UniqueIdentifier, 
    51   [Priority] Int NOT NULL, 
    52   [CoresNeeded] Int NOT NULL, 
    53   [MemoryNeeded] Int NOT NULL, 
    54   [IsParentTask] Bit,
    55   [FinishWhenChildJobsFinished] Bit,
    56   [Command] VarChar(30), 
    57   [JobId] UniqueIdentifier NOT NULL, 
    58   [IsPrivileged] Bit,
     54  [TaskId] UniqueIdentifier NOT NULL,
     55  [TaskState] VarChar(30) NOT NULL,
     56  [ExecutionTimeMs] float NOT NULL,
     57  [LastHeartbeat] DateTime,
     58  [ParentTaskId] UniqueIdentifier,
     59  [Priority] Int NOT NULL,
     60  [CoresNeeded] Int NOT NULL,
     61  [MemoryNeeded] Int NOT NULL,
     62  [IsParentTask] Bit NOT NULL,
     63  [FinishWhenChildJobsFinished] Bit NOT NULL,
     64  [Command] VarChar(30),
     65  [JobId] UniqueIdentifier NOT NULL,
     66  [IsPrivileged] Bit NOT NULL,
    5967  CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId])
    6068  )
    6169CREATE TABLE [dbo].[Downtime](
    62   [DowntimeId] UniqueIdentifier,
    63   [ResourceId] UniqueIdentifier,
    64   [StartDate] DateTime,
    65   [EndDate] DateTime,
    66   [AllDayEvent] Bit,
    67   [Recurring] Bit,
    68   [RecurringId] UniqueIdentifier,
     70  [DowntimeId] UniqueIdentifier NOT NULL,
     71  [ResourceId] UniqueIdentifier NOT NULL,
     72  [StartDate] DateTime NOT NULL,
     73  [EndDate] DateTime NOT NULL,
     74  [AllDayEvent] Bit NOT NULL,
     75  [Recurring] Bit NOT NULL,
     76  [RecurringId] UniqueIdentifier NOT NULL,
    6977  CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId])
    7078  )
    7179CREATE TABLE [dbo].[Job](
    72   [JobId] UniqueIdentifier NOT NULL, 
    73   [Name] VarChar(MAX),
    74   [Description] VarChar(MAX), 
    75   [ResourceIds] VarChar(MAX), 
    76   [OwnerUserId] UniqueIdentifier,
    77   [DateCreated] DateTime,   
     80  [JobId] UniqueIdentifier NOT NULL,
     81  [Name] VarChar(MAX) NOT NULL,
     82  [Description] VarChar(MAX),
     83  [ResourceIds] VarChar(MAX),
     84  [OwnerUserId] UniqueIdentifier NOT NULL,
     85  [DateCreated] DateTime NOT NULL,
    7886  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
    7987  )
    8088CREATE TABLE [dbo].[TaskData](
    81   [TaskId] UniqueIdentifier NOT NULL, 
    82   [Data] VarBinary(MAX),
    83   [LastUpdate] DateTime,
     89  [TaskId] UniqueIdentifier NOT NULL,
     90  [Data] VarBinary(MAX) NOT NULL,
     91  [LastUpdate] DateTime NOT NULL,
    8492  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
    8593  )
    8694CREATE TABLE [dbo].[PluginData](
    87   [PluginDataId] UniqueIdentifier NOT NULL, 
    88   [PluginId] UniqueIdentifier NOT NULL, 
    89   [Data] VarBinary(MAX) NOT NULL, 
    90   [FileName] VarChar(MAX),
     95  [PluginDataId] UniqueIdentifier NOT NULL,
     96  [PluginId] UniqueIdentifier NOT NULL,
     97  [Data] VarBinary(MAX) NOT NULL,
     98  [FileName] VarChar(MAX) NOT NULL,
    9199  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
    92100  )
    93101CREATE TABLE [dbo].[StateLog](
    94   [StateLogId] UniqueIdentifier NOT NULL, 
    95   [State] VarChar(30) NOT NULL, 
    96   [DateTime] DateTime NOT NULL, 
    97   [TaskId] UniqueIdentifier NOT NULL, 
    98   [UserId] UniqueIdentifier, 
    99   [SlaveId] UniqueIdentifier, 
    100   [Exception] VarChar(MAX), 
     102  [StateLogId] UniqueIdentifier NOT NULL,
     103  [State] VarChar(30) NOT NULL,
     104  [DateTime] DateTime NOT NULL,
     105  [TaskId] UniqueIdentifier NOT NULL,
     106  [UserId] UniqueIdentifier,
     107  [SlaveId] UniqueIdentifier,
     108  [Exception] VarChar(MAX),
    101109  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
    102110  )
    103111CREATE TABLE [dbo].[JobPermission](
    104   [JobId] UniqueIdentifier NOT NULL, 
    105   [GrantedUserId] UniqueIdentifier NOT NULL, 
    106   [GrantedByUserId] UniqueIdentifier NOT NULL, 
    107   [Permission] VarChar(15) NOT NULL, 
     112  [JobId] UniqueIdentifier NOT NULL,
     113  [GrantedUserId] UniqueIdentifier NOT NULL,
     114  [GrantedByUserId] UniqueIdentifier NOT NULL,
     115  [Permission] VarChar(15) NOT NULL,
    108116  CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId])
    109117  )
    110118CREATE TABLE [Lifecycle](
    111   [LifecycleId] Int,
    112   [LastCleanup] DateTime,
     119  [LifecycleId] Int NOT NULL,
     120  [LastCleanup] DateTime NOT NULL,
    113121  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
    114122  )
    115123CREATE TABLE [DeletedJobStatistics](
    116   [UserId] UniqueIdentifier NOT NULL, 
    117   [ExecutionTimeS] float NOT NULL, 
    118   [ExecutionTimeSFinishedJobs] float NOT NULL, 
    119   [StartToEndTimeS] float NOT NULL, 
    120   [DeletedJobStatisticsId] UniqueIdentifier NOT NULL, 
     124  [UserId] UniqueIdentifier NOT NULL,
     125  [ExecutionTimeS] float NOT NULL,
     126  [ExecutionTimeSFinishedJobs] float NOT NULL,
     127  [StartToEndTimeS] float NOT NULL,
     128  [DeletedJobStatisticsId] UniqueIdentifier NOT NULL,
    121129  CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId])
    122130  )
    123131CREATE TABLE [UserStatistics](
    124   [StatisticsId] UniqueIdentifier NOT NULL, 
    125   [UserId] UniqueIdentifier NOT NULL, 
    126   [ExecutionTimeMs] float NOT NULL, 
    127   [UsedCores] Int NOT NULL, 
    128   [ExecutionTimeMsFinishedJobs] float NOT NULL, 
    129   [StartToEndTimeMs] float NOT NULL, 
     132  [StatisticsId] UniqueIdentifier NOT NULL,
     133  [UserId] UniqueIdentifier NOT NULL,
     134  [ExecutionTimeMs] float NOT NULL,
     135  [UsedCores] Int NOT NULL,
     136  [ExecutionTimeMsFinishedJobs] float NOT NULL,
     137  [StartToEndTimeMs] float NOT NULL,
    130138  CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId])
    131139  )
    132140CREATE TABLE [SlaveStatistics](
    133   [StatisticsId] UniqueIdentifier NOT NULL, 
    134   [SlaveId] UniqueIdentifier NOT NULL, 
    135   [Cores] Int NOT NULL, 
    136   [FreeCores] Int NOT NULL, 
    137   [CpuUtilization] float NOT NULL, 
    138   [Memory] Int NOT NULL, 
    139   [FreeMemory] Int NOT NULL, 
     141  [StatisticsId] UniqueIdentifier NOT NULL,
     142  [SlaveId] UniqueIdentifier NOT NULL,
     143  [Cores] Int NOT NULL,
     144  [FreeCores] Int NOT NULL,
     145  [CpuUtilization] float NOT NULL,
     146  [Memory] Int NOT NULL,
     147  [FreeMemory] Int NOT NULL,
    140148  CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId])
    141149  )
    142150CREATE TABLE [Statistics](
    143   [StatisticsId] UniqueIdentifier NOT NULL, 
    144   [Timestamp] DateTime NOT NULL, 
     151  [StatisticsId] UniqueIdentifier NOT NULL,
     152  [Timestamp] DateTime NOT NULL,
    145153  CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])
    146154  )
    … …  
    155163ALTER TABLE [dbo].[Resource]
    156164  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
     165ALTER TABLE [dbo].[ResourcePermission]
     166  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
    157167ALTER TABLE [dbo].[Task]
    158168  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])

  • branches/GP-MoveOperators/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

    r7185 r8085  
    8383ALTER TABLE [dbo].[JobPermission]  WITH CHECK ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY([JobId])
    8484REFERENCES [dbo].[Job] ([JobId])
     85ON UPDATE CASCADE
     86ON DELETE CASCADE
     87GO
     88
     89ALTER TABLE [dbo].[ResourcePermission]  DROP  CONSTRAINT [Resource_ResourcePermission]
     90ALTER TABLE [dbo].[ResourcePermission]  WITH CHECK ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY([ResourceId])
     91REFERENCES [dbo].[Resource] ([ResourceId])
    8592ON UPDATE CASCADE
    8693ON DELETE CASCADE
    … …  
    233240
    234241
    235 ALTER TRIGGER [dbo].[tr_StatisticsDeleteCascade] ON [dbo].[Statistics] INSTEAD OF DELETE AS
     242CREATE TRIGGER [dbo].[tr_StatisticsDeleteCascade] ON [dbo].[Statistics] INSTEAD OF DELETE AS
    236243BEGIN
    237244  DELETE SlaveStatistics FROM deleted, SlaveStatistics WHERE deleted.StatisticsId = SlaveStatistics.StatisticsId
Note: See TracChangeset for help on using the changeset viewer.