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 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 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 ForeignKeyUniqueIndexNameChangeTest()
{
RunOptions.Current.FullyScripted = true;
//Set up the database
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE base(a int)");
parser.Parse("CREATE TABLE dependant(a int foreign key references base(a))");
parser.Parse("CREATE UNIQUE NONCLUSTERED INDEX ix_base ON base(a)");
ScriptSet differences = parser.Database.CreateDiffScripts(new Database());
differences.Sort();
Assert.AreEqual(4, differences.Count);
Assert.AreEqual(ScriptType.Table, differences[0].Type);
Assert.AreEqual(ScriptType.Table, differences[1].Type);
Assert.AreEqual(ScriptType.Index, differences[2].Type);
Assert.AreEqual(ScriptType.ForeignKey, differences[3].Type);
//Make sure everything applies correctly
ExecuteScripts(differences);
ScriptParser currentDatabase = ParseDatabase();
differences = parser.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, differences.Count);
//Rename the unique constraint
parser = new ScriptParser();
parser.Parse("CREATE TABLE base(a int)");
parser.Parse("CREATE TABLE dependant(a int foreign key references base(a))");
parser.Parse("CREATE UNIQUE NONCLUSTERED INDEX ix_base_a ON base(a)");
differences = parser.Database.CreateDiffScripts(currentDatabase.Database);
differences.Sort();
Assert.AreEqual(4, differences.Count);
Assert.AreEqual(ScriptType.DropForeignKey, differences[0].Type);
Assert.AreEqual(ScriptType.DropIndex, differences[1].Type);
Assert.AreEqual(ScriptType.Index, differences[2].Type);
Assert.AreEqual(ScriptType.ForeignKey, differences[3].Type);
//Make sure everything applies correctly
ExecuteScripts(differences);
currentDatabase = ParseDatabase();
differences = parser.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, differences.Count);
}
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 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 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 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 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 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 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 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 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 void FulltextStoplistTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE FULLTEXT CATALOG Fulltext AS DEFAULT");
parser.Parse(@"CREATE TABLE Searching
(
SearchingID int NOT NULL,
SearchText varchar(max),
LastUpdate timestamp
)");
parser.Parse("CREATE UNIQUE CLUSTERED INDEX PK_Searching ON dbo.Searching(SearchingID)");
parser.Parse(@"CREATE FULLTEXT INDEX ON Searching(
SearchText LANGUAGE English
)
KEY INDEX PK_Searching ON Fulltext
WITH (CHANGE_TRACKING = AUTO, STOPLIST = OFF)");
Assert.AreEqual(1, parser.Database.Tables.Count);
Assert.AreEqual(1, parser.Database.Tables[0].Indexes.Count);
Assert.AreEqual(1, parser.Database.FulltextIndexes.Count);
ScriptSet scripts = parser.Database.CreateDiffScripts(new Database());
scripts.Sort();
Assert.AreEqual(4, scripts.Count);
Assert.AreEqual(ScriptType.Table, scripts[0].Type);
Assert.AreEqual(@"CREATE TABLE [dbo].[Searching](
[SearchingID] [int] NOT NULL,
[SearchText] [varchar](max) NULL,
[LastUpdate] [timestamp] NULL
)
GO", scripts[0].Text);
Assert.AreEqual(ScriptType.ClusteredIndex, scripts[1].Type);
Assert.AreEqual(@"CREATE UNIQUE CLUSTERED INDEX [PK_Searching]
ON [dbo].[Searching](
[SearchingID] ASC
)
", scripts[1].Text);
Assert.AreEqual(ScriptType.FulltextCatalog, scripts[2].Type);
Assert.AreEqual(ScriptType.FulltextIndex, scripts[3].Type);
Assert.AreEqual(@"CREATE FULLTEXT INDEX ON [dbo].[Searching](
[SearchText] LANGUAGE [English]
)
KEY INDEX [PK_Searching]
ON ([Fulltext])
WITH (CHANGE_TRACKING AUTO, STOPLIST OFF)", scripts[3].Text);
ExecuteScripts(scripts);
ScriptParser database = ParseDatabase();
ScriptSet difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void TextTest()
{
ScriptSet initScripts = new ScriptSet();
initScripts.Add(new Script("CREATE TABLE foo( a text, b ntext, c varchar(max), d nvarchar(max))", "Foo", ScriptType.Table));
initScripts.Add(new Script(@"INSERT INTO foo(a, b, c, d) VALUES('X', 'X', 'X', 'X')
INSERT INTO foo(a, b, c, d) VALUES('A', 'A', 'A', 'A')", "FooData", ScriptType.TableData));
ExecuteScripts(initScripts);
ScriptSet updateScripts = new ScriptSet();
updateScripts.Add(new Script("CREATE TABLE foo( a text, b ntext, c varchar(max), d nvarchar(max))", "Foo", ScriptType.Table));
updateScripts.Add(new Script(
@"INSERT INTO foo(a, b, c, d) VALUES('X', 'X', 'X', 'X')
INSERT INTO foo(a, b, c, d) VALUES('Y', 'Y', 'Y', 'Y')
INSERT INTO dbo.foo(a, b, c, d) VALUES('Z', 'Z', 'Z', 'Z')
insert into dbo.foo(a, b, c, d) values('yabba zabba', 'yabba zabba', 'yabba zabba', 'yabba zabba')", "FooData", ScriptType.TableData));
ScriptParser parsedScripts = new ScriptParser();
parsedScripts.RetrieveParsableObjects(updateScripts);
Assert.AreEqual(0, updateScripts.Count);
ScriptParser parsedDatabase = ParseDatabase();
GetData(parsedDatabase.Database, parsedScripts.Database.GetTablesWithData());
updateScripts.Add(parsedScripts.Database.CreateDiffScripts(parsedDatabase.Database));
Assert.AreEqual(2, updateScripts.Count);
updateScripts.Sort();
Assert.AreEqual(@"DELETE FROM [dbo].[foo]
WHERE
CAST([a] AS VARCHAR(MAX)) = 'A'
AND CAST([b] AS VARCHAR(MAX)) = 'A'
AND [c] = 'A'
AND [d] = 'A'
", updateScripts[0].Text);
Assert.AreEqual(ScriptType.TableRemoveData, updateScripts[0].Type);
Assert.AreEqual(@"INSERT INTO [dbo].[foo]([a], [b], [c], [d])
VALUES('Y', 'Y', 'Y', 'Y')
INSERT INTO [dbo].[foo]([a], [b], [c], [d])
VALUES('Z', 'Z', 'Z', 'Z')
INSERT INTO [dbo].[foo]([a], [b], [c], [d])
VALUES('yabba zabba', 'yabba zabba', 'yabba zabba', 'yabba zabba')
", updateScripts[1].Text);
Assert.AreEqual(ScriptType.TableData, updateScripts[1].Type);
ExecuteScripts(updateScripts);
}
public void AddDefaultTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a int, b int)");
startingDatabase.Parse("INSERT INTO foo(a) VALUES(1)");
ScriptParser currentDatabase = new ScriptParser();
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo( a int, b int NOT NULL DEFAULT(0))");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(4, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.TableSaveData);
Assert.AreEqual("EXEC sp_rename '[dbo].[foo]', 'Tmp__foo', 'OBJECT'", scripts[0].Text);
Assert.AreEqual(scripts[1].Type, ScriptType.Table);
//the actual script should be tested in the parser tests
Assert.AreEqual(scripts[2].Type, ScriptType.DefaultConstraint);
Assert.AreEqual(@"ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [DF_foo_b]
DEFAULT ( 0 )
FOR [b]", scripts[2].Text);
Assert.AreEqual(scripts[3].Type, ScriptType.TableRestoreData);
Assert.AreEqual(@"INSERT INTO [dbo].[foo] (
[a],
[b]
)
SELECT
[a],
ISNULL([b], 0)
FROM [dbo].[Tmp__foo] WITH (HOLDLOCK TABLOCKX)
DROP TABLE [dbo].[Tmp__foo]
GO
", scripts[3].Text);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void DropIndexWithSchemaTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse(@"CREATE TABLE guest.a(b varchar(25))
GO
CREATE INDEX IX_a_b ON guest.a(b)");
ScriptSet scripts = parser.Database.CreateDiffScripts(new Database());
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(ScriptType.Table, scripts[0].Type);
Assert.AreEqual(ScriptType.Index, scripts[1].Type);
ExecuteScripts(scripts);
ScriptParser currentDatabase = ParseDatabase();
scripts = parser.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, scripts.Count);
parser = new ScriptParser();
parser.Parse(@"CREATE TABLE guest.a(b varchar(25), x int)
GO
CREATE INDEX IX_a_b ON guest.a(b)");
scripts = parser.Database.CreateDiffScripts(currentDatabase.Database);
scripts.Sort();
Assert.AreEqual(5, scripts.Count);
Assert.AreEqual(ScriptType.DropIndex, scripts[0].Type);
Assert.AreEqual(ScriptType.TableSaveData, scripts[1].Type);
Assert.AreEqual(ScriptType.Table, scripts[2].Type);
Assert.AreEqual(ScriptType.Index, scripts[3].Type);
Assert.AreEqual(ScriptType.TableRestoreData, scripts[4].Type);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
scripts = parser.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, scripts.Count);
}
public void DataTypesTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse(@"CREATE TABLE a(
b bigint,
c bit,
d char(1),
e datetime,
f decimal,
g float,
h int,
i money,
j ntext,
k nvarchar(max),
l smalldatetime,
m smallint,
n text,
o tinyint,
p varchar(max)
)");
parser.Parse(@"INSERT INTO a(b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) VALUES(
3333333333,
1,
'a',
'2000-01-02 03:04:05.600',
3,
3.14,
42,
3.14,
'lorem ipsum',
'lorem ipsum',
'1/2/2000',
42,
'lorem ipsum',
42,
'lorem ipsum'
)");
ScriptSet initScripts = parser.Database.CreateDiffScripts(new Database());
initScripts.Sort();
Assert.AreEqual(2, initScripts.Count);
Assert.AreEqual(ScriptType.Table, initScripts[0].Type);
Assert.AreEqual(ScriptType.TableData, initScripts[1].Type);
ExecuteScripts(initScripts);
ScriptParser currentDatabase = ParseDatabase();
GetData(currentDatabase.Database, parser.Database.GetTablesWithData());
ScriptSet difference = parser.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count);
}
public void DateTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE a( b int primary key, c datetime)");
parser.Parse("INSERT INTO a(b, c) VALUES(1, '2000-01-02 03:04:05.600')");
ScriptSet initScripts = parser.Database.CreateDiffScripts(new Database());
initScripts.Sort();
Assert.AreEqual(3, initScripts.Count);
Assert.AreEqual(ScriptType.Table, initScripts[0].Type);
Assert.AreEqual(ScriptType.PrimaryKey, initScripts[1].Type);
Assert.AreEqual(ScriptType.TableData, initScripts[2].Type);
Assert.AreEqual(@"INSERT INTO [dbo].[a]([b], [c])
VALUES(1, '2000-01-02 03:04:05.600')
", initScripts[2].Text);
ExecuteScripts(initScripts);
ScriptParser currentDatabase = ParseDatabase();
GetData(currentDatabase.Database, parser.Database.GetTablesWithData());
ScriptSet difference = parser.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count);
ScriptParser updated = new ScriptParser();
updated.Parse("CREATE TABLE a( b int primary key, c datetime)");
updated.Parse("INSERT INTO a(b, c) VALUES(1, '2000-01-02')");
difference = updated.Database.CreateDiffScripts(currentDatabase.Database);
difference.Sort();
Assert.AreEqual(2, difference.Count);
Assert.AreEqual(ScriptType.TableRemoveData, difference[0].Type);
Assert.AreEqual(@"DELETE FROM [dbo].[a]
WHERE
[b] = 1
AND [c] = '2000-01-02 03:04:05.600'
", difference[0].Text);
Assert.AreEqual(ScriptType.TableData, difference[1].Type);
Assert.AreEqual(@"INSERT INTO [dbo].[a]([b], [c])
VALUES(1, '2000-01-02 00:00:00.000')
", difference[1].Text);
ExecuteScripts(difference);
currentDatabase = ParseDatabase();
GetData(currentDatabase.Database, parser.Database.GetTablesWithData());
difference = updated.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count);
}
public void BasicDataTest()
{
ScriptSet initScripts = new ScriptSet();
initScripts.Add(new Script("CREATE TABLE foo( a varchar(15))", "Foo", ScriptType.Table));
initScripts.Add(new Script(@"INSERT INTO foo(a) VALUES('X')
INSERT INTO foo(a) VALUES('A')", "FooData", ScriptType.TableData));
ExecuteScripts(initScripts);
ScriptSet updateScripts = new ScriptSet();
updateScripts.Add(new Script("CREATE TABLE foo( a varchar(15))", "Foo", ScriptType.Table));
updateScripts.Add(new Script(
@"INSERT INTO foo(a) VALUES('X')
INSERT INTO foo(a) VALUES('Y')
INSERT INTO dbo.foo(a) VALUES('Z')
insert into dbo.foo(a) values('yabba zabba')", "FooData", ScriptType.TableData));
ScriptParser parsedScripts = new ScriptParser();
parsedScripts.RetrieveParsableObjects(updateScripts);
Assert.AreEqual(0, updateScripts.Count);
ScriptParser parsedDatabase = ParseDatabase();
GetData(parsedDatabase.Database, parsedScripts.Database.GetTablesWithData());
updateScripts.Add(parsedScripts.Database.CreateDiffScripts(parsedDatabase.Database));
Assert.AreEqual(2, updateScripts.Count);
updateScripts.Sort();
Assert.AreEqual(@"DELETE FROM [dbo].[foo]
WHERE
[a] = 'A'
", updateScripts[0].Text);
Assert.AreEqual(ScriptType.TableRemoveData, updateScripts[0].Type);
Assert.AreEqual(@"INSERT INTO [dbo].[foo]([a])
VALUES('Y')
INSERT INTO [dbo].[foo]([a])
VALUES('Z')
INSERT INTO [dbo].[foo]([a])
VALUES('yabba zabba')
", updateScripts[1].Text);
Assert.AreEqual(ScriptType.TableData, updateScripts[1].Type);
ExecuteScripts(updateScripts);
}
public void UpdateTableDataTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a int PRIMARY KEY, b int)");
startingDatabase.Parse("INSERT INTO foo(a) VALUES (1)");
startingDatabase.Parse("CREATE TABLE bar( c int FOREIGN KEY REFERENCES foo(a))");
startingDatabase.Parse("INSERT INTO bar(c) VALUES (1)");
ScriptParser currentDatabase = new ScriptParser();
ScriptSet scripts = startingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
GetData(currentDatabase.Database, startingDatabase.Database.GetTablesWithData());
ScriptSet difference = startingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo( a int PRIMARY KEY, b int)");
endingDatabase.Parse("INSERT INTO foo(a, b) VALUES (1, 2)");
endingDatabase.Parse("CREATE TABLE bar( c int FOREIGN KEY REFERENCES foo(a))");
endingDatabase.Parse("INSERT INTO bar(c) VALUES (1)");
scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(4, scripts.Count);
Assert.AreEqual(ScriptType.DropForeignKey, scripts[0].Type);
Assert.AreEqual(ScriptType.TableRemoveData, scripts[1].Type);
Assert.AreEqual(@"DELETE FROM [dbo].[foo]
WHERE
[a] = 1
", scripts[1].Text);
Assert.AreEqual(ScriptType.TableData, scripts[2].Type);
Assert.AreEqual(@"INSERT INTO [dbo].[foo]([a], [b])
VALUES(1, 2)
", scripts[2].Text);
Assert.AreEqual(ScriptType.ForeignKey, scripts[3].Type);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
GetData(currentDatabase.Database, endingDatabase.Database.GetTablesWithData());
difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void IdentityColumnTest()
{
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 IDENTITY(0, 1))");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(3, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.TableSaveData);
Assert.AreEqual("EXEC sp_rename '[dbo].[foo]', 'Tmp__foo', 'OBJECT'", scripts[0].Text);
Assert.AreEqual(scripts[1].Type, ScriptType.Table);
//the actual script should be tested in the parser tests
Assert.AreEqual(scripts[2].Type, ScriptType.TableRestoreData);
Assert.AreEqual(@"SET IDENTITY_INSERT [dbo].[foo] ON
GO
INSERT INTO [dbo].[foo] (
[a]
)
SELECT
[a]
FROM [dbo].[Tmp__foo] WITH (HOLDLOCK TABLOCKX)
DROP TABLE [dbo].[Tmp__foo]
GO
SET IDENTITY_INSERT [dbo].[foo] OFF
GO
", scripts[2].Text);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void DropClusteredIndexOnViewTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE Foo(a int, b int, c int)");
parser.Parse("CREATE VIEW Bar WITH SCHEMABINDING AS SELECT a, b FROM dbo.Foo");
parser.Parse("CREATE UNIQUE CLUSTERED INDEX IX_Bar_a ON Bar(a)");
parser.Parse("CREATE INDEX IX_Bar_b ON Bar(b)");
ScriptSet scripts = parser.Database.CreateDiffScripts(new Database());
scripts.Sort();
Assert.AreEqual(4, scripts.Count);
Assert.AreEqual(ScriptType.Table, scripts[0].Type);
Assert.AreEqual(ScriptType.View, scripts[1].Type);
Assert.AreEqual(ScriptType.ClusteredIndex, scripts[2].Type);
Assert.AreEqual(ScriptType.Index, scripts[3].Type);
ExecuteScripts(scripts);
parser = new ScriptParser();
parser.Parse("CREATE TABLE Foo(a int, b int, c int)");
parser.Parse("CREATE VIEW Bar WITH SCHEMABINDING AS SELECT a, b, c FROM dbo.Foo");
parser.Parse("CREATE UNIQUE CLUSTERED INDEX IX_Bar_a ON Bar(a)");
parser.Parse("CREATE INDEX IX_Bar_b ON Bar(b)");
ScriptParser currentDatabase = ParseDatabase();
scripts = parser.Database.CreateDiffScripts(currentDatabase.Database);
scripts.Sort();
Assert.AreEqual(6, scripts.Count);
Assert.AreEqual(ScriptType.DropIndex, scripts[0].Type);
Assert.AreEqual(ScriptType.DropClusteredIndex, scripts[1].Type);
Assert.AreEqual(ScriptType.DropView, scripts[2].Type);
Assert.AreEqual(ScriptType.View, scripts[3].Type);
Assert.AreEqual(ScriptType.ClusteredIndex, scripts[4].Type);
Assert.AreEqual(ScriptType.Index, scripts[5].Type);
ExecuteScripts(scripts);
}
public void ForeignKeyTest()
{
ScriptParser parser = new ScriptParser();
parser.Parse("CREATE TABLE foo(a int PRIMARY KEY)");
parser.Parse("CREATE TABLE bar(a int)");
parser.Parse(@"ALTER TABLE bar WITH CHECK
ADD CONSTRAINT FK_Bar_Foo_a
FOREIGN KEY( a )
REFERENCES foo(a)");
Assert.AreEqual(2, parser.Database.Tables.Count);
Assert.AreEqual(2, parser.Database.Constraints.Count);
ScriptSet scripts = parser.Database.CreateDiffScripts(new Database());
scripts.Sort();
Assert.AreEqual(4, scripts.Count);
Assert.AreEqual(@"CREATE TABLE [dbo].[bar](
[a] [int] NULL
)
GO", scripts[0].Text);
Assert.AreEqual(@"CREATE TABLE [dbo].[foo](
[a] [int] NOT NULL
)
GO", scripts[1].Text);
Assert.AreEqual(@"ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [PK_foo]
PRIMARY KEY(
[a]
)", scripts[2].Text);
Assert.AreEqual(@"ALTER TABLE [dbo].[bar]
WITH CHECK
ADD CONSTRAINT [FK_Bar_Foo_a]
FOREIGN KEY(
[a]
)
REFERENCES [dbo].[foo](
[a]
)", scripts[3].Text);
ExecuteScripts(scripts);
ScriptParser database = ParseDatabase();
ScriptSet difference = parser.Database.CreateDiffScripts(database.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void TextFilegroupTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo( a int, b varchar(max))");
ScriptParser currentDatabase = new ScriptParser();
ExecuteScripts(startingDatabase.Database.CreateDiffScripts(currentDatabase.Database));
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo( a int, b varchar(max)) TEXTIMAGE_ON Secondary");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(3, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.TableSaveData);
Assert.AreEqual("EXEC sp_rename '[dbo].[foo]', 'Tmp__foo', 'OBJECT'", scripts[0].Text);
Assert.AreEqual(scripts[1].Type, ScriptType.Table);
//the actual script should be tested in the parser tests
Assert.AreEqual(scripts[2].Type, ScriptType.TableRestoreData);
Assert.AreEqual(@"INSERT INTO [dbo].[foo] (
[a],
[b]
)
SELECT
[a],
[b]
FROM [dbo].[Tmp__foo] WITH (HOLDLOCK TABLOCKX)
DROP TABLE [dbo].[Tmp__foo]
GO
", scripts[2].Text);
ExecuteScripts(scripts);
currentDatabase = ParseDatabase();
ScriptSet difference = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
public void ForeignKeyNameTest()
{
RunOptions.Current.FullyScripted = false;
ExecuteScripts(new Script(@"CREATE TABLE foo( a int NOT NULL)
GO
ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [PK_foo]
PRIMARY KEY CLUSTERED(
[a]
)
GO
CREATE TABLE bar( c int FOREIGN KEY REFERENCES foo(a))
", "INIT", ScriptType.Unknown));
ScriptParser currentDatabase = ParseDatabase();
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo( a int PRIMARY KEY)");
endingDatabase.Parse("CREATE TABLE bar( c int FOREIGN KEY REFERENCES foo(a))");
ScriptSet differences = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
differences.Sort();
Assert.AreEqual(2, differences.Count);
Assert.AreEqual(ScriptType.DropForeignKey, differences[0].Type);
//don't know what the name will be, doesn't matter
Assert.AreEqual(ScriptType.ForeignKey, differences[1].Type);
Assert.AreEqual(@"ALTER TABLE [dbo].[bar]
ADD CONSTRAINT [FK_bar_foo]
FOREIGN KEY(
[c]
)
REFERENCES [dbo].[foo](
[a]
)", differences[1].Text);
ExecuteScripts(differences);
currentDatabase = ParseDatabase();
differences = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, differences.Count);
}
public void TableRemovedTest()
{
RunOptions.Current.FullyScripted = true;
ScriptParser currentDatabase = new ScriptParser();
currentDatabase.Parse("CREATE TABLE foo(a int)");
currentDatabase.Parse("CREATE INDEX foo_a ON dbo.foo(a)");
ScriptParser endingDatabase = new ScriptParser();
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(currentDatabase.Database);
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(scripts[0].Type, ScriptType.DropIndex);
Assert.AreEqual(scripts[1].Type, ScriptType.DropTable);
}
public void TableTest()
{
ScriptSet scripts = ScriptManager.LoadScripts("TestData\\TableTest");
Assert.AreEqual(1, scripts.Count);
Assert.AreEqual(ScriptType.Table, scripts[0].Type);
ScriptParser parser = new ScriptParser();
parser.RetrieveParsableObjects(scripts);
Assert.AreEqual(0, scripts.Count);
Assert.AreEqual(1, parser.Database.Tables.Count);
ScriptSet differences = parser.Database.CreateDiffScripts(new Database());
differences.Sort();
Assert.AreEqual(1, differences.Count);
Assert.AreEqual(ScriptType.Table, differences[0].Type);
}
public void ForeignKeyCreateTest()
{
ScriptParser startingDatabase = new ScriptParser();
startingDatabase.Parse("CREATE TABLE foo(a int primary key)");
startingDatabase.Parse("CREATE TABLE bar(a int)");
ScriptParser endingDatabase = new ScriptParser();
endingDatabase.Parse("CREATE TABLE foo(a int primary key)");
endingDatabase.Parse("CREATE TABLE bar(a int foreign key references foo(a))");
endingDatabase.Parse("INSERT INTO foo(a) values('1')");
ScriptSet scripts = endingDatabase.Database.CreateDiffScripts(startingDatabase.Database);
scripts.Sort();
Assert.AreEqual(2, scripts.Count);
Assert.AreEqual(ScriptType.TableData, scripts[0].Type);
Assert.AreEqual(ScriptType.ForeignKey, scripts[1].Type);
}
