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));
}
public void Test027()
{
string nativeSql = @"SELECT
c.CustomerId,
c.CompanyName
FROM
customer c
WHERE EXISTS
(SELECT * FROM invoice i WHERE i.Commission < 1.00 AND i.CustomerId = c.CustomerId )";
SqlTableRef customer = SqlDml.TableRef(schema.Tables["customer"], "c");
SqlTableRef invoice = SqlDml.TableRef(schema.Tables["invoice"], "i");
SqlSelect innerSelect = SqlDml.Select(invoice);
SqlSelect select = SqlDml.Select(customer);
innerSelect.Columns.Add(SqlDml.Asterisk);
innerSelect.Where = invoice["Commission"] < 11.00 && invoice["CustomerId"] == customer["CustomerId"];
select.Columns.Add(customer["CustomerId"]);
select.Columns.Add(customer["CompanyName"]);
select.Where = SqlDml.Exists(innerSelect);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test027()
{
string nativeSql = @"SELECT
c.customer_id,
c.first_name
FROM
customer c
WHERE EXISTS
(SELECT * FROM payment p WHERE p.amount > 11.00 AND p.customer_id = c.customer_id )";
SqlTableRef customer = SqlDml.TableRef(schema.Tables["customer"], "c");
SqlTableRef payment = SqlDml.TableRef(schema.Tables["payment"], "p");
SqlSelect innerSelect = SqlDml.Select(payment);
SqlSelect select = SqlDml.Select(customer);
innerSelect.Columns.Add(SqlDml.Asterisk);
innerSelect.Where = payment["amount"] > 11.00 && payment["customer_id"] == customer["customer_id"];
select.Columns.Add(customer["customer_id"]);
select.Columns.Add(customer["first_name"]);
select.Where = SqlDml.Exists(innerSelect);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
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);
}
/// <inheritdoc/>
protected override SqlProvider VisitExistence(ExistenceProvider provider)
{
var source = Compile(provider.Source);
var query = source.Request.Statement.ShallowClone();
query.Columns.Clear();
query.Columns.Add(query.Asterisk);
query.OrderBy.Clear();
query.GroupBy.Clear();
SqlExpression existsExpression = SqlDml.Exists(query);
existsExpression = GetBooleanColumnExpression(existsExpression);
var select = SqlDml.Select();
select.Columns.Add(existsExpression, provider.ExistenceColumnName);
return(CreateProvider(select, provider, source));
}
protected SqlExpression CreateIncludeViaTemporaryTableExpression(
IncludeProvider provider, IList <SqlExpression> sourceColumns,
out TemporaryTableDescriptor tableDescriptor)
{
var filterTupleDescriptor = provider.FilteredColumnsExtractionTransform.Descriptor;
var filteredColumns = provider.FilteredColumns.Select(index => sourceColumns[index]).ToList();
tableDescriptor = DomainHandler.TemporaryTableManager
.BuildDescriptor(Mapping, Guid.NewGuid().ToString(), filterTupleDescriptor);
var filterQuery = tableDescriptor.QueryStatement.ShallowClone();
var tableRef = filterQuery.From;
for (int i = 0; i < filterTupleDescriptor.Count; i++)
{
filterQuery.Where &= filteredColumns[i] == tableRef[i];
}
var resultExpression = SqlDml.Exists(filterQuery);
return(resultExpression);
}
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;
}
}
// Consructors
protected override void ExtractTableIndexes(ExtractionContext context)
{
var tableMap = context.TableMap;
var tableColumns = context.TableColumnMap;
if (tableMap.Count > 0)
{
var tableSpacesTable = PgTablespace;
var relationsTable = PgClass;
var indexTable = PgIndex;
var dependencyTable = PgDepend;
//subselect that index was not created automatically
var subSelect = SqlDml.Select(dependencyTable);
subSelect.Where = dependencyTable["classid"] == PgClassOid &&
dependencyTable["objid"] == indexTable["indexrelid"] &&
dependencyTable["deptype"] == 'i';
subSelect.Columns.Add(dependencyTable[0]);
//not automatically created indexes of our tables
var select = SqlDml.Select(indexTable
.InnerJoin(relationsTable, relationsTable["oid"] == indexTable["indexrelid"])
.LeftOuterJoin(tableSpacesTable, tableSpacesTable["oid"] == relationsTable["reltablespace"]));
select.Where = SqlDml.In(indexTable["indrelid"], CreateOidRow(tableMap.Keys)) && !SqlDml.Exists(subSelect);
select.Columns.Add(indexTable["indrelid"]);
select.Columns.Add(indexTable["indexrelid"]);
select.Columns.Add(relationsTable["relname"]);
select.Columns.Add(indexTable["indisunique"]);
select.Columns.Add(indexTable["indisclustered"]);
select.Columns.Add(indexTable["indkey"]);
select.Columns.Add(tableSpacesTable["spcname"]);
select.Columns.Add(indexTable["indnatts"]);
select.Columns.Add(SqlDml.FunctionCall("pg_get_expr", indexTable["indexprs"], indexTable["indrelid"], true), "indexprstext");
select.Columns.Add(SqlDml.FunctionCall("pg_get_expr", indexTable["indpred"], indexTable["indrelid"], true), "indpredtext");
select.Columns.Add(SqlDml.FunctionCall("pg_get_indexdef", indexTable["indexrelid"]), "inddef");
AddSpecialIndexQueryColumns(select, tableSpacesTable, relationsTable, indexTable, dependencyTable);
int maxColumnNumber = 0;
using (var command = Connection.CreateCommand(select))
using (var dataReader = command.ExecuteReader()) {
while (dataReader.Read())
{
var tableIdentifier = Convert.ToInt64(dataReader["indrelid"]);
var indexIdentifier = Convert.ToInt64(dataReader["indexrelid"]);
var indexName = dataReader["relname"].ToString();
var isUnique = dataReader.GetBoolean(dataReader.GetOrdinal("indisunique"));
var isClustered = dataReader.GetBoolean(dataReader.GetOrdinal("indisclustered"));
var indexKey = (short[])dataReader["indkey"];
var tablespaceName = (dataReader["spcname"] != DBNull.Value) ? dataReader["spcname"].ToString() : (string)null;
var filterExpression = (dataReader["indpredtext"] != DBNull.Value) ? dataReader["indpredtext"].ToString() : string.Empty;
var table = tableMap[tableIdentifier];
var fullTextRegex = @"(?<=CREATE INDEX \S+ ON \S+ USING (?:gist|gin)(?:\s|\S)*)to_tsvector\('(\w+)'::regconfig, \(*(?:(?:\s|\)|\(|\|)*(?:\(""(\S+)""\)|'\s')::text)+\)";
var indexScript = dataReader["inddef"].ToString();
var matches = Regex.Matches(indexScript, fullTextRegex, RegexOptions.Compiled);
if (matches.Count > 0)
{
// Fulltext index
var fullTextIndex = table.CreateFullTextIndex(indexName);
foreach (Match match in matches)
{
var columnConfigurationName = match.Groups[1].Value;
foreach (Capture capture in match.Groups[2].Captures)
{
var columnName = capture.Value;
var fullTextColumn = fullTextIndex.Columns[columnName] ?? fullTextIndex.CreateIndexColumn(table.Columns.Single(column => column.Name == columnName));
if (fullTextColumn.Languages[columnConfigurationName] == null)
{
fullTextColumn.Languages.Add(new Language(columnConfigurationName));
}
}
}
}
else
{
//Regular index
var index = table.CreateIndex(indexName);
index.IsBitmap = false;
index.IsUnique = isUnique;
index.Filegroup = tablespaceName;
if (!string.IsNullOrEmpty(filterExpression))
{
index.Where = SqlDml.Native(filterExpression);
}
// Expression-based index
var some = dataReader["indexprstext"];
if (some != DBNull.Value)
{
context.ExpressionIndexMap[indexIdentifier] = new ExpressionIndexInfo(index, indexKey);
int columnNumber = dataReader.GetInt16(dataReader.GetOrdinal("indnatts"));
if (columnNumber > maxColumnNumber)
{
maxColumnNumber = columnNumber;
}
}
else
{
for (int j = 0; j < indexKey.Length; j++)
{
int colIndex = indexKey[j];
if (colIndex > 0)
{
index.CreateIndexColumn(tableColumns[tableIdentifier][colIndex], true);
}
else
{
int z = 7;
//column index is 0
//this means that this index column is an expression
//which is not possible with SqlDom tables
}
}
}
ReadSpecialIndexProperties(dataReader, index);
}
}
}
var expressionIndexMap = context.ExpressionIndexMap;
if (expressionIndexMap.Count > 0)
{
select = SqlDml.Select(indexTable);
select.Columns.Add(indexTable["indrelid"]);
select.Columns.Add(indexTable["indexrelid"]);
for (int i = 1; i <= maxColumnNumber; i++)
{
select.Columns.Add(SqlDml.FunctionCall("pg_get_indexdef", indexTable["indexrelid"], i, true), i.ToString());
}
select.Where = SqlDml.In(indexTable["indexrelid"], SqlDml.Array(expressionIndexMap.Keys.ToArray()));
using (var command = Connection.CreateCommand(select))
using (var dataReader = command.ExecuteReader()) {
while (dataReader.Read())
{
var exprIndexInfo = expressionIndexMap[Convert.ToInt64(dataReader[1])];
for (int j = 0; j < exprIndexInfo.Columns.Length; j++)
{
int colIndex = exprIndexInfo.Columns[j];
if (colIndex > 0)
{
exprIndexInfo.Index.CreateIndexColumn(tableColumns[Convert.ToInt64(dataReader[0])][colIndex], true);
}
else
{
exprIndexInfo.Index.CreateIndexColumn(SqlDml.Native(dataReader[(j + 1).ToString()].ToString()));
}
}
}
}
}
}
}
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);
}
/// <inheritdoc/>
protected override ISqlCompileUnit BuildExtractTableIndexesQuery(ExtractionContext context)
{
var tableMap = context.TableMap;
var tableSpacesTable = PgTablespace;
var relationsTable = PgClass;
var indexTable = PgIndex;
var dependencyTable = PgDepend;
//subselect that index was not created automatically
var subSelect = SqlDml.Select(dependencyTable);
subSelect.Where = dependencyTable["classid"] == PgClassOid &&
dependencyTable["objid"] == indexTable["indexrelid"] &&
dependencyTable["deptype"] == 'i';
subSelect.Columns.Add(dependencyTable[0]);
//not automatically created indexes of our tables
var select = SqlDml.Select(indexTable
.InnerJoin(relationsTable, relationsTable["oid"] == indexTable["indexrelid"])
.LeftOuterJoin(tableSpacesTable, tableSpacesTable["oid"] == relationsTable["reltablespace"]));
select.Where = SqlDml.In(indexTable["indrelid"], CreateOidRow(tableMap.Keys)) && !SqlDml.Exists(subSelect);
select.Columns.Add(indexTable["indrelid"]);
select.Columns.Add(indexTable["indexrelid"]);
select.Columns.Add(relationsTable["relname"]);
select.Columns.Add(indexTable["indisunique"]);
select.Columns.Add(indexTable["indisclustered"]);
select.Columns.Add(indexTable["indkey"]);
select.Columns.Add(tableSpacesTable["spcname"]);
select.Columns.Add(indexTable["indnatts"]);
select.Columns.Add(SqlDml.FunctionCall("pg_get_expr", indexTable["indexprs"], indexTable["indrelid"], true),
"indexprstext");
select.Columns.Add(SqlDml.FunctionCall("pg_get_expr", indexTable["indpred"], indexTable["indrelid"], true),
"indpredtext");
select.Columns.Add(SqlDml.FunctionCall("pg_get_indexdef", indexTable["indexrelid"]), "inddef");
AddSpecialIndexQueryColumns(select, tableSpacesTable, relationsTable, indexTable, dependencyTable);
return(select);
}