Changeset 7847 for trunk/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql
- Timestamp:
- 05/18/12 14:34:58 (13 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql
r6983 r7847 5 5 CREATE TABLE [dbo].[AssignedResources]( 6 6 [ResourceId] UniqueIdentifier NOT NULL, 7 [TaskId] UniqueIdentifier NOT NULL, 7 [TaskId] UniqueIdentifier NOT NULL, 8 8 CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId]) 9 9 ) 10 10 CREATE 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, 17 17 CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId]) 18 18 ) 19 19 CREATE 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, 23 23 CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId]) 24 24 ) 25 25 CREATE 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 43 CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId]) 44 44 ) 45 45 CREATE 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, 59 59 CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId]) 60 60 ) 61 61 CREATE 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, 69 69 CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId]) 70 70 ) 71 71 CREATE 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, 78 78 CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId]) 79 79 ) 80 80 CREATE 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, 84 84 CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId]) 85 85 ) 86 86 CREATE 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, 91 91 CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId]) 92 92 ) 93 93 CREATE 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), 101 101 CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId]) 102 102 ) 103 103 CREATE 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, 108 108 CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId]) 109 109 ) 110 110 CREATE TABLE [Lifecycle]( 111 [LifecycleId] Int ,112 [LastCleanup] DateTime ,111 [LifecycleId] Int NOT NULL, 112 [LastCleanup] DateTime NOT NULL, 113 113 CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId]) 114 114 ) 115 115 CREATE 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, 121 121 CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId]) 122 122 ) 123 123 CREATE 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, 130 130 CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId]) 131 131 ) 132 132 CREATE 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, 140 140 CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId]) 141 141 ) 142 142 CREATE TABLE [Statistics]( 143 [StatisticsId] UniqueIdentifier NOT NULL, 144 [Timestamp] DateTime NOT NULL, 143 [StatisticsId] UniqueIdentifier NOT NULL, 144 [Timestamp] DateTime NOT NULL, 145 145 CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId]) 146 146 )
Note: See TracChangeset
for help on using the changeset viewer.
