public void QualifiedNamesPolicyTest_UpdateNonQualifiedWithNoWhere()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"UPDATE CompanyInfo
SET
	[Name]=@Name
	,Address=@Address
	,City=@City
	,State=@State
	,Zip=@Zip
	,Phone=@Phone
	,Fax=@Fax
";



            string message         = string.Empty;
            string messageExpected = "Missing schema qualifier on: CompanyInfo (line: 1)";
            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 QualifiedNamesPolicyTest_TriggerDeletedTableIgnore()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"		
				
	CREATE TRIGGER Address_AuditTrig_DELETE ON [Person].[Address] FOR DELETE
AS
BEGIN
	DECLARE @TrxId uniqueidentifier
	SET @TrxId = newid()

	DECLARE @count int
	SELECT @count = count(*) FROM deleted
	
	INSERT INTO [dbo].[AuditTransactionMaster] (TransId,TableName,ModifyType,RowsAffected) VALUES (@TrxId,'Person.Address','DELETE',@count)
	INSERT INTO [Person].[Address_Audit] (TransId,AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,rowguid,ModifiedDate) SELECT @TrxId, AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,rowguid,ModifiedDate FROM deleted
END

";



            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_Cursor()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"		
				DECLARE dbname CURSOR FOR
	SELECT [name] FROM master.sys.sysdatabases WITH (NOLOCK)
	WHERE [name] LIKE ''me%'' OR [name] LIKE ''you%''
	ORDER BY [name]
	
	OPEN dbname;
	
	FETCH next
		FROM dbname         INTO @dbname

";



            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_IgnoreForeignKeyAction()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_ExportBatch_ExportFileType')
BEGIN

	ALTER TABLE tlo.ExportBatch ADD CONSTRAINT
	FK_ExportBatch_ExportFileType FOREIGN KEY
	(
	ExportFileTypeId
	) REFERENCES tlo.ExportFileType
	(
	Id
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
";



            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_UnqualifiedAtEnd()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"SELECT
		  TelePunchPromptID,
		  Type,
		  ISNULL(Message, '''') as Message,
		  AllowPrevious,
		  AllowExit,
		  SingleDigit,
		  ISNULL(Digit, '''') as Digit,
		  ParentPrompt
		  
	FROM TelePunchPrompt
";

            string message         = string.Empty;
            string messageExpected = "Missing schema qualifier on: TelePunchPrompt (line: 11)";
            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 QualifiedNamesPolicyTest_QualifiedNotBracketed()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"ALTER PROCEDURE [dbo].[proc_Accruals]

	(
		@AccrualTemplateDetailID int
		
	)


AS
DELETE FROM dbo.AccrualRules
WHERE AccrualTemplateDetailID = @AccrualTEmplateDetailID";

            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_VariableTableWithWhere()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"DECLARE @CustomerStatistics TABLE
			(
				Statistic	VARCHAR(50),
				Today		BIGINT,
				Yesterday	BIGINT
			)
		
				SELECT  Statistic,
					Today,
					Yesterday FROM
                @CustomerStatistics WHERE
                Today = getdate()
";



            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_QualifiedWithNoLock()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"SELECT
		  TelePunchPromptID,
		  Type,
		  ISNULL(Message, '''') as Message,
		  AllowPrevious,
		  AllowExit,
		  SingleDigit,
		  ISNULL(Digit, '''') as Digit,
		  ParentPrompt
		  
	FROM dbo.TelePunchPrompt WITH (NOLOCK) 
	WHERE 
		ParentPrompt Like @TelePunchPromptID
		AND Enabled = 1
	ORDER BY Type
";

            string message         = string.Empty;
            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 LongDescriptionTest()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string actual;

            actual = target.LongDescription;
            Assert.AreEqual("Checks that object references are fully qualified (<schema>.<object name>) - in beta", actual);
        }
        public void ShortDescriptionTest()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string actual;

            actual = target.ShortDescription;
            Assert.AreEqual("Qualified Names (beta)", actual);
        }
        public void PolicyIdTest()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string actual;

            actual = target.PolicyId;
            string expected = "QualifiedNamesPolicy";

            Assert.AreEqual(actual, expected);
        }
        public void QualifiedNamesPolicyTest_MissingQualifierBeforeGroupBy()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script          = Properties.Resources.MissingQualifierBeforeGroupBy;
            string message         = string.Empty;
            string messageExpected = "Missing schema qualifier on: [BOM_cte] b (line: 39)";
            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 QualifiedNamesPolicyTest_FullyQualifiedBracketed()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script          = Properties.Resources.QualifiedWithBrackets;
            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_UpdateStatementUnqualified()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"UPDATE  MyTable my
	SET       my.Column = '123' AND my.column2 = '345'
	WHERE  my.Column3 = 'hello;
";

            string message         = string.Empty;
            string messageExpected = "Missing schema qualifier on: MyTable my (line: 1)";
            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 QualifiedNamesPolicyTest_DeleteStatementQualified()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"DELETE FROM 
        dbo.MyTable mt
        WHERE
        mt.Column = 'X'
";

            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_WhereInComments4()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"
-- Since this is in a comment block we should ignore the SELECT me  FROM commentTest WHERE this equals that 
";



            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_TempTable()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"		
				SELECT  Statistic,
					Today,
					Yesterday FROM
                    #CustomerStatistics
";



            string message         = string.Empty;
            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 CheckPolicy2Test_FromInDashComment()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"		
				--This is a FROM in a comment
	SELECT [name] FROM dbo.myFunction
	WHERE [name] LIKE ''me%''

";



            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_FunctionSelect()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script = @"		
				
	SELECT [name] FROM dbo.myFunction('test')
	WHERE [name] LIKE ''me%'' OR [name] LIKE ''you%''
	ORDER BY [name]
	
	OPEN dbname;
";



            string message         = string.Empty;
            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 QualifiedNamesPolicyTest_CommonTableEntitiesDeclaration()
        {
            QualifiedNamesPolicy target = new QualifiedNamesPolicy();
            string script          = @"
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tlo].[ExportTimeCardLog_ListByPage]') AND type in (N'P', N'PC'))
BEGIN
	EXEC dbo.sp_executesql @statement = N'
		CREATE PROCEDURE [abc].[abv 122]
			@Page			int,
			@RowsPerPage	int,
			@FromDate		DateTime,
			@ToDate			DateTime
		AS
BEGIN
		/******************************************************************************
		**		Name: t[abc].[abv 122]
		**		Desc: qwerty
		**              
		**		Auth: werwer
		**		Date: 10/20/2009
		*******************************************************************************
		**		Change History
		*******************************************************************************
		**		Date:		Author:		Description:
		**		-----		--------	--------------------------------
		**		
		*******************************************************************************/
	
		DECLARE @startRowIndex int
		DECLARE @endRowIndex int
		DECLARE @nofPages int
	
		SELECT @nofPages = CEILING(count(1)/(@RowsPerPage*1.0))
		FROM abc.qwerty
		WITH(NOLOCK)
		WHERE (RequestDate >= @FromDate) AND (RequestDate <= @ToDate)
		
		IF (@Page > @nofPages)
	BEGIN
			SET @Page = @nofPages
	END

	SET @startRowIndex = ((@Page-1) * @RowsPerPage) + 1
	SET @endRowIndex = @startRowIndex+@RowsPerPage - 1

	;WITH PagingCTE 
     (
		Id,
		LogId,
		RequestDate,
		ServiceType,
		Status,
		StartDate,
		EndDate,
		ReportGuid,
		FileType,
		SequenceNumber,
		RequestingUri,
		RespondingUri,
		Message,
		RowNumber
	)
	AS 
	(
		SELECT 
			Id,
			LogId,
			RequestDate,
			ServiceType,
			Status,
			StartDate,
			EndDate,
			ReportGuid,
			FileType,
			SequenceNumber,
			RequestingUri,
			RespondingUri,
			Message,
			[RowNumber] = ROW_NUMBER() OVER(ORDER BY RequestDate Desc)
		FROM abc.qwerty
		WITH(NOLOCK)
		WHERE (RequestDate >= @FromDate) AND (RequestDate <= @ToDate)
	)

	SELECT
		Id,
		LogId,
		RequestDate,
		ServiceType,
		Status,
		StartDate,
		EndDate,
		ReportGuid,
		FileType,
		SequenceNumber,
		RequestingUri,
		RespondingUri,
		Message
	FROM PagingCTE
	WITH(NOLOCK)
	WHERE RowNumber >=  @startRowIndex  AND RowNumber <=  @endRowIndex
	ORDER By RowNumber

END
' 

END
ELSE
BEGIN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tlo].[ExportTimeCardLog_ListByPage]') AND type in (N'P', N'PC'))
BEGIN
	EXEC dbo.sp_executesql @statement = N'
		ALTER PROCEDURE [abc].[abc123]
			@Page			int,
			@RowsPerPage	int,
			@FromDate		DateTime,
			@ToDate			DateTime
		AS
BEGIN
		/******************************************************************************
		**		Name: abc.qwerty
		**		Desc: For use with TLO Integration with HRO/Preview
		**              
		**		Auth: qweree
		**		Date: 10/20/2009
		*******************************************************************************
		**		Change History
		*******************************************************************************
		**		Date:		Author:		Description:
		**		-----		--------	--------------------------------
		**		
		*******************************************************************************/
	
		DECLARE @startRowIndex int
		DECLARE @endRowIndex int
		DECLARE @nofPages int
	
		SELECT @nofPages = CEILING(count(1)/(@RowsPerPage*1.0))
		FROM abc.qwerty
		WITH(NOLOCK)
		WHERE (RequestDate >= @FromDate) AND (RequestDate <= @ToDate)
		
		IF (@Page > @nofPages)
	BEGIN
			SET @Page = @nofPages
	END

	SET @startRowIndex = ((@Page-1) * @RowsPerPage) + 1
	SET @endRowIndex = @startRowIndex+@RowsPerPage - 1

	;WITH PagingCTE 
     (
		Id,
		LogId,
		RequestDate,
		ServiceType,
		Status,
		StartDate,
		EndDate,
		ReportGuid,
		FileType,
		SequenceNumber,
		RequestingUri,
		RespondingUri,
		Message,
		RowNumber
	)
	AS 
	(
		SELECT 
			Id,
			LogId,
			RequestDate,
			ServiceType,
			Status,
			StartDate,
			EndDate,
			ReportGuid,
			FileType,
			SequenceNumber,
			RequestingUri,
			RespondingUri,
			Message,
			[RowNumber] = ROW_NUMBER() OVER(ORDER BY RequestDate Desc)
		FROM abc.qwerty
		WITH(NOLOCK)
		WHERE (RequestDate >= @FromDate) AND (RequestDate <= @ToDate)
	)

	SELECT
		Id,
		LogId,
		RequestDate,
		ServiceType,
		Status,
		StartDate,
		EndDate,
		ReportGuid,
		FileType,
		SequenceNumber,
		RequestingUri,
		RespondingUri,
		Message
	FROM PagingCTE
	WITH(NOLOCK)
	WHERE RowNumber >=  @startRowIndex  AND RowNumber <=  @endRowIndex
	ORDER By RowNumber

END
' 
	END
END
GO
";
            string message         = string.Empty;
            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);
        }