adding InsertOrUpdate

[ahmad-moussawi]

Update data if certain conditions are met, insert the data otherwise.

[ ] MySql: on duplicate key update
[ ] SqlServer: #93
[ ] Postgres: (don't know)

[fowl2]

MySQL: ON DUPLICATE KEY UPDATE

Sqlite: uses a special ON CONFLICT clause, added in version 3.24.0 (2018-06-04).

Postgres: supports ON CONFLICT and ON DUPLICATE KEY UPDATE which are different.

Firebird: has UPDATE OR INSERT.

SQL Server: doesn't have a upsert, but MERGE can be used to the same effect.

Oracle: doesn't appear to have upsert, but does have MERGE, with some caveats.

MERGE is also supported by Postgres and is more powerful so might be worth investigating as a separate command as well.

[msdbcardoso]

Any update on this ?

[poostwoud]

We are looking for something similar. Currently we are using SQL Server. This would allow use to use an IF EXISTS ... THEN ... ELSE ... construct. I believe at least Postgres supports something like this as well. An example declaration would be:

Program.cs

using SqlKata;
using SqlKata.Compilers;

//*****
var compiler = new SqlServerCompiler();

//*****
var upsertQuery = new Query("dbo.People")
    .Where("Id", Guid.NewGuid(), true)
    .Where("Version", "<", 10) //***** Concurrency;
    .AsUpsert(new Dictionary<string, object>
    {
        { "FirstName", "John" },
        { "LastName", "Doe" }
    });

//*****
Console.WriteLine(compiler.Compile(upsertQuery).Sql);

//*****
Console.Read();

Result

IF EXISTS (SELECT TOP 1 1 FROM [dbo].[People] WHERE [Id] = @p0)
        UPDATE [dbo].[People] SET [FirstName] = @p1, [LastName] = @p2 WHERE [Id] = @p3 AND [Version] < @p4
ELSE
        INSERT INTO [dbo].[People] ([FirstName], [LastName], [Id], [Version]) VALUES (@p5, @p6, @p7, @p8)

This implementation allows for reusing all the existing components. The only required "tweak" is the addition of the IsKey property to the AbstractCondition to allow for conditions to be recognized as a condition used for matching a key field. In the example above the Version condition is added as a concurrency condition to be used in the where clause of the update statement, but not for the initial select.

It required the following changes:

SqlKata.Executions\Query.Extensions.cs

public static int Upsert(this Query query, IEnumerable<KeyValuePair<string, object>> values, IDbTransaction transaction = null, int? timeout = null)
{
    return CreateQueryFactory(query).Execute(query.AsUpsert(values), transaction, timeout);
}

QueryBuilder_Query.Upsert.cs_

public Query AsUpsert(IEnumerable<KeyValuePair<string, object>> values)
{
    if (values == null || values.Any() == false)
    {
        throw new InvalidOperationException($"{values} cannot be null or empty");
    }

    Method = "upsert";

    ClearComponent("upsert").AddComponent("upsert", new InsertClause
    {
        Columns = values.Select(x => x.Key).ToList(),
        Values = values.Select(x => x.Value).ToList(),
    });

    return this;
}

QueryBuilder\Clauses\ConditionClause.cs

public abstract class AbstractCondition : AbstractClause
{
    public bool IsOr { get; set; } = false;
    public bool IsNot { get; set; } = false;

    /// <summary>
    /// Set True if the condition describes a key field being matched for equality.
    /// </summary>
    public bool IsKey { get; set; } = false;
}

QueryBuilder\Base.Where.cs

public abstract partial class BaseQuery<Q>
{
    public Q Where(string column, string op, object value, bool isKey = false)
    {
        // omitted
        return AddComponent("where", new BasicCondition
        {
            Column = column,
            Operator = op,
            Value = value,
            IsOr = GetOr(),
            IsNot = GetNot(),
            IsKey = isKey
        });
    }

    // omitted
}

QueryBuilder\Compilers\Compiler.cs

protected virtual SqlResult CompileRaw(Query query)
{
    SqlResult ctx;

    if (query.Method == "upsert")
    {
        ctx = CompileUpsertQuery(query);
    }

    // omitted
}

protected virtual SqlResult CompileUpsertQuery(Query query)
{
    //*****
    var ctx = new SqlResult { Query = query };

    //***** From;
    if (!(ctx.Query.GetOneComponent<AbstractFrom>("from", EngineCode) is FromClause fromClause))
        throw new InvalidOperationException("No table set to upsert");

    //***** Select;
    var selectQuery = new Query(fromClause.Table);
    var updateQuery = new Query(fromClause.Table);
    var insertQuery = new Query(fromClause.Table);

    //***** Conditions for select and update;
    if (!ctx.Query.HasComponent("where", EngineCode))
        throw new InvalidOperationException("No where conditions set. Requires at least one key column");

    var conditions = ctx.Query.GetComponents<AbstractCondition>("where", EngineCode);
    // for insert requires to be set as insert parameters.

    var selectQueryConditions = new List<AbstractCondition>();
    var updateQueryConditions = new List<AbstractCondition>();
    foreach (var condition in conditions)
    {
        if (condition.IsKey) selectQueryConditions.Add((AbstractCondition)condition.Clone());
        updateQueryConditions.Add((AbstractCondition)condition.Clone());
    }

    //*****
    selectQuery.Clauses.AddRange(selectQueryConditions);
    updateQuery.Clauses.AddRange(updateQueryConditions);

    //*****
    var updateInsertClause = ctx.Query.GetOneComponent<InsertClause>("upsert", EngineCode);
    updateInsertClause = (InsertClause)updateInsertClause.Clone(); //***** Doesn't do deep clone;
    updateInsertClause.Columns = new List<string>(updateInsertClause.Columns);
    updateInsertClause.Values = new List<object>(updateInsertClause.Values);
    updateInsertClause.Component = "update";
    updateQuery.Clauses.Add(updateInsertClause);

    //***** Add where as insert columns;
    var insertInsertClause = ctx.Query.GetOneComponent<InsertClause>("upsert", EngineCode);
    insertInsertClause = (InsertClause)insertInsertClause.Clone();
    insertInsertClause.Columns = new List<string>(insertInsertClause.Columns);
    insertInsertClause.Values = new List<object>(insertInsertClause.Values);
    insertInsertClause.Component = "insert";

    //***** Add conditions as insert values;
    foreach (var condition in conditions)
        if (condition is BasicCondition basicCondition)
        {
            insertInsertClause.Columns.Add(basicCondition.Column);
            insertInsertClause.Values.Add(basicCondition.Value);
        }
    insertQuery.Clauses.Add(insertInsertClause);

    //*****
    selectQuery.SelectRaw("TOP 1 1");

    //*****
    var selectCtx = CompileSelectQuery(selectQuery);
    var updateCtx = CompileUpdateQuery(updateQuery);
    var insertCtx = CompileInsertQuery(insertQuery);

    //*****
    ctx.Bindings.AddRange(selectCtx.Bindings);
    ctx.Bindings.AddRange(updateCtx.Bindings);
    ctx.Bindings.AddRange(insertCtx.Bindings);

    //*****
    ctx.RawSql = $"IF EXISTS ({selectCtx.RawSql})\r\n\t{updateCtx.RawSql}\r\nELSE\r\n\t{insertCtx.RawSql}";

    //*****
    return ctx;
}

Curious on your thoughts. Does anyone else have more experience with the other database systems for supporting this or similar fashion?

[Kipjr]

@poostwoud I'm trying to help with this case but I only have experience with SQLSERVER and MySQL.

I do have implemented this on the latest branch, but it looks like some thing are hardcoded in your proposal. Or.. I'm doing something wrong.. see "VariableName1" in the INSERT-statement

.Where("VariableName1","=", SOMEVARIABLE_VALUE, true)

IF EXISTS (SELECT TOP 1 1 FROM [Table1] WHERE [VariableName1] = @p0)
					UPDATE [Table1] SET [VariableName1] = @p1, [VariableName2] = @p2, [VariableName3] = @p3, [VariableName4] = @p4, [VariableName5] = @p5, [VariableName6] = @p6, [VariableName7] = @p7, [VariableName8] = @p8, [VariableName9] = @p9, [VariableName10] = @p10 WHERE [VariableName1] = @p11
			ELSE
					INSERT INTO [Table1] ([VariableName1], [VariableName2], [VariableName3], [VariableName4], [VariableName5], [VariableName6], [VariableName7], [VariableName8], [VariableName9], [VariableName10], [VariableName1]) VALUES (@p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22)

edit: I managed to fix the issue.

[Kipjr]

@ahmad-moussawi , enclosed is the patch I used to implement @poostwoud changes, plus some additional.
git_upsert.patch

 Program/Program.csproj                       |   38 +++++------ // update dependencies & TargetFramework
 QueryBuilder.Tests/QueryBuilder.Tests.csproj |   36 +++++------  // update dependencies & TargetFramework
 QueryBuilder/Base.Where.cs                   |    3 +
 QueryBuilder/Clauses/ConditionClause.cs      |    5 +
 QueryBuilder/Compilers/Compiler.cs           |   89 ++++++++++++++++++++++++++ // fix  new SqlResult(parameterPlaceholder, EscapeCharacter);
 QueryBuilder/Compilers/SqlServerCompiler.cs  |    2 - // fix  new SqlResult(parameterPlaceholder, EscapeCharacter);
 QueryBuilder/Query.Upsert.cs                 |   62 ++++++++++++++++++
 QueryBuilder/QueryBuilder.csproj             |   67 +++++++++-----------   // update dependencies & TargetFramework
 SqlKata.Execution/Query.Extensions.cs        |    5 +
 SqlKata.Execution/SqlKata.Execution.csproj   |   74 +++++++++++-----------  // update dependencies & TargetFramework