/****** SCRIPT PARA LA CREACIÓN DE LA BASE DE DATOS DE PRESERVACIÓN DE RESULTADOS DE CVX ******/
/****** VERSIÓN PARA SQL SERVER 2000 ******/


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_Results](
	[id_Result] [bigint] IDENTITY(1,1) NOT NULL,
	[id_MasterResult] [bigint] NOT NULL,
	[id_FieldAlias] [varchar](30) NOT NULL,
	[id_ObjectInstance] [bigint] NOT NULL,
	[int_Result1] [bigint] NULL,
	[float_Result1] [float] NULL,
	[float_Result2] [float] NULL,
	[float_Result3] [float] NULL,
	[date_Result1] [datetime] NULL,
	[txt_Result1] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_Results] PRIMARY KEY CLUSTERED 
(
	[id_Result] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [idx_alias] ON [tbl_Results] 
(
	[id_FieldAlias] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_marter_instance_alias] ON [tbl_Results] 
(
	[id_MasterResult] ASC,
	[id_ObjectInstance] ASC,
	[id_FieldAlias] ASC
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_Parameters](
	[id_Parameter] [bigint] IDENTITY(1,1) NOT NULL,
	[id_MasterResult] [bigint] NOT NULL,
	[txtParameterAlias] [varchar](50) NOT NULL,
	[float_Parameter] [float] NULL,
	[date_Parameter] [datetime] NULL,
	[int_Parameter] [int] NULL,
 CONSTRAINT [PK_tbl_Parameters] PRIMARY KEY CLUSTERED 
(
	[id_Parameter] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [idx_date] ON [tbl_Parameters] 
(
	[date_Parameter] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_master_alias] ON [tbl_Parameters] 
(
	[id_MasterResult] ASC,
	[txtParameterAlias] ASC
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_Objects](
	[id_ObjectInstance] [bigint] IDENTITY(1,1) NOT NULL,
	[id_MasterResult] [bigint] NOT NULL,
	[txtObjectClass] [varchar](30) NOT NULL,
	[txtObjectAlias] [varchar](100) NOT NULL,
	[txtObjectName] [varchar](150) NOT NULL,
	[txtObjectAux] [varchar](100) NULL,
	[id_ParentInstance] [bigint] NULL,
	[id_RelatedInstance] [bigint] NULL,
	[txtRelationType] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_Objects] PRIMARY KEY CLUSTERED 
(
	[id_ObjectInstance] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [idx_alias] ON [tbl_Objects] 
(
	[txtObjectAlias] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_master] ON [tbl_Objects] 
(
	[id_MasterResult] ASC
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_MasterResults](
	[id_MasterResult] [bigint] IDENTITY(1,1) NOT NULL,
	[txtSoftwareID] [varchar](7) NOT NULL,
	[txtSoftwareSerial] [varchar](25) NOT NULL,
	[txtSoftwareVersion] [varchar](25) NOT NULL,
	[txtSoftwareBuildMark] [varchar](25) NOT NULL,
	[txtUserLogin] [varchar](30) NOT NULL,
	[txtLanIP] [varchar](15) NOT NULL,
	[txtPCID] [varchar](10) NULL,
	[txtPCName] [varchar](30) NULL,
	[txtOSUser] [varchar](30) NULL,
	[dt_LocalInserted] [datetime] NOT NULL,
	[txtSourceFile] [text] NULL,
	[txtResultsType] [char](3) NOT NULL,
	[bool_active] [bit] NULL,
 CONSTRAINT [PK_tbl_MasterResults] PRIMARY KEY CLUSTERED 
(
	[id_MasterResult] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [tbl_MasterResults] ADD  CONSTRAINT [DF_tbl_MasterResults_txtSoftwareID]  DEFAULT ('Unknown') FOR [txtSoftwareID]
GO
ALTER TABLE [tbl_MasterResults] ADD  CONSTRAINT [DF_tbl_MasterResults_txtSoftwareSerial]  DEFAULT ('Unknown') FOR [txtSoftwareSerial]
GO
ALTER TABLE [tbl_MasterResults] ADD  CONSTRAINT [DF_tbl_MasterResults_txtSoftwareVersion]  DEFAULT ('Unknown') FOR [txtSoftwareVersion]
GO
ALTER TABLE [tbl_MasterResults] ADD  CONSTRAINT [DF_tbl_MasterResults_txtSoftwareBuildMark]  DEFAULT ('???') FOR [txtSoftwareBuildMark]
GO
ALTER TABLE [tbl_MasterResults] ADD  CONSTRAINT [DF_tbl_MasterResults_txtUserLogin]  DEFAULT ('Unknown') FOR [txtUserLogin]
GO
ALTER TABLE [tbl_MasterResults] ADD  CONSTRAINT [DF_tbl_MasterResults_txtLanIP]  DEFAULT ('???.???.???.???') FOR [txtLanIP]
GO
ALTER TABLE [tbl_MasterResults] ADD  CONSTRAINT [DF_tbl_MasterResults_dt_LocalInserted]  DEFAULT (getdate()) FOR [dt_LocalInserted]
GO
ALTER TABLE [tbl_MasterResults] ADD  CONSTRAINT [DF_tbl_MasterResults_txtResultsType]  DEFAULT ('ALL') FOR [txtResultsType]
GO
ALTER TABLE [tbl_MasterResults] ADD  DEFAULT ((1)) FOR [bool_active]
GO
ALTER TABLE [tbl_Objects] ADD  CONSTRAINT [DF_tbl_Objects_txtObjectClass]  DEFAULT ('Unknown') FOR [txtObjectClass]
GO
ALTER TABLE [tbl_Objects] ADD  CONSTRAINT [DF_tbl_Objects_txtObjectAlias]  DEFAULT ('Unknown') FOR [txtObjectAlias]
GO
ALTER TABLE [tbl_Objects] ADD  CONSTRAINT [DF_tbl_Objects_txtObjectName]  DEFAULT ('Unknown') FOR [txtObjectName]
GO
ALTER TABLE [tbl_Parameters] ADD  CONSTRAINT [DF_tbl_Parameters_txtParameterAlias]  DEFAULT ('Unknown') FOR [txtParameterAlias]
GO
ALTER TABLE [tbl_Results] ADD  CONSTRAINT [DF_tbl_Results_txtResultAlias]  DEFAULT ('Unknown') FOR [id_FieldAlias]
GO