Introduce upsert_row QOL function

[majdisorder]

Is your feature request related to a problem? Please describe.

It would be nice to have an upsert function.

Describe the solution you'd like

Here is how I currently solved it in gdscript.

NOTE: it would make sense to replace the ìd_col parameter by a conditions parameter to be more in line with other QOL functions like select_rows and update_rows

const __upsert_query: String = "SELECT %s FROM %s WHERE %s = ? LIMIT 1;" 

# checks arguments and logs debug msgs
static func __guard(db: SQLite, table: String, id_col: String, row_data: Dictionary) -> bool:
    return Guard.check_null_or_empty(db, "db") \
    and Guard.check_null_or_empty(table, "table") \
    and Guard.check_null_or_empty(id_col, "id_col") \
    and Guard.check_has(row_data, id_col, "row_data")

static func upsert_row(db: SQLite, table: String, id_col: String, row_data: Dictionary) -> void:
    if not __guard(db, table, id_col, row_data): return
    var safe_table = escape_db_token(table) # escapes table name
    var safe_id_col = escape_db_token(id_col) # escapes column name
    var safe_row = escape_column_names(row_data) # escapes all property names in the dictionary
    db.query_with_bindings(
        __upsert_query % [safe_id_col, safe_table, safe_id_col], 
        [row_data[id_col]]
    )
    if db.query_result.is_empty():
        db.insert_row(safe_table, safe_row)
        return
    db.update_rows(
        safe_table,
        "%s = '%s'" % [safe_id_col, row_data[id_col]],
        safe_row
    )

EDIT: I just noticed the code above does not account for the fact that id_col may already be escaped or that the corresponding property in row_datamay be escaped. That would be something to keep in mind.

[majdisorder]

Upon further investigation I realized that sqlite actually has syntax for this. https://www.sqlite.org/lang_upsert.html

INSERT INTO my_table(id, col1, col2) 
VALUES
    ('id1','value1', 1),
    ('id2','value2', 2),
    ('id3','value3', 3)
  ON CONFLICT(id) DO 
  UPDATE SET 
    col1=excluded.col1,
    col2=excluded.col2;