internal static string GenerateUpdateSql(DbUpdateCommandTree tree, out List<DbParameter> parameters, bool generateParameters = true)
{
StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, generateParameters);
bool first = true;
commandText.Append("UPDATE ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// set c1 = ..., c2 = ..., ...
commandText.Append("SET ");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
commandText.Append(" = ");
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
if (first)
{
// If first is still true, it indicates there were no set
// clauses. Introduce a fake set clause so that:
// - we acquire the appropriate locks
// - server-gen columns (e.g. timestamp) get recomputed
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
// hope this column isn't indexed to not waste power
EdmMember someColumn = table.ElementType.Members.Last(x => !MetadataHelpers.IsStoreGenerated(x));
commandText.AppendFormat("{0} = {0}", GenerateMemberSql(someColumn));
}
commandText.AppendLine();
// where c1 = ..., c2 = ...
commandText.Append("WHERE ");
tree.Predicate.Accept(translator);
commandText.AppendLine();
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateUpdateSql(DbUpdateCommandTree tree, SqlVersion sqlVersion, out List<SqlParameter> parameters)
{
const string dummySetParameter = "@p";
StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, sqlVersion);
if (tree.SetClauses.Count == 0)
{
commandText.AppendLine("declare " + dummySetParameter + " int");
}
// update [schemaName].[tableName]
commandText.Append("update ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// set c1 = ..., c2 = ..., ...
bool first = true;
commandText.Append("set ");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Property.Accept(translator);
commandText.Append(" = ");
setClause.Value.Accept(translator);
}
if (first)
{
// If first is still true, it indicates there were no set
// clauses. Introduce a fake set clause so that:
// - we acquire the appropriate locks
// - server-gen columns (e.g. timestamp) get recomputed
//
// We use the following pattern:
//
// update Foo
// set @p = 0
// where ...
commandText.Append(dummySetParameter + " = 0");
}
commandText.AppendLine();
// where c1 = ..., c2 = ...
commandText.Append("where ");
tree.Predicate.Accept(translator);
commandText.AppendLine();
// generate returning sql
GenerateReturningSql(commandText, tree, null, translator, tree.Returning, false);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateUpdateSql(DbUpdateCommandTree tree, out List<DbParameter> parameters)
{
StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning);
// update [schemaName].[tableName]
commandText.Append("update ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// set c1 = ..., c2 = ..., ...
bool first = true;
commandText.Append("set ");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Property.Accept(translator);
commandText.Append(" = ");
setClause.Value.Accept(translator);
}
if (first)
{
// If first is still true, it indicates there were no set
// clauses. Introduce a fake set clause so that:
// - we acquire the appropriate locks
// - server-gen columns (e.g. timestamp) get recomputed
//
// We use the following pattern:
//
// update SomeTable
// set @i = 0
// where ...
DbParameter parameter =
translator.CreateParameter(
default(Int32),
TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32)));
commandText.Append(parameter.ParameterName);
commandText.Append(" = 0");
}
commandText.AppendLine();
// where c1 = ..., c2 = ...
commandText.Append("where ");
tree.Predicate.Accept(translator);
commandText.AppendLine();
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateDeleteSql(DbDeleteCommandTree tree, out List<DbParameter> parameters)
{
StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, false);
// delete [schemaName].[tableName]
commandText.Append("delete ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where c1 = ... AND c2 = ...
commandText.Append("where ");
tree.Predicate.Accept(translator);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters)
{
StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, "InsertFunction");
// insert [schemaName].[tableName]
commandText.Append("INSERT INTO ");
tree.Target.Expression.Accept(translator);
if (tree.SetClauses.Count > 0)
{
// (c1, c2, c3, ...)
commandText.Append("(");
bool first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append(" VALUES (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(");");
}
else // No columns specified. Insert an empty row containing default values by inserting null into the rowid
{
commandText.AppendLine(" DEFAULT VALUES;");
}
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateDeleteSql(DbDeleteCommandTree tree, out List<DbParameter> parameters, bool generateParameters = true)
{
StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, false, generateParameters);
commandText.Append("DELETE FROM ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where c1 = ... AND c2 = ...
commandText.Append("WHERE ");
tree.Predicate.Accept(translator);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters, bool generateParameters = true)
{
StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, generateParameters);
bool first = true;
commandText.Append("INSERT INTO ");
tree.Target.Expression.Accept(translator);
// (c1, c2, c3, ...)
commandText.Append("(");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append("VALUES (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
parameters = translator.Parameters;
return commandText.ToString();
}
/// <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 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 string[] GenerateDeleteSql(DbDeleteCommandTree tree, out List<DbParameter> parameters, bool isLocalProvider)
{
var commandTexts = new List<String>();
var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
var translator = new ExpressionTranslator(commandText, tree, false, isLocalProvider);
// delete [schemaName].[tableName]
commandText.Append("delete ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where c1 = ... AND c2 = ...
commandText.Append("where ");
tree.Predicate.Accept(translator);
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
parameters = translator.Parameters;
return commandTexts.ToArray();
}
// <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);
}
}
}
/// <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 => MetadataHelpers.IsStoreGenerated(m))
.Except((!(tree is DbInsertCommandTree) ? table.ElementType.KeyMembers : Enumerable.Empty<EdmMember>()));
StringBuilder startBlock = new StringBuilder();
string separator = string.Empty;
startBlock.Append("EXECUTE BLOCK ");
if (translator.Parameters.Any())
{
startBlock.AppendLine("(");
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.Append(") ");
}
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");
}
/// <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>
/// This method attempts to determine if all the specified key members have
/// values available.
/// </summary>
/// <param name="translator">
/// The <see cref="ExpressionTranslator" /> to use.
/// </param>
/// <param name="keyMembers">
/// The collection of key members to check.
/// </param>
/// <param name="missingKeyMember">
/// The first missing key member that is found. This is only set to a valid
/// value if the method is returning false.
/// </param>
/// <returns>
/// Non-zero if all key members have values; otherwise, zero.
/// </returns>
private static bool DoAllKeyMembersHaveValues(
ExpressionTranslator translator,
ReadOnlyMetadataCollection<EdmMember> keyMembers,
out EdmMember missingKeyMember
)
{
foreach (EdmMember keyMember in keyMembers)
{
if (!translator.MemberValues.ContainsKey(keyMember))
{
missingKeyMember = keyMember;
return false;
}
}
missingKeyMember = null;
return true;
}
/// <summary>
/// Determine whether we should use a generated values variable to return server generated values.
/// This is true when we're attempting to insert a row where the primary key is server generated
/// but is not an integer type (and therefore can't be used with scope_identity()). It is also true
/// where there is a compound server generated key.
/// </summary>
private static bool UseGeneratedValuesVariable(DbInsertCommandTree tree, SqlVersion sqlVersion, ExpressionTranslator translator)
{
bool useGeneratedValuesVariable = false;
if (sqlVersion > SqlVersion.Sql8 && tree.Returning != null)
{
// Figure out which columns have values
HashSet<EdmMember> columnsWithValues = new HashSet<EdmMember>(tree.SetClauses.Cast<DbSetClause>().Select(s => ((DbPropertyExpression)s.Property).Property));
// Only SQL Server 2005+ support an output clause for inserts
bool firstKeyFound = false;
foreach (EdmMember keyMember in ((DbScanExpression)tree.Target.Expression).Target.ElementType.KeyMembers)
{
if (!columnsWithValues.Contains(keyMember))
{
if (firstKeyFound)
{
// compound server gen key
useGeneratedValuesVariable = true;
break;
}
else
{
firstKeyFound = true;
if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
// unsupported type
useGeneratedValuesVariable = true;
break;
}
}
}
}
}
return useGeneratedValuesVariable;
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, SqlVersion sqlVersion, out List<SqlParameter> parameters)
{
var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
var translator = new ExpressionTranslator(
commandText, tree,
null != tree.Returning, sqlVersion);
var useGeneratedValuesVariable = UseGeneratedValuesVariable(tree, sqlVersion);
var tableType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType;
if (useGeneratedValuesVariable)
{
// manufacture the variable, e.g. "declare @generated_values table(id uniqueidentifier)"
commandText
.Append("declare ")
.Append(s_generatedValuesVariableName)
.Append(" table(");
var first = true;
foreach (var column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
var columnType = SqlGenerator.GenerateSqlForStoreType(sqlVersion, column.TypeUsage);
if (columnType == "rowversion"
|| columnType == "timestamp")
{
// rowversion and timestamp are intrinsically read-only. use binary to gather server generated
// values for these types.
columnType = "binary(8)";
}
commandText
.Append(GenerateMemberTSql(column))
.Append(" ")
.Append(columnType);
Facet collationFacet;
if (column.TypeUsage.Facets.TryGetValue(DbProviderManifest.CollationFacetName, false, out collationFacet))
{
var collation = collationFacet.Value as string;
if (!string.IsNullOrEmpty(collation))
{
commandText.Append(" collate ").Append(collation);
}
}
}
Debug.Assert(!first, "if useGeneratedValuesVariable is true, it implies some columns do not have values");
commandText.AppendLine(")");
}
// insert [schemaName].[tableName]
commandText.Append("insert ");
tree.Target.Expression.Accept(translator);
if (0 < tree.SetClauses.Count)
{
// (c1, c2, c3, ...)
commandText.Append("(");
var first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
}
else
{
commandText.AppendLine();
}
if (useGeneratedValuesVariable)
{
// output inserted.id into @generated_values
commandText.Append("output ");
var first = true;
foreach (var column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
commandText.Append("inserted.");
commandText.Append(GenerateMemberTSql(column));
}
commandText
.Append(" into ")
.AppendLine(s_generatedValuesVariableName);
}
if (0 < tree.SetClauses.Count)
{
// values c1, c2, ...
var first = true;
commandText.Append("values (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
}
else
{
// default values
commandText.AppendLine("default values");
}
// generate returning sql
GenerateReturningSql(commandText, tree, tableType, translator, tree.Returning, useGeneratedValuesVariable);
parameters = translator.Parameters;
return commandText.ToString();
}
/// <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;
}
}
}
}
internal static bool HandleIdentity(
SqlStringBuilder commandText, ExpressionTranslator translator, EdmMember member, bool flag, EntitySetBase target)
{
DebugCheck.NotNull(commandText);
DebugCheck.NotNull(translator);
DebugCheck.NotNull(member);
DebugCheck.NotNull(target);
DbParameter parameter;
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("CAST (@@IDENTITY AS ")
.Append(member.TypeUsage.EdmType.Name)
.Append(")");
flag = true;
}
return flag;
}
internal static string[] GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters, bool isLocalProvider)
{
var commandTexts = new List<String>();
var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
var translator = new ExpressionTranslator(
commandText, tree,
null != tree.Returning, isLocalProvider);
// insert [schemaName].[tableName]
commandText.Append("insert ");
tree.Target.Expression.Accept(translator);
if (0 < tree.SetClauses.Count)
{
// (c1, c2, c3, ...)
commandText.Append("(");
var first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append("values (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
}
else
{
// default values
throw ADP1.NotSupported("Default values not supported");
}
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
if (!String.IsNullOrEmpty(commandText.ToString()))
{
commandTexts.Add(commandText.ToString());
}
parameters = translator.Parameters;
return commandTexts.ToArray();
}
internal static string GenerateDeleteSql(
DbDeleteCommandTree tree,
SqlGenerator sqlGenerator,
out List<SqlParameter> parameters,
bool upperCaseKeywords = true,
bool createParameters = true)
{
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator
= new ExpressionTranslator(
commandText,
tree,
false,
sqlGenerator,
createParameters: createParameters);
// delete [schemaName].[tableName]
commandText.AppendKeyword("delete ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where c1 = ... AND c2 = ...
commandText.AppendKeyword("where ");
tree.Predicate.Accept(translator);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string[] GenerateUpdateSql(DbUpdateCommandTree tree, out List<DbParameter> parameters, bool isLocalProvider)
{
var commandTexts = new List<String>();
var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
var translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, isLocalProvider);
// update [schemaName].[tableName]
commandText.Append("update ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// set c1 = ..., c2 = ..., ...
var first = true;
commandText.Append("set ");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
commandText.Append(" = ");
setClause.Value.Accept(translator);
}
if (first)
{
// If first is still true, it indicates there were no set
// clauses.
// - we acquire the appropriate locks
// - server-gen columns (e.g. timestamp) get recomputed
//
// Bug fix #13533 : A fake update DML updating some column item
// with the same value as before to acquire the lock on the table
// while updating some columns in another table. This happens when
// both the table are dependent on an entity and the members of entity
// which is mapped to one table is being updated and the other table
// needs to be locked for consistancy.
string updatableColumnName;
if (GetUpdatableColumn(tree, out updatableColumnName))
{
commandText.Append("[");
commandText.Append(CommonUtils.EscapeSquareBraceNames(updatableColumnName));
commandText.Append("] ");
commandText.Append(" = ");
commandText.Append("[");
commandText.Append(CommonUtils.EscapeSquareBraceNames(updatableColumnName));
commandText.Append("] ");
}
else
{
// Throw some meaningful error
throw ADP1.Update(
EntityRes.GetString(EntityRes.UpdateStatementCannotBeGeneratedForAcquiringLock),
null);
}
}
commandText.AppendLine();
// where c1 = ..., c2 = ...
commandText.Append("where ");
tree.Predicate.Accept(translator);
commandText.AppendLine();
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
if (!String.IsNullOrEmpty(commandText.ToString()))
{
commandTexts.Add(commandText.ToString());
}
parameters = translator.Parameters;
return commandTexts.ToArray();
}
/// <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(";");
}
