public virtual CommandDefinition ToUpdateStatement(UpdateStatement statement, CommandFlags flags)
{
var query = new StringBuilder("UPDATE ");
query.Append(statement.TableName);
query.Append(" SET ");
var first = true;
foreach (var f in statement.UpdateFields)
{
if (!first)
{
query.Append(", ");
}
query.Append(f.Key);
query.Append("=");
query.Append(f.Value);
first = false;
}
if (statement.WhereExpression.Length > 0)
{
query.Append(" WHERE ");
query.Append(statement.WhereExpression);
}
return(new CommandDefinition(query.ToString(), statement.Parameters, flags: flags));
}
public override object InternalExecute(Program program, object[] arguments)
{
var tableVar = APIUtilities.ResolveTableVarName(program, (string)arguments[0]);
var identifyingKey = APIUtilities.FindIdentifyingKey(program, tableVar);
var keyValues = APIUtilities.GetKeyValues(program, identifyingKey, (string)arguments[1]);
var keyEqualExpression = Compiler.BuildKeyEqualExpression(program.Plan, String.Empty, "AKey", new Schema.RowType(identifyingKey.Columns).Columns, keyValues.DataType.Columns);
var updateStatement = new UpdateStatement();
updateStatement.Target = new IdentifierExpression(tableVar.Name);
var row = (IRow)arguments[2];
for (int i = 0; i < row.DataType.Columns.Count; i++)
{
updateStatement.Columns.Add(new UpdateColumnExpression(new IdentifierExpression(row.DataType.Columns[i].Name), new IdentifierExpression(Schema.Object.Qualify(row.DataType.Columns[i].Name, "ARow"))));
}
updateStatement.Condition = keyEqualExpression;
var statement = new D4TextEmitter().Emit(updateStatement);
var dataParams = new DataParams();
dataParams.Add(new DataParam("AKey", keyValues.DataType, Modifier.In, keyValues));
dataParams.Add(new DataParam("ARow", row.DataType, Modifier.In, row));
APIUtilities.Execute(program, statement, dataParams);
return(null);
}
public void Update_Statement_With_Top_N_Clause_With_Missing_Value()
{
// Exercise
UpdateStatement statement = ParserFactory.Execute <UpdateStatement>(
@"update top () t set field = 1 from dbo.table as t join dbo.other o on o.id = a.id where field <> 2"
).First();
}
public static IList <FieldPairReference> GetFieldPairs(
this UpdateStatement updateStatement,
ILogger logger,
SchemaFile file
)
{
// TODO : make use of cteReferences below? or would that mean the scope can get polluted?
var ctePairs = updateStatement
.WithCtesAndXmlNamespaces
?.CommonTableExpressions
.GetFieldPairReferences(logger, file)
.ToList()
?? new List <FieldPairReference>();
var cteReferences = updateStatement
.WithCtesAndXmlNamespaces
?.CommonTableExpressions
.GetSchemaObjectReferences(logger, file)
.ToList()
?? new List <SchemaObjectReference>();
using (new StatementContext(file.FileContext, cteReferences))
{
var updateSpecificationPairs = updateStatement
.UpdateSpecification
.GetFieldPairReferences(logger, file)
.ToList();
return(ctePairs
.Concat(updateSpecificationPairs)
.ToList());
}
}
protected override void VisitUpdate(UpdateStatement statement)
{
State.Write(Symbols.UPDATE);
VisitToken(statement.Target, true);
if (statement.RecordsetSource != null)
{
if (statement.RecordsetSource.Source is Name)
{
State.Write(Symbols.Comma);
VisitToken(statement.RecordsetSource);
}
}
VisitJoin(statement.Joins);
State.Write(Symbols.SET);
VisitTokenSet(statement.Set);
VisitWhereToken(statement.Where);
VisitTopToken(statement.Top);
//VisitFromToken(statement.RecordsetSource);
}
/// <summary>Executes <see cref="UpdateStatement"/>. Bulk updates are executed in multiple round trips - one update at a time.</summary>
public int Execute(UpdateStatement update, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
if (IsBulkUpdate(update))
return ExecuteBulkUpdate(update, dbms, conn, out lastExecutedCommandInfo, cmdTimeout);
else
return ExecuteSingleTableUpdate(update, dbms, conn, out lastExecutedCommandInfo, cmdTimeout);
}
public void Update_Statement_With_From_Join_And_Where_Clauses()
{
// Exercise
UpdateStatement statement = ParserFactory.Execute <UpdateStatement>(
@"update t set field = 1 from dbo.table as t join dbo.other o on o.id = a.id where field <> 2"
).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("t", statement.TableName);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("1", statement.Fields[0].Expression.Value);
Assert.AreEqual(1, statement.From.Count);
Assert.AreEqual("dbo.table", statement.From[0].Name);
Assert.AreEqual("t", statement.From[0].Alias.Name);
Assert.AreEqual(AliasType.As, statement.From[0].Alias.Type);
Assert.AreEqual(1, statement.From[0].Joins.Count);
Join join = statement.From[0].Joins[0];
Assert.AreEqual("dbo.other", join.Name);
Assert.AreEqual("o", join.Alias.Name);
Assert.AreEqual(AliasType.Implicit, join.Alias.Type);
Assert.IsTrue(statement.Where is CriteriaExpression);
CriteriaExpression criteriaExpression = ( CriteriaExpression )statement.Where;
Assert.AreEqual("field <> 2", statement.Where.Value);
Assert.AreEqual("field", criteriaExpression.Left.Value);
Assert.AreEqual("<>", criteriaExpression.Operator);
Assert.AreEqual("2", criteriaExpression.Right.Value);
}
public static UpdateStatement CreateUpdateStatement(IMapping mapping)
{
UpdateStatement statement = new UpdateStatement(mapping.TableName);
foreach (var item in mapping.Columns)
{
var pinfo = (PropertyInfo)item.Property;
SqlParameter prm;
if (!item.IsDbGenerated && !item.IsPrimaryKey)
{
if (pinfo.PropertyType.IsGenericType && pinfo.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
prm = statement.CreateParameter(item.Name, pinfo.PropertyType.GetGenericArguments()[0]);
}
else
{
prm = statement.CreateParameter(item.Name, pinfo.PropertyType);
}
statement.Column(item.ColumnName, prm);
}
else
{
prm = statement.CreateParameter(item.Name, pinfo.PropertyType);
statement.Where(new Condition(item.ColumnName, Comparisons.Equals, prm));
}
}
return(statement);
}
public void Visit(UpdateStatement updateStatement)
{
var froms = (updateStatement.FromList ?? Enumerable.Empty <IFrom>()).OfType <ReferenceFrom>();
foreach (var from in froms)
{
if (from.Name.IdentifierParts.Where(x => !string.IsNullOrWhiteSpace(x)).Count() == 1)
{
from.Name.IdentifierParts = new List <string>()
{
"dbo", from.Name.IdentifierParts.Last()
};
}
}
var target = updateStatement.Target as ReferenceFrom;
if (target == null)
{
return;
}
if (!froms.Any(x => (x.Alias ?? x.Name.Identifier) == target.Name.Identifier))
{
target.Name.IdentifierParts = new List <string>()
{
"dbo", target.Name.IdentifierParts.Last()
};
}
}
public void Update_TypeCast()
{
var expression = CeqlUtils.GetPropertySelectionExpression <Customer>("CreationDate");
var sql = new UpdateStatement <Customer>(expression).Values("2019-01-01").Sql;
Assert.IsTrue(sql == "UPDATE `CEQL_TEST`.`CUSTOMER` T0 SET T0.CREATION_DT = '2019-01-01'");
}
public void Visit(UpdateStatement updateStatement)
{
if (_innerVisitor != null)
{
updateStatement.Accept(_innerVisitor);
}
if (updateStatement.TopExpression != null)
{
updateStatement.TopExpression.Accept(this);
}
updateStatement.Target.Accept(this);
if (updateStatement.SetColumnList != null)
{
foreach (var column in updateStatement.SetColumnList)
{
column.Accept(this);
}
}
if (updateStatement.FromList != null)
{
foreach (var from in updateStatement.FromList)
{
from.Accept(this);
}
}
if (updateStatement.WhereClause != null)
{
updateStatement.WhereClause.Accept(this);
}
}
public void ExecuteTest_Select_Schema_Alias()
{
Table table = new Table {
Name = "EMPLOYEES", Alias = new Alias(null)
{
Name = null
}, Schema = "HR"
};
Table table2 = new Table {
Name = "JOBS", Alias = new Alias(null)
{
Name = "JB", Type = AliasType.As
}, Schema = "HR"
};
string sql = string.Format("UPDATE {0}.{1} SET ID = (SELECT id1, id2 FROM {2}.{3} AS {4})", table.Schema, table.Name, table2.Schema, table2.Name, table2.Alias.Name);
IList <IStatement> statements = ParserFactory.Execute(sql);
Assert.AreEqual(1, statements.Count);
IStatement actualStatement = statements[0];
UpdateStatement actual = actualStatement as UpdateStatement;
Assert.IsNotNull(actual);
Assert.AreEqual(2, actual.Tables.Count);
Assert.IsTrue(AreTablesEqual(table, actual.Tables[0]));
Assert.IsTrue(AreTablesEqual(table2, actual.Tables[1]));
}
protected override object InternalVisit(UpdateStatement node)
{
//TODO:node.OptimizerHints
//TODO:node.WithCtesAndXmlNamespaces
return(Visit <SQLExecutionResult>(node.UpdateSpecification));
}
/// <summary>
/// Executes <see cref="UpdateStatement{TEntity}"/> and updates cached objects
/// </summary>
/// <param name="stmt"></param>
/// <typeparam name="TEntity"></typeparam>
public void Update <TEntity>(UpdateStatement <TEntity> stmt)
{
var conn = _connection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using var cmd = conn.CreateCommand();
cmd.CommandText = stmt.AsSqlString();
cmd.Transaction = _transaction;
foreach (var(type, id, value) in stmt.GetParameters())
{
var param = cmd.CreateParameter();
param.Value = value;
param.DbType = type;
param.ParameterName = id;
cmd.Parameters.Add(param);
}
using var reader = new CachedDataReader(
_ctx.Cache,
stmt.Entity,
cmd.ExecuteReader(),
_ctx
);
// consume update query to execute reader
foreach (var _ in reader.ReadAll().ToList())
{
}
}
protected override void VisitUpdate(UpdateStatement statement)
{
State.Write(Symbols.UPDATE);
if (statement.Only)
{
State.Write(Symbols.ONLY);
}
VisitToken(statement.Target, true);
State.Write(Symbols.SET);
VisitTokenSet(statement.Set);
if (statement.RecordsetSource != null)
{
State.Write(Symbols.FROM);
VisitFromToken(statement.RecordsetSource);
}
VisitCRUDJoinToken(statement.Joins, true);
VisitWhereToken(statement.Where);
VisitCRUDJoinOnToken(statement.Joins, (statement.Where != null));
VisitWhereCurrentOfToken(statement.CursorName);
VisitReturningToken(statement.Output, statement.OutputInto);
}
protected override void VisitWhere(IExpressionVisitor visitor, UpdateStatement statement, IExpression where)
{
/*
* 注意：由于 MySQL 的 UPDATE 语句不支持 FROM 子句，因此必须将其改写为多表修改的语法。
* 由于 FROM 子句中可能包含 JOIN 类型语句，所以必须将 JOIN 子句中的条件式添加到 UPDATE 语句的 WHERE 子句中。
*/
if (statement.HasFrom)
{
var expressions = Expression.Block(BlockExpressionDelimiter.Space);
foreach (var source in statement.From)
{
if (source is JoinClause join)
{
expressions.Add(join.Type == JoinType.Inner ? Expression.Literal("AND") : Expression.Literal("OR"));
expressions.Add(join.Condition);
}
}
if (expressions.Count > 0)
{
expressions.Insert(0, where);
where = expressions;
}
}
//调用基类同名方法
base.VisitWhere(visitor, statement, where);
}
private Statement Create(XElement statementNode)
{
Enum.TryParse <StatementType>(statementNode.Name.LocalName, out var statementType);
Statement statement = null;
switch (statementType)
{
case StatementType.Insert:
statement = new InsertStatement();
break;
case StatementType.Select:
statement = new SelectStatement();
break;
case StatementType.Update:
statement = new UpdateStatement();
break;
case StatementType.Delete:
statement = new DeleteStatement();
break;
case StatementType.Statement:
default:
statement = new Statement();
break;
}
statement.Id = statementNode.Attribute("Id").Value;
statement.SqlTags = new List <ITag>();
return(statement);
}
/*
* BNF:
* <update statement> ::=
* update <table identifier>
* set <update column expression commalist>
* [<where clause>]
*/
protected Statement UpdateStatement(Lexer lexer)
{
UpdateStatement statement = new UpdateStatement();
lexer.NextToken();
statement.UpdateClause = new UpdateClause();
statement.UpdateClause.TableExpression = new TableExpression(QualifiedIdentifier(lexer));
lexer.NextToken().CheckSymbol(Keywords.Set);
do
{
statement.UpdateClause.Columns.Add(UpdateColumnExpression(lexer));
if (lexer.PeekTokenSymbol(1) == Keywords.ListSeparator)
{
lexer.NextToken();
}
else
{
break;
}
} while (true);
if (lexer.PeekTokenSymbol(1) == Keywords.Where)
{
statement.WhereClause = WhereClause(lexer);
}
return(statement);
}
public CommandServiceTests()
{
// Create Alter Query
_alterQuery = new AlterTableStatement <TestClass>("testtableName", AlterTableStatement <TestClass> .AlterType.Create);
// Create Delete Query
_deleteQuery = new DeleteStatement <TestClass>(m => m.Id > 10);
// Create Update Query
Expression <Func <TestClass> > newExpression = () => new TestClass()
{
Id = 3
};
MemberInitExpression memberInit = newExpression.Body as MemberInitExpression;
_updateQuery = new UpdateStatement <TestClass>(
memberInit.Bindings.Cast <MemberAssignment>(),
null
);
// Create Merge Query
Expression <Func <TestClass, int> > expression = t => t.Id;
MemberExpression memberExpression = expression.Body as MemberExpression;
var mergeKeys = new List <MemberExpression>()
{
memberExpression
};
_mergeQuery = new MergeStatement <TestClass>(mergeKeys, "temporarytable", MergeStatement <TestClass> .MergeType.InsertOnly);
}
public override object VisitUpdate_stmt([NotNull] SqlParser.Update_stmtContext context)
{
var statement = new UpdateStatement();
if (context.set_clause() != null)
{
statement.Assignments = new List <AssignExpression>();
foreach (var item in context.set_clause().expr())
{
var assignment = (IExpression)VisitExpr(item);
statement.Assignments.Add(new AssignExpression
{
Column = (ColumnExpression)assignment.SubExpressions[0],
Value = assignment.SubExpressions[1]
});
}
}
if (context.qualified_table_name() != null)
{
statement.Table = (TableName)VisitQualified_table_name(context.qualified_table_name());
}
if (context.where_clause() != null)
{
statement.Where = (IExpression)VisitWhere_clause(context.where_clause());
}
return(statement);
}
protected override void VisitWhere(ExpressionVisitorContext context, UpdateStatement statement, IExpression where)
{
/*
* 注意：由于 MySQL 的 UPDATE 语句不支持 FROM 子句，因此必须将其改写为多表修改的语法。
* 由于 FROM 子句中可能包含 JOIN 类型语句，所以必须将 JOIN 子句中的条件式添加到 UPDATE 语句的 WHERE 子句中。
*/
if (statement.HasFrom)
{
var conditions = ConditionExpression.And();
foreach (var source in statement.From)
{
if (source is JoinClause join)
{
conditions.Add(join.Conditions);
}
}
if (conditions.Count > 0)
{
conditions.Add(where);
where = conditions;
}
}
//调用基类同名方法
base.VisitWhere(context, statement, where);
}
public override void ExplicitVisit(UpdateStatement node)
{
TableNameIllegalCandidate candidate = CreateAndPushCandidate();
base.ExplicitVisit(node);
PopAndSetIllegalStatement(candidate);
}
private static void AppendRelations(UpdateStatement update, DbmsType dbms, StringBuilder output)
{
if (update.Relations != null && update.Relations.Count > 0)
{
output.Append(" ");
update.Relations.RenderFromClause(update.Table, dbms, output);
}
}
public void Update_SingleField_EntireTable()
{
var sql =
new UpdateStatement <Customer>(customer => customer.CreationDate)
.Values("2019-01-01").Sql;
Assert.IsTrue(sql == "UPDATE `CEQL_TEST`.`CUSTOMER` T0 SET T0.CREATION_DT = '2019-01-01'");
}
protected override void VisitFrom(ExpressionVisitorContext context, UpdateStatement statement, ICollection <ISource> sources)
{
//生成OUTPUT(RETURNING)子句
this.VisitOutput(context, statement.Returning);
//调用基类同名方法
base.VisitFrom(context, statement, sources);
}
private void AnalyzeUpdateStatement(UpdateStatement updateStatement, ParserResults results)
{
updateStatement.PrintTSqlStatementBlockToDebugConsole();
List <TableParsingResult> temp = new List <TableParsingResult>();
Dictionary <string, List <TableParsingResult> > cteModel = new Dictionary <string, List <TableParsingResult> >();
if (updateStatement.WithCtesAndXmlNamespaces?.CommonTableExpressions.Count > 0)
{
foreach (CommonTableExpression cte in updateStatement.WithCtesAndXmlNamespaces.CommonTableExpressions)
{
AnalyzeCommonTableExpression(cteModel, cte);
}
}
if (updateStatement.UpdateSpecification.Target is NamedTableReference updateNamedTableReference)
{
string tableName = ExtraceTableNameFromNamedTableRefernce(updateNamedTableReference, out string alias);
if (updateStatement.UpdateSpecification.FromClause == null)
{
temp.AddIfNotExists(tableName, SqlOperationType.UPDATE, alias);
}
else
{
var items = ExtractTablesUsedInFromClause(updateStatement.UpdateSpecification.FromClause);
if (cteModel.Count > 0)
{
foreach (var cte in cteModel)
{
var item = items.Find(x => x.TableName == cte.Key);
if (item != null)
{
items.Remove(item);
foreach (var table in cte.Value)
{
items.AddIfNotExists(table.TableName, table.OperationType, table.Alias);
}
}
}
}
temp.AddIfNotExists(items);
var result = items.Find(x => x.Alias == tableName);
if (result != null)
{
results.AddIfNotExists(result.TableName, SqlOperationType.UPDATE, tableName);
}
}
foreach (var setClause in updateStatement.UpdateSpecification.SetClauses)
{
temp.AddIfNotExists(ExtractTablesUsedInAssignmentClause(setClause));
}
}
updateStatement.PrintTablesToDebugConsole(temp);
results.AddIfNotExists(temp);
}
public void TestWithMultipleUpdate()
{
var updateStatement = new UpdateStatement(
TestEntity,
new List<IWhereFilter>() { WhereFilters[0] },
UpdateStatements
);
Assert.AreEqual("UPDATE table_name t1 SET column1 = @a, column2 = @b WHERE t1.column2 = @test2;", updateStatement.AsSqlString());
}
private void BuildSimpleUpdate(SqlCodeObjectBuilder builder, SimpleUpdateNode node)
{
var whereExpression = ExpressionBuilder.Build(node.WhereExpression);
var assignments = UpdateAssignments(node.Columns);
var statement = new UpdateStatement(node.TableName, whereExpression, assignments);
statement.Limit = node.Limit;
builder.AddObject(statement);
}
public void TestWithWhere()
{
var updateStatement = new UpdateStatement(
TestEntity,
new List<IWhereFilter>() { WhereFilters[0] },
new List<UpdateColumnStatement>() { UpdateStatements[1] }
);
Assert.AreEqual("UPDATE table_name t1 SET column2 = @b WHERE t1.column2 = @test2;", updateStatement.AsSqlString());
}
private void OnTriggerExit(Collider other)
{
if (other.TryGetComponent(out CharacterControls _))
{
interactable = false;
UpdateStatement?.Invoke();
indicator.SetActive(false);
}
}
public void Update_SingleField_SingleRecord()
{
var sql =
new UpdateStatement <Customer>(customer => customer.CreationDate)
.Values("2019-01-01")
.Where(customer => customer.CustomerId == 1).Sql;
Assert.IsTrue(sql == "UPDATE `CEQL_TEST`.`CUSTOMER` T0 SET T0.CREATION_DT = '2019-01-01' WHERE T0.CUSTOMER_ID=1");
}
public override void ExplicitVisit(UpdateStatement node)
{
if (node.UpdateSpecification.WhereClause == null)
{
Warnings.Add(String.Format("Missing where clause in the update statement at startLine {0} and startColumn {1}",
node.StartLine, node.StartColumn));
}
base.Visit(node);
}
/// <summary>Renders UPDATE statement.</summary>
public void RenderUpdate(UpdateStatement update, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
if (update.UpdateList == null || update.UpdateList.Count == 0)
throw new InvalidOperationException(Messages.SqlUpdater_SetListIsEmpty);
output.Append("UPDATE ");
update.Table.RenderTableName(dbms, output);
AppendUpdateList(update.UpdateList, dbms, output, parameters);
AppendRelations(update, dbms, output);
AppendWhere(update.Where, dbms, output, parameters);
}
private int ExecuteSingleTableUpdate(UpdateStatement update, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
DbParameterCollection parameters = new DbParameterCollection();
StringBuilder cmdText = new StringBuilder();
RenderUpdate(update, dbms, cmdText, parameters);
string command = cmdText.ToString();
lastExecutedCommandInfo = new CommandExecutionStatistics(command);
int rowsAffected = DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text, cmdTimeout);
lastExecutedCommandInfo.StopTime();
return rowsAffected;
}
public override void Visit(UpdateStatement node) { this.action(node); }
public override void ExplicitVisit(UpdateStatement node)
{
CommandStatistics.UpdatesCount++;
base.ExplicitVisit(node);
}
/// <summary>Not implemented. Does nothing because bulk update is supported for SQL Server.</summary>
public void RenderSelect(UpdateStatement update, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
}
private static UpdateStatement CreateAtomicUpdate(UpdateStatement update, IDbTable updateTable, DataRow row)
{
UpdateStatement updateOne = new UpdateStatement(updateTable);
// Where PK defined/exists in DataTable.
foreach (IDbColumn pkPart in updateTable.PrimaryKey)
{
object pkValue = row[pkPart.Alias];
updateOne.Where.And(pkPart, pkValue);
}
foreach (UpdateExpression setExpression in update.UpdateList)
{
if (setExpression.ExpressionType == UpdateExpressionType.Null)
{
updateOne.UpdateList.Add(UpdateExpressionFactory.Null(setExpression.Column));
}
else if (setExpression.ExpressionType == UpdateExpressionType.Value)
{
object value = ((DbParameter)setExpression.Item).Value;
updateOne.UpdateList.Add(UpdateExpressionFactory.Value(setExpression.Column, value));
}
else if (setExpression.ExpressionType == UpdateExpressionType.OtherColumn)
{
IDbColumn otherCol = setExpression.SourceColumns[0];
object value = row[otherCol.Alias];
updateOne.UpdateList.Add(UpdateExpressionFactory.Value(setExpression.Column, value));
}
}
return updateOne;
}
/// <summary>Creates a <see cref="SelectStatement"/> that retrieves data that is to be updated. <b>null</b> if data dont't have to be fetched, ie. if bulk update is supported when using multiple tables.</summary>
public void RenderSelect(UpdateStatement update, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Al used objects must be cloned so that internal state of original objects is not changed.
// Clone target table.
IDbTable updateTableClone = update.Table.Clone(update.Table.Alias, update.Table.ColumnAliasesArePrefixed);
SelectStatement select = new SelectStatement(updateTableClone);
// Clone relations.
select.Relations = update.Relations.Clone();
// SearchCondition is not cloned.
select.Where = update.Where;
// Create select list that contains clones of all PKs of target rows and values of source columns used in set expressions.
select.SelectList.Add(updateTableClone.PrimaryKey);
DbRelation[] relations = select.Relations.Relations;
foreach (UpdateExpression setExpression in update.UpdateList)
{
// Add source columns.
foreach (IDbColumn originalSourceColumn in setExpression.SourceColumns)
{
// Skip columns already in list.
if (!select.SelectList.ContainsItemWithAlias(originalSourceColumn.Alias))
{
// Find cloned table that contains current source column.
// Cloned tables may be on either side, ie. parent od child, in one or more of the cloned relations. Find first occurence.
IDbTable sourceTable;
DbRelation relWithSourceTbl = Array.Find<DbRelation>(relations, (r) => r.Parent.HasEqualAliasAndNameAs(originalSourceColumn.Table));
if (relWithSourceTbl != null)
{
sourceTable = relWithSourceTbl.Parent;
}
else
{
relWithSourceTbl = Array.Find<DbRelation>(relations, (r) => r.Child.HasEqualAliasAndNameAs(originalSourceColumn.Table));
sourceTable = relWithSourceTbl.Child;
}
IDbColumn sourceColClone = sourceTable.Columns.GetByColumnName(originalSourceColumn.ColumnName);
select.SelectList.Add(sourceColClone);
}
}
}
select.Render(dbms, output, parameters);
}
public override void ExplicitVisit(UpdateStatement node)
{
UpdateStatements.Add(node);
base.ExplicitVisit(node);
}
public void UpdateData()
{
Isolate.Fake.StaticMethods(typeof(UserLookAndFeel));
Isolate.WhenCalled(() => UserLookAndFeel.Default.ActiveSkinName).WillReturn("ActiveSkinName");
var instance = Isolate.Fake.Instance<FilterDataStoreModule>();
var statement = new UpdateStatement {Operands = new CriteriaOperatorCollection(),Parameters = new QueryParameterCollection(new OperandValue("skinvalue"))};
statement.Operands.Add(new QueryOperand("Skin","Alias"));
Isolate.WhenCalled(() => instance.UpdateData(null)).CallOriginal();
instance.UpdateData(new List<UpdateStatement> { statement });
Assert.AreEqual("ActiveSkinName", statement.Parameters[0].Value);
}
public UpdateTopTable(UpdateStatement statement, Expression top, Table table)
: base(statement)
{
this.Top = top;
this.Table = table;
}
private int ExecuteBulkUpdate(UpdateStatement update, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
StringBuilder selectCmdText = new StringBuilder();
DbParameterCollection selectParams = new DbParameterCollection();
RenderSelect(update, dbms, selectCmdText, selectParams);
DataTable updateData = DbUtil.ExecuteQuery(conn, selectCmdText.ToString(), selectParams, CommandType.Text, null, cmdTimeout);
lastExecutedCommandInfo = null;
IDbTable updateTable = update.Table.Clone(update.Table.Alias, update.Table.ColumnAliasesArePrefixed);
foreach (DataRow row in updateData.Rows)
{
UpdateStatement updateOne = CreateAtomicUpdate(update, updateTable, row);
updateOne.Execute(conn);
lastExecutedCommandInfo = updateOne.LastExecutedCommandInfo;
}
return updateData.Rows.Count;
}
public void ProcessUpdateStatement(UpdateStatement updStmt)
{
String TargetType = GetFragmentType(updStmt.UpdateSpecification.Target);
switch(TargetType){
case "NamedTableReference":
var NTR =(NamedTableReference)updStmt.UpdateSpecification.Target;
var TargetObject = (NTR.SchemaObject.DatabaseIdentifier == null ? this.databaseName : NTR.SchemaObject.DatabaseIdentifier.Value) + "." +
(NTR.SchemaObject.SchemaIdentifier == null ? this.schemaName : NTR.SchemaObject.SchemaIdentifier.Value) + "." +
(NTR.SchemaObject.BaseIdentifier == null ? "" : NTR.SchemaObject.BaseIdentifier.Value);
addTargettoCurrentObject(TargetObject);
break;
}
}
public override void ExplicitVisit(UpdateStatement fragment)
{
_fragments.Add(fragment);
}
private static UpdateStatement CreateUpdateLevelStatement(IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int level)
{
// Leaf filter, if provided, must be rendered in UPDATE-WHERE clause, not in SELECT-WHERE clause.
// Target table of the subquery will an automatically genereted name - which is not used in leaf filter.
IDbTable table = rootEntity.Table;
UpdateStatement updateAtLevel = new UpdateStatement(table);
updateAtLevel.UpdateList = setExpressions;
SelectStatement queryLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.PrimaryKey);
foreach (IDbColumn pkPart in table.PrimaryKey)
{
IDbColumn subQueryPkPart = queryLevel.FromTable.Columns.GetByColumnName(pkPart.ColumnName);
queryLevel.Where.Add(PredicateFactory.Compare(pkPart, "=", subQueryPkPart));
}
updateAtLevel.Where.Add(PredicateFactory.Exists(queryLevel));
if (leafFilter != null && !leafFilter.IsEmpty)
updateAtLevel.Where.Add(leafFilter);
return updateAtLevel;
}
private static bool IsBulkUpdate(UpdateStatement update)
{
bool hasRelations = (update.Relations != null) && (update.Relations.Count > 0);
return hasRelations;
}
public UpdateTable(UpdateStatement statement, Table table)
: base(statement)
{
this.Table = table;
}
UpdateStatement ParseUpdateStatement()
{
UpdateStatement ss = new UpdateStatement();
ReadNextToken(); // skip 'update'
ss.TableName = fCurrentToken.Value;
ReadNextToken();
SkipExpected("SET");
KeyValuePair<string, Expression> kvp = parseSetItem();
ss.Set.Add(kvp.Key, kvp.Value);
while (fCurrentToken.Value == ",")
{
ReadNextToken(); // skip ,
KeyValuePair<string, Expression> kvp2 = parseSetItem();
ss.Set.Add(kvp2.Key, kvp2.Value);
}
if (fCurrentToken.Equals("FROM"))
{
ReadNextToken(); // skip 'from'
if (fCurrentToken.Type == TokenType.Word)
{
// from kısmını sorgula
string firstTableName = fCurrentToken.Value.TrimQuotation();
string firstAlias = firstTableName;
ReadNextToken();
if (fCurrentToken != null && fCurrentToken.Equals("AS"))
{
ReadNextToken();
firstAlias = fCurrentToken.Value.TrimQuotation();
}
ss.From.Add(new Join { Alias = firstAlias, TableName = firstTableName, JoinType = JoinType.Cross });
if (fCurrentToken == null)
return ss; //***
while (fCurrentToken != null &&
(fCurrentToken.Equals(",") ||
fCurrentToken.Equals("LEFT") ||
fCurrentToken.Equals("RIGHT") ||
fCurrentToken.Equals("INNER") ||
fCurrentToken.Equals("CROSS")))
{
if (fCurrentToken.Equals(","))
ReadNextToken();
ss.From.Add(parseJoin());
}
}
}
if (fCurrentToken == null)
return ss;
if (fCurrentToken.Equals("WHERE"))
{
ReadNextToken(); // skip 'where'
ss.Where = ParseExpression();
}
skipEmptyStatements();
return ss;
}
