source: branches/OKB (trunk integration)/HeuristicLab.Services.OKB.DataAccess/3.3/SQL Scripts/Initialize OKB.sql @ 5378

USE [HeuristicLab.OKB]
GO
/****** Object:  Table [dbo].[ValueName]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ValueName](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
  [Category] [tinyint] NOT NULL,
  [Type] [tinyint] NOT NULL,
 CONSTRAINT [PK_ValueName_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ProblemClass]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProblemClass](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
  [Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_ProblemClass_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ_ProblemClass_Name] UNIQUE NONCLUSTERED 
(
  [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ProblemClass] ON
INSERT [dbo].[ProblemClass] ([Id], [Name], [Description]) VALUES (1, N'Unknown', N'Unknown or undefined problem class.')
SET IDENTITY_INSERT [dbo].[ProblemClass] OFF
/****** Object:  Table [dbo].[BinaryData]    Script Date: 01/26/2011 01:50:39 ******/
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
SET ARITHABORT ON
GO
CREATE TABLE [dbo].[BinaryData](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [Data] [varbinary](max) NOT NULL,
  [Hash]  AS (CONVERT([varbinary](20),hashbytes('SHA1',[Data]),(0))) PERSISTED,
 CONSTRAINT [PK_BinaryData_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ_BinaryData_Hash] UNIQUE NONCLUSTERED 
(
  [Hash] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[AlgorithmClass]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AlgorithmClass](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
  [Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_AlgorithmClass_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ_AlgorithmClass_Name] UNIQUE NONCLUSTERED 
(
  [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[AlgorithmClass] ON
INSERT [dbo].[AlgorithmClass] ([Id], [Name], [Description]) VALUES (1, N'Unknown', N'Unknown or undefined algorithm class.')
SET IDENTITY_INSERT [dbo].[AlgorithmClass] OFF
/****** Object:  Table [dbo].[Platform]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Platform](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
  [Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_Platform_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ_Platform_Name] UNIQUE NONCLUSTERED 
(
  [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Platform] ON
INSERT [dbo].[Platform] ([Id], [Name], [Description]) VALUES (1, N'Unknown', N'Unknown or undefined platform.')
INSERT [dbo].[Platform] ([Id], [Name], [Description]) VALUES (2, N'HeuristicLab 3.3', NULL)
SET IDENTITY_INSERT [dbo].[Platform] OFF
/****** Object:  Table [dbo].[DataType]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataType](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [PlatformId] [bigint] NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
  [TypeName] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_DataType_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Problem]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Problem](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [ProblemClassId] [bigint] NOT NULL,
  [PlatformId] [bigint] NOT NULL,
  [DataTypeId] [bigint] NOT NULL,
  [BinaryDataId] [bigint] NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
  [Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_Problem_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ_Problem_Name_PlatformId] UNIQUE NONCLUSTERED 
(
  [Name] ASC,
  [PlatformId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Algorithm]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Algorithm](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [AlgorithmClassId] [bigint] NOT NULL,
  [PlatformId] [bigint] NOT NULL,
  [DataTypeId] [bigint] NOT NULL,
  [BinaryDataId] [bigint] NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
  [Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_Algorithm_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ_Algorithm_Name_PlatformId] UNIQUE NONCLUSTERED 
(
  [Name] ASC,
  [PlatformId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[AlgorithmUser]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AlgorithmUser](
  [AlgorithmId] [bigint] NOT NULL,
  [UserId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_AlgorithmUser_AlgorithmId_UserId] PRIMARY KEY CLUSTERED 
(
  [AlgorithmId] ASC,
  [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Run]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Run](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [AlgorithmId] [bigint] NOT NULL,
  [ProblemId] [bigint] NOT NULL,
  [RandomSeed] [int] NOT NULL,
  [CreatedDate] [datetime2](7) NOT NULL,
  [UserId] [uniqueidentifier] NOT NULL,
  [ClientId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Run_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ProblemUser]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProblemUser](
  [ProblemId] [bigint] NOT NULL,
  [UserId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_ProblemUser_ProblemId_UserId] PRIMARY KEY CLUSTERED 
(
  [ProblemId] ASC,
  [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Value]    Script Date: 01/26/2011 01:50:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Value](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [RunId] [bigint] NOT NULL,
  [ValueNameId] [bigint] NOT NULL,
  [DataTypeId] [bigint] NOT NULL,
  [BoolValue] [bit] SPARSE  NULL,
  [IntValue] [int] SPARSE  NULL,
  [LongValue] [bigint] SPARSE  NULL,
  [FloatValue] [real] SPARSE  NULL,
  [DoubleValue] [float] SPARSE  NULL,
  [StringValue] [nvarchar](max) SPARSE  NULL,
  [BinaryDataId] [bigint] SPARSE  NULL,
 CONSTRAINT [PK_Value_Id] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_AlgorithmClass_Algorithm]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Algorithm]  WITH CHECK ADD  CONSTRAINT [FK_AlgorithmClass_Algorithm] FOREIGN KEY([AlgorithmClassId])
REFERENCES [dbo].[AlgorithmClass] ([Id])
GO
ALTER TABLE [dbo].[Algorithm] CHECK CONSTRAINT [FK_AlgorithmClass_Algorithm]
GO
/****** Object:  ForeignKey [FK_BinaryData_Algorithm]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Algorithm]  WITH CHECK ADD  CONSTRAINT [FK_BinaryData_Algorithm] FOREIGN KEY([BinaryDataId])
REFERENCES [dbo].[BinaryData] ([Id])
GO
ALTER TABLE [dbo].[Algorithm] CHECK CONSTRAINT [FK_BinaryData_Algorithm]
GO
/****** Object:  ForeignKey [FK_DataType_Algorithm]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Algorithm]  WITH CHECK ADD  CONSTRAINT [FK_DataType_Algorithm] FOREIGN KEY([DataTypeId])
REFERENCES [dbo].[DataType] ([Id])
GO
ALTER TABLE [dbo].[Algorithm] CHECK CONSTRAINT [FK_DataType_Algorithm]
GO
/****** Object:  ForeignKey [FK_Platform_Algorithm]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Algorithm]  WITH CHECK ADD  CONSTRAINT [FK_Platform_Algorithm] FOREIGN KEY([PlatformId])
REFERENCES [dbo].[Platform] ([Id])
GO
ALTER TABLE [dbo].[Algorithm] CHECK CONSTRAINT [FK_Platform_Algorithm]
GO
/****** Object:  ForeignKey [FK_Algorithm_AlgorithmUser]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[AlgorithmUser]  WITH CHECK ADD  CONSTRAINT [FK_Algorithm_AlgorithmUser] FOREIGN KEY([AlgorithmId])
REFERENCES [dbo].[Algorithm] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AlgorithmUser] CHECK CONSTRAINT [FK_Algorithm_AlgorithmUser]
GO
/****** Object:  ForeignKey [FK_Platform_DataType]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[DataType]  WITH CHECK ADD  CONSTRAINT [FK_Platform_DataType] FOREIGN KEY([PlatformId])
REFERENCES [dbo].[Platform] ([Id])
GO
ALTER TABLE [dbo].[DataType] CHECK CONSTRAINT [FK_Platform_DataType]
GO
/****** Object:  ForeignKey [FK_BinaryData_Problem]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Problem]  WITH CHECK ADD  CONSTRAINT [FK_BinaryData_Problem] FOREIGN KEY([BinaryDataId])
REFERENCES [dbo].[BinaryData] ([Id])
GO
ALTER TABLE [dbo].[Problem] CHECK CONSTRAINT [FK_BinaryData_Problem]
GO
/****** Object:  ForeignKey [FK_DataType_Problem]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Problem]  WITH CHECK ADD  CONSTRAINT [FK_DataType_Problem] FOREIGN KEY([DataTypeId])
REFERENCES [dbo].[DataType] ([Id])
GO
ALTER TABLE [dbo].[Problem] CHECK CONSTRAINT [FK_DataType_Problem]
GO
/****** Object:  ForeignKey [FK_Platform_Problem]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Problem]  WITH CHECK ADD  CONSTRAINT [FK_Platform_Problem] FOREIGN KEY([PlatformId])
REFERENCES [dbo].[Platform] ([Id])
GO
ALTER TABLE [dbo].[Problem] CHECK CONSTRAINT [FK_Platform_Problem]
GO
/****** Object:  ForeignKey [FK_ProblemClass_Problem]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Problem]  WITH CHECK ADD  CONSTRAINT [FK_ProblemClass_Problem] FOREIGN KEY([ProblemClassId])
REFERENCES [dbo].[ProblemClass] ([Id])
GO
ALTER TABLE [dbo].[Problem] CHECK CONSTRAINT [FK_ProblemClass_Problem]
GO
/****** Object:  ForeignKey [FK_Problem_ProblemUser]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[ProblemUser]  WITH CHECK ADD  CONSTRAINT [FK_Problem_ProblemUser] FOREIGN KEY([ProblemId])
REFERENCES [dbo].[Problem] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ProblemUser] CHECK CONSTRAINT [FK_Problem_ProblemUser]
GO
/****** Object:  ForeignKey [FK_Algorithm_Run]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Run]  WITH CHECK ADD  CONSTRAINT [FK_Algorithm_Run] FOREIGN KEY([AlgorithmId])
REFERENCES [dbo].[Algorithm] ([Id])
GO
ALTER TABLE [dbo].[Run] CHECK CONSTRAINT [FK_Algorithm_Run]
GO
/****** Object:  ForeignKey [FK_Problem_Run]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Run]  WITH CHECK ADD  CONSTRAINT [FK_Problem_Run] FOREIGN KEY([ProblemId])
REFERENCES [dbo].[Problem] ([Id])
GO
ALTER TABLE [dbo].[Run] CHECK CONSTRAINT [FK_Problem_Run]
GO
/****** Object:  ForeignKey [FK_BinaryData_Value]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Value]  WITH CHECK ADD  CONSTRAINT [FK_BinaryData_Value] FOREIGN KEY([BinaryDataId])
REFERENCES [dbo].[BinaryData] ([Id])
GO
ALTER TABLE [dbo].[Value] CHECK CONSTRAINT [FK_BinaryData_Value]
GO
/****** Object:  ForeignKey [FK_DataType_Value]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Value]  WITH CHECK ADD  CONSTRAINT [FK_DataType_Value] FOREIGN KEY([DataTypeId])
REFERENCES [dbo].[DataType] ([Id])
GO
ALTER TABLE [dbo].[Value] CHECK CONSTRAINT [FK_DataType_Value]
GO
/****** Object:  ForeignKey [FK_Run_Value]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Value]  WITH CHECK ADD  CONSTRAINT [FK_Run_Value] FOREIGN KEY([RunId])
REFERENCES [dbo].[Run] ([Id])
GO
ALTER TABLE [dbo].[Value] CHECK CONSTRAINT [FK_Run_Value]
GO
/****** Object:  ForeignKey [FK_ValueName_Value]    Script Date: 01/26/2011 01:50:39 ******/
ALTER TABLE [dbo].[Value]  WITH CHECK ADD  CONSTRAINT [FK_ValueName_Value] FOREIGN KEY([ValueNameId])
REFERENCES [dbo].[ValueName] ([Id])
GO
ALTER TABLE [dbo].[Value] CHECK CONSTRAINT [FK_ValueName_Value]
GO