Beispiel #1
0
        public void ProcessNoLockOptimizationTest_WithCursor()
        {
            #region rawscript ...
            string rawScript = @"DECLARE @TableName varchar(256)
SET @TableName = '<<Table Name>>'
SET @Schema = '<<Table Schema>>'
DECLARE @FkName varchar(500)
DECLARE fkCursor CURSOR FOR 
	SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  
		WHERE Table_name = @TableName AND
			  CONSTRAINT_TYPE = 'FOREIGN KEY'

OPEN fkCursor
FETCH NEXT FROM fkCursor INTO @FkName 
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT 'Droping Constraint '+@FkName +' off Table '+@Schema+'.'+@TableName
		EXEC ('ALTER TABLE '+@Schema+'.'+@TableName+' DROP CONSTRAINT '+@FkName)
		FETCH NEXT FROM fkCursor INTO @FkName 
	END
CLOSE fkCursor
DEALLOCATE fkCursor 
GO
";
            #endregion
            string expected = rawScript;
            string actual;
            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #2
0
        public void ProcessNoLockOptimizationTest_FromWithSameLineWhere()
        {
            string rawScript = "SELECT * FROM TestTable WHERE Column1='test'";
            string expected  = "SELECT * FROM TestTable WITH (NOLOCK) WHERE Column1='test'";
            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #3
0
        public void ProcessNoLockOptimizationTest_BugFix1()
        {
            string rawScript = Properties.Resources.NoLockBugFix1_Input;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(Properties.Resources.NoLockBugFix1_Expected, actual);
        }
Beispiel #4
0
        public void ProcessNoLockOptimizationTest_FromWithNoFollow()
        {
            string rawScript = "SELECT * FROM TestTable";
            string expected  = "SELECT * FROM TestTable WITH (NOLOCK) ";
            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #5
0
        public void ProcessNoLockOptimizationTest_WithDeleteFrom()
        {
            string rawScript = @"DELETE FROM MyTable mt WHERE mt.Address='1st Street'";


            string expected = rawScript;
            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #6
0
        public void ProcessNoLockOptimizationTest_WithSysForeignKeyCols()
        {
            #region rawscript ...
            string rawScript = @"IF NOT EXISTS (SELECT * FROM 
                    sys.foreign_key_columns WHERE object_id = OBJECT_ID(N'[Person].[uAddressType]'))
";
            #endregion
            string expected = rawScript;
            string actual;
            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #7
0
        public void ProcessNoLockOptimizationTest_FromWithLineBreak()
        {
            string rawScript = @"SELECT * FROM TestTable
WHERE Test = 4
";
            string expected  = @"SELECT * FROM TestTable WITH (NOLOCK) 
WHERE Test = 4
";
            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #8
0
        public void ProcessNoLockOptimizationTest_WithUpdate()
        {
            string rawScript = @"UPDATE MyTable mt 
SET mt.Address='1st Street' AND
mt.LastName = 'Jones
WHERE mt.FirstName LIKE ('%Smith%')";


            string expected = rawScript;
            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #9
0
        public void ProcessNoLockOptimizationTest_WithInsert()
        {
            string rawScript = @"INSERT INTO IPTableA
	            (IPTableAName, AllowX, AllowY)
	VALUES  (@IPTableAName,@AllowX,@AllowY)"    ;


            string expected = rawScript;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #10
0
        public void ProcessNoLockOptimizationTest_WithSysIndexesObject()
        {
            #region rawscript ...
            string rawScript = @"IF NOT  EXISTS (SELECT * FROM sysindexes WHERE name = 'myIndex'  AND OBJECT_NAME(id) = N'myTable')
BEGIN
--	 some script here
END
GO
";
            #endregion
            string expected = rawScript;
            string actual;
            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #11
0
        public void ProcessNoLockOptimizationTest_WithDeleteFromWithSubQuery()
        {
            string rawScript = @"DELETE FROM MyTable mt 
WHERE mt.Address='1st Street' AND
mt.LastName IN (SELECT LastName FROM Names WHERE FirstName = 'Mike'";


            string expected = @"DELETE FROM MyTable mt 
WHERE mt.Address='1st Street' AND
mt.LastName IN (SELECT LastName FROM Names WITH (NOLOCK) WHERE FirstName = 'Mike'";;
            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #12
0
        public void ProcessNoLockOptimizationTest_WithInfoSchemaIgnoreObject()
        {
            #region rawscript ...
            string rawScript = @"IF NOT EXISTS(SELECT * FROM information_schema.columns WHERE TABLE_NAME = 'TestTable' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'TestColumn')
BEGIN
	ALTER TABLE [dbo].[TestTable] ADD TestColumn BIT NOT NULL
END
GO
";
            #endregion
            string expected = rawScript;
            string actual;
            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #13
0
        public bool CheckPolicy(string script, List <Match> commentBlockMatches, out string message)
        {
            try
            {
                List <List <string> > tablesMissingNoLock;
                string start = script;
                string end   = ScriptOptimization.ProcessNoLockOptimization(script, commentBlockMatches, out tablesMissingNoLock);

                if (ScriptOptimization.regNoLock.Matches(start).Count == ScriptOptimization.regNoLock.Matches(end).Count)
                {
                    message = string.Empty;
                    return(true);
                }
                else
                {
                    bool isOK = true;

                    foreach (List <string> t in tablesMissingNoLock)
                    {
                        bool hasException = HasNoLockExceptionTag(script, t);
                        if (!hasException)
                        {
                            isOK = false;
                        }
                    }

                    if (isOK)
                    {
                        message = FoundExceptionMessage;
                        return(true);
                    }
                    else
                    {
                        message = MissingNoLockMessage;
                        return(false);
                    }
                }
            }


            catch (Exception exe)
            {
                message = "Error processing script policy. See application log file for details";;
                log.LogError(exe, message);
                return(false);
            }
        }
Beispiel #14
0
        public void ProcessNoLockOptimizationTest_TableVariableSelect()
        {
            string rawScript = @"		
				SELECT  Statistic,
					Today,
					Yesterday FROM
                    @CustomerStatistics
";


            string expected = rawScript;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #15
0
        public void ProcessNoLockOptimizationTest_WithSysObjectsIgnoreObject()
        {
            #region rawscript ...
            string rawScript = @"IF NOT EXISTS (SELECT * FROMFROM sys.objects WITH  WHERE object_id = OBJECT_ID(N'[ats].[RuleTemplates_ReadByClockID]') AND type in (N'P', N'PC'))
BEGIN
	EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [ats].[RuleTemplates_ReadByClockID] 
	(
		@Clock_Id INT
	) 
	AS
BEGIN
";
            #endregion
            string expected = rawScript;
            string actual;
            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #16
0
        public void ProcessNoLockOptimizationTest_FunctionNoParams()
        {
            string script = @"
				SELECT  Statistic,
					Today,
					Yesterday FROM
                dbo.func_GetMyDate() f INNER JOIN dbo.myTable t WITH (NOLOCK) ON t.myCol = f.Yesterday WHERE
                Today = getdate()
";


            string expected = script;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(script);
            Assert.AreEqual(expected, actual);
        }
Beispiel #17
0
        public void ProcessNoLockOptimizationTest_FunctionSelects()
        {
            string script = @"
				SELECT  Statistic,
					Today,
					Yesterday FROM
                dbo.func_GetMyDate('today') WHERE
                Today = getdate()
";


            string expected = script;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(script);
            Assert.AreEqual(expected, actual);
        }
Beispiel #18
0
        public void ProcessNoLockOptimizationTest_WithUpdateAndSubQuery()
        {
            string rawScript = @"UPDATE MyTable mt 
SET mt.Address='1st Street' AND
mt.LastName = 'Jones
WHERE mt.FirstName IN (SELECT FName FROM People p WHERE p.MiddleName = 'Harry')";


            string expected = @"UPDATE MyTable mt 
SET mt.Address='1st Street' AND
mt.LastName = 'Jones
WHERE mt.FirstName IN (SELECT FName FROM People p WITH (NOLOCK) WHERE p.MiddleName = 'Harry')";

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #19
0
        public void ProcessNoLockOptimizationTest_FromInDashComment()
        {
            string script = @"
				SELECT  Statistic,
					Today,
                    -- just adding a FROM in an dash comment
					Yesterday FROM
                dbo.func_GetMyDate() f INNER JOIN dbo.myTable t WITH (NOLOCK) ON t.myCol = f.Yesterday WHERE
                Today = getdate()
";


            string expected = script;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(script);
            Assert.AreEqual(expected, actual);
        }
Beispiel #20
0
        public void ProcessNoLockOptimizationTest_TempTableSelectWithWhere()
        {
            string script = @"		
				SELECT  Statistic,
					Today,
					Yesterday FROM
                    #CustomerStatistics WHERE
                Today = getdate()
";



            string expected = script;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(script);
            Assert.AreEqual(expected, actual);
        }
Beispiel #21
0
        public void ProcessNoLockOptimizationTest_SelectFromTriggerInsertedIgnore()
        {
            string script   = @"BEGIN
	DECLARE @TrxId uniqueidentifier
	SET @TrxId = newid()
	
	DECLARE @count int
	SELECT @count = count(*) FROM inserted
	
	INSERT INTO [dbo].[AuditTransactionMaster] (TransId,TableName,ModifyType,RowsAffected) VALUES (@TrxId,'Person.Address','UPDATE',@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 inserted
END
";
            string expected = script;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(script);
            Assert.AreEqual(expected, actual);
        }
Beispiel #22
0
        public void ProcessNoLockOptimizationTest_MultipleJoins()
        {
            string rawScript = @"SELECT 
              p.DepartmentPageSize
              , p.ColorBackgroundTable
              , p.AdminBenefitRequestState
              , p.ScheduleTemplatesShowPublic
              
       FROM
               MyTablePrefs p 
               Inner Join Themes t on p.ThemeId = t.ThemeId
               RIGHT JOIN table2 t2 ON t2.Theme = p.Theme
               OUTER JOIN table3 t3 
                    ON t3.theme = t2.theme
       WHERE
              p.CustomerID = @CustomerID
              AND p.UserID = @UserID
       ORDER BY p.MyTablePreferenceID DESC";


            string expected = @"SELECT 
              p.DepartmentPageSize
              , p.ColorBackgroundTable
              , p.AdminBenefitRequestState
              , p.ScheduleTemplatesShowPublic
              
       FROM
               MyTablePrefs p WITH (NOLOCK)  
               Inner Join Themes t WITH (NOLOCK) on p.ThemeId = t.ThemeId
               RIGHT JOIN table2 t2 WITH (NOLOCK) ON t2.Theme = p.Theme
               OUTER JOIN table3 t3 WITH (NOLOCK)  
                    ON t3.theme = t2.theme
       WHERE
              p.CustomerID = @CustomerID
              AND p.UserID = @UserID
       ORDER BY p.MyTablePreferenceID DESC";
            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #23
0
        public void ProcessNoLockOptimizationTest_BugFix2()
        {
            string rawScript = @"
        BEGIN
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;
	
		SELECT TOP 1
	        GlobalConfigID,
	        UseLDAP,
	        LdapPath,
	        LdapDomain,
	        IsLicensed
	    FROM
	        dbo.GlobalConfig
END";


            string expected = @"
        BEGIN
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;
	
		SELECT TOP 1
	        GlobalConfigID,
	        UseLDAP,
	        LdapPath,
	        LdapDomain,
	        IsLicensed
	    FROM
	        dbo.GlobalConfig WITH (NOLOCK) 
END";

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #24
0
        public void ProcessNoLockOptimizationTest_FromInStarCommentAtEnd()
        {
            string script = @"
				SELECT  Statistic,
					Today,
                    
					Yesterday FROM
                dbo.func_GetMyDate() f INNER JOIN dbo.myTable t WITH (NOLOCK) ON t.myCol = f.Yesterday WHERE
                Today = getdate()
            /**************
            * This is a FROM comment
            ***************/
";


            string expected = script;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(script);
            Assert.AreEqual(expected, actual);
        }
Beispiel #25
0
        public void ProcessNoLockOptimizationTest_WithSysTriggersObject()
        {
            #region rawscript ...
            string rawScript = @"IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[Person].[uAddressType]'))
EXEC dbo.sp_executesql @statement = N'
CREATE TRIGGER [Person].[uAddressType] ON [Person].[AddressType] 
AFTER UPDATE NOT FOR REPLICATION AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE [Person].[AddressType]
    SET [Person].[AddressType].[ModifiedDate] = GETDATE()
    FROM inserted WITH (NOLOCK)
    WHERE inserted.[AddressTypeID] = [Person].[AddressType].[AddressTypeID];
END;
";
            #endregion
            string expected = rawScript;
            string actual;
            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #26
0
        public void ProcessNoLockOptimizationTest_WithInsertAndSubQuery()
        {
            string rawScript = @"INSERT INTO IPTableA
	            (IPTableAName, AllowX, AllowY)
	VALUES  (
        (SELECT TOP 1 Name FROM MyTable WHERE Name LIKE 't%'), (SELECT Top 1 Allow FROM TableX), (SELECT Top 1 Allow FROM TableY)

)";


            string expected = @"INSERT INTO IPTableA
	            (IPTableAName, AllowX, AllowY)
	VALUES  (
        (SELECT TOP 1 Name FROM MyTable WITH (NOLOCK) WHERE Name LIKE 't%'), (SELECT Top 1 Allow FROM TableX WITH (NOLOCK) ), (SELECT Top 1 Allow FROM TableY WITH (NOLOCK) )

)";;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }
Beispiel #27
0
        public void ProcessNoLockOptimizationTest_TableVariableSelectWithWhere()
        {
            string script = @"DECLARE @CustomerStatistics TABLE
			(
				Statistic	VARCHAR(50),
				Today		BIGINT,
				Yesterday	BIGINT
			)
		
				SELECT  Statistic,
					Today,
					Yesterday FROM
                @CustomerStatistics WHERE
                Today = getdate()
";


            string expected = script;

            string actual;

            actual = ScriptOptimization.ProcessNoLockOptimization(script);
            Assert.AreEqual(expected, actual);
        }
Beispiel #28
0
        public void ProcessNoLockOptimizationTest_WithTwoSysObjsObjectNissingNoLock()
        {
            #region rawscript ...
            string rawScript = @"SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ats].[RuleTemplates_ReadByClockID]') AND type in (N'P', N'PC'))
BEGIN
	EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [ats].[RuleTemplates_ReadByClockID] 
		(
			SELECT * FROM BadTable
		) 
		AS

        SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ats].[RuleTemplates_ReadByClockID]') AND type in (N'P', N'PC'
BEGIN

";
            #endregion
            string expected = @"SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ats].[RuleTemplates_ReadByClockID]') AND type in (N'P', N'PC'))
BEGIN
	EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [ats].[RuleTemplates_ReadByClockID] 
		(
			SELECT * FROM BadTable WITH (NOLOCK) 
		) 
		AS

        SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ats].[RuleTemplates_ReadByClockID]') AND type in (N'P', N'PC'
BEGIN

";
            string actual;
            actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
            Assert.AreEqual(expected, actual);
        }