private void JoinViaIn(SqlStatement statement, SqlSelect select)
{
var table = GetStatementTable(statement);
var where = GetStatementWhere(statement);
JoinedTableRef = table;
var indexMapping = PrimaryIndexes[0];
var columns = new List <ColumnInfo>();
foreach (var columnInfo in indexMapping.PrimaryIndex.KeyColumns.Keys)
{
var s = (SqlSelect)select.Clone();
foreach (var column in columns)
{
var ex = SqlDml.Equals(s.From.Columns[column.Name], table.Columns[column.Name]);
s.Where = s.Where.IsNullReference() ? ex : SqlDml.And(s.Where, ex);
}
var existingColumns = s.Columns.ToChainedBuffer();
s.Columns.Clear();
var columnToAdd = existingColumns.First(c => c.Name.Equals(columnInfo.Name, StringComparison.Ordinal));
s.Columns.Add(columnToAdd);
var @in = SqlDml.In(SqlDml.TableColumn(table, columnInfo.Name), s);
where = where.IsNullReference() ? @in : SqlDml.And(where, @in);
columns.Add(columnInfo);
}
SetStatementWhere(statement, where);
}
public void SqlSelectCloneTest()
{
SqlTableRef tr1 = SqlDml.TableRef(table1);
SqlTableRef tr2 = SqlDml.TableRef(table2);
SqlSelect s = SqlDml.Select();
s.Distinct = true;
s.Limit = 3;
s.Columns.Add(tr1["ID"]);
s.Columns.Add(tr1["ID"], "ID2");
s.Columns.Add(tr1["ID"] + tr1["ID"], "SUM2");
s.Columns.Add(tr2.Asterisk);
s.From = tr1.InnerJoin(tr2, tr1["ID"] == tr2["ID"]);
s.Where = SqlDml.Like(tr1["Name"], "Marat");
s.Hints.Add(SqlDml.FastFirstRowsHint(10));
SqlSelect sClone = (SqlSelect)s.Clone();
Assert.AreNotEqual(s, sClone);
Assert.AreNotEqual(s.Columns, sClone.Columns);
for (int i = 0, l = s.Columns.Count; i < l; i++)
{
Assert.AreNotEqual(s.Columns[i], sClone.Columns[i]);
}
for (int i = 0, l = s.GroupBy.Count; i < l; i++)
{
Assert.AreNotEqual(s.Columns[i], sClone.Columns[i]);
}
Assert.AreEqual(s.Distinct, sClone.Distinct);
if (s.From == null)
{
Assert.AreEqual(s.From, sClone.From);
}
else
{
Assert.AreNotEqual(s.From, sClone.From);
Assert.AreEqual(s.From.NodeType, sClone.From.NodeType);
}
if (!s.Having.IsNullReference())
{
Assert.AreNotEqual(s.Having, sClone.Having);
Assert.AreEqual(s.Having.NodeType, sClone.Having.NodeType);
}
Assert.AreEqual(s.NodeType, sClone.NodeType);
Assert.IsFalse(s.OrderBy == sClone.OrderBy);
Assert.AreEqual(s.OrderBy.Count, sClone.OrderBy.Count);
for (int i = 0, l = s.OrderBy.Count; i < l; i++)
{
Assert.AreNotEqual(s.OrderBy[i], sClone.OrderBy[i]);
Assert.AreEqual(s.OrderBy[i].Ascending, sClone.OrderBy[i].Ascending);
Assert.AreNotEqual(s.OrderBy[i].Expression, sClone.OrderBy[i].Expression);
Assert.AreEqual(s.OrderBy[i].Position, sClone.OrderBy[i].Position);
}
Assert.AreEqual(s.Limit, sClone.Limit);
if (s.Where != null)
{
Assert.AreNotEqual(s.Where, sClone.Where);
Assert.AreEqual(s.Where.NodeType, sClone.Where.NodeType);
}
s.Where &= tr1[0] > 1200 || tr2[1] != "Marat";
s.OrderBy.Add(tr1["ID"], false);
s.OrderBy.Add(2);
sClone = (SqlSelect)s.Clone();
Assert.AreNotEqual(s, sClone);
Assert.AreNotEqual(s.Columns, sClone.Columns);
for (int i = 0, l = s.Columns.Count; i < l; i++)
{
Assert.AreNotEqual(s.Columns[i], sClone.Columns[i]);
}
for (int i = 0, l = s.GroupBy.Count; i < l; i++)
{
Assert.AreNotEqual(s.Columns[i], sClone.Columns[i]);
}
Assert.AreEqual(s.Distinct, sClone.Distinct);
if (s.From == null)
{
Assert.AreEqual(s.From, sClone.From);
}
else
{
Assert.AreNotEqual(s.From, sClone.From);
Assert.AreEqual(s.From.NodeType, sClone.From.NodeType);
}
if (s.Having != null)
{
Assert.AreNotEqual(s.Having, sClone.Having);
Assert.AreEqual(s.Having.NodeType, sClone.Having.NodeType);
}
Assert.AreEqual(s.NodeType, sClone.NodeType);
Assert.AreNotEqual(s.OrderBy, sClone.OrderBy);
Assert.AreEqual(s.OrderBy.Count, sClone.OrderBy.Count);
for (int i = 0, l = s.OrderBy.Count; i < l; i++)
{
Assert.AreNotEqual(s.OrderBy[i], sClone.OrderBy[i]);
Assert.AreEqual(s.OrderBy[i].Ascending, sClone.OrderBy[i].Ascending);
if (s.OrderBy[i].Expression.IsNullReference())
{
Assert.AreEqual(s.OrderBy[i].Expression, sClone.OrderBy[i].Expression);
}
else
{
Assert.AreNotEqual(s.OrderBy[i].Expression, sClone.OrderBy[i].Expression);
}
Assert.AreEqual(s.OrderBy[i].Position, sClone.OrderBy[i].Position);
}
Assert.AreEqual(s.Limit, sClone.Limit);
if (s.Where != null)
{
Assert.AreNotEqual(s.Where, sClone.Where);
Assert.AreEqual(s.Where.NodeType, sClone.Where.NodeType);
}
Assert.AreEqual(s.Hints.Count, sClone.Hints.Count);
SqlSelect s2 = SqlDml.Select();
SqlQueryRef t = SqlDml.QueryRef(s, "SUBSELECT");
s2.From = t;
s2.Columns.Add(t.Asterisk);
SqlSelect s2Clone = (SqlSelect)s2.Clone();
Assert.AreNotEqual(s2, s2Clone);
Assert.AreEqual(s2.Columns.Count, s2Clone.Columns.Count);
}
