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); }