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 void ModifyQueryTest()
{
using (var session = Domain.OpenSession())
using (var tx = session.OpenTransaction()) {
var builder = session.Services.Get <QueryBuilder>();
Assert.That(builder, Is.Not.Null);
var query = Query.All <CheckEntity>();
var translated = builder.TranslateQuery(query);
Assert.That(translated, Is.Not.Null);
Assert.That(translated.Query, Is.Not.Null);
Assert.That(translated.ParameterBindings, Is.Not.Null);
var select = (SqlSelect)translated.Query;
select.Columns.Clear();
select.Columns.Add(SqlDml.Count());
var compiled = builder.CompileQuery(translated.Query);
Assert.That(compiled, Is.Not.Null);
var request = builder.CreateRequest(compiled, translated.ParameterBindings);
Assert.That(request, Is.Not.Null);
var command = builder.CreateCommand(request);
Assert.That(command, Is.Not.Null);
using (command) {
var result = Convert.ToInt32(command.ExecuteScalar());
Assert.That(result, Is.EqualTo(1));
}
tx.Complete();
}
}
public void MultiColumnDynamicFilterTest()
{
var dynamicFilterId = new object();
var tableRef = SqlDml.TableRef(Catalog.Schemas["Person"].Tables["Contact"]);
var select = SqlDml.Select(tableRef);
select.Columns.Add(SqlDml.Count());
var filter = SqlDml.DynamicFilter(dynamicFilterId);
select.Where = filter;
filter.Expressions.Add(tableRef["FirstName"]);
filter.Expressions.Add(tableRef["LastName"]);
var result = sqlConnection.Driver.Compile(select);
using (var command = sqlConnection.CreateCommand()) {
var values = new List <string[]> {
new[] { "'Gustavo'", "'Achong'" }, new[] { "'Catherine'", "'Abel'" }
};
var configuration = new SqlPostCompilerConfiguration {
DynamicFilterValues = { { dynamicFilterId, values } }
};
command.CommandText = result.GetCommandText(configuration);
int count = Convert.ToInt32(command.ExecuteScalar());
Assert.AreEqual(2, count);
}
}
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)
{
if (node.Operand is SqlSubQuery origSubselect)
{
var origQuery = SqlDml.QueryRef(origSubselect.Query);
var 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 ISqlCompileUnit GetQuery(Table generatorTable)
{
var select = SqlDml.Select();
select.Columns.Add(SqlDml.Count());
select.From = SqlDml.TableRef(generatorTable);
return select;
}
public void Test024()
{
string nativeSql = @"SELECT YEAR(r.rental_date) as Year, COUNT(*) Rented
FROM rental r
GROUP BY YEAR(r.rental_date)";
SqlTableRef rental = SqlDml.TableRef(schema.Tables["rental"], "r");
SqlSelect select = SqlDml.Select(rental);
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Year, rental["rental_date"]), "Year");
select.Columns.Add(SqlDml.Count(), "Rented");
select.GroupBy.Add(SqlDml.Extract(SqlDateTimePart.Year, rental["rental_date"]));
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
protected override void TestFixtureSetUp()
{
base.TestFixtureSetUp();
var testSchema = ExtractDefaultSchema();
EnsureTableNotExists(testSchema, TestTable);
var table = testSchema.CreateTable(TestTable);
table.CreateColumn(IdColumn, new SqlValueType(SqlType.Decimal, 10, 0));
ExecuteNonQuery(SqlDdl.Create(table));
tableRef = SqlDml.TableRef(table);
var select = SqlDml.Select(tableRef);
select.Columns.Add(SqlDml.Count());
countQuery = Connection.Driver.Compile(select).GetCommandText();
}
public void Test024()
{
string nativeSql = @"SELECT
strftime('%Y', PaymentDate) as Year,
COUNT(*) Required
FROM invoice r
GROUP BY strftime('%Y', PaymentDate)";
SqlTableRef invoice = SqlDml.TableRef(schema.Tables["invoice"], "r");
SqlSelect select = SqlDml.Select(invoice);
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Year, invoice["PaymentDate"]), "Year");
select.Columns.Add(SqlDml.Count(), "Required");
select.GroupBy.Add(SqlDml.Extract(SqlDateTimePart.Year, invoice["PaymentDate"]));
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void SqlAggregateReplacingTest()
{
SqlAggregate a = SqlDml.Count();
SqlAggregate aReplacing = SqlDml.Avg(1, true);
a.ReplaceWith(aReplacing);
bool passed = false;
try {
a.ReplaceWith(1);
}
catch {
passed = true;
}
Assert.IsTrue(passed);
Assert.AreNotEqual(a, aReplacing);
Assert.AreEqual(a.NodeType, aReplacing.NodeType);
Assert.AreEqual(a.Distinct, aReplacing.Distinct);
Assert.AreEqual(a.Expression, aReplacing.Expression);
}
public void BatchTest()
{
var table = testSchema.CreateTable(BatchTestTable);
CreateIdColumn(table);
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
var singleStatementBatch = SqlDml.Batch();
singleStatementBatch.Add(CreateInsert(tableRef));
ExecuteNonQuery(singleStatementBatch);
var multiStatementBatch = SqlDml.Batch();
multiStatementBatch.Add(CreateInsert(tableRef));
multiStatementBatch.Add(CreateInsert(tableRef));
ExecuteNonQuery(multiStatementBatch);
var innerEmptyBatch = SqlDml.Batch();
var innerSingleStatementBatch = SqlDml.Batch();
innerSingleStatementBatch.Add(CreateInsert(tableRef));
var innerMultiStatementBatch = SqlDml.Batch();
innerMultiStatementBatch.Add(CreateInsert(tableRef));
var outerBatch = SqlDml.Batch();
outerBatch.Add(innerEmptyBatch);
outerBatch.Add(innerSingleStatementBatch);
outerBatch.Add(multiStatementBatch);
ExecuteNonQuery(outerBatch);
var countQuery = SqlDml.Select(tableRef);
countQuery.Columns.Add(SqlDml.Count());
Assert.AreEqual(6, Convert.ToInt32(ExecuteScalar(countQuery)));
}
/// <summary>
/// Translates <see cref="AggregateColumn"/> to corresponding <see cref="SqlExpression"/>.
/// </summary>
/// <param name="source">The source <see cref="SqlProvider"/>.</param>
/// <param name="sourceColumns">The source columns.</param>
/// <param name="aggregateColumn">The aggregate column.</param>
/// <returns>Aggregate processing result (expression).</returns>
protected virtual SqlExpression ProcessAggregate(SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
{
switch (aggregateColumn.AggregateType)
{
case AggregateType.Avg:
return(SqlDml.Avg(sourceColumns[aggregateColumn.SourceIndex]));
case AggregateType.Count:
return(SqlDml.Count(SqlDml.Asterisk));
case AggregateType.Max:
return(SqlDml.Max(sourceColumns[aggregateColumn.SourceIndex]));
case AggregateType.Min:
return(SqlDml.Min(sourceColumns[aggregateColumn.SourceIndex]));
case AggregateType.Sum:
return(SqlDml.Sum(sourceColumns[aggregateColumn.SourceIndex]));
default:
throw new ArgumentException();
}
}
public void SqlAggregateCloneTest()
{
{
SqlAggregate a = SqlDml.Count();
SqlAggregate aClone = (SqlAggregate)a.Clone();
Assert.AreNotEqual(a, aClone);
Assert.AreEqual(a.NodeType, aClone.NodeType);
Assert.AreEqual(a.Distinct, aClone.Distinct);
Assert.AreEqual(aClone.Expression.NodeType, SqlNodeType.Native);
}
Console.WriteLine();
{
SqlAggregate a = SqlDml.Sum(1);
SqlAggregate aClone = (SqlAggregate)a.Clone();
Assert.AreNotEqual(a, aClone);
Assert.AreNotEqual(a.Expression, aClone.Expression);
Assert.AreEqual(a.NodeType, aClone.NodeType);
Assert.AreEqual(a.Distinct, aClone.Distinct);
Assert.AreEqual(a.Expression.NodeType, aClone.Expression.NodeType);
}
}
private void ValidateIgnoredData(Catalog catalog)
{
var validatableSchemas = new List <Schema>();
if (nodeToSchemaMap.Count > 0)
{
validatableSchemas.Add(catalog.Schemas[nodeToSchemaMap[MainNodeId]]);
validatableSchemas.Add(catalog.Schemas[nodeToSchemaMap[AdditionalNodeId]]);
}
else
{
validatableSchemas.Add(catalog.DefaultSchema);
}
foreach (var schema in validatableSchemas)
{
using (var connection = driver.CreateConnection()) {
connection.Open();
var productTable = schema.Tables["Product"];
var @ref = SqlDml.TableRef(productTable);
var selectAll = SqlDml.Select(@ref);
selectAll.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");
var selectWhere = SqlDml.Select(@ref);
selectWhere.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");
selectWhere.Where = @ref["HiddenName"] == SqlDml.Literal("Hidden name");
using (var expectedCountCommand = connection.CreateCommand(selectAll))
using (var actualCountCommand = connection.CreateCommand(selectWhere)) {
var expectedCount = Convert.ToInt64(expectedCountCommand.ExecuteScalar());
var actualCount = Convert.ToInt64(actualCountCommand.ExecuteScalar());
Assert.That(actualCount, Is.EqualTo(expectedCount));
}
var priceListTable = schema.Tables["PriceList"];
@ref = SqlDml.TableRef(priceListTable);
selectAll = SqlDml.Select(@ref);
selectAll.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");
selectWhere = SqlDml.Select(@ref);
selectWhere.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");
selectWhere.Where = @ref["HiddenComment"] == SqlDml.Literal("Some hidden comment");
using (var expectedCountCommand = connection.CreateCommand(selectAll))
using (var actualCountCommand = connection.CreateCommand(selectWhere)) {
var expectedCount = Convert.ToInt64(expectedCountCommand.ExecuteScalar());
var actualCount = Convert.ToInt64(actualCountCommand.ExecuteScalar());
Assert.That(actualCount, Is.EqualTo(expectedCount));
}
var currencyTable = schema.Tables["Currency"];
@ref = SqlDml.TableRef(currencyTable);
selectAll = SqlDml.Select(@ref);
selectAll.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");
selectWhere = SqlDml.Select(@ref);
selectWhere.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");
selectWhere.Where = @ref["NotInDomainColumn1"] == "Not in domain" && @ref["NotInDomainColumn1"] == "Not in domain" && @ref["NotInDomainColumn1"] == "Not in domain";
using (var expectedCountCommand = connection.CreateCommand(selectAll))
using (var actualCountCommand = connection.CreateCommand(selectWhere)) {
var expectedCount = Convert.ToInt64(expectedCountCommand.ExecuteScalar());
var actualCount = Convert.ToInt64(actualCountCommand.ExecuteScalar());
Assert.That(actualCount, Is.EqualTo(expectedCount));
}
}
}
}
private void BuildSelectForUniqueMatchPartial(SqlMatch node, SqlRow row, SqlSelect finalQuery)
{
bool allNull = true;
var @case = SqlDml.Case();
SqlExpression when = true;
//if all row elements are null then true
if (row.Count > 0)
{
var whenNotNeeded = false;
for (var 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;
}
when = i == 0
? SqlDml.IsNull(row[i])
: when && SqlDml.IsNull(row[i]);
}
if (allNull)
{
when = true;
}
if (!whenNotNeeded)
{
_ = @case.Add(when, true);
}
}
//otherwise
if (!allNull)
{
//find row in subquery
var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
var subQuery = SqlDml.Select(originalQuery);
subQuery.Columns.Add(8);
var columns = originalQuery.Columns;
SqlExpression where = null;
for (var 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];
where = where == null ? c3 : where && c3;
}
if (node.Unique)
{
var c4 = SqlDml.Case();
_ = c4.Add(SqlDml.IsNull(row[i]), 0);
c4.Else = columns[i];
subQuery.GroupBy.Add(c4);
}
}
subQuery.Where = where;
if (node.Unique)
{
subQuery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
@case.Else = SqlDml.Exists(subQuery);
}
if (@case.Else == null)
{
@case.Else = false;
}
if (allNull)
{
finalQuery.Where = null;
}
else if (@case.Count > 0)
{
finalQuery.Where = @case;
}
else
{
finalQuery.Where = @case.Else;
}
}
private void BuildSelectForUniqueMatchFull(SqlMatch node, SqlRow row, SqlSelect finalQuery)
{
var @case = SqlDml.Case();
bool noMoreWhenNeeded = false;
bool allNull = true;
SqlExpression when1 = true;
//if all row elements are null then true
if (row.Count > 0)
{
var whenNotNeeded = false;
for (var 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)
{
_ = @case.Add(when1, true);
}
}
if (!noMoreWhenNeeded)
{
var whenNotNeeded = false;
var allLiteral = true;
SqlExpression when2 = true;
//if no row elements are null then subcase
for (var 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
var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
var subQuery = SqlDml.Select(originalQuery);
subQuery.Columns.Add(1);
var 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)
{
subQuery.GroupBy.Add(columns[i]);
}
}
subQuery.Where = where;
if (node.Unique)
{
subQuery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
_ = @case.Add(when2, SqlDml.Exists(subQuery));
}
}
//else false
@case.Else = false;
finalQuery.Where = @case.Count > 0 ? @case : (SqlExpression)false;
}
private void BuildSelectForUniqueMatchNone(SqlMatch node, SqlRow row, SqlSelect finalQuery)
{
var existsNull = false;
var @case = SqlDml.Case();
var subQueryNeeded = true;
//if any of the row elements is NULL then true
if (row.Count > 0)
{
var allLiteral = true; //if true then there is no NULL element
SqlExpression when = null;
for (var i = 0; i < row.Count; i++)
{
var 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)
{
when = when == null
? SqlDml.IsNull(row[i])
: when || 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
{
_ = @case.Add(when == null ? true : when, true);
subQueryNeeded = true;
}
}
//find row in subquery
if (subQueryNeeded)
{
var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
var subquery = SqlDml.Select(originalQuery);
subquery.Columns.Add(1);
var columns = originalQuery.Columns;
SqlExpression where = null;
for (var i = 0; i < columns.Count; i++)
{
if (i == 0)
{
where = columns[i] == row[i];
}
else
{
where = where && columns[i] == row[i];
}
if (node.Unique)
{
subquery.GroupBy.Add(columns[i]);
}
}
subquery.Where = where;
if (node.Unique)
{
subquery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
//c.Add(Sql.Exists(q1), true);
@case.Else = SqlDml.Exists(subquery);
}
if (@case.Else == null)
{
@case.Else = false;
}
if (existsNull)
{
finalQuery.Where = null;
}
else if (@case.Count > 0)
{
finalQuery.Where = @case;
}
else
{
finalQuery.Where = @case.Else;
}
}
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);
}