public string ToSql()
{
var tableConfig = config.GetTable (Table);
var builder = new Sqlizer (tableConfig, config);
var whereClause = Spec.Walk (builder);
var withClause = builder.CommonTableExpressions.Count () == 0 ? "" :
"; WITH " + String.Join (",", builder.CommonTableExpressions) + "\n";
var joinClause = builder.JoinTables.Count () == 0 ? "" :
"\n" + String.Join ("\n", builder.JoinTables);
return String.Format ("{0}SELECT [{1}].* FROM [{1}]{2}\nWHERE {3}",
withClause, tableConfig.Table, joinClause, whereClause);
}
public void Sqlizer_should_return_false_if_anything_goes_wrong()
{
// arrange
var dir = TestFileSystem.CreateTempWorkingDirectory();
TestFileSystem.RemoveDirectory(dir);
var sqlizer = new Sqlizer(Enumerable.Empty<IDirectoryValidationRule>(),
Enumerable.Empty<IPrerequisiteValidationRule>(),
Mock.Of<IScriptsExecutor>(),
Mock.Of<ILogger>());
// act
var result = sqlizer.RunDatabaseScripts(dir);
// assert
result.ShouldBeEquivalentTo(false);
}
public void Sqlizer_should_return_false_if_anything_goes_wrong()
{
// arrange
var dir = TestFileSystem.CreateTempWorkingDirectory();
TestFileSystem.RemoveDirectory(dir);
var sqlizer = new Sqlizer(Enumerable.Empty <IDirectoryValidationRule>(),
Enumerable.Empty <IPrerequisiteValidationRule>(),
Mock.Of <IScriptsExecutor>(),
Mock.Of <ILogger>());
// act
var result = sqlizer.RunDatabaseScripts(dir);
// assert
result.ShouldBeEquivalentTo(false);
}
public void Sqlizer_Should_Order_Files_For_Execution()
{
// arrange
var dir = TestFileSystem.CreateTempWorkingDirectory();
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "01_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "03_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "20_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "02_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "10_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "15_script.sql");
var actualResult = new List <string>();
var scriptExecutor = new Mock <IScriptsExecutor>();
scriptExecutor.Setup(s => s.Execute(It.IsAny <string>()))
.Callback <string>(actualResult.Add);
var sqlizer = new Sqlizer(Enumerable.Empty <IDirectoryValidationRule>(),
Enumerable.Empty <IPrerequisiteValidationRule>(),
scriptExecutor.Object,
Mock.Of <ILogger>());
// act
sqlizer.RunDatabaseScripts(dir);
// assert
actualResult.ShouldBeEquivalentTo(new List <string>
{
Path.Combine(dir, "01_script.sql"),
Path.Combine(dir, "02_script.sql"),
Path.Combine(dir, "03_script.sql"),
Path.Combine(dir, "10_script.sql"),
Path.Combine(dir, "15_script.sql"),
Path.Combine(dir, "20_script.sql")
});
}
public void Sqlizer_Should_Order_Files_For_Execution()
{
// arrange
var dir = TestFileSystem.CreateTempWorkingDirectory();
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "01_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "03_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "20_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "02_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "10_script.sql");
TestFileSystem.CreateFile(baseDirectory: dir, fileName: "15_script.sql");
var actualResult = new List<string>();
var scriptExecutor = new Mock<IScriptsExecutor>();
scriptExecutor.Setup(s => s.Execute(It.IsAny<string>()))
.Callback<string>(actualResult.Add);
var sqlizer = new Sqlizer(Enumerable.Empty<IDirectoryValidationRule>(),
Enumerable.Empty<IPrerequisiteValidationRule>(),
scriptExecutor.Object,
Mock.Of<ILogger>());
// act
sqlizer.RunDatabaseScripts(dir);
// assert
actualResult.ShouldBeEquivalentTo(new List<string>
{
Path.Combine(dir, "01_script.sql"),
Path.Combine(dir, "02_script.sql"),
Path.Combine(dir, "03_script.sql"),
Path.Combine(dir, "10_script.sql"),
Path.Combine(dir, "15_script.sql"),
Path.Combine(dir, "20_script.sql")
});
}
public override BuiltinExpansion ExpandMacro(OracularConfig config, OracularTable parent, OracularTable child, Reference reference, AstNode nestedSpec)
{
var withTable = String.Format ("Annotated{0}{1}", parent.Table, nestedSpec.Id);
var relationship = child.GetRelationshipTo (parent.Table);
if (relationship == null)
{
var message = String.Format ("unable to find relationship from {0} to {1}", child.Table, parent.Table);
throw new OracularException (message);
}
var mainJoin = String.Format ("LEFT JOIN [{0}] ON [{0}].[{1}] = [{2}].[{3}]",
withTable,
parent.Id,
parent.Table,
parent.Id
);
var macroField = String.Format ("{0}{1}{2}", Name, child.Table, nestedSpec.Id);
var builder = new Sqlizer (child, config);
var nestedWhere = nestedSpec == null ? "" : String.Format("\nWHERE {0}",
(invertNested ? nestedSpec.Invert() : nestedSpec).Walk(builder)
);
var nestedJoins = builder.JoinTables.Count() == 0 ? "" : "\n" + String.Join("\n",
builder.JoinTables
);
var nestedQuery = String.Format (@"[{0}] AS (
SELECT DISTINCT [{1}].[{2}], 1 [{3}]
FROM [{1}]
LEFT JOIN [{4}] ON [{4}].[{5}] = [{1}].[{2}]{6}{7}
)", withTable, parent.Table, parent.Id, macroField, child.Table, relationship.Id, nestedJoins, nestedWhere);
var expansion = new BuiltinExpansion ();
expansion.With.AddRange (builder.CommonTableExpressions);
expansion.With.Add (nestedQuery);
expansion.Join.Add (mainJoin);
expansion.Where = String.Format ("[{0}].[{1}]{2}", withTable, macroField, suffix);
return expansion;
}
public void SerializeSpecReferences()
{
var justAnId = new List<FieldConfig>
{
new FieldConfig("Id", null)
};
var idAndBarId = new List<FieldConfig>
{
new FieldConfig("Id", null),
new FieldConfig("BarId", null)
};
var barRelationship = new List<ParentConfig>
{
new ParentConfig("Bar", null, null)
};
var foo = new OracularTable ("Foo", null, barRelationship, idAndBarId);
var tables = new List<OracularTable>
{
foo,
new OracularTable("Bar", null, null, justAnId)
};
var specs = new List<OracularSpec>
{
new OracularSpec("barHasId", "Bar", "Bar.Id != null")
};
var config = new OracularConfig (tables, specs);
var foobarReference = new Reference (new [] { "Foo", "Bar" });
var specReference = new Reference (new [] { "barHasId" });
var macroExpansion = new MacroExpansion (specReference, new [] { foobarReference });
var builder = new Sqlizer (foo, config);
var sql = macroExpansion.Walk (builder);
Assert.AreEqual ("([Foo.Bar].[Id] != NULL)", sql);
Assert.AreEqual (1, builder.JoinTables.Count ());
var joinClause = builder.JoinTables.First();
Assert.AreEqual ("INNER JOIN [Bar] [Foo.Bar] ON [Foo.Bar].[Id] = [Foo].[BarId]", joinClause);
}
public void SerializeReducer(string macro, string prefix, string suffix, string value, string equality)
{
var justAnId = new List<FieldConfig>
{
new FieldConfig("Id", null)
};
var idAndBarId = new List<FieldConfig>
{
new FieldConfig("Id", null),
new FieldConfig("BarId", null)
};
var barRelationship = new List<ParentConfig>
{
new ParentConfig("Bar", null, null)
};
var foo = new OracularTable ("Foo", null, barRelationship, idAndBarId);
var bar = new OracularTable("Bar", null, null, justAnId);
var tables = new List<OracularTable>{ foo, bar };
var specs = new List<OracularSpec> ();
var config = new OracularConfig (tables, specs);
var macroReference = new Reference (new[]{ macro });
var fooReference = new Reference (new[]{ "Foo" });
var idNotNull = new BinaryOperation ("!=",
new Reference (new[]{ "Foo", "Id" }),
new NullLiteral ()
);
var macroExpansion = new MacroExpansion (macroReference, new AstNode[] { fooReference, idNotNull });
var builder = new Sqlizer (bar, config);
var sql = macroExpansion.Walk (builder);
var expected = String.Format ("[AnnotatedBar{0}].[{1}Foo{0}]{2}", idNotNull.Id, prefix, suffix);
Assert.AreEqual (expected, sql);
Assert.AreEqual (1, builder.JoinTables.Count ());
var join = builder.JoinTables.First ();
expected = String.Format ("LEFT JOIN [AnnotatedBar{0}] ON [AnnotatedBar{0}].[Id] = [Bar].[Id]", idNotNull.Id);
Assert.AreEqual (expected, join);
Assert.AreEqual (1, builder.CommonTableExpressions.Count ());
var annotated = builder.CommonTableExpressions.First ();
expected = String.Format (@"[AnnotatedBar{0}] AS (
SELECT DISTINCT [Bar].[Id], {2} [{1}Foo{0}]
FROM [Bar]
LEFT JOIN [Foo] ON [Foo].[BarId] = [Bar].[Id]
WHERE ([Foo].[Id] {3} NULL)
)", idNotNull.Id, prefix, value, equality);
Assert.AreEqual (expected, annotated);
}
public void SerializeParentOnAlias()
{
var justAnId = new List<FieldConfig>
{
new FieldConfig("Id", null)
};
var idAndBarId = new List<FieldConfig>
{
new FieldConfig("Id", null),
new FieldConfig("BarId", null)
};
var barRelationship = new List<ParentConfig>
{
new ParentConfig("Bar", null, null)
};
var foo = new OracularTable ("Foo", null, barRelationship, idAndBarId);
var tables = new List<OracularTable>
{
foo,
new OracularTable("Bar", null, null, justAnId)
};
var specs = new List<OracularSpec> ();
var config = new OracularConfig (tables, specs);
var foobarReference = new Reference (new [] { "Foo", "Bar" });
var builder = new Sqlizer (foo, config, "Alias");
var sql = foobarReference.Walk (builder);
Assert.AreEqual ("[Alias.Bar]", sql);
Assert.AreEqual (1, builder.JoinTables.Count ());
var joinClause = builder.JoinTables.First();
Assert.AreEqual ("INNER JOIN [Bar] [Alias.Bar] ON [Alias.Bar].[Id] = [Alias].[BarId]", joinClause);
}
public void SerializeNestedReducers()
{
var bazFields = new List<FieldConfig>
{
new FieldConfig("Id", null)
};
var barFields = new List<FieldConfig>
{
new FieldConfig("Id", null),
new FieldConfig("BazId", null)
};
var barbazRelationship = new List<ParentConfig>
{
new ParentConfig("Baz", null, null)
};
var fooFields = new List<FieldConfig>
{
new FieldConfig("Id", null),
new FieldConfig("BarId", null),
new FieldConfig("IsQux", FieldType.Boolean)
};
var foobarRelationship = new List<ParentConfig>
{
new ParentConfig("Bar", null, null)
};
var baz = new OracularTable ("Baz", null, null, bazFields);
var tables = new List<OracularTable>
{
new OracularTable ("Foo", null, foobarRelationship, fooFields),
new OracularTable("Bar", null, barbazRelationship, barFields),
baz
};
var specs = new List<OracularSpec> ();
var config = new OracularConfig (tables, specs);
var fooIsQuxNode = new Reference (new [] { "Foo", "IsQux" });
var anyFooNode = new MacroExpansion (
new Reference (new [] { "ANY" }),
new [] {
new Reference(new [] { "Foo" }),
fooIsQuxNode
}
);
var anyBarNode = new MacroExpansion (
new Reference(new [] { "ANY" }),
new AstNode[] {
new Reference(new [] { "Bar" }),
anyFooNode
}
);
var builder = new Sqlizer (baz, config);
var whereClause = anyBarNode.Walk (builder);
var expected = String.Format ("[AnnotatedBaz{0}].[AnyBar{0}] = 1", anyFooNode.Id);
Assert.AreEqual (expected, whereClause);
Assert.AreEqual (1, builder.JoinTables.Count ());
var join = builder.JoinTables.First ();
expected = String.Format ("LEFT JOIN [AnnotatedBaz{0}] ON [AnnotatedBaz{0}].[Id] = [Baz].[Id]", anyFooNode.Id);
Assert.AreEqual (expected, join);
Assert.AreEqual (2, builder.CommonTableExpressions.Count ());
var annotatedBar = builder.CommonTableExpressions.First ();
expected = String.Format (@"[AnnotatedBar{0}] AS (
SELECT DISTINCT [Bar].[Id], 1 [AnyFoo{0}]
FROM [Bar]
LEFT JOIN [Foo] ON [Foo].[BarId] = [Bar].[Id]
WHERE [Foo].[IsQux] = 1
)", fooIsQuxNode.Id);
Assert.AreEqual (expected, annotatedBar);
var annotatedBaz = builder.CommonTableExpressions.Last ();
expected = String.Format(@"[AnnotatedBaz{0}] AS (
SELECT DISTINCT [Baz].[Id], 1 [AnyBar{0}]
FROM [Baz]
LEFT JOIN [Bar] ON [Bar].[BazId] = [Baz].[Id]
LEFT JOIN [AnnotatedBar{1}] ON [AnnotatedBar{1}].[Id] = [Bar].[Id]
WHERE [AnnotatedBar{1}].[AnyFoo{1}] = 1
)", anyFooNode.Id, fooIsQuxNode.Id);
Assert.AreEqual (expected, annotatedBaz);
}
public void SerializeGrandparentTableOnAlias()
{
var justAnId = new List<FieldConfig>
{
new FieldConfig("Id", null)
};
var idAndBazId = new List<FieldConfig>
{
new FieldConfig("Id", null),
new FieldConfig("BazId", null)
};
var bazRelationship = new List<ParentConfig>
{
new ParentConfig("Baz", null, null)
};
var idAndBarId = new List<FieldConfig>
{
new FieldConfig("Id", null),
new FieldConfig("BarId", null)
};
var barRelationship = new List<ParentConfig>
{
new ParentConfig("Bar", null, null)
};
var foo = new OracularTable ("Foo", null, barRelationship, idAndBarId);
var tables = new List<OracularTable>
{
foo,
new OracularTable("Bar", null, bazRelationship, idAndBazId),
new OracularTable("Baz", null, null, justAnId)
};
var specs = new List<OracularSpec> ();
var config = new OracularConfig (tables, specs);
var foobarReference = new Reference (new [] { "Foo", "Bar", "Baz" });
var builder = new Sqlizer (foo, config, "Alias");
var sql = foobarReference.Walk (builder);
Assert.AreEqual ("[Alias.Bar.Baz]", sql);
Assert.AreEqual (2, builder.JoinTables.Count ());
}