Free cookie consent management tool by TermsFeed Policy Generator

Changeset 7847


Ignore:
Timestamp:
05/18/12 14:34:58 (13 years ago)
Author:
jkarder
Message:

#1722: added some integrity constraints in Initialize Hive Database.sql

File:
1 edited

Legend:

Unmodified
Added
Removed

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

    r6983 r7847  
    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,
    4343  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
    4444  )
    4545CREATE 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,
     46  [TaskId] UniqueIdentifier NOT NULL,
     47  [TaskState] VarChar(30) NOT NULL,
     48  [ExecutionTimeMs] float NOT NULL,
     49  [LastHeartbeat] DateTime,
     50  [ParentTaskId] UniqueIdentifier,
     51  [Priority] Int NOT NULL,
     52  [CoresNeeded] Int NOT NULL,
     53  [MemoryNeeded] Int NOT NULL,
     54  [IsParentTask] Bit NOT NULL,
     55  [FinishWhenChildJobsFinished] Bit NOT NULL,
     56  [Command] VarChar(30),
     57  [JobId] UniqueIdentifier NOT NULL,
     58  [IsPrivileged] Bit NOT NULL,
    5959  CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId])
    6060  )
    6161CREATE TABLE [dbo].[Downtime](
    62   [DowntimeId] UniqueIdentifier,
    63   [ResourceId] UniqueIdentifier,
    64   [StartDate] DateTime,
    65   [EndDate] DateTime,
    66   [AllDayEvent] Bit,
    67   [Recurring] Bit,
    68   [RecurringId] UniqueIdentifier,
     62  [DowntimeId] UniqueIdentifier NOT NULL,
     63  [ResourceId] UniqueIdentifier NOT NULL,
     64  [StartDate] DateTime NOT NULL,
     65  [EndDate] DateTime NOT NULL,
     66  [AllDayEvent] Bit NOT NULL,
     67  [Recurring] Bit NOT NULL,
     68  [RecurringId] UniqueIdentifier NOT NULL,
    6969  CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId])
    7070  )
    7171CREATE 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,   
     72  [JobId] UniqueIdentifier NOT NULL,
     73  [Name] VarChar(MAX) NOT NULL,
     74  [Description] VarChar(MAX),
     75  [ResourceIds] VarChar(MAX),
     76  [OwnerUserId] UniqueIdentifier NOT NULL,
     77  [DateCreated] DateTime NOT NULL,
    7878  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
    7979  )
    8080CREATE TABLE [dbo].[TaskData](
    81   [TaskId] UniqueIdentifier NOT NULL, 
    82   [Data] VarBinary(MAX),
    83   [LastUpdate] DateTime,
     81  [TaskId] UniqueIdentifier NOT NULL,
     82  [Data] VarBinary(MAX) NOT NULL,
     83  [LastUpdate] DateTime NOT NULL,
    8484  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
    8585  )
    8686CREATE TABLE [dbo].[PluginData](
    87   [PluginDataId] UniqueIdentifier NOT NULL, 
    88   [PluginId] UniqueIdentifier NOT NULL, 
    89   [Data] VarBinary(MAX) NOT NULL, 
    90   [FileName] VarChar(MAX),
     87  [PluginDataId] UniqueIdentifier NOT NULL,
     88  [PluginId] UniqueIdentifier NOT NULL,
     89  [Data] VarBinary(MAX) NOT NULL,
     90  [FileName] VarChar(MAX) NOT NULL,
    9191  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
    9292  )
    9393CREATE 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), 
     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),
    101101  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
    102102  )
    103103CREATE TABLE [dbo].[JobPermission](
    104   [JobId] UniqueIdentifier NOT NULL, 
    105   [GrantedUserId] UniqueIdentifier NOT NULL, 
    106   [GrantedByUserId] UniqueIdentifier NOT NULL, 
    107   [Permission] VarChar(15) NOT NULL, 
     104  [JobId] UniqueIdentifier NOT NULL,
     105  [GrantedUserId] UniqueIdentifier NOT NULL,
     106  [GrantedByUserId] UniqueIdentifier NOT NULL,
     107  [Permission] VarChar(15) NOT NULL,
    108108  CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId])
    109109  )
    110110CREATE TABLE [Lifecycle](
    111   [LifecycleId] Int,
    112   [LastCleanup] DateTime,
     111  [LifecycleId] Int NOT NULL,
     112  [LastCleanup] DateTime NOT NULL,
    113113  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
    114114  )
    115115CREATE 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, 
     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,
    121121  CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId])
    122122  )
    123123CREATE 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, 
     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,
    130130  CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId])
    131131  )
    132132CREATE 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, 
     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,
    140140  CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId])
    141141  )
    142142CREATE TABLE [Statistics](
    143   [StatisticsId] UniqueIdentifier NOT NULL, 
    144   [Timestamp] DateTime NOT NULL, 
     143  [StatisticsId] UniqueIdentifier NOT NULL,
     144  [Timestamp] DateTime NOT NULL,
    145145  CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])
    146146  )
Note: See TracChangeset for help on using the changeset viewer.