internal static bool TryGenerateKey(DbExpression tree, out string key)
{
var keyGen = new ExpressionKeyGen();
try
{
tree.Accept(keyGen);
key = keyGen._key.ToString();
return true;
}
catch (NotSupportedException)
{
key = null;
return false;
}
}
public override DbExpression Visit(DbConvertExpression exp)
{
DbExpression stripedExp = DbExpressionExtension.StripInvalidConvert(exp);
if (stripedExp.NodeType != DbExpressionType.Convert)
{
stripedExp.Accept(this);
return(exp);
}
exp = (DbConvertExpression)stripedExp;
string dbTypeString;
if (TryGetCastTargetDbTypeString(exp.Operand.Type, exp.Type, out dbTypeString, false))
{
this.BuildCastState(exp.Operand, dbTypeString);
}
else
{
Type targetType = ReflectionExtension.GetUnderlyingType(exp.Type);
if (targetType == PublicConstants.TypeOfDateTime)
{
/* DATETIME('2016-08-06 09:01:24') */
this.SqlBuilder.Append("DATETIME(");
exp.Operand.Accept(this);
this.SqlBuilder.Append(")");
}
else
{
exp.Operand.Accept(this);
}
}
return(exp);
}
public void Process(DbMethodCallExpression exp, SqlGeneratorBase generator)
{
generator.SqlBuilder.Append("SUBSTRING(");
exp.Object.Accept(generator);
generator.SqlBuilder.Append(",");
DbExpression arg1 = exp.Arguments[0];
if (arg1.NodeType == DbExpressionType.Constant)
{
int startIndex = (int)(((DbConstantExpression)arg1).Value) + 1;
generator.SqlBuilder.Append(startIndex.ToString());
}
else
{
arg1.Accept(generator);
generator.SqlBuilder.Append(" + 1");
}
generator.SqlBuilder.Append(",");
if (exp.Method == PublicConstants.MethodInfo_String_Substring_Int32)
{
var string_LengthExp = DbExpression.MemberAccess(PublicConstants.PropertyInfo_String_Length, exp.Object);
string_LengthExp.Accept(generator);
}
else if (exp.Method == PublicConstants.MethodInfo_String_Substring_Int32_Int32)
{
exp.Arguments[1].Accept(generator);
}
else
{
throw UtilExceptions.NotSupportedMethod(exp.Method);
}
generator.SqlBuilder.Append(")");
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and IdentityValue = scope_identity()
///
/// or
///
/// select TimestamptValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and Id = 1
///
/// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">Translator used to produce DML SQL statement
/// for the tree</param>
/// <param name="returning">Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.</param>
private static void GenerateReturningSql(StringBuilder commandText, DbModificationCommandTree tree,
ExpressionTranslator translator, DbExpression returning)
{
// Nothing to do if there is no Returning expression
if (null == returning) { return; }
// select
commandText.Append("SELECT ");
returning.Accept(translator);
commandText.AppendLine();
// from
commandText.Append("FROM ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
commandText.Append("WHERE last_rows_affected() > 0");
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
bool identity = false;
foreach (EdmMember keyMember in table.ElementType.KeyMembers)
{
commandText.Append(" AND ");
commandText.Append(GenerateMemberTSql(keyMember));
commandText.Append(" = ");
// retrieve member value sql. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL)
DbParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
commandText.Append(value.ParameterName);
}
else
{
// if no value is registered for the key member, it means it is an identity
// which can be retrieved using the scope_identity() function
if (identity)
{
// there can be only one server generated key
throw new NotSupportedException(string.Format("Server generated keys are only supported for identity columns. More than one key column is marked as server generated in table '{0}'.", table.Name));
}
commandText.AppendLine("last_insert_rowid();");
identity = true;
}
}
}
private SqlFragment HandleJoinExpression(DbExpressionBinding left, DbExpressionBinding right,
DbExpressionKind joinType, DbExpression joinCondition)
{
JoinFragment join = new JoinFragment();
join.JoinType = Metadata.GetOperator(joinType);
join.Left = VisitInputExpression(left.Expression, left.VariableName, left.VariableType);
WrapJoinInputIfNecessary(join.Left, false);
join.Right = VisitInputExpression(right.Expression, right.VariableName, right.VariableType);
WrapJoinInputIfNecessary(join.Right, true);
// now handle the ON case
if (joinCondition != null)
join.Condition = joinCondition.Accept(this);
return join;
}
public static DbExpression Optimize(DbExpression exp)
{
return(exp.Accept(_optimizer));
}
void BuildCastState(DbExpression castExp, string targetDbTypeString)
{
this.SqlBuilder.Append("CAST(");
castExp.Accept(this);
this.SqlBuilder.Append(" AS ", targetDbTypeString, ")");
}
/// <summary>
/// The entry point
/// </summary>
/// <param name="expr"></param>
/// <returns>True if the tree needs to be rewriten, false otherwise</returns>
internal static bool NeedsRewrite(DbExpression expr)
{
Sql8ConformanceChecker checker = new Sql8ConformanceChecker();
return(expr.Accept(checker));
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and IdentityValue = scope_identity()
///
/// or
///
/// select TimestampValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and Id = 1
///
/// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
///
/// On SQL Server 2005 and up, we have an additional syntax used for non integer return types:
///
/// declare @generatedValues table(ID uniqueidentifier)
/// insert dbo.MyTable
/// output ID into @generated_values
/// values (...);
/// select ID
/// from @generatedValues as g join dbo.MyTable as t on g.ID = t.ID
/// where @@ROWCOUNT > 0;</code>
/// </summary>
/// <param name="commandText"> Builder containing command text </param>
/// <param name="tree"> Modification command tree </param>
/// <param name="tableType"> Type of table. </param>
/// <param name="translator"> Translator used to produce DML SQL statement for the tree </param>
/// <param name="returning"> Returning expression. If null, the method returns immediately without producing a SELECT statement. </param>
private static void GenerateReturningSql(
StringBuilder commandText, DbModificationCommandTree tree, EntityType tableType,
ExpressionTranslator translator, DbExpression returning, bool useGeneratedValuesVariable)
{
// Nothing to do if there is no Returning expression
if (null == returning)
{
return;
}
// select
commandText.Append("select ");
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = "t";
}
returning.Accept(translator);
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = null;
}
commandText.AppendLine();
if (useGeneratedValuesVariable)
{
// from @generated_values
commandText.Append("from ");
commandText.Append(s_generatedValuesVariableName);
commandText.Append(" as g join ");
tree.Target.Expression.Accept(translator);
commandText.Append(" as t on ");
var separator = string.Empty;
foreach (var keyMember in tableType.KeyMembers)
{
commandText.Append(separator);
separator = " and ";
commandText.Append("g.");
var memberTSql = GenerateMemberTSql(keyMember);
commandText.Append(memberTSql);
commandText.Append(" = t.");
commandText.Append(memberTSql);
}
commandText.AppendLine();
commandText.Append("where @@ROWCOUNT > 0");
}
else
{
// from
commandText.Append("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
commandText.Append("where @@ROWCOUNT > 0");
var table = ((DbScanExpression)tree.Target.Expression).Target;
var identity = false;
foreach (var keyMember in table.ElementType.KeyMembers)
{
commandText.Append(" and ");
commandText.Append(GenerateMemberTSql(keyMember));
commandText.Append(" = ");
// retrieve member value sql. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL)
SqlParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
commandText.Append(value.ParameterName);
}
else
{
// if no value is registered for the key member, it means it is an identity
// which can be retrieved using the scope_identity() function
if (identity)
{
// there can be only one server generated key
throw new NotSupportedException(Strings.Update_NotSupportedServerGenKey(table.Name));
}
if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
throw new InvalidOperationException(
Strings.Update_NotSupportedIdentityType(
keyMember.Name, keyMember.TypeUsage.ToString()));
}
commandText.Append("scope_identity()");
identity = true;
}
}
}
}
/// <summary>
/// Convenience method to visit the specified <see cref="DbExpression" />, if non-null.
/// </summary>
/// <param name="expression"> The expression to visit. </param>
/// <exception cref="ArgumentNullException">
/// <paramref name="expression" />
/// is null
/// </exception>
public virtual void VisitExpression(DbExpression expression)
{
// #433613: PreSharp warning 56506: Parameter 'expression' to this public method must be validated: A null-dereference can occur here.
Check.NotNull(expression, "expression");
expression.Accept(this);
}
internal void Dump(DbExpression target)
{
target.Accept((DbExpressionVisitor)this);
}
public override Expression Visit(DbExpression expression)
{
//Expression recalls the specific Visit method
return(expression.Accept(this));
}
/// <summary>
/// Gets the affected entity sets.
/// </summary>
private static EntitySetBase[] GetAffectedEntitySets(DbExpression expression)
{
var visitor = new ScanExpressionVisitor();
expression.Accept(visitor);
return visitor.EntitySets.ToArray();
}
internal static void GenerateReturningSql(
SqlStringBuilder commandText,
DbModificationCommandTree tree,
EntityType tableType,
DmlSqlGenerator.ExpressionTranslator translator,
DbExpression returning,
bool useGeneratedValuesVariable)
{
if (returning == null)
{
return;
}
commandText.AppendKeyword("select ");
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = "t";
}
returning.Accept((DbExpressionVisitor)translator);
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = (string)null;
}
commandText.AppendLine();
if (useGeneratedValuesVariable)
{
commandText.AppendKeyword("from ");
commandText.Append("@generated_keys");
commandText.AppendKeyword(" as ");
commandText.Append("g");
commandText.AppendKeyword(" join ");
tree.Target.Expression.Accept((DbExpressionVisitor)translator);
commandText.AppendKeyword(" as ");
commandText.Append("t");
commandText.AppendKeyword(" on ");
string keyword = string.Empty;
foreach (EdmMember keyMember in tableType.KeyMembers)
{
commandText.AppendKeyword(keyword);
keyword = " and ";
commandText.Append("g.");
string memberTsql = DmlSqlGenerator.GenerateMemberTSql(keyMember);
commandText.Append(memberTsql);
commandText.Append(" = t.");
commandText.Append(memberTsql);
}
commandText.AppendLine();
commandText.AppendKeyword("where @@ROWCOUNT > 0");
}
else
{
commandText.AppendKeyword("from ");
tree.Target.Expression.Accept((DbExpressionVisitor)translator);
commandText.AppendLine();
commandText.AppendKeyword("where @@ROWCOUNT > 0");
EntitySetBase target = ((DbScanExpression)tree.Target.Expression).Target;
bool flag = false;
foreach (EdmMember keyMember in target.ElementType.KeyMembers)
{
commandText.AppendKeyword(" and ");
commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember));
commandText.Append(" = ");
SqlParameter sqlParameter;
if (translator.MemberValues.TryGetValue(keyMember, out sqlParameter))
{
commandText.Append(sqlParameter.ParameterName);
}
else
{
if (flag)
{
throw new NotSupportedException(Strings.Update_NotSupportedServerGenKey((object)target.Name));
}
if (!DmlSqlGenerator.IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
throw new InvalidOperationException(Strings.Update_NotSupportedIdentityType((object)keyMember.Name, (object)keyMember.TypeUsage.ToString()));
}
commandText.Append("scope_identity()");
flag = true;
}
}
}
}
/// <summary>
/// Convenience method to visit the specified <see cref="DbExpression"/>, if non-null.
/// </summary>
/// <param name="expression">The expression to visit.</param>
/// <exception cref="ArgumentNullException"><paramref name="expression"/> is null</exception>
public virtual void VisitExpression(DbExpression expression)
{
// #433613: PreSharp warning 56506: Parameter 'expression' to this public method must be validated: A null-dereference can occur here.
//Contract.Requires(expression != null);
expression.Accept(this);
}
/// <summary>
/// Evaluates scalar node.
/// </summary>
/// <param name="node"> Sub-query returning a scalar value. </param>
/// <param name="row"> Row to evaluate. </param>
/// <returns> Scalar result. </returns>
internal static PropagatorResult Evaluate(DbExpression node, PropagatorResult row)
{
DbExpressionVisitor <PropagatorResult> evaluator = new Evaluator(row);
return(node.Accept(evaluator));
}
public static DbExpression Transform(DbExpression exp)
{
return(exp.Accept(_transformer));
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and IdentityValue = scope_identity()
///
/// or
///
/// select TimestamptValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and Id = 1
///
/// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">Translator used to produce DML SQL statement
/// for the tree</param>
/// <param name="returning">Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.</param>
/// <param name="wasInsert">
/// Non-zero if this method is being called as part of processing an INSERT;
/// otherwise (e.g. UPDATE), zero.
/// </param>
private static void GenerateReturningSql(StringBuilder commandText, DbModificationCommandTree tree,
ExpressionTranslator translator, DbExpression returning, bool wasInsert)
{
// Nothing to do if there is no Returning expression
if (null == returning) { return; }
// select
commandText.Append("SELECT ");
returning.Accept(translator);
commandText.AppendLine();
// from
commandText.Append("FROM ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
#if USE_INTEROP_DLL && INTEROP_EXTENSION_FUNCTIONS
commandText.Append("WHERE last_rows_affected() > 0");
#else
commandText.Append("WHERE changes() > 0");
#endif
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
ReadOnlyMetadataCollection<EdmMember> keyMembers;
EdmMember primaryKeyMember;
EdmMember missingKeyMember;
// Model Types can be (at the time of this implementation):
// Binary, Boolean, Byte, DateTime, Decimal, Double, Guid, Int16,
// Int32, Int64,Single, String
if (IsIntegerPrimaryKey(table, out keyMembers, out primaryKeyMember))
{
//
// NOTE: This must be an INTEGER PRIMARY KEY (i.e. "rowid") table.
//
commandText.Append(" AND ");
commandText.Append(GenerateMemberTSql(primaryKeyMember));
commandText.Append(" = ");
DbParameter value;
if (translator.MemberValues.TryGetValue(primaryKeyMember, out value))
{
//
// NOTE: Use the integer primary key value that was specified as
// part the associated INSERT/UPDATE statement.
//
commandText.Append(value.ParameterName);
}
else if (wasInsert)
{
//
// NOTE: This was part of an INSERT statement and we know the table
// has an integer primary key. This should not fail unless
// something (e.g. a trigger) causes the last_insert_rowid()
// function to return an incorrect result.
//
commandText.AppendLine("last_insert_rowid()");
}
else /* NOT-REACHED? */
{
//
// NOTE: We cannot simply use the "rowid" at this point because:
//
// 1. The last_insert_rowid() function is only valid after
// an INSERT and this was an UPDATE.
//
throw new NotSupportedException(String.Format(
"Missing value for INSERT key member '{0}' in table '{1}'.",
(primaryKeyMember != null) ? primaryKeyMember.Name : "<unknown>",
table.Name));
}
}
else if (DoAllKeyMembersHaveValues(translator, keyMembers, out missingKeyMember))
{
foreach (EdmMember keyMember in keyMembers)
{
commandText.Append(" AND ");
commandText.Append(GenerateMemberTSql(keyMember));
commandText.Append(" = ");
// Retrieve member value SQL. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL).
DbParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
//
// NOTE: Use the primary key value that was specified as part the
// associated INSERT/UPDATE statement. This also applies
// to composite primary keys.
//
commandText.Append(value.ParameterName);
}
else /* NOT-REACHED? */
{
//
// NOTE: We cannot simply use the "rowid" at this point because:
//
// 1. This associated INSERT/UPDATE statement appeared to
// have all the key members availab;e however, there
// appears to be an inconsistency. This is an internal
// error and should be thrown.
//
throw new NotSupportedException(String.Format(
"Missing value for {0} key member '{1}' in table '{2}' " +
"(internal).", wasInsert ? "INSERT" : "UPDATE",
(keyMember != null) ? keyMember.Name : "<unknown>",
table.Name));
}
}
}
else if (wasInsert) /* NOT-REACHED? */
{
//
// NOTE: This was part of an INSERT statement; try using the "rowid"
// column to fetch the most recently inserted row. This may
// still fail if the table is a WITHOUT ROWID table -OR-
// something (e.g. a trigger) causes the last_insert_rowid()
// function to return an incorrect result.
//
commandText.Append(" AND ");
commandText.Append(SqlGenerator.QuoteIdentifier("rowid"));
commandText.Append(" = ");
commandText.AppendLine("last_insert_rowid()");
}
else /* NOT-REACHED? */
{
//
// NOTE: We cannot simply use the "rowid" at this point because:
//
// 1. The last_insert_rowid() function is only valid after
// an INSERT and this was an UPDATE.
//
throw new NotSupportedException(String.Format(
"Missing value for UPDATE key member '{0}' in table '{1}'.",
(missingKeyMember != null) ? missingKeyMember.Name : "<unknown>",
table.Name));
}
commandText.AppendLine(";");
}
/// <summary>
/// Helper method that wrapps the given expession with a conver to varchar(255)
/// </summary>
/// <param name="result"></param>
/// <param name="e"></param>
private static void AppendConvertToVarchar(SqlGenerator sqlgen, SqlBuilder result, DbExpression e)
{
result.Append("convert(varchar(255), ");
result.Append(e.Accept(sqlgen));
result.Append(")");
}
internal ViewValidator.DbExpressionEntitySetInfo VisitExpression(
DbExpression expression)
{
return(expression.Accept <ViewValidator.DbExpressionEntitySetInfo>((DbExpressionVisitor <ViewValidator.DbExpressionEntitySetInfo>) this));
}
public override object Visit(DbExpression expression)
{
return(expression.Accept(this));
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from myschema.MyTable
/// where __ingres_@@ROWCOUNT > 0 and IdentityValue = LAST_IDENTITY()
///
/// The "__ingres_@@ROWCOUNT" will be replaced by
/// the IngresDbCommandInterceptor with the number
/// returned for recordsAffected in the batch execution.
///
/// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">Translator used to produce DML SQL statement
/// for the tree</param>
/// <param name="returning">Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.</param>
/// <param name="wasInsert">
/// Non-zero if this method is being called as part of processing an INSERT;
/// otherwise (e.g. UPDATE), zero.
/// </param>
private static void GenerateReturningSql(
StringBuilder commandText,
DbModificationCommandTree tree,
ExpressionTranslator translator,
DbExpression returning,
bool wasInsert)
{
// Nothing to do if there is no Returning expression
if (null == returning)
{
return;
}
commandText.Append(";"); // semicolon separated batch of SQL statements
// to be executed by IngresDbCommandInterceptor
// select
commandText.Append("SELECT ");
returning.Accept(translator); // e.g. "BlogId", "IntComputedValue"
commandText.AppendLine();
// from
commandText.Append("FROM ");
tree.Target.Expression.Accept(translator); // e.g. "Blogs"
commandText.AppendLine();
// where
// (Ensure prior INSERT statements completed OK using recordsAffected.
// The IngresDbCommandInterceptor will cut/paste __ingres_@@ROWCOUNT
// with the number returned for recordsAffected.)
commandText.Append("WHERE __ingres_@@ROWCOUNT > 0");
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
ReadOnlyMetadataCollection <EdmMember> keyMembers =
table.ElementType.KeyMembers;
var hasIdentityBeenAlreadyProcessed = false;
foreach (var keyMember in keyMembers)
{
commandText.Append(" AND ");
commandText.Append(GenerateMemberTSql(keyMember));
commandText.Append(" = ");
// Retrieve member value sql. the translator remembers
// member values as it constructs the INSERT/UPDATE
// statement (which precedes the "returning" SQL SELECT)
DbParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
commandText.Append(value.ParameterName);
continue; // move on to the next keyMember in keyMembers
}
// We have no member value to retrieve the row. Argh!
// If INSERT then might be able to use IDENTITY.
// Check that the column is an exact numeric data
// type in order to qualify as an Identity column.
if (wasInsert &&
IsaIdentityColumnType(keyMember.TypeUsage))
{
// If no value is registered for the key member,
// it means it is an Identity Column value which can
// be retrieved using the LAST_IDENTITY() function
if (hasIdentityBeenAlreadyProcessed)
{
// there can be only one server generated key
throw new NotSupportedException(String.Format(
"Store-generated keys are only supported for " +
"identity columns. More than one key column '{0}' " +
"is marked as server generated in table '{1}'.",
(keyMember != null) ? keyMember.Name : "<unknown>",
table.Name));
}
// LAST_IDENTITY() is a sequence expression function that
// returns the last value generated for an identity column
// in any table for the current session. Since we had just done
// a successfull INSERT (recordsAffected > 0) of a row into a
// table with an identity column, we expect to retrieve that row.
commandText.Append("LAST_IDENTITY()");
hasIdentityBeenAlreadyProcessed = true;
} // end if (wasInsert and possible Identity column)
else // else UPDATE or not an Identity column
{
// We cannot even try to use LAST_IDENTITY() at this point
// because the LAST_IDENTITY() function is only valid after
// an INSERT and this was an UPDATE or not an Identity.
throw new NotSupportedException(String.Format(
"Missing value for {0} key member '{1}' in table '{2}'.",
(wasInsert) ? "INSERT" : "UPDATE",
(keyMember != null) ? keyMember.Name : "<unknown>",
table.Name));
}
} // end foreach (var keyMember in keyMembers)
commandText.AppendLine(";"); // mark end of batch statements to be
// executed by IngresDbCommandInterceptor
}
internal TreeNode VisitExpression(DbExpression expr)
{
return(expr.Accept <TreeNode>((DbExpressionVisitor <TreeNode>) this));
}
/// <summary>
/// This handles the processing of join expressions.
/// The extents on a left spine are flattened, while joins
/// not on the left spine give rise to new nested sub queries.
///
/// Joins work differently from the rest of the visiting, in that
/// the parent (i.e. the join node) creates the SqlSelectStatement
/// for the children to use.
///
/// The "parameter" IsInJoinContext indicates whether a child extent should
/// add its stuff to the existing SqlSelectStatement, or create a new SqlSelectStatement
/// By passing true, we ask the children to add themselves to the parent join,
/// by passing false, we ask the children to create new Select statements for
/// themselves.
///
/// This method is called from <see cref="Visit(DbApplyExpression)"/> and
/// <see cref="Visit(DbJoinExpression)"/>.
/// </summary>
/// <param name="inputs"></param>
/// <param name="joinKind"></param>
/// <param name="joinString"></param>
/// <param name="joinCondition"></param>
/// <returns> A <see cref="SqlSelectStatement"/></returns>
private ISqlFragment VisitJoinExpression(IList <DbExpressionBinding> inputs, DbExpressionKind joinKind, string joinString, DbExpression joinCondition)
{
// If the parent is not a join( or says that it is not),
// we should create a new SqlSelectStatement.
// otherwise, we add our child extents to the parent's FROM clause.
if (!IsParentAJoin)
{
selectStatementStack.Push(new SqlSelectStatement {
AllJoinExtents = new List <Symbol>()
});
}
var result = CurrentSelectStatement;
// Process each of the inputs, and then the joinCondition if it exists.
// It would be nice if we could call VisitInputExpression - that would
// avoid some code duplication
// but the Join postprocessing is messy and prevents this reuse.
symbolTable.EnterScope();
var separator = "";
var isLeftMostInput = true;
var inputCount = inputs.Count;
foreach (var input in Enumerable.Range(0, inputs.Count).Select(x => inputs[x]))
{
if (separator.Length != 0)
{
result.From.AppendLine();
}
result.From.Append(separator + " ");
// Change this if other conditions are required
// to force the child to produce a nested SqlStatement.
var needsJoinContext = input.Expression.ExpressionKind == DbExpressionKind.Scan ||
(isLeftMostInput && IsApplyOrJoinExpression(input.Expression));
isParentAJoinStack.Push(needsJoinContext ? true : false);
// if the child reuses our select statement, it will append the from
// symbols to our FromExtents list. So, we need to remember the
// start of the child's entries.
var fromSymbolStart = result.FromExtents.Count;
var fromExtentFragment = input.Expression.Accept(this);
isParentAJoinStack.Pop();
ProcessJoinInputResult(fromExtentFragment, result, input, fromSymbolStart);
separator = joinString;
isLeftMostInput = false;
}
// Visit the on clause/join condition.
switch (joinKind)
{
case DbExpressionKind.FullOuterJoin:
case DbExpressionKind.InnerJoin:
case DbExpressionKind.LeftOuterJoin:
result.From.Append(" on ");
isParentAJoinStack.Push(false);
result.From.Append(joinCondition.Accept(this));
isParentAJoinStack.Pop();
break;
}
symbolTable.ExitScope();
if (!IsParentAJoin)
{
selectStatementStack.Pop();
}
return(result);
}
static void StringConcat(DbBinaryExpression exp, SqlGeneratorBase generator)
{
List <DbExpression> operands = new List <DbExpression>();
operands.Add(exp.Right);
DbExpression left = exp.Left;
DbAddExpression e = null;
while ((e = (left as DbAddExpression)) != null && (e.Method == PublicConstants.MethodInfo_String_Concat_String_String || e.Method == PublicConstants.MethodInfo_String_Concat_Object_Object))
{
operands.Add(e.Right);
left = e.Left;
}
operands.Add(left);
DbExpression whenExp = null;
List <DbExpression> operandExps = new List <DbExpression>(operands.Count);
for (int i = operands.Count - 1; i >= 0; i--)
{
DbExpression operand = operands[i];
DbExpression opBody = operand;
if (opBody.Type != PublicConstants.TypeOfString)
{
// 需要 cast type
opBody = DbExpression.Convert(opBody, PublicConstants.TypeOfString);
}
DbExpression equalNullExp = DbExpression.Equal(opBody, PublicConstants.DbConstant_Null_String);
if (whenExp == null)
{
whenExp = equalNullExp;
}
else
{
whenExp = DbExpression.And(whenExp, equalNullExp);
}
operandExps.Add(opBody);
}
generator.SqlBuilder.Append("CASE", " WHEN ");
whenExp.Accept(generator);
generator.SqlBuilder.Append(" THEN ");
DbConstantExpression.Null.Accept(generator);
generator.SqlBuilder.Append(" ELSE ");
generator.SqlBuilder.Append("(");
for (int i = 0; i < operandExps.Count; i++)
{
if (i > 0)
{
generator.SqlBuilder.Append(" + ");
}
generator.SqlBuilder.Append("ISNULL(");
operandExps[i].Accept(generator);
generator.SqlBuilder.Append(",");
DbConstantExpression.StringEmpty.Accept(generator);
generator.SqlBuilder.Append(")");
}
generator.SqlBuilder.Append(")");
generator.SqlBuilder.Append(" END");
}
/// <summary>
/// Dumps a DbExpression by visiting it.
/// </summary>
/// <param name="target">The DbExpression to dump</param>
internal void Dump(DbExpression target)
{
target.Accept(this);
}
/// <summary>
/// Dumps a DbExpression by visiting it.
/// </summary>
/// <param name="target"> The DbExpression to dump </param>
internal void Dump(DbExpression target)
{
target.Accept(this);
}
// <summary>
// Generates SQL fragment returning server-generated values.
// Requires: translator knows about member values so that we can figure out
// how to construct the key predicate.
// <code>Sample SQL:
//
// select IdentityValue
// from MyTable
// where IdentityValue = @@identity
//
// NOTE: not scope_identity() because we don't support it.</code>
// </summary>
// <param name="commandText"> Builder containing command text </param>
// <param name="tree"> Modification command tree </param>
// <param name="translator"> Translator used to produce DML SQL statement for the tree </param>
// <param name="returning"> Returning expression. If null, the method returns immediately without producing a SELECT statement. </param>
private static void GenerateReturningSql(
SqlStringBuilder commandText,
DbModificationCommandTree tree,
ExpressionTranslator translator,
DbExpression returning)
{
if (returning != null)
{
commandText.AppendKeyword("select ");
returning.Accept(translator);
commandText.AppendLine();
commandText.AppendKeyword("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
commandText.AppendKeyword("where ");
var target = ((DbScanExpression)tree.Target.Expression).Target;
var flag = false;
var isFirst = true;
foreach (var member in target.ElementType.KeyMembers)
{
if (!isFirst)
{
commandText.AppendKeyword(" and ");
}
else
{
isFirst = false;
}
commandText.Append(GenerateMemberTSql(member));
commandText.Append(" = ");
flag = HandleIdentity(commandText, translator, member, flag, target);
}
}
}
public static DbExpression Parse(DbExpression exp)
{
return(exp.Accept(_joinConditionExpressionParser));
}
internal TreeNode VisitExpression(DbExpression expr)
{
return(expr.Accept(this));
}
public virtual void VisitExpression(DbExpression expression)
{
if (expression == null) throw new ArgumentException("expression");
expression.Accept(this);
}
internal TreeNode VisitExpression(string name, DbExpression expr)
{
return(new TreeNode(name, expr.Accept(this)));
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and IdentityValue = scope_identity()
///
/// or
///
/// select TimestampValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and Id = 1
///
/// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
///
/// On SQL Server 2005 and up, we have an additional syntax used for non integer return types:
///
/// declare @generatedValues table(ID uniqueidentifier)
/// insert dbo.MyTable
/// output ID into @generated_values
/// values (...);
/// select ID
/// from @generatedValues as g join dbo.MyTable as t on g.ID = t.ID
/// where @@ROWCOUNT > 0;</code>
/// </summary>
/// <param name="commandText"> Builder containing command text </param>
/// <param name="tree"> Modification command tree </param>
/// <param name="tableType"> Type of table. </param>
/// <param name="translator"> Translator used to produce DML SQL statement for the tree </param>
/// <param name="returning"> Returning expression. If null, the method returns immediately without producing a SELECT statement. </param>
internal static void GenerateReturningSql(
SqlStringBuilder commandText,
DbModificationCommandTree tree,
EntityType tableType,
ExpressionTranslator translator,
DbExpression returning,
bool useGeneratedValuesVariable)
{
// Nothing to do if there is no Returning expression
if (null == returning)
{
return;
}
// select
commandText.AppendKeyword("select ");
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = "t";
}
returning.Accept(translator);
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = null;
}
commandText.AppendLine();
if (useGeneratedValuesVariable)
{
// from @generated_keys
commandText.AppendKeyword("from ");
commandText.Append(GeneratedValuesVariableName);
commandText.AppendKeyword(" as ");
commandText.Append("g");
commandText.AppendKeyword(" join ");
tree.Target.Expression.Accept(translator);
commandText.AppendKeyword(" as ");
commandText.Append("t");
commandText.AppendKeyword(" on ");
var separator = string.Empty;
foreach (var keyMember in tableType.KeyMembers)
{
commandText.AppendKeyword(separator);
separator = " and ";
commandText.Append("g.");
var memberTSql = GenerateMemberTSql(keyMember);
commandText.Append(memberTSql);
commandText.Append(" = t.");
commandText.Append(memberTSql);
}
commandText.AppendLine();
commandText.AppendKeyword("where @@ROWCOUNT > 0");
}
else
{
// from
commandText.AppendKeyword("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
commandText.AppendKeyword("where @@ROWCOUNT > 0");
var table = ((DbScanExpression)tree.Target.Expression).Target;
var identity = false;
foreach (var keyMember in table.ElementType.KeyMembers)
{
commandText.AppendKeyword(" and ");
commandText.Append(GenerateMemberTSql(keyMember));
commandText.Append(" = ");
// retrieve member value sql. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL)
SqlParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
commandText.Append(value.ParameterName);
}
else
{
// if no value is registered for the key member, it means it is an identity
// which can be retrieved using the scope_identity() function
if (identity)
{
// there can be only one server generated key
throw new NotSupportedException(Strings.Update_NotSupportedServerGenKey(table.Name));
}
if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
throw new InvalidOperationException(
Strings.Update_NotSupportedIdentityType(
keyMember.Name, keyMember.TypeUsage.ToString()));
}
commandText.Append("scope_identity()");
identity = true;
}
}
}
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where IdentityValue = @@Identity
///
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">
/// Translator used to produce DML SQL statement
/// for the tree
/// </param>
/// <param name="returning">
/// Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.
/// </param>
private static void GenerateReturningSql(StringBuilder commandText, DbModificationCommandTree tree,
ExpressionTranslator translator, DbExpression returning)
{
// Nothing to do if there is no Returning expression
if (returning == null)
{
return;
}
// select
commandText.Append("select ");
returning.Accept(translator);
commandText.AppendLine();
// from
commandText.Append("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
commandText.Append("where ");
var table = ((DbScanExpression)tree.Target.Expression).Target;
var identity = false;
var first = true;
foreach (var keyMember in table.ElementType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(" and ");
}
commandText.Append(SQLiteProviderManifestHelper.QuoteIdentifier(keyMember.Name));
commandText.Append(" = ");
// retrieve member value sql. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL)
DbParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
commandText.Append(value.ParameterName);
}
else
{
// if no value is registered for the key member, it means it is an identity
// which can be retrieved using the @@identity function
if (identity)
{
// there can be only one server generated key
throw new
NotSupportedException(string
.Format("Server generated keys are only supported for identity columns. More than one key column is marked as server generated in table '{0}'.",
table.Name));
}
if (keyMember.TypeUsage.EdmType.Name.ToLower() == "guid")
// We can't retrieve the latest inserted guid from Access
//commandText.Append("@@guid");
{
commandText.AppendFormat("{{{0}}}", _lastGuid);
}
else
{
commandText.Append("@@identity");
}
identity = true;
}
}
}
internal DbExpressionEntitySetInfo VisitExpression(DbExpression expression)
{
return(expression.Accept(this));
}
protected override SqlFragment VisitBinaryExpression(DbExpression left, DbExpression right, string op)
{
BinaryFragment f = new BinaryFragment();
f.Operator = op;
f.Left = left.Accept(this);
f.WrapLeft = ShouldWrapExpression(left);
if (f.Left is ColumnFragment)
{
_columnsVisited.Push( (( DbPropertyExpression )left ).Property );
}
f.Right = right.Accept(this);
if (f.Left is ColumnFragment)
{
_columnsVisited.Pop();
}
f.WrapRight = ShouldWrapExpression(right);
return f;
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from MyTable
/// where IdentityValue = @@identity
///
/// NOTE: not scope_identity() because we don't support it.
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">Translator used to produce DML SQL statement
/// for the tree</param>
/// <param name="returning">Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.</param>
private static void GenerateReturningSql(
StringBuilder commandText, DbModificationCommandTree tree,
ExpressionTranslator translator, DbExpression returning)
{
if (returning != null)
{
commandText.Append("select ");
returning.Accept(translator);
commandText.AppendLine();
commandText.Append("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
commandText.Append("where ");
var target = ((DbScanExpression)tree.Target.Expression).Target;
var flag = false;
var isFirst = true;
foreach (var member in target.ElementType.KeyMembers)
{
DbParameter parameter;
if (!isFirst)
{
commandText.Append(" and ");
}
else
{
isFirst = false;
}
commandText.Append(GenerateMemberTSql(member));
commandText.Append(" = ");
if (translator.MemberValues.TryGetValue(member, out parameter))
{
commandText.Append(parameter.ParameterName);
}
else
{
if (flag)
{
throw ADP1.NotSupported(ADP1.Update_NotSupportedServerGenKey(target.Name));
}
if (!IsValidIdentityColumnType(member.TypeUsage))
{
throw ADP1.InvalidOperation(ADP1.Update_NotSupportedIdentityType(member.Name, member.TypeUsage.ToString()));
}
commandText.Append("@@IDENTITY");
flag = true;
}
}
}
}
internal static bool NeedsRewrite(DbExpression expr)
{
Sql8ConformanceChecker conformanceChecker = new Sql8ConformanceChecker();
return(expr.Accept <bool>((DbExpressionVisitor <bool>)conformanceChecker));
}
/// <summary>
/// Function to translate the StartsWith, EndsWith and Contains canonical functions to LIKE expression in T-SQL
/// and also add the trailing ESCAPE '~' when escaping of the search string for the LIKE expression has occurred
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="targetExpression"></param>
/// <param name="constSearchParamExpression"></param>
/// <param name="result"></param>
/// <param name="insertPercentStart"></param>
/// <param name="insertPercentEnd"></param>
private static void TranslateConstantParameterForLike(
SqlGenerator sqlgen, DbExpression targetExpression, DbConstantExpression constSearchParamExpression, SqlBuilder result,
bool insertPercentStart, bool insertPercentEnd)
{
result.Append(targetExpression.Accept(sqlgen));
result.Append(" LIKE ");
// If it's a DbConstantExpression then escape the search parameter if necessary.
bool escapingOccurred;
var searchParamBuilder = new StringBuilder();
if (insertPercentStart)
{
searchParamBuilder.Append("%");
}
searchParamBuilder.Append(
SqlProviderManifest.EscapeLikeText(constSearchParamExpression.Value as string, false, out escapingOccurred));
if (insertPercentEnd)
{
searchParamBuilder.Append("%");
}
var escapedSearchParamExpression = constSearchParamExpression.ResultType.Constant(searchParamBuilder.ToString());
result.Append(escapedSearchParamExpression.Accept(sqlgen));
// If escaping did occur (special characters were found), then append the escape character used.
if (escapingOccurred)
{
result.Append(" ESCAPE '" + SqlProviderManifest.LikeEscapeChar + "'");
}
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and IdentityValue = scope_identity()
///
/// or
///
/// select TimestamptValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and Id = 1
///
/// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">Translator used to produce DML SQL statement
/// for the tree</param>
/// <param name="returning">Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.</param>
/// <param name="wasInsert">
/// Non-zero if this method is being called as part of processing an INSERT;
/// otherwise (e.g. UPDATE), zero.
/// </param>
private static void GenerateReturningSql(StringBuilder commandText, DbModificationCommandTree tree,
ExpressionTranslator translator, DbExpression returning, bool wasInsert)
{
// Nothing to do if there is no Returning expression
if (null == returning)
{
return;
}
// select
commandText.Append("SELECT ");
returning.Accept(translator);
commandText.AppendLine();
// from
commandText.Append("FROM ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
#if USE_INTEROP_DLL && INTEROP_EXTENSION_FUNCTIONS
commandText.Append("WHERE last_rows_affected() > 0");
#else
commandText.Append("WHERE changes() > 0");
#endif
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
ReadOnlyMetadataCollection <EdmMember> keyMembers;
EdmMember primaryKeyMember;
EdmMember missingKeyMember;
// Model Types can be (at the time of this implementation):
// Binary, Boolean, Byte, DateTime, Decimal, Double, Guid, Int16,
// Int32, Int64,Single, String
if (IsIntegerPrimaryKey(table, out keyMembers, out primaryKeyMember))
{
//
// NOTE: This must be an INTEGER PRIMARY KEY (i.e. "rowid") table.
//
commandText.Append(" AND ");
commandText.Append(GenerateMemberTSql(primaryKeyMember));
commandText.Append(" = ");
DbParameter value;
if (translator.MemberValues.TryGetValue(primaryKeyMember, out value))
{
//
// NOTE: Use the integer primary key value that was specified as
// part the associated INSERT/UPDATE statement.
//
commandText.Append(value.ParameterName);
}
else if (wasInsert)
{
//
// NOTE: This was part of an INSERT statement and we know the table
// has an integer primary key. This should not fail unless
// something (e.g. a trigger) causes the last_insert_rowid()
// function to return an incorrect result.
//
commandText.AppendLine("last_insert_rowid()");
}
else /* NOT-REACHED? */
{
//
// NOTE: We cannot simply use the "rowid" at this point because:
//
// 1. The last_insert_rowid() function is only valid after
// an INSERT and this was an UPDATE.
//
throw new NotSupportedException(String.Format(
"Missing value for INSERT key member '{0}' in table '{1}'.",
(primaryKeyMember != null) ? primaryKeyMember.Name : "<unknown>",
table.Name));
}
}
else if (DoAllKeyMembersHaveValues(translator, keyMembers, out missingKeyMember))
{
foreach (EdmMember keyMember in keyMembers)
{
commandText.Append(" AND ");
commandText.Append(GenerateMemberTSql(keyMember));
commandText.Append(" = ");
// Retrieve member value SQL. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL).
DbParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
//
// NOTE: Use the primary key value that was specified as part the
// associated INSERT/UPDATE statement. This also applies
// to composite primary keys.
//
commandText.Append(value.ParameterName);
}
else /* NOT-REACHED? */
{
//
// NOTE: We cannot simply use the "rowid" at this point because:
//
// 1. This associated INSERT/UPDATE statement appeared to
// have all the key members availab;e however, there
// appears to be an inconsistency. This is an internal
// error and should be thrown.
//
throw new NotSupportedException(String.Format(
"Missing value for {0} key member '{1}' in table '{2}' " +
"(internal).", wasInsert ? "INSERT" : "UPDATE",
(keyMember != null) ? keyMember.Name : "<unknown>",
table.Name));
}
}
}
else if (wasInsert) /* NOT-REACHED? */
{
//
// NOTE: This was part of an INSERT statement; try using the "rowid"
// column to fetch the most recently inserted row. This may
// still fail if the table is a WITHOUT ROWID table -OR-
// something (e.g. a trigger) causes the last_insert_rowid()
// function to return an incorrect result.
//
commandText.Append(" AND ");
commandText.Append(SqlGenerator.QuoteIdentifier("rowid"));
commandText.Append(" = ");
commandText.AppendLine("last_insert_rowid()");
}
else /* NOT-REACHED? */
{
//
// NOTE: We cannot simply use the "rowid" at this point because:
//
// 1. The last_insert_rowid() function is only valid after
// an INSERT and this was an UPDATE.
//
throw new NotSupportedException(String.Format(
"Missing value for UPDATE key member '{0}' in table '{1}'.",
(missingKeyMember != null) ? missingKeyMember.Name : "<unknown>",
table.Name));
}
commandText.AppendLine(";");
}
