public void TestPlSqlBlock()
{
CollectionAssert.AreEqual(
new[] { PlSqlBlock.Replace("\r", "").Trim() },
ScriptSplitter.Process(PlSqlBlock).ToArray()
);
}
public void TestCreateDropPlSqlBlock()
{
CollectionAssert.AreEqual(
new[] { "create table t (number a, varchar2(10) )", "drop table t", PlSqlBlock.Replace("\r", "").Trim() },
ScriptSplitter.Process(CreateDrop + "\n" + PlSqlBlock).ToArray()
);
}
public void TestSimpleStatement()
{
CollectionAssert.AreEqual(
new[] { SimpleStatement },
ScriptSplitter.Process(SimpleStatement).ToArray()
);
}
public void TestCreateDrop()
{
CollectionAssert.AreEqual(
new[] { "create table t (number a, varchar2(10) )", "drop table t" },
ScriptSplitter.Process(CreateDrop).ToArray()
);
}
public void TestPilsql_MultipleStatement_ManyDelim()
{
CollectionAssert.AreEqual(
new[] { NullPilsqlblock, NullPilsqlblock },
ScriptSplitter.Process(NullPilsqlblock + "\n/\n" + "\n/\n" + "\n;\n" + "\n/\n" + NullPilsqlblock).ToArray()
);
}
public void TestPilsql_SingleStatement()
{
CollectionAssert.AreEqual(
new[] { NullPilsqlblock },
ScriptSplitter.Process(NullPilsqlblock).ToArray()
);
}
public void SemiColonDoesNotSplitScript()
{
const string script = "CREATE PROC Blah AS SELECT FOO; SELECT Bar;";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(1, scripts.Count(), "Expected no scripts since they would be empty.");
}
public void Split_GoMustBeAloneOnLine()
{
const string script = @"ABC GO DEF";
var parts = ScriptSplitter.Split(script);
CollectionAssert.AreEqual(new[] { "ABC GO DEF" }, parts);
}
public void CanParseSuccessiveGoStatements()
{
const string script = @"GO
GO";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(0, scripts.Count(), "Expected no scripts since they would be empty.");
}
public void CanParseSimpleScriptEndingInNewLine()
{
string script = "Test" + Environment.NewLine + "GO" + Environment.NewLine;
List <string> scripts = new ScriptSplitter(script).ToList();
Assert.AreEqual(1, scripts.Count());
Assert.AreEqual("Test", scripts.First());
}
public void CanParseCommentBeforeGoStatement()
{
const string script = @"SELECT FOO
/*TEST*/ GO
BAR";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public void Split_NoGO()
{
const string script = @"ABC
DEF";
var parts = ScriptSplitter.Split(script);
CollectionAssert.AreEqual(new [] { script }, parts);
}
public void CanParseQuotedCorrectly()
{
const string script = @"INSERT INTO #Indexes
EXEC sp_helpindex 'dbo.subtext_URLs'" ;
var scripts = new ScriptSplitter(script);
Assert.AreEqual(script, scripts.First(), "Script text should not be modified");
}
public void CanParseCommentBeforeGoStatement()
{
const string script = @"SELECT FOO
/*TEST*/ GO
BAR";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public void Split_CaseInsensitiveGO()
{
const string script = @"ABC
gO
DEF";
var parts = ScriptSplitter.Split(script);
CollectionAssert.AreEqual(new[] { "ABC", "DEF" }, parts);
}
public void Split_LastPartIsEmpty()
{
const string script = @"ABC
GO
";
var parts = ScriptSplitter.Split(script);
CollectionAssert.AreEqual(new[] { "ABC" }, parts);
}
public void Split_GoCanBeSurroundedByWhitespace()
{
const string script = @"ABC
GO
DEF";
var parts = ScriptSplitter.Split(script);
CollectionAssert.AreEqual(new[] { "ABC", "DEF" }, parts);
}
public void Split_TwoParts()
{
const string script = @"ABC
GO
DEF";
var parts = ScriptSplitter.Split(script);
CollectionAssert.AreEqual(new[] { "ABC", "DEF" }, parts);
}
public void CanParseCommentWithQuoteChar()
{
const string script =
@"/* Add the Url column to the subtext_Log table if it doesn't exist */
ADD [Url] VARCHAR(255) NULL
GO
AND COLUMN_NAME = 'BlogGroup') IS NULL";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public static ScriptCollection ParseScripts(string fullScriptText)
{
ScriptCollection scripts = new ScriptCollection(fullScriptText);
ScriptSplitter splitter = new ScriptSplitter(fullScriptText);
foreach (string str in splitter)
{
scripts.Add(new Script(str));
}
return scripts;
}
public static System.Collections.Generic.List <string> SplitScript(string strSQL)
{
System.Collections.Generic.List <string> ls =
new System.Collections.Generic.List <string>();
System.Collections.Generic.IEnumerable <string> scp = new ScriptSplitter(strSQL);
ls.AddRange(scp);
return(ls);
}
public void CanParseDashDashCommentWithQuoteChar()
{
const string script =
@"-- Add the Url column to the subtext_Log table if it doesn't exist
SELECT * FROM BLAH
GO
PRINT 'FOO'";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public void CanParseLineEndingInDashDashComment()
{
const string script = @"SELECT * FROM BLAH -- Comment
GO
FOOBAR
GO";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public void CanParseGoWithDashDashCommentAfter()
{
const string script =
@"SELECT * FROM foo;
GO -- Hello Phil
CREATE PROCEDURE dbo.Test AS SELECT * FROM foo";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public void Split_MultilineParts()
{
const string script = @"ABC
DEF
GO
GHI
JKL";
var parts = ScriptSplitter.Split(script);
CollectionAssert.AreEqual(new[] { "ABC\r\nDEF", "GHI\r\nJKL" }, parts);
}
public void CanParseCommentWithQuoteChar()
{
const string script =
@"/* Add the Url column to the subtext_Log table if it doesn't exist */
ADD [Url] VARCHAR(255) NULL
GO
AND COLUMN_NAME = 'BlogGroup') IS NULL" ;
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public void ScriptSplitterCanEnumerate()
{
var splitter = new ScriptSplitter("This is a test");
IEnumerable<string> enumerable = splitter;
int i = 0;
foreach(string s in enumerable)
{
i++;
}
Assert.AreEqual(1, i);
}
public void CanParseDashDashCommentWithQuoteChar()
{
const string script =
@"-- Add the Url column to the subtext_Log table if it doesn't exist
SELECT * FROM BLAH
GO
PRINT 'FOO'";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public void ScriptSplitterCanEnumerate5()
{
var splitter = new ScriptSplitter("/*This is a test\nGO*/\nLast Line.");
IEnumerable <string> enumerable = splitter;
int i = 0;
foreach (string s in enumerable)
{
i++;
}
Assert.AreEqual(1, i);
}
public void ScriptSplitterCanEnumerate6()
{
var splitter = new ScriptSplitter("declare @x='Hello.\nGO'\nselect @x");
IEnumerable <string> enumerable = splitter;
int i = 0;
foreach (string s in enumerable)
{
i++;
}
Assert.AreEqual(1, i);
}
public void CanParseNestedComments()
{
const string script = @"/*
select 1
/* nested comment */
go
delete from users
-- */";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(1, scripts.Count(), "This contains a comment and no scripts.");
}
public void MultiLineQuoteShouldNotBeSplitByGoKeyword()
{
string script = "PRINT '" + Environment.NewLine
+ "GO" + Environment.NewLine
+ "SELECT * FROM BLAH" + Environment.NewLine
+ "GO" + Environment.NewLine
+ "'";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(script, scripts.First());
Assert.AreEqual(1, scripts.Count(), "expected only one script");
}
public void MultiLineQuoteShouldNotIgnoreDoubleQuote()
{
string script = "PRINT '" + Environment.NewLine
+ "''" + Environment.NewLine
+ "GO" + Environment.NewLine
+ "/*" + Environment.NewLine
+ "GO"
+ "'";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(1, scripts.Count());
Assert.AreEqual(script, scripts.First());
}
public void ExecuteDDL(string commandText)
{
var splitter = new ScriptSplitter(commandText);
IEnumerable <string> enumerable = splitter;
int i = 0;
foreach (string sql in enumerable)
{
i++;
//set a timeout of 60 seconds * 60 minutes = 3600 seconds
//todo: make a global and/or patch specific setting in config
_con.Execute(sql, commandTimeout: 3600);
}
}
public void ScriptSplitterCanEnumerate()
{
var splitter = new ScriptSplitter("This is a test");
IEnumerable <string> enumerable = splitter;
int i = 0;
#pragma warning disable 168
foreach (var s in enumerable)
#pragma warning restore 168
{
i++;
}
Assert.AreEqual(1, i);
}
/// <summary>
/// Executes a SQL script. Includes support for executing
/// scripts in batches using the GO keyword.
/// </summary>
public void ExecuteScript(DbTransaction tran, string script)
{
const string providerVariableName = "##DNM:PROVIDER##";
var batches = new ScriptSplitter(script);
foreach (var batch in batches)
{
// replace the provider name token in the script
var bakedBatch = batch.Replace(providerVariableName, _provider, StringComparison.OrdinalIgnoreCase);
using (var cmd = CreateCommand())
{
cmd.CommandText = bakedBatch;
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
}
public SqlScriptReader(ScriptSplitter splitter) : base(splitter)
{
}
public SeparatorLineReader(ScriptSplitter splitter) : base(splitter)
{
this.builder = new StringBuilder();
}
public SeparatorLineReader(ScriptSplitter splitter) : base(splitter)
{
}
public void CanParseLineEndingInDashDashComment()
{
const string script = @"SELECT * FROM BLAH -- Comment
GO
FOOBAR
GO";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public ScriptReader(ScriptSplitter splitter)
{
this.splitter = splitter;
}
protected ScriptReader(ScriptSplitter splitter)
{
this.Splitter = splitter;
}
public void CanParseGoWithDashDashCommentAfter()
{
const string script =
@"SELECT * FROM foo;
GO -- Hello Phil
CREATE PROCEDURE dbo.Test AS SELECT * FROM foo";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(2, scripts.Count());
}
public void CanParseQuotedCorrectly()
{
const string script = @"INSERT INTO #Indexes
EXEC sp_helpindex 'dbo.subtext_URLs'";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(script, scripts.First(), "Script text should not be modified");
}
public void CanParseNestedComments()
{
const string script = @"/*
select 1
/* nested comment */
go
delete from users
-- */";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(1, scripts.Count(), "This contains a comment and no scripts.");
}
public void ScriptSplitterCanEnumerate()
{
var splitter = new ScriptSplitter("This is a test");
IEnumerable<string> enumerable = splitter;
int i = 0;
#pragma warning disable 168
foreach (var s in enumerable)
#pragma warning restore 168
{
i++;
}
Assert.AreEqual(1, i);
}
public void SemiColonDoesNotSplitScript()
{
const string script = "CREATE PROC Blah AS SELECT FOO; SELECT Bar;";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(1, scripts.Count(), "Expected no scripts since they would be empty.");
}
public void ParseScriptParsesCorrectly()
{
const string script =
@"SET QUOTED_IDENTIFIER OFF
-- Comment
Go
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
CREATE TABLE [<username,varchar,dbo>].[blog_Gost] (
[HostUserName] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Salt] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
gO
";
List<string> scripts = new ScriptSplitter(script).ToList();
Assert.AreEqual(3, scripts.Count(), "This should parse to three scripts.");
for (int i = 0; i < scripts.Count(); i++)
{
string sqlScript = scripts[i];
Assert.IsFalse(sqlScript.StartsWith("GO"), "Script '" + i + "' failed had a GO statement");
}
string expectedThirdScriptBeginning = @"SET ANSI_NULLS ON
CREATE TABLE [<username,varchar,dbo>].[blog_Gost]";
Assert.AreEqual(expectedThirdScriptBeginning,
scripts[2].Substring(0, expectedThirdScriptBeginning.Length),
"Script not parsed correctly");
}
public void MultiLineQuoteShouldNotIgnoreDoubleQuote()
{
string script = "PRINT '" + Environment.NewLine
+ "''" + Environment.NewLine
+ "GO" + Environment.NewLine
+ "/*" + Environment.NewLine
+ "GO"
+ "'";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(1, scripts.Count());
Assert.AreEqual(script, scripts.First());
}
public void MultiLineQuoteShouldNotBeSplitByGoKeyword()
{
string script = "PRINT '" + Environment.NewLine
+ "GO" + Environment.NewLine
+ "SELECT * FROM BLAH" + Environment.NewLine
+ "GO" + Environment.NewLine
+ "'";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(script, scripts.First());
Assert.AreEqual(1, scripts.Count(), "expected only one script");
}
public void CanParseSuccessiveGoStatements()
{
const string script = @"GO
GO";
var scripts = new ScriptSplitter(script);
Assert.AreEqual(0, scripts.Count(), "Expected no scripts since they would be empty.");
}
public void CanParseSimpleScriptEndingInNewLine()
{
string script = "Test" + Environment.NewLine + "GO" + Environment.NewLine;
List<string> scripts = new ScriptSplitter(script).ToList();
Assert.AreEqual(1, scripts.Count());
Assert.AreEqual("Test", scripts.First());
}
