public void SqlMatchReplacingTest()
{
SqlTableRef t = SqlDml.TableRef(table1);
SqlSelect s1 = SqlDml.Select(t);
s1.Columns.Add(t[0]);
s1.Columns.Add(SqlDml.Null, "ID");
SqlSelect s2 = SqlDml.Select(t);
s2.Columns.Add(t[0]);
s2.Columns.Add(t[1]);
SqlMatch m = SqlDml.Match(SqlDml.Row(1, "name"), s1, false, SqlMatchType.Partial);
SqlMatch mReplacing = SqlDml.Match(SqlDml.Row(1, "name"), s2, true, SqlMatchType.None);
m.ReplaceWith(mReplacing);
bool passed = false;
try {
m.ReplaceWith(1);
}
catch {
passed = true;
}
Assert.IsTrue(passed);
Assert.AreNotEqual(m, mReplacing);
Assert.AreEqual(m.NodeType, mReplacing.NodeType);
Assert.AreEqual(m.MatchType, mReplacing.MatchType);
Assert.AreEqual(m.Unique, mReplacing.Unique);
Assert.AreEqual(m.Value, mReplacing.Value);
Assert.AreEqual(m.SubQuery, mReplacing.SubQuery);
}
public void Visit(SqlMatch node)
{
if (!node.Value.IsNullReference())
{
Visit(node.Value);
}
if (!node.SubQuery.IsNullReference())
{
Visit(node.SubQuery);
}
}
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
if (node.Value is SqlRow row)
{
var finalQuery = SqlDml.Select();
finalQuery.Columns.Add(5);
switch (node.MatchType)
{
case SqlMatchType.None: {
BuildSelectForUniqueMatchNone(node, row, finalQuery);
break;
}
case SqlMatchType.Full: {
BuildSelectForUniqueMatchFull(node, row, finalQuery);
break;
}
case SqlMatchType.Partial: {
BuildSelectForUniqueMatchPartial(node, row, finalQuery);
break;
}
}
var 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);
}
private void requestSearch()
{
var err = validateSearch();
drawError(err);
if (err != "")
{
return;
}
if (currentTask != null && !currentTask.IsCompleted)
{
return;
}
drawEnterSearch();
var searchToken = txSearchToken.Text;
var regex = cbCondRegex.Checked;
var word = cbCondWord.Checked;
var ignoreCase = !cbEnableCase.Checked;
var includesCaption = !cbOnlyBody.Checked;
enterTask("検索中");
assignTask(() =>
{
var subTask = SqlFileFacade.RequestSearch(searchToken,
SqlMatch.withRegex(regex),
SqlMatch.withWord(word),
SqlMatch.withIgnoreCase(ignoreCase),
SqlMatch.withIncludesCaption(includesCaption))
.ContinueWith((t) =>
{
listSqls.Clear();
listSqls.AddRange(t.Result);
this.Invoke(new Action(drawEndSearch));
currentTask = null;
});
Task.WaitAll(subTask);
})
.ContinueWith((t) => {
releaseTask();
});
}
public void SqlMatchCloneTest()
{
SqlTableRef t = SqlDml.TableRef(table1);
SqlSelect s = SqlDml.Select(t);
s.Columns.Add(t.Columns[0]);
SqlMatch m = SqlDml.Match(SqlDml.Row(4), s, true, SqlMatchType.Full);
SqlMatch mClone = (SqlMatch)m.Clone();
Assert.AreNotEqual(m, mClone);
Assert.AreNotEqual(m.Value, mClone.Value);
Assert.AreNotEqual(m.SubQuery, mClone.SubQuery);
Assert.AreEqual(m.NodeType, mClone.NodeType);
Assert.AreEqual(m.Value.NodeType, mClone.Value.NodeType);
Assert.AreEqual(m.SubQuery.NodeType, mClone.SubQuery.NodeType);
Assert.AreEqual(m.Unique, mClone.Unique);
Assert.AreEqual(m.MatchType, mClone.MatchType);
}
public virtual void Visit(SqlMatch node)
{
VisitInternal(node.SubQuery);
VisitInternal(node.Value);
}
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);
}
