internal DynamicUpdateCommand(TableChangeProcessor processor, UpdateTranslator translator, ModificationOperator op,
PropagatorResult originalValues, PropagatorResult currentValues, DbModificationCommandTree tree,
Dictionary<int, string> outputIdentifiers)
: base(originalValues, currentValues)
{
m_processor = EntityUtil.CheckArgumentNull(processor, "processor");
m_operator = op;
m_modificationCommandTree = EntityUtil.CheckArgumentNull(tree, "commandTree");
m_outputIdentifiers = outputIdentifiers; // may be null (not all commands have output identifiers)
// initialize identifier information (supports lateral propagation of server gen values)
if (ModificationOperator.Insert == op || ModificationOperator.Update == op)
{
const int capacity = 2; // "average" number of identifiers per row
m_inputIdentifiers = new List<KeyValuePair<int ,DbSetClause>>(capacity);
foreach (KeyValuePair<EdmMember, PropagatorResult> member in
Helper.PairEnumerations(TypeHelpers.GetAllStructuralMembers(this.CurrentValues.StructuralType),
this.CurrentValues.GetMemberValues()))
{
DbSetClause setter;
int identifier = member.Value.Identifier;
if (PropagatorResult.NullIdentifier != identifier &&
TryGetSetterExpression(tree, member.Key, op, out setter)) // can find corresponding setter
{
foreach (int principal in translator.KeyManager.GetPrincipals(identifier))
{
m_inputIdentifiers.Add(new KeyValuePair<int, DbSetClause>(principal, setter));
}
}
}
}
}
// effects: try to find setter expression for the given member
// requires: command tree must be an insert or update tree (since other DML trees hnabve
private static bool TryGetSetterExpression(DbModificationCommandTree tree, EdmMember member, ModificationOperator op, out DbSetClause setter)
{
Debug.Assert(op == ModificationOperator.Insert || op == ModificationOperator.Update, "only inserts and updates have setters");
IEnumerable<DbModificationClause> clauses;
if (ModificationOperator.Insert == op)
{
clauses = ((DbInsertCommandTree)tree).SetClauses;
}
else
{
clauses = ((DbUpdateCommandTree)tree).SetClauses;
}
foreach (DbSetClause setClause in clauses)
{
// check if this is the correct setter
if (((DbPropertyExpression)setClause.Property).Property.EdmEquals(member))
{
setter = setClause;
return true;
}
}
// no match found
setter = null;
return false;
}
protected virtual SelectStatement GenerateReturningSql(DbModificationCommandTree tree, DbExpression returning)
{
SelectStatement select = base.GenerateReturningSql(tree, returning);
ListFragment where = new ListFragment();
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
bool foundIdentity = false;
where.Append(" row_count() > 0");
foreach (EdmMember keyMember in table.ElementType.KeyMembers)
{
SqlFragment value;
if (!values.TryGetValue(keyMember, out value))
{
if (foundIdentity)
throw new NotSupportedException();
foundIdentity = true;
value = new LiteralFragment("last_insert_id()");
}
where.Append(String.Format(" AND `{0}`=", keyMember));
where.Append(value);
}
select.Where = where;
return select;
}
protected virtual SelectStatement GenerateReturningSql(DbModificationCommandTree tree, DbExpression returning)
{
SelectStatement select = base.GenerateReturningSql(tree, returning);
ListFragment where = new ListFragment();
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
bool foundIdentity = false;
where.Append(" row_count() > 0");
foreach (EdmMember keyMember in table.ElementType.KeyMembers)
{
SqlFragment value;
if (!values.TryGetValue(keyMember, out value))
{
if (foundIdentity)
throw new NotSupportedException();
foundIdentity = true;
if (keyMember.TypeUsage.EdmType.BaseType.Name.StartsWith("Int"))
value = new LiteralFragment("last_insert_id()");
else if (keyMember.TypeUsage.EdmType.BaseType.Name == "Guid")
value = new LiteralFragment(string.Format("ANY(SELECT guid FROM tmpIdentity_{0})", (table as MetadataItem).MetadataProperties["Table"].Value));
}
where.Append(String.Format(" AND `{0}`=", keyMember));
where.Append(value);
}
select.Where = where;
return select;
}
protected override SelectStatement GenerateReturningSql(DbModificationCommandTree tree, DbExpression returning)
{
SelectStatement select = base.GenerateReturningSql(tree, returning);
ListFragment where = new ListFragment();
where.Append(" row_count() > 0 and ");
where.Append( ((System.Data.Common.CommandTrees.DbUpdateCommandTree)tree).Predicate.Accept(this) );
select.Where = where;
return select;
}
/// <summary>
/// Initialize a new expression translator populating the given string builder
/// with command text. Command text builder and command tree must not be null.
/// </summary>
/// <param name="commandText">Command text with which to populate commands</param>
/// <param name="commandTree">Command tree generating SQL</param>
/// <param name="preserveMemberValues">Indicates whether the translator should preserve
/// member values while compiling t-SQL (only needed for server generation)</param>
internal ExpressionTranslator(
StringBuilder commandText,
DbModificationCommandTree commandTree,
bool preserveMemberValues)
{
Debug.Assert(null != commandText);
Debug.Assert(null != commandTree);
this.commandText = commandText;
this.commandTree = commandTree;
this.parameters = new List<DbParameter>();
this.memberValues = preserveMemberValues ? new Dictionary<EdmMember, List<DbParameter>>() : null;
}
internal DynamicUpdateCommand(
TableChangeProcessor processor, UpdateTranslator translator,
ModificationOperator modificationOperator, PropagatorResult originalValues, PropagatorResult currentValues,
DbModificationCommandTree tree, Dictionary<int, string> outputIdentifiers)
: base(translator, originalValues, currentValues)
{
//Contract.Requires(processor != null);
//Contract.Requires(translator != null);
//Contract.Requires(tree != null);
_processor = processor;
_operator = modificationOperator;
_modificationCommandTree = tree;
_outputIdentifiers = outputIdentifiers; // may be null (not all commands have output identifiers)
// initialize identifier information (supports lateral propagation of server gen values)
if (ModificationOperator.Insert == modificationOperator
|| ModificationOperator.Update == modificationOperator)
{
const int capacity = 2; // "average" number of identifiers per row
_inputIdentifiers = new List<KeyValuePair<int, DbSetClause>>(capacity);
foreach (var member in
Helper.PairEnumerations(
TypeHelpers.GetAllStructuralMembers(CurrentValues.StructuralType),
CurrentValues.GetMemberValues()))
{
DbSetClause setter;
var identifier = member.Value.Identifier;
if (PropagatorResult.NullIdentifier != identifier
&&
TryGetSetterExpression(tree, member.Key, modificationOperator, out setter)) // can find corresponding setter
{
foreach (var principal in translator.KeyManager.GetPrincipals(identifier))
{
_inputIdentifiers.Add(new KeyValuePair<int, DbSetClause>(principal, setter));
}
}
}
}
}
/// <summary>
/// Initialize a new expression translator populating the given string builder
/// with command text. Command text builder and command tree must not be null.
/// </summary>
/// <param name="commandText">Command text with which to populate commands</param>
/// <param name="commandTree">Command tree generating SQL</param>
/// <param name="preserveMemberValues">Indicates whether the translator should preserve
/// member values while compiling t-SQL (only needed for server generation)</param>
internal ExpressionTranslator(
StringBuilder commandText, DbModificationCommandTree commandTree,
bool preserveMemberValues, bool isLocalProvider)
{
Debug.Assert(null != commandText);
Debug.Assert(null != commandTree);
_commandText = commandText;
_commandTree = commandTree;
_parameters = new List<DbParameter>();
_memberValues = preserveMemberValues
? new Dictionary<EdmMember, DbParameter>()
: null;
_isLocalProvider = isLocalProvider;
}
/// <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;
}
}
}
}
/// <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;
}
}
}
/// <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 (returning == null)
{
return;
}
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
IEnumerable<EdmMember> columnsToFetch =
table.ElementType.Members
.Where(m => IsStoreGenerated(m))
.Except((!(tree is DbInsertCommandTree) ? table.ElementType.KeyMembers : Enumerable.Empty<EdmMember>()));
StringBuilder startBlock = new StringBuilder();
string separator = string.Empty;
startBlock.AppendLine("EXECUTE BLOCK (");
separator = string.Empty;
foreach (FbParameter param in translator.Parameters)
{
startBlock.Append(separator);
startBlock.Append(param.ParameterName.Replace("@", string.Empty));
startBlock.Append(" ");
EdmMember member = translator.MemberValues.First(m => m.Value.Contains(param)).Key;
startBlock.Append(SqlGenerator.GetSqlPrimitiveType(member.TypeUsage));
if (param.FbDbType == FbDbType.VarChar || param.FbDbType == FbDbType.Char)
startBlock.Append(" CHARACTER SET UTF8");
startBlock.Append(" = ");
startBlock.Append(param.ParameterName);
separator = ", ";
}
startBlock.AppendLine(") ");
startBlock.AppendLine("RETURNS (");
separator = string.Empty;
foreach (EdmMember m in columnsToFetch)
{
startBlock.Append(separator);
startBlock.Append(GenerateMemberSql(m));
startBlock.Append(" ");
startBlock.Append(SqlGenerator.GetSqlPrimitiveType(m.TypeUsage));
separator = ", ";
}
startBlock.AppendLine(")");
startBlock.AppendLine("AS BEGIN");
string newCommand = ChangeParamsToPSQLParams(commandText.ToString(), translator.Parameters.Select(p => p.ParameterName).ToArray());
commandText.Remove(0, commandText.Length);
commandText.Insert(0, newCommand);
commandText.Insert(0, startBlock.ToString());
commandText.Append("RETURNING ");
separator = string.Empty;
foreach (EdmMember m in columnsToFetch)
{
commandText.Append(separator);
commandText.Append(GenerateMemberSql(m));
separator = ", ";
}
commandText.Append(" INTO ");
separator = string.Empty;
foreach (EdmMember m in columnsToFetch)
{
commandText.Append(separator);
commandText.Append(":" + GenerateMemberSql(m));
separator = ", ";
}
commandText.AppendLine(";");
commandText.AppendLine("SUSPEND;");
commandText.AppendLine("END");
}
private DbModificationCommandTree RebuildCommandTree(DbModificationCommandTree originalTree, Dictionary<DbSetClause, DbSetClause> clauseMappings)
{
if (clauseMappings.Count == 0)
{
return originalTree;
}
DbModificationCommandTree result;
Debug.Assert(originalTree.CommandTreeKind == DbCommandTreeKind.Insert || originalTree.CommandTreeKind == DbCommandTreeKind.Update, "Set clauses specified for a modification tree that is not an update or insert tree?");
if (originalTree.CommandTreeKind == DbCommandTreeKind.Insert)
{
DbInsertCommandTree insertTree = (DbInsertCommandTree)originalTree;
result = new DbInsertCommandTree(insertTree.MetadataWorkspace, insertTree.DataSpace,
insertTree.Target, ReplaceClauses(insertTree.SetClauses, clauseMappings).AsReadOnly(), insertTree.Returning);
}
else
{
DbUpdateCommandTree updateTree = (DbUpdateCommandTree)originalTree;
result = new DbUpdateCommandTree(updateTree.MetadataWorkspace, updateTree.DataSpace,
updateTree.Target, updateTree.Predicate, ReplaceClauses(updateTree.SetClauses, clauseMappings).AsReadOnly(), updateTree.Returning);
}
return result;
}
/// <summary>
/// Creates a command in the current context.
/// </summary>
/// <param name="commandTree">DbCommand tree</param>
/// <returns>DbCommand produced by the current provider.</returns>
internal DbCommand CreateCommand(DbModificationCommandTree commandTree)
{
DbCommand command;
Debug.Assert(null != m_providerServices, "constructor ensures either the command definition " +
"builder or provider service is available");
Debug.Assert(null != m_connection.StoreConnection, "EntityAdapter.Update ensures the store connection is set");
try
{
command = m_providerServices.CreateCommand(commandTree);
}
catch (Exception e)
{
// we should not be wrapping all exceptions
if (UpdateTranslator.RequiresContext(e))
{
// we don't wan't folks to have to know all the various types of exceptions that can
// occur, so we just rethrow a CommandDefinitionException and make whatever we caught
// the inner exception of it.
throw EntityUtil.CommandCompilation(System.Data.Entity.Strings.EntityClient_CommandDefinitionPreparationFailed, e);
}
throw;
}
return command;
}
/// <summary>
/// Initialize a new expression translator populating the given string builder
/// with command text. Command text builder and command tree must not be null.
/// </summary>
/// <param name="commandText">Command text with which to populate commands</param>
/// <param name="commandTree">Command tree generating SQL</param>
/// <param name="preserveMemberValues">Indicates whether the translator should preserve
/// member values while compiling t-SQL (only needed for server generation)</param>
internal ExpressionTranslator(StringBuilder commandText, DbModificationCommandTree commandTree,
bool preserveMemberValues, SqlVersion version)
{
Debug.Assert(null != commandText);
Debug.Assert(null != commandTree);
_commandText = commandText;
_commandTree = commandTree;
_version = version;
_parameters = new List<SqlParameter>();
_memberValues = preserveMemberValues ? new Dictionary<EdmMember, SqlParameter>() :
null;
}
/// <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 ");
string separator = string.Empty;
foreach (EdmMember keyMember in tableType.KeyMembers)
{
commandText.Append(separator);
separator = " and ";
commandText.Append("g.");
string 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");
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)
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 EntityUtil.NotSupported(System.Data.Entity.Strings.Update_NotSupportedServerGenKey(table.Name));
}
if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.Update_NotSupportedIdentityType(
keyMember.Name, keyMember.TypeUsage.ToString()));
}
commandText.Append("scope_identity()");
identity = true;
}
}
}
}
