Changeset 8085 for branches/GP-MoveOperators/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql
- Timestamp:
- 06/21/12 18:02:33 (12 years ago)
- Location:
- branches/GP-MoveOperators
- Files:
-
- 2 edited
-
. (modified) (2 props)
-
HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql (modified) (2 diffs)
Legend:
- Unmodified
- Added
- Removed
-
branches/GP-MoveOperators
- Property svn:ignore
-
old new 20 20 bin 21 21 protoc.exe 22 _ReSharper.HeuristicLab 3.3 Tests
-
- Property svn:mergeinfo changed
- Property svn:ignore
-
branches/GP-MoveOperators/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql
r6983 r8085 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 [IsDisposable] Bit, 44 [OwnerUserId] UniqueIdentifier, 43 45 CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId]) 44 46 ) 47 CREATE 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 ) 45 53 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 ,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, 59 67 CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId]) 60 68 ) 61 69 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 ,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, 69 77 CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId]) 70 78 ) 71 79 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 ,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, 78 86 CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId]) 79 87 ) 80 88 CREATE 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, 84 92 CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId]) 85 93 ) 86 94 CREATE 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, 91 99 CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId]) 92 100 ) 93 101 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), 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), 101 109 CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId]) 102 110 ) 103 111 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, 112 [JobId] UniqueIdentifier NOT NULL, 113 [GrantedUserId] UniqueIdentifier NOT NULL, 114 [GrantedByUserId] UniqueIdentifier NOT NULL, 115 [Permission] VarChar(15) NOT NULL, 108 116 CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId]) 109 117 ) 110 118 CREATE TABLE [Lifecycle]( 111 [LifecycleId] Int ,112 [LastCleanup] DateTime ,119 [LifecycleId] Int NOT NULL, 120 [LastCleanup] DateTime NOT NULL, 113 121 CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId]) 114 122 ) 115 123 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, 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, 121 129 CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId]) 122 130 ) 123 131 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, 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, 130 138 CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId]) 131 139 ) 132 140 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, 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, 140 148 CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId]) 141 149 ) 142 150 CREATE TABLE [Statistics]( 143 [StatisticsId] UniqueIdentifier NOT NULL, 144 [Timestamp] DateTime NOT NULL, 151 [StatisticsId] UniqueIdentifier NOT NULL, 152 [Timestamp] DateTime NOT NULL, 145 153 CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId]) 146 154 ) … … 155 163 ALTER TABLE [dbo].[Resource] 156 164 ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) 165 ALTER TABLE [dbo].[ResourcePermission] 166 ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) 157 167 ALTER TABLE [dbo].[Task] 158 168 ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
Note: See TracChangeset
for help on using the changeset viewer.
