pFad - Phone/Frame/Anonymizer/Declutterfier! Saves Data!


--- a PPN by Garber Painting Akron. With Image Size Reduction included!

URL: http://github.com/sqlkata/querybuilder/commit/d9d0441a8a1a27735e962d11fafd4bc4fc396ab4

epository-6534fbc3f5e83ac0.css" /> Merge pull request #557 from mathijs-dumon/OracleInsertMany · sqlkata/querybuilder@d9d0441 · GitHub
Skip to content

Commit d9d0441

Browse files
ahmad-moussawiahmad-moussawi
authored
Merge pull request #557 from mathijs-dumon/OracleInsertMany
Fix for Oracle insert many
2 parents 449959a + 0db74cf commit d9d0441

3 files changed

Lines changed: 130 additions & 41 deletions

File tree

‎QueryBuilder.Tests/Oracle/OracleInsertManyTests.cs‎

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
1+
using SqlKata.Compilers;
2+
using SqlKata.Tests.Infrastructure;
3+
using Xunit;
4+
5+
namespace SqlKata.Tests.Oracle
6+
{
7+
public class OracleInsertManyTests : TestSupport
8+
{
9+
private const string TableName = "Table";
10+
private readonly OracleCompiler compiler;
11+
12+
public OracleInsertManyTests()
13+
{
14+
compiler = Compilers.Get<OracleCompiler>(EngineCodes.Oracle);
15+
}
16+
17+
[Fact]
18+
public void InsertManyForOracle_ShouldRepeatColumnsAndAddSelectFromDual()
19+
{
20+
// Arrange:
21+
var cols = new[] { "Name", "Price" };
22+
23+
var data = new[] {
24+
new object[] { "A", 1000 },
25+
new object[] { "B", 2000 },
26+
new object[] { "C", 3000 },
27+
};
28+
29+
var query = new Query(TableName)
30+
.AsInsert(cols, data);
31+
32+
33+
// Act:
34+
var ctx = compiler.Compile(query);
35+
36+
// Assert:
37+
Assert.Equal($@"INSERT ALL INTO ""{TableName}"" (""Name"", ""Price"") VALUES (?, ?) INTO ""{TableName}"" (""Name"", ""Price"") VALUES (?, ?) INTO ""{TableName}"" (""Name"", ""Price"") VALUES (?, ?) SELECT 1 FROM DUAL", ctx.RawSql);
38+
}
39+
40+
[Fact]
41+
public void InsertForOracle_SingleInsertShouldNotAddALLKeywordAndNotHaveSelectFromDual()
42+
{
43+
// Arrange:
44+
var cols = new[] { "Name", "Price" };
45+
46+
var data = new[] {
47+
new object[] { "A", 1000 }
48+
};
49+
50+
var query = new Query(TableName)
51+
.AsInsert(cols, data);
52+
53+
54+
// Act:
55+
var ctx = compiler.Compile(query);
56+
57+
// Assert:
58+
Assert.Equal($@"INSERT INTO ""{TableName}"" (""Name"", ""Price"") VALUES (?, ?)", ctx.RawSql);
59+
}
60+
}
61+
}

‎QueryBuilder/Compilers/Compiler.cs‎

Lines changed: 49 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,11 @@ public partial class Compiler
1717
protected virtual string LastId { get; set; } = "";
1818
protected virtual string EscapeCharacter { get; set; } = "\\";
1919

20+
21+
protected virtual string SingleInsertStartClause { get; set; } = "INSERT INTO";
22+
protected virtual string MultiInsertStartClause { get; set; } = "INSERT INTO";
23+
24+
2025
protected Compiler()
2126
{
2227
_compileConditionMethodsProvider = new ConditionsCompilerProvider(this);
@@ -391,81 +396,84 @@ protected virtual SqlResult CompileInsertQuery(Query query)
391396
};
392397

393398
if (!ctx.Query.HasComponent("from", EngineCode))
394-
{
395399
throw new InvalidOperationException("No table set to insert");
396-
}
397400

398401
var fromClause = ctx.Query.GetOneComponent<AbstractFrom>("from", EngineCode);
399-
400402
if (fromClause is null)
401-
{
402403
throw new InvalidOperationException("Invalid table expression");
403-
}
404404

405405
string table = null;
406-
407406
if (fromClause is FromClause fromClauseCast)
408-
{
409407
table = Wrap(fromClauseCast.Table);
410-
}
411-
412408
if (fromClause is RawFromClause rawFromClause)
413409
{
414410
table = WrapIdentifiers(rawFromClause.Expression);
415411
ctx.Bindings.AddRange(rawFromClause.Bindings);
416412
}
417413

418414
if (table is null)
419-
{
420415
throw new InvalidOperationException("Invalid table expression");
421-
}
422416

423417
var inserts = ctx.Query.GetComponents<AbstractInsertClause>("insert", EngineCode);
418+
if (inserts[0] is InsertQueryClause insertQueryClause)
419+
return CompileInsertQueryClause(ctx, table, insertQueryClause);
420+
else
421+
return CompileValueInsertClauses(ctx, table, inserts.Cast<InsertClause>());
422+
}
424423

425-
if (inserts[0] is InsertClause insertClause)
426-
{
427-
var columns = string.Join(", ", WrapArray(insertClause.Columns));
428-
var values = string.Join(", ", Parameterize(ctx, insertClause.Values));
424+
protected virtual SqlResult CompileInsertQueryClause(
425+
SqlResult ctx, string table, InsertQueryClause clause)
426+
{
427+
string columns = GetInsertColumnsList(clause.Columns);
429428

430-
ctx.RawSql = $"INSERT INTO {table} ({columns}) VALUES ({values})";
429+
var subCtx = CompileSelectQuery(clause.Query);
430+
ctx.Bindings.AddRange(subCtx.Bindings);
431431

432-
if (insertClause.ReturnId && !string.IsNullOrEmpty(LastId))
433-
{
434-
ctx.RawSql += ";" + LastId;
435-
}
436-
}
437-
else
438-
{
439-
var clause = inserts[0] as InsertQueryClause;
432+
ctx.RawSql = $"{SingleInsertStartClause} {table}{columns} {subCtx.RawSql}";
440433

441-
var columns = "";
434+
return ctx;
435+
}
442436

443-
if (clause.Columns.Any())
444-
{
445-
columns = $" ({string.Join(", ", WrapArray(clause.Columns))}) ";
446-
}
437+
protected virtual SqlResult CompileValueInsertClauses(
438+
SqlResult ctx, string table, IEnumerable<InsertClause> insertClauses)
439+
{
440+
bool isMultiValueInsert = insertClauses.Skip(1).Any();
447441

448-
var subCtx = CompileSelectQuery(clause.Query);
449-
ctx.Bindings.AddRange(subCtx.Bindings);
442+
var insertInto = (isMultiValueInsert) ? MultiInsertStartClause : SingleInsertStartClause;
450443

451-
ctx.RawSql = $"INSERT INTO {table}{columns}{subCtx.RawSql}";
452-
}
444+
var firstInsert = insertClauses.First();
445+
string columns = GetInsertColumnsList(firstInsert.Columns);
446+
var values = string.Join(", ", Parameterize(ctx, firstInsert.Values));
453447

454-
if (inserts.Count > 1)
455-
{
456-
foreach (var insert in inserts.GetRange(1, inserts.Count - 1))
457-
{
458-
var clause = insert as InsertClause;
448+
ctx.RawSql = $"{insertInto} {table}{columns} VALUES ({values})";
459449

460-
ctx.RawSql += ", (" + string.Join(", ", Parameterize(ctx, clause.Values)) + ")";
450+
if (isMultiValueInsert)
451+
return CompileRemainingInsertClauses(ctx, table, insertClauses);
461452

462-
}
463-
}
453+
if (firstInsert.ReturnId && !string.IsNullOrEmpty(LastId))
454+
ctx.RawSql += ";" + LastId;
464455

456+
return ctx;
457+
}
465458

459+
protected virtual SqlResult CompileRemainingInsertClauses(SqlResult ctx, string table, IEnumerable<InsertClause> inserts)
460+
{
461+
foreach (var insert in inserts.Skip(1))
462+
{
463+
string values = string.Join(", ", Parameterize(ctx, insert.Values));
464+
ctx.RawSql += $", ({values})";
465+
}
466466
return ctx;
467467
}
468468

469+
protected string GetInsertColumnsList(List<string> columnList)
470+
{
471+
var columns = "";
472+
if (columnList.Any())
473+
columns = $" ({string.Join(", ", WrapArray(columnList))})";
474+
475+
return columns;
476+
}
469477

470478
protected virtual SqlResult CompileCteQuery(SqlResult ctx, Query query)
471479
{

‎QueryBuilder/Compilers/OracleCompiler.cs‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22
using System.Collections.Generic;
33
using System.Diagnostics;
44
using System.Linq;
5+
using System.Text.RegularExpressions;
56

67
namespace SqlKata.Compilers
78
{
@@ -12,6 +13,7 @@ public OracleCompiler()
1213
ColumnAsKeyword = "";
1314
TableAsKeyword = "";
1415
parameterPrefix = ":p";
16+
MultiInsertStartClause = "INSERT ALL INTO";
1517
}
1618

1719
public override string EngineCode { get; } = EngineCodes.Oracle;
@@ -152,5 +154,23 @@ protected override string CompileBasicDateCondition(SqlResult ctx, BasicDateCond
152154
return sql;
153155

154156
}
157+
158+
protected override SqlResult CompileRemainingInsertClauses(
159+
SqlResult ctx, string table, IEnumerable<InsertClause> inserts)
160+
{
161+
foreach (var insert in inserts.Skip(1))
162+
{
163+
string columns = GetInsertColumnsList(insert.Columns);
164+
string values = string.Join(", ", Parameterize(ctx, insert.Values));
165+
166+
string intoFormat = " INTO {0}{1} VALUES ({2})";
167+
var nextInsert = string.Format(intoFormat, table, columns, values);
168+
169+
ctx.RawSql += nextInsert;
170+
}
171+
172+
ctx.RawSql += " SELECT 1 FROM DUAL";
173+
return ctx;
174+
}
155175
}
156176
}

0 commit comments

Comments
 (0)
pFad - Phonifier reborn

Pfad - The Proxy pFad © 2024 Your Company Name. All rights reserved.




Check this box to remove all script contents from the fetched content.



Check this box to remove all images from the fetched content.


Check this box to remove all CSS styles from the fetched content.


Check this box to keep images inefficiently compressed and original size.

Note: This service is not intended for secure transactions such as banking, social media, email, or purchasing. Use at your own risk. We assume no liability whatsoever for broken pages.


Alternative Proxies:

Alternative Proxy

pFad Proxy

pFad v3 Proxy

pFad v4 Proxy