USE [VEPROMS]
GO
/****** Object:  Table [dbo].[RODbs]    Script Date: 01/07/2009 09:44:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RODbs](
	[RODbID] [int] IDENTITY(1,1) NOT NULL,
	[ROName] [nvarchar](255) NOT NULL,
	[FolderPath] [nvarchar](255) NOT NULL,
	[DBConnectionString] [nvarchar](max) NOT NULL,
	[Config] [nvarchar](max) NULL,
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_RODbs_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_RODbs_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_RODbs] PRIMARY KEY CLUSTERED 
(
	[RODbID] 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
CREATE UNIQUE NONCLUSTERED INDEX [IX_RODbs_FolderPath] ON [dbo].[RODbs] 
(
	[FolderPath] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Hook for future - to allow the user to select multiple RO Databases assocaiated with on DocVersion' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RODbs', @level2type=N'COLUMN',@level2name=N'ROName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Path to the RO database' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RODbs', @level2type=N'COLUMN',@level2name=N'FolderPath'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Connection String - Default could just be the full path and name of the database' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RODbs', @level2type=N'COLUMN',@level2name=N'DBConnectionString'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RODbs', @level2type=N'COLUMN',@level2name=N'DTS'
GO
/****** Object:  Table [dbo].[Associations]    Script Date: 01/07/2009 09:44:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Associations](
	[AssociationID] [int] IDENTITY(1,1) NOT NULL,
	[VersionID] [int] NOT NULL,
	[ROFstID] [int] NOT NULL,
	[Config] [nvarchar](max) NULL,
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_Associations_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_Associations_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Associations_1] PRIMARY KEY CLUSTERED 
(
	[AssociationID] 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
CREATE UNIQUE NONCLUSTERED INDEX [IX_Associations_VersionID_ROFstID] ON [dbo].[Associations] 
(
	[VersionID] ASC,
	[ROFstID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Associations', @level2type=N'COLUMN',@level2name=N'DTS'
GO
/****** Object:  Table [dbo].[Figures]    Script Date: 01/07/2009 09:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Figures](
	[FigureID] [int] IDENTITY(1,1) NOT NULL,
	[ROFstID] [int] NOT NULL,
	[ImageID] [int] NOT NULL,
	[Config] [nvarchar](max) NULL,
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_Figures_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_Figures_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Figures_1] PRIMARY KEY CLUSTERED 
(
	[FigureID] 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
CREATE UNIQUE NONCLUSTERED INDEX [IX_Figures_ROFstID_ImageID] ON [dbo].[Figures] 
(
	[ROFstID] ASC,
	[ImageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Figures', @level2type=N'COLUMN',@level2name=N'DTS'
GO
/****** Object:  Table [dbo].[ROImages]    Script Date: 01/07/2009 09:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ROImages](
	[ImageID] [int] IDENTITY(1,1) NOT NULL,
	[RODbID] [int] NOT NULL,
	[FileName] [nvarchar](255) NOT NULL,
	[Content] [varbinary](max) NOT NULL,
	[Config] [nvarchar](max) NULL,
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_ROImages_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_ROImages_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_ROImages] PRIMARY KEY CLUSTERED 
(
	[ImageID] 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
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ROImages', @level2type=N'COLUMN',@level2name=N'DTS'
GO
/****** Object:  Table [dbo].[ROFsts]    Script Date: 01/07/2009 09:44:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ROFsts](
	[ROFstID] [int] IDENTITY(1,1) NOT NULL,
	[RODbID] [int] NOT NULL,
	[ROFST] [varbinary](max) NOT NULL,
	[Config] [nvarchar](max) NULL,
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_Table_1_DTS1]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_ROFsts_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_ROFsts] PRIMARY KEY CLUSTERED 
(
	[ROFstID] 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
CREATE UNIQUE NONCLUSTERED INDEX [IX_ROFsts] ON [dbo].[ROFsts] 
(
	[RODbID] ASC,
	[DTS] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ROFsts', @level2type=N'COLUMN',@level2name=N'DTS'
GO
/****** Object:  ForeignKey [FK_Associations_DocVersions]    Script Date: 01/07/2009 09:44:27 ******/
ALTER TABLE [dbo].[Associations]  WITH CHECK ADD  CONSTRAINT [FK_Associations_DocVersions] FOREIGN KEY([VersionID])
REFERENCES [dbo].[DocVersions] ([VersionID])
GO
ALTER TABLE [dbo].[Associations] CHECK CONSTRAINT [FK_Associations_DocVersions]
GO
/****** Object:  ForeignKey [FK_Associations_ROFsts]    Script Date: 01/07/2009 09:44:27 ******/
ALTER TABLE [dbo].[Associations]  WITH CHECK ADD  CONSTRAINT [FK_Associations_ROFsts] FOREIGN KEY([ROFstID])
REFERENCES [dbo].[ROFsts] ([ROFstID])
GO
ALTER TABLE [dbo].[Associations] CHECK CONSTRAINT [FK_Associations_ROFsts]
GO
/****** Object:  ForeignKey [FK_Figures_ROFsts]    Script Date: 01/07/2009 09:44:29 ******/
ALTER TABLE [dbo].[Figures]  WITH CHECK ADD  CONSTRAINT [FK_Figures_ROFsts] FOREIGN KEY([ROFstID])
REFERENCES [dbo].[ROFsts] ([ROFstID])
GO
ALTER TABLE [dbo].[Figures] CHECK CONSTRAINT [FK_Figures_ROFsts]
GO
/****** Object:  ForeignKey [FK_Figures_ROImages]    Script Date: 01/07/2009 09:44:29 ******/
ALTER TABLE [dbo].[Figures]  WITH CHECK ADD  CONSTRAINT [FK_Figures_ROImages] FOREIGN KEY([ImageID])
REFERENCES [dbo].[ROImages] ([ImageID])
GO
ALTER TABLE [dbo].[Figures] CHECK CONSTRAINT [FK_Figures_ROImages]
GO
/****** Object:  ForeignKey [FK_ROFsts_RODbs]    Script Date: 01/07/2009 09:44:34 ******/
ALTER TABLE [dbo].[ROFsts]  WITH CHECK ADD  CONSTRAINT [FK_ROFsts_RODbs] FOREIGN KEY([RODbID])
REFERENCES [dbo].[RODbs] ([RODbID])
GO
ALTER TABLE [dbo].[ROFsts] CHECK CONSTRAINT [FK_ROFsts_RODbs]
GO
/****** Object:  ForeignKey [FK_Figures_RODbs]    Script Date: 01/07/2009 09:44:37 ******/
ALTER TABLE [dbo].[ROImages]  WITH CHECK ADD  CONSTRAINT [FK_Figures_RODbs] FOREIGN KEY([RODbID])
REFERENCES [dbo].[RODbs] ([RODbID])
GO
ALTER TABLE [dbo].[ROImages] CHECK CONSTRAINT [FK_Figures_RODbs]
GO
TRUNCATE TABLE ROUsages
GO
ALTER TABLE [dbo].[ROUsages]  WITH CHECK ADD  
CONSTRAINT [FK_ROUsages_RODbs] FOREIGN KEY([RODbID])
REFERENCES [dbo].[RODbs] ([RODbID])
GO
ALTER TABLE [dbo].[ROUsages] CHECK CONSTRAINT [FK_ROUsages_RODbs]
GO