Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitec46621

Browse files
authored
Merge pull request#2199 from fluentmigrator/copilot/fix-delete-from-table-issue
Fix RawSql backward compatibility in DELETE and UPDATE WHERE clauses
2 parentsfcc42a9 +e709930 commitec46621

File tree

3 files changed

+241
-3
lines changed

3 files changed

+241
-3
lines changed

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

Lines changed: 48 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -425,9 +425,12 @@ protected virtual void GenerateWhere(List<KeyValuePair<string, object>> where, L
425425
{
426426
op="IS ";
427427
}
428-
elseif(item.ValueisRawSql)
428+
elseif(item.ValueisRawSqlrawSql)
429429
{
430-
op="";
430+
// When there's a column name (key is not empty), check if RawSql starts with an operator.
431+
// If not, add "= " for backward compatibility (e.g., RawSql.Insert("(SELECT ...)") should become "= (SELECT ...)").
432+
// If it does start with an operator (e.g., "= 3", "IS NULL", "IN (1,2)"), use it as-is.
433+
op=item.Key!=""&&!StartsWithOperator(rawSql.Value)?"= ":"";
431434
}
432435
else
433436
{
@@ -441,6 +444,49 @@ protected virtual void GenerateWhere(List<KeyValuePair<string, object>> where, L
441444
}
442445
}
443446

447+
/// <summary>
448+
/// Checks if a raw SQL string starts with an operator (=, IS, IN, comparison operators, LIKE, NOT, BETWEEN)
449+
/// </summary>
450+
/// <param name="rawSqlValue">The raw SQL value to check</param>
451+
/// <returns>True if the string starts with an operator, false otherwise</returns>
452+
privatestaticboolStartsWithOperator(stringrawSqlValue)
453+
{
454+
if(string.IsNullOrEmpty(rawSqlValue))
455+
{
456+
returnfalse;
457+
}
458+
459+
vartrimmed=rawSqlValue.TrimStart();
460+
if(trimmed.Length==0)
461+
{
462+
returnfalse;
463+
}
464+
465+
// Check for symbolic operators first (=, <>, !=, <, >, <=, >=)
466+
varfirstChar=trimmed[0];
467+
if(firstChar=='='||firstChar=='<'||firstChar=='>'||firstChar=='!')
468+
{
469+
returntrue;
470+
}
471+
472+
// Check for keyword operators (case-insensitive)
473+
// IN can be followed by a space or directly by a parenthesis
474+
returnStartsWithKeywordOperator(trimmed,"IS ")
475+
||StartsWithKeywordOperator(trimmed,"IN ")
476+
||StartsWithKeywordOperator(trimmed,"IN(")
477+
||StartsWithKeywordOperator(trimmed,"LIKE ")
478+
||StartsWithKeywordOperator(trimmed,"NOT ")
479+
||StartsWithKeywordOperator(trimmed,"BETWEEN ");
480+
}
481+
482+
/// <summary>
483+
/// Checks if a string starts with a specific keyword operator (case-insensitive)
484+
/// </summary>
485+
privatestaticboolStartsWithKeywordOperator(stringvalue,stringkeyword)
486+
{
487+
returnvalue.StartsWith(keyword,StringComparison.OrdinalIgnoreCase);
488+
}
489+
444490
/// <inheritdoc />
445491
publicoverridestringGenerate(DeleteDataExpressionexpression)
446492
{

‎test/FluentMigrator.Tests/Integration/MigrationRunnerTests.cs‎

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1602,11 +1602,12 @@ public void CanUseRawSqlInUpdateAndDelete(Type processorType, Func<IntegrationTe
16021602
DataSetupDs=processor.ReadTableData("TestSchema","Foo");
16031603

16041604
varrows=upDs.Tables[0].Rows;
1605-
rows.Count.ShouldBe(3);
1605+
rows.Count.ShouldBe(4);
16061606

16071607
rows[0]["Baz"].ShouldBe(1);
16081608
rows[1]["Baz"].ShouldBe(2);
16091609
rows[2]["Baz"].ShouldBe(3);
1610+
rows[3]["Baz"].ShouldBe(4);
16101611

16111612
runner.Up(newRawSqlUpdateMigration());
16121613
upDs=processor.ReadTableData("TestSchema","Foo");
@@ -1615,6 +1616,7 @@ public void CanUseRawSqlInUpdateAndDelete(Type processorType, Func<IntegrationTe
16151616
rows[0]["Baz"].ShouldBe(101);
16161617
rows[1]["Baz"].ShouldBe(102);
16171618
rows[2]["Baz"].ShouldBe(103);
1619+
rows[3]["Baz"].ShouldBe(104);
16181620

16191621
runner.Up(newRawSqlDeleteMigration());
16201622
upDs=processor.ReadTableData("TestSchema","Foo");
@@ -2129,6 +2131,10 @@ public override void Up()
21292131
{
21302132
baz=3,
21312133
})
2134+
.Row(new
2135+
{
2136+
baz=4,
2137+
})
21322138
;
21332139
}
21342140

@@ -2162,6 +2168,17 @@ public override void Up()
21622168
{
21632169
baz=RawSql.Insert("= 3")
21642170
});
2171+
2172+
// UPDATE : Raw SQL with RawSql object inside an anonymous object (backward compatibility - no explicit operator)
2173+
Update.Table("Foo").InSchema("TestSchema")
2174+
.Set(new
2175+
{
2176+
baz=RawSql.Insert("CASE WHEN baz = 4 THEN 104 ELSE 0 END")
2177+
})
2178+
.Where(new
2179+
{
2180+
baz=RawSql.Insert("4")
2181+
});
21652182
}
21662183
}
21672184

@@ -2181,6 +2198,12 @@ public override void Up()
21812198
.Row(new
21822199
{
21832200
baz=RawSql.Insert("= 103")
2201+
})
2202+
2203+
// DELETE : Raw SQL with RawSql object inside an anonymous object (backward compatibility - no explicit operator)
2204+
.Row(new
2205+
{
2206+
baz=RawSql.Insert("104")
21842207
});
21852208
}
21862209
}

‎test/FluentMigrator.Tests/Unit/Generators/SqlServer2005/SqlServer2005DataTests.cs‎

Lines changed: 169 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,10 @@
1616
//
1717
#endregion
1818

19+
usingSystem.Collections.Generic;
20+
21+
usingFluentMigrator.Expressions;
22+
usingFluentMigrator.Model;
1923
usingFluentMigrator.Runner.Generators.SqlServer;
2024
usingFluentMigrator.SqlServer;
2125

@@ -223,5 +227,170 @@ public override void CanUpdateDataWithDbNullCriteria()
223227
varresult=Generator.Generate(expression);
224228
result.ShouldBe("UPDATE [dbo].[TestTable1] SET [Name] = N'Just''in', [Age] = 25 WHERE [Id] = 9 AND [Homepage] IS NULL;");
225229
}
230+
231+
[Test]
232+
publicvoidCanDeleteDataWithRawSqlSubquery()
233+
{
234+
// Test the backward compatibility case: RawSql with a subquery should add "= " operator
235+
varexpression=newDeleteDataExpression
236+
{
237+
TableName="RolePermissions"
238+
};
239+
expression.Rows.Add(newDeletionDataDefinition
240+
{
241+
newKeyValuePair<string,object>("PermissionId",RawSql.Insert("(SELECT [Id] FROM [dbo].[Permissions] WHERE [Name] = 'Foo')"))
242+
});
243+
244+
varresult=Generator.Generate(expression);
245+
result.ShouldBe("DELETE FROM [dbo].[RolePermissions] WHERE [PermissionId] = (SELECT [Id] FROM [dbo].[Permissions] WHERE [Name] = 'Foo');");
246+
}
247+
248+
[Test]
249+
publicvoidCanDeleteDataWithRawSqlExplicitOperator()
250+
{
251+
// Test the new syntax where operator is explicitly included in RawSql
252+
varexpression=newDeleteDataExpression
253+
{
254+
TableName="TestTable"
255+
};
256+
expression.Rows.Add(newDeletionDataDefinition
257+
{
258+
newKeyValuePair<string,object>("Status",RawSql.Insert("= 3"))
259+
});
260+
261+
varresult=Generator.Generate(expression);
262+
result.ShouldBe("DELETE FROM [dbo].[TestTable] WHERE [Status] = 3;");
263+
}
264+
265+
[Test]
266+
publicvoidCanDeleteDataWithRawSqlIsNull()
267+
{
268+
// Test RawSql with IS NULL operator
269+
varexpression=newDeleteDataExpression
270+
{
271+
TableName="TestTable"
272+
};
273+
expression.Rows.Add(newDeletionDataDefinition
274+
{
275+
newKeyValuePair<string,object>("Status",RawSql.Insert("IS NULL"))
276+
});
277+
278+
varresult=Generator.Generate(expression);
279+
result.ShouldBe("DELETE FROM [dbo].[TestTable] WHERE [Status] IS NULL;");
280+
}
281+
282+
[Test]
283+
publicvoidCanDeleteDataWithRawSqlInClause()
284+
{
285+
// Test RawSql with IN clause with space
286+
varexpression=newDeleteDataExpression
287+
{
288+
TableName="TestTable"
289+
};
290+
expression.Rows.Add(newDeletionDataDefinition
291+
{
292+
newKeyValuePair<string,object>("Status",RawSql.Insert("IN (1, 2, 3)"))
293+
});
294+
295+
varresult=Generator.Generate(expression);
296+
result.ShouldBe("DELETE FROM [dbo].[TestTable] WHERE [Status] IN (1, 2, 3);");
297+
}
298+
299+
[Test]
300+
publicvoidCanDeleteDataWithRawSqlInClauseNoSpace()
301+
{
302+
// Test RawSql with IN clause without space after IN
303+
varexpression=newDeleteDataExpression
304+
{
305+
TableName="TestTable"
306+
};
307+
expression.Rows.Add(newDeletionDataDefinition
308+
{
309+
newKeyValuePair<string,object>("Status",RawSql.Insert("IN(1, 2, 3)"))
310+
});
311+
312+
varresult=Generator.Generate(expression);
313+
result.ShouldBe("DELETE FROM [dbo].[TestTable] WHERE [Status] IN(1, 2, 3);");
314+
}
315+
316+
[Test]
317+
publicvoidCanDeleteDataWithRawSqlFullWhereClause()
318+
{
319+
// Test RawSql as full WHERE clause (empty key)
320+
varexpression=newDeleteDataExpression
321+
{
322+
TableName="TestTable"
323+
};
324+
expression.Rows.Add(newDeletionDataDefinition
325+
{
326+
newKeyValuePair<string,object>("",RawSql.Insert("Status = 1 AND Active = 0"))
327+
});
328+
329+
varresult=Generator.Generate(expression);
330+
result.ShouldBe("DELETE FROM [dbo].[TestTable] WHERE Status = 1 AND Active = 0;");
331+
}
332+
333+
[Test]
334+
publicvoidCanUpdateDataWithRawSqlSubquery()
335+
{
336+
// Test backward compatibility for UPDATE with RawSql subquery in WHERE clause
337+
varexpression=newUpdateDataExpression
338+
{
339+
TableName="TestTable"
340+
};
341+
expression.Set=newList<KeyValuePair<string,object>>
342+
{
343+
newKeyValuePair<string,object>("Name","Updated")
344+
};
345+
expression.Where=newList<KeyValuePair<string,object>>
346+
{
347+
newKeyValuePair<string,object>("Id",RawSql.Insert("(SELECT MAX(Id) FROM OtherTable)"))
348+
};
349+
350+
varresult=Generator.Generate(expression);
351+
result.ShouldBe("UPDATE [dbo].[TestTable] SET [Name] = N'Updated' WHERE [Id] = (SELECT MAX(Id) FROM OtherTable);");
352+
}
353+
354+
[Test]
355+
publicvoidCanUpdateDataWithRawSqlLikeOperator()
356+
{
357+
// Test RawSql with LIKE operator
358+
varexpression=newUpdateDataExpression
359+
{
360+
TableName="TestTable"
361+
};
362+
expression.Set=newList<KeyValuePair<string,object>>
363+
{
364+
newKeyValuePair<string,object>("Name","Updated")
365+
};
366+
expression.Where=newList<KeyValuePair<string,object>>
367+
{
368+
newKeyValuePair<string,object>("Name",RawSql.Insert("LIKE 'Test%'"))
369+
};
370+
371+
varresult=Generator.Generate(expression);
372+
result.ShouldBe("UPDATE [dbo].[TestTable] SET [Name] = N'Updated' WHERE [Name] LIKE 'Test%';");
373+
}
374+
375+
[Test]
376+
publicvoidCanUpdateDataWithRawSqlComparisonOperators()
377+
{
378+
// Test RawSql with comparison operators (<, >, <=, >=, <>)
379+
varexpression=newUpdateDataExpression
380+
{
381+
TableName="TestTable"
382+
};
383+
expression.Set=newList<KeyValuePair<string,object>>
384+
{
385+
newKeyValuePair<string,object>("Status",1)
386+
};
387+
expression.Where=newList<KeyValuePair<string,object>>
388+
{
389+
newKeyValuePair<string,object>("Age",RawSql.Insert("> 18"))
390+
};
391+
392+
varresult=Generator.Generate(expression);
393+
result.ShouldBe("UPDATE [dbo].[TestTable] SET [Status] = 1 WHERE [Age] > 18;");
394+
}
226395
}
227396
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp