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);
}
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);
}
public void ProcessNoLockOptimizationTest_BugFix1()
{
string rawScript = Properties.Resources.NoLockBugFix1_Input;
string actual;
actual = ScriptOptimization.ProcessNoLockOptimization(rawScript);
Assert.AreEqual(Properties.Resources.NoLockBugFix1_Expected, actual);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
