public void UnionTest()
{
SqlSelect s1 = SqlDml.Select(SqlDml.TableRef(Catalog.Schemas["main"].Tables["track"]));
s1.Columns.Add(s1.From["TrackId"]);
SqlSelect s2 = SqlDml.Select(SqlDml.TableRef(Catalog.Schemas["main"].Tables["track"]));
s2.Columns.Add(s2.From["TrackId"]);
SqlSelect s3 = SqlDml.Select(SqlDml.TableRef(Catalog.Schemas["main"].Tables["track"]));
s3.Columns.Add(s3.From["TrackId"]);
Console.WriteLine(sqlDriver.Compile(s1.Union(s2)).GetCommandText());
Console.WriteLine(sqlDriver.Compile(s1.Union(s2).Union(s3)).GetCommandText());
Console.WriteLine(sqlDriver.Compile(s1.Union(s2.Union(s3))).GetCommandText());
Console.WriteLine(sqlDriver.Compile(SqlDml.Union(s1, s2)).GetCommandText());
Console.WriteLine(sqlDriver.Compile(SqlDml.Union(s1, s1.Union(s2))).GetCommandText());
Console.WriteLine(sqlDriver.Compile(SqlDml.Union(s1.Union(s2), s1)).GetCommandText());
Console.WriteLine(sqlDriver.Compile(SqlDml.Union(s1.Union(s2), s1.Union(s2))).GetCommandText());
s3.Where = SqlDml.In(1, s1.Union(s2));
Console.WriteLine(sqlDriver.Compile(s3).GetCommandText());
SqlQueryRef qr = SqlDml.QueryRef(s1.Union(s2), "qr");
Assert.Greater(qr.Columns.Count, 0);
}
public override string Translate(SqlCompilerContext context, SqlUnary node, NodeSection section)
{
//substitute UNIQUE predicate with a more complex EXISTS predicate,
//because UNIQUE is not supported
if (node.NodeType == SqlNodeType.Unique)
{
var origSubselect = node.Operand as SqlSubQuery;
if (origSubselect != null)
{
SqlQueryRef origQuery = SqlDml.QueryRef(origSubselect.Query);
SqlSelect existsOp = SqlDml.Select(origQuery);
existsOp.Columns.Add(1);
existsOp.Where = true;
foreach (SqlColumn col in origQuery.Columns)
{
existsOp.Where = existsOp.Where && SqlDml.IsNotNull(col);
existsOp.GroupBy.Add(col);
}
existsOp.Having = SqlDml.Count(SqlDml.Asterisk) > 1;
existsOp.Limit = 1;
node.ReplaceWith(SqlDml.Not(SqlDml.Exists(existsOp)));
}
}
return(base.Translate(context, node, section));
}
private SqlSelect CreateSelectForUniqueMatchNoneOrFull(SqlRow row, SqlSelect query)
{
/*
* select exists(select 1
* from (original subquery) x
* where x.a1=r.a1 and x.a2=r.a2
* group by x.a1, x.a2
* having count(*)=1
* ) }
*/
SqlSelect q0 = SqlDml.Select();
{
SqlQueryRef originalQuery = SqlDml.QueryRef(query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(1);
q1.Where = true; //initially true
{
int index = 0;
foreach (SqlColumn col in originalQuery.Columns)
{
q1.Where = q1.Where && col == row[index];
q1.GroupBy.Add(col);
index++;
}
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
q0.Columns.Add(SqlDml.Exists(q1));
}
return(q0);
}
public override string Translate(SqlCompilerContext context, SqlQueryRef node, TableSection section)
{
if (context.GetTraversalPath().Any(n => n.NodeType == SqlNodeType.Insert))
{
return(string.Empty);
}
return(base.Translate(context, node, section));
}
public void Visit(SqlQueryRef node)
{
foreach (var column in node.Columns)
{
Visit(column);
}
if (node.Query != null)
{
Visit(node.Query);
}
}
public virtual void Visit(SqlQueryRef node)
{
VisitInternal(node.Asterisk);
foreach (SqlTableColumn column in node.Columns)
{
VisitInternal(column);
}
VisitInternal(node.Query);
foreach (SqlTable table in node)
{
VisitInternal(table);
}
}
public void Test201()
{
string nativeSql = "SELECT a.f FROM ((SELECT 1 as f UNION SELECT 2) EXCEPT (SELECT 3 UNION SELECT 4)) a";
SqlSelect s1 = SqlDml.Select();
SqlSelect s2 = SqlDml.Select();
SqlSelect s3 = SqlDml.Select();
SqlSelect s4 = SqlDml.Select();
SqlSelect select;
s1.Columns.Add(1, "f");
s2.Columns.Add(2);
s3.Columns.Add(3);
s4.Columns.Add(4);
SqlQueryRef qr = SqlDml.QueryRef(s1.Union(s2).Except(s3.Union(s4)), "a");
select = SqlDml.Select(qr);
select.Columns.Add(qr["f"]);
Assert.Throws <NotSupportedException>(() => Assert.IsTrue(CompareExecuteNonQuery(nativeSql, select)));
}
private void JoinViaJoin(SqlStatement statement, SqlSelect @select)
{
PrimaryIndexMapping indexMapping = PrimaryIndexes[0];
SqlTableRef left = SqlDml.TableRef(indexMapping.Table);
SqlQueryRef right = SqlDml.QueryRef(@select);
SqlExpression joinExpression = null;
for (int i = 0; i < indexMapping.PrimaryIndex.KeyColumns.Count; i++)
{
SqlBinary binary = (left.Columns[i] == right.Columns[i]);
if (joinExpression.IsNullReference())
{
joinExpression = binary;
}
else
{
joinExpression &= binary;
}
}
JoinedTableRef = left;
SqlJoinedTable joinedTable = left.InnerJoin(right, joinExpression);
SetStatementFrom(statement, joinedTable);
}
public void SqlQueryRefCloneTest()
{
SqlTableRef t = SqlDml.TableRef(table1);
SqlSelect s = SqlDml.Select(t);
s.Columns.Add(t.Columns[0]);
SqlQueryRef qr = SqlDml.QueryRef(s);
SqlQueryRef qrClone = (SqlQueryRef)qr.Clone();
Assert.AreNotEqual(qr, qrClone);
Assert.AreNotEqual(qr.Columns, qrClone.Columns);
Assert.AreEqual(qr.NodeType, qrClone.NodeType);
Assert.AreEqual(qr.Name, qrClone.Name);
Assert.AreNotEqual(qr.Query, qrClone.Query);
Assert.AreEqual(qr.Columns.Count, qrClone.Columns.Count);
for (int i = 0, l = qr.Columns.Count; i < l; i++)
{
Assert.AreNotEqual(qr.Columns[i], qrClone.Columns[i]);
Assert.AreEqual(qr.Columns[i].NodeType, qrClone.Columns[i].NodeType);
Assert.AreEqual(qr.Columns[i].GetType(), qrClone.Columns[i].GetType());
}
}
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);
}
public override string Translate(SqlCompilerContext context, SqlMatch node, MatchSection section)
{
switch (section)
{
case MatchSection.Entry:
//MATCH is not supported by PostgreSQL, we need some workaround
SqlRow row = node.Value as SqlRow;
if (row != null)
{
SqlSelect finalQuery = SqlDml.Select();
finalQuery.Columns.Add(5);
switch (node.MatchType)
{
#region SIMPLE
case SqlMatchType.None: {
bool existsNull = false;
SqlCase c = SqlDml.Case();
{
bool subQueryNeeded = true;
//if any of the row elements is NULL then true
if (row.Count > 0)
{
bool allLiteral = true; //if true then there is no NULL element
SqlExpression when1 = null;
for (int i = 0; i < row.Count; i++)
{
bool elementIsNotLiteral = row[i].NodeType != SqlNodeType.Literal;
//if the row element is the NULL value
if (row[i].NodeType == SqlNodeType.Null)
{
existsNull = true;
break;
}
if (allLiteral && elementIsNotLiteral)
{
allLiteral = false;
}
if (elementIsNotLiteral)
{
if (when1 == null)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 || SqlDml.IsNull(row[i]);
}
}
}
if (existsNull)
{
//Some row element is the NULL value, MATCH result is true
subQueryNeeded = false;
}
else if (allLiteral)
{
//No row element is the NULL value
subQueryNeeded = true;
}
else //(!whenNotNeeded)
{
//Check if any row element is NULL
c.Add(when1 == null ? true : when1, true);
subQueryNeeded = true;
}
}
//find row in subquery
if (subQueryNeeded)
{
SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(1);
{
SqlTableColumnCollection columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
if (i == 0)
{
where = columns[i] == row[i];
}
else
{
where = where && columns[i] == row[i];
}
if (node.Unique)
{
q1.GroupBy.Add(columns[i]);
}
}
q1.Where = where;
if (node.Unique)
{
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
}
//c.Add(Sql.Exists(q1), true);
c.Else = SqlDml.Exists(q1);
}
}
if (c.Else == null)
{
c.Else = false;
}
if (existsNull)
{
finalQuery.Where = null;
}
else if (c.Count > 0)
{
finalQuery.Where = c;
}
else
{
finalQuery.Where = c.Else;
}
break;
}
#endregion
#region FULL
case SqlMatchType.Full: {
SqlCase c1 = SqlDml.Case();
{
bool noMoreWhenNeeded = false;
bool allNull = true;
SqlExpression when1 = true;
//if all row elements are null then true
if (row.Count > 0)
{
bool whenNotNeeded = false;
for (int i = 0; i < row.Count; i++)
{
//if any row element is surely not the NULL value
if (row[i].NodeType == SqlNodeType.Literal)
{
whenNotNeeded = true;
break;
}
if (allNull && row[i].NodeType != SqlNodeType.Null)
{
allNull = false;
}
if (i == 0)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 && SqlDml.IsNull(row[i]);
}
}
if (allNull)
{
when1 = true;
}
if (!whenNotNeeded)
{
c1.Add(when1, true);
}
}
if (!noMoreWhenNeeded)
{
bool whenNotNeeded = false;
bool allLiteral = true;
SqlExpression when2 = true;
//if no row elements are null then subcase
for (int i = 0; i < row.Count; i++)
{
if (row[i].NodeType == SqlNodeType.Null)
{
whenNotNeeded = true;
when2 = false;
break;
}
if (allLiteral && row[i].NodeType != SqlNodeType.Literal)
{
allLiteral = false;
}
if (i == 0)
{
when2 = SqlDml.IsNotNull(row[i]);
}
else
{
when2 = when2 && SqlDml.IsNotNull(row[i]);
}
}
if (allLiteral)
{
when2 = true;
}
if (!whenNotNeeded)
{
//find row in subquery
SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(1);
{
SqlTableColumnCollection columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
if (i == 0)
{
where = columns[i] == row[i];
}
else
{
where = where && columns[i] == row[i];
}
if (node.Unique)
{
q1.GroupBy.Add(columns[i]);
}
}
q1.Where = where;
if (node.Unique)
{
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
}
c1.Add(when2, SqlDml.Exists(q1));
}
}
//else false
c1.Else = false;
}
if (c1.Count > 0)
{
finalQuery.Where = c1;
}
else
{
finalQuery.Where = false;
}
break;
}
#endregion
#region PARTIAL
case SqlMatchType.Partial: {
bool allNull = true;
SqlCase c1 = SqlDml.Case();
{
SqlExpression when1 = true;
//if all row elements are null then true
if (row.Count > 0)
{
bool whenNotNeeded = false;
for (int i = 0; i < row.Count; i++)
{
//if any row element is surely not the NULL value
if (row[i].NodeType == SqlNodeType.Literal)
{
allNull = false;
whenNotNeeded = true;
break;
}
if (allNull && row[i].NodeType != SqlNodeType.Null)
{
allNull = false;
}
if (i == 0)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 && SqlDml.IsNull(row[i]);
}
}
if (allNull)
{
when1 = true;
}
if (!whenNotNeeded)
{
c1.Add(when1, true);
}
}
//otherwise
if (!allNull)
{
//find row in subquery
SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(8);
{
SqlTableColumnCollection columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
//if row[i] would be NULL then c3 would result in true,
if (row[i].NodeType != SqlNodeType.Null)
{
SqlCase c3 = SqlDml.Case();
c3.Add(SqlDml.IsNull(row[i]), true);
c3.Else = row[i] == columns[i];
if (where == null)
{
where = c3;
}
else
{
where = where && c3;
}
}
if (node.Unique)
{
SqlCase c4 = SqlDml.Case();
c4.Add(SqlDml.IsNull(row[i]), 0);
c4.Else = columns[i];
q1.GroupBy.Add(c4);
}
}
q1.Where = where;
if (node.Unique)
{
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
}
c1.Else = SqlDml.Exists(q1);
}
}
if (c1.Else == null)
{
c1.Else = false;
}
if (allNull)
{
finalQuery.Where = null;
}
else if (c1.Count > 0)
{
finalQuery.Where = c1;
}
else
{
finalQuery.Where = c1.Else;
}
}
break;
#endregion
}
SqlMatch newNode = SqlDml.Match(SqlDml.Row(), SqlDml.SubQuery(finalQuery).Query, node.Unique, node.MatchType);
node.ReplaceWith(newNode);
return("EXISTS(SELECT '");
}
else
{
throw new InvalidOperationException(Strings.ExSqlMatchValueMustBeAnSqlRowInstance);
}
case MatchSection.Specification:
return("' WHERE EXISTS");
case MatchSection.Exit:
return(")");
}
return(string.Empty);
}
