Free cookie consent management tool by TermsFeed Policy Generator

source: branches/ProblemRefactoring/HeuristicLab.Services.Access.DataAccess/3.3/SQL Scripts/Initialize AccessServiceDB.sql @ 14579

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

#1648 integrated server parts of the access service into trunk

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