Free cookie consent management tool by TermsFeed Policy Generator

source: branches/ClientUserManagement/HeuristicLab.Services.Access.DataAccess/3.3/SQL Scripts/Initialize ClientManagement.sql @ 7534

Last change on this file since 7534 was 7534, checked in by ascheibe, 12 years ago

#1648 worked on user and client information singletons

File size: 11.2 KB
RevLine 
[6815]1USE [HeuristicLab.ClientManagement]
2GO
3
[6840]4/****** Object:  Table [dbo].[ClientConfiguration]    Script Date: 09/27/2011 17:05:22 ******/
[6815]5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
[6840]11CREATE TABLE [dbo].[ClientConfiguration](
[6815]12  [Id] [uniqueidentifier] NOT NULL,
[6840]13  [Hash] [nvarchar](max) NOT NULL,
[6815]14  [Description] [nvarchar](max) NULL,
[6840]15 CONSTRAINT [PK_ClientConfiguration] PRIMARY KEY CLUSTERED
[6815]16(
17  [Id] ASC
18)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
19) ON [PRIMARY]
20
21GO
[6825]22
[6840]23ALTER TABLE [dbo].[ClientConfiguration] ADD  CONSTRAINT [DF_ClientConfiguration_Id]  DEFAULT (newsequentialid()) FOR [Id]
[6825]24GO
25
[6840]26USE [HeuristicLab.ClientManagement]
[6825]27GO
28
[6840]29/****** Object:  Table [dbo].[ClientError]    Script Date: 09/27/2011 17:05:38 ******/
30SET ANSI_NULLS ON
[6825]31GO
32
[6840]33SET QUOTED_IDENTIFIER ON
[6825]34GO
35
[6840]36CREATE TABLE [dbo].[ClientError](
37  [Id] [uniqueidentifier] NOT NULL,
38  [Timestamp] [datetime] NOT NULL,
39  [Exception] [nvarchar](max) NULL,
40  [UserComment] [nvarchar](max) NULL,
41  [ConfigDump] [nvarchar](max) NULL,
42  [ClientId] [uniqueidentifier] NULL,
43  [UserId] [uniqueidentifier] NULL,
44 CONSTRAINT [PK_ClientError] PRIMARY KEY CLUSTERED
45(
46  [Id] ASC
47)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
48) ON [PRIMARY]
49
[6825]50GO
51
[6840]52ALTER TABLE [dbo].[ClientError]  WITH CHECK ADD  CONSTRAINT [FK_ClientError_Resource] FOREIGN KEY([ClientId])
53REFERENCES [dbo].[Resource] ([Id])
[6825]54GO
55
[6840]56ALTER TABLE [dbo].[ClientError] CHECK CONSTRAINT [FK_ClientError_Resource]
[6825]57GO
58
[6840]59ALTER TABLE [dbo].[ClientError]  WITH CHECK ADD  CONSTRAINT [FK_ClientError_UserGroup] FOREIGN KEY([UserId])
60REFERENCES [dbo].[UserGroup] ([Id])
[6825]61GO
62
[6840]63ALTER TABLE [dbo].[ClientError] CHECK CONSTRAINT [FK_ClientError_UserGroup]
[6825]64GO
65
[6840]66ALTER TABLE [dbo].[ClientError] ADD  CONSTRAINT [DF_ClientError_Id]  DEFAULT (newsequentialid()) FOR [Id]
67GO
[6825]68
[6840]69USE [HeuristicLab.ClientManagement]
70GO
71
72/****** Object:  Table [dbo].[ClientLog]    Script Date: 09/27/2011 17:05:50 ******/
[6815]73SET ANSI_NULLS ON
74GO
75
76SET QUOTED_IDENTIFIER ON
77GO
78
[6840]79CREATE TABLE [dbo].[ClientLog](
80  [Timestamp] [datetime] NOT NULL,
[6815]81  [ResourceId] [uniqueidentifier] NOT NULL,
[6840]82  [Message] [nvarchar](max) NULL,
83 CONSTRAINT [PK_ClientLog] PRIMARY KEY CLUSTERED
[6815]84(
[6840]85  [Timestamp] ASC,
86  [ResourceId] ASC
[6815]87)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
88) ON [PRIMARY]
89
90GO
91
[6840]92ALTER TABLE [dbo].[ClientLog]  WITH CHECK ADD  CONSTRAINT [FK_ClientLog_Resource] FOREIGN KEY([ResourceId])
[6815]93REFERENCES [dbo].[Resource] ([Id])
94GO
95
[6840]96ALTER TABLE [dbo].[ClientLog] CHECK CONSTRAINT [FK_ClientLog_Resource]
[6815]97GO
98
99
[6840]100USE [HeuristicLab.ClientManagement]
[6825]101GO
[6815]102
[6840]103/****** Object:  Table [dbo].[ClientType]    Script Date: 09/27/2011 17:06:09 ******/
[6815]104SET ANSI_NULLS ON
105GO
106
107SET QUOTED_IDENTIFIER ON
108GO
109
[6840]110CREATE TABLE [dbo].[ClientType](
[6825]111  [Id] [uniqueidentifier] NOT NULL,
112  [Name] [nvarchar](max) NOT NULL,
[6840]113 CONSTRAINT [PK_ClientType] PRIMARY KEY CLUSTERED
[6825]114(
115  [Id] ASC
116)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
117) ON [PRIMARY]
118
119GO
120
[6840]121ALTER TABLE [dbo].[ClientType] ADD  CONSTRAINT [DF_ClientType_Id]  DEFAULT (newsequentialid()) FOR [Id]
122GO
123
124USE [HeuristicLab.ClientManagement]
125GO
126
127/****** Object:  Table [dbo].[Country]    Script Date: 09/27/2011 17:06:21 ******/
[6825]128SET ANSI_NULLS ON
129GO
130
131SET QUOTED_IDENTIFIER ON
132GO
133
[6840]134CREATE TABLE [dbo].[Country](
135  [Id] [uniqueidentifier] NOT NULL,
136  [Name] [nvarchar](max) NOT NULL,
137 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
[6815]138(
[6840]139  [Id] ASC
[6815]140)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
141) ON [PRIMARY]
142
143GO
144
[6840]145ALTER TABLE [dbo].[Country] ADD  CONSTRAINT [DF_Country_Id]  DEFAULT (newsequentialid()) FOR [Id]
[6825]146GO
147
[6840]148USE [HeuristicLab.ClientManagement]
[6825]149GO
150
[6840]151/****** Object:  Table [dbo].[OperatingSystem]    Script Date: 09/27/2011 17:06:31 ******/
152SET ANSI_NULLS ON
[6815]153GO
154
[6840]155SET QUOTED_IDENTIFIER ON
[6815]156GO
157
[6840]158CREATE TABLE [dbo].[OperatingSystem](
159  [Id] [uniqueidentifier] NOT NULL,
160  [Name] [nvarchar](max) NOT NULL,
161 CONSTRAINT [PK_OperatingSystem] PRIMARY KEY CLUSTERED
162(
163  [Id] ASC
164)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
165) ON [PRIMARY]
[6825]166
[6840]167GO
168
169ALTER TABLE [dbo].[OperatingSystem] ADD  CONSTRAINT [DF_OperatingSystem_Id]  DEFAULT (newsequentialid()) FOR [Id]
170GO
171
172USE [HeuristicLab.ClientManagement]
173GO
174
175/****** Object:  Table [dbo].[Plugin]    Script Date: 09/27/2011 17:06:39 ******/
[6820]176SET ANSI_NULLS ON
177GO
[6815]178
[6820]179SET QUOTED_IDENTIFIER ON
180GO
[6815]181
[6840]182CREATE TABLE [dbo].[Plugin](
[6820]183  [Id] [uniqueidentifier] NOT NULL,
[6840]184  [Name] [nvarchar](max) NOT NULL,
185  [StrongName] [nvarchar](max) NULL,
186  [Version] [nvarchar](20) NOT NULL,
187 CONSTRAINT [PK_Plugin] PRIMARY KEY CLUSTERED
[6820]188(
189  [Id] ASC
190)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
191) ON [PRIMARY]
[6815]192
[6820]193GO
[6815]194
[6840]195ALTER TABLE [dbo].[Plugin] ADD  CONSTRAINT [DF_Plugin_Id]  DEFAULT (newsequentialid()) FOR [Id]
196GO
[6825]197
198
[6840]199USE [HeuristicLab.ClientManagement]
200GO
201
202/****** Object:  Table [dbo].[ResourcePlugin]    Script Date: 09/27/2011 17:07:01 ******/
[6820]203SET ANSI_NULLS ON
204GO
[6815]205
[6820]206SET QUOTED_IDENTIFIER ON
207GO
208
[6840]209CREATE TABLE [dbo].[ResourcePlugin](
210  [ResourceId] [uniqueidentifier] NOT NULL,
211  [PluginId] [uniqueidentifier] NOT NULL,
212 CONSTRAINT [PK_ResourcePlugin] PRIMARY KEY CLUSTERED
[6825]213(
[6840]214  [ResourceId] ASC,
215  [PluginId] ASC
[6825]216)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
217) ON [PRIMARY]
218
219GO
220
[6840]221ALTER TABLE [dbo].[ResourcePlugin]  WITH CHECK ADD  CONSTRAINT [FK_ResourcePlugin_Plugin] FOREIGN KEY([PluginId])
222REFERENCES [dbo].[Plugin] ([Id])
[6825]223GO
224
[6840]225ALTER TABLE [dbo].[ResourcePlugin] CHECK CONSTRAINT [FK_ResourcePlugin_Plugin]
[6825]226GO
227
[6840]228ALTER TABLE [dbo].[ResourcePlugin]  WITH CHECK ADD  CONSTRAINT [FK_ResourcePlugin_Resource] FOREIGN KEY([ResourceId])
229REFERENCES [dbo].[Resource] ([Id])
[6825]230GO
231
[6840]232ALTER TABLE [dbo].[ResourcePlugin] CHECK CONSTRAINT [FK_ResourcePlugin_Resource]
[6825]233GO
234
235
[6840]236USE [HeuristicLab.ClientManagement]
237GO
[6825]238
[6840]239/****** Object:  Table [dbo].[ResourceResourceGroup]    Script Date: 09/27/2011 17:07:08 ******/
[6825]240SET ANSI_NULLS ON
241GO
242
243SET QUOTED_IDENTIFIER ON
244GO
245
[6840]246CREATE TABLE [dbo].[ResourceResourceGroup](
247  [ResourceId] [uniqueidentifier] NOT NULL,
248  [ResourceGroupId] [uniqueidentifier] NOT NULL,
249 CONSTRAINT [PK_ResourceResourceGroup_Id] PRIMARY KEY CLUSTERED
[6820]250(
[6840]251  [ResourceId] ASC,
252  [ResourceGroupId] ASC
[6820]253)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
254) ON [PRIMARY]
255
256GO
257
[6840]258ALTER TABLE [dbo].[ResourceResourceGroup]  WITH CHECK ADD  CONSTRAINT [FK_ResourceResourceGroup_ResourceGroupId] FOREIGN KEY([ResourceGroupId])
259REFERENCES [dbo].[Resource] ([Id])
260GO
[6825]261
[6840]262ALTER TABLE [dbo].[ResourceResourceGroup] CHECK CONSTRAINT [FK_ResourceResourceGroup_ResourceGroupId]
263GO
[6825]264
[6840]265ALTER TABLE [dbo].[ResourceResourceGroup]  WITH CHECK ADD  CONSTRAINT [FK_ResourceResourceGroup_ResourceId] FOREIGN KEY([ResourceId])
266REFERENCES [dbo].[Resource] ([Id])
[6820]267GO
268
[6840]269ALTER TABLE [dbo].[ResourceResourceGroup] CHECK CONSTRAINT [FK_ResourceResourceGroup_ResourceId]
[6820]270GO
271
[6840]272USE [HeuristicLab.ClientManagement]
[6820]273GO
274
[6840]275/****** Object:  Table [dbo].[Resource]    Script Date: 09/27/2011 17:07:15 ******/
[6825]276SET ANSI_NULLS ON
[6820]277GO
278
[6825]279SET QUOTED_IDENTIFIER ON
[6820]280GO
281
[6840]282CREATE TABLE [dbo].[Resource](
[6825]283  [Id] [uniqueidentifier] NOT NULL,
284  [Name] [nvarchar](max) NOT NULL,
[6840]285  [Description] [nvarchar](max) NULL,
286  [Type] [nvarchar](max) NOT NULL,
287  [ProcessorType] [nvarchar](max) NULL,
288  [NumberOfCores] [int] NULL,
289  [MemorySize] [int] NULL,
290  [OperatingSystemId] [uniqueidentifier] NULL,
291  [CountryId] [uniqueidentifier] NULL,
292  [HeuristicLabVersion] [nvarchar](max) NULL,
293  [ClientTypeId] [uniqueidentifier] NULL,
294  [ClientConfigurationId] [uniqueidentifier] NULL,
295  [Timestamp] [datetime] NULL,
[7534]296  [PerformanceValue] [real] NULL,
[6840]297 CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED
[6825]298(
299  [Id] ASC
300)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
301) ON [PRIMARY]
302
303GO
304
[6840]305ALTER TABLE [dbo].[Resource]  WITH CHECK ADD  CONSTRAINT [FK_Resource_ClientConfiguration] FOREIGN KEY([ClientConfigurationId])
306REFERENCES [dbo].[ClientConfiguration] ([Id])
[6825]307GO
308
[6840]309ALTER TABLE [dbo].[Resource] CHECK CONSTRAINT [FK_Resource_ClientConfiguration]
[6825]310GO
311
[6840]312ALTER TABLE [dbo].[Resource]  WITH CHECK ADD  CONSTRAINT [FK_Resource_ClientType] FOREIGN KEY([ClientTypeId])
313REFERENCES [dbo].[ClientType] ([Id])
314GO
[6825]315
[6840]316ALTER TABLE [dbo].[Resource] CHECK CONSTRAINT [FK_Resource_ClientType]
[6825]317GO
318
[6840]319ALTER TABLE [dbo].[Resource]  WITH CHECK ADD  CONSTRAINT [FK_Resource_Country] FOREIGN KEY([CountryId])
320REFERENCES [dbo].[Country] ([Id])
[6825]321GO
322
[6840]323ALTER TABLE [dbo].[Resource] CHECK CONSTRAINT [FK_Resource_Country]
[6825]324GO
325
[6840]326ALTER TABLE [dbo].[Resource]  WITH CHECK ADD  CONSTRAINT [FK_Resource_OperatingSystem] FOREIGN KEY([OperatingSystemId])
327REFERENCES [dbo].[OperatingSystem] ([Id])
328GO
329
330ALTER TABLE [dbo].[Resource] CHECK CONSTRAINT [FK_Resource_OperatingSystem]
331GO
332
333ALTER TABLE [dbo].[Resource] ADD  CONSTRAINT [DF_Resource_Id]  DEFAULT (newsequentialid()) FOR [Id]
334GO
335
[6825]336USE [HeuristicLab.ClientManagement]
337GO
338
[6840]339/****** Object:  Table [dbo].[UserGroup]    Script Date: 09/27/2011 17:07:23 ******/
[6825]340SET ANSI_NULLS ON
341GO
342
343SET QUOTED_IDENTIFIER ON
344GO
345
[6840]346CREATE TABLE [dbo].[UserGroup](
[6825]347  [Id] [uniqueidentifier] NOT NULL,
[6840]348  [FullName] [nvarchar](max) NULL,
349  [Name] [nvarchar](max) NULL,
350  [Type] [nvarchar](10) NOT NULL,
351 CONSTRAINT [PK_UserGroup] PRIMARY KEY CLUSTERED
[6825]352(
353  [Id] ASC
354)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
355) ON [PRIMARY]
356
357GO
358
[6840]359USE [HeuristicLab.ClientManagement]
[6825]360GO
361
[6840]362/****** Object:  Table [dbo].[UserGroupUserGroup]    Script Date: 09/27/2011 17:07:30 ******/
363SET ANSI_NULLS ON
[6825]364GO
365
[6840]366SET QUOTED_IDENTIFIER ON
367GO
368
369CREATE TABLE [dbo].[UserGroupUserGroup](
370  [UserGroupId] [uniqueidentifier] NOT NULL,
371  [UserGroupUserGroupId] [uniqueidentifier] NOT NULL,
372 CONSTRAINT [PK_UserGroupUserGroup] PRIMARY KEY CLUSTERED
373(
374  [UserGroupId] ASC,
375  [UserGroupUserGroupId] ASC
376)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
377) ON [PRIMARY]
378
379GO
380
381ALTER TABLE [dbo].[UserGroupUserGroup]  WITH CHECK ADD  CONSTRAINT [FK_UserGroupUserGroup_UserGroup] FOREIGN KEY([UserGroupId])
[6820]382REFERENCES [dbo].[UserGroup] ([Id])
383GO
384
[6840]385ALTER TABLE [dbo].[UserGroupUserGroup] CHECK CONSTRAINT [FK_UserGroupUserGroup_UserGroup]
[6820]386GO
[6825]387
[6840]388ALTER TABLE [dbo].[UserGroupUserGroup]  WITH CHECK ADD  CONSTRAINT [FK_UserGroupUserGroup_UserGroup1] FOREIGN KEY([UserGroupUserGroupId])
389REFERENCES [dbo].[UserGroup] ([Id])
[6825]390GO
391
[6840]392ALTER TABLE [dbo].[UserGroupUserGroup] CHECK CONSTRAINT [FK_UserGroupUserGroup_UserGroup1]
[6825]393GO
394
395
[6840]396
397
398
399
400
401
402
403
Note: See TracBrowser for help on using the repository browser.