/*******************************************************************************
* Author:		Frank Bazan
* Created:		8 May 2007
* Description:	Holds replication meta data for subscriber/subscription
*
* Change History:
* ==============================================================================
* Author      Version      Change Date      Description
* ------------------------------------------------------------------------------
* F.Bazan		1.0         8 May 2007      Initial Create Script
* ==============================================================================
*/

IF  EXISTS ( SELECT 1 
			 FROM SYS.FOREIGN_KEYS 
			 WHERE [OBJECT_ID] = OBJECT_ID(N'[dbo].[FK_Subscription_Publication]') 
			 AND [PARENT_OBJECT_ID] = OBJECT_ID(N'[dbo].[Subscription]') )
BEGIN
	ALTER TABLE [dbo].[Subscription] DROP CONSTRAINT [FK_Subscription_Publication]
END

IF EXISTS (	SELECT 1 
			FROM SYS.OBJECTS 
			WHERE [OBJECT_ID] = OBJECT_ID('dbo.Subscription')
			AND [TYPE] IN (N'U'))
BEGIN
	DROP TABLE [dbo].[Subscription]
END

CREATE TABLE [dbo].[Subscription](
	[SubscriptionId] INT IDENTITY NOT NULL,
	[PublicationId] INT NOT NULL,
	[SubscriptionDatabase] NVARCHAR (128) NOT NULL,
	[SubscriberServer] NVARCHAR(128) NOT NULL,
	[SubscriberSecurityMode] BIT NOT NULL,
	[SubscriberLogin] NVARCHAR(128) NULL,
	[SubscriberPassword] NVARCHAR(128) NULL,
	[DistributionAgentLogin] NVARCHAR(128) NULL,
	[DistributionAgentPwd] NVARCHAR(128) NULL,
   [IsDistributionOnDemand] BIT NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[Subscription] 
ADD CONSTRAINT PK_Subscription PRIMARY KEY (SubscriptionId)


ALTER TABLE [dbo].[Subscription] 
ADD CONSTRAINT [FK_Subscription_Publication] FOREIGN KEY([PublicationId])
REFERENCES [dbo].[Publication] ([PublicationId])