/// <summary>
/// Creates sET and UPDATE statements SQL text for a row being modified where an optimistic concurrency model is used.
/// </summary>
/// <returns>The UPDATE SQL query.</returns>
private string GetSqlForModifiedRowUsingOptimisticConcurrency()
{
var parentTable = MySqlTable;
if (parentTable == null || RowState != DataRowState.Modified)
{
return(string.Empty);
}
// Reuse builders instead of using new ones in order to save memory.
var setVariablesBuilder = parentTable.SqlBuilderForDelete;
var wClauseBuilder = parentTable.SqlBuilderForInsert;
var sqlBuilderForUpdate = parentTable.SqlBuilderForUpdate;
setVariablesBuilder.Clear();
wClauseBuilder.Clear();
sqlBuilderForUpdate.Clear();
string serverCollation = parentTable.WbConnection.ServerCollation;
string setSeparator = "SET";
string colsSeparator = string.Empty;
string wClauseColsSeparator = string.Empty;
wClauseBuilder.Append(" WHERE ");
sqlBuilderForUpdate.Append(MySqlStatement.STATEMENT_UPDATE);
sqlBuilderForUpdate.AppendFormat(" `{0}`.`{1}` SET ", parentTable.SchemaName, parentTable.TableNameForSqlQueries);
foreach (MySqlDataColumn column in parentTable.Columns)
{
bool updatingValueIsNull;
bool columnRequiresQuotes = column.MySqlDataType.RequiresQuotesForValue;
bool columnIsText = column.MySqlDataType.IsChar || column.MySqlDataType.IsText || column.MySqlDataType.IsSetOrEnum;
bool columnIsJson = column.MySqlDataType.IsJson;
string valueToDb = column.GetStringValue(this[column.ColumnName, DataRowVersion.Original], out updatingValueIsNull);
string wrapValueCharacter = columnRequiresQuotes && !updatingValueIsNull ? "'" : string.Empty;
if (column.AllowNull)
{
var columnCollation = column.AbsoluteCollation;
bool needToCollateTextValue = columnIsText && !updatingValueIsNull &&
serverCollation != null &&
!serverCollation.Equals(columnCollation, StringComparison.InvariantCultureIgnoreCase) &&
columnCollation.IsUnicodeCharSetOrCollation();
// If the length of the string value * 2 is greater than the string it requires to declare a variable for it, then declare the variable to save query space.
bool needToCreateVariable = (valueToDb.Length * 2) > (valueToDb.Length + 24 + (needToCollateTextValue ? columnCollation.Length + 9 : 0));
string valueForClause;
if (needToCreateVariable)
{
valueForClause = "@OldCol" + (column.Ordinal + 1) + "Value";
setVariablesBuilder.AppendFormat("{0} {1} = {2}{3}{2}", setSeparator, valueForClause, wrapValueCharacter, valueToDb);
setSeparator = ",";
if (needToCollateTextValue)
{
setVariablesBuilder.Append(" COLLATE ");
setVariablesBuilder.Append(columnCollation);
}
}
else
{
valueForClause = string.Format("{0}{1}{0}", wrapValueCharacter, valueToDb);
}
wClauseBuilder.AppendFormat("{0}(({2} IS NULL AND `{1}` IS NULL) ", wClauseColsSeparator, column.ColumnNameForSqlQueries, valueForClause);
wClauseBuilder.AppendFormat(columnIsJson && !updatingValueIsNull ? "OR `{0}`=CAST({1} AS JSON))" : "OR `{0}`={1})", column.ColumnNameForSqlQueries, valueForClause);
}
else
{
wClauseBuilder.AppendFormat(columnIsJson && !updatingValueIsNull ? "{0}`{1}`=CAST({2}{3}{2} AS JSON)" : "{0}`{1}`={2}{3}{2}", wClauseColsSeparator, column.ColumnNameForSqlQueries, wrapValueCharacter, valueToDb);
}
wClauseColsSeparator = " AND ";
if (!ChangedColumnNames.Contains(column.ColumnName))
{
continue;
}
valueToDb = column.GetStringValue(this[column.ColumnName], out updatingValueIsNull);
wrapValueCharacter = columnRequiresQuotes && !updatingValueIsNull ? "'" : string.Empty;
sqlBuilderForUpdate.AppendFormat("{0}`{1}`={2}{3}{2}", colsSeparator, column.ColumnNameForSqlQueries, wrapValueCharacter, valueToDb);
colsSeparator = ",";
}
if (setVariablesBuilder.Length > 0)
{
_setVariablesSql = setVariablesBuilder.ToString();
}
sqlBuilderForUpdate.Append(wClauseBuilder);
return(sqlBuilderForUpdate.ToString());
}
/// <summary>
/// Creates an UPDATE statement SQL text for a row being modified.
/// </summary>
/// <returns>The UPDATE SQL query.</returns>
private string GetSqlForModifiedRow()
{
var parentTable = MySqlTable;
if (parentTable == null || RowState != DataRowState.Modified)
{
return(string.Empty);
}
var sqlBuilderForUpdate = parentTable.SqlBuilderForUpdate;
sqlBuilderForUpdate.Clear();
string colsSeparator = string.Empty;
sqlBuilderForUpdate.Append(MySqlStatement.STATEMENT_UPDATE);
sqlBuilderForUpdate.AppendFormat(" `{0}`.`{1}` SET ", parentTable.SchemaName, parentTable.TableNameForSqlQueries);
foreach (var column in parentTable.Columns.Cast <MySqlDataColumn>().Where(col => ChangedColumnNames.Contains(col.ColumnName)))
{
bool updatingValueIsNull;
var valueToDb = column.GetStringValue(this[column.ColumnName], out updatingValueIsNull);
string wrapValueCharacter = column.MySqlDataType.RequiresQuotesForValue && !updatingValueIsNull ? "'" : string.Empty;
sqlBuilderForUpdate.AppendFormat("{0}`{1}`={2}{3}{2}", colsSeparator, column.ColumnNameForSqlQueries, wrapValueCharacter, valueToDb);
colsSeparator = ",";
}
sqlBuilderForUpdate.Append(GetWhereClauseFromPrimaryKey(true));
return(sqlBuilderForUpdate.ToString());
}
