Skip to content

Commit cc22f38

Browse files
PhenXjzabroski
PhenX
and
jzabroski
authored
Bugfix/1827 raw sql (#1904)
* Support raw SQL in Update clause #1827 * Add support for raw SQL for DELETE and UPDATE statements Remove specific implementations for delete and update generators that were the same as the base implementation * Fix unit tests * Adapt postgresql generator to add ending semicolon on DELETE and UPDATE statements, and adapt unit tests * Fix tests and generators for PostgreSQL, DB2 and Redshift --------- Co-authored-by: John Zabroski <[email protected]>
1 parent 2703cb9 commit cc22f38

12 files changed

Lines changed: 237 additions & 261 deletions

File tree

‎src/FluentMigrator.Runner.Core/Generators/Generic/GenericGenerator.cs‎

Lines changed: 51 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -304,25 +304,64 @@ public override string Generate(UpdateDataExpression expression)
304304
var updateItems = new List<string>();
305305
var whereClauses = new List<string>();
306306

307-
foreach (var item in expression.Set)
308-
{
309-
updateItems.Add(string.Format("{0} = {1}", Quoter.QuoteColumnName(item.Key), Quoter.QuoteValue(item.Value)));
310-
}
307+
GenerateUpdateSet(expression, updateItems);
311308

312-
if(expression.IsAllRows)
309+
if (expression.IsAllRows)
313310
{
314311
whereClauses.Add("1 = 1");
315312
}
316313
else
317314
{
318-
foreach (var item in expression.Where)
315+
GenerateWhere(expression.Where, whereClauses);
316+
}
317+
318+
return string.Format(UpdateData, Quoter.QuoteTableName(expression.TableName, expression.SchemaName), string.Join(", ", updateItems.ToArray()), string.Join(" AND ", whereClauses.ToArray()));
319+
}
320+
321+
/// <summary>
322+
/// Generates the SET clause for UPDATE statements
323+
/// </summary>
324+
/// <param name="expression">The Update expression with a SET</param>
325+
/// <param name="updateItems">The key value pair that is going to be populated</param>
326+
protected virtual void GenerateUpdateSet(UpdateDataExpression expression, List<string> updateItems)
327+
{
328+
foreach (var item in expression.Set)
329+
{
330+
var leftPart = item.Key == "" ? "" : $"{Quoter.QuoteColumnName(item.Key)} = ";
331+
332+
updateItems.Add($"{leftPart}{Quoter.QuoteValue(item.Value)}");
333+
}
334+
}
335+
336+
/// <summary>
337+
/// Generates the WHERE clause for UPDATE and DELETE statements
338+
/// </summary>
339+
/// <param name="where">The where expression</param>
340+
/// <param name="whereClauses">The key value pair that is going to be populated</param>
341+
protected virtual void GenerateWhere(List<KeyValuePair<string, object>> where, List<string> whereClauses)
342+
{
343+
foreach (var item in where)
344+
{
345+
string op;
346+
347+
if (item.Value == null || item.Value == DBNull.Value)
348+
{
349+
op = "IS ";
350+
}
351+
else if (item.Value is RawSql)
319352
{
320-
var op = item.Value == null || item.Value == DBNull.Value ? "IS" : "=";
321-
whereClauses.Add(string.Format("{0} {1} {2}", Quoter.QuoteColumnName(item.Key),
322-
op, Quoter.QuoteValue(item.Value)));
353+
op = "";
323354
}
355+
else
356+
{
357+
op = "= ";
358+
}
359+
360+
// When the key is an empty string it means the value will contain the column name
361+
var columnName = item.Key == "" ? "" : $"{Quoter.QuoteColumnName(item.Key)} ";
362+
363+
whereClauses.Add($"{columnName}{op}{Quoter.QuoteValue(item.Value)}");
324364
}
325-
return string.Format(UpdateData, Quoter.QuoteTableName(expression.TableName, expression.SchemaName), string.Join(", ", updateItems.ToArray()), string.Join(" AND ", whereClauses.ToArray()));
326365
}
327366

328367
public override string Generate(DeleteDataExpression expression)
@@ -338,16 +377,8 @@ public override string Generate(DeleteDataExpression expression)
338377
foreach (var row in expression.Rows)
339378
{
340379
var whereClauses = new List<string>();
341-
foreach (KeyValuePair<string, object> item in row)
342-
{
343-
var op = item.Value == null || item.Value == DBNull.Value ? "IS" : "=";
344-
whereClauses.Add(
345-
string.Format(
346-
"{0} {1} {2}",
347-
Quoter.QuoteColumnName(item.Key),
348-
op,
349-
Quoter.QuoteValue(item.Value)));
350-
}
380+
381+
GenerateWhere(row, whereClauses);
351382

352383
deleteItems.Add(string.Format(DeleteData, Quoter.QuoteTableName(expression.TableName, expression.SchemaName), string.Join(" AND ", whereClauses.ToArray())));
353384
}

‎src/FluentMigrator.Runner.Db2/Generators/Db2/Db2Generator.cs‎

Lines changed: 5 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,11 @@ public Db2Generator(
5050
{
5151
}
5252

53+
protected override StringBuilder AppendSqlStatementEndToken(StringBuilder stringBuilder)
54+
{
55+
return stringBuilder.Append(" ");
56+
}
57+
5358
public override string Generate(Expressions.AlterDefaultConstraintExpression expression)
5459
{
5560
return string.Format(
@@ -223,35 +228,6 @@ public override string Generate(Expressions.DeleteForeignKeyExpression expressio
223228
constraintName);
224229
}
225230

226-
public override string Generate(Expressions.DeleteDataExpression expression)
227-
{
228-
if (expression.IsAllRows)
229-
{
230-
return string.Format("DELETE FROM {0}", Quoter.QuoteTableName(expression.TableName, expression.SchemaName));
231-
}
232-
else
233-
{
234-
var deleteExpressions = new StringBuilder();
235-
foreach (var row in expression.Rows)
236-
{
237-
var clauses = row.Aggregate(new StringBuilder(), (acc, rowVal) =>
238-
{
239-
var accumulator = acc.Length == 0 ? string.Empty : " AND ";
240-
var clauseOperator = rowVal.Value == null || rowVal.Value == DBNull.Value
241-
? "IS"
242-
: "=";
243-
244-
return acc.AppendFormat("{0}{1} {2} {3}", accumulator, Quoter.QuoteColumnName(rowVal.Key), clauseOperator, Quoter.QuoteValue(rowVal.Value));
245-
});
246-
247-
var separator = deleteExpressions.Length > 0 ? " " : string.Empty;
248-
deleteExpressions.AppendFormat("{0}DELETE FROM {1} WHERE {2}", separator, Quoter.QuoteTableName(expression.TableName, expression.SchemaName), clauses);
249-
}
250-
251-
return deleteExpressions.ToString();
252-
}
253-
}
254-
255231
public override string Generate(Expressions.RenameColumnExpression expression)
256232
{
257233
return CompatibilityMode.HandleCompatibility("This feature not directly supported by most versions of DB2.");
@@ -287,30 +263,6 @@ public override string Generate(Expressions.InsertDataExpression expression)
287263
return sb.ToString();
288264
}
289265

290-
public override string Generate(Expressions.UpdateDataExpression expression)
291-
{
292-
var updateClauses = expression.Set.Aggregate(new StringBuilder(), (acc, newRow) =>
293-
{
294-
var accumulator = acc.Length == 0 ? string.Empty : ", ";
295-
return acc.AppendFormat("{0}{1} = {2}", accumulator, Quoter.QuoteColumnName(newRow.Key), Quoter.QuoteValue(newRow.Value));
296-
});
297-
298-
if (expression.IsAllRows)
299-
{
300-
return string.Format("UPDATE {0} SET {1}", Quoter.QuoteTableName(expression.TableName, expression.SchemaName), updateClauses);
301-
}
302-
303-
var whereClauses = expression.Where.Aggregate(new StringBuilder(), (acc, rowVal) =>
304-
{
305-
var accumulator = acc.Length == 0 ? string.Empty : " AND ";
306-
var clauseOperator = rowVal.Value == null || rowVal.Value == DBNull.Value ? "IS" : "=";
307-
308-
return acc.AppendFormat("{0}{1} {2} {3}", accumulator, Quoter.QuoteColumnName(rowVal.Key), clauseOperator, Quoter.QuoteValue(rowVal.Value));
309-
});
310-
311-
return string.Format("UPDATE {0} SET {1} WHERE {2}", Quoter.QuoteTableName(expression.TableName, expression.SchemaName), updateClauses, whereClauses);
312-
}
313-
314266
public override string Generate(Expressions.CreateTableExpression expression)
315267
{
316268
return string.Format("CREATE TABLE {0} ({1})", Quoter.QuoteTableName(expression.TableName, expression.SchemaName), Column.Generate(expression.Columns, expression.TableName));

‎src/FluentMigrator.Runner.Postgres/Generators/Postgres/PostgresGenerator.cs‎

Lines changed: 8 additions & 65 deletions
Original file line numberDiff line numberDiff line change
@@ -84,6 +84,14 @@ public override bool IsAdditionalFeatureSupported(string feature) =>
8484
public override string AlterColumn { get { return "ALTER TABLE {0} {1};"; } }
8585
public override string RenameColumn { get { return "ALTER TABLE {0} RENAME COLUMN {1} TO {2};"; } }
8686

87+
public override string UpdateData { get { return "UPDATE {0} SET {1} WHERE {2};"; } }
88+
public override string DeleteData { get { return "DELETE FROM {0} WHERE {1};"; } }
89+
90+
protected override StringBuilder AppendSqlStatementEndToken(StringBuilder stringBuilder)
91+
{
92+
return stringBuilder.Append(" ");
93+
}
94+
8795
public override string Generate(AlterTableExpression expression)
8896
{
8997
var alterStatement = new StringBuilder();
@@ -519,71 +527,6 @@ public override string Generate(AlterDefaultConstraintExpression expression)
519527
((PostgresColumn)Column).FormatAlterDefaultValue(expression.ColumnName, expression.DefaultValue));
520528
}
521529

522-
public override string Generate(DeleteDataExpression expression)
523-
{
524-
var result = new StringBuilder();
525-
526-
if (expression.IsAllRows)
527-
{
528-
result.AppendFormat("DELETE FROM {0};", Quoter.QuoteTableName(expression.TableName, expression.SchemaName));
529-
}
530-
else
531-
{
532-
foreach (var row in expression.Rows)
533-
{
534-
var where = string.Empty;
535-
var i = 0;
536-
537-
foreach (var item in row)
538-
{
539-
if (i != 0)
540-
{
541-
where += " AND ";
542-
}
543-
544-
var op = item.Value == null || item.Value == DBNull.Value ? "IS" : "=";
545-
where += string.Format("{0} {1} {2}", Quoter.QuoteColumnName(item.Key), op, Quoter.QuoteValue(item.Value));
546-
i++;
547-
}
548-
549-
result.AppendFormat("DELETE FROM {0} WHERE {1};", Quoter.QuoteTableName(expression.TableName, expression.SchemaName), where);
550-
}
551-
}
552-
553-
return result.ToString();
554-
}
555-
556-
public override string Generate(UpdateDataExpression expression)
557-
{
558-
var updateItems = new List<string>();
559-
var whereClauses = new List<string>();
560-
561-
foreach (var item in expression.Set)
562-
{
563-
updateItems.Add(string.Format("{0} = {1}", Quoter.QuoteColumnName(item.Key), Quoter.QuoteValue(item.Value)));
564-
}
565-
566-
if (expression.IsAllRows)
567-
{
568-
whereClauses.Add("1 = 1");
569-
}
570-
else
571-
{
572-
foreach (var item in expression.Where)
573-
{
574-
var op = item.Value == null || item.Value == DBNull.Value ? "IS" : "=";
575-
whereClauses.Add(string.Format("{0} {1} {2}", Quoter.QuoteColumnName(item.Key),
576-
op, Quoter.QuoteValue(item.Value)));
577-
}
578-
}
579-
580-
return string.Format(
581-
"UPDATE {0} SET {1} WHERE {2};",
582-
Quoter.QuoteTableName(expression.TableName, expression.SchemaName),
583-
string.Join(", ", updateItems.ToArray()),
584-
string.Join(" AND ", whereClauses.ToArray()));
585-
}
586-
587530
public override string Generate(AlterSchemaExpression expression)
588531
{
589532
return string.Format("ALTER TABLE {0} SET SCHEMA {1};", Quoter.QuoteTableName(expression.TableName, expression.SourceSchemaName), Quoter.QuoteSchemaName(expression.DestinationSchemaName));

‎src/FluentMigrator.Runner.Redshift/Generators/Redshift/RedshiftGenerator.cs‎

Lines changed: 8 additions & 65 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,9 @@ namespace FluentMigrator.Runner.Generators.Redshift
3232
{
3333
public class RedshiftGenerator : GenericGenerator
3434
{
35+
public override string UpdateData { get { return "UPDATE {0} SET {1} WHERE {2};"; } }
36+
public override string DeleteData { get { return "DELETE FROM {0} WHERE {1};"; } }
37+
3538
public RedshiftGenerator()
3639
: this(new RedshiftQuoter())
3740
{
@@ -50,6 +53,11 @@ public RedshiftGenerator(
5053
{
5154
}
5255

56+
protected override StringBuilder AppendSqlStatementEndToken(StringBuilder stringBuilder)
57+
{
58+
return stringBuilder.Append(" ");
59+
}
60+
5361
public override string Generate(AlterTableExpression expression)
5462
{
5563
var alterStatement = new StringBuilder();
@@ -215,71 +223,6 @@ public override string Generate(AlterDefaultConstraintExpression expression)
215223
((RedshiftColumn)Column).FormatAlterDefaultValue(expression.ColumnName, expression.DefaultValue));
216224
}
217225

218-
public override string Generate(DeleteDataExpression expression)
219-
{
220-
var result = new StringBuilder();
221-
222-
if (expression.IsAllRows)
223-
{
224-
result.AppendFormat("DELETE FROM {0};", Quoter.QuoteTableName(expression.TableName, expression.SchemaName));
225-
}
226-
else
227-
{
228-
foreach (var row in expression.Rows)
229-
{
230-
var where = string.Empty;
231-
var i = 0;
232-
233-
foreach (var item in row)
234-
{
235-
if (i != 0)
236-
{
237-
where += " AND ";
238-
}
239-
240-
var op = item.Value == null || item.Value == DBNull.Value ? "IS" : "=";
241-
where += string.Format("{0} {1} {2}", Quoter.QuoteColumnName(item.Key), op, Quoter.QuoteValue(item.Value));
242-
i++;
243-
}
244-
245-
result.AppendFormat("DELETE FROM {0} WHERE {1};", Quoter.QuoteTableName(expression.TableName, expression.SchemaName), where);
246-
}
247-
}
248-
249-
return result.ToString();
250-
}
251-
252-
public override string Generate(UpdateDataExpression expression)
253-
{
254-
var updateItems = new List<string>();
255-
var whereClauses = new List<string>();
256-
257-
foreach (var item in expression.Set)
258-
{
259-
updateItems.Add(string.Format("{0} = {1}", Quoter.QuoteColumnName(item.Key), Quoter.QuoteValue(item.Value)));
260-
}
261-
262-
if (expression.IsAllRows)
263-
{
264-
whereClauses.Add("1 = 1");
265-
}
266-
else
267-
{
268-
foreach (var item in expression.Where)
269-
{
270-
var op = item.Value == null || item.Value == DBNull.Value ? "IS" : "=";
271-
whereClauses.Add(string.Format("{0} {1} {2}", Quoter.QuoteColumnName(item.Key),
272-
op, Quoter.QuoteValue(item.Value)));
273-
}
274-
}
275-
276-
return string.Format(
277-
"UPDATE {0} SET {1} WHERE {2};",
278-
Quoter.QuoteTableName(expression.TableName, expression.SchemaName),
279-
string.Join(", ", updateItems.ToArray()),
280-
string.Join(" AND ", whereClauses.ToArray()));
281-
}
282-
283226
public override string Generate(AlterSchemaExpression expression)
284227
{
285228
return string.Format(

0 commit comments

Comments
 (0)