public void SerializeBooleanField()
{
var idAndBoolean = new List<FieldConfig>
{
new FieldConfig("Id", null),
new FieldConfig("IsBaz", FieldType.Boolean)
};
var foobar = new OracularTable ("Foobar", null, null, idAndBoolean);
var tables = new List<OracularTable>
{
foobar
};
var specs = new List<OracularSpec> ();
var config = new OracularConfig (tables, specs);
var foobarReference = new Reference (new [] { "Foobar", "IsBaz" });
var sql = foobarReference.Walk (new Sqlizer (foobar, config));
Assert.AreEqual ("[Foobar].[IsBaz] = 1", sql);
}
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 ());
}
