public void ScriptTwiceTest()
{
ScriptSet set = new ScriptSet();
Script a = new Script("Foo", "Foo", ScriptType.Unknown);
set.Add(a);
Script b = new Script("Foo", "Foo", ScriptType.Unknown);
set.Add(b);
}
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 find_all_scripts_if_just_referring_to_script_files()
{
var theSet = new ScriptSet();
theSet.Add("a");
theSet.Add("b");
theSet.Add("c");
theSet.FindScripts(theGraph);
theSet.AllScripts().Select(x => x.Name).ShouldHaveTheSameElementsAs("a", "b", "c");
}
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 find_all_scripts_from_a_mix_of_files_and_sets()
{
theGraph.AddToSet("1", "A");
theGraph.AddToSet("1", "B");
theGraph.AddToSet("1", "C");
var theSet = new ScriptSet();
theSet.Add("1");
theSet.Add("D");
theGraph.ScriptSetFor("1").FindScripts(theGraph);
theSet.FindScripts(theGraph);
theSet.AllScripts().Select(x => x.Name).ShouldHaveTheSameElementsAs("A", "B", "C", "D");
}
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 find_all_scripts_2_deep_child_sets_with_files()
{
theGraph.AddToSet("1", "A");
theGraph.AddToSet("1", "B");
theGraph.AddToSet("1", "2");
theGraph.AddToSet("2", "C");
theGraph.AddToSet("2", "D");
var theSet = new ScriptSet();
theSet.Add("1");
theSet.Add("E");
theGraph.ScriptSetFor("1").FindScripts(theGraph);
theGraph.ScriptSetFor("2").FindScripts(theGraph);
theSet.FindScripts(theGraph);
theSet.AllScripts().Select(x => x.Name).ShouldHaveTheSameElementsAs("A", "B", "C", "D", "E");
}
public void UnparsableTest()
{
ScriptSet scripts = new ScriptSet();
scripts.Add(new Script("SELECT * FROM dbo.Foo", "Foo.sql", ScriptType.Unknown));
ScriptParser parser = new ScriptParser();
parser.RetrieveParsableObjects(scripts);
Assert.AreEqual(1, scripts.Count);
}
public void should_not_return_duplicates()
{
theGraph.AddToSet("1", "A");
theGraph.AddToSet("1", "B");
theGraph.AddToSet("1", "2");
theGraph.AddToSet("2", "C");
theGraph.AddToSet("2", "D");
var theSet = new ScriptSet();
theSet.Add("1");
theSet.Add("2");
theSet.Add("E");
theGraph.ScriptSetFor("1").FindScripts(theGraph);
theGraph.ScriptSetFor("2").FindScripts(theGraph);
theSet.FindScripts(theGraph);
theSet.AllScripts().Select(x => x.Name).ShouldHaveTheSameElementsAs("A", "B", "C", "D", "E");
}
public void UseTest()
{
//This needs to have a type, otherwise we don't really invoke the parser
ScriptSet scripts = new ScriptSet();
scripts.Add(new Script(@"USE master", "Foo.sql", ScriptType.StoredProc));
ScriptParser parser = new ScriptParser();
parser.RetrieveParsableObjects(scripts);
Assert.AreEqual(0, scripts.Count);
}
public void FalseDependencyTest()
{
//This behavior isn't ideal, but we don't want it to crash
ScriptSet set = new ScriptSet();
Script a = new Script("CREATE TABLE Foo(Bar int)", "Foo", ScriptType.Table);
set.Add(a);
Script b = new Script("CREATE TABLE Bar(Foo varchar(10)", "Bar", ScriptType.Table);
set.Add(b);
TestLogger logger = (TestLogger)RunOptions.Current.Logger;
Assert.IsEmpty(logger.messages);
set.Sort();
Assert.AreEqual(1, logger.messages.Count);
Assert.AreEqual("Warning: [dbo].[Bar] and [dbo].[Foo] both seem to refer to each other", logger.messages[0]);
Assert.AreEqual(OutputLevel.Differences, logger.levels[0]);
}
/// <summary>
/// Loads the target database
/// </summary>
/// <param name="referenceParser">Reference Database</param>
/// <returns>The parsed target database</returns>
private static ScriptParser LoadTargetDatabase(ScriptParser referenceParser)
{
ScriptSet targetScripts = new ScriptSet();
ScriptParser targetParser = new ScriptParser();
foreach (ConnectionInfo connection in RunOptions.Current.Connections.Targets)
{
switch (connection.Type)
{
case ConnectionType.Database:
ScriptManager.ParseDatabase(targetParser, connection.Path);
if (referenceParser != null)
{
List <Name> dataTables = referenceParser.Database.GetTablesWithData();
dataTables.AddRange(RunOptions.Current.LoadData);
ScriptManager.GetData(targetParser.Database, dataTables, connection.Path);
}
else
{
ScriptManager.GetData(targetParser.Database, RunOptions.Current.LoadData, connection.Path);
}
break;
case ConnectionType.Directory:
targetScripts.Add(ScriptManager.LoadScripts(connection.Path));
targetParser.RetrieveParsableObjects(targetScripts);
break;
case ConnectionType.File:
targetScripts.Add(ScriptManager.LoadScript(connection.Path));
targetParser.RetrieveParsableObjects(targetScripts);
break;
default:
throw new Exception(connection.Type.ToString());
}
}
return(targetParser);
}
public void find_all_scripts_if_the_set_refers_to_another_set()
{
theGraph.AddToSet("1", "A");
theGraph.AddToSet("1", "B");
theGraph.AddToSet("1", "C");
var theSet = new ScriptSet();
theSet.Add("1");
theGraph.ScriptSetFor("1").FindScripts(theGraph);
theSet.FindScripts(theGraph);
theSet.AllScripts().Select(x => x.Name).ShouldHaveTheSameElementsAs("A", "B", "C");
}
public void should_be_after_bouncing_through_a_set()
{
var s1 = new Script("1");
var s2 = new Script("2");
var s3 = new Script("3");
var set = new ScriptSet();
set.Add(s1);
s2.AddDependency(set);
s3.AddDependency(s2);
s3.MustBeAfter(s1).ShouldBeTrue();
s3.MustBeAfter(s2).ShouldBeTrue();
}
public void GrantWithPrincipalTest()
{
ScriptSet scripts = new ScriptSet();
scripts.Add(new Script(@"CREATE TABLE dbo.Foo( x int )
GO
GRANT SELECT ON dbo.Foo to PUBLIC as dbo",
"Foo.sql", ScriptType.Unknown));
ScriptParser parser = new ScriptParser();
parser.RetrieveParsableObjects(scripts);
Assert.AreEqual(0, scripts.Count);
Assert.AreEqual(1, parser.Database.Tables.Count);
Assert.AreEqual(0, ((TestLogger)RunOptions.Current.Logger).messages.Count);
}
/// <summary>
/// Gets the differences between this set of table data and another.
/// </summary>
/// <param name="other">The other set of table data.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="scripts">A set of scripts to fill with the differences.</param>
/// <returns>True if the two sets are different; otherwise false.</returns>
private bool GetDifferences(TableData other, Name tableName, ScriptSet scripts)
{
if (Count == 0)
{
return(false);
}
if (other == null)
{
other = new TableData(Name);
}
StringBuilder addScript = new StringBuilder();
StringBuilder deleteScript = new StringBuilder();
List <TableRow> testing = new List <TableRow>(this);
//this may not be the most efficient algorithm, but we have no index
foreach (TableRow existing in other)
{
TableRow found = null;
foreach (TableRow adding in testing)
{
if (existing.Equals(adding))
{
found = adding;
break;
}
}
if (found == null)
{
if (scripts == null)
{
return(true);
}
existing.GenerateDelete(deleteScript, tableName);
}
else
{
testing.Remove(found);
}
}
foreach (TableRow adding in testing)
{
if (scripts == null)
{
return(true);
}
adding.GenerateInsert(addScript, tableName);
}
if (addScript.Length > 0)
{
scripts.Add(new Script(addScript.ToString(), tableName.Unescaped + "Data", ScriptType.TableData));
}
if (deleteScript.Length > 0)
{
scripts.Add(new Script(deleteScript.ToString(), tableName + "Data", ScriptType.TableRemoveData));
}
return(scripts != null && scripts.Count > 0);
}
/// <summary>
/// Generates the scripts required to turn another <see cref="Database"/> into a copy of this one.
/// </summary>
/// <param name="other">Another <see cref="Database"/>.</param>
/// <param name="differences">The differences between these databases.</param>
/// <returns></returns>
public ScriptSet CreateDiffScripts(Database other, DifferenceSet differences)
{
ScriptSet scripts = new ScriptSet();
foreach (Difference difference in differences)
{
Item adding = null;
Item removing = null;
switch (difference.DifferenceType)
{
case DifferenceType.Created:
adding = this[difference.Item];
scripts.Add(adding.GenerateCreateScript());
scripts.Add(Permissions.GenerateCreateScript(adding));
break;
case DifferenceType.CreatedPermission:
adding = this[difference.Item];
scripts.Add(Permissions.GenerateCreateScript(adding == null ? other[difference.Item] : adding));
break;
case DifferenceType.Dependency:
case DifferenceType.Modified:
adding = this[difference.Item];
removing = other[difference.Item];
if (adding is Table && !((Table)adding).IsType)
{
scripts.Add(((Table)removing).GenerateSaveScript());
scripts.Add(((Table)adding).GenerateCreateScript());
scripts.Add(((Table)adding).GenerateRestoreScript(((Table)removing)));
}
else
{
scripts.Add(removing.GenerateDropScript());
if (adding != null)
{
scripts.Add(adding.GenerateCreateScript());
}
}
if (adding != null)
{
scripts.Add(Permissions.GenerateCreateScript(adding));
}
break;
case DifferenceType.Removed:
removing = other[difference.Item];
if (removing is Table)
{
if (RunOptions.Current.FullyScripted)
{
scripts.Add(removing.GenerateDropScript());
}
}
else
{
scripts.Add(removing.GenerateDropScript());
}
break;
case DifferenceType.TableData:
TableData newData = Tables[difference.ParentItem].Data;
Table oldTable = other.Tables[difference.ParentItem];
TableData oldData = oldTable == null ? null : oldTable.Data;
scripts.Add(newData.GetDifferenceScript(oldData, difference.ParentItem));
break;
default:
throw new ApplicationException(difference.DifferenceType.ToString());
}
}
return(scripts);
}
/// <summary>
/// The main functionality of the tool
/// </summary>
public void DoUpdate()
{
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 any reference database
ScriptSet referenceScripts = new ScriptSet();
ScriptParser referenceParser = new ScriptParser();
foreach (ConnectionInfo connection in RunOptions.Current.Connections.References)
{
switch (connection.Type)
{
case ConnectionType.Database:
ScriptManager.ParseDatabase(referenceParser, connection.Path);
ScriptManager.GetData(referenceParser.Database, RunOptions.Current.LoadData, connection.Path);
break;
case ConnectionType.Directory:
referenceScripts.Add(ScriptManager.LoadScripts(connection.Path));
referenceParser.RetrieveParsableObjects(referenceScripts);
break;
case ConnectionType.File:
referenceScripts.Add(ScriptManager.LoadScript(connection.Path));
referenceParser.RetrieveParsableObjects(referenceScripts);
break;
default:
throw new Exception(connection.Type.ToString());
}
}
//Load the target database
ScriptParser targetParser = LoadTargetDatabase(referenceParser);
//generate new scripts
RunOptions.Current.Logger.Log("Calculating Differences", OutputLevel.Reads);
DifferenceSet differences = null;
if (referenceParser.Database.IsEmpty)
{
referenceScripts = targetParser.Database.CreateDiffScripts(new Database());
}
else
{
differences = referenceParser.Database.GetDifferences(targetParser.Database);
if (targetParser.Database.IsEmpty && differences.Count > 0)
{
Console.WriteLine("Creating database schema");
}
else
{
differences.Write(Console.Out);
}
referenceScripts.Add(referenceParser.Database.CreateDiffScripts(targetParser.Database, differences));
}
//Make sure everything runs in the right order
referenceScripts.Sort();
//store scripts if requested
if (RunOptions.Current.ScriptOutputDirectory != "")
{
ScriptManager.WriteScripts(referenceScripts, differences);
}
if (!string.IsNullOrEmpty(RunOptions.Current.DiffFile))
{
ScriptManager.WriteDifferenceSummary(referenceParser.Database, targetParser.Database, differences);
}
//run the scripts
if (!RunOptions.Current.Test && RunOptions.Current.Connections.References.Count > 0)
{
if (RunOptions.Current.Connections.Targets.Count > 1)
{
throw new ApplicationException("Updating multiple targets is not supported");
}
ScriptManager.ExecuteScripts(referenceScripts, RunOptions.Current.Connections.Targets[0].Path);
}
}
catch (Exception ex)
{
Exit("Error: " + ex.Message, false);
}
Exit("Update Successful", true);
}
/// <summary>
/// Generate create scripts for all elements in the database
/// </summary>
/// <returns></returns>
public ScriptSet CreateScripts()
{
ScriptSet scripts = new ScriptSet();
foreach (Table table in Tables)
{
if (RunOptions.Current.WriteObjects.Count > 0 && !RunOptions.Current.WriteObjects.Contains(table.Name))
{
continue;
}
Script tableScript = table.GenerateCreateScript();
scripts.Add(tableScript);
foreach (Index index in table.Indexes)
{
tableScript.Append(index.GenerateCreateScript());
}
tableScript.Append(table.Permissions.GenerateCreateScript(table));
foreach (Constraint constraint in Constraints)
{
if (constraint.ConstrainedTable == table.Name)
{
tableScript.Append(constraint.GenerateCreateScript());
}
}
foreach (FulltextIndex index in FulltextIndexes)
{
if (index.Table == table.Name)
{
tableScript.Append(index.GenerateCreateScript());
}
}
if (table.Data.Count > 0)
{
scripts.Add(table.Data.GetDifferenceScript(null, table.Name));
}
}
foreach (View view in Views)
{
if (RunOptions.Current.WriteObjects.Count > 0 && !RunOptions.Current.WriteObjects.Contains(view.Name))
{
continue;
}
Script viewScript = view.GenerateCreateScript();
scripts.Add(viewScript);
foreach (Index index in view.Indexes)
{
viewScript.Append(index.GenerateCreateScript());
}
viewScript.Append(view.Permissions.GenerateCreateScript(view));
foreach (Constraint constraint in Constraints)
{
if (constraint.ConstrainedTable == view.Name)
{
viewScript.Append(constraint.GenerateCreateScript());
}
}
foreach (FulltextIndex index in FulltextIndexes)
{
if (index.Table == view.Name)
{
viewScript.Append(index.GenerateCreateScript());
}
}
}
foreach (Function function in Functions)
{
if (RunOptions.Current.WriteObjects.Count > 0 && !RunOptions.Current.WriteObjects.Contains(function.Name))
{
continue;
}
Script functionScript = function.GenerateCreateScript();
scripts.Add(functionScript);
functionScript.Append(function.Permissions.GenerateCreateScript(function));
}
foreach (Procedure procedure in Procedures)
{
if (RunOptions.Current.WriteObjects.Count > 0 && !RunOptions.Current.WriteObjects.Contains(procedure.Name))
{
continue;
}
Script procedureScript = procedure.GenerateCreateScript();
scripts.Add(procedureScript);
procedureScript.Append(procedure.Permissions.GenerateCreateScript(procedure));
}
foreach (Trigger trigger in Triggers)
{
if (RunOptions.Current.WriteObjects.Count > 0 && !RunOptions.Current.WriteObjects.Contains(trigger.Name))
{
continue;
}
Script triggerScript = trigger.GenerateCreateScript();
scripts.Add(triggerScript);
triggerScript.Append(trigger.Permissions.GenerateCreateScript(trigger));
foreach (TriggerOrder order in TriggerOrder)
{
if (order.Trigger == trigger.Name)
{
triggerScript.Append(order.GenerateCreateScript());
}
}
}
foreach (FulltextCatalog catalog in FulltextCatalogs)
{
if (RunOptions.Current.WriteObjects.Count > 0 && !RunOptions.Current.WriteObjects.Contains(catalog.Name))
{
continue;
}
Script catalogScript = catalog.GenerateCreateScript();
scripts.Add(catalogScript);
}
return(scripts);
}
public void TableUpdateTest()
{
ScriptSet initScripts = new ScriptSet();
initScripts.Add(new Script("CREATE TABLE foo( a varchar(10), b varchar(10))", "Foo", ScriptType.Table));
initScripts.Add(new Script(@"INSERT INTO foo(a,b) VALUES('X', 'Y')
INSERT INTO foo(a,b) VALUES('A','B')", "FooData", ScriptType.TableData));
ExecuteScripts(initScripts);
ScriptSet updateScripts = new ScriptSet();
updateScripts.Add(new Script("CREATE TABLE foo( a varchar(10))", "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')", "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(5, updateScripts.Count);
updateScripts.Sort();
Assert.AreEqual(@"DELETE FROM [dbo].[foo]
WHERE
[a] = 'X'
AND [b] = 'Y'
DELETE FROM [dbo].[foo]
WHERE
[a] = 'A'
AND [b] = 'B'
", updateScripts[0].Text);
Assert.AreEqual(ScriptType.TableRemoveData, updateScripts[0].Type);
Assert.AreEqual(@"EXEC sp_rename '[dbo].[foo]', 'Tmp__foo', 'OBJECT'", updateScripts[1].Text);
Assert.AreEqual(ScriptType.TableSaveData, updateScripts[1].Type);
Assert.AreEqual(@"CREATE TABLE [dbo].[foo](
[a] [varchar](10) NULL
)
GO", updateScripts[2].Text);
Assert.AreEqual(ScriptType.Table, updateScripts[2].Type);
Assert.AreEqual(@"INSERT INTO [dbo].[foo]([a])
VALUES('X')
INSERT INTO [dbo].[foo]([a])
VALUES('Y')
INSERT INTO [dbo].[foo]([a])
VALUES('Z')
INSERT INTO [dbo].[foo]([a])
VALUES('yabba')
", updateScripts[3].Text);
Assert.AreEqual(ScriptType.TableData, updateScripts[3].Type);
ExecuteScripts(updateScripts);
ScriptParser currentDatabase = ParseDatabase();
GetData(currentDatabase.Database, parsedScripts.Database.GetTablesWithData());
ScriptSet difference = parsedScripts.Database.CreateDiffScripts(currentDatabase.Database);
Assert.AreEqual(0, difference.Count, RunOptions.Current.Logger.ToString());
}
