public void CheckPolicy_ForeignKeyNotIncludeTableNameInSecondConstraint() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @"IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE TABLE_NAME = 'Customers' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ClientTypeId') BEGIN ALTER TABLE [dbo].[Customers] ADD ClientTypeId [int] ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD CONSTRAINT [FK_Customers_ClientType] FOREIGN KEY([ClientTypeId]) REFERENCES [dbo].[ClientType] ([ClientTypeId]) ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD CONSTRAINT [FK_Custome_ClientClass] FOREIGN KEY([ClientClass]) REFERENCES [dbo].[ClientType] ([ClientClass]) ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [FK_Customers_ClientType] END GO "; string message; string messageExpected = "The foreign key name '[FK_Custome_ClientClass]' does not contain the referenced table name 'Customers'."; bool expected = false; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void CheckPolicy_MissingSpaceBugFix() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[clk].[ItemOperation]') AND type in (N'U')) BEGIN CREATE TABLE [clk].[ItemOperation]( [Id] [tinyint] NOT NULL, [Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedBy] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ModifiedDate] [datetime] NULL, [ModifiedBy] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_ItemOperation] 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] END GO "; string message; string messageExpected = string.Empty; bool expected = true; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void ShortDescriptionTest() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string actual; actual = target.ShortDescription; string expected = "Constraint Naming (beta)"; Assert.AreEqual(actual, expected); }
public void PolicyIdTest() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string actual; actual = target.PolicyId; string expected = "ConstraintNamePolicy"; Assert.AreEqual(actual, expected); }
public void LongDescriptionTest() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string actual; actual = target.LongDescription; string expected = "Checks that constraints contain the name of the table they are applied to."; Assert.AreEqual(actual, expected); }
public void CheckPolicy_DefaultIncludesTableName() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @"ALTER TABLE [dbo].[AWBuildVersion] ADD CONSTRAINT [DF_AWBuildVersion_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]"; string message; string messageExpected = ""; bool expected = true; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void CheckPolicy_DefaultNoName() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @"ALTER TABLE [dbo].[SqlBuild_Logging] ADD DEFAULT ((1)) FOR [AllowScriptBlock]"; string message; string messageExpected = "No constraint name specified. Default constraint names not allowed."; bool expected = false; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void CheckPolicy_NoConstraints() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @"ALTER TABLE [dbo].[SqlBuild_Logging] ADD COLUMN MyColumn varchar(50)"; string message; string messageExpected = "No constraints found"; bool expected = true; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void CheckPolicy_CheckConstraintIncludeTableName() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @"ALTER TABLE [Person].[Contact] WITH CHECK ADD CONSTRAINT [CK_Contact_EmailPromotion] CHECK (([EmailPromotion]>=(0) AND [EmailPromotion]<=(2)))"; string message; string messageExpected = ""; bool expected = true; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void CheckPolicy_DefaultBadNameTableName() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @"ALTER TABLE [dbo].[AWBuildVersion] ADD CONSTRAINT [DF_NoTableName_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]"; string message; string messageExpected = "The default constraint name '[DF_NoTableName_ModifiedDate]' does not contain the referenced table name 'AWBuildVersion'."; bool expected = false; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void CheckPolicy_FKDoesNotIncludeTableName() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @"ALTER TABLE [HumanResources].[EmployeePayHistory] WITH CHECK ADD CONSTRAINT [FK_DummyTable_Employee_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [HumanResources].[Employee] ([EmployeeID]) "; string message; string messageExpected = "The foreign key name '[FK_DummyTable_Employee_EmployeeID]' does not contain the referenced table name 'EmployeePayHistory'."; bool expected = false; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void CheckPolicy_FKIncludesTableName() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @"ALTER TABLE [HumanResources].[EmployeePayHistory] WITH CHECK ADD CONSTRAINT [FK_EmployeePayHistory_Employee_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [HumanResources].[Employee] ([EmployeeID]) "; string message; string messageExpected = ""; bool expected = true; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void CheckPolicy_PrimaryKeyNotIncludeTableName() { ConstraintNamePolicy target = new ConstraintNamePolicy(); string script = @" ALTER TABLE [Person].[Contact] ADD CONSTRAINT [PK_BadTableName_MyPkID] PRIMARY KEY CLUSTERED ( [ContactID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] "; string message; string messageExpected = "The primary key name '[PK_BadTableName_MyPkID]' does not contain the referenced table name 'Contact'."; bool expected = false; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }