public void GrantTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE FOO( a int)");
parser.Parse("GRANT SELECT ON foo TO public");
ScriptSet difference = parser.Database.CreateDiffScripts(new Database());
Assert.AreEqual(2, difference.Count);
Assert.AreEqual(difference[0].Type, ScriptType.Table);
Assert.AreEqual(@"CREATE TABLE [dbo].[FOO](
[a] [int] NULL
)
GO",
difference[0].Text);
Assert.AreEqual(difference[1].Type, ScriptType.Permission);
Assert.AreEqual(@"GRANT SELECT ON [dbo].[FOO] TO [public]
GO
",
difference[1].Text);
ExecuteScripts(difference);
ScriptParser database = ParseDatabase();
difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void UniqueTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE foo( a varchar(10) UNIQUE )");
ScriptSet scripts = parser.Database.CreateDiffScripts(new ScriptParser().Database);
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(ScriptType.Table, scripts[0].Type);
Assert.AreEqual(@"CREATE TABLE [dbo].[foo](
[a] [varchar](10) NULL
)
GO",
scripts[0].Text);
Assert.AreEqual(ScriptType.UniqueConstraint, scripts[1].Type);
Assert.AreEqual("[dbo].[IX_foo_a]", scripts[1].Name.FullName);
Assert.AreEqual(@"ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [IX_foo_a]
UNIQUE(
[a]
)",
scripts[1].Text);
}
public void FilegroupTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE foo(a int)");
parser.Parse("CREATE INDEX foo_a ON dbo.foo(a) ON Primary");
Assert.AreEqual(1, parser.Database.Tables.Count);
Assert.AreEqual(1, parser.Database.Tables[0].Indexes.Count);
ExecuteScripts(parser.Database.Tables[0].GenerateCreateScript());
Script createScript = parser.Database.Tables[0].Indexes[0].GenerateCreateScript();
Assert.AreEqual(@"CREATE INDEX [foo_a]
ON [dbo].[foo](
[a] ASC
)
ON [Primary]
",
createScript.Text);
ExecuteScripts(createScript);
ScriptParser database = ParseDatabase();
ScriptSet difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void TableScriptTest()
{
ScriptSet scripts = new ScriptSet();
scripts.Add(new Script(@"IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'dbo.Foo')
BEGIN
DROP Table dbo.Foo
END
GO
CREATE TABLE dbo.Foo
(
x int
)
GO
GRANT SELECT ON dbo.Foo to PUBLIC",
"Foo.sql", ScriptType.Unknown));
ScriptParser parser = new ScriptParser();
parser.RetrieveParsableObjects(scripts);
Assert.AreEqual(0, scripts.Count);
Assert.AreEqual(1, parser.Database.Tables.Count);
}
public void StoredProcedureWithTableTypeTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TYPE foo AS TABLE( a int)");
startingDatabase.Parse("CREATE PROCEDURE GetFoo(@a foo READONLY) AS SELECT * FROM @a");
ScriptSet scripts = startingDatabase.Database.CreateDiffScripts(new Database());
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
ExecuteScripts(scripts);
ScriptParser modifiedDatabase = new ScriptParser();
modifiedDatabase.Parse("CREATE TYPE foo AS TABLE( a int, b int)");
modifiedDatabase.Parse("CREATE PROCEDURE GetFoo(@a foo READONLY) AS SELECT * FROM @a");
scripts = modifiedDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(4, scripts.Count);
Assert.AreEqual(ScriptType.DropStoredProc, scripts[0].Type);
Assert.AreEqual(ScriptType.DropTableType, scripts[1].Type);
Assert.AreEqual(ScriptType.TableType, scripts[2].Type);
Assert.AreEqual(ScriptType.StoredProc, scripts[3].Type);
ExecuteScripts(scripts);
}
public void InlineForeignKeyTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE foo( a int primary key)");
parser.Parse("CREATE TABLE bar( a int foreign key references foo(a))");
Assert.AreEqual(2, parser.Database.Tables.Count);
Assert.AreEqual(2, parser.Database.Constraints.Count);
ScriptParser database = ParseDatabase();
ScriptSet scripts = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(4, scripts.Count);
scripts.Sort();
Assert.AreEqual(ScriptType.ForeignKey, scripts[3].Type);
Assert.AreEqual(@"ALTER TABLE [dbo].[bar]
ADD CONSTRAINT [FK_bar_foo]
FOREIGN KEY(
[a]
)
REFERENCES [dbo].[foo](
[a]
)",
scripts[3].Text);
ExecuteScripts(scripts);
database = ParseDatabase();
ScriptSet difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void RenamePrimaryKeyTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a int, b int)");
startingDatabase.Parse(@"ALTER TABLE foo
ADD CONSTRAINT PK_BADNAME
PRIMARY KEY CLUSTERED(a)");
startingDatabase.Parse("CREATE TABLE bar( c int FOREIGN KEY REFERENCES foo(a))");
ScriptSet scripts = startingDatabase.Database.CreateDiffScripts(new Database());
ExecuteScripts(scripts);
ScriptParser modifiedDatabase = new ScriptParser();
modifiedDatabase.Parse("CREATE TABLE foo( a int PRIMARY KEY, b int)");
modifiedDatabase.Parse("CREATE TABLE bar( c int FOREIGN KEY REFERENCES foo(a))");
scripts = modifiedDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(4, scripts.Count);
Assert.AreEqual(ScriptType.DropForeignKey, scripts[0].Type);
Assert.AreEqual(ScriptType.DropPrimaryKey, scripts[1].Type);
Assert.AreEqual(ScriptType.PrimaryKey, scripts[2].Type);
Assert.AreEqual(ScriptType.ForeignKey, scripts[3].Type);
ExecuteScripts(scripts);
}
public void StoredProcedureWithSchemaTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a int, b int)");
startingDatabase.Parse("CREATE TABLE guest.foo( a int, b int)");
startingDatabase.Parse("CREATE PROCEDURE GetFoo(@a int) AS SELECT * FROM foo WHERE a=@a");
startingDatabase.Parse("CREATE PROCEDURE guest.GetFoo(@a int) AS SELECT * FROM guest.foo WHERE a=@a");
ScriptSet scripts = startingDatabase.Database.CreateDiffScripts(new Database());
scripts.Sort();
Assert.AreEqual(4, scripts.Count);
ExecuteScripts(scripts);
ScriptParser modifiedDatabase = new ScriptParser();
modifiedDatabase.Parse("CREATE TABLE foo( a int, b int, c int)");
modifiedDatabase.Parse("CREATE TABLE guest.foo( a int, b int)");
modifiedDatabase.Parse("CREATE PROCEDURE GetFoo(@a int) AS SELECT * FROM foo WHERE a=@a");
modifiedDatabase.Parse("CREATE PROCEDURE guest.GetFoo(@a int) AS SELECT * FROM guest.foo WHERE a=@a");
scripts = modifiedDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(5, scripts.Count);
Assert.AreEqual(ScriptType.DropStoredProc, scripts[0].Type);
Assert.AreEqual(ScriptType.TableSaveData, scripts[1].Type);
Assert.AreEqual(ScriptType.Table, scripts[2].Type);
Assert.AreEqual(ScriptType.TableRestoreData, scripts[3].Type);
Assert.AreEqual(ScriptType.StoredProc, scripts[4].Type);
ExecuteScripts(scripts);
}
public void GrantTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE foo( a int)");
parser.Parse("CREATE PROCEDURE A (@B int) AS SELECT * FROM foo GO GRANT Execute ON A TO public");
ScriptSet difference = parser.Database.CreateDiffScripts(new Database());
Assert.AreEqual(3, difference.Count);
Assert.AreEqual(difference[0].Type, ScriptType.Table);
Assert.AreEqual(difference[1].Type, ScriptType.StoredProc);
Assert.AreEqual(difference[2].Type, ScriptType.Permission);
Assert.AreEqual(@"GRANT Execute ON [dbo].[A] TO [public]
GO
",
difference[2].Text);
ExecuteScripts(difference);
ScriptParser database = ParseDatabase();
difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void GrantTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE Function A (@B int) RETURNS int AS BEGIN RETURN @B END");
parser.Parse("GRANT Execute ON A TO public");
ScriptSet difference = parser.Database.CreateDiffScripts(new Database());
Assert.AreEqual(2, difference.Count);
Assert.AreEqual(difference[0].Type, ScriptType.UserDefinedFunction);
Assert.AreEqual(difference[1].Type, ScriptType.Permission);
Assert.AreEqual(@"GRANT Execute ON [dbo].[A] TO [public]
GO
",
difference[1].Text);
ExecuteScripts(difference);
ScriptParser database = ParseDatabase();
difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void PermissionTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a int)");
ScriptParser currentDatabase = new ScriptParser();
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo( a int)");
endingDatabase.Parse("GRANT SELECT ON foo TO PUBLIC");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
Assert.AreEqual(1, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.Permission);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void TableNameChangeTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a int)");
ScriptParser currentDatabase = new ScriptParser();
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE bar( a int)");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
Assert.AreEqual(1, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.Table);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void PrimaryKeyNameTest()
{
RunOptions.Current.FullyScripted = false;
ExecuteScripts(new Script("CREATE TABLE foo( a int PRIMARY KEY)", "INIT", ScriptType.Unknown));
ScriptParser currentDatabase = ParseDatabase();
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo( a int PRIMARY KEY)");
ScriptSet differences = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
differences.Sort();
Assert.AreEqual(2, differences.Count);
Assert.AreEqual(ScriptType.DropPrimaryKey, differences[0].Type);
//don't know what the name will be, doesn't matter
Assert.AreEqual(ScriptType.PrimaryKey, differences[1].Type);
Assert.AreEqual(@"ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [PK_foo]
PRIMARY KEY(
[a]
)", differences[1].Text);
ExecuteScripts(differences);
currentDatabase = ParseDatabase();
differences = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, differences.Count);
}
public void InsertColumnTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse(@"CREATE TABLE a ( b int, c int)");
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse(@"CREATE TABLE a ( b int, d int, c int)");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(3, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.TableSaveData);
Assert.AreEqual(scripts[1].Type, ScriptType.Table);
Assert.AreEqual(scripts[2].Type, ScriptType.TableRestoreData);
Assert.AreEqual(@"INSERT INTO [dbo].[a] (
[b],
[c]
)
SELECT
[b],
[c]
FROM [dbo].[Tmp__a] WITH (HOLDLOCK TABLOCKX)
DROP TABLE [dbo].[Tmp__a]
GO
",
scripts[2].Text);
}
public void CheckTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a varchar(10) CHECK ( a in ('X', 'Y')))");
ScriptParser currentDatabase = new ScriptParser();
ScriptSet scripts = startingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.Table);
Assert.AreEqual(scripts[1].Type, ScriptType.CheckConstraint);
ExecuteScripts(scripts);
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo( a varchar(10) CHECK ( a in ('X', 'Y', 'Z')))");
scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.DropConstraint);
Assert.AreEqual(scripts[1].Type, ScriptType.CheckConstraint);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void DefaultTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a int default 0)");
ScriptParser currentDatabase = new ScriptParser();
ScriptSet scripts = startingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
ExecuteScripts(scripts);
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo( a int default 2)");
scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.DropConstraint);
Assert.AreEqual(@"ALTER TABLE [dbo].[foo] DROP CONSTRAINT [DF_foo_a]", scripts[0].Text);
Assert.AreEqual(scripts[1].Type, ScriptType.DefaultConstraint);
Assert.AreEqual(@"ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [DF_foo_a]
DEFAULT ( 2 )
FOR [a]", scripts[1].Text);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void FunctionNameChangeTest()
{
RunOptions.Current.FullyScripted = false;
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE Function A (@B int) RETURNS int AS BEGIN RETURN @B END");
ScriptParser currentDatabase = new ScriptParser();
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE Function B (@B int) RETURNS int AS BEGIN RETURN @B END");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
Assert.AreEqual(1, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.UserDefinedFunction);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count);
}
public void BodyChangedTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE Function A (@B int) RETURNS int AS BEGIN RETURN @B END");
ScriptParser currentDatabase = new ScriptParser();
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE Function A (@B int) RETURNS int AS BEGIN RETURN @B+1 END");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.DropUserDefinedFunction);
Assert.AreEqual(@"DROP FUNCTION [dbo].[A]
GO", scripts[0].Text);
Assert.AreEqual(scripts[1].Type, ScriptType.UserDefinedFunction);
//the actual script should be tested in the parser tests
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count);
}
public void TableGrantTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TYPE foo AS TABLE ( a int )");
parser.Parse("GRANT CONTROL ON TYPE::dbo.foo TO public");
Assert.AreEqual(1, parser.Database.Tables.Count);
ScriptSet difference = parser.Database.CreateDiffScripts(new Database());
Assert.AreEqual(2, difference.Count);
Assert.AreEqual(difference[0].Type, ScriptType.TableType);
Assert.AreEqual(@"CREATE TYPE [dbo].[foo] AS TABLE(
[a] [int] NULL
)
GO",
difference[0].Text);
Assert.AreEqual(difference[1].Type, ScriptType.Permission);
Assert.AreEqual(@"GRANT CONTROL ON TYPE::[dbo].[foo] TO [public]
GO
",
difference[1].Text);
ExecuteScripts(difference);
ScriptParser database = ParseDatabase();
difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void FunctionTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE FUNCTION dbo.checker(@val varchar(10)) RETURNS int AS BEGIN RETURN 1 END");
parser.Parse("CREATE TABLE foo( a varchar(10) CHECK ( dbo.checker(a) = 1))");
Assert.AreEqual(1, parser.Database.Tables.Count);
Assert.AreEqual(1, parser.Database.Constraints.Count);
Assert.AreEqual(1, parser.Database.Functions.Count);
ScriptParser database = ParseDatabase();
ScriptSet scripts = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(3, scripts.Count);
Assert.AreEqual(ScriptType.Table, scripts[0].Type);
Assert.AreEqual(ScriptType.CheckConstraint, scripts[1].Type);
Assert.AreEqual(@"ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [CK_foo]
CHECK ( [dbo].[checker] ( [a] ) = 1 )",
scripts[1].Text);
Assert.AreEqual(ScriptType.UserDefinedFunction, scripts[2].Type);
Assert.AreEqual(@"CREATE FUNCTION dbo.checker(@val varchar(10)) RETURNS int AS BEGIN RETURN 1 END
GO",
scripts[2].Text);
ExecuteScripts(scripts);
database = ParseDatabase();
ScriptSet difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void AlterDefaultTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE foo( a int)");
parser.Parse("ALTER TABLE foo ADD CONSTRAINT DF_foo_a DEFAULT 0 FOR a");
Assert.AreEqual(1, parser.Database.Tables.Count);
Assert.AreEqual(1, parser.Database.Constraints.Count);
ScriptParser database = ParseDatabase();
ScriptSet scripts = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(ScriptType.Table, scripts[0].Type);
Assert.AreEqual(ScriptType.DefaultConstraint, scripts[1].Type);
Assert.AreEqual(@"ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [DF_foo_a]
DEFAULT ( 0 )
FOR [a]",
scripts[1].Text);
ExecuteScripts(scripts);
database = ParseDatabase();
ScriptSet difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
/// <summary>
/// Gets difference scripts.
/// </summary>
/// <param name="other">The other set of table data.</param>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
public ScriptSet GetDifferenceScript(TableData other, Name tableName)
{
ScriptSet scripts = new ScriptSet();
GetDifferences(other, tableName, scripts);
return(scripts);
}
public void NameOverlapTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE foo(a int)");
parser.Parse("CREATE TABLE bar(a int)");
parser.Parse("CREATE INDEX a ON dbo.foo(a)");
parser.Parse("CREATE INDEX a ON dbo.bar(a)");
Assert.AreEqual(2, parser.Database.Tables.Count);
Assert.AreEqual(1, parser.Database.Tables[0].Indexes.Count);
Assert.AreEqual(1, parser.Database.Tables[1].Indexes.Count);
ScriptSet scripts = parser.Database.CreateDiffScripts(new Database());
Assert.AreEqual(4, scripts.Count);
scripts.Sort();
Assert.AreEqual(ScriptType.Table, scripts[0].Type);
Assert.AreEqual(ScriptType.Table, scripts[1].Type);
Assert.AreEqual(ScriptType.Index, scripts[2].Type);
Assert.AreEqual(ScriptType.Index, scripts[3].Type);
ExecuteScripts(scripts);
ScriptParser database = ParseDatabase();
Assert.AreEqual(2, database.Database.Tables.Count);
Assert.AreEqual(1, database.Database.Tables[0].Indexes.Count);
Assert.AreEqual(1, database.Database.Tables[1].Indexes.Count);
ScriptSet difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void ViewTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE foo(a int)");
parser.Parse("CREATE VIEW bar WITH SCHEMABINDING AS SELECT a FROM dbo.foo");
parser.Parse("CREATE UNIQUE CLUSTERED INDEX bar_a ON dbo.bar(a)");
Assert.AreEqual(1, parser.Database.Views.Count);
Assert.AreEqual(1, parser.Database.Views[0].Indexes.Count);
ScriptParser database = ParseDatabase();
ScriptSet scripts = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(3, scripts.Count);
Assert.AreEqual(@"CREATE UNIQUE CLUSTERED INDEX [bar_a]
ON [dbo].[bar](
[a] ASC
)
",
scripts[2].Text);
ExecuteScripts(scripts);
database = ParseDatabase();
ScriptSet difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void TableTypeTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TYPE foo AS TABLE( a int)");
ScriptParser currentDatabase = new ScriptParser();
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TYPE foo AS TABLE( a int, b int)");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.DropTableType);
Assert.AreEqual("DROP TYPE [dbo].[foo]", scripts[0].Text);
Assert.AreEqual(scripts[1].Type, ScriptType.TableType);
//the actual script should be tested in the parser tests
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void ViewNameChangeTest()
{
RunOptions.Current.FullyScripted = false;
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a int)");
startingDatabase.Parse("CREATE View A AS SELECT * FROM foo");
ScriptParser currentDatabase = new ScriptParser();
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE View B AS SELECT * FROM foo");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
Assert.AreEqual(1, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.View);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void TableFunctionViewTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse(@"CREATE TABLE dbo.foo(
fooID int identity(0, 1)
)");
startingDatabase.Parse("CREATE Function dbo.bar (@A int) RETURNS int AS BEGIN RETURN @A END");
startingDatabase.Parse(@"CREATE VIEW dbo.baz
AS
SELECT FooID,
dbo.bar(FooID) 'bonk'
FROM dbo.foo");
Assert.AreEqual(1, startingDatabase.Database.Functions.Count);
Assert.AreEqual(1, startingDatabase.Database.Tables.Count);
Assert.AreEqual(1, startingDatabase.Database.Views.Count);
//make sure this runs
ScriptParser currentDatabase = new ScriptParser();
ScriptSet scripts = startingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
//make sure it got results
currentDatabase = ParseDatabase();
ScriptSet difference = startingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
/// <summary>
/// Script out the target database
/// </summary>
public void ScriptTarget()
{
try
{
//make sure we can run
string error = null;
if (RunOptions.Current.Connections.Targets.Count == 0)
{
error = "Missing a valid target database";
}
if (error != null)
{
Exit(error, false);
}
//Load the target database
ScriptParser targetParser = LoadTargetDatabase(null);
//generate new scripts
RunOptions.Current.Logger.Log("Generating Scripts", OutputLevel.Reads);
DifferenceSet differences = null;
ScriptSet scripts = targetParser.Database.CreateScripts();
//store the scripts
ScriptManager.WriteScripts(scripts, differences);
}
catch (Exception ex)
{
Exit("Error: " + ex.Message, false);
}
Exit("Update Successful", true);
}
public static void AddScript(ScriptSet scriptSet)
{
scriptSet.AddRange(
"line 1",
"line 2",
"line 3",
"line 4",
"line 5",
"line 6",
Sb.Line("1","2","3","4","5"),
Sb.Line("1","2","3","4","5"),
Sb.Indent(
"indented",
"line 1",
"line 2",
"line 3",
"line 4",
"line 5",
"line 6",
Sb.Line("1","2","3","4","5"),
Sb.Line("1","2","3","4","5")
),
Sb.Script(
"script",
"line 1",
"line 2",
"line 3",
"line 4",
"line 5"
),
Sb.Wrapper("(",Sb.Line("1","2","3"),")"),
Sb.Wrapper("{", Sb.Script("1", "2", "3"), "}")
);
}
public void DefaultDataTest()
{
ScriptParser parsed = new ScriptParser();
parsed.Parse("CREATE TABLE foo( a varchar(10), b varchar(20) default 'foo')");
parsed.Parse(@"INSERT INTO foo(a) VALUES('X')
INSERT INTO foo(a, b) VALUES('A', 'B')");
ScriptSet scripts = parsed.Database.CreateDiffScripts(new Database());
scripts.Sort();
Assert.AreEqual(3, scripts.Count);
Assert.AreEqual(ScriptType.Table, scripts[0].Type);
Assert.AreEqual(ScriptType.DefaultConstraint, scripts[1].Type);
Assert.AreEqual(ScriptType.TableData, scripts[2].Type);
Assert.AreEqual(@"INSERT INTO [dbo].[foo]([a], [b])
VALUES('X', 'foo')
INSERT INTO [dbo].[foo]([a], [b])
VALUES('A', 'B')
",
scripts[2].Text);
scripts = parsed.Database.CreateDiffScripts(parsed.Database);
Assert.AreEqual(0, scripts.Count);
}
public void ColumnCountTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo(a int, b int)");
startingDatabase.Parse("CREATE INDEX foo_a ON dbo.foo(a)");
ScriptParser currentDatabase = new ScriptParser();
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo(a int, b int)");
endingDatabase.Parse("CREATE INDEX foo_a ON dbo.foo(a, b)");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.DropIndex);
Assert.AreEqual(@"DROP INDEX [foo_a] ON [dbo].[foo]
", scripts[0].Text);
Assert.AreEqual(scripts[1].Type, ScriptType.Index);
//the actual script should be tested in the parser tests
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public HttpServer(Action action, string url, PhantomjsWrapper wrapper)
{
_listener = new HttpListener();
Scripts = new ScriptSet();
_commands = new PhEventSet
{
new OnLoadPhEvent(action, wrapper),
new MainHtmlPhEvent(),
new JQueryPhEvent(),
new ScriptPhEvent(Scripts),
new CallbackPhEvent(Scripts)
};
}
public static void AddTypes(ScriptSet scriptSet)
{
scriptSet.AddRange(
Sb.Line("DateTime = ", new DateTime(2008, 12, 18, 18, 34, 56)),
Sb.Line("Boolean = ", true),
Sb.Line("Int = ", 35345),
Sb.Line("Float = ", 35454.677),
Sb.Line("TimeSpan = ", new TimeSpan(1, 2, 3, 4, 5)),
Sb.Line("String = ", "the cat sat on the mat"),
Sb.Line("String2 = ", "some special characters \"\'&^%$#@!*`~;:<>,./?=-+_"),
Sb.Line("String3 = ", "string on" + Environment.NewLine + "two lines"),
Sb.Line("SecureString = ", Encryption.CreateSecureString("password"))
);
}
public ScriptPhEvent(ScriptSet scripts)
{
_scripts = scripts;
}
public ScriptSet CreateScriptSet(ScriptFormat format, params object[] items)
{
ScriptSet j = new ScriptSet(this.Document, items);
j.SetFormat(format);
return j;
}
public CallbackPhEvent(ScriptSet scripts)
{
_scripts = scripts;
}
