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, 8 years ago

#1648 worked on user and client information singletons

File size: 11.2 KB
Line 
1USE [HeuristicLab.ClientManagement]
2GO
3
4/****** Object:  Table [dbo].[ClientConfiguration]    Script Date: 09/27/2011 17:05:22 ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11CREATE TABLE [dbo].[ClientConfiguration](
12  [Id] [uniqueidentifier] NOT NULL,
13  [Hash] [nvarchar](max) NOT NULL,
14  [Description] [nvarchar](max) NULL,
15 CONSTRAINT [PK_ClientConfiguration] PRIMARY KEY CLUSTERED
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
22
23ALTER TABLE [dbo].[ClientConfiguration] ADD  CONSTRAINT [DF_ClientConfiguration_Id]  DEFAULT (newsequentialid()) FOR [Id]
24GO
25
26USE [HeuristicLab.ClientManagement]
27GO
28
29/****** Object:  Table [dbo].[ClientError]    Script Date: 09/27/2011 17:05:38 ******/
30SET ANSI_NULLS ON
31GO
32
33SET QUOTED_IDENTIFIER ON
34GO
35
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
50GO
51
52ALTER TABLE [dbo].[ClientError]  WITH CHECK ADD  CONSTRAINT [FK_ClientError_Resource] FOREIGN KEY([ClientId])
53REFERENCES [dbo].[Resource] ([Id])
54GO
55
56ALTER TABLE [dbo].[ClientError] CHECK CONSTRAINT [FK_ClientError_Resource]
57GO
58
59ALTER TABLE [dbo].[ClientError]  WITH CHECK ADD  CONSTRAINT [FK_ClientError_UserGroup] FOREIGN KEY([UserId])
60REFERENCES [dbo].[UserGroup] ([Id])
61GO
62
63ALTER TABLE [dbo].[ClientError] CHECK CONSTRAINT [FK_ClientError_UserGroup]
64GO
65
66ALTER TABLE [dbo].[ClientError] ADD  CONSTRAINT [DF_ClientError_Id]  DEFAULT (newsequentialid()) FOR [Id]
67GO
68
69USE [HeuristicLab.ClientManagement]
70GO
71
72/****** Object:  Table [dbo].[ClientLog]    Script Date: 09/27/2011 17:05:50 ******/
73SET ANSI_NULLS ON
74GO
75
76SET QUOTED_IDENTIFIER ON
77GO
78
79CREATE TABLE [dbo].[ClientLog](
80  [Timestamp] [datetime] NOT NULL,
81  [ResourceId] [uniqueidentifier] NOT NULL,
82  [Message] [nvarchar](max) NULL,
83 CONSTRAINT [PK_ClientLog] PRIMARY KEY CLUSTERED
84(
85  [Timestamp] ASC,
86  [ResourceId] ASC
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
92ALTER TABLE [dbo].[ClientLog]  WITH CHECK ADD  CONSTRAINT [FK_ClientLog_Resource] FOREIGN KEY([ResourceId])
93REFERENCES [dbo].[Resource] ([Id])
94GO
95
96ALTER TABLE [dbo].[ClientLog] CHECK CONSTRAINT [FK_ClientLog_Resource]
97GO
98
99
100USE [HeuristicLab.ClientManagement]
101GO
102
103/****** Object:  Table [dbo].[ClientType]    Script Date: 09/27/2011 17:06:09 ******/
104SET ANSI_NULLS ON
105GO
106
107SET QUOTED_IDENTIFIER ON
108GO
109
110CREATE TABLE [dbo].[ClientType](
111  [Id] [uniqueidentifier] NOT NULL,
112  [Name] [nvarchar](max) NOT NULL,
113 CONSTRAINT [PK_ClientType] PRIMARY KEY CLUSTERED
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
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 ******/
128SET ANSI_NULLS ON
129GO
130
131SET QUOTED_IDENTIFIER ON
132GO
133
134CREATE TABLE [dbo].[Country](
135  [Id] [uniqueidentifier] NOT NULL,
136  [Name] [nvarchar](max) NOT NULL,
137 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
138(
139  [Id] ASC
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
145ALTER TABLE [dbo].[Country] ADD  CONSTRAINT [DF_Country_Id]  DEFAULT (newsequentialid()) FOR [Id]
146GO
147
148USE [HeuristicLab.ClientManagement]
149GO
150
151/****** Object:  Table [dbo].[OperatingSystem]    Script Date: 09/27/2011 17:06:31 ******/
152SET ANSI_NULLS ON
153GO
154
155SET QUOTED_IDENTIFIER ON
156GO
157
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]
166
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 ******/
176SET ANSI_NULLS ON
177GO
178
179SET QUOTED_IDENTIFIER ON
180GO
181
182CREATE TABLE [dbo].[Plugin](
183  [Id] [uniqueidentifier] NOT NULL,
184  [Name] [nvarchar](max) NOT NULL,
185  [StrongName] [nvarchar](max) NULL,
186  [Version] [nvarchar](20) NOT NULL,
187 CONSTRAINT [PK_Plugin] PRIMARY KEY CLUSTERED
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]
192
193GO
194
195ALTER TABLE [dbo].[Plugin] ADD  CONSTRAINT [DF_Plugin_Id]  DEFAULT (newsequentialid()) FOR [Id]
196GO
197
198
199USE [HeuristicLab.ClientManagement]
200GO
201
202/****** Object:  Table [dbo].[ResourcePlugin]    Script Date: 09/27/2011 17:07:01 ******/
203SET ANSI_NULLS ON
204GO
205
206SET QUOTED_IDENTIFIER ON
207GO
208
209CREATE TABLE [dbo].[ResourcePlugin](
210  [ResourceId] [uniqueidentifier] NOT NULL,
211  [PluginId] [uniqueidentifier] NOT NULL,
212 CONSTRAINT [PK_ResourcePlugin] PRIMARY KEY CLUSTERED
213(
214  [ResourceId] ASC,
215  [PluginId] ASC
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
221ALTER TABLE [dbo].[ResourcePlugin]  WITH CHECK ADD  CONSTRAINT [FK_ResourcePlugin_Plugin] FOREIGN KEY([PluginId])
222REFERENCES [dbo].[Plugin] ([Id])
223GO
224
225ALTER TABLE [dbo].[ResourcePlugin] CHECK CONSTRAINT [FK_ResourcePlugin_Plugin]
226GO
227
228ALTER TABLE [dbo].[ResourcePlugin]  WITH CHECK ADD  CONSTRAINT [FK_ResourcePlugin_Resource] FOREIGN KEY([ResourceId])
229REFERENCES [dbo].[Resource] ([Id])
230GO
231
232ALTER TABLE [dbo].[ResourcePlugin] CHECK CONSTRAINT [FK_ResourcePlugin_Resource]
233GO
234
235
236USE [HeuristicLab.ClientManagement]
237GO
238
239/****** Object:  Table [dbo].[ResourceResourceGroup]    Script Date: 09/27/2011 17:07:08 ******/
240SET ANSI_NULLS ON
241GO
242
243SET QUOTED_IDENTIFIER ON
244GO
245
246CREATE TABLE [dbo].[ResourceResourceGroup](
247  [ResourceId] [uniqueidentifier] NOT NULL,
248  [ResourceGroupId] [uniqueidentifier] NOT NULL,
249 CONSTRAINT [PK_ResourceResourceGroup_Id] PRIMARY KEY CLUSTERED
250(
251  [ResourceId] ASC,
252  [ResourceGroupId] ASC
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
258ALTER TABLE [dbo].[ResourceResourceGroup]  WITH CHECK ADD  CONSTRAINT [FK_ResourceResourceGroup_ResourceGroupId] FOREIGN KEY([ResourceGroupId])
259REFERENCES [dbo].[Resource] ([Id])
260GO
261
262ALTER TABLE [dbo].[ResourceResourceGroup] CHECK CONSTRAINT [FK_ResourceResourceGroup_ResourceGroupId]
263GO
264
265ALTER TABLE [dbo].[ResourceResourceGroup]  WITH CHECK ADD  CONSTRAINT [FK_ResourceResourceGroup_ResourceId] FOREIGN KEY([ResourceId])
266REFERENCES [dbo].[Resource] ([Id])
267GO
268
269ALTER TABLE [dbo].[ResourceResourceGroup] CHECK CONSTRAINT [FK_ResourceResourceGroup_ResourceId]
270GO
271
272USE [HeuristicLab.ClientManagement]
273GO
274
275/****** Object:  Table [dbo].[Resource]    Script Date: 09/27/2011 17:07:15 ******/
276SET ANSI_NULLS ON
277GO
278
279SET QUOTED_IDENTIFIER ON
280GO
281
282CREATE TABLE [dbo].[Resource](
283  [Id] [uniqueidentifier] NOT NULL,
284  [Name] [nvarchar](max) NOT NULL,
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,
296  [PerformanceValue] [real] NULL,
297 CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED
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
305ALTER TABLE [dbo].[Resource]  WITH CHECK ADD  CONSTRAINT [FK_Resource_ClientConfiguration] FOREIGN KEY([ClientConfigurationId])
306REFERENCES [dbo].[ClientConfiguration] ([Id])
307GO
308
309ALTER TABLE [dbo].[Resource] CHECK CONSTRAINT [FK_Resource_ClientConfiguration]
310GO
311
312ALTER TABLE [dbo].[Resource]  WITH CHECK ADD  CONSTRAINT [FK_Resource_ClientType] FOREIGN KEY([ClientTypeId])
313REFERENCES [dbo].[ClientType] ([Id])
314GO
315
316ALTER TABLE [dbo].[Resource] CHECK CONSTRAINT [FK_Resource_ClientType]
317GO
318
319ALTER TABLE [dbo].[Resource]  WITH CHECK ADD  CONSTRAINT [FK_Resource_Country] FOREIGN KEY([CountryId])
320REFERENCES [dbo].[Country] ([Id])
321GO
322
323ALTER TABLE [dbo].[Resource] CHECK CONSTRAINT [FK_Resource_Country]
324GO
325
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
336USE [HeuristicLab.ClientManagement]
337GO
338
339/****** Object:  Table [dbo].[UserGroup]    Script Date: 09/27/2011 17:07:23 ******/
340SET ANSI_NULLS ON
341GO
342
343SET QUOTED_IDENTIFIER ON
344GO
345
346CREATE TABLE [dbo].[UserGroup](
347  [Id] [uniqueidentifier] NOT NULL,
348  [FullName] [nvarchar](max) NULL,
349  [Name] [nvarchar](max) NULL,
350  [Type] [nvarchar](10) NOT NULL,
351 CONSTRAINT [PK_UserGroup] PRIMARY KEY CLUSTERED
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
359USE [HeuristicLab.ClientManagement]
360GO
361
362/****** Object:  Table [dbo].[UserGroupUserGroup]    Script Date: 09/27/2011 17:07:30 ******/
363SET ANSI_NULLS ON
364GO
365
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])
382REFERENCES [dbo].[UserGroup] ([Id])
383GO
384
385ALTER TABLE [dbo].[UserGroupUserGroup] CHECK CONSTRAINT [FK_UserGroupUserGroup_UserGroup]
386GO
387
388ALTER TABLE [dbo].[UserGroupUserGroup]  WITH CHECK ADD  CONSTRAINT [FK_UserGroupUserGroup_UserGroup1] FOREIGN KEY([UserGroupUserGroupId])
389REFERENCES [dbo].[UserGroup] ([Id])
390GO
391
392ALTER TABLE [dbo].[UserGroupUserGroup] CHECK CONSTRAINT [FK_UserGroupUserGroup_UserGroup1]
393GO
394
395
396
397
398
399
400
401
402
403
Note: See TracBrowser for help on using the repository browser.